Civilization VI - SQLite Modding

First a note, there are a lot of wikipedia links in this section below. You don't need to follow any of them for the guide, I've add them for those interested in learning more about SQL and Databases.

SQL (commonly pronounced sequel) is a language for retrieving data from a relational database[en.wikipedia.org]. There's a few flavors of SQL.

• SQLite[en.wikipedia.org] - This is what we're using when modding Civilization VI. It's based on .sqlite files and is usually smaller in nature. It follows the PostgreSQL[en.wikipedia.org] syntax. It's core strength is that it doesn't rely on a server, but instead the existence of a file on the client (i.e. your computer).
  
• MySQL [en.wikipedia.org]- This is a commonly used version of SQL. It is not used in Civilization VI modding, but most SQL is very similar so learning/knowing it doesn't hurt. The strength of MySQL is that it's open sourced and doesn't have those hefty licensing fees like Microsoft SQL Server.
  
• SQL Server[en.wikipedia.org] & T-SQL[en.wikipedia.org] - This is Microsoft's take on SQL. Commonly used by companies and corporations that can afford to pay licensing fees for the increased stability and performance*.

* - My statement about stability and performance is probably controversial to some, sorry.

Civilization VI uses SQL to store all of its game configurations, similar to how many other games use ini and xml files. For example,

• Civilizations - Their leaders, traits, and everything in between, even colors.
  
• Units - Their promotions, cost, combat stats, religious stats, etc.
  
• Buildings - Their yields, modifiers, cost, etc.
  
• Technologies & Civics - Their boosts, cost, benefits, etc.
  
• Great People - Their costs, descriptions, how to earn them, etc.
  
• The list continues and spans to even hard to understand things like some of the AI decision making for barbarians and leader agendas as well.

The above mentioned are all found in the DebugGameplay.sqlite database file. Which like all other database files mentioned in this guide can be found in the Cache Folder. The other commonly used database file is DebugLocalization.sqlite.

