126.sql (3365B)
1 -- +migrate Up 2 DROP TRIGGER links_after_insert; 3 DROP TRIGGER links_before_update; 4 DROP TRIGGER links_before_update_soft_delete; 5 DROP INDEX links_shorthand_uniq; 6 7 create table links_tmp ( 8 id INTEGER not null primary key, 9 uuid VARCHAR(100) not null unique, 10 url VARCHAR(255) not null unique, 11 title VARCHAR(255) not null, 12 description TEXT not null, 13 signed_certificate TEXT, 14 owner_user_id INTEGER NULL, 15 visited_at DATETIME, 16 created_at DATETIME default CURRENT_TIMESTAMP not null, 17 deleted_at DATETIME, 18 updated_at DATETIME default CURRENT_TIMESTAMP, 19 shorthand VARCHAR(50) UNIQUE, 20 CONSTRAINT links_owner_user_id_fk 21 FOREIGN KEY (owner_user_id) 22 REFERENCES users (id) 23 ON DELETE CASCADE 24 ON UPDATE CASCADE); 25 26 INSERT INTO links_tmp (id, uuid, url, title, description, visited_at, created_at, deleted_at, updated_at, shorthand) 27 SELECT id, uuid, url, title, description, visited_at, created_at, deleted_at, updated_at, shorthand FROM links; 28 29 DROP TABLE links; 30 31 ALTER TABLE links_tmp RENAME TO links; 32 33 CREATE INDEX links_owner_user_id_idx ON links(owner_user_id); 34 35 -- +migrate StatementBegin 36 CREATE TRIGGER links_after_insert 37 AFTER INSERT ON links BEGIN 38 INSERT INTO fts5_links(rowid, uuid, url, title, description, created_at, visited_at) VALUES 39 (new.id, new.uuid, new.url, new.title, new.description, new.created_at, new.visited_at); 40 END; 41 42 CREATE TRIGGER links_after_update 43 AFTER UPDATE ON links WHEN old.deleted_at IS NULL AND new.deleted_at IS NULL BEGIN 44 INSERT INTO fts5_links(fts5_links, rowid, uuid, url, title, description, created_at, visited_at) VALUES 45 ('delete', old.id, old.uuid, old.url, old.title, old.description, old.created_at, old.visited_at); 46 INSERT INTO fts5_links(rowid, uuid, url, title, description, created_at, visited_at) VALUES 47 (new.id, new.uuid, new.url, new.title, new.description, new.created_at, new.visited_at); 48 END; 49 50 CREATE TRIGGER links_after_update_restore 51 AFTER UPDATE ON links WHEN old.deleted_at IS NOT NULL AND new.deleted_at IS NULL BEGIN 52 INSERT INTO fts5_links(fts5_links, rowid, uuid, url, title, description, created_at, visited_at) VALUES 53 ('delete', old.id, old.uuid, old.url, old.title, old.description, old.created_at, old.visited_at); 54 INSERT INTO fts5_links(rowid, uuid, url, title, description, created_at, visited_at) VALUES 55 (new.id, new.uuid, new.url, new.title, new.description, new.created_at, new.visited_at); 56 END; 57 58 CREATE TRIGGER links_after_update_soft_delete 59 AFTER UPDATE ON links WHEN old.deleted_at IS NULL AND new.deleted_at IS NOT NULL BEGIN 60 INSERT INTO fts5_links(fts5_links, rowid, uuid, url, title, description, created_at, visited_at) VALUES 61 ('delete', old.id, old.uuid, old.url, old.title, old.description, old.created_at, old.visited_at); 62 END; 63 64 CREATE TRIGGER links_after_delete 65 AFTER DELETE ON links BEGIN 66 INSERT INTO fts5_links(fts5_links, rowid, uuid, url, title, description, created_at, visited_at) VALUES 67 ('delete', old.id, old.uuid, old.url, old.title, old.description, old.created_at, old.visited_at); 68 END; 69 70 -- +migrate StatementEnd 71 72 -- +migrate Down