Tutorial: Introduction to Report Writing with SQLite Studio

[isau]

This tutorial introduces the concept of writing reports in SQLite Studio. This tutorial is current as of the Rise and Fall expansion.


What You Will Learn:

• How to write simple queries against the Civ 6 database
• How to read a report after creating it
  
• How to limit data returned to make it useful

Prior to reading this tutorial:

• You should have a basic understanding of modding with SQLiteStudio, as described here: https://forums.civfanatics.com/threads/tutorial-modding-with-sqllite-studio-pc.608352/

What is a Report?
A "report" in this case will refer simply to a view of the data in the Civ 6 database. Reports allow us to see data by association. Below is a sample image of a simple report, which displays information contained in the Eras table:

(NOTE: If the above SELECT statement is outside your current skill level with SQL, it is recommended you view my starter tutorial on modding with SQLite Studio before proceeding with this lesson.)


Compared to some tools out there, SQLiteStudio's report writing features are limited. But the tool is free, and we will still be able to generate some very useful views from it, so it is the tool this tutorial will focus on.


Table Connections: Understanding JOINS
The report above is useful, perhaps, but it is limited to showing data from a single table (the Eras table). To really unlock reports, it is important to understand the concept of the JOIN statement.

JOIN is an instruction to cross-reference data from one table to data in another. In most cases, this means taking data from a column in one table then doing a look up using the primary key of that table.

For example, in our report above, we see a list of all of the Eras. EraType is the PrimaryKey of this table; it is the unique way to find any particular record.

Other tables contain references to the EraType in the Eras table. For example, consider the Technologies table:

As we can see, the Technologies table also contains a field called EraType. This is a reference to the PrimaryKey of the Eras table. As such, we can use it to perform a JOIN so that we can see data from both tables in a single report.

The syntax for writing a JOIN is as follows:

SELECT * FROM Technologies
LEFT JOIN Eras ON Technologies.EraType = Eras.EraType ;

• You write the first part of the SELECT statement as normal. This is the first table to include in your selection (in this case, Technologies).
• To associate a second table, add your JOIN statement. JOIN will be preceeded by one of two terms:
  • LEFT JOIN: Will find all records where there is a connection, but also not hide any records in Technologies with no match.
  • INNER JOIN: Will only display records where there is a match between Technologies and EraType

HINT: Don't worry too much about LEFT versus INNER JOIN. 99% of the time when writing reports for Civ 6 what you're looking for is a LEFT JOIN. It may be worth reading some online resources about the differences though if you find this confusing.

• You will use the portion of the code that follows the ON statement to demonstrate the link between the tables. For example, above, we are telling the query that Technologies.EraType is a match to Eras.EraType. Using that information, SQLiteStudio is able to match those records up when we run our query.

Here are the results of running that query:

As we can see, the report is now "wider," with much more scrolling to the right available. That's because the report contains the data from the Technologies table and the Eras table.


Limiting Results We Get Back
We don't have to stick to a wide table for the results. We can control which columns are displayed by replacing the * with the fields we want to see.

You can simply reference fields by name. As a good coding practice, it's also recommended you include the name of the table from which the field is being referenced. For example:

SELECT Technologies.TechnologyType, Eras.EraType, Eras.GreatPersonBaseCost  FROM Technologies
LEFT JOIN Eras ON Technologies.EraType = Eras.EraType ;

Now we see just 3 columns of data.


Using WHERE Statements
Just like we learned in the previous tutorial on working with SQL, you can include WHERE statements to limit the rows returned. Let's say we only want to see Technologies where the GreatPersonCost is less than 100:

SELECT Technologies.TechnologyType, Eras.EraType, Eras.GreatPersonBaseCost  FROM Technologies
LEFT JOIN Eras ON Technologies.EraType = Eras.EraType
WHERE Eras.GreatPersonBaseCost < 100 ;

One thing to watch out for when including multiple tables in a query is that you explicitly declare which table a field is coming from if both tables have a field with the same name. For example, both Eras and Technologies have a field called EraType. If we forget to include the table name when referring to the field, SQLite will throw an error:

SELECT Technologies.TechnologyType, Eras.EraType, Eras.GreatPersonBaseCost  FROM Technologies
LEFT JOIN Eras ON Technologies.EraType = Eras.EraType
WHERE EraType = 'ERA_MEDIEVAL' ;

You fix this by making the table declaration explicit:

Practical Queries: The Modifiers Tables
Now that we have the basics out of the way, we can talk about some of the more practical queries you will use over and over. Perhaps the most common one you will be using will be reports that refer to the various Modifiers tables throughout the database.

Modifiers are objects that change aspects of the game. Modifiers generally involve at least 2 components at a minimum:

