SQL Unit File issue

[Karatekid5]

For my newest custom Civilization I've decided to try writing it in SQL instead of XML since most modders make their database files with SQL these days. I've had some experience with SQL with past mods so now I have no trouble converting from XML to SQL. Most of my files have been working, with the exception of a unit file I created. This unit is a Great General replacement that has combat abilities and can upgrade into stronger versions of itself later on. However, the game refuses to add the unit to the database. Reload Unit System is checked, the file is set to OnModActivated > UpdateDatabase, I checked the entire file with a fine toothed comb and everything is set up correctly, yet the game seems to just flat out refuse it. I checked the Database Log for any errors pertaining to it and absolutely nothing comes up. The only related error is the main Civ SQL file for this custom civ reporting that the unit doesn't exist. Here's the code for the SQL file:

INSERT INTO Units
(Class, Type, Cost, Moves, Special, Combat, CombatClass, Domain, DefaultAI, Description, Civilopedia, Strategy, Help, AdvancedStartCost, GoldenAgeTurns, UnitArtInfo, IconAtlas, UnitFlagAtlas, PortraitIndex, MoveRate)

VALUES ('UNITCLASS_GREAT_GENERAL', 'UNIT_GREAT_HERO', -1, 3, 'SPECIALUNIT_PEOPLE', 14, 'UNITCOMBAT_GUN', 'DOMAIN_LAND', 'UNITAI_ATTACK', 'TXT_KEY_UNIT_GREAT_HERO', 'TXT_KEY_UNIT_GREAT_HERO_TEXT', 'TXT_KEY_UNIT_GREAT_HERO_STRATEGY', 'TXT_KEY_UNIT_HELP_GREAT_HERO', -1, 8, 'ART_DEF_UNIT_GREAT_HERO', 'UA_HIGH_ATLAS', 'UNITS_GREAT_HERO_FLAG_ATLAS', 2, 'GREAT_PERSON');


INSERT INTO Unit_AITypes
(UnitType, UnitAIType)

VALUES ('UNIT_GREAT_HERO', 'UNITAI_ATTACK'),
       ('UNIT_GREAT_HERO', 'UNITAI_DEFENSE');


INSERT INTO Unit_Flavors     
            (UnitType,                 FlavorType, Flavor)
SELECT        ('UNIT_GREAT_HERO'),     FlavorType, Flavor
FROM Unit_Flavors WHERE (UnitType = 'UNIT_GREAT_GENERAL');


INSERT INTO Unit_ClassUpgrades
(UnitType, UnitClassType)

VALUES ('UNIT_GREAT_HERO', 'UNITCLASS_HERO_LEVEL2');


INSERT INTO Unit_FreePromotions
(UnitType, PromotionType)

VALUES ('UNIT_GREAT_HERO', 'PROMOTION_MEDIC_GENERAL'),
       ('UNIT_GREAT_HERO', 'PROMOTION_GREAT_GENERAL'),
       ('UNIT_GREAT_HERO', 'PROMOTION_HEROISM');

INSERT INTO UnitGameplay2DScripts     
(UnitType, SelectionSound, FirstSelectionSound)

VALUES ('UNIT_GREAT_HERO', 'AS2D_GREATHERO_SELECT', 'AS2D_BIRTH_GREATHERO');

SQL doesn't report as many errors as XML does so it's been harder to see just what might be wrong with it. Thanks in advance to anyone who's able to help!

 

[LeeS]

There's no such column called "DefaultAI" in table Units.

If the file's action is set-up properly, there will in fact be an error reported in database.log for this fatal error.

 

[Karatekid5]

Odd, I have several XML-made units in other mods I've made that have that column in the Units table, and all of those units are accepted by the database without issues. So I assumed that it was a normal element for units. Thank you for pointing out the error, I wouldn't have suspected this at all.

 

[LeeS]

The text string "DefaultAI" doesn't show anywhere in the game's base xml-files. The correct column name is DefaultUnitAI.

 

[Karatekid5]

I guess I made an error when copying down the name and didn't notice, glad to have that fixed.

Sadly now more SQL files aren't cooperating. Firstly, an art define file isn't working at all despite being a near-exact copy of a working one from another one of my mods. Secondly, my unit selection sound for some reason hasn't been playing (despite, again, using copied and pasted working code from another mod I made).

Art Defines:

INSERT INTO ArtDefine_StrategicView
        (StrategicViewType,             TileType,        Asset)
VALUES    ('ART_DEF_UNIT_GREAT_HERO',     'Unit',         'UAgreathero_SV.dds');
------------------------------
-- ArtDefine_UnitInfos
------------------------------           
INSERT INTO ArtDefine_UnitInfos
        (Type,                             DamageStates,    Formation)
