How to delete stuff

Pazyryk · Jun 4, 2012

Contrary to the title of the post, this is really a tutorial about how to deal with the potential ill effects of deleting. I'm assuming a basic knowledge of XML and SQL sufficient to actually delete an item.

Spatzimaus said:
Deleting things is BAD.

I've seen this statement in various forms from some of the best modders here. The same modders then identify the specific issues related to deleting (e.g., as Spatz does in the post I'm quoting). I'll go one step further here and tell you how to address these issues.

Further, I'm a contrarian so I'll say that (depending on your mod) deleting things is Good. If it's a total conversion mod, do it. If you are removing a whole era, I'd say do it but others will disagree. If you just want to remove one thing (e.g., Giant Death Robot) it is much easier to disable (e.g., make cost = -1) rather than delete.

Note that everything I say below applies to deleting items from the "main" tables -- i.e., anything that has a Type and ID. You can delete rows from "subtables" without worrying about this stuff at all. Also, note that the trend in patches has been to make the game more and more robust to these issues (especially #2 below). But this is probably a curse rather than a cure: an outright crash is much preferable than an insidious and inconsistent error.

1. Find references to deleted items in both XML and Lua (then remove or decide that deletion is not a good idea after all)

Depending on the table, you may have to have some sophistication in Lua programming and/or Civ5 UI to be able to do this. The first step is to identify all occurrences of your deleted item in base game XML or Lua files. Your friend here is a good application for searching text in files. I use Windows Grep (whoward69 has another that he recommends frequently in his posts). There is a more sophisticated DB-ish way to do this but you don't need that. These cross-references are mostly in Gameplay and UI folders but they can be elsewhere (e.g., DLC Babylon Lua references TECH_WRITING). You may want to do these searches first before you decide whether deletion is the right way to go: some things are used a lot in base Lua (e.g., ResourceTypes and TerrainTypes in AssignStartingPlots.lua) or UI (e.g., YieldTypes, but I doubt you are going to want to delete any of those), others never.

Where you find these in XML (non-UI), just delete that row (if it is a "subtable") or the column entry (if it is a main table item that you don't want to delete). If it is in Lua, you are going to have to do some Lua re-coding, which you will have to figure out yourself. If it is in a particular UI XML/Lua system, then you will have to do some UI re-coding (reading whoward69's UI tutorial is a good start).

2. Re-sequence table IDs

When you delete an item, if you do nothing else you will have "gaps" in a table where the IDs are now noncontinuous. Depending on the particular table, noncontinuous IDs will: 1) crash the game outright, 2) cause nearly silent nonsensical errors that are hard to track, or 3) have no effect. Rather than try to figure out which table fits in which category, it is easiest to re-sequence IDs for any table for which you may have introduced noncontinuous IDs (i.e., you deleted something). The full SQL code for this was posted by lemmy101 here. The basic code is the same for all tables except Technologies. For all but technologies, it looks like this (just replace "Buildings" with your table name):

Code:

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;

The only difference for Technologies is that it has to be ordered by its GridX position (I think this is needed for proper pipe drawing):

Code:

CREATE TABLE IDRemapper ( id INTEGER PRIMARY KEY AUTOINCREMENT, Type TEXT );
INSERT INTO IDRemapper (Type) SELECT Type FROM Technologies ORDER BY GridX ASC;
UPDATE Technologies SET ID =	( SELECT IDRemapper.id-1 FROM IDRemapper WHERE Technologies.Type = IDRemapper.Type);
DROP TABLE IDRemapper;

Obviously, the above code has to run after you have deleted/added items. lemmy101 originally wrote one giant SQL file that does this for all tables in the game. I prefer to add the code above only for the specific tables where I have deleted things.

[Edit 8/24/2012] If there is any possibility of additional items being added to the same table later, for example by another mod, then you need to add the following code after the block above (code from Thalassicus; example given is for UnitPromotions). This will ensure that additions to the table start with the next available ID (seq is the "counter" for this and it got screwed up with your deletions/additions/reIDing above).

Code:

UPDATE sqlite_sequence
SET seq = (SELECT COUNT(ID) FROM UnitPromotions)-1
WHERE name = 'UnitPromotions';

3. Check/fix references to intermediate tables

In unmodded Civ5, these three lines evaluate to the same thing:

GameInfo.Improvements.IMPROVEMENT_FARM.ID
GameInfoTypes.IMPROVEMENT_FARM
ImprovementTypes.IMPROVEMENT_FARM

