ADDED database.sql Index: database.sql ================================================================== --- /dev/null +++ database.sql @@ -0,0 +1,142 @@ +CREATE PROCEDURAL LANGUAGE plpgsql; + +CREATE FUNCTION mark(domain text, new_tag text) RETURNS void + LANGUAGE plpgsql STRICT + AS $$ +declare + my_site varchar[]; + my_site_id smallint; + my_tag text[]; + my_tag_id smallint; +begin + 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 := 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); +end;$$; + +CREATE FUNCTION mark(my_site_id smallint, new_tag text) RETURNS void + LANGUAGE plpgsql STRICT + AS $$ +declare + my_tag text[]; + my_tag_id smallint; +begin + + select tag from urls natural join tag where id_site = my_site_id into my_tag; + if not found then + my_tag := 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); +end;$$; + +CREATE FUNCTION tripdomain(url character varying) RETURNS character varying[] + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +declare + result varchar[]; + splitted varchar[]; + reversed varchar[]; + x integer; +begin + 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; +end; +$_$; + +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 +); + + +CREATE TABLE site ( + 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); + +ALTER TABLE ONLY site + 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); + +CREATE UNIQUE INDEX site_s ON site USING btree (site); + +CREATE INDEX site_sg ON site USING gin (site); + +CREATE INDEX tag_g ON tag USING gin (tag); + +CREATE UNIQUE INDEX tag_s ON tag USING btree (tag); + +CREATE UNIQUE INDEX urls_id_site ON urls USING btree (id_site); Index: squid-tagger ================================================================== --- squid-tagger +++ squid-tagger @@ -21,38 +21,31 @@ def notice(self, message): if not self._silent: self._syslog.syslog(self._syslog.LOG_NOTICE, message) class tagDB: - __slots__ = frozenset(['_prepared', '_db']) + __slots__ = frozenset(['_prepared', '_check_stmt', '_db']) def __init__(self): self._prepared = set() self._db = False + self._check_stmt = self._curs().prepare("select redirect_url from site_rule where site <@ tripdomain($1) and netmask >> $2::text::inet limit 1") def _curs(self): if not self._db: config.section('database') - # needs thinking - #connector = postgresql.api.Connector( - #user = config['user'], password = config['password'], - #database = config['database'], self._db = postgresql.open( 'pq://{0}:{1}@{2}/{3}'.format( config['user'], config['password'], config['host'], config['database'], - )) + ) ) return(self._db) def check(self, ip_address, site): - # doesn't work for inet - #stmt = self._curs().prepare("select redirect_url from site_rules where site <@ tripdomain($1) and netmask >> '$2' limit 1") - #result = stmt(site, ip_address) - stmt = self._curs().prepare("select redirect_url from site_rules where site <@ tripdomain('{0}') and netmask >> '{1}' limit 1".format(site, ip_address)) - result = stmt() + result = self._check_stmt(site, ip_address) if len(result) > 0: return result[0] else: return None