09a01deb52 2009-10-01 1: CREATE PROCEDURAL LANGUAGE plpgsql;
09a01deb52 2009-10-01 2:
09a01deb52 2009-10-01 3: CREATE FUNCTION mark(domain text, new_tag text) RETURNS void
09a01deb52 2009-10-01 4: LANGUAGE plpgsql STRICT
09a01deb52 2009-10-01 5: AS $$
09a01deb52 2009-10-01 6: declare
fce4cc7368 2009-10-02 7: my_site text[];
09a01deb52 2009-10-01 8: my_site_id smallint;
09a01deb52 2009-10-01 9: my_tag text[];
09a01deb52 2009-10-01 10: my_tag_id smallint;
09a01deb52 2009-10-01 11: begin
09a01deb52 2009-10-01 12: my_site := tripdomain(domain);
09a01deb52 2009-10-01 13:
09a01deb52 2009-10-01 14: select id_site from site where my_site = site into my_site_id;
09a01deb52 2009-10-01 15: if not found then
09a01deb52 2009-10-01 16: insert into site (site) values (my_site);
09a01deb52 2009-10-01 17: select id_site from site where my_site = site into my_site_id;
09a01deb52 2009-10-01 18: end if;
09a01deb52 2009-10-01 19:
09a01deb52 2009-10-01 20: select tag from urls natural join tag where id_site = my_site_id into my_tag;
09a01deb52 2009-10-01 21: if not found then
fce4cc7368 2009-10-02 22: my_tag := array[new_tag];
09a01deb52 2009-10-01 23: else
09a01deb52 2009-10-01 24: 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 25: my_tag;
09a01deb52 2009-10-01 26: delete from urls where id_site = my_site_id;
09a01deb52 2009-10-01 27: end if;
09a01deb52 2009-10-01 28:
09a01deb52 2009-10-01 29: select id_tag from tag where my_tag = tag into my_tag_id;
09a01deb52 2009-10-01 30: if not found then
09a01deb52 2009-10-01 31: insert into tag (tag) values(my_tag);
09a01deb52 2009-10-01 32: select id_tag from tag where my_tag = tag into my_tag_id;
09a01deb52 2009-10-01 33: end if;
09a01deb52 2009-10-01 34:
09a01deb52 2009-10-01 35: insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
09a01deb52 2009-10-01 36: end;$$;
09a01deb52 2009-10-01 37:
09a01deb52 2009-10-01 38: CREATE FUNCTION mark(my_site_id smallint, new_tag text) RETURNS void
09a01deb52 2009-10-01 39: LANGUAGE plpgsql STRICT
09a01deb52 2009-10-01 40: AS $$
09a01deb52 2009-10-01 41: declare
09a01deb52 2009-10-01 42: my_tag text[];
09a01deb52 2009-10-01 43: my_tag_id smallint;
09a01deb52 2009-10-01 44: begin
09a01deb52 2009-10-01 45:
09a01deb52 2009-10-01 46: select tag from urls natural join tag where id_site = my_site_id into my_tag;
09a01deb52 2009-10-01 47: if not found then
fce4cc7368 2009-10-02 48: my_tag := array[new_tag];
09a01deb52 2009-10-01 49: else
09a01deb52 2009-10-01 50: 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 51: my_tag;
09a01deb52 2009-10-01 52: delete from urls where id_site = my_site_id;
09a01deb52 2009-10-01 53: end if;
09a01deb52 2009-10-01 54:
09a01deb52 2009-10-01 55: select id_tag from tag where my_tag = tag into my_tag_id;
09a01deb52 2009-10-01 56: if not found then
09a01deb52 2009-10-01 57: insert into tag (tag) values(my_tag);
09a01deb52 2009-10-01 58: select id_tag from tag where my_tag = tag into my_tag_id;
09a01deb52 2009-10-01 59: end if;
09a01deb52 2009-10-01 60:
09a01deb52 2009-10-01 61: insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
09a01deb52 2009-10-01 62: end;$$;
09a01deb52 2009-10-01 63:
fce4cc7368 2009-10-02 64: CREATE FUNCTION tripdomain(url text) RETURNS text[]
09a01deb52 2009-10-01 65: LANGUAGE plpgsql IMMUTABLE STRICT
09a01deb52 2009-10-01 66: AS $_$
09a01deb52 2009-10-01 67: declare
09a01deb52 2009-10-01 68: result varchar[];
09a01deb52 2009-10-01 69: splitted varchar[];
09a01deb52 2009-10-01 70: reversed varchar[];
09a01deb52 2009-10-01 71: x integer;
09a01deb52 2009-10-01 72: begin
09a01deb52 2009-10-01 73: x := 1;
09a01deb52 2009-10-01 74: splitted := string_to_array($1, '.');
09a01deb52 2009-10-01 75: loop
09a01deb52 2009-10-01 76: exit when splitted[x] is null;
09a01deb52 2009-10-01 77: if length(splitted[x]) > 0 then
09a01deb52 2009-10-01 78: reversed := splitted[x] || reversed;
09a01deb52 2009-10-01 79: end if;
09a01deb52 2009-10-01 80: x := x + 1;
09a01deb52 2009-10-01 81: end loop;
09a01deb52 2009-10-01 82: x := 1;
09a01deb52 2009-10-01 83: loop
09a01deb52 2009-10-01 84: exit when reversed[x] is null;
09a01deb52 2009-10-01 85: result[x] := reversed[x] || ':' || x;
09a01deb52 2009-10-01 86: x := x + 1;
09a01deb52 2009-10-01 87: end loop;
09a01deb52 2009-10-01 88: return result;
09a01deb52 2009-10-01 89: end;
09a01deb52 2009-10-01 90: $_$;
09a01deb52 2009-10-01 91:
09a01deb52 2009-10-01 92: CREATE TABLE rules (
09a01deb52 2009-10-01 93: netmask cidr NOT NULL,
09a01deb52 2009-10-01 94: redirect_url text DEFAULT 'about::blank'::text NOT NULL,
09a01deb52 2009-10-01 95: from_weekday smallint DEFAULT 0 NOT NULL,
09a01deb52 2009-10-01 96: to_weekday smallint DEFAULT 6 NOT NULL,
09a01deb52 2009-10-01 97: from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
09a01deb52 2009-10-01 98: to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
09a01deb52 2009-10-01 99: id_tag smallint NOT NULL
09a01deb52 2009-10-01 100: );
09a01deb52 2009-10-01 101:
09a01deb52 2009-10-01 102:
09a01deb52 2009-10-01 103: CREATE TABLE site (
09a01deb52 2009-10-01 104: id_site serial,
09a01deb52 2009-10-01 105: site text[] NOT NULL
09a01deb52 2009-10-01 106: );
09a01deb52 2009-10-01 107:
09a01deb52 2009-10-01 108: CREATE TABLE tag (
09a01deb52 2009-10-01 109: id_tag serial,
09a01deb52 2009-10-01 110: tag text[] NOT NULL
09a01deb52 2009-10-01 111: );
09a01deb52 2009-10-01 112:
09a01deb52 2009-10-01 113: CREATE TABLE urls (
09a01deb52 2009-10-01 114: date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
09a01deb52 2009-10-01 115: id_site smallint NOT NULL,
09a01deb52 2009-10-01 116: id_tag smallint NOT NULL
09a01deb52 2009-10-01 117: );
09a01deb52 2009-10-01 118:
09a01deb52 2009-10-01 119: CREATE VIEW site_rule AS
09a01deb52 2009-10-01 120: 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 121:
09a01deb52 2009-10-01 122: ALTER TABLE ONLY rules
09a01deb52 2009-10-01 123: ADD CONSTRAINT rules_pkey PRIMARY KEY (netmask);
09a01deb52 2009-10-01 124:
09a01deb52 2009-10-01 125: ALTER TABLE ONLY site
09a01deb52 2009-10-01 126: ADD CONSTRAINT site_id PRIMARY KEY (id_site);
09a01deb52 2009-10-01 127:
09a01deb52 2009-10-01 128: ALTER TABLE ONLY tag
09a01deb52 2009-10-01 129: ADD CONSTRAINT tag_id PRIMARY KEY (id_tag);
09a01deb52 2009-10-01 130:
09a01deb52 2009-10-01 131: ALTER TABLE ONLY urls
09a01deb52 2009-10-01 132: ADD CONSTRAINT urls_pkey PRIMARY KEY (date_added);
09a01deb52 2009-10-01 133:
09a01deb52 2009-10-01 134: CREATE UNIQUE INDEX site_s ON site USING btree (site);
09a01deb52 2009-10-01 135:
09a01deb52 2009-10-01 136: CREATE INDEX site_sg ON site USING gin (site);
09a01deb52 2009-10-01 137:
09a01deb52 2009-10-01 138: CREATE INDEX tag_g ON tag USING gin (tag);
09a01deb52 2009-10-01 139:
09a01deb52 2009-10-01 140: CREATE UNIQUE INDEX tag_s ON tag USING btree (tag);
09a01deb52 2009-10-01 141:
09a01deb52 2009-10-01 142: CREATE UNIQUE INDEX urls_id_site ON urls USING btree (id_site);