Tables with both ID and Type columns

whoward69

whoward69

DLL Minion
Joined
May 30, 2011
Messages
8,728
Location
Near Portsmouth, UK
Tables that have both an ID and a Type column are treated specially by the game core. The contents of such tables are made available to Lua via the GameInfo and GameInfoTypes structures.

This enables us to write code such as

Code: 
local iCost = GameInfo.Buildings.BUILDING_HARBOR.GoldMaintenance

or any of the following, they are all the same, just depends on your preference

Code: 
local iCost = GameInfo.Buildings["BUILDING_HARBOR"].GoldMaintenance
local iCost = GameInfo.Buildings["BUILDING_HARBOR"]["GoldMaintenance"]
local iCost = GameInfo.Buildings.BUILDING_HARBOR["GoldMaintenance"]

rather than a complex query

Spoiler :
Code: 
local iCost = 0
for row in DB.Query("SELECT GoldMaintenance FROM Buildings WHERE Type='BUILDING_HARBOR'") do
  iCost = row.GoldMaintenance
end

If we know the ID of the harbor (which I don't, but will use 30 as a guess), we can also write

Code: 
local iCost = GameInfo.Buildings[30].GoldMaintenance

to get the same value.

We can also iterate all buildings with

Code: 
for building in GameInfo.Buildings() do
  print(building.GoldMaintenance)
end

In most of our code we can use the Type name (eg BUILDING_HARBOR) for readability, but when working with the Lua API we almost always want the ID, eg

Code: 
if (pPlot:GetFeatureType() == GameInfo.Features.FEATURE_FOREST.ID) then
  -- Do something on a forest plot
end

This is a common source of confusion, despite the API method ending with Type, it actually returns an ID

The game developers must have gotten bored with writing GameInfo.{tablename}.{type}.ID all the time as they also provide the GameInfoTypes structure, which returns the ID for any Type, eg

Code: 
if (GameInfo.Features.FEATURE_FOREST.ID == GameInfoTypes.FEATURE_FOREST) then
  -- This is ALWAYS true
end

As modders, we can also create our own tables to take advantage of this, eg

Code: 
<GameData>
  <Table name="Animals">
    <Column name="ID" type="integer" primarykey="true" autoincrement="true"/>
    <Column name="Type" type="text" notnull="true" unique="true"/>
    <Column name="Description" type="text"/>
    <!-- Other stuff -->
  </Table>

  <Animals>
    <Row>
      <ID>0</ID>
      <Type>ANIMAL_WORG</Type>
      <Description>TXT_KEY_ANIMAL_WORG_DESC</Description>
      <!-- Other stuff -->
    </Row>
    <Row>
      <Type>ANIMAL_DRAGON</Type>
      <Description>TXT_KEY_ANIMAL_DRAGON_DESC</Description>
      <!-- Other stuff -->
    </Row>
  </Animals>
</GameData>

(example in XML, but I could have used SQL)

and then use the standard GameInfo structure to access them

Code: 
GameInfo.Animals.ANIMAL_WORG.Desc
GameInfo.Animals[1].Desc -- this is the dragon description
GameInfo.Animals[GameInfoTypes.ANIMAL_DRAGON].Desc -- as is this

Note: The game core is not well behaved if you create a table with both ID and Type columns that are not of database types "integer" and "text" respectively - expect it to crash!
 
Now, there are some issues with the GameInfoTypes structure that we need to be aware of - as they can cause very unexpected results.

Whereas we use

Code: 
local iHarbor = GameInfo.Buildings.BUILDING_HARBOR.ID

to get the ID, we only need to do

Code: 
local iHarbor = GameInfoTypes.BUILDING_HARBOR

note the lack of a ".Buildings" in that.

This is not an issue for our own tables, but can cause hard to track problems when we want to add our own data and relate it to an existing main table.

Consider the following

Code: 
<GameData>
  <Table name="BuildingsExtn">
    <Column name="ID" type="integer" primarykey="true" autoincrement="true"/>
    <Column name="Type" type="text" notnull="true" unique="true"/>
    <Column name="Range" type="integer"/>
    <!-- My extra stuff for buildings -->
  </Table>

  <BuildingsExtn>
    <Row>
      <Type>BUILDING_WALLS</Type>
      <Range>1</Range>
      <!-- My stuff -->
    </Row>
    <Row>
      <Type>BUILDING_CASTLE</Type>
      <Range>2</Range>
      <!-- My stuff -->
    </Row>
  </BuildingsExtn>
</GameData>

This seems to work, I can still do

Code: 
GameInfo.Buildings.BUILDING_WALLS.Desc

and also access my own stuff as

Code: 
GameInfo.BuildingsExtn.BUILDING_WALLS.Range

BUT, I've just broken GameInfoTypes for walls (and castle) as I've just overwritten the old value of GameInfoTypes.BUILDING_WALLS with 0

Anything that now uses GameInfoTypes.BUILDING_WALLS to access the Buildings table is now going to get the Courthouse entry! One known such usage is the game load/save code, so we've just unwittingly broken the ability to load a saved game!

So what can we do to fix this?

We could rename our Type column to be BuildingType, but then we lose the ability to do

Code: 
GameInfo.BuildingsExtn.BUILDING_WALLS.Range

as that requires there to be a Type column, so we'd be back with the complex DB.Query() code

or

Code: 
local iRange = 1
for building in GameInfo.BuildingsExtn{BuildingType="BUILDING_WALLS"} do
  iRange = building.Range
end


We could add a prefix to our Type entries, eg

Code: 
<GameData>
  <BuildingsExtn>
    <Row>
      <Type>EXTN_BUILDING_WALLS</Type>
	  <Range>1</Range>
      <!-- My stuff -->
    </Row>
    <Row>
      <Type>EXTN_BUILDING_CASTLE</Type>
	  <Range>2</Range>
      <!-- My stuff -->
    </Row>
  </BuildingsExtn>
</GameData>

and then use

Code: 
GameInfo.BuildingsExtn.EXTN_BUILDING_WALLS.Range

but that's a pain if we have a variable holding the building type, as our code is no longer the same for both tables, which will inevitably lead to errors

Code: 
local buildingType = "BUILDING_WALLS"
local sDesc = GameInfo.Buildings[buildingType].Desc
local iRange = GameInfo.BuildingsExtn["EXTN_"..buildingType].Range

We could write an accessor to make this easier

Code: 
function getBuildingExtn(buildingType, item)
  return GameInfo.BuildingsExtn["EXTN_"..buildingType][item]
end

local buildingType = "BUILDING_WALLS"
local sDesc = GameInfo.Buildings[buildingType].Desc
local iRange = getBuildingExtn(buildingType, "Range")

better, but still not ideal. (We could write the accessor to work for both tables, but that becomes over-engineered.)


Or we can use SQL to renumber our extension table to match the original building table, that way, when we overwrite the values in the GameInfoTypes structure we are using the same value, so it doesn't matter

Code: 
UPDATE BuildingsExtn
  SET ID=(SELECT b.ID FROM Buildings b WHERE b.Type=BuildingsExtn.Type);

If you use the SQL you will have to remove the "primarykey='true'" part of the definition of the ID column.

Which method you choose to use will depend on your (current) modding abilities and what you feel most comfortable with.

EDIT:

For anyone (mis-)using tables like this, the following code may be a life-saver ;)

