Artifact
5ade9090d57ba998b7deca48aa2cd3dfb7f00cb3dadcac7ce9bec45c7e7fbe74:
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 2c 0a 09 75 72 6c 5f 72 65 20 74 65 78 74 29 l,..url_re text)
0190: 3b 0a 63 72 65 61 74 65 20 75 6e 69 71 75 65 20 ;.create unique
01a0: 69 6e 64 65 78 20 72 73 73 74 67 5f 73 6f 75 72 index rsstg_sour
01b0: 63 65 5f 5f 73 6f 75 72 63 65 5f 69 64 20 6f 6e ce__source_id on
01c0: 20 72 73 73 74 67 5f 73 6f 75 72 63 65 28 73 6f rsstg_source(so
01d0: 75 72 63 65 5f 69 64 29 3b 0a 63 72 65 61 74 65 urce_id);.create
01e0: 20 75 6e 69 71 75 65 20 69 6e 64 65 78 20 72 73 unique index rs
01f0: 73 74 67 5f 73 6f 75 72 63 65 5f 5f 63 68 61 6e stg_source__chan
0200: 6e 65 6c 5f 69 64 5f 5f 6f 77 6e 65 72 20 6f 6e nel_id__owner on
0210: 20 72 73 73 74 67 5f 73 6f 75 72 63 65 28 63 68 rsstg_source(ch
0220: 61 6e 6e 65 6c 5f 69 64 2c 20 6f 77 6e 65 72 29 annel_id, owner)
0230: 3b 0a 63 72 65 61 74 65 20 69 6e 64 65 78 20 72 ;.create index r
0240: 73 73 74 67 5f 73 6f 75 72 63 65 5f 5f 6f 77 6e sstg_source__own
0250: 65 72 20 6f 6e 20 72 73 73 74 67 5f 73 6f 75 72 er on rsstg_sour
0260: 63 65 28 6f 77 6e 65 72 29 3b 0a 0a 63 72 65 61 ce(owner);..crea
0270: 74 65 20 74 61 62 6c 65 20 72 73 73 74 67 5f 70 te table rsstg_p
0280: 6f 73 74 20 28 0a 09 73 6f 75 72 63 65 5f 69 64 ost (..source_id
0290: 20 69 6e 74 65 67 65 72 20 6e 6f 74 20 6e 75 6c integer not nul
02a0: 6c 2c 0a 09 70 6f 73 74 65 64 20 74 69 6d 65 73 l,..posted times
02b0: 74 61 6d 70 74 7a 20 6e 6f 74 20 6e 75 6c 6c 2c tamptz not null,
02c0: 0a 09 75 72 6c 20 74 65 78 74 20 6e 6f 74 20 6e ..url text not n
02d0: 75 6c 6c 2c 0a 09 68 6f 75 72 20 73 6d 61 6c 6c ull,..hour small
02e0: 69 6e 74 20 6e 6f 74 20 6e 75 6c 6c 20 67 65 6e int not null gen
02f0: 65 72 61 74 65 64 20 61 6c 77 61 79 73 20 61 73 erated always as
0300: 20 28 65 78 74 72 61 63 74 28 27 68 6f 75 72 27 (extract('hour'
0310: 20 66 72 6f 6d 20 70 6f 73 74 65 64 20 61 74 20 from posted at
0320: 74 69 6d 65 20 7a 6f 6e 65 20 27 75 74 63 27 29 time zone 'utc')
0330: 29 20 73 74 6f 72 65 64 2c 0a 09 68 78 6d 20 73 ) stored,..hxm s
0340: 6d 61 6c 6c 69 6e 74 20 6e 6f 74 20 6e 75 6c 6c mallint not null
0350: 20 67 65 6e 65 72 61 74 65 64 20 61 6c 77 61 79 generated alway
0360: 73 20 61 73 20 28 68 78 6d 28 70 6f 73 74 65 64 s as (hxm(posted
0370: 29 29 20 73 74 6f 72 65 64 2c 0a 09 46 4f 52 45 )) stored,..FORE
0380: 49 47 4e 20 4b 45 59 20 28 73 6f 75 72 63 65 5f IGN KEY (source_
0390: 69 64 29 20 52 45 46 45 52 45 4e 43 45 53 20 72 id) REFERENCES r
03a0: 73 73 74 67 5f 73 6f 75 72 63 65 28 73 6f 75 72 sstg_source(sour
03b0: 63 65 5f 69 64 29 20 6f 6e 20 64 65 6c 65 74 65 ce_id) on delete
03c0: 20 63 61 73 63 61 64 65 0a 29 3b 0a 63 72 65 61 cascade.);.crea
03d0: 74 65 20 75 6e 69 71 75 65 20 69 6e 64 65 78 20 te unique index
03e0: 72 73 73 74 67 5f 70 6f 73 74 5f 5f 75 72 6c 20 rsstg_post__url
03f0: 6f 6e 20 72 73 73 74 67 5f 70 6f 73 74 28 75 72 on rsstg_post(ur
0400: 6c 29 3b 0a 63 72 65 61 74 65 20 69 6e 64 65 78 l);.create index
0410: 20 72 73 73 74 67 5f 70 6f 73 74 5f 5f 68 6f 75 rsstg_post__hou
0420: 72 20 6f 6e 20 72 73 73 74 67 5f 70 6f 73 74 28 r on rsstg_post(
0430: 68 6f 75 72 29 3b 0a 63 72 65 61 74 65 20 69 6e hour);.create in
0440: 64 65 78 20 72 73 73 74 67 5f 70 6f 73 74 5f 5f dex rsstg_post__
0450: 70 6f 73 74 65 64 5f 68 6f 75 72 20 6f 6e 20 72 posted_hour on r
0460: 73 73 74 67 5f 70 6f 73 74 28 70 6f 73 74 65 64 sstg_post(posted
0470: 2c 68 6f 75 72 29 3b 0a 63 72 65 61 74 65 20 69 ,hour);.create i
0480: 6e 64 65 78 20 72 73 73 74 67 5f 70 6f 73 74 5f ndex rsstg_post_
0490: 5f 68 78 6d 20 6f 6e 20 72 73 73 74 67 5f 70 6f _hxm on rsstg_po
04a0: 73 74 28 68 78 6d 29 3b 0a 63 72 65 61 74 65 20 st(hxm);.create
04b0: 69 6e 64 65 78 20 72 73 73 74 67 5f 70 6f 73 74 index rsstg_post
04c0: 5f 5f 70 6f 73 74 65 64 5f 68 78 6d 20 6f 6e 20 __posted_hxm on
04d0: 72 73 73 74 67 5f 70 6f 73 74 28 70 6f 73 74 65 rsstg_post(poste
04e0: 64 2c 68 78 6d 29 3b 0a 0a 63 72 65 61 74 65 20 d,hxm);..create
04f0: 6f 72 20 72 65 70 6c 61 63 65 20 76 69 65 77 20 or replace view
0500: 72 73 73 74 67 5f 6f 72 64 65 72 5f 6f 6c 64 20 rsstg_order_old
0510: 61 73 0a 09 73 65 6c 65 63 74 0a 09 09 73 6f 75 as..select...sou
0520: 72 63 65 5f 69 64 2c 0a 09 09 63 6f 61 6c 65 73 rce_id,...coales
0530: 63 65 28 6c 61 73 74 5f 73 63 72 61 70 65 20 2b ce(last_scrape +
0540: 20 6d 61 6b 65 5f 69 6e 74 65 72 76 61 6c 28 30 make_interval(0
0550: 2c 30 2c 30 2c 30 2c 30 2c 28 36 30 20 2f 20 28 ,0,0,0,0,(60 / (
0560: 63 6f 61 6c 65 73 63 65 28 61 63 74 69 76 69 74 coalesce(activit
0570: 79 2c 20 31 29 2f 37 20 2b 20 31 29 20 29 3a 3a y, 1)/7 + 1) )::
0580: 69 6e 74 65 67 65 72 29 2c 20 6e 6f 77 28 29 20 integer), now()
0590: 2d 20 69 6e 74 65 72 76 61 6c 20 27 31 20 6d 69 - interval '1 mi
05a0: 6e 75 74 65 27 29 20 61 73 20 6e 65 78 74 5f 66 nute') as next_f
05b0: 65 74 63 68 2c 0a 09 09 6f 77 6e 65 72 2c 0a 09 etch,...owner,..
05c0: 09 6c 61 73 74 5f 73 63 72 61 70 65 0a 09 66 72 .last_scrape..fr
05d0: 6f 6d 20 72 73 73 74 67 5f 73 6f 75 72 63 65 20 om rsstg_source
05e0: 6e 61 74 75 72 61 6c 20 6c 65 66 74 20 6a 6f 69 natural left joi
05f0: 6e 0a 09 09 28 73 65 6c 65 63 74 20 73 6f 75 72 n...(select sour
0600: 63 65 5f 69 64 2c 20 63 6f 75 6e 74 28 2a 29 20 ce_id, count(*)
0610: 61 73 20 61 63 74 69 76 69 74 79 0a 09 09 09 66 as activity....f
0620: 72 6f 6d 20 72 73 73 74 67 5f 70 6f 73 74 20 77 rom rsstg_post w
0630: 68 65 72 65 20 0a 09 09 09 09 68 6f 75 72 20 3d here .....hour =
0640: 20 65 78 74 72 61 63 74 28 27 68 6f 75 72 27 20 extract('hour'
0650: 66 72 6f 6d 20 6e 6f 77 28 29 29 3a 3a 73 6d 61 from now())::sma
0660: 6c 6c 69 6e 74 0a 09 09 09 09 61 6e 64 20 70 6f llint.....and po
0670: 73 74 65 64 20 3e 20 6e 6f 77 28 29 20 2d 20 69 sted > now() - i
0680: 6e 74 65 72 76 61 6c 20 27 37 20 64 61 79 73 27 nterval '7 days'
0690: 0a 09 09 09 09 61 6e 64 20 70 6f 73 74 65 64 20 .....and posted
06a0: 3c 20 6e 6f 77 28 29 20 2d 20 69 6e 74 65 72 76 < now() - interv
06b0: 61 6c 20 27 31 20 68 6f 75 72 27 0a 09 09 09 67 al '1 hour'....g
06c0: 72 6f 75 70 20 62 79 20 73 6f 75 72 63 65 5f 69 roup by source_i
06d0: 64 29 20 61 73 20 61 63 74 0a 09 09 77 68 65 72 d) as act...wher
06e0: 65 20 65 6e 61 62 6c 65 64 0a 09 09 6f 72 64 65 e enabled...orde
06f0: 72 20 62 79 20 6e 65 78 74 5f 66 65 74 63 68 3b r by next_fetch;
0700: 0a 0a 63 72 65 61 74 65 20 6f 72 20 72 65 70 6c ..create or repl
0710: 61 63 65 20 66 75 6e 63 74 69 6f 6e 20 68 78 6d ace function hxm
0720: 28 74 69 6d 65 73 74 61 6d 70 74 7a 29 20 72 65 (timestamptz) re
0730: 74 75 72 6e 73 20 73 6d 61 6c 6c 69 6e 74 0a 09 turns smallint..
0740: 61 73 20 24 24 20 73 65 6c 65 63 74 28 65 78 74 as $$ select(ext
0750: 72 61 63 74 28 27 68 6f 75 72 27 20 66 72 6f 6d ract('hour' from
0760: 20 24 31 29 20 2a 20 65 78 74 72 61 63 74 28 27 $1) * extract('
0770: 6d 69 6e 75 74 65 27 20 66 72 6f 6d 20 24 31 29 minute' from $1)
0780: 29 3b 20 24 24 0a 09 6c 61 6e 67 75 61 67 65 20 ); $$..language
0790: 73 71 6c 20 69 6d 6d 75 74 61 62 6c 65 20 72 65 sql immutable re
07a0: 74 75 72 6e 73 20 6e 75 6c 6c 20 6f 6e 20 6e 75 turns null on nu
07b0: 6c 6c 20 69 6e 70 75 74 3b 0a 0a 63 72 65 61 74 ll input;..creat
07c0: 65 20 6f 72 20 72 65 70 6c 61 63 65 20 76 69 65 e or replace vie
07d0: 77 20 72 73 73 74 67 5f 6f 72 64 65 72 20 61 73 w rsstg_order as
07e0: 0a 09 73 65 6c 65 63 74 0a 09 09 73 6f 75 72 63 ..select...sourc
07f0: 65 5f 69 64 2c 0a 09 09 63 6f 61 6c 65 73 63 65 e_id,...coalesce
0800: 28 6c 61 73 74 5f 73 63 72 61 70 65 20 2b 20 6d (last_scrape + m
0810: 61 6b 65 5f 69 6e 74 65 72 76 61 6c 28 30 2c 30 ake_interval(0,0
0820: 2c 30 2c 30 2c 30 2c 28 36 30 20 2f 20 28 63 6f ,0,0,0,(60 / (co
0830: 61 6c 65 73 63 65 28 61 63 74 69 76 69 74 79 2c alesce(activity,
0840: 20 31 29 2f 37 20 2b 20 31 29 20 29 3a 3a 69 6e 1)/7 + 1) )::in
0850: 74 65 67 65 72 29 2c 20 6e 6f 77 28 29 20 2d 20 teger), now() -
0860: 69 6e 74 65 72 76 61 6c 20 27 31 20 6d 69 6e 75 interval '1 minu
0870: 74 65 27 29 20 61 73 20 6e 65 78 74 5f 66 65 74 te') as next_fet
0880: 63 68 2c 0a 09 09 6f 77 6e 65 72 2c 0a 09 09 6c ch,...owner,...l
0890: 61 73 74 5f 73 63 72 61 70 65 0a 09 66 72 6f 6d ast_scrape..from
08a0: 20 72 73 73 74 67 5f 73 6f 75 72 63 65 20 6e 61 rsstg_source na
08b0: 74 75 72 61 6c 20 6c 65 66 74 20 6a 6f 69 6e 0a tural left join.
08c0: 09 09 28 73 65 6c 65 63 74 20 73 6f 75 72 63 65 ..(select source
08d0: 5f 69 64 2c 20 63 6f 75 6e 74 28 2a 29 20 61 73 _id, count(*) as
08e0: 20 61 63 74 69 76 69 74 79 0a 09 09 09 66 72 6f activity....fro
08f0: 6d 20 72 73 73 74 67 5f 70 6f 73 74 20 77 68 65 m rsstg_post whe
0900: 72 65 20 0a 09 09 09 20 20 28 0a 09 09 09 09 09 re .... (......
0910: 28 68 78 6d 20 3e 20 68 78 6d 28 6e 6f 77 28 29 (hxm > hxm(now()
0920: 29 20 2d 20 33 30 20 61 6e 64 20 68 78 6d 20 3c ) - 30 and hxm <
0930: 20 68 78 6d 28 6e 6f 77 28 29 29 20 2b 20 33 30 hxm(now()) + 30
0940: 29 0a 09 09 09 09 09 6f 72 20 28 68 78 6d 20 3c )......or (hxm <
0950: 20 33 30 20 61 6e 64 20 68 78 6d 20 3c 20 68 78 30 and hxm < hx
0960: 6d 28 6e 6f 77 28 29 29 20 2b 20 33 30 20 2d 20 m(now()) + 30 -
0970: 31 34 34 30 29 0a 09 09 09 09 09 6f 72 20 28 68 1440)......or (h
0980: 78 6d 20 3e 20 31 34 31 30 20 61 6e 64 20 68 78 xm > 1410 and hx
0990: 6d 20 3e 20 31 34 34 30 20 2b 20 68 78 6d 28 6e m > 1440 + hxm(n
09a0: 6f 77 28 29 29 20 2d 20 33 30 29 0a 09 09 09 20 ow()) - 30)....
09b0: 20 29 0a 09 09 09 09 61 6e 64 20 70 6f 73 74 65 ).....and poste
09c0: 64 20 3c 20 6e 6f 77 28 29 20 2d 20 69 6e 74 65 d < now() - inte
09d0: 72 76 61 6c 20 27 31 20 68 6f 75 72 27 0a 09 09 rval '1 hour'...
09e0: 09 09 61 6e 64 20 70 6f 73 74 65 64 20 3e 20 6e ..and posted > n
09f0: 6f 77 28 29 20 2d 20 69 6e 74 65 72 76 61 6c 20 ow() - interval
0a00: 27 37 20 64 61 79 73 27 0a 09 09 09 67 72 6f 75 '7 days'....grou
0a10: 70 20 62 79 20 73 6f 75 72 63 65 5f 69 64 29 20 p by source_id)
0a20: 61 73 20 61 63 74 0a 09 09 77 68 65 72 65 20 65 as act...where e
0a30: 6e 61 62 6c 65 64 0a 09 09 6f 72 64 65 72 20 62 nabled...order b
0a40: 79 20 6e 65 78 74 5f 66 65 74 63 68 3b 0a y next_fetch;.