Requesting a little SQL tutoring...

[sman1975]

Hello,

I'm trying to use SQL to make a change to the Policies database - IF Vox Populi is NOT enabled (i.e. in case of a normal, non-VP game).

Here is the syntax:

UPDATE Policies    SET BuildingGoldMaintenanceMod = -33   
    WHERE Type='POLICY_TRADE_UNIONS' AND NOT EXISTS (SELECT * FROM COMMUNITY);

'COMMUNITY' is a table created by the VP mod, otherwise it does not exist in the normal Civ 5 tables.

I've used this subquery with EXISTS successfully in other parts of the mod.

There is a 'Reference' in my mod that ensures VP will load first, if it is enabled.


I've also tried to set the subquery even more restrictive:

UPDATE Policies    SET BuildingGoldMaintenanceMod = -33   
    WHERE Type='POLICY_TRADE_UNIONS' AND NOT EXISTS (SELECT * FROM COMMUNITY WHERE Type='COMMUNITY_CORE_BALANCE_UNITS' AND Value = 1);

Neither statement seems to work. I've looked through other mods and found several examples that look to be structured similarly to these.

What am I missing??? Thanks!

 

[Enginseer]

I'm guessing COMMUNITY table doesn't exist.

CREATE TABLE IF NOT EXISTS COMMUNITY (
Type text,
Value variant default 0
);

Put this above your code and it should fix your problem.

 

[LeeS]

database log would tend to thorw a 'No Such Table' error when a table does not exist. Off the top of my head I don't remember whether table-names are case sensitive in SQL (they aren't in XML) but I've never tried to poke the bear and have always copy-pasted table-names exactly with upper/lower used exactly as named by the base-game or whichever other mod I am trying to reference.

If another mod introduces a new table, you cannot reference to that table unless the other mod is active. Which is why so many of JFD's mods where he is using a custom "settings" table first include the table-creation commands such as

CREATE TABLE IF NOT EXISTS 
CustomModOptions (Name text default null, Value integer default 1);

Which he then follows with a new row and values for whichever mod he is making, like

INSERT OR REPLACE INTO CustomModOptions(Name, Value) VALUES('EVENTS_GOLDEN_AGE', 1);

The table is therefore always defined as a basic first step and then he can enter new values to it or re-direct other SQL commands when needed based on the new setting he added or whether or not some other mod of his / Pook's / etc., need a little massaging by UPDATE statements based on the set of mods currently enabled.

You would need to find the correct current version of the COMMUNITY table as it is being used by VoxP and first do the CREATE TABLE IF NOT EXISTS with all the correct definitions of the table before attempting the UPDATE based on whether a setting exists within that table.

 

[sman1975]

You are correct - looking at the Database.log, there is a "no such table" error shown.


Funny, I saw a similar example where the table was created at the top of the file - it was a custom civ that had a compatibility file, and the table was called 'CSD' - City state something...?

Anyways, the current COMMUNITY schema has 2 columns:

Type (text) -- Value (variant)

So, as I understand your suggestion, I'd need to first execute something like this:

CREATE TABLE IF NOT EXISTS
COMMUNITY (Type text default null, Value variant default -1);

(As far as I know, the COMMUNITY database contains zero as default, but uses lots of positive numbers)


Once the DB is created, I'd use something like this:

INSERT OR REPLACE INTO COMMUNITY (Type, Value) VALUES('MY_DUMMY_KEY', 1234);

So, at this point, I'd know the DB is created and populated with at least one record.

Now, I can look for Vox Populi - specific keys in COMMUNITY to see if VP is enabled:

UPDATE Policies    SET BuildingGoldMaintenanceMod = -33  
    WHERE Type='POLICY_TRADE_UNIONS' AND NOT EXISTS (SELECT * FROM COMMUNITY WHERE Type='COMMUNITY_CORE_BALANCE_UNITS' AND Value = 1);

If VP is enabled, it should find the NOT EXISTS subquery as false, so the statement would NOT execute. But if the only record is the dummy record I added, the subquery would fail, as the Type would not be found - returning TRUE?

 

