Circumventing mod load order issues with database triggers

[whoward69]

Basic Triggers

Consider a mod that adds a pre-Ancient era. Instead of all civs starting out with TECH_AGRICULTURE they now start with TECH_FIRE and have to research their way to TECH_AGRICULTURE.

The mod includes the following SQL to remove the free TECH_AGRICULTURE and replace it with TECH_FIRE for all civs.

UPDATE Civilization_FreeTechs
  SET TechType='TECH_FIRE'
  WHERE TechType='TECH_AGRICULTURE';

This works fine for the base civs but as soon as someone decides to use a custom civ we have a problem. If the custom civ mod loads BEFORE our pre-Ancient era mod, there is no issue - the new custom civ will be modified along with all the others. However, if the custom civ happens to load AFTER our era mod, that civ will NOT start with TECH_FIRE but with TECH_AGRICULTURE.

It's unreasonable for all custom civ modders to add a reference to our era mod, and it's also "unfriendly" to either expect players of our mod to not use custom civs and/or expect them to be able to perform "magic" to get the mods to load in the correct sequence.

What we need is some way to update the Civilization_FreeTechs table if any mod adds to it AFTER our mod's SQL has executed - step forward triggers.

In our mod we can add an SQL trigger to catch any new entries into the Civilization_FreeTechs table and convert them as if they had been there when our mod ran

CREATE TRIGGER CivFireStartingTech
AFTER INSERT ON Civilization_FreeTechs
BEGIN
  UPDATE Civilization_FreeTechs
    SET TechType='TECH_FIRE'
    WHERE TechType='TECH_AGRICULTURE';
END;

This basically says "After an insert has occurred on the Civilization_FreeTechs table, execute the SQL statements between BEGIN and END"

Note that there may be more than one statement between BEGIN and END.

It's also worth noting that a trigger will NOT trigger itself, consider the following to grant Mining to all civs

CREATE TRIGGER CivMiningStartingTech
AFTER INSERT ON Civilization_FreeTechs
BEGIN
  INSERT INTO Civilization_FreeTechs(CivilizationType, TechType)
    VALUES(NEW.CivilizationType, 'TECH_MINING');
END;

It would appear that the INSERT on the Civilization_FreeTechs table between BEGIN/END should itself cause the trigger to execute, but it doesn't.

Note also the use of NEW.CivilizationType to get a column value from the row that caused the trigger to execute.

More info on triggers as understood by SQLite

Conditional Triggers

We can add a condition (WHEN clause) to our trigger to catch specific changes only. Consider a mod that adds leaders for the City States. (We're going to reuse the Leaders and Civilization_Leaders tables for the sample code, but in reality we'd probably create new tables so as not to interfere with the operation of the game core.)

For each City State we need to create an entry for the leader in the Leaders table and an association in the Civilization_Leaders table, eg, for Warsaw

INSERT INTO Leaders(Type, Description, IconAtlas, PortraitIndex)
  VALUES('LEADER_MINOR_CIV_WARSAW', 'TXT_KEY_LEADER_MINOR_CIV_WARSAW', 'MINOR_LEADER_ATLAS', 0);
INSERT INTO Civilization_Leaders(CivilizationType, LeaderheadType)
  VALUES('MINOR_CIV_WARSAW', 'LEADER_MINOR_CIV_WARSAW');

INSERT INTO Leaders(Type, Description, IconAtlas, PortraitIndex)
  VALUES('LEADER_MINOR_CIV_THERA', 'TXT_KEY_LEADER_MINOR_CIV_THERA', 'MINOR_LEADER_ATLAS', 1);
INSERT INTO Civilization_Leaders(CivilizationType, LeaderheadType)
  VALUES('MINOR_CIV_THERA', 'LEADER_MINOR_CIV_THERA');

The problem is that the Thera City State is being added by a 3rd party mod, eg with

INSERT INTO MinorCivilizations(Type, Description, MinorCivTrait)
  VALUES('MINOR_CIV_THERA', 'TXT_KEY_CITYSTATE_THERA', 'MINOR_TRAIT_CULTURED');

