Can XML Not Contain a Reference to Objects Created in SQL?

[isau]

I've been working to merge the Combined Tweaks and Xavierlol's Improvements Patch mods. Combined Tweaks is mostly written in SQL and Improvements Patch is mostly written in XML. It's here where I've run into something I didn't anticipate as I tried to disentangle some codependent files in the Improvements Patch.

I'm hopeful I just made a mistake somewhere in the file and that XML can rely on objects created in SQL.


If I do this in SQL:

INSERT INTO RequirementSets (
RequirementSetType,
RequirementSetId
)
VALUES (
(
'REQUIREMENTSET_TEST_ALL',
'XAV_PLOT_HAS_LUXURY'
) ;


And this exists in the XML:


<Row>
<ModifierId>GUILDHALL_RESOURCEBONUS</ModifierId>
<ModifierType>MODIFIER_CITY_PLOT_YIELDS_ADJUST_PLOT_YIELD</ModifierType>
<SubjectRequirementSetId>XAV_PLOT_HAS_LUXURY</SubjectRequirementSetId>
</Row>


I get this error.

[1601618.856] [Gameplay] ERROR: Invalid Reference on Modifiers.SubjectRequirementSetId - "XAV_PLOT_HAS_LUXURY" does not exist in RequirementSets


Now the interesting thing about this particular fail is if it happens, the mod completely fails to load. Meaning, not a single piece of SQL or XML that exists prior to the file gets executed and when you query the database its like it rolled back all changes in the database to the unmodded state. Its like there is a check on the XML that comes prior to everything else in the file that immediately rejects the mod if it can't find the linked object, and this check runs before any SQL that would set the object up. I've tried setting Priority=1 in the modinfo and still nothing.

It wasn't something I was hoping to run into. Can anyone confirm this observation? I'm hopeful I just messed something else up.

 

[HandyVac]

Both XML and SQL files are parsed as instructions to update the game's SQL database. There is no difference between them so far as the game is concerned.

The order in which files appear in your modinfo is very important. Files are processed from the top down, so if the xml file you mention is above the sql file in your modinfo list it will fail. Because, at the time that xml file is processed, that requirement set does not exist yet, because the sql file has not been processed yet.



Also, and I just noticed this after typing the above. It looks like you have an extra ( in your SQL statement after VALUES.

Are you getting any other errors in Database.log? and does the RequirementSet appear in your DebugGameplay.sqlite after the insert should have happened?

 

[LeeS]

If I do this in my modinfo file

		<UpdateDatabase id="BASE_GAMEPLAY_DATBASE_UPDATES">
			<Items>
				<File>ExtraCityNames.xml</File>
				<File>Districts.xml</File>
				<File>FasterBorders.xml</File>
				<File>Technologies.sql</File>
				<File>UnitsChanges.xml</File>
				<File>MapSizes.xml</File>
				<File>RemoveFloodplains.xml</File>
				<File>FeatureResourceHarvestYields.xml</File>
				<File>TechAlter_Adds.xml</File>
			</Items>
		</UpdateDatabase>

This does not actually mean that "Districts.xml" will load into the game after "ExtraCityNames.xml". The loading order of these two files between each other will depend entirely upon the order in which they appear here

	<Files>
		<File>Districts.xml</File>
		<File>ExtraCityNames.xml</File>
		<File>FasterBorders.xml</File>
		<File>TechAlter_Adds.xml</File>
		<File>Technologies.sql</File>
		<File>UnitsChanges.xml</File>
		<File>MapSizes.xml</File>
		<File>RemoveFloodplains.xml</File>
		<File>FeatureResourceHarvestYields.xml</File>
	</Files>

Modbuddy will automaticallly order files alphabetically in the list of <Files>, so if you throw multiple files into the same "action", you will get execution of those files in alphabetical order when creating a mod in modbuddy, even if they are presented in a different order within an action. The only cure for this is stating a priority but I have not tried that as yet and I have heard from others that this does not work reliably for multiple files within the same action. <LoadOrder> seems to work reasonably well from my own tests and from what other people have reported, but this applies to the action as a whole rather than the files within it, and really is meant for cross-mod load ordering.

This works fine for me and forces the SQL file to execute first and set-up the new tables that are needed by "RASL_GameSet-Up.xml"

	<Components>
		<UpdateDatabase id="MOD_Component_SQL">
			<Items>
				<File>SQL/RASL_Set-Up.sql</File>
			</Items>
		</UpdateDatabase>
		<UpdateDatabase id="MOD_Component_XML">
			<Items>
				<File>RASL_GameSet-Up.xml</File>
			</Items>
		</UpdateDatabase>

This is the method I use exclusively for ensuring file-loading order witihn a mod. IE, every file gets its own action. Bit PITA, but it works for ensuring everything within my mod is loaded in the order I want, and not in some mystery-order asserted by Modbuddy or by a default game behavior.

 

[Infixo]

Yep, it loads them alphabetically. Once I had the need load a couple of SQLs in specific order, I simply named them 1_onefile.sql and 2_anotherfile.sql. Simple.

 

[LeeS]

I also moved to one file each action because it seems that if A.xml errors fatal then B.xml, C.XML, D.xml within the same action are also discarded entirely along with the portions of A.xml that failed. (assuming B.xml, C.XML, D.xml want to load after A.xml).

A confirmation that this is actual game behavior and not just a flawed testing method on my part would be appreciated if anyone has an opportunity to confirm from their own experiences when stuff goes awry.

 

[Magil]

I've loaded SQL and XML files that cross-reference each other fine, and never had problems that were solved by changing the order the files load in. Are you certain it's not merely that the file containing XAV_PLOT_HAS_LUXURY isn't just flat-out failing to load for some other reason?

 

[LeeS]

I've loaded SQL and XML files that cross-reference each other fine, and never had problems that were solved by changing the order the files load in. Are you certain it's not merely that the file containing XAV_PLOT_HAS_LUXURY isn't just flat-out failing to load for some other reason?

This is good to confirm (that the code itself is not failing), but I have had complete failure of half of a mod because file load order was not occuring in the order I thought it was.

 

[Magil]

This is good to confirm (that the code itself is not failing), but I have had complete failure of half of a mod because file load order was not occuring in the order I thought it was.

I'm sure it can happen, though for this specific occurrence (as defined in the OP) I'd say that it's never happened for me. For example, I have this in my modding log:

[1670488.760] Status: UpdateDatabase - Loading Gameplay/EE_Modifiers.sql
[1670488.773] Status: UpdateDatabase - Loading Gameplay/EE_Requirements.sql
[1670488.867] Status: UpdateDatabase - Loading Gameplay/EE_UnitAbilities.sql
[1670488.869] Status: UpdateDatabase - Loading Gameplay/EE_Beliefs.sql
[1670488.877] Status: UpdateDatabase - Loading Gameplay/EE_Resources.xml
[1670488.891] Status: UpdateDatabase - Loading Gameplay/EE_Districts.xml

EE_Modifiers.sql has some Modifiers that use RequirementSets defined in EE_Districts.xml, but despite the fact that EE_Modiifers.sql clearly loads first according to my modding log, it seems to have no problems. Unless there's something I'm misunderstanding.

 

[Infixo]

It can't be. There's a foreign key defined for RequirementSetId and if you will try to insert a row into modifiers that references non-existing RequirementSetId, it will generate an error in database.log. Unless your DB engine is totally broken...
Edit. Can you post that mod? I would really like to check this mysterious case.

 

[Magil]

It can't be. There's a foreign key defined for RequirementSetId and if you will try to insert a row into modifiers that references non-existing RequirementSetId, it will generate an error in database.log. Unless your DB engine is totally broken...
Edit. Can you post that mod? I would really like to check this mysterious case.

I mean, it's not just a case of nothing showing up in the database log--the modifiers work too. I tested it in Firetuner to be sure. Is this really that unknown for a file loaded early on to reference files loaded later on? I'm pretty sure I've done plenty both ways in my mods. Note I am loading it all as part of the same component.

I can't post that specific example anymore because I'm in the process of moving all my Requirements and RequirementSets to EE_Requirements.sql for that mod and I just cleaned out EE_Districts.xml (converting a lot of code from xml to sql to shorten it), but I can give a quick example (heck, EE_Requirements.sql already loads after EE_Modifiers.sql, and EE_Modifiers.sql is filled with references to RequirementSets that are defined in EE_Requirements.sql). Here's code snippets, from EE_Modifiers.sql:

--Great Person Modifiers.
   ('MAGIL_GREATPERSON_LIBRARIES_SCIENCE', 'MODIFIER_PLAYER_CITIES_ADJUST_CITY_YIELD_MODIFIER', 0, 1, NULL, 'MAGIL_CITY_HAS_LIBRARY_REQUIREMENTS'),

Notice it has MAGIL_CITY_HAS_LIBRARY_REQUIREMENTS, defined in EE_GreatPersonUpdate.xml:

   <Row>
           <RequirementSetId>MAGIL_CITY_HAS_LIBRARY_REQUIREMENTS</RequirementSetId>
           <RequirementSetType>REQUIREMENTSET_TEST_ANY</RequirementSetType>
       </Row>

And here's the modding log that shows what order they're loading in:

[1681111.301] Status: UpdateDatabase - Loading Gameplay/EE_Modifiers.sql
[1681111.315] Status: UpdateDatabase - Loading Gameplay/EE_Requirements.sql
[1681111.341] Status: UpdateDatabase - Loading Gameplay/EE_UnitAbilities.sql
[1681111.342] Status: UpdateDatabase - Loading Gameplay/EE_Beliefs.sql
[1681111.342] Status: UpdateDatabase - Loading Gameplay/EE_Resources.xml
[1681111.356] Status: UpdateDatabase - Loading Gameplay/EE_Districts.xml
[1681111.359] Status: UpdateDatabase - Loading Gameplay/EE_Buildings.xml
[1681111.375] Status: UpdateDatabase - Loading Gameplay/EE_Improvements.xml
[1681111.376] Status: UpdateDatabase - Loading Gameplay/EE_Projects.xml
[1681111.376] Status: UpdateDatabase - Loading Gameplay/EE_CityStates.xml
[1681111.380] Status: UpdateDatabase - Loading Gameplay/EE_GlobalParameters.xml
[1681111.381] Status: UpdateDatabase - Loading Gameplay/EE_TechCivicChanges.xml
[1681111.382] Status: UpdateDatabase - Loading Gameplay/EE_Handicap.xml
[1681111.383] Status: UpdateDatabase - Loading Gameplay/EE_Uniques.xml
[1681111.384] Status: UpdateDatabase - Loading Gameplay/EE_Policies.xml
[1681111.387] Status: UpdateDatabase - Loading Gameplay/EE_WorldWonders.xml
[1681111.399] Status: UpdateDatabase - Loading Gameplay/EE_GreatPersonUpdate.xml

And just to test it, I loaded up a game with Firetuner just now and used the Great Scientist in question--and it performed as expected. I can post the mod, but keep in mind this mod is under very heavy construction right now so the code's a bit of a mess. If you want to verify yourself though that I am loading this with no database errors, no modding log errors, and no warnings in gameeffects, here's a link.

I don't know, maybe I'm just misunderstanding what's going on here.

 

[isau]

I haven't had a chance to retest it yet because I was fixing an unrelated crash to desktop. I have temporarily pull the Buildings code entirely. It's possible it's all just a simple typo somewhere.

I use a custom table to keep track of file load order and whether each file opened and closed. The XML would be item number 26 in the list if it were working (the thing listed there now is the start of a SQL version of it just to be sure it loads). When the issue with the XML not reading the values in SQL happens, none of the 27 files listed here loads. In fact the table, which is created in the first file (Quo_AA_Setup_Code) never even gets created and when I query the database against tblQuoDebug I get an error saying no such table exists.

 

[Infixo]

Ok, I think I've figured it out. Loaded your mod and it is exactly as you say - no errors. It only means that during the execution of SQL statements database engine doesn't check for any constraints. It doesn't mean any 'forward looking', etc. SQL is 1-pass only. However, it checks for constraints when entire component has been loaded.

I quickly tested that theory, simply moved Modifiers.sql to a separate component that runs as first one.
Modding.log
[1696107.045] Status: UpdateDatabase - Loading Gameplay/EE_Modifiers.sql
[1696107.063] ERROR: Failed to release save point.
[1696107.083] Warning: Error when calling IComponentHandler::ApplyComponent.
And the database.log looks like this:
[1696097.341] [FullTextSearch]: FTS - Creating Context
[1696107.063] [Gameplay] ERROR: FOREIGN KEY constraint failed
[1696107.063] [Gameplay] ERROR: FOREIGN KEY constraint failed
[1696107.063] [Gameplay]: Validating Foreign Key Constraints...
[1696107.072] [Gameplay] ERROR: Invalid Reference on Modifiers.SubjectRequirementSetId - "MAGIL_REQUIRES_FISHING_BOAT_AND_SEA_RESOURCE" does not exist in RequirementSets
[1696107.073] [Gameplay] ERROR: Invalid Reference on Modifiers.SubjectRequirementSetId - "MAGIL_DISTRICT_IS_HOLYSITE_WITH_OBELISK_REQUIREMENTS" does not exist in RequirementSets
[1696107.073] [Gameplay] ERROR: Invalid Reference on Modifiers.SubjectRequirementSetId - "MAGIL_PLOT_IS_SEA_RESOURCE_REQUIREMENTS" does not exist in RequirementSets
[1696107.073] [Gameplay] ERROR: Invalid Reference on Modifiers.SubjectRequirementSetId - "MAGIL_PLAYER_HAS_STONE_REQUIREMENTS" does not exist in RequirementSets
[1696107.073] [Gameplay] ERROR: Invalid Reference on Modifiers.SubjectRequirementSetId - "MAGIL_PLAYER_HAS_COPPER_REQUIREMENTS" does not exist in RequirementSets
[1696107.073] [Gameplay] ERROR: Invalid Reference on Modifiers.SubjectRequirementSetId - "MAGIL_GRASS_RIVER_REQUIREMENTS" does not exist in RequirementSets
[1696107.073] [Gameplay] ERROR: Invalid Reference on Modifiers.SubjectRequirementSetId - "MAGIL_PLAINS_RIVER_REQUIREMENTS" does not exist in RequirementSets
[1696107.073] [Gameplay] ERROR: Invalid Reference on Modifiers.SubjectRequirementSetId - "MAGIL_GRASS_HILLS_RIVER_REQUIREMENTS" does not exist in RequirementSets
[1696107.073] [Gameplay] ERROR: Invalid Reference on Modifiers.SubjectRequirementSetId - "MAGIL_PLAINS_HILLS_RIVER_REQUIREMENTS" does not exist in RequirementSets
[1696107.073] [Gameplay] ERROR: Invalid Reference on Modifiers.SubjectRequirementSetId - "MAGIL_FIRING_RANGE_REQUIREMENTS" does not exist in RequirementSets
[1696107.073] [Gameplay] ERROR: Invalid Reference on Modifiers.SubjectRequirementSetId - "MAGIL_PLAYER_HAS_COAL_REQUIREMENTS" does not exist in RequirementSets
[1696107.073] [Gameplay] ERROR: Invalid Reference on Modifiers.SubjectRequirementSetId - "MAGIL_PLAYER_HAS_OIL_REQUIREMENTS" does not exist in RequirementSets


I suppose that mod loader opens a db-transaction for each component separately, loads statements and then does commit. And at that moment db engine checks for constraints, etc. It would be also aligned with messages like "Status: Successfully released save point." and "ERROR: Failed to release save point.", meaning that a transaction has been committed successfuly or not.
Or another option is that maybe Firaxis modified the db-engine and can trigger constraint checking at will.

Anyway, to summarize: each component is treated as a whole. You need to have all constraints valid when you finish loading a component. Within it you can put to DB whatever you want in any order you want.

 

[Infixo]

Also, your mod confirms that loading order is just as listed in <Files> section. It happens that you've got a different placing of Resources and Districts files in <Files> and <UpdateDatabase>. Files are loaded in order specified in <Files>. But it doesn't matter as was shown in previous post.

 

[Magil]

Loading as whole components at a time makes sense. It'd make modding a lot less convenient if it loaded individually file-by-file and insisted on checking every one to make sure everything was there.