Modify rowid via C-API callback
(1) By Willem (sn0wbl1nd) on 2020-11-25 04:14:38 [link] [source]
Is it possible to modify the rowid in a rowid table during an insert through the C-API?
(2) By Gunter Hick (gunter_hick) on 2020-11-25 06:28:23 in reply to 1 [source]
Just specify a value for the rowid (or your declared alias) in the insert statement. Why would you want to ask SQLite to choose a rowid and then decide you want a different, specific value? Either you care about the value of rowid (then you should set it) or you don't (then you should leave it alone).
create temp table a (t text);
insert into a (rowid,t) values (17,'siebzehn');
select rowid,t from a;
(3.2) By Keith Medcalf (kmedcalf) on 2020-11-25 10:13:59 edited from 3.1 in reply to 2 [link] [source]
You should not do this because unless you have explicitly aliased the rowid otherwise the value is ephemeral and subject to change at whim. There are actually specific conditions on which the rowid will change, but this is immaterial. To an uneducated consumer it will appear that the rowid has magically changed for no apparent reason and one should never suggest a solution which will result in generating a complaint a dozen messages on that you could have avoided by simply giving a more correct answer.
Define a column alias for the rowid and set it in the insert statement.
But to answer the question asked, No. If you want a specific value you must set it during the insert. You may have an AFTER INSERT trigger which updates it after insert however that is the only control you have. And the value is only stable if the rowid is aliased, otherwise it is subject to change on a whim.
(4) By Keith Medcalf (kmedcalf) on 2020-11-25 10:18:25 in reply to 3.2 [link] [source]
In theory you could write an after update trigger that called a user defined function that set the rowid aliased column to whatever you want -- but if you have a function that can do that why go to all the bother and not just set the value directly in the first instance rather than go through all the convoluted extra complication?
(5) By Gunter Hick (gunter_hick) on 2020-11-25 10:56:47 in reply to 4 [link] [source]
Should that not be an AFTER INSERT trigger?
(6) By Gunter Hick (gunter_hick) on 2020-11-25 11:07:57 in reply to 3.2 [link] [source]
Sorry I was not specific enough. Regarding rowids there are 4 "safe" Possibilities:
a) you neither need nor want rowids: declare a WITHOUT ROWID table
b) you don't care: do not declare an alias an do not use the built-in names
c) you don't care about the specific value: declare an alias, but use it only for foreign keys with appropriate constraints
d) you care about a specific value: declare an alias and always supply a value on INSERT and be prepared to handle conflicts
The example I gave illustrates that you can set it even without explicit declaration. I did not mean to imply that it would be prudent to do so.
(7) By Keith Medcalf (kmedcalf) on 2020-11-25 11:40:28 in reply to 5 [link] [source]
Yes, you are correct, of course. Something got muddled -- PBKAC
(8) By Willem (sn0wbl1nd) on 2020-11-25 18:00:27 in reply to 3.2 [link] [source]
Thank you for all the answers - that helped. We use sqlite as a buffer and interface, relying largely on the preupdate hook, but also authorizer and commit. On these tables we enforce presence of INTEGER PRIMARY KEY.
The problem I am trying to solve is how to create rows that are guaranteed to be unique across different locations that are not in direct communication.
The elegant answer I believe is to create WITHOUT ROWID tables and use an integer column and a location column as index. The location column would be set to the designation of the location. This would require some work, so I was wondering if I could dedicate the upper 32bits of the rowid to a location number, effectively creating subranges specific to each node.
All this is moot since I cannot do this with callbacks. The triggers are an option and I may look into that as a short-term fix.
(9) By Keith Medcalf (kmedcalf) on 2020-11-25 19:44:54 in reply to 8 [link] [source]
Create the table using AUTOINCREMENT. Before you add the first tuple set the sqlite_sequence table to the "start value" for that instance. After you combine the tables, DO NOT INSERT ANY MORE ROWS (that is, keep the instance databases isolated from each other).
(10.2) By Gunter Hick (gunter_hick) on 2020-11-26 08:28:28 edited from 10.1 in reply to 8 [link] [source]
Maybe try this (use a lower left shift for testing):
CREATE TABLE whereami (id INTEGER PRIMARY KEY, name TEXT UNIQUE, low INT AS (id<<32), high INT AS (id<<33)-1);
INSERT INTO whereami (id, name) values (4711,'Cologne');
CREATE TABLE something (id INTEGER PRIMARY KEY AUTOINCREMENT, thing TEXT);
INSERT INTO something SELECT low,'first record' from whereami where name = 'Cologne';
CREATE TRIGGER check_some BEFORE INSERT ON something BEGIN
SELECT RAISE(ROLLBACK,'table full') FROM whereami w, sqlite_sequence s WHERE w.name='Cologne' AND NEW.id between w.low and w.high AND s.name='something' and s.seq >= w.high;
INSERT INTO something VALUES (NULL, 'local record');
INSERT INTO something VALUES (1, 'foreign record');