Code: 
local majorTables = {
  "Civilizations", "Traits",
  "Buildings", "Units",
  "Technologies", "PolicyBranchTypes", "Policies",
  "Projects", "Processes",
  "Terrains", "Resources", "Improvements", "Features", "Worlds",
  "Religions"
}

for _, t in ipairs(majorTables) do
  for r in GameInfo[t]() do
    if (r.ID ~= GameInfoTypes[r.Type]) then
	  print(string.format("FATAL: %s.ID does not match GameInfoTypes.%s", t, r.Type))
	end
  end
end
 
THANKS!

OT: I wish we had a 'thanks' button. Saves having to post just to say thanks!
 
I don't understand what you're trying to do in the 2nd post. It breaks the whole paradigm that BUILDING_WALLS refers to one thing and one thing only. This is the whole point of the ID/Type table archetype, and a good thing when you understand it and can rely on it. If you violate that, the very best you can hope to achieve is not breaking something.

I really don't understand why the example isn't being added to the mod as an extra column for Buildings. You only have one Range value for BUILDING_WALLS, correct? Do folks think adding columns is less modular than adding tables? I can't see why. Firaxis does this from time to time and there is no reason for it to cause you problems. If you are doing something silly like INSERT INTO statements without specifying column names, stop doing that.

Slightly tangential but related to the thread topic, breaking sequential IDs (for tables that use the ID/Type archetype) is also a bad idea. Even for mod-use-only tables, it's often advantageous in Lua to be able to count on sequential IDs. For example, I can traverse a Lua array with cached table data 68 times faster than traversing Units with for unit in GameInfo.Units() (see test #7 here). I realize that modders screamed bloody murder when deletes/inserts didn't work back in 2010 (I was one of them, we didn't understand the problem for a while) and how horrible it was that the developers did it that way (they patched later so sequence wouldn't matter for most tables, only some), but I've found it convenient in my Lua modding to expect sequential IDs and it's very easy to implement.


