7828f877c8 2010-08-07 1: -- general array sorting and domain processing functions
e0ecab03f9 2009-10-07 2: -- sorts array
67e762b39b 2009-10-14 3: CREATE or replace FUNCTION sort(original anyarray) RETURNS anyarray
e0ecab03f9 2009-10-07 4: LANGUAGE sql IMMUTABLE STRICT
e0ecab03f9 2009-10-07 5: AS $_$
e0ecab03f9 2009-10-07 6: select array_agg(item) as result from (select unnest($1) as item order by item) a;
e0ecab03f9 2009-10-07 7: $_$;
e0ecab03f9 2009-10-07 8:
e0ecab03f9 2009-10-07 9: -- sorts array and removes duplicates
67e762b39b 2009-10-14 10: CREATE or replace FUNCTION usort(original anyarray) RETURNS anyarray
e0ecab03f9 2009-10-07 11: LANGUAGE sql IMMUTABLE STRICT
e0ecab03f9 2009-10-07 12: AS $_$
e0ecab03f9 2009-10-07 13: select array_agg(item) as result from (select distinct unnest($1) as item order by item) a;
e0ecab03f9 2009-10-07 14: $_$;
bde51dc0c7 2010-08-26 15:
bde51dc0c7 2010-08-26 16: -- general database structure
bde51dc0c7 2010-08-26 17: -- table to hold all rules
bde51dc0c7 2010-08-26 18: CREATE TABLE rules (
bde51dc0c7 2010-08-26 19: netmask cidr NOT NULL,
bde51dc0c7 2010-08-26 20: redirect_url text DEFAULT 'about::blank'::text NOT NULL,
bde51dc0c7 2010-08-26 21: from_weekday smallint DEFAULT 0 NOT NULL,
bde51dc0c7 2010-08-26 22: to_weekday smallint DEFAULT 6 NOT NULL,
bde51dc0c7 2010-08-26 23: from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
bde51dc0c7 2010-08-26 24: to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
bde51dc0c7 2010-08-26 25: tag text[] NOT NULL
bde51dc0c7 2010-08-26 26: );
bde51dc0c7 2010-08-26 27:
bde51dc0c7 2010-08-26 28: -- table to hold tag - site links
bde51dc0c7 2010-08-26 29: CREATE TABLE urls (
bde51dc0c7 2010-08-26 30: date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
bde51dc0c7 2010-08-26 31: site text[] NOT NULL,
bde51dc0c7 2010-08-26 32: tag text[] NOT NULL,
bde51dc0c7 2010-08-26 33: regexp text
bde51dc0c7 2010-08-26 34: );
bde51dc0c7 2010-08-26 35:
bde51dc0c7 2010-08-26 36: create unique index urls_rst on urls (regexp, usort(site), usort(tag));
bde51dc0c7 2010-08-26 37:
bde51dc0c7 2010-08-26 38: -- rule to join all tables into one to simplify access
bde51dc0c7 2010-08-26 39: -- automaticall uses current day and time data
bde51dc0c7 2010-08-26 40: create view site_rule as
bde51dc0c7 2010-08-26 41: select redirect_url, netmask, site, regexp
bde51dc0c7 2010-08-26 42: from rules join urls
bde51dc0c7 2010-08-26 43: on (urls.tag && rules.tag)
bde51dc0c7 2010-08-26 44: where ('now'::text)::time without time zone >= from_time
bde51dc0c7 2010-08-26 45: and ('now'::text)::time without time zone <= to_time
bde51dc0c7 2010-08-26 46: and date_part('dow'::text, now()) >= (from_weekday)::double precision
bde51dc0c7 2010-08-26 47: and date_part('dow'::text, now()) <= (to_weekday)::double precision;
bde51dc0c7 2010-08-26 48:
bde51dc0c7 2010-08-26 49: CREATE PROCEDURAL LANGUAGE plpgsql;
09a01deb52 2009-10-01 50:
ddb0e69dd7 2009-10-02 51: -- transforms domain into ordered array for indexing
67e762b39b 2009-10-14 52: CREATE or replace FUNCTION tripdomain(url text) RETURNS text[]
ddb0e69dd7 2009-10-02 53: LANGUAGE plpgsql IMMUTABLE STRICT
ddb0e69dd7 2009-10-02 54: AS $_$
ddb0e69dd7 2009-10-02 55: declare
ddb0e69dd7 2009-10-02 56: result text[];
ddb0e69dd7 2009-10-02 57: splitted text[];
ddb0e69dd7 2009-10-02 58: x integer;
ddb0e69dd7 2009-10-02 59: length integer;
ddb0e69dd7 2009-10-02 60: begin
ddb0e69dd7 2009-10-02 61: splitted := string_to_array($1, '.');
ddb0e69dd7 2009-10-02 62: length := array_length(splitted, 1);
ddb0e69dd7 2009-10-02 63: x := 1;
ddb0e69dd7 2009-10-02 64: loop
ddb0e69dd7 2009-10-02 65: exit when splitted[x] is null;
ddb0e69dd7 2009-10-02 66: result[x] := splitted[x] || ':' || length - x;
ddb0e69dd7 2009-10-02 67: x := x + 1;
ddb0e69dd7 2009-10-02 68: end loop;
ddb0e69dd7 2009-10-02 69: return result;
ddb0e69dd7 2009-10-02 70: end;$_$;
ddb0e69dd7 2009-10-02 71:
ddb0e69dd7 2009-10-02 72: -- transforms ordered array into domain
67e762b39b 2009-10-14 73: create or replace function untrip(site text[]) returns text
ddb0e69dd7 2009-10-02 74: language plpgsql immutable strict
ddb0e69dd7 2009-10-02 75: as $_$
ddb0e69dd7 2009-10-02 76: declare
ddb0e69dd7 2009-10-02 77: x integer;
ddb0e69dd7 2009-10-02 78: splitted text[];
ddb0e69dd7 2009-10-02 79: pair text[];
ddb0e69dd7 2009-10-02 80: begin
ddb0e69dd7 2009-10-02 81: x := array_length(site, 1);
ddb0e69dd7 2009-10-02 82: loop
ddb0e69dd7 2009-10-02 83: exit when site[x] is null;
ddb0e69dd7 2009-10-02 84: pair := string_to_array(site[x], ':');
ddb0e69dd7 2009-10-02 85: splitted[0 - pair[2]::integer] := pair[1];
ddb0e69dd7 2009-10-02 86: x := x - 1;
ddb0e69dd7 2009-10-02 87: end loop;
ddb0e69dd7 2009-10-02 88: return array_to_string(splitted, '.');
09a01deb52 2009-10-01 89: end;
09a01deb52 2009-10-01 90: $_$;