Tutorial: How to Write a Basic Debug Tracker in SQL

[isau]

This tutorial explains how to write a very simple debug system in SQL to help track down trouble with files efficiently, and to create an at-a-glance overview of mod file performance.

The system I describe below is implemented into the newest upcoming version of Quo's Combined Tweaks mod (not yet published but should be soon).


This post builds on concepts explained in the intro to working with SQLite which is here: https://forums.civfanatics.com/threads/tutorial-modding-with-sqllite-studio-pc.608352/


Understanding Load Order
The first thing to understand is that when you create a mod, the order of files inside the <UpdateDatabase> <Items> tags in your .modinfo file determines the order files load in. For example, here is the load order from my Combined Tweaks mod for the Basic ruleset.

There are 19 files total. If one of them fails, the whole mod begins to collapse. SQL often doesn't tell us which file failed, which can make it hard to track down issues. So enter the concept of our Debug system.

Creating a Debug Table in a Setup File
To create a Debug system we first need a file that loads prior to all other files. I call this "Quo_Setup_Code.sql" in my mods. (The "Quo_" at the start is my calling card for all files I create. It is there so I can very easily tell apart my materials from other modders, and also more easily avoid naming conflicts. I recommend coming up with your own two to four letter calling card.)


In this file we will use the CREATE TABLE command to create a new table. You can call it whatever you want. I personally called mine tblQuoDebug.

-- Create a table for debugging
CREATE TABLE tblQuoDebug
(
    DebugID text PRIMARY KEY,
    x_Open text, -- did the file open?
    x_Close text, -- did the file close?
    Message text -- optional place for adding a message
);

This creates a simple table with four columns:

• DebugID: This will be our unique key for each row
• x_Open: This will be used to track that a file opened
• x_Close: This will be used to track that a file closed
• Message: A custom field where we can add any custom messages we want

You can also add any additional fields you think will be useful.



Writing to the Debug Table
Once our table is set up in our Setup file (which loads first in our mod order by virtue of its position in the list and the Priority='1' in the modinfo) we can write to this table just like any other table out there. The way I use it is like this:


To Indicate that a File Opened, Add this Code at the Top of the File:

INSERT INTO tblQuoDebug
    (DebugID,         x_Open, x_Close,     Message)
VALUES ('Quo_Filename_Code',    '1',    '0',        '') ;

In the place of Quo_Filename_Code you would put the name of the file that just opened.


Then at the very bottom of each file, you'd add this:

UPDATE tblQuoDebug SET x_Close='1' WHERE DebugID='Quo_Setup_Code';

What this code does is:

• The first part inserts a new row into your bug tracker, with x_Open set to 1 and x_Close set to 0
• The final part updates x_Close to 1
• If your code encounters a bug between opening and closing, x_Open will be 1 and x_Close will be 0, telling that your bug lies somewhere in the code between the two

Seeing the Results of your Debug Code
You can view the results of your debug code by just going into SQLite and writing SELECT statements for your Debug table. For example, here is what Quo's Combined Tweaks v3.08.4 looks like when it is healthy and all files are opening and closing correctly:

With some creativity you can expand this Debug concept greatly. You could for example add a "throttle" field that prioritizes rows in the table by level of urgency. You could also use it to report back information that a particularly difficult piece of code worked. The great thing is this system is flexible and fairly easy to maintain. Design your debug system to the level that works for you.

 

[Infixo]

You can also use the timestamps from database.log to cross-reference modding.log.

 

[isau]

You can also use the timestamps from database.log to cross-reference modding.log.

Yes, that's a fair point, and how I used to do it before I added debug code. The issue with Modding.log is the scrolling, the fact that multiple game's data gets crammed into a single file, and it doesn't come back in a flexible database format that supports filtering or the ability to add Messages/track values/etc. But it's certainly worth knowing that it is there.

 

[Gedemon]

I would post to the actual tutorial section, but I don't have sufficient posting privileges.

I'm moving your thread there.

For general information, you can create a resource without a file, that was done first to allow the creation of threads regrouping multiple mods, but is also needed to create thread in the tutorial category.

edit: but you really can't post in tutorials directly ? that forum is not reserved for resources only AFAIK.

 

[isau]

I'm moving your thread there.

For general information, you can create a resource without a file, that was done first to allow the creation of threads regrouping multiple mods, but is also needed to create thread in the tutorial category.

edit: but you really can't post in tutorials directly ? that forum is not reserved for resources only AFAIK.

Thanks for the move.

It's possible I just don't know how to post to the forum. Here's what I see in the spot where the button normally is: