Tables SQL for total conversion modders

Pazyryk

Deity
Joined
Jun 13, 2008
Messages
3,584
This tutorial is aimed primarily at total conversion modders, though others might find something useful. It's a compilation of lessons and tricks I've learned adding content for Éa. I'm assuming that you already know how to add or modify table items using XML. You can be experienced or totally new to SQL.

The problem for a total conversion modder is that you need to add tons of data and you want that process to be as painless as possible. SQL is an easy way to enter and modify table data. It makes your data easier to see than XML, which facilitates editing. Even better, it can provide a lot of automation (not possible with XML) that will save you both work and errors.

Some resources

SQLite FAQ, syntax, functions, etc.: http://www.sqlite.org/
My own table SQL for Éa: Modified Core Tables, New Tables (there's a lot of SQL there, not all of which is covered here)
Important ref when deleting core items: Hard-coded table IDs and Type strings in dll

The basic delete-everything-add-everything-renumber-everything method

The basic method I use for most tables is:
  1. Delete everything
  2. Add my own mod content in one or more insert and update statements
  3. Renumber IDs if it is a table with IDs (this is usually a good idea, except when it is a bad idea ;); see next section for how to identify the latter)
Here's a bit of my Buildings.sql as an example, abbreviated to show only a few buildings. Skip over the blue stuff (for now). The basic 1,2,3 structure above is in black text:
Code:
DELETE FROM Buildings;
[COLOR="DeepSkyBlue"]ALTER TABLE Buildings ADD COLUMN 'EaPrereqPolicy' TEXT DEFAULT NULL;
ALTER TABLE Buildings ADD COLUMN 'EaSpecial' TEXT DEFAULT NULL;[/COLOR]

-- 1st available
INSERT INTO Buildings (Type,	Cost,	FoodKept,	NeverCapture,	ArtDefineTag,			IconAtlas,		PortraitIndex) VALUES
('BUILDING_MONUMENT',		80,	0,		1,		'MONUMENT',			'BW_ATLAS_1',		21	),
('BUILDING_WARRENS',		200,	10,		1,		'ART_DEF_BUILDING_FORGE',	'NEW_BLDG_ATLAS2_DLC',	1	);

-- early specialist
INSERT INTO Buildings (Type,	Cost,	GoldMaintenance,PrereqTech,		EaPrereqPolicy,		EaSpecial,	Happiness,	SpecialistType,		SpecialistCount,NeverCapture,	ArtDefineTag,			IconAtlas,		PortraitIndex) VALUES
('BUILDING_MARKETPLACE',	200,	0,		'TECH_CURRENCY',	NULL,			NULL,		0,		'SPECIALIST_TRADER',	1,		0,		'ART_DEF_BUILDING_MARKET',	'BW_ATLAS_1',		16	),
('BUILDING_LIBRARY',		200,	1,		'TECH_WRITING',		NULL,			NULL,		0,		'SPECIALIST_SCRIBE',	1,		0,		'ART_DEF_BUILDING_LIBRARY',	'BW_ATLAS_1',		11	),
('BUILDING_AMPHITHEATER',	200,	1,		'TECH_DRAMA',		NULL,			NULL,		1,		'SPECIALIST_ARTISAN',	1,		0,		'COLESSEUM',			'EXPANSION_BW_ATLAS_1',	0	),
('BUILDING_WORKSHOP',		200,	1,		'TECH_MATHEMATICS',	NULL,			NULL,		0,		'SPECIALIST_SMITH',	1,		0,		'ART_DEF_BUILDING_FORGE',	'BW_ATLAS_1',		28	),
('BUILDING_FORGE',		200,	1,		'TECH_BRONZE_WORKING',	NULL,			NULL,		0,		'SPECIALIST_SMITH',	1,		0,		'ART_DEF_BUILDING_FORGE',	'BW_ATLAS_1',		2	),
('BUILDING_SHRINE',		200,	1,		NULL,			'POLICY_PANTHEISM',	'Religious',	0,		'SPECIALIST_DISCIPLE',	1,		1,		'TEMPLE',			'EXPANSION_BW_ATLAS_1',	9	),
('BUILDING_MAGE_SCHOOL',	200,	1,		'TECH_THAUMATURGY',	NULL,			'Arcane',	0,		'SPECIALIST_ADEPT',	1,		1,		'TEMPLE',			'EXPANSION_BW_ATLAS_1',	9	),
('BUILDING_PHARMAKEIA',		200,	1,		'TECH_MALEFICIUM',	NULL,			'Arcane',	0,		'SPECIALIST_ADEPT',	1,		1,		'TEMPLE',			'EXPANSION_BW_ATLAS_1',	9	);

