A bunch of example queries for use in schemaverse

schemaverse_sample_queries.sql

-- Get user ID (usefull as a subquery)
SELECT GET_PLAYER_ID(SESSION_USER);

-- Basic statistics
SELECT 
    balance, 
    fuel_reserve, 
    (SELECT count(*) FROM my_ships) AS ships, 
    (SELECT count(*) FROM planets WHERE conqueror_id=GET_PLAYER_ID(SESSION_USER)) AS planets, 
FROM my_player;

-- Find target planets
SELECT id, location, location <-> point(%lat%,%long%) as distance
FROM planets
WHERE conqueror_id!=GET_PLAYER_ID(SESSION_USER) AND
ORDER BY distance asc 
LIMIT 10;

-- Convert money to fuel
SELECT CONVERT_RESOURCE('MONEY', %amount%);

-- Convert all money to fuel
SELECT fuel_reserve + CONVERT_RESOURCE('MONEY', balance) AS assets FROM my_player

-- Convert fuel to money
SELECT CONVERT_RESOURCE('FUEL', %amount%);

-- Convert all fuel to money
SELECT balance + CONVERT_RESOURCE('FUEL', fuel_reserve) AS assets FROM my_player

-- Refuel a specific ship
SELECT REFUEL_SHIP(%id%);

-- Refuel all ships
SELECT REFUEL_SHIP(id) FROM my_ships WHERE current_fuel < max_fuel;

-- Create a new ship
INSERT INTO my_ships(
    name,
    attack, defense, engineering, prospecting,
    location,
    action,
    action_target_id
)
VALUES(
    'My Ship',
    -- The following numbers should add up to 20, which is the amount of stats you get for free
    0,
    0,
    0,
    20,
    (SELECT location FROM planets WHERE id = %planet_id%),
    'MINE', -- Or 'ATTACK' if you want the auto action to be attack
    %planet_id%
)

-- Upgrade a ship
SELECT UPGRADE(%id%, %option%, %amount%); -- Options are: 'MAX_FUEL', 'PROSPECTING', 'ATTACK', 'DEFENSE', 'RANGE'

-- Order a ship to mine a planet     
UPDATE my_ships SET action = 'MINE', action_target_id = %planet_id% WHERE id = %id%

-- Order all ships to mine closest planet
UPDATE my_ships
SET 
    action = 'MINE', 
    action_target_id = planets_in_range.planet
FROM planets_in_range 
WHERE my_ships.id = planets_in_range.ship

-- Order specific ship to attack specific ship
SELECT ATTACK(%my_ship_id%, %enemy_ship_id%);

-- Order all ships to attack closest enemy
SELECT
    ship_in_range_of AS source,
    ATTACK(
        ship_in_range_of,
        coalesce((
            SELECT id 
            FROM ships_in_range 
            WHERE 
                ship_in_range_of = s.ship_in_range_of AND
                health > 0
            LIMIT 1
        ), 1)
    )
FROM ships_in_range as s