Lines of counter.sql from check-in 38ec946cba that are changed by the sequence of edits moving toward check-in b33171b487:

38ec946cba 2014-07-03    1: SET search_path = auto, pg_catalog;
38ec946cba 2014-07-03    2: BEGIN;
38ec946cba 2014-07-03    3: 
38ec946cba 2014-07-03    4: CREATE TABLE counter (
                         5: 	uid text NOT NULL,
                         6: 	referer text DEFAULT '-' NOT NULL,
                         7: 	ip inet NOT NULL,
                         8: 	day date DEFAULT now() NOT NULL,
                         9: 	count integer DEFAULT 0 NOT NULL
                        10: );
                        11: 
38ec946cba 2014-07-03   12: CREATE TABLE counter_sum (
                        13: 	referer text DEFAULT '-'::text NOT NULL,
                        14: 	day date DEFAULT now() NOT NULL,
                        15: 	count integer DEFAULT 0 NOT NULL
                        16: );
                        17: 
                        18: CREATE or replace FUNCTION get_stats(target text) RETURNS record LANGUAGE sql AS $_$
                        19: select 
38ec946cba 2014-07-03   20: 	coalesce((select count from counter_sum where day = current_date and referer = $1), 0) as today,
38ec946cba 2014-07-03   21: 	coalesce((select count from counter_sum where day = (current_date - interval '1 day')::date and referer = $1), 0) as lastday,
38ec946cba 2014-07-03   22: 	coalesce((select sum(count) from counter_sum
                        23: 		where date_trunc('week', current_date) = date_trunc('week', day) and day <> current_date and referer = $1
                        24: 		group by referer), 0) as week,
38ec946cba 2014-07-03   25: 	coalesce((select sum(count) from counter_sum where day <> current_date and referer = $1 group by referer), 0) as whole
                        26: $_$;
                        27: 
38ec946cba 2014-07-03   28: CREATE FUNCTION counter_sum__change_trigger() RETURNS trigger LANGUAGE plpgsql AS $$
                        29: declare
                        30: 	delta_count smallint;
                        31: begin
                        32: 	if (TG_OP = 'UPDATE') then
                        33: 		if (old.referer <> new.referer or old.day <> new.day) then
                        34: 			raise exception 'Changing referer or day is prohibited';
                        35: 		end if;
                        36: 		delta_count = new.count - old.count;
                        37: 	elsif (TG_OP = 'INSERT') then
                        38: 		delta_count = new.count;
                        39: 	elsif (TG_OP = 'DELETE') then
                        40: 		raise exception 'Deleting counters prohibited';
                        41: 	end if;
                        42: 	<<insert_update>> loop
38ec946cba 2014-07-03   43: 		update counter_sum set count = count + delta_count
                        44: 			where referer = new.referer and day = new.day;
                        45: 		exit insert_update when found;
                        46: 		begin
38ec946cba 2014-07-03   47: 			insert into counter_sum (referer, day, count) values (new.referer, new.day, delta_count);
                        48: 			exit insert_update;
                        49: 			exception when unique_violation then
                        50: 		end;
                        51: 	end loop insert_update;
                        52: 	return new;
                        53: end; $$;
                        54: 
38ec946cba 2014-07-03   55: CREATE FUNCTION merge_counter(merge_uid text, merge_referer text, merge_ip inet, merge_count smallint) RETURNS boolean LANGUAGE plpgsql AS $$
                        56: BEGIN
                        57: 	<<insert_update>> LOOP
38ec946cba 2014-07-03   58: 		UPDATE counter SET count = count + merge_count
                        59: 			WHERE uid = merge_uid and ip = merge_ip and referer = merge_referer and day = current_date;
                        60: 		exit insert_update when found;
                        61: 		BEGIN
38ec946cba 2014-07-03   62: 			INSERT INTO counter (uid, referer, ip, count) VALUES (merge_uid, merge_referer, merge_ip, merge_count);
                        63: 			EXIT insert_update;
                        64: 			EXCEPTION WHEN unique_violation THEN
                        65: 		END;
                        66: 	END LOOP insert_update;
                        67: 	return true;
                        68: END;
                        69: $$;
                        70: 
38ec946cba 2014-07-03   71: CREATE INDEX counter__date ON counter USING btree (day);
38ec946cba 2014-07-03   72: CREATE UNIQUE INDEX counter__uid_nreferer_ip_date ON counter (uid, ip, day) WHERE (referer IS NULL);
38ec946cba 2014-07-03   73: CREATE UNIQUE INDEX counter__uid_referer_ip_date ON counter (uid, referer, ip, day);
38ec946cba 2014-07-03   74: create unique index counter_sum__referer_day ON counter_sum (referer, day);
38ec946cba 2014-07-03   75: CREATE TRIGGER counter__change AFTER INSERT OR DELETE OR UPDATE ON counter FOR EACH ROW EXECUTE PROCEDURE counter_sum__change_trigger();
                        76: 
38ec946cba 2014-07-03   77: end;