Tutorial:Load Order SQL Patches

From Total War Modding

Yes, you read that right. It is now possible to make patches, applied when launching the game, for your specific load order using SQL Scripts.

Why would I need this?

Have you noticed how all 2X or 3X unit size mods need a submod for each unit pack/overhaul in existence, and are largely incompatible with anything that edits the land_units table? Load order SQL patches are the solution for this. Instead of having to rely on a myriad of submods, TWPatcher can scan your load order and apply a SQL script to the entire load order, effectively creating a “whateveramountyourwant”X unit size mod that works no matter what unit pack or overhaul, or mods editing land_units you have.

Another example, you want to use Assladders Begone (with buildable ladders) and The Southern Realms, and you realize none of the factions in the Southern Realms can build ladders when sieging. This is because you need a submod to “give permission” to build ladders for each mod with custom factions. Again, the solution is a load order SQL patch. Just make a patch that scans the load order in search of factions that don’t have permission to build siege ladders, and give it to them. No submods, no risk of crashes because you forget to enable/disable a submod. Just things working as expected.

These two examples have already been implemented (you can check the sql scripts here and here, inside the Pack), but the gist of it is: with load order SQL patches you can forget about incompatibilities in frequently edited tables and a certain amount of compatibility submods.

Ok, so what do I need?

You need two things:

  • TWPatcher 0.9.6 or newer, or a launcher that uses it (like Runcher 0.9.103 or newer).
  • Knowledge of the SQL language (or time to learn it).

It’s also recommended you install SQLiteStudio or another program that lets you open SQLite databases.

Where do the scripts go?

It depends. If you’re using TWPatcher directly, they can go anywhere. If you’re using a custom launcher, wherever the launcher wants to put them. In the case of Runcher, there are 3 places where a script will be loaded from (in order of priority):

  • C:/Users/youruser/AppData/Roaming/FrodoWazEre/runcher/config/sql_scripts_local/gamename
  • C:/Users/youruser/AppData/Roaming/FrodoWazEre/runcher/config/sql_scripts_extracted/
  • C:/Users/youruser/AppData/Roaming/FrodoWazEre/runcher/config/sql_scripts_remote/gamename

The first one is for local script you make for yourself, or download from somewhere. The second one is used for scripts distributed inside the twpatcher/scripts folder in a pack. The third one is for scripts downloaded from the public repo.

How can I make one?

A SQL script is, in reality, composed of two files: a yaml file for storing metadata, and a SQL file containing the actual script. Both are plain text files you can edit in a text editor, and the only particularities are that the yaml file needs to have the .yml extension, not .yaml, and that the name of the sql file needs to be whatever you put in the key field in the yaml file, as that’s what is used to relate each yaml file with its SQL script.

Now, let's make our first script. Go to the folder where you want the scripts to be and create two empty text files with the same name, one with .yml extension, and the other one with .sql extension. Then, open the yaml file and copy this into it: 

key: example_script
name: Example Script (SQL)
description: Example description.
automatic: false
parameters:
tables_affected:
tables_created:
replacements:

Now let's go field by field:

  • key: the identifier of this mod, and name of the sql file.
  • name: the human-readable name of the script. Mainly for UIs, to display something easier to understand than the key.
  • description: a description of what the script is for, in case the name is not clear enough or it needs more explanation.
  • automatic: for UIs. If the mod is marked as automatic, the UI should automatically enable it. For example, if you make a patch that is shipped inside a pack, and all it does is search for incompatibilities in the load order and fix them, you can mark it as automatic so the user doesn’t need to do anything for the patch to fix the incompatibilities.
  • parameters: this is a list of user-configurable parameters that will be used in the SQL Queries. A parameter has the following structure:
    • key: both, the unique key of the parameter, and the string that will be replaced in the script with the value you provide when applying the value.
    • type: the type of the data in this parameter. Types supported are ‘Bool’, ‘Integer’, and ‘Float’.
    • default_value: value applied to this param in case you don’t provide it with a value.
    • name: human-readable name of the parameter.
  • tables_affected: a list of table names without the _tables ending (for example, _kv_rules for _kv_rules_tables). The tables on this list are the ones whose data will end up in the generated pack. So if your script edits, for example, land_units and selects from main_units but it doesn’t edit it, you just need to add land_units.
  • tables_created: a list of table name, file name pairs. You need to put in here the tables in which the script executes INSERT INTO statements, with the same format as in tables_affected, and with the file name being however you want the generated table file in the pack to be named.
  • replacements: a list of key, value. When executing the script, any appearance of the keys in either the script or a replacement value will be replaced by their respective keys. This may sound a bit confusing, but it’s really simple. It’s like a fixed parameter.

And that’s the yaml file. Now the SQL file. This one is simple as it just needs to contain queries, but it has a particularity: table names must end in _tables_vX being X the version of the table. To know what version a table has, open a pack with said table in RPFM, right-click the table, and hit Open.../Open in Decoder. The table version will be at the right of the screen, named PackedFile Version.

Now, an example: 

