NEXT VALUE FOR in SQLite
(1) By Vladimir (I-Vladimir) on 2021-09-08 13:07:14 [link] [source]
Hi, I have a table with Name and Value fields. Can anyone suggest a script to create or auto-increment an existing counter record in just a single statement? Thanks
CREATE TABLE kv(name TEXT PRIMARY KEY, value INTEGER); CREATE INDEX kv_value ON kv(value); CREATE TRIGGER kv_autoinc AFTER INSERT ON kv WHEN new.value IS NULL BEGIN UPDATE kv SET value=(SELECT coalesce(max(value),0)+1 FROM kv) WHERE name=new.name; END;
Then when you insert a NULL
INSERT INTO kv(name) VALUES('first');
The trigger will automatically add in the next larger value, or 1 if the table is initially empty.
(3) By Vladimir (I-Vladimir) on 2021-09-08 14:25:21 in reply to 2 [source]
Thanks Richard, I probably forgot to indicate I would want to get inserted/updated value back as an unique counter to avoid concurrent updates. My app is multi-threaded and my current logic is in an exclusive transaction. Would want to eliminate that.
So do you want to create an incrementing counter, or do you want to update the value and get a returned value back? Those two are not the same.
For creating a counter, Richard's example will work a treat, and it can never return a value because it happens invisibly to the statements (i.e. In the background).
For the second option, should the counter increase by being updated? or upon inserting a new row?
If updating a value and getting the new value back, that can be achieved with this type of query - imaging there is a table "t" with an integer field "i" in record with "id" 5 that will be our update target:
UPDATE t SET i = IFNULL((SELECT MAX(i) FROM t),0) + 1 WHERE id = 5 RETURNING i
If it has to be an Insert query then a very similar (assuming id is also a Autoinc PK):
INSERT INTO t(i, x, y....) VALUES (IFNULL((SELECT MAX(i) FROM t),0) + 1, x-value, y-value, ... ) RETURNING id, i
BIG NOTE: You need a very recent release of SQLite to be able to use the "RETURNING" clause.
ALSO NOTE: I've not tested these, the principle is sound but there may be syntax errors. The docs are your friend.
But what are you going to do with it ? You don't need to know it for the next INSERT. Will you be using it for a FOREIGN KEY ?
Also, assuming that your application really is multi-threading and not just multi-processing, are your multiple threads all using the same connection to the database ? That's not considered safe unless you understand exactly how they interact. They should be using different connections.
(6) By Vladimir (I-Vladimir) on 2021-09-09 18:30:16 in reply to 5.1 [link] [source]
If not exists I have to add some min value, but that can be done in advance. In most cases I have to get a unique range of numbers for the counter (account). I can advance the counter value for the number items in the range and self generate numbers. The only issue if some values in the range jumps over the max range value and will not proper cycled. Don't think a single statement can accommodate such logic. But approach is very nice.
All threads using their own connection with FullMutex flag.
In your situation I might keep the counter in another table of the same database. Make a Config table, and in one column of one row, keep your counter.
UPDATE Config SET counter TO counter + 5 WHERE configItem='customers' RETURNING counter
This does the update in one operation and returns a value which lets you figure out the ids for the 5 rows you want to insert. it is ACID, and threadsaf, given that you are correctly using separate connections.