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

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

Discussion in 'Mod Creation Help' started by Denver, Mar 22, 2020.

  1. Denver

    Denver Chieftain

    Joined:
    Oct 19, 2019
    Messages:
    4
    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:
    Code:
    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 :)
     
  2. LeeS

    LeeS Imperator

    Joined:
    Jul 23, 2013
    Messages:
    7,210
    Location:
    Illinois, USA
    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
    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.

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

    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.
     
    Last edited: Mar 22, 2020
  3. Denver

    Denver Chieftain

    Joined:
    Oct 19, 2019
    Messages:
    4
    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:

    Code:
    [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! :)
     
  4. LeeS

    LeeS Imperator

    Joined:
    Jul 23, 2013
    Messages:
    7,210
    Location:
    Illinois, USA
    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 likes this.
  5. Denver

    Denver Chieftain

    Joined:
    Oct 19, 2019
    Messages:
    4
    Recursive CTE:

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

    Code:
    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
     
    Last edited: Mar 24, 2020
  6. LeeS

    LeeS Imperator

    Joined:
    Jul 23, 2013
    Messages:
    7,210
    Location:
    Illinois, USA
    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.
     

Share This Page