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
|
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);
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,
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
|
|
<
|
|
|
|
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 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_scrape not null timestamptz default now(),
enabled boolean not null default false,
iv_hash text,
owner bigint not null);
create unique index rsstg_source__source_id on rsstg_source(source_id);
create unique index rsstg_source__channel_id__owner on rsstg_source(channel_id, owner);
create index rsstg_source__owner on rsstg_source(owner);
create table rsstg_post (
source_id integer not null,
date int not null,
url text not null,
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
|