Page:
Useful Database Queries
12
Useful Database Queries
Neil Alexander edited this page 2021-01-11 12:24:00 +00:00
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;