09a01deb52 2009-10-01 1: CREATE PROCEDURAL LANGUAGE plpgsql;
09a01deb52 2009-10-01 2:
e0ecab03f9 2009-10-07 3: -- general array sorting functions
e0ecab03f9 2009-10-07 4: -- sorts array
67e762b39b 2009-10-14 5: CREATE or replace FUNCTION sort(original anyarray) RETURNS anyarray
e0ecab03f9 2009-10-07 6: LANGUAGE sql IMMUTABLE STRICT
e0ecab03f9 2009-10-07 7: AS $_$
e0ecab03f9 2009-10-07 8: select array_agg(item) as result from (select unnest($1) as item order by item) a;
e0ecab03f9 2009-10-07 9: $_$;
e0ecab03f9 2009-10-07 10:
e0ecab03f9 2009-10-07 11: -- sorts array and removes duplicates
67e762b39b 2009-10-14 12: CREATE or replace FUNCTION usort(original anyarray) RETURNS anyarray
e0ecab03f9 2009-10-07 13: LANGUAGE sql IMMUTABLE STRICT
e0ecab03f9 2009-10-07 14: AS $_$
e0ecab03f9 2009-10-07 15: select array_agg(item) as result from (select distinct unnest($1) as item order by item) a;
e0ecab03f9 2009-10-07 16: $_$;
ddb0e69dd7 2009-10-02 17:
ddb0e69dd7 2009-10-02 18: -- transforms domain into ordered array for indexing
67e762b39b 2009-10-14 19: CREATE or replace FUNCTION tripdomain(url text) RETURNS text[]
ddb0e69dd7 2009-10-02 20: LANGUAGE plpgsql IMMUTABLE STRICT
ddb0e69dd7 2009-10-02 21: AS $_$
ddb0e69dd7 2009-10-02 22: declare
ddb0e69dd7 2009-10-02 23: result text[];
ddb0e69dd7 2009-10-02 24: splitted text[];
ddb0e69dd7 2009-10-02 25: x integer;
ddb0e69dd7 2009-10-02 26: length integer;
ddb0e69dd7 2009-10-02 27: begin
ddb0e69dd7 2009-10-02 28: splitted := string_to_array($1, '.');
ddb0e69dd7 2009-10-02 29: length := array_length(splitted, 1);
ddb0e69dd7 2009-10-02 30: x := 1;
ddb0e69dd7 2009-10-02 31: loop
ddb0e69dd7 2009-10-02 32: exit when splitted[x] is null;
ddb0e69dd7 2009-10-02 33: result[x] := splitted[x] || ':' || length - x;
ddb0e69dd7 2009-10-02 34: x := x + 1;
ddb0e69dd7 2009-10-02 35: end loop;
ddb0e69dd7 2009-10-02 36: return result;
ddb0e69dd7 2009-10-02 37: end;$_$;
ddb0e69dd7 2009-10-02 38:
ddb0e69dd7 2009-10-02 39: -- transforms ordered array into domain
67e762b39b 2009-10-14 40: create or replace function untrip(site text[]) returns text
ddb0e69dd7 2009-10-02 41: language plpgsql immutable strict
ddb0e69dd7 2009-10-02 42: as $_$
ddb0e69dd7 2009-10-02 43: declare
ddb0e69dd7 2009-10-02 44: x integer;
ddb0e69dd7 2009-10-02 45: splitted text[];
ddb0e69dd7 2009-10-02 46: pair text[];
ddb0e69dd7 2009-10-02 47: begin
ddb0e69dd7 2009-10-02 48: x := array_length(site, 1);
ddb0e69dd7 2009-10-02 49: loop
ddb0e69dd7 2009-10-02 50: exit when site[x] is null;
ddb0e69dd7 2009-10-02 51: pair := string_to_array(site[x], ':');
ddb0e69dd7 2009-10-02 52: splitted[0 - pair[2]::integer] := pair[1];
ddb0e69dd7 2009-10-02 53: x := x - 1;
ddb0e69dd7 2009-10-02 54: end loop;
ddb0e69dd7 2009-10-02 55: return array_to_string(splitted, '.');
09a01deb52 2009-10-01 56: end;
09a01deb52 2009-10-01 57: $_$;
09a01deb52 2009-10-01 58:
12c35e5674 2009-12-15 59: -- this functions returns id of site
12c35e5674 2009-12-15 60: create or replace function get_site(my_site text[]) returns integer
12c35e5674 2009-12-15 61: language plpgsql strict
12c35e5674 2009-12-15 62: as $$
12c35e5674 2009-12-15 63: declare
12c35e5674 2009-12-15 64: site_id integer;
12c35e5674 2009-12-15 65: begin
12c35e5674 2009-12-15 66: select id_site from site where my_site = site into site_id;
12c35e5674 2009-12-15 67: if not found then
12c35e5674 2009-12-15 68: insert into site (site) values (my_site);
12c35e5674 2009-12-15 69: select id_site from site where my_site = site into site_id;
12c35e5674 2009-12-15 70: end if;
12c35e5674 2009-12-15 71: return site_id;
12c35e5674 2009-12-15 72: end;
12c35e5674 2009-12-15 73: $$;
12c35e5674 2009-12-15 74:
12c35e5674 2009-12-15 75: create or replace function get_site(domain text) returns integer
12c35e5674 2009-12-15 76: language sql immutable strict
12c35e5674 2009-12-15 77: as $$
12c35e5674 2009-12-15 78: select get_site(tripdomain($1)) as result;
12c35e5674 2009-12-15 79: $$;
12c35e5674 2009-12-15 80:
f1bafd194a 2010-03-18 81: -- this function inserts or updates record with tags to site by site id with regexp
f1bafd194a 2010-03-18 82: CREATE or replace FUNCTION mark(my_id_site integer, my_id_tag integer, my_regexp text) RETURNS integer
f1bafd194a 2010-03-18 83: LANGUAGE plpgsql STRICT
f1bafd194a 2010-03-18 84: AS $$
f1bafd194a 2010-03-18 85: declare
f1bafd194a 2010-03-18 86: -- maybe check should be added to make sure supplied site id really exists
f1bafd194a 2010-03-18 87: my_tag text[];
f1bafd194a 2010-03-18 88: begin
f1bafd194a 2010-03-18 89: -- selecting tags site already have and adding new tag to them
f1bafd194a 2010-03-18 90: -- note that tags should be sorted to eliminate permutations
f1bafd194a 2010-03-18 91: select coalesce(tag, '{}'::text[]) from urls natural left join tag
f1bafd194a 2010-03-18 92: where id_site = my_id_site and regexp = my_regexp into my_tag;
f1bafd194a 2010-03-18 93: if not found then
f1bafd194a 2010-03-18 94: -- no records found - creating new tag
f1bafd194a 2010-03-18 95: insert into urls (id_site, id_tag, regexp) values (my_id_site, my_id_tag, my_regexp);
f1bafd194a 2010-03-18 96: else
f1bafd194a 2010-03-18 97: -- joining tags
f1bafd194a 2010-03-18 98: select usort(my_tag || tag) from tag where id_tag = my_id_tag into my_tag;
f1bafd194a 2010-03-18 99: -- updating existing record
f1bafd194a 2010-03-18 100: update urls set id_tag = get_tag(my_tag)
f1bafd194a 2010-03-18 101: where id_site = my_id_site and regexp = my_regexp;
f1bafd194a 2010-03-18 102: end if;
f1bafd194a 2010-03-18 103: return my_id_site;
f1bafd194a 2010-03-18 104: end;
f1bafd194a 2010-03-18 105: $$;
f1bafd194a 2010-03-18 106:
12c35e5674 2009-12-15 107: -- this function adds tag to site by site id
12c35e5674 2009-12-15 108: CREATE or replace FUNCTION mark(my_id_site integer, new_tag text) RETURNS integer
12c35e5674 2009-12-15 109: LANGUAGE plpgsql STRICT
12c35e5674 2009-12-15 110: AS $$
12c35e5674 2009-12-15 111: declare
12c35e5674 2009-12-15 112: -- maybe check should be added to make sure supplied site id really exists
12c35e5674 2009-12-15 113: my_tag text[];
12c35e5674 2009-12-15 114: begin
12c35e5674 2009-12-15 115: -- selecting tags site already have and adding new tag to them
12c35e5674 2009-12-15 116: -- note that tags should be sorted to eliminate permutations
f1bafd194a 2010-03-18 117: select coalesce(tag, '{}'::text[]) from urls natural left join tag
f1bafd194a 2010-03-18 118: where id_site = my_id_site and regexp is null into my_tag;
12c35e5674 2009-12-15 119: if not found then
12c35e5674 2009-12-15 120: -- no records found - creating new tag
12c35e5674 2009-12-15 121: insert into urls (id_site, id_tag) values (my_id_site, get_tag(array[new_tag]));
12c35e5674 2009-12-15 122: else
12c35e5674 2009-12-15 123: -- joining tags
12c35e5674 2009-12-15 124: select usort(my_tag || array[new_tag]) into my_tag;
12c35e5674 2009-12-15 125: -- updating existing record
f1bafd194a 2010-03-18 126: update urls set id_tag = get_tag(my_tag) where id_site = my_id_site and regexp is null;
12c35e5674 2009-12-15 127: end if;
12c35e5674 2009-12-15 128: return my_id_site;
12c35e5674 2009-12-15 129: end;
12c35e5674 2009-12-15 130: $$;
12c35e5674 2009-12-15 131:
12c35e5674 2009-12-15 132: -- this function adds tag to domain
12c35e5674 2009-12-15 133: CREATE or replace FUNCTION mark(domain text, new_tag text) RETURNS integer
12c35e5674 2009-12-15 134: LANGUAGE sql immutable STRICT
12c35e5674 2009-12-15 135: AS $$
12c35e5674 2009-12-15 136: select mark(get_site($1), $2) as result;
f1bafd194a 2010-03-18 137: $$;
f1bafd194a 2010-03-18 138:
b16bc5d76f 2010-03-25 139: -- this function sets tags for site without regexp
7224844efa 2010-03-18 140: CREATE or replace FUNCTION set(my_id_site integer, my_id_tag integer) RETURNS integer
7224844efa 2010-03-18 141: LANGUAGE sql STRICT
7224844efa 2010-03-18 142: AS $$
b16bc5d76f 2010-03-25 143: delete from urls where $1 = id_site and regexp is NULL;
7224844efa 2010-03-18 144: insert into urls (id_site, id_tag) values ($1, $2);
7224844efa 2010-03-18 145: select $1;
7224844efa 2010-03-18 146: $$;
7224844efa 2010-03-18 147:
7224844efa 2010-03-18 148: -- this function sets tags for site/regexp pair
7224844efa 2010-03-18 149: CREATE or replace FUNCTION set(my_id_site integer, my_id_tag integer, my_regexp text) RETURNS integer
7224844efa 2010-03-18 150: LANGUAGE sql STRICT
f1bafd194a 2010-03-18 151: AS $$
7224844efa 2010-03-18 152: delete from urls where $1 = id_site and $3 = regexp;
7224844efa 2010-03-18 153: insert into urls (id_site, id_tag, regexp) values ($1, $2, $3);
7224844efa 2010-03-18 154: select $1;
7224844efa 2010-03-18 155: $$;
7224844efa 2010-03-18 156:
7224844efa 2010-03-18 157: -- this function stores new data for site/regexp pair
7224844efa 2010-03-18 158: create or replace function set(domain text, tags text, regexp text) returns integer
7224844efa 2010-03-18 159: language sql immutable strict
7224844efa 2010-03-18 160: as $$
7224844efa 2010-03-18 161: select set(get_site($1), get_tag($2::text[]), $3);
f1bafd194a 2010-03-18 162: $$;
f1bafd194a 2010-03-18 163:
7224844efa 2010-03-18 164: -- this function stores new data for site/regexp pair
7224844efa 2010-03-18 165: create or replace function set(domain text, tags text) returns integer
7224844efa 2010-03-18 166: language sql immutable strict
7224844efa 2010-03-18 167: as $$
7224844efa 2010-03-18 168: select set(get_site($1), get_tag($2::text[]));
12c35e5674 2009-12-15 169: $$;
12c35e5674 2009-12-15 170:
12c35e5674 2009-12-15 171: -- this function returns id of tag array
12c35e5674 2009-12-15 172: create or replace function get_tag(my_tag text[]) returns integer
12c35e5674 2009-12-15 173: language plpgsql strict
12c35e5674 2009-12-15 174: as $$
12c35e5674 2009-12-15 175: declare
12c35e5674 2009-12-15 176: tag_id integer;
12c35e5674 2009-12-15 177: begin
12c35e5674 2009-12-15 178: select id_tag from tag where usort(my_tag) = tag into tag_id;
12c35e5674 2009-12-15 179: if not found then
12c35e5674 2009-12-15 180: insert into tag (tag) values (usort(my_tag));
12c35e5674 2009-12-15 181: select id_tag from tag where usort(my_tag) = tag into tag_id;
12c35e5674 2009-12-15 182: end if;
12c35e5674 2009-12-15 183: return tag_id;
12c35e5674 2009-12-15 184: end;
12c35e5674 2009-12-15 185: $$;
12c35e5674 2009-12-15 186:
ddb0e69dd7 2009-10-02 187: -- table to hold all rules
ddb0e69dd7 2009-10-02 188: CREATE TABLE rules (
ddb0e69dd7 2009-10-02 189: netmask cidr NOT NULL,
ddb0e69dd7 2009-10-02 190: redirect_url text DEFAULT 'about::blank'::text NOT NULL,
ddb0e69dd7 2009-10-02 191: from_weekday smallint DEFAULT 0 NOT NULL,
ddb0e69dd7 2009-10-02 192: to_weekday smallint DEFAULT 6 NOT NULL,
ddb0e69dd7 2009-10-02 193: from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
ddb0e69dd7 2009-10-02 194: to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
ddb0e69dd7 2009-10-02 195: id_tag smallint NOT NULL
ddb0e69dd7 2009-10-02 196: );
ddb0e69dd7 2009-10-02 197:
ddb0e69dd7 2009-10-02 198: ALTER TABLE ONLY rules
ddb0e69dd7 2009-10-02 199: ADD CONSTRAINT rules_pkey PRIMARY KEY (netmask);
f1bafd194a 2010-03-18 200:
f1bafd194a 2010-03-18 201: ALTER TABLE ONLY rules
f1bafd194a 2010-03-18 202: ADD CONSTRAINT rules_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
f1bafd194a 2010-03-18 203: ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
33e72616c9 2009-10-09 204:
ddb0e69dd7 2009-10-02 205: -- table to hold site arrays
ddb0e69dd7 2009-10-02 206: CREATE TABLE site (
ddb0e69dd7 2009-10-02 207: id_site serial,
4b22e25f24 2009-10-07 208: site text[] NOT NULL
ddb0e69dd7 2009-10-02 209: );
09a01deb52 2009-10-01 210:
09a01deb52 2009-10-01 211: ALTER TABLE ONLY site
33e72616c9 2009-10-09 212: ADD CONSTRAINT site_pkey PRIMARY KEY (id_site);
ddb0e69dd7 2009-10-02 213:
33e72616c9 2009-10-09 214: CREATE UNIQUE INDEX site_u ON site (usort(site));
ddb0e69dd7 2009-10-02 215:
e0ecab03f9 2009-10-07 216: CREATE INDEX site_g ON site USING gin (site);
ddb0e69dd7 2009-10-02 217:
ddb0e69dd7 2009-10-02 218: -- table to hold tag combinations
ddb0e69dd7 2009-10-02 219: CREATE TABLE tag (
ddb0e69dd7 2009-10-02 220: id_tag serial,
ddb0e69dd7 2009-10-02 221: tag text[] NOT NULL
ddb0e69dd7 2009-10-02 222: );
09a01deb52 2009-10-01 223:
09a01deb52 2009-10-01 224: ALTER TABLE ONLY tag
33e72616c9 2009-10-09 225: ADD CONSTRAINT tag_pkey PRIMARY KEY (id_tag);
e0ecab03f9 2009-10-07 226:
33e72616c9 2009-10-09 227: CREATE UNIQUE INDEX tag_u ON tag (usort(tag));
09a01deb52 2009-10-01 228:
09a01deb52 2009-10-01 229: CREATE INDEX tag_g ON tag USING gin (tag);
09a01deb52 2009-10-01 230:
ddb0e69dd7 2009-10-02 231: -- table to hold tag - site links
ddb0e69dd7 2009-10-02 232: CREATE TABLE urls (
ddb0e69dd7 2009-10-02 233: date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
ddb0e69dd7 2009-10-02 234: id_site smallint NOT NULL,
7d9c268669 2009-10-02 235: id_tag smallint NOT NULL,
12c35e5674 2009-12-15 236: regexp text
ddb0e69dd7 2009-10-02 237: );
ddb0e69dd7 2009-10-02 238:
f1bafd194a 2010-03-18 239: CREATE UNIQUE INDEX urls_pkey ON urls USING btree (id_site, regexp);
f1bafd194a 2010-03-18 240:
f1bafd194a 2010-03-18 241: CREATE INDEX urls_id_tag ON urls USING btree (id_tag);
4b22e25f24 2009-10-07 242:
f1bafd194a 2010-03-18 243: ALTER TABLE ONLY urls
f1bafd194a 2010-03-18 244: ADD CONSTRAINT urls_site_f FOREIGN KEY (id_site) REFERENCES site(id_site) MATCH FULL
f1bafd194a 2010-03-18 245: ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
ddb0e69dd7 2009-10-02 246:
f1bafd194a 2010-03-18 247: ALTER TABLE ONLY urls
f1bafd194a 2010-03-18 248: ADD CONSTRAINT urls_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
f1bafd194a 2010-03-18 249: ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
09a01deb52 2009-10-01 250:
ddb0e69dd7 2009-10-02 251: -- rule to join all tables into one to simplify access
ddb0e69dd7 2009-10-02 252: -- automaticall uses current day and time data
ddb0e69dd7 2009-10-02 253: CREATE VIEW site_rule AS
7d9c268669 2009-10-02 254: SELECT a.redirect_url, a.netmask, b.site, b.regexp
ddb0e69dd7 2009-10-02 255: FROM ((
ddb0e69dd7 2009-10-02 256: SELECT rules.redirect_url, tag.tag AS rule_tag, rules.netmask
7d9c268669 2009-10-02 257: FROM rules NATURAL JOIN tag
7d9c268669 2009-10-02 258: WHERE ('now'::text)::time without time zone >= rules.from_time
7d9c268669 2009-10-02 259: AND ('now'::text)::time without time zone <= rules.to_time
7d9c268669 2009-10-02 260: AND date_part('dow'::text, now()) >= (rules.from_weekday)::double precision
7d9c268669 2009-10-02 261: AND date_part('dow'::text, now()) <= (rules.to_weekday)::double precision
ddb0e69dd7 2009-10-02 262: ) a JOIN (
7d9c268669 2009-10-02 263: SELECT site.site, tag.tag AS url_tag, regexp
7d9c268669 2009-10-02 264: FROM urls NATURAL JOIN tag NATURAL JOIN site
7d9c268669 2009-10-02 265: ) b ON (b.url_tag && a.rule_tag));