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:
4b0aea09f4 2010-10-14 28: create index rules_tag_g on rules using gin (tag);
4b0aea09f4 2010-10-14 29:
bde51dc0c7 2010-08-26 30: -- table to hold tag - site links
bde51dc0c7 2010-08-26 31: CREATE TABLE urls (
bde51dc0c7 2010-08-26 32: date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
bde51dc0c7 2010-08-26 33: site text[] NOT NULL,
bde51dc0c7 2010-08-26 34: tag text[] NOT NULL,
bde51dc0c7 2010-08-26 35: regexp text
bde51dc0c7 2010-08-26 36: );
bde51dc0c7 2010-08-26 37:
4b0aea09f4 2010-10-14 38: create index urls_tag_g on urls using gin (tag);
4b0aea09f4 2010-10-14 39: create index urls_site_g on urls using gin (site);
bde51dc0c7 2010-08-26 40: create unique index urls_rst on urls (regexp, usort(site), usort(tag));
bde51dc0c7 2010-08-26 41:
bde51dc0c7 2010-08-26 42: -- rule to join all tables into one to simplify access
bde51dc0c7 2010-08-26 43: -- automaticall uses current day and time data
bde51dc0c7 2010-08-26 44: create view site_rule as
bde51dc0c7 2010-08-26 45: select redirect_url, netmask, site, regexp
bde51dc0c7 2010-08-26 46: from rules join urls
bde51dc0c7 2010-08-26 47: on (urls.tag && rules.tag)
bde51dc0c7 2010-08-26 48: where ('now'::text)::time without time zone >= from_time
bde51dc0c7 2010-08-26 49: and ('now'::text)::time without time zone <= to_time
bde51dc0c7 2010-08-26 50: and date_part('dow'::text, now()) >= (from_weekday)::double precision
bde51dc0c7 2010-08-26 51: and date_part('dow'::text, now()) <= (to_weekday)::double precision;
bde51dc0c7 2010-08-26 52:
bde51dc0c7 2010-08-26 53: CREATE PROCEDURAL LANGUAGE plpgsql;
09a01deb52 2009-10-01 54:
ddb0e69dd7 2009-10-02 55: -- transforms domain into ordered array for indexing
67e762b39b 2009-10-14 56: CREATE or replace FUNCTION tripdomain(url text) RETURNS text[]
ddb0e69dd7 2009-10-02 57: LANGUAGE plpgsql IMMUTABLE STRICT
ddb0e69dd7 2009-10-02 58: AS $_$
ddb0e69dd7 2009-10-02 59: declare
ddb0e69dd7 2009-10-02 60: result text[];
ddb0e69dd7 2009-10-02 61: splitted text[];
ddb0e69dd7 2009-10-02 62: x integer;
ddb0e69dd7 2009-10-02 63: length integer;
ddb0e69dd7 2009-10-02 64: begin
ddb0e69dd7 2009-10-02 65: splitted := string_to_array($1, '.');
ddb0e69dd7 2009-10-02 66: length := array_length(splitted, 1);
ddb0e69dd7 2009-10-02 67: x := 1;
ddb0e69dd7 2009-10-02 68: loop
ddb0e69dd7 2009-10-02 69: exit when splitted[x] is null;
ddb0e69dd7 2009-10-02 70: result[x] := splitted[x] || ':' || length - x;
ddb0e69dd7 2009-10-02 71: x := x + 1;
ddb0e69dd7 2009-10-02 72: end loop;
ddb0e69dd7 2009-10-02 73: return result;
ddb0e69dd7 2009-10-02 74: end;$_$;
ddb0e69dd7 2009-10-02 75:
ddb0e69dd7 2009-10-02 76: -- transforms ordered array into domain
67e762b39b 2009-10-14 77: create or replace function untrip(site text[]) returns text
ddb0e69dd7 2009-10-02 78: language plpgsql immutable strict
ddb0e69dd7 2009-10-02 79: as $_$
ddb0e69dd7 2009-10-02 80: declare
ddb0e69dd7 2009-10-02 81: x integer;
ddb0e69dd7 2009-10-02 82: splitted text[];
ddb0e69dd7 2009-10-02 83: pair text[];
ddb0e69dd7 2009-10-02 84: begin
ddb0e69dd7 2009-10-02 85: x := array_length(site, 1);
ddb0e69dd7 2009-10-02 86: loop
ddb0e69dd7 2009-10-02 87: exit when site[x] is null;
ddb0e69dd7 2009-10-02 88: pair := string_to_array(site[x], ':');
ddb0e69dd7 2009-10-02 89: splitted[0 - pair[2]::integer] := pair[1];
ddb0e69dd7 2009-10-02 90: x := x - 1;
ddb0e69dd7 2009-10-02 91: end loop;
ddb0e69dd7 2009-10-02 92: return array_to_string(splitted, '.');
09a01deb52 2009-10-01 93: end;
09a01deb52 2009-10-01 94: $_$;