[TUTORIAL] Adding New XML Columns to Existing XML Tables w/SQL

[Afforess]

It's rather easy to add new columns to XML tables (like Technologies), which can be used Firaxis columns. At the moment, all attempts to add new XML columns via pure XML have failed, but it works with SQL. These new columns can be accessed from the DLL (if you had source code access... ) or from Lua.

Assuming you've already got a project set up in Modbuddy, add a new SQL file. For the purposes of this tutorial, I'll be modifying technologies to add a column for my Tech Diffusion mod, but you can add anything you like to any existing table, so long as you know the datatype you want to use, and the proper name of the table.

In my case, I start with this first line:

ALTER TABLE Technologies

We're telling the game that we are altering the Technologies table. Make sure you get the table name exactly correct, or else you will encounter strange issues.

Next, we're giving the table the name of the new column, and datatype.

ADD DiffusionModifier integer;

My new column will be appended to the end of the list, and be named "DiffusionModifier". It's of type integer.

Now, the column is created, but the values are all undefined. You will want to initialize them to whatever default value you want. In my case, I want to initialize them to zero.

UPDATE Technologies SET 'DiffusionModifier' = 0;

Great, now all Technologies have a new column, DiffusionModifier, which is 0. You can use the "WHERE" command in SQL to make your changes more specific, and manipulate the data in any way you see fit.

So far, this new column is meaningless. Let's change the values in SQL. I want modern techs to diffuse faster, so let's add this new line to change modern techs:

UPDATE Technologies SET 'DiffusionModifier' = 25 WHERE Era = 'ERA_MODERN';

Now all techs in the Modern Era have a DiffusionModifier of 25, and you didn't hardcode a thing! Your CS professors would be so proud.

The full SQL file so far is this:

Spoiler :

ALTER TABLE Technologies
ADD DiffusionModifier integer;
UPDATE Technologies SET 'DiffusionModifier' = 0;

UPDATE Technologies SET 'Cost' = Cost * 1.77 WHERE Era = 'ERA_MODERN';

Now, to actually something with your new column. Since DLL access is out of the question, we can't use it there. But we can access it in Lua easily.

Let's say I want to access the new DiffusionModifier for my Tech Diffusion mod. I simply use this new code in Lua

for eTech in GameInfo.Technologies() do
	print(string.format("Diffusion Modifier for %s: %d", eTech.Type, eTech.DiffusionModifier));
end

in my code and in Firetuner, it will print out the diffusion modifier of each tech. Hopefully you added the new column for more than just debugging statements though.

 

[JeBuS27]

I would like to say that it's probably a safer practice to create your own tables, and key them by Tech keys or whatever. You can have whatever columns you want then with a far smaller chance of collision with other mods or future patches by Firaxis.

 

[Afforess]

I would like to say that it's probably a safer practice to create your own tables, and key them by Tech keys or whatever. You can have whatever columns you want then with a far smaller chance of collision with other mods or future patches by Firaxis.

As long as you don't add exactly the same column as another mod, I can't see it having a negative interaction. If you're really worried, name everything with your username in front of the real name, like Afforess_DiffusionModifier, and then there is no chance of overlap.

 

[killmeplease]

Nice guide Afforess!
At last they did it right.
AFAIR, adding new attributes in Civ4 required adding code in 10 places and DLL recompilation.

 

[xienwolf]

Yes, in C4 you had to add to the XML, to the Schema for the XML, to CvInfos.cpp, CvInfos.h, and then to anywhere that the data should be used, which almost always includes CvGameTextMgr.cpp and one of CvUnit/CvPlayer/CvTeam both .cpp and .h. All but the first 2 being in the DLL so requiring a recompile. If you wanted to use the data in python you also had to add data to the Cy files, which added another 1-4 (BasicInfosInterface, and possibly another cpp, h, and interface).

 

[Blumonster]

Hey, thanks a lot for the tutorial.

I've added and re-added so much to this post...finally I'm deleting the whole thing. This is what it comes down to: I use your example and sometimes I get a red squiggly syntax error message. Using the same format you describe, I can't create my own ADD line without a syntax error. As I said, this even happens when I use your example (unless I do weird things like paste your example above your example...then the second paste has no red lines but the first paste still does). I'm so confused. I can't even get started on this. May be too much to ask, but can anyone help me? Does anyone have any idea what's going on?

For example, if I type out:

ALTER TABLE Technologies
ADD DiffusionModifier integer;
UPDATE Technologies SET 'DiffusionModifier' = 0;

UPDATE Technologies SET 'Cost' = Cost * 1.77 WHERE Era = 'ERA_MODERN';

I get a red syntax error line in the ADD line under "D" of "DiffusionModifier". If I overwrite it with a paste, the same thing happens, BUT if I paste it into a blank file, I get no error lines. If I type out the above lines, even in a new file, I get the red error line again. So I can't type out my own either. I--I--uh, duh--am just at a loss.

 

[Afforess]

I'd just ignore the error lines then. I personally tested everything in this tutorial, and it works.

 

[Lujan]

For example, if I type out:

ALTER TABLE Technologies
ADD DiffusionModifier integer;
UPDATE Technologies SET 'DiffusionModifier' = 0;

UPDATE Technologies SET 'Cost' = Cost * 1.77 WHERE Era = 'ERA_MODERN';

The only thing I can really say on this is be consistent with your formatting. If this is direct paste of your code, try ending your first line in a ";" as well, to match your other lines.

 

[cromcrom]

Thanks for the tutorial, this is very interesting.
But I downloaded your "tech diffusion" mod, and can't find any "sql" file. Anything wrong with me ?

Moreover, I would like to create a mod where some techs are randomly disabled when you finish researching techs, and the cost of the techs grows as more and more techs are researched. ( http://forums.civfanatics.com/showthread.php?t=390638&highlight=age+civilisations )

I think I will add a "disabled" line to the technologies, using your tutorial, and that could be the beginning of it all ^^

Any help or advice in this matter would be so much welcome

 

[Afforess]

Thanks for the tutorial, this is very interesting.
But I downloaded your "tech diffusion" mod, and can't find any "sql" file. Anything wrong with me ?

It's part of a future, unreleased version.

Moreover, I would like to create a mod where some techs are randomly disabled when you finish researching techs, and the cost of the techs grows as more and more techs are researched. ( http://forums.civfanatics.com/showthread.php?t=390638&highlight=age+civilisations )

I think I will add a "disabled" line to the technologies, using your tutorial, and that could be the beginning of it all ^^

Any help or advice in this matter would be so much welcome

I don't think you'll be able to disable the researching of techs, or even change their cost in-game, dynamically, with just SQL and Lua. You'd need the Source Code for that.

 

[cromcrom]

Crap.
So I will have to wait for the SDK, I guess. anyways, thanks a lot for the answers, and your mods. Take care.

Cheers.

 

[Blumonster]

Thanks for responding, and thanks for the feedback.