SQL, automated values in Building_Flavors table (for TC modders or else)

Artisanix · Dec 25, 2014

Well, in short I was sort of inspired by Pazyryk's Tables SQL for total conversion modders thread, especially by "Don't be an Ox" section ;]

It gave me a thought what kind of tables we could make with SQL to fill by itself, to save our time for more creative things. This can be definitely usefull for modders who deal with bigger projects like compillations or total conversions, etc. Pazyryk has already dealt with some simple tables like UnitClasses and BuildingClasses in his thread, but how about a bit more complex stuff like Building_Flavors for instance? Can it be really done? Why I can hear "you must be kidding" from somewhere? ;]

Let's try to crack the process...

How the things are done currently?
Well, usually you have a building that increases certain values (culture, gold, science, etc.), then you check what kind of values are those, and you make a decision to what category of flavors it should be assigned. Then you need to set a particular value how important that bulding should be for the AI. And all of this manually, for each and every building.

If you have one or few buildings in your mod, this is relatively easy. But what if you have them in hundreds? Example: 500 buildings(1) multiplied by three flavors average for each(2) and we have about 1500 entries - anyone eager to edit each and every one by hand? ;]

(1) Such mod as CCTP has even more - latest "Xmass" version has over 1100 entries in buildings table [at attention!] salute to its team! [at ease]
(2) brief calculations from original unmodded game: 258 entries overall in Building_Flavors table, and 92 unique values for BuildingType - 258/92=~2.8

And here is another idea...
What if we could make such queries in SQL which could check whether certain building meets the criteria for particular flavor and add such entry to Building_Flavor table automatically. And then use no more than 1-3 additional queries which could increase the values for more important buildings? And again this in an automated way.

And actually that's quite possible...

"Another idea" in action (in details)...
For the purpose of this example I use original database and with no mods enabled (Civ5CoreDatabase.db). In fact I use the copy where everything from Building_Flavors table has been deleted, and I use unmodified one for reference, so let's pretend that there is nothing in original database in Building_Flavors table and it needs to be filled with values.

First let's see what flavors actually are used by all the buildings,

following query should list all flavors present in Building_Flavors table currently:

Code:

SELECT DISTINCT FlavorType FROM Building_Flavors ORDER BY FlavorType ASC;

which in my case returned this result:

Spoiler :

Well, let's start with the first one from the list then - FLAVOR_CITY_DEFENSE.

Q: What kind of buildings are classified to have this flavor?
A: Mostly those that increase Defense and ExtraCityHitPoints in Buildings table with few exceptions.

We can check this by quering DB:

Code:

SELECT *
FROM Building_Flavors
WHERE FlavorType='FLAVOR_CITY_DEFENSE'
ORDER BY Flavor DESC;

result (8):

Spoiler :

So it's relatively safe to assume that every building that adds any value to Defense and ExtraCityHitPoints columns in Buildings table should have FLAVOR_CITY_DEFENSE entry present in Building_Flavors table.
Let's check it with the following query:

Code:

SELECT Type, Defense, ExtraCityHitpoints
FROM Buildings
WHERE Defense > 0 OR ExtraCityHitPoints > 0
ORDER BY Defense DESC;

result (6):

Spoiler :

Hmm, a bit different. One is added (Palace), three are missing (Kremlin, Great Wall, Himeji Castle). Well, these are our exceptions. However the query is successful for choosing majority of buildings that meet the criteria to be assigned to CITY_DEFENSE flavour group.

What about these exceptions then?
PALACE - no problem here, as the palace is available since the start for free, and if you make a TC where you have additional era before ancient then it can be useful for you to give the palace this flavor actually - so good news is we don't need to do anything to "fix" this.
KREMLIN - while the wonder had defensive bonuses in vanilla or G&K, those were removed in BNW, so Kremlin can't be clearly cosidered as CITY_DEFENSE oriented building any more. So our query actually "fixes" this by removing this entry. Good.
GREAT WALL - this one probably was flagged as city defense because of that effect which slows all enemies down while moving in our territory. We have two ways to ammend this: 1) add additional check to WHERE clause (WHERE Defense > 0 OR ExtraCityHitPoints > 0 OR BorderObstacle > 0); or 2) add this building as an exception with others in simple SQL query which will be discussed below.
HIMEJI CASTLE - this one is classified as CITY_DEFENSE probably because that it adds a free castle, and adds bonus promotion 15% combat strength in friendly lands. This looks too complex to make it generic in an instant, so I opted for an exception here.