However, if you change IDs for the Improvement table, then the last line will not update to reflect your mod's changes and may now give an incorrect value. The 1st and 2nd lines will always correctly reflect your mod's Impvovements table. These "____Types" are a set of "intermediate tables" that you can find in the wiki under "Lua Enums" (although the list is incomplete and not all shown are used by the game). One of these that's used extensively in base game UI is YieldTypes. But I doubt you are deleting yield types so that shouldn't matter. AssignStartingPlots.lua uses these intermediate tables for resources and terrain types. Other than these, I believe that the base Civ5 Lua almost always uses the direct table reference (1st line above) rather than the intermediate table refence (2nd line above). Use your text search program (search "Types.") to find any references to these for tables that you are deleting from, and change them to the 2nd longer "direct table reference" shown above. I've avoided this problem in my own Lua by always writing out the direct table references and never using intermediate tables (well, except YieldTypes...).

Caveat/disclaimer
The one potential problem with this is for scenarios. These add units and other items by ID so things get messy if you reorder these IDs after the scenario loads (see discussion here). Obviously it's a bad idea to load "base game" scenarios in a mod that has deleted base elements. I believe that scenarios created from your mod will not have this problem. But I don't really know since I haven't tried this yet.

Feel free to post questions or other issues that I may have missed.

Spatzimaus · Jun 4, 2012

Pazyryk said:
If it's a total conversion mod, do it (its actually easier the more you delete).

This is really the only case where I'd agree that deletion is clearly the best course, and only on certain tables. (Mainly Units, Buildings, Technologies, and so on.) Unless you're wiping a table clean and building an entirely new table up from scratch, you're often better off finding some way to disable or hide the entity instead. Most tables include some way to hide/disable; sometimes it's an explicit flag (<Disable> on technologies), sometimes it's done by setting a certain value (usually Cost) to -1, and sometimes it's much more complex than that.

These add units and other items by ID so things get messy if you reorder these IDs after the scenario loads

There are several other situations with similar issues. For instance, if you delete a resource or terrain type, then certain parts of AssignStartingPlots (the code used to allocate resources on a newly created map) will break outright because they use an internal table that is not modified by your mod. Likewise, if you delete a victory type, then certain parts of the setup screen will break, and adding new victory types doesn't work well because the setup screen will just turn them off (since the PreGame Lua functions can't be used to enable it).

The game actually uses quite a few intermediate tables, which aren't adjusted by mods, for large parts of the game initialization. For instance, your mod can add a new Yield, and GameInfo.Yields will be updated accordingly, but the game's Lua uses the YieldTypes metatable instead, which is NOT adjusted by your mod, and so new yields won't appear in that table and deleting a yield will cause problems in any function that uses all entries in that table. This also means that any Lua function that accesses a yield (like the readouts in the Top Panel) can't be used for your new Yield.

The game also uses these sorts of tables for Terrains, Resources, Improvements, Features, and so on. In most of the UI elements access to these tables are done cleanly, to where a missing entry won't crash it, but that's not always true.

Pazyryk · Jun 4, 2012

Yeah, the "intermediate tables" are a potential problem. Surprisingly, I have not run into this problem at all with my mod. I think that the only one used extensively by base game Lua is YieldTypes (I'll check this when I have access to the game). Otherwise, it's easy to avoid this problem in your own mod Lua by always referencing the table rather than the intermediate table. For example, use GameInfo.Improvements.IMPROVEMENT_FARM.ID rather than ImprovementTypes.IMPROVEMENT_FARM.

I'll add this to the OP.

Spatzimaus · Jun 4, 2012

Pazyryk said:
Yeah, the "intermediate tables" are a potential problem. Surprisingly, I have not run into this problem at all with my mod.

You'll have problems with the core game's Lua if you add or subtract Victory types, Policy Branches, Eras, Yields, Resources, Terrains, Features, and sometimes Improvements. Some of these will be minor issues (Improvements), some will cause crashes (Terrains, Resources), some will cause a certain UI window to fail (Policy Branches, Eras), and some will simply ignore whatever you've changed (Victories). In some of these cases, adding a new element is fine but deletion is a problem (Resources), but for most you can't go either way without some major work.

Effectively, nearly any Lua function that uses a type variable for these categories will refuse to work with any modifications you make. Yields are by far the most common example of this, as there are a tremendous number of Lua functions designed to manage yields, none of which will work with any new Yield you add. (You would not BELIEVE the amount of work I had to do to get my Mythology mod to manage the new Favor yield.)

But most of the others I listed will have similar issues; while you can use the Game:SetWinner command to award victory for a new victory type you've added, you can't use the Game:IsVictoryValid() command to see if that victory type has been enabled for this game, or the PreGame:SetVictory() function to enable it in the first place, because both of those use the internal VictoryTypes table to parse any arguments. And if you DELETE a Victory, then all sorts of things will go wrong in places like the VictoryProgress screen.

For example, use GameInfo.Improvements.IMPROVEMENT_FARM.ID rather than ImprovementTypes.IMPROVEMENT_FARM.

Sure, if you're talking about your own Lua, it's not difficult to avoid these issues. (And a shorter way to write what you did is just GameInfoTypes.IMPROVEMENT_FARM; GameInfoTypes gives the ID of whatever comes next, no matter what table it was in.) I was referring to issues with the core game's UI elements, like TopPanel, ProductionPopup, CityView, VictoryProgress, SocialPolicyPopup, and so on, all of which I've had to modify in my own mods.

Pazyryk · Jun 4, 2012

Spatzimaus said:
(And a shorter way to write what you did is just GameInfoTypes.IMPROVEMENT_FARM; GameInfoTypes gives the ID of whatever comes next, no matter what table it was in.)

Funny, I've never used this. Is this sensitive to the intermediate table problem or does it update with GameInfo? (I wasn't sure from your post)

