b33171b487 2018-06-01 arcade@b1t.na: CREATE TABLE if not exists counter (
38ec946cba 2014-07-03 arcade@b1t.na: uid text NOT NULL,
38ec946cba 2014-07-03 arcade@b1t.na: referer text DEFAULT '-' NOT NULL,
38ec946cba 2014-07-03 arcade@b1t.na: ip inet NOT NULL,
38ec946cba 2014-07-03 arcade@b1t.na: day date DEFAULT now() NOT NULL,
38ec946cba 2014-07-03 arcade@b1t.na: count integer DEFAULT 0 NOT NULL
38ec946cba 2014-07-03 arcade@b1t.na: );
38ec946cba 2014-07-03 arcade@b1t.na:
b33171b487 2018-06-01 arcade@b1t.na: CREATE TABLE if not exists counter_sum (
38ec946cba 2014-07-03 arcade@b1t.na: referer text DEFAULT '-'::text NOT NULL,
38ec946cba 2014-07-03 arcade@b1t.na: day date DEFAULT now() NOT NULL,
38ec946cba 2014-07-03 arcade@b1t.na: count integer DEFAULT 0 NOT NULL
38ec946cba 2014-07-03 arcade@b1t.na: );
38ec946cba 2014-07-03 arcade@b1t.na:
38ec946cba 2014-07-03 arcade@b1t.na: CREATE or replace FUNCTION get_stats(target text) RETURNS record LANGUAGE sql AS $_$
38ec946cba 2014-07-03 arcade@b1t.na: select
b33171b487 2018-06-01 arcade@b1t.na: coalesce((select count from limbo.counter_sum where day = current_date and referer = $1), 0) as today,
b33171b487 2018-06-01 arcade@b1t.na: 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 arcade@b1t.na: coalesce((select sum(count) from limbo.counter_sum
38ec946cba 2014-07-03 arcade@b1t.na: where date_trunc('week', current_date) = date_trunc('week', day) and day <> current_date and referer = $1
38ec946cba 2014-07-03 arcade@b1t.na: group by referer), 0) as week,
b33171b487 2018-06-01 arcade@b1t.na: 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 arcade@b1t.na: $_$;
38ec946cba 2014-07-03 arcade@b1t.na:
b33171b487 2018-06-01 arcade@b1t.na: CREATE or replace FUNCTION counter_sum__change_trigger() RETURNS trigger LANGUAGE plpgsql AS $$
38ec946cba 2014-07-03 arcade@b1t.na: declare
38ec946cba 2014-07-03 arcade@b1t.na: delta_count smallint;
38ec946cba 2014-07-03 arcade@b1t.na: begin
38ec946cba 2014-07-03 arcade@b1t.na: if (TG_OP = 'UPDATE') then
38ec946cba 2014-07-03 arcade@b1t.na: if (old.referer <> new.referer or old.day <> new.day) then
38ec946cba 2014-07-03 arcade@b1t.na: raise exception 'Changing referer or day is prohibited';
38ec946cba 2014-07-03 arcade@b1t.na: end if;
38ec946cba 2014-07-03 arcade@b1t.na: delta_count = new.count - old.count;
38ec946cba 2014-07-03 arcade@b1t.na: elsif (TG_OP = 'INSERT') then
38ec946cba 2014-07-03 arcade@b1t.na: delta_count = new.count;
38ec946cba 2014-07-03 arcade@b1t.na: elsif (TG_OP = 'DELETE') then
38ec946cba 2014-07-03 arcade@b1t.na: raise exception 'Deleting counters prohibited';
38ec946cba 2014-07-03 arcade@b1t.na: end if;
38ec946cba 2014-07-03 arcade@b1t.na: <<insert_update>> loop
b33171b487 2018-06-01 arcade@b1t.na: update limbo.counter_sum set count = count + delta_count
38ec946cba 2014-07-03 arcade@b1t.na: where referer = new.referer and day = new.day;
38ec946cba 2014-07-03 arcade@b1t.na: exit insert_update when found;
38ec946cba 2014-07-03 arcade@b1t.na: begin
b33171b487 2018-06-01 arcade@b1t.na: insert into limbo.counter_sum (referer, day, count) values (new.referer, new.day, delta_count);
38ec946cba 2014-07-03 arcade@b1t.na: exit insert_update;
38ec946cba 2014-07-03 arcade@b1t.na: exception when unique_violation then
38ec946cba 2014-07-03 arcade@b1t.na: end;
38ec946cba 2014-07-03 arcade@b1t.na: end loop insert_update;
38ec946cba 2014-07-03 arcade@b1t.na: return new;
38ec946cba 2014-07-03 arcade@b1t.na: end; $$;
38ec946cba 2014-07-03 arcade@b1t.na:
b33171b487 2018-06-01 arcade@b1t.na: 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 arcade@b1t.na: BEGIN
38ec946cba 2014-07-03 arcade@b1t.na: <<insert_update>> LOOP
b33171b487 2018-06-01 arcade@b1t.na: UPDATE limbo.counter SET count = count + merge_count
38ec946cba 2014-07-03 arcade@b1t.na: WHERE uid = merge_uid and ip = merge_ip and referer = merge_referer and day = current_date;
38ec946cba 2014-07-03 arcade@b1t.na: exit insert_update when found;
38ec946cba 2014-07-03 arcade@b1t.na: BEGIN
b33171b487 2018-06-01 arcade@b1t.na: INSERT INTO limbo.counter (uid, referer, ip, count) VALUES (merge_uid, merge_referer, merge_ip, merge_count);
38ec946cba 2014-07-03 arcade@b1t.na: EXIT insert_update;
38ec946cba 2014-07-03 arcade@b1t.na: EXCEPTION WHEN unique_violation THEN
38ec946cba 2014-07-03 arcade@b1t.na: END;
38ec946cba 2014-07-03 arcade@b1t.na: END LOOP insert_update;
38ec946cba 2014-07-03 arcade@b1t.na: return true;
38ec946cba 2014-07-03 arcade@b1t.na: END;
38ec946cba 2014-07-03 arcade@b1t.na: $$;
38ec946cba 2014-07-03 arcade@b1t.na:
b33171b487 2018-06-01 arcade@b1t.na: CREATE INDEX if not exists counter__date ON counter USING btree (day);
b33171b487 2018-06-01 arcade@b1t.na: CREATE UNIQUE INDEX if not exists counter__uid_nreferer_ip_date ON counter (uid, ip, day) WHERE (referer IS NULL);
b33171b487 2018-06-01 arcade@b1t.na: CREATE UNIQUE INDEX if not exists counter__uid_referer_ip_date ON counter (uid, referer, ip, day);
b33171b487 2018-06-01 arcade@b1t.na: create unique index if not exists counter_sum__referer_day ON counter_sum (referer, day);
38ec946cba 2014-07-03 arcade@b1t.na:
b33171b487 2018-06-01 arcade@b1t.na: drop trigger if exists counter__change on counter;
b33171b487 2018-06-01 arcade@b1t.na: CREATE TRIGGER counter__change AFTER INSERT OR DELETE OR UPDATE ON counter FOR EACH ROW EXECUTE PROCEDURE counter_sum__change_trigger();