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
   5 create table ttrss_feeds (id integer not null auto_increment primary key,
   6         title varchar(200) not null unique,
   7         feed_url varchar(250) unique not null,
   8         icon_url varchar(250) not null default '',
   9         update_interval integer not null default 0,
  10         last_updated datetime default '') TYPE=InnoDB;
  11
  12 insert into ttrss_feeds (title,feed_url) values ('Footnotes', 'http://gnomedesktop.org/node/feed');
  13 insert into ttrss_feeds (title,feed_url) values ('Freedesktop.org', 'http://planet.freedesktop.org/rss20.xml');
  14 insert into ttrss_feeds (title,feed_url) values ('Planet Debian', 'http://planet.debian.org/rss20.xml');
  15 insert into ttrss_feeds (title,feed_url) values ('Planet GNOME', 'http://planet.gnome.org/rss20.xml');
  16 insert into ttrss_feeds (title,feed_url) values ('Planet Ubuntu', 'http://planet.ubuntulinux.org/rss20.xml');
  17
  18 insert into ttrss_feeds (title,feed_url) values ('Monologue', 'http://www.go-mono.com/monologue/index.rss');
  19
  20 insert into ttrss_feeds (title,feed_url) values ('Latest Linux Kernel Versions',
  21         'http://kernel.org/kdist/rss.xml');
  22
  23 insert into ttrss_feeds (title,feed_url) values ('RPGDot Newsfeed',
  24         'http://www.rpgdot.com/team/rss/rss0.xml');
  25
  26 insert into ttrss_feeds (title,feed_url) values ('Digg.com News',
  27         'http://digg.com/rss/index.xml');
  28
  29 insert into ttrss_feeds (title,feed_url) values ('Technocrat.net',
  30         'http://syndication.technocrat.net/rss');
  31
  32 create table ttrss_entries (id integer not null primary key auto_increment,
  33         feed_id integer not null,
  34         updated datetime not null,
  35         title text not null,
  36         guid varchar(255) not null unique,
  37         link text not null,
  38         content text not null,
  39         content_hash varchar(250) not null,
  40         last_read datetime,
  41         marked bool not null default 0,
  42         date_entered datetime not null,
  43         no_orig_date bool not null default 0,
  44         comments varchar(250) not null default '',
  45         unread bool not null default 1,
  46         index (feed_id),
  47         foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE) TYPE=InnoDB;
  48
  49 drop table if exists ttrss_filters;
  50 drop table if exists ttrss_filter_types;
  51
  52 create table ttrss_filter_types (id integer primary key,
  53         name varchar(120) unique not null,
  54         description varchar(250) not null unique) TYPE=InnoDB;
  55
  56
  57 insert into ttrss_filter_types (id,name,description) values (1, 'title', 'Title');
  58 insert into ttrss_filter_types (id,name,description) values (2, 'content', 'Content');
  59 insert into ttrss_filter_types (id,name,description) values (3, 'both',
  60         'Title or Content');
  61
  62 create table ttrss_filters (id integer primary key auto_increment,
  63         filter_type integer not null references ttrss_filter_types(id),
  64         reg_exp varchar(250) not null,
  65         description varchar(250) not null default '') TYPE=InnoDB;
  66
  67 drop table if exists ttrss_labels;
  68
  69 create table ttrss_labels (id integer primary key auto_increment,
  70         sql_exp varchar(250) not null,
  71         description varchar(250) not null) TYPE=InnoDB;
  72
  73 insert into ttrss_labels (sql_exp,description) values ('unread = true',
  74         'Unread articles');
  75
  76 insert into ttrss_labels (sql_exp,description) values (
  77         'last_read is null and unread = false', 'Updated articles');
  78
  79 create table ttrss_tags (id integer primary key auto_increment,
  80         tag_name varchar(250) not null,
  81         post_id integer not null,
  82         index (post_id),
  83         foreign key (post_id) references ttrss_entries(id) ON DELETE CASCADE) TYPE=InnoDB;
  84