Tutorial: Modding with SQLLite Studio (PC)

[Mettpawwz]

I just checked again, this time setting the amount to '100000', and a scout still took 8 turns to complete (no change from the baseline). There has to be something else wrong but I've spent like 3 hours looking at those few lines of code and nothing seems out of place. I'm completely stumped.

 

[Infixo]

@Mettpawwz Be aware that the lines in-between those are also important. If you set logging level to 3/4 it says much more, e.g. if applying was actually successfull. I'm not saying that it will tell you that, just that various modifiers print out much more info.
I find it curious that you got that modifier at turn 8 - was it at the time when you actually started building the scout? If so, there should be a line before that stating that the production has begun, and as a result the modifier was attached.
Those logs are really very detailed.

Still - everything seems ok. Also a good comment from @isau - Production change could be hard to detect. So, I am lost here as well.

 

[isau]

Have you tried reversing the order of the SQL INSERT? Right now you're attaching Modifier to the Policy prior to adding ModifierArguments. I don't know if this impacts the game in any way. However, I always try to make sure my objects are "complete" before attaching them just in case. I always go in this order: create Modifier on Modifiers table, add ModifierArguments on ModifierArguments table, then as a final step attach the Modifier to whatever. I don't know how the game "expands" the objects after the fact but it feels safer for me for the arguments to be there prior to the modifier being attached.

 

[Mettpawwz]

First off, I can't even tell you guys how grateful I am for your suggestions. It's so awesome that the Civ franchise has such a friendly and helpful community. It makes trying to get into modding that much less daunting.

I've tried switching around the order of the SQL statements as you suggested Isau, but it didn't fix it sadly. Other policy modifiers I've done have worked fine in that order as well so I think that can hopefully be ruled out. Somebody suggested that it might be because in SQL integers don't need apostrophes (i.e. 10000 rather than '10000', and -1 rather than '-1'). I've tried both variations of that, but neither seems to work.

Finally, I've tried running the following SQL statement on its own:

UPDATE ModifierArguments SET Value=100000 where ModifierId='AGOGE_ANCIENT_MELEE_PRODUCTION' AND Name='Amount';

