Database Content - twiederk/ArmyBuilder GitHub Wiki

Data conversion

Checklist to add new army list

Profile Points

Assign profile points

-- All profiles of army list
SELECT al.id as army_list_id, al.name as army_list_name, mm.id as main_model_id, mm.name as main_model_name, sm.id as single_model_id, sm.name as single_model_name, p.id as profile_id, p.name AS profile_name, p.points as profile_points FROM army_list al LEFT JOIN main_model mm ON mm.army_list_id = al.id LEFT JOIN single_model sm ON sm.main_model_id = mm.id LEFT JOIN profile p ON sm.profile_id = p.id WHERE al.id = 14;
  • Copy to Excel
  • Set profile names
  • Set points
  • Create UPDATE statements using VERKETTEN function in Excel
  • Execute the UPDATE statements
  • Export profile table sqlite3 ArmyBuilderTest.db ".dump profile --data-only" > output.sql
  • Update 0002_data.sql with update profile
  • Compare Old: and New: per main model in ArmyBuilder application
    • Profile points of special characters are the difference between expected points and points of equipment
    • Profile points of war machines needs update number of single models for the crew
    • Profile points of monsters are the same a the expected points

Delete duplicates

  • Find all duplicate profiles: SELECT name, count(*) as anz FROM profile GROUP BY name HAVING anz > 1;
  • For each duplicate profile:
    • Choose one profile id: SELECT id, name FROM profile WHERE name = 'Zwerge Basis';
      • => 11893
    • Update all single_models: UPDATE single_model SET profile_id = 11893 WHERE profile_id IN (SELECT id FROM profile WHERE name = 'Zwerge Basis');
    • Delete all other profiles except the one with the new id: DELETE from profile WHERE name = 'Zwerge Basis' AND id <> 11893;
  • After removing all duplicate profiles export single_model and profile table
    • sqlite3 ArmyBuilderTest.db ".dump single_model --data-only" > single_model.sql
    • sqlite3 ArmyBuilderTest.db ".dump profile --data-only" > profile.sql
  • Update 0002_data.sql with exported data

Equipment

Slot

  • Check equipment of character models of army list in ArmyBuilder
    • Custom characters need as many Misc (editable, magic) slots as the can carry magic items
  • Check equipment of trooper models of army list in ArmyBuilder
    • Add equipment options (slot and/or slot_selection)
  • Check equipment of monster models of army list in ArmyBuilder
    • Add equipment options (slot and/or slot_selection)
-- display all main model and single models of army list with ids
SELECT ac.category, mm.id AS main_model_id, mm.name, sm.id as single_model_id, sm.name FROM army_list al LEFT JOIN main_model mm ON al.id  = mm.army_list_id LEFT JOIN single_model sm ON mm.id = sm.main_model_id LEFT JOIN army_category ac ON mm.army_category_id = ac.id WHERE al.id = 5 ORDER BY mm.army_category_id, mm.name;


SELECT id, name FROM item_class;
-- 0|MeleeWeapon
-- 1|Shield
-- 2|RangedWeapon
-- 3|Armor
-- 4|Misc
-- 5|Standard
-- 6|Instrument


SELECT id, name FROM item WHERE name IN ('kein', 'keine', 'keines', 'Handwaffe') ORDER BY id;
-- 1|Handwaffe (Melee)
-- 10|keine (Ranged)
-- 30|keines (Shield)
-- 40|keine (Armor)
-- 60|kein (Misc)
-- 90|keines (Instrument)
-- 100|keine (Standard)


-- Select id of specific single model
SELECT id, name FROM single_model WHERE name LIKE 'Azhag%';

-- Add empty slot to single model
SELECT max(id) + 1 FROM slot;

-- id|single_model_id|item_id|editable|magic|item_class_id
INSERT INTO slot VALUES(5113,46769,1,1,0,0); -- melee weapon (None)
INSERT INTO slot VALUES(5114,46769,30,1,0,1); -- shield (None)
INSERT INTO slot VALUES(5115,46769,10,1,0,2); -- ranged weapon (None)
INSERT INTO slot VALUES(5116,46769,40,1,0,3); -- armor (None)
INSERT INTO slot VALUES(5117,46769,60,1,0,4); -- misc (None)
INSERT INTO slot VALUES(5118,46769,100,1,1,5); -- standard (None)
INSERT INTO slot VALUES(5119,46769,90,1,1,6); -- instrument (None)

Slot Selection

  • Slots must be editable
  • Item class of Slot must fit
  • If an option is completly new a new slot must be created
  • If an option adds an item to an already existing item, the Slot is used
    • The existing item must be added to slot selection
    • The new item(s) must be added to slot selection
SELECT max(id) + 1 FROM slot_selection;
id|slot_id|item_id
INSERT INTO slot_selection VALUES(239,5113,1); -- Handwaffe
INSERT INTO slot_selection VALUES(240,5113,2); -- Zweihändige Waffe
INSERT INTO slot_selection VALUES(241,5113,4); -- Hellebarde
INSERT INTO slot_selection VALUES(242,5113,5); -- Speer
INSERT INTO slot_selection VALUES(243,5113,8); -- Zweite Handwaffe

INSERT INTO slot_selection VALUES(244,5114,30); -- keines
INSERT INTO slot_selection VALUES(245,5114,31); -- Schild

INSERT INTO slot_selection VALUES(246,5115,10); -- kein 
INSERT INTO slot_selection VALUES(247,5115,11); -- Bogen

