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