How Safe is "Soft Crashing" out of a SQL File?

[isau]

I'm going through my existing code and making it more customizable, and one thing that would be very useful would be a way to exit a SQL file is a certain condition is not true. I don't think there is any way in SQL to do this. However, due to how Civ 6 runs SQL files, there is one way to exit a file midway: make it crash before it finishes execution.

My question is how "safe" this operation is. Here's basically how I'd picture it:


CREATE TABLE tblCrasher
(
CrashID text PRIMARY KEY ;
)

INSERT INTO tblCrasher
(CrashID)
VALUES (1) ;

INSERT INTO tblCrasher
(CrashID)
VALUES (1) [[where whatever condition is true ]];


Basically, you'd attempt an INSERT statement into a table that has no bearing on gameplay This would cause Civ 6 to abandon that SQL file and move to the next.

As code goes, it's very, very dirty. Maybe even unstable. I haven't actually included it in any files yet, but the temptation is there. Is there a better way to conditionally abandon a file (short of using Rulesets to exclude them from execution)?

 

[Gedemon]

AFAIK it won't lock the game, the rest of that file is just ignored.

 

[LeeS]

I'm not following this:

Basically, you'd attempt an INSERT statement into a table that has no bearing on gameplay This would cause Civ 6 to abandon that SQL file and move to the next.

So long as your insert into the table does not contain fatal syntax error the game is going to go ahead and read right through the insert. If there are syntax errors it is always going to crash the remainder of the file, regardless of whether any conditions are met. The game does not in any way "know" if the table has any actual gameplay effect. All it knows is whether there is a syntax error.

If there's some secret to SQL I am missing out on please explain. (btw everything I know about SQL I learned from modding -- I've never had formal classes in it, so I may just be misunderstanding what you mean.)

 

[isau]

I'm not following this:So long as your insert into the table does not contain fatal syntax error the game is going to go ahead and read right through the insert. If there are syntax errors it is always going to crash the remainder of the file, regardless of whether any conditions are met. The game does not in any way "know" if the table has any actual gameplay effect. All it knows is whether there is a syntax error.

If there's some secret to SQL I am missing out on please explain. (btw everything I know about SQL I learned from modding -- I've never had formal classes in it, so I may just be misunderstanding what you mean.)

There was actually an error in how I wrote my code snippet above. It should be this:


CREATE TABLE tblCrasher
(
CrashID text PRIMARY KEY
) ;

INSERT INTO tblCrasher
(CrashID)
VALUES (1) ;

INSERT INTO tblCrasher
(CrashID)
SELECT '1' WHERE [[ whatever condition is true ]];


"Crash" may be a bad word for it. But what it does is fail the INSERT due to a duplicate Primary Key, but only if the condition in the WHERE clause is true. I've tested it and it "works" in the strict sense that the game does not appear to crash or do anything other than exit the SQL file. But you get a notice about the Error in the database log and its definitely dirty code.


Here it is as implemented in the actual mod, as part of a "switch" that allows players to shut off rules in a particular file if they update the MyOptions file, which writes to a mod-created table called tblQuoOptions.

/* The code below is a dirty attempt to soft-crash out of the file if hardcore rules are not enabled.*/
CREATE TABLE tblQuoSoftCrash
(
    Quo_ExitFile    TEXT PRIMARY KEY
) ;

INSERT INTO tblQuoSoftCrash
VALUES ('1');

INSERT INTO tblQuoSoftCrash
SELECT '1'
FROM tblQuoOptions WHERE tblQuoOptions.OptionID='QUO_OPTION_ENABLE_HARDCORE_SCIENCE_RULES' AND tblQuoOptions.Value='0' ;

I shoved this into the mod for now just to get a beta test out, and will be changing it so that instead every line in the SQL code instead contains a check for the value of QUO_OPTION_ENABLE_HARDCORE_SCIENCE_RULES. But it would still be nice to have a command that says "If this value is whatever, just exit this file entirely." It's kind of what Rulesets do, but those are more rigid.

 

[LeeS]

OK, now I get you.

But I just do Delete From or Update (etc) dependant on whether the condition is "set" rather than trying to exit file. Like for this civ5 mod where I was creating differing effects for the Great Wall World Wonder and adjusting expire techs and the like based on what users input into a "Configuration" file. I just never actually released the mod yet because Civ6.

UPDATE Technologies 
SET GridX = (SELECT GridX FROM Technologies WHERE Type = (SELECT PrereqTech FROM Buildings WHERE Type = 'BUILDING_GREAT_WALL')), Era = (SELECT Era FROM Technologies WHERE Type = (SELECT PrereqTech FROM Buildings WHERE Type = 'BUILDING_GREAT_WALL')), Cost = (SELECT Cost FROM Technologies WHERE Type = (SELECT PrereqTech FROM Buildings WHERE Type = 'BUILDING_GREAT_WALL'))
WHERE Type = 'TECH_LRS_GREAT_WALL_OBSOLETE';

