0003_strict_tables.sql (6451B)
1 -- Recreate all tables with STRICT mode. 2 -- STRICT enforces column types at the SQLite level, preventing 3 -- silent type coercion (e.g., inserting a string into an INTEGER column). 4 5 PRAGMA foreign_keys=OFF; 6 BEGIN IMMEDIATE; 7 8 -- Drop all triggers first (they reference the old tables) 9 DROP TRIGGER IF EXISTS story_fts_insert; 10 DROP TRIGGER IF EXISTS story_fts_update; 11 DROP TRIGGER IF EXISTS story_fts_delete; 12 DROP TRIGGER IF EXISTS comment_count_insert; 13 DROP TRIGGER IF EXISTS comment_count_delete; 14 DROP TRIGGER IF EXISTS vote_insert_story; 15 DROP TRIGGER IF EXISTS vote_delete_story; 16 DROP TRIGGER IF EXISTS vote_insert_comment; 17 DROP TRIGGER IF EXISTS vote_delete_comment; 18 19 -- Drop all indices 20 DROP INDEX IF EXISTS idx_story_published; 21 DROP INDEX IF EXISTS idx_story_score; 22 DROP INDEX IF EXISTS idx_story_created_by; 23 DROP INDEX IF EXISTS idx_comment_story_id; 24 DROP INDEX IF EXISTS idx_comment_parent_id; 25 DROP INDEX IF EXISTS idx_comment_created_by; 26 DROP INDEX IF EXISTS idx_vote_user_target; 27 DROP INDEX IF EXISTS idx_story_category_news_item; 28 DROP INDEX IF EXISTS idx_story_category_category; 29 30 -- Drop FTS 31 DROP TABLE IF EXISTS story_fts; 32 33 -- Recreate tables in dependency order with STRICT 34 35 ALTER TABLE user RENAME TO user_old; 36 CREATE TABLE user ( 37 id INTEGER PRIMARY KEY AUTOINCREMENT, 38 email TEXT UNIQUE NOT NULL, 39 password_hash TEXT NOT NULL, 40 username TEXT UNIQUE NOT NULL, 41 about TEXT, 42 karma INTEGER DEFAULT 0, 43 created_at TEXT DEFAULT (datetime('now')), 44 email_verified INTEGER NOT NULL DEFAULT 0, 45 verification_token TEXT 46 ) STRICT; 47 INSERT INTO user SELECT * FROM user_old; 48 DROP TABLE user_old; 49 50 ALTER TABLE category RENAME TO category_old; 51 CREATE TABLE category ( 52 id INTEGER PRIMARY KEY AUTOINCREMENT, 53 name TEXT 54 ) STRICT; 55 INSERT INTO category SELECT * FROM category_old; 56 DROP TABLE category_old; 57 58 ALTER TABLE story RENAME TO story_old; 59 CREATE TABLE story ( 60 id INTEGER PRIMARY KEY AUTOINCREMENT, 61 url TEXT, 62 title TEXT NOT NULL, 63 text TEXT NOT NULL DEFAULT '', 64 published TEXT NOT NULL DEFAULT (datetime('now')), 65 author TEXT, 66 language TEXT NOT NULL DEFAULT 'en', 67 created_by INTEGER REFERENCES user(id), 68 score INTEGER NOT NULL DEFAULT 0, 69 comment_count INTEGER NOT NULL DEFAULT 0, 70 domain TEXT 71 ) STRICT; 72 INSERT INTO story SELECT * FROM story_old; 73 DROP TABLE story_old; 74 75 ALTER TABLE comment RENAME TO comment_old; 76 CREATE TABLE comment ( 77 id INTEGER PRIMARY KEY AUTOINCREMENT, 78 story_id INTEGER NOT NULL REFERENCES story(id), 79 parent_id INTEGER, 80 path TEXT NOT NULL DEFAULT '', 81 depth INTEGER NOT NULL DEFAULT 0, 82 text TEXT NOT NULL DEFAULT '', 83 score INTEGER NOT NULL DEFAULT 0, 84 created_at TEXT NOT NULL DEFAULT (datetime('now')), 85 created_by INTEGER NOT NULL REFERENCES user(id) 86 ) STRICT; 87 INSERT INTO comment SELECT * FROM comment_old; 88 DROP TABLE comment_old; 89 90 ALTER TABLE vote RENAME TO vote_old; 91 CREATE TABLE vote ( 92 id INTEGER PRIMARY KEY AUTOINCREMENT, 93 user_id INTEGER NOT NULL REFERENCES user(id), 94 target_type INTEGER NOT NULL, 95 target_id INTEGER NOT NULL, 96 created_at TEXT NOT NULL DEFAULT (datetime('now')), 97 UNIQUE(user_id, target_type, target_id) 98 ) STRICT; 99 INSERT INTO vote SELECT * FROM vote_old; 100 DROP TABLE vote_old; 101 102 ALTER TABLE story_category RENAME TO story_category_old; 103 CREATE TABLE story_category ( 104 id INTEGER PRIMARY KEY AUTOINCREMENT, 105 news_item_id INTEGER NOT NULL REFERENCES story(id), 106 category_id INTEGER NOT NULL REFERENCES category(id) 107 ) STRICT; 108 INSERT INTO story_category SELECT * FROM story_category_old; 109 DROP TABLE story_category_old; 110 111 -- Recreate indices 112 CREATE INDEX idx_story_published ON story(published DESC); 113 CREATE INDEX idx_story_score ON story(score DESC); 114 CREATE INDEX idx_story_created_by ON story(created_by); 115 CREATE INDEX idx_comment_story_id ON comment(story_id); 116 CREATE INDEX idx_comment_parent_id ON comment(parent_id); 117 CREATE INDEX idx_comment_created_by ON comment(created_by); 118 CREATE INDEX idx_vote_user_target ON vote(user_id, target_type, target_id); 119 CREATE INDEX idx_story_category_news_item ON story_category(news_item_id); 120 CREATE INDEX idx_story_category_category ON story_category(category_id); 121 122 -- Recreate FTS 123 CREATE VIRTUAL TABLE story_fts USING fts5( 124 title, text, 125 content='story', 126 content_rowid='id', 127 tokenize='porter unicode61' 128 ); 129 INSERT INTO story_fts(rowid, title, text) SELECT id, title, text FROM story; 130 131 -- Recreate triggers 132 CREATE TRIGGER story_fts_insert AFTER INSERT ON story BEGIN 133 INSERT INTO story_fts(rowid, title, text) VALUES (new.id, new.title, new.text); 134 END; 135 136 CREATE TRIGGER story_fts_update AFTER UPDATE ON story BEGIN 137 INSERT INTO story_fts(story_fts, rowid, title, text) VALUES ('delete', old.id, old.title, old.text); 138 INSERT INTO story_fts(rowid, title, text) VALUES (new.id, new.title, new.text); 139 END; 140 141 CREATE TRIGGER story_fts_delete AFTER DELETE ON story BEGIN 142 INSERT INTO story_fts(story_fts, rowid, title, text) VALUES ('delete', old.id, old.title, old.text); 143 END; 144 145 CREATE TRIGGER comment_count_insert AFTER INSERT ON comment BEGIN 146 UPDATE story SET comment_count = comment_count + 1 WHERE id = new.story_id; 147 END; 148 149 CREATE TRIGGER comment_count_delete AFTER DELETE ON comment BEGIN 150 UPDATE story SET comment_count = comment_count - 1 WHERE id = old.story_id; 151 END; 152 153 CREATE TRIGGER vote_insert_story AFTER INSERT ON vote WHEN new.target_type = 1 BEGIN 154 UPDATE story SET score = score + 1 WHERE id = new.target_id; 155 UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM story WHERE id = new.target_id); 156 END; 157 158 CREATE TRIGGER vote_delete_story AFTER DELETE ON vote WHEN old.target_type = 1 BEGIN 159 UPDATE story SET score = score - 1 WHERE id = old.target_id; 160 UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM story WHERE id = old.target_id); 161 END; 162 163 CREATE TRIGGER vote_insert_comment AFTER INSERT ON vote WHEN new.target_type = 2 BEGIN 164 UPDATE comment SET score = score + 1 WHERE id = new.target_id; 165 UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM comment WHERE id = new.target_id); 166 END; 167 168 CREATE TRIGGER vote_delete_comment AFTER DELETE ON vote WHEN old.target_type = 2 BEGIN 169 UPDATE comment SET score = score - 1 WHERE id = old.target_id; 170 UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM comment WHERE id = old.target_id); 171 END; 172 173 UPDATE migration_version SET version = 3; 174 COMMIT; 175 176 PRAGMA foreign_keys=ON;