SQLiteSpy 101

[whoward69]

SQLiteSpy is a third party utility for executing SQL statements directly against an SQLite database (.db) file. This tutorial covers the basic use of this utility for testing Civ5 SQL statements outside of the game environment.

First we need to download the utility

• Go to SQLiteSpy - https://www.yunqa.de/delphi/apps/sqlitespy/index
• Click the download link (top left)
• Download the zip file
• Extract the SQLiteSpy.exe file from the zip and save it into its own sub-directory somewhere (don't bother with the .dll or .db3 file in the zip)
• Virus scan it (paranoia is good!)

We don't need to install the utility, we can just execute it

• Double-click the .exe to run the application (it will create a new .db3 file in the same sub-dir)

Now we need to open the SQLite database used by the game

• From the "File" menu, select "Open database ..."
• Browse to "My Games\Sid Meier's Civilization 5\cache"
• Select the "Civ5DebugDatabase.db" file and click the "Open" button

(Note 1: The Civ5DebugDatabase.db file contains the modded database, the Civ5CoreDatabase.db file contains the unmodded database)
(Note 2: After you have opened a .db file, you can quickly open it again by selecting it from the "File -> Reopen" menu)

The interface is divided into four main areas

• on the left is a list of all the tables in the database
• top right is an edit area for entering SQL statements
• middle right are the results for the last executed SQL statement
• bottom right is a read-only edit area that can be used to copy a value from a column

You can quickly see the contents of a table by double clicking on it, so scroll down to the Buildings table in the left hand list and double click it

This displays every column, so if you just want to see a few, eg the cost of buildings, enter "SELECT Type, Class, Cost, FaithCost FROM Buildings;" into the top area and from the "Execute" menu, select "Execute SQL" (or just press the F9 key)

Click the Cost column to sort by cost ascending, click it again for descending
Can't spell "Neuschwanstein"? Click on the value in the Type column and copy/paste it from the bottom area

Change the SQL to "SELECT Type, UnitClass, Cost, FaithCost FROM Units;" and press F9
An error is displayed - "no such column: UnitClass" (does that format look familiar?)

The column in the Units table is Class (not UnitClass) so click OK to clear the error, change the SQL and press F9

Moderator Action: Updating link for SQLiteSpy - Pouakai

 

[whoward69]

So how we can we discover what the columns in the tables are?

• Scroll the left hand list down to Units, click on the + icon to the left of the table name and then click on the + icon to the left of "Columns".

Note: If you can't see the column names, resize the field widths at the very top of the left hand list area.

Check Type, Description, Combat and CombatRanged and double-click "Columns", instead of displaying the results of "SELECT * FROM Units" the display will show the results of "SELECT {selected columns} FROM Units"

To find out the exact SQL that was just executed, right-click "Columns", select "Show SELECT statement" and the top right edit area will display the SQL executed, in this case "SELECT Type,Description,Combat,RangedCombat FROM Units"

The SQL is displayed in a new tab, to open a new tab manually from the "File" menu, select "New SQL"
You can execute any valid SQL - SELECT, INSERT, UPDATE, DELETE, ALTER TABLE, CREATE TRIGGER, etc. If you trash the database, don't worry, just restart a Civ game and the game will recreate the database for you.

So where are the Language_en_US et al tables? The language tables are in the "Localization-Merged.db" file. Open this file.

Language_en_US is at the bottom of the left hand area under "Views". Double-click it and you'll see all the text.

Reopen the debug database, and execute the SQL "SELECT u.Type, u.Description FROM Units u;". You'll see all the unit types with their associated TXT_KEY_ descriptions
Now execute "SELECT u.Type, l.Text FROM Units u, Language_en_US l WHERE u.Description=l.Tag;" - you'd expect to see all the actual text rather than the TXT_KEY_s, but you'll get an error "no such table: Language_en_US", so what's going on?

The SQLite C++ libraries enable two or more database (.db) files to appear as a single database schema, SQLiteSpy also supports this, but you need to attach the database.

• From the "File" menu, select "Attach database ...", select the "Localization-Merged.db" file and click "Open".
• Execute the SQL you'll see the expected results.

Take care when writing triggers, as triggers cannot refer to tables in attached database (.db) files.

[END]

 

[whoward69]

So how can we use this for testing a mod?

Consider a mod that implements the SQL in the "Circumventing mod load order issues with database triggers" tutorial

We can test the update of the Civilization_FreeTechs table as follows

• Start SQLiteSpy and (re-)open the debug database
• Scroll down to the Civilization_FreeTechs table and double click on it, note that all entries are set to TECH_AGRICULTURE
• Enter the update SQL ("UPDATE Civilization_FreeTechs SET TechType='TECH_FIRE' WHERE TechType='TECH_AGRICULTURE';") into the top area and execute it
• Note in the bottom status bar it says "44 updated"
• Double click on the Civilization_FreeTechs table and note that everything has changed to TECH_FIRE. (This is one advantage of the way Civ 5 has set up references within the database as being validated by C++ and not a constraint within the database - we don't actually need to create the TECH_FIRE entry.)

So the UPDATE is correct (assuming you made no copy/paste errors, otherwise correct the mistakes and re-execute the update), so lets create the trigger

• Enter the "CREATE TRIGGER CivFireStartingTech ..." SQL into the top area and execute it
• Nothing appears to happen (unless you have a copy/paste error ...)
• Click on the + icon to the left of the Civilization_FreeTechs entry in the left hand list, there is now a Triggers item
• Click on the + icon to the left of that, and we can see our named trigger (CivFireStartingTech)

So how do we know the trigger is functioning correctly? To test it we need to add some rows to the Civilization_FreeTechs table

• Enter "INSERT INTO Civilization_FreeTechs(CivilizationType, TechType) VALUES('CIV_TEST', 'TECH_AGRICULTURE');" and execute it
• The status bar shows "1 updated"
• Double click on the Civilization_FreeTechs table, and scroll down to the bottom of the results
• Note that CIV_TEST is correctly starting with TECH_FIRE and not TECH_AGRICULTURE
• We MUST also test that we can add other free techs, so enter "INSERT INTO Civilization_FreeTechs(CivilizationType, TechType) VALUES('CIV_TEST', 'TECH_POTTERY');" and execute it
• The status bar shows "1 updated"
• Double click on the Civilization_FreeTechs table, and scroll down to the bottom of the results, CIV_TEST has both TECH_FIRE and TECH_POTTERY, so all is working correctly

To test the MinorCivLeaderThera trigger, we will need to add an entry into the MinorCivilizations

• Enter the "CREATE TRIGGER MinorCivLeaderThera ..." SQL into the top area and execute it (I'll assume you got it right!)
• Double click on the Civilization_Leaders table and check there is no entry for MINOR_CIV_THERA
• Execute the SQL "INSERT INTO MinorCivilizations(Type) VALUES('MINOR_CIV_THERA');"
• We get and error telling us that MinorCivTrait cannot be null
• Execute the SQL "INSERT INTO MinorCivilizations(Type, MinorCivTrait) VALUES('MINOR_CIV_THERA', 'MINOR_TRAIT_CULTURED');"
• The status bar shows "1 updated"
• Double click on the Civilization_Leaders table, scroll to the bottom and check there is an entry for MINOR_CIV_THERA
• Also test that we don't generate an entry for values other than MINOR_CIV_THERA

You can test any SQL used in a mod with these techniques, just execute each SQL statement one at a time, from top to bottom in the .sql file and in the order the .sql files load (as determined by the order of the OnModActivated->UpdateDatabase actions)

[END]