Edit: A couple results from my speed tests might be relevant or of interest for the thread topic:
  • Accessing GameInfo.Improvements.IMPROVEMENT_FARM.ID is about 5x slower than accessing GameInfoTypes.IMPROVEMENT_FARM, which is about 5x slower than accessing a localized constant. Use the latter for large iterations, for example if you are searching for a plot type or feature type on all map plots. (This is a case where code optimization aligns with readability, so there is no reason not to do it the fast way.)
  • As I said above, iterating over tables using for row in GameInfo.Units() do seems strangely slow to me. That's not an issue 99% of the time so I'm not telling you not to use it (I do). But if you have a time-critical piece of code, i.e., something running many times per turn, consider an alternative. Caching DB values in a Lua table is the fastest way to deal with this.
 
Pazyryk said:
I really don't understand why the example isn't being added to the mod as an extra column for Buildings.
Click to expand...

The example is a very trivial simplification of the real problem, which involves 5 extra tables each with many tens of columns.

Regardless, continously extending a table to add more and more columns, especially when the values in those columns for most rows will be empty, is NOT good database design. If it was, there woud be no such things as entity-relationship diagrams, foreign keys and cascade operations in modern database design.

Just because the Firaxis developers are stuck in the late 1990's with their database, xml and C approaches doesn't mean we have to be.
 
When you add a column to a table with, say, 10 million rows, you are not adding 10 million data points, even if your default is not NULL. That's not how it works. (Which of course I know you know. But probably most modders don't.)

whoward69 said:
Now, there are some issues with the GameInfoTypes structure that we need to be aware of - as they can cause very unexpected results.
Click to expand...

Unexpected only if you don't know or try to violate the existing DB norms. I'm not saying that everything the devs did was fantastic or that I don't violate their design norms often (it wasn't and I do), but I found the ID/Type table convention very helpful once I understood it. In any case, it seems confusing (for modders that are not super advanced) to violate this DB norm after just having explained it. Perhaps there is some useful reason that I'm not seeing, but it is tricky at best.
 
Pazyryk,

The offender in question is me as this post shows.

Having misunderstood the way the GameInfoTypes array works, I thought it convenient to do what whoward has outlined as being the wrong approach.

As whoward mentioned, I created a series of tables in my mod to break out a stream of new data that relates to buildings for my supply system model. At first I was adding additional columns to the Buildings table but this started getting out of control once I hit around 15+ additional fields.

The sensible approached seemed to break them out into smaller manageable tables similar to any other Buildings support table Firaxis uses. This allowed my SELECT statements to be more easily managed and future updates or player end changes to be made.

The design choice was done on the basis of how easy GaneInfo references could be done rather than using the whole row statement or DB.Query approach.
 
Pazyryk said:
Edit: A couple results from my speed tests might be relevant or of interest for the thread topic:
  • Accessing GameInfo.Improvements.IMPROVEMENT_FARM.ID is about 5x slower than accessing GameInfoTypes.IMPROVEMENT_FARM, which is about 5x slower than accessing a localized constant. Use the latter for large iterations, for example if you are searching for a plot type or feature type on all map plots. (This is a case where code optimization aligns with readability, so there is no reason not to do it the fast way.)
  • As I said above, iterating over tables using for row in GameInfo.Units() do seems strangely slow to me. That's not an issue 99% of the time so I'm not telling you not to use it (I do). But if you have a time-critical piece of code, i.e., something running many times per turn, consider an alternative. Caching DB values in a Lua table is the fastest way to deal with this.
Click to expand...

If you want inefficient how about

Code: 
GameInfo.Buildings[v2].ID

