Artifact
5af75a9f1fac4f01ee700a4fd5b50283169e7e60d8117a2d27c3ee17ede668c3:
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 68 69 73 20 66 75 6e 63 74 69 6f 6e -- this function
0b80: 20 72 65 74 75 72 6e 73 20 69 64 20 6f 66 20 73 returns id of s
0b90: 69 74 65 20 61 72 72 61 79 0a 63 72 65 61 74 65 ite array.create
0ba0: 20 6f 72 20 72 65 70 6c 61 63 65 20 66 75 6e 63 or replace func
0bb0: 74 69 6f 6e 20 67 65 74 5f 73 69 74 65 28 6d 79 tion get_site(my
0bc0: 5f 73 69 74 65 20 74 65 78 74 5b 5d 29 20 72 65 _site text[]) re
0bd0: 74 75 72 6e 73 20 69 6e 74 65 67 65 72 0a 09 6c turns integer..l
0be0: 61 6e 67 75 61 67 65 20 70 6c 70 67 73 71 6c 20 anguage plpgsql
0bf0: 73 74 72 69 63 74 0a 09 61 73 20 24 24 0a 64 65 strict..as $$.de
0c00: 63 6c 61 72 65 0a 09 73 69 74 65 5f 69 64 20 69 clare..site_id i
0c10: 6e 74 65 67 65 72 3b 0a 62 65 67 69 6e 0a 09 73 nteger;.begin..s
0c20: 65 6c 65 63 74 20 69 64 5f 73 69 74 65 20 66 72 elect id_site fr
0c30: 6f 6d 20 73 69 74 65 20 77 68 65 72 65 20 6d 79 om site where my
0c40: 5f 73 69 74 65 20 3d 20 73 69 74 65 20 69 6e 74 _site = site int
0c50: 6f 20 73 69 74 65 5f 69 64 3b 0a 09 69 66 20 6e o site_id;..if n
0c60: 6f 74 20 66 6f 75 6e 64 20 74 68 65 6e 0a 09 09 ot found then...
0c70: 69 6e 73 65 72 74 20 69 6e 74 6f 20 73 69 74 65 insert into site
0c80: 20 28 73 69 74 65 29 20 76 61 6c 75 65 73 20 28 (site) values (
0c90: 6d 79 5f 73 69 74 65 29 3b 0a 09 09 73 65 6c 65 my_site);...sele
0ca0: 63 74 20 69 64 5f 73 69 74 65 20 66 72 6f 6d 20 ct id_site from
0cb0: 73 69 74 65 20 77 68 65 72 65 20 6d 79 5f 73 69 site where my_si
0cc0: 74 65 20 3d 20 73 69 74 65 20 69 6e 74 6f 20 73 te = site into s
0cd0: 69 74 65 5f 69 64 3b 0a 09 65 6e 64 20 69 66 3b ite_id;..end if;
0ce0: 0a 09 72 65 74 75 72 6e 20 73 69 74 65 5f 69 64 ..return site_id
0cf0: 3b 0a 65 6e 64 3b 0a 24 24 3b 0a 0a 2d 2d 20 74 ;.end;.$$;..-- t
0d00: 72 61 6e 73 66 6f 72 6d 73 20 64 6f 6d 61 69 6e ransforms domain
0d10: 20 69 6e 74 6f 20 6f 72 64 65 72 65 64 20 61 72 into ordered ar
0d20: 72 61 79 20 66 6f 72 20 69 6e 64 65 78 69 6e 67 ray for indexing
0d30: 0a 43 52 45 41 54 45 20 46 55 4e 43 54 49 4f 4e .CREATE FUNCTION
0d40: 20 74 72 69 70 64 6f 6d 61 69 6e 28 75 72 6c 20 tripdomain(url
0d50: 74 65 78 74 29 20 52 45 54 55 52 4e 53 20 74 65 text) RETURNS te
0d60: 78 74 5b 5d 0a 09 4c 41 4e 47 55 41 47 45 20 70 xt[]..LANGUAGE p
0d70: 6c 70 67 73 71 6c 20 49 4d 4d 55 54 41 42 4c 45 lpgsql IMMUTABLE
0d80: 20 53 54 52 49 43 54 0a 09 41 53 20 24 5f 24 0a STRICT..AS $_$.
0d90: 64 65 63 6c 61 72 65 0a 09 72 65 73 75 6c 74 20 declare..result
0da0: 74 65 78 74 5b 5d 3b 0a 09 73 70 6c 69 74 74 65 text[];..splitte
0db0: 64 20 74 65 78 74 5b 5d 3b 0a 09 78 20 69 6e 74 d text[];..x int
0dc0: 65 67 65 72 3b 0a 09 6c 65 6e 67 74 68 20 69 6e eger;..length in
0dd0: 74 65 67 65 72 3b 0a 62 65 67 69 6e 0a 09 73 70 teger;.begin..sp
0de0: 6c 69 74 74 65 64 20 3a 3d 20 73 74 72 69 6e 67 litted := string
0df0: 5f 74 6f 5f 61 72 72 61 79 28 24 31 2c 20 27 2e _to_array($1, '.
0e00: 27 29 3b 0a 09 6c 65 6e 67 74 68 20 3a 3d 20 61 ');..length := a
0e10: 72 72 61 79 5f 6c 65 6e 67 74 68 28 73 70 6c 69 rray_length(spli
0e20: 74 74 65 64 2c 20 31 29 3b 0a 09 78 20 3a 3d 20 tted, 1);..x :=
0e30: 31 3b 0a 09 6c 6f 6f 70 0a 09 09 65 78 69 74 20 1;..loop...exit
0e40: 77 68 65 6e 20 73 70 6c 69 74 74 65 64 5b 78 5d when splitted[x]
0e50: 20 69 73 20 6e 75 6c 6c 3b 0a 09 09 72 65 73 75 is null;...resu
0e60: 6c 74 5b 78 5d 20 3a 3d 20 73 70 6c 69 74 74 65 lt[x] := splitte
0e70: 64 5b 78 5d 20 7c 7c 20 27 3a 27 20 7c 7c 20 6c d[x] || ':' || l
0e80: 65 6e 67 74 68 20 2d 20 78 3b 0a 09 09 78 20 3a ength - x;...x :
0e90: 3d 20 78 20 2b 20 31 3b 0a 09 65 6e 64 20 6c 6f = x + 1;..end lo
0ea0: 6f 70 3b 0a 09 72 65 74 75 72 6e 20 72 65 73 75 op;..return resu
0eb0: 6c 74 3b 0a 65 6e 64 3b 24 5f 24 3b 0a 0a 2d 2d lt;.end;$_$;..--
0ec0: 20 74 72 61 6e 73 66 6f 72 6d 73 20 6f 72 64 65 transforms orde
0ed0: 72 65 64 20 61 72 72 61 79 20 69 6e 74 6f 20 64 red array into d
0ee0: 6f 6d 61 69 6e 0a 63 72 65 61 74 65 20 66 75 6e omain.create fun
0ef0: 63 74 69 6f 6e 20 75 6e 74 72 69 70 28 73 69 74 ction untrip(sit
0f00: 65 20 74 65 78 74 5b 5d 29 20 72 65 74 75 72 6e e text[]) return
0f10: 73 20 74 65 78 74 0a 09 6c 61 6e 67 75 61 67 65 s text..language
0f20: 20 70 6c 70 67 73 71 6c 20 69 6d 6d 75 74 61 62 plpgsql immutab
0f30: 6c 65 20 73 74 72 69 63 74 0a 09 61 73 20 24 5f le strict..as $_
0f40: 24 0a 64 65 63 6c 61 72 65 0a 09 78 20 69 6e 74 $.declare..x int
0f50: 65 67 65 72 3b 0a 09 73 70 6c 69 74 74 65 64 20 eger;..splitted
0f60: 74 65 78 74 5b 5d 3b 0a 09 70 61 69 72 20 74 65 text[];..pair te
0f70: 78 74 5b 5d 3b 0a 62 65 67 69 6e 0a 09 78 20 3a xt[];.begin..x :
0f80: 3d 20 61 72 72 61 79 5f 6c 65 6e 67 74 68 28 73 = array_length(s
0f90: 69 74 65 2c 20 31 29 3b 0a 09 6c 6f 6f 70 20 0a ite, 1);..loop .
0fa0: 09 09 65 78 69 74 20 77 68 65 6e 20 73 69 74 65 ..exit when site
0fb0: 5b 78 5d 20 69 73 20 6e 75 6c 6c 3b 0a 09 09 70 [x] is null;...p
0fc0: 61 69 72 20 3a 3d 20 73 74 72 69 6e 67 5f 74 6f air := string_to
0fd0: 5f 61 72 72 61 79 28 73 69 74 65 5b 78 5d 2c 20 _array(site[x],
0fe0: 27 3a 27 29 3b 0a 09 09 73 70 6c 69 74 74 65 64 ':');...splitted
0ff0: 5b 30 20 2d 20 70 61 69 72 5b 32 5d 3a 3a 69 6e [0 - pair[2]::in
1000: 74 65 67 65 72 5d 20 3a 3d 20 70 61 69 72 5b 31 teger] := pair[1
1010: 5d 3b 0a 09 09 78 20 3a 3d 20 78 20 2d 20 31 3b ];...x := x - 1;
1020: 0a 09 65 6e 64 20 6c 6f 6f 70 3b 0a 09 72 65 74 ..end loop;..ret
1030: 75 72 6e 20 61 72 72 61 79 5f 74 6f 5f 73 74 72 urn array_to_str
1040: 69 6e 67 28 73 70 6c 69 74 74 65 64 2c 20 27 2e ing(splitted, '.
1050: 27 29 3b 0a 65 6e 64 3b 0a 24 5f 24 3b 0a 0a 2d ');.end;.$_$;..-
1060: 2d 20 74 61 62 6c 65 20 74 6f 20 68 6f 6c 64 20 - table to hold
1070: 61 6c 6c 20 72 75 6c 65 73 0a 43 52 45 41 54 45 all rules.CREATE
1080: 20 54 41 42 4c 45 20 72 75 6c 65 73 20 28 0a 09 TABLE rules (..
1090: 6e 65 74 6d 61 73 6b 20 63 69 64 72 20 4e 4f 54 netmask cidr NOT
10a0: 20 4e 55 4c 4c 2c 0a 09 72 65 64 69 72 65 63 74 NULL,..redirect
10b0: 5f 75 72 6c 20 74 65 78 74 20 44 45 46 41 55 4c _url text DEFAUL
10c0: 54 20 27 61 62 6f 75 74 3a 3a 62 6c 61 6e 6b 27 T 'about::blank'
10d0: 3a 3a 74 65 78 74 20 4e 4f 54 20 4e 55 4c 4c 2c ::text NOT NULL,
10e0: 0a 09 66 72 6f 6d 5f 77 65 65 6b 64 61 79 20 73 ..from_weekday s
10f0: 6d 61 6c 6c 69 6e 74 20 44 45 46 41 55 4c 54 20 mallint DEFAULT
1100: 30 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 74 6f 5f 0 NOT NULL,..to_
1110: 77 65 65 6b 64 61 79 20 73 6d 61 6c 6c 69 6e 74 weekday smallint
1120: 20 44 45 46 41 55 4c 54 20 36 20 4e 4f 54 20 4e DEFAULT 6 NOT N
1130: 55 4c 4c 2c 0a 09 66 72 6f 6d 5f 74 69 6d 65 20 ULL,..from_time
1140: 74 69 6d 65 20 77 69 74 68 6f 75 74 20 74 69 6d time without tim
1150: 65 20 7a 6f 6e 65 20 44 45 46 41 55 4c 54 20 27 e zone DEFAULT '
1160: 30 30 3a 30 30 3a 30 30 27 3a 3a 74 69 6d 65 20 00:00:00'::time
1170: 77 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e without time zon
1180: 65 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 74 6f 5f e NOT NULL,..to_
1190: 74 69 6d 65 20 74 69 6d 65 20 77 69 74 68 6f 75 time time withou
11a0: 74 20 74 69 6d 65 20 7a 6f 6e 65 20 44 45 46 41 t time zone DEFA
11b0: 55 4c 54 20 27 32 33 3a 35 39 3a 35 39 27 3a 3a ULT '23:59:59'::
11c0: 74 69 6d 65 20 77 69 74 68 6f 75 74 20 74 69 6d time without tim
11d0: 65 20 7a 6f 6e 65 20 4e 4f 54 20 4e 55 4c 4c 2c e zone NOT NULL,
11e0: 0a 09 69 64 5f 74 61 67 20 73 6d 61 6c 6c 69 6e ..id_tag smallin
11f0: 74 20 4e 4f 54 20 4e 55 4c 4c 0a 29 3b 0a 0a 41 t NOT NULL.);..A
1200: 4c 54 45 52 20 54 41 42 4c 45 20 4f 4e 4c 59 20 LTER TABLE ONLY
1210: 72 75 6c 65 73 0a 09 41 44 44 20 43 4f 4e 53 54 rules..ADD CONST
1220: 52 41 49 4e 54 20 72 75 6c 65 73 5f 70 6b 65 79 RAINT rules_pkey
1230: 20 50 52 49 4d 41 52 59 20 4b 45 59 20 28 6e 65 PRIMARY KEY (ne
1240: 74 6d 61 73 6b 29 3b 0a 0a 2d 2d 20 74 61 62 6c tmask);..-- tabl
1250: 65 20 74 6f 20 68 6f 6c 64 20 73 69 74 65 20 61 e to hold site a
1260: 72 72 61 79 73 0a 43 52 45 41 54 45 20 54 41 42 rrays.CREATE TAB
1270: 4c 45 20 73 69 74 65 20 28 0a 09 69 64 5f 73 69 LE site (..id_si
1280: 74 65 20 73 65 72 69 61 6c 2c 0a 09 73 69 74 65 te serial,..site
1290: 20 74 65 78 74 5b 5d 20 4e 4f 54 20 4e 55 4c 4c text[] NOT NULL
12a0: 0a 29 3b 0a 0a 41 4c 54 45 52 20 54 41 42 4c 45 .);..ALTER TABLE
12b0: 20 4f 4e 4c 59 20 73 69 74 65 0a 09 41 44 44 20 ONLY site..ADD
12c0: 43 4f 4e 53 54 52 41 49 4e 54 20 73 69 74 65 5f CONSTRAINT site_
12d0: 70 6b 65 79 20 50 52 49 4d 41 52 59 20 4b 45 59 pkey PRIMARY KEY
12e0: 20 28 69 64 5f 73 69 74 65 29 3b 0a 0a 43 52 45 (id_site);..CRE
12f0: 41 54 45 20 55 4e 49 51 55 45 20 49 4e 44 45 58 ATE UNIQUE INDEX
1300: 20 73 69 74 65 5f 75 20 4f 4e 20 73 69 74 65 20 site_u ON site
1310: 28 75 73 6f 72 74 28 73 69 74 65 29 29 3b 0a 0a (usort(site));..
1320: 43 52 45 41 54 45 20 49 4e 44 45 58 20 73 69 74 CREATE INDEX sit
1330: 65 5f 67 20 4f 4e 20 73 69 74 65 20 55 53 49 4e e_g ON site USIN
1340: 47 20 67 69 6e 20 28 73 69 74 65 29 3b 0a 0a 2d G gin (site);..-
1350: 2d 20 74 61 62 6c 65 20 74 6f 20 68 6f 6c 64 20 - table to hold
1360: 74 61 67 20 63 6f 6d 62 69 6e 61 74 69 6f 6e 73 tag combinations
1370: 0a 43 52 45 41 54 45 20 54 41 42 4c 45 20 74 61 .CREATE TABLE ta
1380: 67 20 28 0a 09 69 64 5f 74 61 67 20 73 65 72 69 g (..id_tag seri
1390: 61 6c 2c 0a 09 74 61 67 20 74 65 78 74 5b 5d 20 al,..tag text[]
13a0: 4e 4f 54 20 4e 55 4c 4c 0a 29 3b 0a 0a 41 4c 54 NOT NULL.);..ALT
13b0: 45 52 20 54 41 42 4c 45 20 4f 4e 4c 59 20 74 61 ER TABLE ONLY ta
13c0: 67 0a 09 41 44 44 20 43 4f 4e 53 54 52 41 49 4e g..ADD CONSTRAIN
13d0: 54 20 74 61 67 5f 70 6b 65 79 20 50 52 49 4d 41 T tag_pkey PRIMA
13e0: 52 59 20 4b 45 59 20 28 69 64 5f 74 61 67 29 3b RY KEY (id_tag);
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 74 61 67 5f 75 20 4f 4e 20 74 INDEX tag_u ON t
1410: 61 67 20 28 75 73 6f 72 74 28 74 61 67 29 29 3b ag (usort(tag));
1420: 0a 0a 43 52 45 41 54 45 20 49 4e 44 45 58 20 74 ..CREATE INDEX t
1430: 61 67 5f 67 20 4f 4e 20 74 61 67 20 55 53 49 4e ag_g ON tag USIN
1440: 47 20 67 69 6e 20 28 74 61 67 29 3b 0a 0a 2d 2d G gin (tag);..--
1450: 20 74 61 62 6c 65 20 74 6f 20 68 6f 6c 64 20 74 table to hold t
1460: 61 67 20 2d 20 73 69 74 65 20 6c 69 6e 6b 73 0a ag - site links.
1470: 43 52 45 41 54 45 20 54 41 42 4c 45 20 75 72 6c CREATE TABLE url
1480: 73 20 28 0a 09 64 61 74 65 5f 61 64 64 65 64 20 s (..date_added
1490: 74 69 6d 65 73 74 61 6d 70 20 77 69 74 68 6f 75 timestamp withou
14a0: 74 20 74 69 6d 65 20 7a 6f 6e 65 20 44 45 46 41 t time zone DEFA
14b0: 55 4c 54 20 28 27 6e 6f 77 27 3a 3a 74 65 78 74 ULT ('now'::text
14c0: 29 3a 3a 74 69 6d 65 73 74 61 6d 70 28 30 29 20 )::timestamp(0)
14d0: 77 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e without time zon
14e0: 65 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 69 64 5f e NOT NULL,..id_
14f0: 73 69 74 65 20 73 6d 61 6c 6c 69 6e 74 20 4e 4f site smallint NO
1500: 54 20 4e 55 4c 4c 2c 0a 09 69 64 5f 74 61 67 20 T NULL,..id_tag
1510: 73 6d 61 6c 6c 69 6e 74 20 4e 4f 54 20 4e 55 4c smallint NOT NUL
1520: 4c 2c 0a 09 72 65 67 65 78 20 74 65 78 74 0a 29 L,..regex text.)
1530: 3b 0a 0a 41 4c 54 45 52 20 54 41 42 4c 45 20 4f ;..ALTER TABLE O
1540: 4e 4c 59 20 75 72 6c 73 0a 09 41 44 44 20 43 4f NLY urls..ADD CO
1550: 4e 53 54 52 41 49 4e 54 20 75 72 6c 73 5f 70 6b NSTRAINT urls_pk
1560: 65 79 20 50 52 49 4d 41 52 59 20 4b 45 59 20 28 ey PRIMARY KEY (
1570: 64 61 74 65 5f 61 64 64 65 64 29 3b 0a 0a 43 52 date_added);..CR
1580: 45 41 54 45 20 55 4e 49 51 55 45 20 49 4e 44 45 EATE UNIQUE INDE
1590: 58 20 75 72 6c 73 5f 69 64 5f 73 69 74 65 20 4f X urls_id_site O
15a0: 4e 20 75 72 6c 73 20 55 53 49 4e 47 20 62 74 72 N urls USING btr
15b0: 65 65 20 28 69 64 5f 73 69 74 65 29 3b 0a 0a 43 ee (id_site);..C
15c0: 52 45 41 54 45 20 55 4e 49 51 55 45 20 49 4e 44 REATE UNIQUE IND
15d0: 45 58 20 75 72 6c 73 5f 69 64 5f 74 61 67 20 4f EX urls_id_tag O
15e0: 4e 20 75 72 6c 73 20 55 53 49 4e 47 20 62 74 72 N urls USING btr
15f0: 65 65 20 28 69 64 5f 74 61 67 29 3b 0a 0a 2d 2d ee (id_tag);..--
1600: 20 72 75 6c 65 20 74 6f 20 6a 6f 69 6e 20 61 6c rule to join al
1610: 6c 20 74 61 62 6c 65 73 20 69 6e 74 6f 20 6f 6e l tables into on
1620: 65 20 74 6f 20 73 69 6d 70 6c 69 66 79 20 61 63 e to simplify ac
1630: 63 65 73 73 0a 2d 2d 20 61 75 74 6f 6d 61 74 69 cess.-- automati
1640: 63 61 6c 6c 20 75 73 65 73 20 63 75 72 72 65 6e call uses curren
1650: 74 20 64 61 79 20 61 6e 64 20 74 69 6d 65 20 64 t day and time d
1660: 61 74 61 0a 43 52 45 41 54 45 20 56 49 45 57 20 ata.CREATE VIEW
1670: 73 69 74 65 5f 72 75 6c 65 20 41 53 0a 53 45 4c site_rule AS.SEL
1680: 45 43 54 20 61 2e 72 65 64 69 72 65 63 74 5f 75 ECT a.redirect_u
1690: 72 6c 2c 20 61 2e 6e 65 74 6d 61 73 6b 2c 20 62 rl, a.netmask, b
16a0: 2e 73 69 74 65 2c 20 62 2e 72 65 67 65 78 70 0a .site, b.regexp.
16b0: 46 52 4f 4d 20 28 28 0a 09 53 45 4c 45 43 54 20 FROM ((..SELECT
16c0: 72 75 6c 65 73 2e 72 65 64 69 72 65 63 74 5f 75 rules.redirect_u
16d0: 72 6c 2c 20 74 61 67 2e 74 61 67 20 41 53 20 72 rl, tag.tag AS r
16e0: 75 6c 65 5f 74 61 67 2c 20 72 75 6c 65 73 2e 6e ule_tag, rules.n
16f0: 65 74 6d 61 73 6b 0a 09 46 52 4f 4d 20 72 75 6c etmask..FROM rul
1700: 65 73 20 4e 41 54 55 52 41 4c 20 4a 4f 49 4e 20 es NATURAL JOIN
1710: 74 61 67 0a 09 57 48 45 52 45 20 28 27 6e 6f 77 tag..WHERE ('now
1720: 27 3a 3a 74 65 78 74 29 3a 3a 74 69 6d 65 20 77 '::text)::time w
1730: 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e 65 ithout time zone
1740: 20 3e 3d 20 72 75 6c 65 73 2e 66 72 6f 6d 5f 74 >= rules.from_t
1750: 69 6d 65 0a 09 09 41 4e 44 20 28 27 6e 6f 77 27 ime...AND ('now'
1760: 3a 3a 74 65 78 74 29 3a 3a 74 69 6d 65 20 77 69 ::text)::time wi
1770: 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e 65 20 thout time zone
1780: 3c 3d 20 72 75 6c 65 73 2e 74 6f 5f 74 69 6d 65 <= rules.to_time
1790: 0a 09 09 41 4e 44 20 64 61 74 65 5f 70 61 72 74 ...AND date_part
17a0: 28 27 64 6f 77 27 3a 3a 74 65 78 74 2c 20 6e 6f ('dow'::text, no
17b0: 77 28 29 29 20 3e 3d 20 28 72 75 6c 65 73 2e 66 w()) >= (rules.f
17c0: 72 6f 6d 5f 77 65 65 6b 64 61 79 29 3a 3a 64 6f rom_weekday)::do
17d0: 75 62 6c 65 20 70 72 65 63 69 73 69 6f 6e 0a 09 uble precision..
17e0: 09 41 4e 44 20 64 61 74 65 5f 70 61 72 74 28 27 .AND date_part('
17f0: 64 6f 77 27 3a 3a 74 65 78 74 2c 20 6e 6f 77 28 dow'::text, now(
1800: 29 29 20 3c 3d 20 28 72 75 6c 65 73 2e 74 6f 5f )) <= (rules.to_
1810: 77 65 65 6b 64 61 79 29 3a 3a 64 6f 75 62 6c 65 weekday)::double
1820: 20 70 72 65 63 69 73 69 6f 6e 0a 29 20 61 20 4a precision.) a J
1830: 4f 49 4e 20 28 0a 09 53 45 4c 45 43 54 20 73 69 OIN (..SELECT si
1840: 74 65 2e 73 69 74 65 2c 20 74 61 67 2e 74 61 67 te.site, tag.tag
1850: 20 41 53 20 75 72 6c 5f 74 61 67 2c 20 72 65 67 AS url_tag, reg
1860: 65 78 70 0a 09 46 52 4f 4d 20 75 72 6c 73 20 4e exp..FROM urls N
1870: 41 54 55 52 41 4c 20 4a 4f 49 4e 20 74 61 67 20 ATURAL JOIN tag
1880: 4e 41 54 55 52 41 4c 20 4a 4f 49 4e 20 73 69 74 NATURAL JOIN sit
1890: 65 0a 29 20 62 20 4f 4e 20 28 62 2e 75 72 6c 5f e.) b ON (b.url_
18a0: 74 61 67 20 26 26 20 61 2e 72 75 6c 65 5f 74 61 tag && a.rule_ta
18b0: 67 29 29 3b 0a g));.