1. We have added a Gift Upgrades feature that allows you to gift an account upgrade to another member, just in time for the holiday season. You can see the gift option when going to the Account Upgrades screen, or on any user profile screen.
    Dismiss Notice

Tutorial: Modding with SQLLite Studio (PC)

Discussion in 'Civ6 - Modding Tutorials & Reference' started by isau, Jan 2, 2017.

  1. isau

    isau Deity

    Jan 15, 2007
    Hello everyone,

    I've relied on the community a lot for help over the past few weeks and wanted to give back with a quick tutorial to help other people along. I'm aware there are some tutorials on using SQL out there, but I didn't see anything grounded in using a particular toolset. The intent of this tutorial is to provide a hands on look at my precise process for editing mods.

    Keep in mind I may not be the best person to do this, and other modders may have other techniques that are just as or more successful. This is just a way to show what my process is and what has worked for me.

    By the end of this tutorial you will be able to:
    • Create a basic .modinfo file that includes references to the elements of your mod
    • Connect SQLite Studio to the Civilization 6 debug database
    I will be following this tutorial up with more in a follow up post. I ran out of ability to upload photos to this post and so it has to happen in a followup. :)

    Here's what to expect in part 2 (currently being written as a follow up to this post):
    • How to use the SQLite database to query data
    • How to edit your SQL file to update the database
    • How to test to make sure your code is working

    What is SQLLite Studio?
    SQLite Studio is a free tool that allows you to connect to SQLLite databases and write queries to gather or change the data they contain. There are many tools out there that you can use for this task. I like this one because it requires no install, is relatively fast, and has a number of useful features.

    What is Notepad++?
    Notepad++ is a free tool that is similar to Windows' default Notepad application, but which is "smarter" and is able to format SQL code and XML code intelligently. This is hugely helpful for modding.

    Before You Begin
    To complete this tutorial you will need to download the latest versions of SQLLite Studio and Notepad++. The links below will help orient you.

    You will also need to download and unzip the tutorial mod file I have attached to this post.
    • Tutorial Mod Structure.zip

    Ok Let's Get Started
    Now that you've got your tools installed, let's take a look at a super basic mod structure. Unzip Tutorial Mod Structure (attached to this post) and have a look.

    But before we do that, let's use a scenario to help us orient the discussion.

    Scenario: Let's write a mod that makes a simple edit to the Iron resource, so that players are able to see the resource once they reach the Craftsmanship civic, instead of researching the Bronze Working tech.

    The basic mod folder structure
    The structure of a mod can be very complex, but with the tutorial folder I have made it as simple as possible. Our mod will ultimately contain two files.
    • A .modinfo file that will tell the game what files our mod contains. This is the first document read by the game upon loading our mod, and the "road map" it follows when deciding what assets to load and in what order.
    • A .sql file that will contain the actual code that edits the game.


    Exploring .modinfo with Notepad ++
    Let's take a look inside our .modinfo file.

    This may work a little differently on your machine. For me, I am able to open .modinfo by right clicking it and selecting "Edit with Notepad++"


    The file loads and looks like this:


    The main thing to note here is that Notepad++ color codes for us the various parts of the XML. This is very handy and just part of why Notepad++ is so helpful to modders.

    So let's get started customizing our mod.
    • The most important thing to update is the GUID you see in the second line of the code. This is the part the currently reads: <Mod id="410d1dd4-1324-4e8f-bdc0-6f3f696ba62e" version="1"> . The game uses this to identify each unique mod, and no two mods are allowed to have the same code.
    To get a new code, go to https://www.guidgenerator.com/online-guid-generator.aspx. Select "Generate some GUIDS." Then just copy and paste the code it gives you over the code the currently exists.


    Once you have put your GUID in place, the rest is mostly cursory. Change the Name, Teaser, and Description to something worthwhile. Here's how I changed mine:


    Notice that further down the page there are some additional pieces. We don't actually need to change any of this for our mod to work, because its already set up for us. But it's helpful to understand what these sections do.
    • The Files section will list every single file in your mod. We just have one file, Code.sql. But if we had more, we'd list every one of them here.
    • The Components section, with its UpdateDatabase tag tells the game to perform a database update using the items listed inside. Since our mod is so simple, again there is only one file listed here. But if things we more complex we might need to use this section to specify a specific load order.
      • There is a string in the UpdateDatabase call called "Id" currently set to PUT_A_UNIQUE_STRING_HERE. Well, technically you don't have to. But if you want to name a section of a database call something unique you can. It's beyond the scope of this tutorial.

    Fun with SQLite
    Now it's time to set our mod folder aside for a little while. We don't want to just jump into our .sql file and start making wild edits. We want to learn about the existing database first, and SQLite is the way we will do that.

    Connecting to the Civilization 6 Debug Database
    Open the SQLite application. Go to the top left and from the pull-down menu, select "Add a database." You'll get a pop-up like this:


    Leave the Database type as "SQLite 3" (at least I always have without problems).

    In the File menu, select the folder icon. Navigate to the Civilization 6 DebugGameplay database. This is a database that the game creates every time you run the game. It is updated in real time at the moment the game loads, and includes edits made by mods. It will be critical in helping us understand what is going on under the hood of the game.

    On my machine (and I assume everyone's) the database is located at \Documents\My Games\Sid Meier's Civilization VI\Cache


    Once the database is added, right click it in the list on the left and select connect to database. If this is successful, a list of all the tables in the database will show up on the left.


    Ok, at this point I'm out of ability to upload pictures. :) In part 2, which I will follow up on shortly, I will show how you will use SQLite Studio to view the database, and how what we see there will affect the code we ultimately write in our mod. Stay tuned. :D

    Attached Files:

    Last edited: Jan 7, 2017
  2. isau

    isau Deity

    Jan 15, 2007
    If you followed up to this point, we just got done connecting to our database. Now let's dive into how see and edit data.

    The SQL Editor
    In order to get started, we first need to open a SQL Editor. From the menus at the top of the screen, select Tools > SQL Editor. (You can actually do this multiple times to open multiple SQL Editors. This is useful if you have a couple of things you are working on at once. Each SQL Editor is accessible from the list at the bottom of the screen.)

    Viewing the Resources Table with a SELECT Statement
    Our scenario was we wanted to update the Iron resource so that it no longer requires the Bronze Working tech, but requires the Craftsmanship civic instead. Before we can do that, we need to know what the structure of the Iron resource is.

    All Resources (Iron, Copper, Cows, Cotton, etc) in the game are listed in a table called Resources. One way to view the contents of that table is to right click it in the list of tables on the left and select that option. But since we're learning SQL, let's do it that way instead, because it gives us more options in the long run.

    In the SQL Editor, type the following code:

    SELECT * FROM Resources ;

    (It is not case sensitive in this case, but it's still a good habit to format SQL commands in ALL CAPS and table names and fields in Title Case Caps.)

    The SELECT ... FROM construction is a command that means "go to this table and pull back a list." The * means "all columns." Here is the result of performing this SELECT statement:


    Two important things are showing on this screen:
    • In the middle of the screen we see the results in the table, a list of all the resources in the game
    • In the bottom of the screen we see the Status, that tells us whether our command was successful, or whether it contained errors. Since there are no errors, it is showing in blue. If there are errors, text will show up in red.

    Another important thing we can see is that there are various columns in the Resources table, including:
    • ResourceType, the unique ID for each resource
    • Name, which links to a local text file that displays in the in-game-name of the resource
    • PrereqTech, which indicates what tech is required to reveal the resource
    • PrereqCivic, which indicated what civic is required to reveal the resource

    Scrolling down the list of results, we can find the listing for RESOURCE_IRON, which is our Iron resource. We can see that Iron is currently listed as having a PrereqTech of TECH_BRONZE_WORKING. It's PrereqCivic is currently NULL.


    Now, let's pretend for a moment that the list of Resources was extremely long, and it was difficult to locate the Iron resource by scrolling. Here's where we can use a number of tricks to find the listing, by tailoring our SELECT statement with conditions.

    If we were unsure of the ResourceType name
    We might know that our ResourceType is likely to contain the word "Iron" in it, but not know much else about it.For cases like that, we can add a WHERE... LIKE clause to the select. It's written like this:

    SELECT * FROM Resources WHERE ResourceType LIKE '%iron%' ;


    The WHERE ... LIKE statement limits the results of the search to anything containing the word "iron." The % before and after the word mean the word "iron" can appear anywhere in the string.

    If we knew the PrereqTech name but not the ResourceType name
    It happens very frequently that you will know the name of one object but not the object its tied to. You can use WHERE clauses to assist with this:


    The WHERE .... = clause limits the results to just rows where conditions match exactly. In this case, we get the exact same results as with our LIKE statement before.


    If we only knew that PrereqTech would not be NULL
    If all we knew going in is that the PrereqTech was unlikely to be NULL, we could use this construction:

    SELECT * FROM Resources WHERE PrereqTech IS NOT NULL ;

    As you can see from the below results, this pulls up a list of every row where the PrereqTech isn't NULL. (You can reverse this and search for rows where it is NULL by changing this to IS NULL).


    Updating the Table with UPDATE Statements

    Now that we have seen the results of the SELECT command it's time to explore how we would go about updating the contents of the table. One of the main ways to change the database is to use UPDATE statements, which simply changes the value of one or more fields to something else. But first, a word of warning.

    Relational Tables
    Civilization 6 uses what is called a 'relational database' to maintain relationships. This means data in one table frequently ties to data in another table. In this case, we can see that the Resources table ties to both the Technologies and Civics tables, via the PrereqTech and PrereqCivic fields respectively.

    This relationship is critical to understand, because it means if there is a bad pointer in the Resources table--say, to a Civic that does not exist--our mod will crash the game back to the loading screen (or maybe even the desktop) when players try to load it.

    The reason SQLite Studio or a similar editor is so useful is it lets us test our updates ahead of time to make sure we got everything perfect.

    What Happens When You Get a Reference Wrong
    As an experiment, try this code in the SQL Editor:

    UPDATE Resources SET PrereqCivic='Test' WHERE ResourceType='RESOURCE_IRON' ;

    Notice that in the Status box we get a warning that says a "Foreign Key constraint failed." What this means is that since there is no Civic in the Civics table with a key of "test", the relationship does not work. If we put this code in our mod, the mod will crash.


    Before we can perform this UPDATE, what we need to do is first check the Civics table to find the right name for the Craftsmanship civic. To find that name, we just need to erase our code, and change it to this:

    SELECT * FROM Civics

    (or if you want to be a show-off, you can use what we learned earlier and go with something like SELECT * FROM CIVICS WHERE CivicType LIKE '%craft%' to limit the results more).

    The Craftmanship civic is the second one listed in the table.


    Editing our UPDATE Statement
    You can use the History tab in the SQL Editor to see the History of SQL queries you've written. This feature is one the reasons I like SQLite Studio. Go to the History tab and select the broken UPDATE we wrote earlier.


    When we change our code to the code below and run it, we can see the error is resolved:

    UPDATE Resources SET PrereqCivic='CIVIC_CRAFTSMANSHIP' where ResourceType='RESOURCE_IRON' ;


    Notice that the Status box tells us that exactly 1 row was affected. This is important. If we saw that 0 rows were affected, we would know there was a problem, and even though the commands we followed were syntactically correct, the code doesn't do what we wanted. Similarly, if we saw 100 rows were affected, we'd know we did something seriously wrong (like left off the WHERE statement in our UPDATE)!

    Returning to our previous SELECT statement lets us ensure our table is the way we want it. Go to the History tab and find the SELECT * FROM Resources query you wrote earlier (or just retype it). Verify that the PrereqCivic is now set to CIVIC_CRAFTSMANSHIP.


    Notice, however that the PrereqTech is still set to TECH_BRONZE_WORKING. We can nix that by adding another simple line of code. Note that each line of code must be terminated with a semicolon ( ; )

    UPDATE Resources SET PrereqTech=NULL WHERE ResourceType='RESOURCE_IRON' ;

    Or you can combine those two commands into one with a comma.

    UPDATE Resources SET PrereqCivic='CIVIC_CRAFTSMANSHIP', PrereqTech=NULL WHERE ResourceType='RESOURCE_IRON' ;

    -- Ok, I'm out of pictures again. In the next step we will return to our actual mod, and test it in an actual game.
    Last edited: Jan 7, 2017
    Ownsya and Cryotek like this.
  3. isau

    isau Deity

    Jan 15, 2007
    Part 3 - Putting Code into Your Mod

    If you followed so far, we just used SQLite to run a bunch of queries against the database. Now it's time to put those queries into the actual mod.

    It's important to understand that the DebugGameplay database gets reset every time you run the game. Any changes we in SQLite against the database are not the same thing as putting them in the mod. They just exist in the database until the next time we run the game. If we want to actually affect the game, we need to go back to the mod folder we were working on earlier, with our file called Code.sql.

    Open Code.sql with Notepad++ (you can use regular Notepad but I don't recommend it). Paste the code we determined works into the file. Make sure not to put it in the commented out section of the file.

    UPDATE Resources SET PrereqCivic='CIVIC_CRAFTSMANSHIP', PrereqTech=NULL WHERE ResourceType='RESOURCE_IRON' ;


    Within your mod .sql files you must also ensure you terminate every command with a semicolon (;). If you do not, the mod will not work.

    Testing the Changes in Game
    It is critical that any changes you make to the database get tested in game. This involves both visual observation of the gameplay effects, and checking the database logs to make sure the data loads correctly. The smallest of typos can cause a mod to not work correctly so it is critical to check.

    Recall that in our .modinfo file, Code.sql gets called in the Components section of the file. This is important, because it tells us that anything we put Code.sql is loaded by the game after the player actually starts the game. This means nothing in our database gets touched until we actually go all the way through the process of starting a game. I've tried to illustrate the basic relationship here:


    (Note that it IS possible to have some parts of the mod load prior to the player actually starting the game. These items go in a Settings tag inside the modinfo file. It's beyond the scope of this tutorial to go into those in detail.)

    It is only once the game is fully loaded that we can return to SQLite and start investigating. But first let's do the obvious thing and look if we see in-game evidence that our change worked:


    And it did! We can see Iron moved off the Tech tree to a space in the Civics tree.

    But let's suppose our change wasn't that easy to detect. It turns out we can use SQLite to verify our change worked as expected.

    Minimize the game (I prefer to run it in Windowed mode for this very reason, as it makes flipping back and forth between the game and SQLite easy.)

    In the SQL Editor, do a SELECT on the Resources table.

    SELECT * FROM Resources


    We can see our changes made it in-game.

    Now you may be thinking "So what, we already saw that the change was made when I wrote the UPDATE statement earlier in SQLite." But no. The game completely refreshes the DebugGameplay database at the start of every new game. When mods load, those mods then get loaded into that database. If your mod was working incorrectly, when you performed the SELECT you would see that Iron was unchanged from the main game.

    Also note that performing any UPDATES in SQLite while the game is running has zero effect on the game. To get a mod into the game it has to go into your mods folder. Nothing you do on the DebugGameplay database through SQLite has a direct effect on the game. It is used only for constructing code and testing it. All of the actual code must go in the .sql files (or .xml files if you work with xml) in the mod folder.

    Checking the Database Log
    Civilization 6 also creates a series of logs each time you run the game. A particularly helpful one is the database log, which can be found in \Documents\My Games\Sid Meier's Civilization VI\Logs

    Open the database log with Notepad++. In a best case scenario, you will see something like this:


    But if you mess something up, you might get something like this, which I got from a recent test where I got halfway through a task and forgot to comment it out before running the game:


    As you can see above, the game does not like whatever I did to the Corvee policy in one of my other mods. :) The only solution to that is to exit the game, make edits to the mod, and try again.
    Last edited: Jan 2, 2017
    Ownsya, Karmah, Cryotek and 1 other person like this.
  4. isau

    isau Deity

    Jan 15, 2007
  5. chillbaka1

    chillbaka1 Chieftain

    Nov 13, 2016
    Wow thank you sooo so much for making this. I have been trying to get into modding recently and I've been going through all of the xml files and trying to make changes which often does not work (for reasons you just mentioned) and when it does work, it is incredibly tedious. On top of that, I could not find, and had no idea how to package changes into mod folders. Again, thank you sooo much and if you have time, I'd like to maybe chat about some mod ideas I've been working on and have gotten stuck.
  6. isau

    isau Deity

    Jan 15, 2007
    I'm glad you found it helpful. :) If you would like help with specifics, feel free to ask about them. I will try to keep the discussion rooted in SQL/SQLite to keep it relevant to the topic, but I think it would be useful to have some case studies to post. So if you have anything specific to ask, feel free. :)
  7. chillbaka1

    chillbaka1 Chieftain

    Nov 13, 2016
    Ok so I have a question about what you said here.

    In what situations would you need to use this? One rather simple mod I am currently attempting to make is a mod that allows you to form corps (2 units) in the classical, armies in medieval (3 units), platoon/large army (not sure what to call each one yet but it has 4 units), and so on for each era. Then I also want to change the combat strength buff to be only +5 with each addition. I think I am on the verge of figuring it out, but is this a situation that I will need to use a load order?
  8. isau

    isau Deity

    Jan 15, 2007

    The main situation would be if you've got multiple files. The simple mod structure I provided puts all the code in a single Code.sql file. But, as mods get more complex, it's a good idea to start breaking off code chunks into multiple files. In that situation you then might need to know what order the different chunks run in.

    A second reason is that with advanced modding, you can add multiple rulesets to your mod. Once you've done that, you may have multiple UpdateDatabase calls, each one tagged with a different ruleset. You can see an example of this in my "Combined Tweaks" mod in my signature. Look at how the 3 rulesets are used to define a different set of files to run depending on which ruleset the player selects.

    The change you describe I don't think would require multiple files. I'm not entirely sure how armies are formed right now to be honest. I do know that the game only currently supports two value slots for corps and armies (COMBAT_ARMY_STRENGTH_MODIFIER and COMBAT_CORPS_STRENGTH_MODIFIER in the GlobalParameters table). It might be possible to extend these though by creating Modifiers to increase strength of an army if additional unit is added to it. The interface portion I think would be the challenging part. That part likely can't be done with just database edits, and would probably require at least some edits to lua files.
  9. Fuyu

    Fuyu Emperor

    Nov 5, 2009
    Load order is only relevant if one part cannot work without another having been applied earlier. It's not something you have to think about when you start modding.

    I'm using SQLiteStudio too, was probably the first thing that popped up on google when I tried to find something to open .sqlite files, which happened shortly after getting Civ6. This Civ5 wiki page lists 3 other programs, and I'm guessing it doesn't really matter which one you use.

    Btw, It's "SQLite" not "SQLLite"
  10. isau

    isau Deity

    Jan 15, 2007

    Yep I realized the spelling mistake after I had posted. I will need the help of a moderator to change the title. :)

    I tried a few others tools for working with SQLite and SQLite studio is the best free one I've found. I'd be open to other tools if someone had something better. There are some features I wish the tool had, but I have found it useful so far, and most importantly, 100% accurate (so far) in terms of permitting the same commands that work in the Studio to work in the game.
  11. chillbaka1

    chillbaka1 Chieftain

    Nov 13, 2016
    OK that makes sense. Thanks for taking the time to answer so thoroughly, and If you have the time have another couple couple of questions. I've been having a lot of trouble finding the particular value slots, like the COMBAT_STRENGTH_ARMY_MODIFIER. What command would you use if you are trying to search for certain keywords in the entire database while in sql? To use the army modifier example, I didn't know which table to find it in, and certainly not the specific row/column. I tried using various combinations of SELECT * FROM..... WHERE..... LIKE '%army' ; but all of them came up with errors. I also could not find anything online when searching for something along the lines of "finding only keywords in sqlite." I ended up finding it with a sort of jerry-rigged solution. I just opened all of the xml files in the assets>gameplay folder in notepad++ and used ctrl+f, searched "army" then clicked on "show all in all opened files." It works, but is a bit tedious. How do you find things when you have no idea where to look?

    So I'm guessing that this next question probably has a very long and complicated answer, so maybe you can point me to a post that explains it. What are Lua files, where can I find them, how do I edit them, and what do they generally do in game? If I want to continue with this mod idea, how should I proceed?

    Here's my final question. (For now) I've also been trying to make a mod to tweak AI values in Operations, Tactics, UnitOperations and BehaviorTrees regarding city assaults. I'm trying to change the team compositions and strength advantage values in Operations, and adjust some other values to make the AI take cities more often. When I use SELECT * FROM Operations ; it pulls up a table with the operation types and nothing else. When I double click on one like Early_City_Assault, there is a drop down table of all of the values I'm looking for that can be seen clearly in the notepad++ version, but I do not know how to call on these values. When i use the SELECT function to call on the one i want, it just shows up as a single box with its name. For example, in Operations I am trying to change the "Initial_Strength_Advantage" in Early_City_Assault to "1", but I don't know how to reference it.
  12. isau

    isau Deity

    Jan 15, 2007
    Good questions. I'm going to answer in two replies because it's kind of complicated. :) I'll start with the SQLite questions on finding things:

    COMBAT_ARMY_STRENGTH_MODIFIER is in the Global Parameters table.

    You could get all the contents of the Global Parameters this way:
    SELECT * FROM GlobalParameters ;

    Or find that specific record like this:

    FWIW a thing I do kind of a lot is start out with a simple SELECT with no WHERE clause just to see what the rows are in the table. Then tailor the SELECT down based on what I see.

    Searching the XML files when you have no idea where to start actually is a good idea sometimes. The XML files and the database contain the same information, but in different formats. The main reason I think it's a good idea to learn SQLite is ultimately you can use to also check that your data actually got into the game correctly. And if you're already doing that, it seems advantageous to me to do the majority of the coding there too and just leave the program open. :) But certainly there are times I do check the raw XML--especially when I have no clue where to start looking.

    FYI, when you're looking at XML, you can also figure out what table in the database stuff is going into. The name of the table is always the name of the most parent node within <GameData> tags. Calendar.xml is really simple example. It sends data to two tables: the Months table, and the Seasons table. You can tell because <Months> and <Seasons> are the most parent element within the <GameData> tags.


    Here's what that same XML looks like in SQLite:


    So, as you can see, the data structure is basically the same.

    There are some additional reasons why SQL is a very good idea I'll write about in a future, more advanced post. There's some ways you can use it to drill down into the data and make connections between tables that let you very quickly figure out how things are connected. But for now I think this should be fairly helpful. Let me know if you still have problems getting it to work.
  13. isau

    isau Deity

    Jan 15, 2007
    Second part of your question: Lua files. Lua files are basically scripts that handle stuff that is cosmetic enough to exist outside the core DLL (i.e. the hardbaked code) but still meaty enough that it cant be handled by just putting it in the database. Key examples of lua stuff tend to be anything involving the user interface, scripting for maps, special scripting for scenarios, and so on. I am not familiar with how lua editing is done. I did so some Python editing back in the Civ 4 days, but thats the extent of my experience.

    I am very unfamiliar with the Operations side of the database, but looking at Operations.xml, I can see there are several tables:
    • TargetTypes
    • AIOperationTypes
    • AIOperationTeams
    • OpTeamRequirements
    • AiOperationDefs
    • AllowedOperations
    • AiOperationLists
    How all these tables interrelate I'm not sure. The data you may be referring to may be in AIOperationTeams:


    If you were going to update anything in this table, you would write an UPDATE statement that includes WHERE clause. For example:

    UPDATE AIOperationTeams SET InitialStrengthAdvantage='2' WHERE TeamName='Simple Early Attack Force' AND OperationName='Attack Barb Camp' ;

    (BTW part of the reason learning to use SELECT statements is useful is the results of a SELECT match exactly the rows of an UPDATE. So if you write a SELECT showing the exact rows you want, you can change it to an UPDATE and it will hit those same rows. Very useful.)
  14. Fuyu

    Fuyu Emperor

    Nov 5, 2009
    The short answer is: there is none. Databases are meant to store data in specific places (specific row, column, in a specific table) - the data has meaning not by itself but by where it is stored. You can't even search an entire table, you can only search columns - you basically already need to know what it is you are looking for if you want to use any sql query.
    For fulltext search your approach (Notepad++: Find in files) is the only approach I know of that gives you the results you want.
    Though most irregular stuff goes into GlobalParameters, that should be the first table to look if you look for something that doesn't really fit anywhere.
    Last edited: Jan 21, 2017
  15. chillbaka1

    chillbaka1 Chieftain

    Nov 13, 2016
    It just seems odd to have a program that is so useful in editing large amounts of interrelating data, with teams working on the data usually greater than 1 person, and not include some type of search function. I was looking for something along the lines of "keyword" as input, and then "Table x, Column z, Rows 1,3,7" as output. I was thinking there might be a way to treat the tables as arrays and search for things like other programs i have used, matlab in particular. Oh well, I guess my current way will have to do.
  16. chillbaka1

    chillbaka1 Chieftain

    Nov 13, 2016
    Awesome, thanks for clearing that up for me. I started catching on to how the tables were separated from the xml format, when some of the drop down arrows displayed information from other tables. While i'm on the topic of the drop down arrows and interrelating data, could you maybe give a couple of quick pointers regarding this? Or maybe send me a message when you make that more advanced post you're talking about?

    Okay, so now that leads me to another question. (Im sorry for the long question, i've just gotten a bit stuck on this one, I don't want you to have to waste a bunch of your time on me.) So lets say that i want to add in a new team called "Raiding Party" composed of only light cavalry units that focuses only on pillaging and killing weak units. Is it possible to add in new rows to existing tables? Or do you need to scrap something in that table completely in order to create something new?
  17. isau

    isau Deity

    Jan 15, 2007

    Once you've worked with the data for a while the structure will become more evident. The database follows some fairly logically consistent patterns. For broad searches though where you have no idea where to start looking, look to the game's XML.

    Also don't forget in Sqlite you can use the list of tables to the left side of the screen to see what table names exist and what columns are in them.
  18. isau

    isau Deity

    Jan 15, 2007
    A really basic example that I use very frequently as I am data hunting and testing mods is the INNER JOIN statement. The structure works like this:

    SELECT * from Table_1
    INNER JOIN Table_2 ON Table_1.ID = Table_2.ID ;

    You can also optionally add a WHERE to it, at the very end:

    SELECT * from Table_1
    INNER JOIN Table_2 ON Table_1.ID = Table_2.ID
    WHERE Table_1.ID='test' ;

    What the INNER JOIN statement does is join the two tables into a single piece of output by returning all records that overlap on the same ID. (Note that other kinds of JOINs exist, but I don't use them that much. INNER JOIN is the one to learn when you are starting).

    Here's a concrete example. Consider the relationship between Civic and Policies. The Civics table contains a column called CivicType that is the unique name for the civic (usually anything with the name ___Type or ____Id is the unique key for the table. This is true for most tables, with the Modifiers table being the exception by having a ModifierType which is not a primary key at all. Instead ModifierId is. Also, in general, the primary key is whatever the first column is on the table.)

    The Policies table contains a column that tells you which Civic unlocks it.

    I can search the entire Policies table with SELECT * FROM Policies ;

    Notice the PrereqCivic column, which is a link to the Civics table.


    This is somewhat helpful. But imagine what I really want to know is "Which Policies are unlocked by Civic X?" (We'll use Code of Laws for our example.)

    The way we'd write that in SQL would be this:

    SELECT * FROM Policies
    INNER JOIN Civics ON Policies.PrereqCivic = Civics.CivicType ;

    Now my results look like this:


    Notice that Code of Laws is listed 4 times in the Civic Type column. That's because there are 4 Policies in the Policies table that that Civic unlocks.

    We can throw in a WHERE clause to limit the results:

    SELECT * FROM Policies
    INNER JOIN Civics ON Policies.PrereqCivic = Civics.CivicType


    And, we can also push things even further. What if what I want to see is all of the actual Modifiers that are attached to each Policy for each Civic? I can keep stacking INNER JOINS as long as there are relationships between the tables.

    SELECT * FROM Policies
    INNER JOIN Civics ON Policies.PrereqCivic = Civics.CivicType
    INNER JOIN PolicyModifiers ON PolicyModifiers.PolicyType = Policies.PolicyType
    INNER JOIN Modifiers ON Modifiers.ModifierId = PolicyModifiers.ModifierId

    For the results I scrolled over to the right so you can see the extra columns coming in.

    I can also decide that instead of SELECT * I want specific columns. I can reference any column from any table in the tables that are joined together. For example, what if I want to see the name of the Civic, name of the Policy, and the type of Modifier that applies to it? Easy:

    SELECT Civics.CivicType, Policies.PolicyType, Modifiers.ModifierType FROM Policies
    INNER JOIN Civics ON Policies.PrereqCivic = Civics.CivicType
    INNER JOIN PolicyModifiers ON PolicyModifiers.PolicyType = Policies.PolicyType
    INNER JOIN Modifiers ON Modifiers.ModifierId = PolicyModifiers.ModifierId


    Once you learn to do map these relationships you can very quickly drill through the data to find what you need. The key is understanding which columns are the links between which tables.

    Attached Files:

  19. isau

    isau Deity

    Jan 15, 2007

    Yes, you can add new rows with the INSERT statement. INSERT is one of those things that's not technically hard, but can get a bit tricky at first because it has a few more caveats than UPDATE.

    A basic insert looks like this:

    INSERT INTO District_GreatPersonPoints
                      (DistrictType,         GreatPersonClassType,         PointsPerTurn)
                      ('DISTRICT_BATH',    'GREAT_PERSON_CLASS_ENGINEER',    '1' ) ;

    Sorry because the formatting is all weird. SQL doesn't care about spacing or tabs. I have trouble spacing it right here on the forums, but the first list of items is the names of columns, and the VALUES sections are the values, row by row. You seperate each new now with a comma and terminate with a semi colon. The code above would add Great Engineer points to the Aqueduct and Roman Bath.

    What you have to watch out for with INSERT is that, like we mentioned before, tables have keys. You can't put the same key into the same table twice. In a case like District_GreatPersonPoints and many other "combo" tables as I call them, the primary key is actually not the first column, but the unique pairing of the first and second column combined. In other words, an Aqueduct gets 1 Great Engineer point according to this table, and if I try a second INSERT and try to add the pairing of Aqueduct + Great Engineer again, the database rejects it and my code doesn't work. The proper way to change the values once I've already INSERTed is to UPDATE. But, I could add a Great Scientist, Prophet, Artist, or whatever and it would be fine, because its based on the unique pairing. I hope that makes sense.

    In other words
    • INSERT - for a new row
    • UPDATE - change an existing row
    About 99% of the code I write in mods is either INSERT or UPDATE. (The other one used occasionally is DELETE but you can probably imagine why I'm shy about putting that one in a basic tutorial).

    Regarding Units specifically, there are a whoooole lot of columns in the Units table, so when you go to write your INSERT it can be a bit overwhelming. Luckily there's a kind of cheat in SQLite. If you do a SELECT for a single unit that is very similar to what you want to insert, you can "steal" most of its values. Like if I wanted to copy a Scout, I could use

    SELECT * FROM Units WHERE UnitType = 'UNIT_SCOUT';

    Then find the little blue X looking button, its the one with the tooltip in screenshot below:


    That button opens the Export panel, which lets you export your query. If you also check "Column names in first row" and select comma as your seperator, the output is basically what you need for a INSERT statement:


    The results:



    ... which is probably too much for you to read on the forums, but does work. You just have to make sure and put '' around all the strings, and change the UNIT_SCOUT key to something else, or else your INSERT fails for reasons described above about duplicate primary keys.

    There is also one other way, which can be risky, but works if you know for sure things aren't changing in the underlying database. You can 'clone' into a table using SELECT. It looks like this. Note this is example is the Features table, copying the Oasis improvement verbatim. The first item in the list is in 'quotes' because we can't clone it--its the primary key and has to be unique just like we were talking about.

    INSERT INTO Features
    SELECT 'QUO_FEATURE_HOTSPOT',Name,Description,Quote,Coast,NoCoast,NoRiver,NoAdjacentFeatures,RequiresRiver,MovementChange,SightThroughModifier,Impassable,NaturalWonder,RemoveTech,Removable,AddCivic,DefenseModifier,AddsFreshWater,Appeal,MinDistanceLand,MaxDistanceLand,NotNearFeature,Lake,Tiles,Adjacent,NoResource,DoubleAdjacentTerrainYield,NotCliff,MinDistanceNW,CustomPlacement,Forest,AntiquityPriority,QuoteAudio,Settlement
    FROM Features WHERE FeatureType='FEATURE_OASIS' ;

    I don't necessarily recommend using the second method above for this, preferring the "hard coded" unit approach, but there are times when it is very useful.
    Last edited: Jan 9, 2017
  20. chillbaka1

    chillbaka1 Chieftain

    Nov 13, 2016
    I just hope you know that you are a wonderful person, thanks so much for all the help. I think its great that we're having this conversation on the post so that other newcomers like me can come along and have their questions answered, but it does take quite a while writing up short essays each time lol. Is there a discord or something for modders to discuss these things over voice chat? I feel like it would be much more efficient and useful, and i'd be happy to make one if there currently isn't. Anyways, thank you once again for taking the time to help me so much, I've learned much more than I ever expected, and i've been spending hours and hours every day messing around with different things and getting the hang of everything.

    I think that 'INNER JOIN' thing is going to help me immensely with trying to figure out how the AI works, in particular how the BehaviorTrees (xml version) works, and how its subtrees (tables in SQLite), ShapeDefinitions, NodeDefinitions, NodeDataDefinitions, DataTypes, BehaviorTrees, BehaviorTreeNodes, and TreeData all work. Ive spent almost a week trying to figure out exactly how they all work so I can begin changing and updating values to make the AI more aggressive, and that INNER JOIN command is helping quite a bit. (still pretty confused)

    Ive got some additional questions, but I'd like to see what you think about discord, cause it'll be much faster and easier. Also, my questions are starting to get a little off topic from your original post, and I dont want to hijack the very useful post you've made.

Share This Page