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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
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
|
+
-
-
+
+
-
-
-
-
+
+
+
+
-
+
+
-
-
-
-
-
+
+
+
+
+
+
+
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
-
-
-
-
-
+
+
+
+
+
+
-
+
+
-
-
+
+
+
-
-
+
+
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-
-
-
-
-
+
+
+
+
+
+
-
+
+
-
-
+
+
-
-
+
+
-
-
+
+
-
-
-
-
+
+
+
+
+
-
-
+
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
-
-
+
+
-
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
|
CREATE PROCEDURAL LANGUAGE plpgsql;
-- this function adds tag to domain
CREATE FUNCTION mark(domain text, new_tag text) RETURNS void
LANGUAGE plpgsql STRICT
AS $$
my_site text[];
my_site_id smallint;
my_tag text[];
my_tag_id smallint;
my_site := tripdomain(domain);
select id_site from site where my_site = site into my_site_id;
if not found then
insert into site (site) values (my_site);
select id_site from site where my_site = site into my_site_id;
end if;
select tag from urls natural join tag where id_site = my_site_id into my_tag;
if not found then
my_tag := array[new_tag];
else
select array_agg(tag) from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a into
my_tag;
delete from urls where id_site = my_site_id;
end if;
select id_tag from tag where my_tag = tag into my_tag_id;
if not found then
insert into tag (tag) values(my_tag);
select id_tag from tag where my_tag = tag into my_tag_id;
end if;
insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
LANGUAGE plpgsql STRICT
AS $$
my_tag text[];
my_tag_id smallint;
select tag from urls natural join tag where id_site = my_site_id into my_tag;
if not found then
my_tag := array[new_tag];
else
select array_agg(tag) from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a into
my_tag;
delete from urls where id_site = my_site_id;
end if;
-- selecting tags site already have and adding new tag to them
-- note that tags should be sorted to eliminate permutations
select tag from urls natural join tag where id_site = my_site_id into my_tag;
if not found then
-- no records found - creating new tag
my_tag := array[new_tag];
else
-- joining tags
select array_agg(tag)
from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a
into my_tag;
-- deleting old site specification
delete from urls where id_site = my_site_id;
end if;
-- selecting new tag id or adding tag to the table
select id_tag from tag where my_tag = tag into my_tag_id;
if not found then
insert into tag (tag) values(my_tag);
select id_tag from tag where my_tag = tag into my_tag_id;
end if;
insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
LANGUAGE plpgsql IMMUTABLE STRICT
AS $_$
result varchar[];
splitted varchar[];
reversed varchar[];
x integer;
x := 1;
splitted := string_to_array($1, '.');
loop
exit when splitted[x] is null;
if length(splitted[x]) > 0 then
reversed := splitted[x] || reversed;
end if;
x := x + 1;
end loop;
x := 1;
loop
exit when reversed[x] is null;
result[x] := reversed[x] || ':' || x;
x := x + 1;
end loop;
return result;
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
id_site serial,
site text[] NOT NULL
);
CREATE TABLE tag (
id_tag serial,
tag text[] NOT NULL
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
);
CREATE VIEW site_rule AS
SELECT a.redirect_url, a.netmask, b.site 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 FROM ((urls NATURAL JOIN tag) NATURAL JOIN site)) b ON ((b.url_tag && a.rule_tag)));
ALTER TABLE ONLY rules
ADD CONSTRAINT rules_pkey PRIMARY KEY (netmask);
ADD CONSTRAINT site_id PRIMARY KEY (id_site);
ALTER TABLE ONLY tag
ADD CONSTRAINT tag_id PRIMARY KEY (id_tag);
ALTER TABLE ONLY urls
ADD CONSTRAINT urls_pkey PRIMARY KEY (date_added);
|