Time to build the code with the current knowledge we have:

Code:

SELECT Type, 'FLAVOR_CITY_DEFENSE', 10
FROM Buildings
WHERE Defense > 0 OR ExtraCityHitPoints > 0 OR BorderObstacle > 0
UNION
SELECT Type, 'FLAVOR_CITY_DEFENSE', 10
FROM Buildings
WHERE Type IN (
	'BUILDING_HIMEJI_CASTLE'
);

What it does?
First select query lists all buildings that meet generic criteria to be classified as City Defense flavored(Defense/ExtraCityHitPoints/BorderObstacle) and second query just adds our exceptions that don't meet the generic criteria but we want for some other reasons to classify these as City Defense oriented buildings. Results from both queries are joined together with the UNION clause.
The code also fills additional columns with proper data we can insert in Building_Flavors table. The result is like this:

Spoiler :

That's the good start. But all flavors are the same, so there's no variety in here, and we want buildings with greater bonuses to be more flavored than the weak ones, right?
And for this we can use the math. A really basic math. You don't even need to be an operator of your pocket calculator ;] So let's replace static number 10 with some calculations that take into consideration actual values.

How about this then:
10 + Defense/100*2 + ExtraCityHitpoints/10 + BorderObstacle*20
? ;D

Explanation:
10 - this static number is added for all buildings that meet the generic criteria for city defense, so no such buildings can have lower flavor than this.
Defense/100*2 - produces values from range 2-24 for default buildings that have Defense from range 250-1200, this calculation is added to those base 10 (larger values are added for buildings that have more Defense)
ExtraCityHitpoints/10 - this returns values from 2-10 range, and this range is lower than results of Defense calculation, which is good because higher Defense slows the time needed to capture city more than its hit points, so it should be more favored. And this calculation is added to overall value.
BorderObstacle*20 - this value is so powerful that if we like a challenge we should tell AI that it's definitely worth to build it against us ;] Of course, all the buildings that have it set to 0 (false) receive no bonus from this calculation. This is also added to final value.

If we add all these calculations together and use in a query the results may be quite promising:
(ORDER BY is not normally needed, it was added to sort results for convenience in this example)

Code:

SELECT Type, 'FLAVOR_CITY_DEFENSE', 10 + Defense/100*2 + ExtraCityHitpoints/10 + BorderObstacle*20
FROM Buildings
WHERE Defense > 0 OR ExtraCityHitPoints > 0 OR BorderObstacle > 0
UNION
SELECT Type, 'FLAVOR_CITY_DEFENSE', 10
FROM Buildings
WHERE Type IN (
	'BUILDING_HIMEJI_CASTLE'
)
ORDER BY 3 DESC;

result:

Spoiler :

Very interesting, but we left Himeji Castle behind

Time to think what we can do with exceptions - in other words buildings that do not meet certain generic criteria, but we think that they should be added here to have such particular flavor.

And here's yet another idea (a sub-idea?):
If we have many of such unique buildings that don't meet generic flavor group criteria and still we want to add them to such flavor group, then we don't want to work with them individually, as this would cause this whole automatic process inefficient, and unecessary. So what we do instead is we assign these exceptions to groups. And the less groups we need to create the better.

In our scenario we have only one such unique building - Himeji Castle. But let's pretend that we want to bring back the Kremlin here for whatever reasons as well. The point is that these two has so different properties that it may not be the best option to give them the same city defense flavor values.

Seems that Himeji Castle should be in higher group here than Kremlin. So let's agree that there are two flavor groups for our exceptions: important and least important. Himeji Castle will be in the former, Kremlin in the latter. Those building that are in important group should have values not lower than 20, and those not important should have values of just 5. Of course if you have more such buildings, just enter additional names in proper WHERE clauses separated by comas.

Let's look at the code:

Code:

SELECT Type, 'FLAVOR_CITY_DEFENSE', 10 + Defense/100*2 + ExtraCityHitpoints/10 + BorderObstacle*20
FROM Buildings
WHERE Defense > 0 OR ExtraCityHitPoints > 0 OR BorderObstacle > 0
UNION
SELECT Type, 'FLAVOR_CITY_DEFENSE', 20
FROM Buildings
WHERE Type IN (
	'BUILDING_HIMEJI_CASTLE'
)
UNION
SELECT Type, 'FLAVOR_CITY_DEFENSE', 5
FROM Buildings
WHERE Type IN (
	'BUILDING_KREMLIN'
)
ORDER BY 3 DESC;

