JDBC persistence with tableUseRealItemNames and tableIdDigitCount=0

I’m rebuilding my openHAB installation after a catastrophic failure on the weekend and I’m attempting to build in a way that reduces the effort to rebuild in the future. Part of what I want to do is keep the DB server and the openHAB server separate. My goal is to allow me to completely rebuild the openHAB server and also maintain the history of items that I persist. The history I’m mainly concerned with is the thermostat setpoints I have in each room, together with the actual temperature and the radiator valve opening in percent. As I introduce more insulation into the house I like to analyse the difference with graphs.

If I use the default DB table naming convention that most people seem to use (and that I’ve used up to now) in which the data for each persisted item is stored in a sequentially numbered table then if I rebuild the server I would have to ensure that each item I create is created in the same order that it was created during the previous build. This would ensure that the LivingRoomTemp item was assigned to table item0001, the setpoint to item0002, etc. However, in reality this is completely impractical, data is sent from various thermostats at random so the order in which the item tables are created is almost random. I could probably edit the values in the items table but there’s scope for error and there would be a window in which data would be written to the wrong tables. All fixable by manually editing the DB but that’s an arduous task.

However, I noticed that there are two options in the jdbc service configuration that should solve the problem: tableUseRealItemNames and tableIdDigitCount. If I set tableUseRealItemNames to true and tableIdDigitCount to zero then the items tables should be set to the name of the item. As long as I follow my build notes and name each persisted item consistently everything should fall in to place. Except, it doesn’t quite work that way. If I set:

tableUseRealItemNames=true
tableIdDigitCount=4

I get the following table names in the items table:

mysql> show tables;
+-----------------------------------------------+
| Tables_in_openHAB                             |
+-----------------------------------------------+
| housefeedelectricitymeter_batterylevel_0001   |
| housefeedelectricitymeter_instantpower_0004   |
| housefeedelectricitymeter_lowbattery_0003     |
| housefeedelectricitymeter_signalstrength_0005 |
| housefeedelectricitymeter_totalusage_0002     |
| items                                         |
+-----------------------------------------------+

mysql> select * from items;
+--------+------------------------------------------+
| ItemId | itemname                                 |
+--------+------------------------------------------+
|      1 | HouseFeedElectricityMeter_BatteryLevel   |
|      2 | HouseFeedElectricityMeter_TotalUsage     |
|      3 | HouseFeedElectricityMeter_LowBattery     |
|      4 | HouseFeedElectricityMeter_InstantPower   |
|      5 | HouseFeedElectricityMeter_SignalStrength |
+--------+------------------------------------------+

as you might expect. However, if I set:

tableUseRealItemNames=true
tableIdDigitCount=0

I get:

mysql> show tables;
+-----------------------------------------------+
| Tables_in_openHAB                             |
+-----------------------------------------------+
| housefeedelectricitymeter_batterylevel_1      |
| housefeedelectricitymeter_instantpower_4      |
| housefeedelectricitymeter_lowbattery_3        |
| housefeedelectricitymeter_signalstrength_5    |
| housefeedelectricitymeter_totalusage_2        |
| items                                         |

+-----------------------------------------------+

mysql> select * from items;
±-------±-----------------------------------------+
| ItemId | itemname |
±-------±-----------------------------------------+
| 1 | HouseFeedElectricityMeter_BatteryLevel |
| 2 | HouseFeedElectricityMeter_TotalUsage |
| 3 | HouseFeedElectricityMeter_LowBattery |
| 4 | HouseFeedElectricityMeter_InstantPower |
| 5 | HouseFeedElectricityMeter_SignalStrength |
±-------±-----------------------------------------+

Not what I was expecting. Each item table has the itemid from the items table appended to it. This means that the items still need to be created in the same order. Or am I missing an important detail? Why do the table names have the itemid appended to them? Item names must be unique so there is no need for anything to be appended to them for use as a unique table name, correct?

Thanks,

Steve.

1 Like

Same here, I consider this a bug in JDBC persistence.

A bumb from me. I have the same issue.
And since I want to use a Garbage collection mechanism, I need the item-name to be the same as the table name.

Any ideas?

has anyone worked around this problem? thanks.