Artifact
c71bdf1f8ed886b643002e04ec78c26df2d23ead3b0dffa63a365e7986b5b376:
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 67 65 6e 65 72 61 6c 20 ql;..-- general
0030: 61 72 72 61 79 20 73 6f 72 74 69 6e 67 20 66 75 array sorting fu
0040: 6e 63 74 69 6f 6e 73 0a 2d 2d 20 73 6f 72 74 73 nctions.-- sorts
0050: 20 61 72 72 61 79 0a 43 52 45 41 54 45 20 46 55 array.CREATE FU
0060: 4e 43 54 49 4f 4e 20 73 6f 72 74 28 6f 72 69 67 NCTION sort(orig
0070: 69 6e 61 6c 20 61 6e 79 61 72 72 61 79 29 20 52 inal anyarray) R
0080: 45 54 55 52 4e 53 20 61 6e 79 61 72 72 61 79 0a ETURNS anyarray.
0090: 09 4c 41 4e 47 55 41 47 45 20 73 71 6c 20 49 4d .LANGUAGE sql IM
00a0: 4d 55 54 41 42 4c 45 20 53 54 52 49 43 54 0a 09 MUTABLE STRICT..
00b0: 41 53 20 24 5f 24 0a 73 65 6c 65 63 74 20 61 72 AS $_$.select ar
00c0: 72 61 79 5f 61 67 67 28 69 74 65 6d 29 20 61 73 ray_agg(item) as
00d0: 20 72 65 73 75 6c 74 20 66 72 6f 6d 20 28 73 65 result from (se
00e0: 6c 65 63 74 20 75 6e 6e 65 73 74 28 24 31 29 20 lect unnest($1)
00f0: 61 73 20 69 74 65 6d 20 6f 72 64 65 72 20 62 79 as item order by
0100: 20 69 74 65 6d 29 20 61 3b 0a 24 5f 24 3b 0a 0a item) a;.$_$;..
0110: 2d 2d 20 73 6f 72 74 73 20 61 72 72 61 79 20 61 -- sorts array a
0120: 6e 64 20 72 65 6d 6f 76 65 73 20 64 75 70 6c 69 nd removes dupli
0130: 63 61 74 65 73 0a 43 52 45 41 54 45 20 46 55 4e cates.CREATE FUN
0140: 43 54 49 4f 4e 20 75 73 6f 72 74 28 6f 72 69 67 CTION usort(orig
0150: 69 6e 61 6c 20 61 6e 79 61 72 72 61 79 29 20 52 inal anyarray) R
0160: 45 54 55 52 4e 53 20 61 6e 79 61 72 72 61 79 0a ETURNS anyarray.
0170: 09 4c 41 4e 47 55 41 47 45 20 73 71 6c 20 49 4d .LANGUAGE sql IM
0180: 4d 55 54 41 42 4c 45 20 53 54 52 49 43 54 0a 09 MUTABLE STRICT..
0190: 41 53 20 24 5f 24 0a 73 65 6c 65 63 74 20 61 72 AS $_$.select ar
01a0: 72 61 79 5f 61 67 67 28 69 74 65 6d 29 20 61 73 ray_agg(item) as
01b0: 20 72 65 73 75 6c 74 20 66 72 6f 6d 20 28 73 65 result from (se
01c0: 6c 65 63 74 20 64 69 73 74 69 6e 63 74 20 75 6e lect distinct un
01d0: 6e 65 73 74 28 24 31 29 20 61 73 20 69 74 65 6d nest($1) as item
01e0: 20 6f 72 64 65 72 20 62 79 20 69 74 65 6d 29 20 order by item)
01f0: 61 3b 0a 24 5f 24 3b 0a 0a 2d 2d 20 74 68 69 73 a;.$_$;..-- this
0200: 20 66 75 6e 63 74 69 6f 6e 20 61 64 64 73 20 74 function adds t
0210: 61 67 20 74 6f 20 64 6f 6d 61 69 6e 0a 43 52 45 ag to domain.CRE
0220: 41 54 45 20 46 55 4e 43 54 49 4f 4e 20 6d 61 72 ATE FUNCTION mar
0230: 6b 28 64 6f 6d 61 69 6e 20 74 65 78 74 2c 20 6e k(domain text, n
0240: 65 77 5f 74 61 67 20 74 65 78 74 29 20 52 45 54 ew_tag text) RET
0250: 55 52 4e 53 20 76 6f 69 64 0a 09 4c 41 4e 47 55 URNS void..LANGU
0260: 41 47 45 20 70 6c 70 67 73 71 6c 20 53 54 52 49 AGE plpgsql STRI
0270: 43 54 0a 09 41 53 20 24 24 0a 64 65 63 6c 61 72 CT..AS $$.declar
0280: 65 0a 09 6d 79 5f 73 69 74 65 20 74 65 78 74 5b e..my_site text[
0290: 5d 3b 0a 09 6d 79 5f 73 69 74 65 5f 69 64 20 73 ];..my_site_id s
02a0: 6d 61 6c 6c 69 6e 74 3b 0a 09 6d 79 5f 74 61 67 mallint;..my_tag
02b0: 20 74 65 78 74 5b 5d 3b 0a 09 6d 79 5f 74 61 67 text[];..my_tag
02c0: 5f 69 64 20 73 6d 61 6c 6c 69 6e 74 3b 0a 62 65 _id smallint;.be
02d0: 67 69 6e 0a 09 6d 79 5f 73 69 74 65 20 3a 3d 20 gin..my_site :=
02e0: 74 72 69 70 64 6f 6d 61 69 6e 28 64 6f 6d 61 69 tripdomain(domai
02f0: 6e 29 3b 0a 0a 09 2d 2d 20 73 65 6c 65 63 74 69 n);...-- selecti
0300: 6e 67 20 73 69 74 65 20 69 64 20 66 72 6f 6d 20 ng site id from
0310: 74 61 62 6c 65 20 6f 72 20 61 64 64 69 6e 67 20 table or adding
0320: 73 69 74 65 20 74 6f 20 74 68 65 20 74 61 62 6c site to the tabl
0330: 65 0a 09 73 65 6c 65 63 74 20 69 64 5f 73 69 74 e..select id_sit
0340: 65 20 66 72 6f 6d 20 73 69 74 65 20 77 68 65 72 e from site wher
0350: 65 20 6d 79 5f 73 69 74 65 20 3d 20 73 69 74 65 e my_site = site
0360: 20 69 6e 74 6f 20 6d 79 5f 73 69 74 65 5f 69 64 into my_site_id
0370: 3b 0a 09 69 66 20 6e 6f 74 20 66 6f 75 6e 64 20 ;..if not found
0380: 74 68 65 6e 0a 09 09 69 6e 73 65 72 74 20 69 6e then...insert in
0390: 74 6f 20 73 69 74 65 20 28 73 69 74 65 29 20 76 to site (site) v
03a0: 61 6c 75 65 73 20 28 6d 79 5f 73 69 74 65 29 3b alues (my_site);
03b0: 0a 09 09 73 65 6c 65 63 74 20 69 64 5f 73 69 74 ...select id_sit
03c0: 65 20 66 72 6f 6d 20 73 69 74 65 20 77 68 65 72 e from site wher
03d0: 65 20 6d 79 5f 73 69 74 65 20 3d 20 73 69 74 65 e my_site = site
03e0: 20 69 6e 74 6f 20 6d 79 5f 73 69 74 65 5f 69 64 into my_site_id
03f0: 3b 0a 09 65 6e 64 20 69 66 3b 0a 0a 09 2d 2d 20 ;..end if;...--
0400: 73 65 6c 65 63 74 69 6e 67 20 74 61 67 73 20 73 selecting tags s
0410: 69 74 65 20 61 6c 72 65 61 64 79 20 68 61 76 65 ite already have
0420: 20 61 6e 64 20 61 64 64 69 6e 67 20 6e 65 77 20 and adding new
0430: 74 61 67 20 74 6f 20 74 68 65 6d 0a 09 2d 2d 20 tag to them..--
0440: 6e 6f 74 65 20 74 68 61 74 20 74 61 67 73 20 73 note that tags s
0450: 68 6f 75 6c 64 20 62 65 20 73 6f 72 74 65 64 20 hould be sorted
0460: 74 6f 20 65 6c 69 6d 69 6e 61 74 65 20 70 65 72 to eliminate per
0470: 6d 75 74 61 74 69 6f 6e 73 0a 09 73 65 6c 65 63 mutations..selec
0480: 74 20 74 61 67 20 66 72 6f 6d 20 75 72 6c 73 20 t tag from urls
0490: 6e 61 74 75 72 61 6c 20 6a 6f 69 6e 20 74 61 67 natural join tag
04a0: 20 77 68 65 72 65 20 69 64 5f 73 69 74 65 20 3d where id_site =
04b0: 20 6d 79 5f 73 69 74 65 5f 69 64 20 69 6e 74 6f my_site_id into
04c0: 20 6d 79 5f 74 61 67 3b 0a 09 69 66 20 6e 6f 74 my_tag;..if not
04d0: 20 66 6f 75 6e 64 20 74 68 65 6e 0a 09 09 2d 2d found then...--
04e0: 20 6e 6f 20 72 65 63 6f 72 64 73 20 66 6f 75 6e no records foun
04f0: 64 20 2d 20 63 72 65 61 74 69 6e 67 20 6e 65 77 d - creating new
0500: 20 74 61 67 0a 09 09 6d 79 5f 74 61 67 20 3a 3d tag...my_tag :=
0510: 20 61 72 72 61 79 5b 6e 65 77 5f 74 61 67 5d 3b array[new_tag];
0520: 0a 09 65 6c 73 65 0a 09 09 2d 2d 20 6a 6f 69 6e ..else...-- join
0530: 69 6e 67 20 74 61 67 73 0a 09 09 73 65 6c 65 63 ing tags...selec
0540: 74 20 75 73 6f 72 74 28 6d 79 5f 74 61 67 20 7c t usort(my_tag |
0550: 7c 20 61 72 72 61 79 5b 6e 65 77 5f 74 61 67 5d | array[new_tag]
0560: 29 20 69 6e 74 6f 20 6d 79 5f 74 61 67 3b 0a 09 ) into my_tag;..
0570: 09 2d 2d 20 64 65 6c 65 74 69 6e 67 20 6f 6c 64 .-- deleting old
0580: 20 73 69 74 65 20 73 70 65 63 69 66 69 63 61 74 site specificat
0590: 69 6f 6e 0a 09 09 64 65 6c 65 74 65 20 66 72 6f ion...delete fro
05a0: 6d 20 75 72 6c 73 20 77 68 65 72 65 20 69 64 5f m urls where id_
05b0: 73 69 74 65 20 3d 20 6d 79 5f 73 69 74 65 5f 69 site = my_site_i
05c0: 64 3b 0a 09 65 6e 64 20 69 66 3b 0a 0a 09 2d 2d d;..end if;...--
05d0: 20 73 65 6c 65 63 74 69 6e 67 20 6e 65 77 20 74 selecting new t
05e0: 61 67 20 69 64 20 6f 72 20 61 64 64 69 6e 67 20 ag id or adding
05f0: 74 61 67 20 74 6f 20 74 68 65 20 74 61 62 6c 65 tag to the table
0600: 0a 09 73 65 6c 65 63 74 20 69 64 5f 74 61 67 20 ..select id_tag
0610: 66 72 6f 6d 20 74 61 67 20 77 68 65 72 65 20 6d from tag where m
0620: 79 5f 74 61 67 20 3d 20 74 61 67 20 69 6e 74 6f y_tag = tag into
0630: 20 6d 79 5f 74 61 67 5f 69 64 3b 0a 09 69 66 20 my_tag_id;..if
0640: 6e 6f 74 20 66 6f 75 6e 64 20 74 68 65 6e 0a 09 not found then..
0650: 09 69 6e 73 65 72 74 20 69 6e 74 6f 20 74 61 67 .insert into tag
0660: 20 28 74 61 67 29 20 76 61 6c 75 65 73 28 6d 79 (tag) values(my
0670: 5f 74 61 67 29 3b 0a 09 09 73 65 6c 65 63 74 20 _tag);...select
0680: 69 64 5f 74 61 67 20 66 72 6f 6d 20 74 61 67 20 id_tag from tag
0690: 77 68 65 72 65 20 6d 79 5f 74 61 67 20 3d 20 74 where my_tag = t
06a0: 61 67 20 69 6e 74 6f 20 6d 79 5f 74 61 67 5f 69 ag into my_tag_i
06b0: 64 3b 0a 09 65 6e 64 20 69 66 3b 0a 0a 09 2d 2d d;..end if;...--
06c0: 20 61 64 64 69 6e 67 20 6e 65 77 20 73 69 74 65 adding new site
06d0: 20 73 70 65 63 69 66 69 63 61 74 69 6f 6e 0a 09 specification..
06e0: 69 6e 73 65 72 74 20 69 6e 74 6f 20 75 72 6c 73 insert into urls
06f0: 20 28 69 64 5f 73 69 74 65 2c 20 69 64 5f 74 61 (id_site, id_ta
0700: 67 29 20 76 61 6c 75 65 73 20 28 6d 79 5f 73 69 g) values (my_si
0710: 74 65 5f 69 64 2c 20 6d 79 5f 74 61 67 5f 69 64 te_id, my_tag_id
0720: 29 3b 0a 65 6e 64 3b 0a 24 24 3b 0a 0a 2d 2d 20 );.end;.$$;..--
0730: 74 68 69 73 20 66 75 6e 63 74 69 6f 6e 20 61 64 this function ad
0740: 64 73 20 74 61 67 20 74 6f 20 73 69 74 65 20 62 ds tag to site b
0750: 79 20 73 69 74 65 20 69 64 0a 43 52 45 41 54 45 y site id.CREATE
0760: 20 46 55 4e 43 54 49 4f 4e 20 6d 61 72 6b 28 6d FUNCTION mark(m
0770: 79 5f 73 69 74 65 5f 69 64 20 73 6d 61 6c 6c 69 y_site_id smalli
0780: 6e 74 2c 20 6e 65 77 5f 74 61 67 20 74 65 78 74 nt, new_tag text
0790: 29 20 52 45 54 55 52 4e 53 20 76 6f 69 64 0a 09 ) RETURNS void..
07a0: 4c 41 4e 47 55 41 47 45 20 70 6c 70 67 73 71 6c LANGUAGE plpgsql
07b0: 20 53 54 52 49 43 54 0a 09 41 53 20 24 24 0a 64 STRICT..AS $$.d
07c0: 65 63 6c 61 72 65 0a 09 2d 2d 20 6d 61 79 62 65 eclare..-- maybe
07d0: 20 63 68 65 63 6b 20 73 68 6f 75 6c 64 20 62 65 check should be
07e0: 20 61 64 64 65 64 20 74 6f 20 6d 61 6b 65 20 73 added to make s
07f0: 75 72 65 20 73 75 70 70 6c 69 65 64 20 73 69 74 ure supplied sit
0800: 65 20 69 64 20 72 65 61 6c 6c 79 20 65 78 69 73 e id really exis
0810: 74 73 0a 09 6d 79 5f 74 61 67 20 74 65 78 74 5b ts..my_tag text[
0820: 5d 3b 0a 09 6d 79 5f 74 61 67 5f 69 64 20 73 6d ];..my_tag_id sm
0830: 61 6c 6c 69 6e 74 3b 0a 62 65 67 69 6e 0a 09 2d allint;.begin..-
0840: 2d 20 73 65 6c 65 63 74 69 6e 67 20 74 61 67 73 - selecting tags
0850: 20 73 69 74 65 20 61 6c 72 65 61 64 79 20 68 61 site already ha
0860: 76 65 20 61 6e 64 20 61 64 64 69 6e 67 20 6e 65 ve and adding ne
0870: 77 20 74 61 67 20 74 6f 20 74 68 65 6d 0a 09 2d w tag to them..-
0880: 2d 20 6e 6f 74 65 20 74 68 61 74 20 74 61 67 73 - note that tags
0890: 20 73 68 6f 75 6c 64 20 62 65 20 73 6f 72 74 65 should be sorte
08a0: 64 20 74 6f 20 65 6c 69 6d 69 6e 61 74 65 20 70 d to eliminate p
08b0: 65 72 6d 75 74 61 74 69 6f 6e 73 0a 09 73 65 6c ermutations..sel
08c0: 65 63 74 20 74 61 67 20 66 72 6f 6d 20 75 72 6c ect tag from url
08d0: 73 20 6e 61 74 75 72 61 6c 20 6a 6f 69 6e 20 74 s natural join t
08e0: 61 67 20 77 68 65 72 65 20 69 64 5f 73 69 74 65 ag where id_site
08f0: 20 3d 20 6d 79 5f 73 69 74 65 5f 69 64 20 69 6e = my_site_id in
0900: 74 6f 20 6d 79 5f 74 61 67 3b 0a 09 69 66 20 6e to my_tag;..if n
0910: 6f 74 20 66 6f 75 6e 64 20 74 68 65 6e 0a 09 09 ot found then...
0920: 2d 2d 20 6e 6f 20 72 65 63 6f 72 64 73 20 66 6f -- no records fo
0930: 75 6e 64 20 2d 20 63 72 65 61 74 69 6e 67 20 6e und - creating n
0940: 65 77 20 74 61 67 0a 09 09 6d 79 5f 74 61 67 20 ew tag...my_tag
0950: 3a 3d 20 61 72 72 61 79 5b 6e 65 77 5f 74 61 67 := array[new_tag
0960: 5d 3b 0a 09 65 6c 73 65 0a 09 09 2d 2d 20 6a 6f ];..else...-- jo
0970: 69 6e 69 6e 67 20 74 61 67 73 0a 09 09 73 65 6c ining tags...sel
0980: 65 63 74 20 75 73 6f 72 74 28 6d 79 5f 74 61 67 ect usort(my_tag
0990: 20 7c 7c 20 61 72 72 61 79 5b 6e 65 77 5f 74 61 || array[new_ta
09a0: 67 5d 29 20 69 6e 74 6f 20 6d 79 5f 74 61 67 3b g]) into my_tag;
09b0: 0a 09 09 2d 2d 20 64 65 6c 65 74 69 6e 67 20 6f ...-- deleting o
09c0: 6c 64 20 73 69 74 65 20 73 70 65 63 69 66 69 63 ld site specific
09d0: 61 74 69 6f 6e 0a 09 09 64 65 6c 65 74 65 20 66 ation...delete f
09e0: 72 6f 6d 20 75 72 6c 73 20 77 68 65 72 65 20 69 rom urls where i
09f0: 64 5f 73 69 74 65 20 3d 20 6d 79 5f 73 69 74 65 d_site = my_site
0a00: 5f 69 64 3b 0a 09 65 6e 64 20 69 66 3b 0a 0a 09 _id;..end if;...
0a10: 2d 2d 20 73 65 6c 65 63 74 69 6e 67 20 6e 65 77 -- selecting new
0a20: 20 74 61 67 20 69 64 20 6f 72 20 61 64 64 69 6e tag id or addin
0a30: 67 20 74 61 67 20 74 6f 20 74 68 65 20 74 61 62 g tag to the tab
0a40: 6c 65 0a 09 73 65 6c 65 63 74 20 69 64 5f 74 61 le..select id_ta
0a50: 67 20 66 72 6f 6d 20 74 61 67 20 77 68 65 72 65 g from tag where
0a60: 20 6d 79 5f 74 61 67 20 3d 20 74 61 67 20 69 6e my_tag = tag in
0a70: 74 6f 20 6d 79 5f 74 61 67 5f 69 64 3b 0a 09 69 to my_tag_id;..i
0a80: 66 20 6e 6f 74 20 66 6f 75 6e 64 20 74 68 65 6e f not found then
0a90: 0a 09 09 69 6e 73 65 72 74 20 69 6e 74 6f 20 74 ...insert into t
0aa0: 61 67 20 28 74 61 67 29 20 76 61 6c 75 65 73 28 ag (tag) values(
0ab0: 6d 79 5f 74 61 67 29 3b 0a 09 09 73 65 6c 65 63 my_tag);...selec
0ac0: 74 20 69 64 5f 74 61 67 20 66 72 6f 6d 20 74 61 t id_tag from ta
0ad0: 67 20 77 68 65 72 65 20 6d 79 5f 74 61 67 20 3d g where my_tag =
0ae0: 20 74 61 67 20 69 6e 74 6f 20 6d 79 5f 74 61 67 tag into my_tag
0af0: 5f 69 64 3b 0a 09 65 6e 64 20 69 66 3b 0a 0a 09 _id;..end if;...
0b00: 2d 2d 20 61 64 64 69 6e 67 20 6e 65 77 20 73 69 -- adding new si
0b10: 74 65 20 73 70 65 63 69 66 69 63 61 74 69 6f 6e te specification
0b20: 0a 09 69 6e 73 65 72 74 20 69 6e 74 6f 20 75 72 ..insert into ur
0b30: 6c 73 20 28 69 64 5f 73 69 74 65 2c 20 69 64 5f ls (id_site, id_
0b40: 74 61 67 29 20 76 61 6c 75 65 73 20 28 6d 79 5f tag) values (my_
0b50: 73 69 74 65 5f 69 64 2c 20 6d 79 5f 74 61 67 5f site_id, my_tag_
0b60: 69 64 29 3b 0a 65 6e 64 3b 0a 24 24 3b 0a 0a 2d id);.end;.$$;..-
0b70: 2d 20 74 72 61 6e 73 66 6f 72 6d 73 20 64 6f 6d - transforms dom
0b80: 61 69 6e 20 69 6e 74 6f 20 6f 72 64 65 72 65 64 ain into ordered
0b90: 20 61 72 72 61 79 20 66 6f 72 20 69 6e 64 65 78 array for index
0ba0: 69 6e 67 0a 43 52 45 41 54 45 20 46 55 4e 43 54 ing.CREATE FUNCT
0bb0: 49 4f 4e 20 74 72 69 70 64 6f 6d 61 69 6e 28 75 ION tripdomain(u
0bc0: 72 6c 20 74 65 78 74 29 20 52 45 54 55 52 4e 53 rl text) RETURNS
0bd0: 20 74 65 78 74 5b 5d 0a 09 4c 41 4e 47 55 41 47 text[]..LANGUAG
0be0: 45 20 70 6c 70 67 73 71 6c 20 49 4d 4d 55 54 41 E plpgsql IMMUTA
0bf0: 42 4c 45 20 53 54 52 49 43 54 0a 09 41 53 20 24 BLE STRICT..AS $
0c00: 5f 24 0a 64 65 63 6c 61 72 65 0a 09 72 65 73 75 _$.declare..resu
0c10: 6c 74 20 74 65 78 74 5b 5d 3b 0a 09 73 70 6c 69 lt text[];..spli
0c20: 74 74 65 64 20 74 65 78 74 5b 5d 3b 0a 09 78 20 tted text[];..x
0c30: 69 6e 74 65 67 65 72 3b 0a 09 6c 65 6e 67 74 68 integer;..length
0c40: 20 69 6e 74 65 67 65 72 3b 0a 62 65 67 69 6e 0a integer;.begin.
0c50: 09 73 70 6c 69 74 74 65 64 20 3a 3d 20 73 74 72 .splitted := str
0c60: 69 6e 67 5f 74 6f 5f 61 72 72 61 79 28 24 31 2c ing_to_array($1,
0c70: 20 27 2e 27 29 3b 0a 09 6c 65 6e 67 74 68 20 3a '.');..length :
0c80: 3d 20 61 72 72 61 79 5f 6c 65 6e 67 74 68 28 73 = array_length(s
0c90: 70 6c 69 74 74 65 64 2c 20 31 29 3b 0a 09 78 20 plitted, 1);..x
0ca0: 3a 3d 20 31 3b 0a 09 6c 6f 6f 70 0a 09 09 65 78 := 1;..loop...ex
0cb0: 69 74 20 77 68 65 6e 20 73 70 6c 69 74 74 65 64 it when splitted
0cc0: 5b 78 5d 20 69 73 20 6e 75 6c 6c 3b 0a 09 09 72 [x] is null;...r
0cd0: 65 73 75 6c 74 5b 78 5d 20 3a 3d 20 73 70 6c 69 esult[x] := spli
0ce0: 74 74 65 64 5b 78 5d 20 7c 7c 20 27 3a 27 20 7c tted[x] || ':' |
0cf0: 7c 20 6c 65 6e 67 74 68 20 2d 20 78 3b 0a 09 09 | length - x;...
0d00: 78 20 3a 3d 20 78 20 2b 20 31 3b 0a 09 65 6e 64 x := x + 1;..end
0d10: 20 6c 6f 6f 70 3b 0a 09 72 65 74 75 72 6e 20 72 loop;..return r
0d20: 65 73 75 6c 74 3b 0a 65 6e 64 3b 24 5f 24 3b 0a esult;.end;$_$;.
0d30: 0a 2d 2d 20 74 72 61 6e 73 66 6f 72 6d 73 20 6f .-- transforms o
0d40: 72 64 65 72 65 64 20 61 72 72 61 79 20 69 6e 74 rdered array int
0d50: 6f 20 64 6f 6d 61 69 6e 0a 63 72 65 61 74 65 20 o domain.create
0d60: 66 75 6e 63 74 69 6f 6e 20 75 6e 74 72 69 70 28 function untrip(
0d70: 73 69 74 65 20 74 65 78 74 5b 5d 29 20 72 65 74 site text[]) ret
0d80: 75 72 6e 73 20 74 65 78 74 0a 09 6c 61 6e 67 75 urns text..langu
0d90: 61 67 65 20 70 6c 70 67 73 71 6c 20 69 6d 6d 75 age plpgsql immu
0da0: 74 61 62 6c 65 20 73 74 72 69 63 74 0a 09 61 73 table strict..as
0db0: 20 24 5f 24 0a 64 65 63 6c 61 72 65 0a 09 78 20 $_$.declare..x
0dc0: 69 6e 74 65 67 65 72 3b 0a 09 73 70 6c 69 74 74 integer;..splitt
0dd0: 65 64 20 74 65 78 74 5b 5d 3b 0a 09 70 61 69 72 ed text[];..pair
0de0: 20 74 65 78 74 5b 5d 3b 0a 62 65 67 69 6e 0a 09 text[];.begin..
0df0: 78 20 3a 3d 20 61 72 72 61 79 5f 6c 65 6e 67 74 x := array_lengt
0e00: 68 28 73 69 74 65 2c 20 31 29 3b 0a 09 6c 6f 6f h(site, 1);..loo
0e10: 70 20 0a 09 09 65 78 69 74 20 77 68 65 6e 20 73 p ...exit when s
0e20: 69 74 65 5b 78 5d 20 69 73 20 6e 75 6c 6c 3b 0a ite[x] is null;.
0e30: 09 09 70 61 69 72 20 3a 3d 20 73 74 72 69 6e 67 ..pair := string
0e40: 5f 74 6f 5f 61 72 72 61 79 28 73 69 74 65 5b 78 _to_array(site[x
0e50: 5d 2c 20 27 3a 27 29 3b 0a 09 09 73 70 6c 69 74 ], ':');...split
0e60: 74 65 64 5b 30 20 2d 20 70 61 69 72 5b 32 5d 3a ted[0 - pair[2]:
0e70: 3a 69 6e 74 65 67 65 72 5d 20 3a 3d 20 70 61 69 :integer] := pai
0e80: 72 5b 31 5d 3b 0a 09 09 78 20 3a 3d 20 78 20 2d r[1];...x := x -
0e90: 20 31 3b 0a 09 65 6e 64 20 6c 6f 6f 70 3b 0a 09 1;..end loop;..
0ea0: 72 65 74 75 72 6e 20 61 72 72 61 79 5f 74 6f 5f return array_to_
0eb0: 73 74 72 69 6e 67 28 73 70 6c 69 74 74 65 64 2c string(splitted,
0ec0: 20 27 2e 27 29 3b 0a 65 6e 64 3b 0a 24 5f 24 3b '.');.end;.$_$;
0ed0: 0a 0a 2d 2d 20 74 61 62 6c 65 20 74 6f 20 68 6f ..-- table to ho
0ee0: 6c 64 20 61 6c 6c 20 72 75 6c 65 73 0a 43 52 45 ld all rules.CRE
0ef0: 41 54 45 20 54 41 42 4c 45 20 72 75 6c 65 73 20 ATE TABLE rules
0f00: 28 0a 09 6e 65 74 6d 61 73 6b 20 63 69 64 72 20 (..netmask cidr
0f10: 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 72 65 64 69 72 NOT NULL,..redir
0f20: 65 63 74 5f 75 72 6c 20 74 65 78 74 20 44 45 46 ect_url text DEF
0f30: 41 55 4c 54 20 27 61 62 6f 75 74 3a 3a 62 6c 61 AULT 'about::bla
0f40: 6e 6b 27 3a 3a 74 65 78 74 20 4e 4f 54 20 4e 55 nk'::text NOT NU
0f50: 4c 4c 2c 0a 09 66 72 6f 6d 5f 77 65 65 6b 64 61 LL,..from_weekda
0f60: 79 20 73 6d 61 6c 6c 69 6e 74 20 44 45 46 41 55 y smallint DEFAU
0f70: 4c 54 20 30 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 LT 0 NOT NULL,..
0f80: 74 6f 5f 77 65 65 6b 64 61 79 20 73 6d 61 6c 6c to_weekday small
0f90: 69 6e 74 20 44 45 46 41 55 4c 54 20 36 20 4e 4f int DEFAULT 6 NO
0fa0: 54 20 4e 55 4c 4c 2c 0a 09 66 72 6f 6d 5f 74 69 T NULL,..from_ti
0fb0: 6d 65 20 74 69 6d 65 20 77 69 74 68 6f 75 74 20 me time without
0fc0: 74 69 6d 65 20 7a 6f 6e 65 20 44 45 46 41 55 4c time zone DEFAUL
0fd0: 54 20 27 30 30 3a 30 30 3a 30 30 27 3a 3a 74 69 T '00:00:00'::ti
0fe0: 6d 65 20 77 69 74 68 6f 75 74 20 74 69 6d 65 20 me without time
0ff0: 7a 6f 6e 65 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 zone NOT NULL,..
1000: 74 6f 5f 74 69 6d 65 20 74 69 6d 65 20 77 69 74 to_time time wit
1010: 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e 65 20 44 hout time zone D
1020: 45 46 41 55 4c 54 20 27 32 33 3a 35 39 3a 35 39 EFAULT '23:59:59
1030: 27 3a 3a 74 69 6d 65 20 77 69 74 68 6f 75 74 20 '::time without
1040: 74 69 6d 65 20 7a 6f 6e 65 20 4e 4f 54 20 4e 55 time zone NOT NU
1050: 4c 4c 2c 0a 09 69 64 5f 74 61 67 20 73 6d 61 6c LL,..id_tag smal
1060: 6c 69 6e 74 20 4e 4f 54 20 4e 55 4c 4c 0a 29 3b lint NOT NULL.);
1070: 0a 0a 41 4c 54 45 52 20 54 41 42 4c 45 20 4f 4e ..ALTER TABLE ON
1080: 4c 59 20 72 75 6c 65 73 0a 09 41 44 44 20 43 4f LY rules..ADD CO
1090: 4e 53 54 52 41 49 4e 54 20 72 75 6c 65 73 5f 70 NSTRAINT rules_p
10a0: 6b 65 79 20 50 52 49 4d 41 52 59 20 4b 45 59 20 key PRIMARY KEY
10b0: 28 6e 65 74 6d 61 73 6b 29 3b 0a 0a 2d 2d 20 74 (netmask);..-- t
10c0: 61 62 6c 65 20 74 6f 20 68 6f 6c 64 20 73 69 74 able to hold sit
10d0: 65 20 61 72 72 61 79 73 0a 43 52 45 41 54 45 20 e arrays.CREATE
10e0: 54 41 42 4c 45 20 73 69 74 65 20 28 0a 09 69 64 TABLE site (..id
10f0: 5f 73 69 74 65 20 73 65 72 69 61 6c 2c 0a 09 73 _site serial,..s
1100: 69 74 65 20 74 65 78 74 5b 5d 20 4e 4f 54 20 4e ite text[] NOT N
1110: 55 4c 4c 0a 29 3b 0a 0a 41 4c 54 45 52 20 54 41 ULL.);..ALTER TA
1120: 42 4c 45 20 4f 4e 4c 59 20 73 69 74 65 0a 09 41 BLE ONLY site..A
1130: 44 44 20 43 4f 4e 53 54 52 41 49 4e 54 20 73 69 DD CONSTRAINT si
1140: 74 65 5f 69 64 20 50 52 49 4d 41 52 59 20 4b 45 te_id PRIMARY KE
1150: 59 20 28 69 64 5f 73 69 74 65 29 3b 0a 0a 43 52 Y (id_site);..CR
1160: 45 41 54 45 20 55 4e 49 51 55 45 20 49 4e 44 45 EATE UNIQUE INDE
1170: 58 20 73 69 74 65 5f 73 20 4f 4e 20 73 69 74 65 X site_s ON site
1180: 20 28 75 73 6f 72 74 28 73 69 74 65 29 29 3b 0a (usort(site));.
1190: 0a 43 52 45 41 54 45 20 49 4e 44 45 58 20 73 69 .CREATE INDEX si
11a0: 74 65 5f 67 20 4f 4e 20 73 69 74 65 20 55 53 49 te_g ON site USI
11b0: 4e 47 20 67 69 6e 20 28 73 69 74 65 29 3b 0a 0a NG gin (site);..
11c0: 2d 2d 20 74 61 62 6c 65 20 74 6f 20 68 6f 6c 64 -- table to hold
11d0: 20 74 61 67 20 63 6f 6d 62 69 6e 61 74 69 6f 6e tag combination
11e0: 73 0a 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 s.CREATE TABLE t
11f0: 61 67 20 28 0a 09 69 64 5f 74 61 67 20 73 65 72 ag (..id_tag ser
1200: 69 61 6c 2c 0a 09 74 61 67 20 74 65 78 74 5b 5d ial,..tag text[]
1210: 20 4e 4f 54 20 4e 55 4c 4c 0a 29 3b 0a 0a 41 4c NOT NULL.);..AL
1220: 54 45 52 20 54 41 42 4c 45 20 4f 4e 4c 59 20 74 TER TABLE ONLY t
1230: 61 67 0a 09 41 44 44 20 43 4f 4e 53 54 52 41 49 ag..ADD CONSTRAI
1240: 4e 54 20 74 61 67 5f 69 64 20 50 52 49 4d 41 52 NT tag_id PRIMAR
1250: 59 20 4b 45 59 20 28 69 64 5f 74 61 67 29 3b 0a Y KEY (id_tag);.
1260: 0a 43 52 45 41 54 45 20 55 4e 49 51 55 45 20 49 .CREATE UNIQUE I
1270: 4e 44 45 58 20 74 61 67 5f 73 20 4f 4e 20 74 61 NDEX tag_s ON ta
1280: 67 20 28 75 73 6f 72 74 28 74 61 67 29 29 3b 0a g (usort(tag));.
1290: 0a 43 52 45 41 54 45 20 49 4e 44 45 58 20 74 61 .CREATE INDEX ta
12a0: 67 5f 67 20 4f 4e 20 74 61 67 20 55 53 49 4e 47 g_g ON tag USING
12b0: 20 67 69 6e 20 28 74 61 67 29 3b 0a 0a 2d 2d 20 gin (tag);..--
12c0: 74 61 62 6c 65 20 74 6f 20 68 6f 6c 64 20 74 61 table to hold ta
12d0: 67 20 2d 20 73 69 74 65 20 6c 69 6e 6b 73 0a 43 g - site links.C
12e0: 52 45 41 54 45 20 54 41 42 4c 45 20 75 72 6c 73 REATE TABLE urls
12f0: 20 28 0a 09 64 61 74 65 5f 61 64 64 65 64 20 74 (..date_added t
1300: 69 6d 65 73 74 61 6d 70 20 77 69 74 68 6f 75 74 imestamp without
1310: 20 74 69 6d 65 20 7a 6f 6e 65 20 44 45 46 41 55 time zone DEFAU
1320: 4c 54 20 28 27 6e 6f 77 27 3a 3a 74 65 78 74 29 LT ('now'::text)
1330: 3a 3a 74 69 6d 65 73 74 61 6d 70 28 30 29 20 77 ::timestamp(0) w
1340: 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e 65 ithout time zone
1350: 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 69 64 5f 73 NOT NULL,..id_s
1360: 69 74 65 20 73 6d 61 6c 6c 69 6e 74 20 4e 4f 54 ite smallint NOT
1370: 20 4e 55 4c 4c 2c 0a 09 69 64 5f 74 61 67 20 73 NULL,..id_tag s
1380: 6d 61 6c 6c 69 6e 74 20 4e 4f 54 20 4e 55 4c 4c mallint NOT NULL
1390: 2c 0a 09 72 65 67 65 78 20 74 65 78 74 0a 29 3b ,..regex text.);
13a0: 0a 0a 41 4c 54 45 52 20 54 41 42 4c 45 20 4f 4e ..ALTER TABLE ON
13b0: 4c 59 20 75 72 6c 73 0a 09 41 44 44 20 43 4f 4e LY urls..ADD CON
13c0: 53 54 52 41 49 4e 54 20 75 72 6c 73 5f 70 6b 65 STRAINT urls_pke
13d0: 79 20 50 52 49 4d 41 52 59 20 4b 45 59 20 28 64 y PRIMARY KEY (d
13e0: 61 74 65 5f 61 64 64 65 64 29 3b 0a 0a 43 52 45 ate_added);..CRE
13f0: 41 54 45 20 55 4e 49 51 55 45 20 49 4e 44 45 58 ATE UNIQUE INDEX
1400: 20 75 72 6c 73 5f 69 64 5f 73 69 74 65 20 4f 4e urls_id_site ON
1410: 20 75 72 6c 73 20 55 53 49 4e 47 20 62 74 72 65 urls USING btre
1420: 65 20 28 69 64 5f 73 69 74 65 29 3b 0a 0a 43 52 e (id_site);..CR
1430: 45 41 54 45 20 55 4e 49 51 55 45 20 49 4e 44 45 EATE UNIQUE INDE
1440: 58 20 75 72 6c 73 5f 69 64 5f 74 61 67 20 4f 4e X urls_id_tag ON
1450: 20 75 72 6c 73 20 55 53 49 4e 47 20 62 74 72 65 urls USING btre
1460: 65 20 28 69 64 5f 74 61 67 29 3b 0a 0a 2d 2d 20 e (id_tag);..--
1470: 72 75 6c 65 20 74 6f 20 6a 6f 69 6e 20 61 6c 6c rule to join all
1480: 20 74 61 62 6c 65 73 20 69 6e 74 6f 20 6f 6e 65 tables into one
1490: 20 74 6f 20 73 69 6d 70 6c 69 66 79 20 61 63 63 to simplify acc
14a0: 65 73 73 0a 2d 2d 20 61 75 74 6f 6d 61 74 69 63 ess.-- automatic
14b0: 61 6c 6c 20 75 73 65 73 20 63 75 72 72 65 6e 74 all uses current
14c0: 20 64 61 79 20 61 6e 64 20 74 69 6d 65 20 64 61 day and time da
14d0: 74 61 0a 43 52 45 41 54 45 20 56 49 45 57 20 73 ta.CREATE VIEW s
14e0: 69 74 65 5f 72 75 6c 65 20 41 53 0a 53 45 4c 45 ite_rule AS.SELE
14f0: 43 54 20 61 2e 72 65 64 69 72 65 63 74 5f 75 72 CT a.redirect_ur
1500: 6c 2c 20 61 2e 6e 65 74 6d 61 73 6b 2c 20 62 2e l, a.netmask, b.
1510: 73 69 74 65 2c 20 62 2e 72 65 67 65 78 70 0a 46 site, b.regexp.F
1520: 52 4f 4d 20 28 28 0a 09 53 45 4c 45 43 54 20 72 ROM ((..SELECT r
1530: 75 6c 65 73 2e 72 65 64 69 72 65 63 74 5f 75 72 ules.redirect_ur
1540: 6c 2c 20 74 61 67 2e 74 61 67 20 41 53 20 72 75 l, tag.tag AS ru
1550: 6c 65 5f 74 61 67 2c 20 72 75 6c 65 73 2e 6e 65 le_tag, rules.ne
1560: 74 6d 61 73 6b 0a 09 46 52 4f 4d 20 72 75 6c 65 tmask..FROM rule
1570: 73 20 4e 41 54 55 52 41 4c 20 4a 4f 49 4e 20 74 s NATURAL JOIN t
1580: 61 67 0a 09 57 48 45 52 45 20 28 27 6e 6f 77 27 ag..WHERE ('now'
1590: 3a 3a 74 65 78 74 29 3a 3a 74 69 6d 65 20 77 69 ::text)::time wi
15a0: 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e 65 20 thout time zone
15b0: 3e 3d 20 72 75 6c 65 73 2e 66 72 6f 6d 5f 74 69 >= rules.from_ti
15c0: 6d 65 0a 09 09 41 4e 44 20 28 27 6e 6f 77 27 3a me...AND ('now':
15d0: 3a 74 65 78 74 29 3a 3a 74 69 6d 65 20 77 69 74 :text)::time wit
15e0: 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e 65 20 3c hout time zone <
15f0: 3d 20 72 75 6c 65 73 2e 74 6f 5f 74 69 6d 65 0a = rules.to_time.
1600: 09 09 41 4e 44 20 64 61 74 65 5f 70 61 72 74 28 ..AND date_part(
1610: 27 64 6f 77 27 3a 3a 74 65 78 74 2c 20 6e 6f 77 'dow'::text, now
1620: 28 29 29 20 3e 3d 20 28 72 75 6c 65 73 2e 66 72 ()) >= (rules.fr
1630: 6f 6d 5f 77 65 65 6b 64 61 79 29 3a 3a 64 6f 75 om_weekday)::dou
1640: 62 6c 65 20 70 72 65 63 69 73 69 6f 6e 0a 09 09 ble precision...
1650: 41 4e 44 20 64 61 74 65 5f 70 61 72 74 28 27 64 AND date_part('d
1660: 6f 77 27 3a 3a 74 65 78 74 2c 20 6e 6f 77 28 29 ow'::text, now()
1670: 29 20 3c 3d 20 28 72 75 6c 65 73 2e 74 6f 5f 77 ) <= (rules.to_w
1680: 65 65 6b 64 61 79 29 3a 3a 64 6f 75 62 6c 65 20 eekday)::double
1690: 70 72 65 63 69 73 69 6f 6e 0a 29 20 61 20 4a 4f precision.) a JO
16a0: 49 4e 20 28 0a 09 53 45 4c 45 43 54 20 73 69 74 IN (..SELECT sit
16b0: 65 2e 73 69 74 65 2c 20 74 61 67 2e 74 61 67 20 e.site, tag.tag
16c0: 41 53 20 75 72 6c 5f 74 61 67 2c 20 72 65 67 65 AS url_tag, rege
16d0: 78 70 0a 09 46 52 4f 4d 20 75 72 6c 73 20 4e 41 xp..FROM urls NA
16e0: 54 55 52 41 4c 20 4a 4f 49 4e 20 74 61 67 20 4e TURAL JOIN tag N
16f0: 41 54 55 52 41 4c 20 4a 4f 49 4e 20 73 69 74 65 ATURAL JOIN site
1700: 0a 29 20 62 20 4f 4e 20 28 62 2e 75 72 6c 5f 74 .) b ON (b.url_t
1710: 61 67 20 26 26 20 61 2e 72 75 6c 65 5f 74 61 67 ag && a.rule_tag
1720: 29 29 3b 0a ));.