7828f877c8 2010-08-07 c.kworr@d4daf: -- general array sorting and domain processing functions
e0ecab03f9 2009-10-07 c.kworr@d4daf: -- sorts array
67e762b39b 2009-10-14 c.kworr@d4daf: CREATE or replace FUNCTION sort(original anyarray) RETURNS anyarray
e0ecab03f9 2009-10-07 c.kworr@d4daf: LANGUAGE sql IMMUTABLE STRICT
e0ecab03f9 2009-10-07 c.kworr@d4daf: AS $_$
e0ecab03f9 2009-10-07 c.kworr@d4daf: select array_agg(item) as result from (select unnest($1) as item order by item) a;
e0ecab03f9 2009-10-07 c.kworr@d4daf: $_$;
e0ecab03f9 2009-10-07 c.kworr@d4daf:
e0ecab03f9 2009-10-07 c.kworr@d4daf: -- sorts array and removes duplicates
67e762b39b 2009-10-14 c.kworr@d4daf: CREATE or replace FUNCTION usort(original anyarray) RETURNS anyarray
e0ecab03f9 2009-10-07 c.kworr@d4daf: LANGUAGE sql IMMUTABLE STRICT
e0ecab03f9 2009-10-07 c.kworr@d4daf: AS $_$
e0ecab03f9 2009-10-07 c.kworr@d4daf: select array_agg(item) as result from (select distinct unnest($1) as item order by item) a;
e0ecab03f9 2009-10-07 c.kworr@d4daf: $_$;
bde51dc0c7 2010-08-26 c.kworr@d4daf:
bde51dc0c7 2010-08-26 c.kworr@d4daf: -- general database structure
bde51dc0c7 2010-08-26 c.kworr@d4daf: -- table to hold all rules
bde51dc0c7 2010-08-26 c.kworr@d4daf: CREATE TABLE rules (
bde51dc0c7 2010-08-26 c.kworr@d4daf: netmask cidr NOT NULL,
bde51dc0c7 2010-08-26 c.kworr@d4daf: redirect_url text DEFAULT 'about::blank'::text NOT NULL,
bde51dc0c7 2010-08-26 c.kworr@d4daf: from_weekday smallint DEFAULT 0 NOT NULL,
bde51dc0c7 2010-08-26 c.kworr@d4daf: to_weekday smallint DEFAULT 6 NOT NULL,
bde51dc0c7 2010-08-26 c.kworr@d4daf: from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
bde51dc0c7 2010-08-26 c.kworr@d4daf: to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
bde51dc0c7 2010-08-26 c.kworr@d4daf: tag text[] NOT NULL
bde51dc0c7 2010-08-26 c.kworr@d4daf: );
bde51dc0c7 2010-08-26 c.kworr@d4daf:
bde51dc0c7 2010-08-26 c.kworr@d4daf: -- table to hold tag - site links
bde51dc0c7 2010-08-26 c.kworr@d4daf: CREATE TABLE urls (
bde51dc0c7 2010-08-26 c.kworr@d4daf: date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
bde51dc0c7 2010-08-26 c.kworr@d4daf: site text[] NOT NULL,
bde51dc0c7 2010-08-26 c.kworr@d4daf: tag text[] NOT NULL,
bde51dc0c7 2010-08-26 c.kworr@d4daf: regexp text
bde51dc0c7 2010-08-26 c.kworr@d4daf: );
bde51dc0c7 2010-08-26 c.kworr@d4daf:
bde51dc0c7 2010-08-26 c.kworr@d4daf: create unique index urls_rst on urls (regexp, usort(site), usort(tag));
bde51dc0c7 2010-08-26 c.kworr@d4daf:
bde51dc0c7 2010-08-26 c.kworr@d4daf: -- rule to join all tables into one to simplify access
bde51dc0c7 2010-08-26 c.kworr@d4daf: -- automaticall uses current day and time data
bde51dc0c7 2010-08-26 c.kworr@d4daf: create view site_rule as
bde51dc0c7 2010-08-26 c.kworr@d4daf: select redirect_url, netmask, site, regexp
bde51dc0c7 2010-08-26 c.kworr@d4daf: from rules join urls
bde51dc0c7 2010-08-26 c.kworr@d4daf: on (urls.tag && rules.tag)
bde51dc0c7 2010-08-26 c.kworr@d4daf: where ('now'::text)::time without time zone >= from_time
bde51dc0c7 2010-08-26 c.kworr@d4daf: and ('now'::text)::time without time zone <= to_time
bde51dc0c7 2010-08-26 c.kworr@d4daf: and date_part('dow'::text, now()) >= (from_weekday)::double precision
bde51dc0c7 2010-08-26 c.kworr@d4daf: and date_part('dow'::text, now()) <= (to_weekday)::double precision;
bde51dc0c7 2010-08-26 c.kworr@d4daf:
bde51dc0c7 2010-08-26 c.kworr@d4daf: CREATE PROCEDURAL LANGUAGE plpgsql;
09a01deb52 2009-10-01 c.kworr@d4daf:
ddb0e69dd7 2009-10-02 c.kworr@d4daf: -- transforms domain into ordered array for indexing
67e762b39b 2009-10-14 c.kworr@d4daf: CREATE or replace FUNCTION tripdomain(url text) RETURNS text[]
ddb0e69dd7 2009-10-02 c.kworr@d4daf: LANGUAGE plpgsql IMMUTABLE STRICT
ddb0e69dd7 2009-10-02 c.kworr@d4daf: AS $_$
ddb0e69dd7 2009-10-02 c.kworr@d4daf: declare
ddb0e69dd7 2009-10-02 c.kworr@d4daf: result text[];
ddb0e69dd7 2009-10-02 c.kworr@d4daf: splitted text[];
ddb0e69dd7 2009-10-02 c.kworr@d4daf: x integer;
ddb0e69dd7 2009-10-02 c.kworr@d4daf: length integer;
ddb0e69dd7 2009-10-02 c.kworr@d4daf: begin
ddb0e69dd7 2009-10-02 c.kworr@d4daf: splitted := string_to_array($1, '.');
ddb0e69dd7 2009-10-02 c.kworr@d4daf: length := array_length(splitted, 1);
ddb0e69dd7 2009-10-02 c.kworr@d4daf: x := 1;
ddb0e69dd7 2009-10-02 c.kworr@d4daf: loop
ddb0e69dd7 2009-10-02 c.kworr@d4daf: exit when splitted[x] is null;
ddb0e69dd7 2009-10-02 c.kworr@d4daf: result[x] := splitted[x] || ':' || length - x;
ddb0e69dd7 2009-10-02 c.kworr@d4daf: x := x + 1;
ddb0e69dd7 2009-10-02 c.kworr@d4daf: end loop;
ddb0e69dd7 2009-10-02 c.kworr@d4daf: return result;
ddb0e69dd7 2009-10-02 c.kworr@d4daf: end;$_$;
ddb0e69dd7 2009-10-02 c.kworr@d4daf:
ddb0e69dd7 2009-10-02 c.kworr@d4daf: -- transforms ordered array into domain
67e762b39b 2009-10-14 c.kworr@d4daf: create or replace function untrip(site text[]) returns text
ddb0e69dd7 2009-10-02 c.kworr@d4daf: language plpgsql immutable strict
ddb0e69dd7 2009-10-02 c.kworr@d4daf: as $_$
ddb0e69dd7 2009-10-02 c.kworr@d4daf: declare
ddb0e69dd7 2009-10-02 c.kworr@d4daf: x integer;
ddb0e69dd7 2009-10-02 c.kworr@d4daf: splitted text[];
ddb0e69dd7 2009-10-02 c.kworr@d4daf: pair text[];
ddb0e69dd7 2009-10-02 c.kworr@d4daf: begin
ddb0e69dd7 2009-10-02 c.kworr@d4daf: x := array_length(site, 1);
ddb0e69dd7 2009-10-02 c.kworr@d4daf: loop
ddb0e69dd7 2009-10-02 c.kworr@d4daf: exit when site[x] is null;
ddb0e69dd7 2009-10-02 c.kworr@d4daf: pair := string_to_array(site[x], ':');
ddb0e69dd7 2009-10-02 c.kworr@d4daf: splitted[0 - pair[2]::integer] := pair[1];
ddb0e69dd7 2009-10-02 c.kworr@d4daf: x := x - 1;
ddb0e69dd7 2009-10-02 c.kworr@d4daf: end loop;
ddb0e69dd7 2009-10-02 c.kworr@d4daf: return array_to_string(splitted, '.');
09a01deb52 2009-10-01 c.kworr@d4daf: end;
09a01deb52 2009-10-01 c.kworr@d4daf: $_$;