0000: 2d 2d 20 67 65 6e 65 72 61 6c 20 64 61 74 61 62 -- general datab
0010: 61 73 65 20 73 74 72 75 63 74 75 72 65 0a 2d 2d ase structure.--
0020: 20 74 61 62 6c 65 20 74 6f 20 68 6f 6c 64 20 61 table to hold a
0030: 6c 6c 20 72 75 6c 65 73 0a 43 52 45 41 54 45 20 ll rules.CREATE
0040: 54 41 42 4c 45 20 72 75 6c 65 73 20 28 0a 09 6e TABLE rules (..n
0050: 65 74 6d 61 73 6b 20 63 69 64 72 20 4e 4f 54 20 etmask cidr NOT
0060: 4e 55 4c 4c 2c 0a 09 72 65 64 69 72 65 63 74 5f NULL,..redirect_
0070: 75 72 6c 20 74 65 78 74 20 44 45 46 41 55 4c 54 url text DEFAULT
0080: 20 27 61 62 6f 75 74 3a 3a 62 6c 61 6e 6b 27 3a 'about::blank':
0090: 3a 74 65 78 74 20 4e 4f 54 20 4e 55 4c 4c 2c 0a :text NOT NULL,.
00a0: 09 66 72 6f 6d 5f 77 65 65 6b 64 61 79 20 73 6d .from_weekday sm
00b0: 61 6c 6c 69 6e 74 20 44 45 46 41 55 4c 54 20 30 allint DEFAULT 0
00c0: 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 74 6f 5f 77 NOT NULL,..to_w
00d0: 65 65 6b 64 61 79 20 73 6d 61 6c 6c 69 6e 74 20 eekday smallint
00e0: 44 45 46 41 55 4c 54 20 36 20 4e 4f 54 20 4e 55 DEFAULT 6 NOT NU
00f0: 4c 4c 2c 0a 09 66 72 6f 6d 5f 74 69 6d 65 20 74 LL,..from_time t
0100: 69 6d 65 20 77 69 74 68 6f 75 74 20 74 69 6d 65 ime without time
0110: 20 7a 6f 6e 65 20 44 45 46 41 55 4c 54 20 27 30 zone DEFAULT '0
0120: 30 3a 30 30 3a 30 30 27 3a 3a 74 69 6d 65 20 77 0:00:00'::time w
0130: 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e 65 ithout time zone
0140: 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 74 6f 5f 74 NOT NULL,..to_t
0150: 69 6d 65 20 74 69 6d 65 20 77 69 74 68 6f 75 74 ime time without
0160: 20 74 69 6d 65 20 7a 6f 6e 65 20 44 45 46 41 55 time zone DEFAU
0170: 4c 54 20 27 32 33 3a 35 39 3a 35 39 27 3a 3a 74 LT '23:59:59'::t
0180: 69 6d 65 20 77 69 74 68 6f 75 74 20 74 69 6d 65 ime without time
0190: 20 7a 6f 6e 65 20 4e 4f 54 20 4e 55 4c 4c 2c 0a zone NOT NULL,.
01a0: 09 69 64 5f 74 61 67 20 73 6d 61 6c 6c 69 6e 74 .id_tag smallint
01b0: 20 4e 4f 54 20 4e 55 4c 4c 0a 29 3b 0a 0a 41 4c NOT NULL.);..AL
01c0: 54 45 52 20 54 41 42 4c 45 20 4f 4e 4c 59 20 72 TER TABLE ONLY r
01d0: 75 6c 65 73 0a 09 41 44 44 20 43 4f 4e 53 54 52 ules..ADD CONSTR
01e0: 41 49 4e 54 20 72 75 6c 65 73 5f 74 61 67 5f 66 AINT rules_tag_f
01f0: 20 46 4f 52 45 49 47 4e 20 4b 45 59 20 28 69 64 FOREIGN KEY (id
0200: 5f 74 61 67 29 20 52 45 46 45 52 45 4e 43 45 53 _tag) REFERENCES
0210: 20 74 61 67 28 69 64 5f 74 61 67 29 20 4d 41 54 tag(id_tag) MAT
0220: 43 48 20 46 55 4c 4c 0a 09 4f 4e 20 55 50 44 41 CH FULL..ON UPDA
0230: 54 45 20 52 45 53 54 52 49 43 54 20 4f 4e 20 44 TE RESTRICT ON D
0240: 45 4c 45 54 45 20 52 45 53 54 52 49 43 54 20 44 ELETE RESTRICT D
0250: 45 46 45 52 52 41 42 4c 45 20 49 4e 49 54 49 41 EFERRABLE INITIA
0260: 4c 4c 59 20 44 45 46 45 52 52 45 44 3b 0a 0a 2d LLY DEFERRED;..-
0270: 2d 20 74 61 62 6c 65 20 74 6f 20 68 6f 6c 64 20 - table to hold
0280: 73 69 74 65 20 61 72 72 61 79 73 0a 43 52 45 41 site arrays.CREA
0290: 54 45 20 54 41 42 4c 45 20 73 69 74 65 20 28 0a TE TABLE site (.
02a0: 09 69 64 5f 73 69 74 65 20 73 65 72 69 61 6c 2c .id_site serial,
02b0: 0a 09 73 69 74 65 20 74 65 78 74 5b 5d 20 4e 4f ..site text[] NO
02c0: 54 20 4e 55 4c 4c 0a 29 3b 0a 0a 41 4c 54 45 52 T NULL.);..ALTER
02d0: 20 54 41 42 4c 45 20 4f 4e 4c 59 20 73 69 74 65 TABLE ONLY site
02e0: 0a 09 41 44 44 20 43 4f 4e 53 54 52 41 49 4e 54 ..ADD CONSTRAINT
02f0: 20 73 69 74 65 5f 70 6b 65 79 20 50 52 49 4d 41 site_pkey PRIMA
0300: 52 59 20 4b 45 59 20 28 69 64 5f 73 69 74 65 29 RY KEY (id_site)
0310: 3b 0a 0a 43 52 45 41 54 45 20 55 4e 49 51 55 45 ;..CREATE UNIQUE
0320: 20 49 4e 44 45 58 20 73 69 74 65 5f 75 20 4f 4e INDEX site_u ON
0330: 20 73 69 74 65 20 28 75 73 6f 72 74 28 73 69 74 site (usort(sit
0340: 65 29 29 3b 0a 0a 43 52 45 41 54 45 20 49 4e 44 e));..CREATE IND
0350: 45 58 20 73 69 74 65 5f 67 20 4f 4e 20 73 69 74 EX site_g ON sit
0360: 65 20 55 53 49 4e 47 20 67 69 6e 20 28 73 69 74 e USING gin (sit
0370: 65 29 3b 0a 0a 2d 2d 20 74 61 62 6c 65 20 74 6f e);..-- table to
0380: 20 68 6f 6c 64 20 74 61 67 20 63 6f 6d 62 69 6e hold tag combin
0390: 61 74 69 6f 6e 73 0a 43 52 45 41 54 45 20 54 41 ations.CREATE TA
03a0: 42 4c 45 20 74 61 67 20 28 0a 09 69 64 5f 74 61 BLE tag (..id_ta
03b0: 67 20 73 65 72 69 61 6c 2c 0a 09 74 61 67 20 74 g serial,..tag t
03c0: 65 78 74 5b 5d 20 4e 4f 54 20 4e 55 4c 4c 0a 29 ext[] NOT NULL.)
03d0: 3b 0a 0a 41 4c 54 45 52 20 54 41 42 4c 45 20 4f ;..ALTER TABLE O
03e0: 4e 4c 59 20 74 61 67 0a 09 41 44 44 20 43 4f 4e NLY tag..ADD CON
03f0: 53 54 52 41 49 4e 54 20 74 61 67 5f 70 6b 65 79 STRAINT tag_pkey
0400: 20 50 52 49 4d 41 52 59 20 4b 45 59 20 28 69 64 PRIMARY KEY (id
0410: 5f 74 61 67 29 3b 0a 0a 43 52 45 41 54 45 20 55 _tag);..CREATE U
0420: 4e 49 51 55 45 20 49 4e 44 45 58 20 74 61 67 5f NIQUE INDEX tag_
0430: 75 20 4f 4e 20 74 61 67 20 28 75 73 6f 72 74 28 u ON tag (usort(
0440: 74 61 67 29 29 3b 0a 0a 43 52 45 41 54 45 20 49 tag));..CREATE I
0450: 4e 44 45 58 20 74 61 67 5f 67 20 4f 4e 20 74 61 NDEX tag_g ON ta
0460: 67 20 55 53 49 4e 47 20 67 69 6e 20 28 74 61 67 g USING gin (tag
0470: 29 3b 0a 0a 2d 2d 20 74 61 62 6c 65 20 74 6f 20 );..-- table to
0480: 68 6f 6c 64 20 74 61 67 20 2d 20 73 69 74 65 20 hold tag - site
0490: 6c 69 6e 6b 73 0a 43 52 45 41 54 45 20 54 41 42 links.CREATE TAB
04a0: 4c 45 20 75 72 6c 73 20 28 0a 09 64 61 74 65 5f LE urls (..date_
04b0: 61 64 64 65 64 20 74 69 6d 65 73 74 61 6d 70 20 added timestamp
04c0: 77 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e without time zon
04d0: 65 20 44 45 46 41 55 4c 54 20 28 27 6e 6f 77 27 e DEFAULT ('now'
04e0: 3a 3a 74 65 78 74 29 3a 3a 74 69 6d 65 73 74 61 ::text)::timesta
04f0: 6d 70 28 30 29 20 77 69 74 68 6f 75 74 20 74 69 mp(0) without ti
0500: 6d 65 20 7a 6f 6e 65 20 4e 4f 54 20 4e 55 4c 4c me zone NOT NULL
0510: 2c 0a 09 69 64 5f 73 69 74 65 20 69 6e 74 65 67 ,..id_site integ
0520: 65 72 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 69 64 er NOT NULL,..id
0530: 5f 74 61 67 20 73 6d 61 6c 6c 69 6e 74 20 4e 4f _tag smallint NO
0540: 54 20 4e 55 4c 4c 2c 0a 09 72 65 67 65 78 70 20 T NULL,..regexp
0550: 74 65 78 74 0a 29 3b 0a 0a 43 52 45 41 54 45 20 text.);..CREATE
0560: 55 4e 49 51 55 45 20 49 4e 44 45 58 20 75 72 6c UNIQUE INDEX url
0570: 73 5f 70 6b 65 79 20 4f 4e 20 75 72 6c 73 20 55 s_pkey ON urls U
0580: 53 49 4e 47 20 62 74 72 65 65 20 28 69 64 5f 73 SING btree (id_s
0590: 69 74 65 2c 20 72 65 67 65 78 70 29 3b 0a 0a 43 ite, regexp);..C
05a0: 52 45 41 54 45 20 49 4e 44 45 58 20 75 72 6c 73 REATE INDEX urls
05b0: 5f 69 64 5f 74 61 67 20 4f 4e 20 75 72 6c 73 20 _id_tag ON urls
05c0: 55 53 49 4e 47 20 62 74 72 65 65 20 28 69 64 5f USING btree (id_
05d0: 74 61 67 29 3b 0a 0a 41 4c 54 45 52 20 54 41 42 tag);..ALTER TAB
05e0: 4c 45 20 4f 4e 4c 59 20 75 72 6c 73 0a 09 41 44 LE ONLY urls..AD
05f0: 44 20 43 4f 4e 53 54 52 41 49 4e 54 20 75 72 6c D CONSTRAINT url
0600: 73 5f 73 69 74 65 5f 66 20 46 4f 52 45 49 47 4e s_site_f FOREIGN
0610: 20 4b 45 59 20 28 69 64 5f 73 69 74 65 29 20 52 KEY (id_site) R
0620: 45 46 45 52 45 4e 43 45 53 20 73 69 74 65 28 69 EFERENCES site(i
0630: 64 5f 73 69 74 65 29 20 4d 41 54 43 48 20 46 55 d_site) MATCH FU
0640: 4c 4c 0a 09 4f 4e 20 55 50 44 41 54 45 20 52 45 LL..ON UPDATE RE
0650: 53 54 52 49 43 54 20 4f 4e 20 44 45 4c 45 54 45 STRICT ON DELETE
0660: 20 52 45 53 54 52 49 43 54 20 44 45 46 45 52 52 RESTRICT DEFERR
0670: 41 42 4c 45 20 49 4e 49 54 49 41 4c 4c 59 20 44 ABLE INITIALLY D
0680: 45 46 45 52 52 45 44 3b 0a 0a 41 4c 54 45 52 20 EFERRED;..ALTER
0690: 54 41 42 4c 45 20 4f 4e 4c 59 20 75 72 6c 73 0a TABLE ONLY urls.
06a0: 09 41 44 44 20 43 4f 4e 53 54 52 41 49 4e 54 20 .ADD CONSTRAINT
06b0: 75 72 6c 73 5f 74 61 67 5f 66 20 46 4f 52 45 49 urls_tag_f FOREI
06c0: 47 4e 20 4b 45 59 20 28 69 64 5f 74 61 67 29 20 GN KEY (id_tag)
06d0: 52 45 46 45 52 45 4e 43 45 53 20 74 61 67 28 69 REFERENCES tag(i
06e0: 64 5f 74 61 67 29 20 4d 41 54 43 48 20 46 55 4c d_tag) MATCH FUL
06f0: 4c 0a 09 4f 4e 20 55 50 44 41 54 45 20 52 45 53 L..ON UPDATE RES
0700: 54 52 49 43 54 20 4f 4e 20 44 45 4c 45 54 45 20 TRICT ON DELETE
0710: 52 45 53 54 52 49 43 54 20 44 45 46 45 52 52 41 RESTRICT DEFERRA
0720: 42 4c 45 20 49 4e 49 54 49 41 4c 4c 59 20 44 45 BLE INITIALLY DE
0730: 46 45 52 52 45 44 3b 0a 0a 2d 2d 20 72 75 6c 65 FERRED;..-- rule
0740: 20 74 6f 20 6a 6f 69 6e 20 61 6c 6c 20 74 61 62 to join all tab
0750: 6c 65 73 20 69 6e 74 6f 20 6f 6e 65 20 74 6f 20 les into one to
0760: 73 69 6d 70 6c 69 66 79 20 61 63 63 65 73 73 0a simplify access.
0770: 2d 2d 20 61 75 74 6f 6d 61 74 69 63 61 6c 6c 20 -- automaticall
0780: 75 73 65 73 20 63 75 72 72 65 6e 74 20 64 61 79 uses current day
0790: 20 61 6e 64 20 74 69 6d 65 20 64 61 74 61 0a 43 and time data.C
07a0: 52 45 41 54 45 20 56 49 45 57 20 73 69 74 65 5f REATE VIEW site_
07b0: 72 75 6c 65 20 41 53 0a 53 45 4c 45 43 54 20 61 rule AS.SELECT a
07c0: 2e 72 65 64 69 72 65 63 74 5f 75 72 6c 2c 20 61 .redirect_url, a
07d0: 2e 6e 65 74 6d 61 73 6b 2c 20 62 2e 73 69 74 65 .netmask, b.site
07e0: 2c 20 62 2e 72 65 67 65 78 70 0a 46 52 4f 4d 20 , b.regexp.FROM
07f0: 28 28 0a 09 53 45 4c 45 43 54 20 72 75 6c 65 73 ((..SELECT rules
0800: 2e 72 65 64 69 72 65 63 74 5f 75 72 6c 2c 20 74 .redirect_url, t
0810: 61 67 2e 74 61 67 20 41 53 20 72 75 6c 65 5f 74 ag.tag AS rule_t
0820: 61 67 2c 20 72 75 6c 65 73 2e 6e 65 74 6d 61 73 ag, rules.netmas
0830: 6b 0a 09 46 52 4f 4d 20 72 75 6c 65 73 20 4e 41 k..FROM rules NA
0840: 54 55 52 41 4c 20 4a 4f 49 4e 20 74 61 67 0a 09 TURAL JOIN tag..
0850: 57 48 45 52 45 20 28 27 6e 6f 77 27 3a 3a 74 65 WHERE ('now'::te
0860: 78 74 29 3a 3a 74 69 6d 65 20 77 69 74 68 6f 75 xt)::time withou
0870: 74 20 74 69 6d 65 20 7a 6f 6e 65 20 3e 3d 20 72 t time zone >= r
0880: 75 6c 65 73 2e 66 72 6f 6d 5f 74 69 6d 65 0a 09 ules.from_time..
0890: 09 41 4e 44 20 28 27 6e 6f 77 27 3a 3a 74 65 78 .AND ('now'::tex
08a0: 74 29 3a 3a 74 69 6d 65 20 77 69 74 68 6f 75 74 t)::time without
08b0: 20 74 69 6d 65 20 7a 6f 6e 65 20 3c 3d 20 72 75 time zone <= ru
08c0: 6c 65 73 2e 74 6f 5f 74 69 6d 65 0a 09 09 41 4e les.to_time...AN
08d0: 44 20 64 61 74 65 5f 70 61 72 74 28 27 64 6f 77 D date_part('dow
08e0: 27 3a 3a 74 65 78 74 2c 20 6e 6f 77 28 29 29 20 '::text, now())
08f0: 3e 3d 20 28 72 75 6c 65 73 2e 66 72 6f 6d 5f 77 >= (rules.from_w
0900: 65 65 6b 64 61 79 29 3a 3a 64 6f 75 62 6c 65 20 eekday)::double
0910: 70 72 65 63 69 73 69 6f 6e 0a 09 09 41 4e 44 20 precision...AND
0920: 64 61 74 65 5f 70 61 72 74 28 27 64 6f 77 27 3a date_part('dow':
0930: 3a 74 65 78 74 2c 20 6e 6f 77 28 29 29 20 3c 3d :text, now()) <=
0940: 20 28 72 75 6c 65 73 2e 74 6f 5f 77 65 65 6b 64 (rules.to_weekd
0950: 61 79 29 3a 3a 64 6f 75 62 6c 65 20 70 72 65 63 ay)::double prec
0960: 69 73 69 6f 6e 0a 29 20 61 20 4a 4f 49 4e 20 28 ision.) a JOIN (
0970: 0a 09 53 45 4c 45 43 54 20 73 69 74 65 2e 73 69 ..SELECT site.si
0980: 74 65 2c 20 74 61 67 2e 74 61 67 20 41 53 20 75 te, tag.tag AS u
0990: 72 6c 5f 74 61 67 2c 20 72 65 67 65 78 70 0a 09 rl_tag, regexp..
09a0: 46 52 4f 4d 20 75 72 6c 73 20 4e 41 54 55 52 41 FROM urls NATURA
09b0: 4c 20 4a 4f 49 4e 20 74 61 67 20 4e 41 54 55 52 L JOIN tag NATUR
09c0: 41 4c 20 4a 4f 49 4e 20 73 69 74 65 0a 29 20 62 AL JOIN site.) b
09d0: 20 4f 4e 20 28 62 2e 75 72 6c 5f 74 61 67 20 26 ON (b.url_tag &
09e0: 26 20 61 2e 72 75 6c 65 5f 74 61 67 29 29 3b 0a & a.rule_tag));.
09f0: 0a 43 52 45 41 54 45 20 50 52 4f 43 45 44 55 52 .CREATE PROCEDUR
0a00: 41 4c 20 4c 41 4e 47 55 41 47 45 20 70 6c 70 67 AL LANGUAGE plpg
0a10: 73 71 6c 3b 0a 0a 2d 2d 20 67 65 6e 65 72 61 6c sql;..-- general
0a20: 20 61 72 72 61 79 20 73 6f 72 74 69 6e 67 20 61 array sorting a
0a30: 6e 64 20 64 6f 6d 61 69 6e 20 70 72 6f 63 65 73 nd domain proces
0a40: 73 69 6e 67 20 66 75 6e 63 74 69 6f 6e 73 0a 2d sing functions.-
0a50: 2d 20 73 6f 72 74 73 20 61 72 72 61 79 0a 43 52 - sorts array.CR
0a60: 45 41 54 45 20 6f 72 20 72 65 70 6c 61 63 65 20 EATE or replace
0a70: 46 55 4e 43 54 49 4f 4e 20 73 6f 72 74 28 6f 72 FUNCTION sort(or
0a80: 69 67 69 6e 61 6c 20 61 6e 79 61 72 72 61 79 29 iginal anyarray)
0a90: 20 52 45 54 55 52 4e 53 20 61 6e 79 61 72 72 61 RETURNS anyarra
0aa0: 79 0a 09 4c 41 4e 47 55 41 47 45 20 73 71 6c 20 y..LANGUAGE sql
0ab0: 49 4d 4d 55 54 41 42 4c 45 20 53 54 52 49 43 54 IMMUTABLE STRICT
0ac0: 0a 09 41 53 20 24 5f 24 0a 73 65 6c 65 63 74 20 ..AS $_$.select
0ad0: 61 72 72 61 79 5f 61 67 67 28 69 74 65 6d 29 20 array_agg(item)
0ae0: 61 73 20 72 65 73 75 6c 74 20 66 72 6f 6d 20 28 as result from (
0af0: 73 65 6c 65 63 74 20 75 6e 6e 65 73 74 28 24 31 select unnest($1
0b00: 29 20 61 73 20 69 74 65 6d 20 6f 72 64 65 72 20 ) as item order
0b10: 62 79 20 69 74 65 6d 29 20 61 3b 0a 24 5f 24 3b by item) a;.$_$;
0b20: 0a 0a 2d 2d 20 73 6f 72 74 73 20 61 72 72 61 79 ..-- sorts array
0b30: 20 61 6e 64 20 72 65 6d 6f 76 65 73 20 64 75 70 and removes dup
0b40: 6c 69 63 61 74 65 73 0a 43 52 45 41 54 45 20 6f licates.CREATE o
0b50: 72 20 72 65 70 6c 61 63 65 20 46 55 4e 43 54 49 r replace FUNCTI
0b60: 4f 4e 20 75 73 6f 72 74 28 6f 72 69 67 69 6e 61 ON usort(origina
0b70: 6c 20 61 6e 79 61 72 72 61 79 29 20 52 45 54 55 l anyarray) RETU
0b80: 52 4e 53 20 61 6e 79 61 72 72 61 79 0a 09 4c 41 RNS anyarray..LA
0b90: 4e 47 55 41 47 45 20 73 71 6c 20 49 4d 4d 55 54 NGUAGE sql IMMUT
0ba0: 41 42 4c 45 20 53 54 52 49 43 54 0a 09 41 53 20 ABLE STRICT..AS
0bb0: 24 5f 24 0a 73 65 6c 65 63 74 20 61 72 72 61 79 $_$.select array
0bc0: 5f 61 67 67 28 69 74 65 6d 29 20 61 73 20 72 65 _agg(item) as re
0bd0: 73 75 6c 74 20 66 72 6f 6d 20 28 73 65 6c 65 63 sult from (selec
0be0: 74 20 64 69 73 74 69 6e 63 74 20 75 6e 6e 65 73 t distinct unnes
0bf0: 74 28 24 31 29 20 61 73 20 69 74 65 6d 20 6f 72 t($1) as item or
0c00: 64 65 72 20 62 79 20 69 74 65 6d 29 20 61 3b 0a der by item) a;.
0c10: 24 5f 24 3b 0a 0a 2d 2d 20 74 72 61 6e 73 66 6f $_$;..-- transfo
0c20: 72 6d 73 20 64 6f 6d 61 69 6e 20 69 6e 74 6f 20 rms domain into
0c30: 6f 72 64 65 72 65 64 20 61 72 72 61 79 20 66 6f ordered array fo
0c40: 72 20 69 6e 64 65 78 69 6e 67 0a 43 52 45 41 54 r indexing.CREAT
0c50: 45 20 6f 72 20 72 65 70 6c 61 63 65 20 46 55 4e E or replace FUN
0c60: 43 54 49 4f 4e 20 74 72 69 70 64 6f 6d 61 69 6e CTION tripdomain
0c70: 28 75 72 6c 20 74 65 78 74 29 20 52 45 54 55 52 (url text) RETUR
0c80: 4e 53 20 74 65 78 74 5b 5d 0a 09 4c 41 4e 47 55 NS text[]..LANGU
0c90: 41 47 45 20 70 6c 70 67 73 71 6c 20 49 4d 4d 55 AGE plpgsql IMMU
0ca0: 54 41 42 4c 45 20 53 54 52 49 43 54 0a 09 41 53 TABLE STRICT..AS
0cb0: 20 24 5f 24 0a 64 65 63 6c 61 72 65 0a 09 72 65 $_$.declare..re
0cc0: 73 75 6c 74 20 74 65 78 74 5b 5d 3b 0a 09 73 70 sult text[];..sp
0cd0: 6c 69 74 74 65 64 20 74 65 78 74 5b 5d 3b 0a 09 litted text[];..
0ce0: 78 20 69 6e 74 65 67 65 72 3b 0a 09 6c 65 6e 67 x integer;..leng
0cf0: 74 68 20 69 6e 74 65 67 65 72 3b 0a 62 65 67 69 th integer;.begi
0d00: 6e 0a 09 73 70 6c 69 74 74 65 64 20 3a 3d 20 73 n..splitted := s
0d10: 74 72 69 6e 67 5f 74 6f 5f 61 72 72 61 79 28 24 tring_to_array($
0d20: 31 2c 20 27 2e 27 29 3b 0a 09 6c 65 6e 67 74 68 1, '.');..length
0d30: 20 3a 3d 20 61 72 72 61 79 5f 6c 65 6e 67 74 68 := array_length
0d40: 28 73 70 6c 69 74 74 65 64 2c 20 31 29 3b 0a 09 (splitted, 1);..
0d50: 78 20 3a 3d 20 31 3b 0a 09 6c 6f 6f 70 0a 09 09 x := 1;..loop...
0d60: 65 78 69 74 20 77 68 65 6e 20 73 70 6c 69 74 74 exit when splitt
0d70: 65 64 5b 78 5d 20 69 73 20 6e 75 6c 6c 3b 0a 09 ed[x] is null;..
0d80: 09 72 65 73 75 6c 74 5b 78 5d 20 3a 3d 20 73 70 .result[x] := sp
0d90: 6c 69 74 74 65 64 5b 78 5d 20 7c 7c 20 27 3a 27 litted[x] || ':'
0da0: 20 7c 7c 20 6c 65 6e 67 74 68 20 2d 20 78 3b 0a || length - x;.
0db0: 09 09 78 20 3a 3d 20 78 20 2b 20 31 3b 0a 09 65 ..x := x + 1;..e
0dc0: 6e 64 20 6c 6f 6f 70 3b 0a 09 72 65 74 75 72 6e nd loop;..return
0dd0: 20 72 65 73 75 6c 74 3b 0a 65 6e 64 3b 24 5f 24 result;.end;$_$
0de0: 3b 0a 0a 2d 2d 20 74 72 61 6e 73 66 6f 72 6d 73 ;..-- transforms
0df0: 20 6f 72 64 65 72 65 64 20 61 72 72 61 79 20 69 ordered array i
0e00: 6e 74 6f 20 64 6f 6d 61 69 6e 0a 63 72 65 61 74 nto domain.creat
0e10: 65 20 6f 72 20 72 65 70 6c 61 63 65 20 66 75 6e e or replace fun
0e20: 63 74 69 6f 6e 20 75 6e 74 72 69 70 28 73 69 74 ction untrip(sit
0e30: 65 20 74 65 78 74 5b 5d 29 20 72 65 74 75 72 6e e text[]) return
0e40: 73 20 74 65 78 74 0a 09 6c 61 6e 67 75 61 67 65 s text..language
0e50: 20 70 6c 70 67 73 71 6c 20 69 6d 6d 75 74 61 62 plpgsql immutab
0e60: 6c 65 20 73 74 72 69 63 74 0a 09 61 73 20 24 5f le strict..as $_
0e70: 24 0a 64 65 63 6c 61 72 65 0a 09 78 20 69 6e 74 $.declare..x int
0e80: 65 67 65 72 3b 0a 09 73 70 6c 69 74 74 65 64 20 eger;..splitted
0e90: 74 65 78 74 5b 5d 3b 0a 09 70 61 69 72 20 74 65 text[];..pair te
0ea0: 78 74 5b 5d 3b 0a 62 65 67 69 6e 0a 09 78 20 3a xt[];.begin..x :
0eb0: 3d 20 61 72 72 61 79 5f 6c 65 6e 67 74 68 28 73 = array_length(s
0ec0: 69 74 65 2c 20 31 29 3b 0a 09 6c 6f 6f 70 20 0a ite, 1);..loop .
0ed0: 09 09 65 78 69 74 20 77 68 65 6e 20 73 69 74 65 ..exit when site
0ee0: 5b 78 5d 20 69 73 20 6e 75 6c 6c 3b 0a 09 09 70 [x] is null;...p
0ef0: 61 69 72 20 3a 3d 20 73 74 72 69 6e 67 5f 74 6f air := string_to
0f00: 5f 61 72 72 61 79 28 73 69 74 65 5b 78 5d 2c 20 _array(site[x],
0f10: 27 3a 27 29 3b 0a 09 09 73 70 6c 69 74 74 65 64 ':');...splitted
0f20: 5b 30 20 2d 20 70 61 69 72 5b 32 5d 3a 3a 69 6e [0 - pair[2]::in
0f30: 74 65 67 65 72 5d 20 3a 3d 20 70 61 69 72 5b 31 teger] := pair[1
0f40: 5d 3b 0a 09 09 78 20 3a 3d 20 78 20 2d 20 31 3b ];...x := x - 1;
0f50: 0a 09 65 6e 64 20 6c 6f 6f 70 3b 0a 09 72 65 74 ..end loop;..ret
0f60: 75 72 6e 20 61 72 72 61 79 5f 74 6f 5f 73 74 72 urn array_to_str
0f70: 69 6e 67 28 73 70 6c 69 74 74 65 64 2c 20 27 2e ing(splitted, '.
0f80: 27 29 3b 0a 65 6e 64 3b 0a 24 5f 24 3b 0a 0a 2d ');.end;.$_$;..-
0f90: 2d 20 66 75 6e 63 74 69 6f 6e 73 20 74 68 61 74 - functions that
0fa0: 20 77 6f 72 6b 73 20 77 69 74 68 20 74 61 62 6c works with tabl
0fb0: 65 73 0a 2d 2d 20 74 68 69 73 20 66 75 6e 63 74 es.-- this funct
0fc0: 69 6f 6e 73 20 72 65 74 75 72 6e 73 20 69 64 20 ions returns id
0fd0: 6f 66 20 73 69 74 65 0a 63 72 65 61 74 65 20 6f of site.create o
0fe0: 72 20 72 65 70 6c 61 63 65 20 66 75 6e 63 74 69 r replace functi
0ff0: 6f 6e 20 67 65 74 5f 73 69 74 65 28 6d 79 5f 73 on get_site(my_s
1000: 69 74 65 20 74 65 78 74 5b 5d 29 20 72 65 74 75 ite text[]) retu
1010: 72 6e 73 20 69 6e 74 65 67 65 72 0a 09 6c 61 6e rns integer..lan
1020: 67 75 61 67 65 20 70 6c 70 67 73 71 6c 20 73 74 guage plpgsql st
1030: 72 69 63 74 0a 09 61 73 20 24 24 0a 64 65 63 6c rict..as $$.decl
1040: 61 72 65 0a 09 73 69 74 65 5f 69 64 20 69 6e 74 are..site_id int
1050: 65 67 65 72 3b 0a 62 65 67 69 6e 0a 09 73 65 6c eger;.begin..sel
1060: 65 63 74 20 69 64 5f 73 69 74 65 20 66 72 6f 6d ect id_site from
1070: 20 73 69 74 65 20 77 68 65 72 65 20 6d 79 5f 73 site where my_s
1080: 69 74 65 20 3d 20 73 69 74 65 20 69 6e 74 6f 20 ite = site into
1090: 73 69 74 65 5f 69 64 3b 0a 09 69 66 20 6e 6f 74 site_id;..if not
10a0: 20 66 6f 75 6e 64 20 74 68 65 6e 0a 09 09 69 6e found then...in
10b0: 73 65 72 74 20 69 6e 74 6f 20 73 69 74 65 20 28 sert into site (
10c0: 73 69 74 65 29 20 76 61 6c 75 65 73 20 28 6d 79 site) values (my
10d0: 5f 73 69 74 65 29 3b 0a 09 09 73 65 6c 65 63 74 _site);...select
10e0: 20 69 64 5f 73 69 74 65 20 66 72 6f 6d 20 73 69 id_site from si
10f0: 74 65 20 77 68 65 72 65 20 6d 79 5f 73 69 74 65 te where my_site
1100: 20 3d 20 73 69 74 65 20 69 6e 74 6f 20 73 69 74 = site into sit
1110: 65 5f 69 64 3b 0a 09 65 6e 64 20 69 66 3b 0a 09 e_id;..end if;..
1120: 72 65 74 75 72 6e 20 73 69 74 65 5f 69 64 3b 0a return site_id;.
1130: 65 6e 64 3b 0a 24 24 3b 0a 0a 63 72 65 61 74 65 end;.$$;..create
1140: 20 6f 72 20 72 65 70 6c 61 63 65 20 66 75 6e 63 or replace func
1150: 74 69 6f 6e 20 67 65 74 5f 73 69 74 65 28 64 6f tion get_site(do
1160: 6d 61 69 6e 20 74 65 78 74 29 20 72 65 74 75 72 main text) retur
1170: 6e 73 20 69 6e 74 65 67 65 72 0a 09 6c 61 6e 67 ns integer..lang
1180: 75 61 67 65 20 73 71 6c 20 69 6d 6d 75 74 61 62 uage sql immutab
1190: 6c 65 20 73 74 72 69 63 74 0a 09 61 73 20 24 24 le strict..as $$
11a0: 0a 73 65 6c 65 63 74 20 67 65 74 5f 73 69 74 65 .select get_site
11b0: 28 74 72 69 70 64 6f 6d 61 69 6e 28 24 31 29 29 (tripdomain($1))
11c0: 20 61 73 20 72 65 73 75 6c 74 3b 0a 24 24 3b 0a as result;.$$;.
11d0: 0a 2d 2d 20 74 68 69 73 20 66 75 6e 63 74 69 6f .-- this functio
11e0: 6e 20 69 6e 73 65 72 74 73 20 6f 72 20 75 70 64 n inserts or upd
11f0: 61 74 65 73 20 72 65 63 6f 72 64 20 77 69 74 68 ates record with
1200: 20 74 61 67 73 20 74 6f 20 73 69 74 65 20 62 79 tags to site by
1210: 20 73 69 74 65 20 69 64 20 77 69 74 68 20 72 65 site id with re
1220: 67 65 78 70 0a 43 52 45 41 54 45 20 6f 72 20 72 gexp.CREATE or r
1230: 65 70 6c 61 63 65 20 46 55 4e 43 54 49 4f 4e 20 eplace FUNCTION
1240: 6d 61 72 6b 28 6d 79 5f 69 64 5f 73 69 74 65 20 mark(my_id_site
1250: 69 6e 74 65 67 65 72 2c 20 6d 79 5f 69 64 5f 74 integer, my_id_t
1260: 61 67 20 69 6e 74 65 67 65 72 2c 20 6d 79 5f 72 ag integer, my_r
1270: 65 67 65 78 70 20 74 65 78 74 29 20 52 45 54 55 egexp text) RETU
1280: 52 4e 53 20 69 6e 74 65 67 65 72 0a 09 4c 41 4e RNS integer..LAN
1290: 47 55 41 47 45 20 70 6c 70 67 73 71 6c 20 53 54 GUAGE plpgsql ST
12a0: 52 49 43 54 0a 09 41 53 20 24 24 0a 64 65 63 6c RICT..AS $$.decl
12b0: 61 72 65 0a 09 2d 2d 20 6d 61 79 62 65 20 63 68 are..-- maybe ch
12c0: 65 63 6b 20 73 68 6f 75 6c 64 20 62 65 20 61 64 eck should be ad
12d0: 64 65 64 20 74 6f 20 6d 61 6b 65 20 73 75 72 65 ded to make sure
12e0: 20 73 75 70 70 6c 69 65 64 20 73 69 74 65 20 69 supplied site i
12f0: 64 20 72 65 61 6c 6c 79 20 65 78 69 73 74 73 0a d really exists.
1300: 09 6d 79 5f 74 61 67 20 74 65 78 74 5b 5d 3b 0a .my_tag text[];.
1310: 62 65 67 69 6e 0a 09 2d 2d 20 73 65 6c 65 63 74 begin..-- select
1320: 69 6e 67 20 74 61 67 73 20 73 69 74 65 20 61 6c ing tags site al
1330: 72 65 61 64 79 20 68 61 76 65 20 61 6e 64 20 61 ready have and a
1340: 64 64 69 6e 67 20 6e 65 77 20 74 61 67 20 74 6f dding new tag to
1350: 20 74 68 65 6d 0a 09 2d 2d 20 6e 6f 74 65 20 74 them..-- note t
1360: 68 61 74 20 74 61 67 73 20 73 68 6f 75 6c 64 20 hat tags should
1370: 62 65 20 73 6f 72 74 65 64 20 74 6f 20 65 6c 69 be sorted to eli
1380: 6d 69 6e 61 74 65 20 70 65 72 6d 75 74 61 74 69 minate permutati
1390: 6f 6e 73 0a 09 73 65 6c 65 63 74 20 63 6f 61 6c ons..select coal
13a0: 65 73 63 65 28 74 61 67 2c 20 27 7b 7d 27 3a 3a esce(tag, '{}'::
13b0: 74 65 78 74 5b 5d 29 20 66 72 6f 6d 20 75 72 6c text[]) from url
13c0: 73 20 6e 61 74 75 72 61 6c 20 6c 65 66 74 20 6a s natural left j
13d0: 6f 69 6e 20 74 61 67 0a 09 09 77 68 65 72 65 20 oin tag...where
13e0: 69 64 5f 73 69 74 65 20 3d 20 6d 79 5f 69 64 5f id_site = my_id_
13f0: 73 69 74 65 20 61 6e 64 20 72 65 67 65 78 70 20 site and regexp
1400: 3d 20 6d 79 5f 72 65 67 65 78 70 20 69 6e 74 6f = my_regexp into
1410: 20 6d 79 5f 74 61 67 3b 0a 09 69 66 20 6e 6f 74 my_tag;..if not
1420: 20 66 6f 75 6e 64 20 74 68 65 6e 0a 09 09 2d 2d found then...--
1430: 20 6e 6f 20 72 65 63 6f 72 64 73 20 66 6f 75 6e no records foun
1440: 64 20 2d 20 63 72 65 61 74 69 6e 67 20 6e 65 77 d - creating new
1450: 20 74 61 67 0a 09 09 69 6e 73 65 72 74 20 69 6e tag...insert in
1460: 74 6f 20 75 72 6c 73 20 28 69 64 5f 73 69 74 65 to urls (id_site
1470: 2c 20 69 64 5f 74 61 67 2c 20 72 65 67 65 78 70 , id_tag, regexp
1480: 29 20 76 61 6c 75 65 73 20 28 6d 79 5f 69 64 5f ) values (my_id_
1490: 73 69 74 65 2c 20 6d 79 5f 69 64 5f 74 61 67 2c site, my_id_tag,
14a0: 20 6d 79 5f 72 65 67 65 78 70 29 3b 0a 09 65 6c my_regexp);..el
14b0: 73 65 0a 09 09 2d 2d 20 6a 6f 69 6e 69 6e 67 20 se...-- joining
14c0: 74 61 67 73 0a 09 09 73 65 6c 65 63 74 20 75 73 tags...select us
14d0: 6f 72 74 28 6d 79 5f 74 61 67 20 7c 7c 20 74 61 ort(my_tag || ta
14e0: 67 29 20 66 72 6f 6d 20 74 61 67 20 77 68 65 72 g) from tag wher
14f0: 65 20 69 64 5f 74 61 67 20 3d 20 6d 79 5f 69 64 e id_tag = my_id
1500: 5f 74 61 67 20 69 6e 74 6f 20 6d 79 5f 74 61 67 _tag into my_tag
1510: 3b 0a 09 09 2d 2d 20 75 70 64 61 74 69 6e 67 20 ;...-- updating
1520: 65 78 69 73 74 69 6e 67 20 72 65 63 6f 72 64 0a existing record.
1530: 09 09 75 70 64 61 74 65 20 75 72 6c 73 20 73 65 ..update urls se
1540: 74 20 69 64 5f 74 61 67 20 3d 20 67 65 74 5f 74 t id_tag = get_t
1550: 61 67 28 6d 79 5f 74 61 67 29 0a 09 09 09 77 68 ag(my_tag)....wh
1560: 65 72 65 20 69 64 5f 73 69 74 65 20 3d 20 6d 79 ere id_site = my
1570: 5f 69 64 5f 73 69 74 65 20 61 6e 64 20 72 65 67 _id_site and reg
1580: 65 78 70 20 3d 20 6d 79 5f 72 65 67 65 78 70 3b exp = my_regexp;
1590: 0a 09 65 6e 64 20 69 66 3b 0a 09 72 65 74 75 72 ..end if;..retur
15a0: 6e 20 6d 79 5f 69 64 5f 73 69 74 65 3b 0a 65 6e n my_id_site;.en
15b0: 64 3b 0a 24 24 3b 0a 0a 2d 2d 20 74 68 69 73 20 d;.$$;..-- this
15c0: 66 75 6e 63 74 69 6f 6e 20 61 64 64 73 20 74 61 function adds ta
15d0: 67 20 74 6f 20 73 69 74 65 20 62 79 20 73 69 74 g to site by sit
15e0: 65 20 69 64 0a 43 52 45 41 54 45 20 6f 72 20 72 e id.CREATE or r
15f0: 65 70 6c 61 63 65 20 46 55 4e 43 54 49 4f 4e 20 eplace FUNCTION
1600: 6d 61 72 6b 28 6d 79 5f 69 64 5f 73 69 74 65 20 mark(my_id_site
1610: 69 6e 74 65 67 65 72 2c 20 6e 65 77 5f 74 61 67 integer, new_tag
1620: 20 74 65 78 74 29 20 52 45 54 55 52 4e 53 20 69 text) RETURNS i
1630: 6e 74 65 67 65 72 0a 09 4c 41 4e 47 55 41 47 45 nteger..LANGUAGE
1640: 20 73 71 6c 20 69 6d 6d 75 74 61 62 6c 65 20 53 sql immutable S
1650: 54 52 49 43 54 0a 09 41 53 20 24 24 0a 73 65 6c TRICT..AS $$.sel
1660: 65 63 74 20 6d 61 72 6b 28 24 31 2c 20 67 65 74 ect mark($1, get
1670: 5f 74 61 67 28 61 72 72 61 79 5b 24 32 5d 29 2c _tag(array[$2]),
1680: 20 4e 55 4c 4c 29 20 61 73 20 72 65 73 75 6c 74 NULL) as result
1690: 3b 0a 24 24 3b 0a 0a 2d 2d 20 74 68 69 73 20 66 ;.$$;..-- this f
16a0: 75 6e 63 74 69 6f 6e 20 61 64 64 73 20 74 61 67 unction adds tag
16b0: 20 74 6f 20 64 6f 6d 61 69 6e 0a 43 52 45 41 54 to domain.CREAT
16c0: 45 20 6f 72 20 72 65 70 6c 61 63 65 20 46 55 4e E or replace FUN
16d0: 43 54 49 4f 4e 20 6d 61 72 6b 28 64 6f 6d 61 69 CTION mark(domai
16e0: 6e 20 74 65 78 74 2c 20 6e 65 77 5f 74 61 67 20 n text, new_tag
16f0: 74 65 78 74 29 20 52 45 54 55 52 4e 53 20 69 6e text) RETURNS in
1700: 74 65 67 65 72 0a 09 4c 41 4e 47 55 41 47 45 20 teger..LANGUAGE
1710: 73 71 6c 20 69 6d 6d 75 74 61 62 6c 65 20 53 54 sql immutable ST
1720: 52 49 43 54 0a 09 41 53 20 24 24 0a 73 65 6c 65 RICT..AS $$.sele
1730: 63 74 20 6d 61 72 6b 28 67 65 74 5f 73 69 74 65 ct mark(get_site
1740: 28 24 31 29 2c 20 67 65 74 5f 74 61 67 28 61 72 ($1), get_tag(ar
1750: 72 61 79 5b 24 32 5d 29 2c 20 4e 55 4c 4c 29 20 ray[$2]), NULL)
1760: 61 73 20 72 65 73 75 6c 74 3b 0a 24 24 3b 0a 0a as result;.$$;..
1770: 2d 2d 20 74 68 69 73 20 66 75 6e 63 74 69 6f 6e -- this function
1780: 20 73 65 74 73 20 74 61 67 73 20 66 6f 72 20 73 sets tags for s
1790: 69 74 65 20 77 69 74 68 6f 75 74 20 72 65 67 65 ite without rege
17a0: 78 70 0a 43 52 45 41 54 45 20 6f 72 20 72 65 70 xp.CREATE or rep
17b0: 6c 61 63 65 20 46 55 4e 43 54 49 4f 4e 20 73 65 lace FUNCTION se
17c0: 74 28 6d 79 5f 69 64 5f 73 69 74 65 20 69 6e 74 t(my_id_site int
17d0: 65 67 65 72 2c 20 6d 79 5f 69 64 5f 74 61 67 20 eger, my_id_tag
17e0: 69 6e 74 65 67 65 72 29 20 52 45 54 55 52 4e 53 integer) RETURNS
17f0: 20 69 6e 74 65 67 65 72 0a 09 4c 41 4e 47 55 41 integer..LANGUA
1800: 47 45 20 73 71 6c 20 53 54 52 49 43 54 0a 09 41 GE sql STRICT..A
1810: 53 20 24 24 0a 64 65 6c 65 74 65 20 66 72 6f 6d S $$.delete from
1820: 20 75 72 6c 73 20 77 68 65 72 65 20 24 31 20 3d urls where $1 =
1830: 20 69 64 5f 73 69 74 65 20 61 6e 64 20 72 65 67 id_site and reg
1840: 65 78 70 20 69 73 20 4e 55 4c 4c 3b 0a 69 6e 73 exp is NULL;.ins
1850: 65 72 74 20 69 6e 74 6f 20 75 72 6c 73 20 28 69 ert into urls (i
1860: 64 5f 73 69 74 65 2c 20 69 64 5f 74 61 67 29 20 d_site, id_tag)
1870: 76 61 6c 75 65 73 20 28 24 31 2c 20 24 32 29 3b values ($1, $2);
1880: 0a 73 65 6c 65 63 74 20 24 31 3b 0a 24 24 3b 0a .select $1;.$$;.
1890: 0a 2d 2d 20 74 68 69 73 20 66 75 6e 63 74 69 6f .-- this functio
18a0: 6e 20 73 65 74 73 20 74 61 67 73 20 66 6f 72 20 n sets tags for
18b0: 73 69 74 65 2f 72 65 67 65 78 70 20 70 61 69 72 site/regexp pair
18c0: 0a 43 52 45 41 54 45 20 6f 72 20 72 65 70 6c 61 .CREATE or repla
18d0: 63 65 20 46 55 4e 43 54 49 4f 4e 20 73 65 74 28 ce FUNCTION set(
18e0: 6d 79 5f 69 64 5f 73 69 74 65 20 69 6e 74 65 67 my_id_site integ
18f0: 65 72 2c 20 6d 79 5f 69 64 5f 74 61 67 20 69 6e er, my_id_tag in
1900: 74 65 67 65 72 2c 20 6d 79 5f 72 65 67 65 78 70 teger, my_regexp
1910: 20 74 65 78 74 29 20 52 45 54 55 52 4e 53 20 69 text) RETURNS i
1920: 6e 74 65 67 65 72 0a 09 4c 41 4e 47 55 41 47 45 nteger..LANGUAGE
1930: 20 73 71 6c 20 53 54 52 49 43 54 0a 09 41 53 20 sql STRICT..AS
1940: 24 24 0a 64 65 6c 65 74 65 20 66 72 6f 6d 20 75 $$.delete from u
1950: 72 6c 73 20 77 68 65 72 65 20 24 31 20 3d 20 69 rls where $1 = i
1960: 64 5f 73 69 74 65 20 61 6e 64 20 24 33 20 3d 20 d_site and $3 =
1970: 72 65 67 65 78 70 3b 0a 69 6e 73 65 72 74 20 69 regexp;.insert i
1980: 6e 74 6f 20 75 72 6c 73 20 28 69 64 5f 73 69 74 nto urls (id_sit
1990: 65 2c 20 69 64 5f 74 61 67 2c 20 72 65 67 65 78 e, id_tag, regex
19a0: 70 29 20 76 61 6c 75 65 73 20 28 24 31 2c 20 24 p) values ($1, $
19b0: 32 2c 20 24 33 29 3b 0a 73 65 6c 65 63 74 20 24 2, $3);.select $
19c0: 31 3b 0a 24 24 3b 0a 0a 2d 2d 20 74 68 69 73 20 1;.$$;..-- this
19d0: 66 75 6e 63 74 69 6f 6e 20 73 74 6f 72 65 73 20 function stores
19e0: 6e 65 77 20 64 61 74 61 20 66 6f 72 20 73 69 74 new data for sit
19f0: 65 2f 72 65 67 65 78 70 20 70 61 69 72 0a 63 72 e/regexp pair.cr
1a00: 65 61 74 65 20 6f 72 20 72 65 70 6c 61 63 65 20 eate or replace
1a10: 66 75 6e 63 74 69 6f 6e 20 73 65 74 28 64 6f 6d function set(dom
1a20: 61 69 6e 20 74 65 78 74 2c 20 74 61 67 73 20 74 ain text, tags t
1a30: 65 78 74 2c 20 72 65 67 65 78 70 20 74 65 78 74 ext, regexp text
1a40: 29 20 72 65 74 75 72 6e 73 20 69 6e 74 65 67 65 ) returns intege
1a50: 72 0a 09 6c 61 6e 67 75 61 67 65 20 73 71 6c 20 r..language sql
1a60: 69 6d 6d 75 74 61 62 6c 65 20 73 74 72 69 63 74 immutable strict
1a70: 0a 09 61 73 20 24 24 0a 73 65 6c 65 63 74 20 73 ..as $$.select s
1a80: 65 74 28 67 65 74 5f 73 69 74 65 28 24 31 29 2c et(get_site($1),
1a90: 20 67 65 74 5f 74 61 67 28 24 32 3a 3a 74 65 78 get_tag($2::tex
1aa0: 74 5b 5d 29 2c 20 24 33 29 3b 0a 24 24 3b 0a 0a t[]), $3);.$$;..
1ab0: 2d 2d 20 74 68 69 73 20 66 75 6e 63 74 69 6f 6e -- this function
1ac0: 20 73 74 6f 72 65 73 20 6e 65 77 20 64 61 74 61 stores new data
1ad0: 20 66 6f 72 20 73 69 74 65 2f 72 65 67 65 78 70 for site/regexp
1ae0: 20 70 61 69 72 0a 63 72 65 61 74 65 20 6f 72 20 pair.create or
1af0: 72 65 70 6c 61 63 65 20 66 75 6e 63 74 69 6f 6e replace function
1b00: 20 73 65 74 28 64 6f 6d 61 69 6e 20 74 65 78 74 set(domain text
1b10: 2c 20 74 61 67 73 20 74 65 78 74 29 20 72 65 74 , tags text) ret
1b20: 75 72 6e 73 20 69 6e 74 65 67 65 72 0a 09 6c 61 urns integer..la
1b30: 6e 67 75 61 67 65 20 73 71 6c 20 69 6d 6d 75 74 nguage sql immut
1b40: 61 62 6c 65 20 73 74 72 69 63 74 0a 09 61 73 20 able strict..as
1b50: 24 24 0a 73 65 6c 65 63 74 20 73 65 74 28 67 65 $$.select set(ge
1b60: 74 5f 73 69 74 65 28 24 31 29 2c 20 67 65 74 5f t_site($1), get_
1b70: 74 61 67 28 24 32 3a 3a 74 65 78 74 5b 5d 29 29 tag($2::text[]))
1b80: 3b 0a 24 24 3b 0a 0a 2d 2d 20 74 68 69 73 20 66 ;.$$;..-- this f
1b90: 75 6e 63 74 69 6f 6e 20 72 65 74 75 72 6e 73 20 unction returns
1ba0: 69 64 20 6f 66 20 74 61 67 20 61 72 72 61 79 0a id of tag array.
1bb0: 63 72 65 61 74 65 20 6f 72 20 72 65 70 6c 61 63 create or replac
1bc0: 65 20 66 75 6e 63 74 69 6f 6e 20 67 65 74 5f 74 e function get_t
1bd0: 61 67 28 6d 79 5f 74 61 67 20 74 65 78 74 5b 5d ag(my_tag text[]
1be0: 29 20 72 65 74 75 72 6e 73 20 69 6e 74 65 67 65 ) returns intege
1bf0: 72 0a 09 6c 61 6e 67 75 61 67 65 20 70 6c 70 67 r..language plpg
1c00: 73 71 6c 20 73 74 72 69 63 74 0a 09 61 73 20 24 sql strict..as $
1c10: 24 0a 64 65 63 6c 61 72 65 0a 09 74 61 67 5f 69 $.declare..tag_i
1c20: 64 20 69 6e 74 65 67 65 72 3b 0a 62 65 67 69 6e d integer;.begin
1c30: 0a 09 73 65 6c 65 63 74 20 69 64 5f 74 61 67 20 ..select id_tag
1c40: 66 72 6f 6d 20 74 61 67 20 77 68 65 72 65 20 75 from tag where u
1c50: 73 6f 72 74 28 6d 79 5f 74 61 67 29 20 3d 20 74 sort(my_tag) = t
1c60: 61 67 20 69 6e 74 6f 20 74 61 67 5f 69 64 3b 0a ag into tag_id;.
1c70: 09 69 66 20 6e 6f 74 20 66 6f 75 6e 64 20 74 68 .if not found th
1c80: 65 6e 0a 09 09 69 6e 73 65 72 74 20 69 6e 74 6f en...insert into
1c90: 20 74 61 67 20 28 74 61 67 29 20 76 61 6c 75 65 tag (tag) value
1ca0: 73 20 28 75 73 6f 72 74 28 6d 79 5f 74 61 67 29 s (usort(my_tag)
1cb0: 29 3b 0a 09 09 73 65 6c 65 63 74 20 69 64 5f 74 );...select id_t
1cc0: 61 67 20 66 72 6f 6d 20 74 61 67 20 77 68 65 72 ag from tag wher
1cd0: 65 20 75 73 6f 72 74 28 6d 79 5f 74 61 67 29 20 e usort(my_tag)
1ce0: 3d 20 74 61 67 20 69 6e 74 6f 20 74 61 67 5f 69 = tag into tag_i
1cf0: 64 3b 0a 09 65 6e 64 20 69 66 3b 0a 09 72 65 74 d;..end if;..ret
1d00: 75 72 6e 20 74 61 67 5f 69 64 3b 0a 65 6e 64 3b urn tag_id;.end;
1d10: 0a 24 24 3b 0a .$$;.