1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
|
<?php
$GLOBALS['SQL_CREATE_DB'] = <<<'SQL'
CREATE DATABASE IF NOT EXISTS `%1$s` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SQL;
$GLOBALS['SQL_CREATE_TABLES'] = <<<'SQL'
CREATE TABLE IF NOT EXISTS `_category` (
`id` INT NOT NULL AUTO_INCREMENT, -- v0.7
`name` VARCHAR(191) NOT NULL, -- Max index length for Unicode is 191 characters (767 bytes) FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE
`kind` SMALLINT DEFAULT 0, -- 1.20.0
`lastUpdate` BIGINT DEFAULT 0, -- 1.20.0
`error` SMALLINT DEFAULT 0, -- 1.20.0
`attributes` TEXT, -- v1.15.0
PRIMARY KEY (`id`),
UNIQUE KEY (`name`) -- v0.7
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
ENGINE = INNODB;
CREATE TABLE IF NOT EXISTS `_feed` (
`id` INT NOT NULL AUTO_INCREMENT, -- v0.7
`url` VARCHAR(32768) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`kind` SMALLINT DEFAULT 0, -- 1.20.0
`category` INT DEFAULT 0, -- 1.20.0
`name` VARCHAR(191) NOT NULL,
`website` TEXT CHARACTER SET latin1 COLLATE latin1_bin,
`description` TEXT,
`lastUpdate` BIGINT DEFAULT 0,
`priority` TINYINT(2) NOT NULL DEFAULT 10,
`pathEntries` VARCHAR(4096) DEFAULT NULL,
`httpAuth` VARCHAR(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`error` BOOLEAN DEFAULT 0,
`ttl` INT NOT NULL DEFAULT 0, -- v0.7.3
`attributes` TEXT, -- v1.11.0
`cache_nbEntries` INT DEFAULT 0, -- v0.7
`cache_nbUnreads` INT DEFAULT 0, -- v0.7
PRIMARY KEY (`id`),
FOREIGN KEY (`category`) REFERENCES `_category`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
INDEX (`name`), -- v0.7
INDEX (`priority`) -- v0.7
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
ENGINE = INNODB;
CREATE TABLE IF NOT EXISTS `_entry` (
`id` BIGINT NOT NULL, -- v0.7
`guid` VARCHAR(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, -- Maximum for UNIQUE is 767B
`title` VARCHAR(8192) NOT NULL,
`author` VARCHAR(1024),
`content_bin` MEDIUMBLOB, -- v0.7
`link` VARCHAR(16383) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`date` BIGINT,
`lastSeen` BIGINT DEFAULT 0,
`hash` BINARY(16), -- v1.1.1
`is_read` BOOLEAN NOT NULL DEFAULT 0,
`is_favorite` BOOLEAN NOT NULL DEFAULT 0,
`id_feed` INT, -- 1.20.0
`tags` VARCHAR(2048),
`attributes` TEXT, -- v1.20.0
PRIMARY KEY (`id`),
FOREIGN KEY (`id_feed`) REFERENCES `_feed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE KEY (`id_feed`,`guid`), -- v0.7
INDEX (`is_favorite`), -- v0.7
INDEX (`is_read`), -- v0.7
INDEX `entry_lastSeen_index` (`lastSeen`), -- v1.1.1
INDEX `entry_feed_read_index` (`id_feed`,`is_read`) -- v1.7
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
ENGINE = INNODB;
INSERT IGNORE INTO `_category` (id, name) VALUES(1, "Uncategorized");
CREATE TABLE IF NOT EXISTS `_entrytmp` ( -- v1.7
`id` BIGINT NOT NULL,
`guid` VARCHAR(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`title` VARCHAR(8192) NOT NULL,
`author` VARCHAR(1024),
`content_bin` MEDIUMBLOB,
`link` VARCHAR(16383) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`date` BIGINT,
`lastSeen` BIGINT DEFAULT 0,
`hash` BINARY(16),
`is_read` BOOLEAN NOT NULL DEFAULT 0,
`is_favorite` BOOLEAN NOT NULL DEFAULT 0,
`id_feed` INT, -- 1.20.0
`tags` VARCHAR(2048),
`attributes` TEXT, -- v1.20.0
PRIMARY KEY (`id`),
FOREIGN KEY (`id_feed`) REFERENCES `_feed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE KEY (`id_feed`,`guid`),
INDEX (`date`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
ENGINE = INNODB;
CREATE TABLE IF NOT EXISTS `_tag` ( -- v1.12
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(191) NOT NULL,
`attributes` TEXT,
PRIMARY KEY (`id`),
UNIQUE KEY (`name`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
ENGINE = INNODB;
CREATE TABLE IF NOT EXISTS `_entrytag` ( -- v1.12
`id_tag` INT, -- 1.20.0
`id_entry` BIGINT,
PRIMARY KEY (`id_tag`,`id_entry`),
FOREIGN KEY (`id_tag`) REFERENCES `_tag`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`id_entry`) REFERENCES `_entry`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX (`id_entry`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
ENGINE = INNODB;
SQL;
$GLOBALS['SQL_DROP_TABLES'] = <<<'SQL'
DROP TABLE IF EXISTS `_entrytag`, `_tag`, `_entrytmp`, `_entry`, `_feed`, `_category`;
SQL;
$GLOBALS['SQL_UPDATE_MINOR'] = <<<'SQL'
ALTER TABLE `_feed`
MODIFY COLUMN `lastUpdate` BIGINT DEFAULT 0,
MODIFY COLUMN `pathEntries` VARCHAR(4096),
MODIFY COLUMN `httpAuth` VARCHAR(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL;
ALTER TABLE `_entry`
MODIFY COLUMN `date` BIGINT,
MODIFY COLUMN `lastSeen` BIGINT DEFAULT 0,
MODIFY COLUMN `guid` VARCHAR(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
MODIFY COLUMN `title` VARCHAR(8192) NOT NULL,
MODIFY COLUMN `author` VARCHAR(1024),
MODIFY COLUMN `link` VARCHAR(16383) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
MODIFY COLUMN `tags` VARCHAR(2048);
ALTER TABLE `_entrytmp`
MODIFY COLUMN `date` BIGINT,
MODIFY COLUMN `lastSeen` BIGINT DEFAULT 0,
MODIFY COLUMN `guid` VARCHAR(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
MODIFY COLUMN `title` VARCHAR(8192) NOT NULL,
MODIFY COLUMN `author` VARCHAR(1024),
MODIFY COLUMN `link` VARCHAR(16383) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
MODIFY COLUMN `tags` VARCHAR(2048);
ALTER TABLE `_tag`
MODIFY COLUMN `name` VARCHAR(191) NOT NULL;
SQL;
|
