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