Artifact
25e0fc0a65bff6fcd8172b92d739d0f37f727387f1c384aa6ea4a7a99a5ce16b:
0000: 43 52 45 41 54 45 20 50 52 4f 43 45 44 55 52 41 CREATE PROCEDURA
0010: 4c 20 4c 41 4e 47 55 41 47 45 20 70 6c 70 67 73 L LANGUAGE plpgs
0020: 71 6c 3b 0a 0a 2d 2d 20 74 68 69 73 20 66 75 6e ql;..-- this fun
0030: 63 74 69 6f 6e 20 61 64 64 73 20 74 61 67 20 74 ction adds tag t
0040: 6f 20 64 6f 6d 61 69 6e 0a 43 52 45 41 54 45 20 o domain.CREATE
0050: 46 55 4e 43 54 49 4f 4e 20 6d 61 72 6b 28 64 6f FUNCTION mark(do
0060: 6d 61 69 6e 20 74 65 78 74 2c 20 6e 65 77 5f 74 main text, new_t
0070: 61 67 20 74 65 78 74 29 20 52 45 54 55 52 4e 53 ag text) RETURNS
0080: 20 76 6f 69 64 0a 09 4c 41 4e 47 55 41 47 45 20 void..LANGUAGE
0090: 70 6c 70 67 73 71 6c 20 53 54 52 49 43 54 0a 09 plpgsql STRICT..
00a0: 41 53 20 24 24 0a 64 65 63 6c 61 72 65 0a 09 6d AS $$.declare..m
00b0: 79 5f 73 69 74 65 20 74 65 78 74 5b 5d 3b 0a 09 y_site text[];..
00c0: 6d 79 5f 73 69 74 65 5f 69 64 20 73 6d 61 6c 6c my_site_id small
00d0: 69 6e 74 3b 0a 09 6d 79 5f 74 61 67 20 74 65 78 int;..my_tag tex
00e0: 74 5b 5d 3b 0a 09 6d 79 5f 74 61 67 5f 69 64 20 t[];..my_tag_id
00f0: 73 6d 61 6c 6c 69 6e 74 3b 0a 62 65 67 69 6e 0a smallint;.begin.
0100: 09 6d 79 5f 73 69 74 65 20 3a 3d 20 74 72 69 70 .my_site := trip
0110: 64 6f 6d 61 69 6e 28 64 6f 6d 61 69 6e 29 3b 0a domain(domain);.
0120: 0a 09 2d 2d 20 73 65 6c 65 63 74 69 6e 67 20 73 ..-- selecting s
0130: 69 74 65 20 69 64 20 66 72 6f 6d 20 74 61 62 6c ite id from tabl
0140: 65 20 6f 72 20 61 64 64 69 6e 67 20 73 69 74 65 e or adding site
0150: 20 74 6f 20 74 68 65 20 74 61 62 6c 65 0a 09 73 to the table..s
0160: 65 6c 65 63 74 20 69 64 5f 73 69 74 65 20 66 72 elect id_site fr
0170: 6f 6d 20 73 69 74 65 20 77 68 65 72 65 20 6d 79 om site where my
0180: 5f 73 69 74 65 20 3d 20 73 69 74 65 20 69 6e 74 _site = site int
0190: 6f 20 6d 79 5f 73 69 74 65 5f 69 64 3b 0a 09 69 o my_site_id;..i
01a0: 66 20 6e 6f 74 20 66 6f 75 6e 64 20 74 68 65 6e f not found then
01b0: 0a 09 09 69 6e 73 65 72 74 20 69 6e 74 6f 20 73 ...insert into s
01c0: 69 74 65 20 28 73 69 74 65 29 20 76 61 6c 75 65 ite (site) value
01d0: 73 20 28 6d 79 5f 73 69 74 65 29 3b 0a 09 09 73 s (my_site);...s
01e0: 65 6c 65 63 74 20 69 64 5f 73 69 74 65 20 66 72 elect id_site fr
01f0: 6f 6d 20 73 69 74 65 20 77 68 65 72 65 20 6d 79 om site where my
0200: 5f 73 69 74 65 20 3d 20 73 69 74 65 20 69 6e 74 _site = site int
0210: 6f 20 6d 79 5f 73 69 74 65 5f 69 64 3b 0a 09 65 o my_site_id;..e
0220: 6e 64 20 69 66 3b 0a 0a 09 2d 2d 20 73 65 6c 65 nd if;...-- sele
0230: 63 74 69 6e 67 20 74 61 67 73 20 73 69 74 65 20 cting tags site
0240: 61 6c 72 65 61 64 79 20 68 61 76 65 20 61 6e 64 already have and
0250: 20 61 64 64 69 6e 67 20 6e 65 77 20 74 61 67 20 adding new tag
0260: 74 6f 20 74 68 65 6d 0a 09 2d 2d 20 6e 6f 74 65 to them..-- note
0270: 20 74 68 61 74 20 74 61 67 73 20 73 68 6f 75 6c that tags shoul
0280: 64 20 62 65 20 73 6f 72 74 65 64 20 74 6f 20 65 d be sorted to e
0290: 6c 69 6d 69 6e 61 74 65 20 70 65 72 6d 75 74 61 liminate permuta
02a0: 74 69 6f 6e 73 0a 09 73 65 6c 65 63 74 20 74 61 tions..select ta
02b0: 67 20 66 72 6f 6d 20 75 72 6c 73 20 6e 61 74 75 g from urls natu
02c0: 72 61 6c 20 6a 6f 69 6e 20 74 61 67 20 77 68 65 ral join tag whe
02d0: 72 65 20 69 64 5f 73 69 74 65 20 3d 20 6d 79 5f re id_site = my_
02e0: 73 69 74 65 5f 69 64 20 69 6e 74 6f 20 6d 79 5f site_id into my_
02f0: 74 61 67 3b 0a 09 69 66 20 6e 6f 74 20 66 6f 75 tag;..if not fou
0300: 6e 64 20 74 68 65 6e 0a 09 09 2d 2d 20 6e 6f 20 nd then...-- no
0310: 72 65 63 6f 72 64 73 20 66 6f 75 6e 64 20 2d 20 records found -
0320: 63 72 65 61 74 69 6e 67 20 6e 65 77 20 74 61 67 creating new tag
0330: 0a 09 09 6d 79 5f 74 61 67 20 3a 3d 20 61 72 72 ...my_tag := arr
0340: 61 79 5b 6e 65 77 5f 74 61 67 5d 3b 0a 09 65 6c ay[new_tag];..el
0350: 73 65 0a 09 09 2d 2d 20 6a 6f 69 6e 69 6e 67 20 se...-- joining
0360: 74 61 67 73 0a 09 09 73 65 6c 65 63 74 20 61 72 tags...select ar
0370: 72 61 79 5f 61 67 67 28 74 61 67 29 0a 09 09 09 ray_agg(tag)....
0380: 66 72 6f 6d 20 28 73 65 6c 65 63 74 20 64 69 73 from (select dis
0390: 74 69 6e 63 74 20 75 6e 6e 65 73 74 28 6d 79 5f tinct unnest(my_
03a0: 74 61 67 20 7c 7c 20 61 72 72 61 79 5b 6e 65 77 tag || array[new
03b0: 5f 74 61 67 5d 29 20 61 73 20 74 61 67 20 6f 72 _tag]) as tag or
03c0: 64 65 72 20 62 79 20 74 61 67 20 61 73 63 29 20 der by tag asc)
03d0: 61 0a 09 09 09 69 6e 74 6f 20 6d 79 5f 74 61 67 a....into my_tag
03e0: 3b 0a 09 09 2d 2d 20 64 65 6c 65 74 69 6e 67 20 ;...-- deleting
03f0: 6f 6c 64 20 73 69 74 65 20 73 70 65 63 69 66 69 old site specifi
0400: 63 61 74 69 6f 6e 0a 09 09 64 65 6c 65 74 65 20 cation...delete
0410: 66 72 6f 6d 20 75 72 6c 73 20 77 68 65 72 65 20 from urls where
0420: 69 64 5f 73 69 74 65 20 3d 20 6d 79 5f 73 69 74 id_site = my_sit
0430: 65 5f 69 64 3b 0a 09 65 6e 64 20 69 66 3b 0a 0a e_id;..end if;..
0440: 09 2d 2d 20 73 65 6c 65 63 74 69 6e 67 20 6e 65 .-- selecting ne
0450: 77 20 74 61 67 20 69 64 20 6f 72 20 61 64 64 69 w tag id or addi
0460: 6e 67 20 74 61 67 20 74 6f 20 74 68 65 20 74 61 ng tag to the ta
0470: 62 6c 65 0a 09 73 65 6c 65 63 74 20 69 64 5f 74 ble..select id_t
0480: 61 67 20 66 72 6f 6d 20 74 61 67 20 77 68 65 72 ag from tag wher
0490: 65 20 6d 79 5f 74 61 67 20 3d 20 74 61 67 20 69 e my_tag = tag i
04a0: 6e 74 6f 20 6d 79 5f 74 61 67 5f 69 64 3b 0a 09 nto my_tag_id;..
04b0: 69 66 20 6e 6f 74 20 66 6f 75 6e 64 20 74 68 65 if not found the
04c0: 6e 0a 09 09 69 6e 73 65 72 74 20 69 6e 74 6f 20 n...insert into
04d0: 74 61 67 20 28 74 61 67 29 20 76 61 6c 75 65 73 tag (tag) values
04e0: 28 6d 79 5f 74 61 67 29 3b 0a 09 09 73 65 6c 65 (my_tag);...sele
04f0: 63 74 20 69 64 5f 74 61 67 20 66 72 6f 6d 20 74 ct id_tag from t
0500: 61 67 20 77 68 65 72 65 20 6d 79 5f 74 61 67 20 ag where my_tag
0510: 3d 20 74 61 67 20 69 6e 74 6f 20 6d 79 5f 74 61 = tag into my_ta
0520: 67 5f 69 64 3b 0a 09 65 6e 64 20 69 66 3b 0a 0a g_id;..end if;..
0530: 09 2d 2d 20 61 64 64 69 6e 67 20 6e 65 77 20 73 .-- adding new s
0540: 69 74 65 20 73 70 65 63 69 66 69 63 61 74 69 6f ite specificatio
0550: 6e 0a 09 69 6e 73 65 72 74 20 69 6e 74 6f 20 75 n..insert into u
0560: 72 6c 73 20 28 69 64 5f 73 69 74 65 2c 20 69 64 rls (id_site, id
0570: 5f 74 61 67 29 20 76 61 6c 75 65 73 20 28 6d 79 _tag) values (my
0580: 5f 73 69 74 65 5f 69 64 2c 20 6d 79 5f 74 61 67 _site_id, my_tag
0590: 5f 69 64 29 3b 0a 65 6e 64 3b 24 24 3b 0a 0a 2d _id);.end;$$;..-
05a0: 2d 20 74 68 69 73 20 66 75 6e 63 74 69 6f 6e 20 - this function
05b0: 61 64 64 73 20 74 61 67 20 74 6f 20 73 69 74 65 adds tag to site
05c0: 20 62 79 20 73 69 74 65 20 69 64 0a 43 52 45 41 by site id.CREA
05d0: 54 45 20 46 55 4e 43 54 49 4f 4e 20 6d 61 72 6b TE FUNCTION mark
05e0: 28 6d 79 5f 73 69 74 65 5f 69 64 20 73 6d 61 6c (my_site_id smal
05f0: 6c 69 6e 74 2c 20 6e 65 77 5f 74 61 67 20 74 65 lint, new_tag te
0600: 78 74 29 20 52 45 54 55 52 4e 53 20 76 6f 69 64 xt) RETURNS void
0610: 0a 09 4c 41 4e 47 55 41 47 45 20 70 6c 70 67 73 ..LANGUAGE plpgs
0620: 71 6c 20 53 54 52 49 43 54 0a 09 41 53 20 24 24 ql STRICT..AS $$
0630: 0a 64 65 63 6c 61 72 65 0a 09 2d 2d 20 6d 61 79 .declare..-- may
0640: 62 65 20 63 68 65 63 6b 20 73 68 6f 75 6c 64 20 be check should
0650: 62 65 20 61 64 64 65 64 20 74 6f 20 6d 61 6b 65 be added to make
0660: 20 73 75 72 65 20 73 75 70 70 6c 69 65 64 20 73 sure supplied s
0670: 69 74 65 20 69 64 20 72 65 61 6c 6c 79 20 65 78 ite id really ex
0680: 69 73 74 73 0a 09 6d 79 5f 74 61 67 20 74 65 78 ists..my_tag tex
0690: 74 5b 5d 3b 0a 09 6d 79 5f 74 61 67 5f 69 64 20 t[];..my_tag_id
06a0: 73 6d 61 6c 6c 69 6e 74 3b 0a 62 65 67 69 6e 0a smallint;.begin.
06b0: 09 2d 2d 20 73 65 6c 65 63 74 69 6e 67 20 74 61 .-- selecting ta
06c0: 67 73 20 73 69 74 65 20 61 6c 72 65 61 64 79 20 gs site already
06d0: 68 61 76 65 20 61 6e 64 20 61 64 64 69 6e 67 20 have and adding
06e0: 6e 65 77 20 74 61 67 20 74 6f 20 74 68 65 6d 0a new tag to them.
06f0: 09 2d 2d 20 6e 6f 74 65 20 74 68 61 74 20 74 61 .-- note that ta
0700: 67 73 20 73 68 6f 75 6c 64 20 62 65 20 73 6f 72 gs should be sor
0710: 74 65 64 20 74 6f 20 65 6c 69 6d 69 6e 61 74 65 ted to eliminate
0720: 20 70 65 72 6d 75 74 61 74 69 6f 6e 73 0a 09 73 permutations..s
0730: 65 6c 65 63 74 20 74 61 67 20 66 72 6f 6d 20 75 elect tag from u
0740: 72 6c 73 20 6e 61 74 75 72 61 6c 20 6a 6f 69 6e rls natural join
0750: 20 74 61 67 20 77 68 65 72 65 20 69 64 5f 73 69 tag where id_si
0760: 74 65 20 3d 20 6d 79 5f 73 69 74 65 5f 69 64 20 te = my_site_id
0770: 69 6e 74 6f 20 6d 79 5f 74 61 67 3b 0a 09 69 66 into my_tag;..if
0780: 20 6e 6f 74 20 66 6f 75 6e 64 20 74 68 65 6e 0a not found then.
0790: 09 09 2d 2d 20 6e 6f 20 72 65 63 6f 72 64 73 20 ..-- no records
07a0: 66 6f 75 6e 64 20 2d 20 63 72 65 61 74 69 6e 67 found - creating
07b0: 20 6e 65 77 20 74 61 67 0a 09 09 6d 79 5f 74 61 new tag...my_ta
07c0: 67 20 3a 3d 20 61 72 72 61 79 5b 6e 65 77 5f 74 g := array[new_t
07d0: 61 67 5d 3b 0a 09 65 6c 73 65 0a 09 09 2d 2d 20 ag];..else...--
07e0: 6a 6f 69 6e 69 6e 67 20 74 61 67 73 0a 09 09 73 joining tags...s
07f0: 65 6c 65 63 74 20 61 72 72 61 79 5f 61 67 67 28 elect array_agg(
0800: 74 61 67 29 0a 09 09 09 66 72 6f 6d 20 28 73 65 tag)....from (se
0810: 6c 65 63 74 20 64 69 73 74 69 6e 63 74 20 75 6e lect distinct un
0820: 6e 65 73 74 28 6d 79 5f 74 61 67 20 7c 7c 20 61 nest(my_tag || a
0830: 72 72 61 79 5b 6e 65 77 5f 74 61 67 5d 29 20 61 rray[new_tag]) a
0840: 73 20 74 61 67 20 6f 72 64 65 72 20 62 79 20 74 s tag order by t
0850: 61 67 20 61 73 63 29 20 61 0a 09 09 09 69 6e 74 ag asc) a....int
0860: 6f 20 6d 79 5f 74 61 67 3b 0a 09 09 2d 2d 20 64 o my_tag;...-- d
0870: 65 6c 65 74 69 6e 67 20 6f 6c 64 20 73 69 74 65 eleting old site
0880: 20 73 70 65 63 69 66 69 63 61 74 69 6f 6e 0a 09 specification..
0890: 09 64 65 6c 65 74 65 20 66 72 6f 6d 20 75 72 6c .delete from url
08a0: 73 20 77 68 65 72 65 20 69 64 5f 73 69 74 65 20 s where id_site
08b0: 3d 20 6d 79 5f 73 69 74 65 5f 69 64 3b 0a 09 65 = my_site_id;..e
08c0: 6e 64 20 69 66 3b 0a 0a 09 2d 2d 20 73 65 6c 65 nd if;...-- sele
08d0: 63 74 69 6e 67 20 6e 65 77 20 74 61 67 20 69 64 cting new tag id
08e0: 20 6f 72 20 61 64 64 69 6e 67 20 74 61 67 20 74 or adding tag t
08f0: 6f 20 74 68 65 20 74 61 62 6c 65 0a 09 73 65 6c o the table..sel
0900: 65 63 74 20 69 64 5f 74 61 67 20 66 72 6f 6d 20 ect id_tag from
0910: 74 61 67 20 77 68 65 72 65 20 6d 79 5f 74 61 67 tag where my_tag
0920: 20 3d 20 74 61 67 20 69 6e 74 6f 20 6d 79 5f 74 = tag into my_t
0930: 61 67 5f 69 64 3b 0a 09 69 66 20 6e 6f 74 20 66 ag_id;..if not f
0940: 6f 75 6e 64 20 74 68 65 6e 0a 09 09 69 6e 73 65 ound then...inse
0950: 72 74 20 69 6e 74 6f 20 74 61 67 20 28 74 61 67 rt into tag (tag
0960: 29 20 76 61 6c 75 65 73 28 6d 79 5f 74 61 67 29 ) values(my_tag)
0970: 3b 0a 09 09 73 65 6c 65 63 74 20 69 64 5f 74 61 ;...select id_ta
0980: 67 20 66 72 6f 6d 20 74 61 67 20 77 68 65 72 65 g from tag where
0990: 20 6d 79 5f 74 61 67 20 3d 20 74 61 67 20 69 6e my_tag = tag in
09a0: 74 6f 20 6d 79 5f 74 61 67 5f 69 64 3b 0a 09 65 to my_tag_id;..e
09b0: 6e 64 20 69 66 3b 0a 0a 09 2d 2d 20 61 64 64 69 nd if;...-- addi
09c0: 6e 67 20 6e 65 77 20 73 69 74 65 20 73 70 65 63 ng new site spec
09d0: 69 66 69 63 61 74 69 6f 6e 0a 09 69 6e 73 65 72 ification..inser
09e0: 74 20 69 6e 74 6f 20 75 72 6c 73 20 28 69 64 5f t into urls (id_
09f0: 73 69 74 65 2c 20 69 64 5f 74 61 67 29 20 76 61 site, id_tag) va
0a00: 6c 75 65 73 20 28 6d 79 5f 73 69 74 65 5f 69 64 lues (my_site_id
0a10: 2c 20 6d 79 5f 74 61 67 5f 69 64 29 3b 0a 65 6e , my_tag_id);.en
0a20: 64 3b 24 24 3b 0a 0a 2d 2d 20 74 72 61 6e 73 66 d;$$;..-- transf
0a30: 6f 72 6d 73 20 64 6f 6d 61 69 6e 20 69 6e 74 6f orms domain into
0a40: 20 6f 72 64 65 72 65 64 20 61 72 72 61 79 20 66 ordered array f
0a50: 6f 72 20 69 6e 64 65 78 69 6e 67 0a 43 52 45 41 or indexing.CREA
0a60: 54 45 20 46 55 4e 43 54 49 4f 4e 20 74 72 69 70 TE FUNCTION trip
0a70: 64 6f 6d 61 69 6e 28 75 72 6c 20 74 65 78 74 29 domain(url text)
0a80: 20 52 45 54 55 52 4e 53 20 74 65 78 74 5b 5d 0a RETURNS text[].
0a90: 09 4c 41 4e 47 55 41 47 45 20 70 6c 70 67 73 71 .LANGUAGE plpgsq
0aa0: 6c 20 49 4d 4d 55 54 41 42 4c 45 20 53 54 52 49 l IMMUTABLE STRI
0ab0: 43 54 0a 09 41 53 20 24 5f 24 0a 64 65 63 6c 61 CT..AS $_$.decla
0ac0: 72 65 0a 09 72 65 73 75 6c 74 20 74 65 78 74 5b re..result text[
0ad0: 5d 3b 0a 09 73 70 6c 69 74 74 65 64 20 74 65 78 ];..splitted tex
0ae0: 74 5b 5d 3b 0a 09 78 20 69 6e 74 65 67 65 72 3b t[];..x integer;
0af0: 0a 09 6c 65 6e 67 74 68 20 69 6e 74 65 67 65 72 ..length integer
0b00: 3b 0a 62 65 67 69 6e 0a 09 73 70 6c 69 74 74 65 ;.begin..splitte
0b10: 64 20 3a 3d 20 73 74 72 69 6e 67 5f 74 6f 5f 61 d := string_to_a
0b20: 72 72 61 79 28 24 31 2c 20 27 2e 27 29 3b 0a 09 rray($1, '.');..
0b30: 6c 65 6e 67 74 68 20 3a 3d 20 61 72 72 61 79 5f length := array_
0b40: 6c 65 6e 67 74 68 28 73 70 6c 69 74 74 65 64 2c length(splitted,
0b50: 20 31 29 3b 0a 09 78 20 3a 3d 20 31 3b 0a 09 6c 1);..x := 1;..l
0b60: 6f 6f 70 0a 09 09 65 78 69 74 20 77 68 65 6e 20 oop...exit when
0b70: 73 70 6c 69 74 74 65 64 5b 78 5d 20 69 73 20 6e splitted[x] is n
0b80: 75 6c 6c 3b 0a 09 09 72 65 73 75 6c 74 5b 78 5d ull;...result[x]
0b90: 20 3a 3d 20 73 70 6c 69 74 74 65 64 5b 78 5d 20 := splitted[x]
0ba0: 7c 7c 20 27 3a 27 20 7c 7c 20 6c 65 6e 67 74 68 || ':' || length
0bb0: 20 2d 20 78 3b 0a 09 09 78 20 3a 3d 20 78 20 2b - x;...x := x +
0bc0: 20 31 3b 0a 09 65 6e 64 20 6c 6f 6f 70 3b 0a 09 1;..end loop;..
0bd0: 72 65 74 75 72 6e 20 72 65 73 75 6c 74 3b 0a 65 return result;.e
0be0: 6e 64 3b 24 5f 24 3b 0a 0a 2d 2d 20 74 72 61 6e nd;$_$;..-- tran
0bf0: 73 66 6f 72 6d 73 20 6f 72 64 65 72 65 64 20 61 sforms ordered a
0c00: 72 72 61 79 20 69 6e 74 6f 20 64 6f 6d 61 69 6e rray into domain
0c10: 0a 63 72 65 61 74 65 20 66 75 6e 63 74 69 6f 6e .create function
0c20: 20 75 6e 74 72 69 70 28 73 69 74 65 20 74 65 78 untrip(site tex
0c30: 74 5b 5d 29 20 72 65 74 75 72 6e 73 20 74 65 78 t[]) returns tex
0c40: 74 0a 09 6c 61 6e 67 75 61 67 65 20 70 6c 70 67 t..language plpg
0c50: 73 71 6c 20 69 6d 6d 75 74 61 62 6c 65 20 73 74 sql immutable st
0c60: 72 69 63 74 0a 09 61 73 20 24 5f 24 0a 64 65 63 rict..as $_$.dec
0c70: 6c 61 72 65 0a 09 78 20 69 6e 74 65 67 65 72 3b lare..x integer;
0c80: 0a 09 73 70 6c 69 74 74 65 64 20 74 65 78 74 5b ..splitted text[
0c90: 5d 3b 0a 09 70 61 69 72 20 74 65 78 74 5b 5d 3b ];..pair text[];
0ca0: 0a 62 65 67 69 6e 0a 09 78 20 3a 3d 20 61 72 72 .begin..x := arr
0cb0: 61 79 5f 6c 65 6e 67 74 68 28 73 69 74 65 2c 20 ay_length(site,
0cc0: 31 29 3b 0a 09 6c 6f 6f 70 20 0a 09 09 65 78 69 1);..loop ...exi
0cd0: 74 20 77 68 65 6e 20 73 69 74 65 5b 78 5d 20 69 t when site[x] i
0ce0: 73 20 6e 75 6c 6c 3b 0a 09 09 70 61 69 72 20 3a s null;...pair :
0cf0: 3d 20 73 74 72 69 6e 67 5f 74 6f 5f 61 72 72 61 = string_to_arra
0d00: 79 28 73 69 74 65 5b 78 5d 2c 20 27 3a 27 29 3b y(site[x], ':');
0d10: 0a 09 09 73 70 6c 69 74 74 65 64 5b 30 20 2d 20 ...splitted[0 -
0d20: 70 61 69 72 5b 32 5d 3a 3a 69 6e 74 65 67 65 72 pair[2]::integer
0d30: 5d 20 3a 3d 20 70 61 69 72 5b 31 5d 3b 0a 09 09 ] := pair[1];...
0d40: 78 20 3a 3d 20 78 20 2d 20 31 3b 0a 09 65 6e 64 x := x - 1;..end
0d50: 20 6c 6f 6f 70 3b 0a 09 72 65 74 75 72 6e 20 61 loop;..return a
0d60: 72 72 61 79 5f 74 6f 5f 73 74 72 69 6e 67 28 73 rray_to_string(s
0d70: 70 6c 69 74 74 65 64 2c 20 27 2e 27 29 3b 0a 65 plitted, '.');.e
0d80: 6e 64 3b 0a 24 5f 24 3b 0a 0a 2d 2d 20 74 61 62 nd;.$_$;..-- tab
0d90: 6c 65 20 74 6f 20 68 6f 6c 64 20 61 6c 6c 20 72 le to hold all r
0da0: 75 6c 65 73 0a 43 52 45 41 54 45 20 54 41 42 4c ules.CREATE TABL
0db0: 45 20 72 75 6c 65 73 20 28 0a 09 6e 65 74 6d 61 E rules (..netma
0dc0: 73 6b 20 63 69 64 72 20 4e 4f 54 20 4e 55 4c 4c sk cidr NOT NULL
0dd0: 2c 0a 09 72 65 64 69 72 65 63 74 5f 75 72 6c 20 ,..redirect_url
0de0: 74 65 78 74 20 44 45 46 41 55 4c 54 20 27 61 62 text DEFAULT 'ab
0df0: 6f 75 74 3a 3a 62 6c 61 6e 6b 27 3a 3a 74 65 78 out::blank'::tex
0e00: 74 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 66 72 6f t NOT NULL,..fro
0e10: 6d 5f 77 65 65 6b 64 61 79 20 73 6d 61 6c 6c 69 m_weekday smalli
0e20: 6e 74 20 44 45 46 41 55 4c 54 20 30 20 4e 4f 54 nt DEFAULT 0 NOT
0e30: 20 4e 55 4c 4c 2c 0a 09 74 6f 5f 77 65 65 6b 64 NULL,..to_weekd
0e40: 61 79 20 73 6d 61 6c 6c 69 6e 74 20 44 45 46 41 ay smallint DEFA
0e50: 55 4c 54 20 36 20 4e 4f 54 20 4e 55 4c 4c 2c 0a ULT 6 NOT NULL,.
0e60: 09 66 72 6f 6d 5f 74 69 6d 65 20 74 69 6d 65 20 .from_time time
0e70: 77 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e without time zon
0e80: 65 20 44 45 46 41 55 4c 54 20 27 30 30 3a 30 30 e DEFAULT '00:00
0e90: 3a 30 30 27 3a 3a 74 69 6d 65 20 77 69 74 68 6f :00'::time witho
0ea0: 75 74 20 74 69 6d 65 20 7a 6f 6e 65 20 4e 4f 54 ut time zone NOT
0eb0: 20 4e 55 4c 4c 2c 0a 09 74 6f 5f 74 69 6d 65 20 NULL,..to_time
0ec0: 74 69 6d 65 20 77 69 74 68 6f 75 74 20 74 69 6d time without tim
0ed0: 65 20 7a 6f 6e 65 20 44 45 46 41 55 4c 54 20 27 e zone DEFAULT '
0ee0: 32 33 3a 35 39 3a 35 39 27 3a 3a 74 69 6d 65 20 23:59:59'::time
0ef0: 77 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e without time zon
0f00: 65 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 69 64 5f e NOT NULL,..id_
0f10: 74 61 67 20 73 6d 61 6c 6c 69 6e 74 20 4e 4f 54 tag smallint NOT
0f20: 20 4e 55 4c 4c 0a 29 3b 0a 0a 41 4c 54 45 52 20 NULL.);..ALTER
0f30: 54 41 42 4c 45 20 4f 4e 4c 59 20 72 75 6c 65 73 TABLE ONLY rules
0f40: 0a 09 41 44 44 20 43 4f 4e 53 54 52 41 49 4e 54 ..ADD CONSTRAINT
0f50: 20 72 75 6c 65 73 5f 70 6b 65 79 20 50 52 49 4d rules_pkey PRIM
0f60: 41 52 59 20 4b 45 59 20 28 6e 65 74 6d 61 73 6b ARY KEY (netmask
0f70: 29 3b 0a 0a 2d 2d 20 74 61 62 6c 65 20 74 6f 20 );..-- table to
0f80: 68 6f 6c 64 20 73 69 74 65 20 61 72 72 61 79 73 hold site arrays
0f90: 0a 2d 2d 20 6d 61 79 62 65 20 66 75 6c 6c 20 6f .-- maybe full o
0fa0: 72 69 67 69 6e 61 6c 20 64 6f 6d 61 69 6e 20 73 riginal domain s
0fb0: 68 6f 75 6c 64 20 62 65 20 61 64 64 65 64 20 77 hould be added w
0fc0: 69 74 68 20 74 72 69 67 67 65 72 20 74 6f 20 6c ith trigger to l
0fd0: 65 61 76 65 20 74 72 69 70 64 6f 6d 61 69 6e 20 eave tripdomain
0fe0: 66 75 6e 63 74 69 6f 6e 20 61 77 61 79 0a 43 52 function away.CR
0ff0: 45 41 54 45 20 54 41 42 4c 45 20 73 69 74 65 20 EATE TABLE site
1000: 28 0a 09 69 64 5f 73 69 74 65 20 73 65 72 69 61 (..id_site seria
1010: 6c 2c 0a 09 73 69 74 65 20 74 65 78 74 5b 5d 20 l,..site text[]
1020: 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 64 6f 6d 61 69 NOT NULL,..domai
1030: 6e 20 6e 6f 74 20 6e 75 6c 6c 0a 29 3b 0a 0a 41 n not null.);..A
1040: 4c 54 45 52 20 54 41 42 4c 45 20 4f 4e 4c 59 20 LTER TABLE ONLY
1050: 73 69 74 65 0a 09 41 44 44 20 43 4f 4e 53 54 52 site..ADD CONSTR
1060: 41 49 4e 54 20 73 69 74 65 5f 69 64 20 50 52 49 AINT site_id PRI
1070: 4d 41 52 59 20 4b 45 59 20 28 69 64 5f 73 69 74 MARY KEY (id_sit
1080: 65 29 3b 0a 0a 43 52 45 41 54 45 20 55 4e 49 51 e);..CREATE UNIQ
1090: 55 45 20 49 4e 44 45 58 20 73 69 74 65 5f 73 20 UE INDEX site_s
10a0: 4f 4e 20 73 69 74 65 20 55 53 49 4e 47 20 62 74 ON site USING bt
10b0: 72 65 65 20 28 73 69 74 65 29 3b 0a 0a 43 52 45 ree (site);..CRE
10c0: 41 54 45 20 49 4e 44 45 58 20 73 69 74 65 5f 73 ATE INDEX site_s
10d0: 67 20 4f 4e 20 73 69 74 65 20 55 53 49 4e 47 20 g ON site USING
10e0: 67 69 6e 20 28 73 69 74 65 29 3b 0a 0a 2d 2d 20 gin (site);..--
10f0: 74 61 62 6c 65 20 74 6f 20 68 6f 6c 64 20 74 61 table to hold ta
1100: 67 20 63 6f 6d 62 69 6e 61 74 69 6f 6e 73 0a 43 g combinations.C
1110: 52 45 41 54 45 20 54 41 42 4c 45 20 74 61 67 20 REATE TABLE tag
1120: 28 0a 09 69 64 5f 74 61 67 20 73 65 72 69 61 6c (..id_tag serial
1130: 2c 0a 09 74 61 67 20 74 65 78 74 5b 5d 20 4e 4f ,..tag text[] NO
1140: 54 20 4e 55 4c 4c 0a 29 3b 0a 0a 41 4c 54 45 52 T NULL.);..ALTER
1150: 20 54 41 42 4c 45 20 4f 4e 4c 59 20 74 61 67 0a TABLE ONLY tag.
1160: 09 41 44 44 20 43 4f 4e 53 54 52 41 49 4e 54 20 .ADD CONSTRAINT
1170: 74 61 67 5f 69 64 20 50 52 49 4d 41 52 59 20 4b tag_id PRIMARY K
1180: 45 59 20 28 69 64 5f 74 61 67 29 3b 0a 0a 43 52 EY (id_tag);..CR
1190: 45 41 54 45 20 49 4e 44 45 58 20 74 61 67 5f 67 EATE INDEX tag_g
11a0: 20 4f 4e 20 74 61 67 20 55 53 49 4e 47 20 67 69 ON tag USING gi
11b0: 6e 20 28 74 61 67 29 3b 0a 0a 43 52 45 41 54 45 n (tag);..CREATE
11c0: 20 55 4e 49 51 55 45 20 49 4e 44 45 58 20 74 61 UNIQUE INDEX ta
11d0: 67 5f 73 20 4f 4e 20 74 61 67 20 55 53 49 4e 47 g_s ON tag USING
11e0: 20 62 74 72 65 65 20 28 74 61 67 29 3b 0a 0a 2d btree (tag);..-
11f0: 2d 20 74 61 62 6c 65 20 74 6f 20 68 6f 6c 64 20 - table to hold
1200: 74 61 67 20 2d 20 73 69 74 65 20 6c 69 6e 6b 73 tag - site links
1210: 0a 43 52 45 41 54 45 20 54 41 42 4c 45 20 75 72 .CREATE TABLE ur
1220: 6c 73 20 28 0a 09 64 61 74 65 5f 61 64 64 65 64 ls (..date_added
1230: 20 74 69 6d 65 73 74 61 6d 70 20 77 69 74 68 6f timestamp witho
1240: 75 74 20 74 69 6d 65 20 7a 6f 6e 65 20 44 45 46 ut time zone DEF
1250: 41 55 4c 54 20 28 27 6e 6f 77 27 3a 3a 74 65 78 AULT ('now'::tex
1260: 74 29 3a 3a 74 69 6d 65 73 74 61 6d 70 28 30 29 t)::timestamp(0)
1270: 20 77 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f without time zo
1280: 6e 65 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 69 64 ne NOT NULL,..id
1290: 5f 73 69 74 65 20 73 6d 61 6c 6c 69 6e 74 20 4e _site smallint N
12a0: 4f 54 20 4e 55 4c 4c 2c 0a 09 69 64 5f 74 61 67 OT NULL,..id_tag
12b0: 20 73 6d 61 6c 6c 69 6e 74 20 4e 4f 54 20 4e 55 smallint NOT NU
12c0: 4c 4c 0a 29 3b 0a 0a 41 4c 54 45 52 20 54 41 42 LL.);..ALTER TAB
12d0: 4c 45 20 4f 4e 4c 59 20 75 72 6c 73 0a 09 41 44 LE ONLY urls..AD
12e0: 44 20 43 4f 4e 53 54 52 41 49 4e 54 20 75 72 6c D CONSTRAINT url
12f0: 73 5f 70 6b 65 79 20 50 52 49 4d 41 52 59 20 4b s_pkey PRIMARY K
1300: 45 59 20 28 64 61 74 65 5f 61 64 64 65 64 29 3b EY (date_added);
1310: 0a 0a 43 52 45 41 54 45 20 55 4e 49 51 55 45 20 ..CREATE UNIQUE
1320: 49 4e 44 45 58 20 75 72 6c 73 5f 69 64 5f 73 69 INDEX urls_id_si
1330: 74 65 20 4f 4e 20 75 72 6c 73 20 55 53 49 4e 47 te ON urls USING
1340: 20 62 74 72 65 65 20 28 69 64 5f 73 69 74 65 29 btree (id_site)
1350: 3b 0a 0a 2d 2d 20 72 75 6c 65 20 74 6f 20 6a 6f ;..-- rule to jo
1360: 69 6e 20 61 6c 6c 20 74 61 62 6c 65 73 20 69 6e in all tables in
1370: 74 6f 20 6f 6e 65 20 74 6f 20 73 69 6d 70 6c 69 to one to simpli
1380: 66 79 20 61 63 63 65 73 73 0a 2d 2d 20 61 75 74 fy access.-- aut
1390: 6f 6d 61 74 69 63 61 6c 6c 20 75 73 65 73 20 63 omaticall uses c
13a0: 75 72 72 65 6e 74 20 64 61 79 20 61 6e 64 20 74 urrent day and t
13b0: 69 6d 65 20 64 61 74 61 0a 43 52 45 41 54 45 20 ime data.CREATE
13c0: 56 49 45 57 20 73 69 74 65 5f 72 75 6c 65 20 41 VIEW site_rule A
13d0: 53 0a 53 45 4c 45 43 54 20 61 2e 72 65 64 69 72 S.SELECT a.redir
13e0: 65 63 74 5f 75 72 6c 2c 20 61 2e 6e 65 74 6d 61 ect_url, a.netma
13f0: 73 6b 2c 20 62 2e 73 69 74 65 0a 46 52 4f 4d 20 sk, b.site.FROM
1400: 28 28 0a 09 53 45 4c 45 43 54 20 72 75 6c 65 73 ((..SELECT rules
1410: 2e 72 65 64 69 72 65 63 74 5f 75 72 6c 2c 20 74 .redirect_url, t
1420: 61 67 2e 74 61 67 20 41 53 20 72 75 6c 65 5f 74 ag.tag AS rule_t
1430: 61 67 2c 20 72 75 6c 65 73 2e 6e 65 74 6d 61 73 ag, rules.netmas
1440: 6b 0a 09 46 52 4f 4d 20 28 72 75 6c 65 73 20 4e k..FROM (rules N
1450: 41 54 55 52 41 4c 20 4a 4f 49 4e 20 74 61 67 29 ATURAL JOIN tag)
1460: 0a 09 57 48 45 52 45 20 28 28 28 28 28 27 6e 6f ..WHERE ((((('no
1470: 77 27 3a 3a 74 65 78 74 29 3a 3a 74 69 6d 65 20 w'::text)::time
1480: 77 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e without time zon
1490: 65 20 3e 3d 20 72 75 6c 65 73 2e 66 72 6f 6d 5f e >= rules.from_
14a0: 74 69 6d 65 29 0a 09 09 41 4e 44 20 28 28 27 6e time)...AND (('n
14b0: 6f 77 27 3a 3a 74 65 78 74 29 3a 3a 74 69 6d 65 ow'::text)::time
14c0: 20 77 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f without time zo
14d0: 6e 65 20 3c 3d 20 72 75 6c 65 73 2e 74 6f 5f 74 ne <= rules.to_t
14e0: 69 6d 65 29 29 0a 09 09 41 4e 44 20 28 64 61 74 ime))...AND (dat
14f0: 65 5f 70 61 72 74 28 27 64 6f 77 27 3a 3a 74 65 e_part('dow'::te
1500: 78 74 2c 20 6e 6f 77 28 29 29 20 3e 3d 20 28 72 xt, now()) >= (r
1510: 75 6c 65 73 2e 66 72 6f 6d 5f 77 65 65 6b 64 61 ules.from_weekda
1520: 79 29 3a 3a 64 6f 75 62 6c 65 20 70 72 65 63 69 y)::double preci
1530: 73 69 6f 6e 29 29 0a 09 09 41 4e 44 20 28 64 61 sion))...AND (da
1540: 74 65 5f 70 61 72 74 28 27 64 6f 77 27 3a 3a 74 te_part('dow'::t
1550: 65 78 74 2c 20 6e 6f 77 28 29 29 20 3c 3d 20 28 ext, now()) <= (
1560: 72 75 6c 65 73 2e 74 6f 5f 77 65 65 6b 64 61 79 rules.to_weekday
1570: 29 3a 3a 64 6f 75 62 6c 65 20 70 72 65 63 69 73 )::double precis
1580: 69 6f 6e 29 29 0a 29 20 61 20 4a 4f 49 4e 20 28 ion)).) a JOIN (
1590: 0a 09 53 45 4c 45 43 54 20 73 69 74 65 2e 73 69 ..SELECT site.si
15a0: 74 65 2c 20 74 61 67 2e 74 61 67 20 41 53 20 75 te, tag.tag AS u
15b0: 72 6c 5f 74 61 67 0a 09 46 52 4f 4d 20 28 28 75 rl_tag..FROM ((u
15c0: 72 6c 73 20 4e 41 54 55 52 41 4c 20 4a 4f 49 4e rls NATURAL JOIN
15d0: 20 74 61 67 29 20 4e 41 54 55 52 41 4c 20 4a 4f tag) NATURAL JO
15e0: 49 4e 20 73 69 74 65 29 0a 29 20 62 20 4f 4e 20 IN site).) b ON
15f0: 28 28 62 2e 75 72 6c 5f 74 61 67 20 26 26 20 61 ((b.url_tag && a
1600: 2e 72 75 6c 65 5f 74 61 67 29 29 29 3b 0a .rule_tag)));.