[Enginseer]

This is why people gotta take Logical Philosophy lessons when addressing negatives.

Your code says:
Make Trade Unions give -33 BuildingGoldMaintenanceMod when it does not find COMMUNITY_CORE_BALANCE_UNITS with Value 1 (VP has this finding, so yes Trade Unions won't have the Update if VP is enabled).

If it does find, don't do anything because you haven't made a condition for it as seen here.

 

[LeeS]

You want your CREATE TABLE IF NOT EXISTS statement to repeat exactly the definition of all the columns as it is done by VoxP. I never used VoxP so I am not certain exactly how they defined the table.

You would not need to add a 'dummy' row.

If no rows exist in the table the game is fine with that: it would just not find a row with the 'match' you are looking for and the NOT EXISTS would be implemented when VoxP is not active.

 

[sman1975]

OK, I had some partial success with the syntax above. Other statements, not so much. Here's what worked:

CREATE TABLE IF NOT EXISTS COMMUNITY (Type text default null, Value variant default -1);
--INSERT OR REPLACE INTO COMMUNITY (Type, Value) VALUES('MY_DUMMY_KEY', 1234);


UPDATE Policies    SET IncludesOneShotFreeUnits = 0
    WHERE Type='POLICY_VOLUNTEER_ARMY' AND NOT EXISTS (SELECT * FROM COMMUNITY WHERE Type='COMMUNITY_CORE_BALANCE_UNITS');


UPDATE Policies    SET BuildingGoldMaintenanceMod = -33
    WHERE Type='POLICY_TRADE_UNIONS' AND NOT EXISTS (SELECT * FROM COMMUNITY WHERE Type='COMMUNITY_CORE_BALANCE_UNITS');

Here's what didn't work:

INSERT INTO Policy_BuildingClassYieldModifiers (PolicyType, BuildingClassType, YieldType, YieldMod) VALUES
    ('POLICY_VOLUNTEER_ARMY', 'BUILDINGCLASS_AIRPORT', 'YIELD_GOLD', 50)
    WHERE NOT EXISTS (SELECT * FROM COMMUNITY WHERE Type='COMMUNITY_CORE_BALANCE_UNITS' );


DELETE FROM Policy_FreeUnitClasses
    WHERE PolicyType = 'POLICY_VOLUNTEER_ARMY' AND UnitClassType='UNITCLASS_FOREIGNLEGION' AND NOT EXISTS (SELECT * FROM COMMUNITY WHERE Type='COMMUNITY_CORE_BALANCE_UNITS' );


UPDATE [Language_en_US] SET Text = 'Privatization' WHERE Tag = 'TXT_KEY_POLICY_TRADE_UNIONS' AND NOT EXISTS (SELECT * FROM COMMUNITY WHERE Type='COMMUNITY_CORE_BALANCE_UNITS' );

@Enginseer - I had to chuckle reading your comment - my head was kind of spinning back and forth with conditionals inside the NOT EXISTS query. Being dyslexic, I don't need much help confusing myself, but this one threw me quite a curve...


@LeeS - good to know. I'll adjust the code, but am obviously still putzing with it. Appreciate it!

 

[LeeS]

WHERE NOT EXISTS is going to fail in an INSERT INTO. I don't think INSERT INTO even has a WHERE clause available, as I recall.

https://www.sqlite.org/lang_insert.html

 

[sman1975]

I'm having success adding prereq techs using this syntax:

INSERT INTO Technology_PrereqTechs (TechType, PrereqTech) SELECT 'TECH_NUCLEAR_FUSION', 'TECH_SMAN_MANNED_SPACEFLIGHT' WHERE EXISTS (SELECT * FROM COMMUNITY WHERE Type='COMMUNITY_CORE_BALANCE_UNITS' AND Value= 1 );

So, I suppose I should follow my own notes....

 

[LeeS]

First I do this (it's XML, though)

<GameData>
	<BuildingClasses>
		<Row>
			<Type>BUILDINGCLASS_LRS_SCIENCE_EE_MARKER</Type>
			<DefaultBuilding>BUILDING_LRS_SCIENCE_EE_MARKER</DefaultBuilding>
			<Description>TXT_KEY_BUILDING_LRS_SCIENCE_EE_MARKER</Description>
		</Row>
	</BuildingClasses>
	<Buildings>
		<Row>
			<Type>BUILDING_LRS_SCIENCE_EE_MARKER</Type>
			<BuildingClass>BUILDINGCLASS_LRS_SCIENCE_EE_MARKER</BuildingClass>
			<Cost>-1</Cost>
			<FaithCost>-1</FaithCost>
			<PrereqTech>NULL</PrereqTech>
			<GreatWorkCount>-1</GreatWorkCount>
			<ArtDefineTag>NONE</ArtDefineTag>
			<MinAreaSize>-1</MinAreaSize>
			<NeverCapture>true</NeverCapture>
			<HurryCostModifier>-1</HurryCostModifier>
			<IconAtlas>CALCUL8OR_GIFTED_ATLAS</IconAtlas>
			<PortraitIndex>6</PortraitIndex>
			<Description>TXT_KEY_BUILDING_LRS_SCIENCE_EE</Description>
		</Row>

Then in an SQL file I do this to remove the buildnig from the Database if the Enlightenment Era mod is not active ('ERA_ENLIGHTENMENT' will not exist if the EE mod is not active).

DELETE FROM Buildings WHERE Type = 'BUILDING_LRS_SCIENCE_EE_MARKER' 
AND NOT EXISTS (SELECT 1 FROM Eras WHERE Type = 'ERA_ENLIGHTENMENT');

----------------------

Also the Language update might fail because Language_en_US is not actually part of the normal database. The data in the language tables are actually their own database and are not 'user-friendly' a lot of the time when reading or setting data 'cross-databases' within the same SQL statement. I usually define a seperate TXT_KEY and tell the game to use the other TXT_KEY when an SQL condition is 'X', like this

UPDATE Technologies 
SET Help = 'TXT_KEY_TECH_LRSDUMMY_RENAISSANCE_HELP_ENL', Civilopedia = 'TXT_KEY_TECH_LRSDUMMY_RENAISSANCE_DESC_ENL'
WHERE Type = 'TECH_LRSDUMMY_RENAISSANCE'
AND EXISTS (SELECT 1 FROM Eras WHERE Type = 'ERA_ENLIGHTENMENT');

 

[LeeS]

I'm having success adding prereq techs using this syntax:

INSERT INTO Technology_PrereqTechs (TechType, PrereqTech) SELECT 'TECH_NUCLEAR_FUSION', 'TECH_SMAN_MANNED_SPACEFLIGHT' WHERE EXISTS (SELECT * FROM COMMUNITY WHERE Type='COMMUNITY_CORE_BALANCE_UNITS' AND Value= 1 );

So, I suppose I should follow my own notes....

The SELECT statement will allow you to do so whereas from reading the SQL flowchart VALUES acts differently. You just have to make sure your SELECT entries are not so complex that the whole thing fails because SQLite can't understand what you want.

This works in a mod I never got around to publishing but it was a bit of a PITA to configure the needed syntax:

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';

Tho it's an UPDATE rather than an INSERT

 

[sman1975]

Finessing the text db makes sense - seems like there is always a catch of some kind. So, defining an alternate text key then changing the reference in the various Policies would seem a safer bet.

The problem in this particular file is that when it executes, VP would have already executed, if it were enabled. It makes a ton of changes to the Policies area, where as my little mod only makes a couple. If I touch the Policies table (et al) I'll do a lot of damage to what VP put in place. I'm trying to not touch these tables at all if VP is alive.

The SELECT statement will allow you to do so whereas from reading the SQL flowchart VALUES acts differently.

I realized this about an Ohnosecond after you mentioned it. It was something I experimented with for a couple of hours before I got right - just two days ago...

Thanks for all the help. I think I have enough to run with now.