A Failsafe Way of adding or updating a row in Sqlite

[GSte]

When modding I encountered the problem that some or all of my changes I wanted to make were not performed. This was, because adding or updating within the xml-interface is frustrating at its best.
Imaging following Situation:
You have 2 mods that may interact and should also run independantly. So let's say, you have made to wonders. Let's call them "The Philophers' Stone" and "The Sages' Pebbles", each increasing the science output by 50. The science output is related to the traits TRAIT_PHILOSPHERS_STONE resp. TRAIT_SAGES_PEBBLES. These are in two mods so when you combine these mods you get a total yield of 100 science. That seems a bit to much, and you want to change the yield to 40 for each when the two mods are combined.
If you do it the xml way you might get two rows of each trait or none or ...
Your game will show no changes or worse behave unexpected and if 'badder' adds to worse the CPU is wandering around in cyber space, looking for a programmer to tell it what to do (also known as 'freezing').

To add or change the yield of science you have to alter contents of the table "Trait_YieldChanges ", that is you must add or update a row.There are a couple of commands needed

First add the yield change for "TRAIT_PHILOSPHERS_STONE", but only when the yield change doesn't exist but the trait and yields are already defined:

INSERT INTO Trait_YieldChanges ([COLOR=DarkRed]TraitType[/COLOR],[COLOR=DarkGreen]YieldType[/COLOR],[COLOR=Blue]Yield[/COLOR])
VALUES ( [COLOR=DarkRed]CASE WHEN NOT EXISTS(SELECT TraitType FROM Trait_YieldChanges  WHERE TraitType="TRAIT_PHILOSPHERS_STONE" AND YieldType="YIELD_SCIENCE" )
                   AND EXISTS(SELECT Type FROM Traits WHERE Type="TRAIT_PHILOSPHERS_STONE")
                   AND EXISTS(SELECT Type FROM Yields WHERE Type="YIELD_SCIENCE")
              THEN "TRAIT_PHILOSPHERS_STONE"
              ELSE NULL END[/COLOR],
         [COLOR=DarkGreen]CASE WHEN NOT EXISTS(SELECT TraitType FROM Trait_YieldChanges  WHERE TraitType="TRAIT_PHILOSPHERS_STONE" AND YieldType="YIELD_SCIENCE" )
                   AND EXISTS(SELECT Type FROM Traits WHERE Type="TRAIT_PHILOSPHERS_STONE")
                   AND EXISTS(SELECT Type FROM Yields WHERE Type="YIELD_SCIENCE")
              THEN "YIELD_SCIENCE"
              ELSE NULL END[/COLOR],
         [COLOR=Blue]CASE WHEN NOT EXISTS(SELECT TraitType FROM Trait_YieldChanges WHERE TraitType="TRAIT_PHILOSPHERS_STONE" AND YieldType="YIELD_SCIENCE" )
                   AND EXISTS(SELECT Type FROM Traits WHERE Type="TRAIT_PHILOSPHERS_STONE")
                   AND EXISTS(SELECT Type FROM Yields WHERE Type="YIELD_SCIENCE")
              THEN "50"
              ELSE NULL END[/COLOR]
        );

This odd looking INSERT command is valid SQL! and it works like a peach. All it does it checks if the row doesn't exist and if the references exist as well. If one of the checks fail, it inserts a row with lots of NULL values. This must be done because you can't abort an INSERT statement in SQLite.
The different colors in the row list correspond with the colors in the VALUES statement.

The second command must be split in two. The order of the commands is important

UPDATE Trait_YieldChanges SET Yield="50"
WHERE TraitType="TRAIT_PHILOSPHERS_STONE" AND YieldType="YIELD_SCIENCE"
      AND EXISTS(SELECT Type FROM Traits WHERE Type="TRAIT_PHILOSPHERS_STONE");

Because this will set the yield unconditionally, we must issue the update command next if there are Sages' Pebbles present

UPDATE Trait_YieldChanges SET Yield="40"
WHERE TraitType="TRAIT_PHILOSPHERS_STONE" AND YieldType="YIELD_SCIENCE"
      AND EXISTS(SELECT Type FROM Traits WHERE Type="TRAIT_PHILOSPHERS_STONE")
      AND EXISTS(SELECT Type FROM Traits WHERE Type="TRAIT_SAGES_PEBBLES");

The same procedure (INSERT and UPDATE) must be followed for TRAIT_PHILOSPHERS_STONE.

And last we have some cleaning to do

DELETE FROM Trait_YieldChanges WHERE TraitType ISNULL  AND YieldType ISNULL;

Otherwise we end up with lots of useless rows.

If you want to change other tables just check the associated XML-file from CiV at the beginning and look for the table definitions. Then adjust the number of rows and the names and all should be well.

That way you can add and update a row without needing to know if that row exists and your mods become independent and combineable at the same time.

See also "The Power of SQL" by Juniper Hush.