Lines of database.sql from check-in 5f6dc4c42d that are changed by the sequence of edits moving toward check-in bde51dc0c7:

5f6dc4c42d 2010-08-13    1: -- general database structure
5f6dc4c42d 2010-08-13    2: -- table to hold all rules
5f6dc4c42d 2010-08-13    3: CREATE TABLE rules (
5f6dc4c42d 2010-08-13    4: 	netmask cidr NOT NULL,
5f6dc4c42d 2010-08-13    5: 	redirect_url text DEFAULT 'about::blank'::text NOT NULL,
5f6dc4c42d 2010-08-13    6: 	from_weekday smallint DEFAULT 0 NOT NULL,
5f6dc4c42d 2010-08-13    7: 	to_weekday smallint DEFAULT 6 NOT NULL,
5f6dc4c42d 2010-08-13    8: 	from_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
5f6dc4c42d 2010-08-13    9: 	to_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
5f6dc4c42d 2010-08-13   10: 	id_tag smallint NOT NULL
5f6dc4c42d 2010-08-13   11: );
5f6dc4c42d 2010-08-13   12: 
5f6dc4c42d 2010-08-13   13: ALTER TABLE ONLY rules
5f6dc4c42d 2010-08-13   14: 	ADD CONSTRAINT rules_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
5f6dc4c42d 2010-08-13   15: 	ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
5f6dc4c42d 2010-08-13   16: 
5f6dc4c42d 2010-08-13   17: -- table to hold site arrays
5f6dc4c42d 2010-08-13   18: CREATE TABLE site (
5f6dc4c42d 2010-08-13   19: 	id_site serial,
5f6dc4c42d 2010-08-13   20: 	site text[] NOT NULL
5f6dc4c42d 2010-08-13   21: );
5f6dc4c42d 2010-08-13   22: 
5f6dc4c42d 2010-08-13   23: ALTER TABLE ONLY site
5f6dc4c42d 2010-08-13   24: 	ADD CONSTRAINT site_pkey PRIMARY KEY (id_site);
5f6dc4c42d 2010-08-13   25: 
5f6dc4c42d 2010-08-13   26: CREATE UNIQUE INDEX site_u ON site (usort(site));
5f6dc4c42d 2010-08-13   27: 
5f6dc4c42d 2010-08-13   28: CREATE INDEX site_g ON site USING gin (site);
5f6dc4c42d 2010-08-13   29: 
5f6dc4c42d 2010-08-13   30: -- table to hold tag combinations
5f6dc4c42d 2010-08-13   31: CREATE TABLE tag (
5f6dc4c42d 2010-08-13   32: 	id_tag serial,
5f6dc4c42d 2010-08-13   33: 	tag text[] NOT NULL
5f6dc4c42d 2010-08-13   34: );
5f6dc4c42d 2010-08-13   35: 
5f6dc4c42d 2010-08-13   36: ALTER TABLE ONLY tag
5f6dc4c42d 2010-08-13   37: 	ADD CONSTRAINT tag_pkey PRIMARY KEY (id_tag);
5f6dc4c42d 2010-08-13   38: 
5f6dc4c42d 2010-08-13   39: CREATE UNIQUE INDEX tag_u ON tag (usort(tag));
5f6dc4c42d 2010-08-13   40: 
5f6dc4c42d 2010-08-13   41: CREATE INDEX tag_g ON tag USING gin (tag);
5f6dc4c42d 2010-08-13   42: 
5f6dc4c42d 2010-08-13   43: -- table to hold tag - site links
5f6dc4c42d 2010-08-13   44: CREATE TABLE urls (
5f6dc4c42d 2010-08-13   45: 	date_added timestamp without time zone DEFAULT ('now'::text)::timestamp(0) without time zone NOT NULL,
5f6dc4c42d 2010-08-13   46: 	id_site integer NOT NULL,
5f6dc4c42d 2010-08-13   47: 	id_tag smallint NOT NULL,
5f6dc4c42d 2010-08-13   48: 	regexp text
5f6dc4c42d 2010-08-13   49: );
5f6dc4c42d 2010-08-13   50: 
5f6dc4c42d 2010-08-13   51: CREATE UNIQUE INDEX urls_pkey ON urls USING btree (id_site, regexp);
5f6dc4c42d 2010-08-13   52: 
5f6dc4c42d 2010-08-13   53: CREATE INDEX urls_id_tag ON urls USING btree (id_tag);
5f6dc4c42d 2010-08-13   54: 
5f6dc4c42d 2010-08-13   55: ALTER TABLE ONLY urls
5f6dc4c42d 2010-08-13   56: 	ADD CONSTRAINT urls_site_f FOREIGN KEY (id_site) REFERENCES site(id_site) MATCH FULL
5f6dc4c42d 2010-08-13   57: 	ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
5f6dc4c42d 2010-08-13   58: 
5f6dc4c42d 2010-08-13   59: ALTER TABLE ONLY urls
5f6dc4c42d 2010-08-13   60: 	ADD CONSTRAINT urls_tag_f FOREIGN KEY (id_tag) REFERENCES tag(id_tag) MATCH FULL
5f6dc4c42d 2010-08-13   61: 	ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
5f6dc4c42d 2010-08-13   62: 
5f6dc4c42d 2010-08-13   63: -- rule to join all tables into one to simplify access
5f6dc4c42d 2010-08-13   64: -- automaticall uses current day and time data
5f6dc4c42d 2010-08-13   65: CREATE VIEW site_rule AS
5f6dc4c42d 2010-08-13   66: SELECT a.redirect_url, a.netmask, b.site, b.regexp
5f6dc4c42d 2010-08-13   67: FROM ((
5f6dc4c42d 2010-08-13   68: 	SELECT rules.redirect_url, tag.tag AS rule_tag, rules.netmask
5f6dc4c42d 2010-08-13   69: 	FROM rules NATURAL JOIN tag
5f6dc4c42d 2010-08-13   70: 	WHERE ('now'::text)::time without time zone >= rules.from_time
5f6dc4c42d 2010-08-13   71: 		AND ('now'::text)::time without time zone <= rules.to_time
5f6dc4c42d 2010-08-13   72: 		AND date_part('dow'::text, now()) >= (rules.from_weekday)::double precision
5f6dc4c42d 2010-08-13   73: 		AND date_part('dow'::text, now()) <= (rules.to_weekday)::double precision
5f6dc4c42d 2010-08-13   74: ) a JOIN (
5f6dc4c42d 2010-08-13   75: 	SELECT site.site, tag.tag AS url_tag, regexp
5f6dc4c42d 2010-08-13   76: 	FROM urls NATURAL JOIN tag NATURAL JOIN site
5f6dc4c42d 2010-08-13   77: ) b ON (b.url_tag && a.rule_tag));
5f6dc4c42d 2010-08-13   78: 
5f6dc4c42d 2010-08-13   79: CREATE PROCEDURAL LANGUAGE plpgsql;
5f6dc4c42d 2010-08-13   80: 
                        81: -- general array sorting and domain processing functions
                        82: -- sorts array
                        83: CREATE or replace FUNCTION sort(original anyarray) RETURNS anyarray
                        84: 	LANGUAGE sql IMMUTABLE STRICT
                        85: 	AS $_$
                        86: select array_agg(item) as result from (select unnest($1) as item order by item) a;
                        87: $_$;
                        88: 
                        89: -- sorts array and removes duplicates
                        90: CREATE or replace FUNCTION usort(original anyarray) RETURNS anyarray
                        91: 	LANGUAGE sql IMMUTABLE STRICT
                        92: 	AS $_$
                        93: select array_agg(item) as result from (select distinct unnest($1) as item order by item) a;
                        94: $_$;
                        95: 
                        96: -- transforms domain into ordered array for indexing
                        97: CREATE or replace FUNCTION tripdomain(url text) RETURNS text[]
                        98: 	LANGUAGE plpgsql IMMUTABLE STRICT
                        99: 	AS $_$
                       100: declare
                       101: 	result text[];
                       102: 	splitted text[];
                       103: 	x integer;
                       104: 	length integer;
                       105: begin
                       106: 	splitted := string_to_array($1, '.');
                       107: 	length := array_length(splitted, 1);
                       108: 	x := 1;
                       109: 	loop
                       110: 		exit when splitted[x] is null;
                       111: 		result[x] := splitted[x] || ':' || length - x;
                       112: 		x := x + 1;
                       113: 	end loop;
                       114: 	return result;
                       115: end;$_$;
                       116: 
                       117: -- transforms ordered array into domain
                       118: create or replace function untrip(site text[]) returns text
                       119: 	language plpgsql immutable strict
                       120: 	as $_$
                       121: declare
                       122: 	x integer;
                       123: 	splitted text[];
                       124: 	pair text[];
                       125: begin
                       126: 	x := array_length(site, 1);
                       127: 	loop 
                       128: 		exit when site[x] is null;
                       129: 		pair := string_to_array(site[x], ':');
                       130: 		splitted[0 - pair[2]::integer] := pair[1];
                       131: 		x := x - 1;
                       132: 	end loop;
                       133: 	return array_to_string(splitted, '.');
                       134: end;
                       135: $_$;