Spatzimaus said:
I was referring to issues with the core game's UI elements, like TopPanel, ProductionPopup, CityView, VictoryProgress, SocialPolicyPopup, and so on, all of which I've had to modify in my own mods.

Yes, I can see that adding a YieldType would be horrendous, and I certainly would not recommend deleting one. I think my main point in #1 above (rewritten somewhat) is that it is easy to find these and make the difficulty assessment yourself. That difficulty assessment may be "easy" or "super duper hard" depending on what you find and your own abilities.

Spatzimaus · Jun 4, 2012

Pazyryk said:
Funny, I've never used this. Is this sensitive to the intermediate table problem or does it update with GameInfo? (I wasn't sure from your post)

It's fully tied to GameInfo, and isn't related to any of the intermediate tables, so it'll reflect any changes your mod makes. It's just a shorthand way of getting an ID; if you actually want to use the full structure for something, then you're better off the old way (extracting something like GameInfo.Units["UNIT_WARRIOR"]), and then just adding .ID, .Combat, etc. as needed to get all of the related variables as needed. But if all you want is the ID, and when writing Lua it really IS all you need most of the time, then GameInfoTypes is a big help.

It works for any table that has a unique Type variable that can be used as an array index, which is pretty much all of the major ones (anything with an ID field, basically). I use it constantly, because it's nice being able to write GameInfoTypes.UNIT_MINOTAUR, GameInfoTypes.BUILDING_WALL_STREET, and GameInfoTypes.RESOURCE_NEUTRONIUM and have the game know exactly which table you meant with each one, without having to write out something like GameInfo.Units["UNIT_MINOTAUR"].ID. The only time it's not useful is when you haven't decided which element you want to access beforehand; if I wanted to pass the unit type in as a string argument, then you can do GameInfo.Units[uType].ID and it'll work as long as uType is something that exists in the database, but GameInfoTypes wouldn't work.

Yes, I can see that adding a YieldType would be horrendous, and I certainly would not recommend deleting one.

If I had known how painful it was going to be, I'd never have done it. But having done it, I can say that you're almost better off not even TRYING to call it a Yield and just do all of the necessary bookkeeping through Lua in some sort of persistent data structure instead. (I use MapModData for this.) There are a few Yield-related fields that do work, like the yield change/modifier fields for Buildings or Policies, but pretty much anything beyond that is just not going to work for your new Yield.

The bottom line is, deleting a single unit or building or something isn't too bad, once you get past the obvious dependencies. But the more fundamental stuff, like Yields or Terrains, just can't be deleted nearly as easily because it's inherently tied to too many of the core Lua functions through those intermediate tables I mentioned; even if you were to remove every secondary table that used those values, it'd STILL break in all sorts of places.

muyb · Jul 11, 2012

I've been trying for a week or so now to remove all of the default city states because I'm working on a total conversion which includes it's own. It has been nothing but pain. I can get my own city states added in just fine, but attempting to delete the existing ones either causes a crash, or does nothing(the base game city states still appear in game).

