Lines of
rsstg.sql
from check-in acb0a4ac54
that are changed by the sequence of edits moving toward
check-in dc2089ff6a:
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,
acb0a4ac54 2025-09-28 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
34: select
35: source_id,
36: coalesce(last_scrape + make_interval(0,0,0,0,0,(60 / (coalesce(activity, 1)/7 + 1) )::integer), now() - interval '1 minute') as next_fetch,
37: owner,
38: last_scrape
39: from rsstg_source natural left join
40: (select source_id, count(*) as activity
41: from rsstg_post where
42: hour = extract('hour' from now())::smallint
43: and posted > now() - interval '7 days'
44: and posted < now() - interval '1 hour'
45: group by source_id) as act
46: where enabled
47: order by next_fetch;
48:
49: create or replace function hxm(timestamptz) returns smallint
50: as $$ select(extract('hour' from $1) * extract('minute' from $1)); $$
51: language sql immutable returns null on null input;
52:
53: create or replace view rsstg_order as
54: select
55: source_id,
56: coalesce(last_scrape + make_interval(0,0,0,0,0,(60 / (coalesce(activity, 1)/7 + 1) )::integer), now() - interval '1 minute') as next_fetch,
57: owner,
58: last_scrape
59: from rsstg_source natural left join
60: (select source_id, count(*) as activity
61: from rsstg_post where
62: (
63: (hxm > hxm(now()) - 30 and hxm < hxm(now()) + 30)
64: or (hxm < 30 and hxm < hxm(now()) + 30 - 1440)
65: or (hxm > 1410 and hxm > 1440 + hxm(now()) - 30)
66: )
67: and posted < now() - interval '1 hour'
68: and posted > now() - interval '7 days'
69: group by source_id) as act
70: where enabled
71: order by next_fetch;