0005_epoch_timestamps.sql (7534B)
1 -- Convert all timestamp columns from TEXT to INTEGER (epoch seconds). 2 -- Epoch integers are unambiguous (always UTC), smaller, and don't 3 -- need parsing. 4 5 PRAGMA foreign_keys=OFF; 6 BEGIN IMMEDIATE; 7 8 -- Drop triggers and indices (same as migration 0003) 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 INDEX IF EXISTS idx_story_published; 20 DROP INDEX IF EXISTS idx_story_score; 21 DROP INDEX IF EXISTS idx_story_created_by; 22 DROP INDEX IF EXISTS idx_comment_story_id; 23 DROP INDEX IF EXISTS idx_comment_parent_id; 24 DROP INDEX IF EXISTS idx_comment_created_by; 25 DROP INDEX IF EXISTS idx_vote_user_target; 26 DROP INDEX IF EXISTS idx_story_category_news_item; 27 DROP INDEX IF EXISTS idx_story_category_category; 28 DROP INDEX IF EXISTS idx_session_user_id; 29 30 DROP TABLE IF EXISTS story_fts; 31 32 -- Recreate tables with INTEGER timestamps 33 34 ALTER TABLE user RENAME TO user_old; 35 CREATE TABLE user ( 36 id INTEGER PRIMARY KEY AUTOINCREMENT, 37 email TEXT UNIQUE NOT NULL, 38 password_hash TEXT NOT NULL, 39 username TEXT UNIQUE NOT NULL, 40 about TEXT, 41 karma INTEGER DEFAULT 0, 42 created_at INTEGER NOT NULL, 43 email_verified INTEGER NOT NULL DEFAULT 0, 44 verification_token TEXT 45 ) STRICT; 46 INSERT INTO user (id, email, password_hash, username, about, karma, created_at, email_verified, verification_token) 47 SELECT id, email, password_hash, username, about, karma, 48 CAST(strftime('%s', created_at) AS INTEGER), 49 email_verified, verification_token 50 FROM user_old; 51 DROP TABLE user_old; 52 53 ALTER TABLE category RENAME TO category_old; 54 CREATE TABLE category ( 55 id INTEGER PRIMARY KEY AUTOINCREMENT, 56 name TEXT 57 ) STRICT; 58 INSERT INTO category SELECT * FROM category_old; 59 DROP TABLE category_old; 60 61 ALTER TABLE story RENAME TO story_old; 62 CREATE TABLE story ( 63 id INTEGER PRIMARY KEY AUTOINCREMENT, 64 url TEXT, 65 title TEXT NOT NULL, 66 text TEXT NOT NULL DEFAULT '', 67 published INTEGER NOT NULL, 68 author TEXT, 69 language TEXT NOT NULL DEFAULT 'en', 70 created_by INTEGER REFERENCES user(id), 71 score INTEGER NOT NULL DEFAULT 0, 72 comment_count INTEGER NOT NULL DEFAULT 0, 73 domain TEXT 74 ) STRICT; 75 INSERT INTO story (id, url, title, text, published, author, language, created_by, score, comment_count, domain) 76 SELECT id, url, title, text, 77 CAST(strftime('%s', published) AS INTEGER), 78 author, language, created_by, score, comment_count, domain 79 FROM story_old; 80 DROP TABLE story_old; 81 82 ALTER TABLE comment RENAME TO comment_old; 83 CREATE TABLE comment ( 84 id INTEGER PRIMARY KEY AUTOINCREMENT, 85 story_id INTEGER NOT NULL REFERENCES story(id), 86 parent_id INTEGER, 87 path TEXT NOT NULL DEFAULT '', 88 depth INTEGER NOT NULL DEFAULT 0, 89 text TEXT NOT NULL DEFAULT '', 90 score INTEGER NOT NULL DEFAULT 0, 91 created_at INTEGER NOT NULL, 92 created_by INTEGER NOT NULL REFERENCES user(id) 93 ) STRICT; 94 INSERT INTO comment (id, story_id, parent_id, path, depth, text, score, created_at, created_by) 95 SELECT id, story_id, parent_id, path, depth, text, score, 96 CAST(strftime('%s', created_at) AS INTEGER), 97 created_by 98 FROM comment_old; 99 DROP TABLE comment_old; 100 101 ALTER TABLE vote RENAME TO vote_old; 102 CREATE TABLE vote ( 103 id INTEGER PRIMARY KEY AUTOINCREMENT, 104 user_id INTEGER NOT NULL REFERENCES user(id), 105 target_type INTEGER NOT NULL, 106 target_id INTEGER NOT NULL, 107 created_at INTEGER NOT NULL, 108 UNIQUE(user_id, target_type, target_id) 109 ) STRICT; 110 INSERT INTO vote (id, user_id, target_type, target_id, created_at) 111 SELECT id, user_id, target_type, target_id, 112 CAST(strftime('%s', created_at) AS INTEGER) 113 FROM vote_old; 114 DROP TABLE vote_old; 115 116 ALTER TABLE story_category RENAME TO story_category_old; 117 CREATE TABLE story_category ( 118 id INTEGER PRIMARY KEY AUTOINCREMENT, 119 news_item_id INTEGER NOT NULL REFERENCES story(id), 120 category_id INTEGER NOT NULL REFERENCES category(id) 121 ) STRICT; 122 INSERT INTO story_category SELECT * FROM story_category_old; 123 DROP TABLE story_category_old; 124 125 ALTER TABLE session RENAME TO session_old; 126 CREATE TABLE session ( 127 token TEXT PRIMARY KEY NOT NULL, 128 user_id INTEGER NOT NULL REFERENCES user(id), 129 created_at INTEGER NOT NULL 130 ) STRICT; 131 INSERT INTO session (token, user_id, created_at) 132 SELECT token, user_id, 133 CAST(strftime('%s', created_at) AS INTEGER) 134 FROM session_old; 135 DROP TABLE session_old; 136 137 -- Recreate indices 138 CREATE INDEX idx_story_published ON story(published DESC); 139 CREATE INDEX idx_story_score ON story(score DESC); 140 CREATE INDEX idx_story_created_by ON story(created_by); 141 CREATE INDEX idx_comment_story_id ON comment(story_id); 142 CREATE INDEX idx_comment_parent_id ON comment(parent_id); 143 CREATE INDEX idx_comment_created_by ON comment(created_by); 144 CREATE INDEX idx_vote_user_target ON vote(user_id, target_type, target_id); 145 CREATE INDEX idx_story_category_news_item ON story_category(news_item_id); 146 CREATE INDEX idx_story_category_category ON story_category(category_id); 147 CREATE INDEX idx_session_user_id ON session(user_id); 148 149 -- Recreate FTS 150 CREATE VIRTUAL TABLE story_fts USING fts5( 151 title, text, 152 content='story', 153 content_rowid='id', 154 tokenize='porter unicode61' 155 ); 156 INSERT INTO story_fts(rowid, title, text) SELECT id, title, text FROM story; 157 158 -- Recreate triggers 159 CREATE TRIGGER story_fts_insert AFTER INSERT ON story BEGIN 160 INSERT INTO story_fts(rowid, title, text) VALUES (new.id, new.title, new.text); 161 END; 162 163 CREATE TRIGGER story_fts_update AFTER UPDATE ON story BEGIN 164 INSERT INTO story_fts(story_fts, rowid, title, text) VALUES ('delete', old.id, old.title, old.text); 165 INSERT INTO story_fts(rowid, title, text) VALUES (new.id, new.title, new.text); 166 END; 167 168 CREATE TRIGGER story_fts_delete AFTER DELETE ON story BEGIN 169 INSERT INTO story_fts(story_fts, rowid, title, text) VALUES ('delete', old.id, old.title, old.text); 170 END; 171 172 CREATE TRIGGER comment_count_insert AFTER INSERT ON comment BEGIN 173 UPDATE story SET comment_count = comment_count + 1 WHERE id = new.story_id; 174 END; 175 176 CREATE TRIGGER comment_count_delete AFTER DELETE ON comment BEGIN 177 UPDATE story SET comment_count = comment_count - 1 WHERE id = old.story_id; 178 END; 179 180 CREATE TRIGGER vote_insert_story AFTER INSERT ON vote WHEN new.target_type = 1 BEGIN 181 UPDATE story SET score = score + 1 WHERE id = new.target_id; 182 UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM story WHERE id = new.target_id); 183 END; 184 185 CREATE TRIGGER vote_delete_story AFTER DELETE ON vote WHEN old.target_type = 1 BEGIN 186 UPDATE story SET score = score - 1 WHERE id = old.target_id; 187 UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM story WHERE id = old.target_id); 188 END; 189 190 CREATE TRIGGER vote_insert_comment AFTER INSERT ON vote WHEN new.target_type = 2 BEGIN 191 UPDATE comment SET score = score + 1 WHERE id = new.target_id; 192 UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM comment WHERE id = new.target_id); 193 END; 194 195 CREATE TRIGGER vote_delete_comment AFTER DELETE ON vote WHEN old.target_type = 2 BEGIN 196 UPDATE comment SET score = score - 1 WHERE id = old.target_id; 197 UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM comment WHERE id = old.target_id); 198 END; 199 200 UPDATE migration_version SET version = 5; 201 COMMIT; 202 203 PRAGMA foreign_keys=ON;