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: Modding with SQLLite Studio (PC)

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

  1. isau

    isau Warlord

    Joined:
    Jan 15, 2007
    Messages:
    2,733

    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.
     
  2. richardsvensson

    richardsvensson Chieftain

    Joined:
    Sep 25, 2010
    Messages:
    32
    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!
     
  3. isau

    isau Warlord

    Joined:
    Jan 15, 2007
    Messages:
    2,733

    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.
     
  4. richardsvensson

    richardsvensson Chieftain

    Joined:
    Sep 25, 2010
    Messages:
    32
    Thanks a lot, perfect!
     
  5. Drenghbar

    Drenghbar Chieftain

    Joined:
    Feb 27, 2017
    Messages:
    3
    Gender:
    Male
    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 !
     
  6. Shadole

    Shadole Chieftain

    Joined:
    Aug 7, 2015
    Messages:
    109
    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.
     
  7. isau

    isau Warlord

    Joined:
    Jan 15, 2007
    Messages:
    2,733

    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'


    upload_2017-3-8_16-20-40.png


    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' ;
     

    Attached Files:

  8. cromcrom

    cromcrom Cernu

    Joined:
    Nov 11, 2005
    Messages:
    268
    Gender:
    Male
    Location:
    Chateauroux
    Thank you so much for this great Tut, you make this world a better place. Cheers :)
     
  9. Mettpawwz

    Mettpawwz Chieftain

    Joined:
    Feb 4, 2017
    Messages:
    66
    Gender:
    Male
    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.
     
  10. Infixo

    Infixo Warlord

    Joined:
    Jan 9, 2016
    Messages:
    2,207
    Gender:
    Male
    Location:
    Warsaw
    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.
     
    Last edited: Apr 16, 2017
  11. Mettpawwz

    Mettpawwz Chieftain

    Joined:
    Feb 4, 2017
    Messages:
    66
    Gender:
    Male
    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.
     
  12. Infixo

    Infixo Warlord

    Joined:
    Jan 9, 2016
    Messages:
    2,207
    Gender:
    Male
    Location:
    Warsaw
    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.
     
  13. Mettpawwz

    Mettpawwz Chieftain

    Joined:
    Feb 4, 2017
    Messages:
    66
    Gender:
    Male
    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?
     
  14. Infixo

    Infixo Warlord

    Joined:
    Jan 9, 2016
    Messages:
    2,207
    Gender:
    Male
    Location:
    Warsaw
    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?
     
  15. Mettpawwz

    Mettpawwz Chieftain

    Joined:
    Feb 4, 2017
    Messages:
    66
    Gender:
    Male
    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. :p
     
  16. Infixo

    Infixo Warlord

    Joined:
    Jan 9, 2016
    Messages:
    2,207
    Gender:
    Male
    Location:
    Warsaw
  17. Mettpawwz

    Mettpawwz Chieftain

    Joined:
    Feb 4, 2017
    Messages:
    66
    Gender:
    Male
    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.

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

    Code:
    --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.
     
    Last edited: Apr 17, 2017
  18. Infixo

    Infixo Warlord

    Joined:
    Jan 9, 2016
    Messages:
    2,207
    Gender:
    Male
    Location:
    Warsaw
    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...
     
  19. Mettpawwz

    Mettpawwz Chieftain

    Joined:
    Feb 4, 2017
    Messages:
    66
    Gender:
    Male
    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.
     
  20. isau

    isau Warlord

    Joined:
    Jan 15, 2007
    Messages:
    2,733
    Describe what you mean

    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.
     

Share This Page