INSERT INTO slot_selection VALUES(248,5116,40); -- keine
INSERT INTO slot_selection VALUES(249,5116,41); -- Leichte Rüstung

Add new main model

SELECT max(id) + 1 FROM main_model;
-- id|army_list_id|army_category_id|name|description|uniquely|points|standard_bearer|musician
INSERT INTO main_model VALUES(12426,14,1,'Speerträger','',0,12.0,0,0);


SELECT max(id) + 1 FROM single_model;
-- id|profile_id|name|main_model_id|count|mount_save
INSERT INTO single_model VALUES(47369,11893,'Speerträger',12426,1,0);


SELECT max(id) + 1 FROM slot;
-- id|single_model_id|item_id|editable|magic|item_class_id
INSERT INTO slot VALUES(4932,47369,31,0,0,1); -- Shield
INSERT INTO slot VALUES(4933,47369,41,0,0,3); -- Light Armor
INSERT INTO slot VALUES(4934,47369,5,0,0,0); -- Spear


SELECT MAX(id) + 1 FROM slot_selection;
-- id|slot_id|item_id


SELECT MAX(id) + 1 FROM main_model_figure;
-- id|main_model_id|figure_id
INSERT INTO main_model_figure VALUES(345,12431,11);

Figures

  • Copy all images to project.
  • In Visual Studio set property Copy to Ouput Directory of all images to Copy if newer
  • Add all images to publish settings

Assignment Guidelines

Assing proper command group to proper trooper.

  • Example: command group of White Lions to White Lions

Assign character figure with proper equipment to proper trooper

  • Example: Character figure with bow to bowman

Assign general standard bearer and musician to every trooper without proper command group, but an image of the troopers.

Beware: Movement type of command group and trooper must match.

  • Example: General standard bearer and musician to bowman when no proper command group exits for spearmen.

How to check images

-- displays images which are not assigned to a main model for high elf army list
SELECT f.id AS figure_id, image_path, mmf.main_model_id FROM figure f LEFT JOIN main_model_figure mmf ON f.id = mmf.figure_id WHERE army_list_id = 7 ORDER BY mmf.main_model_id;
  • Copy all image files to separate directory. Open ArmyBuilder
  • Put separate directory and ArmyBuilder side-by-side
  • Check each image from the separate directory if it is shown by the right main model in the ArmyBuilder.

Add figure to main model

SELECT id, name, image_path FROM figure WHERE image_path LIKE '%HighElf_SeaGuardOfLothern_Musician.jpg';
=> 86

SELECT id, name FROM main_model WHERE name LIKE '%Lothern%' AND army_list_id = 7;
=> 11905

SELECT max(id) + 1 FROM main_model_figure;
-- id|main_model_id|figure_id
INSERT INTO main_model_figure VALUES(79,11905,86);

Unique items

-- Display all main models carrying this item
SELECT mm.id, mm.name, sm.id, sm.name, s.id AS slot_id, i.id AS item_id, i.name AS item_name FROM item i INNER JOIN slot s ON s.item_id = i.id INNER JOIN single_model sm ON sm.id = s.single_model_id INNER JOIN main_model mm ON mm.id = sm.main_model_id WHERE i.id = 6026;

-- Display all unique items with the main model carrying it
SELECT i.item_type, al.name AS army_list_name, mm.id, mm.name, sm.id, sm.name, s.id AS slot_id, i.id AS item_id, i.name AS item_name FROM item i INNER JOIN slot s ON s.item_id = i.id INNER JOIN single_model sm ON sm.id = s.single_model_id INNER JOIN main_model mm ON mm.id = sm.main_model_id INNER JOIN army_list al ON al.id = mm.army_list_id WHERE i.id IN (SELECT id FROM item WHERE uniquely = 1) ORDER BY i.item_type, army_list_name;

-- Display all unique items with the main model carrying it of High Elf army list
SELECT i.item_type, al.name AS army_list_name, mm.id, mm.name, sm.id, sm.name, s.id AS slot_id, i.id AS item_id, i.name AS item_name FROM item i INNER JOIN slot s ON s.item_id = i.id INNER JOIN single_model sm ON sm.id = s.single_model_id INNER JOIN main_model mm ON mm.id = sm.main_model_id INNER JOIN army_list al ON al.id = mm.army_list_id WHERE i.id IN (SELECT id FROM item WHERE uniquely = 1) AND al.id = 7 ORDER BY i.item_type, army_list_name;

-- Display all unique items ordered by item type
SELECT * FROM item WHERE uniquely = 1 ORDER BY item_type, name;

SQL statements

-- select slots of single model/equipment
SELECT s.id AS slot_id, i.id AS item_id, i.name FROM slot s LEFT JOIN item i ON s.item_id = i.id WHERE s.single_model_id = 47563;

-- select slot_selection of slot 
SELECT * FROM slot_selection WHERE slot_id = 4880;

-- id|single_model_id|item_id|editable|magic|item_class_id
INSERT INTO slot VALUES(4884,46726,60,1,0,4);

-- id|slot_id|item_id
INSERT INTO slot_selection VALUES(105, 4884, 60);

Army Lists

High Elves

  • New points of Eltharion and Ellyrianische Grenzreiter doesn't match old points
  • Points of Schwertmeister, Weiße Löwen and Schattenkrieger are fixed by using special equipment (Zweihänder von Hoeth (3), Löwenumhang (1), Schattenumhang (1))
  • Trianoc Streitwagen doesn't support Roßharnisch as equipment option

Dark Elves

  • New Points of Schwarze Reiter doesn't match old points.