]> git.wh0rd.org - tt-rss.git/blame - schema/ttrss_schema_mysql.sql
schema: fix reinitialization not working for older databases
[tt-rss.git] / schema / ttrss_schema_mysql.sql
CommitLineData
84004f8c
AD
1SET NAMES utf8;
2SET CHARACTER SET utf8;
150b4a2c 3
889a5f9f 4drop table if exists ttrss_error_log;
5d9abb1e 5drop table if exists ttrss_plugin_storage;
58fa1bbc
AD
6drop table if exists ttrss_linked_feeds;
7drop table if exists ttrss_linked_instances;
8801fb01 8drop table if exists ttrss_access_keys;
f8bfb81f
AD
9drop table if exists ttrss_user_labels2;
10drop table if exists ttrss_labels2;
0c630ef3 11drop table if exists ttrss_feedbrowser_cache;
81dde650
AD
12drop table if exists ttrss_version;
13drop table if exists ttrss_labels;
6aff7845
AD
14drop table if exists ttrss_filters2_actions;
15drop table if exists ttrss_filters2_rules;
16drop table if exists ttrss_filters2;
5ddc7504 17drop table if exists ttrss_filters;
81dde650 18drop table if exists ttrss_filter_types;
53d6935b 19drop table if exists ttrss_filter_actions;
81dde650
AD
20drop table if exists ttrss_user_prefs;
21drop table if exists ttrss_prefs;
22drop table if exists ttrss_prefs_types;
58fa1bbc 23drop table if exists ttrss_prefs_sections;
eb36b4eb 24drop table if exists ttrss_tags;
963d3314 25drop table if exists ttrss_enclosures;
d9084cf2 26drop table if exists ttrss_settings_profiles;
8caa7999 27drop table if exists ttrss_entry_comments;
81dde650 28drop table if exists ttrss_user_entries;
648472a7 29drop table if exists ttrss_entries;
ab5eb26f 30drop table if exists ttrss_scheduled_updates;
4d736378 31drop table if exists ttrss_counters_cache;
8a4c759e 32drop table if exists ttrss_cat_counters_cache;
648472a7 33drop table if exists ttrss_feeds;
24902606 34drop table if exists ttrss_archived_feeds;
4a9a8bd8 35drop table if exists ttrss_feed_categories;
a6ccf566 36drop table if exists ttrss_users;
e552e5a2 37drop table if exists ttrss_themes;
36bfab86 38drop table if exists ttrss_sessions;
e552e5a2 39
855d0ecf
AD
40begin;
41
a6ccf566
AD
42create table ttrss_users (id integer primary key not null auto_increment,
43 login varchar(120) not null unique,
44 pwd_hash varchar(250) not null,
f16ef236 45 last_login datetime default null,
e552e5a2
AD
46 access_level integer not null default 0,
47 theme_id integer default null,
8629e09d 48 email varchar(250) not null default '',
73fe13af 49 full_name varchar(250) not null default '',
32be4b10
AD
50 email_digest bool not null default false,
51 last_digest_sent datetime default null,
e90053fe 52 salt varchar(250) not null default '',
54a3d3cf 53 created datetime default null,
57e24c82 54 twitter_oauth longtext default null,
fb70f26e 55 otp_enabled boolean not null default false,
7bfeaa17 56 index (theme_id)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
a6ccf566 57
58fa1bbc 58insert into ttrss_users (login,pwd_hash,access_level) values ('admin',
7f16656e 59 'SHA1:5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8', 10);
648472a7 60
17095c5a 61create table ttrss_feed_categories(id integer not null primary key auto_increment,
5a214f9d
AD
62 owner_uid integer not null,
63 title varchar(200) not null,
28bcadff 64 collapsed bool not null default false,
782ddd70 65 order_id integer not null default 0,
9432bfa0 66 parent_cat integer,
b4f83063 67 view_settings varchar(250) not null default '',
9432bfa0
AD
68 index(parent_cat),
69 foreign key (parent_cat) references ttrss_feed_categories(id) ON DELETE SET NULL,
5a214f9d 70 index(owner_uid),
7bfeaa17 71 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
4a9a8bd8 72
24902606
AD
73create table ttrss_archived_feeds (id integer not null primary key,
74 owner_uid integer not null,
58fa1bbc
AD
75 title varchar(200) not null,
76 feed_url text not null,
24902606
AD
77 site_url varchar(250) not null default '',
78 index(owner_uid),
7bfeaa17 79 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
24902606 80
4d736378
AD
81create table ttrss_counters_cache (
82 feed_id integer not null,
83 owner_uid integer not null,
84 value integer not null default 0,
a1eba24b 85 updated datetime not null,
8a4c759e
AD
86 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE
87);
88
ed7f0ecd
AD
89create index ttrss_counters_cache_feed_id_idx on ttrss_counters_cache(feed_id);
90create index ttrss_counters_cache_owner_uid_idx on ttrss_counters_cache(owner_uid);
91create index ttrss_counters_cache_value_idx on ttrss_counters_cache(value);
92
8a4c759e
AD
93create table ttrss_cat_counters_cache (
94 feed_id integer not null,
95 owner_uid integer not null,
96 value integer not null default 0,
97 updated datetime not null,
4d736378
AD
98 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE
99);
100
ed7f0ecd
AD
101create index ttrss_cat_counters_cache_owner_uid_idx on ttrss_cat_counters_cache(owner_uid);
102
648472a7 103create table ttrss_feeds (id integer not null auto_increment primary key,
a6ccf566 104 owner_uid integer not null,
58fa1bbc 105 title varchar(200) not null,
4a9a8bd8 106 cat_id integer default null,
58fa1bbc 107 feed_url text not null,
648472a7 108 icon_url varchar(250) not null default '',
d148926e 109 update_interval integer not null default 0,
1089b16b 110 purge_interval integer not null default 0,
13b98333 111 last_updated datetime default 0,
0d276d67 112 last_error varchar(250) not null default '',
14118af3 113 favicon_avg_color varchar(11) default null,
a6ccf566 114 site_url varchar(250) not null default '',
e93a3c96
AD
115 auth_login varchar(250) not null default '',
116 auth_pass varchar(250) not null default '',
de99f500 117 parent_feed integer default null,
5b35b4de 118 private bool not null default false,
70f6dbb1 119 rtl_content bool not null default false,
7da377ca 120 hidden bool not null default false,
3dd9183c 121 include_in_digest boolean not null default true,
fb67e2ba 122 cache_images boolean not null default false,
bfd61d3f 123 hide_images boolean not null default false,
87764a50 124 cache_content boolean not null default false,
155a2a53 125 auth_pass_encrypted boolean not null default false,
14fb4e91 126 last_viewed datetime default null,
3c50da83 127 last_update_started datetime default null,
e0382fd6 128 always_display_enclosures boolean not null default false,
5b8534ef 129 update_method integer not null default 0,
56781036 130 order_id integer not null default 0,
9382ca84 131 mark_unread_on_update boolean not null default false,
74b046a1 132 update_on_checksum_change boolean not null default false,
9382ca84 133 strip_images boolean not null default false,
b4f83063 134 view_settings varchar(250) not null default '',
5ab9791f 135 pubsub_state integer not null default 0,
382268c6 136 favicon_last_checked datetime default null,
a6ccf566 137 index(owner_uid),
4a9a8bd8
AD
138 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE,
139 index(cat_id),
f0907182 140 foreign key (cat_id) references ttrss_feed_categories(id) ON DELETE SET NULL,
de99f500 141 index(parent_feed),
7bfeaa17 142 foreign key (parent_feed) references ttrss_feeds(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
a6ccf566 143
44f1eeed
AD
144create index ttrss_feeds_owner_uid_index on ttrss_feeds(owner_uid);
145create index ttrss_feeds_cat_id_idx on ttrss_feeds(cat_id);
146
078b5702 147insert into ttrss_feeds (owner_uid, title, feed_url) values
1f8c187d 148 (1, 'Tiny Tiny RSS: New Releases', 'http://tt-rss.org/releases.rss');
078b5702 149
58fa1bbc 150insert into ttrss_feeds (owner_uid, title, feed_url) values
1f8c187d 151 (1, 'Tiny Tiny RSS: Forum', 'http://tt-rss.org/forum/rss.php');
648472a7 152
58fa1bbc
AD
153create table ttrss_entries (id integer not null primary key auto_increment,
154 title text not null,
155 guid varchar(255) not null unique,
156 link text not null,
157 updated datetime not null,
8e54a21e 158 content longtext not null,
648472a7 159 content_hash varchar(250) not null,
87764a50 160 cached_content longtext,
8158c57a 161 no_orig_date bool not null default 0,
c62d62f6 162 date_entered datetime not null,
25ea2805 163 date_updated datetime not null,
eb40e11b 164 num_comments integer not null default 0,
7bfb3dab 165 plugin_data longtext,
4bc760da 166 comments varchar(250) not null default '',
7bfeaa17 167 author varchar(250) not null default '') ENGINE=InnoDB DEFAULT CHARSET=UTF8;
c62d62f6 168
b60e1d86 169create index ttrss_entries_date_entered_index on ttrss_entries(date_entered);
ed7f0ecd
AD
170create index ttrss_entries_guid_index on ttrss_entries(guid);
171create index ttrss_entries_updated_idx on ttrss_entries(updated);
b60e1d86 172
c62d62f6
AD
173create table ttrss_user_entries (
174 int_id integer not null primary key auto_increment,
175 ref_id integer not null,
83cd33fc 176 uuid varchar(200) not null,
58fa1bbc
AD
177 feed_id int,
178 orig_feed_id int,
c62d62f6
AD
179 owner_uid integer not null,
180 marked bool not null default 0,
ffba8297 181 published bool not null default 0,
490c366d 182 tag_cache text not null,
905ff52a 183 label_cache text not null,
c62d62f6 184 last_read datetime,
afb6038e 185 score int not null default 0,
8e54a21e 186 note longtext,
7873d588
AD
187 last_marked datetime,
188 last_published datetime,
1696229f 189 unread bool not null default 1,
c62d62f6
AD
190 index (ref_id),
191 foreign key (ref_id) references ttrss_entries(id) ON DELETE CASCADE,
1696229f 192 index (feed_id),
a6ccf566 193 foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE,
e04c18a2 194 index (orig_feed_id),
24902606 195 foreign key (orig_feed_id) references ttrss_archived_feeds(id) ON DELETE SET NULL,
a6ccf566 196 index (owner_uid),
7bfeaa17 197 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
648472a7 198
8514e925
AD
199create index ttrss_user_entries_owner_uid_index on ttrss_user_entries(owner_uid);
200create index ttrss_user_entries_ref_id_index on ttrss_user_entries(ref_id);
201create index ttrss_user_entries_feed_id on ttrss_user_entries(feed_id);
ed7f0ecd
AD
202create index ttrss_user_entries_unread_idx on ttrss_user_entries(unread);
203
8caa7999
AD
204create table ttrss_entry_comments (id integer not null primary key,
205 ref_id integer not null,
206 owner_uid integer not null,
207 private bool not null default 0,
208 date_entered datetime not null,
209 index (ref_id),
210 foreign key (ref_id) references ttrss_entries(id) ON DELETE CASCADE,
211 index (owner_uid),
7bfeaa17 212 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
8caa7999 213
58fa1bbc
AD
214create table ttrss_filter_types (id integer primary key,
215 name varchar(120) unique not null,
7bfeaa17 216 description varchar(250) not null unique) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
648472a7 217
648472a7
AD
218insert into ttrss_filter_types (id,name,description) values (1, 'title', 'Title');
219insert into ttrss_filter_types (id,name,description) values (2, 'content', 'Content');
58fa1bbc 220insert into ttrss_filter_types (id,name,description) values (3, 'both',
648472a7 221 'Title or Content');
58fa1bbc 222insert into ttrss_filter_types (id,name,description) values (4, 'link',
3a933f22 223 'Link');
58fa1bbc 224insert into ttrss_filter_types (id,name,description) values (5, 'date',
75fa1e31 225 'Article Date');
fa3317be 226insert into ttrss_filter_types (id,name,description) values (6, 'author', 'Author');
c3fc5e47 227insert into ttrss_filter_types (id,name,description) values (7, 'tag', 'Article Tags');
648472a7 228
58fa1bbc
AD
229create table ttrss_filter_actions (id integer not null primary key,
230 name varchar(120) unique not null,
7bfeaa17 231 description varchar(250) not null unique) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
53d6935b 232
58fa1bbc 233insert into ttrss_filter_actions (id,name,description) values (1, 'filter',
b9ad7cfa 234 'Delete article');
53d6935b 235
58fa1bbc 236insert into ttrss_filter_actions (id,name,description) values (2, 'catchup',
53d6935b
AD
237 'Mark as read');
238
58fa1bbc 239insert into ttrss_filter_actions (id,name,description) values (3, 'mark',
dd7d3187
AD
240 'Set starred');
241
58fa1bbc 242insert into ttrss_filter_actions (id,name,description) values (4, 'tag',
de07b5ee 243 'Assign tags');
7cb9311b 244
58fa1bbc 245insert into ttrss_filter_actions (id,name,description) values (5, 'publish',
0c4811be
AD
246 'Publish article');
247
58fa1bbc 248insert into ttrss_filter_actions (id,name,description) values (6, 'score',
fdb7b03f
AD
249 'Modify score');
250
58fa1bbc 251insert into ttrss_filter_actions (id,name,description) values (7, 'label',
ceb30ba4
AD
252 'Assign label');
253
5e736e45
AD
254insert into ttrss_filter_actions (id,name,description) values (8, 'stop',
255 'Stop / Do nothing');
256
6aff7845
AD
257create table ttrss_filters2(id integer primary key auto_increment,
258 owner_uid integer not null,
259 match_any_rule boolean not null default false,
260 enabled boolean not null default true,
a3a896a1 261 inverse bool not null default false,
8e8c8934
AD
262 title varchar(250) not null default '',
263 order_id integer not null default 0,
6aff7845
AD
264 index(owner_uid),
265 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
fb70f26e 266
6aff7845
AD
267create table ttrss_filters2_rules(id integer primary key auto_increment,
268 filter_id integer not null references ttrss_filters2(id) on delete cascade,
269 reg_exp varchar(250) not null,
a3a896a1 270 inverse bool not null default false,
6aff7845
AD
271 filter_type integer not null,
272 feed_id integer default null,
273 cat_id integer default null,
274 cat_filter boolean not null default false,
275 index (filter_id),
276 foreign key (filter_id) references ttrss_filters2(id) on delete cascade,
277 index (filter_type),
278 foreign key (filter_type) references ttrss_filter_types(id) ON DELETE CASCADE,
279 index (feed_id),
280 foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE,
281 index (cat_id),
282 foreign key (cat_id) references ttrss_feed_categories(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
283
284create table ttrss_filters2_actions(id integer primary key auto_increment,
285 filter_id integer not null,
286 action_id integer not null default 1 references ttrss_filter_actions(id) on delete cascade,
287 action_param varchar(250) not null default '',
288 index (filter_id),
289 foreign key (filter_id) references ttrss_filters2(id) on delete cascade,
290 index (action_id),
291 foreign key (action_id) references ttrss_filter_actions(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
292
58fa1bbc
AD
293create table ttrss_tags (id integer primary key auto_increment,
294 owner_uid integer not null,
eb36b4eb 295 tag_name varchar(250) not null,
c62d62f6
AD
296 post_int_id integer not null,
297 index (post_int_id),
298 foreign key (post_int_id) references ttrss_user_entries(int_id) ON DELETE CASCADE,
a6ccf566 299 index (owner_uid),
7bfeaa17 300 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
eb36b4eb 301
7bfeaa17 302create table ttrss_version (schema_version int not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
5f171894 303
7b149552 304insert into ttrss_version values (120);
5f171894 305
d9084cf2 306create table ttrss_enclosures (id integer primary key auto_increment,
963d3314
AD
307 content_url text not null,
308 content_type varchar(250) not null,
309 post_id integer not null,
aa6d1e97 310 title text not null,
ce53e200 311 duration text not null,
963d3314 312 index (post_id),
7bfeaa17 313 foreign key (post_id) references ttrss_entries(id) ON DELETE cascade) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
963d3314 314
f0143d4e
AD
315create index ttrss_enclosures_post_id_idx on ttrss_enclosures(post_id);
316
d9084cf2
AD
317create table ttrss_settings_profiles(id integer primary key auto_increment,
318 title varchar(250) not null,
319 owner_uid integer not null,
320 index (owner_uid),
7bfeaa17 321 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
d9084cf2 322
58fa1bbc 323create table ttrss_prefs_types (id integer not null primary key,
7bfeaa17 324 type_name varchar(100) not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
e0257be1
AD
325
326insert into ttrss_prefs_types (id, type_name) values (1, 'bool');
327insert into ttrss_prefs_types (id, type_name) values (2, 'string');
328insert into ttrss_prefs_types (id, type_name) values (3, 'integer');
329
58fa1bbc 330create table ttrss_prefs_sections (id integer not null primary key,
923b5c8b 331 order_id integer not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
e0257be1 332
923b5c8b
AD
333insert into ttrss_prefs_sections (id, order_id) values (1, 0);
334insert into ttrss_prefs_sections (id, order_id) values (2, 1);
335insert into ttrss_prefs_sections (id, order_id) values (3, 3);
336insert into ttrss_prefs_sections (id, order_id) values (4, 2);
e0257be1 337
a6ccf566 338create table ttrss_prefs (pref_name varchar(250) not null primary key,
e0257be1
AD
339 type_id integer not null,
340 section_id integer not null default 1,
2b6ed06b 341 access_level integer not null default 0,
e0257be1 342 def_value text not null,
e0257be1
AD
343 index(type_id),
344 foreign key (type_id) references ttrss_prefs_types(id),
345 index(section_id),
7bfeaa17 346 foreign key (section_id) references ttrss_prefs_sections(id)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
e0257be1 347
2ced2bb8
AD
348create index ttrss_prefs_pref_name_idx on ttrss_prefs(pref_name);
349
923b5c8b
AD
350insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('PURGE_OLD_DAYS', 3, '60', 1);
351insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DEFAULT_UPDATE_INTERVAL', 3, '30', 1);
352insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DEFAULT_ARTICLE_LIMIT', 3, '30', 2);
881a1580 353insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('ALLOW_DUPLICATE_POSTS', 1, 'false', 1);
923b5c8b
AD
354insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('ENABLE_FEED_CATS', 1, 'true', 2);
355insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('SHOW_CONTENT_PREVIEW', 1, 'true', 2);
356insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('SHORT_DATE_FORMAT', 2, 'M d, G:i', 3);
357insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('LONG_DATE_FORMAT', 2, 'D, M d Y - G:i', 3);
358insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('COMBINED_DISPLAY_MODE', 1, 'true', 2);
359insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('HIDE_READ_FEEDS', 1, 'false', 2);
360insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('ON_CATCHUP_SHOW_NEXT_FEED', 1, 'false', 2);
361insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('FEEDS_SORT_BY_UNREAD', 1, 'false', 2);
362insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('REVERSE_HEADLINES', 1, 'false', 2);
363insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DIGEST_ENABLE', 1, 'false', 4);
364insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('CONFIRM_FEED_CATCHUP', 1, 'true', 2);
365insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('CDM_AUTO_CATCHUP', 1, 'false', 2);
366insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_DEFAULT_VIEW_MODE', 2, 'adaptive', 1);
367insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_DEFAULT_VIEW_LIMIT', 3, '30', 1);
368insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_PREFS_ACTIVE_TAB', 2, '', 1);
369insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('STRIP_UNSAFE_TAGS', 1, 'true', 3);
370insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('BLACKLISTED_TAGS', 2, 'main, generic, misc, uncategorized, blog, blogroll, general, news', 3);
371insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('FRESH_ARTICLE_MAX_AGE', 3, '24', 2);
372insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DIGEST_CATCHUP', 1, 'false', 4);
373insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('CDM_EXPANDED', 1, 'true', 2);
374insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('PURGE_UNREAD_ARTICLES', 1, 'true', 3);
375insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('HIDE_READ_SHOWS_SPECIAL', 1, 'true', 2);
376insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('VFEED_GROUP_BY_FEED', 1, 'false', 2);
377insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('STRIP_IMAGES', 1, 'false', 2);
378insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_DEFAULT_VIEW_ORDER_BY', 2, 'default', 1);
379insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('ENABLE_API_ACCESS', 1, 'false', 1);
380insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_COLLAPSED_SPECIAL', 1, 'false', 1);
381insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_COLLAPSED_LABELS', 1, 'false', 1);
382insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_COLLAPSED_UNCAT', 1, 'false', 1);
383insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_COLLAPSED_FEEDLIST', 1, 'false', 1);
384insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_ENABLE_CATS', 1, 'false', 1);
385insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_SHOW_IMAGES', 1, 'false', 1);
386insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_HIDE_READ', 1, 'false', 1);
387insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_SORT_FEEDS_UNREAD', 1, 'false', 1);
388insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_THEME_ID', 2, '0', 1);
3fbb4d18 389insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_TIMEZONE', 2, 'Automatic', 1);
923b5c8b
AD
390insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_STYLESHEET', 2, '', 2);
391insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('SORT_HEADLINES_BY_FEED_DATE', 1, 'false', 2);
392insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_BROWSE_CATS', 1, 'true', 1);
393insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('SSL_CERT_SERIAL', 2, '', 3);
394insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DIGEST_PREFERRED_TIME', 2, '00:00', 4);
395insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_PREFS_SHOW_EMPTY_CATS', 1, 'false', 1);
396insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_DEFAULT_INCLUDE_CHILDREN', 1, 'false', 1);
397insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('AUTO_ASSIGN_LABELS', 1, 'true', 3);
398insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_ENABLED_PLUGINS', 2, '', 1);
399insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_REVERSE_HEADLINES', 1, 'false', 1);
400insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_CSS_THEME', 2, '', 2);
7b149552 401insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_LANGUAGE', 2, '', 2);
5d40efc9 402
f9ebb32c
AD
403update ttrss_prefs set access_level = 1 where pref_name in ('ON_CATCHUP_SHOW_NEXT_FEED',
404 'SORT_HEADLINES_BY_FEED_DATE',
405 'VFEED_GROUP_BY_FEED',
406 'FRESH_ARTICLE_MAX_AGE',
407 'CDM_EXPANDED',
408 'SHOW_CONTENT_PREVIEW',
b24504b1 409 'AUTO_ASSIGN_LABELS',
f9ebb32c
AD
410 'HIDE_READ_SHOWS_SPECIAL');
411
a6ccf566
AD
412create table ttrss_user_prefs (
413 owner_uid integer not null,
414 pref_name varchar(250),
88e4e597 415 value longtext not null,
d9084cf2
AD
416 profile integer,
417 index (profile),
418 foreign key (profile) references ttrss_settings_profiles(id) ON DELETE CASCADE,
a6ccf566
AD
419 index (owner_uid),
420 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE,
421 index (pref_name),
7bfeaa17 422 foreign key (pref_name) references ttrss_prefs(pref_name) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
a6ccf566 423
2ced2bb8
AD
424create index ttrss_user_prefs_owner_uid_index on ttrss_user_prefs(owner_uid);
425create index ttrss_user_prefs_pref_name_idx on ttrss_user_prefs(pref_name);
426
04febb04 427create table ttrss_sessions (id varchar(250) unique not null primary key,
36bfab86
AD
428 data text,
429 expire integer not null,
58fa1bbc 430 index (id),
7bfeaa17 431 index (expire)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
36bfab86 432
0c630ef3
AD
433create table ttrss_feedbrowser_cache (
434 feed_url text not null,
109662e1 435 site_url text not null,
931dcbc1 436 title text not null,
58fa1bbc 437 subscribers integer not null) DEFAULT CHARSET=UTF8;
0c630ef3 438
58fa1bbc 439create table ttrss_labels2 (id integer not null primary key auto_increment,
ceb30ba4
AD
440 owner_uid integer not null,
441 caption varchar(250) not null,
aec57d7a
AD
442 fg_color varchar(15) not null default '',
443 bg_color varchar(15) not null default '',
ceb30ba4 444 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE
7bfeaa17 445) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
ceb30ba4
AD
446
447create table ttrss_user_labels2 (label_id integer not null,
448 article_id integer not null,
449 foreign key (label_id) references ttrss_labels2(id) ON DELETE CASCADE,
450 foreign key (article_id) references ttrss_entries(id) ON DELETE CASCADE
7bfeaa17 451) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
ceb30ba4 452
58fa1bbc 453create table ttrss_access_keys (id integer not null primary key auto_increment,
8801fb01
AD
454 access_key varchar(250) not null,
455 feed_id varchar(250) not null,
456 is_cat bool not null default false,
457 owner_uid integer not null,
7bfeaa17 458 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
8801fb01 459
58fa1bbc 460create table ttrss_linked_instances (id integer not null primary key auto_increment,
afb875cc 461 last_connected datetime not null,
cfc06471
AD
462 last_status_in integer not null,
463 last_status_out integer not null,
1eb3012f 464 access_key varchar(250) not null unique,
7bfeaa17 465 access_url text not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
58fa1bbc
AD
466
467create table ttrss_linked_feeds (
468 feed_url text not null,
109662e1 469 site_url text not null,
58fa1bbc
AD
470 title text not null,
471 created datetime not null,
472 updated datetime not null,
473 instance_id integer not null,
474 subscribers integer not null,
7bfeaa17 475 foreign key (instance_id) references ttrss_linked_instances(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
58fa1bbc 476
5d9abb1e
AD
477create table ttrss_plugin_storage (
478 id integer not null auto_increment primary key,
479 name varchar(100) not null,
480 owner_uid integer not null,
481 content longtext not null,
482 foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
483
889a5f9f
AD
484create table ttrss_error_log(
485 id integer not null auto_increment primary key,
486 owner_uid integer,
487 errno integer not null,
488 errstr text not null,
489 filename text not null,
3fbb4d18 490 lineno integer not null,
889a5f9f
AD
491 context text not null,
492 created_at datetime not null,
493 foreign key (owner_uid) references ttrss_users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
5d9abb1e 494
855d0ecf 495commit;