and the result (acceptable me thinks):

Spoiler :

FINALE
Finally time to combine this with DELETE/INSERT query, and lucky we only need to add two additional lines of code to the previous query and delete ORDER BY line:

Code:

DELETE FROM Building_Flavors WHERE FlavorType='FLAVOR_CITY_DEFENSE';

INSERT INTO Building_Flavors (BuildingType, FlavorType, Flavor)
SELECT Type, 'FLAVOR_CITY_DEFENSE', 10 + Defense/100*2 + ExtraCityHitpoints/10 + BorderObstacle*20
FROM Buildings
WHERE Defense > 0 OR ExtraCityHitPoints > 0 OR BorderObstacle > 0
UNION
SELECT Type, 'FLAVOR_CITY_DEFENSE', 20
FROM Buildings
WHERE Type IN (
	'BUILDING_HIMEJI_CASTLE'
)
UNION
SELECT Type, 'FLAVOR_CITY_DEFENSE', 5
FROM Buildings
WHERE Type IN (
	'BUILDING_KREMLIN'
);

Above code deletes every entry in Building_Flavors table for FLAVOR_CITY_DEFENSE, then re-populates it with new generated data for every building that meets generic criteria to have this flavor, and for those few unique exceptions that have been listed in additional select queries.

Well, there's only one thing more we could do here. This INSERT code don't differentiate between the normal building and national or world wonder. We can fix this by adding two additional queries afterwards that modify already inserted values:

this query increases flavor for city defense value by 20 if the building is world wonder:

Code:

UPDATE Building_Flavors
SET Flavor = Flavor + 20
WHERE BuildingType IN (
	SELECT BuildingType
	FROM Building_Flavors
	INNER JOIN BuildingClasses ON Building_Flavors.BuildingType = BuildingClasses.DefaultBuilding
	WHERE BuildingType=BuildingClasses.DefaultBuilding
	AND BuildingClasses.MaxGlobalInstances > 0
)
AND FlavorType='FLAVOR_CITY_DEFENSE'
;

and this by 10 if it is national wonder:

Code:

UPDATE Building_Flavors
SET Flavor = Flavor + 10
WHERE BuildingType IN (
	SELECT BuildingType
	FROM Building_Flavors
	INNER JOIN BuildingClasses ON Building_Flavors.BuildingType = BuildingClasses.DefaultBuilding
	WHERE BuildingType=BuildingClasses.DefaultBuilding
	AND BuildingClasses.MaxPlayerInstances > 0
)
AND FlavorType='FLAVOR_CITY_DEFENSE'
;

After all these four queries are executed we end up with such data related to city defense flavor in the Building_Flavors table:

Spoiler :

BENEFITS?
If you decide to include this (or similar) piece of code in your TC or compillation, then every time you add a building that is related to city defense, you won't have to bother yourself with setting up this flavor for it.
And of course, if such code will be provided for all the flavors (original game has these in number of 38), then with one relatively short .sql file you take care of all building flavors you have in your mod, and those you may add in the future with minimal editing if at all.

Well, that's all for now. If I find the time I may create the code for other flavors as well if the right mood strikes me ;] So technically that could be a separate small mod which could generate building_flavors for all mods we have enabled... though don't know whether that's a good or bad idea yet ;]

Artisanix · Dec 25, 2014

just some sample code that handles automatic generation of CITY_DEFENSE and CULTURE flavors...

Code:

-- Deletes all flavor groups for which we have "generators" present in the code
DELETE FROM Building_Flavors
WHERE FlavorType IN (
	'FLAVOR_CITY_DEFENSE',
	'FLAVOR_CULTURE'
);


-- FLAVOR_CITY_DEFENSE
-- inserts flavors for buildings that meet generic criteria and adds exceptions
INSERT INTO Building_Flavors (BuildingType, FlavorType, Flavor)
SELECT Type, 'FLAVOR_CITY_DEFENSE', 10 + Defense/100*2 + ExtraCityHitpoints/10 + BorderObstacle*20
FROM Buildings
WHERE Defense > 0 OR ExtraCityHitPoints > 0 OR BorderObstacle > 0
UNION
SELECT Type, 'FLAVOR_CITY_DEFENSE', 20
FROM Buildings
WHERE Type IN (
	'BUILDING_ALHAMBRA',
	'BUILDING_HIMEJI_CASTLE'
)
UNION
SELECT Type, 'FLAVOR_CITY_DEFENSE', 5
FROM Buildings
WHERE Type IN (
	'BUILDING_KREMLIN'
);


