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:
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:
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:
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:
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:
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):
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:
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:
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:
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:
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:
Here's a couple lines specific for my mod's unit line-up:
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?:
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.
.
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:
- Delete everything
- Add my own mod content in one or more insert and update statements
- 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)
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;
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';
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_');
Code:
UPDATE Buildings SET BuildingClass = REPLACE(Type, 'BUILDING_', 'BUILDINGCLASS_');
UPDATE Buildings SET BuildingClass = 'BUILDINGCLASS_CASTLE' WHERE Type = 'BUILDING_STRONGHOLD';
-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)
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
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');
-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';
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
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
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
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.
.