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