1. We have added the ability to collapse/expand forum categories and widgets on forum home.
    Dismiss Notice
  2. All Civ avatars are brought back and available for selection in the Avatar Gallery! There are 945 avatars total.
    Dismiss Notice
  3. To make the site more secure, we have installed SSL certificates and enabled HTTPS for both the main site and forums.
    Dismiss Notice
  4. Civ6 is released! Order now! (Amazon US | Amazon UK | Amazon CA | Amazon DE | Amazon FR)
    Dismiss Notice
  5. Dismiss Notice
  6. Forum account upgrades are available for ad-free browsing.
    Dismiss Notice

Tutorial: Introduction to Report Writing with SQLite Studio

Discussion in 'Civ6 - Creation & Customization' started by isau, Mar 6, 2018.

  1. isau

    isau Warlord

    Joined:
    Jan 15, 2007
    Messages:
    2,750
    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:


    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:

    upload_2018-3-6_4-2-49.png

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

    upload_2018-3-6_4-10-34.png


    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:

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

    upload_2018-3-6_4-20-8.png

    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:

    Code:
    SELECT Technologies.TechnologyType, Eras.EraType, Eras.GreatPersonBaseCost  FROM Technologies
    LEFT JOIN Eras ON Technologies.EraType = Eras.EraType ;
    upload_2018-3-6_4-23-6.png

    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:

    Code:
    SELECT Technologies.TechnologyType, Eras.EraType, Eras.GreatPersonBaseCost  FROM Technologies
    LEFT JOIN Eras ON Technologies.EraType = Eras.EraType
    WHERE Eras.GreatPersonBaseCost < 100 ;
    upload_2018-3-6_4-26-8.png



    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:

    Code:
    SELECT Technologies.TechnologyType, Eras.EraType, Eras.GreatPersonBaseCost  FROM Technologies
    LEFT JOIN Eras ON Technologies.EraType = Eras.EraType
    WHERE EraType = 'ERA_MEDIEVAL' ;
    upload_2018-3-6_4-28-49.png


    You fix this by making the table declaration explicit:

    upload_2018-3-6_4-30-18.png



    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.

    Code:
    SELECT * FROM PolicyModifiers
    LEFT JOIN Modifiers on Modifiers.ModifierId = PolicyModifiers.ModifierId
    
    upload_2018-3-6_4-37-29.png


    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:

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

    Attached Files:

    qqqbbb likes this.
  2. isau

    isau Warlord

    Joined:
    Jan 15, 2007
    Messages:
    2,750
    When we left off, we had just added the ModifierArguments table to our SQL query. Doing that gives us results like this:

    upload_2018-3-6_4-47-33.png


    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:

    upload_2018-3-6_4-49-31.png


    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:

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

    upload_2018-3-6_4-54-17.png



    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:

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

    upload_2018-3-6_5-6-18.png


    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:

    upload_2018-3-6_5-7-29.png


    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:

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

    upload_2018-3-6_5-12-50.png
     
    Last edited: Mar 6, 2018
  3. isau

    isau Warlord

    Joined:
    Jan 15, 2007
    Messages:
    2,750
    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.

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

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

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

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

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

    Code:
    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 
     
    Last edited: Mar 6, 2018
  4. isau

    isau Warlord

    Joined:
    Jan 15, 2007
    Messages:
    2,750
    [placeholder]
     

Share This Page

Ebates: Get Paid to Shop