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
