[FEATURE] Store tagpaths in history table sqlth_te with correct case

Ignition
1

It's always seemed odd to me that tagpaths in the history tag table (sqlth_te) are stored as lowercase when most other strings are case-sensitive in Ignition. The biggest issue with storing as lowercase is that that's what the operator sees when they go to browse for tags in the power chart tag browser, and it doesn't look great. Sometimes case is required to more easily distinguish tag names, and flattening them makes it difficult to read

What was the reason behind lowercasing tag paths? Is it possible to not do that? :slight_smile:

Converting these strings in the table doesn't seem to pose an issue in my tests.

Dirty script I used to auto-fix tagpaths in my table (I wouldn't run this if you have multiple tag providers storing to the same history tables, it also doesn't do any error checking of the ign_tagpaths results!):

db_te = system.db.runPrepQuery('SELECT id, tagpath FROM sqlth_te ORDER BY tagpath')
db_tags = [t['tagpath'] + '.path' for t in db_te]
db_tagids = [t['id'] for t in db_te]

ign_tagvals = system.tag.readBlocking(db_tags)
ign_tagpaths = [str(p.value).split(']')[-1] for p in ign_tagvals]

# T-SQL query to update sqlth_te table, fixing the case of tagpaths
q = '''
WITH UpdateValues AS (
    SELECT * FROM (
        VALUES
            {value_pairs}
    ) AS Updates(id, NewTagPath)
)
MERGE INTO sqlth_te AS target
USING UpdateValues AS source
    ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET tagpath = source.NewTagPath;
'''

val_pairs = ["({}, '{}')".format(i, t) for i,t in zip(db_tagids, ign_tagpaths)]
val_pairs_ = ','.join(val_pairs)
q = q.format(value_pairs=val_pairs_)
system.db.runPrepUpdate(q)
5 Likes