CREATE PROCEDURAL LANGUAGE plpgsql;
-- this function adds tag to domain
CREATE FUNCTION mark(domain text, new_tag text) RETURNS void
LANGUAGE plpgsql STRICT
AS $$
declare
my_site text[];
my_site_id smallint;
my_tag text[];
my_tag_id smallint;
begin
my_site := tripdomain(domain);
-- selecting site id from table or adding site to the table
select id_site from site where my_site = site into my_site_id;
if not found then
insert into site (site) values (my_site);
select id_site from site where my_site = site into my_site_id;
end if;
-- selecting tags site already have and adding new tag to them
-- note that tags should be sorted to eliminate permutations
select tag from urls natural join tag where id_site = my_site_id into my_tag;
if not found then
-- no records found - creating new tag
my_tag := array[new_tag];
else
-- joining tags
select array_agg(tag)
from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a
into my_tag;
-- deleting old site specification
delete from urls where id_site = my_site_id;
end if;
-- selecting new tag id or adding tag to the table
select id_tag from tag where my_tag = tag into my_tag_id;
if not found then
insert into tag (tag) values(my_tag);
select id_tag from tag where my_tag = tag into my_tag_id;
end if;
-- adding new site specification
insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
end;$$;
-- this function adds tag to site by site id
CREATE FUNCTION mark(my_site_id smallint, new_tag text) RETURNS void
LANGUAGE plpgsql STRICT
AS $$
declare
-- maybe check should be added to make sure supplied site id really exists
my_tag text[];
my_tag_id smallint;
begin
-- selecting tags site already have and adding new tag to them
-- note that tags should be sorted to eliminate permutations
select tag from urls natural join tag where id_site = my_site_id into my_tag;
if not found then
-- no records found - creating new tag
my_tag := array[new_tag];
else
-- joining tags
select array_agg(tag)
from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a
into my_tag;
-- deleting old site specification
delete from urls where id_site = my_site_id;
end if;
-- selecting new tag id or adding tag to the table
select id_tag from tag where my_tag = tag into my_tag_id;
if not found then
insert into tag (tag) values(my_tag);
select id_tag from tag where my_tag = tag into my_tag_id;
end if;
-- adding new site specification
insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
end;$$;
-- transforms domain into ordered array for indexing
CREATE FUNCTION tripdomain(url text) RETURNS text[]
LANGUAGE plpgsql IMMUTABLE STRICT
AS $_$
declare
result text[];
splitted text[];
x integer;
length integer;
begin
splitted := string_to_array($1, '.');
length := array_length(splitted, 1);
x := 1;
loop
exit when splitted[x] is null;
result[x] := splitted[x] || ':' || length - x;
x := x + 1;
end loop;
return result;
end;$_$;
-- transforms ordered array into domain
create function untrip(site text[]) returns text
language plpgsql immutable strict
as $_$
declare
x integer;
splitted text[];
pair text[];
begin
x := array_length(site, 1);
loop
exit when site[x] is null;
pair := string_to_array(site[x], ':');
splitted[0 - pair[2]::integer] := pair[1];
x := x - 1;
end loop;
return array_to_string(splitted, '.');
end;
$_$;
-- table to hold all rules
CREATE TABLE rules (
netmask cidr NOT NULL,
redirect_url text DEFAULT 'about::blank'::text NOT NULL,
from_weekday smallint DEFAULT 0 NOT NULL,
to_weekday smallint DEFAULT 6 NOT NULL,
from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
id_tag smallint NOT NULL
);
ALTER TABLE ONLY rules
ADD CONSTRAINT rules_pkey PRIMARY KEY (netmask);
-- table to hold site arrays
CREATE TABLE site (
id_site serial,
site text[] NOT NULL
);
ALTER TABLE ONLY site
ADD CONSTRAINT site_id PRIMARY KEY (id_site);
CREATE UNIQUE INDEX site_s ON site USING btree (site);
CREATE INDEX site_sg ON site USING gin (site);
-- table to hold tag combinations
CREATE TABLE tag (
id_tag serial,
tag text[] NOT NULL
);
ALTER TABLE ONLY tag
ADD CONSTRAINT tag_id PRIMARY KEY (id_tag);
CREATE INDEX tag_g ON tag USING gin (tag);
CREATE UNIQUE INDEX tag_s ON tag USING btree (tag);
-- table to hold tag - site links
CREATE TABLE urls (
date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
id_site smallint NOT NULL,
id_tag smallint NOT NULL,
regex text
);
ALTER TABLE ONLY urls
ADD CONSTRAINT urls_pkey PRIMARY KEY (date_added);
CREATE UNIQUE INDEX urls_id_site ON urls USING btree (id_site);
CREATE UNIQUE INDEX urls_id_tag ON urls USING btree (id_tag);
-- rule to join all tables into one to simplify access
-- automaticall uses current day and time data
CREATE VIEW site_rule AS
SELECT a.redirect_url, a.netmask, b.site, b.regexp
FROM ((
SELECT rules.redirect_url, tag.tag AS rule_tag, rules.netmask
FROM rules NATURAL JOIN tag
WHERE ('now'::text)::time without time zone >= rules.from_time
AND ('now'::text)::time without time zone <= rules.to_time
AND date_part('dow'::text, now()) >= (rules.from_weekday)::double precision
AND date_part('dow'::text, now()) <= (rules.to_weekday)::double precision
) a JOIN (
SELECT site.site, tag.tag AS url_tag, regexp
FROM urls NATURAL JOIN tag NATURAL JOIN site
) b ON (b.url_tag && a.rule_tag));