Re: [sqlite] What is so slow on dropping tables?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/31/2011 12:18 PM, Jan Hudec wrote: > - Is there any way to speed it up? Another way (somewhat hinted at by Nico) is that you can create these tables in separate databases and use attach to bring them in. To drop a table you can just detach and then delete the database file (at a later point if necessary). If the new database is going to be the approximately the same size as the old one then you can overwrite the database header to cause the database to be empty but already the allocated size so the operating system won't have to go through a free then allocate cycle for the file blocks. This will also allow you to do hot swapping if that is important to you. You can build up the new tables/databases separately and then when ready to use them do a detach/attach which should be very quick. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk3l6IQACgkQmOOfHg372QRfTwCgqT0qnW9osxVGff9M1w5rDyKh ANIAoKr+cCCpkJRXnfIGmdglpGdWEEtZ =DDJh -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Big difference in performance between Python and gcc
Hello, I made some preliminary tests for an application storing big chunks of data in a sqlite database. I did firsts tests with python and they gave me quite impressive results. I then tried to make the same test using C. I expected to get execution times to be the same of those of python. However I was surprised as the performance got a lot worse, with execution times being more than 3 times more. I tried everything I could think of and also peeked at python module's source but i couldn't find any way to get C program performance to match python's one. Any suggestion of what could i be doing wrong? I include both python and C source code's. The only thing that this program does is creating a database, making a table where a pair of integer maps 8192-bytes blobs and writing 100k rows in it. Any suggestions of what I could be doing wrong? Note: These are the results i get for Windows/MinGW environment. I have no Linux box at hand at the moment. -- Python results bash-3.1$ python --version Python 2.7rc2 bash-3.1$ time python testsqlite.py 0 1 2 3 4 5 6 7 8 9 30.8555624521 real0m31.249s user0m0.015s sys 0m0.015s -- C results bash-3.1$ gcc --version gcc.exe (GCC) 4.5.0 Copyright (C) 2010 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. bash-3.1$ gcc -O2 test.c sqlite/sqlite3.c && time ./a.exe 0 1 2 3 4 5 6 7 8 9 real2m33.376s user0m0.015s sys 0m0.015s -- Python script conn = sqlite3.connect('test1.sqlite') c=conn.cursor() c.executescript('''CREATE TABLE IF NOT EXISTS values_log(acquisition INTEGER,chunk INTEGER, acq_data BLOB); CREATE INDEX IF NOT EXISTS values_step ON values_log(acquisition,chunk); ''' ) conn.commit() def f(): data="01234567"*1024 with conn: for i in range(0,10): conn.execute("INSERT INTO values_log VALUES (?,?,?)",(1,i,data)) if not i%1: print i conn.commit() ret=timeit.timeit(f,'gc.enable()',number=1) print ret --- C source #include "sqlite3.7.6.3/sqlite3.h" sqlite3* db; char* db_err; int main(int argc,const char *argv) { int ret; sqlite3_stmt *db_stm; sqlite3_open("testDB.sql", &db); if(SQLITE_OK!=(ret=sqlite3_exec(db, "create table if not exists 'helloworld' (acq integer, chunk integer, data blob);" "CREATE INDEX IF NOT EXISTS acq_index ON helloworld(acq,chunk);" , NULL, 0, &db_err))) { fprintf(stderr,"sqlite error in sqlite3_exec() [%d]",ret); return -1; } if(SQLITE_OK!=(ret=sqlite3_exec(db, "PRAGMA synchronous=OFF;", NULL, 0, &db_err))) { fprintf(stderr,"sqlite error in sqlite3_exec() [%d]",ret); return -1; } if(SQLITE_OK!=(ret=sqlite3_exec(db, "begin transaction;", NULL, 0, &db_err))) { fprintf(stderr,"sqlite error in sqlite3_exec() [%d]",ret); return -1; } if(SQLITE_OK!=(ret=sqlite3_prepare_v2(db, "INSERT INTO helloworld VALUES (?,?,?)", -1, &db_stm, NULL ))) { fprintf(stderr,"sqlite error in prepare() [%d]",ret); return -1; }; int i; char data[1024*8+1]; for(i=0;i<1024*8;i++)data[i]='0'; data[1024*8]='\0'; for(i=0;i<10;i++) { if(!(i%1))printf("%d\n",i); if(SQLITE_OK!=(ret=sqlite3_bind_int(db_stm, 1, 1))) { fprintf(stderr,"sqlite error in bind()"); return -1; } if(SQLITE_OK!=(ret=sqlite3_bind_int(db_stm, 2, i))) { fprintf(stderr,"sqlite error in bind()"); return -1; } //if(SQLITE_OK!=(ret=sqlite3_bind_blob(db_stm, 3, data,-1, SQLITE_STATIC/*SQLITE_TRANSIENT*/))) if(SQLITE_OK!=(ret=sqlite3_bind_blob(db_stm, 3, data,8192, SQLITE_STATIC/*SQLITE_TRANSIENT*/))) { fprintf(stderr,"sqlite error in bind_blob() [%d] ok=%d",ret,SQLITE_OK); return -1; } ret=sqlite3_step(db_stm); if(ret!=SQLITE_DONE) { fprintf(stderr,"sqlite error in sqlite3_step() [%d]",ret); return -1; } if(SQLITE_OK!=(ret=sqlite3_reset(db_stm))) { fprintf(stderr,"sqlite error in sqlite3_reset() [%d]",ret); return -1; } sqlite3_clear_bindings(db_stm); } if(SQLITE_OK!=(ret=sqlite3_exec(db, "commit;", NULL, 0, &db_err))) { fprintf(stderr,"sqlite error in sqlite3_exec() [%d]",ret); return -1; } sqlite3_close(db); } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big difference in performance between Python and gcc
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/01/2011 12:25 AM, Alessandro Marzocchi wrote: > The only thing that this program does > is creating a database, making a table where a pair of integer maps > 8192-bytes blobs and writing 100k rows in it. Any suggestions of what > I could be doing wrong? A major difference I see is that you are storing strings in the Python version but blobs in the C version. Additionally the contents are different between the two being all '0' for the C while '01234567' for the Python. To make them identical use buffer() around the Python string which will then cause it to be treated as blob at the SQLite level. Another difference is that your timing does not include program startup for Python but does for C. Finally the databases have different file names. I'll bet that virus scanners and backup software are interfering to some degree during startup and operation. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk3l7rAACgkQmOOfHg372QS29gCfQ6hWfdEojNbnAb1knuxIWtUO NkEAnRzR0gQvz3W3caCUrEsBUuOyt6iD =S7lS -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is so slow on dropping tables?
Quoth Roger Binns , on 2011-06-01 00:21:44 -0700: > On 05/31/2011 12:18 PM, Jan Hudec wrote: > > - Is there any way to speed it up? > > Another way (somewhat hinted at by Nico) is that you can create these tables > in separate databases and use attach to bring them in. To drop a table you > can just detach and then delete the database file (at a later point if > necessary). If the new database is going to be the approximately the same > size as the old one then you can overwrite the database header to cause the > database to be empty but already the allocated size so the operating system > won't have to go through a free then allocate cycle for the file blocks. This is a reasonable suggestion, but watch out for the limit on the number of attached databases. You cannot reliably have more than 30 of them on a custom compiled SQLite3 (for 32-bit integers; the doc is silent re int64 type) and the default is a compile-time limit of 10. Whether this is a problem depends on your data and application architecture. ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is so slow on dropping tables?
>Quoth Roger Binns , on 2011-06-01 00:21:44 -0700: > > On 05/31/2011 12:18 PM, Jan Hudec wrote: > > > - Is there any way to speed it up? > > > > Another way (somewhat hinted at by Nico) is that you can create > these tables > > in separate databases and use attach to bring them in. To drop a > table you > > can just detach and then delete the database file (at a later point if > > necessary). If the new database is going to be the approximately > the same > > size as the old one then you can overwrite the database header to > cause the > > database to be empty but already the allocated size so the > operating system > > won't have to go through a free then allocate cycle for the file > blocks. > >This is a reasonable suggestion, but watch out for the limit on the >number of attached databases. You cannot reliably have more than 30 >of them on a custom compiled SQLite3 (for 32-bit integers; the doc is >silent re int64 type) and the default is a compile-time limit of 10. >Whether this is a problem depends on your data and application >architecture. Also no foreign key to/from attached DBs, which may be a stronger limitation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Big difference in performance between Python and gcc
Hi Roger, thank for your answer. I tried to modify programs to match your suggestion, performances improved but are still far from pyhon's ones. (Sorry if this message starts a different thread but i had forwards of single messages disabled so i could not reply to the original post) C modified source and timing - bash-3.1$ diff test.c testold.c 9c9 < sqlite3_open("test1.sqlite", &db); --- > sqlite3_open("testDB.sql", &db); 45c45 < for(i=0;i<1024*8;i++)data[i]='0'+i%10; --- > for(i=0;i<1024*8;i++)data[i]='0'; 61c61 < if(SQLITE_OK!=(ret=sqlite3_bind_text(db_stm, 3, data,-1, --- > //if(SQLITE_OK!=(ret=sqlite3_bind_text(db_stm, 3, data,-1, 63c63 < //if(SQLITE_OK!=(ret=sqlite3_bind_blob(db_stm, 3, data,8192, --- > if(SQLITE_OK!=(ret=sqlite3_bind_blob(db_stm, 3, data,8192, bash-3.1$ gcc -O2 test.c sqlite/sqlite3.c && time ./a.exe [...] real1m58.056s user0m0.015s sys 0m0.015s Python's timing (another run, i'm looking at time tool statistic not at timeit module so they include also startup times) bash-3.1$ time python testsqlite.py [...] real0m30.906s user0m0.015s sys 0m0.000s Trying the other way round (making python's code same as old C code) bash-3.1$ diff testsqlite.py testsqlite_old.py 12c12 < conn = sqlite3.connect('testDB.sql') --- > conn = sqlite3.connect('test1.sqlite') 51c51 < data=buffer(""*1024) --- > data="01234567"*1024 bash-3.1$ time python testsqlite.py [...] real0m30.421s user0m0.015s sys 0m0.015s ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 reserved characters
On 06/01/2011 06:30 AM, Mohd Radzi Ibrahim wrote: > Hi, > I was searching for any reference to reserved characters used in FTS4, but > failed to find any. > > I have problem with query with - ftstable match 'width 5" ' > > But it's ok with - ftstable match 'width 5' > > to fix this, I replaces every double-qoute in query with empty space. > > My question, is there any other characters that has special meaning in FTS > match query? Those mentioned in the query syntax here: http://www.sqlite.org/fts3.html#section_3 The query parser is probably confusing the unmatched '"' character for the start of a quoted phrase. I guess you also have to watch out for '-', '*' and ':'. Also the uppercase keywords AND, OR, NOT and NEAR. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is so slow on dropping tables?
On 06/01/2011 02:18 AM, Jan Hudec wrote: > Hellow folks, > > At $work we have an application that processes *huge* (tens of millions of > rows in some of the larger tables, sometimes over 30GiB file size). This > application changes and when it does, it drops some tables and calculates > them again. What is somewhat surprising is that dropping the tables itself > takes quite long (order of minutes) time. > > - What is the reason it might take that long? I didn't expect removing the > table entry in sqlite_master and adding it's pages to the free list to > take that long. > - Is there any way to speed it up? The application works in big tasks, each > of which opens a transaction and creates one or few tables, dropping any > old versions of those tables first. So could perhaps moving the drops out > of the transaction help? It would be correct, once the table is found > obsolete, it would be found obsolete after rollback and retry again, but > it would take quite a bit of refactoring, so I'd only do it if it's likely > to help significantly. If you have foreign-keys enabled (and one or more FK's that involve the table being dropped), that can slow things down. If this is the case, try using the pragma to disable FKs before running the DROP TABLE. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Substring (LIKE "%key%") searches, would FTS3 with suffix-tree tokenizer be the fast way?
Were you able to solve the problem? What solution have to chosen? BR, Fedor I'd appreciate any suggestions on good ways to do this, I'm neither an SQL or sqlite expert, so I might be thinking about it all wrong. I have something like a (read-only) address book/rolodex, with interactive searching. As users type into the search box, I need to first know for each section how many rows match the substring typed so far. I only display the rows that are visible on screen. I have two queries: (A) I count the rows in a letter group. If they typed "e": select substr(name,1,1), count(*) from my_table where name like '%e%' group by substr(name,1,1); A|94 B|118 C|131 ... This is too slow, ~3sec, with 2500 rows, and we want to have 1 rows. Worse, when they type "es", the search is as slow after they type "s" as when they typed "e", even though the "es" rows are a sub-set of the rows that matched "e". FTS3 only searches full terms/words by default, but I think if I built a custom tokenizer that returned all the suffix trees for a name: "fu bar" => [ "r", "ar", "bar", " bar", "u bar", "fu bar"] That I could do rewrite query (A) like this: select substr(name,1,1), count(*) from my_table where name match 'e*' group by substr(name,1,1); Is this a reasonable approach? Is there a better way? Has somebody else done this? (B) I access specific rows within a letter group. For visible rows, I fetch them by offset into a letter group, so row 4 in the "g" section of names containing "e" would be: select * from my_table where name like "g%" and name like "%e%" order by name limit 1 offset 4; The performance for this is OK, right now, I think it's because the first LIKE can use the index, so the linear scan is over only a few hundred rows. Or it could be that the on-screen display of each row is slower than the DB search. I think it might become a problem, though. I'm not sure how I would rewrite this to use FTS3 if it turns out to be to slow for a larger DB, maybe a tokenizer that puts the first letter of the name as the first letter of every suffix? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Biggest number in an INTEGER field
Guys, an an SQLite3 INTEGER field what is the maximum number that fits in an INTEGER PRIMARY KEY field? Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Biggest number in an INTEGER field
> Guys, an an SQLite3 INTEGER field what is the maximum number that fits in an INTEGER PRIMARY KEY field? According to http://www.sqlite.org/faq.html#q1, it is 9223372036854775807. Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Syncing databases on different servers
Hey guys, First of all, I'm aware that SQLite is not a good choice for concurrency over several machines. I do not need instant syncing of SQLite databases, however I do have a table stored on server X which I would like to gradually send changes of to various other servers connected by TCP. In the first instance, I'd be quite happy to send rows with ids which do not exist in the other servers' tables, and not worry about changes to rows. Does anyone have any advice on this matter? Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syncing databases on different servers
Ian Hardingham wrote: > I do not need instant syncing of SQLite databases, however I do have a > table stored on server X which I would like to gradually send changes of > to various other servers connected by TCP. Perhaps you could use something like rsync to synchronize at the file level. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create DB file and then Create Table - Table FAILS.
I don't see anywhere a "sqlCmd.Execute()" or whatever the method is to execute the command. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Don Ireland Sent: Tuesday, May 31, 2011 11:21 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Create DB file and then Create Table - Table FAILS. Ooh. I don't know what happened to that message it was sent as paragraphs. It's kinda hard to read now. Don Ireland -Original Message- From: Don Ireland To: SQLite Sent: Tue, 31 May 2011 10:18 PM Subject: [sqlite] Create DB file and then Create Table - Table FAILS. I'm hoping someone can help me with this. Using Visual Studio C++, the following code DOES create the DB file. But the table doesn't get created and I'm stumped as to why it won't create the table. SQLiteConnection conn; conn.ConnectionString = "Data Source=D:\Users\Don\Downloads\CashBoxPrefs.dat"; conn.Open(); SQLiteCommand sqlCmd(%conn); sqlCmd.CommandText = "CREATE TABLE IF NOT EXISTS Prefs (SyncEnabled bool,SyncWatchPort int,SyncLoginKey TEXT,SyncAccessKey TEXT, SyncLogFile TEXT, SyncLogClearInterval int,GenLatestBook TEXT, GenBookMRU_0 TEXT,GenBookMRU_1 TEXT, GenBookMRU_2 TEXT, GenBookMRU_3 TEXT, GenBookMRU_4 TEXT);"; But if I run following at the SQLite3.exe from the command line, it DOES create the file and the table. sqlite3 Cashboxprefs.dat sqlite> CREATE TABLE IF NOT EXISTS Prefs (SyncEnabled bool,SyncWatchPort int,SyncLoginKey TEXT,SyncAccessKey TEXT, SyncLogFile TEXT, SyncLogClearInterval int,GenLatestBook TEXT, GenBookMRU_0 TEXT,GenBookMRU_1 TEXT, GenBookMRU_2 TEXT, GenBookMRU_3 TEXT, GenBookMRU_4 TEXT); ___ 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] Syncing databases on different servers
See http://sqlite.mobigroup.ru/wiki?name=sqlite3-rdiff 2011/6/1 Ian Hardingham : > Hey guys, > > First of all, I'm aware that SQLite is not a good choice for concurrency > over several machines. > > I do not need instant syncing of SQLite databases, however I do have a > table stored on server X which I would like to gradually send changes of > to various other servers connected by TCP. > > In the first instance, I'd be quite happy to send rows with ids which do > not exist in the other servers' tables, and not worry about changes to rows. > > Does anyone have any advice on this matter? > > Thanks, > Ian > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TRANSACTIONs
Hey guys, thanks for all the help so far today. From within a BEGIN TRANSACTION and END TRANSACTION block, should I not update the same row in a table more than once? What are the exact limitations on what I can do during a Transaction? Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRANSACTIONs
> From within a BEGIN TRANSACTION and END TRANSACTION block, should I not > update the same row in a table more than once? You can update it as many times as you need. > What are the exact limitations on what I can do during a Transaction? Do not update a table if there is some select statement currently active and using it. Active means you made at least one call to sqlite3_step and last call to sqlite3_step returned SQLITE_ROW. Pavel On Wed, Jun 1, 2011 at 10:00 AM, Ian Hardingham wrote: > Hey guys, thanks for all the help so far today. > > From within a BEGIN TRANSACTION and END TRANSACTION block, should I not > update the same row in a table more than once? What are the exact > limitations on what I can do during a Transaction? > > Thanks, > Ian > ___ > 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] Error (LOCKFILE_EXCLUSIVE_LOCK) at building sqlite project for pocket pc
Hi, Thank you for reading my post. I have downloaded the following archive: http://www.sqlite.org/sqlite-amalgamation-3070603.zip It contains: - shell.c - sqlite3.c - sqlite3.h - sqlite3ext.h My OS is "Windows Vista". With "Microsoft Visual Studio 2005 Version 8.0", I created a new "Project": "File -> New -> Project" I chose: "Visual C++", "Smart Device" "Win32 Smart Device Project" I gave it a name: "sqlite6". I selected the SDK: "Pocket PC 2003". I chose "Application type": "Static Library". I unchecked the additional option (which was checked): "Precompiled header". In the "Header Files" section of the project, I added the existing items: - sqlite3.h - sqlite3ext.h In the "Source files" section of the project, I added the existing item: - sqlite3.c When I choose "Build sqlite6", I get the following error: = Error 1 error C2065: 'LOCKFILE_EXCLUSIVE_LOCK' : undeclared identifier c:\sqlite\sqlite-amalgamation-3070603\sqlite3.c 32390 = Can you tell me what to do to solve that issue? Best regards, -- hmas -- View this message in context: http://old.nabble.com/Error-%28LOCKFILE_EXCLUSIVE_LOCK%29-at-building-sqlite-project-for-pocket-pc-tp31750353p31750353.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syncing databases on different servers
Hi Ian, > I do not need instant syncing of SQLite databases, however I do have > a table stored on server X which I would like to gradually send > changes of to various other servers connected by TCP. > > In the first instance, I'd be quite happy to send rows with ids which > do not exist in the other servers' tables, and not worry about > changes to rows Here are two methods that you may consider, they are suitable to send new rows or sync entry value changes. 1) The first is a standard logging method. To make an example of a schema: - There are three tables: SearchIndex, ThreadSession and ThreadIndex. Each table has three indexing columns or fields. Indexing in this case means if the entry value changes, then the new information in this row should be synced with the server. There are other columns but their content is further desciption relative to the indexing columns in the table. - INSERT, UPDATE and DELETE triggers monitor the 3 indexing columns in each table using the LIKE operator. A change in the entry value of the indexing fields fires the trigger which inserts a record in a logging table with the table name and Integer Primary Key. Joining the data table with the log table generates the row containing the current values. - Use the generated row to sync the dbs. - This method has been heavily exercised and tested over consider time and proves to be reliable. Using the LIKE operator in this scenario has not been detrimental to performance and accurate. 2) Method 2 uses temporary tables to hold the queried values in a user session. The same tables as in 1) are used in this example. One temporary table holds the query result rows for each table. So for the tables SearchIndex, ThreadSession and ThreadIndex there are also SearchIndex_temp, ThreadSession_temp and ThreadIndex_temp. In addition one column flags changes based on the same criteria as 1). If the contents of one of the three indexing columns or fields in the temp tables changes, then the temp row is flagged. - In the three tables DELETE the rows that have not changed and are not flagged and what remains can be used to sync the changes. - Gary Gabriel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error (LOCKFILE_EXCLUSIVE_LOCK) at building sqlite project for pocket pc
Me again... Here is what I've read: = FYI, Compiling for Pocket PC 2003 stopped to work since version 3.7.1 and 3.7.2. So using 3.6.23.1 for now until it gets fixed. = (http://comments.gmane.org/gmane.comp.db.sqlite.general/59021) My SQlite version is 3.7.6.3. An answer was: = There have been several posts on this issue just this week, and suggested workarounds. = but I couldn't find what workarounds the author was talking about. So, I was wondering if the problem mentionned above was fixed or not... and what I have to do. Thank you and all the best, -- hmas hmas wrote: > > Hi, > > Thank you for reading my post. > > I have downloaded the following archive: > http://www.sqlite.org/sqlite-amalgamation-3070603.zip > It contains: > - shell.c > - sqlite3.c > - sqlite3.h > - sqlite3ext.h > > My OS is "Windows Vista". > With "Microsoft Visual Studio 2005 Version 8.0", I created a new > "Project": > "File -> New -> Project" > I chose: > "Visual C++", "Smart Device" > "Win32 Smart Device Project" > > I gave it a name: "sqlite6". > I selected the SDK: "Pocket PC 2003". > I chose "Application type": "Static Library". > I unchecked the additional option (which was checked): "Precompiled > header". > > In the "Header Files" section of the project, I added the existing items: > - sqlite3.h > - sqlite3ext.h > > In the "Source files" section of the project, I added the existing item: > - sqlite3.c > > When I choose "Build sqlite6", I get the following error: > > = > Error 1 error C2065: 'LOCKFILE_EXCLUSIVE_LOCK' : undeclared identifier > c:\sqlite\sqlite-amalgamation-3070603\sqlite3.c 32390 > = > > Can you tell me what to do to solve that issue? > > Best regards, > -- > hmas > -- View this message in context: http://old.nabble.com/Error-%28LOCKFILE_EXCLUSIVE_LOCK%29-at-building-sqlite-project-for-pocket-pc-tp31750353p31750888.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create DB file and then Create Table - Table FAILS.
Check to see if escaping combined with double quotes works for you: SQLiteConnection conn; conn.ConnectionString = "Data Source=D:\Users\Don\Downloads\CashBoxPrefs.dat"; conn.Open(); SQLiteCommand sqlCmd(%conn); sqlCmd.CommandText = "CREATE TABLE IF NOT EXISTS Prefs (\"SyncEnabled\" bool , \"SyncWatchPort\" int , \"SyncLoginKey\" TEXT , \"SyncAccessKey\" TEXT , \"SyncLogFile\" TEXT , \"SyncLogClearInterval\" int , \"GenLatestBook\" TEXT , \"GenBookMRU_0\" TEXT , \"GenBookMRU_1\" TEXT , \"GenBookMRU_2\" TEXT , \"GenBookMRU_3\" TEXT , \"GenBookMRU_4\" TEXT);"; sqlite3 Cashboxprefs.dat sqlite> CREATE TABLE IF NOT EXISTS Prefs ("SyncEnabled" bool , "SyncWatchPort" int , "SyncLoginKey" TEXT , "SyncAccessKey" TEXT , "SyncLogFile" TEXT , "SyncLogClearInterval" int , "GenLatestBook" TEXT , "GenBookMRU_0" TEXT , "GenBookMRU_1" TEXT , "GenBookMRU_2" TEXT , "GenBookMRU_3" TEXT , "GenBookMRU_4" TEXT); -- -- -- --ô¿ô-- K e V i N On Tue, May 31, 2011 at 11:18 PM, Don Ireland wrote: > I'm hoping someone can help me with this. Using Visual Studio C++, the > following code DOES create the DB file. But the table doesn't get created > and I'm stumped as to why it won't create the table. SQLiteConnection conn; > conn.ConnectionString = "Data > Source=D:\Users\Don\Downloads\CashBoxPrefs.dat"; conn.Open(); SQLiteCommand > sqlCmd(%conn); sqlCmd.CommandText = "CREATE TABLE IF NOT EXISTS Prefs > (SyncEnabled bool,SyncWatchPort int,SyncLoginKey TEXT,SyncAccessKey TEXT, > SyncLogFile TEXT, SyncLogClearInterval int,GenLatestBook TEXT, GenBookMRU_0 > TEXT,GenBookMRU_1 TEXT, GenBookMRU_2 TEXT, GenBookMRU_3 TEXT, GenBookMRU_4 > TEXT);"; But if I run following at the SQLite3.exe from the command line, it > DOES create the file and the table. sqlite3 Cashboxprefs.dat sqlite> CREATE > TABLE IF NOT EXISTS Prefs (SyncEnabled bool,SyncWatchPort int,SyncLoginKey > TEXT,SyncAccessKey TEXT, SyncLogFile TEXT, SyncLogClearInterval > int,GenLatestBook TEXT, GenBookMRU_0 TEXT,GenBookMRU_1 TEXT, GenBookMRU_2 > TEXT, GenBookMRU_3 TEXT, GenBookMRU_4 TEXT); > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create DB file and then Create Table - Table FAILS.
On Wed, Jun 1, 2011 at 5:18 AM, Don Ireland wrote: > GenBookMRU_3 TEXT, GenBookMRU_4 TEXT); > i might be very wrong here, but i SEEM to remember that having a semicolon on the end of a line sent to sqlite3_exec() (or similar) doesn't work. i might, however, be mis-remembering from a session with the MySQL or ocilib (Oracle) APIs. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create DB file and then Create Table - Table FAILS.
On Wed, Jun 1, 2011 at 12:19 PM, Stephan Beal wrote: > On Wed, Jun 1, 2011 at 5:18 AM, Don Ireland wrote: > > > GenBookMRU_3 TEXT, GenBookMRU_4 TEXT); > > > > i might be very wrong here, but i SEEM to remember that having a semicolon > on the end of a line sent to sqlite3_exec() (or similar) doesn't work. i > might, however, be mis-remembering from a session with the MySQL or ocilib > (Oracle) APIs. > I think you are probably remembering a MySQL or Oracle episode. SQLite is very tolerant of semicolons in sqlite3_exec(). You can put multiple semicolons before and/or after the SQL statement and it should still work. > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_bind_parameter_name
On Wed, Jun 1, 2011 at 12:32 AM, Roger Binns wrote: > It does seem to me that this specific example is "wrong". Selects return > zero or more rows yet the equality wants exactly one value. I would expect > an error rather an implied "LIMIT 1" should there be anything other than > exactly one row. In some cases you'd only know by executing that subquery > how many rows it returns, rather than at prepare time. This is my general feeling as well, but I'm ignoring this since I'm okay with erroneous queries generating ambiguous results. >>> So, in summary, the "problem" has been with us for 6 years and nobody has >>> cared. And "fixing" it reduces the battery life on your cellphone by some >>> small amount. Are you *sure* this is something that needs to change? > > I think it is important to correct, especially as there is no workaround. The fact that there is no straight-forward work-around is the most problematic part. One possible work-around would be to build a tokenizer that re-extracts all the SQLite parameters so their values can be verified. Another possibility is to completely ignore missing named parameters, leading to the inability to detect errors for the user. Understanding the query would be more of a leap, so I don't think it's a reasonable solution to attempt to remove the "extra" parameter. >From these, the "best" solution seems to be re-tokenizing the sql in the provider. Is there another work-around? It seems that implementing this at the database level would be the most efficient approach. Obviously re-tokenizing all the SQL would be expensive. Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_bind_parameter_name
On Wed, Jun 1, 2011 at 6:24 PM, Patrick Earl wrote: > From these, the "best" solution seems to be re-tokenizing the sql in > the provider. Is there another work-around? It seems that > implementing this at the database level would be the most efficient > approach. Obviously re-tokenizing all the SQL would be expensive. Actually... if you just want to tokenize the SQL for the parameters, as opposed to checking the validity of the SQL itself, it is not all that difficult to do. i recently did just that to add named parameter support to the MySQL back-end of a db access abstraction API. MySQLv5 doesn't support named parameters natively, so i wrote a relatively simple tokenizer which can fish them out and another routine which converts named parameters to question marks so that we can feed the data to mysql (but can also remember enough info to map the named param positions to qmark positions): http://fossil.wanderinghorse.net/repos/cpdo/index.cgi/artifact/b5ba5aa115c73b63179456d1fed7846a11878c05 Search that file for: cpdo_find_next_named_param cpdo_named_params_to_qmarks The code for those functions is public domain and should be generic enough to extract for your own use (almost without modification - i think only the return code symbols would need to be replaced). The docs are in the header file: http://fossil.wanderinghorse.net/repos/cpdo/index.cgi/artifact/6749b977687afa081f6b5b3e7fc3b19cd62bb70a That code has worked for me very well so far, and i haven't yet had any mis-parsing except on code which was itself not legal SQL (i.e. it doesn't seem to create any new problems where there isn't already a problem). -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Support for .Net CE 3.5
I am programming for Windows Mobile 6.5.3 and would like to use SQLite. I see this message on the "Features" page: Supports the Full and Compact .NET Framework, and native C/C++ development. 100% binary compatible with the original sqlite3.dll. Compact framework not currently not included. We hope to have this feature included again soon. Can anyone tell me the latest version of System.Data.SQLite that supports .Net CE 3.5? Any other advice would be welcome too! Thanks, Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syncing databases on different servers
See undo extension: http://sqlite.mobigroup.ru/artifact/265e408b4352d66cfc79a9990cb2c22fb390d3b6 http://sqlite.mobigroup.ru/artifact/2250bbbc83f80eff73ce003ab7a30293c688ae9b And history extension: http://sqlite.mobigroup.ru/artifact/0b8eaab9564575fcf37f459bb85c501f65089b31 http://sqlite.mobigroup.ru/artifact/315fe02f9d84a4b40270d736e6e996599e05e010 http://sqlite.mobigroup.ru/artifact/4bc946d184aac14660d13531f67e516131ab7175 And versioning extension: http://sqlite.mobigroup.ru/artifact/7c6ef278210c6a06e9efd4b197649ff6084cea3c http://sqlite.mobigroup.ru/artifact/db7f9954fbd9bb825f338f4717ec9a89621da08e 2011/6/1 Gary_Gabriel : > Hi Ian, > >> I do not need instant syncing of SQLite databases, however I do have >> a table stored on server X which I would like to gradually send >> changes of to various other servers connected by TCP. >> >> In the first instance, I'd be quite happy to send rows with ids which >> do not exist in the other servers' tables, and not worry about >> changes to rows > > Here are two methods that you may consider, they are suitable to send > new rows or sync entry value changes. > > 1) The first is a standard logging method. To make an example of a schema: > - There are three tables: SearchIndex, ThreadSession and > ThreadIndex. Each table has three indexing columns or fields. Indexing > in this case means if the entry value changes, then the new information > in this row should be synced with the server. There are other columns > but their content is further desciption relative to the indexing columns > in the table. > - INSERT, UPDATE and DELETE triggers monitor the 3 indexing columns > in each table using the LIKE operator. A change in the entry value of > the indexing fields fires the trigger which inserts a record in a > logging table with the table name and Integer Primary Key. Joining the > data table with the log table generates the row containing the current > values. > - Use the generated row to sync the dbs. > - This method has been heavily exercised and tested over consider time > and proves to be reliable. Using the LIKE operator in this scenario has > not been detrimental to performance and accurate. > > 2) Method 2 uses temporary tables to hold the queried values in a user > session. The same tables as in 1) are used in this example. One > temporary table holds the query result rows for each table. So for the > tables SearchIndex, ThreadSession and ThreadIndex there are also > SearchIndex_temp, ThreadSession_temp and ThreadIndex_temp. In addition > one column flags changes based on the same criteria as 1). If the > contents of one of the three indexing columns or fields in the temp > tables changes, then the temp row is flagged. > - In the three tables DELETE the rows that have not changed and are not > flagged and what remains can be used to sync the changes. > > > - Gary Gabriel > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is so slow on dropping tables?
On Tue, May 31, 2011 at 16:44:13 -0500, Nico Williams wrote: > On Tue, May 31, 2011 at 4:22 PM, Simon Slavin wrote: > > Split the DROP into two stages: > > > > DELETE FROM myTable; > > DROP TABLE myTable; > > > > Which one takes all the time ? If it's the second one, then perhaps just > > delete all the records. Filling the table back up again with new rows > > obviously already takes considerable time so adding extra time may not be > > as inconvenient. The first one alone takes more time than the drop alone did before (already tried some time ago). > That's gonna have the same problem: in order to find out what pages to > add to the free list the engine will have to traverse the interior > nodes of the table's b-tree. > > You could rename the table and then delete from ... order by rowid asc > limit 1000, to delete 1000 rows at a time. Add in incremental > autovacuum and that might do the trick. The autovacuum would just add more work reshuffling the data around. And it's not that the pages are going to be unused long. > You could also re-create the DB in a new file then use the filesystem > to delete the old file -- the filesystem will have to do the same work > to free filesystem blocks -more or less-, but it will do it in the > asynchronously, in the background. I am deleting one table, but there are many, many others in the file that I need. Though I already thought about having each table in it's own file and attach the ones I need (the number of attached databases is limited, but I don't think single statement ever uses more than 6 or 7 tables, so it could be doable. Except it would be a lot of work to do it. > Personally I recommend Roger's VIEW approach to schema changes to > minimize data re-write overhead. Well, besides there being many tables that are written from code (though the data still come from the database, some complex transformations are involved) the main reason is, that when table X changes, I need to rebuild that table and the tables that depend on it, but not the tables it depends on. If it depended on views instead, I would have to reread them. And each join involved does make the query a bit slower (especially when joining huge tables like each query would if I used views) it would quickly become unbearable. -- Jan 'Bulb' Hudec ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create DB file and then Create Table - Table FAILS.
On Wed, Jun 01, 2011 at 12:21:15 -0400, Richard Hipp wrote: > On Wed, Jun 1, 2011 at 12:19 PM, Stephan Beal wrote: > > > On Wed, Jun 1, 2011 at 5:18 AM, Don Ireland wrote: > > > > > GenBookMRU_3 TEXT, GenBookMRU_4 TEXT); > > > > > > > i might be very wrong here, but i SEEM to remember that having a semicolon > > on the end of a line sent to sqlite3_exec() (or similar) doesn't work. i > > might, however, be mis-remembering from a session with the MySQL or ocilib > > (Oracle) APIs. > > > > I think you are probably remembering a MySQL or Oracle episode. SQLite is > very tolerant of semicolons in sqlite3_exec(). You can put multiple > semicolons before and/or after the SQL statement and it should still work. More than that. sqlite3_exec will happily accept *multiple* statements separated with semicolons and execute *all* of them. In contrast to sqlite3_prepare(_v2), which will prepare only the first one (but still accept the string). -- Jan 'Bulb' Hudec ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big difference in performance between Python and gcc
On Wed, Jun 01, 2011 at 09:25:04 +0200, Alessandro Marzocchi wrote: > However I was surprised as the performance got a lot > worse, with execution times being more than 3 times more. I tried > everything I could think of and also peeked at python module's source > but i couldn't find any way to get C program performance to match > python's one. Are you linking against existing sqlite library or compiling it yourself?i If the later, make sure you enabled all optimizations and disabled assertions in sqlite. Your flags when compiling sqlite source should include -O3 (most optimizations) and -DNDEBUG (to turn off assertions). These tend to make huge effect on sqlite performance. -- Jan 'Bulb' Hudec ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRANSACTIONs
On Wed, Jun 01, 2011 at 10:17:02 -0400, Pavel Ivanov wrote: > > From within a BEGIN TRANSACTION and END TRANSACTION block, should I not > > update the same row in a table more than once? > > You can update it as many times as you need. > > > What are the exact limitations on what I can do during a Transaction? > > Do not update a table if there is some select statement currently Actually insert, update and delete are OK. Drop and alter table are a problem. > active and using it. Active means you made at least one call to > sqlite3_step and last call to sqlite3_step returned SQLITE_ROW. -- Jan 'Bulb' Hudec ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error (LOCKFILE_EXCLUSIVE_LOCK) at building sqlite project for pocket pc
On Wed, Jun 01, 2011 at 09:13:30 -0700, hmas wrote: > Here is what I've read: > = > FYI, Compiling for Pocket PC 2003 stopped to work since version 3.7.1 > and 3.7.2. So using 3.6.23.1 for now until it gets fixed. > = > (http://comments.gmane.org/gmane.comp.db.sqlite.general/59021) > > My SQlite version is 3.7.6.3. > > An answer was: > = > There have been several posts on this issue just this week, and suggested > workarounds. > = > but I couldn't find what workarounds the author was talking about. The workaround is to disable the write-ahead log by defining SQLITE_OMIT_WAL Hm, wouldn't adding #ifdef _WIN32_WCE #define SQLITE_OMIT_WAL #endif somewhere be in order? It's not like it was possible to get WAL wokring on WinCE given limitations of it's file locking. -- Jan 'Bulb' Hudec ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRANSACTIONs
On 1 Jun 2011, at 7:12pm, Jan Hudec wrote: > On Wed, Jun 01, 2011 at 10:17:02 -0400, Pavel Ivanov wrote: >>> From within a BEGIN TRANSACTION and END TRANSACTION block, should I not >>> update the same row in a table more than once? >> >> You can update it as many times as you need. >> >>> What are the exact limitations on what I can do during a Transaction? >> >> Do not update a table if there is some select statement currently > > Actually insert, update and delete are OK. Drop and alter table are > a problem. Pavel is right. He left out some details to make things simple. Suppose you do a SELECT ... WHERE ... that returns ten rows. You retrieve three rows, then make a change that would mean you should have retrieved eleven rows, not ten. You can't predict what SQLite will do without knowing the internal workings of SQLite, right ? So don't do that. The same is true even if the only thing you change is values to be returned. Does SQLite copy the all values when you execute the SELECT, or row-by-row as you step through the results ? Again, you don't know unless you know the internal workings of SQLite. So don't do that. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is so slow on dropping tables?
On Jun 1, 2011 1:46 PM, "Jan Hudec" wrote: > On Tue, May 31, 2011 at 16:44:13 -0500, Nico Williams wrote: > > On Tue, May 31, 2011 at 4:22 PM, Simon Slavin wrote: > > > Split the DROP into two stages: > > > > > > DELETE FROM myTable; > > > DROP TABLE myTable; > > > > > > Which one takes all the time ? If it's the second one, then perhaps just delete all the records. Filling the table back up again with new rows obviously already takes considerable time so adding extra time may not be as inconvenient. > > The first one alone takes more time than the drop alone did before (already > tried some time ago). Delete might require visiting all the leaf nodes as well (even when doing a delete with no WHERE clause). > > That's gonna have the same problem: in order to find out what pages to > > add to the free list the engine will have to traverse the interior > > nodes of the table's b-tree. > > > > You could rename the table and then delete from ... order by rowid asc > > limit 1000, to delete 1000 rows at a time. Add in incremental > > autovacuum and that might do the trick. > > The autovacuum would just add more work reshuffling the data around. And > it's not that the pages are going to be unused long. But you've not tried it, right? You don't need the autovacuum unless you're concerned about storage consumption anyways. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRANSACTIONs
On 6/1/2011 1:23 PM, Simon Slavin wrote: > On 1 Jun 2011, at 7:12pm, Jan Hudec wrote: > >> On Wed, Jun 01, 2011 at 10:17:02 -0400, Pavel Ivanov wrote: From within a BEGIN TRANSACTION and END TRANSACTION block, should I not update the same row in a table more than once? >>> You can update it as many times as you need. >>> What are the exact limitations on what I can do during a Transaction? >>> Do not update a table if there is some select statement currently >> Actually insert, update and delete are OK. Drop and alter table are >> a problem. > Pavel is right. He left out some details to make things simple. > > Suppose you do a SELECT ... WHERE ... that returns ten rows. You retrieve > three rows, then make a change that would mean you should have retrieved > eleven rows, not ten. You can't predict what SQLite will do without knowing > the internal workings of SQLite, right ? So don't do that. The same is true > even if the only thing you change is values to be returned. Does SQLite copy > the all values when you execute the SELECT, or row-by-row as you step through > the results ? Again, you don't know unless you know the internal workings of > SQLite. So don't do that. > > Simon. > ___ Actually, you do know what SQLite does without knowing the internals. It claims to be serializable and ACID (http://www.sqlite.org/transactional.html), therefore it's fine. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRANSACTIONs
> Actually, you do know what SQLite does without knowing the internals. It > claims to be serializable and ACID > (http://www.sqlite.org/transactional.html), therefore it's fine. "Serializable" there means that once transaction is started statements won't see any data committed in other transactions. But it doesn't impose any restrictions on how statements should behave within the same transaction. And in SQLite it's pretty unpredictable and so even SQLite's documentation prohibits updating some table while select statement on it is active (I can find a link on sqlite.org for you when I have some spare time). Pavel On Wed, Jun 1, 2011 at 2:41 PM, Robert Myers wrote: > On 6/1/2011 1:23 PM, Simon Slavin wrote: >> On 1 Jun 2011, at 7:12pm, Jan Hudec wrote: >> >>> On Wed, Jun 01, 2011 at 10:17:02 -0400, Pavel Ivanov wrote: > From within a BEGIN TRANSACTION and END TRANSACTION block, should I not > update the same row in a table more than once? You can update it as many times as you need. > What are the exact limitations on what I can do during a Transaction? Do not update a table if there is some select statement currently >>> Actually insert, update and delete are OK. Drop and alter table are >>> a problem. >> Pavel is right. He left out some details to make things simple. >> >> Suppose you do a SELECT ... WHERE ... that returns ten rows. You retrieve >> three rows, then make a change that would mean you should have retrieved >> eleven rows, not ten. You can't predict what SQLite will do without knowing >> the internal workings of SQLite, right ? So don't do that. The same is >> true even if the only thing you change is values to be returned. Does >> SQLite copy the all values when you execute the SELECT, or row-by-row as you >> step through the results ? Again, you don't know unless you know the >> internal workings of SQLite. So don't do that. >> >> Simon. >> ___ > > Actually, you do know what SQLite does without knowing the internals. It > claims to be serializable and ACID > (http://www.sqlite.org/transactional.html), therefore it's fine. > ___ > 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] TRANSACTIONs
On 6/1/2011 1:47 PM, Pavel Ivanov wrote: >> Actually, you do know what SQLite does without knowing the internals. It >> claims to be serializable and ACID >> (http://www.sqlite.org/transactional.html), therefore it's fine. > "Serializable" there means that once transaction is started statements > won't see any data committed in other transactions. But it doesn't > impose any restrictions on how statements should behave within the > same transaction. And in SQLite it's pretty unpredictable and so even > SQLite's documentation prohibits updating some table while select > statement on it is active (I can find a link on sqlite.org for you > when I have some spare time). What about the I of ACID? The select should have an implicit transaction around it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRANSACTIONs
> What about the I of ACID? The select should have an implicit transaction > around it. No, it shouldn't. It can be a part of some other transaction. I in ACID means Isolation of transactions, not Isolation of select statements. Otherwise there would be no way for select statement to read uncommitted data in the same transaction. Pavel On Wed, Jun 1, 2011 at 2:57 PM, Robert Myers wrote: > On 6/1/2011 1:47 PM, Pavel Ivanov wrote: >>> Actually, you do know what SQLite does without knowing the internals. It >>> claims to be serializable and ACID >>> (http://www.sqlite.org/transactional.html), therefore it's fine. >> "Serializable" there means that once transaction is started statements >> won't see any data committed in other transactions. But it doesn't >> impose any restrictions on how statements should behave within the >> same transaction. And in SQLite it's pretty unpredictable and so even >> SQLite's documentation prohibits updating some table while select >> statement on it is active (I can find a link on sqlite.org for you >> when I have some spare time). > What about the I of ACID? The select should have an implicit transaction > around it. > > ___ > 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] Error (LOCKFILE_EXCLUSIVE_LOCK) at building sqlite project for pocket pc
Thank you for your answer. Actually, I managed to make it work by selecting "Windows Mobile 5.0 Pocket PC SDK" instead of "Pocket PC 2003" for the SDK in the procedure described in my first post. All the best, -- hmas -- View this message in context: http://old.nabble.com/Error-%28LOCKFILE_EXCLUSIVE_LOCK%29-at-building-sqlite-project-for-pocket-pc-tp31750353p31752308.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRANSACTIONs
On 1 Jun 2011, at 7:57pm, Robert Myers wrote: > What about the I of ACID? The select should have an implicit transaction > around it. That would violate the SQL standard which says you can SELECT data which you haven't committed yet. Actually the bit of ACID you want for SELECT is really the 'A': Atomic. A SELECT should really be a single operation which gathers all the data and stores it somewhere. The problems with this are when the SELECT gathers a lot of data: first you get one command taking a long time, which can make your GUI jerky, and second you have to store the result set, so you need as much spare memory as the entire result set takes up. Which can be gigabytes. So the solution is to implement SELECT atomically in your programming. Don't do any other SQL commands until you've done the last _step(). Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Big difference in performance between Python and gcc
Alessandro Marzocchi wrote: > Hello, > I made some preliminary tests for an application storing big > chunks of data in a sqlite database. I did firsts tests with python > and they gave me quite impressive results. I then tried to make the > same test using C. I expected to get execution times to be the same of > those of python. However I was surprised as the performance got a lot > worse, with execution times being more than 3 times more. ...snip... > if(SQLITE_OK!=(ret=sqlite3_prepare_v2(db, > "INSERT INTO helloworld VALUES (?,?,?)", > -1, > &db_stm, > NULL > ))) Since you're going to always insert 1 in the first column, why not use: INSERT INTO helloworld VALUES (1,?,?) > { > fprintf(stderr,"sqlite error in prepare() [%d]",ret); > return -1; > }; > > int i; > char data[1024*8+1]; > for(i=0;i<1024*8;i++)data[i]='0'; > data[1024*8]='\0'; Since the data column also does not change, you could bind it only once before entering the for loop (rather than binding it at each loop iteration) > for(i=0;i<10;i++) > { > if(!(i%1))printf("%d\n",i); > > if(SQLITE_OK!=(ret=sqlite3_bind_int(db_stm, 1, 1))) > { > fprintf(stderr,"sqlite error in bind()"); > return -1; > } Above bind is useless if you used: INSERT INTO helloworld VALUES (1,?,?) > if(SQLITE_OK!=(ret=sqlite3_bind_int(db_stm, 2, i))) > { > fprintf(stderr,"sqlite error in bind()"); > return -1; > } > //if(SQLITE_OK!=(ret=sqlite3_bind_blob(db_stm, 3, data,-1, > SQLITE_STATIC/*SQLITE_TRANSIENT*/))) > if(SQLITE_OK!=(ret=sqlite3_bind_blob(db_stm, 3, data,8192, > SQLITE_STATIC/*SQLITE_TRANSIENT*/))) > { > fprintf(stderr,"sqlite error in bind_blob() [%d] ok=%d",ret,SQLITE_OK); > return -1; > } above bind to blob can be moved outside the loop. > ret=sqlite3_step(db_stm); > if(ret!=SQLITE_DONE) > { > fprintf(stderr,"sqlite error in sqlite3_step() [%d]",ret); > return -1; > } > if(SQLITE_OK!=(ret=sqlite3_reset(db_stm))) > { > fprintf(stderr,"sqlite error in sqlite3_reset() [%d]",ret); > return -1; > } > sqlite3_clear_bindings(db_stm); Calling sqlite3_clear_binding(...) at each iteration is not needed in your example. In fact, you should not call it at all if you decide to move the bind to the blob outside the loop. That should make it faster. -- Dominique ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trigger help needed
Hopefully someone can help me with this I have a table with IP addresses and timestamps - I want to update the table when the new timestamp is later than the old one $ sqlite3 test.db SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .schema CREATE TABLE ip_timestamps (ip text unique, timestamp date); CREATE UNIQUE INDEX ip_index on ip_timestamps (ip ASC); CREATE TRIGGER ts_update after update on ip_timestamps when NEW.timestamp < OLD.timestamp BEGIN update ip_timestamps set timestamp = OLD.timestamp; END; I'm adding/updating records with statements like: INSERT OR REPLACE into ip_timestamps VALUES ( "182.71.33.222" , 1306932777 ); The goal is to keep the latest timestamp in the database (the older timestamp could occur later in the input than the newer timestamp), but the trigger doesn't seem to be working - I assume the trigger is flawed. Any suggestions? Thanks in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger help needed
NEW.timestamp < OLD.timestamp (less than)? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jim Mellander Sent: Wednesday, June 01, 2011 3:31 PM To: General Discussion of SQLite Database Subject: [sqlite] Trigger help needed Hopefully someone can help me with this I have a table with IP addresses and timestamps - I want to update the table when the new timestamp is later than the old one $ sqlite3 test.db SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .schema CREATE TABLE ip_timestamps (ip text unique, timestamp date); CREATE UNIQUE INDEX ip_index on ip_timestamps (ip ASC); CREATE TRIGGER ts_update after update on ip_timestamps when NEW.timestamp < OLD.timestamp BEGIN update ip_timestamps set timestamp = OLD.timestamp; END; I'm adding/updating records with statements like: INSERT OR REPLACE into ip_timestamps VALUES ( "182.71.33.222" , 1306932777 ); The goal is to keep the latest timestamp in the database (the older timestamp could occur later in the input than the newer timestamp), but the trigger doesn't seem to be working - I assume the trigger is flawed. Any suggestions? Thanks in advance. ___ 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] Big difference in performance between Python and gcc
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/01/2011 12:25 PM, Dominique Pellé wrote: > [Various optimisations] While those are all valid, they don't address the underlying issue which is C code taking five times longer than Python code for the same SQLite operations. In addition that same "redundant" code is executed behind the scenes in Python so it is fair for these comparisons/benchmark. Other things having been ruled out, it looks like Jan's suggestion of compilation options and code is likely the cause. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk3ml7MACgkQmOOfHg372QRuoQCfcKEV8YauTG2BB2a3ux1XqSVi 9HIAoIZtc/qP4p+kJxrv1Av+DlFg7T2n =BHQY -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger help needed
> CREATE TRIGGER ts_update after update on ip_timestamps when This means that your trigger fires after each UPDATE statement. But > I'm adding/updating records with statements like: > > INSERT OR REPLACE into ip_timestamps VALUES ( "182.71.33.222" , 1306932777 ); you do INSERT statements, so your trigger is never executed. Don't be confused: REPLACE means that if needed the old row is deleted and new is inserted. Row is never updated and UPDATE triggers are never executed (I'm not sure about DELETE and INSERT triggers though). Pavel On Wed, Jun 1, 2011 at 3:31 PM, Jim Mellander wrote: > Hopefully someone can help me with this > > I have a table with IP addresses and timestamps - I want to update the > table when the new timestamp is later than the old one > > > $ sqlite3 test.db > SQLite version 3.7.4 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> .schema > CREATE TABLE ip_timestamps (ip text unique, timestamp date); > CREATE UNIQUE INDEX ip_index on ip_timestamps (ip ASC); > CREATE TRIGGER ts_update after update on ip_timestamps when > NEW.timestamp < OLD.timestamp BEGIN update ip_timestamps set timestamp > = OLD.timestamp; END; > > > I'm adding/updating records with statements like: > > INSERT OR REPLACE into ip_timestamps VALUES ( "182.71.33.222" , 1306932777 ); > > The goal is to keep the latest timestamp in the database (the older > timestamp could occur later in the input than the newer timestamp), > but the trigger doesn't seem to be working - I assume the trigger is > flawed. Any suggestions? > > > Thanks in advance. > ___ > 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] Trigger help needed
On 6/1/2011 3:31 PM, Jim Mellander wrote: > CREATE TABLE ip_timestamps (ip text unique, timestamp date); > CREATE UNIQUE INDEX ip_index on ip_timestamps (ip ASC); > CREATE TRIGGER ts_update after update on ip_timestamps when > NEW.timestamp< OLD.timestamp BEGIN update ip_timestamps set timestamp > = OLD.timestamp; END; This updates all rows, not just the one on which the trigger was fired. Is this intentional? You might want something lile update ip_timestamps set timestamp = OLD.timestamp where rowid = NEW.rowid; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger help needed
I appreciate the help - I'm new at using triggers. What I want to accomplish is: 1. insert if the ip isn't already in the database 2. update the timestamp if the new timestamp after the current timestamp in the database what would be the proper trigger to accomplish this task? Thanks in advance On Wed, Jun 1, 2011 at 1:22 PM, Igor Tandetnik wrote: > On 6/1/2011 3:31 PM, Jim Mellander wrote: >> CREATE TABLE ip_timestamps (ip text unique, timestamp date); >> CREATE UNIQUE INDEX ip_index on ip_timestamps (ip ASC); >> CREATE TRIGGER ts_update after update on ip_timestamps when >> NEW.timestamp< OLD.timestamp BEGIN update ip_timestamps set timestamp >> = OLD.timestamp; END; > > This updates all rows, not just the one on which the trigger was fired. > Is this intentional? You might want something lile > > update ip_timestamps set timestamp = OLD.timestamp where rowid = NEW.rowid; > > -- > 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] Trigger help needed
On 6/1/2011 5:23 PM, Jim Mellander wrote: > I appreciate the help - I'm new at using triggers. What I want to > accomplish is: > > 1. insert if the ip isn't already in the database > 2. update the timestamp if the new timestamp after the current > timestamp in the database Something like this, perhaps: insert or replace into ip_timestamps values(:ip, max(:timestamp, coalesce((select timestamp from ip_timestamps where ip = :ip), 0)) ); No trigger needed. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger help needed
Thanks - and with a little reading of the manpages, understandable too Appreciate it. On Wed, Jun 1, 2011 at 2:34 PM, Igor Tandetnik wrote: > On 6/1/2011 5:23 PM, Jim Mellander wrote: >> I appreciate the help - I'm new at using triggers. What I want to >> accomplish is: >> >> 1. insert if the ip isn't already in the database >> 2. update the timestamp if the new timestamp after the current >> timestamp in the database > > Something like this, perhaps: > > insert or replace into ip_timestamps values(:ip, > max(:timestamp, coalesce((select timestamp from ip_timestamps where > ip = :ip), 0)) > ); > > No trigger needed. > -- > 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] Create DB file and then Create Table - Table FAILS.
I'm hoping someone can help me with this. Using Visual Studio C++, the following code DOES create the DB file. But the table doesn't get created and I'm stumped as to why it won't create the table. SQLiteConnection conn; conn.ConnectionString = "Data Source=D:\Users\Don\Downloads\CashBoxPrefs.dat"; conn.Open(); SQLiteCommand sqlCmd(%conn); sqlCmd.CommandText = "CREATE TABLE IF NOT EXISTS Prefs (SyncEnabled bool,SyncWatchPort int,SyncLoginKey TEXT,SyncAccessKey TEXT, SyncLogFile TEXT, SyncLogClearInterval int,GenLatestBook TEXT, GenBookMRU_0 TEXT,GenBookMRU_1 TEXT, GenBookMRU_2 TEXT, GenBookMRU_3 TEXT, GenBookMRU_4 TEXT);"; But if I run following at the SQLite3.exe from the command line, it DOES create the file and the table. sqlite3 Cashboxprefs.dat sqlite> CREATE TABLE IF NOT EXISTS Prefs (SyncEnabled bool,SyncWatchPort int,SyncLoginKey TEXT,SyncAccessKey TEXT, SyncLogFile TEXT, SyncLogClearInterval int,GenLatestBook TEXT, GenBookMRU_0 TEXT,GenBookMRU_1 TEXT, GenBookMRU_2 TEXT, GenBookMRU_3 TEXT, GenBookMRU_4 TEXT); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create DB file and then Create Table - Table FAILS.
On 2 Jun 2011, at 1:06am, Don Ireland wrote: > Using Visual Studio C++, the following code DOES create the DB file. > But the table doesn't get created and I'm stumped as to why it won't > create the table. Aren't you meant to do something like SQLiteCommand SQLiteCommand = new SQLiteCommand(); ? I can't find any documentation for using that library with C++. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_bind_parameter_name
So, to move forward on this bug a decision needs to be made at what level it will be handled. 1. At the SQLite level. 2. At the provider level (ex. System.Data.SQLite, Python providers, etc.) 3. At the user level (ex. NHibernate, Entity Framework, User Applications, etc.) Doing it in #3 would involve figuring out which parameters would be removed and not including those, a very difficult option. Doing it in #2 would involve adding something that does manual parameter parsing and validation (such as the parameter tokenizer). Doing it in #1 would involve things that I don't understand, though it would simultaneously correct the problems with all providers mentioned on this thread. I'm not clear on who is maintaining System.Data.SQLite, but I would certainly be happy to see some progress towards the resolution of this issue. Since I don't believe #3 is a feasible option (nor even the correct place to abstract away the SQLite oddity), the solution should be #1 or #2. For #2 there is a performance hit on every query performed using such a provider (.NET, Python, etc.). The queries need to be parsed by the provider to determine validity. If the SQLite syntax changes, these providers need to be updated. For #1, it sounds like there is a minor performance penalty, but perhaps it can be implemented in a way where the effects are minimal. #1 has the advantage that it may be possible to avoid any sort of significant performance hit unless an "optimized" parameter is encountered. #2 doesn't have this luxury since it doesn't know when a parameter might be optimized out. What would the SQLite team suggest to help progress the fix for this? If it's at the System.Data.SQLite level, I would be willing to help contribute a fix. If that were the case, I would hope that the SQLite syntax could be parsed by a regex for performance reasons. Patrick Earl On Wed, Jun 1, 2011 at 10:36 AM, Stephan Beal wrote: > On Wed, Jun 1, 2011 at 6:24 PM, Patrick Earl wrote: > >> From these, the "best" solution seems to be re-tokenizing the sql in >> the provider. Is there another work-around? It seems that >> implementing this at the database level would be the most efficient >> approach. Obviously re-tokenizing all the SQL would be expensive. > > > Actually... if you just want to tokenize the SQL for the parameters, as > opposed to checking the validity of the SQL itself, it is not all that > difficult to do. i recently did just that to add named parameter support to > the MySQL back-end of a db access abstraction API. MySQLv5 doesn't support > named parameters natively, so i wrote a relatively simple tokenizer which > can fish them out and another routine which converts named parameters to > question marks so that we can feed the data to mysql (but can also remember > enough info to map the named param positions to qmark positions): > > http://fossil.wanderinghorse.net/repos/cpdo/index.cgi/artifact/b5ba5aa115c73b63179456d1fed7846a11878c05 > > Search that file for: > > cpdo_find_next_named_param > cpdo_named_params_to_qmarks > > The code for those functions is public domain and should be generic enough > to extract for your own use (almost without modification - i think only the > return code symbols would need to be replaced). The docs are in the header > file: > > http://fossil.wanderinghorse.net/repos/cpdo/index.cgi/artifact/6749b977687afa081f6b5b3e7fc3b19cd62bb70a > > That code has worked for me very well so far, and i haven't yet had any > mis-parsing except on code which was itself not legal SQL (i.e. it doesn't > seem to create any new problems where there isn't already a problem). > > -- > - stephan beal > http://wanderinghorse.net/home/stephan/ > ___ > 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] Bug in sqlite3_bind_parameter_name
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/01/2011 06:42 PM, Patrick Earl wrote: > So, to move forward on this bug a decision needs to be made at what > level it will be handled. > > 1. At the SQLite level. That is exactly what the team did. See the timeline: http://www.sqlite.org/src/timeline Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk3m7HIACgkQmOOfHg372QQqAQCgg1fxUQzy4L1RPDvJe6puUpMm ouAAn3DSeFWchNXyQVnZJGfB+MENKjmF =wYCW -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in sqlite3_bind_parameter_name
That's awesome. Thanks so much. :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Two Requests for System.Data.SQLite
1. Please release a zipped version of the x86 and x64 binaries so we don't have to install the package, copy the files, then uninstall it. 2. Please release a version built against .NET 4 so the legacy runtime flag can be avoided. Thanks for considering these things. They would certainly make my life easier. Patrick Earl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users