SQL - twiederk/ArmyBuilder GitHub Wiki

SQLite

Export

  • Export database
    • sqlite3 ArmyBuilderTest.db .dump > output.sql
  • Export only the schema without data:
    • sqlite3 ArmyBuilderTest.db ".schema" > schema.sql
  • Export a specific table:
    • sqlite3 ArmyBuilderTest.db ".dump table_name --data-only" > output.sql
  • Export all tables with prefix army
    • sqlite3 ArmyBuilderTest.db ".dump army% --data-only" > output.sql

Import

Create and import

  • Create empty database: sqlite3 ArmyBuilderTest.db
  • Create schema and import data
.read 0001_schema.sql
.read 0002_data.sql
.read 0003_army.sql

Copy

  • Create table and copy data
    • CREATE TABLE _table_name AS SELECT * FROM table_name;
  • Copy data into existing table
    • INSERT INTO _table_name SELECT * FROM table_name;

Normalize profiles

-- Display profiles of army list to be normalized
SELECT profile_name, count(*) as num FROM army_list_profile WHERE army_list_id = 7 GROUP BY profile_name having num > 1 ORDER BY num DESC;

-- Select id of profile use
SELECT profile_id, profile_name, profile_points FROM army_list_profile WHERE profile_name = 'Hochelfen Elite';
-- 11901

-- Normalize profiles
UPDATE single_model SET profile_id = 11901 WHERE profile_id IN (SELECT profile_id FROM army_list_profile WHERE profile_name = 'Hochelfen Elite');
DELETE FROM profile WHERE name = 'Hochelfen Elite' AND id <> 11901;

-- Export normalized data
sqlite3 armybuilder.db ".dump profile" > profile.sql
sqlite3 armybuilder.db ".dump single_model" > single_model.sql

Verification

SELECT count(*) FROM army_list;
-- => 15
SELECT count(*) FROM army_category;
-- => 4
SELECT COUNT(*) FROM item_class;
-- => 7
SELECT count(*) FROM profile;
-- => 589
SELECT count(*) FROM main_model;
-- => 858
SELECT count(*) FROM single_model;
-- => 1009
SELECT count(*) FROM melee_weapon;
-- => 201
SELECT count(*) FROM ranged_weapon;
-- => 21
SELECT count(*) FROM armor;
-- => 63
SELECT count(*) FROM shield;
-- => 17
SELECT count(*) FROM standard;
-- => 55
SELECT count(*) FROM instrument;
-- => 2
SELECT count(*) FROM misc;
-- => 88
SELECT count(*) FROM item;
-- => 447
SELECT COUNT(*) FROM slot;
-- => 2156
SELECT COUNT(*) FROM slot_selection;
-- => 258
SELECT count(*) FROM mount_model;
-- => 98
SELECT count(*) FROM figure;
-- => 469
SELECT count(*) FROM main_model_figure;
-- => 452
SELECT sum(number_of_figures) FROM figure;
-- => 2200

Select statements

-- count main models by army list
SELECT al.name, count(*) FROM army_list al, main_model mm WHERE al.id = mm.army_list_id GROUP BY mm.army_list_id;


-- display main models of high elf army list
SELECT al.name, mm.id, mm.name FROM army_list al, main_model mm WHERE al.id = mm.army_list_id AND al.id = 7;


-- display single models of high elf army list
SELECT al.name, mm.name, 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 = m.main_model_id WHERE al.id = 7;

-- display single models of high elf army list ordered by army category
SELECT al.name, ac.category, mm.name, 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 = 7 ORDER BY mm.army_category_id;
 
-- display single models with profile of high elf army list
SELECT al.name, ac.category, mm.name, mm.points, sm.name, p.movement, p.weapon_skill, p.ballistic_skill, p.strength, p.toughness, p.wounds, p.initiative, p.attacks, p.moral, p.points FROM army_list al, army_category ac, main_model mm, single_model sm, profile p WHERE mm.army_category_id = ac.id AND al.id = mm.army_list_id AND mm.id = sm.main_model_id AND sm.profile_id = p.id AND al.id = 7 ORDER BY ac.category, mm.name;


-- display all army_* tables
SELECT * FROM army; SELECT * FROM army_unit; SELECT * FROM army_main_model; SELECT * FROM army_single_model; SELECT * FROM army_slot; SELECT * FROM army_slot_selection;

-- display all armies
SELECT * FROM army;

-- display armies with units
SELECT a.id, a.name AS army_name, u.name AS unit_name FROM army a, unit u WHERE a.id = u.army_id;

-- display armies with units and main models
SELECT a.id, a.name AS army_name, u.name AS unit_name, mm.name AS main_model_name, umm.count FROM army a, unit u, unit_main_model umm, main_model mm WHERE a.id = u.army_id AND u.id = umm.unit_id AND mm.id = umm.main_model_id;

-- display all single models with all slots with item
SELECT s.id AS slot_id, sm.id, sm.name, i.id, i.name FROM single_model sm LEFT JOIN slot s ON sm.id = s.single_model_id LEFT JOIN item i ON s.item_id = i.id;

-- display slots with item of high elf spearmen 
SELECT s.id AS slot_id, s.magic, sm.id, sm.name, i.id, i.name, ic.name FROM single_model sm LEFT JOIN slot s ON sm.id = s.single_model_id LEFT JOIN item i ON s.item_id = i.id LEFT JOIN item_class ic ON s.item_class_id = ic.id WHERE sm.id = 46814;

