CODEWITHSUNDEEP
sqlite
Denver Thomas
Denver Thomas

Reputation: 114

SQLite: How to exectue statements based on other conditions?

A video game exists with 2 stages: Set-Up, and Gameplay. Players can use Set-Up, to alter a database, which affects Gameplay. However some features of Set-Up must account for other features of Set-Up when interacting with the database, and must therefore alter statements of execution depending on other factors in the database.

For example:

In Set-Up, a player can either choose to enable or disable a setting called "complex commerce", which creates new rows in table "Buildings" with the IDs "BUILDING_WAYSTATION", and "BUILDING_MINT", among many other changes in the database. A player can choose to enable a second setting called "easier commerce" which updates the table "ModiferArguments", increasing the value in the column "VALUE" for the column ID associated through many key constraints to "BUILDING_MARKET" (which will always exist, no matter what) in table "Buildings". However, if "complex commerce" is enabled, then we will want to change update the column "VALUE" differently for market, as well as other buildings introduced by "complex commerce" setting inserts.

Is it possible to perform this kind of logical interaction within an SQLite statement?

Code attempt:

IF
(
    EXISTS
    (
        SELECT
            *
        FROM
            Buildings
        WHERE
            BuildingType = 'BUILDING_WAYSTATION'
        ;
    )
)
THEN
(
    Update
        ModifierArguments
    SET
        Value = Value+1
    WHERE
        ModifierID = 'MARKET_TRADE_ROUTE_CAPACITY'
    ;
    
    Update
        ModifierArguments
    SET
        Value = Value+2
    WHERE
        ModifierID = 'MINT_TRADE_ROUTE_CAPACITY'
    ;
    
    Update
        ModifierArguments
    SET
        Value = Value+3
    WHERE
        ModifierID = 'WAYSTATION_TRADE_ROUTE_CAPACITY'
    ;
)
ELSE
(
    Update
        ModifierArguments
    SET
        Value = Value+2
    WHERE
        ModifierID = 'MARKET_TRADE_ROUTE_CAPACITY'
)
;

I am very sorry about the eye-sore formatting, but I wanted to make sure that the logic of what I am trying to do is as clear as possible.

Code theory:

Using IF and EXISTS we can see in the database whether or not the "complex commerce" set of database updates have occurred, simply by seeing if there is a "Building" table entry row with the Id of "BUILDING_WAYSTATION". This allows to choose which set of UPDATEs to execute for the setting of "easier commerce". If "complex commerce" has not been enabled, then we only need to update a single value to "2". However, if it has been enabled, then that single value must instead be updated to "1" AND we must update other values that would otherwise not exist.

Alternatively:

I have looked at using CASE, but I am not sure if it is capable of fulfilling the same purpose, simply just substituting IF for CASE WHEN. I have also looked at using iif(), but have the same issue in that it does not seem fit for purpose outside of replacing return data from SELECT statements upon display.

Upvotes: -1

Views: 62

Answers (1)

forpas
forpas

Reputation: 164089

You can use CASE expressions to apply your logic:

UPDATE ModifierArguments
SET Value = Value + 
  CASE 
    WHEN EXISTS (SELECT * FROM Buildings WHERE BuildingType = 'BUILDING_WAYSTATION')
      THEN CASE ModifierID
        WHEN 'MARKET_TRADE_ROUTE_CAPACITY' THEN 1
        WHEN 'MINT_TRADE_ROUTE_CAPACITY' THEN 2
        WHEN 'WAYSTATION_TRADE_ROUTE_CAPACITY' THEN 3
      END
    ELSE CASE WHEN ModifierID = 'MARKET_TRADE_ROUTE_CAPACITY' THEN 2 ELSE 0 END
  END
WHERE ModifierID IN ('MARKET_TRADE_ROUTE_CAPACITY', 'MINT_TRADE_ROUTE_CAPACITY', 'WAYSTATION_TRADE_ROUTE_CAPACITY');

Upvotes: 1

Related Questions