WhysSql Template VI

[Ryan F. Mercer]

Ultimate Party​

This tutorial explains how to:

• Install the modding tools.
• And more.

The true beauty of modding is that you never need to know everything at once. No matter how new and confusing something might appear, there is always a next step. One need only focus on the specific question in front of them and then explore what is possible. Within every modder is an eternal desire to experiment and discover. And with each little answer comes a small wonder that leads to whatever comes next. Join me, so that we might explore together. I have assembled these tools for you.

The WhysSql Template streamlines and enhances the Civ6 SQL environment, allows greater interoperability of mods, and provides developer tools for debugging, available to all SQL files.

To learn more, explore the Table of Contents below. The blue arrows are clickable buttons for navigating this tutorial.

 

[Ryan F. Mercer]

Features​

Print​

Variables​

Dummys & Restrict​

Trace & Source​

Disableable Triggers​

Initialize & Finalize​

Tutorials​

For Dummies​

SQLite​

Register Mod ​

Custom Table​

Custom Trigger​

Miscellaneous​

About The Author​

Technical​

Troubleshooting​

 

[Ryan F. Mercer]

Spoiler :

The WhysSql Template allows users to easily print notices, warnings, and errors, as well as a trigger execution trace. This provides visibility into the workings of the code, allowing the user to identify any issues and where they occur. Additionally, an error terminates the script execution.

-- notice, warning, error.
INSERT INTO Whys_Print(Type, Message) SELECT  0, 'This is a notice.';
INSERT INTO Whys_Print(Type, Message) SELECT  1, 'This is a warning.';
INSERT INTO Whys_Print(Type, Message) SELECT -1, 'This is an error.'; -- terminates script.

Whys_Print:

Output to the print table automatically includes a timestamp, the mod, and the file from where the print occurred.

Whys_Print:

The trace output is indented according to the execution depth of the triggers, allowing the user to easily identify the source of the output and in what order the triggers were executed. In the example below, note the trigger named WhysResoHarvCtrl_BEF_INS_WhysResoHarvCtrl_Conv. This trigger occurs before an item is inserted into the table named WhysResoHarvCtrl_Conv. Within that trigger, six additional triggers are executed. Once complete, it is then followed by WhysResoHarvCtrl_AFT_INS_WhysResoHarvCtrl_Conv. That trigger occurs after the item is inserted, completing the process.

Whys_Print:

Notices and warnings can be disabled for specific triggers, both for your own mod and any other mod that uses the WhysSql Template.

-- disable in all triggers.
UPDATE Whys_Trig SET DoNotice = 0, DoWarning = 0;

-- disable in all triggers of a mod.
UPDATE Whys_Trig SET DoNotice = 0, DoWarning = 0
WHERE Mod IS 'AnyMod';

-- disable in all triggers of a file.
UPDATE Whys_Trig SET DoNotice = 0, DoWarning = 0
WHERE File IS 'AnyFile';

-- disable in named trigger.
UPDATE Whys_Trig SET DoNotice = 0, DoWarning = 0
WHERE Name IS 'AnyTrigger';

While the trace itself can not be disabled, the output of any contents of specified triggers can be truncated. Truncated triggers identify themselves as "[truncated]" in their trace output and will not display any notices, warnings, or traces from within the truncated trigger. In the following example, note the trigger WhysResoHarvCtrl_BEF_INS_WhysResoHarvCtrl_Conv from the earlier example. The six inner triggers are now hidden.

-- truncate output.
UPDATE Whys_Trig SET DoOutput = 0
WHERE Name IS 'WhysResoHarvCtrl_BEF_INS_WhysResoHarvCtrl_Conv';

Whys_Print:

Output can also be made dependent on the current debug mode, allowing it to be hidden except when debugging.

-- notice on debug.
INSERT INTO Whys_Print(Type, Message) SELECT 0, 'This is a debugging notice.'
WHERE (SELECT Debug FROM Whys_Run) >= 1;

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

The WhysSql Template allows users to easily create variables for their mod, readable by other mods. Variable names only need to be unique to the mod, allowing each mod to create variables by the same name.

Whys_Var:

Read:

SELECT Value FROM Whys_Var
WHERE Mod IS '__system' AND Name IS 'defaultPrefix';

Write:

INSERT INTO Whys_Var(Mod, Name, Value)
SELECT 'MyMod', 'defaultPrefix', 'MM_';

For convenience, Whys_myVar is provided for interacting with your own variables, without specifying the mod name.

Write:

INSERT INTO Whys_myVar(Name, Value)
SELECT 'defaultPrefix', 'MM_';

Read:

SELECT Value FROM Whys_myVar
WHERE Name IS 'defaultPrefix';

