Artifact
b4b5fc5ff82fb9b0368c1e7ccd817cc752e3facdde9e37fe6dbe3a76ada1ea6a:
0000: 63 72 65 61 74 65 20 74 61 62 6c 65 20 72 73 73 create table rss
0010: 74 67 5f 75 70 64 61 74 65 73 20 28 6f 77 6e 65 tg_updates (owne
0020: 72 20 69 6e 74 65 67 65 72 2c 20 75 70 64 61 74 r integer, updat
0030: 65 20 6a 73 6f 6e 62 29 3b 0a 0a 63 72 65 61 74 e jsonb);..creat
0040: 65 20 75 6e 69 71 75 65 20 69 6e 64 65 78 20 72 e unique index r
0050: 73 73 74 67 5f 75 70 64 61 74 65 73 5f 5f 69 64 sstg_updates__id
0060: 20 6f 6e 20 72 73 73 74 67 5f 75 70 64 61 74 65 on rsstg_update
0070: 73 28 75 70 64 61 74 65 2d 3e 3e 27 75 70 64 61 s(update->>'upda
0080: 74 65 5f 69 64 27 29 3b 0a 0a 63 72 65 61 74 65 te_id');..create
0090: 20 74 61 62 6c 65 20 72 73 73 74 67 5f 73 6f 75 table rsstg_sou
00a0: 72 63 65 20 28 0a 09 73 6f 75 72 63 65 5f 69 64 rce (..source_id
00b0: 20 73 65 72 69 61 6c 2c 0a 09 63 68 61 6e 6e 65 serial,..channe
00c0: 6c 20 74 65 78 74 20 6e 6f 74 20 6e 75 6c 6c 2c l text not null,
00d0: 0a 09 63 68 61 6e 6e 65 6c 5f 69 64 20 69 6e 74 ..channel_id int
00e0: 65 67 65 72 20 6e 6f 74 20 6e 75 6c 6c 2c 0a 09 eger not null,..
00f0: 75 72 6c 20 74 65 78 74 20 6e 6f 74 20 6e 75 6c url text not nul
0100: 6c 2c 0a 09 6c 61 73 74 5f 73 63 72 61 70 65 20 l,..last_scrape
0110: 6e 6f 74 20 6e 75 6c 6c 20 74 69 6d 65 73 74 61 not null timesta
0120: 6d 70 74 7a 20 64 65 66 61 75 6c 74 20 6e 6f 77 mptz default now
0130: 28 29 2c 0a 09 65 6e 61 62 6c 65 64 20 62 6f 6f (),..enabled boo
0140: 6c 65 61 6e 20 6e 6f 74 20 6e 75 6c 6c 20 64 65 lean not null de
0150: 66 61 75 6c 74 20 74 72 75 65 2c 0a 09 69 76 5f fault true,..iv_
0160: 68 61 73 68 20 74 65 78 74 2c 0a 09 6f 77 6e 65 hash text,..owne
0170: 72 20 62 69 67 69 6e 74 20 6e 6f 74 20 6e 75 6c r bigint not nul
0180: 6c 29 3b 0a 63 72 65 61 74 65 20 75 6e 69 71 75 l);.create uniqu
0190: 65 20 69 6e 64 65 78 20 72 73 73 74 67 5f 73 6f e index rsstg_so
01a0: 75 72 63 65 5f 5f 73 6f 75 72 63 65 5f 69 64 20 urce__source_id
01b0: 6f 6e 20 72 73 73 74 67 5f 73 6f 75 72 63 65 28 on rsstg_source(
01c0: 73 6f 75 72 63 65 5f 69 64 29 3b 0a 63 72 65 61 source_id);.crea
01d0: 74 65 20 75 6e 69 71 75 65 20 69 6e 64 65 78 20 te unique index
01e0: 72 73 73 74 67 5f 73 6f 75 72 63 65 5f 5f 63 68 rsstg_source__ch
01f0: 61 6e 6e 65 6c 5f 69 64 5f 5f 6f 77 6e 65 72 20 annel_id__owner
0200: 6f 6e 20 72 73 73 74 67 5f 73 6f 75 72 63 65 28 on rsstg_source(
0210: 63 68 61 6e 6e 65 6c 5f 69 64 2c 20 6f 77 6e 65 channel_id, owne
0220: 72 29 3b 0a 63 72 65 61 74 65 20 69 6e 64 65 78 r);.create index
0230: 20 72 73 73 74 67 5f 73 6f 75 72 63 65 5f 5f 6f rsstg_source__o
0240: 77 6e 65 72 20 6f 6e 20 72 73 73 74 67 5f 73 6f wner on rsstg_so
0250: 75 72 63 65 28 6f 77 6e 65 72 29 3b 0a 0a 63 72 urce(owner);..cr
0260: 65 61 74 65 20 74 61 62 6c 65 20 72 73 73 74 67 eate table rsstg
0270: 5f 70 6f 73 74 20 28 0a 09 73 6f 75 72 63 65 5f _post (..source_
0280: 69 64 20 69 6e 74 65 67 65 72 20 6e 6f 74 20 6e id integer not n
0290: 75 6c 6c 2c 0a 09 70 6f 73 74 65 64 20 74 69 6d ull,..posted tim
02a0: 65 73 74 61 6d 70 74 7a 20 6e 6f 74 20 6e 75 6c estamptz not nul
02b0: 6c 2c 0a 09 75 72 6c 20 74 65 78 74 20 6e 6f 74 l,..url text not
02c0: 20 6e 75 6c 6c 2c 0a 09 68 6f 75 72 20 73 6d 61 null,..hour sma
02d0: 6c 6c 69 6e 74 20 6e 6f 74 20 6e 75 6c 6c 20 67 llint not null g
02e0: 65 6e 65 72 61 74 65 64 20 61 6c 77 61 79 73 20 enerated always
02f0: 61 73 20 28 65 78 74 72 61 63 74 28 27 68 6f 75 as (extract('hou
0300: 72 27 20 66 72 6f 6d 20 70 6f 73 74 65 64 20 61 r' from posted a
0310: 74 20 74 69 6d 65 20 7a 6f 6e 65 20 27 75 74 63 t time zone 'utc
0320: 27 29 29 20 73 74 6f 72 65 64 2c 0a 09 68 78 6d ')) stored,..hxm
0330: 20 73 6d 61 6c 6c 69 6e 74 20 6e 6f 74 20 6e 75 smallint not nu
0340: 6c 6c 20 67 65 6e 65 72 61 74 65 64 20 61 6c 77 ll generated alw
0350: 61 79 73 20 61 73 20 28 68 78 6d 28 70 6f 73 74 ays as (hxm(post
0360: 65 64 29 29 20 73 74 6f 72 65 64 2c 0a 09 46 4f ed)) stored,..FO
0370: 52 45 49 47 4e 20 4b 45 59 20 28 73 6f 75 72 63 REIGN KEY (sourc
0380: 65 5f 69 64 29 20 52 45 46 45 52 45 4e 43 45 53 e_id) REFERENCES
0390: 20 72 73 73 74 67 5f 73 6f 75 72 63 65 28 73 6f rsstg_source(so
03a0: 75 72 63 65 5f 69 64 29 20 6f 6e 20 64 65 6c 65 urce_id) on dele
03b0: 74 65 20 63 61 73 63 61 64 65 2c 0a 29 3b 0a 63 te cascade,.);.c
03c0: 72 65 61 74 65 20 75 6e 69 71 75 65 20 69 6e 64 reate unique ind
03d0: 65 78 20 72 73 73 74 67 5f 70 6f 73 74 5f 5f 75 ex rsstg_post__u
03e0: 72 6c 20 6f 6e 20 72 73 73 74 67 5f 70 6f 73 74 rl on rsstg_post
03f0: 28 75 72 6c 29 3b 0a 63 72 65 61 74 65 20 69 6e (url);.create in
0400: 64 65 78 20 72 73 73 74 67 5f 70 6f 73 74 5f 5f dex rsstg_post__
0410: 68 6f 75 72 20 6f 6e 20 72 73 73 74 67 5f 70 6f hour on rsstg_po
0420: 73 74 28 68 6f 75 72 29 3b 0a 63 72 65 61 74 65 st(hour);.create
0430: 20 69 6e 64 65 78 20 72 73 73 74 67 5f 70 6f 73 index rsstg_pos
0440: 74 5f 5f 70 6f 73 74 65 64 5f 68 6f 75 72 20 6f t__posted_hour o
0450: 6e 20 72 73 73 74 67 5f 70 6f 73 74 28 70 6f 73 n rsstg_post(pos
0460: 74 65 64 2c 68 6f 75 72 29 3b 0a 63 72 65 61 74 ted,hour);.creat
0470: 65 20 69 6e 64 65 78 20 72 73 73 74 67 5f 70 6f e index rsstg_po
0480: 73 74 5f 5f 68 78 6d 20 6f 6e 20 72 73 73 74 67 st__hxm on rsstg
0490: 5f 70 6f 73 74 28 68 78 6d 29 3b 0a 63 72 65 61 _post(hxm);.crea
04a0: 74 65 20 69 6e 64 65 78 20 72 73 73 74 67 5f 70 te index rsstg_p
04b0: 6f 73 74 5f 5f 70 6f 73 74 65 64 5f 68 78 6d 20 ost__posted_hxm
04c0: 6f 6e 20 72 73 73 74 67 5f 70 6f 73 74 28 70 6f on rsstg_post(po
04d0: 73 74 65 64 2c 68 78 6d 29 3b 0a 0a 63 72 65 61 sted,hxm);..crea
04e0: 74 65 20 6f 72 20 72 65 70 6c 61 63 65 20 76 69 te or replace vi
04f0: 65 77 20 72 73 73 74 67 5f 6f 72 64 65 72 5f 6f ew rsstg_order_o
0500: 6c 64 20 61 73 0a 09 73 65 6c 65 63 74 20 73 6f ld as..select so
0510: 75 72 63 65 5f 69 64 2c 20 63 6f 61 6c 65 73 63 urce_id, coalesc
0520: 65 28 6c 61 73 74 5f 73 63 72 61 70 65 20 2b 20 e(last_scrape +
0530: 6d 61 6b 65 5f 69 6e 74 65 72 76 61 6c 28 30 2c make_interval(0,
0540: 30 2c 30 2c 30 2c 30 2c 28 36 30 20 2f 20 28 63 0,0,0,0,(60 / (c
0550: 6f 61 6c 65 73 63 65 28 61 63 74 69 76 69 74 79 oalesce(activity
0560: 2c 20 31 29 2f 37 20 2b 20 31 29 20 29 3a 3a 69 , 1)/7 + 1) )::i
0570: 6e 74 65 67 65 72 29 2c 20 6e 6f 77 28 29 20 2d nteger), now() -
0580: 20 69 6e 74 65 72 76 61 6c 20 27 31 20 6d 69 6e interval '1 min
0590: 75 74 65 27 29 20 61 73 20 6e 65 78 74 5f 66 65 ute') as next_fe
05a0: 74 63 68 2c 20 6f 77 6e 65 72 0a 09 09 66 72 6f tch, owner...fro
05b0: 6d 20 72 73 73 74 67 5f 73 6f 75 72 63 65 20 6e m rsstg_source n
05c0: 61 74 75 72 61 6c 20 6c 65 66 74 20 6a 6f 69 6e atural left join
05d0: 0a 09 09 28 73 65 6c 65 63 74 20 73 6f 75 72 63 ...(select sourc
05e0: 65 5f 69 64 2c 20 63 6f 75 6e 74 28 2a 29 20 61 e_id, count(*) a
05f0: 73 20 61 63 74 69 76 69 74 79 0a 09 09 09 66 72 s activity....fr
0600: 6f 6d 20 72 73 73 74 67 5f 70 6f 73 74 20 77 68 om rsstg_post wh
0610: 65 72 65 20 0a 09 09 09 09 68 6f 75 72 20 3d 20 ere .....hour =
0620: 65 78 74 72 61 63 74 28 27 68 6f 75 72 27 20 66 extract('hour' f
0630: 72 6f 6d 20 6e 6f 77 28 29 29 3a 3a 73 6d 61 6c rom now())::smal
0640: 6c 69 6e 74 0a 09 09 09 09 61 6e 64 20 70 6f 73 lint.....and pos
0650: 74 65 64 20 3e 20 6e 6f 77 28 29 20 2d 20 69 6e ted > now() - in
0660: 74 65 72 76 61 6c 20 27 37 20 64 61 79 73 27 0a terval '7 days'.
0670: 09 09 09 09 61 6e 64 20 70 6f 73 74 65 64 20 3c ....and posted <
0680: 20 6e 6f 77 28 29 20 2d 20 69 6e 74 65 72 76 61 now() - interva
0690: 6c 20 27 31 20 68 6f 75 72 27 0a 09 09 09 67 72 l '1 hour'....gr
06a0: 6f 75 70 20 62 79 20 73 6f 75 72 63 65 5f 69 64 oup by source_id
06b0: 29 20 61 73 20 61 63 74 0a 09 09 77 68 65 72 65 ) as act...where
06c0: 20 65 6e 61 62 6c 65 64 0a 09 09 6f 72 64 65 72 enabled...order
06d0: 20 62 79 20 6e 65 78 74 5f 66 65 74 63 68 3b 0a by next_fetch;.
06e0: 0a 63 72 65 61 74 65 20 6f 72 20 72 65 70 6c 61 .create or repla
06f0: 63 65 20 66 75 6e 63 74 69 6f 6e 20 68 78 6d 28 ce function hxm(
0700: 74 69 6d 65 73 74 61 6d 70 74 7a 29 20 72 65 74 timestamptz) ret
0710: 75 72 6e 73 20 73 6d 61 6c 6c 69 6e 74 0a 09 61 urns smallint..a
0720: 73 20 24 24 20 73 65 6c 65 63 74 28 65 78 74 72 s $$ select(extr
0730: 61 63 74 28 27 68 6f 75 72 27 20 66 72 6f 6d 20 act('hour' from
0740: 24 31 29 20 2a 20 65 78 74 72 61 63 74 28 27 6d $1) * extract('m
0750: 69 6e 75 74 65 27 20 66 72 6f 6d 20 24 31 29 29 inute' from $1))
0760: 3b 20 24 24 0a 09 6c 61 6e 67 75 61 67 65 20 73 ; $$..language s
0770: 71 6c 20 69 6d 6d 75 74 61 62 6c 65 20 72 65 74 ql immutable ret
0780: 75 72 6e 73 20 6e 75 6c 6c 20 6f 6e 20 6e 75 6c urns null on nul
0790: 6c 20 69 6e 70 75 74 3b 0a 0a 63 72 65 61 74 65 l input;..create
07a0: 20 6f 72 20 72 65 70 6c 61 63 65 20 76 69 65 77 or replace view
07b0: 20 72 73 73 74 67 5f 6f 72 64 65 72 20 61 73 0a rsstg_order as.
07c0: 09 73 65 6c 65 63 74 20 73 6f 75 72 63 65 5f 69 .select source_i
07d0: 64 2c 20 63 6f 61 6c 65 73 63 65 28 6c 61 73 74 d, coalesce(last
07e0: 5f 73 63 72 61 70 65 20 2b 20 6d 61 6b 65 5f 69 _scrape + make_i
07f0: 6e 74 65 72 76 61 6c 28 30 2c 30 2c 30 2c 30 2c nterval(0,0,0,0,
0800: 30 2c 28 36 30 20 2f 20 28 63 6f 61 6c 65 73 63 0,(60 / (coalesc
0810: 65 28 61 63 74 69 76 69 74 79 2c 20 31 29 2f 37 e(activity, 1)/7
0820: 20 2b 20 31 29 20 29 3a 3a 69 6e 74 65 67 65 72 + 1) )::integer
0830: 29 2c 20 6e 6f 77 28 29 20 2d 20 69 6e 74 65 72 ), now() - inter
0840: 76 61 6c 20 27 31 20 6d 69 6e 75 74 65 27 29 20 val '1 minute')
0850: 61 73 20 6e 65 78 74 5f 66 65 74 63 68 2c 20 6f as next_fetch, o
0860: 77 6e 65 72 0a 09 09 66 72 6f 6d 20 72 73 73 74 wner...from rsst
0870: 67 5f 73 6f 75 72 63 65 20 6e 61 74 75 72 61 6c g_source natural
0880: 20 6c 65 66 74 20 6a 6f 69 6e 0a 09 09 28 73 65 left join...(se
0890: 6c 65 63 74 20 73 6f 75 72 63 65 5f 69 64 2c 20 lect source_id,
08a0: 63 6f 75 6e 74 28 2a 29 20 61 73 20 61 63 74 69 count(*) as acti
08b0: 76 69 74 79 0a 09 09 09 66 72 6f 6d 20 72 73 73 vity....from rss
08c0: 74 67 5f 70 6f 73 74 20 77 68 65 72 65 20 0a 09 tg_post where ..
08d0: 09 09 20 20 28 0a 09 09 09 09 09 28 68 78 6d 20 .. (......(hxm
08e0: 3e 20 68 78 6d 28 6e 6f 77 28 29 29 20 2d 20 33 > hxm(now()) - 3
08f0: 30 20 61 6e 64 20 68 78 6d 20 3c 20 68 78 6d 28 0 and hxm < hxm(
0900: 6e 6f 77 28 29 29 20 2b 20 33 30 29 0a 09 09 09 now()) + 30)....
0910: 09 09 6f 72 20 28 68 78 6d 20 3c 20 33 30 20 61 ..or (hxm < 30 a
0920: 6e 64 20 68 78 6d 20 3c 20 68 78 6d 28 6e 6f 77 nd hxm < hxm(now
0930: 28 29 29 20 2b 20 33 30 20 2d 20 31 34 34 30 29 ()) + 30 - 1440)
0940: 0a 09 09 09 09 09 6f 72 20 28 68 78 6d 20 3e 20 ......or (hxm >
0950: 31 34 31 30 20 61 6e 64 20 68 78 6d 20 3e 20 31 1410 and hxm > 1
0960: 34 34 30 20 2b 20 68 78 6d 28 6e 6f 77 28 29 29 440 + hxm(now())
0970: 20 2d 20 33 30 29 0a 09 09 09 20 20 29 0a 09 09 - 30).... )...
0980: 09 09 61 6e 64 20 70 6f 73 74 65 64 20 3c 20 6e ..and posted < n
0990: 6f 77 28 29 20 2d 20 69 6e 74 65 72 76 61 6c 20 ow() - interval
09a0: 27 31 20 68 6f 75 72 27 0a 09 09 09 09 61 6e 64 '1 hour'.....and
09b0: 20 70 6f 73 74 65 64 20 3e 20 6e 6f 77 28 29 20 posted > now()
09c0: 2d 20 69 6e 74 65 72 76 61 6c 20 27 37 20 64 61 - interval '7 da
09d0: 79 73 27 0a 09 09 09 67 72 6f 75 70 20 62 79 20 ys'....group by
09e0: 73 6f 75 72 63 65 5f 69 64 29 20 61 73 20 61 63 source_id) as ac
09f0: 74 0a 09 09 77 68 65 72 65 20 65 6e 61 62 6c 65 t...where enable
0a00: 64 0a 09 09 6f 72 64 65 72 20 62 79 20 6e 65 78 d...order by nex
0a10: 74 5f 66 65 74 63 68 3b 0a t_fetch;.