Tell HN: SQLite3 does have something like stored procedures

5 pointscryptonector5 comments

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 (...), ...;

  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.

View on HN

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;

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;

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;