Tutorial: Modding with SQLLite Studio (PC)

[isau]

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.

Not a problem. I enjoy helping people out, for many of the same reasons I like providing mods. I enjoy when people are able to see their visions come alive. And the cool thing is, the more you mess with this, the more you'll start to do a lot of this very quickly.

I am not sure if there is a chat channel. Writing may actually be a better way to communicate anyway, since a lot of times it takes some preparation for me to come up with a relevant example and make sure the code I'm providing is accurate.

BTW I do recommend looking around the internet for some SQL tutorials. There is some very useful stuff out there. Keep in mind SQLite used by Civ 6 is a "lighter" version of SQL that is missing some features of the more robust engines. But you'll find a lot of stuff out there, with examples to help you conceptualize what is happening when you run commands like INNER JOIN.

If you have questions that are off topic for this post, you can always ask in the other forums as well. I do try to glance every other day or so to see if I can answer questions there.

 

[richardsvensson]

Thanks a lot for this great introduction to modding with SQL!!

I have a problem though, and hopefully you can help me. I started making some changes, and everything was ok, but when I wanted to add a new Civilization, by adding a row to the Types table, I got the following error message: "no such function make hash".

The thing is, in the Types table there is a (numeric) column called hash. I can't assign a value to it, and if I just leave it out, it tells me that there is no such function make hash.

I 'solved' the problem by changing a city-state to full civ, and everything seemed to be ok. But I ran into the same problem when I wanted to add a leader by adding a row in the Types table.

I looked around and found this in another forum: "There is no such function built into SQLite3." Is there an easy way around this?

