09a01deb52 2009-10-01 1: CREATE PROCEDURAL LANGUAGE plpgsql;
09a01deb52 2009-10-01 2:
ddb0e69dd7 2009-10-02 3: -- this function adds tag to domain
ddb0e69dd7 2009-10-02 4: CREATE FUNCTION mark(domain text, new_tag text) RETURNS void
ddb0e69dd7 2009-10-02 5: LANGUAGE plpgsql STRICT
ddb0e69dd7 2009-10-02 6: AS $$
ddb0e69dd7 2009-10-02 7: declare
ddb0e69dd7 2009-10-02 8: my_site text[];
ddb0e69dd7 2009-10-02 9: my_site_id smallint;
ddb0e69dd7 2009-10-02 10: my_tag text[];
ddb0e69dd7 2009-10-02 11: my_tag_id smallint;
ddb0e69dd7 2009-10-02 12: begin
ddb0e69dd7 2009-10-02 13: my_site := tripdomain(domain);
ddb0e69dd7 2009-10-02 14:
ddb0e69dd7 2009-10-02 15: -- selecting site id from table or adding site to the table
ddb0e69dd7 2009-10-02 16: select id_site from site where my_site = site into my_site_id;
ddb0e69dd7 2009-10-02 17: if not found then
ddb0e69dd7 2009-10-02 18: insert into site (site) values (my_site);
ddb0e69dd7 2009-10-02 19: select id_site from site where my_site = site into my_site_id;
ddb0e69dd7 2009-10-02 20: end if;
ddb0e69dd7 2009-10-02 21:
ddb0e69dd7 2009-10-02 22: -- selecting tags site already have and adding new tag to them
ddb0e69dd7 2009-10-02 23: -- note that tags should be sorted to eliminate permutations
ddb0e69dd7 2009-10-02 24: select tag from urls natural join tag where id_site = my_site_id into my_tag;
ddb0e69dd7 2009-10-02 25: if not found then
ddb0e69dd7 2009-10-02 26: -- no records found - creating new tag
ddb0e69dd7 2009-10-02 27: my_tag := array[new_tag];
ddb0e69dd7 2009-10-02 28: else
ddb0e69dd7 2009-10-02 29: -- joining tags
ddb0e69dd7 2009-10-02 30: select array_agg(tag)
ddb0e69dd7 2009-10-02 31: from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a
ddb0e69dd7 2009-10-02 32: into my_tag;
ddb0e69dd7 2009-10-02 33: -- deleting old site specification
ddb0e69dd7 2009-10-02 34: delete from urls where id_site = my_site_id;
ddb0e69dd7 2009-10-02 35: end if;
ddb0e69dd7 2009-10-02 36:
ddb0e69dd7 2009-10-02 37: -- selecting new tag id or adding tag to the table
ddb0e69dd7 2009-10-02 38: select id_tag from tag where my_tag = tag into my_tag_id;
ddb0e69dd7 2009-10-02 39: if not found then
ddb0e69dd7 2009-10-02 40: insert into tag (tag) values(my_tag);
ddb0e69dd7 2009-10-02 41: select id_tag from tag where my_tag = tag into my_tag_id;
ddb0e69dd7 2009-10-02 42: end if;
ddb0e69dd7 2009-10-02 43:
ddb0e69dd7 2009-10-02 44: -- adding new site specification
ddb0e69dd7 2009-10-02 45: insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
ddb0e69dd7 2009-10-02 46: end;$$;
ddb0e69dd7 2009-10-02 47:
ddb0e69dd7 2009-10-02 48: -- this function adds tag to site by site id
ddb0e69dd7 2009-10-02 49: CREATE FUNCTION mark(my_site_id smallint, new_tag text) RETURNS void
ddb0e69dd7 2009-10-02 50: LANGUAGE plpgsql STRICT
ddb0e69dd7 2009-10-02 51: AS $$
ddb0e69dd7 2009-10-02 52: declare
ddb0e69dd7 2009-10-02 53: -- maybe check should be added to make sure supplied site id really exists
ddb0e69dd7 2009-10-02 54: my_tag text[];
ddb0e69dd7 2009-10-02 55: my_tag_id smallint;
ddb0e69dd7 2009-10-02 56: begin
ddb0e69dd7 2009-10-02 57: -- selecting tags site already have and adding new tag to them
ddb0e69dd7 2009-10-02 58: -- note that tags should be sorted to eliminate permutations
ddb0e69dd7 2009-10-02 59: select tag from urls natural join tag where id_site = my_site_id into my_tag;
ddb0e69dd7 2009-10-02 60: if not found then
ddb0e69dd7 2009-10-02 61: -- no records found - creating new tag
ddb0e69dd7 2009-10-02 62: my_tag := array[new_tag];
ddb0e69dd7 2009-10-02 63: else
ddb0e69dd7 2009-10-02 64: -- joining tags
ddb0e69dd7 2009-10-02 65: select array_agg(tag)
ddb0e69dd7 2009-10-02 66: from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a
ddb0e69dd7 2009-10-02 67: into my_tag;
ddb0e69dd7 2009-10-02 68: -- deleting old site specification
ddb0e69dd7 2009-10-02 69: delete from urls where id_site = my_site_id;
ddb0e69dd7 2009-10-02 70: end if;
ddb0e69dd7 2009-10-02 71:
ddb0e69dd7 2009-10-02 72: -- selecting new tag id or adding tag to the table
ddb0e69dd7 2009-10-02 73: select id_tag from tag where my_tag = tag into my_tag_id;
ddb0e69dd7 2009-10-02 74: if not found then
ddb0e69dd7 2009-10-02 75: insert into tag (tag) values(my_tag);
ddb0e69dd7 2009-10-02 76: select id_tag from tag where my_tag = tag into my_tag_id;
ddb0e69dd7 2009-10-02 77: end if;
ddb0e69dd7 2009-10-02 78:
ddb0e69dd7 2009-10-02 79: -- adding new site specification
ddb0e69dd7 2009-10-02 80: insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
ddb0e69dd7 2009-10-02 81: end;$$;
ddb0e69dd7 2009-10-02 82:
ddb0e69dd7 2009-10-02 83: -- transforms domain into ordered array for indexing
ddb0e69dd7 2009-10-02 84: CREATE FUNCTION tripdomain(url text) RETURNS text[]
ddb0e69dd7 2009-10-02 85: LANGUAGE plpgsql IMMUTABLE STRICT
ddb0e69dd7 2009-10-02 86: AS $_$
ddb0e69dd7 2009-10-02 87: declare
ddb0e69dd7 2009-10-02 88: result text[];
ddb0e69dd7 2009-10-02 89: splitted text[];
ddb0e69dd7 2009-10-02 90: x integer;
ddb0e69dd7 2009-10-02 91: length integer;
ddb0e69dd7 2009-10-02 92: begin
ddb0e69dd7 2009-10-02 93: splitted := string_to_array($1, '.');
ddb0e69dd7 2009-10-02 94: length := array_length(splitted, 1);
ddb0e69dd7 2009-10-02 95: x := 1;
ddb0e69dd7 2009-10-02 96: loop
ddb0e69dd7 2009-10-02 97: exit when splitted[x] is null;
ddb0e69dd7 2009-10-02 98: result[x] := splitted[x] || ':' || length - x;
ddb0e69dd7 2009-10-02 99: x := x + 1;
ddb0e69dd7 2009-10-02 100: end loop;
ddb0e69dd7 2009-10-02 101: return result;
ddb0e69dd7 2009-10-02 102: end;$_$;
ddb0e69dd7 2009-10-02 103:
ddb0e69dd7 2009-10-02 104: -- transforms ordered array into domain
ddb0e69dd7 2009-10-02 105: create function untrip(site text[]) returns text
ddb0e69dd7 2009-10-02 106: language plpgsql immutable strict
ddb0e69dd7 2009-10-02 107: as $_$
ddb0e69dd7 2009-10-02 108: declare
ddb0e69dd7 2009-10-02 109: x integer;
ddb0e69dd7 2009-10-02 110: splitted text[];
ddb0e69dd7 2009-10-02 111: pair text[];
ddb0e69dd7 2009-10-02 112: begin
ddb0e69dd7 2009-10-02 113: x := array_length(site, 1);
ddb0e69dd7 2009-10-02 114: loop
ddb0e69dd7 2009-10-02 115: exit when site[x] is null;
ddb0e69dd7 2009-10-02 116: pair := string_to_array(site[x], ':');
ddb0e69dd7 2009-10-02 117: splitted[0 - pair[2]::integer] := pair[1];
ddb0e69dd7 2009-10-02 118: x := x - 1;
ddb0e69dd7 2009-10-02 119: end loop;
ddb0e69dd7 2009-10-02 120: return array_to_string(splitted, '.');
09a01deb52 2009-10-01 121: end;
09a01deb52 2009-10-01 122: $_$;
09a01deb52 2009-10-01 123:
ddb0e69dd7 2009-10-02 124: -- table to hold all rules
ddb0e69dd7 2009-10-02 125: CREATE TABLE rules (
ddb0e69dd7 2009-10-02 126: netmask cidr NOT NULL,
ddb0e69dd7 2009-10-02 127: redirect_url text DEFAULT 'about::blank'::text NOT NULL,
ddb0e69dd7 2009-10-02 128: from_weekday smallint DEFAULT 0 NOT NULL,
ddb0e69dd7 2009-10-02 129: to_weekday smallint DEFAULT 6 NOT NULL,
ddb0e69dd7 2009-10-02 130: from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
ddb0e69dd7 2009-10-02 131: to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
ddb0e69dd7 2009-10-02 132: id_tag smallint NOT NULL
ddb0e69dd7 2009-10-02 133: );
ddb0e69dd7 2009-10-02 134:
ddb0e69dd7 2009-10-02 135: ALTER TABLE ONLY rules
ddb0e69dd7 2009-10-02 136: ADD CONSTRAINT rules_pkey PRIMARY KEY (netmask);
ddb0e69dd7 2009-10-02 137:
ddb0e69dd7 2009-10-02 138: -- table to hold site arrays
ddb0e69dd7 2009-10-02 139: -- maybe full original domain should be added with trigger to leave tripdomain function away
ddb0e69dd7 2009-10-02 140: CREATE TABLE site (
ddb0e69dd7 2009-10-02 141: id_site serial,
ddb0e69dd7 2009-10-02 142: site text[] NOT NULL,
ddb0e69dd7 2009-10-02 143: domain not null
ddb0e69dd7 2009-10-02 144: );
ddb0e69dd7 2009-10-02 145:
ddb0e69dd7 2009-10-02 146: ALTER TABLE ONLY site
ddb0e69dd7 2009-10-02 147: ADD CONSTRAINT site_id PRIMARY KEY (id_site);
09a01deb52 2009-10-01 148:
09a01deb52 2009-10-01 149: CREATE UNIQUE INDEX site_s ON site USING btree (site);
09a01deb52 2009-10-01 150:
09a01deb52 2009-10-01 151: CREATE INDEX site_sg ON site USING gin (site);
09a01deb52 2009-10-01 152:
ddb0e69dd7 2009-10-02 153: -- table to hold tag combinations
ddb0e69dd7 2009-10-02 154: CREATE TABLE tag (
ddb0e69dd7 2009-10-02 155: id_tag serial,
ddb0e69dd7 2009-10-02 156: tag text[] NOT NULL
ddb0e69dd7 2009-10-02 157: );
ddb0e69dd7 2009-10-02 158:
ddb0e69dd7 2009-10-02 159: ALTER TABLE ONLY tag
ddb0e69dd7 2009-10-02 160: ADD CONSTRAINT tag_id PRIMARY KEY (id_tag);
ddb0e69dd7 2009-10-02 161:
09a01deb52 2009-10-01 162: CREATE INDEX tag_g ON tag USING gin (tag);
09a01deb52 2009-10-01 163:
09a01deb52 2009-10-01 164: CREATE UNIQUE INDEX tag_s ON tag USING btree (tag);
09a01deb52 2009-10-01 165:
ddb0e69dd7 2009-10-02 166: -- table to hold tag - site links
ddb0e69dd7 2009-10-02 167: CREATE TABLE urls (
ddb0e69dd7 2009-10-02 168: date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
ddb0e69dd7 2009-10-02 169: id_site smallint NOT NULL,
ddb0e69dd7 2009-10-02 170: id_tag smallint NOT NULL
ddb0e69dd7 2009-10-02 171: );
ddb0e69dd7 2009-10-02 172:
ddb0e69dd7 2009-10-02 173: ALTER TABLE ONLY urls
ddb0e69dd7 2009-10-02 174: ADD CONSTRAINT urls_pkey PRIMARY KEY (date_added);
ddb0e69dd7 2009-10-02 175:
09a01deb52 2009-10-01 176: CREATE UNIQUE INDEX urls_id_site ON urls USING btree (id_site);
ddb0e69dd7 2009-10-02 177:
ddb0e69dd7 2009-10-02 178: -- rule to join all tables into one to simplify access
ddb0e69dd7 2009-10-02 179: -- automaticall uses current day and time data
ddb0e69dd7 2009-10-02 180: CREATE VIEW site_rule AS
ddb0e69dd7 2009-10-02 181: SELECT a.redirect_url, a.netmask, b.site
ddb0e69dd7 2009-10-02 182: FROM ((
ddb0e69dd7 2009-10-02 183: SELECT rules.redirect_url, tag.tag AS rule_tag, rules.netmask
ddb0e69dd7 2009-10-02 184: FROM (rules NATURAL JOIN tag)
ddb0e69dd7 2009-10-02 185: WHERE ((((('now'::text)::time without time zone >= rules.from_time)
ddb0e69dd7 2009-10-02 186: AND (('now'::text)::time without time zone <= rules.to_time))
ddb0e69dd7 2009-10-02 187: AND (date_part('dow'::text, now()) >= (rules.from_weekday)::double precision))
ddb0e69dd7 2009-10-02 188: AND (date_part('dow'::text, now()) <= (rules.to_weekday)::double precision))
ddb0e69dd7 2009-10-02 189: ) a JOIN (
ddb0e69dd7 2009-10-02 190: SELECT site.site, tag.tag AS url_tag
ddb0e69dd7 2009-10-02 191: FROM ((urls NATURAL JOIN tag) NATURAL JOIN site)
ddb0e69dd7 2009-10-02 192: ) b ON ((b.url_tag && a.rule_tag)));