Thank you Nico! for a general case, I would need to persist the counter into a table (for a specified sequencer) and doing the nextval inside a mutex lock
Is it possible to insert/ select from a UDF if the statements are serialized? or should I use the virtual tables? (should we store the sequencers in a different DB?) Le mer. 2 août 2017 à 19:46, Nico Williams <n...@cryptonector.com> a écrit : > On Wed, Aug 02, 2017 at 05:41:38PM +0100, Simon Slavin wrote: > > On 2 Aug 2017, at 5:35pm, Nico Williams <n...@cryptonector.com> wrote: > > > On Wed, Aug 02, 2017 at 04:56:34PM +0100, Simon Slavin wrote: > > >> Can someone explain ? > > > > > > They make it easy to have N tables with the same rowid namespace, for > > > example. So you could do something like: > > > > > > ... > > > > Okay, I understand that. Thanks, Nico. > > > > So the reason I didn’t understand the need for sequences is that > > someone who had been using SQLite for a long time would never come up > > with the concept. It’s just not needed. You’d just create individual > > tables, each with their own AUTOINC key as normal, then key into them > > using a VIEW or FOREIGN KEY. The problem solved by SEQUENCEs never > > arises. > > Sometimes you have external constraints on your schema and so don't have > the freedom to do what you propose. I guess it can happen that you've > never had to experience that, but I have. > > > This gives us a problem with Peter’s original question, because it > > seems unlike that implmenting sequences with SQLite is common enough > > that we have a ready solution. > > Well, if no one's done the UDF thing, maybe OP can do it and maybe open > source it. I mean, it's pretty simple. Alternatively OP can use > triggers as discussed. > > Another thing I often do with SQLite3 is to create VIEWs with INSTEAD OF > triggers that do the right thing. Something like: > > CREATE TABLE sequences (...); -- see previous post > CREATE TABLE real_thing (...); > CREATE VIEW thing AS > SELECT * FROM real_thing; > CREATE TRIGGER thing_ins INSTEAD OF INSERT ON thing > FOR EACH ROW > BEGIN > UPDATE sequences > SET last = last + 1 > WHERE name = 'foo'; > INSERT INTO real_thing (...) > SELECT (SELECT last FROM sequences WHERE name = 'foo'), > NEW.column_1, ...; > END; > CREATE TRIGGER thing_upd INSTEAD OF UPDATE ON thing > FOR EACH ROW > BEGIN > UPDATE real_thing > SET ... > WHERE ...; > END; > CREATE TRIGGER thing_del INSTEAD OF DELETE ON thing > FOR EACH ROW > BEGIN > DELETE FROM real_thing > WHERE ...; > END; > > I often basically use VIEWs in SQLite3 as a form of SQL-coded stored > procedures, with NEW.* / OLD.* being the "function"'s arguments. > > This can get very verbose and somewhat tedious though. I've used SQL to > generate all of these VIEWs and INSTEAD OF TRIGGERs in order to reduce > the amount of code to hand-maintain. (I also do similar things with PG: > https://github.com/twosigma/postgresql-contrib .) > > Since it's not possible to have an INSERT output a result, nor can you > have a SELECT on such a VIEW have a side-effect, it's not possible to > write a sequences VIEW that you can then use like this: > > -- Doesn't work because SELECT on VIEWs can't have side-effects (not > -- without side-effect-having UDFs in the VIEW's definition): > INSERT INTO real_thing (...) > SELECT (SELECT next FROM seq WHERE name = 'foo'), ...; > > Without a next_serial() UDF one has to resort to the triggers discussed > earlier. So there's a limit to what one can do with this technique if > you also appreciate and want elegance (which I do). > > An actual next_serial()-type function would be very nice. > > In short, SQLite3 basically has stored procedures. It's just missing > syntactic sugar for them. > > It really does help to not be afraid of using SQL as a programming > language. Many are allergic to SQL as a programming language -- those > poor souls often end up using ORMs and pay the price for it later. > > Nico > -- > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
