003_trigger.sql
1-- +goose up
2-- +goose StatementBegin
3
4-- cleanup old anonymous user records if no registration in 30 days
5CREATE TRIGGER IF NOT EXISTS anon_cleanup AFTER INSERT ON users
6 BEGIN
7 DELETE FROM users WHERE email IS NULL AND handle IS NULL AND ctime < datetime('now', '-30 days');
8 END;
9
10-- auto update modified time
11CREATE TRIGGER IF NOT EXISTS adjust_mtime AFTER UPDATE ON users
12 BEGIN
13 UPDATE users SET mtime=datetime('now') WHERE id=old.id;
14 END;
15
16-- create registered user views
17CREATE VIEW IF NOT EXISTS users_registered AS SELECT * FROM users WHERE email IS NOT NULL OR handle IS NOT NULL;
18
19-- create normalized handle and email view
20CREATE VIEW IF NOT EXISTS users_normalized AS SELECT id, lower(email) AS email, lower(handle) AS handle, password_hash, metadata, ctime, mtime FROM users;
21
22-- +goose StatementEnd
