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