71.sql (2844B)
1 -- +migrate Up 2 CREATE TABLE IF NOT EXISTS links ( 3 id INTEGER NOT NULL PRIMARY KEY, 4 uuid VARCHAR(100) UNIQUE NOT NULL, 5 url VARCHAR(255) UNIQUE NOT NULL, 6 title VARCHAR(255) NOT NULL, 7 description TEXT NOT NULL, 8 visited_at DATETIME NULL, 9 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 10 deleted_at DATETIME NULL, 11 updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP); 12 13 CREATE TABLE IF NOT EXISTS links_categories( 14 id INTEGER NOT NULL PRIMARY KEY, 15 name VARCHAR(255) NOT NULL UNIQUE); 16 17 CREATE TABLE IF NOT EXISTS links_categories_links( 18 link_id INTEGER NOT NULL, 19 category_id INTEGER NOT NULL, 20 PRIMARY KEY (link_id, category_id), 21 CONSTRAINT links_categories_links_link_id_fk 22 FOREIGN KEY (link_id) 23 REFERENCES links (id) 24 ON DELETE CASCADE 25 ON UPDATE CASCADE, 26 CONSTRAINT links_categories_links_category_id_fk 27 FOREIGN KEY (category_id) 28 REFERENCES links_categories (id) 29 ON DELETE CASCADE 30 ON UPDATE CASCADE); 31 32 CREATE TABLE IF NOT EXISTS links_tags( 33 id INTEGER NOT NULL PRIMARY KEY, 34 name VARCHAR(255) NOT NULL UNIQUE); 35 36 CREATE TABLE IF NOT EXISTS links_tags_links( 37 link_id INTEGER NOT NULL, 38 tag_id INTEGER NOT NULL, 39 PRIMARY KEY (link_id, tag_id), 40 CONSTRAINT links_tags_links_link_id_fk 41 FOREIGN KEY (link_id) 42 REFERENCES links (id) 43 ON DELETE CASCADE 44 ON UPDATE CASCADE, 45 CONSTRAINT links_tags_links_tag_id_fk 46 FOREIGN KEY (tag_id) 47 REFERENCES links_tags (id) 48 ON DELETE CASCADE 49 ON UPDATE CASCADE); 50 51 52 CREATE VIRTUAL TABLE fts5_links USING fts5(id UNINDEXED, uuid UNINDEXED, url UNINDEXED, title, description, created_at UNINDEXED, visited_at UNINDEXED, content='links', content_rowid='id'); 53 54 -- +migrate StatementBegin 55 CREATE TRIGGER links_before_update 56 BEFORE UPDATE ON links BEGIN 57 DELETE FROM fts5_links WHERE id=old.id; 58 END; 59 60 CREATE TRIGGER links_before_delete 61 BEFORE DELETE ON links BEGIN 62 DELETE FROM fts5_links WHERE id=old.id; 63 END; 64 65 CREATE TRIGGER links_after_update 66 AFTER UPDATE ON links BEGIN 67 INSERT INTO fts5_links(id, uuid, url, title, description, created_at, visited_at) 68 SELECT id, uuid, url, title, description, created_at, visited_at 69 FROM links 70 WHERE new.id = links.id; 71 END; 72 73 CREATE TRIGGER links_after_insert 74 AFTER INSERT ON links BEGIN 75 INSERT INTO fts5_links(id, uuid, url, title, description, created_at, visited_at) 76 SELECT id, uuid, url, title, description, created_at, visited_at 77 FROM links 78 WHERE new.id = links.id; 79 END; 80 -- +migrate StatementEnd 81 82 INSERT INTO fts5_links SELECT id, uuid, url, title, description, created_at, visited_at FROM links; 83 INSERT INTO fts5_links(fts5_links) VALUES('rebuild'); 84 85 -- +migrate Down