Re: [sqlite] Strange foreign key constraint failed with DROP TABLE
On 08/20/2011 12:50 AM, Duquette, William H (318K) wrote: On 8/19/11 10:44 AM, Boris Kolpackovbo...@codesynthesis.com wrote: Hi William, Duquette, William H (318K)william.h.duque...@jpl.nasa.gov writes: On 8/19/11 10:18 AM, Boris Kolpackovbo...@codesynthesis.com wrote: BEGIN TRANSACTION; DROP TABLE employer; DROP TABLE employee; COMMIT; According to the sqlite docs, dropping a table when FK constraints are enabled does an implicit DELETE FROM first. so how can there still be FK constraint violations? Am I missing something? If so, then that would be the two of us. Though I think this is a bug in SQLite. I think it might be. It is. Now fixed in the trunk. http://www.sqlite.org/src/info/b1d3a2e531 Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Add/modify a table in the existing Sqlite database in client side.
Hi, I am using C# windows application(.Net2010) with sqlite, and i need to change the database table. Can you pls let me know, how to add or modify a new column/ table in the existing Sqlite database that is in my client desktop machine. I want the existing database to have its data, and only change the script in order to add a new table/column. Is there any way something like bat file - to execute and update the database file in client machine. or any other way to execute the scripts( like oracle updation of script) Waiting for your reply. Thanks - Madhan This email and any attached files (Message) may contain confidential and/or privileged information. It is intended solely for the addressee(s). If you receive this Message in error, inform the sender by reply email, delete the Message and destroy any printed copy. Any unauthorized use, distribution, or copying of this Message or any part thereof is prohibited. Emails are susceptible to alteration. Neither Technip nor any of its affiliates shall be liable for the Message if altered or falsified nor shall they be liable for any damage caused by any virus that might be transmitted with this Message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Add/modify a table in the existing Sqlite database in client side.
On 22 Aug 2011, at 11:17am, Madhankumar Rajaram wrote: I am using C# windows application(.Net2010) with sqlite, and i need to change the database table. Can you pls let me know, how to add or modify a new column/ table in the existing Sqlite database that is in my client desktop machine. I want the existing database to have its data, and only change the script in order to add a new table/column. Is there any way something like bat file - to execute and update the database file in client machine. or any other way to execute the scripts( like oracle updation of script) You can download the SQLite shell tool from http://www.sqlite.org/download.html I'd guess that you would want sqlite-shell-win32-x86-3070701.zip You can use this to open your database file and execute any SQL commands you want against it, including 'CREATE TABLE ...' and 'ALTER TABLE ...'. Instructions and examples for using the shell tool can be found here: http://www.sqlite.org/sqlite.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to Add/modify a table in the existing Sqlite database at client side
Hi, I am using C# windows application(.Net2010) with sqlite, and i need to change the database table. Can you pls let me know, how to add or modify a new column/ table in the existing Sqlite database that is in my client desktop machine. I want the existing database to have its data, and only change the script in order to add a new table/column. Is there any way something like bat file - to execute and update the database file in client machine. or any other way to execute the scripts( like oracle updation of script) Waiting for your reply. Thanks - Madhan This email and any attached files (Message) may contain confidential and/or privileged information. It is intended solely for the addressee(s). If you receive this Message in error, inform the sender by reply email, delete the Message and destroy any printed copy. Any unauthorized use, distribution, or copying of this Message or any part thereof is prohibited. Emails are susceptible to alteration. Neither Technip nor any of its affiliates shall be liable for the Message if altered or falsified nor shall they be liable for any damage caused by any virus that might be transmitted with this Message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Add/modify a table in the existing Sqlite database in client side.
I am using C# windows application(.Net2010) with sqlite, and i need to change the database table. Can you pls let me know, how to add or modify a new column/ table in the existing Sqlite database that is in my client desktop machine. I want the existing database to have its data, and only change the script in order to add a new table/column. Is there any way something like bat file - to execute and update the database file in client machine. or any other way to execute the scripts( like oracle updation of script) Give http://www.sqliteexpert.com/SQLite Expert a try. This third-party SQLite manager allows you to change your schema very easily as well as query/update your DB in all possible ways. If you're going to use SQLite professionnally, don't hesitate to buy the Pro version which offers more features that the freeware one. Whatever rate you're being paid, the tool will pay back in days, if not within hours. -- mailto:j...@q-e-d.orgj...@antichoc.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to Add/modify a table in the existing Sqlite database at client side
Take a look at Fluent Migrator project it supports modifying SQLite schema. http://lostechies.com/seanchambers/2011/04/02/fluentmigrator-getting-started/ -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Madhankumar Rajaram Sent: 22 August 2011 12:15 To: sqlite-users@sqlite.org Subject: [sqlite] how to Add/modify a table in the existing Sqlite database at client side Hi, I am using C# windows application(.Net2010) with sqlite, and i need to change the database table. Can you pls let me know, how to add or modify a new column/ table in the existing Sqlite database that is in my client desktop machine. I want the existing database to have its data, and only change the script in order to add a new table/column. Is there any way something like bat file - to execute and update the database file in client machine. or any other way to execute the scripts( like oracle updation of script) Waiting for your reply. Thanks - Madhan This email and any attached files (Message) may contain confidential and/or privileged information. It is intended solely for the addressee(s). If you receive this Message in error, inform the sender by reply email, delete the Message and destroy any printed copy. Any unauthorized use, distribution, or copying of this Message or any part thereof is prohibited. Emails are susceptible to alteration. Neither Technip nor any of its affiliates shall be liable for the Message if altered or falsified nor shall they be liable for any damage caused by any virus that might be transmitted with this Message. ___ 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 to Add/modify a table in the existing Sqlite database at client side
Hello, IF you need to do that just to setup your database once, then you can use any of free tools as suggested. Or you can simply run a SQLCommand through your code to drop and recreate a table using Standard SQL Create Table statement. Alter query will work to drop and add column as well. Sumit -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Madhankumar Rajaram Sent: 22 August 2011 16:45 To: sqlite-users@sqlite.org Subject: [sqlite] how to Add/modify a table in the existing Sqlite database at client side Hi, I am using C# windows application(.Net2010) with sqlite, and i need to change the database table. Can you pls let me know, how to add or modify a new column/ table in the existing Sqlite database that is in my client desktop machine. I want the existing database to have its data, and only change the script in order to add a new table/column. Is there any way something like bat file - to execute and update the database file in client machine. or any other way to execute the scripts( like oracle updation of script) Waiting for your reply. Thanks - Madhan This email and any attached files (Message) may contain confidential and/or privileged information. It is intended solely for the addressee(s). If you receive this Message in error, inform the sender by reply email, delete the Message and destroy any printed copy. Any unauthorized use, distribution, or copying of this Message or any part thereof is prohibited. Emails are susceptible to alteration. Neither Technip nor any of its affiliates shall be liable for the Message if altered or falsified nor shall they be liable for any damage caused by any virus that might be transmitted with this Message. ___ 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 to Add a Table in the existing Sqlite database at client machine
I, I am using C# windows application(.Net2010) with sqlite, Can you pls let me know, how to add or modify a new column/ table in the existing Sqlite database that is in my client desktop machine. I want the existing database to have its data(not disturbed), and only add a new table/column. Is there any way something like bat file - to execute and update the database file in client machine. or any other way to execute the scripts( like oracle updation of script) Waiting for your reply. Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] update required
2011/8/21 Richard Hipp d...@sqlite.org: But we've also gotten messages (including some irate late-night phone calls to my personal telephone) complaining of problems with English-language versions as well. That is a disgrace, and I hope that I speak for everyone on this list in condemning this sort of completely unacceptable behaviour. I have known projects in the past which have floundered because the lead programmer just said that they had had enough of rude/obscene/offensive emails/communications and that they were oprhaning the project. Just let me say that I hope you regard people who engage in this sort of idiocy as being unworthy of even your disdain and that you don't stop your marvellous contributions to the software world. I have Bugzilla working with SQLite - brillo! In the coming months I hope to evaluate Fossil. As the French would say (or rather write) Sincères salutations. Paul... D. Richard Hipp -- lineh...@tcd.ie Mob: 00 353 86 864 5772 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to Add a Table in the existing Sqlite database at client machine
Using Fluent Migrator project you write the following class... [Migration(201101011411)] public class Version_002 : FluentMigrator.Migration { public override void Up() { Create.Column(ColumnName).OnTable(TableName).AsInt32().Nullable(); } public override void Down() { Delete.Column(ColumnName).FromTable(TableName); } } -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Madhan Kumar Sent: 22 August 2011 13:27 To: sqlite-users@sqlite.org Subject: [sqlite] how to Add a Table in the existing Sqlite database at client machine I, I am using C# windows application(.Net2010) with sqlite, Can you pls let me know, how to add or modify a new column/ table in the existing Sqlite database that is in my client desktop machine. I want the existing database to have its data(not disturbed), and only add a new table/column. Is there any way something like bat file - to execute and update the database file in client machine. or any other way to execute the scripts( like oracle updation of script) Waiting for your reply. Thanks ___ 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] Last record in db
Hi, 1.What's the fastest way to retrieve the last record in the DB. 2. Sqlite3_step takes you to the next record. Is there an equivalent for navigating backwards ? Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
Oh...can I guess? select * from table where rowid=max(rowid); Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of sreekumar...@gmail.com [sreekumar...@gmail.com] Sent: Monday, August 22, 2011 7:51 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Last record in db Hi, 1.What's the fastest way to retrieve the last record in the DB. 2. Sqlite3_step takes you to the next record. Is there an equivalent for navigating backwards ? Sent from BlackBerry® on Airtel ___ 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] Last record in db
sreekumar...@gmail.com wrote: 1.What's the fastest way to retrieve the last record in the DB. a) Define last. b) From which table in the DB? 2. Sqlite3_step takes you to the next record. Is there an equivalent for navigating backwards ? Not really, but see http://www.sqlite.org/cvstrac/wiki/wiki?p=ScrollingCursor -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
Or if you are talking about some specific select statement. select * from table order by mystuff desc; Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of sreekumar...@gmail.com [sreekumar...@gmail.com] Sent: Monday, August 22, 2011 7:51 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Last record in db Hi, 1.What's the fastest way to retrieve the last record in the DB. 2. Sqlite3_step takes you to the next record. Is there an equivalent for navigating backwards ? Sent from BlackBerry® on Airtel ___ 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] Last record in db
Let's say there is a table in a db that holds a few thousands of records.. Records are inserted and deleted from the table. At any given point I should be able to retrieve the 'last' record.. 'Last' is probably the record which is stored at the node with max depth? --Original Message-- From: Igor Tandetnik Sender: sqlite-users-boun...@sqlite.org To: sqlite-users@sqlite.org ReplyTo: General Discussion of SQLite Database Subject: Re: [sqlite] Last record in db Sent: Aug 22, 2011 18:33 sreekumar...@gmail.com wrote: 1.What's the fastest way to retrieve the last record in the DB. a) Define last. b) From which table in the DB? 2. Sqlite3_step takes you to the next record. Is there an equivalent for navigating backwards ? Not really, but see http://www.sqlite.org/cvstrac/wiki/wiki?p=ScrollingCursor -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
sreekumar...@gmail.com wrote: Let's say there is a table in a db that holds a few thousands of records.. Records are inserted and deleted from the table. At any given point I should be able to retrieve the 'last' record.. Last by what ordering? 'Last' is probably the record which is stored at the node with max depth? What's a node or a depth in this context? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
Is 'last' valid only for 'ordered' set of records? --Original Message-- From: Igor Tandetnik Sender: sqlite-users-boun...@sqlite.org To: sqlite-users@sqlite.org ReplyTo: General Discussion of SQLite Database Subject: Re: [sqlite] Last record in db Sent: Aug 22, 2011 18:51 sreekumar...@gmail.com wrote: Let's say there is a table in a db that holds a few thousands of records.. Records are inserted and deleted from the table. At any given point I should be able to retrieve the 'last' record.. Last by what ordering? 'Last' is probably the record which is stored at the node with max depth? What's a node or a depth in this context? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
sreekumar...@gmail.com wrote: Is 'last' valid only for 'ordered' set of records? It would be more precise to say that an ordering induces GetLastRecord function, and vice versa. If you have a total ordering, then the last record is the one that compares greater than all others in this ordering. In the other direction, if you have GetLastRecord(set_of_records) function defined somehow, then you can pick the last record, remove it from the set, pick the last of the remaining, remove that one from the set, and so on. This process generates a total ordering of the set. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: Last record in db
No...if you use autoincrement you can guarantee that last will be the last record inserted. So select * from mytable where myid=max(myid) will work where myid is autoincrement. The normal rowid will work also as long as you don't delete the max(rowid) and you don't insert more than 9,223,372,036,854,775,807 rows. select * from mytable where rowid=max(rowid) http://www.sqlite.org/autoinc.html Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of sreekumar...@gmail.com [sreekumar...@gmail.com] Sent: Monday, August 22, 2011 8:28 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Last record in db Is 'last' valid only for 'ordered' set of records? --Original Message-- From: Igor Tandetnik Sender: sqlite-users-boun...@sqlite.org To: sqlite-users@sqlite.org ReplyTo: General Discussion of SQLite Database Subject: Re: [sqlite] Last record in db Sent: Aug 22, 2011 18:51 sreekumar...@gmail.com wrote: Let's say there is a table in a db that holds a few thousands of records.. Records are inserted and deleted from the table. At any given point I should be able to retrieve the 'last' record.. Last by what ordering? 'Last' is probably the record which is stored at the node with max depth? What's a node or a depth in this context? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Sent from BlackBerry® on Airtel ___ 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] Autoincrement failure
Hallo, Have you ever seen a SQLite3 DB file with autoincrement algoritm broken? Every new record seems to be added with rowid=1 overwriting existing info... Any idea about the causes of this issue and about extracting lost data (if present)? Many thanks Alessandro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
Black, Michael (IS) wrote: select * from table where rowid=max(rowid); $ sqlite3 m2d1.sql3 -- Loading resources from /home/sk/.sqliterc SQLite version 3.7.3 Enter .help for instructions Enter SQL statements terminated with a ; .tables t1 t2 t3 .schema t1 CREATE TABLE t1(id INT,data TEXT); select * from t1 ; id data -- -- 1 one 2 two 3 tre select * from t1 where rowid = max( rowid ) ; Error: misuse of aggregate function max() select max( rowid ) from t1 ; max( rowid ) 3 -- Stanley C. Kitching Human Being Phoenix, Arizona ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
What happens when you do: select * from t1 where rowid = (select max( rowid ) from t1); or select * from t1 where rowid in (select max( rowid ) from t1); On Mon, Aug 22, 2011 at 10:01 AM, Cousin Stanley cousinstan...@gmail.com wrote: Black, Michael (IS) wrote: select * from table where rowid=max(rowid); $ sqlite3 m2d1.sql3 -- Loading resources from /home/sk/.sqliterc SQLite version 3.7.3 Enter .help for instructions Enter SQL statements terminated with a ; .tables t1 t2 t3 .schema t1 CREATE TABLE t1(id INT,data TEXT); select * from t1 ; id data -- -- 1 one 2 two 3 tre select * from t1 where rowid = max( rowid ) ; Error: misuse of aggregate function max() select max( rowid ) from t1 ; max( rowid ) 3 -- Stanley C. Kitching Human Being Phoenix, Arizona ___ 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] Last record in db
On Mon, Aug 22, 2011 at 4:01 PM, Cousin Stanley cousinstan...@gmail.comwrote: select * from t1 where rowid = max( rowid ) ; Error: misuse of aggregate function max() That can be rewritten as: select * from t1 order by rowid desc limit 1; sqlite3 guarantees that the rowid only increments, never decrements. If the rowid limit is ever hit (very unlikely to happen!) you'll get a db full error, in which case it's probably time to recreate the table to get the rowid to start counting at 1 again. -- - 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] EXT :Re: Last record in db
Brad got it: sqlite select * from t1 where rowid = (select max(rowid) from t1); 3|three Why is max(rowid) a misuse. Seems perfectly logical to me. Not for an update but should work for select. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Brad Stiles [bradley.sti...@gmail.com] Sent: Monday, August 22, 2011 9:04 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Last record in db What happens when you do: select * from t1 where rowid = (select max( rowid ) from t1); or select * from t1 where rowid in (select max( rowid ) from t1); On Mon, Aug 22, 2011 at 10:01 AM, Cousin Stanley cousinstan...@gmail.com wrote: Black, Michael (IS) wrote: select * from table where rowid=max(rowid); $ sqlite3 m2d1.sql3 -- Loading resources from /home/sk/.sqliterc SQLite version 3.7.3 Enter .help for instructions Enter SQL statements terminated with a ; .tables t1 t2 t3 .schema t1 CREATE TABLE t1(id INT,data TEXT); select * from t1 ; id data -- -- 1 one 2 two 3 tre select * from t1 where rowid = max( rowid ) ; Error: misuse of aggregate function max() select max( rowid ) from t1 ; max( rowid ) 3 -- Stanley C. Kitching Human Being Phoenix, Arizona ___ 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] Last record in db
sqlite3 does NOT guarantee rowid always increments and never gives FULL return (at least according to the docs). autoincrement does. http://www.sqlite.org/autoinc.html Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Stephan Beal [sgb...@googlemail.com] Sent: Monday, August 22, 2011 9:07 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Last record in db On Mon, Aug 22, 2011 at 4:01 PM, Cousin Stanley cousinstan...@gmail.comwrote: select * from t1 where rowid = max( rowid ) ; Error: misuse of aggregate function max() That can be rewritten as: select * from t1 order by rowid desc limit 1; sqlite3 guarantees that the rowid only increments, never decrements. If the rowid limit is ever hit (very unlikely to happen!) you'll get a db full error, in which case it's probably time to recreate the table to get the rowid to start counting at 1 again. -- - 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] Autoincrement failure
On Mon, Aug 22, 2011 at 9:56 AM, a.azzol...@custom.it wrote: Hallo, Have you ever seen a SQLite3 DB file with autoincrement algoritm broken? Every new record seems to be added with rowid=1 overwriting existing info... Any idea about the causes of this issue and about extracting lost data (if present)? Please run from the sqlite3.exe shell: .dump sqlite_sequence Tell us what you see. Many thanks Alessandro ___ 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] EXT :Re: Last record in db
On Mon, Aug 22, 2011 at 10:08 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: Brad got it: select * from t1 where rowid = max( rowid ) ; Error: misuse of aggregate function max() sqlite select * from t1 where rowid = (select max(rowid) from t1); 3|three Why is max(rowid) a misuse. Seems perfectly logical to me. Not for an update but should work for select. I'm guessing that max(rowid) all by itself is either evaluating rowid as a variable or value independent of a table, or failing entirely because there is no context for evaluating rowid in the failing instance. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Autoincrement failure
sqlite .dump sqlite_sequence PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; sqlite Any idea? Thanks Alessandro From: Richard Hipp d...@sqlite.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Date: 22/08/2011 16.11 Subject: Re: [sqlite] Autoincrement failure On Mon, Aug 22, 2011 at 9:56 AM, a.azzol...@custom.it wrote: Hallo, Have you ever seen a SQLite3 DB file with autoincrement algoritm broken? Every new record seems to be added with rowid=1 overwriting existing info... Any idea about the causes of this issue and about extracting lost data (if present)? Please run from the sqlite3.exe shell: .dump sqlite_sequence Tell us what you see. Many thanks Alessandro ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Autoincrement failure
SELECT rowid FROM (mytable) WHERE (mystuff) returns 37 identical rows(!) where Rowid = 1 Alessandro From: a.azzol...@custom.it To: sqlite-users@sqlite.org Date: 22/08/2011 16.26 Subject: Re: [sqlite] Autoincrement failure sqlite .dump sqlite_sequence PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; sqlite Any idea? Thanks Alessandro From: Richard Hipp d...@sqlite.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Date: 22/08/2011 16.11 Subject: Re: [sqlite] Autoincrement failure On Mon, Aug 22, 2011 at 9:56 AM, a.azzol...@custom.it wrote: Hallo, Have you ever seen a SQLite3 DB file with autoincrement algoritm broken? Every new record seems to be added with rowid=1 overwriting existing info... Any idea about the causes of this issue and about extracting lost data (if present)? Please run from the sqlite3.exe shell: .dump sqlite_sequence Tell us what you see. Many thanks Alessandro ___ 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 ___ 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] Autoincrement failure
On Mon, Aug 22, 2011 at 11:17 AM, a.azzol...@custom.it wrote: SELECT rowid FROM (mytable) WHERE (mystuff) returns 37 identical rows(!) where Rowid = 1 What is your schema? If you run PRAGMA integrity_check? Alessandro From: a.azzol...@custom.it To: sqlite-users@sqlite.org Date: 22/08/2011 16.26 Subject: Re: [sqlite] Autoincrement failure sqlite .dump sqlite_sequence PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; sqlite Any idea? Thanks Alessandro From: Richard Hipp d...@sqlite.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Date: 22/08/2011 16.11 Subject: Re: [sqlite] Autoincrement failure On Mon, Aug 22, 2011 at 9:56 AM, a.azzol...@custom.it wrote: Hallo, Have you ever seen a SQLite3 DB file with autoincrement algoritm broken? Every new record seems to be added with rowid=1 overwriting existing info... Any idea about the causes of this issue and about extracting lost data (if present)? Please run from the sqlite3.exe shell: .dump sqlite_sequence Tell us what you see. Many thanks Alessandro ___ 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 ___ 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 -- 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] Autoincrement failure
schema 3 PRAGMA integrity_check returns *** in database main *** rowid 0 missing from index JournalDateIndex rowid 0 missing from index sqlite_autoindex_Journal_1 wrong # of entries in index JournalDateIndex wrong # of entries in index sqlite_autoindex_Journal_1 From: Richard Hipp d...@sqlite.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Date: 22/08/2011 17.21 Subject: Re: [sqlite] Autoincrement failure On Mon, Aug 22, 2011 at 11:17 AM, a.azzol...@custom.it wrote: SELECT rowid FROM (mytable) WHERE (mystuff) returns 37 identical rows(!) where Rowid = 1 What is your schema? If you run PRAGMA integrity_check? Alessandro From: a.azzol...@custom.it To: sqlite-users@sqlite.org Date: 22/08/2011 16.26 Subject: Re: [sqlite] Autoincrement failure sqlite .dump sqlite_sequence PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; sqlite Any idea? Thanks Alessandro From: Richard Hipp d...@sqlite.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Date: 22/08/2011 16.11 Subject: Re: [sqlite] Autoincrement failure On Mon, Aug 22, 2011 at 9:56 AM, a.azzol...@custom.it wrote: Hallo, Have you ever seen a SQLite3 DB file with autoincrement algoritm broken? Every new record seems to be added with rowid=1 overwriting existing info... Any idea about the causes of this issue and about extracting lost data (if present)? Please run from the sqlite3.exe shell: .dump sqlite_sequence Tell us what you see. Many thanks Alessandro ___ 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 ___ 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 -- D. Richard Hipp d...@sqlite.org ___ 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] Autoincrement failure
On Mon, Aug 22, 2011 at 11:25 AM, a.azzol...@custom.it wrote: schema 3 PRAGMA integrity_check returns *** in database main *** rowid 0 missing from index JournalDateIndex rowid 0 missing from index sqlite_autoindex_Journal_1 wrong # of entries in index JournalDateIndex wrong # of entries in index sqlite_autoindex_Journal_1 Try running REINDEX and see if that clears up the problem. From: Richard Hipp d...@sqlite.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Date: 22/08/2011 17.21 Subject: Re: [sqlite] Autoincrement failure On Mon, Aug 22, 2011 at 11:17 AM, a.azzol...@custom.it wrote: SELECT rowid FROM (mytable) WHERE (mystuff) returns 37 identical rows(!) where Rowid = 1 What is your schema? If you run PRAGMA integrity_check? Alessandro From: a.azzol...@custom.it To: sqlite-users@sqlite.org Date: 22/08/2011 16.26 Subject: Re: [sqlite] Autoincrement failure sqlite .dump sqlite_sequence PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; sqlite Any idea? Thanks Alessandro From: Richard Hipp d...@sqlite.org To: General Discussion of SQLite Database sqlite-users@sqlite.org Date: 22/08/2011 16.11 Subject: Re: [sqlite] Autoincrement failure On Mon, Aug 22, 2011 at 9:56 AM, a.azzol...@custom.it wrote: Hallo, Have you ever seen a SQLite3 DB file with autoincrement algoritm broken? Every new record seems to be added with rowid=1 overwriting existing info... Any idea about the causes of this issue and about extracting lost data (if present)? Please run from the sqlite3.exe shell: .dump sqlite_sequence Tell us what you see. Many thanks Alessandro ___ 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 ___ 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 -- D. Richard Hipp d...@sqlite.org ___ 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 -- 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] Last record in db
On 8/22/2011 10:04 AM, Brad Stiles wrote: What happens when you do: select * from t1 where rowid = (select max( rowid ) from t1); or select * from t1 where rowid in (select max( rowid ) from t1); or select * from t1 order by rowid desc limit 1; Likely more efficient this way. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: Last record in db
On 8/22/2011 9:52 AM, Black, Michael (IS) wrote: No...if you use autoincrement you can guarantee that last will be the last record inserted. There's no contradiction. Last is still defined only for ordered sets - you just chose a particular ordering, by rowid. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: Last record in db
On 22 Aug 2011, at 5:13pm, Igor Tandetnik wrote: On 8/22/2011 9:52 AM, Black, Michael (IS) wrote: No...if you use autoincrement you can guarantee that last will be the last record inserted. There's no contradiction. Last is still defined only for ordered sets - you just chose a particular ordering, by rowid. And even if you do that, it's easy to break the expected ordering: .schema t1 CREATE TABLE t1(id INT,data TEXT); select * from t1 ; id data -- -- 1 one 2 two 3 tre delete from t1 where data = 'two'; insert into t1 (it, data) values (2, 'second'); Now the 'last' record is not the one with the highest value in the id column. Igor is right. The question from the original poster doesn't mean anything in SQL. SQL has no concept of an order for rows, so it has no idea which row is 'first' or 'last'. If you, the programmer have your own idea what is first or last, write your own numbers into the database. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
autoincrement does keep order regardless of deletes. rowid won't guarantee it. So you don't have to add your own unless you need more than autoincrement. sqlite create table t1(id integer primary key,data text); sqlite insert into t1 values(NULL,'one'); sqlite insert into t1 values(NULL,'two'); sqlite insert into t1 values(NULL,'three'); sqlite select rowid from t1 where rowid=(select max(rowid) from t1); 3 sqlite delete from t1 where data='two'; sqlite insert into t1 values(NULL,'two'); sqlite select rowid from t1 where rowid=(select max(rowid) from t1); 4 You'll always get the last record that was successfully inserted. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Monday, August 22, 2011 11:32 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] EXT :Re: Last record in db On 22 Aug 2011, at 5:13pm, Igor Tandetnik wrote: On 8/22/2011 9:52 AM, Black, Michael (IS) wrote: No...if you use autoincrement you can guarantee that last will be the last record inserted. There's no contradiction. Last is still defined only for ordered sets - you just chose a particular ordering, by rowid. And even if you do that, it's easy to break the expected ordering: .schema t1 CREATE TABLE t1(id INT,data TEXT); select * from t1 ; id data -- -- 1 one 2 two 3 tre delete from t1 where data = 'two'; insert into t1 (it, data) values (2, 'second'); Now the 'last' record is not the one with the highest value in the id column. Igor is right. The question from the original poster doesn't mean anything in SQL. SQL has no concept of an order for rows, so it has no idea which row is 'first' or 'last'. If you, the programmer have your own idea what is first or last, write your own numbers into the database. Simon. ___ 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] Last record in db
On Mon, Aug 22, 2011 at 6:42 PM, Black, Michael (IS) michael.bla...@ngc.com wrote: You'll always get the last record that was successfully inserted. Just to play devil's advocate for a moment... As i recall, someone posted a report on this list a few months ago to report that the last insert ID (sqlite3_last_insert_rowid()) in his case was the ID of an insert (in a different table) caused as a side-effect of an on-insert trigger in the original target table. -- - 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] Last record in db
On 8/22/2011 12:42 PM, Black, Michael (IS) wrote: autoincrement does keep order regardless of deletes. rowid won't guarantee it. So you don't have to add your own unless you need more than autoincrement. sqlite create table t1(id integer primary key,data text); sqlite insert into t1 values(NULL,'one'); sqlite insert into t1 values(NULL,'two'); sqlite insert into t1 values(NULL,'three'); sqlite select rowid from t1 where rowid=(select max(rowid) from t1); 3 sqlite delete from t1 where data='two'; sqlite insert into t1 values(NULL,'two'); sqlite select rowid from t1 where rowid=(select max(rowid) from t1); 4 You'll always get the last record that was successfully inserted. sqlite insert into t1 values(2, 'another two'); sqlite select rowid from t1 where rowid=(select max(rowid) from t1); 4 It seems that the record your statement returns is not the record that was successfully inserted most recently. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange foreign key constraint failed with DROP TABLE
Hi Dan, Dan Kennedy danielk1...@gmail.com writes: It is. Now fixed in the trunk. Thanks for the fix. I patched 3.7.7.1 with it and indeed this now works: BEGIN TRANSACTION; DROP TABLE employer; DROP TABLE employee; COMMIT; However, this transaction: BEGIN TRANSACTION; DELETE FROM employer; DROP TABLE employer; DELETE FROM employee; DROP TABLE employee; COMMIT; Still issues Error: no such table: main.employer after the second DELETE. I don't think this should happen either. What do you think? Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
Sure it does. sqlite select rowid,id,* from t1 where id=(select max(id) from t1); 4|4|4|two two was the last succesful insert in my example. I'm now noticing though that rowid is not working as documented. http://www.sqlite.org/autoinc.html Says If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. Butif I delete the max rowid I expect it to be re-used based on the above sqlite create table t1(id integer primary key autoincrement,data text); sqlite insert into t1 values(null,'one'); sqlite insert into t1 values(null,'two'); sqlite insert into t1 values(null,'three'); sqlite select rowid,* from t1; 1|1|one 2|2|two 3|3|three sqlite delete from t1 where data='two'; sqlite insert into t1 values(null,'two'); sqlite select rowid,* from t1; 1|1|one 3|3|three 4|4|two sqlite delete from t1 where data='two'; sqlite insert into t1 values(null,'two'); sqlite select rowid,* from t1; 1|1|one 3|3|three 5|5|two This should be 4,5 and not 5,5 according to the docs as 3 was the largest in the table prior to insert. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, August 22, 2011 11:49 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Last record in db On 8/22/2011 12:42 PM, Black, Michael (IS) wrote: autoincrement does keep order regardless of deletes. rowid won't guarantee it. So you don't have to add your own unless you need more than autoincrement. sqlite create table t1(id integer primary key,data text); sqlite insert into t1 values(NULL,'one'); sqlite insert into t1 values(NULL,'two'); sqlite insert into t1 values(NULL,'three'); sqlite select rowid from t1 where rowid=(select max(rowid) from t1); 3 sqlite delete from t1 where data='two'; sqlite insert into t1 values(NULL,'two'); sqlite select rowid from t1 where rowid=(select max(rowid) from t1); 4 You'll always get the last record that was successfully inserted. sqlite insert into t1 values(2, 'another two'); sqlite select rowid from t1 where rowid=(select max(rowid) from t1); 4 It seems that the record your statement returns is not the record that was successfully inserted most recently. -- 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] Last record in db
On 8/22/2011 1:11 PM, Black, Michael (IS) wrote: Sure it does. sqlite select rowid,id,* from t1 where id=(select max(id) from t1); 4|4|4|two two was the last succesful insert in my example. But 'another two' (id==2) was the last successful insert in my extension of your example. Yet your statement still returns 4. I'm now noticing though that rowid is not working as documented. http://www.sqlite.org/autoinc.html Says If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. Butif I delete the max rowid I expect it to be re-used based on the above The quote you cite applies to the case where AUTOINCREMENT keyword is not specified. But in your latest example, you do specify one. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
That's because my id is autoincrement and yours is not. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, August 22, 2011 12:18 PM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Last record in db On 8/22/2011 1:11 PM, Black, Michael (IS) wrote: Sure it does. sqlite select rowid,id,* from t1 where id=(select max(id) from t1); 4|4|4|two two was the last succesful insert in my example. But 'another two' (id==2) was the last successful insert in my extension of your example. Yet your statement still returns 4. I'm now noticing though that rowid is not working as documented. http://www.sqlite.org/autoinc.html Says If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. Butif I delete the max rowid I expect it to be re-used based on the above The quote you cite applies to the case where AUTOINCREMENT keyword is not specified. But in your latest example, you do specify one. -- 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] Last record in db
On 8/22/2011 1:33 PM, Black, Michael (IS) wrote: That's because my id is autoincrement and yours is not. What do you mean, mine vs yours? I continue with your example, using the same setup. To avoid any confusion, here's a full session: sqlite create table NoAuto(id integer primary key, data text); sqlite insert into NoAuto values (NULL, 'one'); sqlite insert into NoAuto values (NULL, 'two'); sqlite insert into NoAuto values (NULL, 'three'); sqlite select rowid, data from NoAuto where rowid=(select max(rowid) from NoAuto); 3|three sqlite delete from NoAuto where data='two'; sqlite insert into NoAuto values(2, 'most recent'); sqlite select rowid, data from NoAuto where rowid=(select max(rowid) from NoAuto); 3|three sqlite create table Auto(id integer primary key autoincrement, data text); sqlite insert into Auto values (NULL, 'one'); sqlite insert into Auto values (NULL, 'two'); sqlite insert into Auto values (NULL, 'three'); sqlite select rowid, data from Auto where rowid=(select max(rowid) from Auto); 3|three sqlite delete from Auto where data='two'; sqlite insert into Auto values(2, 'most recent'); sqlite select rowid, data from Auto where rowid=(select max(rowid) from Auto); 3|three Note how the record with data=='most recent' was never selected, despite being inserted by the most recent successful INSERT statement. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
Ahhh...you didn't let autoincrement do it's job... sqlite create table Auto(id integer primary key autoincrement, data text); sqlite insert into Auto values (NULL, 'one'); sqlite insert into Auto values (NULL, 'two'); sqlite insert into Auto values (NULL, 'three'); sqlite select rowid, data from Auto where rowid=(select max(rowid) from Auto); 3|three sqlite delete from Auto where data='two'; sqlite insert into Auto values(NULL, 'most recent'); sqlite select id, data from Auto where id=(select max(id) from Auto); 4|most recent I wouldn't trust rowid given the description that it can reuse numbers. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, August 22, 2011 12:44 PM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Last record in db On 8/22/2011 1:33 PM, Black, Michael (IS) wrote: That's because my id is autoincrement and yours is not. What do you mean, mine vs yours? I continue with your example, using the same setup. To avoid any confusion, here's a full session: sqlite create table NoAuto(id integer primary key, data text); sqlite insert into NoAuto values (NULL, 'one'); sqlite insert into NoAuto values (NULL, 'two'); sqlite insert into NoAuto values (NULL, 'three'); sqlite select rowid, data from NoAuto where rowid=(select max(rowid) from NoAuto); 3|three sqlite delete from NoAuto where data='two'; sqlite insert into NoAuto values(2, 'most recent'); sqlite select rowid, data from NoAuto where rowid=(select max(rowid) from NoAuto); 3|three sqlite create table Auto(id integer primary key autoincrement, data text); sqlite insert into Auto values (NULL, 'one'); sqlite insert into Auto values (NULL, 'two'); sqlite insert into Auto values (NULL, 'three'); sqlite select rowid, data from Auto where rowid=(select max(rowid) from Auto); 3|three sqlite delete from Auto where data='two'; sqlite insert into Auto values(2, 'most recent'); sqlite select rowid, data from Auto where rowid=(select max(rowid) from Auto); 3|three Note how the record with data=='most recent' was never selected, despite being inserted by the most recent successful INSERT statement. -- 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] Last record in db
On 8/22/2011 1:56 PM, Black, Michael (IS) wrote: Ahhh...you didn't let autoincrement do it's job... Yes, quite intentionally, in order to emphasize the point that record with the largest rowid and record inserted most recently are not necessarily one and the same, whether or not AUTOINCREMENT was specified when the table was created. Of course it's possible to construct an example where the same record is both most recently inserted and has the largest rowid. But it's also possible to construct an example where these are two different records. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simulating the BINARY data type
How can I store and retrieve data in the equivalent of mySQL's BINARY datatype? The collation sequence doesn't matter in this instance. Is BLOB the appropriate sqlite datatype? Pete ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simulating the BINARY data type
On 8/22/2011 2:34 PM, Pete wrote: How can I store and retrieve data in the equivalent of mySQL's BINARY datatype? The collation sequence doesn't matter in this instance. Is BLOB the appropriate sqlite datatype? Yes, use BLOB. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
I thought we were answering the question how can I retrive the last row -- though we never got a definition of what last meant. I assumed last inserted. Sure you can construct an example that doesn't work. But he didn't ask how NOT to do it. Don't you agree that using autoincrement properly guarantees retrieving the last inserted row? Or are you maintaining that is a false statement? I'd like to see an example to disprove it if you maintain that its false. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, August 22, 2011 1:14 PM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Last record in db On 8/22/2011 1:56 PM, Black, Michael (IS) wrote: Ahhh...you didn't let autoincrement do it's job... Yes, quite intentionally, in order to emphasize the point that record with the largest rowid and record inserted most recently are not necessarily one and the same, whether or not AUTOINCREMENT was specified when the table was created. Of course it's possible to construct an example where the same record is both most recently inserted and has the largest rowid. But it's also possible to construct an example where these are two different records. -- 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] Last record in db
On 22 Aug 2011, at 8:43pm, Black, Michael (IS) wrote: I thought we were answering the question how can I retrive the last row -- though we never got a definition of what last meant. [snip] which is, of course, the problem with that question. Don't you agree that using autoincrement properly guarantees retrieving the last inserted row? As long as you're not messing about with the way SQLite does things, using the SELECT ... ORDER BY rowid DESC LIMIT 1 form is as good an answer as any. By the way, I don't think anyone has mentioned either SELECT last_insert_rowid() FROM myTable or the sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*) C function yet. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
According to the docs rowid is not guaranteed to be monotonic. So this is not guaranteed to give the right answer. SELECT ... ORDER BY rowid DESC LIMIT 1 However, define your own autoincrement (myid) and it is. SELECT ... ORDER BY myid DESC LIMIT 1 Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Monday, August 22, 2011 2:49 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Last record in db On 22 Aug 2011, at 8:43pm, Black, Michael (IS) wrote: I thought we were answering the question how can I retrive the last row -- though we never got a definition of what last meant. [snip] which is, of course, the problem with that question. Don't you agree that using autoincrement properly guarantees retrieving the last inserted row? As long as you're not messing about with the way SQLite does things, using the SELECT ... ORDER BY rowid DESC LIMIT 1 form is as good an answer as any. By the way, I don't think anyone has mentioned either SELECT last_insert_rowid() FROM myTable or the sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*) C function yet. Simon. ___ 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] Strange foreign key constraint failed with DROP TABLE
On Mon, Aug 22, 2011 at 12:54 PM, Boris Kolpackov bo...@codesynthesis.comwrote: Hi Dan, Dan Kennedy danielk1...@gmail.com writes: It is. Now fixed in the trunk. Thanks for the fix. I patched 3.7.7.1 with it and indeed this now works: BEGIN TRANSACTION; DROP TABLE employer; DROP TABLE employee; COMMIT; However, this transaction: BEGIN TRANSACTION; DELETE FROM employer; DROP TABLE employer; DELETE FROM employee; Dan has convinced me that the SQLite code is correct as documented. The documentation states: Foreign key DML errors are may be reported if: (1) The parent table does not exist... And for the DELETE statement above, the parent table does not exist. So it is appropriate to rais an error. DROP TABLE employee; COMMIT; Still issues Error: no such table: main.employer after the second DELETE. I don't think this should happen either. What do you think? Boris -- Boris Kolpackov, Code Synthesis http://codesynthesis.com/~boris/blog Compiler-based http://codesynthesis.com/%7Eboris/blog%0ACompiler-basedORM system for C++ http://codesynthesis.com/products/odb Open-source http://codesynthesis.com/products/odb%0AOpen-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ 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] Last record in db
last_insert_row_id isn't guaranteed either...and here's the complete example of using autoincrement that is guaranteed to work and not be volatile. D:\SQLitesqlite3 t1.db SQLite version 3.7.4 Enter .help for instructions Enter SQL statements terminated with a ; sqlite create table t1 (id integer primary key autoincrement,data text); sqlite insert into t1 values(null,'one'); sqlite insert into t1 values(null,'two'); sqlite insert into t1 values(null,'three'); sqlite .quit D:\SQLitesqlite3 t1.db SQLite version 3.7.4 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select last_insert_rowid() from t1 limit 1; 0 last_insert_rowid is volatile. sqlite select id,data from t1 order by id desc limit 1; 3,three sqlite delete from t1 where data='two'; sqlite insert into t1 values(null,'two'); sqlite select id,data from t1 order by id desc limit 1; 4|two sqlite.quit D:\SQLitesqlite3 t1.db SQLite version 3.7.4 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select id,data from t1 order by id desc limit 1; 4|two Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, August 22, 2011 3:01 PM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Last record in db On 8/22/2011 3:43 PM, Black, Michael (IS) wrote: I thought we were answering the question how can I retrive the last row -- though we never got a definition of what last meant. I assumed last inserted. Defining the meaning of the word last in terms of an expression that includes the word last is a bit circular. Let's say, most recently inserted. Sure you can construct an example that doesn't work. But he didn't ask how NOT to do it. OK then, show how to do it. You haven't, yet. Don't you agree that using autoincrement properly guarantees retrieving the last inserted row? Or are you maintaining that is a false statement? I maintain that the request you have shown - select id, data from Auto where id=(select max(id) from Auto); - doesn't always retrieve the most recently inserted row, and thus doesn't in fact solve the problem you claim it solves. I'd like to see an example to disprove it if you maintain that its false. I have shown one. -- 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] Bug in GCC - suggestions sought for a backup tool chain
Consider this line of code in the build.c source file of SQLite: http://www.sqlite.org/src/artifact/77be7c217430?ln=3372 It appears that GCC 4.1.0 is not generating any code for the second test in the conditional. In other words, GCC 4.1.0 is compiling that statement as if it omitted the p-a term and looked like this: if( p ){ You can see this for yourself by downloading the file above and then running: gcc -g -S build.c And then looking at the build.s output file. With GCC 4.1.0, I get this: .loc 1 3372 0 cmpl$0, 8(%ebp) je.L920 Looks like only one test to me. But with GCC 4.5.2 I get this: .loc 1 3372 0 cmpq$0, -24(%rbp) je.L611 .loc 1 3372 0 is_stmt 0 discriminator 1 movq-24(%rbp), %rax addq$8, %rax testq%rax, %rax je.L611 Both tests appear to be coded this time. As it happens, the GCC bug is harmless in this case. SQLite never invokes the sqlite3SrcListShiftJoinType() function with a non-NULL SrcList pointer that has a NULL p-a value. So the p-a!=NULL test really is always true. (Note that the GCC optimizer has no way of knowing that because the function has external linkage.) And so it didn't matter that the test was omitted. I didn't notice the problem until this morning, when I upgraded my desktop to the latest Ubuntu containing GCC 4.5.2, and reran the full branch coverage tests. GCC 4.5.2 was showing that the p-a!=NULL branch was always true. Further investigation shows that it has always been always true but that the GCC 4.1.0 bug simply masked the error up until now. I see two take-aways from this episode: (1) Compilers sometimes make mistakes. So it is important that you test your object code - not just your source code. That means running your test cases using exactly the same *.o files that you use for delivery. Fly what you test and test what you fly. (2) I need to come up with a second, independent method of verifying branch test coverage in SQLite. I have been using GCC+GCOV and it does a great job and I fully intend to continue using it as the primary tool chain for development and testing. But in this case, because GCC was omitting a test, it missed the fact that there was no test coverage for the omitted test. So it would be nice to have an independently developed tool chain that can be used to confirm the results we get from GCOV. Anybody have any suggestions? -- 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] How to reindex an FTS3 table after changing the tokenizer
On Fri, Aug 12, 2011 at 11:27 AM, john Papier johnpap...@gmail.com wrote: I have a FTS3 table that was created with the simple tokenizer. I want to change the tokenizer and reindex the table. Is there a way to change the tokenizer in place and have it reindex with minimal code? Else the other option I was thinking about was dropping the table, re-creating it with the new tokenizer, and inserting back the data That's the option! Make it seamless something like: BEGIN; ALTER TABLE my_table RENAME TO my_table_tmp; CREATE VIRTUAL TABLE my_table USING FTS3(blah blah blah); INSERT INTO my_table SELECT x, y, z FROM my_table_tmp; DROP TABLE my_table_tmp; COMMIT; There's really not anything more in-place, because the code would have to be tricky, like remembering which rows were indexed with which tokenizer, so that it can update the right parts of the index when the row is updated or deleted. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: Last record in db
I understand now. How does sqlite insert a record ? More specifically how does sqlite update the B-tree with the new record . Is there a linkage made between the newly inserted record and the previous one ? Sent from BlackBerry® on Airtel -Original Message- From: Simon Slavin slav...@bigfraud.org Sender: sqlite-users-boun...@sqlite.org Date: Mon, 22 Aug 2011 17:32:51 To: General Discussion of SQLite Databasesqlite-users@sqlite.org Reply-To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] EXT :Re: Last record in db On 22 Aug 2011, at 5:13pm, Igor Tandetnik wrote: On 8/22/2011 9:52 AM, Black, Michael (IS) wrote: No...if you use autoincrement you can guarantee that last will be the last record inserted. There's no contradiction. Last is still defined only for ordered sets - you just chose a particular ordering, by rowid. And even if you do that, it's easy to break the expected ordering: .schema t1 CREATE TABLE t1(id INT,data TEXT); select * from t1 ; id data -- -- 1 one 2 two 3 tre delete from t1 where data = 'two'; insert into t1 (it, data) values (2, 'second'); Now the 'last' record is not the one with the highest value in the id column. Igor is right. The question from the original poster doesn't mean anything in SQL. SQL has no concept of an order for rows, so it has no idea which row is 'first' or 'last'. If you, the programmer have your own idea what is first or last, write your own numbers into the database. Simon. ___ 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] Split Function for SQLite?
I need to split up a list of items in a single row so they each have their own row. Basically I need to take this: Key. Code -- 1. V1, v2, v3 And convert it to this: Key. Code -- 1. V1 1. V2 1. V3 After much googling I'm thinking I need a split function. I know SQLite allows addition of functions. I did find a few different implementations of split functions, but there were from full SQL and SQL Server sites. Do I need a split function designed specifically for SQLite? How would I go about adding the function so I can use it? Thanks! Greg Moore thewatchful...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in GCC - suggestions sought for a backup tool chain
On 23 Aug 2011, at 1:22am, Richard Hipp wrote: It appears that GCC 4.1.0 is not generating any code for the second test in the conditional. In other words, GCC 4.1.0 is compiling that statement as if it omitted the p-a term How interesting. Can't solve your problem but pure curiosity on my part: is this a result of optimization ? Would it be worth trying all the '-O' options, especially '-O0', in http://gcc.gnu.org/onlinedocs/gcc/Optimize-Options.html Or would your observation be independent of any such settings ? Given that the later version of GCC fixes the bug, I guess someone somewhere noticed the problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: Last record in db
sreekumar...@gmail.com wrote: How does sqlite insert a record ? More specifically how does sqlite update the B-tree with the new record . Is there a linkage made between the newly inserted record and the previous one ? http://www.sqlite.org/autoinc.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in GCC - suggestions sought for a backup tool chain
On Mon, Aug 22, 2011 at 11:15 PM, Simon Slavin slav...@bigfraud.org wrote: On 23 Aug 2011, at 1:22am, Richard Hipp wrote: It appears that GCC 4.1.0 is not generating any code for the second test in the conditional. In other words, GCC 4.1.0 is compiling that statement as if it omitted the p-a term How interesting. Can't solve your problem but pure curiosity on my part: is this a result of optimization ? Would it be worth trying all the '-O' options, especially '-O0', in http://gcc.gnu.org/onlinedocs/gcc/Optimize-Options.html Or would your observation be independent of any such settings ? Notice that my assembly-language listings were generated without any options other than -g (so that I would get line numbers) and -S (to stop before running the assemblier). No optimizations specified, which unless I'm badly mistaken, means than no optimizations are run. Given that the later version of GCC fixes the bug, I guess someone somewhere noticed the problem. Simon. ___ 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] Split Function for SQLite?
Gregory Moore thewatchful...@gmail.com wrote: I need to split up a list of items in a single row so they each have their own row. Basically I need to take this: Key. Code -- 1. V1, v2, v3 And convert it to this: Key. Code -- 1. V1 1. V2 1. V3 I don't think you can do this with SQL alone. You'll have to implement the logic in your favorite programming language. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in GCC - suggestions sought for a backup tool chain
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/23/2011 05:52 AM, Richard Hipp wrote: (1) Compilers sometimes make mistakes. So it is important that you test your object code - not just your source code. That means running your test cases using exactly the same *.o files that you use for delivery. Fly what you test and test what you fly. I would recommend also compiling and running the test suite using PCC (it is becoming the standard compiler on BSDs), CLANG (increasingly pervasive) and the Intel compiler (known for strong optimisations, excellent VTune performance analysis tool). http://en.wikipedia.org/wiki/Portable_C_Compiler http://software.intel.com/en-us/articles/intel-parallel-studio-xe/ (2) I need to come up with a second, independent method of verifying branch test coverage in SQLite. It looks like you need both a compiler that generates the necessary information in the debug tables and a profiling tool that knows how to read that. I can only find gcc+gcov doing that, although the Intel tools may do so as well. It seems like you should be able to get statement test coverage using almost any compiler and gcov or callgrind, which is better than nothing but not as good as branch coverage. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk5TMSwACgkQmOOfHg372QQkEgCaAkX4Nl8ezZwgP6eDinWh1NIz ORQAoN3TN2+WMc3QumnXxDcsEp+eDOo7 =dEiZ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users