schema/ttrss_schema_mysql.sql

   1 SET NAMES utf8;
   2 SET CHARACTER SET utf8;
   3
   4 drop table if exists ttrss_error_log;
   5 drop table if exists ttrss_plugin_storage;
   6 drop table if exists ttrss_linked_feeds;
   7 drop table if exists ttrss_linked_instances;
   8 drop table if exists ttrss_access_keys;
   9 drop table if exists ttrss_user_labels2;
  10 drop table if exists ttrss_labels2;
  11 drop table if exists ttrss_feedbrowser_cache;
  12 drop table if exists ttrss_version;
  13 drop table if exists ttrss_labels;
  14 drop table if exists ttrss_filters2_actions;
  15 drop table if exists ttrss_filters2_rules;
  16 drop table if exists ttrss_filters2;
  17 drop table if exists ttrss_filters;
  18 drop table if exists ttrss_filter_types;
  19 drop table if exists ttrss_filter_actions;
  20 drop table if exists ttrss_user_prefs;
  21 drop table if exists ttrss_prefs;
  22 drop table if exists ttrss_prefs_types;
  23 drop table if exists ttrss_prefs_sections;
  24 drop table if exists ttrss_tags;
  25 drop table if exists ttrss_enclosures;
  26 drop table if exists ttrss_settings_profiles;
  27 drop table if exists ttrss_entry_comments;
  28 drop table if exists ttrss_user_entries;
  29 drop table if exists ttrss_entries;
  30 drop table if exists ttrss_scheduled_updates;
  31 drop table if exists ttrss_counters_cache;
  32 drop table if exists ttrss_cat_counters_cache;
  33 drop table if exists ttrss_feeds;
  34 drop table if exists ttrss_archived_feeds;
  35 drop table if exists ttrss_feed_categories;
  36 drop table if exists ttrss_users;
  37 drop table if exists ttrss_themes;
  38 drop table if exists ttrss_sessions;
  39
  40 begin;
  41
  42 create 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,
  45         last_login datetime default null,
  46         access_level integer not null default 0,
  47         email varchar(250) not null default '',
  48         full_name varchar(250) not null default '',
  49         email_digest bool not null default false,
  50         last_digest_sent datetime default null,
  51         salt varchar(250) not null default '',
  52         created datetime default null,
  53         twitter_oauth longtext default null,
  54         otp_enabled boolean not null default false,
  55         resetpass_token varchar(250) default null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  56
  57 insert into ttrss_users (login,pwd_hash,access_level) values ('admin',
  58         'SHA1:5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8', 10);
  59
  60 create table ttrss_feed_categories(id integer not null primary key auto_increment,
  61         owner_uid integer not null,
  62         title varchar(200) not null,
  63         collapsed bool not null default false,
  64         order_id integer not null default 0,
  65         parent_cat integer,
  66         view_settings varchar(250) not null default '',
  67         index(parent_cat),
  68         foreign key (parent_cat) references ttrss_feed_categories(id) ON DELETE SET NULL,
  69         index(owner_uid),
  70         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  71
  72 create table ttrss_archived_feeds (id integer not null primary key,
  73         owner_uid integer not null,
  74         title varchar(200) not null,
  75         feed_url text not null,
  76         site_url varchar(250) not null default '',
  77         index(owner_uid),
  78         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  79
  80 create table ttrss_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
  88 create index ttrss_counters_cache_feed_id_idx on ttrss_counters_cache(feed_id);
  89 create index ttrss_counters_cache_owner_uid_idx on ttrss_counters_cache(owner_uid);
  90 create index ttrss_counters_cache_value_idx on ttrss_counters_cache(value);
  91
  92 create table ttrss_cat_counters_cache (
  93         feed_id integer not null,
  94         owner_uid integer not null,
  95         value integer not null default 0,
  96         updated datetime not null,
  97         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE
  98 ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
  99
 100 create index ttrss_cat_counters_cache_owner_uid_idx on ttrss_cat_counters_cache(owner_uid);
 101
 102 create table ttrss_feeds (id integer not null auto_increment primary key,
 103         owner_uid integer not null,
 104         title varchar(200) not null,
 105         cat_id integer default null,
 106         feed_url text not null,
 107         icon_url varchar(250) not null default '',
 108         update_interval integer not null default 0,
 109         purge_interval integer not null default 0,
 110         last_updated datetime default 0,
 111         last_error varchar(250) not null default '',
 112         favicon_avg_color varchar(11) default null,
 113         site_url varchar(250) not null default '',
 114         auth_login varchar(250) not null default '',
 115         auth_pass varchar(250) not null default '',
 116         parent_feed integer default null,
 117         private bool not null default false,
 118         rtl_content bool not null default false,
 119         hidden bool not null default false,
 120         include_in_digest boolean not null default true,
 121         cache_images boolean not null default false,
 122         hide_images boolean not null default false,
 123         cache_content boolean not null default false,
 124         auth_pass_encrypted boolean not null default false,
 125         last_viewed datetime default null,
 126         last_update_started datetime default null,
 127         always_display_enclosures boolean not null default false,
 128         update_method integer not null default 0,
 129         order_id integer not null default 0,
 130         mark_unread_on_update boolean not null default false,
 131         update_on_checksum_change boolean not null default false,
 132         strip_images boolean not null default false,
 133         view_settings varchar(250) not null default '',
 134         pubsub_state integer not null default 0,
 135         favicon_last_checked datetime default null,
 136         index(owner_uid),
 137         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE,
 138         index(cat_id),
 139         foreign key (cat_id) references ttrss_feed_categories(id) ON DELETE SET NULL,
 140         index(parent_feed),
 141         foreign key (parent_feed) references ttrss_feeds(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 142
 143 create index ttrss_feeds_owner_uid_index on ttrss_feeds(owner_uid);
 144 create index ttrss_feeds_cat_id_idx on ttrss_feeds(cat_id);
 145
 146 insert into ttrss_feeds (owner_uid, title, feed_url) values
 147         (1, 'Tiny Tiny RSS: New Releases', 'http://tt-rss.org/releases.rss');
 148
 149 insert into ttrss_feeds (owner_uid, title, feed_url) values
 150         (1, 'Tiny Tiny RSS: Forum', 'http://tt-rss.org/forum/rss.php');
 151
 152 create table ttrss_entries (id integer not null primary key auto_increment,
 153         title text not null,
 154         guid varchar(255) not null unique,
 155         link text not null,
 156         updated datetime not null,
 157         content longtext not null,
 158         content_hash varchar(250) not null,
 159         cached_content longtext,
 160         no_orig_date bool not null default 0,
 161         date_entered datetime not null,
 162         date_updated datetime not null,
 163         num_comments integer not null default 0,
 164         plugin_data longtext,
 165         lang varchar(2),
 166         comments varchar(250) not null default '',
 167         author varchar(250) not null default '') ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 168
 169 create index ttrss_entries_date_entered_index on ttrss_entries(date_entered);
 170 create index ttrss_entries_guid_index on ttrss_entries(guid);
 171 create index ttrss_entries_updated_idx on ttrss_entries(updated);
 172
 173 create table ttrss_user_entries (
 174         int_id integer not null primary key auto_increment,
 175         ref_id integer not null,
 176         uuid varchar(200) not null,
 177         feed_id int,
 178         orig_feed_id int,
 179         owner_uid integer not null,
 180         marked bool not null default 0,
 181         published bool not null default 0,
 182         tag_cache text not null,
 183         label_cache text not null,
 184         last_read datetime,
 185         score int not null default 0,
 186         note longtext,
 187         last_marked datetime,
 188         last_published datetime,
 189         unread bool not null default 1,
 190         index (ref_id),
 191         foreign key (ref_id) references ttrss_entries(id) ON DELETE CASCADE,
 192         index (feed_id),
 193         foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE,
 194         index (orig_feed_id),
 195         foreign key (orig_feed_id) references ttrss_archived_feeds(id) ON DELETE SET NULL,
 196         index (owner_uid),
 197         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 198
 199 create index ttrss_user_entries_owner_uid_index on ttrss_user_entries(owner_uid);
 200 create index ttrss_user_entries_ref_id_index on ttrss_user_entries(ref_id);
 201 create index ttrss_user_entries_feed_id on ttrss_user_entries(feed_id);
 202 create index ttrss_user_entries_unread_idx on ttrss_user_entries(unread);
 203
 204 create 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),
 212         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 213
 214 create table ttrss_filter_types (id integer primary key,
 215         name varchar(120) unique not null,
 216         description varchar(250) not null unique) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 217
 218 insert into ttrss_filter_types (id,name,description) values (1, 'title', 'Title');
 219 insert into ttrss_filter_types (id,name,description) values (2, 'content', 'Content');
 220 insert into ttrss_filter_types (id,name,description) values (3, 'both',
 221         'Title or Content');
 222 insert into ttrss_filter_types (id,name,description) values (4, 'link',
 223         'Link');
 224 insert into ttrss_filter_types (id,name,description) values (5, 'date',
 225         'Article Date');
 226 insert into ttrss_filter_types (id,name,description) values (6, 'author', 'Author');
 227 insert into ttrss_filter_types (id,name,description) values (7, 'tag', 'Article Tags');
 228
 229 create table ttrss_filter_actions (id integer not null primary key,
 230         name varchar(120) unique not null,
 231         description varchar(250) not null unique) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 232
 233 insert into ttrss_filter_actions (id,name,description) values (1, 'filter',
 234         'Delete article');
 235
 236 insert into ttrss_filter_actions (id,name,description) values (2, 'catchup',
 237         'Mark as read');
 238
 239 insert into ttrss_filter_actions (id,name,description) values (3, 'mark',
 240         'Set starred');
 241
 242 insert into ttrss_filter_actions (id,name,description) values (4, 'tag',
 243         'Assign tags');
 244
 245 insert into ttrss_filter_actions (id,name,description) values (5, 'publish',
 246         'Publish article');
 247
 248 insert into ttrss_filter_actions (id,name,description) values (6, 'score',
 249         'Modify score');
 250
 251 insert into ttrss_filter_actions (id,name,description) values (7, 'label',
 252         'Assign label');
 253
 254 insert into ttrss_filter_actions (id,name,description) values (8, 'stop',
 255         'Stop / Do nothing');
 256
 257 create 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,
 261         inverse bool not null default false,
 262         title varchar(250) not null default '',
 263         order_id integer not null default 0,
 264         index(owner_uid),
 265         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 266
 267 create 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,
 270         inverse bool not null default false,
 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
 284 create 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
 293 create table ttrss_tags (id integer primary key auto_increment,
 294         owner_uid integer not null,
 295         tag_name varchar(250) not null,
 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,
 299         index (owner_uid),
 300         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 301
 302 create table ttrss_version (schema_version int not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 303
 304 insert into ttrss_version values (126);
 305
 306 create table ttrss_enclosures (id integer primary key auto_increment,
 307         content_url text not null,
 308         content_type varchar(250) not null,
 309         post_id integer not null,
 310         title text not null,
 311         duration text not null,
 312         width integer not null default 0,
 313         height integer not null default 0,
 314         index (post_id),
 315         foreign key (post_id) references ttrss_entries(id) ON DELETE cascade) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 316
 317 create index ttrss_enclosures_post_id_idx on ttrss_enclosures(post_id);
 318
 319 create table ttrss_settings_profiles(id integer primary key auto_increment,
 320         title varchar(250) not null,
 321         owner_uid integer not null,
 322         index (owner_uid),
 323         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 324
 325 create table ttrss_prefs_types (id integer not null primary key,
 326         type_name varchar(100) not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 327
 328 insert into ttrss_prefs_types (id, type_name) values (1, 'bool');
 329 insert into ttrss_prefs_types (id, type_name) values (2, 'string');
 330 insert into ttrss_prefs_types (id, type_name) values (3, 'integer');
 331
 332 create table ttrss_prefs_sections (id integer not null primary key,
 333         order_id integer not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 334
 335 insert into ttrss_prefs_sections (id, order_id) values (1, 0);
 336 insert into ttrss_prefs_sections (id, order_id) values (2, 1);
 337 insert into ttrss_prefs_sections (id, order_id) values (3, 3);
 338 insert into ttrss_prefs_sections (id, order_id) values (4, 2);
 339
 340 create table ttrss_prefs (pref_name varchar(250) not null primary key,
 341         type_id integer not null,
 342         section_id integer not null default 1,
 343         access_level integer not null default 0,
 344         def_value text not null,
 345         index(type_id),
 346         foreign key (type_id) references ttrss_prefs_types(id),
 347         index(section_id),
 348         foreign key (section_id) references ttrss_prefs_sections(id)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 349
 350 create index ttrss_prefs_pref_name_idx on ttrss_prefs(pref_name);
 351
 352 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('PURGE_OLD_DAYS', 3, '60', 1);
 353 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DEFAULT_UPDATE_INTERVAL', 3, '30', 1);
 354 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DEFAULT_ARTICLE_LIMIT', 3, '30', 2);
 355 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('ALLOW_DUPLICATE_POSTS', 1, 'false', 1);
 356 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('ENABLE_FEED_CATS', 1, 'true', 2);
 357 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('SHOW_CONTENT_PREVIEW', 1, 'true', 2);
 358 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('SHORT_DATE_FORMAT', 2, 'M d, G:i', 3);
 359 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('LONG_DATE_FORMAT', 2, 'D, M d Y - G:i', 3);
 360 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('COMBINED_DISPLAY_MODE', 1, 'true', 2);
 361 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('HIDE_READ_FEEDS', 1, 'false', 2);
 362 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('ON_CATCHUP_SHOW_NEXT_FEED', 1, 'false', 2);
 363 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('FEEDS_SORT_BY_UNREAD', 1, 'false', 2);
 364 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('REVERSE_HEADLINES', 1, 'false', 2);
 365 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DIGEST_ENABLE', 1, 'false', 4);
 366 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('CONFIRM_FEED_CATCHUP', 1, 'true', 2);
 367 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('CDM_AUTO_CATCHUP', 1, 'false', 2);
 368 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_DEFAULT_VIEW_MODE', 2, 'adaptive', 1);
 369 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_DEFAULT_VIEW_LIMIT', 3, '30', 1);
 370 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_PREFS_ACTIVE_TAB', 2, '', 1);
 371 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('STRIP_UNSAFE_TAGS', 1, 'true', 3);
 372 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);
 373 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('FRESH_ARTICLE_MAX_AGE', 3, '24', 2);
 374 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DIGEST_CATCHUP', 1, 'false', 4);
 375 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('CDM_EXPANDED', 1, 'true', 2);
 376 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('PURGE_UNREAD_ARTICLES', 1, 'true', 3);
 377 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('HIDE_READ_SHOWS_SPECIAL', 1, 'true', 2);
 378 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('VFEED_GROUP_BY_FEED', 1, 'false', 2);
 379 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('STRIP_IMAGES', 1, 'false', 2);
 380 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_DEFAULT_VIEW_ORDER_BY', 2, 'default', 1);
 381 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('ENABLE_API_ACCESS', 1, 'false', 1);
 382 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_COLLAPSED_SPECIAL', 1, 'false', 1);
 383 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_COLLAPSED_LABELS', 1, 'false', 1);
 384 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_COLLAPSED_UNCAT', 1, 'false', 1);
 385 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_COLLAPSED_FEEDLIST', 1, 'false', 1);
 386 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_ENABLE_CATS', 1, 'false', 1);
 387 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_SHOW_IMAGES', 1, 'false', 1);
 388 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_HIDE_READ', 1, 'false', 1);
 389 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_SORT_FEEDS_UNREAD', 1, 'false', 1);
 390 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_THEME_ID', 2, '0', 1);
 391 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_TIMEZONE', 2, 'Automatic', 1);
 392 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_STYLESHEET', 2, '', 2);
 393 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('SORT_HEADLINES_BY_FEED_DATE', 1, 'false', 2);
 394 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_BROWSE_CATS', 1, 'true', 1);
 395 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('SSL_CERT_SERIAL', 2, '', 3);
 396 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('DIGEST_PREFERRED_TIME', 2, '00:00', 4);
 397 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_PREFS_SHOW_EMPTY_CATS', 1, 'false', 1);
 398 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_DEFAULT_INCLUDE_CHILDREN', 1, 'false', 1);
 399 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('AUTO_ASSIGN_LABELS', 1, 'false', 3);
 400 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_ENABLED_PLUGINS', 2, '', 1);
 401 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('_MOBILE_REVERSE_HEADLINES', 1, 'false', 1);
 402 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_CSS_THEME', 2, '', 2);
 403 insert into ttrss_prefs (pref_name,type_id,def_value,section_id) values('USER_LANGUAGE', 2, '', 2);
 404
 405 update ttrss_prefs set access_level = 1 where pref_name in ('ON_CATCHUP_SHOW_NEXT_FEED',
 406         'SORT_HEADLINES_BY_FEED_DATE',
 407         'VFEED_GROUP_BY_FEED',
 408         'FRESH_ARTICLE_MAX_AGE',
 409         'CDM_EXPANDED',
 410         'SHOW_CONTENT_PREVIEW',
 411         'AUTO_ASSIGN_LABELS',
 412         'HIDE_READ_SHOWS_SPECIAL');
 413
 414 create table ttrss_user_prefs (
 415    owner_uid integer not null,
 416    pref_name varchar(250),
 417    value longtext not null,
 418         profile integer,
 419         index (profile),
 420         foreign key (profile) references ttrss_settings_profiles(id) ON DELETE CASCADE,
 421         index (owner_uid),
 422         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE,
 423         index (pref_name),
 424         foreign key (pref_name) references ttrss_prefs(pref_name) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 425
 426 create index ttrss_user_prefs_owner_uid_index on ttrss_user_prefs(owner_uid);
 427 create index ttrss_user_prefs_pref_name_idx on ttrss_user_prefs(pref_name);
 428
 429 create table ttrss_sessions (id varchar(250) unique not null primary key,
 430         data text,
 431         expire integer not null,
 432         index (id),
 433         index (expire)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 434
 435 create table ttrss_feedbrowser_cache (
 436         feed_url text not null,
 437         site_url text not null,
 438         title text not null,
 439         subscribers integer not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 440
 441 create table ttrss_labels2 (id integer not null primary key auto_increment,
 442         owner_uid integer not null,
 443         caption varchar(250) not null,
 444         fg_color varchar(15) not null default '',
 445         bg_color varchar(15) not null default '',
 446         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE
 447 ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 448
 449 create table ttrss_user_labels2 (label_id integer not null,
 450         article_id integer not null,
 451         foreign key (label_id) references ttrss_labels2(id) ON DELETE CASCADE,
 452         foreign key (article_id) references ttrss_entries(id) ON DELETE CASCADE
 453 ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 454
 455 create table ttrss_access_keys (id integer not null primary key auto_increment,
 456         access_key varchar(250) not null,
 457         feed_id varchar(250) not null,
 458         is_cat bool not null default false,
 459         owner_uid integer not null,
 460         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 461
 462 create table ttrss_linked_instances (id integer not null primary key auto_increment,
 463         last_connected datetime not null,
 464         last_status_in integer not null,
 465         last_status_out integer not null,
 466         access_key varchar(250) not null unique,
 467         access_url text not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 468
 469 create table ttrss_linked_feeds (
 470         feed_url text not null,
 471         site_url text not null,
 472         title text not null,
 473         created datetime not null,
 474         updated datetime not null,
 475         instance_id integer not null,
 476         subscribers integer not null,
 477         foreign key (instance_id) references ttrss_linked_instances(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 478
 479 create table ttrss_plugin_storage (
 480         id integer not null auto_increment primary key,
 481         name varchar(100) not null,
 482         owner_uid integer not null,
 483         content longtext not null,
 484         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 485
 486 create table ttrss_error_log(
 487         id integer not null auto_increment primary key,
 488         owner_uid integer,
 489         errno integer not null,
 490         errstr text not null,
 491         filename text not null,
 492         lineno integer not null,
 493         context text not null,
 494         created_at datetime not null,
 495         foreign key (owner_uid) references ttrss_users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 496
 497 commit;