v2 is known to be either the id of a building or a unit, and the code has already figured out it's for a building by this line - go figure!

(Taken straight from ReligionOverview.lua)
 
whoward69 said:
If you want inefficient how about

Code: 
GameInfo.Buildings[v2].ID

v2 is known to be either the id of a building or a unit, and the code has already figured out it's for a building by this line - go figure!

(Taken straight from ReligionOverview.lua)
Click to expand...
GameInfo is weird. Is GameInfo.Buildings a table or a function? Without looking, I suspect GameInfo is a proxy table that triggers some metamethods (which happen to be very slow).

GameInfoTypes seems to be a normal well-behaved Lua table. Quite fast especially if you localize it. I've come to appreciate it a lot (for my total conversion modding) which is why I'm perhaps being overprotective of it in my comments here.

ww2commander said:
hit around 15+ additional fields.
Click to expand...
That's not really very many. Some base tables are ... what? ...100? I've added tables with 100 columns.

ww2commander said:
The sensible approached seemed to break them out into smaller manageable tables similar to any other Buildings support table Firaxis uses.
Click to expand...
It's not similar to Firaxis's support tables. These support many-to-many relationships and don't use the ID/Type convention (and have to be traversed).

ww2commander said:
This allowed my SELECT statements to be more easily managed and future updates or player end changes to be made.
Click to expand...
Can you give me an example of a SELECT, INSERT INTO or UPDATE statement that is made more difficult by additional columns? Or any Lua statement?

ww2commander said:
The design choice was done on the basis of how easy GaneInfo references could be done
Click to expand...
Very easy if you put all your 1-to-1 information in the base table with the base item.

ww2commander said:
or DB.Query approach.
Click to expand...
Icky!... I haven't written or looked at one of these since I wrote my TableSaverLoader over a year ago.

(Just having fun here. Hope nobody takes offence...)
 
Pazyryk said:
Unexpected only if you don't know or try to violate the existing DB norms.
Click to expand...

That's DB norms for the game, not best practice database design norms and patterns. Anyone familiar with those are going to hit problems due to the poor DB design used by the game.

My house is a building in the town I live in, but I would NOT expect in a good database design for there to be "lifts", "loading bay capacity" and "car parking spaces" associated with it, neither would I expect the warehouses on the industrial park (also buildings) to have "number of bedrooms" values.
 
whoward69 said:
That's DB norms for the game, not best practice database design norms and patterns. Anyone familiar with those are going to hit problems due to the poor DB design used by the game.

My house is a building in the town I live in, but I would NOT expect in a good database design for there to be "lifts", "loading bay capacity" and "car parking spaces" associated with it, neither would I expect the warehouses on the industrial park (also buildings) to have "number of bedrooms" values.
Click to expand...
Yes but the first would be in a Houses table and the second in a Businesses table. You could have a third table Buildings that links to entities in these first two. I wouldn't consider it good design to have two tables called Buildings and BuildingsExtn, and I still see no advantage to two tables with 1-to-1 Type relationship for Civ5 modding.

(At least we would agree that DBs are nice. I made that suggestion on the Civ4 modding forum and got lectured that they are not useful if you have C++.)
 
No offense taken and this is a learning experience for me.

Here is an old example of a support table I use for the scenario I am developing. The choice to separate this data was my personal preference and I could have just dumped it into 'Buildings'. Formatting is a bit off with the cut/paste below!

Code: 
INSERT INTO EF_SupplyBuildings (Type, RailPointsRequired, BaseWorkerModifier, SteelInput, AlumInput, FoodInput, PowerInput, ProdLevels, L1_EqType1, L1_EqQty1, L1_EqType2, L1_EqQty2, L2_ProjPrereq, L2_EqType1, L2_EqQty1, L2_EqType2, L2_EqQty2, L3_ProjPrereq, L3_EqType1, L3_EqQty1, L3_EqType2, L3_EqQty2)
--		Type							RailReq	BaseWorker	SteelIn	AlumIn	FoodIn	PowerIn	ProdLvls	L1EqT1		L1EqQ1	L1EqT2	L1EqQ2		L2_ProjPrereq					L2EqT1	L2EqQ1	L2EqT2	L2EqQ2	L3_ProjPrereq					L3EqT1	L3EqQ1	L3EqT2	L3EqQ2		

