# HG changeset patch
# User Peter Gervai <grin@grin.hu>
# Date 1646229037 -3600
# Node ID 6aea45bf033b294d6d803171bfac7eeeeeefad9f
# Parent  85afc1496738472af8dddf99d0b9396df7ae75bf
Add room and event display to cmd.sh

diff -r 85afc1496738 -r 6aea45bf033b synapse/_config.sh
--- a/synapse/_config.sh	Mon Jan 24 10:21:08 2022 +0100
+++ b/synapse/_config.sh	Wed Mar 02 14:50:37 2022 +0100
@@ -6,6 +6,8 @@
 # database readonly
 export   DB="postgresql://matrixro:password@localhost:5432/synapse"
 # database r/w
-export DBRW="postgresql://synapse:passwordd@localhost:5432/synapse"
+export DBRW="postgresql://synapseuser:passwordd@localhost:5432/synapse"
 # token
 export    T="MDAxNabCadacada111..."
+# psql command line (pw in ~/.pgpass)
+export PSQL_CMD='psql -h 1.2.3.4 -U matrixro synapse -c'
diff -r 85afc1496738 -r 6aea45bf033b synapse/cmd.sh
--- a/synapse/cmd.sh	Mon Jan 24 10:21:08 2022 +0100
+++ b/synapse/cmd.sh	Wed Mar 02 14:50:37 2022 +0100
@@ -1,18 +1,24 @@
 #!/bin/bash
-#$Id: cmd.sh,v 7c8632f2b6be 2022/01/21 09:05:10 grin $
+#$Id: cmd.sh,v 728762bf2e91 2022/03/02 13:38:25 grin $
+#
 #
 # This program queries PostgreSQL directly for various user statistics.
 # Set psql access below (CMD=...), ensure that .pgpass lets user to login
 #  without password entry.
 # Start without arguments for help.
 #
-# (c)Peter Gervai, 2020-21
+# (c)Peter Gervai, 2020-22
 # @grin:grin.hu
 # Licensed under GPLv3+ and CC_By-Sa-4.0-int
 #
 
 #CMD='sudo -u postgres psql synapse -tAc'
-CMD='psql -h 10.1.1.257 -U synapse synapse -c'
+#CMD='psql -h 1.2.3.4 -U synapse synapse -c'
+
+. ./_config.sh
+
+CMD="${PSQL_CMD}"
+CMDQ='-At'
 
 MODE="$1"
 ARG="$2"
@@ -21,9 +27,11 @@
 	echo "Usage: $0 <mode> <args> ...."
 	echo " modes: user @someuser:grin.hu       - user stats"
 	echo "        userevents @someuser:grin.hu - user events"
-	echo "        active1                      - last 1 day active users timeline"
+	echo "        active1                      - last 1 day active users"
 	echo "        active1u                     - last 1 day users (unique results)"
 	echo "        lastreg [<n>]	           - last <n> (25) registrations"
+	echo "        room <room_id without !>     - room info"
+	echo "        evt <event_id w/o $>         - event info"
 	exit
 fi
 
@@ -32,7 +40,7 @@
 egy_arg() {
 	local s=$1
 	if [ "x$ARG" = "x" ]; then
-		echo "Missing arg ${s}!"
+		echo "MIssing arg ${s}!"
 		echo "$0 $MODE $s"
 		exit
 	fi
@@ -111,3 +119,25 @@
  	exit
 fi
 
+
+if [ "$MODE" = "room" ]; then
+	$CMD "SELECT rs.name, rs.canonical_alias,rs.join_rules,rs.topic FROM room_stats_state rs WHERE room_id='!${ARG}'" | cat
+	$CMD "SELECT room_alias, creator FROM room_aliases WHERE room_id='!${ARG}'" | cat
+	$CMD "SELECT * FROM rooms WHERE room_id='!${ARG}'" | cat
+	$CMD "SELECT * FROM room_stats_current WHERE room_id='!${ARG}'" | cat
+	# $CMD "SELECT COUNT(*), type FROM state_groups_state WHERE room_id='!${ARG}' GROUP BY 2 ORDER BY 1 DESC" | cat
+fi
+
+
+if [ "$MODE" = "evt" ]; then
+	$CMD "SELECT event_id,type,room_id,content,processed,depth,sender,instance_name,state_key,unts(origin_server_ts) AS orig, unts(received_ts) AS rcv FROM events WHERE event_id='\$${ARG}'" | cat
+	$CMD "SELECT * FROM event_json WHERE event_id='\$${ARG}'" | cat
+	evt='$'"${ARG}"
+	room=`$CMD "SELECT room_id FROM events WHERE event_id='${evt}'" $CMDQ`
+
+	#echo "Room_id=$room!"
+        $CMD "SELECT rs.name, rs.canonical_alias,rs.join_rules,rs.topic FROM room_stats_state rs WHERE room_id='$room'" | cat
+        $CMD "SELECT room_alias, creator FROM room_aliases WHERE room_id='$room'" | cat
+
+fi
+