7828f877c8 2010-08-07 c.kworr@d4daf: -- general database structure
7828f877c8 2010-08-07 c.kworr@d4daf: -- table to hold all rules
7828f877c8 2010-08-07 c.kworr@d4daf: CREATE TABLE rules (
7828f877c8 2010-08-07 c.kworr@d4daf: netmask cidr NOT NULL,
7828f877c8 2010-08-07 c.kworr@d4daf: redirect_url text DEFAULT 'about::blank'::text NOT NULL,
7828f877c8 2010-08-07 c.kworr@d4daf: from_weekday smallint DEFAULT 0 NOT NULL,
7828f877c8 2010-08-07 c.kworr@d4daf: to_weekday smallint DEFAULT 6 NOT NULL,
7828f877c8 2010-08-07 c.kworr@d4daf: from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
7828f877c8 2010-08-07 c.kworr@d4daf: to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
7828f877c8 2010-08-07 c.kworr@d4daf: id_tag smallint NOT NULL
7828f877c8 2010-08-07 c.kworr@d4daf: );
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: ALTER TABLE ONLY rules
7828f877c8 2010-08-07 c.kworr@d4daf: ADD CONSTRAINT rules_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
7828f877c8 2010-08-07 c.kworr@d4daf: ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: -- table to hold site arrays
7828f877c8 2010-08-07 c.kworr@d4daf: CREATE TABLE site (
7828f877c8 2010-08-07 c.kworr@d4daf: id_site serial,
7828f877c8 2010-08-07 c.kworr@d4daf: site text[] NOT NULL
7828f877c8 2010-08-07 c.kworr@d4daf: );
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: ALTER TABLE ONLY site
7828f877c8 2010-08-07 c.kworr@d4daf: ADD CONSTRAINT site_pkey PRIMARY KEY (id_site);
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: CREATE UNIQUE INDEX site_u ON site (usort(site));
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: CREATE INDEX site_g ON site USING gin (site);
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: -- table to hold tag combinations
7828f877c8 2010-08-07 c.kworr@d4daf: CREATE TABLE tag (
7828f877c8 2010-08-07 c.kworr@d4daf: id_tag serial,
7828f877c8 2010-08-07 c.kworr@d4daf: tag text[] NOT NULL
7828f877c8 2010-08-07 c.kworr@d4daf: );
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: ALTER TABLE ONLY tag
7828f877c8 2010-08-07 c.kworr@d4daf: ADD CONSTRAINT tag_pkey PRIMARY KEY (id_tag);
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: CREATE UNIQUE INDEX tag_u ON tag (usort(tag));
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: CREATE INDEX tag_g ON tag USING gin (tag);
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: -- table to hold tag - site links
7828f877c8 2010-08-07 c.kworr@d4daf: CREATE TABLE urls (
7828f877c8 2010-08-07 c.kworr@d4daf: date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
7828f877c8 2010-08-07 c.kworr@d4daf: id_site smallint NOT NULL,
7828f877c8 2010-08-07 c.kworr@d4daf: id_tag smallint NOT NULL,
7828f877c8 2010-08-07 c.kworr@d4daf: regexp text
7828f877c8 2010-08-07 c.kworr@d4daf: );
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: CREATE UNIQUE INDEX urls_pkey ON urls USING btree (id_site, regexp);
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: CREATE INDEX urls_id_tag ON urls USING btree (id_tag);
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: ALTER TABLE ONLY urls
7828f877c8 2010-08-07 c.kworr@d4daf: ADD CONSTRAINT urls_site_f FOREIGN KEY (id_site) REFERENCES site(id_site) MATCH FULL
7828f877c8 2010-08-07 c.kworr@d4daf: ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: ALTER TABLE ONLY urls
7828f877c8 2010-08-07 c.kworr@d4daf: ADD CONSTRAINT urls_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
7828f877c8 2010-08-07 c.kworr@d4daf: ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
7828f877c8 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: -- rule to join all tables into one to simplify access
7828f877c8 2010-08-07 c.kworr@d4daf: -- automaticall uses current day and time data
7828f877c8 2010-08-07 c.kworr@d4daf: CREATE VIEW site_rule AS
7828f877c8 2010-08-07 c.kworr@d4daf: SELECT a.redirect_url, a.netmask, b.site, b.regexp
7828f877c8 2010-08-07 c.kworr@d4daf: FROM ((
7828f877c8 2010-08-07 c.kworr@d4daf: SELECT rules.redirect_url, tag.tag AS rule_tag, rules.netmask
7828f877c8 2010-08-07 c.kworr@d4daf: FROM rules NATURAL JOIN tag
7828f877c8 2010-08-07 c.kworr@d4daf: WHERE ('now'::text)::time without time zone >= rules.from_time
7828f877c8 2010-08-07 c.kworr@d4daf: AND ('now'::text)::time without time zone <= rules.to_time
7828f877c8 2010-08-07 c.kworr@d4daf: AND date_part('dow'::text, now()) >= (rules.from_weekday)::double precision
7828f877c8 2010-08-07 c.kworr@d4daf: AND date_part('dow'::text, now()) <= (rules.to_weekday)::double precision
7828f877c8 2010-08-07 c.kworr@d4daf: ) a JOIN (
7828f877c8 2010-08-07 c.kworr@d4daf: SELECT site.site, tag.tag AS url_tag, regexp
7828f877c8 2010-08-07 c.kworr@d4daf: FROM urls NATURAL JOIN tag NATURAL JOIN site
7828f877c8 2010-08-07 c.kworr@d4daf: ) b ON (b.url_tag && a.rule_tag));
7828f877c8 2010-08-07 c.kworr@d4daf:
16383de08e 2010-08-07 c.kworr@d4daf: CREATE PROCEDURAL LANGUAGE plpgsql;
16383de08e 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: -- general array sorting and domain processing functions
16383de08e 2010-08-07 c.kworr@d4daf: -- sorts array
16383de08e 2010-08-07 c.kworr@d4daf: CREATE or replace FUNCTION sort(original anyarray) RETURNS anyarray
16383de08e 2010-08-07 c.kworr@d4daf: LANGUAGE sql IMMUTABLE STRICT
16383de08e 2010-08-07 c.kworr@d4daf: AS $_$
16383de08e 2010-08-07 c.kworr@d4daf: select array_agg(item) as result from (select unnest($1) as item order by item) a;
16383de08e 2010-08-07 c.kworr@d4daf: $_$;
16383de08e 2010-08-07 c.kworr@d4daf:
16383de08e 2010-08-07 c.kworr@d4daf: -- sorts array and removes duplicates
16383de08e 2010-08-07 c.kworr@d4daf: CREATE or replace FUNCTION usort(original anyarray) RETURNS anyarray
16383de08e 2010-08-07 c.kworr@d4daf: LANGUAGE sql IMMUTABLE STRICT
16383de08e 2010-08-07 c.kworr@d4daf: AS $_$
16383de08e 2010-08-07 c.kworr@d4daf: select array_agg(item) as result from (select distinct unnest($1) as item order by item) a;
16383de08e 2010-08-07 c.kworr@d4daf: $_$;
16383de08e 2010-08-07 c.kworr@d4daf:
16383de08e 2010-08-07 c.kworr@d4daf: -- transforms domain into ordered array for indexing
16383de08e 2010-08-07 c.kworr@d4daf: CREATE or replace FUNCTION tripdomain(url text) RETURNS text[]
16383de08e 2010-08-07 c.kworr@d4daf: LANGUAGE plpgsql IMMUTABLE STRICT
16383de08e 2010-08-07 c.kworr@d4daf: AS $_$
16383de08e 2010-08-07 c.kworr@d4daf: declare
16383de08e 2010-08-07 c.kworr@d4daf: result text[];
16383de08e 2010-08-07 c.kworr@d4daf: splitted text[];
16383de08e 2010-08-07 c.kworr@d4daf: x integer;
16383de08e 2010-08-07 c.kworr@d4daf: length integer;
16383de08e 2010-08-07 c.kworr@d4daf: begin
16383de08e 2010-08-07 c.kworr@d4daf: splitted := string_to_array($1, '.');
16383de08e 2010-08-07 c.kworr@d4daf: length := array_length(splitted, 1);
16383de08e 2010-08-07 c.kworr@d4daf: x := 1;
16383de08e 2010-08-07 c.kworr@d4daf: loop
16383de08e 2010-08-07 c.kworr@d4daf: exit when splitted[x] is null;
16383de08e 2010-08-07 c.kworr@d4daf: result[x] := splitted[x] || ':' || length - x;
16383de08e 2010-08-07 c.kworr@d4daf: x := x + 1;
16383de08e 2010-08-07 c.kworr@d4daf: end loop;
16383de08e 2010-08-07 c.kworr@d4daf: return result;
16383de08e 2010-08-07 c.kworr@d4daf: end;$_$;
16383de08e 2010-08-07 c.kworr@d4daf:
16383de08e 2010-08-07 c.kworr@d4daf: -- transforms ordered array into domain
16383de08e 2010-08-07 c.kworr@d4daf: create or replace function untrip(site text[]) returns text
16383de08e 2010-08-07 c.kworr@d4daf: language plpgsql immutable strict
16383de08e 2010-08-07 c.kworr@d4daf: as $_$
16383de08e 2010-08-07 c.kworr@d4daf: declare
16383de08e 2010-08-07 c.kworr@d4daf: x integer;
16383de08e 2010-08-07 c.kworr@d4daf: splitted text[];
16383de08e 2010-08-07 c.kworr@d4daf: pair text[];
16383de08e 2010-08-07 c.kworr@d4daf: begin
16383de08e 2010-08-07 c.kworr@d4daf: x := array_length(site, 1);
16383de08e 2010-08-07 c.kworr@d4daf: loop
16383de08e 2010-08-07 c.kworr@d4daf: exit when site[x] is null;
16383de08e 2010-08-07 c.kworr@d4daf: pair := string_to_array(site[x], ':');
16383de08e 2010-08-07 c.kworr@d4daf: splitted[0 - pair[2]::integer] := pair[1];
16383de08e 2010-08-07 c.kworr@d4daf: x := x - 1;
16383de08e 2010-08-07 c.kworr@d4daf: end loop;
16383de08e 2010-08-07 c.kworr@d4daf: return array_to_string(splitted, '.');
16383de08e 2010-08-07 c.kworr@d4daf: end;
16383de08e 2010-08-07 c.kworr@d4daf: $_$;
16383de08e 2010-08-07 c.kworr@d4daf:
7828f877c8 2010-08-07 c.kworr@d4daf: -- functions that works with tables
16383de08e 2010-08-07 c.kworr@d4daf: -- this functions returns id of site
16383de08e 2010-08-07 c.kworr@d4daf: create or replace function get_site(my_site text[]) returns integer
16383de08e 2010-08-07 c.kworr@d4daf: language plpgsql strict
16383de08e 2010-08-07 c.kworr@d4daf: as $$
16383de08e 2010-08-07 c.kworr@d4daf: declare
16383de08e 2010-08-07 c.kworr@d4daf: site_id integer;
16383de08e 2010-08-07 c.kworr@d4daf: begin
16383de08e 2010-08-07 c.kworr@d4daf: select id_site from site where my_site = site into site_id;
16383de08e 2010-08-07 c.kworr@d4daf: if not found then
16383de08e 2010-08-07 c.kworr@d4daf: insert into site (site) values (my_site);
16383de08e 2010-08-07 c.kworr@d4daf: select id_site from site where my_site = site into site_id;
16383de08e 2010-08-07 c.kworr@d4daf: end if;
16383de08e 2010-08-07 c.kworr@d4daf: return site_id;
16383de08e 2010-08-07 c.kworr@d4daf: end;
16383de08e 2010-08-07 c.kworr@d4daf: $$;
16383de08e 2010-08-07 c.kworr@d4daf:
16383de08e 2010-08-07 c.kworr@d4daf: create or replace function get_site(domain text) returns integer
16383de08e 2010-08-07 c.kworr@d4daf: language sql immutable strict
16383de08e 2010-08-07 c.kworr@d4daf: as $$
16383de08e 2010-08-07 c.kworr@d4daf: select get_site(tripdomain($1)) as result;
16383de08e 2010-08-07 c.kworr@d4daf: $$;
16383de08e 2010-08-07 c.kworr@d4daf:
16383de08e 2010-08-07 c.kworr@d4daf: -- this function inserts or updates record with tags to site by site id with regexp
16383de08e 2010-08-07 c.kworr@d4daf: CREATE or replace FUNCTION mark(my_id_site integer, my_id_tag integer, my_regexp text) RETURNS integer
16383de08e 2010-08-07 c.kworr@d4daf: LANGUAGE plpgsql STRICT
16383de08e 2010-08-07 c.kworr@d4daf: AS $$
16383de08e 2010-08-07 c.kworr@d4daf: declare
16383de08e 2010-08-07 c.kworr@d4daf: -- maybe check should be added to make sure supplied site id really exists
16383de08e 2010-08-07 c.kworr@d4daf: my_tag text[];
16383de08e 2010-08-07 c.kworr@d4daf: begin
16383de08e 2010-08-07 c.kworr@d4daf: -- selecting tags site already have and adding new tag to them
16383de08e 2010-08-07 c.kworr@d4daf: -- note that tags should be sorted to eliminate permutations
16383de08e 2010-08-07 c.kworr@d4daf: select coalesce(tag, '{}'::text[]) from urls natural left join tag
16383de08e 2010-08-07 c.kworr@d4daf: where id_site = my_id_site and regexp = my_regexp into my_tag;
16383de08e 2010-08-07 c.kworr@d4daf: if not found then
16383de08e 2010-08-07 c.kworr@d4daf: -- no records found - creating new tag
16383de08e 2010-08-07 c.kworr@d4daf: insert into urls (id_site, id_tag, regexp) values (my_id_site, my_id_tag, my_regexp);
16383de08e 2010-08-07 c.kworr@d4daf: else
16383de08e 2010-08-07 c.kworr@d4daf: -- joining tags
16383de08e 2010-08-07 c.kworr@d4daf: select usort(my_tag || tag) from tag where id_tag = my_id_tag into my_tag;
16383de08e 2010-08-07 c.kworr@d4daf: -- updating existing record
16383de08e 2010-08-07 c.kworr@d4daf: update urls set id_tag = get_tag(my_tag)
16383de08e 2010-08-07 c.kworr@d4daf: where id_site = my_id_site and regexp = my_regexp;
16383de08e 2010-08-07 c.kworr@d4daf: end if;
16383de08e 2010-08-07 c.kworr@d4daf: return my_id_site;
16383de08e 2010-08-07 c.kworr@d4daf: end;
16383de08e 2010-08-07 c.kworr@d4daf: $$;
16383de08e 2010-08-07 c.kworr@d4daf:
16383de08e 2010-08-07 c.kworr@d4daf: -- this function adds tag to site by site id
16383de08e 2010-08-07 c.kworr@d4daf: CREATE or replace FUNCTION mark(my_id_site integer, new_tag text) RETURNS integer
16383de08e 2010-08-07 c.kworr@d4daf: LANGUAGE sql immutable STRICT
16383de08e 2010-08-07 c.kworr@d4daf: AS $$
16383de08e 2010-08-07 c.kworr@d4daf: select mark($1, get_tag(array[$2]), NULL) as result;
16383de08e 2010-08-07 c.kworr@d4daf: $$;
16383de08e 2010-08-07 c.kworr@d4daf:
16383de08e 2010-08-07 c.kworr@d4daf: -- this function adds tag to domain
16383de08e 2010-08-07 c.kworr@d4daf: CREATE or replace FUNCTION mark(domain text, new_tag text) RETURNS integer
16383de08e 2010-08-07 c.kworr@d4daf: LANGUAGE sql immutable STRICT
16383de08e 2010-08-07 c.kworr@d4daf: AS $$
16383de08e 2010-08-07 c.kworr@d4daf: select mark(get_site($1), get_tag(array[$2]), NULL) as result;
16383de08e 2010-08-07 c.kworr@d4daf: $$;
16383de08e 2010-08-07 c.kworr@d4daf:
16383de08e 2010-08-07 c.kworr@d4daf: -- this function sets tags for site without regexp
16383de08e 2010-08-07 c.kworr@d4daf: CREATE or replace FUNCTION set(my_id_site integer, my_id_tag integer) RETURNS integer
16383de08e 2010-08-07 c.kworr@d4daf: LANGUAGE sql STRICT
16383de08e 2010-08-07 c.kworr@d4daf: AS $$
16383de08e 2010-08-07 c.kworr@d4daf: delete from urls where $1 = id_site and regexp is NULL;
16383de08e 2010-08-07 c.kworr@d4daf: insert into urls (id_site, id_tag) values ($1, $2);
16383de08e 2010-08-07 c.kworr@d4daf: select $1;
16383de08e 2010-08-07 c.kworr@d4daf: $$;
16383de08e 2010-08-07 c.kworr@d4daf:
16383de08e 2010-08-07 c.kworr@d4daf: -- this function sets tags for site/regexp pair
16383de08e 2010-08-07 c.kworr@d4daf: CREATE or replace FUNCTION set(my_id_site integer, my_id_tag integer, my_regexp text) RETURNS integer
16383de08e 2010-08-07 c.kworr@d4daf: LANGUAGE sql STRICT
16383de08e 2010-08-07 c.kworr@d4daf: AS $$
16383de08e 2010-08-07 c.kworr@d4daf: delete from urls where $1 = id_site and $3 = regexp;
16383de08e 2010-08-07 c.kworr@d4daf: insert into urls (id_site, id_tag, regexp) values ($1, $2, $3);
16383de08e 2010-08-07 c.kworr@d4daf: select $1;
16383de08e 2010-08-07 c.kworr@d4daf: $$;
16383de08e 2010-08-07 c.kworr@d4daf:
16383de08e 2010-08-07 c.kworr@d4daf: -- this function stores new data for site/regexp pair
16383de08e 2010-08-07 c.kworr@d4daf: create or replace function set(domain text, tags text, regexp text) returns integer
16383de08e 2010-08-07 c.kworr@d4daf: language sql immutable strict
16383de08e 2010-08-07 c.kworr@d4daf: as $$
16383de08e 2010-08-07 c.kworr@d4daf: select set(get_site($1), get_tag($2::text[]), $3);
16383de08e 2010-08-07 c.kworr@d4daf: $$;
16383de08e 2010-08-07 c.kworr@d4daf:
16383de08e 2010-08-07 c.kworr@d4daf: -- this function stores new data for site/regexp pair
16383de08e 2010-08-07 c.kworr@d4daf: create or replace function set(domain text, tags text) returns integer
16383de08e 2010-08-07 c.kworr@d4daf: language sql immutable strict
16383de08e 2010-08-07 c.kworr@d4daf: as $$
16383de08e 2010-08-07 c.kworr@d4daf: select set(get_site($1), get_tag($2::text[]));
16383de08e 2010-08-07 c.kworr@d4daf: $$;
16383de08e 2010-08-07 c.kworr@d4daf:
16383de08e 2010-08-07 c.kworr@d4daf: -- this function returns id of tag array
16383de08e 2010-08-07 c.kworr@d4daf: create or replace function get_tag(my_tag text[]) returns integer
16383de08e 2010-08-07 c.kworr@d4daf: language plpgsql strict
16383de08e 2010-08-07 c.kworr@d4daf: as $$
16383de08e 2010-08-07 c.kworr@d4daf: declare
16383de08e 2010-08-07 c.kworr@d4daf: tag_id integer;
16383de08e 2010-08-07 c.kworr@d4daf: begin
16383de08e 2010-08-07 c.kworr@d4daf: select id_tag from tag where usort(my_tag) = tag into tag_id;
16383de08e 2010-08-07 c.kworr@d4daf: if not found then
16383de08e 2010-08-07 c.kworr@d4daf: insert into tag (tag) values (usort(my_tag));
16383de08e 2010-08-07 c.kworr@d4daf: select id_tag from tag where usort(my_tag) = tag into tag_id;
16383de08e 2010-08-07 c.kworr@d4daf: end if;
16383de08e 2010-08-07 c.kworr@d4daf: return tag_id;
16383de08e 2010-08-07 c.kworr@d4daf: end;
16383de08e 2010-08-07 c.kworr@d4daf: $$;