-- FLAVOR_CULTURE
-- inserts flavors for buildings that meet generic criteria
INSERT INTO Building_Flavors (BuildingType, FlavorType, Flavor)
SELECT
	Type, 'FLAVOR_CULTURE', 
	10 + CultureRateModifier + GlobalCultureRateModifier + GreatWorkCount*5 + GreatWorksTourismModifier/10 + LandmarksTourismPercent/10 + FreePolicies*10 + SpecialistCount*20
	+ abs(PolicyCostModifier)*2 + abs(GlobalPlotCultureCostModifier)/5
FROM Buildings
WHERE (
	CultureRateModifier > 0 OR GlobalCultureRateModifier > 0 OR GreatWorkCount > 0 OR GreatWorksTourismModifier > 0 OR LandmarksTourismPercent > 0 OR FreePolicies > 0
	OR SpecialistType = 'SPECIALIST_ARTIST' OR SpecialistType = 'SPECIALIST_MUSICIAN' OR SpecialistType = 'SPECIALIST_WRITER'
	OR PolicyCostModifier < 0 OR GlobalPlotCultureCostModifier < 0
);

-- inserts flavors for buildings that don't meet generic criteria but add some points of culture
INSERT INTO Building_Flavors (BuildingType, FlavorType, Flavor)
SELECT BuildingType, 'FLAVOR_CULTURE', 5
FROM Building_YieldChanges
WHERE (
	YieldType = 'YIELD_CULTURE' AND Yield > 1
	AND BuildingType NOT IN (SELECT BuildingType FROM Building_Flavors WHERE FlavorType='FLAVOR_CULTURE')
);

-- adds more flavor for buildings which add more points of culture
UPDATE Building_Flavors
SET Flavor = Flavor + 2*(SELECT Yield FROM Building_YieldChanges WHERE BuildingType = Building_Flavors.BuildingType AND YieldType = 'YIELD_CULTURE')
WHERE EXISTS (SELECT Yield FROM Building_YieldChanges WHERE BuildingType = Building_Flavors.BuildingType AND YieldType = 'YIELD_CULTURE')
AND FlavorType = 'FLAVOR_CULTURE'
;


-- General updates to flavors already generated with earlier queries

-- Increases flavor if building is a World Wonder
UPDATE Building_Flavors
SET Flavor = Flavor + 20
WHERE BuildingType IN (
	SELECT DISTINCT DefaultBuilding
	FROM BuildingClasses
	WHERE MaxGlobalInstances > 0
)
AND FlavorType IN (
	'FLAVOR_CITY_DEFENSE',
	'FLAVOR_CULTURE'
);

-- Increases flavor if building is a National Wonder
UPDATE Building_Flavors
SET Flavor = Flavor + 10
WHERE BuildingType IN (
	SELECT DISTINCT DefaultBuilding
	FROM BuildingClasses
	WHERE MaxPlayerInstances > 0
)
AND FlavorType IN (
	'FLAVOR_CITY_DEFENSE',
	'FLAVOR_CULTURE'
);

meRlinX_AT · May 30, 2015

mmh ... you mean something like this?

LeeS · May 30, 2015

Sorry. Did not realize I had actually hit the submit message button. But these flavor, are they not actually used in BNW?

Code:

FLAVOR_AIR
FLAVOR_ESPIONAGE
FLAVOR_DIPLOMACY
FLAVOR_AIRLIFT

FLAVOR_AIR I could see might not be because it is used with the Recycling Center and the Flavor might be a hold-over from the previous "Pollution/Unhealthiness" versions of CIV.

FLAVOR_DIPLOMACY might not be used for anything anymore since the changes from Vanilla, but it is used as a Flavoring in BNW with the Intelligence Agency.

FLAVOR_ESPIONAGE I would have thought were valid because of all the espoinage buildings in G&K and BNW, and it is used in BNW with at least one of those buildings.

FLAVOR_AIRLIFT is added to the Airport.

SQL, automated values in Building_Flavors table (for TC modders or else)

Artisanix

Warlord

Artisanix

Warlord

meRlinX_AT

JustMe

LeeS

Imperator

Similar threads