Database Schema Query Examples

Version: 1.0.0 Category: Database Purpose: Query examples for database schema features

Overview

These examples demonstrate SQL queries for database schema features: destroyed structs tracking, cheatsheet details on struct types, the permission hash level, and signer transaction table changes.

Features Covered

Destroyed Structs

Database change (2025-12-29): Added destroyed column to the struct table. Destroyed structs persist for 5 blocks (StructSweepDelay) before their slots are cleared.

Find All Destroyed Structs

SELECT * FROM struct WHERE destroyed = true

Example result:

[
  {
    "id": "5-1",
    "owner_id": "1-11",
    "struct_type_id": 14,
    "location_type": 1,
    "location_id": "2-1",
    "status": 0,
    "destroyed": true,
    "created_at": "2026-01-01T10:00:00Z",
    "updated_at": "2026-01-01T10:05:00Z"
  }
]

Columns returned: id, owner_id, struct_type_id, location_type, location_id, status, destroyed, created_at, updated_at.

Find Destroyed Structs by Owner

SELECT * FROM struct WHERE owner_id = ? AND destroyed = true

Parameter: owner_id = 1-11

Find Destroyed Structs on a Planet

SELECT * FROM struct WHERE location_type = 1 AND location_id = ? AND destroyed = true

Parameter: location_id = 2-1

Destroyed structs persist for StructSweepDelay (5 blocks) before the slot is cleared.

Count Destroyed Structs

SELECT COUNT(*) FROM struct WHERE destroyed = true

Find Recently Destroyed Structs

SELECT * FROM struct WHERE destroyed = true AND updated_at > NOW() - INTERVAL '5 blocks'

Useful for monitoring structs during the StructSweepDelay period.

Use Cases

Monitor sweep delay: Query destroyed structs to track which ones are still in the delay period and haven’t been cleaned up yet.

Cleanup tracking: After destruction, verify that cleanup occurs after 5 blocks by re-querying destroyed structs.

Cheatsheet Details

Database changes:

Get Cheatsheet Details for a Struct Type

SELECT cheatsheet_details, cheatsheet_extended_details FROM struct_type WHERE id = ?

Parameter: id = 14

Example result:

{
  "id": 14,
  "name": "Command Ship",
  "cheatsheet_details": "Command ship details...",
  "cheatsheet_extended_details": "Extended command ship details..."
}

Get All Struct Types with Cheatsheet Details

SELECT id, name, cheatsheet_details, cheatsheet_extended_details
FROM struct_type
WHERE cheatsheet_details IS NOT NULL

Search Cheatsheet Content

SELECT * FROM struct_type
WHERE cheatsheet_details LIKE ? OR cheatsheet_extended_details LIKE ?

Parameter: pattern = %keyword%

Useful for finding struct types by searching their cheatsheet content.

Use Cases

Documentation generation: Query cheatsheet details to build comprehensive struct type documentation automatically.

Struct type lookup: Query cheatsheet details for quick reference information about any struct type.

Permission Hash

Database change: Hash permissions are stored as granular columns in the permission view, mapping to bits 20-23 in the API permission value. PermHashAll = 15728640. The full permission bitmask is 25 bits (PermAll = 33554431); bit 24 (PermGuildUGCUpdate = 16777216) was added in v0.16.0 for guild-moderated UGC updates and is not yet exposed as a dedicated column in the permission view – check it with (permission.val & 16777216) > 0.

Get Hash Permissions for a Specific Permission

SELECT permission_hash_build, permission_hash_mine, permission_hash_refine, permission_hash_raid
FROM permission WHERE object_id = ? AND player_id = ?

Parameters: object_id = 0-1, player_id = 1-11

Example result:

{
  "object_id": "0-1",
  "player_id": "1-11",
  "permission_hash_build": true,
  "permission_hash_mine": true,
  "permission_hash_refine": true,
  "permission_hash_raid": true
}

All hash columns true maps to (permission.value & 15728640) == 15728640 in the API (HasAll check).

Get All Permissions with All Hash Permissions

