09a01deb52 2009-10-01 c.kworr@d4daf: CREATE PROCEDURAL LANGUAGE plpgsql;
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: CREATE FUNCTION mark(domain text, new_tag text) RETURNS void
09a01deb52 2009-10-01 c.kworr@d4daf: LANGUAGE plpgsql STRICT
09a01deb52 2009-10-01 c.kworr@d4daf: AS $$
09a01deb52 2009-10-01 c.kworr@d4daf: declare
fce4cc7368 2009-10-02 c.kworr@d4daf: my_site text[];
09a01deb52 2009-10-01 c.kworr@d4daf: my_site_id smallint;
09a01deb52 2009-10-01 c.kworr@d4daf: my_tag text[];
09a01deb52 2009-10-01 c.kworr@d4daf: my_tag_id smallint;
09a01deb52 2009-10-01 c.kworr@d4daf: begin
09a01deb52 2009-10-01 c.kworr@d4daf: my_site := tripdomain(domain);
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: select id_site from site where my_site = site into my_site_id;
09a01deb52 2009-10-01 c.kworr@d4daf: if not found then
09a01deb52 2009-10-01 c.kworr@d4daf: insert into site (site) values (my_site);
09a01deb52 2009-10-01 c.kworr@d4daf: select id_site from site where my_site = site into my_site_id;
09a01deb52 2009-10-01 c.kworr@d4daf: end if;
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: select tag from urls natural join tag where id_site = my_site_id into my_tag;
09a01deb52 2009-10-01 c.kworr@d4daf: if not found then
fce4cc7368 2009-10-02 c.kworr@d4daf: my_tag := array[new_tag];
09a01deb52 2009-10-01 c.kworr@d4daf: else
09a01deb52 2009-10-01 c.kworr@d4daf: select array_agg(tag) from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a into
09a01deb52 2009-10-01 c.kworr@d4daf: my_tag;
09a01deb52 2009-10-01 c.kworr@d4daf: delete from urls where id_site = my_site_id;
09a01deb52 2009-10-01 c.kworr@d4daf: end if;
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: select id_tag from tag where my_tag = tag into my_tag_id;
09a01deb52 2009-10-01 c.kworr@d4daf: if not found then
09a01deb52 2009-10-01 c.kworr@d4daf: insert into tag (tag) values(my_tag);
09a01deb52 2009-10-01 c.kworr@d4daf: select id_tag from tag where my_tag = tag into my_tag_id;
09a01deb52 2009-10-01 c.kworr@d4daf: end if;
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
09a01deb52 2009-10-01 c.kworr@d4daf: end;$$;
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: CREATE FUNCTION mark(my_site_id smallint, new_tag text) RETURNS void
09a01deb52 2009-10-01 c.kworr@d4daf: LANGUAGE plpgsql STRICT
09a01deb52 2009-10-01 c.kworr@d4daf: AS $$
09a01deb52 2009-10-01 c.kworr@d4daf: declare
09a01deb52 2009-10-01 c.kworr@d4daf: my_tag text[];
09a01deb52 2009-10-01 c.kworr@d4daf: my_tag_id smallint;
09a01deb52 2009-10-01 c.kworr@d4daf: begin
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: select tag from urls natural join tag where id_site = my_site_id into my_tag;
09a01deb52 2009-10-01 c.kworr@d4daf: if not found then
fce4cc7368 2009-10-02 c.kworr@d4daf: my_tag := array[new_tag];
09a01deb52 2009-10-01 c.kworr@d4daf: else
09a01deb52 2009-10-01 c.kworr@d4daf: select array_agg(tag) from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a into
09a01deb52 2009-10-01 c.kworr@d4daf: my_tag;
09a01deb52 2009-10-01 c.kworr@d4daf: delete from urls where id_site = my_site_id;
09a01deb52 2009-10-01 c.kworr@d4daf: end if;
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: select id_tag from tag where my_tag = tag into my_tag_id;
09a01deb52 2009-10-01 c.kworr@d4daf: if not found then
09a01deb52 2009-10-01 c.kworr@d4daf: insert into tag (tag) values(my_tag);
09a01deb52 2009-10-01 c.kworr@d4daf: select id_tag from tag where my_tag = tag into my_tag_id;
09a01deb52 2009-10-01 c.kworr@d4daf: end if;
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
09a01deb52 2009-10-01 c.kworr@d4daf: end;$$;
09a01deb52 2009-10-01 c.kworr@d4daf:
fce4cc7368 2009-10-02 c.kworr@d4daf: CREATE FUNCTION tripdomain(url text) RETURNS text[]
09a01deb52 2009-10-01 c.kworr@d4daf: LANGUAGE plpgsql IMMUTABLE STRICT
09a01deb52 2009-10-01 c.kworr@d4daf: AS $_$
09a01deb52 2009-10-01 c.kworr@d4daf: declare
09a01deb52 2009-10-01 c.kworr@d4daf: result varchar[];
09a01deb52 2009-10-01 c.kworr@d4daf: splitted varchar[];
09a01deb52 2009-10-01 c.kworr@d4daf: reversed varchar[];
09a01deb52 2009-10-01 c.kworr@d4daf: x integer;
09a01deb52 2009-10-01 c.kworr@d4daf: begin
09a01deb52 2009-10-01 c.kworr@d4daf: x := 1;
09a01deb52 2009-10-01 c.kworr@d4daf: splitted := string_to_array($1, '.');
09a01deb52 2009-10-01 c.kworr@d4daf: loop
09a01deb52 2009-10-01 c.kworr@d4daf: exit when splitted[x] is null;
09a01deb52 2009-10-01 c.kworr@d4daf: if length(splitted[x]) > 0 then
09a01deb52 2009-10-01 c.kworr@d4daf: reversed := splitted[x] || reversed;
09a01deb52 2009-10-01 c.kworr@d4daf: end if;
09a01deb52 2009-10-01 c.kworr@d4daf: x := x + 1;
09a01deb52 2009-10-01 c.kworr@d4daf: end loop;
09a01deb52 2009-10-01 c.kworr@d4daf: x := 1;
09a01deb52 2009-10-01 c.kworr@d4daf: loop
09a01deb52 2009-10-01 c.kworr@d4daf: exit when reversed[x] is null;
09a01deb52 2009-10-01 c.kworr@d4daf: result[x] := reversed[x] || ':' || x;
09a01deb52 2009-10-01 c.kworr@d4daf: x := x + 1;
09a01deb52 2009-10-01 c.kworr@d4daf: end loop;
09a01deb52 2009-10-01 c.kworr@d4daf: return result;
09a01deb52 2009-10-01 c.kworr@d4daf: end;
09a01deb52 2009-10-01 c.kworr@d4daf: $_$;
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: CREATE TABLE rules (
09a01deb52 2009-10-01 c.kworr@d4daf: netmask cidr NOT NULL,
09a01deb52 2009-10-01 c.kworr@d4daf: redirect_url text DEFAULT 'about::blank'::text NOT NULL,
09a01deb52 2009-10-01 c.kworr@d4daf: from_weekday smallint DEFAULT 0 NOT NULL,
09a01deb52 2009-10-01 c.kworr@d4daf: to_weekday smallint DEFAULT 6 NOT NULL,
09a01deb52 2009-10-01 c.kworr@d4daf: from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
09a01deb52 2009-10-01 c.kworr@d4daf: to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
09a01deb52 2009-10-01 c.kworr@d4daf: id_tag smallint NOT NULL
09a01deb52 2009-10-01 c.kworr@d4daf: );
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: CREATE TABLE site (
09a01deb52 2009-10-01 c.kworr@d4daf: id_site serial,
09a01deb52 2009-10-01 c.kworr@d4daf: site text[] NOT NULL
09a01deb52 2009-10-01 c.kworr@d4daf: );
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: CREATE TABLE tag (
09a01deb52 2009-10-01 c.kworr@d4daf: id_tag serial,
09a01deb52 2009-10-01 c.kworr@d4daf: tag text[] NOT NULL
09a01deb52 2009-10-01 c.kworr@d4daf: );
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: CREATE TABLE urls (
09a01deb52 2009-10-01 c.kworr@d4daf: date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
09a01deb52 2009-10-01 c.kworr@d4daf: id_site smallint NOT NULL,
09a01deb52 2009-10-01 c.kworr@d4daf: id_tag smallint NOT NULL
09a01deb52 2009-10-01 c.kworr@d4daf: );
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: CREATE VIEW site_rule AS
09a01deb52 2009-10-01 c.kworr@d4daf: SELECT a.redirect_url, a.netmask, b.site 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 FROM ((urls NATURAL JOIN tag) NATURAL JOIN site)) b ON ((b.url_tag && a.rule_tag)));
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: ALTER TABLE ONLY rules
09a01deb52 2009-10-01 c.kworr@d4daf: ADD CONSTRAINT rules_pkey PRIMARY KEY (netmask);
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: ALTER TABLE ONLY site
09a01deb52 2009-10-01 c.kworr@d4daf: ADD CONSTRAINT site_id PRIMARY KEY (id_site);
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: ALTER TABLE ONLY tag
09a01deb52 2009-10-01 c.kworr@d4daf: ADD CONSTRAINT tag_id PRIMARY KEY (id_tag);
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: ALTER TABLE ONLY urls
09a01deb52 2009-10-01 c.kworr@d4daf: ADD CONSTRAINT urls_pkey PRIMARY KEY (date_added);
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: CREATE UNIQUE INDEX site_s ON site USING btree (site);
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: CREATE INDEX site_sg ON site USING gin (site);
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: CREATE INDEX tag_g ON tag USING gin (tag);
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: CREATE UNIQUE INDEX tag_s ON tag USING btree (tag);
09a01deb52 2009-10-01 c.kworr@d4daf:
09a01deb52 2009-10-01 c.kworr@d4daf: CREATE UNIQUE INDEX urls_id_site ON urls USING btree (id_site);