UPDATE Technologies 
SET GridX = (SELECT GridX FROM Technologies WHERE Type = (SELECT Value FROM LRS_GreatWallEffects WHERE Name = 'StandardBorderObstacleExpirationTech')), Era = (SELECT Era FROM Technologies WHERE Type = (SELECT Value FROM LRS_GreatWallEffects WHERE Name = 'StandardBorderObstacleExpirationTech')), Cost = (SELECT Cost FROM Technologies WHERE Type = (SELECT Value FROM LRS_GreatWallEffects WHERE Name = 'StandardBorderObstacleExpirationTech'))
WHERE Type = 'TECH_LRS_GREAT_WALL_OBSOLETE'
AND EXISTS (SELECT * FROM LRS_GreatWallEffects WHERE Name = 'AllowRegularBorderObstacleEffect' AND Value = 1);

UPDATE Technologies 
SET GridX = (SELECT GridX FROM Technologies WHERE Type = (SELECT Value FROM LRS_GreatWallEffects WHERE Name = 'BorderObstacleExpirationTech')), Era = (SELECT Era FROM Technologies WHERE Type = (SELECT Value FROM LRS_GreatWallEffects WHERE Name = 'BorderObstacleExpirationTech')), Cost = (SELECT Cost FROM Technologies WHERE Type = (SELECT Value FROM LRS_GreatWallEffects WHERE Name = 'BorderObstacleExpirationTech'))
WHERE Type = 'TECH_LRS_GREAT_WALL_OBSOLETE'
AND EXISTS (SELECT * FROM LRS_GreatWallEffects WHERE Name = 'AllowRegularBorderObstacleEffect' AND Value = 1);

 

[isau]

OK, now I get you.

But I just do Delete From or Update (etc) dependant on whether the condition is "set" rather than trying to exit file. Like for this civ5 mod where I was creating differing effects for the Great Wall World Wonder and adjusting expire techs and the like based on what users input into a "Configuration" file. I just never actually released the mod yet because Civ6.

UPDATE Technologies
SET GridX = (SELECT GridX FROM Technologies WHERE Type = (SELECT PrereqTech FROM Buildings WHERE Type = 'BUILDING_GREAT_WALL')), Era = (SELECT Era FROM Technologies WHERE Type = (SELECT PrereqTech FROM Buildings WHERE Type = 'BUILDING_GREAT_WALL')), Cost = (SELECT Cost FROM Technologies WHERE Type = (SELECT PrereqTech FROM Buildings WHERE Type = 'BUILDING_GREAT_WALL'))
WHERE Type = 'TECH_LRS_GREAT_WALL_OBSOLETE';

UPDATE Technologies
SET GridX = (SELECT GridX FROM Technologies WHERE Type = (SELECT Value FROM LRS_GreatWallEffects WHERE Name = 'StandardBorderObstacleExpirationTech')), Era = (SELECT Era FROM Technologies WHERE Type = (SELECT Value FROM LRS_GreatWallEffects WHERE Name = 'StandardBorderObstacleExpirationTech')), Cost = (SELECT Cost FROM Technologies WHERE Type = (SELECT Value FROM LRS_GreatWallEffects WHERE Name = 'StandardBorderObstacleExpirationTech'))
WHERE Type = 'TECH_LRS_GREAT_WALL_OBSOLETE'
AND EXISTS (SELECT * FROM LRS_GreatWallEffects WHERE Name = 'AllowRegularBorderObstacleEffect' AND Value = 1);

UPDATE Technologies
SET GridX = (SELECT GridX FROM Technologies WHERE Type = (SELECT Value FROM LRS_GreatWallEffects WHERE Name = 'BorderObstacleExpirationTech')), Era = (SELECT Era FROM Technologies WHERE Type = (SELECT Value FROM LRS_GreatWallEffects WHERE Name = 'BorderObstacleExpirationTech')), Cost = (SELECT Cost FROM Technologies WHERE Type = (SELECT Value FROM LRS_GreatWallEffects WHERE Name = 'BorderObstacleExpirationTech'))
WHERE Type = 'TECH_LRS_GREAT_WALL_OBSOLETE'
AND EXISTS (SELECT * FROM LRS_GreatWallEffects WHERE Name = 'AllowRegularBorderObstacleEffect' AND Value = 1);

Yep, that's how I do it too. I just wish that for the sake of simplicity we had a simple "escape" command to exit a SQL file without executing code. Pure gold would be some way to use the Config database to tell the Gameplay database not to load a file based on a selection made on that screen, but the only way I've seen to do that is with the Ruleset drop-down, hard-coded to the list of files to load in the .modinfo.

 

[Infixo]

There's a 'Criteria' parameter you can use in Component definition in .modinfo. DLCs use it e.g. to check if a leader is enabled. This parameter is somehow related to Parameters table in Configuration database. In another thread we tried to use this exactly for this: to trigger a specific component. That way you can have various sqls depending on startup options. We've tried a ton of various settings but it doesn't work. However, there's definitely a connection, I described it in the thread. I'll find it in a moment if you are interested.
Edit. Here https://forums.civfanatics.com/posts/14703275/

 

[isau]

There's a 'Criteria' parameter you can use in Component definition in .modinfo. DLCs use it e.g. to check if a leader is enabled. This parameter is somehow related to Parameters table in Configuration database. In another thread we tried to use this exactly for this: to trigger a specific component. That way you can have various sqls depending on startup options. We've tried a ton of various settings but it doesn't work. However, there's definitely a connection, I described it in the thread. I'll find it in a moment if you are interested.
Edit. Here https://forums.civfanatics.com/posts/14703275/

You are a saint. Even if it doesn't work, knowing other people looked for it is helpful.