schema/ttrss_schema_pgsql.sql

   1 drop table ttrss_access_keys;
   2 drop table ttrss_user_labels2;
   3 drop table ttrss_labels2;
   4 drop table ttrss_feedbrowser_cache;
   5 drop table ttrss_version;
   6 drop table ttrss_labels;
   7 drop table ttrss_filters;
   8 drop table ttrss_filter_types;
   9 drop table ttrss_filter_actions;
  10 drop table ttrss_user_prefs;
  11 drop table ttrss_prefs;
  12 drop table ttrss_prefs_types;
  13 drop table ttrss_prefs_sections;
  14 drop table ttrss_tags;
  15 drop table ttrss_enclosures;
  16 drop table ttrss_settings_profiles;
  17 drop table ttrss_entry_comments;
  18 drop table ttrss_user_entries;
  19 drop table ttrss_entries;
  20 drop table ttrss_scheduled_updates;
  21 drop table ttrss_counters_cache;
  22 drop table ttrss_cat_counters_cache;
  23 drop table ttrss_archived_feeds;
  24 drop table ttrss_feeds;
  25 drop table ttrss_feed_categories;
  26 drop table ttrss_users;
  27 drop table ttrss_themes;
  28 drop table ttrss_sessions;
  29 drop function SUBSTRING_FOR_DATE(timestamp, int, int);
  30
  31 begin;
  32
  33 create table ttrss_themes(id serial not null primary key,
  34         theme_name varchar(200) not null,
  35         theme_path varchar(200) not null);
  36
  37 insert into ttrss_themes (theme_name, theme_path) values ('Old-skool', 'compat');
  38 insert into ttrss_themes (theme_name, theme_path) values ('Graycube', 'graycube');
  39 insert into ttrss_themes (theme_name, theme_path) values ('Default (Compact)', 'compact');
  40 insert into ttrss_themes (theme_name, theme_path) values ('Three-pane', '3pane');
  41
  42 create table ttrss_users (id serial not null primary key,
  43         login varchar(120) not null unique,
  44         pwd_hash varchar(250) not null,
  45         last_login timestamp 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 boolean not null default false,
  50         last_digest_sent timestamp default null,
  51         twitter_oauth text default null,
  52         created timestamp default null,
  53         theme_id integer references ttrss_themes(id) default null);
  54
  55 insert into ttrss_users (login,pwd_hash,access_level) values ('admin',
  56         'SHA1:5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8', 10);
  57
  58 create table ttrss_feed_categories(id serial not null primary key,
  59         owner_uid integer not null references ttrss_users(id) on delete cascade,
  60         collapsed boolean not null default false,
  61         order_id integer not null default 0,
  62         title varchar(200) not null);
  63
  64 create table ttrss_feeds (id serial not null primary key,
  65         owner_uid integer not null references ttrss_users(id) on delete cascade,
  66         title varchar(200) not null,
  67         cat_id integer default null references ttrss_feed_categories(id) on delete set null,
  68         feed_url text not null,
  69         icon_url varchar(250) not null default '',
  70         update_interval integer not null default 0,
  71         purge_interval integer not null default 0,
  72         last_updated timestamp default null,
  73         last_error text not null default '',
  74         site_url varchar(250) not null default '',
  75         auth_login varchar(250) not null default '',
  76         parent_feed integer default null references ttrss_feeds(id) on delete set null,
  77         private boolean not null default false,
  78         auth_pass varchar(250) not null default '',
  79         hidden boolean not null default false,
  80         include_in_digest boolean not null default true,
  81         rtl_content boolean not null default false,
  82         cache_images boolean not null default false,
  83         last_viewed timestamp default null,
  84         last_update_started timestamp default null,
  85         update_method integer not null default 0,
  86         always_display_enclosures boolean not null default false,
  87         order_id integer not null default 0,
  88         mark_unread_on_update boolean not null default false,
  89         update_on_checksum_change boolean not null default false,
  90         strip_images boolean not null default false,
  91         pubsub_state integer not null default 0,
  92         auth_pass_encrypted boolean not null default false);
  93
  94 create index ttrss_feeds_owner_uid_index on ttrss_feeds(owner_uid);
  95
  96 insert into ttrss_feeds (owner_uid, title, feed_url) values
  97         (1, 'Tiny Tiny RSS: New Releases', 'http://tt-rss.org/releases.rss');
  98
  99 insert into ttrss_feeds (owner_uid, title, feed_url) values
 100         (1, 'Tiny Tiny RSS: Forum', 'http://tt-rss.org/forum/rss.php');
 101
 102 create table ttrss_archived_feeds (id integer not null primary key,
 103         owner_uid integer not null references ttrss_users(id) on delete cascade,
 104         title varchar(200) not null,
 105         feed_url text not null,
 106         site_url varchar(250) not null default '');
 107
 108 create table ttrss_counters_cache (
 109         feed_id integer not null,
 110         owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE,
 111         updated timestamp not null,
 112         value integer not null default 0);
 113
 114 create index ttrss_counters_cache_feed_id_idx on ttrss_counters_cache(feed_id);
 115 create index ttrss_counters_cache_owner_uid_idx on ttrss_counters_cache(owner_uid);
 116 create index ttrss_counters_cache_value_idx on ttrss_counters_cache(value);
 117
 118 create table ttrss_cat_counters_cache (
 119         feed_id integer not null,
 120         owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE,
 121         updated timestamp not null,
 122         value integer not null default 0);
 123
 124 create index ttrss_cat_counters_cache_owner_uid_idx on ttrss_cat_counters_cache(owner_uid);
 125
 126 create table ttrss_entries (id serial not null primary key,
 127         title text not null,
 128         guid text not null unique,
 129         link text not null,
 130         updated timestamp not null,
 131         content text not null,
 132         content_hash varchar(250) not null,
 133         no_orig_date boolean not null default false,
 134         date_entered timestamp not null,
 135         date_updated timestamp not null,
 136         num_comments integer not null default 0,
 137         comments varchar(250) not null default '',
 138         author varchar(250) not null default '');
 139
 140 create index ttrss_entries_guid_index on ttrss_entries(guid);
 141 -- create index ttrss_entries_title_index on ttrss_entries(title);
 142 create index ttrss_entries_date_entered_index on ttrss_entries(date_entered);
 143 create index ttrss_entries_updated_idx on ttrss_entries(updated);
 144
 145 create table ttrss_user_entries (
 146         int_id serial not null primary key,
 147         ref_id integer not null references ttrss_entries(id) ON DELETE CASCADE,
 148         feed_id int references ttrss_feeds(id) ON DELETE CASCADE,
 149         orig_feed_id integer references ttrss_archived_feeds(id) ON DELETE SET NULL,
 150         owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE,
 151         marked boolean not null default false,
 152         published boolean not null default false,
 153         tag_cache text not null,
 154         label_cache text not null,
 155         last_read timestamp,
 156         score int not null default 0,
 157         note text,
 158         unread boolean not null default true);
 159
 160 -- create index ttrss_user_entries_feed_id_index on ttrss_user_entries(feed_id);
 161 create index ttrss_user_entries_owner_uid_index on ttrss_user_entries(owner_uid);
 162 create index ttrss_user_entries_ref_id_index on ttrss_user_entries(ref_id);
 163 create index ttrss_user_entries_feed_id on ttrss_user_entries(feed_id);
 164 create index ttrss_user_entries_unread_idx on ttrss_user_entries(unread);
 165
 166 create table ttrss_entry_comments (id serial not null primary key,
 167         ref_id integer not null references ttrss_entries(id) ON DELETE CASCADE,
 168         owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE,
 169         private boolean not null default false,
 170         date_entered timestamp not null);
 171
 172 create index ttrss_entry_comments_ref_id_index on ttrss_entry_comments(ref_id);
 173 -- create index ttrss_entry_comments_owner_uid_index on ttrss_entry_comments(owner_uid);
 174
 175 create table ttrss_filter_types (id integer not null primary key,
 176         name varchar(120) unique not null,
 177         description varchar(250) not null unique);
 178
 179 insert into ttrss_filter_types (id,name,description) values (1, 'title', 'Title');
 180 insert into ttrss_filter_types (id,name,description) values (2, 'content', 'Content');
 181 insert into ttrss_filter_types (id,name,description) values (3, 'both',
 182         'Title or Content');
 183 insert into ttrss_filter_types (id,name,description) values (4, 'link',
 184         'Link');
 185 insert into ttrss_filter_types (id,name,description) values (5, 'date',
 186         'Article Date');
 187 insert into ttrss_filter_types (id,name,description) values (6, 'author', 'Author');
 188 insert into ttrss_filter_types (id,name,description) values (7, 'tag', 'Article Tags');
 189
 190 create table ttrss_filter_actions (id integer not null primary key,
 191         name varchar(120) unique not null,
 192         description varchar(250) not null unique);
 193
 194 insert into ttrss_filter_actions (id,name,description) values (1, 'filter',
 195         'Delete article');
 196
 197 insert into ttrss_filter_actions (id,name,description) values (2, 'catchup',
 198         'Mark as read');
 199
 200 insert into ttrss_filter_actions (id,name,description) values (3, 'mark',
 201         'Set starred');
 202
 203 insert into ttrss_filter_actions (id,name,description) values (4, 'tag',
 204         'Assign tags');
 205
 206 insert into ttrss_filter_actions (id,name,description) values (5, 'publish',
 207         'Publish article');
 208
 209 insert into ttrss_filter_actions (id,name,description) values (6, 'score',
 210         'Modify score');
 211
 212 insert into ttrss_filter_actions (id,name,description) values (7, 'label',
 213         'Assign label');
 214
 215 create table ttrss_filters (id serial not null primary key,
 216         owner_uid integer not null references ttrss_users(id) on delete cascade,
 217         feed_id integer references ttrss_feeds(id) on delete cascade default null,
 218         filter_type integer not null references ttrss_filter_types(id),
 219         reg_exp varchar(250) not null,
 220         filter_param varchar(250) not null default '',
 221         enabled boolean not null default true,
 222         inverse boolean not null default false,
 223         action_id integer not null default 1 references ttrss_filter_actions(id) on delete cascade,
 224         action_param varchar(250) not null default '');
 225
 226 create table ttrss_tags (id serial not null primary key,
 227         tag_name varchar(250) not null,
 228         owner_uid integer not null references ttrss_users(id) on delete cascade,
 229         post_int_id integer references ttrss_user_entries(int_id) ON DELETE CASCADE not null);
 230
 231 create index ttrss_tags_owner_uid_index on ttrss_tags(owner_uid);
 232 create index ttrss_tags_post_int_id_idx on ttrss_tags(post_int_id);
 233
 234 create table ttrss_version (schema_version int not null);
 235
 236 insert into ttrss_version values (83);
 237
 238 create table ttrss_enclosures (id serial not null primary key,
 239         content_url text not null,
 240         content_type varchar(250) not null,
 241         title text not null,
 242         duration text not null,
 243         post_id integer references ttrss_entries(id) ON DELETE cascade NOT NULL);
 244
 245 create table ttrss_settings_profiles(id serial not null primary key,
 246         title varchar(250) not null,
 247         owner_uid integer not null references ttrss_users(id) on delete cascade);
 248
 249 create table ttrss_prefs_types (id integer not null primary key,
 250         type_name varchar(100) not null);
 251
 252 insert into ttrss_prefs_types (id, type_name) values (1, 'bool');
 253 insert into ttrss_prefs_types (id, type_name) values (2, 'string');
 254 insert into ttrss_prefs_types (id, type_name) values (3, 'integer');
 255
 256 create table ttrss_prefs_sections (id integer not null primary key,
 257         section_name varchar(100) not null);
 258
 259 insert into ttrss_prefs_sections (id, section_name) values (1, 'General');
 260 insert into ttrss_prefs_sections (id, section_name) values (2, 'Interface');
 261 insert into ttrss_prefs_sections (id, section_name) values (3, 'Advanced');
 262
 263 create table ttrss_prefs (pref_name varchar(250) not null primary key,
 264         type_id integer not null references ttrss_prefs_types(id),
 265         section_id integer not null references ttrss_prefs_sections(id) default 1,
 266         short_desc text not null,
 267         help_text text not null default '',
 268         access_level integer not null default 0,
 269         def_value text not null);
 270
 271 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('PURGE_OLD_DAYS', 3, '60', 'Purge old posts after this number of days (0 - disables)',1);
 272
 273 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('DEFAULT_UPDATE_INTERVAL', 3, '30', 'Default interval between feed updates',1);
 274
 275 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('DEFAULT_ARTICLE_LIMIT', 3, '30', 'Amount of articles to display at once',2);
 276
 277 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('ALLOW_DUPLICATE_POSTS', 1, 'true', 'Allow duplicate posts',1, 'This option is useful when you are reading several planet-type aggregators with partially colliding userbase. When disabled, it forces same posts from different feeds to appear only once.');
 278
 279 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ENABLE_FEED_CATS', 1, 'false', 'Enable feed categories',2);
 280
 281 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('SHOW_CONTENT_PREVIEW', 1, 'true', 'Show content preview in headlines list',2);
 282
 283 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('SHORT_DATE_FORMAT', 2, 'M d, G:i', 'Short date format',3);
 284
 285 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('LONG_DATE_FORMAT', 2, 'D, M d Y - G:i', 'Long date format',3);
 286
 287 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('COMBINED_DISPLAY_MODE', 1, 'false', 'Combined feed display',2, 'Display expanded list of feed articles, instead of separate displays for headlines and article content');
 288
 289 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('HIDE_READ_FEEDS', 1, 'false', 'Hide feeds with no unread messages',2);
 290
 291 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('ON_CATCHUP_SHOW_NEXT_FEED', 1, 'false', 'On catchup show next feed',2, 'When "Mark as read" button is clicked in toolbar, automatically open next feed with unread articles.');
 292
 293 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('FEEDS_SORT_BY_UNREAD', 1, 'false', 'Sort feeds by unread articles count',2);
 294
 295 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('REVERSE_HEADLINES', 1, 'false', 'Reverse headline order (oldest first)',2);
 296
 297 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('DIGEST_ENABLE', 1, 'false', 'Enable e-mail digest',1, 'This option enables sending daily digest of new (and unread) headlines on your configured e-mail address');
 298
 299 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('CONFIRM_FEED_CATCHUP', 1, 'true', 'Confirm marking feed as read',3);
 300
 301 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('CDM_AUTO_CATCHUP', 1, 'false', 'Automatically mark articles as read',3, 'This option enables marking articles as read automatically while you scroll article list.');
 302
 303 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_DEFAULT_VIEW_MODE', 2, 'adaptive', '', 1);
 304
 305 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_DEFAULT_VIEW_LIMIT', 3, '30', '', 1);
 306
 307 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_PREFS_ACTIVE_TAB', 2, '', '', 1);
 308
 309 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('STRIP_UNSAFE_TAGS', 1, 'true', 'Strip unsafe tags from articles', 3, 'Strip all but most common HTML tags when reading articles.');
 310
 311 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('BLACKLISTED_TAGS', 2, 'main, generic, misc, uncategorized, blog, blogroll, general, news', 'Blacklisted tags', 3, 'When auto-detecting tags in articles these tags will not be applied (comma-separated list).');
 312
 313 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('FRESH_ARTICLE_MAX_AGE', 3, '24', 'Maximum age of fresh articles (in hours)',2);
 314
 315 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('DIGEST_CATCHUP', 1, 'false', 'Mark articles in e-mail digest as read',1);
 316
 317 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('CDM_EXPANDED', 1, 'true', 'Automatically expand articles in combined mode',3);
 318
 319 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('PURGE_UNREAD_ARTICLES', 1, 'true', 'Purge unread articles',3);
 320
 321 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('HIDE_READ_SHOWS_SPECIAL', 1, 'true', 'Show special feeds when hiding read feeds',3);
 322
 323 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('VFEED_GROUP_BY_FEED', 1, 'false', 'Group headlines in virtual feeds',2, 'When this option is enabled, headlines in Special feeds and Labels are grouped by feeds');
 324
 325 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('STRIP_IMAGES', 1, 'false', 'Do not show images in articles', 2);
 326
 327 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_DEFAULT_VIEW_ORDER_BY', 2, 'default', '', 1);
 328
 329 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ENABLE_API_ACCESS', 1, 'false', 'Enable external API', 3);
 330
 331 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_COLLAPSED_SPECIAL', 1, 'false', '', 1);
 332
 333 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_COLLAPSED_LABELS', 1, 'false', '', 1);
 334
 335 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_COLLAPSED_UNCAT', 1, 'false', '', 1);
 336
 337 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_COLLAPSED_FEEDLIST', 1, 'false', '', 1);
 338
 339 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_ENABLE_CATS', 1, 'false', '', 1);
 340
 341 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_SHOW_IMAGES', 1, 'false', '', 1);
 342
 343 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_HIDE_READ', 1, 'false', '', 1);
 344
 345 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_SORT_FEEDS_UNREAD', 1, 'false', '', 1);
 346
 347 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_THEME_ID', 2, '0', '', 1);
 348
 349 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('USER_TIMEZONE', 2, 'UTC', 'User timezone', 1);
 350
 351 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('SORT_HEADLINES_BY_FEED_DATE', 1, 'true', 'Sort headlines by feed date',3, 'Use feed-specified date to sort headlines instead of local import date.');
 352
 353 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('USER_STYLESHEET', 2, '', 'Customize stylesheet', 2, 'Customize CSS stylesheet to your liking');
 354
 355 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('_MOBILE_BROWSE_CATS', 1, 'true', '', 1);
 356
 357 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('SSL_CERT_SERIAL', 2, '', 'Login with an SSL certificate',3, 'Click to register your SSL client certificate with tt-rss');
 358
 359 create table ttrss_user_prefs (
 360         owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE,
 361         pref_name varchar(250) not null references ttrss_prefs(pref_name) ON DELETE CASCADE,
 362         profile integer references ttrss_settings_profiles(id) ON DELETE CASCADE,
 363         value text not null);
 364
 365 create index ttrss_user_prefs_owner_uid_index on ttrss_user_prefs(owner_uid);
 366 -- create index ttrss_user_prefs_value_index on ttrss_user_prefs(value);
 367
 368 create table ttrss_scheduled_updates (id serial not null primary key,
 369         owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE,
 370         feed_id integer default null references ttrss_feeds(id) ON DELETE CASCADE,
 371         entered timestamp not null default NOW());
 372
 373 create table ttrss_sessions (id varchar(250) unique not null primary key,
 374         data text,
 375         expire integer not null);
 376
 377 create index ttrss_sessions_expire_index on ttrss_sessions(expire);
 378
 379 create function SUBSTRING_FOR_DATE(timestamp, int, int) RETURNS text AS 'SELECT SUBSTRING(CAST($1 AS text), $2, $3)' LANGUAGE 'sql';
 380
 381 create table ttrss_feedbrowser_cache (
 382         feed_url text not null primary key,
 383         title text not null,
 384         subscribers integer not null);
 385
 386 create table ttrss_labels2 (id serial not null primary key,
 387         owner_uid integer not null references ttrss_users(id) ON DELETE CASCADE,
 388         fg_color varchar(15) not null default '',
 389         bg_color varchar(15) not null default '',
 390         caption varchar(250) not null
 391 );
 392
 393 create table ttrss_user_labels2 (
 394         label_id integer not null references ttrss_labels2(id) ON DELETE CASCADE,
 395         article_id integer not null references ttrss_entries(id) ON DELETE CASCADE
 396 );
 397
 398 create table ttrss_access_keys (id serial not null primary key,
 399         access_key varchar(250) not null,
 400         feed_id varchar(250) not null,
 401         is_cat boolean not null default false,
 402         owner_uid integer not null references ttrss_users(id) on delete cascade);
 403
 404 commit;