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