Re: [sqlite] Explanation about ROWID requested
Hello Dr. Hipp, On 1 may 2004, at 8:38, D. Richard Hipp wrote: When you do a "SELECT *", the results contain only columns that are explicitly declared in the CREATE TABLE statement. If you have declared an INTEGER PRIMARY KEY column, then the rowid will appear under that column name. If there is no INTEGER PRIMARY KEY, then the rowid will not be a part of the result. The ".dump" command works by doing a "SELECT *". So if you do not have an INTEGER PRIMARY KEY in your table declaration, the rowid will not be part of the saved data and will be lost when the table is reconstructed. This is the explanation I was hoping for. Excellent! BTW... I think that this information should be mentioned in the documentation, FAQ, or Wiki (why not all 3! ;-) If nobody is against it, I can take care of the Wiki page. Is that OK with you? I would like to thank everyone else that answered my post. I truly appreciate it. Best regards, -- Tito - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Explanation about ROWID requested
Every row of every table has a ROWID. The ROWID can be called "ROWID", "_ROWID_", and/or "OID". All three names refer to the same value and can be used interchangably. But if you declare a column with any of those names, the name refers to your declared column, not the actual ROWID. This is similar to how an local automatic variable will hide a global variable by the same name in C/C++. If you declare a column to be an INTEGER PRIMARY KEY, then that column becomes another alias for the ROWID. Examples: Given a table of the following form: CREATE TABLE t1(x INTEGER PRIMARY KEY, y); All of the following statements are equivalent: SELECT rowid, y FROM t1; SELECT _rowid_, y FROM t1; SELECT oid, y FROM t1; SELECT x, y FROM t1; As far as SQLite is concerned, "rowid", "_rowid_", "oid", and "x" are just different names for the same value. These statements are also all equivalent: INSERT INTO t1(rowid,y) VALUES(1,2); INSERT INTO t1(_rowid_,y) VALUES(1,2); INSERT INTO t1(oid,y) VALUES(1,2); INSERT INTO t1(x,y) VALUES(1,2); Any place you can use the column "x" associated with table "t1", you can substitute "rowid", "_rowid_", or "oid" and get the same result. When you do a "SELECT *", the results contain only columns that are explicitly declared in the CREATE TABLE statement. If you have declared an INTEGER PRIMARY KEY column, then the rowid will appear under that column name. If there is no INTEGER PRIMARY KEY, then the rowid will not be a part of the result. The ".dump" command works by doing a "SELECT *". So if you do not have an INTEGER PRIMARY KEY in your table declaration, the rowid will not be part of the saved data and will be lost when the table is reconstructed. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Explanation about ROWID requested
Tito Ciuro <[EMAIL PROTECTED]> writes: > Hello, > > A few days ago I posted a question and I haven't seen any comments so > far. I'm really curious about ROWID's volatility. How can I make sure that > ROWIDs do not get re-initialized? I'm posting the message once again hoping > that someone will explain how I should properly use ROWIDs. If you declare an INTEGER PRIMARY KEY then it will be used as the ROWID: sqlite> CREATE TABLE test ...> ( ...> id INTEGER PRIMARY KEY, ...> t TEXT ...> ); sqlite> INSERT INTO test VALUES (23, 'hello'); sqlite> INSERT INTO test VALUES (42, 'world'); sqlite> SELECT id, t FROM test; id = 23 t = hello id = 42 t = world sqlite> SELECT ROWID, t FROM test; ROWID = 23 t = hello ROWID = 42 t = world sqlite> .dump BEGIN TRANSACTION; CREATE TABLE test ( id INTEGER PRIMARY KEY, t TEXT ); INSERT INTO test VALUES(23,'hello'); INSERT INTO test VALUES(42,'world'); COMMIT; sqlite> Note that whether I select 'id' or 'ROWID', I get the same value. Also note that the .dump command generates a script that will recreate exactly the same data as I did initially, so ROWID will have the same value after a re-create as it does initially. Derrell - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Explanation about ROWID requested
Tito Ciuro wrote: Hello, A few days ago I posted a question and I haven't seen any comments so far. I'm really curious about ROWID's volatility. How can I make sure that ROWIDs do not get re-initialized? I'm posting the message once again hoping that someone will explain how I should properly use ROWIDs. Thanks again. Best regards, -- Tito Hi Tito, AFAIK the ROWID is used internally as a means to organize the individual rows of a table in storage. Basically it is an integer, which is different between different rows. However, if a table features an INTEGER PRIMARY KEY column (which obviously is different between different rows too), the database engine might use that value instead if an internally only handled value, thereby saving one stage of indirection. Of course, when writing such a table into an external format and rereading it, these values shall not change - as it is with all values saved via ".dump" and reread (if they would, nobody would ".dump" call a backup utility, but "obfuscator" instead :) I even think referencing the ROWID, even when possible, is IMHO a sign of bad table layout; why would you even want it when you could easily reference an "official" SQL column, without any tradebacks. (I even think that exposing the ROWID of a column via SQL is a bad thing(tm), but then I don't have to use it.) - if I declare ROWID as INTEGER PRIMARY KEY in the CREATE TABLE statement, would that be enough to guarantee that the ROWID is safely dumped and reimported? > [ ... ] > - If this is the case I guess I would have to create my own unique > column (i.e. MyUniqueUID and type INTEGER PRIMARY KEY, right?) If I understand you right: you define a column named "ROWID"? Why would you do that? Just call it ID, and you are better off (and even save 3 keystrokes) - The part that confuses me about Dr. Hipp's statement is this: 'If you use an INTEGER PRIMARY KEY, the ".dump" command will preserve the values and your references will not be broken by the export.': this is the reason I posted my first question above... When importing a table from the .dump output, the database engine has no chance to guess what ROWID has been used prior to ".dump". Therefore, it cannot guarantee accessing the same RowID to the same row again. Consider this example: create table a (i1, i2); insert into a values (1, 2); insert into a values (1, 3); insert into a values (1, 4); delete from a where i2 = 3; .dump This creates the following output: BEGIN TRANSACTION; create table a (i1, i2); INSERT INTO a VALUES(1,2); INSERT INTO a VALUES(1,4); COMMIT; If this is all information the DB engine gets when reconstruction the table, it *cannot* know which ROWIDs have been assigned to each row. If you use an INTEGER PRIMARY KEY the situatoin is different: the DB engine does use the appr. value of this column as the ROWID, knowing that it will be unique and therefore meets all requirements for an ROWID. It doesn't have to assign ROWIDs on its own. But anyway: As far as I am concerned, I would want to see this ROWID stuff in the tips, tricks, and optimizations section of the documentation (and ROWID not accessible via the ROWID/OID/_ROWID_ columns.) Any comments? Thanks! -- Tito Hope it helped, /eno - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