I have tried both a line by line deletion method, and dropping and reconstructing all three city state tables(MinorCivilizations, MinorCivilizations_Flavors and MinorCivilizations_CityNames), can anyone give me some advice as to what I should be doing to replace the stock city states with an entirely new set(I have considered using updates to alter the city states to my new ones, but that would rely on matching the quantity of city states exactly and hoping Firaxis doesn't change the numbers/types in future(like they did when Korea DLC came out.)

PawelS · Jul 12, 2012

I have no problems with deleting City States in my mod. I just delete all content of the tables using the following SQL code:

Code:

DELETE FROM MinorCivilizations;
DELETE FROM MinorCivilization_CityNames;
DELETE FROM MinorCivilization_Flavors;

Then I add new ones, and resequence the IDs for the MinorCivilizations table, as shown in the first post of this thread.

muyb · Jul 12, 2012

Yep, got it working, turns out it was two of my custom city states causing my crash because I had used "grey" instead of "gray" when defining their colours.

PawelS · Nov 2, 2013

I found something that might be useful for total conversion mods. When I need to delete all content of a table with IDs and fill it with new content, I used to do it the following way:

1. Delete all existing content of a table (and secondary tables related to it)
2. Add new content
3. Resequence IDs

Now I found a way that is better for me, because it doesn't need anything done after adding new content, only before doing it:

1. Delete all existing content of a table (and secondary tables related to it), for example:

Code:

DELETE FROM BuildingClasses;
DELETE FROM BuildingClass_VictoryThresholds;

2. Set the count to 0:

Code:

UPDATE sqlite_sequence
SET seq = 0
WHERE name = 'BuildingClasses';

3. Add new content, where the first row has

Code:

<ID>0</ID>

(It's needed because without it it starts counting from 1. Setting the count to -1 doesn't help - it still starts from 1.)

Pazyryk · Nov 4, 2013

PawelS said:
2. Set the count to 0:

Funny, I swear I tried this way back and it didn't work for some reason. Maybe it changed with a patch (the SQLite version certainly changed). Or maybe I did something wrong.

I agree this is much more elegant than renumbering after the fact, and should be faster too.

FramedArchitect · Nov 4, 2013

Since BNW release, Lemmy's auto-incrementer no longer works for me. Have you changed the sql in initial post for use with BNW?

Pazyryk · Nov 5, 2013

FramedArchitect said:
Since BNW release, Lemmy's auto-incrementer no longer works for me. Have you changed the sql in initial post for use with BNW?

Lemmy's code was missing the part in red below:

Code:

INSERT INTO IDRemapper (Type) SELECT Type FROM Buildings [COLOR="Red"]ORDER BY ID[/COLOR];

I believe this ordering was default in prior SQLite versions, but is no longer default in current SQLite (which we got with pre-BNW patch). So, yes, my code above should work.

FramedArchitect · Nov 10, 2013

Pazyryk said:
Lemmy's code was missing the part in red below:

Code:

INSERT INTO IDRemapper (Type) SELECT Type FROM Buildings [COLOR="Red"]ORDER BY ID[/COLOR];

I believe this ordering was default in prior SQLite versions, but is no longer default in current SQLite (which we got with pre-BNW patch). So, yes, my code above should work.

Aha! Thanks, I hadn't seen that ordering function before (nor used it).

isnorden · Jun 1, 2014

I'm working on a BNW-compatible mod which adds several new resources (gems and metals for use with a Goldsmith's shop). Before I put my own data in, however, I plan to remove duplicated/similar resource names --

Spoiler :

Although I've read the Modiki and this tutorial, I still have two questions:

Does Civ5 XML have a simpler way to delete table entries which aren't official game content?
How and where do I use <DeleteMissingReferences> to clean up what the other code leaves behind?

If you're willing and able to help, thank you; I still have a lot to learn.

Pazyryk · Jun 1, 2014

Unfortunately, I can't answer either question. I've sort of forgotten how to use Firaxis' "sql by xml" system. I'm pretty sure you need a "where" in there somewhere to tell it what rows to delete.

IIRC, whoward69 has some posts on DeleteMissingReferences somewhere. I don't really remember what this does.

isnorden · Jun 2, 2014

Found the tutorial in the 2K Games archive...whew! WHoward69: is this syntax correct for DMR'ing the resource list?

Code:

<!-- Close the holes in all relevant tables -->

<DeleteMissingReferences table="Resources" column="Type"/>
<DeleteMissingReferences table="Resource_YieldChanges" column="Type"/>
<DeleteMissingReferences table="Resource_Flavors" column="Type"/>
<DeleteMissingReferences table="Resource_Terrain_Booleans" column="Type"/>
<DeleteMissingReferences table="Improvement_ResourceTypes" column="Type"/>
<DeleteMissingReferences table="Improvement_ResourceType_Yields" column="Type"/>

