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