Civilization VI supports multiple languages, the way it does this is instead of setting text (names, descriptions, etc) in the gameplay database, it assigns them unique keys (e.g. "LOC_UNIT_SETTLER_NAME" which is the key for a settler unit's name). Then when the game asks for a settler's name, it combines that with your language setting to grab the correct text.

Example SQL Queries below (don't worry if you don't fully understand the queries, I'll explain more later).

The guide below shows you how to setup SQLite Browser (portable version). Feel free to install another version, you should be able to still roughly follow along.

• Download SQLite Browser[sqlitebrowser.org] (portable version recommended).
  
  
• After that, run the installer that you've downloaded. Really all the portable version does is extracts the files to a folder despite them calling it an installer. You can move that folder anywhere you want after installation, even a flash-drive.
  
• Once complete, open Sqlite Browser by running "SQLiteDatabaseBrowserPortable.exe"
  
  
• Once open, you'll need to open a database. In SQLite, all databases are .sqlite files.
  
  
• You can find the Civilization VI .sqlite files in your Cache Folder.
  
  
• Once you open a .sqlite database, you'll find a few tabs across the top. The first of which is the Database Structure tab. This shows you, among other things, all tables in the database (so in our case all gameplay tables) and if you click the arrow next to the table name you can view all of its columns (names and data types).
  
  
• The second tab across the top is the Browse Data tab. Here you can select a table (in this case the first one, Adjacency_YieldChanges) and edit the data in the table with the UI on the right-side.
  
  
  
• We're going to ignore/skip the 3rd tab (Edit Pragmas). It's irrelevant, unnecessary, and complex for Civilization VI modding.
  
• The 4th and last tab is the most useful tab, Execute SQL. Here you can execute SQL statements from really advanced to really simple. Here's a very basic SQL statement below. By pressing the blue play arrow or pressing F5 you can execute your statements (don't forget your semi-colons).
  
  
• The reason the Execute SQL tab is the best tab, despite not having a UI, is because the UI cannot do advanced queries that join results from multiple tables together, like below (there's a lot going on below so don't get intimidated. I'll try to cover it all later in this guide).
  

There's a lot of sections to the basics. Increased by the fact I'm relying on a lot of explaining from other websites since SQL can be an entire career.

In SQL there are generally 4 statements (there are many more, but they're for advanced stuff. We'll get to a few later).

• 1. SELECT - This is a statement used to view or read data in a table.
  
• 2. INSERT - This is a statement used to add or create data in a table.
  
• 3. UPDATE - This is a statement used to edit or update data in a table.
  
• 4. DELETE - This is a statement used to remove or delete data from a table.
  
• 5. Comments [www.w3schools.com]- Though they're not actually SQL statements, you can write comments in your code that are ignored completely by SQL. Anything after a double-dash (--) will be ignored.

These 4 statement types together are commonly referred to as CRUD operations (Create, Read, Update, and Delete). A SELECT statement retrieves data from a table in the database. You specify one or more columns (separated by commas) then the table you're selecting from.

You can use an asterisk (*) to select ALL columns from a table.

The SELECT statement below retrieves all information (*) from the Buildings table (See the section on SQLite Browser to learn how to find a list of tables in a database).

This statement is the bread & butter of exploration. Try looking through the list of tables and doing select statements on them. An INSERT statement adds data to a table in the database. To insert you must specify the table, columns you're inserting, and then the values you're adding. Please note that there are too many columns in this table for sake of example I put '...' to represent the rest of them. A UPDATE statement changes data that already exists in a table in the database. To update data in a table you must specify the table, which columns you're updating, and their values. WARNING: This will set ALL unit costs to 1.
A DELETE statement deletes one or more rows in a table in the database. To delete data from a table, you must specify the table. WARNING: This will delete ALL units.
There are many ways to filter a SQL statement. I've elected to link other website examples instead of entirely reinventing the wheel.

• Standard Operators[www.w3schools.com] (e.g. =, <>, <, >, <=, >=, etc)
  
• The IN keyword[www.w3schools.com].
  
• The LIKE keyword[www.w3schools.com].

Here are some starting examples, Try and apply the information in the links above to the Buildings or Units tables.



Now that we've covered the WHERE clause, I should mention that you can do multiple conditions in the WHERE clause with the AND and OR operators.

• AND Operator[www.w3schools.com] - For AND, both conditions must be true in order for the row to be retrieved.
  
• OR Operator[www.w3schools.com] - For OR, Only one of the conditions must be true in order for the row to be retrieved.

NULL represents no value. In SQL, the condition would be false. In fact, just about every operator returns False when provided a value of NULL. You check for NULL with the 'IS' and 'IS NOT' operators.


You need to put a semi-colon at the end of a STATEMENT (not line) in SQLite. If you forget one you'll find a syntax error in the Database.log file in your Logs Folder when you start up the game when the mod is loaded into the SQLite tables.

Joins in SQL are a way to select, update, or delete data from multiple tables at once. Since the topic of SQL Joins is so large, I'm going to keep this information to the bits that are helpful for Civilization VI modding.

Naturally there's a lot of data that is connected in some way. This query grabs all civilization and their respective leader information.

This query grabs all buildings that change provide yields (one building will appear multiple times if it changes multiple yields).
It's not uncommon to see a table in the Civilization VI database ending in "_XP1" or "_XP2" standing for "Expansion 1" and "Expansion 2" respectively. You can use a JOIN to select unit data from the Units and Units_XP2 tables.
This query retrieves a unit's strategic resource and it's per turn strategic resource cost. In SQLite you can use the ATTACH keyword to connect to a second database file on the same connection.

So for me, I use:
Note the two single-quotes instead of one single-quote for "Sid Meier's". If you're inside a text value in SQLite you have to "escape" single-quotes by doing a second one so that it knows you're not wanting to end the text. Now that I've connected the localization database, I can retrieve actual string values instead of their keys I mentioned earlier.
This query retrieves all units with their American English unit names. Notice in this query how you can join twice to the same table to retrieve not only American English unit names, but American English unit descriptions.

Please note that the ATTACH keyword is a feature of SQLite specifically and not other flavors of SQL.

For learning how to turn your SQL into a mod, we're going to make a couple of ridiculous mods.

Here are the changes we will be making,

• 1. All units will cost 1 production to create, which we'll learn affects purchase cost as well.
  
• 2. We're going to make Ancient Era buildings cost triple production.
  
• 3. We're going to change Victoria's name to "Annoying Victoria" because I don't like her.
  
• 4. We're going to change Victoria to only say "Blah Blah Blah", same reason.

Start by opening ModBuddy.
Create a new empty mod and give it any name you'd like,


Now that we've created our mod we need to add a SQL file so that we can change all unit production cost to 1 and triple Ancient Era building costs. Start by adding a new SQL file.
Select "Database (SQL)" and name it Production.sql,

We will be putting all queries that change production in this file.

New Query - Setting Unit Costs to 1.

Updating all unit production cost is pretty easy. We just do an UPDATE statement on the Units table and set the Cost column to 1.
Just remember, without a WHERE clause an UPDATE or DELETE statement affects ALL RECORDS in the table.

New Query - Tripling Ancient Era Building Costs

Tripling the cost of only Ancient Era buildings is a little more involved, so I'm going to ease into it making a few extra queries for explanatory purposes.

If we were updating ALL buildings to triple cost it would be as simple as the query below. Just like our units query.

This next query updates all buildings that require a technology in the list provided, which just so happens to be the list of technologies in the Ancient Era.
The problem with this gets very tedious with longer lists. What if you wanted to update buildings from multiple eras? What if you wanted to support mods that change technologies? Let's write the same query, but have it retrieve a list of technologies from the ancient era using the Technologies table.
That's better. Now, you might think we're done, but you've forgotten that Civics can also grant buildings as well. So here's the same query as above, but for Civics.

Okay, so we've created two separate queries. Technically, we could just execute both queries separately and be done, which is completely okay. But, just in case you run into a scenario where you need to combine two queries into one, I'm going to show you two methods to do so. The first method is to make use of an OR operator in the WHERE clause.
If the building's PrereqCivic is found in the list of ancient era civics OR its PrereqTech is found in the list of ancient era technologies, its cost will be tripled. The second method for combining the two queries is by using the EXISTS and UNION keywords with a slightly different WHERE clause on our sub-queries.

Checking Update Statements
If you ever want to test what rows an UPDATE statement is going to update, you can remove the UPDATE and SET parts and replace them with SELECT and FROM.

For text modding, we're going to start out just like we did in the Production section and add a new SQL file. This time call it "Victoria_Text.sql".

Here are the queries we will be reviewing below.

Pay no attention to the terrible terrible spelling. Not going to say which word, just in case you didn't notice.

The order I placed them in the photo clashes with the order we'll be reviewing them in, more on the reasoning later.

LocalizedText Table & Tags
The LocalizedText table from the DebugLocalization.sql database in your Cache Folder is the table we'll be used for both of these changes. As I stated previously in the guide, it holds all text for the game. You can find the text you want using the Tag and Language columns.

As for knowing which tags to update, you've got two methods.

• 1. Find the relevant table, get the key, update the text.
  
• 2. You'll quickly notice that almost all the tags follow the same pattern. "LOC_X_NAME" or "LOC_X_DESC" or something slightly different.

We're going to take the easy way out and use #2. Changing Victoria's Name
Let's quickly look through the LocalizedText table for things related to Victoria. This query will give you a list of all American English tags containing Victoria.
If you do a quick scan through the list, you'll find 3 that stand out. Upon further inspection, you'll realize that the first one is a city name and we're here to change a leaders name so we will just work with the last two. Here are the two that were obvious to be Victoria name related to me.

Now we'll just adjust the above query to be an UPDATE statement, and now we have the query we need to change Victoria's name.

Changing Victoria's Speech
(and probably everything else too) We've already written a query to look for all text tags containing Victoria, so let's bring that back.

And we've already identified 3 things we don't want to turn into "Blah Blah Blah" (The 2 names and the city name).
After quickly skimming through the results, I'm fine with updating all of these. Feel free to filter out some for yourself, I'm just going to stick with this.

In fact, I've taken laziness to the next level as a learning experience for you guys. We can remove the leader names from the WHERE statement above if we just run this query before the leader name query. This is why the order of the queries in the screenshot doesn't match the guide. The screenshot did it the lazy way.

The Lazy Way

The reason this works is that the first query is updating everything except the city name to "Blah Blah Blah" then our second query comes through and changes that "Blah Blah Blah" for the two leader name tags to "Annoying Victoria". Now that we have our files created and our queries written we need to let the mod know to include them. Right-click on the project in the Solution Explorer and choose Properties.

Front-End Actions
Text for the game setup screens are loaded when the game boots up.

To tell the game to load a SQL text for the game setup screens create a front-end action of type UpdateText (name it what you like) and add the Victoria_Text.sql file.

In-Game Actions
Text for in-game is during the loading screen for a game.

To tell the game to load text or gameplay SQL files for in-game create an in-game action of type UpdateText and UpdateDatabase and add the appropriate sql file.

There were a lot more changes than this, these were just the ones I had time to screenshot (also, some of the image files were too big and I was too lazy to deal with it).

After testing this mod, I realized I missed the buildings that have neither a Technology nor Civic requirement a part of the ancient era (aka the monument).

Good thing this mod was made for teaching and I don't actually need to go back and fix that part. #homework4u