--Building specific stuff
UPDATE Buildings SET BuildingProductionModifier = 10 WHERE Type = 'BUILDING_WORKSHOP';

--fixinator
CREATE TABLE IDRemapper (id INTEGER PRIMARY KEY AUTOINCREMENT, Type TEXT);
INSERT INTO IDRemapper (Type) SELECT Type FROM Buildings ORDER BY ID;
UPDATE Buildings SET ID = (SELECT IDRemapper.id-1 FROM IDRemapper WHERE Buildings.Type = IDRemapper.Type);
DROP TABLE IDRemapper;
Hopefully the columns are nicely lined up for you (maybe not depending on your browser). Basically, you just tab to line up columns in ModBuddy. This makes it much easier to see the "big picture" and to make edits. Group items in "insert blocks" that make sense (for example, specialist buildings) so you only use the fields needed for that set of items. Specific-case tags can be more easily dealt with as UPDATEs after all your INSERTs, as I did above for workshop. The "fixinator" at the end is the part that renumbers the IDs.

You might be wondering, Where is Description? Where is Help? Where is Civilopedia? Where is BuildingClass? I got tired of adding those for every building (and making mistakes doing so). And they clutter the table. So I don't add them anymore. I let the computer do it for me. In fact, I don't even make entries into the BuildingClasses table anymore. It's stupid and only causes errors. (See how to skip this "dumb data entry" stuff in the Don't Be an Ox section below.)

I don't know if there is a limit to how many rows you can add with one INSERT statement. If you look in my Technologies.sql, you will see >100 techs added by one INSERT. [Edit: Well, the applicable limit is probably "Maximum Length Of An SQL Statement" which can be found here. It's 1000000 bytes unless Firaxis changed the default setting.]

What can and can't be deleted or renumbered safely?

You can delete a lot of the core stuff in Civ5. My mod deletes all contents from many tables including Civilizations, Technologies, Policies, Units and others. But it can be painful figuring out cases where you can't do this. The reason you can't in some cases is that the dll expects a certain item to be in the game, or has its own idea of what IDs matches what item (hardcoded Enums). Check this reference to see where this is a problem: Hard-coded table IDs and Type strings in dll.

There are also some funky things you just have to know. Like, the game won't work if I have < 7 civilizations (although the Civil War scenario does it somehow) or if I give them all the same player color. This is just another reason why you should test frequently when making changes. One of the hardest things in making a total conversion mod is getting something that functions again after that initial "strip out" of core content (especially Civilizations for various reason), but it can be done.

Adding columns to existing tables and adding entirely new tables

You can do this without fear of anything going wrong. In fact, nothing happens at all unless you add Lua, C++ or SQL code to look at the new columns or tables and do something with it. You can see two examples of added columns in my SQL above: look for the ALTER TABLE lines in blue. These two tags, EaPrereqPolicy and EaSpecial, are implemented via Lua. The dll has no awareness of their existence. For many examples of creating new tables with SQL, look in my mod folder here. There are more than a dozen new tables created there, some with >100 columns, and none of them involve any dll modding. Most are implemented by Lua. Some are used to fill out other tables or subtables using SQL (that's a bit "advanced" so I don't know if I will cover it here).

