0001_initial_schema.sql (4777B)
1 BEGIN IMMEDIATE; 2 3 CREATE TABLE IF NOT EXISTS user ( 4 id INTEGER PRIMARY KEY AUTOINCREMENT, 5 email TEXT UNIQUE NOT NULL, 6 password_hash TEXT NOT NULL, 7 username TEXT UNIQUE NOT NULL, 8 about TEXT, 9 karma INTEGER DEFAULT 0, 10 created_at TEXT DEFAULT (datetime('now')) 11 ); 12 13 CREATE TABLE IF NOT EXISTS story ( 14 id INTEGER PRIMARY KEY AUTOINCREMENT, 15 url TEXT, 16 title TEXT NOT NULL, 17 text TEXT NOT NULL DEFAULT '', 18 published TEXT NOT NULL DEFAULT (datetime('now')), 19 author TEXT, 20 language TEXT NOT NULL DEFAULT 'en', 21 created_by INTEGER REFERENCES user(id), 22 score INTEGER NOT NULL DEFAULT 0, 23 comment_count INTEGER NOT NULL DEFAULT 0, 24 domain TEXT 25 ); 26 27 CREATE TABLE IF NOT EXISTS comment ( 28 id INTEGER PRIMARY KEY AUTOINCREMENT, 29 story_id INTEGER NOT NULL REFERENCES story(id), 30 parent_id INTEGER REFERENCES comment(id), 31 path TEXT NOT NULL DEFAULT '', 32 depth INTEGER NOT NULL DEFAULT 0, 33 text TEXT NOT NULL DEFAULT '', 34 score INTEGER NOT NULL DEFAULT 0, 35 created_at TEXT NOT NULL DEFAULT (datetime('now')), 36 created_by INTEGER NOT NULL REFERENCES user(id) 37 ); 38 39 CREATE TABLE IF NOT EXISTS vote ( 40 id INTEGER PRIMARY KEY AUTOINCREMENT, 41 user_id INTEGER NOT NULL REFERENCES user(id), 42 target_type INTEGER NOT NULL, 43 target_id INTEGER NOT NULL, 44 created_at TEXT NOT NULL DEFAULT (datetime('now')), 45 UNIQUE(user_id, target_type, target_id) 46 ); 47 48 CREATE TABLE IF NOT EXISTS category ( 49 id INTEGER PRIMARY KEY AUTOINCREMENT, 50 name TEXT 51 ); 52 53 CREATE TABLE IF NOT EXISTS story_category ( 54 id INTEGER PRIMARY KEY AUTOINCREMENT, 55 news_item_id INTEGER NOT NULL REFERENCES story(id), 56 category_id INTEGER NOT NULL REFERENCES category(id) 57 ); 58 59 CREATE VIRTUAL TABLE IF NOT EXISTS story_fts USING fts5( 60 title, text, 61 content='story', 62 content_rowid='id', 63 tokenize='porter unicode61' 64 ); 65 66 -- Indices 67 CREATE INDEX IF NOT EXISTS idx_story_published ON story(published DESC); 68 CREATE INDEX IF NOT EXISTS idx_story_score ON story(score DESC); 69 CREATE INDEX IF NOT EXISTS idx_story_created_by ON story(created_by); 70 CREATE INDEX IF NOT EXISTS idx_comment_story_id ON comment(story_id); 71 CREATE INDEX IF NOT EXISTS idx_comment_parent_id ON comment(parent_id); 72 CREATE INDEX IF NOT EXISTS idx_comment_created_by ON comment(created_by); 73 CREATE INDEX IF NOT EXISTS idx_vote_user_target ON vote(user_id, target_type, target_id); 74 CREATE INDEX IF NOT EXISTS idx_story_category_news_item ON story_category(news_item_id); 75 CREATE INDEX IF NOT EXISTS idx_story_category_category ON story_category(category_id); 76 77 -- FTS triggers 78 CREATE TRIGGER IF NOT EXISTS story_fts_insert AFTER INSERT ON story BEGIN 79 INSERT INTO story_fts(rowid, title, text) VALUES (new.id, new.title, new.text); 80 END; 81 82 CREATE TRIGGER IF NOT EXISTS story_fts_update AFTER UPDATE ON story BEGIN 83 INSERT INTO story_fts(story_fts, rowid, title, text) VALUES ('delete', old.id, old.title, old.text); 84 INSERT INTO story_fts(rowid, title, text) VALUES (new.id, new.title, new.text); 85 END; 86 87 CREATE TRIGGER IF NOT EXISTS story_fts_delete AFTER DELETE ON story BEGIN 88 INSERT INTO story_fts(story_fts, rowid, title, text) VALUES ('delete', old.id, old.title, old.text); 89 END; 90 91 -- Comment count triggers 92 CREATE TRIGGER IF NOT EXISTS comment_count_insert AFTER INSERT ON comment BEGIN 93 UPDATE story SET comment_count = comment_count + 1 WHERE id = new.story_id; 94 END; 95 96 CREATE TRIGGER IF NOT EXISTS comment_count_delete AFTER DELETE ON comment BEGIN 97 UPDATE story SET comment_count = comment_count - 1 WHERE id = old.story_id; 98 END; 99 100 -- Vote score/karma triggers 101 CREATE TRIGGER IF NOT EXISTS vote_insert_story AFTER INSERT ON vote WHEN new.target_type = 1 BEGIN 102 UPDATE story SET score = score + 1 WHERE id = new.target_id; 103 UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM story WHERE id = new.target_id); 104 END; 105 106 CREATE TRIGGER IF NOT EXISTS vote_delete_story AFTER DELETE ON vote WHEN old.target_type = 1 BEGIN 107 UPDATE story SET score = score - 1 WHERE id = old.target_id; 108 UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM story WHERE id = old.target_id); 109 END; 110 111 CREATE TRIGGER IF NOT EXISTS vote_insert_comment AFTER INSERT ON vote WHEN new.target_type = 2 BEGIN 112 UPDATE comment SET score = score + 1 WHERE id = new.target_id; 113 UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM comment WHERE id = new.target_id); 114 END; 115 116 CREATE TRIGGER IF NOT EXISTS vote_delete_comment AFTER DELETE ON vote WHEN old.target_type = 2 BEGIN 117 UPDATE comment SET score = score - 1 WHERE id = old.target_id; 118 UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM comment WHERE id = old.target_id); 119 END; 120 121 UPDATE migration_version SET version = 1; 122 COMMIT;