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