Re: [sqlite] TRIM Function in Python3's sqlite3 import
David - thanks for the pointer to when TRIM showed up. Python is 10 years out of date!! Wow!! Upgrade was not super difficult, I kind of figured it out with a lot of searching for answers. Searching about upgrades, I found this listing: https://www.reddit.com/r/Python/comments/3docsa/how_do_i_upgrade_the_sqlite_lib_in_python34/ It pointed me in a direction which worked on Ubuntu 17.10 and Fedora Server 27. I actually am running RHEL 5, but wanted to test the upgrade before getting back to the office. I figured Fedora Server was 'close' to RHEL. I'm using a python installed without root (setting the ./configure --prefix=). So when I saw the download options for SQLite3, I picked the one with configure and tried the same --prefix option. After make install, I had the so libraries. I got the new libraries to be used by setting the LD_LIBRARY_PATH to a value of the sqlite3-321/lib. All the python modules showed sqlite3.sqlite_version of 3.20.1 when I don't have the LD_LIBRARY_PATH set, and 3.21.00 when I do. Thanks again, Merry Christmas!! Date: Fri, 22 Dec 2017 15:39:38 + From: David Raymond <david.raym...@tomtom.com> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] TRIM Function in Python3's sqlite3 import Message-ID: < he1pr0701mb1931ec66b2de2686d238831787...@he1pr0701mb1931.eurprd07.prod.outlook.com > Content-Type: text/plain; charset="utf-8" http://www.sqlite.org/changes.html Looks like trim was added in 10 years ago in 3.4.0 (2007-06-18) Can't help you with the Linux side of things, sorry. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Ed Lipson --cut-- Thanks, Ed ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TRIM Function in Python3's sqlite3 import
I shifting some Python code to Linux from Windows. I find that TRIN i smissing from the verison on Linux. Python reports this version information sqlite.version 2.6.0 for both Windows and Linux sqlite.sqlite_version 3.3.6 for Linux, 3.15.2 for Windows. Two questions: 1. When was TRIM added as a function? 2. How do I update the RHEL Python to a version which has the TRIM function, or is there a replacement function available in 3.3.6 to which I can change the code? Thanks, Ed ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Date Formatting from Excel Float Date/Time Representation
I'm trying to work with JIRA information. I export my JIRA records and load them into a SQLite3 database via a python script. The export is in Excel format. When I send the data back from SQLite3 to Excel and have Excel format the date, it is correctly interpreted. From that I am assuming I have a 'proper' process of reading the Excel and writing the Excel, with SQLite3 as the middle. How would I format the data in a SQL statement so it looks correct? When I run: select t0.key, "Issue Type", Updated from JIRA_Stat_0_20170106124800 t0 inner join JIRA_Stat_1_20170106124800 t1 on t0.key = t1.key where "Last Comment" is not null order by assignee I get output which looks like: key Issue Type Updated TSGOPM-176 Task 42674.629861 TSGOPM-139 Task 42698.225695 The Updated column is properly reflected as a float, as that is the internal Excel format. What formatting functions can I use to get it to appear as a date time in SQL output, as it appears in Excel? I have tried strftime and date but I don't get any meaningful data. Thanks, Ed ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_FTS3_MAX_EXPR_DEPTH and upgrading sqlite
Hello all, Apologies in advance if this question has been asked and answered elsewhere – a (brief, admittedly) search did not turn up anything and so I’m posting this. We’re in the process of upgrading sqlite in our service. We were on a version which did not have the compile option SQLITE_FTS3_MAX_EXPR_DEPTH and are moving up to one that does. As it turns out we have run into a problem with one of our clients where they hit this limit now where previously the query just worked. My question is whether or not there’s any guidance on how to set this limit at compile time? Part of my confusion is that I’m not sure what would have happened previously with no limit enforced (as was the case on our older version of sqlite) - was the risk stack exhaustion and a resulting crash or was it something else entirely? Basically what I’m worried about is that we'll raise the limit to allow this one client to do their queries as they used to do, but will not know that we’ve raised it enough to allow all our clients to get the same behavior they were accustomed to? How do people choose this limit? Thanks much! Ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] YACC/Bison vs. Lemon vs. standard input
I'm trying to convert a calculator from Bison to Lemon.
I ran into an unexpected problem involving standard input where
the two programs behave quite differently. The Bison version
prints the result immediately after pressing [Enter]. With the
Lemon version, the result is delayed until I type a new
expression and press [Enter].
I've created tiny Bison and Lemon grammars and Flex scanners to
illustrate the problem. This is on Windows 7, using a July 2014
version of Lemon, Bison 2.41, and gcc (tdm64-2) 4.8.1.
*A simple session with the Bison version*
3 + 4[enter]
7
6 + 2[enter]
8
*Notice how the result is returned after pressing [Enter] after
the simple expression.*
*A simple session with the Lemon version*
3 + 4[enter]
6 + 2[enter]
7
1 + 1[enter]
8
^Z
2
*Notice how the result is only returned after the entering of a
second expression and pressing of [Enter] (ctrl Z signals
end-of-input for cmd.exe).*
What have I done wrong?
Bison/Flex version source
=
badd.l:
%{
#include "y.tab.h"
#include
%}
%%
[0-9]+ {yylval = atoi(yytext); return INTEGER;}
[+] return PLUS;
[\n] return NL;
[ \t] ; /* skip whitespace */
. {printf("Unknown character '%c'\n", yytext[0]); return 0;}
%%
int yywrap(void) {
return 1;
}
badd.y:
%{
#include
int yylex(void);
void yyerror(char *);
%}
%token INTEGER PLUS NL
%left PLUS MINUS
%%
prog: prog expr NL { printf("%d\n", $2); }
|
;
expr: INTEGER { $$ = $1; }
| expr PLUS expr { $$ = $1 + $3; }
;
%%
void yyerror(char *s) {
fprintf(stderr, "%s\n", s);
}
int main(void) {
yyparse();
return 0;
}
To build:
bison -y -d badd.y
flex badd.l
gcc y.tab.c lex.yy.c -o badd.exe
Lemon/Flex version source
=
ladd.l
%{
#include "ladd.h"
#include
extern int yylval;
%}
%%
[0-9]+ {yylval = atoi(yytext); return INTEGER;}
[+] return PLUS;
[\n] return NL;
[ \t] ; /* skip whitespace */
. {printf("Unknown character '%c'\n", yytext[0]); return 0;}
%%
int yywrap(void) {
return 1;
}
ladd.y:
%include { #include }
%syntax_error { printf("Lemon syntax error\n"); }
%token_type {int}
%left PLUS MINUS .
start ::= prog .
prog ::= prog expr(a) NL . { printf("%d\n", a); }
prog ::= .
expr(a) ::= INTEGER(b) . { a = b; }
expr(a) ::= expr(b) PLUS expr(c) . { a = b + c; }
main.c:
#include
#include
void *ParseAlloc(void *(*mallocProc)(size_t));
void ParseFree(void *p, void (*freeProc)(void*));
void Parse(void *yyp, int yymajor, int foo);
int yylex(void);
int yylval;
int main(void) {
void *pParser;
int tok;
pParser = ParseAlloc(malloc);
while ((tok = yylex()) != 0) {
Parse(pParser, tok, yylval);
}
Parse(pParser, 0, 0);
ParseFree(pParser, free );
return 0;
}
To build:
lemon ladd.y
flex ladd.l
gcc main.c ladd.c lex.yy.c -o ladd.exe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FW: Need Help with Golf Handicap Calculation
I thought LIMIT did the same thing as TOP. With both LIMIT (or TOP) and ORDER BY, my assumption (and my experience) is that the result is sorted, and then the first NN are returned. tenholde -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James K. Lowden Sent: Sunday, February 09, 2014 6:04 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] FW: Need Help with Golf Handicap Calculation On Sat, 8 Feb 2014 19:47:44 + Ed Tenholder <e...@tenholder.net> wrote: > Query: SELECT MAX(ScoreDate),AVG((Score-Rating)*(113.0/Slope))*.96 > FROM (SELECT * FROM (SELECT * FROM (SELECT > ScoreDate,Score,Rating,Slope FROM Scores WHERE Player="Joe Smith" > ORDER BY ScoreDate ASC LIMIT 3) ORDER BY ScoreDate DESC LIMIT 20) > ORDER BY (Score-Rating)*(113.0/Slope) ASC LIMIT 10) That's hard to read, so I'm relying on your description, > Logic: > > ? Select the oldest N scores (3 in the example above) > ? From that, select the 20 newest scores > ? From that, select the 10 lowest handicap-indexes: > (Score-Rating)*(113/Slope) ? Return the lowest ScoreDate and the > average of the handicap-indexes multiplied by .96 If you're learning SQL, you'll want to know that in the SQL standard LIMIT does not exist and ORDER BY is valid only to order the final output. I describe ranking rows in a general way at http://www.schemamania.org/sql/#rank.rows. HTH. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FW: Need Help with Golf Handicap Calculation
From: Ed Tenholder Sent: Saturday, February 08, 2014 1:44 PM To: 'sqlite-users@sqlite.org' Subject: Need Help with Golf Handicap Calculation I’m just trying to learn SQL, and after lots of google searches and reading posts on this email list, I’ve gotten pretty close. Table: CREATE TABLE Scores (ScoreID Integer Primary Key,ScoreDate Text,Player Text,CourseName Text,TeeName Text,Score Integer,Rating Real,Slope Integer); Query: SELECT MAX(ScoreDate),AVG((Score-Rating)*(113.0/Slope))*.96 FROM (SELECT * FROM (SELECT * FROM (SELECT ScoreDate,Score,Rating,Slope FROM Scores WHERE Player="Joe Smith" ORDER BY ScoreDate ASC LIMIT 3) ORDER BY ScoreDate DESC LIMIT 20) ORDER BY (Score-Rating)*(113.0/Slope) ASC LIMIT 10) Result: MAX(ScoreDate)AVG((Score-Rating)*(113.0/Slope))*.96 2000-05-16 29.2436825396825 Logic: • Select the oldest N scores (3 in the example above) • From that, select the 20 newest scores • From that, select the 10 lowest handicap-indexes: (Score-Rating)*(113/Slope) • Return the lowest ScoreDate and the average of the handicap-indexes multiplied by .96 The first SELECT is there because I am going to execute this query iteratively, substituting for the “3”,from 1 to the count of total records (so I can create a chart of the change in handicap over time) The flaw is that the ScoreDate that is returned is the oldest date in the lowest 10 records, and what I need is the oldest date in the most recent 20 records (from the sub-query). I cannot figure out how to do this without breaking up the query using temp tables (which I can do, but I am interested in learning more about SQL and I’m sure there must be a way to do this (if you can solve Soduko puzzles!) Thanks for any help, Ed t. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Significant Performance Problem with System.Data.SQLite.
I have never used a profiling tool. Have VS 2010 installed. A quick web search shows several available free tools. What do you recommend for VS 2010 and SQLite? Are you able to run the application with profiling enabled to determine which SQL queries and/or System.Data.SQLite methods are running slowly? This information, if available, would be extremely helpful in tracking down the root cause of the slowdown you are observing. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Significant Performance Problem with System.Data.SQLite.
Thanks for the quick reply. (1) The queries are specified in the SqlDataSource. Examples: "SELECT * FROM [Scores] WHERE ([Player] = @Player) ORDER BY [ScoreDate] DESC" No more than a few hundred rows in the table. (2) I will look into the LogPrep and TraceListener and see what I can do (3) The DB file is located natively to the app on each machine I've tried, in the /App_Data folder of the website. (4) The system event log contains no errors. I am experiencing no other problems on the server nor with any other asp.net apps running on IIS. The only change to make it work is to use an older version of System.Data.SQLite.dll (5) There are at most three SqlDataSource controls on any page, and it is possible that each would have the same data connection open simultaneously, but all for read access, except when inserting or deleting a record. The performance problem occurs when only populating the grids with data. Why work on one system, but not the other? Why work on both systems when using earlier version ? (6) I have tried this app with the x64 assemblies, and have no luck whatsoever. The download page indicates that only the x32 assemblies will work with VS. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Monday, January 20, 2014 8:33 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Significant Performance Problem with System.Data.SQLite. Ed Tenholder wrote: > > The asp.net app is several pages, each with from one to three datagrids that > are filled using ADO.NET. Very little actual code. Using GridView, > DetailsView, DropDownList, and SqlDataSource > (ProviderName="System.Data.SQLite") controls. > Do you know what kind of queries these controls are using? Using the LogPrepare connection flag can help reveal the queries. In that case, the connection string would look something like: SQLiteConnection connection = new SQLiteConnection( "Data Source=test.db;Flags=LogPrepare;"); Please note that you will need a TraceListener setup in order to be able to see the output generated by this connection flag. > > Anyway, the page loads and the grids populate nearly instantaneously on the > development machine under VS2012, but when deployed to WinServer 2012, it > can take more than 20 seconds to populate the grids. > Is the database file on a network share? How many connections are trying to access the database concurrently? Perhaps the server is missing some patches to the .NET Framework? Maybe the server has other issues (e.g. file-system corruption, hardware issues, etc) that cause problems running System.Data.SQLite? Is there any trouble seen in the event logs? > > Note: Both machines are x64 and using NF4.0.Also, could only get ANY > of many versions of System.Data.SQLite.dll that I tried on WINServer 2012 by > configuring the AppPool to allow 32 bit execution. I don't understand that > either. .dll is located in /Bin folder. > There are x64 builds of System.Data.SQLite that should work in a native 64-bit process on Windows Server. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Significant Performance Problem with System.Data.SQLite.
I've finally finished converting from SQLserver Express to SQLite for an ASP.NET app on my development machine. VS 2010 Professional. System.Data.SQLite version 1.0.90.0, and 1.0.89.0 Got everything working great in development, but did I ever have problems deploying to Win Server 2012. Finally figured out (I think) the MANY downloads of System.Data.SQLite and was able to get everything properly loading and executing, except on Win Server 2012, the application ran VERY slowly, but did work properly. The asp.net app is several pages, each with from one to three datagrids that are filled using ADO.NET. Very little actual code. Using GridView, DetailsView, DropDownList, and SqlDataSource (ProviderName="System.Data.SQLite") controls. It's really not an ASP.NET app, but an ASP.NET Website. Anyway, the page loads and the grids populate nearly instantaneously on the development machine under VS2012, but when deployed to WinServer 2012, it can take more than 20 seconds to populate the grids. After days of trying every configuration of DLLs that I could think of, I finally achieved success by merely replacing the System.Data.SQLite.dll file that is version 1.0.90.0 (bundled) with a .dll file from an app I wrote a few years ago:System.Data.SQLite.dll version 1.0.60.0.Now, the grids load instantaneously on both systems! I would like to find out what is wrong, as I would prefer to keep current with SQLite releases. Any suggestions on next steps? Note: Both machines are x64 and using NF4.0.Also, could only get ANY of many versions of System.Data.SQLite.dll that I tried on WINServer 2012 by configuring the AppPool to allow 32 bit execution. I don't understand that either. .dll is located in /Bin folder. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Inconsistent SELECT results
Hi, I've been using Sqlite on and off for a while now, with great results. Awesome piece of software :) Yesterday I ran into an odd problem which has me stumped. Perhaps I'm overlooking something ridiculously simple? My original implementation is quite a bit more complicated, so I've simplified it just enough to be reproducable (I hope ...) When I run: rm ./testme.db ; sqlite3 ./testme.db < input.sql The first sql batch sequence below returns nothing (unexpected). The second sql sequence returns the value that I expected. The only difference between the two sequences is one uses a TEXT value of "md5" and the other uses a TEXT value of "hu5" --- this fails (no result) CREATE TABLE f_main (id INTEGER PRIMARY KEY,pathid INTEGER,name TEXT); INSERT INTO f_main VALUES(4,5,"my_filename"); CREATE TABLE f_path (pathid INTEGER PRIMARY KEY,name TEXT); INSERT INTO f_path VALUES(5,"md5"); CREATE TABLE f_stats (id INTEGER PRIMARY KEY,size INTEGER,md5 TEXT); INSERT INTO f_stats VALUES(4,973,"16f5a3c8edc1668d7318f6113b810009"); SELECT s.id,s.size,s.md5 FROM f_main m,f_stats s,f_path p WHERE m.name="my_filename" AND p.name="md5" AND m.pathid=p.pathid AND m.id=s.id; -- but this succeeds: 4|973|16f5a3c8edc1668d7318f6113b810009 --- CREATE TABLE f_main (id INTEGER PRIMARY KEY,pathid INTEGER,name TEXT); INSERT INTO f_main VALUES(4,5,"my_filename"); CREATE TABLE f_path (pathid INTEGER PRIMARY KEY,name TEXT); INSERT INTO f_path VALUES(5,"hu5"); CREATE TABLE f_stats (id INTEGER PRIMARY KEY,size INTEGER,md5 TEXT); INSERT INTO f_stats VALUES(4,973,"16f5a3c8edc1668d7318f6113b810009"); SELECT s.id,s.size,s.md5 FROM f_main m,f_stats s,f_path p WHERE m.name="my_filename" AND p.name="hu5" AND m.pathid=p.pathid AND m.id=s.id; I get the same results on 3 different Linux machines, across 3 different Sqlite versions. 2 versions were Ubuntu-based and the third (3.7.4) I compiled from source obtained from the sqlite.org website. Linux outpost3 2.6.24-23-xen #1 SMP Mon Jan 26 03:09:12 UTC 2009 x86_64 GNU/Linux a virtual server (xen based) running Ubuntu 8 /dev/sda1 on / type ext3 (rw,relatime,errors=remount-ro) sqlite3 3.4.2 sqlite3 3.7.4 Linux castle 2.6.32-23-generic #37-Ubuntu SMP Fri Jun 11 07:54:58 UTC 2010 i686 GNU/Linux a desktop machine running Ubuntu 10 /dev/sda1 on / type reiserfs (rw,relatime,notail) sqlite3 3.6.22 sqlite3 3.7.4 Linux guardian 2.4.28 #1 Fri Dec 10 18:55:16 CST 2004 i686 unknown unknown GNU/Linux an older server running slackware 10 /dev/hda2 on / type reiserfs (rw) sqlite3 3.7.4 So, am I missing something? Or is this a bug of some sort? Thanks for any help, Ed Nolan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Horrendous slowdown when updating versions
Hi all, I was running a system using an old sqlite3.dll (version 3.0.8, I believe). Updating to the latest version (3.6.23) causes my program to run incredibly slowly. As an example, selecting approximately 30,000 records was taking 10 seconds with the old dll, and with the updated version takes 70 seconds. Whereas 10 was just about acceptable, 70 is definitely not; can anyone think of a reason why this might be, and what steps I could take to rectify it? Regards, Ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] notify all processes of database modification
ve at a mutually beneficial solution. 2. Any solution involving the database writer/changer to take any explicit action (e.g. signaling, etc.) to notify other processes possibly using that database file that it has changed is likely to have undesirable vulnerabilities (change can occur that will go undetected, etc.) Consider: (a) file-level copy operations, (b) changes by other tools that don't "follow the rules," etc. 3. As I recall, there were a number of advantages to going with a more "built into SQLite" approach. Among them (a few that I remember right now): - By building it into SQLite, it is more likely to work consistently and robustly across all SQLite-based apps and tools and on all platforms, even if the apps and tools were developed by different people/organizations. - There are certain control points in the lower levels of SQLite (e.g., the b-tree and pager layers) that make excellent places to wedge in change notification mechanisms. In the absence of using such control points, notification mechanisms are harder to implement and tend to suffer from inefficiencies (e.g., doing certain notification-related things when a particular database file is known to be locked and just changed can be much more optimal than alternatives). - Simplicity from the perspective of the SQLite library user. - Who wants to keep re-inventing this wheel? 4. An interesting point to ponder as you're evaluating alternative approaches to this problem: What layer of software really knows what files are involved in a database connection over time? I'd claim that it's SQLite (not the app layer above it). Take, for example, an application that opens an SQLite database connection and then executes arbitrary (e.g., from a console or an input file) SQL commands against it. Some of those commands might be ATTACH DATABASE or DETACH DATABASE commands. So... the set of files that need to be monitored for change is itself changing. Where is that information most readily available? Yup... at the SQLite level. 5. It would be possible to implement a good, high quality cross-process notification mechanism without building it directly into SQLite if SQLite were first enhanced with some new callback hooks at just the right places (I won't elaborate on these further without first reviewing our code to see where those hooks would need to be). Some SQLite VFS extensions could also help put platform-dependent code where it belongs and minimize the surface area of code for anyone wanting to port SQLite (along with the cross-process notification mechanism) to a new platform. 6. The change granularity is a significant issue. It's not too hard or expensive to provide change notification on a database file level. Getting more fine grained, though, becomes much more difficult. While it would be great to get table (or even row-level) cross-process change notification, it is difficult to see how this could be done efficiently without significant implementation done inside the bowels of SQLite. Database-level granularity is enough to at least get the ball rolling; additional mechanisms (e.g., trigger-based change logging to a journal table or some such) can always be layered on top of this if finer grain is needed. Of course, such finer-grained features *could* be built into SQLite at a later date. 7. From the perspective of a user of the SQLite library, it's hard to beat the simplicity of a function like what we did: void * sqlite3_async_update_hook(sqlite3 *db, void(*)(void *context, char const *dbname), void *context); 8. I'd really like to either get this functionality built into SQLite *or* go the opposite direction and get more-or-less the same API-level functionality, but with the notification mechanism source code as decoupled as much as possible from the SQLite source code. It's just not desirable for us to maintain the customized-SQLite-source-code solution we're using right now. -- Ed Scherer Innovision Corporation ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] notify all processes of database modification
possibly using that database file that it has changed is likely to have undesirable vulnerabilities (change can occur that will go undetected, etc.) Consider: (a) file-level copy operations, (b) changes by other tools that don't "follow the rules," etc. 3. As I recall, there were a number of advantages to going with a more "built into SQLite" approach. Among them (a few that I remember right now): - By building it into SQLite, it is more likely to work consistently and robustly across all SQLite-based apps and tools and on all platforms, even if the apps and tools were developed by different people/organizations. - There are certain control points in the lower levels of SQLite (e.g., the b-tree and pager layers) that make excellent places to wedge in change notification mechanisms. In the absence of using such control points, notification mechanisms are harder to implement and tend to suffer from inefficiencies (e.g., doing certain notification-related things when a particular database file is known to be locked and just changed can be much more optimal than alternatives). - Simplicity from the perspective of the SQLite library user. - Who wants to keep re-inventing this wheel? 4. An interesting point to ponder as you're evaluating alternative approaches to this problem: What layer of software really knows what files are involved in a database connection over time? I'd claim that it's SQLite (not the app layer above it). Take, for example, an application that opens an SQLite database connection and then executes arbitrary (e.g., from a console or an input file) SQL commands against it. Some of those commands might be ATTACH DATABASE or DETACH DATABASE commands. So... the set of files that need to be monitored for change is itself changing. Where is that information most readily available? Yup... at the SQLite level. 5. It would be possible to implement a good, high quality cross-process notification mechanism without building it directly into SQLite if SQLite were first enhanced with some new callback hooks at just the right places (I won't elaborate on these further without first reviewing our code to see where those hooks would need to be). Some SQLite VFS extensions could also help put platform-dependent code where it belongs and minimize the surface area of code for anyone wanting to port SQLite (along with the cross-process notification mechanism) to a new platform. 6. The change granularity is a significant issue. It's not too hard or expensive to provide change notification on a database file level. Getting more fine grained, though, becomes much more difficult. While it would be great to get table (or even row-level) cross-process change notification, it is difficult to see how this could be done efficiently without significant implementation done inside the bowels of SQLite. Database-level granularity is enough to at least get the ball rolling; additional mechanisms (e.g., trigger-based change logging to a journal table or some such) can always be layered on top of this if finer grain is needed. Of course, such finer-grained features *could* be built into SQLite at a later date. 7. From the perspective of a user of the SQLite library, it's hard to beat the simplicity of a function like what we did: void * sqlite3_async_update_hook(sqlite3 *db, void(*)(void *context, char const *dbname), void *context); 8. I'd really like to either get this functionality built into SQLite *or* go the opposite direction and get more-or-less the same API-level functionality, but with the notification mechanism source code as decoupled as much as possible from the SQLite source code. It's just not desirable for us to maintain the customized-SQLite-source-code solution we're using right now. -- Ed Scherer Innovision Corporation ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT question (computing day of week the usingstrftime() function)
Please cancel my subscription to this list!! My Mail client is overwhelmed.
Roger Andersson wrote:
>> I need to find out how many specific weekdays (e.g., how many
>> Sundays) I have in any given range of dates.
>> My problem: How to use the COUNT function in combination with
>> the strftime() function.
>>
>>
> Maybe something like
>
> sqlite3 test.db
> SQLite version 3.6.23
> sqlite> CREATE TABLE test (date TEXT, money INTEGER);
> sqlite> INSERT INTO test VALUES('2007-07-20', 1000);
> sqlite> INSERT INTO test VALUES('2007-07-21', 2100);
> sqlite> INSERT INTO test VALUES('2007-07-22', 2200);
> sqlite> INSERT INTO test VALUES('2007-07-27', 7000);
> sqlite> INSERT INTO test VALUES('2007-07-28', 2800);
> sqlite> INSERT INTO test VALUES('2007-07-29', 2900);
> sqlite> INSERT INTO test VALUES('2007-07-22', 9200);
> sqlite> SELECT strftime('%w', date) weekday, count(*) cnt FROM test GROUP BY
> weekday;
> weekday|cnt
> 0|3
> 5|2
> 6|2
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unsubsribe plz
Bennie wrote: > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > Me too please! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas been run through the sqlite3_prepare_v2 function?
Yes. At times I get so wound up in the details of various parts of the code that I miss the obvious of setting the stmt pointer to null before using it. Thank you to Jay for reminding me of this. Your collective replies, answers and advice are very much appreciated, however the "how stupid are you" attitude of some of the posts are really quite ridiculous. Splitting hairs about my phrasing lacking the specifics of the levels of indirection to the statement doesn't change the fundamentals of the question, which was basically "how do I know if the stmt is initalized". Again thank you Jay for getting me out of the details so that the obvious became clear again. I'll try to be more clear in future questions. > From: paiva...@gmail.com > Date: Tue, 9 Mar 2010 13:21:18 -0500 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas been > run through the sqlite3_prepare_v2 function? > > > int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt > > **ppStmt, const char **pzTail); > > > > Please tell me what the 4th parameter is then if it not a statement so that > > I may ask you in the words you are looking for. > > It's not a statement. Speaking in Igor's words it's a _pointer_ to > statement. Speaking in Jay's words it's a _pointer_ to _pointer_ to > statement structure. > Usually when people say they have "function that is being passed an > sqlite3_stmt object" they mean this prototype: > > void f(sqlite3_stmt* stmt); > > If you will check for NULL inside this function (as Jay suggested) and > prepare statement if it's NULL then you will never have a non-NULL > value there and memory will leak from you significantly. > > > Pavel > > On Tue, Mar 9, 2010 at 1:07 PM, Ed Curren <ecur...@hotmail.com> wrote: > > > > According to the documentation the function prototype for > > sqlite3_prepare_v2 is the following: > > > > > > > > int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt > > **ppStmt, const char **pzTail); > > > > > > > > Please tell me what the 4th parameter is then if it not a statement so that > > I may ask you in the words you are looking for. > > > >> To: sqlite-users@sqlite.org > >> From: itandet...@mvps.org > >> Date: Tue, 9 Mar 2010 13:03:00 -0500 > >> Subject: Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas > >> been run through the sqlite3_prepare_v2 function? > >> > >> Ed Curren <ecur...@hotmail.com> wrote: > >> > Okay, let's try asking the question this way then. How do I know if > >> > I need to call prepare based on the condition or value or whatever of > >> > a statement that may or may not have already been passed to perpare > >> > as a parameter? > >> > >> You _cannot_ pass a statement to prepare as a parameter. > >> > >> Igor Tandetnik > >> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I query a sqlite3_stmt to find out if it has been run through the sqlite3_prepare_v2 function?
Thank you for a straight answer Jay. > Date: Tue, 9 Mar 2010 12:10:17 -0600 > From: j...@kreibi.ch > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] How can I query a sqlite3_stmt to find out if it has > been run through the sqlite3_prepare_v2 function? > > On Tue, Mar 09, 2010 at 11:47:39AM -0500, Ed Curren scratched on the wall: > > > > Hello all, > > > > I have a function that is being passed an sqlite3_stmt object. > > I assume you mean a sqlite3_stmt pointer. You should never > instance an actual sqlite3_stmt structure yourself. > > > Within this function I need to determine if the statement has been > > prepared. > > How can I accomplish this? > > Ummm... it's a pointer. Set it to NULL when you initialize whatever > data-structure it is in. When you prepare the statement, it will be > non-NULL. If you have reason to call sqlite3_finalize(), set the > pointer back to NULL. > > Test for NULL. > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic bombs. We have > a protractor." "I'll go home and see if I can scrounge up a ruler > and a piece of string." --from Anathem by Neal Stephenson > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas been run through the sqlite3_prepare_v2 function?
According to the documentation the function prototype for sqlite3_prepare_v2 is the following: int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail); Please tell me what the 4th parameter is then if it not a statement so that I may ask you in the words you are looking for. > To: sqlite-users@sqlite.org > From: itandet...@mvps.org > Date: Tue, 9 Mar 2010 13:03:00 -0500 > Subject: Re: [sqlite] How can I query a sqlite3_stmt to find outif ithas been > run through the sqlite3_prepare_v2 function? > > Ed Curren <ecur...@hotmail.com> wrote: > > Okay, let's try asking the question this way then. How do I know if > > I need to call prepare based on the condition or value or whatever of > > a statement that may or may not have already been passed to perpare > > as a parameter? > > You _cannot_ pass a statement to prepare as a parameter. > > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I query a sqlite3_stmt to find out if ithas been run through the sqlite3_prepare_v2 function?
Okay, let's try asking the question this way then. How do I know if I need to call prepare based on the condition or value or whatever of a statement that may or may not have already been passed to perpare as a parameter? > To: sqlite-users@sqlite.org > From: itandet...@mvps.org > Date: Tue, 9 Mar 2010 12:47:42 -0500 > Subject: Re: [sqlite] How can I query a sqlite3_stmt to find out if ithas > been run through the sqlite3_prepare_v2 function? > > Ed Curren <ecur...@hotmail.com> wrote: > > This function will be called several times. The first time through > > the statment won't be prepared > > What do you mean, won't be prepared? How can one obtain a statement handle > without calling prepare? > > > so in that case the function will > > call sqlite3_preapre_v2 to prepare the statement. > > Note that sqlite3_preapre_v2 doesn't take a statement as a parameter. It > takes a database connection and a SQL string, and _produces_ the statement > from them. > > Imagine one saying "I have a FILE* handle and I need to call fopen on it", or > "I have a void* pointer and I need to call malloc on it". Well, your question > makes about as much sense. > > Igor Tandetnik > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I query a sqlite3_stmt to find out if it has been run through the sqlite3_prepare_v2 function?
Hi Igor, This function will be called several times. The first time through the statment won't be prepared, so in that case the function will call sqlite3_preapre_v2 to prepare the statement. Subsequent calls to the function will have the statment that is already prepared, so I do not want to attempt to call sqlite3_prepare_v2 again on a statment that is already prepared. Thanks very much for your help. > To: sqlite-users@sqlite.org > From: itandet...@mvps.org > Date: Tue, 9 Mar 2010 12:04:15 -0500 > Subject: Re: [sqlite] How can I query a sqlite3_stmt to find out if it has > been run through the sqlite3_prepare_v2 function? > > Ed Curren <ecur...@hotmail.com> wrote: > > I have a function that is being passed an sqlite3_stmt object. > > Within this function I need to determine if the statement has been > > prepared. How can I accomplish this? > > Where else would a statement handle come from, if not from > sqlite3_prepare[_v2] ? What precisely is the other possibility you are trying > to defend against? > > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How can I query a sqlite3_stmt to find out if it has been run through the sqlite3_prepare_v2 function?
Hello all, I have a function that is being passed an sqlite3_stmt object. Within this function I need to determine if the statement has been prepared. How can I accomplish this? Thanks very much. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested Inner Join Help
Fine. Do the "a2-style" (for want of a better way of defining them) names exist outside the SQL statement which defines them? i.e. if I execute the example statement that you gave me, then later execute a statement which references c2 will that work? Will other statements in the same transaction be able to reference c2, or does the reference only persist in the statement in which c2 was defined? Ed Igor Tandetnik wrote: > Ed Hawke wrote: > >> All I meant was that in a database you have defined tables with >> defined column names. These are defined before any SQL statements are >> executed and therefore I would classify them as pre-defined. >> >> Contrast this with the "labels" applied to tables to create a separate >> reference to them within an SQL statement (e.g. a2 in your example). >> Before the SQL statement creating these references is executed then >> they will not be "recognised" by anything (i.e. any references to >> them in other statements will throw up an error). Therefore these are >> only defined when the SQL Statement that defines them is run, and >> therefore I would classify them as run-time defined. >> > > I don't see how this fine distinction is interesting, nor why it's worth > making. While "predefined" column names may exist, in some philosophical > sense, outside the context of a SQL statement, the only way for you to > use them or refer to them is by mentioning them in a SQL statement, at > which point all distinction between "predefined" and "dynamically > generated" names evaporates. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested Inner Join Help
All I meant was that in a database you have defined tables with defined column names. These are defined before any SQL statements are executed and therefore I would classify them as pre-defined. Contrast this with the "labels" applied to tables to create a separate reference to them within an SQL statement (e.g. a2 in your example). Before the SQL statement creating these references is executed then they will not be "recognised" by anything (i.e. any references to them in other statements will throw up an error). Therefore these are only defined when the SQL Statement that defines them is run, and therefore I would classify them as run-time defined. I am aware as I said that these are probably not the correct SQL definitions, they were self-applied definitions to help make clear what I was referring to. I can only apologise that it made it more complicated. Ed Igor Tandetnik wrote: > Ed Hawke wrote: > >> By run-time defined fields I meant column names that SQL would not >> recognise until the query was executed >> > > I don't get the distinction. Could you give an example of column names > that SQL would somehow "recognize" before a query is executed? What do > you mean by "recognize" here, anyway? > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested Inner Join Help
Thank you again Igor. By run-time defined fields I meant column names that SQL would not recognise until the query was executed, and therefore are only defined when the statement is "run". I am aware that this is probably not the correct terminology. Ed Igor Tandetnik wrote: > Ed Hawke > <edward.ha...@hawkeyeinnovations.co.uk> wrote: > >> Out of interest, would I be able to use binding on the run-time >> defined fields? >> > > What's "run-time defined fields"? I'm not familiar with the term. > > >> If I wanted to use: >> >> select * from A >>join B b1 on (A.Column3 = b1.ID) >>join C c1 on (b1.Column1 = c1.ID) >>join D d1 on (b1.Column2 = d1.ID) >> >>join B b2 on (A.Column4 = b2.ID) >>join C c2 on (b2.Column1 = c2.ID) >>join D d2 on (b2.Column2 = d2.ID); >> where d2.ID = ? >> > > Remove the semicolon before WHERE. Otherwise, I don't see anything wrong > with this. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested Inner Join Help
Thank you very much for this Igor. Out of interest, would I be able to use binding on the run-time defined fields? If I wanted to use: select * from A join B b1 on (A.Column3 = b1.ID) join C c1 on (b1.Column1 = c1.ID) join D d1 on (b1.Column2 = d1.ID) join B b2 on (A.Column4 = b2.ID) join C c2 on (b2.Column1 = c2.ID) join D d2 on (b2.Column2 = d2.ID); where d2.ID = ? Would that work? Regards, Ed Igor Tandetnik wrote: > Ed Hawke > <edward.ha...@hawkeyeinnovations.co.uk> wrote: > >> To clarify this (I hope) if my table set-up is: >> >> Table A: >> IDColumn1Column2Column3Column4 >> 112345678921 23 >> 216321587622 21 >> 331456910822 24 >> >> Table B: >> IDColumn1Column2 >> 2131 42 >> 2231 41 >> 2333 43 >> 2432 41 >> >> Table C: >> IDColumn1 >> 31 Name1 >> 32 Name2 >> 33 Name3 >> >> Table D: >> IDColumn1 >> 41 Info1 >> 42 Info2 >> 43 Info3 >> >> How would I select A.ID, A.Column1, A.Column2, C.Column1, D.Column1, >> C.Column1, D.Column1 where the first set of info from C and D is based >> on the ID contained in A.Column3, and the second set on the ID in >> A.Column4? >> > > select * from A > join B b1 on (A.Column3 = b1.ID) > join C c1 on (b1.Column1 = c1.ID) > join D d1 on (b1.Column2 = d1.ID) > > join B b2 on (A.Column4 = b2.ID) > join C c2 on (b2.Column1 = c2.ID) > join D d2 on (b2.Column2 = d2.ID); > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested Inner Join Help
Thank you both for your help. That works, but what happens if I want to do a more complex query. If Table B contains references to tables C and D then I can just extend your example to select information from D as well as C. However if I have multiple Columns in A that are occurrences of B.ID how would I create a statement to select the information from C and D for each of these? To clarify this (I hope) if my table set-up is: Table A: IDColumn1Column2Column3Column4 112345678921 23 216321587622 21 331456910822 24 Table B: IDColumn1Column2 2131 42 2231 41 2333 43 2432 41 Table C: IDColumn1 31 Name1 32 Name2 33 Name3 Table D: IDColumn1 41 Info1 42 Info2 43 Info3 How would I select A.ID, A.Column1, A.Column2, C.Column1, D.Column1, C.Column1, D.Column1 where the first set of info from C and D is based on the ID contained in A.Column3, and the second set on the ID in A.Column4? Sorry, I know this is massively more complicated, but that was why I was attempting to use nested Inner Joins, although I'm not necessarily sure they would have helped in this situation anyway. Regards, Ed Adam DeVita wrote: > why not use: > > SELECT A.ID <http://a.id/>, A.Column1, A.Column2, B.Column1, C.Column1 > FROM A > INNER JOIN B ON A.Column3 = B.ID <http://b.id/> > INNER JOIN C ON B.Column2 = C.ID <http://c.id/> > > ? > > On Thu, Jul 2, 2009 at 2:53 PM, Ed Hawke > <edward.ha...@hawkeyeinnovations.co.uk > <mailto:edward.ha...@hawkeyeinnovations.co.uk>> wrote: > > Hi all, > > I'm having problems getting nested inner joins to work with SQLite. As > far as I can tell from various resources the correct way of joining > multiple tables is this: > > SELECT A.ID <http://A.ID>, A.Column1, A.Column2, B.Column1, > C.Column1 FROM A INNER > JOIN B (INNER JOIN C ON B.Column2 = C.ID <http://C.ID>) ON > A.Column3 = B.ID <http://B.ID> > > However depending upon where I put the parentheses I get various > different errors from the viewer I use (SQLite Manager for Firefox). A > normal Inner Join without the nesting works fine. > > Can anyone tell me what I'm doing wrong? > > Regards, > > Ed > ___ > sqlite-users mailing list > sqlite-users@sqlite.org <mailto:sqlite-users@sqlite.org> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > VerifEye Technologies Inc. > 905-948-0015x245 > 7100 Warden Ave, Unit 3 > Markham ON, L3R 8B5 > Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Nested Inner Join Help
Hi all, I'm having problems getting nested inner joins to work with SQLite. As far as I can tell from various resources the correct way of joining multiple tables is this: SELECT A.ID, A.Column1, A.Column2, B.Column1, C.Column1 FROM A INNER JOIN B (INNER JOIN C ON B.Column2 = C.ID) ON A.Column3 = B.ID However depending upon where I put the parentheses I get various different errors from the viewer I use (SQLite Manager for Firefox). A normal Inner Join without the nesting works fine. Can anyone tell me what I'm doing wrong? Regards, Ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] preserve column constraints
Does 'INSERT INTO mem_db SELECT * FROM file_db' work the same as
'CREATE TABLE mem_db AS SELECT * FROM file_db' with regard to
transactions? Are the inserts going to be handled as one large
transaction in either scenario? Do I need to explicitly use the BEGIN
and COMMIT commands?
thanks,
ed
On Fri, May 22, 2009 at 10:50 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
> "ed" <epdm...@gmail.com> wrote in message
> news:d92e64410905221021p39a4f833y960370145e187...@mail.gmail.com
>> I have an application that copies my disk based db into an in memory
>> table (":memory:") using the command
>> "create table mem_db as select * from file_db;"
>>
>> The problem is the disk db has a column set to INTEGER PRIMARY KEY,
>> but this is not preserved when the new in memory db is created. When i
>> do a "select * from sqlite_master;" on the in-memory db, there is no
>> IPK. How can I accomplish creating the in-memory db with the file db
>> data and preserve the column configuration from the original table?
>
> You create the table the way you want it, then run INSERT ... SELECT
> statement to populate it.
>
> You may also find this interesting:
> http://sqlite.org/c3ref/backup_finish.html
>
> Igor Tandetnik
>
>
>
> _______
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
thanks,
ed
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] preserve column constraints
ok, thanks
I guess i was assuming the command "create table x as select * from y"
should preserve column constraints and maybe i was going about it
wrong.
thanks for the link to the backup api, i will see if this makes sense
for my application.
thanks,
ed
On Fri, May 22, 2009 at 10:50 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
> "ed" <epdm...@gmail.com> wrote in message
> news:d92e64410905221021p39a4f833y960370145e187...@mail.gmail.com
>> I have an application that copies my disk based db into an in memory
>> table (":memory:") using the command
>> "create table mem_db as select * from file_db;"
>>
>> The problem is the disk db has a column set to INTEGER PRIMARY KEY,
>> but this is not preserved when the new in memory db is created. When i
>> do a "select * from sqlite_master;" on the in-memory db, there is no
>> IPK. How can I accomplish creating the in-memory db with the file db
>> data and preserve the column configuration from the original table?
>
> You create the table the way you want it, then run INSERT ... SELECT
> statement to populate it.
>
> You may also find this interesting:
> http://sqlite.org/c3ref/backup_finish.html
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
thanks,
ed
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] preserve column constraints
Hello,
I have an application that copies my disk based db into an in memory
table (":memory:") using the command
"create table mem_db as select * from file_db;"
The problem is the disk db has a column set to INTEGER PRIMARY KEY,
but this is not preserved when the new in memory db is created. When i
do a "select * from sqlite_master;" on the in-memory db, there is no
IPK. How can I accomplish creating the in-memory db with the file db
data and preserve the column configuration from the original table?
I am using SQLite 3.3.4.
--
thanks,
ed
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA page_cache=x doesnt seem to have an effect
Does anyone have any experience or examples of reducing sqlite memory usage through the pragma cache_size, page_size satements? I am misunderstanding the intended use of the pragmas? thanks, ed On Fri, Feb 20, 2009 at 5:58 PM, ed <epdm...@gmail.com> wrote: > Hello, > I am using sqlite in an embedded environment with very limited memory. > In an attempt to reduce memory consumed by sqlite, I am executing > PRAGMA cache_size=new_size and PRAGMA default_cache_size=new_size upon > opening all of my db's, which are both file based and in-memory. I > have varied the value of new_size from 2000 (default), to 1000 to 500 > to 0 and none of these values seems to lower the amount of memory > sqlite consumes. > > I have verified the new cache size settings were registered in the > db's using PRAGMA cache_size. > I then verify sqlite memory usage with the sqlite3_status() functions, > which do not show a change in return value for > SQLITE_STATUS_PAGECACHE_OVERFLOW or SQLITE_STATUS_MEMORY_USED (around > 7.9MB and 8.2MB respectively) > > I have not changed the default memory allocation (ie no scratch, cache > or heap memory allocation at startup). > I have configured sqlite3_soft_heap_limit(8192000). > > Any help in figuring out how to limit the cache usage of memory would > be appreciated. > > -- > thanks, > ed > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA page_cache=x doesnt seem to have an effect
Hello, I am using sqlite in an embedded environment with very limited memory. In an attempt to reduce memory consumed by sqlite, I am executing PRAGMA cache_size=new_size and PRAGMA default_cache_size=new_size upon opening all of my db's, which are both file based and in-memory. I have varied the value of new_size from 2000 (default), to 1000 to 500 to 0 and none of these values seems to lower the amount of memory sqlite consumes. I have verified the new cache size settings were registered in the db's using PRAGMA cache_size. I then verify sqlite memory usage with the sqlite3_status() functions, which do not show a change in return value for SQLITE_STATUS_PAGECACHE_OVERFLOW or SQLITE_STATUS_MEMORY_USED (around 7.9MB and 8.2MB respectively) I have not changed the default memory allocation (ie no scratch, cache or heap memory allocation at startup). I have configured sqlite3_soft_heap_limit(8192000). Any help in figuring out how to limit the cache usage of memory would be appreciated. -- thanks, ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] open/close db's across threads
any help on this would be appreciated. thanks, ed -- Forwarded message -- Hello,I have an application that is calling sqlite3_open() in one thread and sqlite3_close() in a different thread. The db's can potentially be opened and closed many times during execution of my app. Could this potentially introduce any problems? In particular, I am trying to determine why sqlite3 is consuming increasingly more memory as the db's are opened and closed (as seen with valgrind's massif). -- thanks, ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] open/close db's across threads
Hello,I have an application that is calling sqlite3_open() in one thread and sqlite3_close() in a different thread. The db's can potentially be opened and closed many times during execution of my app. Could this potentially introduce any problems? In particular, I am trying to determine why sqlite3 is consuming increasingly more memory as the db's are opened and closed (as seen with valgrind's massif). -- thanks, ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Binding with Views
Hi all, I have an SQL application that relies on using views to allow a user to make an array of choices to filter down the information returned to them from multiple tables, without having to code complex statements to take into account the order in which they need to be applied. To speed up this process a little I would like to use binding on select statements, but I am selecting from a view and creating a new view from the information returned. Without knowing what view I will be querying beforehand, there is no way that I can use binding is there? Is there a way around this problem? Regards, Ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] KEY keyword
Igor,thank you for the explanation however, I am still not clear if the conflict resolution clause will be used on a table with no UNIQUE or PRIMARY KEY columns? I would assume not but i couldn't find the answer in the documentation. thanks, ed On Fri, Jan 9, 2009 at 11:17 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > ed <epdm...@gmail.com> wrote: > > I have a sqlite 3.3.4 app using a db with the following schema: > > > > CREATE TABLE my_data(n INTEGER KEY, s INTEGER, p INTEGER, od VARCHAR); > > > > Is the KEY keyword utilized? > > The way this statement is parsed, column 'n' has the type of 'INTEGER > KEY'. Remember, SQLite allows almost any odd sequence of identifiers as > a type name (except that it can't contain certain keywords that > introduce column-constraint clause, e.g. PRIMARY). You could just as > well write > > CREATE TABLE my_data(n I LOVE SQLITE, ...); > > (try it - it does work). > > > Will it act the same as a primary key ? > > No. > > > Ultimately, i'm trying to determine if the KEY will enforce a unique > > constraint > > No. But PRIMARY KEY or UNIQUE will. > > > such that the following insert's conflict clause would > > even be necessary. > > Conflict resolution clause is never necessary. If none is specified, the > default of ABORT is used. > -- > With best wishes, >Igor Tandetnik > > With sufficient thrust, pigs fly just fine. However, this is not > necessarily a good idea. It is hard to be sure where they are going to > land, and it could be dangerous sitting under them as they fly > overhead. -- RFC 1925 > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] KEY keyword
Hello, I have a sqlite 3.3.4 app using a db with the following schema: CREATE TABLE my_data(n INTEGER KEY, s INTEGER, p INTEGER, od VARCHAR); Is the KEY keyword utilized? Will it act the same as a primary key ? Ultimately, i'm trying to determine if the KEY will enforce a unique constraint, such that the following insert's conflict clause would even be necessary. INSERT OR REPLACE INTO elevator_data (id, schedule, panel, output_data) VALUES thanks, ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: memory usage
I haven't looked much at the source but will give it a shot.Thanks for the reply. On Wed, Jan 7, 2009 at 12:31 PM, Kees Nuyt <k.n...@zonnet.nl> wrote: > On Wed, 7 Jan 2009 10:25:12 -0800, ed <epdm...@gmail.com> > wrote in General Discussion of SQLite Database > <sqlite-users@sqlite.org>: > > >Hello, I did not receive a reply to my question.Does anyone have any > >information on this? > > Apparently not. > I am not much of a source hacker, but perhaps you are. > You might be able to intercept allocation and free calls and > keep tallies per active database handle. You would have to > add a few entrypoints for this. > > In short: > Setup a hash table with counters for current and maximum > allocation, use db handle as a key in the hashtable. > > Add an entrypoint that registers which db handle will be > used in the next sqlite3_* call. Call that entrypoint before > every sqlite3_* call. > > Add code to the "allocate" entrypoint: >increment the current and maximum memory >counter for the currently active db handle. > > Add code to the "free" entrypoint: >decrement the current memory counter >for the currently active db handle > > Add an entrypoint to report the contents of the hashtable. > > >thanks, > >ed > > > >-- Forwarded message -- > >From: ed <epdm...@gmail.com> > >Date: Tue, Dec 30, 2008 at 10:02 AM > >Subject: memory usage > >To: sqlite-users@sqlite.org > > > > > >Hello, > >My multi-threaded application has various sqlite db's open simultaneously, > >in memory using the :memory: keyword, disk based db's and at times, tmpfs > >(ram) db's. Is there a way to view each individual database's memory > usage? > > > >I found the functions sqlite3_memory_used() and > >sqlite3_status(SQLITE_STATUS_MEMORY_USED, ...) but these look like they > >provide memory statistics for all of sqlite, not per database. > > > >thanks, > >ed > > Hope this helps. > -- > ( Kees Nuyt > ) > c[_] > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: memory usage
Hello, I did not receive a reply to my question.Does anyone have any information on this? thanks, ed -- Forwarded message -- From: ed <epdm...@gmail.com> Date: Tue, Dec 30, 2008 at 10:02 AM Subject: memory usage To: sqlite-users@sqlite.org Hello, My multi-threaded application has various sqlite db's open simultaneously, in memory using the :memory: keyword, disk based db's and at times, tmpfs (ram) db's. Is there a way to view each individual database's memory usage? I found the functions sqlite3_memory_used() and sqlite3_status(SQLITE_STATUS_MEMORY_USED, ...) but these look like they provide memory statistics for all of sqlite, not per database. thanks, ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] memory usage
Hello, My multi-threaded application has various sqlite db's open simultaneously, in memory using the :memory: keyword, disk based db's and at times, tmpfs (ram) db's. Is there a way to view each individual database's memory usage? I found the functions sqlite3_memory_used() and sqlite3_status(SQLITE_STATUS_MEMORY_USED, ...) but these look like they provide memory statistics for all of sqlite, not per database. thanks, ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Where to use PRAGMAs
Hi all, I'm using a C++ wrapper to SQLite (CppSQLite3) and want to be able to set the temp_store, page_size, cache_size etc. I am currently doing this directly after opening the database (but before creating any tables) with the sqlite3_exec statement, however when I come to do anything to the tables if I check their statuses they have been reset to the default values. What am I doing wrong? Regards, Ed ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shared Cache for Processes
Hi, Speaking only as a non-professional, I still try to answer. I don't want to comment on the benefits of shared cache mode now, but only on the question if it can be enabled in Apache. And I believe it can. As you say Apache pre-forks different processes, but within each process it pre-establishes different threads and I think the default is in the order of 100. So practically you will only use a single process and hence can easily enable shared-cache mode. This assumes that your application is loaded into Apache, and does not run via cgi. Regards, Edzard Pasma Op 24-jan-2008, om 16:48 heeft Brandon, Nicholas (UK) het volgende geschreven: Hi all, Could the 'Shared Cache' option in SQLite theoretically improve the performance of the db if used by multiple processes? The application in particular is Apache using pre-fork processes accessing the same db. The info at http://www.sqlite.org/sharedcache.html seems to indicate it could benefit threads only. I believe it would not but would like confirmation from someone else. Thanks Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite File Access Restriction
1) Sqlite database file access restriction: Is there a built-in or preferred method to block other processes from writing or even accessing a database file when its opened first by another process. I can get this to work by having the first process to open the file issue a BEGIN Exclusion or BEGIN Restricted but there is always a chance, that right after I commit and go to issue another BEGIN, a 2^nd progress can grab and hold the file. I would like for the 2^nd process to see if another process has control of the file and warn/adapt based on that. Definitely NO multiple writers. That is Absolutely the only way get that kind of 'useful' lock on windows. End of story! There is a pragma locking_mode that may still be considered: http:// sqlite.org/pragma.html#pragma_locking_mode - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] shared cache mode locking
No, you did not confuse me. We are talking about different things it appears. My post refers to the shared-cache locking model (http:// sqlite.org/sharedcache.html). The document is clear by itself. What makes it confusing, is that a shared cache instance exist as a single normal connection which may coincide with other, "normal" database connections. Quite a generous feature. But it means that the two locking models do apply at the same time. The joint connections within a shared cache are subject to the regular locking model in relation to possible other connections to the same database. Confusing or not? Ken wrote: Ed, Sorry if I confused you, a "Write" lock is really an EXCLUSIVE lock per sqlite documentation. I used the two interchangeably, pardon my error. A begin exclusive indicates the beginning of a transaction, It escalates the database lock to an EXCLUSIVE lock. The begin transaction does not immediately do this, rather it waits until the buffer cache spills to disk. At this point it attempts to escalate the Reserved lock to a Pending then an Exclusive lock. There is only 1 type of EXCLUSIVE (write) lock, It is database wide and is all or nothing. Once you have the lock, it prevents other access to the DB. Ken Ed Pasma <[EMAIL PROTECTED]> wrote: The ticket has already been resolved, I see. So it has been considered a bug. In my earlier reply I tried to defend the current behavour to be in line with the document, http://sqlite.org/ sharedcache.html. I'm happy to change my mind now. Only I miss something in the model as described in the document. This may either be: - exclusive transactions as a new kind of transactions, apart form read- and write-transactions or - database-level locking as a new level above transaction-level locking. May be this suggestion is too naive, anyway it helps me explain the wonderful cache sharing. Ken wrote: Ed, Dan opened a ticket. I agree the documentation isn't clear on the Exlusive locking state. Not really sure, if this is by design or a bug at this stage. I do think its a great feature of the Shared cache mode to allow table level locking. But I'm curious with this table level locking what would happen if two threads performed writes to two seperate tables concurrently using only a begin immediate. Thread a writes to tab1, Thread b writes to tab2, (Is this allowed ? or is a sqlite_locked kicked returned?) If it is allowed then would there be two journal files concurrently existing? And What happens during a crash with two journals ? This gets complicated very quickly. Ken Ed Pasma wrote: Hello,` Empirically I found that it is exactly true. Must admit I'm confused but may it is in line with the Shared-Cache locking model. This does not mention the EXCLUSIVE locking state. The most 'secure' locking state it mentions is a write-transaction and this can coexist with read-transactions from others. Thus "begin exclusive" starts a write-transaction and the on-going read does not interfere. The error message seems to clarify the situation further: database table is locked. Thus the collision occurs at the table-level. And yes, taking different tables for read and write, it does not occur. Practically this may not help very much. But may be the following does in case you have a busy_timeout setting. When having Shared-Cache mode enabled, the timeout setting appears to be ignored by SQLite. This makes locking situations surface rather soon, also when there is no dead-lock. The situation may be handled by a programmatic retry? Regards, Ed Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven: Some additional info: when the sqlite_lock is returned there is another thread that appears to be reading the same table. Does the sqlite3 step return sqlite_locked in this case? Thanks, Ken Ken wrote: While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a strange lock situation. SQLITE_LOCK is returned from an insert statement, even though the thread/connection performed a successful "begin exclusive" transaction. begin exclusive insert into table... ---> returns SQLITE_LOCKED Is it possible for both connections to begin exclusive transactions whilst having the shared cache anabled? Thanks, ken - - --- To unsubscribe, send email to [EMAIL PROTECTED] - - --- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] shared cache mode locking
The ticket has already been resolved, I see. So it has been considered a bug. In my earlier reply I tried to defend the current behavour to be in line with the document, http://sqlite.org/ sharedcache.html. I'm happy to change my mind now. Only I miss something in the model as described in the document. This may either be: - exclusive transactions as a new kind of transactions, apart form read- and write-transactions or - database-level locking as a new level above transaction-level locking. May be this suggestion is too naive, anyway it helps me explain the wonderful cache sharing. Ken wrote: Ed, Dan opened a ticket. I agree the documentation isn't clear on the Exlusive locking state. Not really sure, if this is by design or a bug at this stage. I do think its a great feature of the Shared cache mode to allow table level locking. But I'm curious with this table level locking what would happen if two threads performed writes to two seperate tables concurrently using only a begin immediate. Thread a writes to tab1, Thread b writes to tab2, (Is this allowed ? or is a sqlite_locked kicked returned?) If it is allowed then would there be two journal files concurrently existing? And What happens during a crash with two journals ? This gets complicated very quickly. Ken Ed Pasma <[EMAIL PROTECTED]> wrote: Hello,` Empirically I found that it is exactly true. Must admit I'm confused but may it is in line with the Shared-Cache locking model. This does not mention the EXCLUSIVE locking state. The most 'secure' locking state it mentions is a write-transaction and this can coexist with read-transactions from others. Thus "begin exclusive" starts a write-transaction and the on-going read does not interfere. The error message seems to clarify the situation further: database table is locked. Thus the collision occurs at the table-level. And yes, taking different tables for read and write, it does not occur. Practically this may not help very much. But may be the following does in case you have a busy_timeout setting. When having Shared-Cache mode enabled, the timeout setting appears to be ignored by SQLite. This makes locking situations surface rather soon, also when there is no dead-lock. The situation may be handled by a programmatic retry? Regards, Ed Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven: Some additional info: when the sqlite_lock is returned there is another thread that appears to be reading the same table. Does the sqlite3 step return sqlite_locked in this case? Thanks, Ken Ken wrote: While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a strange lock situation. SQLITE_LOCK is returned from an insert statement, even though the thread/connection performed a successful "begin exclusive" transaction. begin exclusive insert into table... ---> returns SQLITE_LOCKED Is it possible for both connections to begin exclusive transactions whilst having the shared cache anabled? Thanks, ken -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] shared cache mode locking
Hello,` Empirically I found that it is exactly true. Must admit I'm confused but may it is in line with the Shared-Cache locking model. This does not mention the EXCLUSIVE locking state. The most 'secure' locking state it mentions is a write-transaction and this can coexist with read-transactions from others. Thus "begin exclusive" starts a write-transaction and the on-going read does not interfere. The error message seems to clarify the situation further: database table is locked. Thus the collision occurs at the table-level. And yes, taking different tables for read and write, it does not occur. Practically this may not help very much. But may be the following does in case you have a busy_timeout setting. When having Shared-Cache mode enabled, the timeout setting appears to be ignored by SQLite. This makes locking situations surface rather soon, also when there is no dead-lock. The situation may be handled by a programmatic retry? Regards, Ed Op 19-dec-2007, om 19:12 heeft Ken het volgende geschreven: Some additional info: when the sqlite_lock is returned there is another thread that appears to be reading the same table. Does the sqlite3 step return sqlite_locked in this case? Thanks, Ken Ken <[EMAIL PROTECTED]> wrote: While using the new 3.5.4 sqlite3_enable_shared_cache I ran into a strange lock situation. SQLITE_LOCK is returned from an insert statement, even though the thread/connection performed a successful "begin exclusive" transaction. begin exclusive insert into table... ---> returns SQLITE_LOCKED Is it possible for both connections to begin exclusive transactions whilst having the shared cache anabled? Thanks, ken - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Huge performance drop when using prepared statement
Hello, think I got it, but it is disappointingly simple, see below. Ed.
Markus Gritsch wrote:
Even more strange:
c.execute("""SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB ?
""", ('tes*',))
takes less than 1ms but
c.execute("""SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB ?
""", ('test',))
takes several hundred ms.
The execute in Python includes prepare (or get from cache), bind and
the first step.
The answer must be that the wait time lies in the first step.
The engine is doing a full scan and it all depends how far in the
table it needs to go to find the first match.
So the bind values with * just come across a match sooner.
Wilhelm Braun wrote:
I just tried for fun:
start = time.time()
SQLString=('''SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB '%s'
''' % "hui*")
c.execute(SQLString)
and it is as fast as your first one - seems a pysqlite problem to me
I know they say this is not a secure way to do it -- well.
This seems the only solution after all.
But it floods the wonderful pysqlite statement cache, with new SQL
statements for each new bind value.
Preferably, only the operator is substituted in the SQL, for "GLOB"
or just "=", depending on the actual bind value.
That leaves just two different statements.
But I don't know if the result is the same as I don't know GLOB very
well.
If it is affected by the case_sensitive_like pragma my idea is too
simple.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Trigger update of multiple columns
Hi, I tried to update a list of columns: UPDATE t SET (c1, c2, c3) = (SELECT c1, c2, c3) FROM t2 WHERE .. but this syntax is not accepted as you probably already know. I may promote [INSERT OR] REPLACE then. It is syntactically described in the SQLite documentation but for the semantics you may see the original MySQL doc. http://dev.mysql.com/doc/refman/5.0/en/replace.html It is the only way that I see to do the update with only a single scan of the product table. But may be REPLACE causes troubles in combination with triggers. Because indirectly it performs a DELETE and a new INSERT. Other suggestions should be welcome. Regards, Ed Op 17-jun-2007, om 10:00 heeft T het volgende geschreven: Hi All, I have a pretty standard sales tracking database consisting of tables: Products - Each row is a product available for sale. Includes fields: Code, Buy, Sell, Description Sales - Each row is a sale made to a customer. Includes fields: Ref, Customer Sale_Products - Each row is an product (many) included in a sale (one). Includes fields: Sale_Ref, Code, Buy, Sell, Description Now, when I add a new Sale_Products row and assign a product Code to it, I want to trigger it to auto enter the Buy and Sell prices, and the description, by looking up the related Product (ie where Sale_Products.Code = Products.Code) How can I do this? I have something like this: create trigger Update_Sale_Products_Code after update of Code on Sale_Products begin update Sale_Products set Buy = (select Buy from Products where Products.Code = new.Code) , Sell = (select Sell from Products where Products.Code = new.Code) , Description = (select Description from Products where Products.Code = new.Code) where rowid=new.rowid ; end It works, but it's unnecessarily slow, since it takes a while to look up the huge Products table (which is actually a UNION ALL of various supplier catalogs), and it's looking it up for each updating field (and I have more fields to lookup than shown in this example). It would be more efficient to look it up once to find the corresponding product (according to Products.Code = new.Code), but I'm stumped as to how to do that. I tried: create trigger Update_Sale_Products_Code after update of Code on Sale_Products begin update Sale_Products set Buy = (select Buy from Products) , Sell = (select Sell from Products) , Description = (select Description from Products) where rowid=new.rowid and Products.Code = new.Code ; end But that fails, and seems a bit ambiguous anyway. It seems to need some kind of JOIN, but I can't see provision for it in the UPDATE syntax. There must be a much simpler way that I'm overlooking. Please enlighten me. Thanks, Tom -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] Can't update table from itself or multiple tables
Hi, Trey. I checked not implemented features list of sqlite and found nothing about "update ... from". There's no "FROM" on http://www.sqlite.org/lang_update.html, so that's a hint that it's not supported. All the same, maybe this should be added to http://www.sqlite.org/omitted.html update table1 set val = ss.v from (select t2.some as v, t1.id as id from table1 t1, table2 t2 where t1.id = t2.nid) as ss where ss.id = table1.id How about update table1 set val = (select some from table2 where table1.id = table2.nid ); - TMack It works. Thanx. Sometimes this approach it's too slow, sometimes it doesn't works but I can go ahead now :). Hello, I wish to propose another statement, though it does not look so attractive. But sure it is fast: insert or replace into table1 (rowid, id, val2, val) select t1.rowid, t1.id, t1.val2, t2.some from table1 t1 left outer join table2 t2 on t2.id = t1.nid; This is equivalent to the update statement, assuming: 1. table1 has no primary key (so rowid is used) 2. table1 has no further columns as the ones mentioned (id, val and val2) The outer join is added fot complete equivalence. It arranges that "val" gets assigned null where no matching row is found in table2. If that is not desirable, this can be omitted to leave singular rows unchanged. Hope this is useful, Ed Pasma - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] One more SQLite threading question
However, it would be too time consuming to serialize every call to sqlite3_step(), so I wonder whether it can be called in another thread. This almost immediately raises "library routine called out of sequence". It occurs as soon as the processing of A and B overlap, that means A is preparing statement #2 while B is still executing #1. Have you tried using Mutex or some other way to prevevent really simultaneous calling of SQLite methods? My guess that it should work well then, but I haven't tried it myself... Yes I did the same experiment with a lock that made thread A wait until B was finished. So actually only one thread can be active at the time. I don't see how the outcome of this experiment can be of any interest, as there is no time reduction any longer. But your guess is right that, it works. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] One more SQLite threading question
Hello, I have no inside-knowledge from SQLite, but I'am in the circumstance to easily do this experiment. Hope I understand it right and that you consider a sort of pipe-lining. Anyway, I started the two threads A and B, and made A exclusively do all the sqlite3_prepare calls, and B the rest, including sqlite3_step. This almost immediately raises "library routine called out of sequence". It occurs as soon as the processing of A and B overlap, that means A is preparing statement #2 while B is still executing #1. So the experimental conclusion is that this won't work. But this applies only to the pipelining idea. The serializing to use a single connection may still offer an interesting new locking model. Regards, Ed Pasma Op 14-mei-2007, om 13:04 heeft Jiri Hajek het volgende geschreven: Hello, I have tried to search all the documentation about threading in SQLite, but I'm still somewhat confused. It's often suggested to create a pool of sqlite3 structures, but what if I would like to have only only sqlite3 connection and serialize all the DB operations to one thread (name it 'A') that would prepare and execute all the queries. I guess that this would work well... However, it would be too time consuming to serialize every call to sqlite3_step(), so I wonder whether it can be called in another thread. So my scenario is: 1. Thread B wants to open a query 'SELECT * FROM Tbl1' 2. Thread A is used to prepare the query 'SELECT * FROM Tbl1' using sqlite3_prepare16_v2() and using the same sqlite3 DB connection as for possibly some other running SQL statements. 3. Thread B now repeatedly calls sqlite3_step(), sqlite3_column_text16() and similar functions in order to get all rows from DB. 4. Thread A is used to call sqlite3_finalize() on the openned query. So my questions are: a. Would the code described above work. b. In step 3., do I have to somehow make sure that calls to sqlite3_step() don't interfere with other SQLite processing in thread A, e.g. by Windows CriticalSections? Is anything like this also needed for sqlite3_column_text16()? Thanks for any explanation, Jiri - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Most appropriate Web based database? (Newbie)
Hi Len, The question you are asking is "loaded". Stay with the ansi SQL statements that SQLite supports and you will be okay. If you need to use the SQLite extensions now, you will have to write a "translator" in future - not too difficult. For an experienced programmer (which you will be if the situation you describe ever comes to pass) the answer is "use SQLite - it is a no brainer" Good Luck! At 10:13 AM 4/6/06 -0700, you wrote: > >Thanks for your reply Richard > >After further Googling and reading the replies I've received here I am >coming around to the idea that SQLite probably is the best choice. > >I would like to know whether - at some point in the future should it be >necessary - I could convert my entire SQLite databases including tables etc >to another RDMS such as MySQL? Is this straightforward or convoluted? > >Regards >Len >-- >View this message in context: http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404 628.html#a3788822 >Sent from the SQLite forum at Nabble.com. > > Sincerely, Ed Porter
Re: [sqlite] Re: Select with left outer join - Am I going mad or just having a bad day?
Hi Ian, This one has been interesting! I'm trying to repeat the problem with several different databases - so far no luck. I use SQLite 2.8xx (project is already ongoing) and will test when I have time. I will post results if I find anything of value. At 06:41 PM 3/21/06 +, you wrote: > >On 21 Mar 2006, at 18:11, Ed Porter wrote: > >> Hi Ian, >> >> You nee to use a Full Outer Join. I don't know if SQLite has this >> function. >> >> > >Nah, definitely only wanted a left outer, didn't want the results >multiplied up or anything, just wanted every Category and any Cases >if matching the criteria. > >It was solved by moving the where clause up into an AND on the join, >seems that the where clause choking the result set from the outer >join. Never seen this before, but then again I've generally used "old >style" syntax joins (in the where clause), not ANSI, so I'm a bit new >to how the where clause can effect the results of an ANSI join. But >I'm learning! > >Thanks for your response though Ed, much appreciated. > >Regards, >-- >Ian M. Jones >_______ >IMiJ Software >http://www.imijsoft.com >http://www.ianmjones.net (blog) > > > Sincerely, Ed Porter
[sqlite] Please help, am I doing something wrong?
Hi, I know that sqlite3 uses reader-writer locks and I've read the part of FAQ that describes the multiple client access. But I'm still having a lot of problem. I use sqlite3-ruby to access the database file with Rails/ActiveRecord. I also use sqlite3 C API to access the database file using C in my other client. I have another program that uses regular ruby code and sqlite3-ruby to access the same database file. I kept on getting database is locked error. I have busy-handler registered for all of them and the write should take only milliseconds (very simple write). All the programs do some writing. Is there a way to only lock the whole file when writing and not when reading? Actually, for anyone experienced in sqlite3, what's the correct way of using it in my situation? Thank you very much. --ed
Re: [sqlite] Storing text in sqlite vs. external flat file
Hi Ken I found that performance began to fail miserably as the blob size increased above 500 bytes (has anyone else experienced this problem?). When I posted the problem, I think that someone stated the docs show the reasonable limit on blobs is 230 bytes. Anyways, I had to store the blobs direct to disk and use SQLite to track the addresses (no different than Oracle, MSSQL, MySQL etc). Please notify if you can get your system to work! At 01:21 PM 9/10/04 -0700, [EMAIL PROTECTED] wrote: >I am looking into using sqlite for storing some data that will be ~100,000 >records in size, where each record will contain text that has an average >size of 40k, but could be > 200k. I will likely need to encrypt (and >potentially compress) the database. > > > >My question is whether to store the text in the database, or to keep a >separate file for the text with seek pointers and lengths in the database. >My preference is for the former, since I wouldn't have to manage a separate >robust encryption/compression/deletion process, but I am concerned about the >perf and size of the insertion stress experiments I've been running, and >I've seen comments on this list suggesting the latter for BLOBs. Are there >specific tuning tweaks I can make to improve my results? > > > >Thanks, > >Ken Cooper > > Sincerely, Ed Porter
Re: [sqlite] ANN: SQLiteDB, a COM wrapper around sqlite has been released
HI George, I have tested the SQLiteDB for Visual Basic. I find it to be excellent - it has raised the standard for VB wrappers exponentially. I will be changing over all our new applications to this product - please keep it standardized as I will be posting references to it all over the 'Net. Again, thanks for developing an excellent product! At 09:23 AM 7/22/04 +0300, George Ionescu wrote: >Hello SQLite users, >and VB sqlite users, > >I have just released SQLiteDB, a COM wrapper around sqlite written in ATL. Go get your copy from http://www.terrainformatica.com/users/george (note that the link will change in a few days to http://www.terrainformatica.com/SQLiteDB). > >You will also get some samples written in VB: SQLiteImport, a tiny database import utility, SQLiteBenchmark - compare SQLiteDB's performance with an Access database (ADO/JET) and SQLiteMultiThread - hammer an sqlite database by reading data from multiple threads. > >Best regards, >George Ionescu Sincerely, Ed Porter
[sqlite] Saving BLOBs in Sqlite
Dear sirs,
I am trying to develop techniques to save BLOBs in Sqlite. What is the best
way to save jpg(S), bmp(s), etc?
Would it be best to use GetBitmapBits ("gdi32" library) and then save to
Sqlite as a byte array or is there a more efficient method?
I am new to Sqlite but it seems to be an amazing tool!
I look forward to any help and I thank you in advance for your input.
Sincerely,
Ed Porter
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