SELECT	'BUILDING_ARMAMENTS_PLANT',			2,		2.5,	3,		5,		18,		0,		1,			'FG',		220,	NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_ZAVOD_KATYUSHA',			1,		1.8,	2,		1,		18,		0,		0,			NULL,		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL

SELECT	'BUILDING_ZAVOD_LIGHT',				2,		2.5,	8,		8,		28,		0,		3,			'LT',		40,		NULL,		0,		'PROJECT_SU_76',				'LT',	20,		'LSP',	7,		'PROJECT_TANK_500',				'LSP',	14,		NULL,	0		UNION ALL
SELECT	'BUILDING_ZAVOD_T34',				3,		2.9,	12,		10,		35,		0,		1,			'MT',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_ZAVOD_KV_IS',				4,		2.9,	18,		10,		40,		0,		1,			'HT',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_ZAVOD_SU',				3,		2.9,	12,		10,		35,		0,		3,			'MSP',		0,		NULL,		0,		'PROJECT_SU_85',				'MSP',	0,		'MTD',	0,		'PROJECT_SU_100',				'MTD',	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_ZAVOD_ISU',				4,		2.9,	18,		10,		40,		0,		1,			'HSP',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL

SELECT	'BUILDING_KRUPP',					4,		3,		14,		15,		40,		0,		2,			'MT',		0,		NULL,		0,		'PROJECT_STUG_IV',				'MT',	0,		'MSP',	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_MAN',						3,		2.9,	15,		15,		33,		0,		3,			'LT',		0,		NULL,		0,		'PROJECT_MARDER',				'LT',	0,		'LTD',	0,		'PROJECT_PANTHER',				'MT',	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_MIAG',					3,		2.9,	14,		15,		33,		0,		3,			'LT',		0,		'LSP',		0,		'PROJECT_STUG_III_G',			'LSP',	0,		NULL,	0,		'PROJECT_JAGDPANTHER',			'LSP',	0,		'MTD',	0		UNION ALL
SELECT	'BUILDING_HENSCHEL',				4,		2.9,	20,		15,		38,		0,		3,			'LT',		0,		NULL,		0,		'PROJECT_TIGER',				'LT',	0,		'HT',	0,		'PROJECT_PANTHER',				'HT',	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_ALKETT',					3,		2.9,	14,		15,		33,		0,		3,			'LSP',		0,		'LTD',		0,		'PROJECT_WESPE_HUMMEL',			'LSP',	0,		'LSP2',	0,		'PROJECT_JAGDPANZER_IV',		'LSP',	0,		'MTD',	0		UNION ALL
SELECT	'BUILDING_DAIMLER',					3,		2.9,	15,		15,		33,		0,		3,			'LT',		0,		NULL,		0,		'PROJECT_MARDER',				'LT',	0,		'LTD',	0,		'PROJECT_PANTHER',				'MT',	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_SKODA',					2,		2.7,	14,		15,		33,		0,		2,			'LT',		0,		NULL,		0,		'PROJECT_MARDER',				'LTD',	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_BMM',						2,		2.7,	14,		15,		33,		0,		2,			'LT',		0,		NULL,		0,		'PROJECT_MARDER',				'LTD',	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_NIBELUNGEN_WORKS',		4,		3,		20,		15,		40,		0,		2,			'MT',		0,		NULL,		0,		'PROJECT_JAGDPANZER_IV',		'MT',	0,		'MTD',	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_VOMAG',					3,		2.9,	14,		15,		33,		0,		3,			'MT',		0,		NULL,		0,		'PROJECT_JAGDPANZER_IV',		'MT',	0,		'MTD',	0,		'PROJECT_JAGDTIGER',			'MT',	0,		'HTD',	0		UNION ALL			
SELECT	'BUILDING_MNH',						3,		2.9,	15,		15,		33,		0,		3,			'LT',		0,		NULL,		0,		'PROJECT_PANTHER',				'MT',	0,		NULL,	0,		'PROJECT_JADPANTHER',			'MT',	0,		'MTD',	0		UNION ALL			
SELECT	'BUILDING_DEW',						3,		2.7,	14,		15,		33,		0,		3,			'MT',		0,		NULL,		0,		'PROJECT_WESPE_HUMMEL',			'MT',	0,		'LSP2',	0,		'PROJECT_NASHORN',				'MT',	0,		'MTD',	0		UNION ALL
SELECT	'BUILDING_FAMO',					2,		2.6,	14,		15,		33,		0,		3,			'LT',		0,		'LSP',		0,		'PROJECT_WESPE_HUMMEL',			'LSP',	0,		'LSP2',	0,		'PROJECT_JADPANTHER',			'LSP',	0,		NULL,	0		UNION ALL			
SELECT	'BUILDING_WWAG',					2,		2.6,	14,		15,		33,		0,		3,			'LT',		0,		'LSP',		0,		'PROJECT_MARDER',				'LSP',	0,		'LTD',	0,		'PROJECT_STUG_III_G',			'LSP',	0,		NULL,	0		UNION ALL			

