schema/ttrss_schema_mysql.sql

   1 drop table if exists ttrss_tags;
   2 drop table if exists ttrss_entries;
   3 drop table if exists ttrss_feeds;
   4 drop table if exists ttrss_labels;
   5 drop table if exists ttrss_filters;
   6
   7 drop table if exists ttrss_user_prefs;
   8 drop table if exists ttrss_users;
   9
  10 create table ttrss_users (id integer primary key not null auto_increment,
  11         login varchar(120) not null unique,
  12         pwd_hash varchar(250) not null,
  13         access_level integer not null default 0) TYPE=InnoDB;
  14
  15 insert into ttrss_users (login,pwd_hash,access_level) values ('admin', 'password', 10);
  16 insert into ttrss_users (login,pwd_hash,access_level) values ('user-1', 'password1', 0);
  17 insert into ttrss_users (login,pwd_hash,access_level) values ('user-2', 'password2', 0);
  18
  19 create table ttrss_feeds (id integer not null auto_increment primary key,
  20         owner_uid integer not null,
  21         title varchar(200) not null,
  22         feed_url varchar(250) not null,
  23         icon_url varchar(250) not null default '',
  24         update_interval integer not null default 0,
  25         purge_interval integer not null default 0,
  26         last_updated datetime default '',
  27         last_error text not null default '',
  28         site_url varchar(250) not null default '',
  29         index(owner_uid),
  30         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) TYPE=InnoDB;
  31
  32 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'Footnotes', 'http://gnomedesktop.org/node/feed');
  33 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'Freedesktop.org', 'http://planet.freedesktop.org/rss20.xml');
  34 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'Planet Debian', 'http://planet.debian.org/rss20.xml');
  35 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'Planet GNOME', 'http://planet.gnome.org/rss20.xml');
  36 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'Planet Ubuntu', 'http://planet.ubuntulinux.org/rss20.xml');
  37
  38 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'Monologue', 'http://www.go-mono.com/monologue/index.rss');
  39
  40 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'Latest Linux Kernel Versions',
  41    'http://kernel.org/kdist/rss.xml');
  42
  43 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'RPGDot Newsfeed',
  44    'http://www.rpgdot.com/team/rss/rss0.xml');
  45
  46 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'Digg.com News',
  47    'http://digg.com/rss/index.xml');
  48
  49 insert into ttrss_feeds (owner_uid,title,feed_url) values (1,'Technocrat.net',
  50    'http://syndication.technocrat.net/rss');
  51
  52
  53 create table ttrss_entries (id integer not null primary key auto_increment,
  54         owner_uid integer not null,
  55         feed_id integer not null,
  56         updated datetime not null,
  57         title text not null,
  58         guid varchar(255) not null unique,
  59         link text not null,
  60         content text not null,
  61         content_hash varchar(250) not null,
  62         last_read datetime,
  63         marked bool not null default 0,
  64         date_entered datetime not null,
  65         no_orig_date bool not null default 0,
  66         comments varchar(250) not null default '',
  67         unread bool not null default 1,
  68         index (feed_id),
  69         foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE,
  70         index (owner_uid),
  71         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) TYPE=InnoDB;
  72
  73 drop table if exists ttrss_filters;
  74 drop table if exists ttrss_filter_types;
  75
  76 create table ttrss_filter_types (id integer primary key,
  77         name varchar(120) unique not null,
  78         description varchar(250) not null unique) TYPE=InnoDB;
  79
  80
  81 insert into ttrss_filter_types (id,name,description) values (1, 'title', 'Title');
  82 insert into ttrss_filter_types (id,name,description) values (2, 'content', 'Content');
  83 insert into ttrss_filter_types (id,name,description) values (3, 'both',
  84         'Title or Content');
  85
  86 create table ttrss_filters (id integer not null primary key auto_increment,
  87         owner_uid integer not null,
  88         filter_type integer not null,
  89         reg_exp varchar(250) not null,
  90         description varchar(250) not null default '',
  91         index (filter_type),
  92         foreign key (filter_type) references ttrss_filter_types(id) ON DELETE CASCADE,
  93         index (owner_uid),
  94         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) TYPE=InnoDB;
  95
  96 drop table if exists ttrss_labels;
  97
  98 create table ttrss_labels (id integer not null primary key auto_increment,
  99         owner_uid integer not null,
 100         sql_exp varchar(250) not null,
 101         description varchar(250) not null,
 102         index (owner_uid),
 103         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) TYPE=InnoDB;
 104
 105 insert into ttrss_labels (owner_uid,sql_exp,description) values (1,'unread = true',
 106         'Unread articles');
 107
 108 insert into ttrss_labels (owner_uid,sql_exp,description) values (1,
 109         'last_read is null and unread = false', 'Updated articles');
 110
 111 create table ttrss_tags (id integer primary key auto_increment,
 112         owner_uid integer not null,
 113         tag_name varchar(250) not null,
 114         post_id integer not null,
 115         index (post_id),
 116         foreign key (post_id) references ttrss_entries(id) ON DELETE CASCADE,
 117         index (owner_uid),
 118         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) TYPE=InnoDB;
 119
 120 drop table if exists ttrss_version;
 121
 122 create table ttrss_version (schema_version int not null) TYPE=InnoDB;
 123
 124 insert into ttrss_version values (2);
 125
 126 drop table if exists ttrss_prefs;
 127 drop table if exists ttrss_prefs_types;
 128 drop table if exists ttrss_prefs_sections;
 129
 130 create table ttrss_prefs_types (id integer not null primary key,
 131         type_name varchar(100) not null) TYPE=InnoDB;
 132
 133 insert into ttrss_prefs_types (id, type_name) values (1, 'bool');
 134 insert into ttrss_prefs_types (id, type_name) values (2, 'string');
 135 insert into ttrss_prefs_types (id, type_name) values (3, 'integer');
 136
 137 create table ttrss_prefs_sections (id integer not null primary key,
 138         section_name varchar(100) not null) TYPE=InnoDB;
 139
 140 insert into ttrss_prefs_sections (id, section_name) values (1, 'General');
 141 insert into ttrss_prefs_sections (id, section_name) values (2, 'Interface');
 142 insert into ttrss_prefs_sections (id, section_name) values (3, 'Advanced');
 143
 144 create table ttrss_prefs (pref_name varchar(250) not null primary key,
 145         type_id integer not null,
 146         section_id integer not null default 1,
 147         short_desc text not null,
 148         help_text text not null default '',
 149         def_value text not null,
 150         index(type_id),
 151         foreign key (type_id) references ttrss_prefs_types(id),
 152         index(section_id),
 153         foreign key (section_id) references ttrss_prefs_sections(id)) TYPE=InnoDB;
 154
 155 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);
 156 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ICONS_DIR', 2, 'icons', 'Local directory for feed icons',1);
 157 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ICONS_URL', 2, 'icons', 'Local URL for icons',1);
 158 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);
 159 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);
 160 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);
 161 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id,help_text) values('ENABLE_LABELS', 1, 'false', 'Enable labels',3,
 162    '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.');
 163
 164 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);
 165 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('DISPLAY_HEADER', 1, 'true', 'Display header',2);
 166 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('DISPLAY_FOOTER', 1, 'true', 'Display footer',2);
 167 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);
 168 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,
 169    'Default limit for articles to display, any custom number you like (0 - disables).');
 170
 171 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,
 172    'Updates to all feeds will only run when the backend script is invoked with a "daemon" option on the URI stem.');
 173
 174 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,
 175    'Display separate dropbox for feedlist actions, if disabled these actions are available in global actions menu.');
 176
 177 insert into ttrss_prefs (pref_name,type_id,def_value,short_desc,section_id) values('ENABLE_SPLASH', 1, 'false', 'Enable loading splashscreen',2);
 178
 179 create table ttrss_user_prefs (
 180    owner_uid integer not null,
 181    pref_name varchar(250),
 182    value text not null,
 183         index (owner_uid),
 184         foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE,
 185         index (pref_name),
 186         foreign key (pref_name) references ttrss_prefs(pref_name) ON DELETE CASCADE) TYPE=InnoDB;
 187
 188