3 ms·

Tell HN: SQLite3 does have something like stored procedures

5 points by cryptonector 4y ago

FYI, `INSTEAD OF` triggers on views can function like stored procedures in SQLite3. It's not exactly the same, no, but you still get to have DMLs "stored" in the DB. For example, I'm not sure how to return results from an `INSTEAD OF` trigger (that is, if I write `INSERT INTO <view> (...) SELECT ... RETURNING ...;`, what will be returned? Apparently what's returned is the values of the NEW row, while one might think that if the view ends in a `SELECT` then maybe that would be used as the source for the `RETURNING` (but, no). Even with limitations, `INSTEAD OF` triggers as an approximation of stored procedures is very powerful. You'd do something like: CREATE VIEW IF NOT EXISTS things AS ...; -- -- INSERTs on `things` function like SPs -- where values furnished for columns -- function as SP input parameters. CREATE TRIGGER IF NOT EXISTS create_thing INSTEAD OF INSERT ON things FOR EACH ROW BEGIN ... END; and to invoke such a "stored procedure" you'd INSERT INTO things (...) VALUES (...), ...; or INSERT .. SELECT ..; This language is Turing-complete. You can also loop via recursive triggers. And you can use `WHERE` clauses (and `OR IGNORE`) on statements in the bodies of `INSTEAD OF` triggers to implement conditionals. Most of this is taken from a comment I just wrote, https://news.ycombinator.com/item?id=31912640 but I've written this sort of thing before elsewhere too, including on the old sqlite3-users mailing list years ago. I'm pretty sure lots of you know about this -- it's really quite obvious.

bbkane 4y ago
Hi! I'm from the other comment chain you originally posted this on. One question - how should I keep track of generated primary key IDs for future inserts? The best I could come up with is: CREATE TRIGGER role_user_view_trigger INSTEAD OF INSERT ON role_user_view FOR EACH ROW BEGIN INSERT INTO role(name) VALUES(new.role_name); INSERT INTO user(name) VALUES(new.user_name); INSERT INTO role_user(role_id, user_id, created_at) VALUES( (SELECT id FROM role WHERE name = new.role_name), (SELECT id FROM user WHERE name = new.user_name), "now" ); END; Full code at https://gist.github.com/bbkane/ed385787f06efe4473f3cb7a3b32c8ac https://gist.github.com/bbkane/ed385787f06efe4473f3cb7a3b32c... Do you know of a better way than those select statements (which I presume are slow) to get the IDs?
- cryptonector 4y ago
  First, you should know that you should only use single quotes for quoting strings, even in SQLite3 which allows double quotes. Second, yeah, `last_insert_rowid()` doesn't work in this case, but what you might do is a) have a timestamp column and/or maybe a transaction ID column as well that you set in the INSERTs in the trigger body, b) maybe have audit/log tables that you insert into in those same trigger bodies, where you might use `last_insert_rowid()`. Third, I urge you to think about why you care about the inserted row IDs. If you're doing INSERTs w/o `OR IGNORE`, then if your INSERT into the view succeeds then you know you can SELECT to see what IDs you got, then COMMIT. If you're doing `OR IGNORE`, then maybe you don't care whether you got IDs assigned just now or in the past, but you still want to know what IDs those rows have, so you select for them. Lastly, I'd think about whether you want row IDs at all. I mean, they can be useful. And when it comes to things like "users", you generally want some sort of internal identifier other than the name. Like, if you rename users or roles often (but, you really should not allow that!) then using row IDs in the foreign keys means you don't have to worry about cascading renames. But you might be able to get away w/o role IDs. Also, you might find it better to have a single table for all "numeric ID" assignments, and then you could make your users and roles tables not have an INTEGER PRIMARY KEY, just the name as the PRIMARY KEY and a numeric ID column as a foreign key referencing that one single table. E.g., CREATE TABLE IF NOT EXISTS IDs (id INTEGER PRIMARY KEY, kind TEXT NOT NULL, name TEXT NOT NULL, UNIQUE (kind, name)); CREATE TABLE IF NOT EXISTS user (name TEXT PRIMARY KEY, id INTEGER FOREIGN KEY REFERENCES IDs (id)) WITHOUT ROWID; CREATE TABLE IF NOT EXISTS role (name TEXT PRIMARY KEY, id INTEGER FOREIGN KEY REFERENCES IDs (id)) WITHOUT ROWID; then in your INSTEAD OF INSERT trigger you'd - insert into IDs twice to get IDs for the user and role - insert into user - insert into role like this: CREATE TABLE role_user ( role_kind TEXT NOT NULL DEFAULT('role') CHECK(role_kind = 'role'), user_kind TEXT NOT NULL DEFAULT('user') CHECK(user_kind = 'user'), role_name TEXT, -- should be NOT NULL bc SQLite3 -- does not force that in PKs user_name TEXT, -- ditto PRIMARY KEY (role_name, user_name), FOREIGN KEY (role_kind, role_name) REFERENCES role(kind, name) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (user_kind, user_name) REFERENCES user(kind, name) ON DELETE CASCADE ON UPDATE CASCADE ) STRICT, WITHOUT ROWID; CREATE TRIGGER role_user_view_trigger INSTEAD OF INSERT ON role_user_view FOR EACH ROW BEGIN INSERT OR IGNORE INTO IDs(kind, name) VALUES('role', NEW.role_name), VALUES('user', NEW.user_name); INSERT OR IGNORE INTO role(name, id) SELECT NEW.role_name, id FROM IDs WHERE kind = 'role' AND name = NEW.role_name; INSERT OR IGNORE INTO user(name, id) SELECT NEW.user_name, id FROM IDs WHERE kind = 'user' AND name = NEW.user_name; INSERT OR IGNORE INTO role_user(role_name, user_name) VALUES(NEW.role_name, NEW.user_name); END; I think you really want `OR IGNORE` in all cases, as it makes this trigger idempotent. Want to know the numeric IDs assigned to the users and roles possibly created this way? Just SELECT for them!
zzo38computer 4y ago
I know that and I have done that many times in the past (it probably is really obvious, like you say). However, triggers cannot return result rows, and a SELECT query that a view is defined as cannot take parameters (although there is a work-around by using a virtual table which contains a single row whose value is whichever value it is constrained to be). There is also WITH RECURSIVE, which also has many uses.

5 points by cryptonector 4y ago

bbkane 4y ago

Hi! I'm from the other comment chain you originally posted this on. One question - how should I keep track of generated primary key IDs for future inserts? The best I could come up with is: CREATE TRIGGER role_user_view_trigger INSTEAD OF INSERT ON role_user_view FOR EACH ROW BEGIN INSERT INTO role(name) VALUES(new.role_name); INSERT INTO user(name) VALUES(new.user_name); INSERT INTO role_user(role_id, user_id, created_at) VALUES( (SELECT id FROM role WHERE name = new.role_name), (SELECT id FROM user WHERE name = new.user_name), "now" ); END; Full code at https://gist.github.com/bbkane/ed385787f06efe4473f3cb7a3b32c8ac https://gist.github.com/bbkane/ed385787f06efe4473f3cb7a3b32c... Do you know of a better way than those select statements (which I presume are slow) to get the IDs?

cryptonector 4y ago
First, you should know that you should only use single quotes for quoting strings, even in SQLite3 which allows double quotes. Second, yeah, `last_insert_rowid()` doesn't work in this case, but what you might do is a) have a timestamp column and/or maybe a transaction ID column as well that you set in the INSERTs in the trigger body, b) maybe have audit/log tables that you insert into in those same trigger bodies, where you might use `last_insert_rowid()`. Third, I urge you to think about why you care about the inserted row IDs. If you're doing INSERTs w/o `OR IGNORE`, then if your INSERT into the view succeeds then you know you can SELECT to see what IDs you got, then COMMIT. If you're doing `OR IGNORE`, then maybe you don't care whether you got IDs assigned just now or in the past, but you still want to know what IDs those rows have, so you select for them. Lastly, I'd think about whether you want row IDs at all. I mean, they can be useful. And when it comes to things like "users", you generally want some sort of internal identifier other than the name. Like, if you rename users or roles often (but, you really should not allow that!) then using row IDs in the foreign keys means you don't have to worry about cascading renames. But you might be able to get away w/o role IDs. Also, you might find it better to have a single table for all "numeric ID" assignments, and then you could make your users and roles tables not have an INTEGER PRIMARY KEY, just the name as the PRIMARY KEY and a numeric ID column as a foreign key referencing that one single table. E.g., CREATE TABLE IF NOT EXISTS IDs (id INTEGER PRIMARY KEY, kind TEXT NOT NULL, name TEXT NOT NULL, UNIQUE (kind, name)); CREATE TABLE IF NOT EXISTS user (name TEXT PRIMARY KEY, id INTEGER FOREIGN KEY REFERENCES IDs (id)) WITHOUT ROWID; CREATE TABLE IF NOT EXISTS role (name TEXT PRIMARY KEY, id INTEGER FOREIGN KEY REFERENCES IDs (id)) WITHOUT ROWID; then in your INSTEAD OF INSERT trigger you'd - insert into IDs twice to get IDs for the user and role - insert into user - insert into role like this: CREATE TABLE role_user ( role_kind TEXT NOT NULL DEFAULT('role') CHECK(role_kind = 'role'), user_kind TEXT NOT NULL DEFAULT('user') CHECK(user_kind = 'user'), role_name TEXT, -- should be NOT NULL bc SQLite3 -- does not force that in PKs user_name TEXT, -- ditto PRIMARY KEY (role_name, user_name), FOREIGN KEY (role_kind, role_name) REFERENCES role(kind, name) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (user_kind, user_name) REFERENCES user(kind, name) ON DELETE CASCADE ON UPDATE CASCADE ) STRICT, WITHOUT ROWID; CREATE TRIGGER role_user_view_trigger INSTEAD OF INSERT ON role_user_view FOR EACH ROW BEGIN INSERT OR IGNORE INTO IDs(kind, name) VALUES('role', NEW.role_name), VALUES('user', NEW.user_name); INSERT OR IGNORE INTO role(name, id) SELECT NEW.role_name, id FROM IDs WHERE kind = 'role' AND name = NEW.role_name; INSERT OR IGNORE INTO user(name, id) SELECT NEW.user_name, id FROM IDs WHERE kind = 'user' AND name = NEW.user_name; INSERT OR IGNORE INTO role_user(role_name, user_name) VALUES(NEW.role_name, NEW.user_name); END; I think you really want `OR IGNORE` in all cases, as it makes this trigger idempotent. Want to know the numeric IDs assigned to the users and roles possibly created this way? Just SELECT for them!

zzo38computer 4y ago

I know that and I have done that many times in the past (it probably is really obvious, like you say). However, triggers cannot return result rows, and a SELECT query that a view is defined as cannot take parameters (although there is a work-around by using a virtual table which contains a single row whose value is whichever value it is constrained to be). There is also WITH RECURSIVE, which also has many uses.