Artifact
0dcf65df963dbf158756c726f758cec608d5a18e0df676b32587f0cd2367f10d:
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 73 void..LANGUAGE s
0270: 71 6c 20 69 6d 6d 75 74 61 62 6c 65 20 53 54 52 ql immutable STR
0280: 49 43 54 0a 09 41 53 20 24 24 0a 73 65 6c 65 63 ICT..AS $$.selec
0290: 74 20 6d 61 72 6b 28 67 65 74 5f 73 69 74 65 28 t mark(get_site(
02a0: 24 31 29 2c 20 24 32 29 20 61 73 20 72 65 73 75 $1), $2) as resu
02b0: 6c 74 3b 0a 24 24 3b 0a 0a 2d 2d 20 74 68 69 73 lt;.$$;..-- this
02c0: 20 66 75 6e 63 74 69 6f 6e 20 61 64 64 73 20 74 function adds t
02d0: 61 67 20 74 6f 20 73 69 74 65 20 62 79 20 73 69 ag to site by si
02e0: 74 65 20 69 64 0a 43 52 45 41 54 45 20 6f 72 20 te id.CREATE or
02f0: 72 65 70 6c 61 63 65 20 46 55 4e 43 54 49 4f 4e replace FUNCTION
0300: 20 6d 61 72 6b 28 6d 79 5f 69 64 5f 73 69 74 65 mark(my_id_site
0310: 20 69 6e 74 65 67 65 72 2c 20 6e 65 77 5f 74 61 integer, new_ta
0320: 67 20 74 65 78 74 29 20 52 45 54 55 52 4e 53 20 g text) RETURNS
0330: 76 6f 69 64 0a 09 4c 41 4e 47 55 41 47 45 20 70 void..LANGUAGE p
0340: 6c 70 67 73 71 6c 20 53 54 52 49 43 54 0a 09 41 lpgsql STRICT..A
0350: 53 20 24 24 0a 64 65 63 6c 61 72 65 0a 09 2d 2d S $$.declare..--
0360: 20 6d 61 79 62 65 20 63 68 65 63 6b 20 73 68 6f maybe check sho
0370: 75 6c 64 20 62 65 20 61 64 64 65 64 20 74 6f 20 uld be added to
0380: 6d 61 6b 65 20 73 75 72 65 20 73 75 70 70 6c 69 make sure suppli
0390: 65 64 20 73 69 74 65 20 69 64 20 72 65 61 6c 6c ed site id reall
03a0: 79 20 65 78 69 73 74 73 0a 09 6d 79 5f 74 61 67 y exists..my_tag
03b0: 20 74 65 78 74 5b 5d 3b 0a 09 6d 79 5f 74 61 67 text[];..my_tag
03c0: 5f 69 64 20 69 6e 74 65 67 65 72 3b 0a 62 65 67 _id integer;.beg
03d0: 69 6e 0a 09 2d 2d 20 73 65 6c 65 63 74 69 6e 67 in..-- selecting
03e0: 20 74 61 67 73 20 73 69 74 65 20 61 6c 72 65 61 tags site alrea
03f0: 64 79 20 68 61 76 65 20 61 6e 64 20 61 64 64 69 dy have and addi
0400: 6e 67 20 6e 65 77 20 74 61 67 20 74 6f 20 74 68 ng new tag to th
0410: 65 6d 0a 09 2d 2d 20 6e 6f 74 65 20 74 68 61 74 em..-- note that
0420: 20 74 61 67 73 20 73 68 6f 75 6c 64 20 62 65 20 tags should be
0430: 73 6f 72 74 65 64 20 74 6f 20 65 6c 69 6d 69 6e sorted to elimin
0440: 61 74 65 20 70 65 72 6d 75 74 61 74 69 6f 6e 73 ate permutations
0450: 0a 09 73 65 6c 65 63 74 20 63 6f 61 6c 65 73 63 ..select coalesc
0460: 65 28 74 61 67 2c 20 27 7b 7d 27 3a 3a 74 65 78 e(tag, '{}'::tex
0470: 74 5b 5d 29 20 66 72 6f 6d 20 75 72 6c 73 20 6e t[]) from urls n
0480: 61 74 75 72 61 6c 20 6c 65 66 74 20 6a 6f 69 6e atural left join
0490: 20 74 61 67 20 77 68 65 72 65 20 69 64 5f 73 69 tag where id_si
04a0: 74 65 20 3d 20 6d 79 5f 69 64 5f 73 69 74 65 20 te = my_id_site
04b0: 69 6e 74 6f 20 6d 79 5f 74 61 67 3b 0a 09 69 66 into my_tag;..if
04c0: 20 6e 6f 74 20 66 6f 75 6e 64 20 74 68 65 6e 0a not found then.
04d0: 09 09 2d 2d 20 6e 6f 20 72 65 63 6f 72 64 73 20 ..-- no records
04e0: 66 6f 75 6e 64 20 2d 20 63 72 65 61 74 69 6e 67 found - creating
04f0: 20 6e 65 77 20 74 61 67 0a 09 09 69 6e 73 65 72 new tag...inser
0500: 74 20 69 6e 74 6f 20 75 72 6c 73 20 28 69 64 5f t into urls (id_
0510: 73 69 74 65 2c 20 69 64 5f 74 61 67 29 20 76 61 site, id_tag) va
0520: 6c 75 65 73 20 28 6d 79 5f 69 64 5f 73 69 74 65 lues (my_id_site
0530: 2c 20 67 65 74 5f 74 61 67 28 61 72 72 61 79 5b , get_tag(array[
0540: 6e 65 77 5f 74 61 67 5d 29 29 3b 0a 09 65 6c 73 new_tag]));..els
0550: 65 0a 09 09 2d 2d 20 6a 6f 69 6e 69 6e 67 20 74 e...-- joining t
0560: 61 67 73 0a 09 09 73 65 6c 65 63 74 20 75 73 6f ags...select uso
0570: 72 74 28 6d 79 5f 74 61 67 20 7c 7c 20 61 72 72 rt(my_tag || arr
0580: 61 79 5b 6e 65 77 5f 74 61 67 5d 29 20 69 6e 74 ay[new_tag]) int
0590: 6f 20 6d 79 5f 74 61 67 3b 0a 09 09 2d 2d 20 75 o my_tag;...-- u
05a0: 70 64 61 74 69 6e 67 20 65 78 69 73 74 69 6e 67 pdating existing
05b0: 20 72 65 63 6f 72 64 0a 09 09 75 70 64 61 74 65 record...update
05c0: 20 75 72 6c 73 20 73 65 74 20 69 64 5f 74 61 67 urls set id_tag
05d0: 20 3d 20 67 65 74 5f 74 61 67 28 6d 79 5f 74 61 = get_tag(my_ta
05e0: 67 20 7c 7c 20 61 72 72 61 79 5b 6e 65 77 5f 74 g || array[new_t
05f0: 61 67 5d 29 20 77 68 65 72 65 20 69 64 5f 73 69 ag]) where id_si
0600: 74 65 20 3d 20 6d 79 5f 69 64 5f 73 69 74 65 3b te = my_id_site;
0610: 0a 09 65 6e 64 20 69 66 3b 0a 65 6e 64 3b 0a 24 ..end if;.end;.$
0620: 24 3b 0a 0a 2d 2d 20 74 68 69 73 20 66 75 6e 63 $;..-- this func
0630: 74 69 6f 6e 20 72 65 74 75 72 6e 73 20 69 64 20 tion returns id
0640: 6f 66 20 74 61 67 20 61 72 72 61 79 0a 63 72 65 of tag array.cre
0650: 61 74 65 20 6f 72 20 72 65 70 6c 61 63 65 20 66 ate or replace f
0660: 75 6e 63 74 69 6f 6e 20 67 65 74 5f 74 61 67 28 unction get_tag(
0670: 6d 79 5f 74 61 67 20 74 65 78 74 5b 5d 29 20 72 my_tag text[]) r
0680: 65 74 75 72 6e 73 20 69 6e 74 65 67 65 72 0a 09 eturns integer..
0690: 6c 61 6e 67 75 61 67 65 20 70 6c 70 67 73 71 6c language plpgsql
06a0: 20 73 74 72 69 63 74 0a 09 61 73 20 24 24 0a 64 strict..as $$.d
06b0: 65 63 6c 61 72 65 0a 09 74 61 67 5f 69 64 20 69 eclare..tag_id i
06c0: 6e 74 65 67 65 72 3b 0a 62 65 67 69 6e 0a 09 73 nteger;.begin..s
06d0: 65 6c 65 63 74 20 69 64 5f 74 61 67 20 66 72 6f elect id_tag fro
06e0: 6d 20 74 61 67 20 77 68 65 72 65 20 75 73 6f 72 m tag where usor
06f0: 74 28 6d 79 5f 74 61 67 29 20 3d 20 74 61 67 20 t(my_tag) = tag
0700: 69 6e 74 6f 20 74 61 67 5f 69 64 3b 0a 09 69 66 into tag_id;..if
0710: 20 6e 6f 74 20 66 6f 75 6e 64 20 74 68 65 6e 0a not found then.
0720: 09 09 69 6e 73 65 72 74 20 69 6e 74 6f 20 74 61 ..insert into ta
0730: 67 20 28 74 61 67 29 20 76 61 6c 75 65 73 20 28 g (tag) values (
0740: 75 73 6f 72 74 28 6d 79 5f 74 61 67 29 29 3b 0a usort(my_tag));.
0750: 09 09 73 65 6c 65 63 74 20 69 64 5f 74 61 67 20 ..select id_tag
0760: 66 72 6f 6d 20 74 61 67 20 77 68 65 72 65 20 75 from tag where u
0770: 73 6f 72 74 28 6d 79 5f 74 61 67 29 20 3d 20 74 sort(my_tag) = t
0780: 61 67 20 69 6e 74 6f 20 74 61 67 5f 69 64 3b 0a ag into tag_id;.
0790: 09 65 6e 64 20 69 66 3b 0a 09 72 65 74 75 72 6e .end if;..return
07a0: 20 74 61 67 5f 69 64 3b 0a 65 6e 64 3b 0a 24 24 tag_id;.end;.$$
07b0: 3b 0a 0a 2d 2d 20 74 68 69 73 20 66 75 6e 63 74 ;..-- this funct
07c0: 69 6f 6e 73 20 72 65 74 75 72 6e 73 20 69 64 20 ions returns id
07d0: 6f 66 20 73 69 74 65 0a 63 72 65 61 74 65 20 6f of site.create o
07e0: 72 20 72 65 70 6c 61 63 65 20 66 75 6e 63 74 69 r replace functi
07f0: 6f 6e 20 67 65 74 5f 73 69 74 65 28 64 6f 6d 61 on get_site(doma
0800: 69 6e 20 74 65 78 74 29 20 72 65 74 75 72 6e 73 in text) returns
0810: 20 69 6e 74 65 67 65 72 0a 09 6c 61 6e 67 75 61 integer..langua
0820: 67 65 20 73 71 6c 20 69 6d 6d 75 74 61 62 6c 65 ge sql immutable
0830: 20 73 74 72 69 63 74 0a 09 61 73 20 24 24 0a 73 strict..as $$.s
0840: 65 6c 65 63 74 20 67 65 74 5f 73 69 74 65 28 74 elect get_site(t
0850: 72 69 70 64 6f 6d 61 69 6e 28 24 31 29 29 20 61 ripdomain($1)) a
0860: 73 20 72 65 73 75 6c 74 3b 0a 24 24 3b 0a 0a 63 s result;.$$;..c
0870: 72 65 61 74 65 20 6f 72 20 72 65 70 6c 61 63 65 reate or replace
0880: 20 66 75 6e 63 74 69 6f 6e 20 67 65 74 5f 73 69 function get_si
0890: 74 65 28 6d 79 5f 73 69 74 65 20 74 65 78 74 5b te(my_site text[
08a0: 5d 29 20 72 65 74 75 72 6e 73 20 69 6e 74 65 67 ]) returns integ
08b0: 65 72 0a 09 6c 61 6e 67 75 61 67 65 20 70 6c 70 er..language plp
08c0: 67 73 71 6c 20 73 74 72 69 63 74 0a 09 61 73 20 gsql strict..as
08d0: 24 24 0a 64 65 63 6c 61 72 65 0a 09 73 69 74 65 $$.declare..site
08e0: 5f 69 64 20 69 6e 74 65 67 65 72 3b 0a 62 65 67 _id integer;.beg
08f0: 69 6e 0a 09 73 65 6c 65 63 74 20 69 64 5f 73 69 in..select id_si
0900: 74 65 20 66 72 6f 6d 20 73 69 74 65 20 77 68 65 te from site whe
0910: 72 65 20 6d 79 5f 73 69 74 65 20 3d 20 73 69 74 re my_site = sit
0920: 65 20 69 6e 74 6f 20 73 69 74 65 5f 69 64 3b 0a e into site_id;.
0930: 09 69 66 20 6e 6f 74 20 66 6f 75 6e 64 20 74 68 .if not found th
0940: 65 6e 0a 09 09 69 6e 73 65 72 74 20 69 6e 74 6f en...insert into
0950: 20 73 69 74 65 20 28 73 69 74 65 29 20 76 61 6c site (site) val
0960: 75 65 73 20 28 6d 79 5f 73 69 74 65 29 3b 0a 09 ues (my_site);..
0970: 09 73 65 6c 65 63 74 20 69 64 5f 73 69 74 65 20 .select id_site
0980: 66 72 6f 6d 20 73 69 74 65 20 77 68 65 72 65 20 from site where
0990: 6d 79 5f 73 69 74 65 20 3d 20 73 69 74 65 20 69 my_site = site i
09a0: 6e 74 6f 20 73 69 74 65 5f 69 64 3b 0a 09 65 6e nto site_id;..en
09b0: 64 20 69 66 3b 0a 09 72 65 74 75 72 6e 20 73 69 d if;..return si
09c0: 74 65 5f 69 64 3b 0a 65 6e 64 3b 0a 24 24 3b 0a te_id;.end;.$$;.
09d0: 0a 2d 2d 20 74 72 61 6e 73 66 6f 72 6d 73 20 64 .-- transforms d
09e0: 6f 6d 61 69 6e 20 69 6e 74 6f 20 6f 72 64 65 72 omain into order
09f0: 65 64 20 61 72 72 61 79 20 66 6f 72 20 69 6e 64 ed array for ind
0a00: 65 78 69 6e 67 0a 43 52 45 41 54 45 20 46 55 4e exing.CREATE FUN
0a10: 43 54 49 4f 4e 20 74 72 69 70 64 6f 6d 61 69 6e CTION tripdomain
0a20: 28 75 72 6c 20 74 65 78 74 29 20 52 45 54 55 52 (url text) RETUR
0a30: 4e 53 20 74 65 78 74 5b 5d 0a 09 4c 41 4e 47 55 NS text[]..LANGU
0a40: 41 47 45 20 70 6c 70 67 73 71 6c 20 49 4d 4d 55 AGE plpgsql IMMU
0a50: 54 41 42 4c 45 20 53 54 52 49 43 54 0a 09 41 53 TABLE STRICT..AS
0a60: 20 24 5f 24 0a 64 65 63 6c 61 72 65 0a 09 72 65 $_$.declare..re
0a70: 73 75 6c 74 20 74 65 78 74 5b 5d 3b 0a 09 73 70 sult text[];..sp
0a80: 6c 69 74 74 65 64 20 74 65 78 74 5b 5d 3b 0a 09 litted text[];..
0a90: 78 20 69 6e 74 65 67 65 72 3b 0a 09 6c 65 6e 67 x integer;..leng
0aa0: 74 68 20 69 6e 74 65 67 65 72 3b 0a 62 65 67 69 th integer;.begi
0ab0: 6e 0a 09 73 70 6c 69 74 74 65 64 20 3a 3d 20 73 n..splitted := s
0ac0: 74 72 69 6e 67 5f 74 6f 5f 61 72 72 61 79 28 24 tring_to_array($
0ad0: 31 2c 20 27 2e 27 29 3b 0a 09 6c 65 6e 67 74 68 1, '.');..length
0ae0: 20 3a 3d 20 61 72 72 61 79 5f 6c 65 6e 67 74 68 := array_length
0af0: 28 73 70 6c 69 74 74 65 64 2c 20 31 29 3b 0a 09 (splitted, 1);..
0b00: 78 20 3a 3d 20 31 3b 0a 09 6c 6f 6f 70 0a 09 09 x := 1;..loop...
0b10: 65 78 69 74 20 77 68 65 6e 20 73 70 6c 69 74 74 exit when splitt
0b20: 65 64 5b 78 5d 20 69 73 20 6e 75 6c 6c 3b 0a 09 ed[x] is null;..
0b30: 09 72 65 73 75 6c 74 5b 78 5d 20 3a 3d 20 73 70 .result[x] := sp
0b40: 6c 69 74 74 65 64 5b 78 5d 20 7c 7c 20 27 3a 27 litted[x] || ':'
0b50: 20 7c 7c 20 6c 65 6e 67 74 68 20 2d 20 78 3b 0a || length - x;.
0b60: 09 09 78 20 3a 3d 20 78 20 2b 20 31 3b 0a 09 65 ..x := x + 1;..e
0b70: 6e 64 20 6c 6f 6f 70 3b 0a 09 72 65 74 75 72 6e nd loop;..return
0b80: 20 72 65 73 75 6c 74 3b 0a 65 6e 64 3b 24 5f 24 result;.end;$_$
0b90: 3b 0a 0a 2d 2d 20 74 72 61 6e 73 66 6f 72 6d 73 ;..-- transforms
0ba0: 20 6f 72 64 65 72 65 64 20 61 72 72 61 79 20 69 ordered array i
0bb0: 6e 74 6f 20 64 6f 6d 61 69 6e 0a 63 72 65 61 74 nto domain.creat
0bc0: 65 20 66 75 6e 63 74 69 6f 6e 20 75 6e 74 72 69 e function untri
0bd0: 70 28 73 69 74 65 20 74 65 78 74 5b 5d 29 20 72 p(site text[]) r
0be0: 65 74 75 72 6e 73 20 74 65 78 74 0a 09 6c 61 6e eturns text..lan
0bf0: 67 75 61 67 65 20 70 6c 70 67 73 71 6c 20 69 6d guage plpgsql im
0c00: 6d 75 74 61 62 6c 65 20 73 74 72 69 63 74 0a 09 mutable strict..
0c10: 61 73 20 24 5f 24 0a 64 65 63 6c 61 72 65 0a 09 as $_$.declare..
0c20: 78 20 69 6e 74 65 67 65 72 3b 0a 09 73 70 6c 69 x integer;..spli
0c30: 74 74 65 64 20 74 65 78 74 5b 5d 3b 0a 09 70 61 tted text[];..pa
0c40: 69 72 20 74 65 78 74 5b 5d 3b 0a 62 65 67 69 6e ir text[];.begin
0c50: 0a 09 78 20 3a 3d 20 61 72 72 61 79 5f 6c 65 6e ..x := array_len
0c60: 67 74 68 28 73 69 74 65 2c 20 31 29 3b 0a 09 6c gth(site, 1);..l
0c70: 6f 6f 70 20 0a 09 09 65 78 69 74 20 77 68 65 6e oop ...exit when
0c80: 20 73 69 74 65 5b 78 5d 20 69 73 20 6e 75 6c 6c site[x] is null
0c90: 3b 0a 09 09 70 61 69 72 20 3a 3d 20 73 74 72 69 ;...pair := stri
0ca0: 6e 67 5f 74 6f 5f 61 72 72 61 79 28 73 69 74 65 ng_to_array(site
0cb0: 5b 78 5d 2c 20 27 3a 27 29 3b 0a 09 09 73 70 6c [x], ':');...spl
0cc0: 69 74 74 65 64 5b 30 20 2d 20 70 61 69 72 5b 32 itted[0 - pair[2
0cd0: 5d 3a 3a 69 6e 74 65 67 65 72 5d 20 3a 3d 20 70 ]::integer] := p
0ce0: 61 69 72 5b 31 5d 3b 0a 09 09 78 20 3a 3d 20 78 air[1];...x := x
0cf0: 20 2d 20 31 3b 0a 09 65 6e 64 20 6c 6f 6f 70 3b - 1;..end loop;
0d00: 0a 09 72 65 74 75 72 6e 20 61 72 72 61 79 5f 74 ..return array_t
0d10: 6f 5f 73 74 72 69 6e 67 28 73 70 6c 69 74 74 65 o_string(splitte
0d20: 64 2c 20 27 2e 27 29 3b 0a 65 6e 64 3b 0a 24 5f d, '.');.end;.$_
0d30: 24 3b 0a 0a 2d 2d 20 74 61 62 6c 65 20 74 6f 20 $;..-- table to
0d40: 68 6f 6c 64 20 61 6c 6c 20 72 75 6c 65 73 0a 43 hold all rules.C
0d50: 52 45 41 54 45 20 54 41 42 4c 45 20 72 75 6c 65 REATE TABLE rule
0d60: 73 20 28 0a 09 6e 65 74 6d 61 73 6b 20 63 69 64 s (..netmask cid
0d70: 72 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 72 65 64 r NOT NULL,..red
0d80: 69 72 65 63 74 5f 75 72 6c 20 74 65 78 74 20 44 irect_url text D
0d90: 45 46 41 55 4c 54 20 27 61 62 6f 75 74 3a 3a 62 EFAULT 'about::b
0da0: 6c 61 6e 6b 27 3a 3a 74 65 78 74 20 4e 4f 54 20 lank'::text NOT
0db0: 4e 55 4c 4c 2c 0a 09 66 72 6f 6d 5f 77 65 65 6b NULL,..from_week
0dc0: 64 61 79 20 73 6d 61 6c 6c 69 6e 74 20 44 45 46 day smallint DEF
0dd0: 41 55 4c 54 20 30 20 4e 4f 54 20 4e 55 4c 4c 2c AULT 0 NOT NULL,
0de0: 0a 09 74 6f 5f 77 65 65 6b 64 61 79 20 73 6d 61 ..to_weekday sma
0df0: 6c 6c 69 6e 74 20 44 45 46 41 55 4c 54 20 36 20 llint DEFAULT 6
0e00: 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 66 72 6f 6d 5f NOT NULL,..from_
0e10: 74 69 6d 65 20 74 69 6d 65 20 77 69 74 68 6f 75 time time withou
0e20: 74 20 74 69 6d 65 20 7a 6f 6e 65 20 44 45 46 41 t time zone DEFA
0e30: 55 4c 54 20 27 30 30 3a 30 30 3a 30 30 27 3a 3a ULT '00:00:00'::
0e40: 74 69 6d 65 20 77 69 74 68 6f 75 74 20 74 69 6d time without tim
0e50: 65 20 7a 6f 6e 65 20 4e 4f 54 20 4e 55 4c 4c 2c e zone NOT NULL,
0e60: 0a 09 74 6f 5f 74 69 6d 65 20 74 69 6d 65 20 77 ..to_time time w
0e70: 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e 65 ithout time zone
0e80: 20 44 45 46 41 55 4c 54 20 27 32 33 3a 35 39 3a DEFAULT '23:59:
0e90: 35 39 27 3a 3a 74 69 6d 65 20 77 69 74 68 6f 75 59'::time withou
0ea0: 74 20 74 69 6d 65 20 7a 6f 6e 65 20 4e 4f 54 20 t time zone NOT
0eb0: 4e 55 4c 4c 2c 0a 09 69 64 5f 74 61 67 20 73 6d NULL,..id_tag sm
0ec0: 61 6c 6c 69 6e 74 20 4e 4f 54 20 4e 55 4c 4c 0a allint NOT NULL.
0ed0: 29 3b 0a 0a 41 4c 54 45 52 20 54 41 42 4c 45 20 );..ALTER TABLE
0ee0: 4f 4e 4c 59 20 72 75 6c 65 73 0a 09 41 44 44 20 ONLY rules..ADD
0ef0: 43 4f 4e 53 54 52 41 49 4e 54 20 72 75 6c 65 73 CONSTRAINT rules
0f00: 5f 70 6b 65 79 20 50 52 49 4d 41 52 59 20 4b 45 _pkey PRIMARY KE
0f10: 59 20 28 6e 65 74 6d 61 73 6b 29 3b 0a 0a 2d 2d Y (netmask);..--
0f20: 20 74 61 62 6c 65 20 74 6f 20 68 6f 6c 64 20 73 table to hold s
0f30: 69 74 65 20 61 72 72 61 79 73 0a 43 52 45 41 54 ite arrays.CREAT
0f40: 45 20 54 41 42 4c 45 20 73 69 74 65 20 28 0a 09 E TABLE site (..
0f50: 69 64 5f 73 69 74 65 20 73 65 72 69 61 6c 2c 0a id_site serial,.
0f60: 09 73 69 74 65 20 74 65 78 74 5b 5d 20 4e 4f 54 .site text[] NOT
0f70: 20 4e 55 4c 4c 0a 29 3b 0a 0a 41 4c 54 45 52 20 NULL.);..ALTER
0f80: 54 41 42 4c 45 20 4f 4e 4c 59 20 73 69 74 65 0a TABLE ONLY site.
0f90: 09 41 44 44 20 43 4f 4e 53 54 52 41 49 4e 54 20 .ADD CONSTRAINT
0fa0: 73 69 74 65 5f 70 6b 65 79 20 50 52 49 4d 41 52 site_pkey PRIMAR
0fb0: 59 20 4b 45 59 20 28 69 64 5f 73 69 74 65 29 3b Y KEY (id_site);
0fc0: 0a 0a 43 52 45 41 54 45 20 55 4e 49 51 55 45 20 ..CREATE UNIQUE
0fd0: 49 4e 44 45 58 20 73 69 74 65 5f 75 20 4f 4e 20 INDEX site_u ON
0fe0: 73 69 74 65 20 28 75 73 6f 72 74 28 73 69 74 65 site (usort(site
0ff0: 29 29 3b 0a 0a 43 52 45 41 54 45 20 49 4e 44 45 ));..CREATE INDE
1000: 58 20 73 69 74 65 5f 67 20 4f 4e 20 73 69 74 65 X site_g ON site
1010: 20 55 53 49 4e 47 20 67 69 6e 20 28 73 69 74 65 USING gin (site
1020: 29 3b 0a 0a 2d 2d 20 74 61 62 6c 65 20 74 6f 20 );..-- table to
1030: 68 6f 6c 64 20 74 61 67 20 63 6f 6d 62 69 6e 61 hold tag combina
1040: 74 69 6f 6e 73 0a 43 52 45 41 54 45 20 54 41 42 tions.CREATE TAB
1050: 4c 45 20 74 61 67 20 28 0a 09 69 64 5f 74 61 67 LE tag (..id_tag
1060: 20 73 65 72 69 61 6c 2c 0a 09 74 61 67 20 74 65 serial,..tag te
1070: 78 74 5b 5d 20 4e 4f 54 20 4e 55 4c 4c 0a 29 3b xt[] NOT NULL.);
1080: 0a 0a 41 4c 54 45 52 20 54 41 42 4c 45 20 4f 4e ..ALTER TABLE ON
1090: 4c 59 20 74 61 67 0a 09 41 44 44 20 43 4f 4e 53 LY tag..ADD CONS
10a0: 54 52 41 49 4e 54 20 74 61 67 5f 70 6b 65 79 20 TRAINT tag_pkey
10b0: 50 52 49 4d 41 52 59 20 4b 45 59 20 28 69 64 5f PRIMARY KEY (id_
10c0: 74 61 67 29 3b 0a 0a 43 52 45 41 54 45 20 55 4e tag);..CREATE UN
10d0: 49 51 55 45 20 49 4e 44 45 58 20 74 61 67 5f 75 IQUE INDEX tag_u
10e0: 20 4f 4e 20 74 61 67 20 28 75 73 6f 72 74 28 74 ON tag (usort(t
10f0: 61 67 29 29 3b 0a 0a 43 52 45 41 54 45 20 49 4e ag));..CREATE IN
1100: 44 45 58 20 74 61 67 5f 67 20 4f 4e 20 74 61 67 DEX tag_g ON tag
1110: 20 55 53 49 4e 47 20 67 69 6e 20 28 74 61 67 29 USING gin (tag)
1120: 3b 0a 0a 2d 2d 20 74 61 62 6c 65 20 74 6f 20 68 ;..-- table to h
1130: 6f 6c 64 20 74 61 67 20 2d 20 73 69 74 65 20 6c old tag - site l
1140: 69 6e 6b 73 0a 43 52 45 41 54 45 20 54 41 42 4c inks.CREATE TABL
1150: 45 20 75 72 6c 73 20 28 0a 09 64 61 74 65 5f 61 E urls (..date_a
1160: 64 64 65 64 20 74 69 6d 65 73 74 61 6d 70 20 77 dded timestamp w
1170: 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e 65 ithout time zone
1180: 20 44 45 46 41 55 4c 54 20 28 27 6e 6f 77 27 3a DEFAULT ('now':
1190: 3a 74 65 78 74 29 3a 3a 74 69 6d 65 73 74 61 6d :text)::timestam
11a0: 70 28 30 29 20 77 69 74 68 6f 75 74 20 74 69 6d p(0) without tim
11b0: 65 20 7a 6f 6e 65 20 4e 4f 54 20 4e 55 4c 4c 2c e zone NOT NULL,
11c0: 0a 09 69 64 5f 73 69 74 65 20 73 6d 61 6c 6c 69 ..id_site smalli
11d0: 6e 74 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 69 64 nt NOT NULL,..id
11e0: 5f 74 61 67 20 73 6d 61 6c 6c 69 6e 74 20 4e 4f _tag smallint NO
11f0: 54 20 4e 55 4c 4c 2c 0a 09 72 65 67 65 78 20 74 T NULL,..regex t
1200: 65 78 74 0a 29 3b 0a 0a 41 4c 54 45 52 20 54 41 ext.);..ALTER TA
1210: 42 4c 45 20 4f 4e 4c 59 20 75 72 6c 73 0a 09 41 BLE ONLY urls..A
1220: 44 44 20 43 4f 4e 53 54 52 41 49 4e 54 20 75 72 DD CONSTRAINT ur
1230: 6c 73 5f 70 6b 65 79 20 50 52 49 4d 41 52 59 20 ls_pkey PRIMARY
1240: 4b 45 59 20 28 64 61 74 65 5f 61 64 64 65 64 29 KEY (date_added)
1250: 3b 0a 0a 43 52 45 41 54 45 20 55 4e 49 51 55 45 ;..CREATE UNIQUE
1260: 20 49 4e 44 45 58 20 75 72 6c 73 5f 69 64 5f 73 INDEX urls_id_s
1270: 69 74 65 20 4f 4e 20 75 72 6c 73 20 55 53 49 4e ite ON urls USIN
1280: 47 20 62 74 72 65 65 20 28 69 64 5f 73 69 74 65 G btree (id_site
1290: 29 3b 0a 0a 43 52 45 41 54 45 20 55 4e 49 51 55 );..CREATE UNIQU
12a0: 45 20 49 4e 44 45 58 20 75 72 6c 73 5f 69 64 5f E INDEX urls_id_
12b0: 74 61 67 20 4f 4e 20 75 72 6c 73 20 55 53 49 4e tag ON urls USIN
12c0: 47 20 62 74 72 65 65 20 28 69 64 5f 74 61 67 29 G btree (id_tag)
12d0: 3b 0a 0a 2d 2d 20 72 75 6c 65 20 74 6f 20 6a 6f ;..-- rule to jo
12e0: 69 6e 20 61 6c 6c 20 74 61 62 6c 65 73 20 69 6e in all tables in
12f0: 74 6f 20 6f 6e 65 20 74 6f 20 73 69 6d 70 6c 69 to one to simpli
1300: 66 79 20 61 63 63 65 73 73 0a 2d 2d 20 61 75 74 fy access.-- aut
1310: 6f 6d 61 74 69 63 61 6c 6c 20 75 73 65 73 20 63 omaticall uses c
1320: 75 72 72 65 6e 74 20 64 61 79 20 61 6e 64 20 74 urrent day and t
1330: 69 6d 65 20 64 61 74 61 0a 43 52 45 41 54 45 20 ime data.CREATE
1340: 56 49 45 57 20 73 69 74 65 5f 72 75 6c 65 20 41 VIEW site_rule A
1350: 53 0a 53 45 4c 45 43 54 20 61 2e 72 65 64 69 72 S.SELECT a.redir
1360: 65 63 74 5f 75 72 6c 2c 20 61 2e 6e 65 74 6d 61 ect_url, a.netma
1370: 73 6b 2c 20 62 2e 73 69 74 65 2c 20 62 2e 72 65 sk, b.site, b.re
1380: 67 65 78 70 0a 46 52 4f 4d 20 28 28 0a 09 53 45 gexp.FROM ((..SE
1390: 4c 45 43 54 20 72 75 6c 65 73 2e 72 65 64 69 72 LECT rules.redir
13a0: 65 63 74 5f 75 72 6c 2c 20 74 61 67 2e 74 61 67 ect_url, tag.tag
13b0: 20 41 53 20 72 75 6c 65 5f 74 61 67 2c 20 72 75 AS rule_tag, ru
13c0: 6c 65 73 2e 6e 65 74 6d 61 73 6b 0a 09 46 52 4f les.netmask..FRO
13d0: 4d 20 72 75 6c 65 73 20 4e 41 54 55 52 41 4c 20 M rules NATURAL
13e0: 4a 4f 49 4e 20 74 61 67 0a 09 57 48 45 52 45 20 JOIN tag..WHERE
13f0: 28 27 6e 6f 77 27 3a 3a 74 65 78 74 29 3a 3a 74 ('now'::text)::t
1400: 69 6d 65 20 77 69 74 68 6f 75 74 20 74 69 6d 65 ime without time
1410: 20 7a 6f 6e 65 20 3e 3d 20 72 75 6c 65 73 2e 66 zone >= rules.f
1420: 72 6f 6d 5f 74 69 6d 65 0a 09 09 41 4e 44 20 28 rom_time...AND (
1430: 27 6e 6f 77 27 3a 3a 74 65 78 74 29 3a 3a 74 69 'now'::text)::ti
1440: 6d 65 20 77 69 74 68 6f 75 74 20 74 69 6d 65 20 me without time
1450: 7a 6f 6e 65 20 3c 3d 20 72 75 6c 65 73 2e 74 6f zone <= rules.to
1460: 5f 74 69 6d 65 0a 09 09 41 4e 44 20 64 61 74 65 _time...AND date
1470: 5f 70 61 72 74 28 27 64 6f 77 27 3a 3a 74 65 78 _part('dow'::tex
1480: 74 2c 20 6e 6f 77 28 29 29 20 3e 3d 20 28 72 75 t, now()) >= (ru
1490: 6c 65 73 2e 66 72 6f 6d 5f 77 65 65 6b 64 61 79 les.from_weekday
14a0: 29 3a 3a 64 6f 75 62 6c 65 20 70 72 65 63 69 73 )::double precis
14b0: 69 6f 6e 0a 09 09 41 4e 44 20 64 61 74 65 5f 70 ion...AND date_p
14c0: 61 72 74 28 27 64 6f 77 27 3a 3a 74 65 78 74 2c art('dow'::text,
14d0: 20 6e 6f 77 28 29 29 20 3c 3d 20 28 72 75 6c 65 now()) <= (rule
14e0: 73 2e 74 6f 5f 77 65 65 6b 64 61 79 29 3a 3a 64 s.to_weekday)::d
14f0: 6f 75 62 6c 65 20 70 72 65 63 69 73 69 6f 6e 0a ouble precision.
1500: 29 20 61 20 4a 4f 49 4e 20 28 0a 09 53 45 4c 45 ) a JOIN (..SELE
1510: 43 54 20 73 69 74 65 2e 73 69 74 65 2c 20 74 61 CT site.site, ta
1520: 67 2e 74 61 67 20 41 53 20 75 72 6c 5f 74 61 67 g.tag AS url_tag
1530: 2c 20 72 65 67 65 78 70 0a 09 46 52 4f 4d 20 75 , regexp..FROM u
1540: 72 6c 73 20 4e 41 54 55 52 41 4c 20 4a 4f 49 4e rls NATURAL JOIN
1550: 20 74 61 67 20 4e 41 54 55 52 41 4c 20 4a 4f 49 tag NATURAL JOI
1560: 4e 20 73 69 74 65 0a 29 20 62 20 4f 4e 20 28 62 N site.) b ON (b
1570: 2e 75 72 6c 5f 74 61 67 20 26 26 20 61 2e 72 75 .url_tag && a.ru
1580: 6c 65 5f 74 61 67 29 29 3b 0a le_tag));.