Re: [sqlite] Help Using RowID
Gerry Snyder wrote: > I am not sure what all this means when taken together. > > It sounds as if defining id as shown above is unnecessary, since it is > just an alias for ROWID, and if one is stable the other has to be. Correct? > > So now I can't interpret "ROWID can change" as meaning anything other > than "the ROWID supplied by SQLite while doing an insertion, if none is > supplied by the user, can change at any time." Is this correct? > > My little Tcl/Tk routines for displaying and managing SQLite files have > always assumed ROWID is a safe stable way of determining a row. Have I > missed something? (again?) If you want to truly be safe, then *never* use ROWID (that is, ignore its existence), and *always* declare your own primary key, even if that is just an integer, and always reference your explicit primary key rather than ROWID. This way, what columns exist and their values are always controlled by you, and moreover your schemata would then be more portable between different DBMSs. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
D. Richard Hipp wrote: > On Sep 6, 2008, at 2:50 AM, Scott Hess wrote: > > >> On Fri, Sep 5, 2008 at 11:31 PM, jonwood <[EMAIL PROTECTED]> >> wrote: >> >>> But the documentation states the ROWID >>> can change at any time so I'm not sure what I can do safely. >>> >> Do this: >> >> CREATE TABLE t ( >> id INTEGER PRIMARY KEY, >> a TEXT NOT NULL, >> b INTEGER NOT NULL, >> UNIQUE (a, b) >> ); >> >> (a,b) will be just as unique as in the first case, but now you can use >> id as a stable alias for rowid >> > > I promise that INTEGER PRIMARY KEY will always be an alias for the > rowid in SQLite. This will not change. > I am not sure what all this means when taken together. It sounds as if defining id as shown above is unnecessary, since it is just an alias for ROWID, and if one is stable the other has to be. Correct? So now I can't interpret "ROWID can change" as meaning anything other than "the ROWID supplied by SQLite while doing an insertion, if none is supplied by the user, can change at any time." Is this correct? My little Tcl/Tk routines for displaying and managing SQLite files have always assumed ROWID is a safe stable way of determining a row. Have I missed something? (again?) Thanks, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
On Sat, Sep 6, 2008 at 7:44 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > On Sep 6, 2008, at 2:50 AM, Scott Hess wrote: >> As a bonus, if the implementation of SQLite changes, SQL itself won't, >> so id will _still_ be a valid integer primary key, even if the >> implementation detail of rowid changes. > > I promise that INTEGER PRIMARY KEY will always be an alias for the > rowid in SQLite. This will not change. :-). I'm just trying to discourage relying on implementation details when you can easily enough be explicit (in this case, there's no penalty for doing so). -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
On Sat, Sep 6, 2008 at 10:44 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > As a bonus, if the implementation of SQLite changes, SQL itself won't, > > so id will _still_ be a valid integer primary key, even if the > > implementation detail of rowid changes. > > I promise that INTEGER PRIMARY KEY will always be an alias for the > rowid in SQLite. This will not change. I recall there was an issue a while back where the rowid of a record could change if the table was vacuumed and it did not have an explicit INTEGER PRIMARY KEY field. If that's still (or ever was, assuming I'm remembering correctly) the case, then having an explicit INTEGER PRIMARY KEY is important if you want to reference the single-field rowid (by whatever name) and assume it won't change over the life of the record. Derrell ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
On Sep 6, 2008, at 2:50 AM, Scott Hess wrote: > On Fri, Sep 5, 2008 at 11:31 PM, jonwood <[EMAIL PROTECTED]> > wrote: >> I have a table where the primary key consists of two columns. I'm >> doing this >> because these two columns combined must be unique, while each >> column is not >> unique on its own. >> >> Because of the increased complexity of a dual-column primary key >> for some >> operations, I'd like to use ROWID. But the documentation states the >> ROWID >> can change at any time so I'm not sure what I can do safely. > > Do this: > > CREATE TABLE t ( > id INTEGER PRIMARY KEY, > a TEXT NOT NULL, > b INTEGER NOT NULL, > UNIQUE (a, b) > ); > > (a,b) will be just as unique as in the first case, but now you can use > id as a stable alias for rowid. There will be the same number of > btrees (one keyed by rowid/id for the table, one for the index on > a,b), and things should take the same amount of space (since id is an > alias for rowid, not a new column). > > As a bonus, if the implementation of SQLite changes, SQL itself won't, > so id will _still_ be a valid integer primary key, even if the > implementation detail of rowid changes. I promise that INTEGER PRIMARY KEY will always be an alias for the rowid in SQLite. This will not change. > D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
Scott Hess wrote: > > CREATE TABLE t ( > id INTEGER PRIMARY KEY, > a TEXT NOT NULL, > b INTEGER NOT NULL, > UNIQUE (a, b) > ); > > (a,b) will be just as unique as in the first case, but now you can use > id as a stable alias for rowid. There will be the same number of > btrees (one keyed by rowid/id for the table, one for the index on > a,b), and things should take the same amount of space (since id is an > alias for rowid, not a new column). > > As a bonus, if the implementation of SQLite changes, SQL itself won't, > so id will _still_ be a valid integer primary key, even if the > implementation detail of rowid changes. > Many thanks for the speedy response. I need a little while to absorb this and consider all the ramifications. But my initial thought is that this solves the issues in an easy and stable way. Thanks again. -- View this message in context: http://www.nabble.com/Help-Using-RowID-tp19343483p19343677.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help Using RowID
On Fri, Sep 5, 2008 at 11:31 PM, jonwood <[EMAIL PROTECTED]> wrote: > I have a table where the primary key consists of two columns. I'm doing this > because these two columns combined must be unique, while each column is not > unique on its own. > > Because of the increased complexity of a dual-column primary key for some > operations, I'd like to use ROWID. But the documentation states the ROWID > can change at any time so I'm not sure what I can do safely. Instead of doing this: CREATE TABLE t ( a TEXT NOT NULL, b INTEGER NOT NULL, PRIMARY KEY (a, b) ); Do this: CREATE TABLE t ( id INTEGER PRIMARY KEY, a TEXT NOT NULL, b INTEGER NOT NULL, UNIQUE (a, b) ); (a,b) will be just as unique as in the first case, but now you can use id as a stable alias for rowid. There will be the same number of btrees (one keyed by rowid/id for the table, one for the index on a,b), and things should take the same amount of space (since id is an alias for rowid, not a new column). As a bonus, if the implementation of SQLite changes, SQL itself won't, so id will _still_ be a valid integer primary key, even if the implementation detail of rowid changes. -scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help Using RowID
I have a table where the primary key consists of two columns. I'm doing this because these two columns combined must be unique, while each column is not unique on its own. Because of the increased complexity of a dual-column primary key for some operations, I'd like to use ROWID. But the documentation states the ROWID can change at any time so I'm not sure what I can do safely. The first operation I have in mind is to allow the user to edit a record with the option of modifying one of the columns that make up the primary key. The WHERE clause of my UPDATE statement would be far simpler if I could use the ROWID instead of two keys where one could've changed. But hwat if the ROWID has changed? The second operation I have in mind is to populate a Windows listview control with the contents of a table and store a unique ID in the DataItem of each row in the control. But if I use the ROWID as this ID, how do I know it will still be valid if I need to reference the row in the database. Can anyone offer any advice here? Thanks! -- View this message in context: http://www.nabble.com/Help-Using-RowID-tp19343483p19343483.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
