Lines of
database.sql
from check-in 7828f877c8
that are changed by the sequence of edits moving toward
check-in 5f6dc4c42d:
1: -- general database structure
2: -- table to hold all rules
3: CREATE TABLE rules (
4: netmask cidr NOT NULL,
5: redirect_url text DEFAULT 'about::blank'::text NOT NULL,
6: from_weekday smallint DEFAULT 0 NOT NULL,
7: to_weekday smallint DEFAULT 6 NOT NULL,
8: from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
9: to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
10: id_tag smallint NOT NULL
11: );
12:
13: ALTER TABLE ONLY rules
14: ADD CONSTRAINT rules_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
15: ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
16:
17: -- table to hold site arrays
18: CREATE TABLE site (
19: id_site serial,
20: site text[] NOT NULL
21: );
22:
23: ALTER TABLE ONLY site
24: ADD CONSTRAINT site_pkey PRIMARY KEY (id_site);
25:
26: CREATE UNIQUE INDEX site_u ON site (usort(site));
27:
28: CREATE INDEX site_g ON site USING gin (site);
29:
30: -- table to hold tag combinations
31: CREATE TABLE tag (
32: id_tag serial,
33: tag text[] NOT NULL
34: );
35:
36: ALTER TABLE ONLY tag
37: ADD CONSTRAINT tag_pkey PRIMARY KEY (id_tag);
38:
39: CREATE UNIQUE INDEX tag_u ON tag (usort(tag));
40:
41: CREATE INDEX tag_g ON tag USING gin (tag);
42:
43: -- table to hold tag - site links
44: CREATE TABLE urls (
45: date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
7828f877c8 2010-08-07 46: id_site smallint NOT NULL,
47: id_tag smallint NOT NULL,
48: regexp text
49: );
50:
51: CREATE UNIQUE INDEX urls_pkey ON urls USING btree (id_site, regexp);
52:
53: CREATE INDEX urls_id_tag ON urls USING btree (id_tag);
54:
55: ALTER TABLE ONLY urls
56: ADD CONSTRAINT urls_site_f FOREIGN KEY (id_site) REFERENCES site(id_site) MATCH FULL
57: ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
58:
59: ALTER TABLE ONLY urls
60: ADD CONSTRAINT urls_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
61: ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
62:
63: -- rule to join all tables into one to simplify access
64: -- automaticall uses current day and time data
65: CREATE VIEW site_rule AS
66: SELECT a.redirect_url, a.netmask, b.site, b.regexp
67: FROM ((
68: SELECT rules.redirect_url, tag.tag AS rule_tag, rules.netmask
69: FROM rules NATURAL JOIN tag
70: WHERE ('now'::text)::time without time zone >= rules.from_time
71: AND ('now'::text)::time without time zone <= rules.to_time
72: AND date_part('dow'::text, now()) >= (rules.from_weekday)::double precision
73: AND date_part('dow'::text, now()) <= (rules.to_weekday)::double precision
74: ) a JOIN (
75: SELECT site.site, tag.tag AS url_tag, regexp
76: FROM urls NATURAL JOIN tag NATURAL JOIN site
77: ) b ON (b.url_tag && a.rule_tag));
78:
79: CREATE PROCEDURAL LANGUAGE plpgsql;
80:
81: -- general array sorting and domain processing functions
82: -- sorts array
83: CREATE or replace FUNCTION sort(original anyarray) RETURNS anyarray
84: LANGUAGE sql IMMUTABLE STRICT
85: AS $_$
86: select array_agg(item) as result from (select unnest($1) as item order by item) a;
87: $_$;
88:
89: -- sorts array and removes duplicates
90: CREATE or replace FUNCTION usort(original anyarray) RETURNS anyarray
91: LANGUAGE sql IMMUTABLE STRICT
92: AS $_$
93: select array_agg(item) as result from (select distinct unnest($1) as item order by item) a;
94: $_$;
95:
96: -- transforms domain into ordered array for indexing
97: CREATE or replace 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 or replace 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: -- functions that works with tables
138: -- this functions returns id of site
139: create or replace function get_site(my_site text[]) returns integer
140: language plpgsql strict
141: as $$
142: declare
143: site_id integer;
144: begin
145: select id_site from site where my_site = site into site_id;
146: if not found then
147: insert into site (site) values (my_site);
148: select id_site from site where my_site = site into site_id;
149: end if;
150: return site_id;
151: end;
152: $$;
153:
154: create or replace function get_site(domain text) returns integer
155: language sql immutable strict
156: as $$
157: select get_site(tripdomain($1)) as result;
158: $$;
159:
160: -- this function inserts or updates record with tags to site by site id with regexp
161: CREATE or replace FUNCTION mark(my_id_site integer, my_id_tag integer, my_regexp text) RETURNS integer
162: LANGUAGE plpgsql STRICT
163: AS $$
164: declare
165: -- maybe check should be added to make sure supplied site id really exists
166: my_tag text[];
167: begin
168: -- selecting tags site already have and adding new tag to them
169: -- note that tags should be sorted to eliminate permutations
170: select coalesce(tag, '{}'::text[]) from urls natural left join tag
171: where id_site = my_id_site and regexp = my_regexp into my_tag;
172: if not found then
173: -- no records found - creating new tag
174: insert into urls (id_site, id_tag, regexp) values (my_id_site, my_id_tag, my_regexp);
175: else
176: -- joining tags
177: select usort(my_tag || tag) from tag where id_tag = my_id_tag into my_tag;
178: -- updating existing record
179: update urls set id_tag = get_tag(my_tag)
180: where id_site = my_id_site and regexp = my_regexp;
181: end if;
182: return my_id_site;
183: end;
184: $$;
185:
186: -- this function adds tag to site by site id
187: CREATE or replace FUNCTION mark(my_id_site integer, new_tag text) RETURNS integer
188: LANGUAGE sql immutable STRICT
189: AS $$
190: select mark($1, get_tag(array[$2]), NULL) as result;
191: $$;
192:
193: -- this function adds tag to domain
194: CREATE or replace FUNCTION mark(domain text, new_tag text) RETURNS integer
195: LANGUAGE sql immutable STRICT
196: AS $$
197: select mark(get_site($1), get_tag(array[$2]), NULL) as result;
198: $$;
199:
200: -- this function sets tags for site without regexp
201: CREATE or replace FUNCTION set(my_id_site integer, my_id_tag integer) RETURNS integer
202: LANGUAGE sql STRICT
203: AS $$
204: delete from urls where $1 = id_site and regexp is NULL;
205: insert into urls (id_site, id_tag) values ($1, $2);
206: select $1;
207: $$;
208:
209: -- this function sets tags for site/regexp pair
210: CREATE or replace FUNCTION set(my_id_site integer, my_id_tag integer, my_regexp text) RETURNS integer
211: LANGUAGE sql STRICT
212: AS $$
213: delete from urls where $1 = id_site and $3 = regexp;
214: insert into urls (id_site, id_tag, regexp) values ($1, $2, $3);
215: select $1;
216: $$;
217:
218: -- this function stores new data for site/regexp pair
219: create or replace function set(domain text, tags text, regexp text) returns integer
220: language sql immutable strict
221: as $$
222: select set(get_site($1), get_tag($2::text[]), $3);
223: $$;
224:
225: -- this function stores new data for site/regexp pair
226: create or replace function set(domain text, tags text) returns integer
227: language sql immutable strict
228: as $$
229: select set(get_site($1), get_tag($2::text[]));
230: $$;
231:
232: -- this function returns id of tag array
233: create or replace function get_tag(my_tag text[]) returns integer
234: language plpgsql strict
235: as $$
236: declare
237: tag_id integer;
238: begin
239: select id_tag from tag where usort(my_tag) = tag into tag_id;
240: if not found then
241: insert into tag (tag) values (usort(my_tag));
242: select id_tag from tag where usort(my_tag) = tag into tag_id;
243: end if;
244: return tag_id;
245: end;
246: $$;