SELECT	'BUILDING_ZAVOD_YAK',				2,		2.4,	3,		50,		15,		0,		1,			'F',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_ZAVOD_LA',				2,		2.4,	3,		50,		15,		0,		1,			'F',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_ZAVOD_IL2',				2,		2.7,	6,		86,		20,		0,		1,			'GA',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_ZAVOD_IL4',				2,		2.5,	7,		94,		20,		0,		1,			'MB',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_ZAVOD_PE2',				2,		2.5,	6,		90,		20,		0,		1,			'FB',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL

SELECT	'BUILDING_FOCKE_WULF',				2,		2.4,	4,		60,		15,		0,		1,			'F',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_AGO',						2,		2.4,	4,		60,		15,		0,		1,			'F',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_FIESLER',					2,		2.4,	4,		60,		15,		0,		1,			'F',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_KURT',					2,		2,		4,		60,		15,		0,		1,			'F',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_MESSERSCHMITT',			2,		2.6,	4,		60,		15,		0,		1,			'F',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_ERLA_I',					2,		2.4,	4,		60,		15,		0,		1,			'F',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_ERLA_II',					2,		2.4,	4,		60,		15,		0,		2,			'F',		0,		NULL,		0,		'PROJECT_ME_262',				'JF',	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_ERLA_III',				2,		2.4,	6,		90,		15,		0,		1,			'F',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_JUNKERS_I',				2,		2.4,	6,		90,		15,		0,		1,			'GA',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_JUNKERS_II',				2,		2.4,	7,		110,	15,		0,		1,			'FB',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_ATG',						2,		2,		7,		110,	15,		0,		1,			'FB',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_WESERFLUG_I',				2,		2.2,	6,		90,		15,		0,		1,			'GA',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_WESERFLUG_II',			2,		2.2,	7,		110,	15,		0,		1,			'FB',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		UNION ALL
SELECT	'BUILDING_HEINKEL',					2,		2.5,	7,		130,	15,		0,		1,			'MB',		0,		NULL,		0,		NULL,							NULL,	0,		NULL,	0,		NULL,							NULL,	0,		NULL,	0		;

Very easy if you put all your 1-to-1 information in the base table with the base item.
Click to expand...
It's not similar to Firaxis's support tables. These support many-to-many relationships and don't use the ID/Type convention (and have to be traversed).
Click to expand...

Having a separate table for 1-1 as opposed to 1-m relationships is not a sin in itself from what I recall (was a VBA/SQL developer many moons ago) as long as it served a practical purpose to the developer such as sorting out less used data, avoiding massive tables structures or making it easier to house-keep your code. The key thing is that any new table did not mess up the overall relationship model.

EDIT: There is also the paranoid assumption that Firaxis might get 'lucky' and add an identically named field in the base table with any future expansion that then causes dramas. Better to separate your change alone for this reason.

The fact that Firaxis devs came up with the GameInfoTypes 'shortcut' is actually the issue at hand. ;)
 
I'm trying to convince folks to like GameInfoTypes and not abuse it wantonly. I'm quite fond of it myself. It's been my friend over the last year, since Spatz first introduced it to me.

Yes, I'll concede that there are 1-to-1 tables in the real world and they have their value. But in Civ5 modding you have a conflict between these 3 things:
  1. GameInfoTypes
  2. the ID/Type table structure
  3. having different tables that share Type names
You can't have all three, even if you would like to. (Unless by some of whoward69's wizardry... But then you will have some other trade-off like non-sequential IDs.)

