1
2
3
4
5
6
7
8
9
10
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-
+
|
-- general database structure
-- table to hold all rules
CREATE TABLE rules (
netmask cidr NOT NULL,
redirect_url text DEFAULT 'about::blank'::text NOT NULL,
from_weekday smallint DEFAULT 0 NOT NULL,
to_weekday smallint DEFAULT 6 NOT NULL,
from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
id_tag smallint NOT NULL
);
ALTER TABLE ONLY rules
ADD CONSTRAINT rules_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
-- table to hold site arrays
CREATE TABLE site (
id_site serial,
site text[] NOT NULL
);
ALTER TABLE ONLY site
ADD CONSTRAINT site_pkey PRIMARY KEY (id_site);
CREATE UNIQUE INDEX site_u ON site (usort(site));
CREATE INDEX site_g ON site USING gin (site);
-- table to hold tag combinations
CREATE TABLE tag (
id_tag serial,
tag text[] NOT NULL
);
ALTER TABLE ONLY tag
ADD CONSTRAINT tag_pkey PRIMARY KEY (id_tag);
CREATE UNIQUE INDEX tag_u ON tag (usort(tag));
CREATE INDEX tag_g ON tag USING gin (tag);
-- table to hold tag - site links
CREATE TABLE urls (
date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
id_site smallint NOT NULL,
id_tag smallint NOT NULL,
regexp text
);
CREATE UNIQUE INDEX urls_pkey ON urls USING btree (id_site, regexp);
CREATE INDEX urls_id_tag ON urls USING btree (id_tag);
ALTER TABLE ONLY urls
ADD CONSTRAINT urls_site_f FOREIGN KEY (id_site) REFERENCES site(id_site) MATCH FULL
ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE ONLY urls
ADD CONSTRAINT urls_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
-- rule to join all tables into one to simplify access
-- automaticall uses current day and time data
CREATE VIEW site_rule AS
SELECT a.redirect_url, a.netmask, b.site, b.regexp
FROM ((
SELECT rules.redirect_url, tag.tag AS rule_tag, rules.netmask
FROM rules NATURAL JOIN tag
WHERE ('now'::text)::time without time zone >= rules.from_time
AND ('now'::text)::time without time zone <= rules.to_time
AND date_part('dow'::text, now()) >= (rules.from_weekday)::double precision
AND date_part('dow'::text, now()) <= (rules.to_weekday)::double precision
) a JOIN (
SELECT site.site, tag.tag AS url_tag, regexp
FROM urls NATURAL JOIN tag NATURAL JOIN site
) b ON (b.url_tag && a.rule_tag));
CREATE PROCEDURAL LANGUAGE plpgsql;
-- general array sorting functions
-- general array sorting and domain processing functions
-- sorts array
CREATE or replace FUNCTION sort(original anyarray) RETURNS anyarray
LANGUAGE sql IMMUTABLE STRICT
AS $_$
select array_agg(item) as result from (select unnest($1) as item order by item) a;
$_$;
|
| ︙ | | |
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
+
|
splitted[0 - pair[2]::integer] := pair[1];
x := x - 1;
end loop;
return array_to_string(splitted, '.');
end;
$_$;
-- functions that works with tables
-- this functions returns id of site
create or replace function get_site(my_site text[]) returns integer
language plpgsql strict
as $$
declare
site_id integer;
begin
|
| ︙ | | |
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
|
240
241
242
243
244
245
246
|
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
|
if not found then
insert into tag (tag) values (usort(my_tag));
select id_tag from tag where usort(my_tag) = tag into tag_id;
end if;
return tag_id;
end;
$$;
-- table to hold all rules
CREATE TABLE rules (
netmask cidr NOT NULL,
redirect_url text DEFAULT 'about::blank'::text NOT NULL,
from_weekday smallint DEFAULT 0 NOT NULL,
to_weekday smallint DEFAULT 6 NOT NULL,
from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
id_tag smallint NOT NULL
);
ALTER TABLE ONLY rules
ADD CONSTRAINT rules_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
-- table to hold site arrays
CREATE TABLE site (
id_site serial,
site text[] NOT NULL
);
ALTER TABLE ONLY site
ADD CONSTRAINT site_pkey PRIMARY KEY (id_site);
CREATE UNIQUE INDEX site_u ON site (usort(site));
CREATE INDEX site_g ON site USING gin (site);
-- table to hold tag combinations
CREATE TABLE tag (
id_tag serial,
tag text[] NOT NULL
);
ALTER TABLE ONLY tag
ADD CONSTRAINT tag_pkey PRIMARY KEY (id_tag);
CREATE UNIQUE INDEX tag_u ON tag (usort(tag));
CREATE INDEX tag_g ON tag USING gin (tag);
-- table to hold tag - site links
CREATE TABLE urls (
date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
id_site smallint NOT NULL,
id_tag smallint NOT NULL,
regexp text
);
CREATE UNIQUE INDEX urls_pkey ON urls USING btree (id_site, regexp);
CREATE INDEX urls_id_tag ON urls USING btree (id_tag);
ALTER TABLE ONLY urls
ADD CONSTRAINT urls_site_f FOREIGN KEY (id_site) REFERENCES site(id_site) MATCH FULL
ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE ONLY urls
ADD CONSTRAINT urls_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
-- rule to join all tables into one to simplify access
-- automaticall uses current day and time data
CREATE VIEW site_rule AS
SELECT a.redirect_url, a.netmask, b.site, b.regexp
FROM ((
SELECT rules.redirect_url, tag.tag AS rule_tag, rules.netmask
FROM rules NATURAL JOIN tag
WHERE ('now'::text)::time without time zone >= rules.from_time
AND ('now'::text)::time without time zone <= rules.to_time
AND date_part('dow'::text, now()) >= (rules.from_weekday)::double precision
AND date_part('dow'::text, now()) <= (rules.to_weekday)::double precision
) a JOIN (
SELECT site.site, tag.tag AS url_tag, regexp
FROM urls NATURAL JOIN tag NATURAL JOIN site
) b ON (b.url_tag && a.rule_tag));
|
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |