Easy detection of SQL and XML failure to load to end of file

[Pazyryk]

It's rather annoying that errors in SQL or XML just cause the file not to load (silently) rather than give you an explicit error message. Sure, you can check the DB for every change you make. And it's always obvious after a while that something is wrong. But, really, this is the most common kind of error in my modding. I want instant feedback. Like this:

 EaMain: !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
 EaMain: !!!! ERROR: UnitPromotions.sql was not loaded to end of file !!!!
 EaMain: !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Or, if all is well, then this:

 EaMain: All expected SQL and XML files loaded to end of file...

I set this up in my mod as follows:

Create a new SQL file called DebugTableCheck.sql. Add it as an OnModActivated and make sure it loads before all of your other SQL/XML. Contents:

CREATE TABLE EaDebugTableCheck ('FileName' TEXT NOT NULL);

At the last line of every SQL file you want to test, add a line like this (blue text is unique name for the file):

INSERT INTO EaDebugTableCheck(FileName) SELECT '[COLOR="Blue"]UnitPromotions.sql[/COLOR]';

You can do the same for an XML file like this:

	<EaDebugTableCheck>
		<Row FileName="[COLOR="blue"]EaText_Help.xml[/COLOR]"/>
	</EaDebugTableCheck>

Now, all you have to do is check that all of the expected files are present somewhere in your Lua init code. In mine, it looks like this:

function OnLoadEaMain()
	--check to make sure all expected SQL files were loaded to end of file
	local expectedSQLfiles = {	'EaImages.sql',		--from Ea Media Pack
					'EaActions.sql',
					'EaAI.sql',
					'EaRaces.sql',
					'EaCivsAndTraits.sql',
					'EaPeople.sql',
					'EaCreations.sql',
					'AI.sql',
					'Buildings.sql',
					'Civilizations.sql',
					'GameInfo.sql',
					'GlobalDefines.sql',
					'Policies.sql',
					'Religions.sql',
					'Technologies.sql',
					'Terrain.sql',
					'UnitControl.sql',
					'UnitPromotions.sql',
					'Units.sql',
					'EaText.sql',
					'EaText_Actions.xml',
					'EaText_Civs.xml',
					'EaText_Help.xml',
					'EaText_Misc.xml',
					'EaText_Pedia.xml',
					'EaText_People.xml',
					'EaText_Units.xml'	}
	local bAnyErrors = false
	for i = 1, #expectedSQLfiles do
		local expectedFile = expectedSQLfiles[i]
		local bLoaded = false
		for row in GameInfo.EaDebugTableCheck() do
			if expectedFile == row.FileName then
				bLoaded = true
				break
			end
		end
		if not bLoaded then
			bAnyErrors = true
			print("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
			print("!!!! ERROR: "..expectedFile.." was not loaded to end of file !!!!")
			print("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
		end
	end
	if not bAnyErrors then
		print("All expected SQL and XML files loaded to end of file...")
	end

end

--at end of file
OnLoadEaMain()

This saves me minutes every time I start civ for another testing session, which may be 100 times a week I guess...

 

[FramedArchitect]

There's something like this in Vanilla Enhanced, though much less methodical. Thank you for sharing.

 

[Gilgamesch]

Good to know this thanks.

 

[ls612]

Does this work with BNW? I'd assume so, but it is a year old and it can't hurt to check.

 

[Pazyryk]

I don't see why it won't work for BNW. The logic is the main point; you can change implementation in many ways (I now add the list of files names in a new table added by sql).

I couldn't imagine modding without this. I suppose I've made about 1000 stupid xml/sql mistakes by now (mostly missing commas in sql), and this saves me anywhere from 1 to 5 minutes each time I do that.

 

[ExpiredReign]

@Pazyryk

Is this approach better than, worse than or just different to, the method Thalassicus uses in his mods

ie.

CREATE TABLE IF NOT EXISTS LoadedFile(Type text, Value);
INSERT INTO LoadedFile(Type, Value) VALUES ('MT_Data.xml'	, 0);
INSERT INTO LoadedFile(Type, Value) VALUES ('CAT_Misc.sql'	, 0);
INSERT INTO LoadedFile(Type, Value) VALUES ('Cat_Options.sql'	, 0);
...

and then at the end of each SQL file there is

UPDATE LoadedFile SET Value=1 WHERE Type='CAT_Misc.sql';

and for XML files

<LoadedFile>
	<Update>
		<Where Type="MT_Data.xml"/>
		<Set Value="1"/>
	</Update>
</LoadedFile>

And then in Lua code:

LoadedFile = LoadedFile or {}
for row in GameInfo.LoadedFile() do
 	LoadedFile[row.Type] = row.Value
 	if row.Value ~= 1 then
  		print("ERROR: Failed to load " .. row.Type)

So CREATE a table with an initial value of 0 for listed files, load it and UPDATE the value to 1, and then check it with a chunk of Lua code.


Just a query, not a criticism.

 

[Pazyryk]

It's exactly the same logic, just done a little differently.

My only quibble with Thalassicus code above is that (in my mod) I would surely miss the "ERROR: Failed to load..." print statement. I just wouldn't see it among 100 other lines of print. Needs more "!!!!!".

 

[ExpiredReign]

That's what I thought, just wanted to verify.


!!!!!!!!!!!!! More than one way to skin a cat.!!!!!!!!!!!!

Thanks.

 

[Pazyryk]

Here's some more improvement to Thalassicus' code.

Change this:

CREATE TABLE IF NOT EXISTS LoadedFile(Type text, Value);
INSERT INTO LoadedFile(Type, Value) VALUES ('MT_Data.xml'	, 0);
INSERT INTO LoadedFile(Type, Value) VALUES ('CAT_Misc.sql'	, 0);
INSERT INTO LoadedFile(Type, Value) VALUES ('Cat_Options.sql'	, 0);

To this:

CREATE TABLE IF NOT EXISTS LoadedFile(Type text, Value);
INSERT INTO LoadedFile(Type, Value) VALUES
('MT_Data.xml'	, 0),
('CAT_Misc.sql'	, 0),
('Cat_Options.sql'	, 0);

Or you can simplify further:

CREATE TABLE IF NOT EXISTS LoadedFile(Type TEXT, Value INTEGER DEFAULT 0);
INSERT INTO LoadedFile(Type) VALUES
('MT_Data.xml'),
('CAT_Misc.sql'),
('Cat_Options.sql');

 

[ExpiredReign]

That last one is very nice. Elegantly simple yet functional.
I like it a lot.

Nice work. Thanks.