Can I add new civs and new leaders without changing the Types table (I have only had problems with that one so far, and it's the only table with a hash-column as far as I know).

Thanks again!

 

[isau]

Thanks a lot for this great introduction to modding with SQL!!

I have a problem though, and hopefully you can help me. I started making some changes, and everything was ok, but when I wanted to add a new Civilization, by adding a row to the Types table, I got the following error message: "no such function make hash".

The thing is, in the Types table there is a (numeric) column called hash. I can't assign a value to it, and if I just leave it out, it tells me that there is no such function make hash.

I 'solved' the problem by changing a city-state to full civ, and everything seemed to be ok. But I ran into the same problem when I wanted to add a leader by adding a row in the Types table.

I looked around and found this in another forum: "There is no such function built into SQLite3." Is there an easy way around this?

Can I add new civs and new leaders without changing the Types table (I have only had problems with that one so far, and it's the only table with a hash-column as far as I know).

Thanks again!

Hiya, in order to insert anything that has a Type, the game itself has to make a Hash record. This is done by performing an INSERT into the Types table like you mentioned. This operation will fail in SQLite but will work in the game.

For example to do for a Trait, you'd do this:

INSERT INTO Types
(Type, Kind)
VALUES ('QUO_TRAIT_FRIENDS_EGYPT', 'KIND_TRAIT' ;

This operation will fail in SQLite but succeed in the game.


Once you have run the game with this insert in place, it will remain in the database until the next time you play a game without your mod loaded. So if you need to do testing, write this INSERT statement in your mod, load the mod up, and then the proper Type record will exist and you can do all the normal INSERTing into other tables.

 

[richardsvensson]

Hiya, in order to insert anything that has a Type, the game itself has to make a Hash record. This is done by performing an INSERT into the Types table like you mentioned. This operation will fail in SQLite but will work in the game.

...

Thanks a lot, perfect!

 

[Drenghbar]

That was exactly what I was looking for to bring my first mod online. I went through a lot of trouble, but you helped me so much ! Thanks a lot, you rock !

 

[Shadole]

Thanks so much for this tutorial. I'm trying to apply the lessons to change city names, but I can't find the Row/Text defines for CityName. If I want to change 'Pokrovka' to 'City A' for example, I'm a little confused.

 

[isau]

Thanks so much for this tutorial. I'm trying to apply the lessons to change city names, but I can't find the Row/Text defines for CityName. If I want to change 'Pokrovka' to 'City A' for example, I'm a little confused.

The display name you see for a City is maintained in the Text database, which is separate from the Gameplay database. What's in the Gameplay database is the tags for each civ. These are in the CityNames table.

In Scythia's case, we can find that civ's tags with this query in SQLite:

SELECT* FROM CityNames where CivilizationType = 'CIVILIZATION_SCYTHIA'

That gives a list of tags we need to update in the text database. To actually do those updates, we need a separate SQL file that specifically targets that database. You do the targeting in the .modinfo--take a look at some example mods to see the structure.


The SQL in the separate SQL file for text would look like this (assuming you are bypassing the language selector system, which I always do since I only speak English):

UPDATE LocalizedText SET Text= 'Happyfunland.' WHERE Tag='LOC_CITY_NAME_POKROVKA' ;

 

[cromcrom]

Thank you so much for this great Tut, you make this world a better place. Cheers

 

[Mettpawwz]

Sorry to bother. I was wondering where the policy descriptions and names are stored and how to edit them in SQL. I checked out the code in Firaxis' example policy mod but that is in XML, and while I could probably make a new policy by changing the relevant bits of their code, I'd rather actually understand where things are coming from and where policy names and descriptions are stored, as well as how they are retrieved. What's confusing me is that there is no foreign key in the "Name" and "Description" columns of the Policies table.

Could you by any chance make a tutorial for how to create a new policy in SQL?

Thanks in advance Tut.

 

[Infixo]

Each object is stored in relevant table that so happens that usually have quite a meaningful name. Policies are stored in Policies table. Primary key is usually named with Type as suffix, e.g. PolicyType.
Names and descriptions for all objects in game are stored in a separate localization DB. So, in Policies table you put only codes to actual names and descriptions. This allow for multiple languages to be used easily. Since it is a separate DB, all text updates happen in a separate mod component (UpdateText instead of UpdateDatabase). Both databases can be changed via xml or normal sql commands.
Later in game you use Local.Lookup() function to retrieve a text associated with given LOC_ string and it returns a language version of the currently used language.
If you want a new object in the game you have to a) register it's primary key (type) b) define an object c) define other related data.
For a - you insert a row into Types table and specify its Kind. For policies it's KIND_POLICY. You can see other kinds in Kinds table.
For b - insert a row into Policies table.
For c - depends on object, e.g. For policies you may want to add modifiers for PolicyModifiers table.
Localized texts are actually optional. The game will use the value in Name/Description field if cannot find a relevant string in localization DB.

 

[Mettpawwz]

Each object is stored in relevant table that so happens that usually have quite a meaningful name. Policies are stored in Policies table.
Names and descriptions for all objects in game are stored in a separate localization DB. So, in Policies table you put only codes to actual names and descriptions. This allow for multiple languages to be used easily. Since it is a separate DB, all text updates happen in a separate mod component (UpdateText instead of UpdateDatabase). Both databases can be changed via xml or normal sql commands.
Later in game you use Local.Lookup() function to retrieve a text associated with given LOC_ string and it returns a language version of the currently used language.

Wait Infixo, so is there actually a way to open the database where the names and descriptions are stored in SQLiteStudio the way you can open the DebugGameplay database? I'm just asking because otherwise I'm basically trying to edit it blind.

 

[Infixo]

DebugLocalization.sqlite in cache folder. But I'm not sure if it's created by default, like DebugGameplay.sqlite. I may have switched it on in some options long time ago when basically enabling all debug related params.

 

[Mettpawwz]

DebugLocalization.sqlite in cache folder. But I'm not sure if it's created by default, like DebugGameplay.sqlite. I may have switched it on in some options long time ago when basically enabling all debug related params.

Thanks a bunch for the help, man! I did have DebugLocalization, but for whatever reason there are no names or descriptions stored within my copy of it as far as I can tell. The only tables are AudioLanguages, DefaultAudioLanguages, FontStyleSheets, LanguagePriorities, Languages, LocalizedText, and SteamLanguages. None of those actually hold the data I'm interested in. Am I overlooking something really obvious?

 

[Infixo]

LocalizedText stores all language-specific versions of texts. Field Language specifies language code (e.g. 'de_DE' for German, etc.), Tag is what you put earlier into your name, description, etc. fields in objects, and Text is the actual text that will be displayed anywhere in the game. There's also Gender and Plurality tu support diffrent handling of those by different languages.
So, what are you exactly looking for in there?

 

[Mettpawwz]

Oh haha thanks a bunch. I didn't realize how enormous the LocalizedText table was (>160k entries), I was seeing names of buildings and wonders in german and wasn't sure why, but it turned out all the rest of the stuff was just further in. Thanks so much! You've been super helpful.

 

[Infixo]

Yes, it's a huge table, very incovinient to look into.

 

[Mettpawwz]

Anyone know what's wrong with the code below? It should work to attach a city production modifier for ancient era Recon units to the Survey policy. And though it does actually edit all the tables (I've checked in SQLite) it doesn't seem to have an effect in game.

It seems to follow all the same format as similar policies (Agoge, Chivalry, etc.), so I have no idea what's wrong.

    --50% Production towards Recon Units.
        --Ancient
        -- PolicyModifiers Table entry
        INSERT INTO PolicyModifiers     (PolicyType, ModifierId)
        VALUES                             ('POLICY_SURVEY', 'SURVEY_ANCIENT_RECON_PRODUCTION');

        -- Modifiers Table entry
        INSERT INTO Modifiers     (ModifierId, ModifierType)
        VALUES                     ('SURVEY_ANCIENT_RECON_PRODUCTION', 'MODIFIER_PLAYER_CITIES_ADJUST_UNIT_TAG_ERA_PRODUCTION');

        -- ModifierArguments Table entries
        INSERT INTO ModifierArguments     (ModifierId, Name, Value, Extra)
        VALUES                             ('SURVEY_ANCIENT_RECON_PRODUCTION', 'UnitPromotionClass', 'PROMOTION_CLASS_RECON', '-1');
        INSERT INTO ModifierArguments     (ModifierId, Name, Value, Extra)
        VALUES                             ('SURVEY_ANCIENT_RECON_PRODUCTION', 'EraType', 'ERA_ANCIENT', '-1');
        INSERT INTO ModifierArguments     (ModifierId, Name, Value, Extra)
        VALUES                             ('SURVEY_ANCIENT_RECON_PRODUCTION', 'Amount', '50', '-1');

I've also tried a variation that just affects all recon units regardless of era as that's actually what I want it to do (it is in the Modifiers table even though it isn't used by any of the units in the base game) but this doesn't work either.

--50% Production towards Recon Units.
        -- PolicyModifiers Table entry
        INSERT INTO PolicyModifiers     (PolicyType, ModifierId)
        VALUES                             ('POLICY_SURVEY', 'SURVEY_RECON_PRODUCTION');

        -- Modifiers Table entry
        INSERT INTO Modifiers     (ModifierId, ModifierType)
        VALUES                     ('SURVEY_RECON_PRODUCTION', 'MODIFIER_PLAYER_CITIES_ADJUST_UNIT_PRODUCTION');

        -- ModifierArguments Table entries
        INSERT INTO ModifierArguments     (ModifierId, Name, Value, Extra)
        VALUES                             ('SURVEY_RECON_PRODUCTION', 'UnitPromotionClass', 'PROMOTION_CLASS_RECON', '-1');
        INSERT INTO ModifierArguments     (ModifierId, Name, Value, Extra)
        VALUES                             ('SURVEY_RECON_PRODUCTION', 'Amount', '50', '-1');

Thanks in advance.

 

[Infixo]

There's a log for modifiers, GameEffects.log. It must be switched on however in UserOptions.txt. Set logging to Detailed or Diagnostics, run the game and see what is says about your modifiers.
I suggest you later turn it off or set logging to Minimal, it tends to output huge amounts of data.

Edit. And you can also check in FireTuner if those modifiers are attached to anything...

 

[Mettpawwz]

Thanks for the tip!

These were all the entries (using the SURVEY_ANCIENT_RECON_PRODUCTION modifier) in the GameEffects.log in a game where I tried to test it:

[Turn: 1] Modifier <SURVEY_ANCIENT_RECON_PRODUCTION> is valid.
[Turn: 7] Attaching Modifier <341:SURVEY_ANCIENT_RECON_PRODUCTION> to <Player: 2>.
[Turn: 7] Applying effect from <341:SURVEY_ANCIENT_RECON_PRODUCTION> to <City (65536), Owner: 2, Name: LOC_CITY_NAME_ZANZIBAR>.
[Turn: 7] Attaching Modifier <344:SURVEY_ANCIENT_RECON_PRODUCTION> to <Player: 3>.
[Turn: 7] Applying effect from <344:SURVEY_ANCIENT_RECON_PRODUCTION> to <City (65536), Owner: 3, Name: LOC_CITY_NAME_JAKARTA>.
[Turn: 7] Attaching Modifier <347:SURVEY_ANCIENT_RECON_PRODUCTION> to <Player: 4>.
[Turn: 7] Applying effect from <347:SURVEY_ANCIENT_RECON_PRODUCTION> to <City (65536), Owner: 4, Name: LOC_CITY_NAME_ANTANANARIVO>.
[Turn: 8] Attaching Modifier <352:SURVEY_ANCIENT_RECON_PRODUCTION> to <Player: 0>.
[Turn: 8] Applying effect from <352:SURVEY_ANCIENT_RECON_PRODUCTION> to <City (65536), Owner: 0, Name: LOC_CITY_NAME_ROME>.
[Turn: 1] Detaching Modifier <341:SURVEY_ANCIENT_RECON_PRODUCTION> from <Player: 2>.
[Turn: 1] Detaching Modifier <344:SURVEY_ANCIENT_RECON_PRODUCTION> from <Player: 3>.
[Turn: 1] Detaching Modifier <347:SURVEY_ANCIENT_RECON_PRODUCTION> from <Player: 4>.
[Turn: 1] Detaching Modifier <352:SURVEY_ANCIENT_RECON_PRODUCTION> from <Player: 0>.

I should mention that I was Rome in this game.

They appeared in that order. I can't really understand what the problem seems to be. Aside from the fact that the production cost of scouts remains unchanged in-game (30 hammers), everything seems to be correct. I'm super sorry to be bothering you a bunch Infixo, I'm still just very new to modding.

 

[isau]

Describe what you mean

Thanks for the tip!

These were all the entries (using the SURVEY_ANCIENT_RECON_PRODUCTION modifier) in the GameEffects.log in a game where I tried to test it:

[Turn: 1] Modifier <SURVEY_ANCIENT_RECON_PRODUCTION> is valid.
[Turn: 7] Attaching Modifier <341:SURVEY_ANCIENT_RECON_PRODUCTION> to <Player: 2>.
[Turn: 7] Applying effect from <341:SURVEY_ANCIENT_RECON_PRODUCTION> to <City (65536), Owner: 2, Name: LOC_CITY_NAME_ZANZIBAR>.
[Turn: 7] Attaching Modifier <344:SURVEY_ANCIENT_RECON_PRODUCTION> to <Player: 3>.
[Turn: 7] Applying effect from <344:SURVEY_ANCIENT_RECON_PRODUCTION> to <City (65536), Owner: 3, Name: LOC_CITY_NAME_JAKARTA>.
[Turn: 7] Attaching Modifier <347:SURVEY_ANCIENT_RECON_PRODUCTION> to <Player: 4>.
[Turn: 7] Applying effect from <347:SURVEY_ANCIENT_RECON_PRODUCTION> to <City (65536), Owner: 4, Name: LOC_CITY_NAME_ANTANANARIVO>.
[Turn: 8] Attaching Modifier <352:SURVEY_ANCIENT_RECON_PRODUCTION> to <Player: 0>.
[Turn: 8] Applying effect from <352:SURVEY_ANCIENT_RECON_PRODUCTION> to <City (65536), Owner: 0, Name: LOC_CITY_NAME_ROME>.
[Turn: 1] Detaching Modifier <341:SURVEY_ANCIENT_RECON_PRODUCTION> from <Player: 2>.
[Turn: 1] Detaching Modifier <344:SURVEY_ANCIENT_RECON_PRODUCTION> from <Player: 3>.
[Turn: 1] Detaching Modifier <347:SURVEY_ANCIENT_RECON_PRODUCTION> from <Player: 4>.
[Turn: 1] Detaching Modifier <352:SURVEY_ANCIENT_RECON_PRODUCTION> from <Player: 0>.

I should mention that I was Rome in this game.

They appeared in that order. I can't really understand what the problem seems to be. Aside from the fact that the production cost of scouts remains unchanged in-game (30 hammers), everything seems to be correct. I'm super sorry to be bothering you a bunch Infixo, I'm still just very new to modding.

How did you go about testing to see whether it worked? The modifier increases Production toward a build item, which is different than reducing its cost, although a lot of people tend to still think of it as "half off the cost." The cost of the unit actually stays the same and whats multiplied is the production toward completing it, which can be difficult to detect in the UI. To test it I sometimes set the production modifier to something outrageous like 9999% and just make sure the build time drops to 1 turn. If it does, then I feelc comfortable changing it back to 50 or 100 and assume it is working.