which the player may or may not be playing with, and if not, we really don't want the entry in the Civilization_Leaders table as the reference to MINOR_CIV_THERA will then be "dangling".
We can change our insert to trap this by using a WHERE clause

INSERT INTO Civilization_Leaders(CivilizationType, LeaderheadType)
  SELECT Type, 'LEADER_MINOR_CIV_THERA' FROM MinorCivilizations
    WHERE Type='MINOR_CIV_THERA';

but this now requires the Thera mod to load before our mod. We could use a References in ModBuddy but that requires us to know the Thera mod's guid.

Alternatively we could create a trigger that just adds an entry into the Civilization_Leaders table every time a mod adds a new City State, but then we run the risk of getting entries that have no corresponding LeaderheadType entry.
What we really want to do is just activate the trigger when the Thera city state is added, and we can do this by adding a WHEN clause to our trigger

CREATE TRIGGER MinorCivLeaderThera
AFTER INSERT ON MinorCivilizations
WHEN 'MINOR_CIV_THERA'=NEW.Type
BEGIN
  INSERT INTO Civilization_Leaders(CivilizationType, LeaderheadType)
    VALUES('MINOR_CIV_THERA', 'LEADER_MINOR_CIV_THERA');
END;

or more generically to hook up any of our added City State leaders as any 3rd party mod adds the corresponding city state

CREATE TRIGGER MinorCivLeaders
AFTER INSERT ON MinorCivilizations
WHEN EXISTS (SELECT Type FROM Leaders WHERE Type='LEADER_'||NEW.Type)
BEGIN
  INSERT INTO Civilization_Leaders(CivilizationType, LeaderheadType)
    VALUES(NEW.Type, 'LEADER_'||NEW.Type);
END;

Note: The above assumes that there is a fixed naming convention where the leader type is the minor civ type prefixed by "LEADER_"

Note 2: || is the SQL string concatenation operator

We can test the last trigger by adding a Minoa leader and then adding the City State itself, eg

INSERT INTO Leaders(Type, Description, IconAtlas, PortraitIndex)
  VALUES('LEADER_MINOR_CIV_MINOA', 'TXT_KEY_LEADER_MINOR_CIV_MINOA', 'MINOR_LEADER_ATLAS', 1);
INSERT INTO MinorCivilizations(Type, Description, MinorCivTrait)
  VALUES('MINOR_CIV_MINOA', 'TXT_KEY_CITYSTATE_MINOA', 'MINOR_TRAIT_MARITIME');

 

[bc1]

there may be a workaround to [load order dependence]: using CREATE TRIGGER to update the database AFTER INSERT ON Civilizations table rows is done by other mods

Sledgehammer meet Nut

 

[whoward69]

Your sledgehammer approach was to create a civ specific improvement for EVERY civ in order to get a tooltip to display when a simple DLL+Lua mod would have sufficed.

Apples meet Oranges

 

[S3rgeus]

Nice one, I didn't know about SQL triggers. Sounds very useful!

 

[Vicevirtuoso]

Thanks for this!

Now I just have to look into how to do string concatenation with SQL, and I could potentially make my Gilgamesh civ be able to build every unique unit in the game, including those of all other mod civs which are loaded.

 

[whoward69]

Now I just have to look into how to do string concatenation with SQL

"The || operator is "concatenate" - it joins together the two strings of its operands." - see http://www.sqlite.org/lang_expr.html

 

[bc1]

Apples meet Oranges

just peeved at lack of credit for the original idea, while being condescending...
Great writeup though

 

[whoward69]

The write up was actually prompted by this thread. I've been using triggers, cascades and stored procedures since Oracle 6 - so they're not exactly new to me.

 

[bc1]

Of course, you're so full of knowledge...

 

[whoward69]

To add to the knowledge base, the original post has been updated to include conditional triggers - those with a WHEN clause

 

[ExpiredReign]

Do triggers 'fire' if a table is modified by a column being added?

Case in question.

