Skip to content

DATABASE_QUERIES.md

Latest commit

 

History

History
258 lines (226 loc) · 5.33 KB

DATABASE_QUERIES.md

File metadata and controls

258 lines (226 loc) · 5.33 KB

Useful Database Queries

These SQL queries can be run directly on your PostgreSQL server for managing your worldgate network.

Connect to your database:

psql -U worldgate -d worldgate

Server Management

List All Active Servers

SELECT name, url, updated_at
FROM servers
WHERE is_active = TRUE
ORDER BY name;

Find Inactive Servers

SELECT name, url, updated_at
FROM servers
WHERE is_active = FALSE
  OR updated_at < NOW() - INTERVAL '5 minutes'
ORDER BY updated_at DESC;

Deactivate a Server

UPDATE servers
SET is_active = FALSE
WHERE name = 'Server Name';

Gate Management

List All Gates

SELECT
  w.id,
  s.name as server_name,
  w.position,
  w.quality
FROM worldgates w
JOIN servers s ON w.server_id = s.id
ORDER BY s.name, w.position;

Find Unlinked Gates

SELECT
  w.id,
  s.name as server_name,
  w.position
FROM worldgates w
JOIN servers s ON w.server_id = s.id
WHERE w.destination_gate_id IS NULL
ORDER BY s.name;

Find Linked Gates

SELECT
  w.id as gate_id,
  s1.name as source_server,
  w.position as source_position,
  w2.id as dest_gate_id,
  s2.name as dest_server,
  w2.position as dest_position
FROM worldgates w
JOIN servers s1 ON w.server_id = s1.id
LEFT JOIN worldgates w2 ON w.destination_gate_id = w2.id
LEFT JOIN servers s2 ON w.destination_server_id = s2.id
WHERE w.destination_gate_id IS NOT NULL
ORDER BY s1.name;

Count Gates Per Server

SELECT
  s.name,
  COUNT(w.id) as gate_count,
  COUNT(w.destination_gate_id) as linked_gates
FROM servers s
LEFT JOIN worldgates w ON s.id = w.server_id
GROUP BY s.name
ORDER BY gate_count DESC;

Link Two Gates

-- Link gate A to gate B
UPDATE worldgates
SET
  destination_gate_id = 'uuid-of-gate-b',
  destination_server_id = 'uuid-of-server-b'
WHERE id = 'uuid-of-gate-a';

-- Link gate B to gate A (bidirectional)
UPDATE worldgates
SET
  destination_gate_id = 'uuid-of-gate-a',
  destination_server_id = 'uuid-of-server-a'
WHERE id = 'uuid-of-gate-b';

Unlink a Gate

UPDATE worldgates
SET
  destination_gate_id = NULL,
  destination_server_id = NULL
WHERE id = 'gate-uuid';

Transfer Analytics

Recent Transfers

SELECT
  player_name,
  s1.name as from_server,
  s2.name as to_server,
  transfer_time,
  success
FROM transfer_logs t
LEFT JOIN servers s1 ON t.source_server_id = s1.id
LEFT JOIN servers s2 ON t.destination_server_id = s2.id
ORDER BY transfer_time DESC
LIMIT 50;

Transfer Count by Player

SELECT
  player_name,
  COUNT(*) as transfer_count,
  COUNT(*) FILTER (WHERE success = TRUE) as successful_transfers
FROM transfer_logs
GROUP BY player_name
ORDER BY transfer_count DESC;

Transfer Count by Server Pair

SELECT
  s1.name as from_server,
  s2.name as to_server,
  COUNT(*) as transfer_count
FROM transfer_logs t
JOIN servers s1 ON t.source_server_id = s1.id
JOIN servers s2 ON t.destination_server_id = s2.id
GROUP BY s1.name, s2.name
ORDER BY transfer_count DESC;

Failed Transfers

SELECT
  player_name,
  s1.name as from_server,
  s2.name as to_server,
  transfer_time
FROM transfer_logs t
LEFT JOIN servers s1 ON t.source_server_id = s1.id
LEFT JOIN servers s2 ON t.destination_server_id = s2.id
WHERE success = FALSE
ORDER BY transfer_time DESC
LIMIT 50;

Transfers in Last Hour

SELECT
  COUNT(*) as total_transfers,
  COUNT(*) FILTER (WHERE success = TRUE) as successful,
  COUNT(DISTINCT player_name) as unique_players
FROM transfer_logs
WHERE transfer_time > NOW() - INTERVAL '1 hour';

Maintenance

Delete Old Transfer Logs (Older than 30 days)

DELETE FROM transfer_logs
WHERE transfer_time < NOW() - INTERVAL '30 days';

Find Orphaned Gates (Server Deleted)

SELECT w.id, w.position
FROM worldgates w
LEFT JOIN servers s ON w.server_id = s.id
WHERE s.id IS NULL;

Clean Up Orphaned Gates

DELETE FROM worldgates
WHERE server_id NOT IN (SELECT id FROM servers);

Reset All Gate Links (USE WITH CAUTION)

UPDATE worldgates
SET
  destination_gate_id = NULL,
  destination_server_id = NULL;

Network Topology

Find Gate Pairs (Both Directions)

SELECT
  w1.id as gate_a,
  s1.name as server_a,
  w2.id as gate_b,
  s2.name as server_b
FROM worldgates w1
JOIN worldgates w2 ON w1.destination_gate_id = w2.id
JOIN servers s1 ON w1.server_id = s1.id
JOIN servers s2 ON w2.server_id = s2.id
WHERE w2.destination_gate_id = w1.id
ORDER BY s1.name, s2.name;

Network Graph (Connection Matrix)

SELECT
  s1.name as from_server,
  s2.name as to_server,
  COUNT(*) as connection_count
FROM worldgates w
JOIN servers s1 ON w.server_id = s1.id
JOIN servers s2 ON w.destination_server_id = s2.id
WHERE w.destination_gate_id IS NOT NULL
GROUP BY s1.name, s2.name
ORDER BY s1.name, s2.name;

Tips

  1. Always backup before running UPDATE or DELETE queries
  2. Test queries with SELECT before running modifications
  3. Use UUIDs from the database, not manually typed
  4. Check constraints - some deletes may cascade
  5. Monitor logs table size and clean regularly

PostgreSQL Tools

  • psql: Command-line PostgreSQL client
  • pgAdmin: Web-based PostgreSQL administration
  • Table Viewer: \dt to list tables, \d tablename for schema
  • Query History: \s to view command history
  • Help: \? for psql commands, \h for SQL help