• The Modifier itself, contained in a table called Modifiers
• The linkage of the Modifier, which defines how the Modifier is applied. This is usually in a table with a name that ends in "Modifiers." E.g. "PolicyModifiers" is the table that links Policies and Modifiers. "GovernmentModifiers" is the table that links Governments and Modifiers. "BeliefModifiers" links Beliefs and Modifiers. And so on.

There are additional aspects of Modifiers, but we will talk about them when we get to them.


Writing a Report that Links Modifiers
Because the Modifier system is so consistent, we can find the linkage between a Modifier and how it is applied by performing a simple LEFT JOIN.

SELECT * FROM PolicyModifiers
LEFT JOIN Modifiers on Modifiers.ModifierId = PolicyModifiers.ModifierId

This gives us a report showing all of the Modifiers that are attached to each Policy.


Now, a lot of the time the reason you are writing a report like this is you want to see not just which Modifiers are set up, but also how they are configured. Modifiers are supported by a table called ModifierArguments, that contains all the variables that go along with each Modifier. If we want to see them listed, we can just add ModifierArguments to the join, as so:

SELECT * FROM PolicyModifiers
LEFT JOIN Modifiers on Modifiers.ModifierId = PolicyModifiers.ModifierId
LEFT JOIN ModifierArguments on ModifierArguments.ModifierId = Modifiers.ModifierId

This will generate a report listing every Policy, followed by each attached Modifier, followed by each ModifierArgument attached to that Modifier.

