Lines of
database.sql
from check-in e0ecab03f9
that are changed by the sequence of edits moving toward
check-in 2326f3bb9a:
1: CREATE PROCEDURAL LANGUAGE plpgsql;
2:
3: -- general array sorting functions
4: -- sorts array
5: CREATE FUNCTION sort(original anyarray) RETURNS anyarray
6: LANGUAGE sql IMMUTABLE STRICT
7: AS $_$
8: select array_agg(item) as result from (select unnest($1) as item order by item) a;
9: $_$;
10:
11: -- sorts array and removes duplicates
12: CREATE FUNCTION usort(original anyarray) RETURNS anyarray
13: LANGUAGE sql IMMUTABLE STRICT
14: AS $_$
15: select array_agg(item) as result from (select distinct unnest($1) as item order by item) a;
16: $_$;
17:
18: -- this function adds tag to domain
e0ecab03f9 2009-10-07 19: CREATE FUNCTION mark(domain text, new_tag text) RETURNS void
20: LANGUAGE plpgsql STRICT
21: AS $$
22: declare
23: my_site text[];
e0ecab03f9 2009-10-07 24: my_site_id smallint;
25: my_tag text[];
e0ecab03f9 2009-10-07 26: my_tag_id smallint;
27: begin
28: my_site := tripdomain(domain);
29:
30: -- selecting site id from table or adding site to the table
e0ecab03f9 2009-10-07 31: select id_site from site where my_site = site into my_site_id;
32: if not found then
33: insert into site (site) values (my_site);
e0ecab03f9 2009-10-07 34: select id_site from site where my_site = site into my_site_id;
35: end if;
36:
37: -- selecting tags site already have and adding new tag to them
38: -- note that tags should be sorted to eliminate permutations
e0ecab03f9 2009-10-07 39: select tag from urls natural join tag where id_site = my_site_id into my_tag;
40: if not found then
41: -- no records found - creating new tag
42: my_tag := array[new_tag];
43: else
44: -- joining tags
45: select usort(my_tag || array[new_tag]) into my_tag;
46: -- deleting old site specification
e0ecab03f9 2009-10-07 47: delete from urls where id_site = my_site_id;
e0ecab03f9 2009-10-07 48: end if;
e0ecab03f9 2009-10-07 49:
e0ecab03f9 2009-10-07 50: -- selecting new tag id or adding tag to the table
e0ecab03f9 2009-10-07 51: select id_tag from tag where my_tag = tag into my_tag_id;
e0ecab03f9 2009-10-07 52: if not found then
e0ecab03f9 2009-10-07 53: insert into tag (tag) values(my_tag);
e0ecab03f9 2009-10-07 54: select id_tag from tag where my_tag = tag into my_tag_id;
55: end if;
e0ecab03f9 2009-10-07 56:
e0ecab03f9 2009-10-07 57: -- adding new site specification
e0ecab03f9 2009-10-07 58: insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
59: end;
60: $$;
61:
62: -- this function adds tag to site by site id
e0ecab03f9 2009-10-07 63: CREATE FUNCTION mark(my_site_id smallint, new_tag text) RETURNS void
64: LANGUAGE plpgsql STRICT
65: AS $$
66: declare
67: -- maybe check should be added to make sure supplied site id really exists
68: my_tag text[];
e0ecab03f9 2009-10-07 69: my_tag_id smallint;
70: begin
71: -- selecting tags site already have and adding new tag to them
72: -- note that tags should be sorted to eliminate permutations
e0ecab03f9 2009-10-07 73: select tag from urls natural join tag where id_site = my_site_id into my_tag;
74: if not found then
75: -- no records found - creating new tag
e0ecab03f9 2009-10-07 76: my_tag := array[new_tag];
77: else
78: -- joining tags
79: select usort(my_tag || array[new_tag]) into my_tag;
e0ecab03f9 2009-10-07 80: -- deleting old site specification
e0ecab03f9 2009-10-07 81: delete from urls where id_site = my_site_id;
82: end if;
e0ecab03f9 2009-10-07 83:
e0ecab03f9 2009-10-07 84: -- selecting new tag id or adding tag to the table
e0ecab03f9 2009-10-07 85: select id_tag from tag where my_tag = tag into my_tag_id;
86: if not found then
e0ecab03f9 2009-10-07 87: insert into tag (tag) values(my_tag);
e0ecab03f9 2009-10-07 88: select id_tag from tag where my_tag = tag into my_tag_id;
89: end if;
e0ecab03f9 2009-10-07 90:
e0ecab03f9 2009-10-07 91: -- adding new site specification
e0ecab03f9 2009-10-07 92: insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
93: end;
94: $$;
95:
96: -- transforms domain into ordered array for indexing
97: CREATE FUNCTION tripdomain(url text) RETURNS text[]
98: LANGUAGE plpgsql IMMUTABLE STRICT
99: AS $_$
100: declare
101: result text[];
102: splitted text[];
103: x integer;
104: length integer;
105: begin
106: splitted := string_to_array($1, '.');
107: length := array_length(splitted, 1);
108: x := 1;
109: loop
110: exit when splitted[x] is null;
111: result[x] := splitted[x] || ':' || length - x;
112: x := x + 1;
113: end loop;
114: return result;
115: end;$_$;
116:
117: -- transforms ordered array into domain
118: create function untrip(site text[]) returns text
119: language plpgsql immutable strict
120: as $_$
121: declare
122: x integer;
123: splitted text[];
124: pair text[];
125: begin
126: x := array_length(site, 1);
127: loop
128: exit when site[x] is null;
129: pair := string_to_array(site[x], ':');
130: splitted[0 - pair[2]::integer] := pair[1];
131: x := x - 1;
132: end loop;
133: return array_to_string(splitted, '.');
134: end;
135: $_$;
136:
137: -- table to hold all rules
138: CREATE TABLE rules (
139: netmask cidr NOT NULL,
140: redirect_url text DEFAULT 'about::blank'::text NOT NULL,
141: from_weekday smallint DEFAULT 0 NOT NULL,
142: to_weekday smallint DEFAULT 6 NOT NULL,
143: from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
144: to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
145: id_tag smallint NOT NULL
146: );
147:
148: ALTER TABLE ONLY rules
149: ADD CONSTRAINT rules_pkey PRIMARY KEY (netmask);
150:
151: -- table to hold site arrays
152: CREATE TABLE site (
153: id_site serial,
154: site text[] NOT NULL
155: );
156:
157: ALTER TABLE ONLY site
e0ecab03f9 2009-10-07 158: ADD CONSTRAINT site_id PRIMARY KEY (id_site);
159:
160: CREATE UNIQUE INDEX site_s ON site (usort(site));
161:
162: CREATE INDEX site_g ON site USING gin (site);
163:
164: -- table to hold tag combinations
165: CREATE TABLE tag (
166: id_tag serial,
167: tag text[] NOT NULL
168: );
169:
170: ALTER TABLE ONLY tag
171: ADD CONSTRAINT tag_id PRIMARY KEY (id_tag);
172:
173: CREATE UNIQUE INDEX tag_s ON tag (usort(tag));
174:
175: CREATE INDEX tag_g ON tag USING gin (tag);
176:
177: -- table to hold tag - site links
178: CREATE TABLE urls (
179: date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
180: id_site smallint NOT NULL,
181: id_tag smallint NOT NULL,
182: regex text
183: );
184:
185: ALTER TABLE ONLY urls
186: ADD CONSTRAINT urls_pkey PRIMARY KEY (date_added);
187:
188: CREATE UNIQUE INDEX urls_id_site ON urls USING btree (id_site);
189:
190: CREATE UNIQUE INDEX urls_id_tag ON urls USING btree (id_tag);
191:
192: -- rule to join all tables into one to simplify access
193: -- automaticall uses current day and time data
194: CREATE VIEW site_rule AS
195: SELECT a.redirect_url, a.netmask, b.site, b.regexp
196: FROM ((
197: SELECT rules.redirect_url, tag.tag AS rule_tag, rules.netmask
198: FROM rules NATURAL JOIN tag
199: WHERE ('now'::text)::time without time zone >= rules.from_time
200: AND ('now'::text)::time without time zone <= rules.to_time
201: AND date_part('dow'::text, now()) >= (rules.from_weekday)::double precision
202: AND date_part('dow'::text, now()) <= (rules.to_weekday)::double precision
203: ) a JOIN (
204: SELECT site.site, tag.tag AS url_tag, regexp
205: FROM urls NATURAL JOIN tag NATURAL JOIN site
206: ) b ON (b.url_tag && a.rule_tag));