[R&F] Why is my SQL not working?!

[Question]

Getting very frustrated over this. Im trying to use UPDATE to update some simple values, but it is simply not taking effect.

What's going on?

UPDATE ModifierArguments
SET    Value = 5
WHERE  ModifierId  = 'TRAIT_TOQUI_COMBAT_BONUS_VS_GOLDEN_AGE_CIV';

UPDATE LocalizedText
SET    Text = 'All units trained in cities with an established [ICON_Governor] Governor gain 25% more experience in combat. +5 [ICON_Strength] Combat Strength bonus against civilizations that are in a Golden Age.'
WHERE  Tag  = 'LOC_TRAIT_CIVILIZATION_MAPUCHE_TOQUI_DESCRIPTION';

Seems pretty simple...yet it will not work...Mapuche still has +10 vs golden age civs...

 

[TheLunarArmy]

What does Database.log say?

 

[Question]

The only error i can find in it is this :

[1050425.802] [Gameplay] ERROR: no such table: LocalizedText

Which I think is complaining about the LocalizedText update for the BetterBeliefs mod...

 

[Question]

I even tried editing the XML directly :

 <Row>
           <ModifierId>TRAIT_TOQUI_COMBAT_BONUS_VS_GOLDEN_AGE_CIV</ModifierId>
           <Name>Amount</Name>
           <Value>5</Value>
       </Row>

And nope...still +10 combat strength after loading a save...

 

[TheLunarArmy]

I would try and debug this issue. I think, and I stand to be corrected, but any error causes complete load failure afterwards. So I think it may be preventing the rest of your SQL files from executing, which might be the same problem you're having with the Encampment Production problem.

 

[Question]

OK, but how am i supposed to tell which file...or which line even....is causing the issue?

 

[TheLunarArmy]

For one, disable all other mods you're using. Then its process of elimination.
However, I think I may have found your problem.
You need to make sure you are updating the correct database with the correct SQL commands.
The [1050425.802] [Gameplay] ERROR: no such table: LocalizedText error is caused by modbuddy attempting to add a record into the Gameplay database when it should be uploading into the Localization database.

To fix this you need to separate your .sql file into two. Name one TextUpdate.sql and the other GameplayUpdate.sql.

In TextUpdate.sql put:

UPDATE LocalizedText
SET    Text = 'All units trained in cities with an established [ICON_Governor] Governor gain 25% more experience in combat. +5 [ICON_Strength] Combat Strength bonus against civilizations that are in a Golden Age.'
WHERE  Tag  = 'LOC_TRAIT_CIVILIZATION_MAPUCHE_TOQUI_DESCRIPTION';

And in GameplayUpdate.sql put:

UPDATE ModifierArguments
SET    Value = 5
WHERE  ModifierId  = 'TRAIT_TOQUI_COMBAT_BONUS_VS_GOLDEN_AGE_CIV';

Now make sure you load the correct file in the correct database, right click on Solution → Properties → FrontEnd Actions* → Add Action → (select New Action) → (Type → UpdateDatabase) → Add → (File → GameplayUpdate.sql → OK) → Add Action → (select New Action) → (Type → UpdateText) → Add → (File → TextUpdate.sql → OK) → Save All → Rebuild.

* = You may need to repeat this for In-Game Actions too

 

[TheLunarArmy]

Correct?

 

[Question]

Thanks, I can see the text updates, but no gameplay related changes seem to work at all.

For example, I did this :

UPDATE Units
SET    Combat = 36, RangedCombat = 25
WHERE  UnitType  = 'UNIT_PERSIAN_IMMORTAL';

I started a new game, checked the civlopedia, and the immortal combat strength is still the same...

Edit : OK just to make sure....for a SQL entry like that, I do NOT have to restart the game to see it take effect right? I can just start a new game or load a save?

 

[Question]

Yea i have no clue whats going on, even when i edit the SQL for other mods, the code that i paste in has no effect at all, although the base mod works.

I downloaded this mod : https://forums.civfanatics.com/resources/constant-settler-cost.26506/

Which has

