12 Useful Database Queries

Neil Alexander edited this page 2021-01-11 12:24:00 +00:00

Table of Contents

• Roomserver
• Get current room state
• Get room state at (before) a specific event
• Get specific state snapshot
• Get a list of rooms that local users are joined to
• Clean unused state snapshots and state blocks
• Count forward extremities

The below queries may come in useful when hacking on or debugging Dendrite. These queries are tested with Postgres only.

Roomserver

Get current room state

SELECT event_type, event_state_key, event_json FROM roomserver_rooms
INNER JOIN roomserver_state_snapshots
	ON roomserver_rooms.state_snapshot_nid = roomserver_state_snapshots.state_snapshot_nid
INNER JOIN roomserver_state_block
	ON roomserver_state_block.state_block_nid = ANY(roomserver_state_snapshots.state_block_nids)
INNER JOIN roomserver_event_state_keys
	ON roomserver_state_block.event_state_key_nid = roomserver_event_state_keys.event_state_key_nid
INNER JOIN roomserver_event_types
	ON roomserver_state_block.event_type_nid = roomserver_event_types.event_type_nid
INNER JOIN roomserver_event_json
	ON roomserver_state_block.event_nid = roomserver_event_json.event_nid
WHERE roomserver_rooms.room_id = '!zVpPeWAObqutioiNzB:jki.re';

Get room state at (before) a specific event

SELECT event_type, event_state_key, event_json FROM roomserver_events
INNER JOIN roomserver_state_snapshots
	ON roomserver_events.state_snapshot_nid = roomserver_state_snapshots.state_snapshot_nid
INNER JOIN roomserver_state_block
	ON roomserver_state_block.state_block_nid = ANY(roomserver_state_snapshots.state_block_nids)
INNER JOIN roomserver_event_state_keys
	ON roomserver_state_block.event_state_key_nid = roomserver_event_state_keys.event_state_key_nid
INNER JOIN roomserver_event_types
	ON roomserver_state_block.event_type_nid = roomserver_event_types.event_type_nid
INNER JOIN roomserver_event_json
	ON roomserver_state_block.event_nid = roomserver_event_json.event_nid
WHERE roomserver_events.event_id = '$ZONCZPPj1dISY581ddYDqCqgMW4YrmGXclLWfP8ttJA';

Get specific state snapshot

SELECT event_type, event_state_key, event_json FROM roomserver_state_snapshots
INNER JOIN roomserver_state_block
	ON roomserver_state_block.state_block_nid = ANY(roomserver_state_snapshots.state_block_nids)
INNER JOIN roomserver_event_state_keys
	ON roomserver_state_block.event_state_key_nid = roomserver_event_state_keys.event_state_key_nid
INNER JOIN roomserver_event_types
	ON roomserver_state_block.event_type_nid = roomserver_event_types.event_type_nid
INNER JOIN roomserver_event_json
	ON roomserver_state_block.event_nid = roomserver_event_json.event_nid
WHERE roomserver_state_snapshots.state_snapshot_nid = 109;

Get a list of rooms that local users are joined to

SELECT room_id, event_state_key as user_id FROM roomserver_membership
INNER JOIN roomserver_event_state_keys
	ON roomserver_membership.target_nid = roomserver_event_state_keys.event_state_key_nid
INNER JOIN roomserver_rooms
	ON roomserver_rooms.room_nid = roomserver_membership.room_nid
WHERE target_local = true AND membership_nid = 3;

Clean unused state snapshots and state blocks

BEGIN WORK;
LOCK TABLE roomserver_state_snapshots IN EXCLUSIVE MODE;
LOCK TABLE roomserver_rooms IN EXCLUSIVE MODE;
LOCK TABLE roomserver_events IN EXCLUSIVE MODE;
DELETE FROM roomserver_state_snapshots WHERE state_snapshot_nid = ANY(
	SELECT state_snapshot_nid FROM roomserver_state_snapshots EXCEPT ALL(
		SELECT state_snapshot_nid FROM roomserver_events UNION
		SELECT state_snapshot_nid FROM roomserver_rooms
	)
);
DELETE FROM roomserver_state_block WHERE state_block_nid = ANY( 
	SELECT DISTINCT state_block_nid FROM roomserver_state_block EXCEPT ALL(
		SELECT DISTINCT unnest(state_block_nids) FROM roomserver_state_snapshots
	)
);
COMMIT WORK;

Count forward extremities

SELECT room_id, array_length(latest_event_nids, 1) AS fwd_extremities
FROM roomserver_rooms
GROUP BY room_id, latest_event_nids
ORDER BY fwd_extremities DESC;