[GS] I think I've almost made Stock Exchange give gold per citizen - but I've gone wrong somewhere.

[Denver]

I'm trying to make it so that every Stock Exchange will give 2 gold per citizen in the city, up to 255 citizens. (In the future +1 when unpowered, another +1 when powered or something similar).

Here is what I have so far in an sql file:

DECLARE @cnt INT = 1;

WHILE @cnt < 256
BEGIN
    @seyield = 'BUILDING_STOCK_EXCHANGE_YIELD_' + @cnt;
    @secitizens = 'BUILDING_STOCK_EXCHANGE_CITIZENS_' + @cnt;
    @secount = 'COUNT_CITIZENS_' + @cnt;
    
    INSERT INTO
        BuildingModifiers (BuildingType, ModifierId)
    VALUES
        ('BUILDING_STOCK_EXCHANGE', @seyield);
    
    INSERT INTO
        Modifiers (ModifierId, ModifierType, RunOnce, Permanent, SubjectRequirementSetId)
    VALUES
        (@seyield, 'MODIFIER_BUILDING_YIELD_CHANGE', 0, 0, @secitizens);
    
    INSERT INTO
        ModifierArguments (ModifierID, Name, Value)
    VALUES
        (@seyield, 'BuildingType', 'BUILDING_STOCK_EXCHANGE'),
        (@seyield, 'Amount', '2'),
        (@seyield, 'YieldType', 'YIELD_GOLD');
    
    INSERT INTO
        RequirementSets(RequirementSetId, RequirementSetType)
    VALUES
        (@secitizens, 'REQUIREMENT_TEST_ALL');
    
    INSERT INTO
        RequirementSetRequirements(RequirementSetId, RequirementId)
    VALUES
        (@secitizens, @secount);
    
    INSERT INTO
        Requirements(RequirementId, RequirementType)
    VALUES
        (@secount, 'REQUIREMENT_COLLECTION_ATLEAST');
        
    INSERT INTO
        RequirementArguments(RequirementId, Name, Value)
    VALUES
        (@secount, 'CollectionType', 'COLLECTION_CITY_PLOT_YIELDS'),
        (@secount, 'Count', '1');
    
   SET @cnt = @cnt + 1;
END;

In game, when building a Stock Exchange in an unpowered city, with only this mod enabled, the gold per turn increases by exactly the default value (4), ignoring the additional intended effect. Other basic changes in the same modfile can work.

What I think I might've done wrong:

1. Made one tiny spelling mistake or wrong ID somewhere.
   • I'm way too tired right now so this is really likely and will check again after some sleep.
2. Maybe Civ 6 SQL doesn't support variables, or WHILE, or string concatenation for some reason.
3. I've completely dropped the ball and what I'm trying to do will never work with this method and should try a different approach.

Does anyone have any advice or can point me in the right direction? I will also try looking at this again after I've had some sleep in case I can spot what I'm doing wrong. Thank you

 

[LeeS]

What is in Database.log and what is in the game's database for the affected tables after you execute the code of your mod ?

Generally for Civ6 we don't use While loops or declare variable statements but I do not know for a fact that they will not work.

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

Database.log is an openable text file and is at

C:\Users\user\Documents\My Games\Sid Meier's Civilization VI\Logs

It's better to open the Database.log file using Notepad++ rather than old Notepad since the encoding used does not come across correctly for old Notepad.

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

The database itself can be opened by any SQLite database viewer program or any "regular" SQL database viewer program which can open the SQLite type of file.

C:\Users\user\Documents\My Games\Sid Meier's Civilization VI\Cache/DebugGameplay.sqlite

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

1. Start the game
2. make sure your mod is enabled
3. start a test game
4. once you are in-game and can move your first units, exit directly to desktop. This will cause the game to recreate the database with your mod enabled and will preserve the contents of the Database.log file and the DebugGameplay.sqlite file.

Every time you start Civ6 the Database.log and DebugGameplay.sqlite files are wiped and then re-created as first the base game loads and then any mods and expansions. If you exit back to the main menu of the game this can cause the database and the log file to be restructured yet again depending on exactly what you do after returning to the main menu. So it is always better when searching for debugging answers to exit directly to desktop so you can examine what occurred.

 

[Denver]

Thank you for the comprehensive reply!

I have opened the Database.log file following your instructions exactly and I think I've found the problem:

[110162.786] [Gameplay] ERROR: near "DECLARE": syntax error

I also managed to open the DebugGameplay.sqlite file by converting it into an SQL file and then importing it into some software called PHPMyAdmin (a XAMPP webserver based MySql admin tool) and confirmed that in the BuildingModifiers table, no record was with the BuildingType "BUILDING_STOCK_EXCHANGE".


So all the evidence suggests Civ 6 SQL doesn't like variables or uses a syntax have not used. Back to the drawing board I guess...

Thank you for the help, I was not aware of these debug tools!

 

[LeeS]