UPDATE Units SET Cost='100' WHERE UnitType='UNIT_SETTLER';

I changed the 100 to 1000 and the change is reflected in game.

But when I did this :

UPDATE Units SET Cost='1000' WHERE UnitType='UNIT_SETTLER';

UPDATE Units SET Cost='1000' WHERE UnitType='UNIT_PERSIAN_IMMORTAL';

Immortals have no change at all...

I really dont get it. Why does it work for settlers but not immortals?

 

[LeeS]

it's probably a loading order issue. Settlers are part of the base game and therefore any code witihn mods adjusting Settlers does so after the Settler Unit is added to the database.

The Immortal is added by a DLC and therefore the order the DLC loads as compared to the mod comes into play. In Civ6 DLC are treated the same as another mod, and are not necessarily loaded into the game's database before the code from any mod. So your update statement for the Immortal might be failing because there is no match as yet when the SQL statement executes

WHERE UnitType='UNIT_PERSIAN_IMMORTAL'

Afterwards, the Macedon/Persia DLC would load the Immortal into the game, but by that time it is too late for the SQL line to succeed.

 

[Question]

it's probably a loading order issue. Settlers are part of the base game and therefore any code witihn mods adjusting Settlers does so after the Settler Unit is added to the database.

The Immortal is added by a DLC and therefore the order the DLC loads as compared to the mod comes into play. In Civ6 DLC are treated the same as another mod, and are not necessarily loaded into the game's database before the code from any mod. So your update statement for the Immortal might be failing because there is no match as yet when the SQL statement executes

WHERE UnitType='UNIT_PERSIAN_IMMORTAL'

Afterwards, the Macedon/Persia DLC would load the Immortal into the game, but by that time it is too late for the SQL line to succeed.

How do i make the mod that affects the immortal load last then?

 

[Question]

OK I figured it out. Just have to do this in the mod info :

<InGameActions>
    <UpdateDatabase id="NewAction">
       <Properties>
       <LoadOrder>100</LoadOrder>
   </Properties>
      <File>test.sql</File>
    </UpdateDatabase>

Thanks for helping me to figure it out. Shame the game doesnt actually warn you about this...

 

[isau]

First things first, if you are using a SQL tool like SQLite Studio you should run a report to pull back data on your modded civ. For a civilization I use this:

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

Here's a similar report. I don't have your mod so I pulled back a report showing my custom changes to Russia:

You'll need a little experience to read the report, but its basically showing all values connected to the leader. If after running your mod these values are incorrect that's when you know there's some a load order issue or something else going on preventing values from making into into the game. I spot check every civ or leader I edit with a report like this to make sure I'm getting what I expect.



Now, RE: the Mapuche ability that changes damage in a certain age. Remember that any combat modifier in the game relies on the ModifierStrings table to display the preview damage. In the case of that ability the Modifier is TRAIT_TOQUI_COMBAT_BONUS_VS_GOLDEN_AGE_CIV and the string you need to update is LOC_TRAIT_TOQUI_COMBAT_BONUS_VS_GOLDEN_AGE_CIV_DESCRIPTION. If you don't fix this then in-game it will still claim the Mapuche get +10 against targets in a Golden Age. The math won't work out right, but it's what the combat preview window will say.

 

[isau]

And nope...still +10 combat strength after loading a save...

I just read this part and realized another potential issue.

Most things attached to a leader or civilization (or a unit or city) via a Modifier are generally not editable again through the database once a game is loaded. The way the game treats Modifiers is it grabs the info from the database and "expands" it into live memory. When a game is saved, that live memory is serialized and written to the save file. That means any tweaks made in the database are no longer relevant. They only affect new games.

There are situations when editing the database will affect objects that already expanded in memory, but those tend to be cases where the database is merely holding a recipe for something that could exist. E.g. if you change the cost of a Building or Unit, that cost will be reflected in the game. But if you change how many Moves units get or the numbers on a leader/civ ability, those generally do not end up reflected in any unit that already exists.

