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