CEP makes copies of certain tables and then performs instructions on those tables.
With the latest DLL release some of those tables, like 'Worlds' have added columns. This means we copy the original table, do some work on it and then when we try to copy it back, after the DLL mod is loaded, we can't because the columns no longer match.

I guess I could add in a dependency but I would like to try this method as it seems to be an excellent way to keep mods working with new mods.

 

[whoward69]

Do triggers 'fire' if a table is modified by a column being added?

No. Triggers only fire for (some) DML statements, not DDL or DCL statements

 

[ExpiredReign]

Thanks.

Looks setting a dependency is the way to go then.

 

[Leugi]

Is there a way of using this to detect your dll mod without dependance?

I was trying something like this without success:

CREATE TRIGGER UpdateAfterWhoward
AFTER INSERT ON Yields WHEN 'YIELD_GOLDEN_AGE_POINTS' = NEW.Type
BEGIN
	UPDATE Units
	SET FaithCost = (200)
	WHERE Type = 'UNIT_LEUGI_HOUNGAN'	
END;

Because I have an override for a missionary, but I want it to work both ways (with or without DLL, without it means costing no faith and manually through lua switching the unit)

 

[whoward69]

Is there a way of using this to detect your dll mod without dependence?

Don't use a Dependency but a Reference, and then use a "... WHERE EXISTS ..." clause in your mod.

So your mod references the DLL, if the DLL is enabled it will load the database first, then your mod will load the database. In your mod set the faith cost within the unit as if the DLL isn't present and then use the following SQL to update it if the DLL is actually present

UPDATE Units
  SET FaithCost = 200
  WHERE Type = 'UNIT_LEUGI_HOUNGAN'
  AND EXISTS (SELECT 1 FROM Yields WHERE Type = 'YIELD_GOLDEN_AGE_POINTS');

 

[meRlinX_AT]

hallo,

is there a workaround that i don't need 2 triggers... 1 for update, 1 for insert?

thanks

 

[whoward69]

is there a workaround that i don't need 2 triggers... 1 for update, 1 for insert

No - see https://www.sqlite.org/lang_createtrigger.html for the syntax diagram for the Trigger statement

 

[meRlinX_AT]

stored queries are not possible with sqlite - or is there a solution?
Maybe someone can help with a sqlite_prepare/sqlite_userfunction?

 

[bootrecords]

I tried to include an SQL trigger on the Buildings table to add buildings of BUILDINGCLASS_UNIVERSITY to the Building_ResourceQuantity for a strategic resource that I want universities [and their replacements, possibly by modded civs] to grant.

Using an SQL file with the following code makes ModBuddy complain about the 'TRIGGER' keyword, saying it encoutered a syntax error and expects a 'table unique index' (a standard DDL table identifier, I'd assume). Is that ModBuddy's usual behaviour on that keyword or am I doing something wrong?

CREATE TRIGGER UniversityResourceQuantityUpdates
AFTER INSERT ON Buildings
WHEN 'BUILDINGCLASS_UNIVERSITY'=NEW.BuildingClass
BEGIN
	INSERT INTO Building_ResourceQuantity(BuildingType, ResourceType, Quantity)
		VALUES(NEW.BuildingType, 'RESOURCE_MYNEWRESOURCE', 1);
END;

The issue is, this trigger should evaluate on loading a mod like the Timurids (which I used to test this trigger), but their university replacement doesn't grant MYNEWRESOURCE. It does, however, work on vanilla universities (and Siamese wats), which I added to the database via XML in my mod.

Any ideas why that is? Is something wrong with this SQL or could mod load order play a role (i.e. the Timurid university replacement may be added before the trigger and thus the trigger would never activate)? If the latter is a possibility, how could I ensure my SQL trigger would get attached to the database before any other mods are loaded?

 

[Nutty]

I believe the first value of the VALUES() list should be NEW.Type (the name of the column in the Buildings table--the order of the entries given in the insert parenthetical allows the game to know that you wish to insert NEW.Type in the BuildingType column).

You need to run a regular insert once to cover all vanilla entries and modded entries that have been activated prior in the load order. The trigger covers entries introduced by mods that follow in the load order.