If you are using save games to speed up the ability to test scenarios I recommend looking at Firetuner and/or Quo's QA Godmode mod or another godmode so you can quickly access the pieces you're trying to test after rolling a fresh start.

 

[Question]

OK i spoke too soon. While I can modify immortals now, I STILL cannot get encampments to produce base yield the way the mbanza does.

There are no errors in the database.log...whats going on?

INSERT OR REPLACE INTO Modifiers
        (ModifierId, ModifierType)
VALUES ('MODIFIER_ENCAMPMENT_PRODUCTION','MODIFIER_PLAYER_DISTRICT_ADJUST_BASE_YIELD_CHANGE');


INSERT OR REPLACE INTO ModifierArguments
        (ModifierId, Name, Value)
VALUES    ('MODIFIER_ENCAMPMENT_PRODUCTION','Amount', '2'), -- For testing
        ('MODIFIER_ENCAMPMENT_PRODUCTION','YieldType', 'YIELD_PRODUCTION');

       
INSERT OR REPLACE INTO DistrictModifiers
        (DistrictType,     ModifierId)
VALUES    ('DISTRICT_ENCAMPMENT','MODIFIER_ENCAMPMENT_PRODUCTION');

Mod info :

  <InGameActions>
    <UpdateDatabase id="NewAction">
       <Properties>
       <LoadOrder>100</LoadOrder>
   </Properties>
      <File>test.sql</File>
    </UpdateDatabase>
  </InGameActions>

 

[isau]

Do you have a SQL tool that can run queries? What happens when you run the query below?

SELECT * FROM Districts
LEFT JOIN DistrictModifiers on DistrictModifiers.DistrictType = Districts.DistrictType
LEFT JOIN Modifiers on Modifiers.Modifierid = DistrictModifiers.ModifierId
LEFT JOIN ModifierArguments on ModifierArguments.ModifierId = Modifiers.ModifierId
WHERE Districts.DistrictType LIKE "%ENCAMPMENT%" ;

If it's not pulling back data the issue is the mod itself. If it is, there's a different problem.

 

[Question]

Its retrieving data just fine.

DISTRICT_ENCAMPMENT   LOC_DISTRICT_ENCAMPMENT_NAME   TECH_BRONZE_WORKING       0   LOC_DISTRICT_ENCAMPMENT_DESCRIPTION   54   1   1   1   0   0   0   1   0   100   1   0   NO_PLUNDER   0   0   DOMAIN_LAND   COST_PROGRESSION_NUM_UNDER_AVG_PLUS_TECH   20       -1   0   0   1   0   1   0       -1   2   0   0   ADVISOR_CONQUEST   0   -1   DISTRICT_ENCAMPMENT   MODIFIER_ENCAMPMENT_PRODUCTION   MODIFIER_ENCAMPMENT_PRODUCTION   MODIFIER_PLAYER_DISTRICT_ADJUST_BASE_YIELD_CHANGE   0   0   0                   MODIFIER_ENCAMPMENT_PRODUCTION   Amount   ARGTYPE_IDENTITY   2       
DISTRICT_ENCAMPMENT   LOC_DISTRICT_ENCAMPMENT_NAME   TECH_BRONZE_WORKING       0   LOC_DISTRICT_ENCAMPMENT_DESCRIPTION   54   1   1   1   0   0   0   1   0   100   1   0   NO_PLUNDER   0   0   DOMAIN_LAND   COST_PROGRESSION_NUM_UNDER_AVG_PLUS_TECH   20       -1   0   0   1   0   1   0       -1   2   0   0   ADVISOR_CONQUEST   0   -1   DISTRICT_ENCAMPMENT   MODIFIER_ENCAMPMENT_PRODUCTION   MODIFIER_ENCAMPMENT_PRODUCTION   MODIFIER_PLAYER_DISTRICT_ADJUST_BASE_YIELD_CHANGE   0   0   0                   MODIFIER_ENCAMPMENT_PRODUCTION   YieldType   ARGTYPE_IDENTITY   YIELD_PRODUCTION

Might be the modinfo file? Could you post your mod info so that i can compare it with mine please?