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
e0ecab03f9 2009-10-07 5: CREATE 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
e0ecab03f9 2009-10-07 12: CREATE 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: $_$;
e0ecab03f9 2009-10-07 17:
ddb0e69dd7 2009-10-02 18: -- this function adds tag to domain
2326f3bb9a 2009-10-08 19: CREATE or replace FUNCTION mark(domain text, new_tag text) RETURNS void
2326f3bb9a 2009-10-08 20: LANGUAGE plpgsql STRICT
2326f3bb9a 2009-10-08 21: AS $$
2326f3bb9a 2009-10-08 22: declare
2326f3bb9a 2009-10-08 23: my_site text[];
2326f3bb9a 2009-10-08 24: my_id_site smallint;
2326f3bb9a 2009-10-08 25: my_tag text[];
2326f3bb9a 2009-10-08 26: my_id_tag smallint;
2326f3bb9a 2009-10-08 27: begin
2326f3bb9a 2009-10-08 28: my_site := tripdomain(domain);
2326f3bb9a 2009-10-08 29:
2326f3bb9a 2009-10-08 30: -- selecting site id from table or adding site to the table
2326f3bb9a 2009-10-08 31: select id_site from site where my_site = site into my_id_site;
2326f3bb9a 2009-10-08 32: if not found then
2326f3bb9a 2009-10-08 33: insert into site (site) values (my_site);
2326f3bb9a 2009-10-08 34: select id_site from site where my_site = site into my_id_site;
2326f3bb9a 2009-10-08 35: end if;
2326f3bb9a 2009-10-08 36:
2326f3bb9a 2009-10-08 37: -- selecting tags site already have and adding new tag to them
2326f3bb9a 2009-10-08 38: -- note that tags should be sorted to eliminate permutations
2326f3bb9a 2009-10-08 39: select coalesce(tag, '{}'::text[]) from urls natural join tag where id_site = my_id_site into my_tag;
2326f3bb9a 2009-10-08 40: if not found then
2326f3bb9a 2009-10-08 41: -- no records found - creating new tag
2326f3bb9a 2009-10-08 42: insert into urls (id_site, id_tag) values (my_id_site, get_tag(array[new_tag]));
2326f3bb9a 2009-10-08 43: my_tag := array[new_tag];
2326f3bb9a 2009-10-08 44: else
2326f3bb9a 2009-10-08 45: -- joining tags
2326f3bb9a 2009-10-08 46: select usort(my_tag || array[new_tag]) into my_tag;
2326f3bb9a 2009-10-08 47: -- deleting old site specification
2326f3bb9a 2009-10-08 48: update urls set id_tag = get_tag(my_tag || array[new_tag]) where id_site = my_id_site;
2326f3bb9a 2009-10-08 49: end if;
2326f3bb9a 2009-10-08 50: end;
2326f3bb9a 2009-10-08 51: $$;
2326f3bb9a 2009-10-08 52:
2326f3bb9a 2009-10-08 53: -- this function adds tag to site by site id
2326f3bb9a 2009-10-08 54: CREATE or replace FUNCTION mark(my_id_site integer, new_tag text) RETURNS void
2326f3bb9a 2009-10-08 55: LANGUAGE plpgsql STRICT
2326f3bb9a 2009-10-08 56: AS $$
2326f3bb9a 2009-10-08 57: declare
2326f3bb9a 2009-10-08 58: -- maybe check should be added to make sure supplied site id really exists
2326f3bb9a 2009-10-08 59: my_tag text[];
2326f3bb9a 2009-10-08 60: my_tag_id integer;
2326f3bb9a 2009-10-08 61: begin
2326f3bb9a 2009-10-08 62: -- selecting tags site already have and adding new tag to them
2326f3bb9a 2009-10-08 63: -- note that tags should be sorted to eliminate permutations
2326f3bb9a 2009-10-08 64: select coalesce(tag, '{}'::text[]) from urls natural left join tag where id_site = my_id_site into my_tag;
2326f3bb9a 2009-10-08 65: if not found then
2326f3bb9a 2009-10-08 66: -- no records found - creating new tag
2326f3bb9a 2009-10-08 67: insert into urls (id_site, id_tag) values (my_id_site, get_tag(array[new_tag]));
2326f3bb9a 2009-10-08 68: else
2326f3bb9a 2009-10-08 69: -- joining tags
2326f3bb9a 2009-10-08 70: select usort(my_tag || array[new_tag]) into my_tag;
2326f3bb9a 2009-10-08 71: -- updating existing record
2326f3bb9a 2009-10-08 72: update urls set id_tag = get_tag(my_tag || array[new_tag]) where id_site = my_id_site;
2326f3bb9a 2009-10-08 73: end if;
2326f3bb9a 2009-10-08 74: end;
2326f3bb9a 2009-10-08 75: $$;
2326f3bb9a 2009-10-08 76:
2326f3bb9a 2009-10-08 77: -- this function returns id of tag array
2326f3bb9a 2009-10-08 78: create or replace function get_tag(my_tag text[]) returns integer
2326f3bb9a 2009-10-08 79: language plpgsql strict
2326f3bb9a 2009-10-08 80: as $$
2326f3bb9a 2009-10-08 81: declare
2326f3bb9a 2009-10-08 82: tag_id integer;
2326f3bb9a 2009-10-08 83: begin
2326f3bb9a 2009-10-08 84: select id_tag from tag where usort(my_tag) = tag into tag_id;
2326f3bb9a 2009-10-08 85: if not found then
2326f3bb9a 2009-10-08 86: insert into tag (tag) values (usort(my_tag));
2326f3bb9a 2009-10-08 87: select id_tag from tag where usort(my_tag) = tag into tag_id;
2326f3bb9a 2009-10-08 88: end if;
2326f3bb9a 2009-10-08 89: return tag_id;
e74427953f 2009-10-10 90: end;
e74427953f 2009-10-10 91: $$;
e74427953f 2009-10-10 92:
e74427953f 2009-10-10 93: -- this function returns id of site array
e74427953f 2009-10-10 94: create or replace function get_site(my_site text[]) returns integer
e74427953f 2009-10-10 95: language plpgsql strict
e74427953f 2009-10-10 96: as $$
e74427953f 2009-10-10 97: declare
e74427953f 2009-10-10 98: site_id integer;
e74427953f 2009-10-10 99: begin
e74427953f 2009-10-10 100: select id_site from site where my_site = site into site_id;
e74427953f 2009-10-10 101: if not found then
e74427953f 2009-10-10 102: insert into site (site) values (my_site);
e74427953f 2009-10-10 103: select id_site from site where my_site = site into site_id;
e74427953f 2009-10-10 104: end if;
e74427953f 2009-10-10 105: return site_id;
33e72616c9 2009-10-09 106: end;
33e72616c9 2009-10-09 107: $$;
33e72616c9 2009-10-09 108:
ddb0e69dd7 2009-10-02 109: -- transforms domain into ordered array for indexing
fce4cc7368 2009-10-02 110: CREATE FUNCTION tripdomain(url text) RETURNS text[]
ddb0e69dd7 2009-10-02 111: LANGUAGE plpgsql IMMUTABLE STRICT
ddb0e69dd7 2009-10-02 112: AS $_$
ddb0e69dd7 2009-10-02 113: declare
ddb0e69dd7 2009-10-02 114: result text[];
ddb0e69dd7 2009-10-02 115: splitted text[];
ddb0e69dd7 2009-10-02 116: x integer;
ddb0e69dd7 2009-10-02 117: length integer;
ddb0e69dd7 2009-10-02 118: begin
ddb0e69dd7 2009-10-02 119: splitted := string_to_array($1, '.');
ddb0e69dd7 2009-10-02 120: length := array_length(splitted, 1);
ddb0e69dd7 2009-10-02 121: x := 1;
ddb0e69dd7 2009-10-02 122: loop
ddb0e69dd7 2009-10-02 123: exit when splitted[x] is null;
ddb0e69dd7 2009-10-02 124: result[x] := splitted[x] || ':' || length - x;
ddb0e69dd7 2009-10-02 125: x := x + 1;
ddb0e69dd7 2009-10-02 126: end loop;
ddb0e69dd7 2009-10-02 127: return result;
ddb0e69dd7 2009-10-02 128: end;$_$;
ddb0e69dd7 2009-10-02 129:
ddb0e69dd7 2009-10-02 130: -- transforms ordered array into domain
ddb0e69dd7 2009-10-02 131: create function untrip(site text[]) returns text
ddb0e69dd7 2009-10-02 132: language plpgsql immutable strict
ddb0e69dd7 2009-10-02 133: as $_$
fce4cc7368 2009-10-02 134: declare
ddb0e69dd7 2009-10-02 135: x integer;
ddb0e69dd7 2009-10-02 136: splitted text[];
ddb0e69dd7 2009-10-02 137: pair text[];
fce4cc7368 2009-10-02 138: begin
ddb0e69dd7 2009-10-02 139: x := array_length(site, 1);
ddb0e69dd7 2009-10-02 140: loop
ddb0e69dd7 2009-10-02 141: exit when site[x] is null;
ddb0e69dd7 2009-10-02 142: pair := string_to_array(site[x], ':');
ddb0e69dd7 2009-10-02 143: splitted[0 - pair[2]::integer] := pair[1];
ddb0e69dd7 2009-10-02 144: x := x - 1;
ddb0e69dd7 2009-10-02 145: end loop;
ddb0e69dd7 2009-10-02 146: return array_to_string(splitted, '.');
09a01deb52 2009-10-01 147: end;
09a01deb52 2009-10-01 148: $_$;
09a01deb52 2009-10-01 149:
ddb0e69dd7 2009-10-02 150: -- table to hold all rules
ddb0e69dd7 2009-10-02 151: CREATE TABLE rules (
ddb0e69dd7 2009-10-02 152: netmask cidr NOT NULL,
ddb0e69dd7 2009-10-02 153: redirect_url text DEFAULT 'about::blank'::text NOT NULL,
ddb0e69dd7 2009-10-02 154: from_weekday smallint DEFAULT 0 NOT NULL,
ddb0e69dd7 2009-10-02 155: to_weekday smallint DEFAULT 6 NOT NULL,
ddb0e69dd7 2009-10-02 156: from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
ddb0e69dd7 2009-10-02 157: to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
ddb0e69dd7 2009-10-02 158: id_tag smallint NOT NULL
ddb0e69dd7 2009-10-02 159: );
ddb0e69dd7 2009-10-02 160:
ddb0e69dd7 2009-10-02 161: ALTER TABLE ONLY rules
ddb0e69dd7 2009-10-02 162: ADD CONSTRAINT rules_pkey PRIMARY KEY (netmask);
ddb0e69dd7 2009-10-02 163:
ddb0e69dd7 2009-10-02 164: -- table to hold site arrays
ddb0e69dd7 2009-10-02 165: CREATE TABLE site (
ddb0e69dd7 2009-10-02 166: id_site serial,
4b22e25f24 2009-10-07 167: site text[] NOT NULL
ddb0e69dd7 2009-10-02 168: );
ddb0e69dd7 2009-10-02 169:
ddb0e69dd7 2009-10-02 170: ALTER TABLE ONLY site
33e72616c9 2009-10-09 171: ADD CONSTRAINT site_pkey PRIMARY KEY (id_site);
ddb0e69dd7 2009-10-02 172:
33e72616c9 2009-10-09 173: CREATE UNIQUE INDEX site_u ON site (usort(site));
ddb0e69dd7 2009-10-02 174:
e0ecab03f9 2009-10-07 175: CREATE INDEX site_g ON site USING gin (site);
ddb0e69dd7 2009-10-02 176:
ddb0e69dd7 2009-10-02 177: -- table to hold tag combinations
ddb0e69dd7 2009-10-02 178: CREATE TABLE tag (
ddb0e69dd7 2009-10-02 179: id_tag serial,
ddb0e69dd7 2009-10-02 180: tag text[] NOT NULL
ddb0e69dd7 2009-10-02 181: );
ddb0e69dd7 2009-10-02 182:
ddb0e69dd7 2009-10-02 183: ALTER TABLE ONLY tag
33e72616c9 2009-10-09 184: ADD CONSTRAINT tag_pkey PRIMARY KEY (id_tag);
e0ecab03f9 2009-10-07 185:
33e72616c9 2009-10-09 186: CREATE UNIQUE INDEX tag_u ON tag (usort(tag));
09a01deb52 2009-10-01 187:
09a01deb52 2009-10-01 188: CREATE INDEX tag_g ON tag USING gin (tag);
09a01deb52 2009-10-01 189:
ddb0e69dd7 2009-10-02 190: -- table to hold tag - site links
ddb0e69dd7 2009-10-02 191: CREATE TABLE urls (
ddb0e69dd7 2009-10-02 192: date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
ddb0e69dd7 2009-10-02 193: id_site smallint NOT NULL,
7d9c268669 2009-10-02 194: id_tag smallint NOT NULL,
7d9c268669 2009-10-02 195: regex text
ddb0e69dd7 2009-10-02 196: );
ddb0e69dd7 2009-10-02 197:
ddb0e69dd7 2009-10-02 198: ALTER TABLE ONLY urls
ddb0e69dd7 2009-10-02 199: ADD CONSTRAINT urls_pkey PRIMARY KEY (date_added);
09a01deb52 2009-10-01 200:
09a01deb52 2009-10-01 201: CREATE UNIQUE INDEX urls_id_site ON urls USING btree (id_site);
4b22e25f24 2009-10-07 202:
4b22e25f24 2009-10-07 203: CREATE UNIQUE INDEX urls_id_tag ON urls USING btree (id_tag);
7d9c268669 2009-10-02 204:
ddb0e69dd7 2009-10-02 205: -- rule to join all tables into one to simplify access
ddb0e69dd7 2009-10-02 206: -- automaticall uses current day and time data
ddb0e69dd7 2009-10-02 207: CREATE VIEW site_rule AS
7d9c268669 2009-10-02 208: SELECT a.redirect_url, a.netmask, b.site, b.regexp
ddb0e69dd7 2009-10-02 209: FROM ((
ddb0e69dd7 2009-10-02 210: SELECT rules.redirect_url, tag.tag AS rule_tag, rules.netmask
7d9c268669 2009-10-02 211: FROM rules NATURAL JOIN tag
7d9c268669 2009-10-02 212: WHERE ('now'::text)::time without time zone >= rules.from_time
7d9c268669 2009-10-02 213: AND ('now'::text)::time without time zone <= rules.to_time
7d9c268669 2009-10-02 214: AND date_part('dow'::text, now()) >= (rules.from_weekday)::double precision
7d9c268669 2009-10-02 215: AND date_part('dow'::text, now()) <= (rules.to_weekday)::double precision
ddb0e69dd7 2009-10-02 216: ) a JOIN (
7d9c268669 2009-10-02 217: SELECT site.site, tag.tag AS url_tag, regexp
7d9c268669 2009-10-02 218: FROM urls NATURAL JOIN tag NATURAL JOIN site
7d9c268669 2009-10-02 219: ) b ON (b.url_tag && a.rule_tag));