In order to test whether I can actually change the value of AGOGE, and neither the version with apostrophies nor without actually seemed to reduce the time to train of warriors down to 1 turn, which it should with a 100,000x modifier. Oddly enough though, it did reduce the build time (in my ''Quick" speed game) the build time of warriors down from 4 turns to 3 turns. The entry in the ModifierArguments table looks like this though:

http://imgur.com/a/R8v2j

So it is definitely writing into the table.

Could it possibly be that the particular ModifierType MODIFIER_PLAYER_CITIES_ADJUST_UNIT_TAG_ERA_PRODUCTION is actually just broken in the base game?

 

[Mettpawwz]

Just an update, from my testing (and what it said in the GameEffects Log) it seems that the value never actually is recognized by the game as changing even though it changes in the tables. Basically, no matter what number gets put in for Agoge, the value of the modifier is always 50% increased production. And for a new modifier like I made for scouts (from Survey) it just stays at 0 no matter what you put in and what gets displayed in the ModifierArguments table.

Does anyone know of any mods where someone changed something like this and had it actually work?

 

[isau]

Just an update, from my testing (and what it said in the GameEffects Log) it seems that the value never actually is recognized by the game as changing even though it changes in the tables. Basically, no matter what number gets put in for Agoge, the value of the modifier is always 50% increased production. And for a new modifier like I made for scouts (from Survey) it just stays at 0 no matter what you put in and what gets displayed in the ModifierArguments table.

Does anyone know of any mods where someone changed something like this and had it actually work?

I'm fairly sure Catherine's 100% production to Spies in my mod works but now I want to go back and test to be sure its actually working. It's a direct Unit Type buff though and not a class of units:

INSERT INTO Modifiers
    (ModifierId, ModifierType, RunOnce, Permanent, OwnerRequirementSetId, SubjectRequirementSetId)
VALUES    ('QUO_FRANCE_CHEAP_SPY', 'MODIFIER_PLAYER_CITIES_ADJUST_UNIT_PRODUCTION', 0, 0, NULL, NULL) ;


INSERT INTO ModifierArguments
    (ModifierId,             Name,         Type,             Value,             Extra,     SecondExtra)
VALUES    ('QUO_FRANCE_CHEAP_SPY',     'Amount',     'ARGTYPE_IDENTITY',     '100',            NULL,     NULL) ,
    ('QUO_FRANCE_CHEAP_SPY',     'UnitType',     'ARGTYPE_IDENTITY',     'UNIT_SPY',    NULL,     NULL) ;


INSERT INTO TraitModifiers
    (TraitType,             ModifierID)
VALUES    ('FLYING_SQUADRON_TRAIT',    'QUO_FRANCE_CHEAP_SPY') ;

I also have a Belief that is supposed to raise build speed of Ranged units of any era, that uses this format:

INSERT INTO Modifiers
    (ModifierId, ModifierType, RunOnce, Permanent, OwnerRequirementSetId, SubjectRequirementSetId)
VALUES    ('QUO_GODDESS_HUNT', 'MODIFIER_ALL_CITIES_ATTACH_MODIFIER', 0, 0, NULL, 'CITY_FOLLOWS_PANTHEON_REQUIREMENTS'),
    ('QUO_GODDESS_HUNT_2', 'MODIFIER_PLAYER_CITIES_ADJUST_MILITARY_UNITS_PRODUCTION', 0, 0, NULL, NULL) ;


INSERT INTO ModifierArguments
    (ModifierId,             Name,             Type,             Value,                 Extra,     SecondExtra)
VALUES    ('QUO_GODDESS_HUNT',     'ModifierId',         'ARGTYPE_IDENTITY',     'QUO_GODDESS_HUNT_2',        NULL,     NULL) ,
    ('QUO_GODDESS_HUNT_2',     'Amount',         'ARGTYPE_IDENTITY',     '15',                NULL,     NULL) , 
    ('QUO_GODDESS_HUNT_2',     'PromotionClass',     'ARGTYPE_IDENTITY',     'PROMOTION_CLASS_RANGED',    NULL,     NULL) ;



INSERT INTO BeliefModifiers
    (BeliefType,             ModifierID)
VALUES    ('BELIEF_GODDESS_OF_THE_HUNT',    'QUO_GODDESS_HUNT');

 

[Mettpawwz]

Isau, I tried running your code for your Goddess of the Hunt belief and I think it isn't working. I started building an archer in a game the turn before getting a pantheon and checked the production I got towards the archer per turn after choosing goddess of the hunt. Then I reloaded to the turn before getting a pantheon and tried again with a different belief, and it gave the same amount of hammers per turn towards the archer completion.

I'm going to bed I think, it's almost 1 am here, but if anyone else finds a solution or workaround to this issue that would be awesome.

 

[Mettpawwz]

After a lot of testing, this is the only thing that has worked (actually reduces the build time of any unit it is applied to to 1 turn):

        INSERT INTO Modifiers     (ModifierId, ModifierType)
        VALUES                    ('SURVEY_RECON_PRODUCTION', 'MODIFIER_PLAYER_UNITS_ADJUST_UNIT_PRODUCTION');
      
        INSERT INTO ModifierArguments    (ModifierId, Name, Value)
        VALUES                            ('SURVEY_RECON_PRODUCTION', 'Amount', 10000000000),
                                        ('SURVEY_RECON_PRODUCTION', 'UnitType', 'UNIT_SCOUT');
                                      
        INSERT INTO PolicyModifiers    (PolicyType, ModifierId)
        VALUES                         ('POLICY_SURVEY', 'SURVEY_RECON_PRODUCTION');

Which is bizarre, because the collection that MODIFIER_PLAYER_UNITS_ADJUST_UNIT_PRODUCTION acts on is COLLECTION_OWNER, which would mean that it acts on the policy itself rather than acting on player cities like MODIFIER_PLAYER_CITIES_ADJUST_UNIT_TAG_ERA_PRODUCTION (which acts on COLLECTION_PLAYER_CITIES).

I really dislike this workaround though because it will make any policy made this way incompatible with mods like MOAR UNITS which add additional units not covered by this code. I've tried replacing the UnitType='UNIT_SCOUT' with PromotionClass='PROMOTION_CLASS_RECON' but apparently that argument is invalid for this effect (according to the GameEffects log).

I think I'm going to give up on this for now and move on to working on other parts of the mod I'm making but if anyone actually figures out a way (and tests it to make sure it works) to edit the unit production modifier for an entire promotion group rather than a single unit type, please let me know.

Thanks again for everyone who helped out!

 

[Infixo]

When you are working with policies or beliefs, you may want to use effect_attach_modifier. Like 2-step approach. Since modifier cannot act on policy or belief itself, it's better this way to avoid confusion and unexplained behaviors.

 

[Magil]

I think I'm going to give up on this for now and move on to working on other parts of the mod I'm making but if anyone actually figures out a way (and tests it to make sure it works) to edit the unit production modifier for an entire promotion group rather than a single unit type, please let me know.

Thanks again for everyone who helped out!

It worked fine for me when I added a modifier to Agoge to make it apply to anti-cavalry units. I cranked it up to 9999 just to be sure, but for sure it's working (just now ran a test and it resulted in 1-turn Spearmen with tons of overflow). The relevant bits of code:

INSERT INTO Modifiers
   (ModifierId,   ModifierType,   RunOnce,   Permanent,   OwnerRequirementSetId,   SubjectRequirementSetId)
VALUES  
    ('MAGIL_AGOGE_ANCIENT_ANTICAV_PRODUCTION', 'MODIFIER_PLAYER_CITIES_ADJUST_UNIT_TAG_ERA_PRODUCTION', 0, 0, NULL, NULL);
--Modifier Arguments
INSERT INTO ModifierArguments
   (ModifierId,   Name,   Value,   Extra,   SecondExtra)
VALUES  
    ('MAGIL_AGOGE_ANCIENT_ANTICAV_PRODUCTION', 'UnitPromotionClass', 'PROMOTION_CLASS_ANTI_CAVALRY', -1, NULL) ,      
   ('MAGIL_AGOGE_ANCIENT_ANTICAV_PRODUCTION', 'EraType', 'ERA_ANCIENT', -1, NULL) ,      
   ('MAGIL_AGOGE_ANCIENT_ANTICAV_PRODUCTION', 'Amount', '9999', -1, NULL) ;

I attached to the policy in a second file via XML though I doubt that matters:

       <Row>
           <PolicyType>POLICY_AGOGE</PolicyType>
           <ModifierId>MAGIL_AGOGE_ANCIENT_ANTICAV_PRODUCTION</ModifierId>
       </Row>

At a glance I couldn't tell you why yours isn't working. I use Notepad++ to write my mods rather than SQLite Studio. I'd make absolutely sure it wasn't some other bit of code conflicting.

 

[isau]

Just to check to see that every object is receiving the values you expect it to, would you mind running the following code in SQLite Studio and posting the results? It will be several columns long so if you can export to excel or to a text file, or just take a pic that shows all the columns and paste that, that would be very helpful.

-- perform a select that grabs all modifiers and their modifierarguments connected to the Survey policy
select * from policymodifiers
left join modifiers on modifiers.ModifierId = policymodifiers.ModifierId
left join modifierarguments on modifierarguments.ModifierId = modifiers.ModifierId
where policymodifiers.PolicyType='POLICY_SURVEY' ;

 

[Mettpawwz]

Okay Magil, so after a few more hours of testing I think I finally have the answer. I literally copy-pasted your code and it worked fine when applied to PROMOTION_CLASS_ANTI_CAVALRY (for spearmen) and PROMOTION_CLASS_HEAVY_CAVALRY (for Heavy Chariot). However it does not work when applied to PROMOTION_CLASS_MELEE (for warrior), or when applied to PROMOTION_CLASS_RECON (for Scout).

The most enlightening example however is PROMOTION_CLASS_RANGED, because in the Ancient Era there are 2 units that belong to it (Slinger and Archer).
When applied to PROMOTION_CLASS_RANGED your code works for Archers, but not for Slingers.

I thought that perhaps the game uses the PrereqTech field to assign an era to a unit (since it isn't a column in the Units table), and after setting the PrereqTech of Slingers to TECH_POTTERY the policy did actually affect Slingers too.

So something is fundamentally wrong in the base game with the TAG_ERA effects when it comes to starting units, or that's what it seems like at any rate. Modifiers have to be individually applied to them.

I'm glad this finally got sorted out. It's been driving me crazy for a couple days now.

EDIT:

There you go, Isau, here's my SQL table output after running that join. It looks okay, so I think it's the issue I just mentioned.

http://imgur.com/a/25RiC

 

[Magil]

So it wouldn't apply to units that don't have Prerequisite Tech (or Civic)? I suppose that makes sense on some level.

 

[isau]

Ah. So the issue could be that since Scouts have no pre-req tech, the Scout unit basically also has no Era?


If that's the case and you want to account not just for the actual Scout unit but for anything Scout like (like a unique Scout unit added by mods) you could do a dynamic insert. This is how I approach stuff like World Wonders and many operations involving leaders, because I'm often not aware of what stuff people are installing with mods and need to do a database search. I was going to write something up on that anyway, so here's an explanation of how it would work here.

-- Create a table and populate it with a list of units that are similar to a Scout
-- Refine this select statement to meet the needs of the kind of unit you are trying to operate on
CREATE TABLE tblScoutLikeUnits AS SELECT units.UnitType
                                    FROM units
                                   WHERE units.PrereqCivic IS NULL AND
                                         units.PrereqTech IS NULL AND
                                         units.PromotionClass = 'PROMOTION_CLASS_RECON';


-- Use a SELECT statement to dynamically insert into the Modifiers table, for every unit we put in tblScoutLikeUnits
        INSERT INTO Modifiers     (ModifierId, ModifierType)
        SELECT 'SURVEY_RECON_PRODUCTION_' || tblScoutLikeUnits.UnitType , 'MODIFIER_PLAYER_UNITS_ADJUST_UNIT_PRODUCTION'
        FROM tblScoutLikeUnits ;
      
        INSERT INTO ModifierArguments    (ModifierId, Name, Value)
        SELECT                            'SURVEY_RECON_PRODUCTION_' || tblScoutLikeUnits.UnitType, 'Amount', 10000000000
                                FROM tblScoutLikeUnits ;
                                
        
        INSERT INTO ModifierArguments    (ModifierId, Name, Value)
        SELECT                            'SURVEY_RECON_PRODUCTION_' || tblScoutLikeUnits.UnitType, 'UnitType', 'UNIT_SCOUT'
                                FROM tblScoutLikeUnits ;

                                      
        INSERT INTO PolicyModifiers    (PolicyType, ModifierId)
        SELECT                         'POLICY_SURVEY', 'SURVEY_RECON_PRODUCTION_' || tblScoutLikeUnits.UnitType
                           FROM tblScoutLikeUnits ;

Basically the code above is similar to what you're already doing, it just dynamically looks up every unit that has characteristics of a Scout. Rather than being named 'SURVEY_RECON_PRODUCTION', you're now creating a modifier that is a concatenation of the unit name (e.g. 'SURVEY_RECON_PRODUCTION_UNIT_SCOUT)."

Here are the results of doing search for the created ModifierArguments after running the code (by searching ModifierArguments using a LIKE tag to find anything with a name close to what we added).

The downside of doing this is it increases the total number of Modifiers, which at some point could begin to bog down the game. So just be aware of that.


Also FYI--I didn't mention it before, but I highly recommend changing your modifier names to include a tag of some kind. Every database object I create has the tag QUO_ in front of it. That's so that I can clearly see what my mod is adding, and easily perform searches to investigate problems. I just type in:

SELECT * FROM ModifierArguments WHERE ModifierId LIKE '%QUO%' ;

...and I can see all the ModifierArguments I've ever created. It's hugely handy as the mod continues to grow. Pick an unlikely set of characters to make searching easy and to avoid conflicts with other modders. The code works whether you do it this way or not but I find it so convenient, and it makes it MUCH easier to combine two mods together.

 

[Mettpawwz]

Oh wow, that's awesome Isau!

It's so good to know there is actually a method like the one you described to get around this issue. I don't think in this particular instance I will use it since it just means that the Ancient Era production-granting policies won't affect new starting units (and I've never really seen a mod that adds starting units, plus the base game doesn't have this functionality either), but I'm happy to learn of the things you can do with SQL at a slightly more advanced level that I hadn't even thought of.

Thanks for the tip regarding adding an identifyer. I'm definitely going to do it now.

Just thanks so much for being so helpful and awesome, seriously. This tutorial on SQL helped me out so much with getting started with Civ 6 modding and this community has so far been incredible with giving pointers and answering questions.

 

[Mettpawwz]

Heya, so I was wondering if there is any way to open the icon database in SQLiteStudio. Alternatively, is there another way to figure out what the index number of any given icon is so I can reuse some of the base game's icons for new buildings? It seems incredibly tedious to just have to do it by trial and error every time I add a new building.

 

[Infixo]

Each icon is defined in xml. Just look in game's base files.
You can't open icons database because there isn't one. They are loaded into VFS which is stored in-memory. That's why we you use Imports component.

 

[Mettpawwz]

Cool, thanks! I found the index definitions for icons under .../Base/Assets/UI/Icons

 

[evmcmunn]

okay this has been helpful so far but i got a couple questions, how do u make a modinfo file and when you are done with whatever you are doing do you save your work to a sql file that is automatically separate? i ask cause i dont want to mess anything up and im new to civ 6 stuff. The real meat of my problem is Im trying to add repeatable future techs at the end of the tech tree, and I dont know how to do that. As you can see I have a long way to go, i learned how to do it in civ 4 but its a different beast with civ 6.

 

[evmcmunn]

I ran across another problem if you don't mind tackling this one, I can't get the improvement_bonusyieldchanges tab or whatever to populate because of some foreign key issue. I try to make a reference to the right table and column but the only option that looks close seems to be "dictionary" and it just gives me the gamedebug file thing, not a list of tables or specific columns. I hope thats enough info for you.