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