Invisible Cities using SQL

[lordplane]

So the problem I'm having is that sometimes the cities will vanish with only the city banner remaining. I've read on the forums that it's either "new building with DisplayPosition column inserted that clashes with another building" or "wrongly inserted art defines for building styles in SQL". However, I have yet to find the right way to insert the art define and I have checked that my new building has no DisplayPosition column.

Here's the code:

INSERT INTO Civilizations    (Type,                        Description,                    ShortDescription,                    Adjective,                            CivilopediaTag,            DefaultPlayerColor,        ArtDefineTag,                    ArtStyleType,        ArtStyleSuffix,        ArtStylePrefix,        PortraitIndex,    IconAtlas,        AlphaIconAtlas,    MapImage,                DawnOfManQuote,                        DawnOfManImage,            SoundtrackTag)
VALUES                        ('CIVILIZATION_HAIFURI',    'TXT_KEY_CIV_HAIFURI_DESC',        'TXT_KEY_CIV_HAIFURI_SHORT_DESC',    'TXT_KEY_CIV_HAIFURI_ADJECTIVE',    'TXT_KEY_CIV5_HAIFURI', 'PLAYERCOLOR_HAIFURI',    'ART_DEF_CIVILIZATION_JAPAN',    'ARTSTYLE_ASIAN',    '_ASIA',            'ASIAN ',            4,                'haifuriAtlas',    'haifuriAlpha',    'haifuriPortrait1.dds',    'TXT_KEY_CIV5_DAWN_HAIFURI_TEXT',    'haifuriDawnOfMan1.dds', 'HAIFURI');

And for the building:

INSERT INTO Buildings
(BuildingClass,                Type,                            Cost,    PrereqTech,                    Water,    MinAreaSize,    TrainedFreePromotion,    XBuiltTriggersIdeologyChoice,    CityConnectionTradeRouteModifier,    TradeRouteSeaGoldBonus,    TradeRouteSeaDistanceModifier,    SpecialistType,            SpecialistCount,    GreatPeopleRateChange,    EnhancedYieldTech,    TechEnhancedTourism,    HurryCostModifier,    NeverCapture,    IconAtlas,        PortraitIndex,    Description,                            Help,                                        Civilopedia,                                        Strategy,                                        ArtDefineTag                ) VALUES
('BUILDINGCLASS_FACTORY',    'BUILDING_FLOATING_DISTRICT',    430,    'TECH_INDUSTRIALIZATION',    1,        10,                'PROMOTION_AMPHIBIOUS',    4,                                10,                                    200,                    25,                                'SPECIALIST_ENGINEER',    2,                    3,                        'TECH_COMBUSTION',    3,                        -1,                    1,                'haifuriAtlas',    1,                'TXT_KEY_BUILDING_FLOATING_DISTRICT',    'TXT_KEY_BUILDING_FLOATING_DISTRICT_HELP',    'TXT_KEY_CIV5_BUILDINGS_FLOATING_DISTRICT_TEXT',    'TXT_KEY_BUILDING_FLOATING_DISTRICT_STRATEGY',    'ART_DEF_BUILDING_FACTORY');

Many thanks in advance!

 

[Artisanix]

That's not SQL's fault. It is caused by the incorrect data in database, and you can insert such incorrect data via XML as well.

Have you tried not to use ArtDefineTag for the Buildings at all? Well, I've created hundreds of buildings without this entry, and had no any problems with vanishing cities at all.

 

[LeeS]

I don't see anything obviously wrong there. You can do any one of:

1. Use the art define tag you are using because it is valid, without the DisplayPosition column, exactly as you are doing
2. Use "NONE"
3. Omit the column entirely

I do not see where the definition of the building as you have it would cause the city to disappear, but there are issues that will cause this. Without the actual mod to look at it is nigh on to impossible to determine where the issue really is.

1. Zip the version of the mod the game is actually trying to use, which will be found as a sub-folder in ~\Documents\My Games\Sid Meier's Civilization 5\MODS. Zip the whole sub-folder for your mod.
2. See: whoward69's zip your mods and attach tutorial Just bear in mind that the process for actually adding the zip to a forum post is now different since the CFC website software was updated a few months ago about a year ago. To attach the zip to a forum post, look for a button called Upload A File when composing your thread reply: this button opens a browse menu where you can select the zipped folder to attach directly to a forum post.
3. If the zipped folder is too large for the forum to accept then upload the zipped mod to a file-sharing site such as dropbox and provide a link where the mod can be downloaded.
   • Mediafire is not really a very good choice any more because they have become rife with spam and malware, as well as attempts to get you to download unwanted and uneeded kludge by confusing the layout of the download pages. You'll note for example that Mediafire links are no longer allowed on Steam, whereas CFC (this site) and dropbox links are still allowed.

 

