How does drvid in sqlth_partitions relate to scid and to entries in sqlth_te?

Ignition
1

I’m trying to figure out if my SQL queries should care about the drvid that is in the sqlth_partitions table. I need to go from an entry in sqlth_te to a query against a historical data table (e.g., sqlt_data_1_2019_04).

Here’s a snapshot of the tables on a clean installation of Ignition:
Capture

Capture2

Capture3

All good so far… we can map from a tag entry to a drvid.

Now we look in sqlth_partitions:

Capture4

No entry for drvid == 2.

How do I make that final leap?

2

If you will only ever have one Ignition gateway talking to this database, then you can probably ignore sqlth_drv - you will still need it if you add any more realtime tag providers to this gateway as well.

3

Thanks PGriffith-- however it is the case that there will be multiple gateways/providers. (This question is in the context of an Ignition Module that my company writes and maintains.)

One pattern I noticed is that the sqlth_partitions table only ever seems to reference a drvid entry in sqlth_drv where “provider” is null. Is that always the case? If so I could do this (somewhat crazy) SQL command to narrow in on the drvid that is used by sqlth_partitions:

SELECT sqlth_te.id AS tagid, gateway.id as drvid FROM sqlth_te
  INNER JOIN sqlth_scinfo ON sqlth_scinfo.id = sqlth_te.scid
  INNER JOIN sqlth_drv ON sqlth_drv.id = sqlth_scinfo.drvid
  INNER JOIN sqlth_drv AS gateway ON sqlth_drv.name = gateway.name
  WHERE gateway.provider IS NULL;
1 Like
4

I know this conversation is old, but it was never really resolved and I'm curious about the same thing.

I just setup a brand new Ignition (8.1.22) and DB to do some development work and right out of the gate noticed that the drvid is mismatched. All of the tags in sqlth_te are referencing the only scinfo entry, which has a drvid=2 ... but all the partition entries say drvid=1 and those are indeed the tables that the history is landing in...

MariaDB [ignitiondb]> SELECT DISTINCT(scid) FROM sqlth_te;
+------+
| scid |
+------+
|    1 |
+------+
1 row in set (0.001 sec)

MariaDB [ignitiondb]> SELECT * FROM sqlth_scinfo;
+----+----------+-------+
| id | scname   | drvid |
+----+----------+-------+
|  1 | _exempt_ |     2 |
+----+----------+-------+
1 row in set (0.001 sec)

MariaDB [ignitiondb]> SELECT * FROM sqlth_partitions;
+---------------------+-------+---------------+---------------+-----------+-------+
| pname               | drvid | start_time    | end_time      | blocksize | flags |
+---------------------+-------+---------------+---------------+-----------+-------+
| sqlt_data_1_2022_12 |     1 | 1669852800000 | 1672531200000 |         0 |  NULL |
+---------------------+-------+---------------+---------------+-----------+-------+
1 row in set (0.001 sec)

I can't ignore this since our production DB has accumulated history from multiple gateways and I need to be sure I'm looking at the right tables. Why is it already apparently mismatched on the very first entries being made?

5

It's not represented as a(n) SQL relationship, but there are always at least two records in sqlth_drv:

Each tag provider you add to a given gateway will create a new row in sqlth_drv with the gateway name and provider name. When it comes time to retrieve history, the historian "knows" to look for the entry with a null provider.

6

So, in general, a sqlth_te will reference to a sqlth_drv entry with both a gateway and provider name. But when looking up data entries for that tag, I can look for partitions using either that specific drv ID or the one that has the same gateway name and a NULL provider?

7

It depends on what you're trying to do, exactly.
The scid corresponding to a specific id in sqlth_te will be 1:1 with an id in sqlth_scinfo.
The drvid corresponding to a specific id in sqlth_scinfo will be 1:1 with an id in sqlth_drv.

So given just a path, you need to go from sqlth_te -> sqlth_scinfo -> sqlth_drv, and then from there find the entry with the matching gateway name but a null provider ID. That ID will be the prefix used for historical tables.

2 Likes