Here's some tutorials to make something happen with your new columns or tables. The first involves Lua only. The other three involve dll modding:

Adding xml tags that work through Lua
Adding new XML Tags/Tables to work through the DLL

whoward69's DLL/C++ tutorials:
Adding new columns to the database
Adding new tables to the database

Learn how to look at the DB contents outside of Civ5!

You need SQLite Manager (an add-on to Firefox) or SQLiteSpy (a stand-alone application) or one of many other programs (see Nutty's post below) that will allow you to look at the DB contents. This is important regardless of whether you modify it using SQL or XML. It'll take you a lot less time to figure out what is in the DB by looking at it rather than trying to deduce it in-game or from the logs. The DBs are in the cache folder (same place as the MODS folder). You want the one that has "Debug" in its name ("Core" is the DB before it has been modified by any mods or DLC including expansions).

Catch basic errors in 1 second rather than 1 minute

Probably 50% of my modding errors are table mistakes when adding new content. Unfortunately, when you make these table errors (in SQL or XML) Civ5 doesn't tell you. It just stops loading the file. Sure, you will figure this out in your testing, probably in the first few minutes. But that's 1000s of minutes maybe you don't want to spend. Here's a solution that will tell you immediately when an SQL or XML file doesn't load to the end. Well, not immediately, you still have to start Civ5 and load your mod.

To catch these errors without even loading Civ5, use SQLite Manager (mentioned above) or similar application. You can open the DB and run blocks of SQL code to see if they are free of errors. I use it to test out complicated SELECT statements so I can see immediately what they will produce.

Where to use XML instead

Use XML wherever it's easier for you. One place you may need it is texts. International characters like "É" don't work in SQL files. Since I do a lot of that stuff, I add all of my mod texts via XML. Several of the sections above apply equally whether you happen to add data via SQL or XML (e.g., they both crash silently when there is an error, so you want some mechanism to detect when they don't read to end-of-file).

Don't Be an Ox, Let SQL Do the Dumb Work

The remainder of the tutorial covers a number of areas where you can reduce or eliminate dumb work, which will save you both effort and errors.

-Part 1: Text keys

There's no reason for lines and lines of this sort of thing:
'TXT_KEY_BUILDING_MONUMENT', 'TXT_KEY_BUILDING_MONUMENT_HELP', 'TXT_KEY_BUILDING_MONUMENT_PEDIA', 'TXT_KEY_BUILDING_MONUMENT_STRATEGY'
...and so on for every darned building.

It's hard to see the real data around all those text key caps. Instead, take advantage of the concatenate operator || to build those text key entries from the Type field. So, skip all the text key fields when you add items to your table. After you've finished inserting everything:
Code:
UPDATE Buildings SET Description = 'TXT_KEY_EA_' || Type;
UPDATE Buildings SET Help = Description || '_HELP', Civilopedia = Description || '_PEDIA', Strategy = Description || '_STRATEGY';
Done. No need to enter all those text key fields. The first line builds the Description text key from Type (for all items). The next line builds all those other text keys by concatenating on the end of Description. The only price you have to pay is that you have to be consistent in your text key constructions. (Firaxis isn't, but that's no reason you shouldn't be.)

You might notice that I add a little mod identifier in my text keys (all start with "TXT_KEY_EA_..."). I do this because I'm adding so much stuff that it's easier for me to add text for all mod items rather than look up or try to remember which ones already exist in base Civ5. Adding a redundant TXT_KEY_ causes a table error. So, for example, my mod has BUILDING_PUBLIC_BATHS, and I have no memory of whether that building exists in base or some scenario. By using TXT_KEY_EA_BUILDING_PUBLIC_BATHS I don't have to worry about it.

-Part 2: Other fields that can be derived

Similar to Part 1, there are other fields in some tables that can be derived. Every one of these that is automated is one less chance for error. Here's an example:
Code:
UPDATE Units SET Class = REPLACE(Type, 'UNIT_', 'UNITCLASS_');
Hopefully that's clear enough. It takes the Type field (let's say UNIT_WARRIOR) and replaces UNIT_ with UNITCLASS_ and puts the result (UNITCLASS_WARRIOR) in the Class field. For buildings, however, not all have a one-to-one relationship with buildingclass (although most do). So I implement the rule first and then update the exceptions:
Code:
UPDATE Buildings SET BuildingClass = REPLACE(Type, 'BUILDING_', 'BUILDINGCLASS_');
UPDATE Buildings SET BuildingClass = 'BUILDINGCLASS_CASTLE' WHERE Type = 'BUILDING_STRONGHOLD';
These two are fairly general so should apply to almost any total conversion mod. But there may be other analogous situations depending on your mod.

-Part 3: Be done with UnitClasses and BuildingClasses table mistakes, forever!

For cripes sake! There is no reason for putting up with CTDs every time you add a Unit and forget UnitClass, or slightly misspell in one or the other, or do the same for Buildings and Buildingclasses. You will have 100s of units and buildings in your total conversion mod, and these may be the two things that you will be adding long after techs, policies and everything else has settled down. Here's my UnitClasses SQL:

Code:
DELETE FROM UnitClasses;
INSERT INTO UnitClasses (Type, Description, DefaultUnit) SELECT Class, Description, Type FROM Units;
--omitted ID fixinator code (same as above)
Yep. That's it. My entire UnitClasses table added in one line. No more unitclass errors. Ever.

The above works because I always have a one-to-one relationship between Unit and UnitClass. I'll show below (for Buildings and BuildingClasses) how to handle it if you don't have one-to-one relationship. But first, make sure you understand the syntax above. It should be pretty clear: just take these three items from Units (Class, Description, Type) and add them in the UnitClasses table as (Type, Description, DefaultUnit) in that exact order. There is no WHERE clause here, so it will do this for every row in the Units table.

For Buildings and BuildingClasses my mod does not have a one-to-one relationship. Here I achieved the same automation by resorting to more advanced SQL (adding a GROUP BY clause):
Code:
DELETE FROM BuildingClasses;
INSERT INTO BuildingClasses (Type, DefaultBuilding, Description) SELECT BuildingClass, Type, Description FROM Buildings GROUP BY BuildingClass ORDER BY ID;
--omitted ID fixinator again
The GROUP BY part makes sure that I generate only one entry in the BuildingClasses table for each BuildingClass, even if it appears >1 time in the Buildings table. The DefaultBuilding and Description fields in the BuildingClasses table will match the last Building belonging to that class in my Buildings table.

Note that there are some more fields in BuildingClasses to deal with (MaxPlayerInstances and MaxGlobalInstances for national and world wonders) but they only apply to a small subset of your buildings. Add these as separate updates:
Code:
UPDATE BuildingClasses SET MaxPlayerInstances = 1 WHERE Type = 'BUILDINGCLASS_PALACE';
UPDATE BuildingClasses SET MaxGlobalInstances = 1 WHERE Type IN ('BUILDINGCLASS_KOLOSSOS', 'BUILDINGCLASS_MEGALOS_FAROS', 'BUILDINGCLASS_HANGING_GARDENS', 'BUILDINGCLASS_UUC_YABNAL', 'BUILDINGCLASS_THE_LONG_WALL', 'BUILDINGCLASS_CLOG_MOR', 'BUILDINGCLASS_DA_BAOEN_SI');
Note the use of "=" versus "IN()" for matching one item or a list of items.

-Part 4: Auto-generation of dumb subtable rows (e.g., Unit_AITypes)

I always used to have mistakes in Unit_AITypes. There are mistakes in the base game table. I have an irrational hatred of entering stuff here each time I add a unit. No, it's actually very rational. Let's say I'm adding a unit late at night after a couple beers and I make a mistake. Will I know? No. Not until a mod user tells me 2 months later! that AI players aren't using their catapults.

So let's start from scratch:
Code:
DELETE FROM Unit_AITypes;

Now let's solve that catapult problem for ever, and knock out fire throwing catapults and bombards and so forth. All of these seige units have UNITAI_CITY_BOMBARD and UNITAI_RANGED. So:
Code:
INSERT INTO Unit_AITypes (UnitType, UnitAIType)
SELECT Type, 'UNITAI_CITY_BOMBARD' FROM Units WHERE CombatClass = 'UNITCOMBAT_SIEGE' UNION ALL
SELECT Type, 'UNITAI_RANGED' FROM Units WHERE CombatClass = 'UNITCOMBAT_SIEGE';
The first line is the standard INSERT telling SQL what columns you will be passing to it (notice no VALUES here - we use SELECT instead because we can do more with it). The 2nd line finds all units with UNITCOMBAT_SIEGE from the Units table. For each one, it generates a new row in Unit_AITypes filling in the two columns (UnitType, UnitAIType) with Type (from Units table of course) and the constant string value 'UNITAI_CITY_BOMBARD'. The UNION ALL is just stacking up rows from the 2nd line with the 3rd. The 3rd line is just like the 2nd, except it generates rows with UNITAI_RANGED instead of UNITCOMBAT_SIEGE. And remember the semicolon at the end of any SQL statement (this is all one statement).

OK, what about UNITCOMBAT_MELEE. They seem to all have UNITAI_ATTACK and UNITAI_DEFENSE, except that warrior (only, I think) also has UNITAI_EXPLORE. I guess they want only that initial melee unit doing exploration. You could do this different ways. I do it here in a way that works for my mod given that I have >1 "warrior" units:

Code:
SELECT Type, 'UNITAI_ATTACK' FROM Units WHERE CombatClass = 'UNITCOMBAT_MELEE' UNION ALL
SELECT Type, 'UNITAI_DEFENSE' FROM Units WHERE CombatClass = 'UNITCOMBAT_MELEE' UNION ALL
SELECT Type, 'UNITAI_EXPLORE' FROM Units WHERE CombatClass = 'UNITCOMBAT_MELEE' AND Combat < 7 UNION ALL
This is not a statement! It's just 3 lines copied out of the middle of my longish statement. Remember to always end with ; (no UNION ALL in the last line).

Firaxis annoyingly lumped archers and fast mounted archers together in the same CombatClass. Anyway, the slow archers use UNITAI_RANGED only and the fast ones use that plus UNITAI_FAST_ATTACK. So:

Code:
SELECT Type, 'UNITAI_RANGED' FROM Units WHERE CombatClass = 'UNITCOMBAT_ARCHER' UNION ALL
SELECT Type, 'UNITAI_RANGED' FROM Units WHERE CombatClass = 'UNITAI_FAST_ATTACK' AND Moves > 2 UNION ALL

Here's a couple lines specific for my mod's unit line-up:
Code:
SELECT Type, 'UNITAI_EXPLORE' FROM Units WHERE CombatClass = 'UNITCOMBAT_RECON' UNION ALL
SELECT Type, 'UNITAI_ATTACK' FROM Units WHERE CombatClass = 'UNITCOMBAT_RECON' AND Combat > 8 UNION ALL		--Trackers, Rangers can attack
SELECT Type, 'UNITAI_DEFENSE' FROM Units WHERE CombatClass = 'UNITCOMBAT_RECON' AND Combat > 8 UNION ALL	--Trackers, Rangers can defend
The first line takes care of scouts (by whatever name). But I also have some recon upgrades with some more substantial combat strength. So I gave them attack and defense AI in the next 2 lines.

What about all the oddball ones like settlers and so forth?:
Code:
SELECT Type, DefaultUnitAI FROM Units WHERE DefaultUnitAI IN ('UNITAI_SETTLE', 'UNITAI_WORKER', 'UNITAI_WORKER_SEA') UNION ALL
Just like Type, the interpreter will get DefaultUnitAI from Units (that's what FROM Units is telling it to do). Then I just throw in a list of DefaultUnitAI that may need to be added. Actually, I don't know if these need to be added to this table or not because Firaxis is inconsistent about it. But no harm so I added them.

I'm not copying my actual table here because it has a lot of mod-specific stuff that would just be confusing. So you have to complete it yourself. Just make sure you understand the syntax! Each line is making multiple rows in the table and UNION ALL is used to stack those rows on top of each other. You need the INSERT statement at the beginning and a semicolon (not UNION ALL!) at the end of it all.

A good way to learn this is by using these statements in SQLite Manager. If you input an actual statement, then it will do it (i.e., modify the table, or throw an error). But the other neat trick is that you can input just the SELECT line (or lines). It will show you the rows generated without actually doing anything. So you can fiddle around until you get what you want. That's how I figured all this out.





.
 
@Pazyryk: I'm trying to take your advice for Warhammer, but am running into this sort of thing a lot:
Code:
INSERT INTO WhateverItIs(ThingyA, ThingyB, ThingyC)
    VALUES ('XXX', 'YYY', 'ZZZ')
INSERT INTO WhateverItIs(ThingyA, ThingyB, ThingyC)
    VALUES ('TTT', 'UUU', 'VVV')
This, when I have about 300 different units, is annoying.
Will the following code(s) work just fine, or do I need to do something else?
Code:
INSERT INTO WhateverItIs(ThingyA, ThingyB, ThingyC)
    VALUES ('XXX', 'YYY', 'ZZZ')
    VALUES ('TTT', 'UUU', 'VVV')
or maybe this:
Code:
INSERT INTO WhateverItIs(ThingyA, ThingyB, ThingyC)
    VALUES ('XXX', 'YYY', 'ZZZ')
           ('TTT', 'UUU', 'VVV')
Thanks!
 
This tutorial looks awesome! I know enough about SQL to be dangerous, meaning that I played around with databases a little bit with ASP and Access but nobody would let me sit at the table with the big boys :( I can't wait to get started, using XML is such a pain!
I will let you know when I can proudly show off my first creation :)
 
@Civitar

Code:
INSERT INTO Yields(Type, Description, IconString) VALUES
  ('YIELD_LOCAL_HAPPINESS', 'TXT_KEY_YIELD_LOCAL_HAPPINESS', '[ICON_HAPPINESS]'),
  ('YIELD_GLOBAL_HAPPINESS', 'TXT_KEY_YIELD_GLOBAL_HAPPINESS', '[ICON_HAPPINESS]')
;
 
@ Pazyryk or whoward69: I've completed my basic table of Warhammer Buildings:
Code:
DELETE FROM BuildingClasses;
INSERT INTO BuildingClasses(Type,										DefaultBuilding) VALUES
			('BUILDINGCLASS_WHFB_MONUMENT',				'BUILDING_WHFB_MONUMENT'),
						   ('BUILDINGCLASS_WHFB_SHRINE',				'BUILDING_WHFB_SHRINE'),
						   ('BUILDINGCLASS_WHFB_TEMPLE',				'BUILDING_WHFB_TEMPLE'),
						   ('BUILDINGCLASS_WHFB_MILL',					'BUILDING_WHFB_MILL'),
						   ('BUILDINGCLASS_WHFB_BREWERY',				'BUILDING_WHFB_BREWERY'),
						   ('BUILDINGCLASS_WHFB_STOREHOUSE',			'BUILDING_WHFB_STOREHOUSE'),
						   ('BUILDINGCLASS_WHFB_MARKET',				'BUILDING_WHFB_MARKET'),
						   ('BUILDINGCLASS_WHFB_TAVERN',				'BUILDING_WHFB_TAVERN'),
						   ('BUILDINGCLASS_WHFB_INN',					'BUILDING_WHFB_INN'),
						   ('BUILDINGCLASS_WHFB_TRAINING_GROUND',		'BUILDING_WHFB_TRAINING_GROUND'),
						   ('BUILDINGCLASS_WHFB_BARRACKS',				'BUILDING_WHFB_BARRACKS'),
						   ('BUILDINGCLASS_WHFB_BESTIARY',				'BUILDING_WHFB_BESTIARY'),
						   ('BUILDINGCLASS_WHFB_CASTLE',				'BUILDING_WHFB_CASTLE'),
						   ('BUILDINGCLASS_WHFB_PALISADE',				'BUILDING_WHFB_PALISADE'),
						   ('BUILDINGCLASS_WHFB_WALLS',					'BUILDING_WHFB_WALLS'),
						   ('BUILDINGCLASS_WHFB_BATTLEMENTS',			'BUILDING_WHFB_BATTLEMENTS'),
						   ('BUILDINGCLASS_WHFB_BULWARKS',				'BUILDING_WHFB_BULWARKS'),
						   ('BUILDINGCLASS_WHFB_TOWER',					'BUILDING_WHFB_TOWER'),
						   ('BUILDINGCLASS_WHFB_PERMANENT_GARRISON',	'BUILDING_WHFB_PERMANENT_GARRISON'),
						   ('BUILDINGCLASS_WHFB_SCHOOL_OF_MAGIC',		'BUILDING_WHFB_SCHOOL_OF_MAGIC'),
						   ('BUILDINGCLASS_WHFB_LODESTONE',				'BUILDING_WHFB_LODESTONE'),
						   ('BUILDINGCLASS_WHFB_HARBOR',				'BUILDING_WHFB_HARBOR'),
						   ('BUILDINGCLASS_WHFB_LIGHTHOUSE',			'BUILDING_WHFB_LIGHTHOUSE'),
						   ('BUILDINGCLASS_WHFB_SEA_GATE',				'BUILDING_WHFB_SEA_GATE'),
						   ('BUILDINGCLASS_WHFB_WORKSHOP',				'BUILDING_WHFB_WORKSHOP'),
						   ('BUILDINGCLASS_WHFB_FORGE',					'BUILDING_WHFB_FORGE'),
						   ('BUILDINGCLASS_WHFB_ARMORY',				'BUILDING_WHFB_ARMORY'),
						   ('BUILDINGCLASS_WHFB_BUTCHER',				'BUILDING_WHFB_BUTCHER'),
						   ('BUILDINGCLASS_WHFB_SEWERS',				'BUILDING_WHFB_SEWERS'),
						   ('BUILDINGCLASS_WHFB_CIRCUS',				'BUILDING_WHFB_CIRCUS'),
						   ('BUILDINGCLASS_WHFB_CEMETERY',				'BUILDING_WHFB_CEMETERY'),
						   ('BUILDINGCLASS_WHFB_LIBRARY',				'BUILDING_WHFB_LIBRARY'),
						   ('BUILDINGCLASS_WHFB_SPYMASTER',				'BUILDING_WHFB_SPYMASTER');
UPDATE BuildingClasses SET Description = 'TXT_KEY_' || DefaultBuilding;
Sorry if it doesn't line up nicely, but it's a pretty simple table. It's supposed to delete all base game BuildingClasses, insert 33 new ones with Type and DefaultBuilding, then define the Description text key with the last bit at the end.
Will it work properly, or are there any mistakes you can see? Thanks!
 
Will it work properly, or are there any mistakes you can see? Thanks!
You don't need to ask. Just use SQLite Manager for Firefox or SQLiteSpy [that's the one that whoward69 uses, Paz]

[...or SQLite Database Browser or SQLite Studio or Sqliteman or SQLiteManager or SQLite Administrator...],

...then open the %userprofile%\Documents\My Games\Sid Meier's Civilization 5\cache\Civ5DebugDatabase.db file, paste your SQL into the appropriate box (the "Execute SQL" tab in SQLite Manager), and see if an error pops up; if not, take a look at the database and see if the changes were made appropriately.

Note you'll actually be making changes to the database, but when you launch CiV, it will re-populate the database. So if you're going to be trying a few things, you might want to keep a backup copy of the db file so you don't have to launch CiV to get it to reset.

EDIT: Note this is mentioned in the "Learn how to look at the DB contents outside of Civ5!" in the original post.
 
Probably a dumb question, but how do I put in an empty row entry? Basically I would like to know how to do this:
Code:
<Civilization_UnitClassOverrides>
	<Row>
		<CivilizationType>CIVILIZATION_BARBARIAN</CivilizationType>
		<UnitClassType>UNITCLASS_TANK</UnitClassType>
		[B]<UnitType/>[/B]
	</Row>
</Civilization_UnitClassOverrides>
...in SQL.
 
Code:
INSERT INTO Civilization_UnitClassOverrides(CivilizationType, UnitClassType, UnitType)
  VALUES('CIVILIZATION_BARBARIAN', 'UNITCLASS_TANK', NULL);

or you can just omit the column and let the database use the default value (which in this case is NULL)

Code:
INSERT INTO Civilization_UnitClassOverrides(CivilizationType, UnitClassType)
  VALUES('CIVILIZATION_BARBARIAN', 'UNITCLASS_TANK');
 
this picked my attention ;]

Where to use XML instead

Use XML wherever it's easier for you. One place you may need it is texts. International characters like "É" don't work in SQL files. Since I do a lot of that stuff, I add all of my mod texts via XML. Several of the sections above apply equally whether you happen to add data via SQL or XML (e.g., they both crash silently when there is an error, so you want some mechanism to detect when they don't read to end-of-file).

Well, actually you can add all international characters via SQL to DB without problems. The issue is how ModBuddy codes text files by default. But there's a workaround.

SQLite database for the game has default encoding set to UTF-8, so there should not be any problems here. ModBuddy however by default may create such text files with different encoding like ANSI for instance. And then all those information related to national chars are lost during the save.

Fix:
If you create empty SQL file in ModBuddy, make sure you have it open and selected in Solution Explorer, then from menu:

File -> Save <your file name> As... -> press that part of the save button with small black arrow -> Save with Encoding... -> agree to replace the file -> and for Encoding choose: Unicode (UTF-8 with signature) - Codepage 65001 -> press OK

You may want to confirm if indeed the file has been changed to UTF-8 encoding. Open it in Notepadd++, from top menu choose Encoding - black dot should be near Encode in UTF-8 option.

Well, until your file stays in UTF-8 format, you can add all national characters and it should be saved properly by ModBuddy from now on...
 
Is not this bit an error?
Code:
SELECT Type, 'UNITAI_RANGED' FROM Units WHERE CombatClass = 'UNITCOMBAT_ARCHER' UNION ALL
SELECT Type, '[COLOR="Red"]UNITAI_RANGED[/COLOR]' FROM Units WHERE CombatClass = '[COLOR="red"]UNITAI_FAST_ATTACK[/COLOR]' AND Moves > 2 UNION ALL
Since there is no such thing as a UNITAI_FAST_ATTACK in the definitions of UnitCombatClass.
Should it not be?
Code:
SELECT Type, 'UNITAI_RANGED' FROM Units WHERE CombatClass = 'UNITCOMBAT_ARCHER' UNION ALL
SELECT Type, '[COLOR="Blue"]UNITAI_FAST_ATTACK[/COLOR]' FROM Units WHERE CombatClass = '[COLOR="blue"]UNITCOMBAT_ARCHER[/COLOR]' AND Moves > 2 UNION ALL
 
Top Bottom