Strange XML behavior

[PawelS]

I create a table using SQL:

CREATE TABLE CoA_CityNames (
'Code' TEXT NOT NULL,
'Number' TEXT NOT NULL,
'Name' TEXT PRIMARY KEY,
'Key' TEXT);

And then fill it using XML (only one row for now):

<GameData>
	<CoA_CityNames>
		<Row Code="DRE" Number="001" Name="Paradera"/>	
	</CoA_CityNames>
</GameData>

The problem is that the Number becomes '1' instead of '001' in the database. It's a text column, so it shouldn't do such things... does anyone know why it happens?

 

[Nutty]

Because you're using Firaxis' magical XML to SQL parser.

 

[LeeS]

I looked into this a little yesterday and saw the same behavior where <DataColumn>005</DataColumn> got parsed into '5' in the DebugDatabase, even though I forced <DataColumn> to be "text" in my table definition.

I also noticed this 'gem' in Firaxis's own code

<Table name="Building_BuildingClassYieldChanges">
	<Column name="BuildingType" type="text" reference="Buildings(Type)"/>
	<Column name="BuildingClassType" type="text" reference="BuildingClasses(Type)"/>
	<Column name="YieldType" type="[COLOR="Blue"]integer[/COLOR]" reference="Yields(Type)"/>
	<Column name="YieldChange" type="integer" default="0"/>
</Table>

Everywhere else I've looked so far, Column name="YieldType" uses 'text' for the column's 'type'.

So it would seem in the Civ5 parser that anything in XML that looks like a number is treated as a number and anything that looks like text is treated as text regardless of the column's 'type' setting.

------------------------------------------------
[edit]'Course, the whole thing with that table Building_BuildingClassYieldChanges could just be the whole Firaxis we don't ever impliment two tables in the same way, even if they are virtually the same in their column-names.

 

[PawelS]

Interesting... it seems the same happens with booleans, which are in turn converted to integers because SQLite doesn't support booleans. This would explain why a text starting with 'true' is converted to '1' (see here), probably the same happens with 'false' and '0'.

 

[LeeS]

Interesting... it seems the same happens with booleans, which are in turn converted to integers because SQLite doesn't support booleans. This would explain why a text starting with 'true' is converted to '1' (see here), probably the same happens with 'false' and '0'.

Yeah, there was another thread which I could not find where William explained how CIV5 handles 'true' and 'false' and converts them to '1' and '0' respectively. And also I recall he said it was better in mod xml to use '1' and '0' but I since I can't seem to find the original thread I don't remember what the exact reasoning was other than the fact SQL has no 'true', only '1'.

Take this with a a dollop of skepticism until proved because I am going from memory here:
I believe he said that the parser interprets anything (not in the game's core xml-files) with 'false', 'true', or any other text in a boolean column as being equal to 'true' because the parser is looking for whether or not the original data is a string rather than what the contents of the string are, and therefore arrives in the database as '1' even if the original data was 'false'.

I'm still looking for that thread, but haven't found it yet.

Answered and corrected, with further explanation for why I could not find that thread anywhere on CivFanatics.

 

[whoward69]

It was a bug in the XML to SQL parser that Firaxis seem to have fixed (the original thread is on the 2kgames forums)

In C++, only the values 0, (boolean) false and null are all treated as false. The text string of five characters "f" "a" "l" "s" and "e" is not 0, (boolean) false nor null, so it was being treated as true.

If Firaxis are trying to convert all text to numbers (along the lines of if it looks like a duck, waddles like a duck and quacks like a duck, it's probably a swan) and treating those that pass as numbers and those that fail as strings (which is probably not unreasonable given that SQLite pretty much doesn't care what you feed it), then you'll get the issue as reported.

Using correctly quoted SQL in that case will be the only solution.