SELECT    'ART_DEF_UNIT_GREAT_HERO',    DamageStates,     Formation
FROM ArtDefine_UnitInfos WHERE Type = 'ART_DEF_UNIT_XCOM_SQUAD';

------------------------
-- ArtDefine_UnitMemberCombats
------------------------
INSERT INTO ArtDefine_UnitMemberCombats
        (UnitMemberType,                        EnableActions, DisableActions, MoveRadius, ShortMoveRadius, ChargeRadius, AttackRadius, RangedAttackRadius, MoveRate, ShortMoveRate, TurnRateMin, TurnRateMax, TurnFacingRateMin, TurnFacingRateMax, RollRateMin, RollRateMax, PitchRateMin, PitchRateMax, LOSRadiusScale, TargetRadius, TargetHeight, HasShortRangedAttack, HasLongRangedAttack, HasLeftRightAttack, HasStationaryMelee, HasStationaryRangedAttack, HasRefaceAfterCombat, ReformBeforeCombat, HasIndependentWeaponFacing, HasOpponentTracking, HasCollisionAttack, AttackAltitude, AltitudeDecelerationDistance, OnlyTurnInMovementActions, RushAttackFormation)
SELECT    'ART_DEF_UNIT_MEMBER_GREAT_HERO',    EnableActions, DisableActions, MoveRadius, ShortMoveRadius, ChargeRadius, AttackRadius, RangedAttackRadius, MoveRate, ShortMoveRate, TurnRateMin, TurnRateMax, TurnFacingRateMin, TurnFacingRateMax, RollRateMin, RollRateMax, PitchRateMin, PitchRateMax, LOSRadiusScale, TargetRadius, TargetHeight, HasShortRangedAttack, HasLongRangedAttack, HasLeftRightAttack, HasStationaryMelee, HasStationaryRangedAttack, HasRefaceAfterCombat, ReformBeforeCombat, HasIndependentWeaponFacing, HasOpponentTracking, HasCollisionAttack, AttackAltitude, AltitudeDecelerationDistance, OnlyTurnInMovementActions, RushAttackFormation
FROM ArtDefine_UnitMemberCombats WHERE UnitMemberType = 'ART_DEF_UNIT_MEMBER_XCOM_SQUAD';
------------------------
-- ArtDefine_UnitMemberCombatWeapons
------------------------
INSERT INTO ArtDefine_UnitMemberCombatWeapons   
        (UnitMemberType,                        "Index", SubIndex, ID, VisKillStrengthMin, VisKillStrengthMax, ProjectileSpeed, ProjectileTurnRateMin, ProjectileTurnRateMax, HitEffect, HitEffectScale, HitRadius, ProjectileChildEffectScale, AreaDamageDelay, ContinuousFire, WaitForEffectCompletion, TargetGround, IsDropped, WeaponTypeTag, WeaponTypeSoundOverrideTag)
SELECT    'ART_DEF_UNIT_MEMBER_GREAT_HERO',    "Index", SubIndex, ID, VisKillStrengthMin, VisKillStrengthMax, ProjectileSpeed, ProjectileTurnRateMin, ProjectileTurnRateMax, HitEffect, HitEffectScale, HitRadius, ProjectileChildEffectScale, AreaDamageDelay, ContinuousFire, WaitForEffectCompletion, TargetGround, IsDropped, WeaponTypeTag, WeaponTypeSoundOverrideTag
FROM ArtDefine_UnitMemberCombatWeapons WHERE UnitMemberType = 'ART_DEF_UNIT_MEMBER_XCOM_SQUAD';
------------------------
-- ArtDefine_UnitMemberInfos
------------------------
INSERT INTO ArtDefine_UnitMemberInfos     
        (Type,                                     Scale,    ZOffset, Domain, Model,              MaterialTypeTag, MaterialTypeSoundOverrideTag)
SELECT    'ART_DEF_UNIT_MEMBER_GREAT_HERO',    Scale,    ZOffset, Domain, 'UAgreathero.fxsxml', MaterialTypeTag, MaterialTypeSoundOverrideTag
FROM ArtDefine_UnitMemberInfos WHERE Type = 'ART_DEF_UNIT_MEMBER_XCOM_SQUAD';

INSERT INTO ArtDefine_UnitInfoMemberInfos
(UnitInfoType, UnitMemberInfoType, NumMembers)

VALUES ('ART_DEF_UNIT_GREAT_HERO', 'ART_DEF_UNIT_MEMBER_GREAT_HERO', 1);

Sounds:

INSERT INTO UnitGameplay2DScripts    
(UnitType, SelectionSound, FirstSelectionSound)

VALUES ('UNIT_GREAT_HERO', 'AS2D_SELECT_GREATHERO', 'AS2D_BIRTH_GREATHERO');


