3 ms·

> Row id > We can uniquely identify an object, even if it is deleted later, without worrying a new object could occupy the same rowid I don’t know anything ab

by tirrex 6y ago

> Row id > We can uniquely identify an object, even if it is deleted later, without worrying a new object could occupy the same rowid I don’t know anything about mobile and not sure if it applies here but if you call vacuum on sqlite, it rewrites row ids. Is there a way to prevent that? If you have a column “PRIMARY KEY INTEGER”, it is your row id and it won’t change on vacuum, otherwise if you rely on automatic row id, you may have a surprise later.

sltkr 6y ago
It's not just on vacuum. Rowids are reused whenever stuff is deleted from the end of the table: sqlite> CREATE TABLE t(x); sqlite> INSERT INTO t(x) VALUES ('foo'), ('bar'); sqlite> SELECT rowid, x FROM t; 1|foo 2|bar sqlite> DELETE FROM t WHERE x='bar'; sqlite> INSERT INTO t(x) VALUES ('baz'); sqlite> SELECT rowid, x FROM t; 1|foo 2|baz This is true even if rowid is aliased by an INTEGER PRIMARY KEY. Whenever a row is inserted, the new rowid is simply 1 greater than the largest rowid in use (or 1 for an empty table). But Dflag is using: rowid INTEGER PRIMARY KEY AUTOINCREMENT which prevents ids from being reused. So this isn't actually a problem for them, but the documentation could be clearer about it.
- tirrex 6y ago
  > This is true even if rowid is aliased by an INTEGER PRIMARY KEY. Thanks. Just to be clear, I think this is the scenario when you don’t set your primary key but other columns in your insert statement. CREATE TABLE t(x INTEGER PRIMARY KEY, y); INSERT INTO(y) VALUES(‘value’); Otherwise, if you set primary key in your insert statements, you’ll be fine I guess. I’m not worried about reusing ids but if ids change e.g on vacuum, it is a disaster. Because people use row id(via last_insert_row_id()) assuming it won’t change but vacuum changes it and now you have a “dangling id”, if you fetch data again with that id, either you won’t find the row or get some other random row.
liuliu 6y ago
I may omit details in that paragraph, sorry! Autoincrement was used to prevent rowid reuse (and it is explicit primary key, where the properties you marked as primary in flatbuffers schema is just unique index), SQLite documentation claims that should be enough: https://sqlite.org/autoinc.html https://sqlite.org/autoinc.html
- tirrex 6y ago
  Thank you, sounds great.

by tirrex 6y ago

sltkr 6y ago

It's not just on vacuum. Rowids are reused whenever stuff is deleted from the end of the table: sqlite> CREATE TABLE t(x); sqlite> INSERT INTO t(x) VALUES ('foo'), ('bar'); sqlite> SELECT rowid, x FROM t; 1|foo 2|bar sqlite> DELETE FROM t WHERE x='bar'; sqlite> INSERT INTO t(x) VALUES ('baz'); sqlite> SELECT rowid, x FROM t; 1|foo 2|baz This is true even if rowid is aliased by an INTEGER PRIMARY KEY. Whenever a row is inserted, the new rowid is simply 1 greater than the largest rowid in use (or 1 for an empty table). But Dflag is using: rowid INTEGER PRIMARY KEY AUTOINCREMENT which prevents ids from being reused. So this isn't actually a problem for them, but the documentation could be clearer about it.

tirrex 6y ago
> This is true even if rowid is aliased by an INTEGER PRIMARY KEY. Thanks. Just to be clear, I think this is the scenario when you don’t set your primary key but other columns in your insert statement. CREATE TABLE t(x INTEGER PRIMARY KEY, y); INSERT INTO(y) VALUES(‘value’); Otherwise, if you set primary key in your insert statements, you’ll be fine I guess. I’m not worried about reusing ids but if ids change e.g on vacuum, it is a disaster. Because people use row id(via last_insert_row_id()) assuming it won’t change but vacuum changes it and now you have a “dangling id”, if you fetch data again with that id, either you won’t find the row or get some other random row.

liuliu 6y ago

I may omit details in that paragraph, sorry! Autoincrement was used to prevent rowid reuse (and it is explicit primary key, where the properties you marked as primary in flatbuffers schema is just unique index), SQLite documentation claims that should be enough: https://sqlite.org/autoinc.html https://sqlite.org/autoinc.html

tirrex 6y ago
Thank you, sounds great.