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

[Question]

The one here is very bare bones : http://modiki.civfanatics.com/index.php?title=XML-SQL_Cheat_Sheet_(Civ5)#Updating_rows

For example, lets say i want to increase the cost of all units by 50% EXCEPT for settlers, builders and traders.

How do i do that?

I can probably do this :

UPDATE Units
SET Cost = Cost*1.5;
WHERE UnitType NOT = 'UNIT_SETTLER';

But that's only for one unit...

 

[Infixo]

SQLite has an official page with full documentation.

 

[isau]

The one here is very bare bones : http://modiki.civfanatics.com/index.php?title=XML-SQL_Cheat_Sheet_(Civ5)#Updating_rows

For example, lets say i want to increase the cost of all units by 50% EXCEPT for settlers, builders and traders.

How do i do that?

I can probably do this :

UPDATE Units
SET Cost = Cost*1.5;
WHERE UnitType NOT = 'UNIT_SETTLER';

But that's only for one unit...

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:

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.

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;

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

 

[Infixo]

Not equal operator is <> in sql.

 

[Question]

@isau : I did this :

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

 

[isau]

@isau : I did this :

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

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

 

[Question]

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...

 

[LeeS]

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.

 

[isau]

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.

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.