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