Artifact
6f8e0e1cc8d6fee89d0c9eed581bff53592284d4c32b0de1064e90f1e2d7ae6e:
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 6f 72 20 72 65 70 6c 61 63 65 20 46 ATE or replace F
0230: 55 4e 43 54 49 4f 4e 20 6d 61 72 6b 28 64 6f 6d UNCTION mark(dom
0240: 61 69 6e 20 74 65 78 74 2c 20 6e 65 77 5f 74 61 ain text, new_ta
0250: 67 20 74 65 78 74 29 20 52 45 54 55 52 4e 53 20 g text) RETURNS
0260: 76 6f 69 64 0a 09 4c 41 4e 47 55 41 47 45 20 70 void..LANGUAGE p
0270: 6c 70 67 73 71 6c 20 53 54 52 49 43 54 0a 09 41 lpgsql STRICT..A
0280: 53 20 24 24 0a 64 65 63 6c 61 72 65 0a 09 6d 79 S $$.declare..my
0290: 5f 73 69 74 65 20 74 65 78 74 5b 5d 3b 0a 09 6d _site text[];..m
02a0: 79 5f 69 64 5f 73 69 74 65 20 73 6d 61 6c 6c 69 y_id_site smalli
02b0: 6e 74 3b 0a 09 6d 79 5f 74 61 67 20 74 65 78 74 nt;..my_tag text
02c0: 5b 5d 3b 0a 09 6d 79 5f 69 64 5f 74 61 67 20 73 [];..my_id_tag s
02d0: 6d 61 6c 6c 69 6e 74 3b 0a 62 65 67 69 6e 0a 09 mallint;.begin..
02e0: 6d 79 5f 73 69 74 65 20 3a 3d 20 74 72 69 70 64 my_site := tripd
02f0: 6f 6d 61 69 6e 28 64 6f 6d 61 69 6e 29 3b 0a 0a omain(domain);..
0300: 09 2d 2d 20 73 65 6c 65 63 74 69 6e 67 20 73 69 .-- selecting si
0310: 74 65 20 69 64 20 66 72 6f 6d 20 74 61 62 6c 65 te id from table
0320: 20 6f 72 20 61 64 64 69 6e 67 20 73 69 74 65 20 or adding site
0330: 74 6f 20 74 68 65 20 74 61 62 6c 65 0a 09 73 65 to the table..se
0340: 6c 65 63 74 20 69 64 5f 73 69 74 65 20 66 72 6f lect id_site fro
0350: 6d 20 73 69 74 65 20 77 68 65 72 65 20 6d 79 5f m site where my_
0360: 73 69 74 65 20 3d 20 73 69 74 65 20 69 6e 74 6f site = site into
0370: 20 6d 79 5f 69 64 5f 73 69 74 65 3b 0a 09 69 66 my_id_site;..if
0380: 20 6e 6f 74 20 66 6f 75 6e 64 20 74 68 65 6e 0a not found then.
0390: 09 09 69 6e 73 65 72 74 20 69 6e 74 6f 20 73 69 ..insert into si
03a0: 74 65 20 28 73 69 74 65 29 20 76 61 6c 75 65 73 te (site) values
03b0: 20 28 6d 79 5f 73 69 74 65 29 3b 0a 09 09 73 65 (my_site);...se
03c0: 6c 65 63 74 20 69 64 5f 73 69 74 65 20 66 72 6f lect id_site fro
03d0: 6d 20 73 69 74 65 20 77 68 65 72 65 20 6d 79 5f m site where my_
03e0: 73 69 74 65 20 3d 20 73 69 74 65 20 69 6e 74 6f site = site into
03f0: 20 6d 79 5f 69 64 5f 73 69 74 65 3b 0a 09 65 6e my_id_site;..en
0400: 64 20 69 66 3b 0a 0a 09 2d 2d 20 73 65 6c 65 63 d if;...-- selec
0410: 74 69 6e 67 20 74 61 67 73 20 73 69 74 65 20 61 ting tags site a
0420: 6c 72 65 61 64 79 20 68 61 76 65 20 61 6e 64 20 lready have and
0430: 61 64 64 69 6e 67 20 6e 65 77 20 74 61 67 20 74 adding new tag t
0440: 6f 20 74 68 65 6d 0a 09 2d 2d 20 6e 6f 74 65 20 o them..-- note
0450: 74 68 61 74 20 74 61 67 73 20 73 68 6f 75 6c 64 that tags should
0460: 20 62 65 20 73 6f 72 74 65 64 20 74 6f 20 65 6c be sorted to el
0470: 69 6d 69 6e 61 74 65 20 70 65 72 6d 75 74 61 74 iminate permutat
0480: 69 6f 6e 73 0a 09 73 65 6c 65 63 74 20 63 6f 61 ions..select coa
0490: 6c 65 73 63 65 28 74 61 67 2c 20 27 7b 7d 27 3a lesce(tag, '{}':
04a0: 3a 74 65 78 74 5b 5d 29 20 66 72 6f 6d 20 75 72 :text[]) from ur
04b0: 6c 73 20 6e 61 74 75 72 61 6c 20 6a 6f 69 6e 20 ls natural join
04c0: 74 61 67 20 77 68 65 72 65 20 69 64 5f 73 69 74 tag where id_sit
04d0: 65 20 3d 20 6d 79 5f 69 64 5f 73 69 74 65 20 69 e = my_id_site i
04e0: 6e 74 6f 20 6d 79 5f 74 61 67 3b 0a 09 69 66 20 nto my_tag;..if
04f0: 6e 6f 74 20 66 6f 75 6e 64 20 74 68 65 6e 0a 09 not found then..
0500: 09 2d 2d 20 6e 6f 20 72 65 63 6f 72 64 73 20 66 .-- no records f
0510: 6f 75 6e 64 20 2d 20 63 72 65 61 74 69 6e 67 20 ound - creating
0520: 6e 65 77 20 74 61 67 0a 09 09 69 6e 73 65 72 74 new tag...insert
0530: 20 69 6e 74 6f 20 75 72 6c 73 20 28 69 64 5f 73 into urls (id_s
0540: 69 74 65 2c 20 69 64 5f 74 61 67 29 20 76 61 6c ite, id_tag) val
0550: 75 65 73 20 28 6d 79 5f 69 64 5f 73 69 74 65 2c ues (my_id_site,
0560: 20 67 65 74 5f 74 61 67 28 61 72 72 61 79 5b 6e get_tag(array[n
0570: 65 77 5f 74 61 67 5d 29 29 3b 0a 09 09 6d 79 5f ew_tag]));...my_
0580: 74 61 67 20 3a 3d 20 61 72 72 61 79 5b 6e 65 77 tag := array[new
0590: 5f 74 61 67 5d 3b 0a 09 65 6c 73 65 0a 09 09 2d _tag];..else...-
05a0: 2d 20 6a 6f 69 6e 69 6e 67 20 74 61 67 73 0a 09 - joining tags..
05b0: 09 73 65 6c 65 63 74 20 75 73 6f 72 74 28 6d 79 .select usort(my
05c0: 5f 74 61 67 20 7c 7c 20 61 72 72 61 79 5b 6e 65 _tag || array[ne
05d0: 77 5f 74 61 67 5d 29 20 69 6e 74 6f 20 6d 79 5f w_tag]) into my_
05e0: 74 61 67 3b 0a 09 09 2d 2d 20 64 65 6c 65 74 69 tag;...-- deleti
05f0: 6e 67 20 6f 6c 64 20 73 69 74 65 20 73 70 65 63 ng old site spec
0600: 69 66 69 63 61 74 69 6f 6e 0a 09 09 75 70 64 61 ification...upda
0610: 74 65 20 75 72 6c 73 20 73 65 74 20 69 64 5f 74 te urls set id_t
0620: 61 67 20 3d 20 67 65 74 5f 74 61 67 28 6d 79 5f ag = get_tag(my_
0630: 74 61 67 20 7c 7c 20 61 72 72 61 79 5b 6e 65 77 tag || array[new
0640: 5f 74 61 67 5d 29 20 77 68 65 72 65 20 69 64 5f _tag]) where id_
0650: 73 69 74 65 20 3d 20 6d 79 5f 69 64 5f 73 69 74 site = my_id_sit
0660: 65 3b 0a 09 65 6e 64 20 69 66 3b 0a 65 6e 64 3b e;..end if;.end;
0670: 0a 24 24 3b 0a 0a 2d 2d 20 74 68 69 73 20 66 75 .$$;..-- this fu
0680: 6e 63 74 69 6f 6e 20 61 64 64 73 20 74 61 67 20 nction adds tag
0690: 74 6f 20 73 69 74 65 20 62 79 20 73 69 74 65 20 to site by site
06a0: 69 64 0a 43 52 45 41 54 45 20 6f 72 20 72 65 70 id.CREATE or rep
06b0: 6c 61 63 65 20 46 55 4e 43 54 49 4f 4e 20 6d 61 lace FUNCTION ma
06c0: 72 6b 28 6d 79 5f 69 64 5f 73 69 74 65 20 69 6e rk(my_id_site in
06d0: 74 65 67 65 72 2c 20 6e 65 77 5f 74 61 67 20 74 teger, new_tag t
06e0: 65 78 74 29 20 52 45 54 55 52 4e 53 20 76 6f 69 ext) RETURNS voi
06f0: 64 0a 09 4c 41 4e 47 55 41 47 45 20 70 6c 70 67 d..LANGUAGE plpg
0700: 73 71 6c 20 53 54 52 49 43 54 0a 09 41 53 20 24 sql STRICT..AS $
0710: 24 0a 64 65 63 6c 61 72 65 0a 09 2d 2d 20 6d 61 $.declare..-- ma
0720: 79 62 65 20 63 68 65 63 6b 20 73 68 6f 75 6c 64 ybe check should
0730: 20 62 65 20 61 64 64 65 64 20 74 6f 20 6d 61 6b be added to mak
0740: 65 20 73 75 72 65 20 73 75 70 70 6c 69 65 64 20 e sure supplied
0750: 73 69 74 65 20 69 64 20 72 65 61 6c 6c 79 20 65 site id really e
0760: 78 69 73 74 73 0a 09 6d 79 5f 74 61 67 20 74 65 xists..my_tag te
0770: 78 74 5b 5d 3b 0a 09 6d 79 5f 74 61 67 5f 69 64 xt[];..my_tag_id
0780: 20 69 6e 74 65 67 65 72 3b 0a 62 65 67 69 6e 0a integer;.begin.
0790: 09 2d 2d 20 73 65 6c 65 63 74 69 6e 67 20 74 61 .-- selecting ta
07a0: 67 73 20 73 69 74 65 20 61 6c 72 65 61 64 79 20 gs site already
07b0: 68 61 76 65 20 61 6e 64 20 61 64 64 69 6e 67 20 have and adding
07c0: 6e 65 77 20 74 61 67 20 74 6f 20 74 68 65 6d 0a new tag to them.
07d0: 09 2d 2d 20 6e 6f 74 65 20 74 68 61 74 20 74 61 .-- note that ta
07e0: 67 73 20 73 68 6f 75 6c 64 20 62 65 20 73 6f 72 gs should be sor
07f0: 74 65 64 20 74 6f 20 65 6c 69 6d 69 6e 61 74 65 ted to eliminate
0800: 20 70 65 72 6d 75 74 61 74 69 6f 6e 73 0a 09 73 permutations..s
0810: 65 6c 65 63 74 20 63 6f 61 6c 65 73 63 65 28 74 elect coalesce(t
0820: 61 67 2c 20 27 7b 7d 27 3a 3a 74 65 78 74 5b 5d ag, '{}'::text[]
0830: 29 20 66 72 6f 6d 20 75 72 6c 73 20 6e 61 74 75 ) from urls natu
0840: 72 61 6c 20 6c 65 66 74 20 6a 6f 69 6e 20 74 61 ral left join ta
0850: 67 20 77 68 65 72 65 20 69 64 5f 73 69 74 65 20 g where id_site
0860: 3d 20 6d 79 5f 69 64 5f 73 69 74 65 20 69 6e 74 = my_id_site int
0870: 6f 20 6d 79 5f 74 61 67 3b 0a 09 69 66 20 6e 6f o my_tag;..if no
0880: 74 20 66 6f 75 6e 64 20 74 68 65 6e 0a 09 09 2d t found then...-
0890: 2d 20 6e 6f 20 72 65 63 6f 72 64 73 20 66 6f 75 - no records fou
08a0: 6e 64 20 2d 20 63 72 65 61 74 69 6e 67 20 6e 65 nd - creating ne
08b0: 77 20 74 61 67 0a 09 09 69 6e 73 65 72 74 20 69 w tag...insert i
08c0: 6e 74 6f 20 75 72 6c 73 20 28 69 64 5f 73 69 74 nto urls (id_sit
08d0: 65 2c 20 69 64 5f 74 61 67 29 20 76 61 6c 75 65 e, id_tag) value
08e0: 73 20 28 6d 79 5f 69 64 5f 73 69 74 65 2c 20 67 s (my_id_site, g
08f0: 65 74 5f 74 61 67 28 61 72 72 61 79 5b 6e 65 77 et_tag(array[new
0900: 5f 74 61 67 5d 29 29 3b 0a 09 65 6c 73 65 0a 09 _tag]));..else..
0910: 09 2d 2d 20 6a 6f 69 6e 69 6e 67 20 74 61 67 73 .-- joining tags
0920: 0a 09 09 73 65 6c 65 63 74 20 75 73 6f 72 74 28 ...select usort(
0930: 6d 79 5f 74 61 67 20 7c 7c 20 61 72 72 61 79 5b my_tag || array[
0940: 6e 65 77 5f 74 61 67 5d 29 20 69 6e 74 6f 20 6d new_tag]) into m
0950: 79 5f 74 61 67 3b 0a 09 09 2d 2d 20 75 70 64 61 y_tag;...-- upda
0960: 74 69 6e 67 20 65 78 69 73 74 69 6e 67 20 72 65 ting existing re
0970: 63 6f 72 64 0a 09 09 75 70 64 61 74 65 20 75 72 cord...update ur
0980: 6c 73 20 73 65 74 20 69 64 5f 74 61 67 20 3d 20 ls set id_tag =
0990: 67 65 74 5f 74 61 67 28 6d 79 5f 74 61 67 20 7c get_tag(my_tag |
09a0: 7c 20 61 72 72 61 79 5b 6e 65 77 5f 74 61 67 5d | array[new_tag]
09b0: 29 20 77 68 65 72 65 20 69 64 5f 73 69 74 65 20 ) where id_site
09c0: 3d 20 6d 79 5f 69 64 5f 73 69 74 65 3b 0a 09 65 = my_id_site;..e
09d0: 6e 64 20 69 66 3b 0a 65 6e 64 3b 0a 24 24 3b 0a nd if;.end;.$$;.
09e0: 0a 2d 2d 20 74 68 69 73 20 66 75 6e 63 74 69 6f .-- this functio
09f0: 6e 20 72 65 74 75 72 6e 73 20 69 64 20 6f 66 20 n returns id of
0a00: 74 61 67 20 61 72 72 61 79 0a 63 72 65 61 74 65 tag array.create
0a10: 20 6f 72 20 72 65 70 6c 61 63 65 20 66 75 6e 63 or replace func
0a20: 74 69 6f 6e 20 67 65 74 5f 74 61 67 28 6d 79 5f tion get_tag(my_
0a30: 74 61 67 20 74 65 78 74 5b 5d 29 20 72 65 74 75 tag text[]) retu
0a40: 72 6e 73 20 69 6e 74 65 67 65 72 0a 09 6c 61 6e rns integer..lan
0a50: 67 75 61 67 65 20 70 6c 70 67 73 71 6c 20 73 74 guage plpgsql st
0a60: 72 69 63 74 0a 09 61 73 20 24 24 0a 64 65 63 6c rict..as $$.decl
0a70: 61 72 65 0a 09 74 61 67 5f 69 64 20 69 6e 74 65 are..tag_id inte
0a80: 67 65 72 3b 0a 62 65 67 69 6e 0a 09 73 65 6c 65 ger;.begin..sele
0a90: 63 74 20 69 64 5f 74 61 67 20 66 72 6f 6d 20 74 ct id_tag from t
0aa0: 61 67 20 77 68 65 72 65 20 75 73 6f 72 74 28 6d ag where usort(m
0ab0: 79 5f 74 61 67 29 20 3d 20 74 61 67 20 69 6e 74 y_tag) = tag int
0ac0: 6f 20 74 61 67 5f 69 64 3b 0a 09 69 66 20 6e 6f o tag_id;..if no
0ad0: 74 20 66 6f 75 6e 64 20 74 68 65 6e 0a 09 09 69 t found then...i
0ae0: 6e 73 65 72 74 20 69 6e 74 6f 20 74 61 67 20 28 nsert into tag (
0af0: 74 61 67 29 20 76 61 6c 75 65 73 20 28 75 73 6f tag) values (uso
0b00: 72 74 28 6d 79 5f 74 61 67 29 29 3b 0a 09 09 73 rt(my_tag));...s
0b10: 65 6c 65 63 74 20 69 64 5f 74 61 67 20 66 72 6f elect id_tag fro
0b20: 6d 20 74 61 67 20 77 68 65 72 65 20 75 73 6f 72 m tag where usor
0b30: 74 28 6d 79 5f 74 61 67 29 20 3d 20 74 61 67 20 t(my_tag) = tag
0b40: 69 6e 74 6f 20 74 61 67 5f 69 64 3b 0a 09 65 6e into tag_id;..en
0b50: 64 20 69 66 3b 0a 09 72 65 74 75 72 6e 20 74 61 d if;..return ta
0b60: 67 5f 69 64 3b 0a 65 6e 64 3b 0a 24 24 3b 0a 0a g_id;.end;.$$;..
0b70: 2d 2d 20 74 72 61 6e 73 66 6f 72 6d 73 20 64 6f -- transforms do
0b80: 6d 61 69 6e 20 69 6e 74 6f 20 6f 72 64 65 72 65 main into ordere
0b90: 64 20 61 72 72 61 79 20 66 6f 72 20 69 6e 64 65 d array for inde
0ba0: 78 69 6e 67 0a 43 52 45 41 54 45 20 46 55 4e 43 xing.CREATE FUNC
0bb0: 54 49 4f 4e 20 74 72 69 70 64 6f 6d 61 69 6e 28 TION tripdomain(
0bc0: 75 72 6c 20 74 65 78 74 29 20 52 45 54 55 52 4e url text) RETURN
0bd0: 53 20 74 65 78 74 5b 5d 0a 09 4c 41 4e 47 55 41 S text[]..LANGUA
0be0: 47 45 20 70 6c 70 67 73 71 6c 20 49 4d 4d 55 54 GE plpgsql IMMUT
0bf0: 41 42 4c 45 20 53 54 52 49 43 54 0a 09 41 53 20 ABLE STRICT..AS
0c00: 24 5f 24 0a 64 65 63 6c 61 72 65 0a 09 72 65 73 $_$.declare..res
0c10: 75 6c 74 20 74 65 78 74 5b 5d 3b 0a 09 73 70 6c ult text[];..spl
0c20: 69 74 74 65 64 20 74 65 78 74 5b 5d 3b 0a 09 78 itted text[];..x
0c30: 20 69 6e 74 65 67 65 72 3b 0a 09 6c 65 6e 67 74 integer;..lengt
0c40: 68 20 69 6e 74 65 67 65 72 3b 0a 62 65 67 69 6e h integer;.begin
0c50: 0a 09 73 70 6c 69 74 74 65 64 20 3a 3d 20 73 74 ..splitted := st
0c60: 72 69 6e 67 5f 74 6f 5f 61 72 72 61 79 28 24 31 ring_to_array($1
0c70: 2c 20 27 2e 27 29 3b 0a 09 6c 65 6e 67 74 68 20 , '.');..length
0c80: 3a 3d 20 61 72 72 61 79 5f 6c 65 6e 67 74 68 28 := array_length(
0c90: 73 70 6c 69 74 74 65 64 2c 20 31 29 3b 0a 09 78 splitted, 1);..x
0ca0: 20 3a 3d 20 31 3b 0a 09 6c 6f 6f 70 0a 09 09 65 := 1;..loop...e
0cb0: 78 69 74 20 77 68 65 6e 20 73 70 6c 69 74 74 65 xit when splitte
0cc0: 64 5b 78 5d 20 69 73 20 6e 75 6c 6c 3b 0a 09 09 d[x] is null;...
0cd0: 72 65 73 75 6c 74 5b 78 5d 20 3a 3d 20 73 70 6c result[x] := spl
0ce0: 69 74 74 65 64 5b 78 5d 20 7c 7c 20 27 3a 27 20 itted[x] || ':'
0cf0: 7c 7c 20 6c 65 6e 67 74 68 20 2d 20 78 3b 0a 09 || length - x;..
0d00: 09 78 20 3a 3d 20 78 20 2b 20 31 3b 0a 09 65 6e .x := x + 1;..en
0d10: 64 20 6c 6f 6f 70 3b 0a 09 72 65 74 75 72 6e 20 d loop;..return
0d20: 72 65 73 75 6c 74 3b 0a 65 6e 64 3b 24 5f 24 3b result;.end;$_$;
0d30: 0a 0a 2d 2d 20 74 72 61 6e 73 66 6f 72 6d 73 20 ..-- transforms
0d40: 6f 72 64 65 72 65 64 20 61 72 72 61 79 20 69 6e ordered array in
0d50: 74 6f 20 64 6f 6d 61 69 6e 0a 63 72 65 61 74 65 to domain.create
0d60: 20 66 75 6e 63 74 69 6f 6e 20 75 6e 74 72 69 70 function untrip
0d70: 28 73 69 74 65 20 74 65 78 74 5b 5d 29 20 72 65 (site text[]) re
0d80: 74 75 72 6e 73 20 74 65 78 74 0a 09 6c 61 6e 67 turns text..lang
0d90: 75 61 67 65 20 70 6c 70 67 73 71 6c 20 69 6d 6d uage plpgsql imm
0da0: 75 74 61 62 6c 65 20 73 74 72 69 63 74 0a 09 61 utable strict..a
0db0: 73 20 24 5f 24 0a 64 65 63 6c 61 72 65 0a 09 78 s $_$.declare..x
0dc0: 20 69 6e 74 65 67 65 72 3b 0a 09 73 70 6c 69 74 integer;..split
0dd0: 74 65 64 20 74 65 78 74 5b 5d 3b 0a 09 70 61 69 ted text[];..pai
0de0: 72 20 74 65 78 74 5b 5d 3b 0a 62 65 67 69 6e 0a r text[];.begin.
0df0: 09 78 20 3a 3d 20 61 72 72 61 79 5f 6c 65 6e 67 .x := array_leng
0e00: 74 68 28 73 69 74 65 2c 20 31 29 3b 0a 09 6c 6f th(site, 1);..lo
0e10: 6f 70 20 0a 09 09 65 78 69 74 20 77 68 65 6e 20 op ...exit when
0e20: 73 69 74 65 5b 78 5d 20 69 73 20 6e 75 6c 6c 3b site[x] is null;
0e30: 0a 09 09 70 61 69 72 20 3a 3d 20 73 74 72 69 6e ...pair := strin
0e40: 67 5f 74 6f 5f 61 72 72 61 79 28 73 69 74 65 5b g_to_array(site[
0e50: 78 5d 2c 20 27 3a 27 29 3b 0a 09 09 73 70 6c 69 x], ':');...spli
0e60: 74 74 65 64 5b 30 20 2d 20 70 61 69 72 5b 32 5d tted[0 - pair[2]
0e70: 3a 3a 69 6e 74 65 67 65 72 5d 20 3a 3d 20 70 61 ::integer] := pa
0e80: 69 72 5b 31 5d 3b 0a 09 09 78 20 3a 3d 20 78 20 ir[1];...x := x
0e90: 2d 20 31 3b 0a 09 65 6e 64 20 6c 6f 6f 70 3b 0a - 1;..end loop;.
0ea0: 09 72 65 74 75 72 6e 20 61 72 72 61 79 5f 74 6f .return array_to
0eb0: 5f 73 74 72 69 6e 67 28 73 70 6c 69 74 74 65 64 _string(splitted
0ec0: 2c 20 27 2e 27 29 3b 0a 65 6e 64 3b 0a 24 5f 24 , '.');.end;.$_$
0ed0: 3b 0a 0a 2d 2d 20 74 61 62 6c 65 20 74 6f 20 68 ;..-- table to h
0ee0: 6f 6c 64 20 61 6c 6c 20 72 75 6c 65 73 0a 43 52 old all rules.CR
0ef0: 45 41 54 45 20 54 41 42 4c 45 20 72 75 6c 65 73 EATE TABLE rules
0f00: 20 28 0a 09 6e 65 74 6d 61 73 6b 20 63 69 64 72 (..netmask cidr
0f10: 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 72 65 64 69 NOT NULL,..redi
0f20: 72 65 63 74 5f 75 72 6c 20 74 65 78 74 20 44 45 rect_url text DE
0f30: 46 41 55 4c 54 20 27 61 62 6f 75 74 3a 3a 62 6c FAULT 'about::bl
0f40: 61 6e 6b 27 3a 3a 74 65 78 74 20 4e 4f 54 20 4e ank'::text NOT N
0f50: 55 4c 4c 2c 0a 09 66 72 6f 6d 5f 77 65 65 6b 64 ULL,..from_weekd
0f60: 61 79 20 73 6d 61 6c 6c 69 6e 74 20 44 45 46 41 ay smallint DEFA
0f70: 55 4c 54 20 30 20 4e 4f 54 20 4e 55 4c 4c 2c 0a ULT 0 NOT NULL,.
0f80: 09 74 6f 5f 77 65 65 6b 64 61 79 20 73 6d 61 6c .to_weekday smal
0f90: 6c 69 6e 74 20 44 45 46 41 55 4c 54 20 36 20 4e lint DEFAULT 6 N
0fa0: 4f 54 20 4e 55 4c 4c 2c 0a 09 66 72 6f 6d 5f 74 OT NULL,..from_t
0fb0: 69 6d 65 20 74 69 6d 65 20 77 69 74 68 6f 75 74 ime time without
0fc0: 20 74 69 6d 65 20 7a 6f 6e 65 20 44 45 46 41 55 time zone DEFAU
0fd0: 4c 54 20 27 30 30 3a 30 30 3a 30 30 27 3a 3a 74 LT '00:00:00'::t
0fe0: 69 6d 65 20 77 69 74 68 6f 75 74 20 74 69 6d 65 ime without time
0ff0: 20 7a 6f 6e 65 20 4e 4f 54 20 4e 55 4c 4c 2c 0a zone NOT NULL,.
1000: 09 74 6f 5f 74 69 6d 65 20 74 69 6d 65 20 77 69 .to_time time wi
1010: 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e 65 20 thout time zone
1020: 44 45 46 41 55 4c 54 20 27 32 33 3a 35 39 3a 35 DEFAULT '23:59:5
1030: 39 27 3a 3a 74 69 6d 65 20 77 69 74 68 6f 75 74 9'::time without
1040: 20 74 69 6d 65 20 7a 6f 6e 65 20 4e 4f 54 20 4e time zone NOT N
1050: 55 4c 4c 2c 0a 09 69 64 5f 74 61 67 20 73 6d 61 ULL,..id_tag sma
1060: 6c 6c 69 6e 74 20 4e 4f 54 20 4e 55 4c 4c 0a 29 llint NOT NULL.)
1070: 3b 0a 0a 41 4c 54 45 52 20 54 41 42 4c 45 20 4f ;..ALTER TABLE O
1080: 4e 4c 59 20 72 75 6c 65 73 0a 09 41 44 44 20 43 NLY rules..ADD C
1090: 4f 4e 53 54 52 41 49 4e 54 20 72 75 6c 65 73 5f ONSTRAINT rules_
10a0: 70 6b 65 79 20 50 52 49 4d 41 52 59 20 4b 45 59 pkey PRIMARY KEY
10b0: 20 28 6e 65 74 6d 61 73 6b 29 3b 0a 0a 2d 2d 20 (netmask);..--
10c0: 74 61 62 6c 65 20 74 6f 20 68 6f 6c 64 20 73 69 table to hold si
10d0: 74 65 20 61 72 72 61 79 73 0a 43 52 45 41 54 45 te arrays.CREATE
10e0: 20 54 41 42 4c 45 20 73 69 74 65 20 28 0a 09 69 TABLE site (..i
10f0: 64 5f 73 69 74 65 20 73 65 72 69 61 6c 2c 0a 09 d_site serial,..
1100: 73 69 74 65 20 74 65 78 74 5b 5d 20 4e 4f 54 20 site text[] NOT
1110: 4e 55 4c 4c 0a 29 3b 0a 0a 41 4c 54 45 52 20 54 NULL.);..ALTER T
1120: 41 42 4c 45 20 4f 4e 4c 59 20 73 69 74 65 0a 09 ABLE ONLY site..
1130: 41 44 44 20 43 4f 4e 53 54 52 41 49 4e 54 20 69 ADD CONSTRAINT i
1140: 64 5f 73 69 74 65 20 50 52 49 4d 41 52 59 20 4b d_site PRIMARY K
1150: 45 59 20 28 69 64 5f 73 69 74 65 29 3b 0a 0a 43 EY (id_site);..C
1160: 52 45 41 54 45 20 55 4e 49 51 55 45 20 49 4e 44 REATE UNIQUE IND
1170: 45 58 20 73 69 74 65 5f 73 20 4f 4e 20 73 69 74 EX site_s ON sit
1180: 65 20 28 75 73 6f 72 74 28 73 69 74 65 29 29 3b e (usort(site));
1190: 0a 0a 43 52 45 41 54 45 20 49 4e 44 45 58 20 73 ..CREATE INDEX s
11a0: 69 74 65 5f 67 20 4f 4e 20 73 69 74 65 20 55 53 ite_g ON site US
11b0: 49 4e 47 20 67 69 6e 20 28 73 69 74 65 29 3b 0a ING gin (site);.
11c0: 0a 2d 2d 20 74 61 62 6c 65 20 74 6f 20 68 6f 6c .-- table to hol
11d0: 64 20 74 61 67 20 63 6f 6d 62 69 6e 61 74 69 6f d tag combinatio
11e0: 6e 73 0a 43 52 45 41 54 45 20 54 41 42 4c 45 20 ns.CREATE TABLE
11f0: 74 61 67 20 28 0a 09 69 64 5f 74 61 67 20 73 65 tag (..id_tag se
1200: 72 69 61 6c 2c 0a 09 74 61 67 20 74 65 78 74 5b rial,..tag text[
1210: 5d 20 4e 4f 54 20 4e 55 4c 4c 0a 29 3b 0a 0a 41 ] NOT NULL.);..A
1220: 4c 54 45 52 20 54 41 42 4c 45 20 4f 4e 4c 59 20 LTER TABLE ONLY
1230: 74 61 67 0a 09 41 44 44 20 43 4f 4e 53 54 52 41 tag..ADD CONSTRA
1240: 49 4e 54 20 74 61 67 5f 69 64 20 50 52 49 4d 41 INT tag_id PRIMA
1250: 52 59 20 4b 45 59 20 28 69 64 5f 74 61 67 29 3b RY KEY (id_tag);
1260: 0a 0a 43 52 45 41 54 45 20 55 4e 49 51 55 45 20 ..CREATE UNIQUE
1270: 49 4e 44 45 58 20 74 61 67 5f 73 20 4f 4e 20 74 INDEX tag_s ON t
1280: 61 67 20 28 75 73 6f 72 74 28 74 61 67 29 29 3b ag (usort(tag));
1290: 0a 0a 43 52 45 41 54 45 20 49 4e 44 45 58 20 74 ..CREATE INDEX t
12a0: 61 67 5f 67 20 4f 4e 20 74 61 67 20 55 53 49 4e ag_g ON tag USIN
12b0: 47 20 67 69 6e 20 28 74 61 67 29 3b 0a 0a 2d 2d G gin (tag);..--
12c0: 20 74 61 62 6c 65 20 74 6f 20 68 6f 6c 64 20 74 table to hold t
12d0: 61 67 20 2d 20 73 69 74 65 20 6c 69 6e 6b 73 0a ag - site links.
12e0: 43 52 45 41 54 45 20 54 41 42 4c 45 20 75 72 6c CREATE TABLE url
12f0: 73 20 28 0a 09 64 61 74 65 5f 61 64 64 65 64 20 s (..date_added
1300: 74 69 6d 65 73 74 61 6d 70 20 77 69 74 68 6f 75 timestamp withou
1310: 74 20 74 69 6d 65 20 7a 6f 6e 65 20 44 45 46 41 t time zone DEFA
1320: 55 4c 54 20 28 27 6e 6f 77 27 3a 3a 74 65 78 74 ULT ('now'::text
1330: 29 3a 3a 74 69 6d 65 73 74 61 6d 70 28 30 29 20 )::timestamp(0)
1340: 77 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e without time zon
1350: 65 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 69 64 5f e NOT NULL,..id_
1360: 73 69 74 65 20 73 6d 61 6c 6c 69 6e 74 20 4e 4f site smallint NO
1370: 54 20 4e 55 4c 4c 2c 0a 09 69 64 5f 74 61 67 20 T NULL,..id_tag
1380: 73 6d 61 6c 6c 69 6e 74 20 4e 4f 54 20 4e 55 4c smallint NOT NUL
1390: 4c 2c 0a 09 72 65 67 65 78 20 74 65 78 74 0a 29 L,..regex text.)
13a0: 3b 0a 0a 41 4c 54 45 52 20 54 41 42 4c 45 20 4f ;..ALTER TABLE O
13b0: 4e 4c 59 20 75 72 6c 73 0a 09 41 44 44 20 43 4f NLY urls..ADD CO
13c0: 4e 53 54 52 41 49 4e 54 20 75 72 6c 73 5f 70 6b NSTRAINT urls_pk
13d0: 65 79 20 50 52 49 4d 41 52 59 20 4b 45 59 20 28 ey PRIMARY KEY (
13e0: 64 61 74 65 5f 61 64 64 65 64 29 3b 0a 0a 43 52 date_added);..CR
13f0: 45 41 54 45 20 55 4e 49 51 55 45 20 49 4e 44 45 EATE UNIQUE INDE
1400: 58 20 75 72 6c 73 5f 69 64 5f 73 69 74 65 20 4f X urls_id_site O
1410: 4e 20 75 72 6c 73 20 55 53 49 4e 47 20 62 74 72 N urls USING btr
1420: 65 65 20 28 69 64 5f 73 69 74 65 29 3b 0a 0a 43 ee (id_site);..C
1430: 52 45 41 54 45 20 55 4e 49 51 55 45 20 49 4e 44 REATE UNIQUE IND
1440: 45 58 20 75 72 6c 73 5f 69 64 5f 74 61 67 20 4f EX urls_id_tag O
1450: 4e 20 75 72 6c 73 20 55 53 49 4e 47 20 62 74 72 N urls USING btr
1460: 65 65 20 28 69 64 5f 74 61 67 29 3b 0a 0a 2d 2d ee (id_tag);..--
1470: 20 72 75 6c 65 20 74 6f 20 6a 6f 69 6e 20 61 6c rule to join al
1480: 6c 20 74 61 62 6c 65 73 20 69 6e 74 6f 20 6f 6e l tables into on
1490: 65 20 74 6f 20 73 69 6d 70 6c 69 66 79 20 61 63 e to simplify ac
14a0: 63 65 73 73 0a 2d 2d 20 61 75 74 6f 6d 61 74 69 cess.-- automati
14b0: 63 61 6c 6c 20 75 73 65 73 20 63 75 72 72 65 6e call uses curren
14c0: 74 20 64 61 79 20 61 6e 64 20 74 69 6d 65 20 64 t day and time d
14d0: 61 74 61 0a 43 52 45 41 54 45 20 56 49 45 57 20 ata.CREATE VIEW
14e0: 73 69 74 65 5f 72 75 6c 65 20 41 53 0a 53 45 4c site_rule AS.SEL
14f0: 45 43 54 20 61 2e 72 65 64 69 72 65 63 74 5f 75 ECT a.redirect_u
1500: 72 6c 2c 20 61 2e 6e 65 74 6d 61 73 6b 2c 20 62 rl, a.netmask, b
1510: 2e 73 69 74 65 2c 20 62 2e 72 65 67 65 78 70 0a .site, b.regexp.
1520: 46 52 4f 4d 20 28 28 0a 09 53 45 4c 45 43 54 20 FROM ((..SELECT
1530: 72 75 6c 65 73 2e 72 65 64 69 72 65 63 74 5f 75 rules.redirect_u
1540: 72 6c 2c 20 74 61 67 2e 74 61 67 20 41 53 20 72 rl, tag.tag AS r
1550: 75 6c 65 5f 74 61 67 2c 20 72 75 6c 65 73 2e 6e ule_tag, rules.n
1560: 65 74 6d 61 73 6b 0a 09 46 52 4f 4d 20 72 75 6c etmask..FROM rul
1570: 65 73 20 4e 41 54 55 52 41 4c 20 4a 4f 49 4e 20 es NATURAL JOIN
1580: 74 61 67 0a 09 57 48 45 52 45 20 28 27 6e 6f 77 tag..WHERE ('now
1590: 27 3a 3a 74 65 78 74 29 3a 3a 74 69 6d 65 20 77 '::text)::time w
15a0: 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e 65 ithout time zone
15b0: 20 3e 3d 20 72 75 6c 65 73 2e 66 72 6f 6d 5f 74 >= rules.from_t
15c0: 69 6d 65 0a 09 09 41 4e 44 20 28 27 6e 6f 77 27 ime...AND ('now'
15d0: 3a 3a 74 65 78 74 29 3a 3a 74 69 6d 65 20 77 69 ::text)::time wi
15e0: 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e 65 20 thout time zone
15f0: 3c 3d 20 72 75 6c 65 73 2e 74 6f 5f 74 69 6d 65 <= rules.to_time
1600: 0a 09 09 41 4e 44 20 64 61 74 65 5f 70 61 72 74 ...AND date_part
1610: 28 27 64 6f 77 27 3a 3a 74 65 78 74 2c 20 6e 6f ('dow'::text, no
1620: 77 28 29 29 20 3e 3d 20 28 72 75 6c 65 73 2e 66 w()) >= (rules.f
1630: 72 6f 6d 5f 77 65 65 6b 64 61 79 29 3a 3a 64 6f rom_weekday)::do
1640: 75 62 6c 65 20 70 72 65 63 69 73 69 6f 6e 0a 09 uble precision..
1650: 09 41 4e 44 20 64 61 74 65 5f 70 61 72 74 28 27 .AND date_part('
1660: 64 6f 77 27 3a 3a 74 65 78 74 2c 20 6e 6f 77 28 dow'::text, now(
1670: 29 29 20 3c 3d 20 28 72 75 6c 65 73 2e 74 6f 5f )) <= (rules.to_
1680: 77 65 65 6b 64 61 79 29 3a 3a 64 6f 75 62 6c 65 weekday)::double
1690: 20 70 72 65 63 69 73 69 6f 6e 0a 29 20 61 20 4a precision.) a J
16a0: 4f 49 4e 20 28 0a 09 53 45 4c 45 43 54 20 73 69 OIN (..SELECT si
16b0: 74 65 2e 73 69 74 65 2c 20 74 61 67 2e 74 61 67 te.site, tag.tag
16c0: 20 41 53 20 75 72 6c 5f 74 61 67 2c 20 72 65 67 AS url_tag, reg
16d0: 65 78 70 0a 09 46 52 4f 4d 20 75 72 6c 73 20 4e exp..FROM urls N
16e0: 41 54 55 52 41 4c 20 4a 4f 49 4e 20 74 61 67 20 ATURAL JOIN tag
16f0: 4e 41 54 55 52 41 4c 20 4a 4f 49 4e 20 73 69 74 NATURAL JOIN sit
1700: 65 0a 29 20 62 20 4f 4e 20 28 62 2e 75 72 6c 5f e.) b ON (b.url_
1710: 74 61 67 20 26 26 20 61 2e 72 75 6c 65 5f 74 61 tag && a.rule_ta
1720: 67 29 29 3b 0a g));.