Lines of
rsstg.sql
from check-in 4e0a19d772
that are changed by the sequence of edits moving toward
check-in 659724c658:
1: create table rsstg_updates (owner integer, update jsonb);
2:
3: create unique index rsstg_updates__id on rsstg_updates(update->>'update_id');
4:
5: create table rsstg_source (
6: source_id serial,
7: channel text not null,
8: channel_id integer not null,
9: url text not null,
10: last_scrape not null timestamptz default now(),
4e0a19d772 2021-10-01 11: enabled boolean not null default false,
12: iv_hash text,
13: owner bigint not null);
14: create unique index rsstg_source__source_id on rsstg_source(source_id);
15: create unique index rsstg_source__channel_id__owner on rsstg_source(channel_id, owner);
16: create index rsstg_source__owner on rsstg_source(owner);
17:
18: create table rsstg_post (
19: source_id integer not null,
20: posted timestamptz not null,
21: url text not null,
22: hour smallint not null generated always as (extract('hour' from posted at time zone 'utc')) stored,
23: hxm smallint not null generated always as (hxm(posted)) stored,
24: FOREIGN KEY (source_id) REFERENCES rsstg_source(source_id) on delete cascade,
25: );
26: create unique index rsstg_post__url on rsstg_post(url);
27: create index rsstg_post__hour on rsstg_post(hour);
28: create index rsstg_post__posted_hour on rsstg_post(posted,hour);
29: create index rsstg_post__hxm on rsstg_post(hxm);
30: create index rsstg_post__posted_hxm on rsstg_post(posted,hxm);
31:
32: create or replace view rsstg_order_old as
33: select source_id, coalesce(last_scrape + make_interval(0,0,0,0,0,(60 / (coalesce(activity, 1)/7 + 1) )::integer), now() - interval '1 minute') as next_fetch, owner
34: from rsstg_source natural left join
35: (select source_id, count(*) as activity
36: from rsstg_post where
37: hour = extract('hour' from now())::smallint
38: and posted > now() - interval '7 days'
39: and posted < now() - interval '1 hour'
40: group by source_id) as act
41: where enabled
42: order by next_fetch;
43:
44: create or replace function hxm(timestamptz) returns smallint
45: as $$ select(extract('hour' from $1) * extract('minute' from $1)); $$
46: language sql immutable returns null on null input;
47:
48: create or replace view rsstg_order as
49: select source_id, coalesce(last_scrape + make_interval(0,0,0,0,0,(60 / (coalesce(activity, 1)/7 + 1) )::integer), now() - interval '1 minute') as next_fetch, owner
50: from rsstg_source natural left join
51: (select source_id, count(*) as activity
52: from rsstg_post where
53: (
54: (hxm > hxm(now()) - 30 and hxm < hxm(now()) + 30)
55: or (hxm < 30 and hxm < hxm(now()) + 30 - 1440)
56: or (hxm > 1410 and hxm > 1440 + hxm(now()) - 30)
57: )
58: and posted < now() - interval '1 hour'
59: and posted > now() - interval '7 days'
60: group by source_id) as act
61: where enabled
62: order by next_fetch;