[Note: I've run out of the ability to attach images. More in the follow up to come.]

 

[isau]

When we left off, we had just added the ModifierArguments table to our SQL query. Doing that gives us results like this:

Note the number of times Agoge is now listed in the PolicyType column. That's because what our Report is showing is every combination of matches from the PolicyModifiers, Modifiers, and ModifierArguments tables. Since there are so many ModifierArguments ulimately attached, we get more rows. If we scroll over, we can see the values coming from the ModifierArguments table:

NOTE: You may notice the column name that says ModifierId:2. This is a name automatically created by SQLiteStudio to account for the fact that the column name ModifierID exists on all three tables.



Limiting the Results for More Useful Report
I like to use the LIKE statement to limit report results. In a case like this, I might edit the code as follows:

SELECT * FROM PolicyModifiers
LEFT JOIN Modifiers on Modifiers.ModifierId = PolicyModifiers.ModifierId
LEFT JOIN ModifierArguments on ModifierArguments.ModifierId = Modifiers.ModifierId
WHERE PolicyModifiers.ModifierId LIKE '%aesthetics%' ;

The added LIKE statement with the search term surrounded in '%wildcards%' allows me a bit of flexibility in how I type in the name of the policy. Now that I've got this set up, I can quickly look up how any policy in the game is configured. This is useful when creating new abilities or tweaking existing ones.

Dealing with Recursive Fields
In some cases when working with Modifiers specifically, the view you get from a simple report can be somewhat misleading. That's because some Modifiers are actually broadcast Modifiers--Modifiers that broadcast or "attach" other Modifiers. Typically these Modifiers have names like:

• MODIFIER_ALL_CITIES_ATTACH_MODIFIER
  MODIFIER_ALL_PLAYERS_ATTACH_MODIFIER
  MODIFIER_ALL_UNITS_ATTACH_MODIFIER
  MODIFIER_PLAYER_UNITS_ATTACH_MODIFIER
  MODIFIER_SINGLE_UNIT_ATTACH_MODIFIER
  MODIFIER_PLAYER_CITIES_ATTACH_MODIFIER
  MODIFIER_SINGLE_CITY_ATTACH_MODIFIER
  MODIFIER_PLAYER_CAPTURED_CITY_ATTACH_MODIFIER

Typical examples of broadcast or attach modifiers are City State Bonuses (they are broadcast by the City State to players) and Beliefs (same).

The Beliefs tables offer a lot of these, so let's take a look. Using our standard code from above with minor customizations, we can pull up a list of each Belief's characteristics:

SELECT * FROM BeliefModifiers
LEFT JOIN Modifiers on Modifiers.ModifierId = BeliefModifiers.ModifierId
LEFT JOIN ModifierArguments on ModifierArguments.ModifierId = Modifiers.ModifierId ;

Check out the ModifierType column, and the sheer number of times you see MODIFIER_ALL_CITIES_ATTACH_MODIFIER. When we scroll over to see the ModifierArguments we see this:

This is most likely not the information we're looking for in the Value column of ModifierArguments. A lot of times what we're really looking for is what the actual effects of the religion are once applied. This may be so we can tweak the values, or so we can copy them to create a new ability.

To get ahold of this info, we're going to have to dig a level deeper. What's displaying in the Value column in these cases is actually the name of a Modifier. To get ahold of its values, we have to perform recursion back to the Modifiers table. It sounds challenging but is actually easy.

The trick is to use the AS command to rename the Modifiers and ModifierArguments tables in our query. See below:

SELECT * FROM BeliefModifiers
LEFT JOIN Modifiers on Modifiers.ModifierId = BeliefModifiers.ModifierId
LEFT JOIN ModifierArguments on ModifierArguments.ModifierId = Modifiers.ModifierId
LEFT JOIN Modifiers as m2 on m2.ModifierId = ModifierArguments.Value
LEFT JOIN ModifierArguments as ma2 on ma2.ModifierId = ModifierArguments.Value

By renaming the second references to Modifiers as "m2" and the second reference to ModifierArguments as "ma2" we can refer back to that table and dig a level deeper to see the Modifiers and Arguments the broadcast Modifiers refer to. Scrolling over, we can now see their values:

 

[isau]

Useful Pre-Created Reports
This section isn't so much a tutorial as a list of pre-created reports you can use as a starter. These are some of the reports I keep on hand to help in the rapid development of Quo's Combined Tweaks mod.

Tip: If you don't want to include the WHERE clause included in some of these, in SQLiteStudio just highlight the parts of the code you want to run. The tool will then run only the parts that are selected.


Random Agendas Mapped to Their Modifiers
Note that this can be adapted to HistoricalAgendas by changing the references to that table. Useful if you are modding diplomacy.

select * from randomagendas
left join agendas on agendas.AgendaType = randomagendas.AgendaType
left join agendatraits on agendatraits.AgendaType = randomagendas.AgendaType
left join traits on traits.traittype = agendatraits.traittype
left join traitmodifiers on traitmodifiers.TraitType = traits.TraitType
left join modifiers on modifiers.ModifierId = traitmodifiers.ModifierId
left join modifierarguments on modifierarguments.ModifierId = modifiers.ModifierId

CivilizationTraits to Modifiers
Useful if you want to copy an ability attached to a Civilization.

select * from civilizationtraits
left join traits on civilizationtraits.TraitType = traits.TraitType
left join traitmodifiers on traitmodifiers.TraitType = CIVILIZATIONtraits.TraitType
left join modifiers on modifiers.ModifierId = traitmodifiers.ModifierId
left join modifierarguments on modifierarguments.ModifierId = modifiers.modifierid
where civilizationtraits.civilizationtype like '%RUSSIA%' ;

LeaderTraits to Modifiers
Useful if you want to copy an ability attached to a Leader.

select * from leadertraits
left join traits on leadertraits.TraitType = traits.TraitType
left join traitmodifiers on traitmodifiers.TraitType = leadertraits.TraitType
left join modifiers on modifiers.ModifierId = traitmodifiers.ModifierId
left join modifierarguments on modifierarguments.ModifierId = modifiers.modifierid
where leadertraits.LeaderType like '%CLEOPATRA%' ;

PolicyModifiers
Useful if you want to copy an ability attached to a Policy.

select * from policymodifiers
left join modifiers on modifiers.ModifierId = policymodifiers.ModifierId
left join modifierarguments on modifierarguments.ModifierId = modifiers.modifierid
where policymodifiers.policytype like '%corvee%'

RequirementSets
Useful if you want to drill through a RequirementSet.

select * from requirementsets

left join requirementsetrequirements on requirementsetrequirements.RequirementSetId = requirementsets.RequirementSetId

left join requirements on requirements.requirementid = requirementsetrequirements.RequirementId

left join requirementarguments on requirementarguments.RequirementId = requirements.requirementid

where requirements.RequirementType like '%tech%'

Beliefs (including RequirementSet mappings)
A full drill-down through the Beliefs tables, including mapping through RequirementSets.

select * from beliefs
left join beliefmodifiers on beliefmodifiers.BeliefType = beliefs.BeliefType
left join modifierarguments as MA1 on ma1.ModifierId = beliefmodifiers.ModifierID
left join modifiers on MA1.Value = modifiers.ModifierId
left join modifierarguments as Ma2 on Ma2.ModifierId = MA1.Value
left join requirementsets on requirementsets.RequirementSetId = modifiers.SubjectRequirementSetId
left join requirementsetrequirements on requirementsetrequirements.RequirementSetId = requirementsets.RequirementSetId
left join requirementarguments on requirementarguments.RequirementId = requirementsetrequirements.RequirementId

 

[isau]

[placeholder]

 

[isau]

Bumping this thread from last year. Anyone know a way to petition a mod to add a thread to the Tutorials and References section?

This post could use some fine tuning (I have since discovered how to use the 'using' keyword in SQL) but overall should still be accurate/helpful.

 

[Infixo]

Just ask them and use @...

 

[isau]

Just ask them and use @...

I don't actually know any of their names.

 

[Infixo]

On the main Forum page there is a list "Staff Online" on the right.