Variables can be set as constants, which can only be set on mod initialization and then can not be changed. Otherwise, variables can be added, set, or removed at any time, but only by your own mod. Variables can also be shared, so other mods can change the value, but not delete the variable itself.

Whys_Var:

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

The WhysSql Template allows users to easily manage dummy types, created by your own mod or any other mod that uses the WhysSql Template. This allows the user to filter any dummy types while performing actions on tables that may contain them.

Write:

-- add dummy type.
INSERT INTO Types(Type, Kind)
SELECT 'TECH_MY_DUMMY', 'KIND_TECH';

-- add dummy tech.
INSERT INTO Technologies(TechnologyType, Name, Cost, EraType)
SELECT 'TECH_MY_DUMMY', 'LOC_TECH_MY_DUMMY', 1000, 'ERA_ANCIENT';

-- register dummy.
INSERT INTO Whys_Dummy(Mod, Type)
SELECT 'MyMod', 'TECH_MY_DUMMY';

Read:

-- filter all dummy types.
SELECT TechnologyType FROM Technologies
WHERE TechnologyType NOT IN (SELECT Type FROM Whys_Dummy);

-- filter dummy types by mod.
SELECT TechnologyType FROM Technologies
WHERE TechnologyType NOT IN (SELECT Type FROM Whys_Dummy WHERE Mod IS 'OtherMod');

Whys_Dummy:

For convenience, Whys_myDummy is provided for interacting with your own dummys, without specifying the mod name.

-- register dummy.
INSERT INTO Whys_myDummy(Type)
SELECT 'TECH_MY_DUMMY';

-- filter my dummys.
SELECT TechnologyType FROM Technologies
WHERE TechnologyType NOT IN (SELECT Type FROM Whys_myDummy);

If another mod deletes items from a table without first checking the dummy table, it might delete your dummy, and then your own mod will fail. To prevent this, dummys and any other type you want protected, can be added to the restrict table. This ensures that any attempt to delete your dummy will cause the other mod to fail instead. The restrict table has a column for each kind of type in the game.

Whys_Restr:

-- restrict technology type.
INSERT INTO Whys_Restr(Mod, Type, TechnologyType)
SELECT 'MyMod', 'TECH_MY_DUMMY', 'TECH_MY_DUMMY';

-- restrict building type.
INSERT INTO Whys_Restr(Mod, Type, BuildingType)
SELECT 'MyMod', 'BUILDING_MY_DUMMY', 'BUILDING_MY_DUMMY';

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

The WhysSql Template allows users to easily track the traceback of trigger execution, and determine the triggering source of each trigger in the sequence. Triggers are added to the traceback as execution depth increases, and then removed as they are completed.

Whys_Trace: (traceback)

WhysResoHarvCtrl_BEF_INS_WhysResoHarvCtrl_Conv:

-- returns 'WhysResoHarvCtrl_AFT_INS_Resource_Harvests'.
SELECT SourceTrigger FROM Whys_Run;

WhysResoHarvCtrl_BEF_INS_TechnologyPrereqs:

-- returns 'WhysResoHarvCtrl_BEF_INS_WhysResoHarvCtrl_Conv'.
SELECT SourceTrigger FROM Whys_Run;

-- returns 'WhysResoHarvCtrl_VI'.
SELECT SourceMod FROM Whys_Run;

-- returns 'WhysResoHarvCtrl_VI.sql'
SELECT SourceFile FROM Whys_Run;

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

The WhysSql Template allows users to easily disable and re-enable triggers at runtime, for your own mod or any other mod that uses the WhysSql Template.

-- disable trigger.
UPDATE Whys_Trig SET Enabled = 0
WHERE Name IS 'AnyTrigger';

However, it's important to understand that disabling a trigger does not prevent an insert, update, or delete action on the table the trigger is attached to. It disables the trigger only and the action will occur without it. While there are scenarios where disabling a trigger can be useful, it should generally be avoided.

The WhysSql Template also allows triggers to be recursion limited. Recursion is when a trigger's operation retriggers itself in the process. If handled improperly, this can lead to an infinite loop, which will cause the script to fail. For this reason, triggers using the WhysSql Template are limited to zero recursion by default. Thus if a trigger triggers itself, the repeat action will still occur, but it will do so as if the trigger has been disabled. The WhysSql Template allows the user to increase and decrease the recursion limit as needed. The most common scenario for doing so is when an update trigger needs to update the table again in response to the original update.

-- increment recursion limit.
UPDATE Whys_Trig SET RecurMax =
    (SELECT RecurMax FROM Whys_Trig WHERE Name IS 'AnyTrigger') +1
WHERE Name IS 'AnyTrigger';