[lordplane]

That's not SQL's fault. It is caused by the incorrect data in database, and you can insert such incorrect data via XML as well.

Have you tried not to use ArtDefineTag for the Buildings at all? Well, I've created hundreds of buildings without this entry, and had no any problems with vanishing cities at all.

I don't see anything obviously wrong there. You can do any one of:

1. Use the art define tag you are using because it is valid, without the DisplayPosition column, exactly as you are doing
2. Use "NONE"
3. Omit the column entirely

Okay, thanks for the quick replies, I shall first omit the the column and see if that will do the trick. I can't exactly remember when the invisible cities happen, be it when I build the specific buildings that have in game models (e.g. Colosseum, castle etc.) or it happens when I build the unique building specifically, just that it does happen.
I was mostly worried about the

'ART_DEF_CIVILIZATION_JAPAN'
'ARTSTYLE_ASIAN'
'_ASIA'
'ASIA '

being wrong.

But I'll play test a bit more first.

EDIT:

Okay, I tested the civ a bit more, and found that the unique building does not cause the city to vanish, but building a colosseum does. Any ideas what could cause that? The same styles are used in another mod of mine but that mod is based entirely in XML. I thought you could just put the values into the table that are the same as XML and it should be fine in SQL but seems like that's not the case.

Currently I'm suspecting that

'ASIA '

is causing the problems since it has that space on the end of the string, which SQL treats differently perhaps? I say this because the art defines for the colosseum is

<WonderArtInfo>
        <Type>ASIAN COLESSEUM</Type>
        <WonderArtState state="Any">
            <fScale>0.2</fScale>
            <Granny>Assets/Buildings/City Buildings/Colesseum/Colesseum_Asia.fxsxml</Granny>
        </WonderArtState>
    </WonderArtInfo>

What do you think?

 

[Artisanix]

I smell something fishy over there... ;]

