LUA/SQL visibility

psparky · Apr 12, 2019

A Mod (Vox Populi) adds a column to the 'Worlds' table in sql:

Code:

ALTER TABLE Worlds ADD COLUMN 'ReformationPercentRequired' INTEGER DEFAULT 100;

and sets it for each world size in sql e.g.:

Code:

UPDATE Worlds
SET ReformationPercentRequired = '100'
WHERE Type = 'WORLDSIZE_STANDARD';

In LUA code in the UI, I can access vanilla 'Worlds' columns like this:

Code:

GameInfo.Worlds[Map.GetWorldSize()].MaxActiveReligions

When I try something similar for the added column it returns nil:

Code:

GameInfo.Worlds[Map.GetWorldSize()].ReformationPercentRequired

Is there a way to access the new column from LUA? I'm guessing that perhaps this problem arises because of the use of sql rather than xml.

I tried posting in the Vox Populi forum but nobody could help and it's really more of a general modding question anyway.

Thanks for any help!

whoward69 · Apr 12, 2019

There is no difference (at the database level) to using XML or SQL (the XML is translated into SQL anyway)

psparky · Apr 13, 2019

whoward69 said:
There is no difference (at the database level) to using XML or SQL (the XML is translated into SQL anyway)

Thank you for the reply and that certainly eliminated some fruitless lines of investigation.

I have continued to work on the problem by printing out the contents of the table returned from GameInfo. It does not contain the added columns but I also see that it has the vanilla values rather than those changed by the mod (the mod is definitely active and I can see it's effects in game). It looks like my technique of using GameInfo in this way does not work. It seems that I will need to use a database query to get the information, which I have not used before. Looking at other examples, I think I need something like this:

Code:

local g_ReformationPercentRequired
for row in DB.Query("SELECT ReformationPercentRequired FROM Worlds WHERE Type = Map.GetWorldSize()") do
    g_ReformationPercentRequired = row.ReformationPercentRequired
end

Does that look about right? Or is there a better technique I could use?

Thanks again.

bc1 · Apr 13, 2019

It won't work that way: SQL cannot evaluate Map.GetWorldSize(). You need to pre-evaluate it and include the result value in the string.
And that loop is a pretty inefficient way of doing things...

LeeS · Apr 13, 2019

If it were me for trouble-shooting purposes I'd check whether this code results in nils or correct values in the lua.log:

Code:

for row in GameInfo.Worlds() do
	print("ID # " .. row.ID .. " is Type  " .. row.Type .. " : ReformationPercentRequired is " .. tostring(row.ReformationPercentRequired))
end

I've added columns to existing tables before and had no trouble extracting via lua the various settings within each row where I added data for that column to existing and new rows.

I didn't think there were any issues relating to a column added to a parent table not being readable via lua, but for full disclosure (as it were) the table I added the column to was not a parent table with column "ID". And I traversed the table on game-setup and then saved the data from the database into an lua table which I then persisted between saves and never after read the data within the database for the remainder of that user's playthrough.

Spoiler :

Although looking at it now, my column add is a bit different

Code:

ALTER TABLE Unit_UniqueNames ADD CivilizationType text REFERENCES Civilizations(Type) default null;

In that I am not using text string designators for the name of the new column. My SQL-foo is not of a high enough level to remember offhand whether that makes any difference to proper addition of the column.

whoward69 · Apr 13, 2019

I think the issue is related to tables that are used pre-mod loading (eg in the game setup) are handled differently, but my memory of such things is poor at best

psparky · Apr 14, 2019

bc1 said:
It won't work that way: SQL cannot evaluate Map.GetWorldSize(). You need to pre-evaluate it and include the result value in the string.
And that loop is a pretty inefficient way of doing things...

Thank you for pointing out that embarrassing error :blush:

. Once I corrected this it worked fine. Even more embarrassingly, I have not been able to find the correct syntax to remove the loop, despite thinking I'd cracked it several times. In this case, it doesn't really matter as I only need to evaluate it once on initialization, but it would be interesting to know how.

My current code:

Code:

for row in DB.Query(string.format("SELECT ID, ReformationPercentRequired FROM Worlds WHERE ID = %d", Map.GetWorldSize())) do
     reformationPercentRequired = row.ReformationPercentRequired
end

Lee, thanks for our input. I have established that ReformationPercentRequired from GameInfo is nil and also printed out the whole table (for one mapsize):

Code:

local info = GameInfo.Worlds[Map.GetWorldSize()]
 print("AFW info:", info, info.Description, info.MaxActiveReligions, info.ReformationPercentRequired)
 for x,y in pairs(info) do
     print(x,y)
end

Spoiler Output :

None of the three new columns added by the mod are present and the value of MaxActiveReligions is set to 4 by the mod (this is an old VP version for any VP players reading this), but this shows the vanilla value of 5.

Unless anyone has any other ideas, I'm going to assume that the DB query is needed and look at other places where I have used GameInfo.

Thanks again to all.

whoward69 · Apr 14, 2019

You don't need the string.format

Code:

for row in DB.Query("SELECT ID, ReformationPercentRequired FROM Worlds WHERE ID = ?", Map.GetWorldSize()) do

Infixo · Apr 24, 2019

I confirm what @whoward69 said.
There are a few tables that are used in front-end functionalities and they are loaded into GameInfo only once in their "vanilla" state.
I encountered the same problem when doing some updates to VP UI, can't recall which table it was, but it was related to some game parameters.
The only way to get the modded values was via DB.Query.

LUA/SQL visibility

psparky

King

whoward69

DLL Minion

psparky

King

bc1

LeeS

Imperator

whoward69

DLL Minion

psparky

King

whoward69

DLL Minion

Infixo

Deity

Similar threads