I have an app the has a STATIC SQlite database. as in when the app is run for the very first time, the DB is created. The app will update various rows, but at no time does it ever add a new row, or delete an existing row… It just may change the status of a specific field.
The question then is. It is correct that the ROWID value under these circumstances NEVER changes… correct?
Unless you specify otherwise, it’s the primary key, so yes.
Turns out the rowid seems to stay consisten in a static table, even if there is another PK defined
RowID without a field, maps to the physical row number. A field with autoincrement would be safer, but a virtual rowID may CHANGE; for example, if you delete rows and do a VACUUM, the remaining rows will get renumbered. There are very few reasons to not add a proper ID field and avoid things like that.
not an issue in this use case
i thought i read in the sqlite documentation that if you add an autoincrementing id field, it is just an alias for ROWID.
That was more or less true for a VERY long time
I think it was defining an INTEGER PRIMARY KEY (not necessarily auto incrementing)
But I could be wrong there
Now you can create a WITHOUT ROWID table and that wont necessarily be true
As SQLite is updated they have altered some former truisms
Like in later versions you CAN define tables to be STRICT and then SQLIte will strictly enforce data typing
I hope this make it clear:
Var db As New SQLiteDatabase
db.Connect
db.ExecuteSQL("CREATE TABLE noid (text TEXT, old_id INTEGER);")
Var lines() As String
lines.Add("INSERT INTO noid (text) VALUES ('AAA');")
lines.Add("INSERT INTO noid (text) VALUES ('BBB');")
lines.Add("INSERT INTO noid (text) VALUES ('CCC');")
lines.Add("INSERT INTO noid (text) VALUES ('DDD');")
lines.Add("UPDATE noid SET old_id = rowid;")
db.ExecuteSQL(String.FromArray(lines,EndOfLine.LF))
Var rs As RowSet = db.SelectSQL("SELECT rowid, * FROM noid")
lines.RemoveAll
For each row As DatabaseRow in rs
lines.Add(row.Column("rowid").StringValue+", "+_
row.Column("text").StringValue+", "+_
row.Column("old_id").StringValue)
Next
lines.Add(EndOfLine)
db.ExecuteSQL("DELETE FROM noid WHERE rowid=2; VACUUM")
rs = db.SelectSQL("SELECT rowid, * FROM noid")
For each row As DatabaseRow in rs
lines.Add(row.Column("rowid").StringValue+", "+_
row.Column("text").StringValue+", "+_
row.Column("old_id").StringValue)
Next
lines.Add(EndOfLine)
MessageBox(String.FromArray(lines, EndOfLine))
Quit
You miss the point
HIS USE CASE (which started this thread) will NOT INSERT or DELETE rows
EVER
Or Vacuum the database
EVER
So, while technically correct, IT DOESNT MATTER for Daves usage
No I didn’t.
The explanation is for people with doubts about how rowid works, as I see doubts going on all the time.
Another info: Not adding a proper ID column is considered a kind of a bad practice to be avoided, like a safe guard for the future.
Yes, if you add one, with PRIMARY KEY, rowid acts like an alias to that field.
i think you’ve got it backwards, but that’s just me being picky for the sake of being picky.
Item 2 from this page in the sqlite reference: 2. In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.
Also, for everyone’s interest, item 4 from the same page: 4. If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.
Also for anyone who is nerding out over SQLite, in the documentation for vacuum, under 3. How Vacuum Works: The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.
Yes, you are correct. You are repeating in extensive wordings what I wrote because something like that.
apologies. i’m just nerding out about a detail that doesn’t matter, because i think it’s an interesting topic. once i started looking, it was “ooh, that’s interesting” and “ooh, that’s also interesting”.
if we handn’t had this discussion, i’m willing to bet that 90% of the people reading the thread would have had no idea that this was even a thing.
That’s why I’ve take a time to say few words and show a case in code. Many people around the world got bitten by this “feature” over the years.