ttrss_schema_mysql.sql 22 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471
  1. SET NAMES utf8;
  2. SET CHARACTER SET utf8;
  3. drop table if exists ttrss_error_log;
  4. drop table if exists ttrss_plugin_storage;
  5. drop table if exists ttrss_linked_feeds;
  6. drop table if exists ttrss_linked_instances;
  7. drop table if exists ttrss_access_keys;
  8. drop table if exists ttrss_user_labels2;
  9. drop table if exists ttrss_labels2;
  10. drop table if exists ttrss_feedbrowser_cache;
  11. drop table if exists ttrss_version;
  12. drop table if exists ttrss_labels;
  13. drop table if exists ttrss_filters2_actions;
  14. drop table if exists ttrss_filters2_rules;
  15. drop table if exists ttrss_filters2;
  16. drop table if exists ttrss_filters;
  17. drop table if exists ttrss_filter_types;
  18. drop table if exists ttrss_filter_actions;
  19. drop table if exists ttrss_user_prefs;
  20. drop table if exists ttrss_prefs;
  21. drop table if exists ttrss_prefs_types;
  22. drop table if exists ttrss_prefs_sections;
  23. drop table if exists ttrss_tags;
  24. drop table if exists ttrss_enclosures;
  25. drop table if exists ttrss_settings_profiles;
  26. drop table if exists ttrss_entry_comments;
  27. drop table if exists ttrss_user_entries;
  28. drop table if exists ttrss_entries;
  29. drop table if exists ttrss_scheduled_updates;
  30. drop table if exists ttrss_counters_cache;
  31. drop table if exists ttrss_cat_counters_cache;
  32. drop table if exists ttrss_feeds;
  33. drop table if exists ttrss_archived_feeds;
  34. drop table if exists ttrss_feed_categories;
  35. drop table if exists ttrss_users;
  36. drop table if exists ttrss_themes;
  37. drop table if exists ttrss_sessions;
  38. begin;
  39. create table ttrss_users (id integer primary key not null auto_increment,
  40. login varchar(120) not null unique,
  41. pwd_hash varchar(250) not null,
  42. last_login datetime default null,
  43. access_level integer not null default 0,
  44. email varchar(250) not null default '',
  45. full_name varchar(250) not null default '',
  46. email_digest bool not null default false,
  47. last_digest_sent datetime default null,
  48. salt varchar(250) not null default '',
  49. created datetime default null,
  50. twitter_oauth longtext default null,
  51. otp_enabled boolean not null default false,
  52. resetpass_token varchar(250) default null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  53. insert into ttrss_users (login,pwd_hash,access_level) values ('admin',
  54. 'SHA1:5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8', 10);
  55. create table ttrss_feed_categories(id integer not null primary key auto_increment,
  56. owner_uid integer not null,
  57. title varchar(200) not null,
  58. collapsed bool not null default false,
  59. order_id integer not null default 0,
  60. parent_cat integer,
  61. view_settings varchar(250) not null default '',
  62. foreign key (parent_cat) references ttrss_feed_categories(id) ON DELETE SET NULL,
  63. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  64. create table ttrss_archived_feeds (id integer not null primary key,
  65. owner_uid integer not null,
  66. created datetime not null,
  67. title varchar(200) not null,
  68. feed_url text not null,
  69. site_url varchar(250) not null default '',
  70. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  71. create table ttrss_counters_cache (
  72. feed_id integer not null,
  73. owner_uid integer not null,
  74. value integer not null default 0,
  75. updated datetime not null,
  76. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE
  77. ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  78. create index ttrss_counters_cache_feed_id_idx on ttrss_counters_cache(feed_id);
  79. create index ttrss_counters_cache_value_idx on ttrss_counters_cache(value);
  80. create table ttrss_cat_counters_cache (
  81. feed_id integer not null,
  82. owner_uid integer not null,
  83. value integer not null default 0,
  84. updated datetime not null,
  85. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE
  86. ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  87. create table ttrss_feeds (id integer not null auto_increment primary key,
  88. owner_uid integer not null,
  89. title varchar(200) not null,
  90. cat_id integer default null,
  91. feed_url text not null,
  92. icon_url varchar(250) not null default '',
  93. update_interval integer not null default 0,
  94. purge_interval integer not null default 0,
  95. last_updated datetime default null,
  96. last_unconditional datetime default null,
  97. last_error varchar(250) not null default '',
  98. last_modified varchar(250) not null default '',
  99. favicon_avg_color varchar(11) default null,
  100. site_url varchar(250) not null default '',
  101. auth_login varchar(250) not null default '',
  102. auth_pass varchar(250) not null default '',
  103. parent_feed integer default null,
  104. private bool not null default false,
  105. rtl_content bool not null default false,
  106. hidden bool not null default false,
  107. include_in_digest boolean not null default true,
  108. cache_images boolean not null default false,
  109. hide_images boolean not null default false,
  110. cache_content boolean not null default false,
  111. auth_pass_encrypted boolean not null default false,
  112. last_viewed datetime default null,
  113. last_update_started datetime default null,
  114. always_display_enclosures boolean not null default false,
  115. update_method integer not null default 0,
  116. order_id integer not null default 0,
  117. mark_unread_on_update boolean not null default false,
  118. update_on_checksum_change boolean not null default false,
  119. strip_images boolean not null default false,
  120. view_settings varchar(250) not null default '',
  121. pubsub_state integer not null default 0,
  122. favicon_last_checked datetime default null,
  123. feed_language varchar(100) not null default '',
  124. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE,
  125. foreign key (cat_id) references ttrss_feed_categories(id) ON DELETE SET NULL,
  126. foreign key (parent_feed) references ttrss_feeds(id) ON DELETE SET NULL,
  127. unique(feed_url(255), owner_uid)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  128. insert into ttrss_feeds (owner_uid, title, feed_url) values
  129. (1, 'Tiny Tiny RSS: Forum', 'http://tt-rss.org/forum/rss.php');
  130. create table ttrss_entries (id integer not null primary key auto_increment,
  131. title text not null,
  132. guid varchar(255) not null unique,
  133. link text not null,
  134. updated datetime not null,
  135. content longtext not null,
  136. content_hash varchar(250) not null,
  137. cached_content longtext,
  138. no_orig_date bool not null default 0,
  139. date_entered datetime not null,
  140. date_updated datetime not null,
  141. num_comments integer not null default 0,
  142. plugin_data longtext,
  143. lang varchar(2),
  144. comments varchar(250) not null default '',
  145. author varchar(250) not null default '') ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  146. create index ttrss_entries_date_entered_index on ttrss_entries(date_entered);
  147. create index ttrss_entries_updated_idx on ttrss_entries(updated);
  148. create table ttrss_user_entries (
  149. int_id integer not null primary key auto_increment,
  150. ref_id integer not null,
  151. uuid varchar(200) not null,
  152. feed_id int,
  153. orig_feed_id int,
  154. owner_uid integer not null,
  155. marked bool not null default 0,
  156. published bool not null default 0,
  157. tag_cache text not null,
  158. label_cache text not null,
  159. last_read datetime,
  160. score int not null default 0,
  161. note longtext,
  162. last_marked datetime,
  163. last_published datetime,
  164. unread bool not null default 1,
  165. foreign key (ref_id) references ttrss_entries(id) ON DELETE CASCADE,
  166. foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE,
  167. foreign key (orig_feed_id) references ttrss_archived_feeds(id) ON DELETE SET NULL,
  168. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  169. create index ttrss_user_entries_unread_idx on ttrss_user_entries(unread);
  170. create table ttrss_entry_comments (id integer not null primary key,
  171. ref_id integer not null,
  172. owner_uid integer not null,
  173. private bool not null default 0,
  174. date_entered datetime not null,
  175. index (ref_id),
  176. foreign key (ref_id) references ttrss_entries(id) ON DELETE CASCADE,
  177. index (owner_uid),
  178. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  179. create table ttrss_filter_types (id integer primary key,
  180. name varchar(120) unique not null,
  181. description varchar(250) not null unique) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  182. insert into ttrss_filter_types (id,name,description) values (1, 'title', 'Title');
  183. insert into ttrss_filter_types (id,name,description) values (2, 'content', 'Content');
  184. insert into ttrss_filter_types (id,name,description) values (3, 'both',
  185. 'Title or Content');
  186. insert into ttrss_filter_types (id,name,description) values (4, 'link',
  187. 'Link');
  188. insert into ttrss_filter_types (id,name,description) values (5, 'date',
  189. 'Article Date');
  190. insert into ttrss_filter_types (id,name,description) values (6, 'author', 'Author');
  191. insert into ttrss_filter_types (id,name,description) values (7, 'tag', 'Article Tags');
  192. create table ttrss_filter_actions (id integer not null primary key,
  193. name varchar(120) unique not null,
  194. description varchar(250) not null unique) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  195. insert into ttrss_filter_actions (id,name,description) values (1, 'filter',
  196. 'Delete article');
  197. insert into ttrss_filter_actions (id,name,description) values (2, 'catchup',
  198. 'Mark as read');
  199. insert into ttrss_filter_actions (id,name,description) values (3, 'mark',
  200. 'Set starred');
  201. insert into ttrss_filter_actions (id,name,description) values (4, 'tag',
  202. 'Assign tags');
  203. insert into ttrss_filter_actions (id,name,description) values (5, 'publish',
  204. 'Publish article');
  205. insert into ttrss_filter_actions (id,name,description) values (6, 'score',
  206. 'Modify score');
  207. insert into ttrss_filter_actions (id,name,description) values (7, 'label',
  208. 'Assign label');
  209. insert into ttrss_filter_actions (id,name,description) values (8, 'stop',
  210. 'Stop / Do nothing');
  211. insert into ttrss_filter_actions (id,name,description) values (9, 'plugin',
  212. 'Invoke plugin');
  213. create table ttrss_filters2(id integer primary key auto_increment,
  214. owner_uid integer not null,
  215. match_any_rule boolean not null default false,
  216. enabled boolean not null default true,
  217. inverse bool not null default false,
  218. title varchar(250) not null default '',
  219. order_id integer not null default 0,
  220. last_triggered datetime default null,
  221. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  222. create table ttrss_filters2_rules(id integer primary key auto_increment,
  223. filter_id integer not null references ttrss_filters2(id) on delete cascade,
  224. reg_exp text not null,
  225. inverse bool not null default false,
  226. filter_type integer not null,
  227. feed_id integer default null,
  228. cat_id integer default null,
  229. cat_filter boolean not null default false,
  230. match_on text,
  231. index (filter_id),
  232. foreign key (filter_id) references ttrss_filters2(id) on delete cascade,
  233. index (filter_type),
  234. foreign key (filter_type) references ttrss_filter_types(id) ON DELETE CASCADE,
  235. index (feed_id),
  236. foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE,
  237. index (cat_id),
  238. foreign key (cat_id) references ttrss_feed_categories(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  239. create table ttrss_filters2_actions(id integer primary key auto_increment,
  240. filter_id integer not null,
  241. action_id integer not null default 1 references ttrss_filter_actions(id) on delete cascade,
  242. action_param varchar(250) not null default '',
  243. index (filter_id),
  244. foreign key (filter_id) references ttrss_filters2(id) on delete cascade,
  245. index (action_id),
  246. foreign key (action_id) references ttrss_filter_actions(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  247. create table ttrss_tags (id integer primary key auto_increment,
  248. owner_uid integer not null,
  249. tag_name varchar(250) not null,
  250. post_int_id integer not null,
  251. index (post_int_id),
  252. foreign key (post_int_id) references ttrss_user_entries(int_id) ON DELETE CASCADE,
  253. index (owner_uid),
  254. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  255. create table ttrss_version (schema_version int not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  256. insert into ttrss_version values (138);
  257. create table ttrss_enclosures (id integer primary key auto_increment,
  258. content_url text not null,
  259. content_type varchar(250) not null,
  260. post_id integer not null,
  261. title text not null,
  262. duration text not null,
  263. width integer not null default 0,
  264. height integer not null default 0,
  265. foreign key (post_id) references ttrss_entries(id) ON DELETE cascade) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  266. create table ttrss_settings_profiles(id integer primary key auto_increment,
  267. title varchar(250) not null,
  268. owner_uid integer not null,
  269. index (owner_uid),
  270. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  271. create table ttrss_prefs_types (id integer not null primary key,
  272. type_name varchar(100) not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  273. insert into ttrss_prefs_types (id, type_name) values (1, 'bool');
  274. insert into ttrss_prefs_types (id, type_name) values (2, 'string');
  275. insert into ttrss_prefs_types (id, type_name) values (3, 'integer');
  276. create table ttrss_prefs_sections (id integer not null primary key,
  277. order_id integer not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  278. insert into ttrss_prefs_sections (id, order_id) values (1, 0);
  279. insert into ttrss_prefs_sections (id, order_id) values (2, 1);
  280. insert into ttrss_prefs_sections (id, order_id) values (3, 3);
  281. insert into ttrss_prefs_sections (id, order_id) values (4, 2);
  282. create table ttrss_prefs (pref_name varchar(250) not null primary key,
  283. type_id integer not null,
  284. section_id integer not null default 1,
  285. access_level integer not null default 0,
  286. def_value text not null,
  287. foreign key (type_id) references ttrss_prefs_types(id),
  288. foreign key (section_id) references ttrss_prefs_sections(id)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  289. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('PURGE_OLD_DAYS', 3, '60', 1);
  290. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DEFAULT_UPDATE_INTERVAL', 3, '30', 1);
  291. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DEFAULT_ARTICLE_LIMIT', 3, '30', 2);
  292. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('ALLOW_DUPLICATE_POSTS', 1, 'false', 1);
  293. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('ENABLE_FEED_CATS', 1, 'true', 2);
  294. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('SHOW_CONTENT_PREVIEW', 1, 'true', 2);
  295. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('SHORT_DATE_FORMAT', 2, 'M d, G:i', 3);
  296. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('LONG_DATE_FORMAT', 2, 'D, M d Y - G:i', 3);
  297. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('COMBINED_DISPLAY_MODE', 1, 'true', 2);
  298. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('HIDE_READ_FEEDS', 1, 'false', 2);
  299. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('ON_CATCHUP_SHOW_NEXT_FEED', 1, 'false', 2);
  300. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('FEEDS_SORT_BY_UNREAD', 1, 'false', 2);
  301. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('REVERSE_HEADLINES', 1, 'false', 2);
  302. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DIGEST_ENABLE', 1, 'false', 4);
  303. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('CONFIRM_FEED_CATCHUP', 1, 'true', 2);
  304. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('CDM_AUTO_CATCHUP', 1, 'false', 2);
  305. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_DEFAULT_VIEW_MODE', 2, 'adaptive', 1);
  306. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_DEFAULT_VIEW_LIMIT', 3, '30', 1);
  307. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_PREFS_ACTIVE_TAB', 2, '', 1);
  308. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('STRIP_UNSAFE_TAGS', 1, 'true', 3);
  309. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('BLACKLISTED_TAGS', 2, 'main, generic, misc, uncategorized, blog, blogroll, general, news', 3);
  310. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('FRESH_ARTICLE_MAX_AGE', 3, '24', 2);
  311. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DIGEST_CATCHUP', 1, 'false', 4);
  312. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('CDM_EXPANDED', 1, 'true', 2);
  313. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('PURGE_UNREAD_ARTICLES', 1, 'true', 3);
  314. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('HIDE_READ_SHOWS_SPECIAL', 1, 'true', 2);
  315. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('VFEED_GROUP_BY_FEED', 1, 'false', 2);
  316. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('STRIP_IMAGES', 1, 'false', 2);
  317. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_DEFAULT_VIEW_ORDER_BY', 2, 'default', 1);
  318. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('ENABLE_API_ACCESS', 1, 'false', 1);
  319. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_COLLAPSED_SPECIAL', 1, 'false', 1);
  320. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_COLLAPSED_LABELS', 1, 'false', 1);
  321. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_COLLAPSED_UNCAT', 1, 'false', 1);
  322. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_COLLAPSED_FEEDLIST', 1, 'false', 1);
  323. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_ENABLE_CATS', 1, 'false', 1);
  324. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_SHOW_IMAGES', 1, 'false', 1);
  325. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_HIDE_READ', 1, 'false', 1);
  326. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_SORT_FEEDS_UNREAD', 1, 'false', 1);
  327. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_THEME_ID', 2, '0', 1);
  328. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_TIMEZONE', 2, 'Automatic', 1);
  329. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_STYLESHEET', 2, '', 2);
  330. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('SORT_HEADLINES_BY_FEED_DATE', 1, 'false', 2);
  331. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_BROWSE_CATS', 1, 'true', 1);
  332. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('SSL_CERT_SERIAL', 2, '', 3);
  333. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DIGEST_PREFERRED_TIME', 2, '00:00', 4);
  334. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_PREFS_SHOW_EMPTY_CATS', 1, 'false', 1);
  335. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_DEFAULT_INCLUDE_CHILDREN', 1, 'false', 1);
  336. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('AUTO_ASSIGN_LABELS', 1, 'false', 3);
  337. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_ENABLED_PLUGINS', 2, '', 1);
  338. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_REVERSE_HEADLINES', 1, 'false', 1);
  339. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_CSS_THEME', 2, '', 2);
  340. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_LANGUAGE', 2, '', 2);
  341. insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DEFAULT_SEARCH_LANGUAGE', 2, '', 2);
  342. update ttrss_prefs set access_level = 1 where pref_name in ('ON_CATCHUP_SHOW_NEXT_FEED',
  343. 'SORT_HEADLINES_BY_FEED_DATE',
  344. 'VFEED_GROUP_BY_FEED',
  345. 'FRESH_ARTICLE_MAX_AGE',
  346. 'CDM_EXPANDED',
  347. 'SHOW_CONTENT_PREVIEW',
  348. 'AUTO_ASSIGN_LABELS',
  349. 'HIDE_READ_SHOWS_SPECIAL');
  350. create table ttrss_user_prefs (
  351. owner_uid integer not null,
  352. pref_name varchar(250),
  353. value longtext not null,
  354. profile integer,
  355. index (profile),
  356. foreign key (profile) references ttrss_settings_profiles(id) ON DELETE CASCADE,
  357. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE,
  358. foreign key (pref_name) references ttrss_prefs(pref_name) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  359. create table ttrss_sessions (id varchar(250) not null primary key,
  360. data text,
  361. expire integer not null,
  362. index (expire)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  363. create table ttrss_feedbrowser_cache (
  364. feed_url text not null,
  365. site_url text not null,
  366. title text not null,
  367. subscribers integer not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  368. create table ttrss_labels2 (id integer not null primary key auto_increment,
  369. owner_uid integer not null,
  370. caption varchar(250) not null,
  371. fg_color varchar(15) not null default '',
  372. bg_color varchar(15) not null default '',
  373. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE
  374. ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  375. create table ttrss_user_labels2 (label_id integer not null,
  376. article_id integer not null,
  377. foreign key (label_id) references ttrss_labels2(id) ON DELETE CASCADE,
  378. foreign key (article_id) references ttrss_entries(id) ON DELETE CASCADE
  379. ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  380. create table ttrss_access_keys (id integer not null primary key auto_increment,
  381. access_key varchar(250) not null,
  382. feed_id varchar(250) not null,
  383. is_cat bool not null default false,
  384. owner_uid integer not null,
  385. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  386. create table ttrss_linked_instances (id integer not null primary key auto_increment,
  387. last_connected datetime not null,
  388. last_status_in integer not null,
  389. last_status_out integer not null,
  390. access_key varchar(250) not null unique,
  391. access_url text not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  392. create table ttrss_linked_feeds (
  393. feed_url text not null,
  394. site_url text not null,
  395. title text not null,
  396. created datetime not null,
  397. updated datetime not null,
  398. instance_id integer not null,
  399. subscribers integer not null,
  400. foreign key (instance_id) references ttrss_linked_instances(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  401. create table ttrss_plugin_storage (
  402. id integer not null auto_increment primary key,
  403. name varchar(100) not null,
  404. owner_uid integer not null,
  405. content longtext not null,
  406. foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  407. create table ttrss_error_log(
  408. id integer not null auto_increment primary key,
  409. owner_uid integer,
  410. errno integer not null,
  411. errstr text not null,
  412. filename text not null,
  413. lineno integer not null,
  414. context text not null,
  415. created_at datetime not null,
  416. foreign key (owner_uid) references ttrss_users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  417. commit;