5f6dc4c42d 2010-08-13  136: 
5f6dc4c42d 2010-08-13  137: -- functions that works with tables
5f6dc4c42d 2010-08-13  138: -- this functions returns id of site
5f6dc4c42d 2010-08-13  139: create or replace function get_site(my_site text[]) returns integer
5f6dc4c42d 2010-08-13  140: 	language plpgsql strict
5f6dc4c42d 2010-08-13  141: 	as $$
5f6dc4c42d 2010-08-13  142: declare
5f6dc4c42d 2010-08-13  143: 	site_id integer;
5f6dc4c42d 2010-08-13  144: begin
5f6dc4c42d 2010-08-13  145: 	select id_site from site where my_site = site into site_id;
5f6dc4c42d 2010-08-13  146: 	if not found then
5f6dc4c42d 2010-08-13  147: 		insert into site (site) values (my_site);
5f6dc4c42d 2010-08-13  148: 		select id_site from site where my_site = site into site_id;
5f6dc4c42d 2010-08-13  149: 	end if;
5f6dc4c42d 2010-08-13  150: 	return site_id;
5f6dc4c42d 2010-08-13  151: end;
5f6dc4c42d 2010-08-13  152: $$;
5f6dc4c42d 2010-08-13  153: 
5f6dc4c42d 2010-08-13  154: create or replace function get_site(domain text) returns integer
5f6dc4c42d 2010-08-13  155: 	language sql immutable strict
5f6dc4c42d 2010-08-13  156: 	as $$
5f6dc4c42d 2010-08-13  157: select get_site(tripdomain($1)) as result;
5f6dc4c42d 2010-08-13  158: $$;
5f6dc4c42d 2010-08-13  159: 
5f6dc4c42d 2010-08-13  160: -- this function inserts or updates record with tags to site by site id with regexp
5f6dc4c42d 2010-08-13  161: CREATE or replace FUNCTION mark(my_id_site integer, my_id_tag integer, my_regexp text) RETURNS integer
5f6dc4c42d 2010-08-13  162: 	LANGUAGE plpgsql STRICT
5f6dc4c42d 2010-08-13  163: 	AS $$
5f6dc4c42d 2010-08-13  164: declare
5f6dc4c42d 2010-08-13  165: 	-- maybe check should be added to make sure supplied site id really exists
5f6dc4c42d 2010-08-13  166: 	my_tag text[];
5f6dc4c42d 2010-08-13  167: begin
5f6dc4c42d 2010-08-13  168: 	-- selecting tags site already have and adding new tag to them
5f6dc4c42d 2010-08-13  169: 	-- note that tags should be sorted to eliminate permutations
5f6dc4c42d 2010-08-13  170: 	select coalesce(tag, '{}'::text[]) from urls natural left join tag
5f6dc4c42d 2010-08-13  171: 		where id_site = my_id_site and regexp = my_regexp into my_tag;
5f6dc4c42d 2010-08-13  172: 	if not found then
5f6dc4c42d 2010-08-13  173: 		-- no records found - creating new tag
5f6dc4c42d 2010-08-13  174: 		insert into urls (id_site, id_tag, regexp) values (my_id_site, my_id_tag, my_regexp);
5f6dc4c42d 2010-08-13  175: 	else
5f6dc4c42d 2010-08-13  176: 		-- joining tags
5f6dc4c42d 2010-08-13  177: 		select usort(my_tag || tag) from tag where id_tag = my_id_tag into my_tag;
5f6dc4c42d 2010-08-13  178: 		-- updating existing record
5f6dc4c42d 2010-08-13  179: 		update urls set id_tag = get_tag(my_tag)
5f6dc4c42d 2010-08-13  180: 			where id_site = my_id_site and regexp = my_regexp;
5f6dc4c42d 2010-08-13  181: 	end if;
5f6dc4c42d 2010-08-13  182: 	return my_id_site;
5f6dc4c42d 2010-08-13  183: end;
5f6dc4c42d 2010-08-13  184: $$;
5f6dc4c42d 2010-08-13  185: 
5f6dc4c42d 2010-08-13  186: -- this function adds tag to site by site id
5f6dc4c42d 2010-08-13  187: CREATE or replace FUNCTION mark(my_id_site integer, new_tag text) RETURNS integer
5f6dc4c42d 2010-08-13  188: 	LANGUAGE sql immutable STRICT
5f6dc4c42d 2010-08-13  189: 	AS $$
5f6dc4c42d 2010-08-13  190: select mark($1, get_tag(array[$2]), NULL) as result;
5f6dc4c42d 2010-08-13  191: $$;
5f6dc4c42d 2010-08-13  192: 
5f6dc4c42d 2010-08-13  193: -- this function adds tag to domain
5f6dc4c42d 2010-08-13  194: CREATE or replace FUNCTION mark(domain text, new_tag text) RETURNS integer
5f6dc4c42d 2010-08-13  195: 	LANGUAGE sql immutable STRICT
5f6dc4c42d 2010-08-13  196: 	AS $$
5f6dc4c42d 2010-08-13  197: select mark(get_site($1), get_tag(array[$2]), NULL) as result;
5f6dc4c42d 2010-08-13  198: $$;
5f6dc4c42d 2010-08-13  199: 
5f6dc4c42d 2010-08-13  200: -- this function sets tags for site without regexp
5f6dc4c42d 2010-08-13  201: CREATE or replace FUNCTION set(my_id_site integer, my_id_tag integer) RETURNS integer
5f6dc4c42d 2010-08-13  202: 	LANGUAGE sql STRICT
5f6dc4c42d 2010-08-13  203: 	AS $$
5f6dc4c42d 2010-08-13  204: delete from urls where $1 = id_site and regexp is NULL;
5f6dc4c42d 2010-08-13  205: insert into urls (id_site, id_tag) values ($1, $2);
5f6dc4c42d 2010-08-13  206: select $1;
5f6dc4c42d 2010-08-13  207: $$;
5f6dc4c42d 2010-08-13  208: 
5f6dc4c42d 2010-08-13  209: -- this function sets tags for site/regexp pair
5f6dc4c42d 2010-08-13  210: CREATE or replace FUNCTION set(my_id_site integer, my_id_tag integer, my_regexp text) RETURNS integer
5f6dc4c42d 2010-08-13  211: 	LANGUAGE sql STRICT
5f6dc4c42d 2010-08-13  212: 	AS $$
5f6dc4c42d 2010-08-13  213: delete from urls where $1 = id_site and $3 = regexp;
5f6dc4c42d 2010-08-13  214: insert into urls (id_site, id_tag, regexp) values ($1, $2, $3);
5f6dc4c42d 2010-08-13  215: select $1;
5f6dc4c42d 2010-08-13  216: $$;
5f6dc4c42d 2010-08-13  217: 
5f6dc4c42d 2010-08-13  218: -- this function stores new data for site/regexp pair
5f6dc4c42d 2010-08-13  219: create or replace function set(domain text, tags text, regexp text) returns integer
5f6dc4c42d 2010-08-13  220: 	language sql immutable strict
5f6dc4c42d 2010-08-13  221: 	as $$
5f6dc4c42d 2010-08-13  222: select set(get_site($1), get_tag($2::text[]), $3);
5f6dc4c42d 2010-08-13  223: $$;
5f6dc4c42d 2010-08-13  224: 
5f6dc4c42d 2010-08-13  225: -- this function stores new data for site/regexp pair
5f6dc4c42d 2010-08-13  226: create or replace function set(domain text, tags text) returns integer
5f6dc4c42d 2010-08-13  227: 	language sql immutable strict
5f6dc4c42d 2010-08-13  228: 	as $$
5f6dc4c42d 2010-08-13  229: select set(get_site($1), get_tag($2::text[]));
5f6dc4c42d 2010-08-13  230: $$;
5f6dc4c42d 2010-08-13  231: 
5f6dc4c42d 2010-08-13  232: -- this function returns id of tag array
5f6dc4c42d 2010-08-13  233: create or replace function get_tag(my_tag text[]) returns integer
5f6dc4c42d 2010-08-13  234: 	language plpgsql strict
5f6dc4c42d 2010-08-13  235: 	as $$
5f6dc4c42d 2010-08-13  236: declare
5f6dc4c42d 2010-08-13  237: 	tag_id integer;
5f6dc4c42d 2010-08-13  238: begin
5f6dc4c42d 2010-08-13  239: 	select id_tag from tag where usort(my_tag) = tag into tag_id;
5f6dc4c42d 2010-08-13  240: 	if not found then
5f6dc4c42d 2010-08-13  241: 		insert into tag (tag) values (usort(my_tag));
5f6dc4c42d 2010-08-13  242: 		select id_tag from tag where usort(my_tag) = tag into tag_id;
5f6dc4c42d 2010-08-13  243: 	end if;
5f6dc4c42d 2010-08-13  244: 	return tag_id;
5f6dc4c42d 2010-08-13  245: end;
5f6dc4c42d 2010-08-13  246: $$;