For your example: In all honesty, I would have added the columns to Buildings without a second thought. Your large INSERT statement isn't any different except you are inserting these into Buildings instead of EF_SupplyBuildings (in a separate insert block from other buildings that don't use supply stats). What is the cost? OK, there is a slight cost in that rows will be bigger when you traverse the table (for non-NULL columns). But:
  1. GameInfo table traversal is already very slow. I don't think trying to conserve column number is going to help with this.
  2. If time matters then cache the values you need in a Lua table at game init. For example, store all your supply buildings in a Lua table called supplyBuildingIDs (by traversing the table once at game init). Then traverse your Lua table instead of GameInfo.Buildings. That will be at least 100x faster. (If you are doing a GameInfo table traversal many times, or really much more than once a game at init or when some event happens, stop that!)
Another option would be to make lemonade from your lemons and allow yourself a 1-to-many relationship between SupplyTypes and BuildingTypes. So instead of trying to force BUILDING_WALL into another table or having to use the awkward BUILDING_WALL_EXTN, you have a SUPPLYTYPE_OLD_FORTIFICATION. You give that entity all of your mod stats you need for supply. Then you add only one column to Buildings called SupplyType (default NULL) where you link some buildings to supplyTypes. You could then assign SUPPLYTYPE_OLD_FORTIFICATION to both walls and castles.
 
I completely agree and support your stance.

In the example I provided, keeping the ID in sync (ala whoward method) is concerning me so I will most likely have to init the IDs in the manner you mentioned.

However, I want to avoid storing this info in global arrays (i.e. gSupplyBuildings = {...} as that can become a headache when your data set grows. This was my early days approach based on what I learnt from Gedemon's RED WWII mod. This approach served well for his purpose, but got out of control for mine :(
 
ww2commander said:
However, I want to avoid storing this info in global arrays (i.e. gSupplyBuildings = {...} as that can become a headache when your data set grows. This was my early days approach based on what I learnt from Gedemon's RED WWII mod. This approach served well for his purpose, but got out of control for mine :(
Click to expand...

I actually like the ID/Type table system as implemented (though I couldn't have written a better tutorial than whoward69 who hates it). I've entered a ton of new data with it. But it's slow to access in run-time code. So I just suck up whatever info I need into Lua tables at game init. Not the entire table. Just info I need into small Lua tables for specific functions.
 
Pazyryk said:
Yes but the first would be in a Houses table and the second in a Businesses table. You could have a third table Buildings that links to entities in these first two.
Click to expand...

You would ... in the real world. But the examples are Civ inspired, where we ONLY have a buildings table where we have to dump everything. We don't have DefensiveBuildings, ReligiousBuildings, ProductionBuildings, et al tables.

The Civ DB design is flawed. So modders have two choices. We can either follow the norms of the game (icky! to use your term ;)) or we can follow real world norms.

If it wasn't for the GameInfoTypes structure we could easily do both. But that structure causes problems for those wanting to do the latter. This REFERENCE (not a tutorial) shows you how (in the second post, which is why it's seperated from the first) to overcome the flawed design within the game code to enable you to do the latter if you so desire.

Which is better - extending the base table or adding a seperate table? As always, the answer is "it depends on your exact circumstances and personal preferences". What is easy and obvious for one modder, causes confusion and misunderstanding for another. Neither approach is right, neither is wrong, but due to the design of the Lua API one will cause you completely unexpected problems when it really shouldn't.
 
<Features> in the OP towards the bottom. Didn't you actually want to write <Animals> ? That threw me for a few moments.

Late to the game as it were by being referred here from JFD's thread related to this issue.

Thanks for the info, whoward. And the, err, "debate" actually did learn me some more beyond what whoward presented in the first two posts.
 
LeeS said:
<Features> in the OP towards the bottom. Didn't you actually want to write <Animals> ? That threw me for a few moments.
Click to expand...
Picky, picky, picky ;)

OP corrected :D
 
You must log in or register to reply here.

Similar threads

A
Template narrative story mod
Replies
3
Views
796
Grandy
G
I
HELP Missionaries buildable with Mosques mod issues
Replies
2
Views
526
Icthiolavarunt
I
L
Problem with Tooltiphelper.lua files
Replies
0
Views
251
Lucill
L
Solver
Example gameplay mod
2 3
Replies
41
Views
5K
Jeppetto
Jeppetto
beezany
configuring mod Options with a dedicated Mods tab
Replies
18
Views
1K
beezany
beezany
Back
Top Bottom