equal
deleted
inserted
replaced
1 #!/bin/bash |
1 #!/bin/bash |
2 #$Id: cmd.sh,v 728762bf2e91 2022/03/02 13:38:25 grin $ |
2 #$Id: cmd.sh,v efd1721dba68 2022/03/04 09:54:35 grin $ |
3 # |
3 # |
4 # |
4 # |
5 # This program queries PostgreSQL directly for various user statistics. |
5 # This program queries PostgreSQL directly for various user statistics. |
6 # Set psql access below (CMD=...), ensure that .pgpass lets user to login |
6 # Set psql access below (CMD=...), ensure that .pgpass lets user to login |
7 # without password entry. |
7 # without password entry. |
8 # Start without arguments for help. |
8 # Start without arguments for help. |
|
9 # |
|
10 # You need to run 'unts' the first time with write access or copy the |
|
11 # output into psql. |
9 # |
12 # |
10 # (c)Peter Gervai, 2020-22 |
13 # (c)Peter Gervai, 2020-22 |
11 # @grin:grin.hu |
14 # @grin:grin.hu |
12 # Licensed under GPLv3+ and CC_By-Sa-4.0-int |
15 # Licensed under GPLv3+ and CC_By-Sa-4.0-int |
13 # |
16 # |
29 echo " userevents @someuser:grin.hu - user events" |
32 echo " userevents @someuser:grin.hu - user events" |
30 echo " active1 - last 1 day active users" |
33 echo " active1 - last 1 day active users" |
31 echo " active1u - last 1 day users (unique results)" |
34 echo " active1u - last 1 day users (unique results)" |
32 echo " lastreg [<n>] - last <n> (25) registrations" |
35 echo " lastreg [<n>] - last <n> (25) registrations" |
33 echo " room <room_id without !> - room info" |
36 echo " room <room_id without !> - room info" |
34 echo " evt <event_id w/o $> - event info" |
37 echo " evt <event w/o $ - event info" |
|
38 echo " unts - create unts() function for human-readable" |
|
39 echo " timestamps; requires Write access or copy displayed sql." |
35 exit |
40 exit |
36 fi |
41 fi |
37 |
42 |
38 |
43 |
39 |
44 |
43 echo "MIssing arg ${s}!" |
48 echo "MIssing arg ${s}!" |
44 echo "$0 $MODE $s" |
49 echo "$0 $MODE $s" |
45 exit |
50 exit |
46 fi |
51 fi |
47 } |
52 } |
|
53 |
|
54 |
|
55 ### create unts() func |
|
56 if [ "$MODE" = "unts" ]; then |
|
57 echo "Creating unts() function; needs write access or you can simply copy the following code into psql." |
|
58 echo " " |
|
59 SQL='CREATE OR REPLACE FUNCTION public.unts(bigint) |
|
60 RETURNS timestamp with time zone |
|
61 LANGUAGE sql |
|
62 IMMUTABLE STRICT |
|
63 AS $function$ |
|
64 SELECT TIMESTAMP WITH TIME ZONE ''epoch'' + $1/1000 * INTERVAL ''1 second'' |
|
65 $function$' |
|
66 echo "${SQL}" |
|
67 echo " " |
|
68 $CMD "${SQL}" |
|
69 exit |
|
70 fi |
48 |
71 |
49 |
72 |
50 |
73 |
51 if [ "$MODE" = "user" ]; then |
74 if [ "$MODE" = "user" ]; then |
52 egy_arg 'user' |
75 egy_arg 'user' |