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