forked from astro/harvester
/
db.sql
42 lines (37 loc) · 1.01 KB
/
db.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
create table sources (
collection varchar(255) not null,
rss varchar(255) not null,
last varchar(40),
title varchar(255),
link varchar(255),
description text,
unique (collection, rss)
);
create table items (
rss varchar(255) not null,
title varchar(255),
link varchar(255),
date timestamp,
description text,
unique (rss, link)
);
create table enclosures (
rss varchar(255) not null,
link varchar(255) not null,
href varchar(255) not null,
mime varchar(255),
title varchar(255),
length int,
unique (rss, link, href)
);
create view last48hrs as select items.rss, items.title, items.link, sources.title as blogtitle, sources.collection from items, sources where items.rss = sources.rss and now() - interval '48 hour' < items.date order by date;
create table jabbersubscriptions (
jid varchar(255) not null,
collection varchar(255) not null,
unique (jid, collection)
);
create table jabbersettings (
jid varchar(255) primary key,
respect_status boolean,
message_type varchar(16)
);