1. We have added a Gift Upgrades feature that allows you to gift an account upgrade to another member, just in time for the holiday season. You can see the gift option when going to the Account Upgrades screen, or on any user profile screen.
    Dismiss Notice

[R&F] Does anyone have a full SQL guide?

Discussion in 'Mod Creation Help' started by Question, Mar 20, 2018.

  1. Question

    Question King

    Joined:
    Mar 12, 2008
    Messages:
    945
  2. Infixo

    Infixo Deity

    Joined:
    Jan 9, 2016
    Messages:
    3,685
    Gender:
    Male
    Location:
    Warsaw
  3. isau

    isau Deity

    Joined:
    Jan 15, 2007
    Messages:
    3,068

    There's a few ways to achieve that. Probably easiest here to just create a NOT IN condition.

    UPDATE Units
    SET Cost = Cost*1.5
    WHERE UnitType NOT IN ("UNIT_SETTLER", "UNIT_TRADER") ;

    etc.

    You could also use a temporary table. More useful as the list of units increases, especially if you want to be able to query the database later to see what units got swept up in the query:

    Code:
    CREATE TABLE tblMyUnits AS SELECT UnitType FROM Units WHERE Units.UnitType NOT IN ("UNIT_SETTLER," "UNIT_TRADER");
    

    As stuff gets more complex you can get more and more complex joins. Here's a case where for my mod I recently needed to create a list of all light and heavy cavalry that were from the Industrial Age or earlier and were not unique civ units.

    Code:
    create table tblPreModernCavalry as
    select unittype, technologies.eratype from units
    inner join technologies on technologies.TechnologyType = units.PrereqTech
    where promotionclass in  ("PROMOTION_CLASS_HEAVY_CAVALRY","PROMOTION_CLASS_LIGHT_CAVALRY") AND EraType in ("ERA_ANCIENT", "ERA_CLASSICAL", "ERA_MEDIEVAL", "ERA_RENAISSANCE", "ERA_INDUSTRIAL") AND TraitType IS NULL
    
    union
    
    select unittype, civics.EraType from units
    inner join civics on civics.CivicType = units.PrereqCivic
    where promotionclass in  ("PROMOTION_CLASS_HEAVY_CAVALRY","PROMOTION_CLASS_LIGHT_CAVALRY") AND EraType in ("ERA_ANCIENT", "ERA_CLASSICAL", "ERA_MEDIEVAL", "ERA_RENAISSANCE", "ERA_INDUSTRIAL") AND TraitType IS NULL;
    upload_2018-3-20_19-54-56.png


    A lot of it comes from just learning basic SQLite commands and building up.
     
  4. Infixo

    Infixo Deity

    Joined:
    Jan 9, 2016
    Messages:
    3,685
    Gender:
    Male
    Location:
    Warsaw
  5. Question

    Question King

    Joined:
    Mar 12, 2008
    Messages:
    945
    @isau : I did this :

    Code:
    UPDATE Units
    SET    Cost = Cost*1.5;
    WHERE UnitType NOT IN ("UNIT_SETTLER", "UNIT_TRADER", 'UNIT_BUILDER', 'UNIT_SCOUT') ;
    But it doesn't appear to work and database.log shows : [1281787.095] [Gameplay] ERROR: near "WHERE": syntax error
     
  6. isau

    isau Deity

    Joined:
    Jan 15, 2007
    Messages:
    3,068

    There's an extra ; in there. Delete the one after the 1.5.
     
  7. Question

    Question King

    Joined:
    Mar 12, 2008
    Messages:
    945
    Thanks, just tried it again, unfortunately the change does not appear to affect saved games properly...the base cost changes but not the actual cost...
     
  8. LeeS

    LeeS Imperator Supporter

    Joined:
    Jul 23, 2013
    Messages:
    6,910
    Location:
    Illinois, USA
    Best rule of thumb is to always assume any database changes (ie, XML or SQL) you make in a mod will not be implemented properly unless you start a new game.

    This is not really true but you will save yourself a lot of headaches if you assume it to be true and test any mod-changes accordingly. Some things will take effect "dynamically" with a reload of a save and some will not, and to know which is which would require keeping a scorecard (essentially) on which parts of the database relflect changes when loading a save and which do not -- a task really not worth the effort.
     
  9. isau

    isau Deity

    Joined:
    Jan 15, 2007
    Messages:
    3,068

    What Lee said above.

    I can tell you in general that if a change made to the database effects an object that is expanded into active memory that you need a totally fresh start. The biggest systems affected by this are Players, Units, Buildings, and Cities. One of the easiest ways to see the issue is how change BaseMoves of a Unit changes it for any new units you build but doesn't change any units created (expanded in active memory) prior.

    Sometimes if the object isn't created yet a database change can still effect the game. E.g. I've modded my game mid-game before to change where Wonders can be built if I find a ridiculously amazing location that happens to have the right kind of tile.

    It's generally not a good idea though. Policies and Governments for example are in between the two systems. They are expanded in memory when the player adopts them and saved with the player, but any dormant polices don't exist in active memory and are loaded fresh from the database. In theory this could create some bad conflicts.
     

Share This Page