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