1-- deleting the not null on canonical 2-- 7. Making Other Kinds Of Table Schema Changes, see https://www.sqlite.org/lang_altertable.html 3PRAGMA foreign_keys= OFF; 4-- SQlite starts its own transaction, no BEGIN TRANSACTION; 5create table PAGES_NEW 6( 7 ID TEXT PRIMARY KEY, 8 PATH TEXT, 9 PAGE_ID TEXT UNIQUE, 10 PAGE_ID_ABBR TEXT, 11 CANONICAL TEXT, 12 NAME TEXT, 13 TITLE TEXT, 14 H1 TEXT, 15 DESCRIPTION TEXT, 16 DATE_MODIFIED TEXT, 17 DATE_CREATED TEXT, 18 DATE_PUBLISHED TEXT, 19 TYPE TEXT, 20 LANG TEXT, 21 WORD_COUNT INTEGER, 22 IS_LOW_QUALITY INTEGER, 23 DATE_START TEXT, 24 DATE_END TEXT, 25 COUNTRY TEXT, 26 BACKLINK_COUNT INTEGER, 27 IS_HOME TEXT, 28 DATE_REPLICATION TEXT, 29 REGION TEXT, 30 LEVEL INTEGER, 31 IS_INDEX TEXT, 32 ANALYTICS TEXT 33); 34 35 36INSERT INTO pages_new (ID, PATH, PAGE_ID, PAGE_ID_ABBR, CANONICAL, NAME, TITLE, H1, DESCRIPTION, DATE_MODIFIED, 37 DATE_CREATED, DATE_PUBLISHED, TYPE, LANG, WORD_COUNT, IS_LOW_QUALITY, DATE_START, DATE_END, 38 COUNTRY, BACKLINK_COUNT, IS_HOME, DATE_REPLICATION, REGION, LEVEL, IS_INDEX, ANALYTICS) 39SELECT ID, 40 PATH, 41 PAGE_ID, 42 PAGE_ID_ABBR, 43 CANONICAL, 44 NAME, 45 TITLE, 46 H1, 47 DESCRIPTION, 48 DATE_MODIFIED, 49 DATE_CREATED, 50 DATE_PUBLISHED, 51 TYPE, 52 LANG, 53 WORD_COUNT, 54 IS_LOW_QUALITY, 55 DATE_START, 56 DATE_END, 57 COUNTRY, 58 BACKLINK_COUNT, 59 IS_HOME, 60 DATE_REPLICATION, 61 REGION, 62 LEVEL, 63 IS_INDEX, 64 ANALYTICS 65FROM pages 66where ROWID in (select max(ROWID) from pages group by id); 67 68-- drop view 69drop view PAGE_REFERENCES_VW; 70drop view PAGE_ALIASES_VW; 71 72DROP TABLE pages; 73ALTER TABLE pages_new 74 RENAME TO pages; 75 76-- Index 77create index PAGES_BACKLINK_COUNT_IDX on PAGES (BACKLINK_COUNT); 78create index PAGES_DATE_END_IDX on PAGES (DATE_END desc); 79create index PAGES_DATE_START_IDX on PAGES (DATE_START desc); 80create index PAGES_IS_HOME_IDX on PAGES (IS_HOME); 81create index PAGES_IS_LOW_QUALITY_IDX on PAGES (IS_LOW_QUALITY); 82create index PAGES_LANG_IDX on PAGES (LANG); 83create index PAGES_DATE_CREATED_IDX on PAGES (DATE_CREATED desc); 84create index PAGES_DATE_MODIFED_IDX on PAGES (DATE_MODIFIED desc); 85create index PAGES_DATE_PUBLISHED_IDX on PAGES (DATE_CREATED desc); 86create index PAGES_DATE_REPLICATION_IDX on PAGES (DATE_REPLICATION); 87create index PAGES_PAGES_NAME on PAGES (NAME); 88create index PAGES_PATH_IDX on PAGES (PATH); 89create index PAGES_TYPE_IDX ON PAGES (TYPE); 90create index PAGES_WORD_COUNT_IDX on PAGES (WORD_COUNT); 91 92-- recreate views 93create view IF NOT EXISTS PAGE_REFERENCES_VW as 94select 95 p.path as referent_path, 96 pr.reference as reference_path 97from 98 page_references pr 99 inner join pages p on pr.page_id = p.page_id; 100create view IF NOT EXISTS PAGE_ALIASES_VW as 101select 102 p.path as page_path, 103 pa.path as alias_path, 104 pa.type as alias_type 105from 106 page_aliases pa 107 inner join pages p on pa.page_id = p.page_id; 108 109PRAGMA foreign_keys=ON; 110