UPDATE _kv_unit_ability_scaling_rules_tables_VERSION__KV_UNIT_ABILITY_SCALING_RULES
SET value = value * $0
WHERE key IN (
    'direct_damage_large',
    'direct_damage_medium',
    'direct_damage_small',
    'direct_damage_ultra'
);

You may see two weird things in this query: $0 and the table name. $0 is a parameter with key $0, of type float. When applying the script, that $0 will be replaced with whatever the user passes as the $0 parameter or its default value of nothing is passed. And that’s how you use parameters. Pretty simple.

And as for the weird table name, it’s a partial replacement. Basically, we have a replacement called VERSION__KV_UNIT_ABILITY_SCALING_RULES with value v0. When applying the script, _kv_unit_ability_scaling_rules_tables_VERSION__KV_UNIT_ABILITY_SCALING_RULES will be converted to _kv_unit_ability_scaling_rules_tables_v0. Why do we do this? Because table versions can change in a game update, it’s way easier to update a value in the replacement list than to search and update 20 identical values in the SQL script, making it easier to update SQL scripts.

I made my script. How do I use it?

If you’re using Runcher, just drop it in C:/Users/youruser/AppData/Roaming/FrodoWazEre/runcher/config/sql_scripts_local/gamename, restart Runcher, and it’ll show up under the Play submenu, like any launch option. If the script has parameters, they can be configured from there.

If you’re using another launcher, depends on how said launcher has implemented it.

If you’re just using TWPatcher, just call it with the argument --sql-script pathtotheyamlfile;param_value_1;param_value_2;.... You can pass multiple scripts.

How do I debug broken scripts?

If there is a sintaxis problem with the yaml, in the twpatcher-sql-scripts repo there is a schema.json file you can use to validate your yaml file. To do so, just convert your yaml file to json with an online converter, then go to an online json validator, and validate the converted json using the schema.json file. If there is an invalid value or a missing field, it should tell you right there.

If a script fails during the patching process, the TWPatcher terminal will show you the error, a copy of the entire SQL Script it tried to execute with all the parameters and replacements applied, and it’ll wait 60 seconds before closing itself. Use those 60 seconds to copy the script and the error to a text file where you can inspect the script more calmly.

Then open the database file, which should be in C:/Users/youruser/AppData/Roaming/FrodoWazEre/twpatcher/config/, in SQLiteStudio or the program you use to open SQLite databases, and use that to debug your query. When you’re sure the query is correct, port the changes to your SQL script and try again.

While it depends on the launcher, you generally don’t need to restart the launcher after editing the sql part of a script (the replacements list or the .sql file), meaning if a script fails, you can just open the script in a text editor, fix it, then launch the game again.

Did you said you can replace submods with this?

Yes, but with an asterisk. While the logic for applying scripts is launcher-agnostic, this is not. This is Runcher-exclusive, at least for now. Any script inside a Pack under the twpatcher/scripts/ path will be automatically extracted and treated as a normal script when enabling the mod containing it, and will be removed from the launch options list when disabling the mod. This allows you to distribute scripts that will be automatically applied ONLY WHEN YOUR MOD IS ENABLED.

An example of this is Assladders Begone, which ships with a script to scan your load order for custom factions and generates the tables and entries needed for them to be able to build buildable ladders. No submods are needed, and works with any custom faction.

My script has many parameters and it’s hard to configure. Any help?

Yes, actually. First, start by actually thinking if you really need all those parameters. Trust me, you don’t need one param per db field.

Second, if you use Runcher or another launcher that supports this, you can use a preset. Presets are yaml files inside a Pack under the twpatcher/presets/ folder, with the following structure:

  • key: a unique key to identify the preset.
  • name: the human-readable name of this preset.
  • script_key: the key of the script this preset is meant to.
  • params: list of `parameter key, parameter value`, containing the value this preset will assign to each parameter.

In the launcher UI, instead of inputting each parameter’s value, you can just select a preset. When applying the script, the preset values will be used to configure the script. So for example, you can make Easy, Normal and Hard presets for a script, and let the users just pick one.

How can I distribute my scripts?

TWPatcher only applies patches, it doesn’t distribute them. This is up to the launcher you use. In the case of Runcher, it supports two ways:

I’m a launcher dev, how can I implement this?

It’s pretty easy, and ignore what others may say: you don’t need to know Rust to do it. Here’s how:

  • (Mandatory) Ship the TWPatcher (downloaded from here) with your program.
  • (Mandatory) Call TWPatcher between finishing making the load order file, and launching the game, with the arguments you want.
  • (Mandatory) Make sure TWPatcher outputs the generated pack either in /data (not recommended) or in a folder that is included in your load order through an add_working_directory line.
  • (Optional) Provide some way to configure the parameters in the UI (if your program has an UI).
  • (Optional) Provide support for extracting and using presets and scripts from Packs.
  • (Optional) Provide support for downloading scripts from here.

Anyway, I’m Frodo and this has been my Ted Talk on how to use SQL for something you’re not going to get paid for. Come next week for advice on how to evade taxes (may cause to get you in trouble with your government).

Enjoy!

Retrieved from "https://tw-modding.com/index.php?title=Tutorial:Load_Order_SQL_Patches&oldid=1319"