-- display slots with item of high elf general
SELECT s.id AS slot_id, s.magic, sm.id, sm.name, i.id, i.name, ic.name FROM single_model sm LEFT JOIN slot s ON sm.id = s.single_model_id LEFT JOIN item i ON s.item_id = i.id LEFT JOIN item_class ic ON s.item_class_id = ic.id WHERE sm.id = 46491;

-- display non-magic items of high elf army list
SELECT COUNT(*) FROM melee_weapon WHERE magic = 0 AND (army_list_id = 7 OR army_list_id = -1);

-- display non-magic and magic items of high elf army list
SELECT COUNT(*) FROM melee_weapon WHERE army_list_id = 7 OR army_list_id = -1;

-- display id and name of single models carrying item with id 70
SELECT sm.id, sm.name FROM single_model sm JOIN slot s ON sm.id = s.single_model_id WHERE s.item_id = 70;

-- display ids of 'None' item
SELECT id FROM item WHERE name IN ('kein', 'keine', 'keines', 'Handwaffe') ORDER BY id;
-- 1, 10, 30, 40, 60, 90, 100

-- Display character main models of high elf army
SELECT sm.id, sm.name FROM single_model sm LEFT JOIN main_model mm ON mm.id = sm.main_model_id WHERE mm.army_list_id = 7 AND mm.army_category_id = 0;

-- Display ids of slots with none item of special characters of high elf amry
SELECT s.id FROM slot s LEFT JOIN item i ON s.item_id = i.id WHERE i.id IN (1, 10, 30, 40, 60, 90, 100) AND s.single_model_id IN (46367, 46368, 46369, 46370, 46371, 46491, 46499, 46500, 46501, 46502, 46838, 46839);

-- Display main models and single models orderd by army category of high elf army
SELECT al.name, ac.category, mm.id, mm.name, sm.id, sm.name FROM main_model mm LEFT JOIN army_list al ON mm.army_list_id = al.id LEFT JOIN army_category ac ON mm.army_category_id = ac.id LEFT JOIN single_model sm ON mm.id = sm.main_model_id WHERE mm.army_list_id = 7 ORDER BY ac.id, sm.name;

-- Display complete army with units, main models, single models, slots and items
SELECT a.id AS army_id, a.name AS army_name, au.id AS army_unit_id, au.name AS unit_name, amm.id AS army_main_model_id, amm.name AS main_model_name, asm.id AS army_single_model_id, asm.name AS single_model_name, army_slot.id AS army_slot_id, ic.id, ic.name, i.id, i.name FROM army a LEFT JOIN army_unit au ON au.army_id = a.id LEFT JOIN army_main_model amm ON amm.army_unit_id = au.id LEFT JOIN army_single_model asm ON asm.army_main_model_id = amm.id LEFT JOIN army_slot ON army_slot.army_single_model_id = asm.id LEFT JOIN item_class ic ON ic.id = army_slot.item_class_id LEFT JOIN item i ON i.id = army_slot.item_id WHERE army_id = 1;

-- Display profile id of single model with name Schwertmeister
SELECT p.id AS profile_id FROM single_model sm LEFT JOIN profile p ON sm.profile_id = p.id WHERE sm.name = 'Schwertmeister';

-- Display army list, main model, single model and profile of high elf 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.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 = 7;

-- display equipment points of all single models
SELECT single_model_id, SUM(i.points) FROM slot s LEFT JOIN item i ON s.item_id = i.id GROUP BY single_model_id;

-- Display main_models with exactly one single model of high elf army
SELECT mm.id as main_model_id, mm.name as main_model_name, sm.id as single_model_id, sm.name as single_model_name, count(sm.id) as num FROM single_model sm LEFT JOIN main_model mm ON sm.main_model_id = mm.id WHERE mm.army_list_id = 7 GROUP BY mm.id HAVING num = 1;

-- Display main_model_points, single_model_points and profile of high elf army list
SELECT mm.id AS main_model_id, mm.name AS main_model_name, sm.id AS single_model_id, sm.name AS single_model_name, count(sm.id) AS num , p.id AS profile_id, single_model_points, mm.points AS main_model_points FROM single_model sm LEFT JOIN main_model mm ON sm.main_model_id = mm.id LEFT JOIN (SELECT single_model_id, SUM(i.points) AS single_model_points FROM slot s LEFT JOIN item i ON s.item_id = i.id GROUP BY single_model_id) eq ON sm.id = eq.single_model_id LEFT JOIN profile p ON sm.profile_id = p.id WHERE mm.army_list_id = 7 GROUP BY mm.id HAVING num = 1;

-- Display main model, single model and profile of high elf army list
SELECT 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 FROM single_model sm LEFT JOIN main_model mm ON sm.main_model_id = mm.id LEFT JOIN profile p ON sm.profile_id = p.id WHERE mm.army_list_id = 7;

-- Display main models of high elf army with old points
SELECT mm.id, ac.category, mm.name, mm.points FROM main_model mm INNER JOIN army_category ac ON mm.army_category_id = ac.id WHERE army_list_id = 7 ORDER BY army_category_id, name;