I investigated this a bit (haven't tested though). But it seems to me that some things, like those tags, the game engine combines "on the fly".
There's no an entry like ASIAN COLESSEUM, EUROPEN COLESSEUM, AFRICAN COLESSEUM, etc. used at all in game's database. What you have there is just generic COLESSEUM.
The same case is with other similar buildings, like Castle, Palace, etc. - no any adjectives in ArtDefineTag column ever used.

What I suspect that game does in this particular case is, it reads the generic COLESSUM tag then appends "on the fly" prefix to it like ASIAN, EUROPEAN, etc.
So if you use as ArtDefineTag something like ASIAN COLESSEUM, then the game probably searches for base tag and tries to append adjective to it, so it will make something like ASIAN ASIAN COLESSEUM from this. And such tag not being present will make the city to vanish.

The same case is with certain buildings which are supposed to change the model across the eras, like ANCIENT LIGHTHOUSE, INDUSTRIAL LIGHTHOUSE, etc. In table Buildings in ArtDefineTag the generic name is only ever used, just LIGHTHOUSE.

Well, that's just the theory from what I can see... ;]



EDIT:
As for the prefix, you are right to suspect it. It should not have the space at the end, as in original database no prefix has the space at the end.

If that XML has a space there at the end then that's error. And the case while it may work for XML is that, XML is parsed and values are validated (and maybe even trimmed - means spaces at the beginning, and at the end are removed automatically), and from those XML processed values SQL query is constructed anyway.



MY SUGGESTION:
Don't believe those XML mods at all as far as game's data are concerned. Really, I mean it.
Just get yourself SQLiteSpy or other such program, and check everything directly in game's database. This way you save yourself a lot of headache ;]

 

[LeeS]

not quite true

		<Row>
			<Type>CIVILIZATION_JAPAN</Type>
			<Description>TXT_KEY_CIV_JAPAN_DESC</Description>
			<ShortDescription>TXT_KEY_CIV_JAPAN_SHORT_DESC</ShortDescription>
			<Adjective>TXT_KEY_CIV_JAPAN_ADJECTIVE</Adjective>
			<CivilopediaTag>TXT_KEY_CIV5_JAPAN</CivilopediaTag>
			<DefaultPlayerColor>PLAYERCOLOR_JAPAN</DefaultPlayerColor>
			<ArtDefineTag>ART_DEF_CIVILIZATION_JAPAN</ArtDefineTag>
			<ArtStyleType>ARTSTYLE_ASIAN</ArtStyleType>
			<ArtStyleSuffix>_ASIA</ArtStyleSuffix>
			<ArtStylePrefix>ASIAN </ArtStylePrefix>
			<PortraitIndex>13</PortraitIndex>
			<IconAtlas>CIV_COLOR_ATLAS</IconAtlas>
			<AlphaIconAtlas>CIV_ALPHA_ATLAS</AlphaIconAtlas>
			<MapImage>MapJapan512.dds</MapImage>
			<DawnOfManQuote>TXT_KEY_CIV5_DAWN_JAPAN_TEXT</DawnOfManQuote>
			<DawnOfManImage>DOM_Oda.dds</DawnOfManImage>
			<DawnOfManAudio>AS2D_DOM_SPEECH_JAPAN</DawnOfManAudio>
		</Row>

<ArtStylePrefix> is ASIAN with a trailing space. This is consistent in the Firaxis supplied definitions of the civs within their XML files. It does appear however this might get truncated when added to the database (ie, when the XML files are parsed into SQL and inserted into the database), so try without the trailing space in your SQL file as Artisanix suggested. It is also possible that the databse viewer program I am using is truncating the final space whereas the game's "true" database might not actually be truncating to eliminate the trailing space. If defining the civ within an XML file, you definitely want the trailing space.

 

[Artisanix]

If defining the civ within an XML file, you definitely want the trailing space.

It makes no difference. I've just tested this. No matter how many spaces you have before or after values in XML file, all of those are trimmed, and only the proper values end in database.

So you can have even something like this and it will work properly:

       <Row>
            <Type>                     CIVILIZATION_JAPAN                     </Type>
            <Description>            TXT_KEY_CIV_JAPAN_DESC              </Description>
            <ShortDescription>                   TXT_KEY_CIV_JAPAN_SHORT_DESC                </ShortDescription>
            <Adjective>                   TXT_KEY_CIV_JAPAN_ADJECTIVE                    </Adjective>
            <CivilopediaTag>                       TXT_KEY_CIV5_JAPAN                    </CivilopediaTag>
            <DefaultPlayerColor>                     PLAYERCOLOR_JAPAN                         </DefaultPlayerColor>
            <ArtDefineTag>                        ART_DEF_CIVILIZATION_JAPAN                    </ArtDefineTag>
            <ArtStyleType>                       ARTSTYLE_ASIAN                  </ArtStyleType>
            <ArtStyleSuffix>                      _ASIA                     </ArtStyleSuffix>
            <ArtStylePrefix>                       ASIAN                </ArtStylePrefix>
            <PortraitIndex>                 13                    </PortraitIndex>
            <IconAtlas>               CIV_COLOR_ATLAS                    </IconAtlas>
            <AlphaIconAtlas>                CIV_ALPHA_ATLAS                     </AlphaIconAtlas>
            <MapImage>                   MapJapan512.dds                  </MapImage>
            <DawnOfManQuote>                TXT_KEY_CIV5_DAWN_JAPAN_TEXT                 </DawnOfManQuote>
            <DawnOfManImage>               DOM_Oda.dds                </DawnOfManImage>
            <DawnOfManAudio>                   AS2D_DOM_SPEECH_JAPAN                  </DawnOfManAudio>
        </Row>

Well, in such a case I think that trailing space in Firaxis' original xml file is just a typo - it matters not in the end as far as xml files are concerned.

However, in SQL, all leading and trailing spaces are just plain errors usually in case of text values (all kinds of tags especially).

It is also possible that the databse viewer program I am using is truncating the final space

Err, what? O_O Pardon my surprise, but behavior like that would be ridiculous for such a "program". If you indeed suspect that app for doing so, just get rid of it immediately! ;]

whereas the game's "true" database might not actually be truncating to eliminate the trailing space.

Game's database is in sqlite format, the most widespread database on Earth ;] (as plenty of apps for Android use it, and you can find it in many more devices).
The programs which I use to inspect and sometimes manipulate data in sqlite databases (and Civ5 one too) are: SQLiteSpy, and SQLiteStudio. These work decent.

 

[lordplane]

As for the prefix, you are right to suspect it. It should not have the space at the end, as in original database no prefix has the space at the end.

so try without the trailing space in your SQL file as Artisanix suggested

Thank you guys for the support and help. It was indeed caused by the prefix and everything is working now without the extra space on the end.

Thanks again!