schema/ttrss_schema_pgsql.sql

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