SELECT * FROM permission
WHERE permission_hash_build = true
  AND permission_hash_mine = true
  AND permission_hash_refine = true
  AND permission_hash_raid = true

Example result:

[
  { "object_id": "0-1", "player_id": "1-11", "permission_hash_build": true, "permission_hash_mine": true, "permission_hash_refine": true, "permission_hash_raid": true, "val": 33554431 }
]

Get Permissions with Specific Hash Bit (e.g., Mine)

SELECT * FROM permission WHERE player_id = ? AND permission_hash_mine = true

Parameter: player_id = 1-11

Get Hash Permissions for an Object

SELECT * FROM permission WHERE object_id = ?
  AND (permission_hash_build = true OR permission_hash_mine = true
       OR permission_hash_refine = true OR permission_hash_raid = true)

Parameter: object_id = 0-1

Count Permissions with Any Hash Bit

SELECT COUNT(*) FROM permission
WHERE permission_hash_build = true
   OR permission_hash_mine = true
   OR permission_hash_refine = true
   OR permission_hash_raid = true

Database to API Mapping

Database Column Bit API Equivalent (HasAll check)
permission_hash_build = true 20 (permission.value & 1048576) == 1048576
permission_hash_mine = true 21 (permission.value & 2097152) == 2097152
permission_hash_refine = true 22 (permission.value & 4194304) == 4194304
permission_hash_raid = true 23 (permission.value & 8388608) == 8388608
All hash columns true 20-23 (permission.value & 15728640) == 15728640

Example mapping:

{
  "database": { "permission_hash_build": true, "permission_hash_mine": true, "permission_hash_refine": true, "permission_hash_raid": true, "val": 33554431 },
  "api": { "value": "33554431", "hasAllHashPermissions": true, "hasGuildUgcUpdate": true }
}

Use Cases

Permission audit: Query hash permission columns to find all hash permission grants across the system, broken down by operation type.

Access control: Query the specific hash column (e.g., permission_hash_mine) to verify a player has the required hash permission before allowing the operation.

Signer Transaction Changes

Database changes:

Get Signer Transaction by Hash

SELECT * FROM signer_tx WHERE tx_hash = ?

Parameter: tx_hash = transaction_hash

Example result:

{
  "tx_hash": "transaction_hash",
  "permission_level": "hash",
  "tx_type": "struct_build_complete",
  "nonce": "proof_of_work_nonce"
}

The nonce field is now CHARACTER VARYING (was INTEGER).

Get Transactions by Permission Level

SELECT * FROM signer_tx WHERE permission_level = 'hash'

Get Transactions by Type

SELECT * FROM signer_tx WHERE tx_type = ?

Parameter: tx_type = struct_build_complete

New transaction types are available.

Get Transactions with Hash Complete Nonces

SELECT * FROM signer_tx WHERE tx_type LIKE '%complete%' AND nonce IS NOT NULL

Nonces for Hash Complete transactions are CHARACTER VARYING instead of INTEGER.

Get Recent Signer Transactions

SELECT * FROM signer_tx ORDER BY created_at DESC LIMIT ?

Parameter: limit = 100

Use Cases

Transaction audit: Query signer_tx to track all transactions signed with Hash permission level.

Nonce verification: Query signer_tx to verify proof-of-work nonces, keeping in mind the CHARACTER VARYING format.

Transaction type tracking: Query signer_tx to analyze the distribution of transaction types.

Combined Queries

Destroyed Structs with Type Details

SELECT s.*, st.name, st.cheatsheet_details
FROM struct s
JOIN struct_type st ON s.struct_type_id = st.id
WHERE s.destroyed = true

Provides comprehensive information about destroyed structs including their type name and cheatsheet details.

All Hash Permissions for a Player

SELECT * FROM permission WHERE player_id = ?
  AND (permission_hash_build = true OR permission_hash_mine = true
       OR permission_hash_refine = true OR permission_hash_raid = true)

Audits all hash permissions granted to a specific player across all objects, broken down by operation type.

Cross-References