Character joins a battle

Submitted by Brutus5000 on Sun, 07/24/2016 - 01:12

Character joins a battle in a chosen role (attacker, defender) with a preselected set of reinforcements

Input data
  • <character id>
  • <role>
  • <battle id>
  • aggregated list of reinforcements: <unit id>, <unit quantity>

Character is not attacking/defending elsewhere:

SELECT FROM battle_participants
JOIN battles ON battle_participants.fk_battle =
WHERE battle_participants.fk_charackter = <character id> AND battles.status IN ('I','R')

ASSERT THAT no result (otherwise player is already working in


Gather more information:

SELECT battles.attacking_faction, battles.defending faction, map.total_slots
FROM battles
JOIN planets ON battles.fk_planet =
JOIN map_pool ON planets.fk_map =
WHERE id = <battle id>
SELECT faction FROM character WHERE id = <character id>


Character's faction fits to role:

IF role = 'A' THEN
--> ASSERT THAT battles.attacking_faction = characters.faction
--> ASSERT TJAT battles.defending_faction = characters.faction

Open slot is available:

SELECT COUNT(*) AS taken_slots FROM battle_participants
WHERE fk_battle = <battle id> AND role = <role>

ASSERT THAT taken_slot < total_slots / 2


Chosen reinforcement units are actually "in stock":

SELECT unit_transaction_positions.fk_unit, SUM(unit_transaction_positions. quantity) FROM unit_transactions
JOIN unit_transaction_positions ON = unit_transaction_positions.fk_unit_transaction
WHERE unit_transactions.fk_character = <character id>
GROUP BY unit_transaction_positions.fk_unit

ASSERT THAT each for each unit quantity > selected <unit id> <unit quantity>

Database transaction

INSERT INTO battle_participants (fk_battle, fk_character, role) VALUES (<battle id>, <character id>, <role>)


IF aggregated list of reinforcements is NOT EMPTY THEN

INSERT INTO unit_transaction (fk_character, fk_battle, type, created_at) VALUES (<character id>, <battle id>, 'D', NOW() )
@transaction = LAST_INSERT_ID()

for each tuple <unit id>, <unit quantity>:

INSERT INTO unit_transaction_positions (fk_unit_transaction, fk_unit, quantity) VALUES (@transaction, <unit id>, <unit quantity>


Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.