0007_structured_pks.sql (3217B)
1 -- Replace surrogate primary keys with structured/composite primary keys 2 -- on junction/association tables where the surrogate id is never referenced. 3 -- 4 -- vote: PK becomes (user_id, target_type, target_id) — eliminates the 5 -- separate UNIQUE index and the unused autoincrement id. 6 -- story_category: PK becomes (news_item_id, category_id) — also adds 7 -- a uniqueness guarantee that was previously missing. 8 9 PRAGMA foreign_keys=OFF; 10 BEGIN IMMEDIATE; 11 12 -- Drop vote triggers (they reference the old table) 13 DROP TRIGGER IF EXISTS vote_insert_story; 14 DROP TRIGGER IF EXISTS vote_delete_story; 15 DROP TRIGGER IF EXISTS vote_insert_comment; 16 DROP TRIGGER IF EXISTS vote_delete_comment; 17 18 -- Drop vote index (the composite PK replaces it) 19 DROP INDEX IF EXISTS idx_vote_user_target; 20 21 -- Recreate vote with composite PK 22 ALTER TABLE vote RENAME TO vote_old; 23 CREATE TABLE vote ( 24 user_id INTEGER NOT NULL REFERENCES user(id), 25 target_type INTEGER NOT NULL, 26 target_id INTEGER NOT NULL, 27 created_at INTEGER NOT NULL, 28 PRIMARY KEY (user_id, target_type, target_id) 29 ) STRICT, WITHOUT ROWID; 30 INSERT INTO vote (user_id, target_type, target_id, created_at) 31 SELECT user_id, target_type, target_id, created_at FROM vote_old; 32 DROP TABLE vote_old; 33 34 -- Drop story_category indices 35 DROP INDEX IF EXISTS idx_story_category_news_item; 36 DROP INDEX IF EXISTS idx_story_category_category; 37 38 -- Recreate story_category with composite PK 39 ALTER TABLE story_category RENAME TO story_category_old; 40 CREATE TABLE story_category ( 41 news_item_id INTEGER NOT NULL REFERENCES story(id), 42 category_id INTEGER NOT NULL REFERENCES category(id), 43 PRIMARY KEY (news_item_id, category_id) 44 ) STRICT, WITHOUT ROWID; 45 INSERT OR IGNORE INTO story_category (news_item_id, category_id) 46 SELECT news_item_id, category_id FROM story_category_old; 47 DROP TABLE story_category_old; 48 49 -- Recreate index for looking up stories by category 50 -- (PK covers news_item_id lookups, but category_id lookups need this) 51 CREATE INDEX idx_story_category_category ON story_category(category_id); 52 53 -- Recreate vote triggers 54 CREATE TRIGGER vote_insert_story AFTER INSERT ON vote WHEN new.target_type = 1 BEGIN 55 UPDATE story SET score = score + 1 WHERE id = new.target_id; 56 UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM story WHERE id = new.target_id); 57 END; 58 59 CREATE TRIGGER vote_delete_story AFTER DELETE ON vote WHEN old.target_type = 1 BEGIN 60 UPDATE story SET score = score - 1 WHERE id = old.target_id; 61 UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM story WHERE id = old.target_id); 62 END; 63 64 CREATE TRIGGER vote_insert_comment AFTER INSERT ON vote WHEN new.target_type = 2 BEGIN 65 UPDATE comment SET score = score + 1 WHERE id = new.target_id; 66 UPDATE user SET karma = karma + 1 WHERE id = (SELECT created_by FROM comment WHERE id = new.target_id); 67 END; 68 69 CREATE TRIGGER vote_delete_comment AFTER DELETE ON vote WHEN old.target_type = 2 BEGIN 70 UPDATE comment SET score = score - 1 WHERE id = old.target_id; 71 UPDATE user SET karma = karma - 1 WHERE id = (SELECT created_by FROM comment WHERE id = old.target_id); 72 END; 73 74 UPDATE migration_version SET version = 7; 75 COMMIT; 76 77 PRAGMA foreign_keys=ON;