Stuck on generating localized text in SQL using gameplay tables

[TC_]

So I finished up the functionality of a new mod, but I'm not finding a solution to generate localized text dynamically, as the rest of the mod works.

I'm needing to start the localizedtxt sql file with a table like this...

CREATE TABLE Resources_TC_Text(ResourceType varchar(100), ResourceName varchar(100) null, PrereqTech varchar(100) null);

INSERT INTO Resources_TC_Text(ResourceType, ResourceName, PrereqTech)
SELECT ResourceType, null, PrereqTech FROM Resource_Harvests;

...but as soon as I try to reference a gameplay table (Resource_Harvests), the UpdateText file won't load. Is there some workaround to give an UpdateText file access to gameplay database tables?

 

[LeeS]

You cannot. Whether you use SQL or XML it is impossible. All you can do within a Gameplay or Frontend "UpdateDatabase" table is to redirect a Tag-name that is to be used, but you cannot access the Text information for a Tag from the Gameplay or Frontend databases, nor can you access UpdateDatabase Gameplay or Frontend tables from within an UpdateText type of action.

The reason for this is that the "UpdateText" localizations are contained within a separate and sandboxed database.

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

Also the construction of this table is a bit confusing

CREATE TABLE Resources_TC_Text(ResourceType varchar(100), ResourceName varchar(100) null, PrereqTech varchar(100) null);

What in the world the is "varchar(100)" attempting to signify in the definition of the column attributes the table will require ?

OK I looked that up. Never seen anyone use that SQL option before in Civ5 or Civ6. I would not use that SQL command as it is not going to gain you anything really. Just use "text" since the game's localization database already contains the needed limitations as to length of text that can be asserted for a Text field (and you really have to work at hitting the limit in most cases except civilopedia entries).

I assume the SQLite version used by Civ6 will accept the varchar command, but it may not as it is still a bit of a customized implementation on top of being SQLite rather than "High" SQL as it were.

 

[Gedemon]

could you provide a bit of context ?

