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 (
38ec946cba 2014-07-03 5: uid text NOT NULL,
38ec946cba 2014-07-03 6: referer text DEFAULT '-' NOT NULL,
38ec946cba 2014-07-03 7: ip inet NOT NULL,
38ec946cba 2014-07-03 8: day date DEFAULT now() NOT NULL,
38ec946cba 2014-07-03 9: count integer DEFAULT 0 NOT NULL
38ec946cba 2014-07-03 10: );
38ec946cba 2014-07-03 11:
38ec946cba 2014-07-03 12: CREATE TABLE counter_sum (
38ec946cba 2014-07-03 13: referer text DEFAULT '-'::text NOT NULL,
38ec946cba 2014-07-03 14: day date DEFAULT now() NOT NULL,
38ec946cba 2014-07-03 15: count integer DEFAULT 0 NOT NULL
38ec946cba 2014-07-03 16: );
38ec946cba 2014-07-03 17:
38ec946cba 2014-07-03 18: CREATE or replace FUNCTION get_stats(target text) RETURNS record LANGUAGE sql AS $_$
38ec946cba 2014-07-03 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
38ec946cba 2014-07-03 23: where date_trunc('week', current_date) = date_trunc('week', day) and day <> current_date and referer = $1
38ec946cba 2014-07-03 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
38ec946cba 2014-07-03 26: $_$;
38ec946cba 2014-07-03 27:
38ec946cba 2014-07-03 28: CREATE FUNCTION counter_sum__change_trigger() RETURNS trigger LANGUAGE plpgsql AS $$
38ec946cba 2014-07-03 29: declare
38ec946cba 2014-07-03 30: delta_count smallint;
38ec946cba 2014-07-03 31: begin
38ec946cba 2014-07-03 32: if (TG_OP = 'UPDATE') then
38ec946cba 2014-07-03 33: if (old.referer <> new.referer or old.day <> new.day) then
38ec946cba 2014-07-03 34: raise exception 'Changing referer or day is prohibited';
38ec946cba 2014-07-03 35: end if;
38ec946cba 2014-07-03 36: delta_count = new.count - old.count;
38ec946cba 2014-07-03 37: elsif (TG_OP = 'INSERT') then
38ec946cba 2014-07-03 38: delta_count = new.count;
38ec946cba 2014-07-03 39: elsif (TG_OP = 'DELETE') then
38ec946cba 2014-07-03 40: raise exception 'Deleting counters prohibited';
38ec946cba 2014-07-03 41: end if;
38ec946cba 2014-07-03 42: <<insert_update>> loop
38ec946cba 2014-07-03 43: update counter_sum set count = count + delta_count
38ec946cba 2014-07-03 44: where referer = new.referer and day = new.day;
38ec946cba 2014-07-03 45: exit insert_update when found;
38ec946cba 2014-07-03 46: begin
38ec946cba 2014-07-03 47: insert into counter_sum (referer, day, count) values (new.referer, new.day, delta_count);
38ec946cba 2014-07-03 48: exit insert_update;
38ec946cba 2014-07-03 49: exception when unique_violation then
38ec946cba 2014-07-03 50: end;
38ec946cba 2014-07-03 51: end loop insert_update;
38ec946cba 2014-07-03 52: return new;
38ec946cba 2014-07-03 53: end; $$;
38ec946cba 2014-07-03 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 $$
38ec946cba 2014-07-03 56: BEGIN
38ec946cba 2014-07-03 57: <<insert_update>> LOOP
38ec946cba 2014-07-03 58: UPDATE counter SET count = count + merge_count
38ec946cba 2014-07-03 59: WHERE uid = merge_uid and ip = merge_ip and referer = merge_referer and day = current_date;
38ec946cba 2014-07-03 60: exit insert_update when found;
38ec946cba 2014-07-03 61: BEGIN
38ec946cba 2014-07-03 62: INSERT INTO counter (uid, referer, ip, count) VALUES (merge_uid, merge_referer, merge_ip, merge_count);
38ec946cba 2014-07-03 63: EXIT insert_update;
38ec946cba 2014-07-03 64: EXCEPTION WHEN unique_violation THEN
38ec946cba 2014-07-03 65: END;
38ec946cba 2014-07-03 66: END LOOP insert_update;
38ec946cba 2014-07-03 67: return true;
38ec946cba 2014-07-03 68: END;
38ec946cba 2014-07-03 69: $$;
38ec946cba 2014-07-03 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();
38ec946cba 2014-07-03 76:
38ec946cba 2014-07-03 77: end;