Artifact
d8156bda31adab2dbbb2f7b825cd65b1435b5e067d69210d74a8071dab75721e:
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 73 ADD CONSTRAINT s
1140: 69 74 65 5f 70 6b 65 79 20 50 52 49 4d 41 52 59 ite_pkey PRIMARY
1150: 20 4b 45 59 20 28 69 64 5f 73 69 74 65 29 3b 0a KEY (id_site);.
1160: 0a 43 52 45 41 54 45 20 55 4e 49 51 55 45 20 49 .CREATE UNIQUE I
1170: 4e 44 45 58 20 73 69 74 65 5f 75 20 4f 4e 20 73 NDEX site_u ON s
1180: 69 74 65 20 28 75 73 6f 72 74 28 73 69 74 65 29 ite (usort(site)
1190: 29 3b 0a 0a 43 52 45 41 54 45 20 49 4e 44 45 58 );..CREATE INDEX
11a0: 20 73 69 74 65 5f 67 20 4f 4e 20 73 69 74 65 20 site_g ON site
11b0: 55 53 49 4e 47 20 67 69 6e 20 28 73 69 74 65 29 USING gin (site)
11c0: 3b 0a 0a 2d 2d 20 74 61 62 6c 65 20 74 6f 20 68 ;..-- table to h
11d0: 6f 6c 64 20 74 61 67 20 63 6f 6d 62 69 6e 61 74 old tag combinat
11e0: 69 6f 6e 73 0a 43 52 45 41 54 45 20 54 41 42 4c ions.CREATE TABL
11f0: 45 20 74 61 67 20 28 0a 09 69 64 5f 74 61 67 20 E tag (..id_tag
1200: 73 65 72 69 61 6c 2c 0a 09 74 61 67 20 74 65 78 serial,..tag tex
1210: 74 5b 5d 20 4e 4f 54 20 4e 55 4c 4c 0a 29 3b 0a t[] NOT NULL.);.
1220: 0a 41 4c 54 45 52 20 54 41 42 4c 45 20 4f 4e 4c .ALTER TABLE ONL
1230: 59 20 74 61 67 0a 09 41 44 44 20 43 4f 4e 53 54 Y tag..ADD CONST
1240: 52 41 49 4e 54 20 74 61 67 5f 70 6b 65 79 20 50 RAINT tag_pkey P
1250: 52 49 4d 41 52 59 20 4b 45 59 20 28 69 64 5f 74 RIMARY KEY (id_t
1260: 61 67 29 3b 0a 0a 43 52 45 41 54 45 20 55 4e 49 ag);..CREATE UNI
1270: 51 55 45 20 49 4e 44 45 58 20 74 61 67 5f 75 20 QUE INDEX tag_u
1280: 4f 4e 20 74 61 67 20 28 75 73 6f 72 74 28 74 61 ON tag (usort(ta
1290: 67 29 29 3b 0a 0a 43 52 45 41 54 45 20 49 4e 44 g));..CREATE IND
12a0: 45 58 20 74 61 67 5f 67 20 4f 4e 20 74 61 67 20 EX tag_g ON tag
12b0: 55 53 49 4e 47 20 67 69 6e 20 28 74 61 67 29 3b USING gin (tag);
12c0: 0a 0a 2d 2d 20 74 61 62 6c 65 20 74 6f 20 68 6f ..-- table to ho
12d0: 6c 64 20 74 61 67 20 2d 20 73 69 74 65 20 6c 69 ld tag - site li
12e0: 6e 6b 73 0a 43 52 45 41 54 45 20 54 41 42 4c 45 nks.CREATE TABLE
12f0: 20 75 72 6c 73 20 28 0a 09 64 61 74 65 5f 61 64 urls (..date_ad
1300: 64 65 64 20 74 69 6d 65 73 74 61 6d 70 20 77 69 ded timestamp wi
1310: 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e 65 20 thout time zone
1320: 44 45 46 41 55 4c 54 20 28 27 6e 6f 77 27 3a 3a DEFAULT ('now'::
1330: 74 65 78 74 29 3a 3a 74 69 6d 65 73 74 61 6d 70 text)::timestamp
1340: 28 30 29 20 77 69 74 68 6f 75 74 20 74 69 6d 65 (0) without time
1350: 20 7a 6f 6e 65 20 4e 4f 54 20 4e 55 4c 4c 2c 0a zone NOT NULL,.
1360: 09 69 64 5f 73 69 74 65 20 73 6d 61 6c 6c 69 6e .id_site smallin
1370: 74 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 69 64 5f t NOT NULL,..id_
1380: 74 61 67 20 73 6d 61 6c 6c 69 6e 74 20 4e 4f 54 tag smallint NOT
1390: 20 4e 55 4c 4c 2c 0a 09 72 65 67 65 78 20 74 65 NULL,..regex te
13a0: 78 74 0a 29 3b 0a 0a 41 4c 54 45 52 20 54 41 42 xt.);..ALTER TAB
13b0: 4c 45 20 4f 4e 4c 59 20 75 72 6c 73 0a 09 41 44 LE ONLY urls..AD
13c0: 44 20 43 4f 4e 53 54 52 41 49 4e 54 20 75 72 6c D CONSTRAINT url
13d0: 73 5f 70 6b 65 79 20 50 52 49 4d 41 52 59 20 4b s_pkey PRIMARY K
13e0: 45 59 20 28 64 61 74 65 5f 61 64 64 65 64 29 3b EY (date_added);
13f0: 0a 0a 43 52 45 41 54 45 20 55 4e 49 51 55 45 20 ..CREATE UNIQUE
1400: 49 4e 44 45 58 20 75 72 6c 73 5f 69 64 5f 73 69 INDEX urls_id_si
1410: 74 65 20 4f 4e 20 75 72 6c 73 20 55 53 49 4e 47 te ON urls USING
1420: 20 62 74 72 65 65 20 28 69 64 5f 73 69 74 65 29 btree (id_site)
1430: 3b 0a 0a 43 52 45 41 54 45 20 55 4e 49 51 55 45 ;..CREATE UNIQUE
1440: 20 49 4e 44 45 58 20 75 72 6c 73 5f 69 64 5f 74 INDEX urls_id_t
1450: 61 67 20 4f 4e 20 75 72 6c 73 20 55 53 49 4e 47 ag ON urls USING
1460: 20 62 74 72 65 65 20 28 69 64 5f 74 61 67 29 3b btree (id_tag);
1470: 0a 0a 2d 2d 20 72 75 6c 65 20 74 6f 20 6a 6f 69 ..-- rule to joi
1480: 6e 20 61 6c 6c 20 74 61 62 6c 65 73 20 69 6e 74 n all tables int
1490: 6f 20 6f 6e 65 20 74 6f 20 73 69 6d 70 6c 69 66 o one to simplif
14a0: 79 20 61 63 63 65 73 73 0a 2d 2d 20 61 75 74 6f y access.-- auto
14b0: 6d 61 74 69 63 61 6c 6c 20 75 73 65 73 20 63 75 maticall uses cu
14c0: 72 72 65 6e 74 20 64 61 79 20 61 6e 64 20 74 69 rrent day and ti
14d0: 6d 65 20 64 61 74 61 0a 43 52 45 41 54 45 20 56 me data.CREATE V
14e0: 49 45 57 20 73 69 74 65 5f 72 75 6c 65 20 41 53 IEW site_rule AS
14f0: 0a 53 45 4c 45 43 54 20 61 2e 72 65 64 69 72 65 .SELECT a.redire
1500: 63 74 5f 75 72 6c 2c 20 61 2e 6e 65 74 6d 61 73 ct_url, a.netmas
1510: 6b 2c 20 62 2e 73 69 74 65 2c 20 62 2e 72 65 67 k, b.site, b.reg
1520: 65 78 70 0a 46 52 4f 4d 20 28 28 0a 09 53 45 4c exp.FROM ((..SEL
1530: 45 43 54 20 72 75 6c 65 73 2e 72 65 64 69 72 65 ECT rules.redire
1540: 63 74 5f 75 72 6c 2c 20 74 61 67 2e 74 61 67 20 ct_url, tag.tag
1550: 41 53 20 72 75 6c 65 5f 74 61 67 2c 20 72 75 6c AS rule_tag, rul
1560: 65 73 2e 6e 65 74 6d 61 73 6b 0a 09 46 52 4f 4d es.netmask..FROM
1570: 20 72 75 6c 65 73 20 4e 41 54 55 52 41 4c 20 4a rules NATURAL J
1580: 4f 49 4e 20 74 61 67 0a 09 57 48 45 52 45 20 28 OIN tag..WHERE (
1590: 27 6e 6f 77 27 3a 3a 74 65 78 74 29 3a 3a 74 69 'now'::text)::ti
15a0: 6d 65 20 77 69 74 68 6f 75 74 20 74 69 6d 65 20 me without time
15b0: 7a 6f 6e 65 20 3e 3d 20 72 75 6c 65 73 2e 66 72 zone >= rules.fr
15c0: 6f 6d 5f 74 69 6d 65 0a 09 09 41 4e 44 20 28 27 om_time...AND ('
15d0: 6e 6f 77 27 3a 3a 74 65 78 74 29 3a 3a 74 69 6d now'::text)::tim
15e0: 65 20 77 69 74 68 6f 75 74 20 74 69 6d 65 20 7a e without time z
15f0: 6f 6e 65 20 3c 3d 20 72 75 6c 65 73 2e 74 6f 5f one <= rules.to_
1600: 74 69 6d 65 0a 09 09 41 4e 44 20 64 61 74 65 5f time...AND date_
1610: 70 61 72 74 28 27 64 6f 77 27 3a 3a 74 65 78 74 part('dow'::text
1620: 2c 20 6e 6f 77 28 29 29 20 3e 3d 20 28 72 75 6c , now()) >= (rul
1630: 65 73 2e 66 72 6f 6d 5f 77 65 65 6b 64 61 79 29 es.from_weekday)
1640: 3a 3a 64 6f 75 62 6c 65 20 70 72 65 63 69 73 69 ::double precisi
1650: 6f 6e 0a 09 09 41 4e 44 20 64 61 74 65 5f 70 61 on...AND date_pa
1660: 72 74 28 27 64 6f 77 27 3a 3a 74 65 78 74 2c 20 rt('dow'::text,
1670: 6e 6f 77 28 29 29 20 3c 3d 20 28 72 75 6c 65 73 now()) <= (rules
1680: 2e 74 6f 5f 77 65 65 6b 64 61 79 29 3a 3a 64 6f .to_weekday)::do
1690: 75 62 6c 65 20 70 72 65 63 69 73 69 6f 6e 0a 29 uble precision.)
16a0: 20 61 20 4a 4f 49 4e 20 28 0a 09 53 45 4c 45 43 a JOIN (..SELEC
16b0: 54 20 73 69 74 65 2e 73 69 74 65 2c 20 74 61 67 T site.site, tag
16c0: 2e 74 61 67 20 41 53 20 75 72 6c 5f 74 61 67 2c .tag AS url_tag,
16d0: 20 72 65 67 65 78 70 0a 09 46 52 4f 4d 20 75 72 regexp..FROM ur
16e0: 6c 73 20 4e 41 54 55 52 41 4c 20 4a 4f 49 4e 20 ls NATURAL JOIN
16f0: 74 61 67 20 4e 41 54 55 52 41 4c 20 4a 4f 49 4e tag NATURAL JOIN
1700: 20 73 69 74 65 0a 29 20 62 20 4f 4e 20 28 62 2e site.) b ON (b.
1710: 75 72 6c 5f 74 61 67 20 26 26 20 61 2e 72 75 6c url_tag && a.rul
1720: 65 5f 74 61 67 29 29 3b 0a e_tag));.