Dangerous way to make SQLite config DB smaller

Ignition
1

I am in no way recommending anyone do this, but while doing some debug tasks that involved digging through Ignition's internal TAGCONFIG database I noticed that the JSON fields include a lot of white space.

Out of curiosity, I decided to minimize all of the JSON fields and see how much space it would save. I did an SQLite VACUUM both before and after to ensure it was a meaningful comparison. For reference, we have over 1.2 million tags...

Before = 960MB
After = 790MB

This is not an insignificant change. I have not yet tried to reload this config back into Ignition, but I'm expecting it should go easily and will be trying it soon on a test system.

For the truly brave of heart:

import json
import sqlite3

# SQLite has a built in json() function which is supposed to verify that its 
# argument X is valid JSON and return a minified version.
# I originally tried this with: UPDATE TAGCONFIG SET CFG=JSON(CFG)
# but it flagged some of my TAGCONFIG entries as invalid.
# Python's json.loads() had no such complaints about those same entries.
# So, we minify them using Python... :/

db_connection = sqlite3.connect('ignition_config.idb')
print('Loading...')
all_tags = [x for x in db_connection.execute('SELECT ID, CFG FROM TAGCONFIG')]
print('Scanning...')
param_list = []
for tag_id, cfg in all_tags:
	repack_cfg = json.dumps(json.loads(cfg), separators=(',', ':'))
	param_list.append((repack_cfg, tag_id))
print('Updating...')
db_connection.executemany('UPDATE TAGCONFIG SET CFG=? WHERE ID=?', param_list)
db_connection.commit()
print('Done!')
db_connection.close()
1 Like
2

And for what it's worth, I went back to look more carefully at the TAGCONFIG entries that sqlite flagged as not valid JSON, and all of them have NaN or Infinity jammed into a float value. I'm pretty sure I know how those values got into Ignition, even though Ignition tries to prevent it. And I can confirm Ignition has odd behavior when a float tag has one of those as a value. :stuck_out_tongue:

Maybe I should adjust those to more mundane float values...

3

I can attest that just using json dumps for tag configs has worked reliably for me assuming the json itself is wellformed

4

I'm pretty confident you could do this all-in-one via sqlite; it has a builtin json function that automatically minifies:

EDIT: Lol nevermind, you already have a comment addressing that.

Not really sure why we aren't doing that ourselves for storage...definitely probably just an oversight.