1. We have added the ability to collapse/expand forum categories and widgets on forum home.
    Dismiss Notice
  2. Photobucket has changed its policy concerning hotlinking images and now requires an account with a $399.00 annual fee to allow hotlink. More information is available at: this link.
    Dismiss Notice
  3. All Civ avatars are brought back and available for selection in the Avatar Gallery! There are 945 avatars total.
    Dismiss Notice
  4. To make the site more secure, we have installed SSL certificates and enabled HTTPS for both the main site and forums.
    Dismiss Notice
  5. Civ6 is released! Order now! (Amazon US | Amazon UK | Amazon CA | Amazon DE | Amazon FR)
    Dismiss Notice
  6. Dismiss Notice
  7. Forum account upgrades are available for ad-free browsing.
    Dismiss Notice

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

Discussion in 'Civ5 - Modding Tutorials & Reference' started by Afforess, Oct 20, 2010.

  1. Afforess

    Afforess The White Wizard

    Joined:
    Jul 31, 2007
    Messages:
    12,239
    Location:
    Austin, Texas
    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:

    Code:
    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.

    Code:
    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.

    Code:
    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:

    Code:
    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 :
    Code:
    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
    Code:
    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.
     
  2. JeBuS27

    JeBuS27 Heretic

    Joined:
    Sep 21, 2005
    Messages:
    321
    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.
     
  3. Afforess

    Afforess The White Wizard

    Joined:
    Jul 31, 2007
    Messages:
    12,239
    Location:
    Austin, Texas
    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.
     
  4. killmeplease

    killmeplease Mk Z on Steam

    Joined:
    Nov 22, 2007
    Messages:
    2,690
    Location:
    Samara
    Nice guide Afforess!
    At last they did it right.
    AFAIR, adding new attributes in Civ4 required adding code in 10 places and DLL recompilation.
     
  5. xienwolf

    xienwolf Chieftain

    Joined:
    Oct 4, 2007
    Messages:
    10,589
    Location:
    Location! Location!
    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).
     
  6. Blumonster

    Blumonster Chieftain

    Joined:
    Oct 22, 2010
    Messages:
    90
    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.
     
  7. Afforess

    Afforess The White Wizard

    Joined:
    Jul 31, 2007
    Messages:
    12,239
    Location:
    Austin, Texas
    I'd just ignore the error lines then. I personally tested everything in this tutorial, and it works.
     
  8. Lujan

    Lujan Chieftain

    Joined:
    Nov 23, 2005
    Messages:
    3
    Location:
    Ft. Worth, TX
    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.
     
  9. cromcrom

    cromcrom Cernu

    Joined:
    Nov 11, 2005
    Messages:
    268
    Gender:
    Male
    Location:
    Chateauroux
    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 :)
     
  10. Afforess

    Afforess The White Wizard

    Joined:
    Jul 31, 2007
    Messages:
    12,239
    Location:
    Austin, Texas
    It's part of a future, unreleased version. ;)

    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.
     
  11. cromcrom

    cromcrom Cernu

    Joined:
    Nov 11, 2005
    Messages:
    268
    Gender:
    Male
    Location:
    Chateauroux
    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.
     
  12. Blumonster

    Blumonster Chieftain

    Joined:
    Oct 22, 2010
    Messages:
    90
    Thanks for responding, and thanks for the feedback. :)
     

Share This Page