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