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

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?

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.

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

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?

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.

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?

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.