Google searching turns up expertise pages stating that SQLite (it's called that for a reason) doesn't support variables or procedures. So, yeah, since Civ6 doesn't need the extra capability of "full" SQL it won't do a lot of things that "full" SQL will do.

This might be of use to you to give you a better feel for what SQLite will and won't do: https://www.sqlite.org/syntaxdiagrams.html

 

[Denver]

Recursive CTE:

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

create temporary table temptable(cnt int, seyield text, secitizens text, secount text);

with
  recursive constants as (
    select
      'BUILDING_STOCK_EXCHANGE_YIELD_' seyield,
      'BUILDING_STOCK_EXCHANGE_CITIZENS_' secitizens,
      'COUNT_CITIZENS_' secount
  ),
  numbers as (
    select 1 cnt
    from constants
    union all
    select cnt + 1 from numbers
    where cnt < 255
  ),
  cte as (
    select
      n.cnt cnt,
      c.seyield || n.cnt seyield,
      c.secitizens || n.cnt secitizens,
      c.secount || n.cnt secount
    from numbers n cross join constants c
  )

insert into temptable
select * from cte;

INSERT INTO BuildingModifiers (BuildingType, ModifierId)
SELECT 'BUILDING_STOCK_EXCHANGE', seyield FROM temptable;

INSERT INTO Modifiers (ModifierId, ModifierType, RunOnce, Permanent, SubjectRequirementSetId)
SELECT seyield, 'MODIFIER_BUILDING_YIELD_CHANGE', 0, 0, secitizens FROM temptable;

INSERT INTO ModifierArguments (ModifierID, Name, Value)
SELECT seyield, 'BuildingType', 'BUILDING_STOCK_EXCHANGE' FROM temptable
UNION ALL
SELECT seyield, 'Amount', '2' FROM temptable
UNION ALL
SELECT seyield, 'YieldType', 'YIELD_GOLD' FROM temptable;

INSERT INTO RequirementSets(RequirementSetId, RequirementSetType)
SELECT secitizens, 'REQUIREMENTSET_TEST_ALL' FROM temptable;

INSERT INTO RequirementSetRequirements(RequirementSetId, RequirementId)
SELECT secitizens, secount FROM temptable;

INSERT INTO Requirements(RequirementId, RequirementType)
SELECT secount, 'REQUIREMENT_COLLECTION_COUNT_ATLEAST' FROM temptable;

INSERT INTO RequirementArguments(RequirementId, Name, Value)
SELECT secount, 'CollectionType', 'COLLECTION_CITY_PLOT_YIELDS' FROM temptable
UNION ALL
SELECT secount, 'Count', cnt FROM temptable;

It works!

But actually no.

I made a test city, Niani, on Standard Speed, starting from Modern Era, no barbarians, but everything else default.

This Niani had 11 population and 20 turns until next population. +5% Gold due to +2 Amenities.

2 other cities existed, with 5 and 6 population.

The important part of the default Stock Exchange text (i.e. in the tech tree) reads: +2 Gold for every citizen when living in this city. (I put that there). +4 gold. + 7 gold additionally when powered.

The Stock Exchange built in Niani reads: +2 Gold for every citizen when living in this city. (I put that there). +44 gold. + 7 gold additionally when powered. While alt+tabbing between here and the game.

This extra 44 gold is applying exactly as it said I would. -4 from default, and that leaves 40 gold coming out of nowhere, as opposed to the expected 22. If the other 2 cities had the same effect applied, they would account for 10 and 12 gold respectively, which totals 22. Possibly the Stock Exchange accounts for ALL city population. However, 1 turn later and the 6 pop city became 7, but the 44 Gold remained. Another turn later, and Niani became 12 population. But the +44 gold remained unchanged. I donated both non-capital cities to another player and the +44 gold remained. Niani made 3 settlers in a row, lowering the pop to 8, and still +44 gold. I then made a 4th city (my second because of trading off other 2), Timbuktu, and the Stock Exchange text is for +11 Gold, despite city having 5 population. Niani remains at +44.

This will require some looking at!

Update: Niani's Stock Exchange got pillaged after a dust storm. After fixing it, it now has +46 Gold, despite being at 10 population (was +44). Timbuktu had exactly the same scenario and is at +32 Gold while at 6 population (was +11 at 5 population). Wild! Total population (1 sold city flipped back to me, I founded another) is 17. Total population with Stock Exchange access is 16 (or 10 before Timbuktu completed repair).

This is getting me nowhere it seems, I'm gonna check the tables

 

[LeeS]

Part of your problem would seem to me to be the fact that you are counting the size of the COLLECTION_CITY_PLOT_YIELDS, which actually has nothing whatever to do with city population, and could never exceed a total plot count of 37. A city can only get yields (and therefore COLLECTION_CITY_PLOT_YIELDS only applies to) the plots the city "owns" which are within the 3-tile range of the city-center, plus theoretically the city center itself.