schema/ttrss_schema_mysql.sql

   1 drop table if exists ttrss_tags;
   2
   3 drop table if exists ttrss_user_entries;
   4 drop table if exists ttrss_entry_comments;
   5 drop table if exists ttrss_entries;
   6
   7 drop table if exists ttrss_feeds;
   8 drop table if exists ttrss_labels;
   9 drop table if exists ttrss_filters;
  10 drop table if exists ttrss_feed_categories;
  11
  12 drop table if exists ttrss_user_prefs;
  13 drop table if exists ttrss_users;
  14
  15 create table ttrss_users (id integer primary key not null auto_increment,
  16         login varchar(120) not null unique,
  17         pwd_hash varchar(250) not null,
  18         last_login datetime default null,
  19         access_level integer not null default 0) TYPE=InnoDB;
  20
  21 insert into ttrss_users (login,pwd_hash,access_level) values ('admin', 'password', 10);
  22
  23 create table ttrss_feed_categories(id integer not null primary key,
  24         owner_uid integer not null,
  25         title varchar(200) not null,
  26         index(owner_uid),
  27         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE);
  28
  29 create table ttrss_feeds (id integer not null auto_increment primary key,
  30         owner_uid integer not null,
  31         title varchar(200) not null,
  32         cat_id integer default null,
  33         feed_url varchar(250) not null,
  34         icon_url varchar(250) not null default '',
  35         update_interval integer not null default 0,
  36         purge_interval integer not null default 0,
  37         last_updated datetime default '',
  38         last_error text not null default '',
  39         site_url varchar(250) not null default '',
  40         index(owner_uid),
  41         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE,
  42         index(cat_id),
  43         foreign key (cat_id) references ttrss_feed_categories(id)) TYPE=InnoDB;
  44
  45 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'Footnotes', 'http://gnomedesktop.org/node/feed');
  46 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'Latest Linux Kernel Versions','http://kernel.org/kdist/rss.xml');
  47 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'RPGDot Newsfeed',
  48    'http://www.rpgdot.com/team/rss/rss0.xml');
  49 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'Digg.com News',
  50    'http://digg.com/rss/index.xml');
  51 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'Technocrat.net',
  52    'http://syndication.technocrat.net/rss');
  53
  54 create table ttrss_entries (id integer not null primary key auto_increment,
  55         title text not null,
  56         guid varchar(255) not null unique,
  57         link text not null,
  58         updated datetime not null,
  59         content text not null,
  60         content_hash varchar(250) not null,
  61         no_orig_date bool not null default 0,
  62         date_entered datetime not null,
  63         comments varchar(250) not null default '') TYPE=InnoDB;
  64
  65 create table ttrss_user_entries (
  66         int_id integer not null primary key auto_increment,
  67         ref_id integer not null,
  68         feed_id int not null,
  69         owner_uid integer not null,
  70         marked bool not null default 0,
  71         last_read datetime,
  72         unread bool not null default 1,
  73         index (ref_id),
  74         foreign key (ref_id) references ttrss_entries(id) ON DELETE CASCADE,
  75         index (feed_id),
  76         foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE,
  77         index (owner_uid),
  78         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) TYPE=InnoDB;
  79
  80 create table ttrss_entry_comments (id integer not null primary key,
  81         ref_id integer not null,
  82         owner_uid integer not null,
  83         private bool not null default 0,
  84         date_entered datetime not null,
  85         index (ref_id),
  86         foreign key (ref_id) references ttrss_entries(id) ON DELETE CASCADE,
  87         index (owner_uid),
  88         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) TYPE=InnoDB;
  89
  90 drop table if exists ttrss_filters;
  91 drop table if exists ttrss_filter_types;
  92
  93 create table ttrss_filter_types (id integer primary key,
  94         name varchar(120) unique not null,
  95         description varchar(250) not null unique) TYPE=InnoDB;
  96
  97 insert into ttrss_filter_types (id,name,description) values (1, 'title', 'Title');
  98 insert into ttrss_filter_types (id,name,description) values (2, 'content', 'Content');
  99 insert into ttrss_filter_types (id,name,description) values (3, 'both',
 100         'Title or Content');
 101 insert into ttrss_filter_types (id,name,description) values (4, 'link',
 102         'Link');
 103
 104 create table ttrss_filters (id integer not null primary key auto_increment,
 105         owner_uid integer not null,
 106         feed_id integer default null,
 107         filter_type integer not null,
 108         reg_exp varchar(250) not null,
 109         description varchar(250) not null default '',
 110         index (filter_type),
 111         foreign key (filter_type) references ttrss_filter_types(id) ON DELETE CASCADE,
 112         index (owner_uid),
 113         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE,
 114         index (feed_id),
 115         foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE) TYPE=InnoDB;
 116
 117 drop table if exists ttrss_labels;
 118
 119 create table ttrss_labels (id integer not null primary key auto_increment,
 120         owner_uid integer not null,
 121         sql_exp varchar(250) not null,
 122         description varchar(250) not null,
 123         index (owner_uid),
 124         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) TYPE=InnoDB;
 125
 126 insert into ttrss_labels (owner_uid,sql_exp,description) values (1,'unread = true',
 127         'Unread articles');
 128
 129 insert into ttrss_labels (owner_uid,sql_exp,description) values (1,
 130         'last_read is null and unread = false', 'Updated articles');
 131
 132 create table ttrss_tags (id integer primary key auto_increment,
 133         owner_uid integer not null,
 134         tag_name varchar(250) not null,
 135         post_int_id integer not null,
 136         index (post_int_id),
 137         foreign key (post_int_id) references ttrss_user_entries(int_id) ON DELETE CASCADE,
 138         index (owner_uid),
 139         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) TYPE=InnoDB;
 140
 141 drop table if exists ttrss_version;
 142
 143 create table ttrss_version (schema_version int not null) TYPE=InnoDB;
 144
 145 insert into ttrss_version values (2);
 146
 147 drop table if exists ttrss_prefs;
 148 drop table if exists ttrss_prefs_types;
 149 drop table if exists ttrss_prefs_sections;
 150
 151 create table ttrss_prefs_types (id integer not null primary key,
 152         type_name varchar(100) not null) TYPE=InnoDB;
 153
 154 insert into ttrss_prefs_types (id, type_name) values (1, 'bool');
 155 insert into ttrss_prefs_types (id, type_name) values (2, 'string');
 156 insert into ttrss_prefs_types (id, type_name) values (3, 'integer');
 157
 158 create table ttrss_prefs_sections (id integer not null primary key,
 159         section_name varchar(100) not null) TYPE=InnoDB;
 160
 161 insert into ttrss_prefs_sections (id, section_name) values (1, 'General');
 162 insert into ttrss_prefs_sections (id, section_name) values (2, 'Interface');
 163 insert into ttrss_prefs_sections (id, section_name) values (3, 'Advanced');
 164
 165 create table ttrss_prefs (pref_name varchar(250) not null primary key,
 166         type_id integer not null,
 167         section_id integer not null default 1,
 168         short_desc text not null,
 169         help_text text not null default '',
 170         def_value text not null,
 171         index(type_id),
 172         foreign key (type_id) references ttrss_prefs_types(id),
 173         index(section_id),
 174         foreign key (section_id) references ttrss_prefs_sections(id)) TYPE=InnoDB;
 175
 176 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ENABLE_FEED_ICONS', 1, 'true', 'Enable icons in feedlist',2);
 177 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);
 178 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('UPDATE_POST_ON_CHECKSUM_CHANGE', 1, 'true', 'Update post on checksum change',1);
 179 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ENABLE_PREFS_CATCHUP_UNCATCHUP', 1, 'false', 'Enable catchup/uncatchup buttons in feed editor',2);
 180 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('ENABLE_LABELS', 1, 'false', 'Enable labels',3,
 181    'Experimental support for virtual feeds based on user crafted SQL queries. This feature is highly experimental and at this point not user friendly. Use with caution.');
 182
 183 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 (in minutes)',1);
 184 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('DISPLAY_HEADER', 1, 'true', 'Display header',2);
 185 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('DISPLAY_FOOTER', 1, 'true', 'Display footer',2);
 186 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('USE_COMPACT_STYLESHEET', 1, 'false', 'Use compact stylesheet by default',2);
 187 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('DEFAULT_ARTICLE_LIMIT', 3, '0', 'Default article limit',2,
 188    'Default limit for articles to display, any custom number you like (0 - disables).');
 189
 190 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('DAEMON_REFRESH_ONLY', 1, 'false', 'Daemon refresh only', 3,
 191    'Updates to all feeds will only run when the backend script is invoked with a "daemon" option on the URI stem.');
 192
 193 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('DISPLAY_FEEDLIST_ACTIONS', 1, 'false', 'Display feedlist actions',2,
 194    'Display separate dropbox for feedlist actions, if disabled these actions are available in global actions menu.');
 195
 196 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ENABLE_SPLASH', 1, 'false', 'Enable loading splashscreen',2);
 197
 198 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,
 199         'This option is useful when you are reading several planet-type aggregators with partially colliding userbase.
 200         When disabled, it forces same posts from different feeds to appear only once.');
 201
 202 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('USER_STYLESHEET_URL', 2, '', 'User stylesheet URL',2,
 203         'Link to user stylesheet to override default style, disabled if empty.');
 204
 205 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ENABLE_FEED_CATS', 1, 'false', 'Enable feed categories',2);
 206
 207
 208 create table ttrss_user_prefs (
 209    owner_uid integer not null,
 210    pref_name varchar(250),
 211    value text not null,
 212         index (owner_uid),
 213         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE,
 214         index (pref_name),
 215         foreign key (pref_name) references ttrss_prefs(pref_name) ON DELETE CASCADE) TYPE=InnoDB;
 216
 217