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