On Wed, Aug 02, 2017 at 04:56:34PM +0100, Simon Slavin wrote:
> On 2 Aug 2017, at 4:54pm, Peter Da Silva <peter.dasi...@flightaware.com>
> wrote:
> > Can’t you do the same basic logic then use (SELECT value FROM
> > super_sequences WHERE id=’SEQ_1’) instead of SEQ_1.nextval?
>
> Actually, I don’t understand how sequences are superior to normal use
> of an AUTOINC column. Can someone explain ?
Think of sequences as non-deterministic functions. (They are actually
deterministic, but using hidden state, so from the engine's perspective
they are non-deterministic.)
They make it easy to have N tables with the same rowid namespace, for
example. So you could do something like:
CREATE SEQUENCE foo START WITH 0 INCREMENT BY 5;
CREATE TABLE t1 (rowid integer primary key default (next_serial(foo)), ...);
CREATE TABLE t2 (rowid integer primary key default (next_serial(foo)), ...);
CREATE TABLE t3 (rowid integer primary key default (next_serial(foo)), ...);
and have those three tables share a rowid namespace -- there will be no
collisions between them. The way one might handle this use case in
SQLite3 is to create one master table with autoincrement and then have
foreign key references onto it from the others; you'd insert into the
master table first then into the referring table using
last_insert_rowid() or a sub-query that returns the same.
There are other workarounds too, as others have mentioned (triggers,
...).
One could always create a UDF that does an UPDATE and SELECT behind the
scenes. Then one would create a table like so:
CREATE TABLE sequences (name TEXT PRIMARY KEY,
start INTEGER DEFAULT (0),
increment INTEGER DEFAULT (1),
last INTEGER DEFAULT (0)) WITHOUT ROWID;
and one would create sequences by INSERTing rows into that table, and
the UDF would just do the obvious
UPDATE sequences SET last = last + 1 WHERE name = _name_argument;
then
SELECT last FROM sequences WHERE name = _name_argument;
and return that.
Such a UDF probably exists as open source soemwhere, so OP should look
around for it.
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
