Squid url redirector

Check-in [12c35e5674]
anonymous

Check-in [12c35e5674]

Overview
Comment:functions reorderd, added missing letter to declaration
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | master | trunk
Files: files | file ages | folders
SHA3-256: 12c35e56747ab116d4c34ddf29ca847f3f5fafd9032205390573862b450d8c37
User & Date: c.kworr@d4daf22a-8aaf-11de-a64d-234b64dd91b4 on 2009-12-15 20:57:00.000
Other Links: branch diff | manifest | tags
Context
2010-03-01
14:29
added logged fail when compiling regexp check-in: d2c54d0451 user: c.kworr@d4daf22a-8aaf-11de-a64d-234b64dd91b4 tags: master, trunk
2009-12-15
20:57
functions reorderd, added missing letter to declaration check-in: 12c35e5674 user: c.kworr@d4daf22a-8aaf-11de-a64d-234b64dd91b4 tags: master, trunk
2009-10-27
15:27
now mark function returns id of site added check-in: 318311c7d2 user: c.kworr@d4daf22a-8aaf-11de-a64d-234b64dd91b4 tags: master, trunk
Changes
Side-by-Side Diff Ignore Whitespace Patch
Modified database.sql from [c376e51c3f] to [0f042d67c0].
︙
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

11
12
13
14
15
16
17






































































18
19
20
21
22
23
24








-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-








-- sorts array and removes duplicates
CREATE or replace FUNCTION usort(original anyarray) RETURNS anyarray
	LANGUAGE sql IMMUTABLE STRICT
	AS $_$
select array_agg(item) as result from (select distinct unnest($1) as item order by item) a;
$_$;

-- 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
	select id_site from site where my_site = site into site_id;
	if not found then
		insert into site (site) values (my_site);
		select id_site from site where my_site = site into site_id;
	end if;
	return site_id;
end;
$$;

create or replace function get_site(domain text) returns integer
	language sql immutable strict
	as $$
select get_site(tripdomain($1)) as result;
$$;

-- this function adds tag to domain
CREATE or replace FUNCTION mark(domain text, new_tag text) RETURNS integer
	LANGUAGE sql immutable STRICT
	AS $$
select mark(get_site($1), $2) as result;
$$;

-- this function adds tag to site by site id
CREATE or replace FUNCTION mark(my_id_site integer, new_tag text) RETURNS integer
	LANGUAGE plpgsql STRICT
	AS $$
declare
	-- maybe check should be added to make sure supplied site id really exists
	my_tag text[];
	my_tag_id integer;
begin
	-- selecting tags site already have and adding new tag to them
	-- note that tags should be sorted to eliminate permutations
	select coalesce(tag, '{}'::text[]) from urls natural left join tag where id_site = my_id_site into my_tag;
	if not found then
		-- no records found - creating new tag
		insert into urls (id_site, id_tag) values (my_id_site, get_tag(array[new_tag]));
	else
		-- joining tags
		select usort(my_tag || array[new_tag]) into my_tag;
		-- updating existing record
		update urls set id_tag = get_tag(my_tag || array[new_tag]) where id_site = my_id_site;
	end if;
	return my_id_site;
end;
$$;

-- this function returns id of tag array
create or replace function get_tag(my_tag text[]) returns integer
	language plpgsql strict
	as $$
declare
	tag_id integer;
begin
	select id_tag from tag where usort(my_tag) = tag into tag_id;
	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;
$$;

-- transforms domain into ordered array for indexing
CREATE or replace FUNCTION tripdomain(url text) RETURNS text[]
	LANGUAGE plpgsql IMMUTABLE STRICT
	AS $_$
declare
	result text[];
	splitted text[];
︙
121
122
123
124
125
126
127






































































128
129
130
131
132
133
134

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








+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+








		pair := string_to_array(site[x], ':');
		splitted[0 - pair[2]::integer] := pair[1];
		x := x - 1;
	end loop;
	return array_to_string(splitted, '.');
end;
$_$;

-- 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
	select id_site from site where my_site = site into site_id;
	if not found then
		insert into site (site) values (my_site);
		select id_site from site where my_site = site into site_id;
	end if;
	return site_id;
end;
$$;

create or replace function get_site(domain text) returns integer
	language sql immutable strict
	as $$
select get_site(tripdomain($1)) as result;
$$;

-- this function adds tag to site by site id
CREATE or replace FUNCTION mark(my_id_site integer, new_tag text) RETURNS integer
	LANGUAGE plpgsql STRICT
	AS $$
declare
	-- maybe check should be added to make sure supplied site id really exists
	my_tag text[];
	my_tag_id integer;
begin
	-- selecting tags site already have and adding new tag to them
	-- note that tags should be sorted to eliminate permutations
	select coalesce(tag, '{}'::text[]) from urls natural left join tag where id_site = my_id_site into my_tag;
	if not found then
		-- no records found - creating new tag
		insert into urls (id_site, id_tag) values (my_id_site, get_tag(array[new_tag]));
	else
		-- joining tags
		select usort(my_tag || array[new_tag]) into my_tag;
		-- updating existing record
		update urls set id_tag = get_tag(my_tag || array[new_tag]) where id_site = my_id_site;
	end if;
	return my_id_site;
end;
$$;

-- this function adds tag to domain
CREATE or replace FUNCTION mark(domain text, new_tag text) RETURNS integer
	LANGUAGE sql immutable STRICT
	AS $$
select mark(get_site($1), $2) as result;
$$;

-- this function returns id of tag array
create or replace function get_tag(my_tag text[]) returns integer
	language plpgsql strict
	as $$
declare
	tag_id integer;
begin
	select id_tag from tag where usort(my_tag) = tag into tag_id;
	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,
︙
167
168
169
170
171
172
173
174

175
176
177
178
179
180
181

167
168
169
170
171
172
173

174
175
176
177
178
179
180
181








-
+








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,
	regex text
	regexp text
);

ALTER TABLE ONLY urls
	ADD CONSTRAINT urls_pkey PRIMARY KEY (date_added);

CREATE UNIQUE INDEX urls_id_site ON urls USING btree (id_site);
︙