would dynamically creating the text tag (or merge multiple text tags if that's what you need) into the gameplay database work ?

 

[LeeS]

could you provide a bit of context ?

OP or me as far as the providing context goes ?

 

[Gedemon]

OP or me as far as the providing context goes ?

OP, to understand the goal.

 

[TC_]

I began to assume as much, thanks as always LeeS.

Gedemon, I'd be happy to, thank you.

The mod adds the ability to harvest(remove) any strategic or luxury resource. I generate the Resource_Harvests data in a way that will pull all resources from the gameplay database, and define their harvest yields based on theirresourceclasstype (if luxury) or their revealEra (if strategic).

The goal is to add a line onto technologies descriptions that will read, such as for mining: "Allows harvesting of Diamonds, Gypsum, Salt"...etc. But, I need to generate this text within the mod and then add it to the end of the description. I have the following table producing the data I need:

Now I just need to slap the PrereqTechDescriptionTextAddition column onto the end of the technology descriptions.

Any good way to do that?

 

[LeeS]

UPDATE Buildings SET Description='Our Cheeseburgers are Cheesy and Delicious' WHERE BuildingType='BUILDING_BURGERSTAND';

This bypasses the localization system and causes the game to use the text directly entered for the "Description" column, but at the cost that it completely borks the LocalizedText system for those game elements where you do this.

In order to make use of this capability, however, you would need to physically and individually hunt down and copy/paste the original text that is used for LOC_X and then add your additions to it. At which point you may as well just do like:

UPDATE Buildings SET Description='LOC_BURGERSTAND_CHEESEBURGER_DELICIOUS' WHERE BuildingType='BUILDING_BURGERSTAND';

And then add the new tag into the LocalizedText table with the complete text that needs to be displayed when your mod is enabled. Dynamism isn't really all that easy when working to the localizations, unfortunately.

Another trick you can do is like this as I did for a Civ5 mod of mine where localization texts were re-directed to a different Tag-Name when the Civ5 Enlightenment Era mod was also enabled:

UPDATE Buildings 
SET Strategy = 'TXT_KEY_BUILDING_LRS_SAILMAKER_STRATEGY_EE', Help = 'TXT_KEY_BUILDING_LRS_SAILMAKER_HELP_EE'
WHERE Type = 'BUILDING_LRS_SAILMAKER'
AND EXISTS (SELECT 1 FROM Eras WHERE Type = 'ERA_ENLIGHTENMENT');

And then I had to define these two alternative TXT_KEY (ie, LOC_SOMETHING) tags in the Civ5 version of the localization database. And I had to ensure that the Enlightenment Era mod would always load first when both mods were enabled.

The 'insurance' as to which mod loads first is much easier and more stable in Civ6 than it actually was for Civ5, because all that is needed is a separated UpdateDatabase Action with a high LoadOrder value to ensure your SQL code will execute last if there are more than just your mod that are commonly used and which affect the Resource Harvesting table. But this part of the issue(s) really only apply when attempting to make compatibility to one or more mods other than your own.

 

[TC_]

I assume there is nothing along these lines that would work, where it would combine the localized text with a new string?

UPDATE Buildings SET Description='LOC_BURGERSTAND_CHEESEBURGER_DELISIOUS'||'Allows harvesting of delicious cheeseburgers.' WHERE BuildingType='BUILDING_BURGERSTAND';

I'm just trying to avoid overwriting any changes to localized text that are done in mods which are loading before this one. I can live without solving this, and just bypass the localized text as you showed, but that's not the most ideal solution.

 

[LeeS]

I assume there is nothing along these lines that would work, where it would combine the localized text with a new string?

UPDATE Buildings SET Description='LOC_BURGERSTAND_CHEESEBURGER_DELISIOUS'||'Allows harvesting of delicious cheeseburgers.' WHERE BuildingType='BUILDING_BURGERSTAND';

I'm just trying to avoid overwriting any changes to localized text that are done in mods which are loading before this one. I can live without solving this, and just bypass the localized text as you showed, but that's not the most ideal solution.

Unfortunately this approach won't work because the concatenation would occur upon the LOC Tag-Name and not the Text contained within the Tag-Name. So the game would go looking for a Tag-Name called

LOC_BURGERSTAND_CHEESEBURGER_DELISIOUSAllows harvesting of delicious cheeseburgers.

Leading to numerous funs and excitements not the least of which would be localization errors because Tag-Names cannot contain spaces or special characters such as period symbols. You'd literally get the following shown In-Game

LOC_BURGERSTAND_CHEESEBURGER_DELISIOUSAllows harvesting of delicious cheeseburgers.

As soon as you alter the in-game text that displays as a result of your mod running you are by definition over-writing the changes made to the same text-tag by any and all other mods. Whether you use the Localization System or bypass it by directly entering the text that is to be used for a Description, you are over-writing any changes already made by any other mod or expansion pack. If another mod loads after yours that is also adjusting the text used for the same Description, that mod by definition will be over-writing the changes you made. The exception to this rule is where another mod is directly altering the Text data in the LocalizedText table but your mod is altering the Gameplay "Description" to use a different Tag-Name or you are bypassing the localization system entirely.

You have to keep the "Highander" rule in mind at all times when modding:

At the end of the game-loading process, there can be only one alteration to the same game-element that survives the loading process.

--LeeS

 

[TC_]

Your response, and thinking of how [ICON_DELICIOUS_CHEESEBURGER] is used in text, i was inspired to try:
SET Description = '[LOC_TECH_ENGINEERING_DESCRIPTION] test'
That's a big nope as well (which of course you already know).

Looks like I'll just be overriding with my new text, that I'll manually grab from the original localization database and combine with my new string.

This look like the best way to you?

UPDATE Technologies
SET Description =  '***DEFAULT LOCALIZATION TEXT*** '||
(SELECT PrereqTechDescriptionTextAddition FROM Resources_TC_Harvestable_Text_Grouped WHERE PrereqTechDescription = 'LOC_TECH_ANIMAL_HUSBANDRY_DESCRIPTION_ADDON')
WHERE TechnologyType = 'TECH_ANIMAL_HUSBANDRY';

 

[LeeS]

It looks reasonable. Of course you'll have to try it in-game to ensure there aren't any little goof typos that aren't apparent, and that there aren't logical SQL errors in the code that constructs the argument-data for column 'PrereqTechDescriptionTextAddition'

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

You'll have to pardon me for an hour or two: there's some Aztec stuff that needs pillaging because Monty is Monty and needs to learn the lesson that Rome Always Rules.

 

[Gedemon]

You can use

{LOC_SOMETHING1} {LOC_SOMETHING2}

in a text field in the gameplay DB (and in Lua when using the "locale." methods IIRC) the game with translate both.

This is working for example (my Resources table has extra fields) to automatically generate new ressources names for each technology and a type of storage (book, scrolls, etc...)

/* Create Resources from Technologies */
INSERT OR REPLACE INTO Resources (ResourceType, Name, ResourceClassType, Frequency, FixedPrice, SpecialStock, TechnologyType)
    SELECT 'RESOURCE_KNOWLEDGE_' || Technologies.TechnologyType , '{' || Technologies.Name || '} {LOC_RESOURCECLASS_KNOWLEDGE}', 'RESOURCECLASS_KNOWLEDGE', 0, 1, 1, Technologies.TechnologyType
    FROM Technologies;
INSERT OR REPLACE INTO Resources (ResourceType, Name, ResourceClassType, Frequency, FixedPrice, SpecialStock, TechnologyType)
    SELECT 'RESOURCE_TABLETS_' || Technologies.TechnologyType , '{' || Technologies.Name || '} {LOC_RESOURCECLASS_TABLETS}', 'RESOURCECLASS_TABLETS', 0, 0, 1, Technologies.TechnologyType
    FROM Technologies;
INSERT OR REPLACE INTO Resources (ResourceType, Name, ResourceClassType, Frequency, FixedPrice, SpecialStock, TechnologyType)
    SELECT 'RESOURCE_SCROLLS_' || Technologies.TechnologyType , '{' || Technologies.Name || '} {LOC_RESOURCECLASS_SCROLLS}', 'RESOURCECLASS_SCROLLS', 0, 0, 1, Technologies.TechnologyType
    FROM Technologies;
INSERT OR REPLACE INTO Resources (ResourceType, Name, ResourceClassType, Frequency, FixedPrice, SpecialStock, TechnologyType)
    SELECT 'RESOURCE_BOOKS_' || Technologies.TechnologyType , '{' || Technologies.Name || '} {LOC_RESOURCECLASS_BOOKS}', 'RESOURCECLASS_BOOKS', 0, 0, 1, Technologies.TechnologyType
    FROM Technologies;
INSERT OR REPLACE INTO Resources (ResourceType, Name, ResourceClassType, Frequency, FixedPrice, SpecialStock, TechnologyType)
    SELECT 'RESOURCE_DIGITAL_' || Technologies.TechnologyType , '{' || Technologies.Name || '} {LOC_RESOURCECLASS_DIGITAL}', 'RESOURCECLASS_DIGITAL', 0, 0, 1, Technologies.TechnologyType
    FROM Technologies;

 

[TC_]

Thanks again guys. I finished this mod up and released it on steam. I posted the link in the forums here if you'd like to take a look - https://forums.civfanatics.com/threads/removable-resources-and-features.655951/