Artifact
3e04f7b82924df9a0cced6af0c0be945d6c43ca74d8cef282ab62ac76e839d6f:
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 66 61 6c 73 65 2c 0a 09 69 76 fault false,..iv
0160: 5f 68 61 73 68 20 74 65 78 74 2c 0a 09 6f 77 6e _hash text,..own
0170: 65 72 20 62 69 67 69 6e 74 20 6e 6f 74 20 6e 75 er bigint not nu
0180: 6c 6c 29 3b 0a 63 72 65 61 74 65 20 75 6e 69 71 ll);.create uniq
0190: 75 65 20 69 6e 64 65 78 20 72 73 73 74 67 5f 73 ue index rsstg_s
01a0: 6f 75 72 63 65 5f 5f 73 6f 75 72 63 65 5f 69 64 ource__source_id
01b0: 20 6f 6e 20 72 73 73 74 67 5f 73 6f 75 72 63 65 on rsstg_source
01c0: 28 73 6f 75 72 63 65 5f 69 64 29 3b 0a 63 72 65 (source_id);.cre
01d0: 61 74 65 20 75 6e 69 71 75 65 20 69 6e 64 65 78 ate unique index
01e0: 20 72 73 73 74 67 5f 73 6f 75 72 63 65 5f 5f 63 rsstg_source__c
01f0: 68 61 6e 6e 65 6c 5f 69 64 5f 5f 6f 77 6e 65 72 hannel_id__owner
0200: 20 6f 6e 20 72 73 73 74 67 5f 73 6f 75 72 63 65 on rsstg_source
0210: 28 63 68 61 6e 6e 65 6c 5f 69 64 2c 20 6f 77 6e (channel_id, own
0220: 65 72 29 3b 0a 63 72 65 61 74 65 20 69 6e 64 65 er);.create inde
0230: 78 20 72 73 73 74 67 5f 73 6f 75 72 63 65 5f 5f x rsstg_source__
0240: 6f 77 6e 65 72 20 6f 6e 20 72 73 73 74 67 5f 73 owner on rsstg_s
0250: 6f 75 72 63 65 28 6f 77 6e 65 72 29 3b 0a 0a 63 ource(owner);..c
0260: 72 65 61 74 65 20 74 61 62 6c 65 20 72 73 73 74 reate table rsst
0270: 67 5f 70 6f 73 74 20 28 0a 09 73 6f 75 72 63 65 g_post (..source
0280: 5f 69 64 20 69 6e 74 65 67 65 72 20 6e 6f 74 20 _id integer not
0290: 6e 75 6c 6c 2c 0a 09 70 6f 73 74 65 64 20 74 69 null,..posted ti
02a0: 6d 65 73 74 61 6d 70 74 7a 20 6e 6f 74 20 6e 75 mestamptz not nu
02b0: 6c 6c 2c 0a 09 75 72 6c 20 74 65 78 74 20 6e 6f ll,..url text no
02c0: 74 20 6e 75 6c 6c 2c 0a 09 68 6f 75 72 20 73 6d t null,..hour sm
02d0: 61 6c 6c 69 6e 74 20 6e 6f 74 20 6e 75 6c 6c 20 allint not null
02e0: 67 65 6e 65 72 61 74 65 64 20 61 6c 77 61 79 73 generated always
02f0: 20 61 73 20 28 65 78 74 72 61 63 74 28 27 68 6f as (extract('ho
0300: 75 72 27 20 66 72 6f 6d 20 70 6f 73 74 65 64 20 ur' from posted
0310: 61 74 20 74 69 6d 65 20 7a 6f 6e 65 20 27 75 74 at time zone 'ut
0320: 63 27 29 29 20 73 74 6f 72 65 64 2c 0a 09 68 78 c')) stored,..hx
0330: 6d 20 73 6d 61 6c 6c 69 6e 74 20 6e 6f 74 20 6e m smallint not n
0340: 75 6c 6c 20 67 65 6e 65 72 61 74 65 64 20 61 6c ull generated al
0350: 77 61 79 73 20 61 73 20 28 68 78 6d 28 70 6f 73 ways as (hxm(pos
0360: 74 65 64 29 29 20 73 74 6f 72 65 64 2c 0a 09 46 ted)) stored,..F
0370: 4f 52 45 49 47 4e 20 4b 45 59 20 28 73 6f 75 72 OREIGN KEY (sour
0380: 63 65 5f 69 64 29 20 52 45 46 45 52 45 4e 43 45 ce_id) REFERENCE
0390: 53 20 72 73 73 74 67 5f 73 6f 75 72 63 65 28 73 S rsstg_source(s
03a0: 6f 75 72 63 65 5f 69 64 29 20 6f 6e 20 64 65 6c ource_id) on del
03b0: 65 74 65 20 63 61 73 63 61 64 65 2c 0a 29 3b 0a ete cascade,.);.
03c0: 63 72 65 61 74 65 20 75 6e 69 71 75 65 20 69 6e create unique in
03d0: 64 65 78 20 72 73 73 74 67 5f 70 6f 73 74 5f 5f dex rsstg_post__
03e0: 75 72 6c 20 6f 6e 20 72 73 73 74 67 5f 70 6f 73 url on rsstg_pos
03f0: 74 28 75 72 6c 29 3b 0a 63 72 65 61 74 65 20 69 t(url);.create i
0400: 6e 64 65 78 20 72 73 73 74 67 5f 70 6f 73 74 5f ndex rsstg_post_
0410: 5f 68 6f 75 72 20 6f 6e 20 72 73 73 74 67 5f 70 _hour on rsstg_p
0420: 6f 73 74 28 68 6f 75 72 29 3b 0a 63 72 65 61 74 ost(hour);.creat
0430: 65 20 69 6e 64 65 78 20 72 73 73 74 67 5f 70 6f e index rsstg_po
0440: 73 74 5f 5f 70 6f 73 74 65 64 5f 68 6f 75 72 20 st__posted_hour
0450: 6f 6e 20 72 73 73 74 67 5f 70 6f 73 74 28 70 6f on rsstg_post(po
0460: 73 74 65 64 2c 68 6f 75 72 29 3b 0a 63 72 65 61 sted,hour);.crea
0470: 74 65 20 69 6e 64 65 78 20 72 73 73 74 67 5f 70 te index rsstg_p
0480: 6f 73 74 5f 5f 68 78 6d 20 6f 6e 20 72 73 73 74 ost__hxm on rsst
0490: 67 5f 70 6f 73 74 28 68 78 6d 29 3b 0a 63 72 65 g_post(hxm);.cre
04a0: 61 74 65 20 69 6e 64 65 78 20 72 73 73 74 67 5f ate index rsstg_
04b0: 70 6f 73 74 5f 5f 70 6f 73 74 65 64 5f 68 78 6d post__posted_hxm
04c0: 20 6f 6e 20 72 73 73 74 67 5f 70 6f 73 74 28 70 on rsstg_post(p
04d0: 6f 73 74 65 64 2c 68 78 6d 29 3b 0a 0a 63 72 65 osted,hxm);..cre
04e0: 61 74 65 20 6f 72 20 72 65 70 6c 61 63 65 20 76 ate or replace v
04f0: 69 65 77 20 72 73 73 74 67 5f 6f 72 64 65 72 5f iew rsstg_order_
0500: 6f 6c 64 20 61 73 0a 09 73 65 6c 65 63 74 20 73 old as..select s
0510: 6f 75 72 63 65 5f 69 64 2c 20 63 6f 61 6c 65 73 ource_id, coales
0520: 63 65 28 6c 61 73 74 5f 73 63 72 61 70 65 20 2b ce(last_scrape +
0530: 20 6d 61 6b 65 5f 69 6e 74 65 72 76 61 6c 28 30 make_interval(0
0540: 2c 30 2c 30 2c 30 2c 30 2c 28 36 30 20 2f 20 28 ,0,0,0,0,(60 / (
0550: 63 6f 61 6c 65 73 63 65 28 61 63 74 69 76 69 74 coalesce(activit
0560: 79 2c 20 31 29 2f 37 20 2b 20 31 29 20 29 3a 3a y, 1)/7 + 1) )::
0570: 69 6e 74 65 67 65 72 29 2c 20 6e 6f 77 28 29 20 integer), now()
0580: 2d 20 69 6e 74 65 72 76 61 6c 20 27 31 20 6d 69 - interval '1 mi
0590: 6e 75 74 65 27 29 20 61 73 20 6e 65 78 74 5f 66 nute') as next_f
05a0: 65 74 63 68 2c 20 6f 77 6e 65 72 0a 09 09 66 72 etch, owner...fr
05b0: 6f 6d 20 72 73 73 74 67 5f 73 6f 75 72 63 65 20 om rsstg_source
05c0: 6e 61 74 75 72 61 6c 20 6c 65 66 74 20 6a 6f 69 natural left joi
05d0: 6e 0a 09 09 28 73 65 6c 65 63 74 20 73 6f 75 72 n...(select sour
05e0: 63 65 5f 69 64 2c 20 63 6f 75 6e 74 28 2a 29 20 ce_id, count(*)
05f0: 61 73 20 61 63 74 69 76 69 74 79 0a 09 09 09 66 as activity....f
0600: 72 6f 6d 20 72 73 73 74 67 5f 70 6f 73 74 20 77 rom rsstg_post w
0610: 68 65 72 65 20 0a 09 09 09 09 68 6f 75 72 20 3d here .....hour =
0620: 20 65 78 74 72 61 63 74 28 27 68 6f 75 72 27 20 extract('hour'
0630: 66 72 6f 6d 20 6e 6f 77 28 29 29 3a 3a 73 6d 61 from now())::sma
0640: 6c 6c 69 6e 74 0a 09 09 09 09 61 6e 64 20 70 6f llint.....and po
0650: 73 74 65 64 20 3e 20 6e 6f 77 28 29 20 2d 20 69 sted > now() - i
0660: 6e 74 65 72 76 61 6c 20 27 37 20 64 61 79 73 27 nterval '7 days'
0670: 0a 09 09 09 09 61 6e 64 20 70 6f 73 74 65 64 20 .....and posted
0680: 3c 20 6e 6f 77 28 29 20 2d 20 69 6e 74 65 72 76 < now() - interv
0690: 61 6c 20 27 31 20 68 6f 75 72 27 0a 09 09 09 67 al '1 hour'....g
06a0: 72 6f 75 70 20 62 79 20 73 6f 75 72 63 65 5f 69 roup by source_i
06b0: 64 29 20 61 73 20 61 63 74 0a 09 09 77 68 65 72 d) as act...wher
06c0: 65 20 65 6e 61 62 6c 65 64 0a 09 09 6f 72 64 65 e enabled...orde
06d0: 72 20 62 79 20 6e 65 78 74 5f 66 65 74 63 68 3b r by next_fetch;
06e0: 0a 0a 63 72 65 61 74 65 20 6f 72 20 72 65 70 6c ..create or repl
06f0: 61 63 65 20 66 75 6e 63 74 69 6f 6e 20 68 78 6d ace function hxm
0700: 28 74 69 6d 65 73 74 61 6d 70 74 7a 29 20 72 65 (timestamptz) re
0710: 74 75 72 6e 73 20 73 6d 61 6c 6c 69 6e 74 0a 09 turns smallint..
0720: 61 73 20 24 24 20 73 65 6c 65 63 74 28 65 78 74 as $$ select(ext
0730: 72 61 63 74 28 27 68 6f 75 72 27 20 66 72 6f 6d ract('hour' from
0740: 20 24 31 29 20 2a 20 65 78 74 72 61 63 74 28 27 $1) * extract('
0750: 6d 69 6e 75 74 65 27 20 66 72 6f 6d 20 24 31 29 minute' from $1)
0760: 29 3b 20 24 24 0a 09 6c 61 6e 67 75 61 67 65 20 ); $$..language
0770: 73 71 6c 20 69 6d 6d 75 74 61 62 6c 65 20 72 65 sql immutable re
0780: 74 75 72 6e 73 20 6e 75 6c 6c 20 6f 6e 20 6e 75 turns null on nu
0790: 6c 6c 20 69 6e 70 75 74 3b 0a 0a 63 72 65 61 74 ll input;..creat
07a0: 65 20 6f 72 20 72 65 70 6c 61 63 65 20 76 69 65 e or replace vie
07b0: 77 20 72 73 73 74 67 5f 6f 72 64 65 72 20 61 73 w rsstg_order as
07c0: 0a 09 73 65 6c 65 63 74 20 73 6f 75 72 63 65 5f ..select source_
07d0: 69 64 2c 20 63 6f 61 6c 65 73 63 65 28 6c 61 73 id, coalesce(las
07e0: 74 5f 73 63 72 61 70 65 20 2b 20 6d 61 6b 65 5f t_scrape + make_
07f0: 69 6e 74 65 72 76 61 6c 28 30 2c 30 2c 30 2c 30 interval(0,0,0,0
0800: 2c 30 2c 28 36 30 20 2f 20 28 63 6f 61 6c 65 73 ,0,(60 / (coales
0810: 63 65 28 61 63 74 69 76 69 74 79 2c 20 31 29 2f ce(activity, 1)/
0820: 37 20 2b 20 31 29 20 29 3a 3a 69 6e 74 65 67 65 7 + 1) )::intege
0830: 72 29 2c 20 6e 6f 77 28 29 20 2d 20 69 6e 74 65 r), now() - inte
0840: 72 76 61 6c 20 27 31 20 6d 69 6e 75 74 65 27 29 rval '1 minute')
0850: 20 61 73 20 6e 65 78 74 5f 66 65 74 63 68 2c 20 as next_fetch,
0860: 6f 77 6e 65 72 0a 09 09 66 72 6f 6d 20 72 73 73 owner...from rss
0870: 74 67 5f 73 6f 75 72 63 65 20 6e 61 74 75 72 61 tg_source natura
0880: 6c 20 6c 65 66 74 20 6a 6f 69 6e 0a 09 09 28 73 l left join...(s
0890: 65 6c 65 63 74 20 73 6f 75 72 63 65 5f 69 64 2c elect source_id,
08a0: 20 63 6f 75 6e 74 28 2a 29 20 61 73 20 61 63 74 count(*) as act
08b0: 69 76 69 74 79 0a 09 09 09 66 72 6f 6d 20 72 73 ivity....from rs
08c0: 73 74 67 5f 70 6f 73 74 20 77 68 65 72 65 20 0a stg_post where .
08d0: 09 09 09 20 20 28 0a 09 09 09 09 09 28 68 78 6d ... (......(hxm
08e0: 20 3e 20 68 78 6d 28 6e 6f 77 28 29 29 20 2d 20 > hxm(now()) -
08f0: 33 30 20 61 6e 64 20 68 78 6d 20 3c 20 68 78 6d 30 and hxm < hxm
0900: 28 6e 6f 77 28 29 29 20 2b 20 33 30 29 0a 09 09 (now()) + 30)...
0910: 09 09 09 6f 72 20 28 68 78 6d 20 3c 20 33 30 20 ...or (hxm < 30
0920: 61 6e 64 20 68 78 6d 20 3c 20 68 78 6d 28 6e 6f and hxm < hxm(no
0930: 77 28 29 29 20 2b 20 33 30 20 2d 20 31 34 34 30 w()) + 30 - 1440
0940: 29 0a 09 09 09 09 09 6f 72 20 28 68 78 6d 20 3e )......or (hxm >
0950: 20 31 34 31 30 20 61 6e 64 20 68 78 6d 20 3e 20 1410 and hxm >
0960: 31 34 34 30 20 2b 20 68 78 6d 28 6e 6f 77 28 29 1440 + hxm(now()
0970: 29 20 2d 20 33 30 29 0a 09 09 09 20 20 29 0a 09 ) - 30).... )..
0980: 09 09 09 61 6e 64 20 70 6f 73 74 65 64 20 3c 20 ...and posted <
0990: 6e 6f 77 28 29 20 2d 20 69 6e 74 65 72 76 61 6c now() - interval
09a0: 20 27 31 20 68 6f 75 72 27 0a 09 09 09 09 61 6e '1 hour'.....an
09b0: 64 20 70 6f 73 74 65 64 20 3e 20 6e 6f 77 28 29 d posted > now()
09c0: 20 2d 20 69 6e 74 65 72 76 61 6c 20 27 37 20 64 - interval '7 d
09d0: 61 79 73 27 0a 09 09 09 67 72 6f 75 70 20 62 79 ays'....group by
09e0: 20 73 6f 75 72 63 65 5f 69 64 29 20 61 73 20 61 source_id) as a
09f0: 63 74 0a 09 09 77 68 65 72 65 20 65 6e 61 62 6c ct...where enabl
0a00: 65 64 0a 09 09 6f 72 64 65 72 20 62 79 20 6e 65 ed...order by ne
0a10: 78 74 5f 66 65 74 63 68 3b 0a xt_fetch;.