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