INSERT INTO Audio_2DSounds
(ScriptID, SoundID, SoundType, MinVolume, MaxVolume)

VALUES ('AS2D_SELECT_GREATHERO', 'SND_MIDORIYA_SMASH', 'GAME_SFX', 50, 50),
VALUES ('AS2D_BIRTH_GREATHERO', 'SND_MIDORIYA_SMASH', 'GAME_SFX', 50, 50);


INSERT INTO Audio_Sounds
(SoundID, Filename, LoadType)

VALUES ('SND_MIDORIYA_SMASH', 'DelawareSmash', 'Streamed');

Reload audio system is checked and the selection sound is set to VFS = true. The Artdefine file is set to OnModActivate UpdatDatabase, and I checked all of the IDs, working to make sure there were no typos. Database shows nothing other than a failed Validation error and Modding.log shows nothing.
I'm sorry for being a bit bothersome, I just often have simple things go wrong. >~<

 

[LeeS]

Your ART_DEF code appears not to have any problems. Nor does your Sound code appear to have any syntax errors. The problem would not appear to be in the code. But without the mod itself to look at it is impossible to figure out where the problem is.

 

[Karatekid5]

Here's the ZIP of the mod so far. Checked over all of the files and I couldn't find any pointing mistakes or files set up incorrectly.

 

[LeeS]

Actually, your sound definition had a problem which I missed but database.log did not miss:

INSERT INTO Audio_2DSounds
(ScriptID, SoundID, SoundType, MinVolume, MaxVolume)

VALUES ('AS2D_SELECT_GREATHERO', 'SND_MIDORIYA_SMASH', 'GAME_SFX', 50, 50),
VALUES ('AS2D_BIRTH_GREATHERO', 'SND_MIDORIYA_SMASH', 'GAME_SFX', 50, 50);

Database.log errors:

[1811687.578] near "VALUES": syntax error
[1811687.921] no such column: True

The first of these tracks to this set of files in Stopwatch.log:

[1811687.578] , 					Update Database - SQL/Units/UAHigh_Units.sql, 0.002167
[1811687.578] , 					Update Database - SQL/Buildings/UAHigh_Buildings.sql, 0.000897
[1811687.578] , 					Update Database - SQL/Units/UAHigh_HeroUpgradeUnits.sql, 0.002275
[1811687.578] , 					Update Database - SQL/GameInfos/UAHigh_IconAtlases.xml, 0.000651

And the error already indicated.

The second listed error tracks to these two files in stopwatch.log:

[1811687.921] , 					Update Database - SQL/Leaders/DummyPolicy.sql, 0.086607
[1811687.921] , 					Update Database - SQL/Buildings/DummyBuildings.sql, 0.000771

And this error:

INSERT INTO BuildingClasses
(Type, DefaultBuilding, Description, NoLimit)

VALUES ('BUILDINGCLASS_NEZU_CULTURE', 'BUILDING_NEZU_CULTURE', 'TXT_KEY_BUILDING_NEZU_CULTURE', True),
       ('BUILDINGCLASS_NEZU_GREAT_PEOPLE', 'BUILDING_NEZU_GREAT_PEOPLE', 'TXT_KEY_BUILDING_NEZU_GREAT_PEOPLE', True);

1. SQL does not have true and false. 1 and 0 are used in SQL for booleans instead of the text strings "true" and "false"
2. SQL interprets your usage of the text-string True as the name of a column it needs to access.
3. NoLimit is not necessary. The game does not care about the setting of this column.

The problem with your ArtDefine file for the Hero Graphics is not in the SQL code, it is in the modinfo file:

<UpdateUserData>SQL/Units/HeroArtDefines.sql</UpdateUserData>

Well, that and you have no Art Atlases at the moment except for the Icon of the one unit, either in XML definitions or in DDS files.

 

[Karatekid5]

Didn't even know stopwatch.log was a thing, thank you for informing me of this. It'll make debugging much easier in the future since SQL doesn't always report exact files.

I was able to fix everything, and thank you for informing me of the boolean information. Though, isn't the NoLimit column needed so that multiple copies can be placed in a city? It's a part of the Unique Ability (something I'll probably need help with finishing, though that's for another time when I actually get to putting the code together), so I'll need to be able to place multiple copies of the dummy buildings.

Regarding the art assets, I'm aware of those. Aside from the unit's flag I haven't set up the atlases yet, since I want to finish the functionality first (and so that I won't get lots of missing asset errors while testing).

 

[LeeS]

NoLimit setting makes no difference. I've got mods that place multiple copies of a building into the same city, some of the mods using NoLimit=true and some using the default false value, and there is no difference. NoLimit seems to be orphan code in the database.