-- decrement recursion limit.
UPDATE Whys_Trig SET RecurMax =
    (SELECT RecurMax FROM Whys_Trig WHERE Name IS 'AnyTrigger') -1
WHERE Name IS 'AnyTrigger';

The current recursion count can be seen in the trace output for each trigger in the Whys_Print table. It appears in parentheses at the head of the trigger name.

Whys_Print:

It also appears as its own column in the Whys_Trace table, allowing the user to easily retrieve the recursion count of the previous trigger.

Whys_Trace:

-- returns recursion count of previous trigger.
SELECT SourceRecur FROM Whys_Run;

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

The WhysSql Template requires mods to be registered before accessing many of the features. Mod registration relies on custom initialization-triggers that ensure interoperability with other mods. These triggers must be attached to the Whys_Mod table and then registered before initialization.

-- create initialization-triggers.
CREATE TRIGGER MyMod_BEF_INS_Whys_Mod__init
BEFORE INSERT ON Whys_Mod BEGIN ... END;
CREATE TRIGGER MyMod_AFT_INS_Whys_Mod__init
AFTER INSERT ON Whys_Mod BEGIN ... END;

-- register initialization-triggers.
INSERT INTO Whys_Trig(Mod, File, Initialize, Name)
SELECT 'MyMod', 'MyMod.sql', 1, 'MyMod_BEF_INS_Whys_Mod__init';
INSERT INTO Whys_Trig(Mod, File, Initialize, Name)
SELECT 'MyMod', 'MyMod.sql', 1, 'MyMod_AFT_INS_Whys_Mod__init';

-- initialize.
INSERT INTO Whys_Mod(Name) SELECT 'MyMod';

Once initialized, mods appear in the Whys_Mod table. It includes a Chronology column, indicating the order mods were registered.

Whys_Mod:

Optionally, custom finalization-triggers may also be registered. These ensure proper cleanup of all database modifications made by your mod.

-- create finalization-triggers.
CREATE TRIGGER MyMod_BEF_DEL_Whys_Mod__final
BEFORE DELETE ON Whys_Mod BEGIN ... END;
CREATE TRIGGER MyMod_AFT_DEL_Whys_Mod__final
AFTER DELETE ON Whys_Mod BEGIN ... END;

-- register finalization-triggers.
INSERT INTO Whys_Trig(Mod, File, Finalize, Name)
SELECT 'MyMod', 'MyMod.sql', 1, 'MyMod_BEF_DEL_Whys_Mod__final';
INSERT INTO Whys_Trig(Mod, File, Finalize, Name)
SELECT 'MyMod', 'MyMod.sql', 1, 'MyMod_AFT_DEL_Whys_Mod__final';

Are finalization-triggers in the Civ6 SQL environment useful?

¯\_(ツ)_/¯   ...it makes reinitialization possible.​

The current state of each mod can be found in the Whys_State table. It indicates if a mod is initialized or finalized, as well as intermediate states such as pre-initializing, initializing, reinitializing, and finalizing. Knowing these intermediate states can be useful for your own triggers.

Whys_State:

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

...


Top

Contents

 

[Ryan F. Mercer]

Spoiler :

...


Top

Contents

 

[Ryan F. Mercer]

Spoiler 🚩 :

...


Top

Contents

 

[Ryan F. Mercer]

Spoiler :

...


Top

Contents

 

[Ryan F. Mercer]

Spoiler :

...


Top

Contents

 

[Ryan F. Mercer]

Spoiler :

I am a Civ modding enthusiast with no special relationship with Firaxis or any associated entity. I began developing the WhysSql Template on about August 1st 2025. All of the programming is my own work and was built from scratch. I developed it to enhance the Civ SQL modding environment and make it more user friendly. It was designed organically from first principles. I have made it freely available for noncommerical use in hopes of widespread adoption. This will lead to better mods, better modders, and a more active modding community.

Special Thanks:

• Deliverator – for their initial 'Execute SQL' for database testing outside of Civ6.
• whoward69 – for their Circumventing mod load order issues with database triggers tutorial, explaining how to use triggers to improve mod interoperability.

Top

Contents

 

[Ryan F. Mercer]

Spoiler :

This author uses a version ID for their work. The ID is composed not only of the version number, but also the build date, and is preceded by a format indicator. The version number is always a whole number and only increments when a build is no longer backwards compatible with the previous build. Example below.

Spoiler Change Log :

None.

Spoiler Deprecated :

None.

Top

Contents

 

[Ryan F. Mercer]

...reserved...

 

[Ryan F. Mercer]

...reserved...

 

[Ryan F. Mercer]

...reserved...

 

[Ryan F. Mercer]

...reserved...

 

[Ryan F. Mercer]

...reserved...