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
|
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
|
-
+
-
-
-
+
+
-
+
|
create table rsstg_updates (owner integer, update jsonb);
create unique index rsstg_updates__id on rsstg_updates(update->>'update_id');
-- create table rsstg_users (id integer);
create table rsstg_channel (
channel_id bigint primary key,
username text);
username text not null);
create unique index rsstg_channel__username on rsstg_channel(username);
create table rsstg_source (
source_id serial,
channel_id integer not null,
url text not null,
last_fetch timestamptz,
last_scrape timestamptz default now(),
enabled boolean default false,
hour smallint generated always as (extract('hour' from posted)) stored,
hour smallint not null generated always as (extract('hour' from posted)) stored,
FOREIGN KEY (source_id) REFERENCES rsstg_source(source_id)
);
create unique index rsstg_post__url on rsstg_post(url);
create index rsstg_post__hour on rsstg_post(hour);
create index rsstg_post__posted_hour on rsstg_post(posted,hour);
create or replace view rsstg_order as
|