Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
Sorry, I didn't read the whole story before answering. You are right, the documentation on sqlite3_last_insert_rowid should contain some comment about the conflicts. I guess that, after working with SQLite for a long time, obvious things are not obvious to everyone and are easily forgotten in documentation :) Michael Ruck wrote: Daniel, My usecase is the following: I maintain a lot of tables, which are simply catalogs of predefined or user entered values. I wanted to reduce the amount of code and memory to maintain those tables, as the user can enter values as free text in a lot of web forms and also choose from previously entered values via ajax autocompletion. To simplify my code I wanted to use INSERT OR IGNORE in those catalog tables, as I don't care if the value is already there or not. I just need the rowids as foreign keys for other table(s). An example would be: - CREATE TABLE song (id INTEGER PRIMARY KEY, artist INTEGER, writer INTEGER, title INTEGER) - CREATE TABLE artists (id INTEGER PRIMARY KEY, value TEXT UNIQUE) - CREATE TABLE writers (id INTEGER PRIMARY KEY, value TEXT UNIQUE) - CREATE TABLE titles (id INTEGER PRIMARY KEY, value TEXT UNIQUE) For an insert of a new song I wanted to do (pseudo code) INSERT OR IGNORE INTO artists VALUES (NULL, 'user entered or chosen artistname'); rowid-of-first-insert = sqlite3_last_insert_rowid(); INSERT OR IGNORE INTO writers VALUES (NULL, 'user entered or chosen writer name'); rowid-of-second-insert = sqlite3_last_insert_rowid(); INSERT OR IGNORE INTO titles VALUES (NULL, 'user entered or chosen title'); rowid-of-third-insert = sqlite3_last_insert_rowid(); INSERT INTO song (NULL, rowid-of-first-insert, rowid-of-second-insert, rowid-of-third-insert); This is just a trivial example of what I want to do. My understanding of INSERT OR IGNORE was that it always succeeds, even though the record is already there (which is exactly what I want.) In contrast to INSERT OR REPLACE it doesn't remove the old row and thus keeps the same rowid. In conjunction with the documentation for sqlite3_last_insert_rowid(), which states: "This routine returns the rowid of the most recent INSERT into the database from the database connection given in the first argument. If no inserts have ever occurred on this database connection, zero is returned." I was assuming that I'll receive the rowid even in case where the conflict clause from INSERT OR IGNORE caused the insert not to happen. I was just a bit surprised about this and that's why I asked if this was expected behavior. Dr. Hipps answer cleared the reason for this up and I already started looking for alternatives. The only thing I was asking for is that this behavior (sqlite3_last_insert_rowid() and its results with CONFLICT clauses) be explicitly mentioned in the documentation of sqlite3_last_insert_rowid(). Unfortunately the solution proposed by Shawn Odekirk fails too, as I don't have the rowids for insert statements. If i did, I wouldn't INSERT OR IGNORE or INSERT OR REPLACE in the first place. The id column is automatically maintained by SQlite and I don't want to mess with it. So I'll just do blind inserts and check the return value and do a select if insert fails. Little more code, but it works. Mike -Ursprüngliche Nachricht- Von: Daniel Önnerby [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 30. Oktober 2007 12:03 An: sqlite-users@sqlite.org Betreff: Re: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() Why are you using the INSERT OR IGNORE? If you read the http://www.sqlite.org/lang_conflict.html it states that the IGNORE will not return any errors. Isn't the default behavior INSERT OR ABORT (or just plain INSERT) what you are looking for? The default INSERT will return an error (SQLITE_CONSTRAINT) if there is a conflict. If you get a conflict then do not trust the sqlite3_last_insert_rowid since (I guess) it will return the last successful insert rowid. Best regards Daniel Michael Ruck wrote: I'm not blaming anyone. I just think it should be mentioned in the docs. Mike -Ursprüngliche Nachricht- Von: John Stanton [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 29. November 2007 20:12 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() As has been carefully explained by several people, it is reliable. You just did not think through your application. You could make an extension to Sqlite and implement an sqlite3_last_insert_or_ignore_id function, but to blithely assume that you can use last_insert_id with INSERT OR IGNORE is not logical and to blame others for your oversight is not helpful. Michael Ruck wrote: I don't get an error code. So how should I decide if I should call sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any indication if an insert was actually performed or if it was simply ignored - thus I don't have any possibility
RE: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
Daniel, My usecase is the following: I maintain a lot of tables, which are simply catalogs of predefined or user entered values. I wanted to reduce the amount of code and memory to maintain those tables, as the user can enter values as free text in a lot of web forms and also choose from previously entered values via ajax autocompletion. To simplify my code I wanted to use INSERT OR IGNORE in those catalog tables, as I don't care if the value is already there or not. I just need the rowids as foreign keys for other table(s). An example would be: - CREATE TABLE song (id INTEGER PRIMARY KEY, artist INTEGER, writer INTEGER, title INTEGER) - CREATE TABLE artists (id INTEGER PRIMARY KEY, value TEXT UNIQUE) - CREATE TABLE writers (id INTEGER PRIMARY KEY, value TEXT UNIQUE) - CREATE TABLE titles (id INTEGER PRIMARY KEY, value TEXT UNIQUE) For an insert of a new song I wanted to do (pseudo code) INSERT OR IGNORE INTO artists VALUES (NULL, 'user entered or chosen artistname'); rowid-of-first-insert = sqlite3_last_insert_rowid(); INSERT OR IGNORE INTO writers VALUES (NULL, 'user entered or chosen writer name'); rowid-of-second-insert = sqlite3_last_insert_rowid(); INSERT OR IGNORE INTO titles VALUES (NULL, 'user entered or chosen title'); rowid-of-third-insert = sqlite3_last_insert_rowid(); INSERT INTO song (NULL, rowid-of-first-insert, rowid-of-second-insert, rowid-of-third-insert); This is just a trivial example of what I want to do. My understanding of INSERT OR IGNORE was that it always succeeds, even though the record is already there (which is exactly what I want.) In contrast to INSERT OR REPLACE it doesn't remove the old row and thus keeps the same rowid. In conjunction with the documentation for sqlite3_last_insert_rowid(), which states: "This routine returns the rowid of the most recent INSERT into the database from the database connection given in the first argument. If no inserts have ever occurred on this database connection, zero is returned." I was assuming that I'll receive the rowid even in case where the conflict clause from INSERT OR IGNORE caused the insert not to happen. I was just a bit surprised about this and that's why I asked if this was expected behavior. Dr. Hipps answer cleared the reason for this up and I already started looking for alternatives. The only thing I was asking for is that this behavior (sqlite3_last_insert_rowid() and its results with CONFLICT clauses) be explicitly mentioned in the documentation of sqlite3_last_insert_rowid(). Unfortunately the solution proposed by Shawn Odekirk fails too, as I don't have the rowids for insert statements. If i did, I wouldn't INSERT OR IGNORE or INSERT OR REPLACE in the first place. The id column is automatically maintained by SQlite and I don't want to mess with it. So I'll just do blind inserts and check the return value and do a select if insert fails. Little more code, but it works. Mike -Ursprüngliche Nachricht- Von: Daniel Önnerby [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 30. Oktober 2007 12:03 An: sqlite-users@sqlite.org Betreff: Re: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() Why are you using the INSERT OR IGNORE? If you read the http://www.sqlite.org/lang_conflict.html it states that the IGNORE will not return any errors. Isn't the default behavior INSERT OR ABORT (or just plain INSERT) what you are looking for? The default INSERT will return an error (SQLITE_CONSTRAINT) if there is a conflict. If you get a conflict then do not trust the sqlite3_last_insert_rowid since (I guess) it will return the last successful insert rowid. Best regards Daniel Michael Ruck wrote: > I'm not blaming anyone. I just think it should be mentioned in the docs. > > Mike > > -Ursprüngliche Nachricht- > Von: John Stanton [mailto:[EMAIL PROTECTED] > Gesendet: Donnerstag, 29. November 2007 20:12 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() > > As has been carefully explained by several people, it is reliable. > You just did not think through your application. You could make an > extension to Sqlite and implement an sqlite3_last_insert_or_ignore_id > function, but to blithely assume that you can use last_insert_id with > INSERT OR IGNORE is not logical and to blame others for your oversight > is not helpful. > > Michael Ruck wrote: > >> I don't get an error code. So how should I decide if I should call >> sqlite3_last_insert_rowid() or not? :) That's the problem - I don't >> have >> > any > >> indication if an insert >> was actually performed or if it was simply ignored - thus I don't >> have any possibility to decide if the call is valid or not. This >> makes the OR >> > IGNORE > >> clause or the sqlite3_last_insert_rowid() function useless for *my >> purposes*. I wou
Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
As has been carefully explained by several people, it is reliable. You just did not think through your application. You could make an extension to Sqlite and implement an sqlite3_last_insert_or_ignore_id function, but to blithely assume that you can use last_insert_id with INSERT OR IGNORE is not logical and to blame others for your oversight is not helpful. Michael Ruck wrote: I don't get an error code. So how should I decide if I should call sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any indication if an insert was actually performed or if it was simply ignored - thus I don't have any possibility to decide if the call is valid or not. This makes the OR IGNORE clause or the sqlite3_last_insert_rowid() function useless for *my purposes*. I would have never pursued this path in tests, if I would've known beforehand that it is not reliable if used with ON CONFLICT clauses. Mike -Ursprüngliche Nachricht- Von: Odekirk, Shawn [mailto:[EMAIL PROTECTED] Gesendet: Montag, 29. Oktober 2007 14:04 An: sqlite-users@sqlite.org Betreff: RE: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() The sqlite3_last_insert_rowid function is completely, 100% reliable in your scenario. The problem is that in your scenario you shouldn't be calling that function. The function is called sqlite3_last_insert_rowid, not sqlite3_last_insert_or_ignore_rowid, and not sqlite3_last_insert_or_fail_rowid. It makes perfect sense that it returns the row id of the last row inserted successfully. This function should only be called after a successful insert. In your scenario you have not performed a successful insert. There is no reason to think that the function will return a meaningful row id after a failed insert attempt. I hope my response was not too harsh. You seem so adamant that there is a problem with the function or documentation, and I completely disagree. Shawn -Original Message- From: Michael Ruck [mailto:[EMAIL PROTECTED] Sent: Sunday, October 28, 2007 12:55 PM To: sqlite-users@sqlite.org Subject: AW: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() I'd suggest putting this into the documentation of sqlite3_last_insert_rowid(), that the call is not reliable in scenarios such as this one. -Ursprüngliche Nachricht- Von: D. Richard Hipp [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 28. Oktober 2007 17:48 An: sqlite-users@sqlite.org Betreff: Re: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote: Yes, I am well aware of this possibility as I've written in my initial mail. It just doesn't fit with the description of sqlite3_last_insert_rowid() in my understanding. I think this is a bug - either in the documentation or in the implementation. sqlite3_last_insert_rowid() should return the correct id, no matter what and it doesn't. Consider this scenario: CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE); INSERT INTO ex1 VALUES(1,1,1); INSERT INTO ex1 VALUES(2,2,2); INSERT INTO ex1 VALUES(3,3,3); Now you do your INSERT OR IGNORE: INSERT OR IGNORE INTO ex1 VALUES(1,2,3); Three different constraints fail, one for each of three different rows. So if sqlite3_last_insert_rowid() were to operate as you suggest and return the rowid of the failed insert, when rowid would it return? 1, 2, or 3? D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
On 29/10/2007, Odekirk, Shawn <[EMAIL PROTECTED]> wrote: > Adding "primary key" to column "a" results in the behavior I think you > were first expecting. > > sqlite> create table tmp (a integer primary key, b integer); > sqlite> create unique index tmpIndex on tmp (a, b); > sqlite> insert into tmp values (1, 1); > sqlite> insert into tmp values (2, 2); > sqlite> select last_insert_rowid(); > 2 > sqlite> insert or replace into tmp values (1, 1); > sqlite> select last_insert_rowid(); > 1 > sqlite> select * from tmp; > 1|1 > 2|2 > > I wonder if Michael could use OR REPLACE instead of OR IGNORE to solve > his problem. > > Shawn > That was the result I was seeking (as a potential answer to Michael's problem). Looking at Michael's original post, he has declared an INTEGER PRIMARY KEY column, so "OR REPLACE" instead of "OR IGNORE" could be the solution as you suggest. Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
[Default] On Mon, 29 Oct 2007 15:00:51 +0100, "Michael Ruck" <[EMAIL PROTECTED]> wrote: >I don't get an error code. So how should I decide if I should call >sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any >indication if an insert >was actually performed or if it was simply ignored - thus I don't have any >possibility to decide if the call is valid or not. This makes the OR IGNORE >clause or the sqlite3_last_insert_rowid() function useless for *my >purposes*. I would have never pursued this path in tests, if I would've >known beforehand that it is not reliable if used with ON CONFLICT clauses. > >Mike Perhaps http://www.sqlite.org/capi3ref.html#sqlite3_update_hook can help you solve your problem? -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
Adding "primary key" to column "a" results in the behavior I think you were first expecting. sqlite> create table tmp (a integer primary key, b integer); sqlite> create unique index tmpIndex on tmp (a, b); sqlite> insert into tmp values (1, 1); sqlite> insert into tmp values (2, 2); sqlite> select last_insert_rowid(); 2 sqlite> insert or replace into tmp values (1, 1); sqlite> select last_insert_rowid(); 1 sqlite> select * from tmp; 1|1 2|2 I wonder if Michael could use OR REPLACE instead of OR IGNORE to solve his problem. Shawn -Original Message- From: Simon Davies [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 11:02 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() Thanks for the explanation! On 29/10/2007, Dennis Cote <[EMAIL PROTECTED]> wrote: > Simon Davies wrote: > > Following this thread, I was experimenting with last_insert_rowid(), > > and found the following, which does not look right: > > > > SQLite version 3.4.2 > > Enter ".help" for instructions > > sqlite> > > sqlite> create table tmp( a integer, b integer ); > > sqlite> create unique index tmpIndex on tmp( a, b ); > > sqlite> insert into tmp values( 1, 1 ); > > sqlite> insert into tmp values( 2, 2 ); > > sqlite> select last_insert_rowid(); > > 2 > > sqlite> > > sqlite> insert or replace into tmp values( 1, 1 ); > > sqlite> select last_insert_rowid(); > > 3 > > <-- !!!???!!! > > sqlite> select * from tmp; > > 2|2 > > 1|1 > > sqlite> > > > > > > > > > Simon, > > If you change your query to; > >select rowid, * from tmp; > > it will display the rowid which is different than either of the fields > in the table. > > When doing a replace sqlite deletes the existing row and adds a new row. > > HTH > Dennis Cote > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
This link gives a little more information: http://www.sqlite.org/autoinc.html Shawn -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Monday, October 29, 2007 10:57 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() Simon Davies wrote: > Following this thread, I was experimenting with last_insert_rowid(), > and found the following, which does not look right: > > SQLite version 3.4.2 > Enter ".help" for instructions > sqlite> > sqlite> create table tmp( a integer, b integer ); > sqlite> create unique index tmpIndex on tmp( a, b ); > sqlite> insert into tmp values( 1, 1 ); > sqlite> insert into tmp values( 2, 2 ); > sqlite> select last_insert_rowid(); > 2 > sqlite> > sqlite> insert or replace into tmp values( 1, 1 ); > sqlite> select last_insert_rowid(); > 3 > <-- !!!???!!! > sqlite> select * from tmp; > 2|2 > 1|1 > sqlite> > > > > Simon, If you change your query to; select rowid, * from tmp; it will display the rowid which is different than either of the fields in the table. When doing a replace sqlite deletes the existing row and adds a new row. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
Thanks for the explanation! On 29/10/2007, Dennis Cote <[EMAIL PROTECTED]> wrote: > Simon Davies wrote: > > Following this thread, I was experimenting with last_insert_rowid(), > > and found the following, which does not look right: > > > > SQLite version 3.4.2 > > Enter ".help" for instructions > > sqlite> > > sqlite> create table tmp( a integer, b integer ); > > sqlite> create unique index tmpIndex on tmp( a, b ); > > sqlite> insert into tmp values( 1, 1 ); > > sqlite> insert into tmp values( 2, 2 ); > > sqlite> select last_insert_rowid(); > > 2 > > sqlite> > > sqlite> insert or replace into tmp values( 1, 1 ); > > sqlite> select last_insert_rowid(); > > 3 > > <-- !!!???!!! > > sqlite> select * from tmp; > > 2|2 > > 1|1 > > sqlite> > > > > > > > > > Simon, > > If you change your query to; > >select rowid, * from tmp; > > it will display the rowid which is different than either of the fields > in the table. > > When doing a replace sqlite deletes the existing row and adds a new row. > > HTH > Dennis Cote > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
Simon Davies wrote: Following this thread, I was experimenting with last_insert_rowid(), and found the following, which does not look right: SQLite version 3.4.2 Enter ".help" for instructions sqlite> sqlite> create table tmp( a integer, b integer ); sqlite> create unique index tmpIndex on tmp( a, b ); sqlite> insert into tmp values( 1, 1 ); sqlite> insert into tmp values( 2, 2 ); sqlite> select last_insert_rowid(); 2 sqlite> sqlite> insert or replace into tmp values( 1, 1 ); sqlite> select last_insert_rowid(); 3 <-- !!!???!!! sqlite> select * from tmp; 2|2 1|1 sqlite> Simon, If you change your query to; select rowid, * from tmp; it will display the rowid which is different than either of the fields in the table. When doing a replace sqlite deletes the existing row and adds a new row. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
Michael Ruck wrote: I don't get an error code. So how should I decide if I should call sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any indication if an insert was actually performed or if it was simply ignored - thus I don't have any possibility to decide if the call is valid or not. This makes the OR IGNORE clause or the sqlite3_last_insert_rowid() function useless for *my purposes*. I would have never pursued this path in tests, if I would've known beforehand that it is not reliable if used with ON CONFLICT clauses. Mike, It seems to me that you have asked for exactly that behavior by using INSERT OR IGNORE. By using this clause you have said you don't care if a row is inserted or not. Why would you expect an error code? If you need to know if the insert fails then you should use INSERT OR FAIL. When it fails you will be notified, and you can handle the failure in an appropriate manner. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
Hi All, Following this thread, I was experimenting with last_insert_rowid(), and found the following, which does not look right: SQLite version 3.4.2 Enter ".help" for instructions sqlite> sqlite> create table tmp( a integer, b integer ); sqlite> create unique index tmpIndex on tmp( a, b ); sqlite> insert into tmp values( 1, 1 ); sqlite> insert into tmp values( 2, 2 ); sqlite> select last_insert_rowid(); 2 sqlite> sqlite> insert or replace into tmp values( 1, 1 ); sqlite> select last_insert_rowid(); 3 <-- !!!???!!! sqlite> select * from tmp; 2|2 1|1 sqlite> Rgds, Simon - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
I don't get an error code. So how should I decide if I should call sqlite3_last_insert_rowid() or not? :) That's the problem - I don't have any indication if an insert was actually performed or if it was simply ignored - thus I don't have any possibility to decide if the call is valid or not. This makes the OR IGNORE clause or the sqlite3_last_insert_rowid() function useless for *my purposes*. I would have never pursued this path in tests, if I would've known beforehand that it is not reliable if used with ON CONFLICT clauses. Mike -Ursprüngliche Nachricht- Von: Odekirk, Shawn [mailto:[EMAIL PROTECTED] Gesendet: Montag, 29. Oktober 2007 14:04 An: sqlite-users@sqlite.org Betreff: RE: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() The sqlite3_last_insert_rowid function is completely, 100% reliable in your scenario. The problem is that in your scenario you shouldn't be calling that function. The function is called sqlite3_last_insert_rowid, not sqlite3_last_insert_or_ignore_rowid, and not sqlite3_last_insert_or_fail_rowid. It makes perfect sense that it returns the row id of the last row inserted successfully. This function should only be called after a successful insert. In your scenario you have not performed a successful insert. There is no reason to think that the function will return a meaningful row id after a failed insert attempt. I hope my response was not too harsh. You seem so adamant that there is a problem with the function or documentation, and I completely disagree. Shawn -Original Message- From: Michael Ruck [mailto:[EMAIL PROTECTED] Sent: Sunday, October 28, 2007 12:55 PM To: sqlite-users@sqlite.org Subject: AW: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() I'd suggest putting this into the documentation of sqlite3_last_insert_rowid(), that the call is not reliable in scenarios such as this one. -Ursprüngliche Nachricht- Von: D. Richard Hipp [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 28. Oktober 2007 17:48 An: sqlite-users@sqlite.org Betreff: Re: AW: AW: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid() On Oct 28, 2007, at 10:59 AM, Michael Ruck wrote: > Yes, I am well aware of this possibility as I've written in my > initial mail. > It just doesn't fit with the > description of sqlite3_last_insert_rowid() in my understanding. I > think this > is a bug - either in the documentation > or in the implementation. sqlite3_last_insert_rowid() should return > the > correct id, no matter what and it doesn't. > Consider this scenario: CREATE TABLE ex1(id INTEGER PRIMARY KEY, b UNIQUE, c UNIQUE); INSERT INTO ex1 VALUES(1,1,1); INSERT INTO ex1 VALUES(2,2,2); INSERT INTO ex1 VALUES(3,3,3); Now you do your INSERT OR IGNORE: INSERT OR IGNORE INTO ex1 VALUES(1,2,3); Three different constraints fail, one for each of three different rows. So if sqlite3_last_insert_rowid() were to operate as you suggest and return the rowid of the failed insert, when rowid would it return? 1, 2, or 3? D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] INSERT OR IGNORE and sqlite3_last_insert_rowid()
[Default] On Sat, 27 Oct 2007 16:26:36 +0200, "Michael Ruck" <[EMAIL PROTECTED]> wrote: >Hi, > >I have a table of unique values in the following format: > >CREATE TABLE categories (id INTEGER PRIMARY KEY, category UNIQUE TEXT) > >I want inserts into this table to succeed, even though the corresponding >entry already exists. So I use inserts in the following format: > >INSERT OR IGNORE INTO categories VALUES (NULL, ?) > >However, if I follow this successful execution with a call to >sqlite3_last_insert_rowid() I don't get the rowid of row, which caused the >insert to be ignored, but one I preformed previously (which doesn't >necessarily have anything to do with this one.) This causes some relations >in my database model to break. > >I know I could use INSERT OR FAIL and a subsequent SELECT, but that seems >awkward and like unnecessary code bloat to me. Additionally I kind of think, > >this breaks the description and sense of sqlite3_last_insert_rowid(). > >SQlite version used is 3.3.16. > >Is this intentional? Any suggestions or should I file a ticket for this? > >Thanks! >Mike You supply NULL for the primary key, which in this case means SQLite will make up a new id for you. http://www.sqlite.org/lang_createtable.html : Specifying a PRIMARY KEY normally just creates a UNIQUE index on the corresponding columns. However, if primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. (Except for this one case, SQLite ignores the datatype specification of columns and allows any kind of data to be put in a column regardless of its declared datatype.) If a table does not have an INTEGER PRIMARY KEY column, then the B-Tree key will be a automatically generated integer. The B-Tree key for a row can always be accessed using one of the special names "ROWID", "OID", or "_ROWID_". This is true regardless of whether or not there is an INTEGER PRIMARY KEY. An INTEGER PRIMARY KEY column can also include the keyword AUTOINCREMENT. The AUTOINCREMENT keyword modified the way that B-Tree keys are automatically generated. Additional detail on automatic B-Tree key generation is available separately. http://www.sqlite.org/autoinc.html : When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. To specify a ROWID manually, just include it in the list of values to be inserted. For example: CREATE TABLE test1(a INT, b TEXT); INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello'); If no ROWID is specified on the insert, 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. And: If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. By supplying NULL as the key (ROWID) you actually don't specify a value, so SQLite creates a new row with a new id. If you want category to be unique, you will have to specify a UNIQUE constraint for it. HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
