Artifact
9e36f017b89ad3f9ce241d38303b150260b5ccd834e8070ef09e1815689745b1:
0000: 53 45 54 20 73 65 61 72 63 68 5f 70 61 74 68 20 SET search_path
0010: 3d 20 61 75 74 6f 2c 20 70 67 5f 63 61 74 61 6c = auto, pg_catal
0020: 6f 67 3b 0a 42 45 47 49 4e 3b 0a 0a 43 52 45 41 og;.BEGIN;..CREA
0030: 54 45 20 54 41 42 4c 45 20 63 6f 75 6e 74 65 72 TE TABLE counter
0040: 20 28 0a 09 75 69 64 20 74 65 78 74 20 4e 4f 54 (..uid text NOT
0050: 20 4e 55 4c 4c 2c 0a 09 72 65 66 65 72 65 72 20 NULL,..referer
0060: 74 65 78 74 20 44 45 46 41 55 4c 54 20 27 2d 27 text DEFAULT '-'
0070: 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 69 70 20 69 NOT NULL,..ip i
0080: 6e 65 74 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 64 net NOT NULL,..d
0090: 61 79 20 64 61 74 65 20 44 45 46 41 55 4c 54 20 ay date DEFAULT
00a0: 6e 6f 77 28 29 20 4e 4f 54 20 4e 55 4c 4c 2c 0a now() NOT NULL,.
00b0: 09 63 6f 75 6e 74 20 69 6e 74 65 67 65 72 20 44 .count integer D
00c0: 45 46 41 55 4c 54 20 30 20 4e 4f 54 20 4e 55 4c EFAULT 0 NOT NUL
00d0: 4c 0a 29 3b 0a 0a 43 52 45 41 54 45 20 54 41 42 L.);..CREATE TAB
00e0: 4c 45 20 63 6f 75 6e 74 65 72 5f 73 75 6d 20 28 LE counter_sum (
00f0: 0a 09 72 65 66 65 72 65 72 20 74 65 78 74 20 44 ..referer text D
0100: 45 46 41 55 4c 54 20 27 2d 27 3a 3a 74 65 78 74 EFAULT '-'::text
0110: 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 64 61 79 20 NOT NULL,..day
0120: 64 61 74 65 20 44 45 46 41 55 4c 54 20 6e 6f 77 date DEFAULT now
0130: 28 29 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 63 6f () NOT NULL,..co
0140: 75 6e 74 20 69 6e 74 65 67 65 72 20 44 45 46 41 unt integer DEFA
0150: 55 4c 54 20 30 20 4e 4f 54 20 4e 55 4c 4c 0a 29 ULT 0 NOT NULL.)
0160: 3b 0a 0a 43 52 45 41 54 45 20 6f 72 20 72 65 70 ;..CREATE or rep
0170: 6c 61 63 65 20 46 55 4e 43 54 49 4f 4e 20 67 65 lace FUNCTION ge
0180: 74 5f 73 74 61 74 73 28 74 61 72 67 65 74 20 74 t_stats(target t
0190: 65 78 74 29 20 52 45 54 55 52 4e 53 20 72 65 63 ext) RETURNS rec
01a0: 6f 72 64 20 4c 41 4e 47 55 41 47 45 20 73 71 6c ord LANGUAGE sql
01b0: 20 41 53 20 24 5f 24 0a 73 65 6c 65 63 74 20 0a AS $_$.select .
01c0: 09 63 6f 61 6c 65 73 63 65 28 28 73 65 6c 65 63 .coalesce((selec
01d0: 74 20 63 6f 75 6e 74 20 66 72 6f 6d 20 63 6f 75 t count from cou
01e0: 6e 74 65 72 5f 73 75 6d 20 77 68 65 72 65 20 64 nter_sum where d
01f0: 61 79 20 3d 20 63 75 72 72 65 6e 74 5f 64 61 74 ay = current_dat
0200: 65 20 61 6e 64 20 72 65 66 65 72 65 72 20 3d 20 e and referer =
0210: 24 31 29 2c 20 30 29 20 61 73 20 74 6f 64 61 79 $1), 0) as today
0220: 2c 0a 09 63 6f 61 6c 65 73 63 65 28 28 73 65 6c ,..coalesce((sel
0230: 65 63 74 20 63 6f 75 6e 74 20 66 72 6f 6d 20 63 ect count from c
0240: 6f 75 6e 74 65 72 5f 73 75 6d 20 77 68 65 72 65 ounter_sum where
0250: 20 64 61 79 20 3d 20 28 63 75 72 72 65 6e 74 5f day = (current_
0260: 64 61 74 65 20 2d 20 69 6e 74 65 72 76 61 6c 20 date - interval
0270: 27 31 20 64 61 79 27 29 3a 3a 64 61 74 65 20 61 '1 day')::date a
0280: 6e 64 20 72 65 66 65 72 65 72 20 3d 20 24 31 29 nd referer = $1)
0290: 2c 20 30 29 20 61 73 20 6c 61 73 74 64 61 79 2c , 0) as lastday,
02a0: 0a 09 63 6f 61 6c 65 73 63 65 28 28 73 65 6c 65 ..coalesce((sele
02b0: 63 74 20 73 75 6d 28 63 6f 75 6e 74 29 20 66 72 ct sum(count) fr
02c0: 6f 6d 20 63 6f 75 6e 74 65 72 5f 73 75 6d 0a 09 om counter_sum..
02d0: 09 77 68 65 72 65 20 64 61 74 65 5f 74 72 75 6e .where date_trun
02e0: 63 28 27 77 65 65 6b 27 2c 20 63 75 72 72 65 6e c('week', curren
02f0: 74 5f 64 61 74 65 29 20 3d 20 64 61 74 65 5f 74 t_date) = date_t
0300: 72 75 6e 63 28 27 77 65 65 6b 27 2c 20 64 61 79 runc('week', day
0310: 29 20 61 6e 64 20 64 61 79 20 3c 3e 20 63 75 72 ) and day <> cur
0320: 72 65 6e 74 5f 64 61 74 65 20 61 6e 64 20 72 65 rent_date and re
0330: 66 65 72 65 72 20 3d 20 24 31 0a 09 09 67 72 6f ferer = $1...gro
0340: 75 70 20 62 79 20 72 65 66 65 72 65 72 29 2c 20 up by referer),
0350: 30 29 20 61 73 20 77 65 65 6b 2c 0a 09 63 6f 61 0) as week,..coa
0360: 6c 65 73 63 65 28 28 73 65 6c 65 63 74 20 73 75 lesce((select su
0370: 6d 28 63 6f 75 6e 74 29 20 66 72 6f 6d 20 63 6f m(count) from co
0380: 75 6e 74 65 72 5f 73 75 6d 20 77 68 65 72 65 20 unter_sum where
0390: 64 61 79 20 3c 3e 20 63 75 72 72 65 6e 74 5f 64 day <> current_d
03a0: 61 74 65 20 61 6e 64 20 72 65 66 65 72 65 72 20 ate and referer
03b0: 3d 20 24 31 20 67 72 6f 75 70 20 62 79 20 72 65 = $1 group by re
03c0: 66 65 72 65 72 29 2c 20 30 29 20 61 73 20 77 68 ferer), 0) as wh
03d0: 6f 6c 65 0a 24 5f 24 3b 0a 0a 43 52 45 41 54 45 ole.$_$;..CREATE
03e0: 20 46 55 4e 43 54 49 4f 4e 20 63 6f 75 6e 74 65 FUNCTION counte
03f0: 72 5f 73 75 6d 5f 5f 63 68 61 6e 67 65 5f 74 72 r_sum__change_tr
0400: 69 67 67 65 72 28 29 20 52 45 54 55 52 4e 53 20 igger() RETURNS
0410: 74 72 69 67 67 65 72 20 4c 41 4e 47 55 41 47 45 trigger LANGUAGE
0420: 20 70 6c 70 67 73 71 6c 20 41 53 20 24 24 0a 64 plpgsql AS $$.d
0430: 65 63 6c 61 72 65 0a 09 64 65 6c 74 61 5f 63 6f eclare..delta_co
0440: 75 6e 74 20 73 6d 61 6c 6c 69 6e 74 3b 0a 62 65 unt smallint;.be
0450: 67 69 6e 0a 09 69 66 20 28 54 47 5f 4f 50 20 3d gin..if (TG_OP =
0460: 20 27 55 50 44 41 54 45 27 29 20 74 68 65 6e 0a 'UPDATE') then.
0470: 09 09 69 66 20 28 6f 6c 64 2e 72 65 66 65 72 65 ..if (old.refere
0480: 72 20 3c 3e 20 6e 65 77 2e 72 65 66 65 72 65 72 r <> new.referer
0490: 20 6f 72 20 6f 6c 64 2e 64 61 79 20 3c 3e 20 6e or old.day <> n
04a0: 65 77 2e 64 61 79 29 20 74 68 65 6e 0a 09 09 09 ew.day) then....
04b0: 72 61 69 73 65 20 65 78 63 65 70 74 69 6f 6e 20 raise exception
04c0: 27 43 68 61 6e 67 69 6e 67 20 72 65 66 65 72 65 'Changing refere
04d0: 72 20 6f 72 20 64 61 79 20 69 73 20 70 72 6f 68 r or day is proh
04e0: 69 62 69 74 65 64 27 3b 0a 09 09 65 6e 64 20 69 ibited';...end i
04f0: 66 3b 0a 09 09 64 65 6c 74 61 5f 63 6f 75 6e 74 f;...delta_count
0500: 20 3d 20 6e 65 77 2e 63 6f 75 6e 74 20 2d 20 6f = new.count - o
0510: 6c 64 2e 63 6f 75 6e 74 3b 0a 09 65 6c 73 69 66 ld.count;..elsif
0520: 20 28 54 47 5f 4f 50 20 3d 20 27 49 4e 53 45 52 (TG_OP = 'INSER
0530: 54 27 29 20 74 68 65 6e 0a 09 09 64 65 6c 74 61 T') then...delta
0540: 5f 63 6f 75 6e 74 20 3d 20 6e 65 77 2e 63 6f 75 _count = new.cou
0550: 6e 74 3b 0a 09 65 6c 73 69 66 20 28 54 47 5f 4f nt;..elsif (TG_O
0560: 50 20 3d 20 27 44 45 4c 45 54 45 27 29 20 74 68 P = 'DELETE') th
0570: 65 6e 0a 09 09 72 61 69 73 65 20 65 78 63 65 70 en...raise excep
0580: 74 69 6f 6e 20 27 44 65 6c 65 74 69 6e 67 20 63 tion 'Deleting c
0590: 6f 75 6e 74 65 72 73 20 70 72 6f 68 69 62 69 74 ounters prohibit
05a0: 65 64 27 3b 0a 09 65 6e 64 20 69 66 3b 0a 09 3c ed';..end if;..<
05b0: 3c 69 6e 73 65 72 74 5f 75 70 64 61 74 65 3e 3e <insert_update>>
05c0: 20 6c 6f 6f 70 0a 09 09 75 70 64 61 74 65 20 63 loop...update c
05d0: 6f 75 6e 74 65 72 5f 73 75 6d 20 73 65 74 20 63 ounter_sum set c
05e0: 6f 75 6e 74 20 3d 20 63 6f 75 6e 74 20 2b 20 64 ount = count + d
05f0: 65 6c 74 61 5f 63 6f 75 6e 74 0a 09 09 09 77 68 elta_count....wh
0600: 65 72 65 20 72 65 66 65 72 65 72 20 3d 20 6e 65 ere referer = ne
0610: 77 2e 72 65 66 65 72 65 72 20 61 6e 64 20 64 61 w.referer and da
0620: 79 20 3d 20 6e 65 77 2e 64 61 79 3b 0a 09 09 65 y = new.day;...e
0630: 78 69 74 20 69 6e 73 65 72 74 5f 75 70 64 61 74 xit insert_updat
0640: 65 20 77 68 65 6e 20 66 6f 75 6e 64 3b 0a 09 09 e when found;...
0650: 62 65 67 69 6e 0a 09 09 09 69 6e 73 65 72 74 20 begin....insert
0660: 69 6e 74 6f 20 63 6f 75 6e 74 65 72 5f 73 75 6d into counter_sum
0670: 20 28 72 65 66 65 72 65 72 2c 20 64 61 79 2c 20 (referer, day,
0680: 63 6f 75 6e 74 29 20 76 61 6c 75 65 73 20 28 6e count) values (n
0690: 65 77 2e 72 65 66 65 72 65 72 2c 20 6e 65 77 2e ew.referer, new.
06a0: 64 61 79 2c 20 64 65 6c 74 61 5f 63 6f 75 6e 74 day, delta_count
06b0: 29 3b 0a 09 09 09 65 78 69 74 20 69 6e 73 65 72 );....exit inser
06c0: 74 5f 75 70 64 61 74 65 3b 0a 09 09 09 65 78 63 t_update;....exc
06d0: 65 70 74 69 6f 6e 20 77 68 65 6e 20 75 6e 69 71 eption when uniq
06e0: 75 65 5f 76 69 6f 6c 61 74 69 6f 6e 20 74 68 65 ue_violation the
06f0: 6e 0a 09 09 65 6e 64 3b 0a 09 65 6e 64 20 6c 6f n...end;..end lo
0700: 6f 70 20 69 6e 73 65 72 74 5f 75 70 64 61 74 65 op insert_update
0710: 3b 0a 09 72 65 74 75 72 6e 20 6e 65 77 3b 0a 65 ;..return new;.e
0720: 6e 64 3b 20 24 24 3b 0a 0a 43 52 45 41 54 45 20 nd; $$;..CREATE
0730: 46 55 4e 43 54 49 4f 4e 20 6d 65 72 67 65 5f 63 FUNCTION merge_c
0740: 6f 75 6e 74 65 72 28 6d 65 72 67 65 5f 75 69 64 ounter(merge_uid
0750: 20 74 65 78 74 2c 20 6d 65 72 67 65 5f 72 65 66 text, merge_ref
0760: 65 72 65 72 20 74 65 78 74 2c 20 6d 65 72 67 65 erer text, merge
0770: 5f 69 70 20 69 6e 65 74 2c 20 6d 65 72 67 65 5f _ip inet, merge_
0780: 63 6f 75 6e 74 20 73 6d 61 6c 6c 69 6e 74 29 20 count smallint)
0790: 52 45 54 55 52 4e 53 20 62 6f 6f 6c 65 61 6e 20 RETURNS boolean
07a0: 4c 41 4e 47 55 41 47 45 20 70 6c 70 67 73 71 6c LANGUAGE plpgsql
07b0: 20 41 53 20 24 24 0a 42 45 47 49 4e 0a 09 3c 3c AS $$.BEGIN..<<
07c0: 69 6e 73 65 72 74 5f 75 70 64 61 74 65 3e 3e 20 insert_update>>
07d0: 4c 4f 4f 50 0a 09 09 55 50 44 41 54 45 20 63 6f LOOP...UPDATE co
07e0: 75 6e 74 65 72 20 53 45 54 20 63 6f 75 6e 74 20 unter SET count
07f0: 3d 20 63 6f 75 6e 74 20 2b 20 6d 65 72 67 65 5f = count + merge_
0800: 63 6f 75 6e 74 0a 09 09 09 57 48 45 52 45 20 75 count....WHERE u
0810: 69 64 20 3d 20 6d 65 72 67 65 5f 75 69 64 20 61 id = merge_uid a
0820: 6e 64 20 69 70 20 3d 20 6d 65 72 67 65 5f 69 70 nd ip = merge_ip
0830: 20 61 6e 64 20 72 65 66 65 72 65 72 20 3d 20 6d and referer = m
0840: 65 72 67 65 5f 72 65 66 65 72 65 72 20 61 6e 64 erge_referer and
0850: 20 64 61 79 20 3d 20 63 75 72 72 65 6e 74 5f 64 day = current_d
0860: 61 74 65 3b 0a 09 09 65 78 69 74 20 69 6e 73 65 ate;...exit inse
0870: 72 74 5f 75 70 64 61 74 65 20 77 68 65 6e 20 66 rt_update when f
0880: 6f 75 6e 64 3b 0a 09 09 42 45 47 49 4e 0a 09 09 ound;...BEGIN...
0890: 09 49 4e 53 45 52 54 20 49 4e 54 4f 20 63 6f 75 .INSERT INTO cou
08a0: 6e 74 65 72 20 28 75 69 64 2c 20 72 65 66 65 72 nter (uid, refer
08b0: 65 72 2c 20 69 70 2c 20 63 6f 75 6e 74 29 20 56 er, ip, count) V
08c0: 41 4c 55 45 53 20 28 6d 65 72 67 65 5f 75 69 64 ALUES (merge_uid
08d0: 2c 20 6d 65 72 67 65 5f 72 65 66 65 72 65 72 2c , merge_referer,
08e0: 20 6d 65 72 67 65 5f 69 70 2c 20 6d 65 72 67 65 merge_ip, merge
08f0: 5f 63 6f 75 6e 74 29 3b 0a 09 09 09 45 58 49 54 _count);....EXIT
0900: 20 69 6e 73 65 72 74 5f 75 70 64 61 74 65 3b 0a insert_update;.
0910: 09 09 09 45 58 43 45 50 54 49 4f 4e 20 57 48 45 ...EXCEPTION WHE
0920: 4e 20 75 6e 69 71 75 65 5f 76 69 6f 6c 61 74 69 N unique_violati
0930: 6f 6e 20 54 48 45 4e 0a 09 09 45 4e 44 3b 0a 09 on THEN...END;..
0940: 45 4e 44 20 4c 4f 4f 50 20 69 6e 73 65 72 74 5f END LOOP insert_
0950: 75 70 64 61 74 65 3b 0a 09 72 65 74 75 72 6e 20 update;..return
0960: 74 72 75 65 3b 0a 45 4e 44 3b 0a 24 24 3b 0a 0a true;.END;.$$;..
0970: 43 52 45 41 54 45 20 49 4e 44 45 58 20 63 6f 75 CREATE INDEX cou
0980: 6e 74 65 72 5f 5f 64 61 74 65 20 4f 4e 20 63 6f nter__date ON co
0990: 75 6e 74 65 72 20 55 53 49 4e 47 20 62 74 72 65 unter USING btre
09a0: 65 20 28 64 61 79 29 3b 0a 43 52 45 41 54 45 20 e (day);.CREATE
09b0: 55 4e 49 51 55 45 20 49 4e 44 45 58 20 63 6f 75 UNIQUE INDEX cou
09c0: 6e 74 65 72 5f 5f 75 69 64 5f 6e 72 65 66 65 72 nter__uid_nrefer
09d0: 65 72 5f 69 70 5f 64 61 74 65 20 4f 4e 20 63 6f er_ip_date ON co
09e0: 75 6e 74 65 72 20 28 75 69 64 2c 20 69 70 2c 20 unter (uid, ip,
09f0: 64 61 79 29 20 57 48 45 52 45 20 28 72 65 66 65 day) WHERE (refe
0a00: 72 65 72 20 49 53 20 4e 55 4c 4c 29 3b 0a 43 52 rer IS NULL);.CR
0a10: 45 41 54 45 20 55 4e 49 51 55 45 20 49 4e 44 45 EATE UNIQUE INDE
0a20: 58 20 63 6f 75 6e 74 65 72 5f 5f 75 69 64 5f 72 X counter__uid_r
0a30: 65 66 65 72 65 72 5f 69 70 5f 64 61 74 65 20 4f eferer_ip_date O
0a40: 4e 20 63 6f 75 6e 74 65 72 20 28 75 69 64 2c 20 N counter (uid,
0a50: 72 65 66 65 72 65 72 2c 20 69 70 2c 20 64 61 79 referer, ip, day
0a60: 29 3b 0a 63 72 65 61 74 65 20 75 6e 69 71 75 65 );.create unique
0a70: 20 69 6e 64 65 78 20 63 6f 75 6e 74 65 72 5f 73 index counter_s
0a80: 75 6d 5f 5f 72 65 66 65 72 65 72 5f 64 61 79 20 um__referer_day
0a90: 4f 4e 20 63 6f 75 6e 74 65 72 5f 73 75 6d 20 28 ON counter_sum (
0aa0: 72 65 66 65 72 65 72 2c 20 64 61 79 29 3b 0a 43 referer, day);.C
0ab0: 52 45 41 54 45 20 54 52 49 47 47 45 52 20 63 6f REATE TRIGGER co
0ac0: 75 6e 74 65 72 5f 5f 63 68 61 6e 67 65 20 41 46 unter__change AF
0ad0: 54 45 52 20 49 4e 53 45 52 54 20 4f 52 20 44 45 TER INSERT OR DE
0ae0: 4c 45 54 45 20 4f 52 20 55 50 44 41 54 45 20 4f LETE OR UPDATE O
0af0: 4e 20 63 6f 75 6e 74 65 72 20 46 4f 52 20 45 41 N counter FOR EA
0b00: 43 48 20 52 4f 57 20 45 58 45 43 55 54 45 20 50 CH ROW EXECUTE P
0b10: 52 4f 43 45 44 55 52 45 20 63 6f 75 6e 74 65 72 ROCEDURE counter
0b20: 5f 73 75 6d 5f 5f 63 68 61 6e 67 65 5f 74 72 69 _sum__change_tri
0b30: 67 67 65 72 28 29 3b 0a 0a 65 6e 64 3b 0a gger();..end;.