whoward69 · Jun 2, 2014

isnorden said:

Code:

<DeleteMissingReferences table="Resources" column="Type"/>
<DeleteMissingReferences table="Resource_YieldChanges" column="Type"/>
<DeleteMissingReferences table="Resource_Flavors" column="Type"/>
<DeleteMissingReferences table="Resource_Terrain_Booleans" column="Type"/>
<DeleteMissingReferences table="Improvement_ResourceTypes" column="Type"/>
<DeleteMissingReferences table="Improvement_ResourceType_Yields" column="Type"/>

Is this syntax correct for DMR'ing the resource list?

No.

Only the first line is needed. The hint is that none of the other tables have a Type column. Instead they have

Code:

<Column name="ResourceType" type="text" reference="Resources(Type)"/>

columns and this is the crux of how <DeleteMissingReferences> works.

When you add

Code:

<DeleteMissingReferences table="Xyz" column="Abc"/>

the XML parser effectively searches the database for ALL tables that have a column with a "reference="Xyz(Abc)" entry and then deletes all rows in those tables that no longer have a corresponding entry in the Xyz table. (Most database implementations would do this the other way around with a "cascade delete" clause.)

This won't entirely clean up the database as

some cross-reference columns don't have the "reference=" attribute (and in the latest patch Firaxis broke one - the one that gets reported about policies in the logs)
it's dangerous to try and clean up the text table this way (as it takes forever and also doesn't play nicely with I18N data)
some text will still refer by name to the thing you've just deleted

For example, you can delete the X-COM squad and all civ specific versions with

Code:

<UnitClasses>
  <Delete Type="UNITCLASS_XCOM_SQUAD"/>
</UnitClasses>

<DeleteMissingReferences table="UnitClasses" column="Type"/>
<DeleteMissingReferences table="Units" column="Type"/>

First we delete the UnitClass, then we use <DeleteMissingReferences table="UnitClasses" column="Type"/> to delete everything that refences that unit class (this will be the actual XCOM Squad in the Units table and also any civ specific variants)

The second DeleteMissingReferences tag then deletes everything that references the units that just got deleted - flavours, free promotions, resource requirements, upgrades, etc, etc, etc

If you want to tidy up the text tables, you will also need

Code:

<Language_en_US>
  <Delete Tag="TXT_KEY_UNIT_XCOM_SQUAD"/>
  <Delete Tag="TXT_KEY_UNIT_XCOM_SQUAD_STRATEGY"/>
  <Delete Tag="TXT_KEY_UNIT_HELP_XCOM_SQUAD"/>
  <Delete Tag="TXT_KEY_CIV5_XCOM_SQUAD_TEXT"/>

  <Update>
    <Where Tag="TXT_KEY_TECH_NANOTECHNOLOGY_HELP"/>
    <Set Text="Allows you to build the [COLOR_POSITIVE_TEXT]SS Stasis Chamber[ENDCOLOR], one of the Spaceship parts necessary to win a [COLOR_POSITIVE_TEXT]Science Victory[ENDCOLOR]."/>
  </Update>
</Language_en_US>

isnorden · Jun 2, 2014

@WHoward69: Thank you so much for keeping several lines of bad code out of the mods that I'm updating now. I do have one last question, though -- do any tables other than "BuildingClasses" need to be cleaned up after deleting a building from the list? There are so many tables for building-related stats that I get confused easily.

whoward69 · Jun 2, 2014

isnorden said:
do any tables other than "BuildingClasses" need to be cleaned up after deleting a building from the list? There are so many tables for building-related stats that I get confused easily.

If you delete a building and then use <DeleteMissingReferences table="Buildings" column="Type"/> it would have NO effect on the BuildingClasses table.

Why would it? If I have a default building and a civ specific variant of that building and delete the civ specific building I don't want the building class to be deleted as that would then break the default building.

If you really want to delete all buildings that share the same building class, the approach would be the same as for the XCOM example ... delete the specific building class, then use two DeleteMissingReferences tags - the first for the BuildingClasses table and the second for the Buildings table.

If you just want to delete a building and all its associated entries in other tables, just delete the building and then use a single DeleteMissingReferences for the Buildings table.

How to delete stuff

Deity

Mad Scientist

Deity

Mad Scientist

Deity

Mad Scientist

Chieftain

Ancient Druid

Chieftain

Ancient Druid

Deity

Reluctant Modder

Deity

Reluctant Modder

Amnesiac Modder

Deity

Amnesiac Modder

DLL Minion

Amnesiac Modder

DLL Minion

Similar threads