On 2017-08-02 20:24, Nico Williams wrote:
On Wed, Aug 02, 2017 at 07:48:52PM +0300, Alek Paunov wrote:
On 2017-08-02 18:23, Sylvain Pointeau wrote:
...
CREATE SEQUENCE IF NOT EXISTS SEQ_1 START WITH 12123;
insert into MYTABLE(SPECIFIED_NUMBER, OTHERINFO) values (seq_1.nextval,
'other info')
BTW, your request is somewhat related with the brand new union-vtab SQLite
extension [1] (for optimized union view of identical tables) - if we have
the basic PostreSQL nextval in SQLite, the following pattern would become
possible:
I find the union vtab thing mostly not useful because it requires
constraining the tables to be union'ed to have distinct ranges of
rowids. This is of too narrow utility.
Indeed - My assertion was simply wrong because of the ranges
requirement. Query in the VTable DDL also was totally not what the
implementation expects - sorry for the noise :-(.
PostgreSQL-style sequence support would be much more general.
If the union vtab thing is aiming to make it easier to implement
something like table inheritance, I'll warn you right off that
PostgreSQL's INHERIT is utterly useless -- do not copy it.
I've implemented "inheritance" with triggers to map DMLs on "derived"
tables onto "base" tables. That works and is much more general. If you
need a rowid, however, the triggers have to do more work, first acquring
the rowid from the base table, then setting it on the derived table
rows, and this can get tricky.
Yes. I meant exactly that usecase - where many logically "subclass"
tables share common "address" (rowid) space, so that, any object (or the
rest of the DB) could "reference" objects in the whole hierarchy (like
unified value references in script languages data-models).
Of course, even without sequence build-in, both your suggestions: main
"object" table with triggers on every subclass table or simple UDF
next_serial are enough to build such representation.
King Regards,
Alek
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
