JDBC Persistence and the magic in item names mapping

Add-ons Persistence Services
,
1

I’m trying to set up a timescaledb based persistence without any item names mapping.
No mapping means that I’d like to have an item name, as specified in the /etc/openhab/persistence/jdbc.persist file, to be accessible in the database.

Setting up the timescaledb persistence as follows:

$ grep -e '^[a-zA-Z]' /etc/openhab/services/jdbc.cfg | grep -v -e user -e password
url=jdbc:timescaledb://127.0.0.1:5432/openhab_db
tableUseRealItemNames=true
tableCaseSensitiveItemNames=true
tableIdDigitCount=0

and having defined in the /etc/openhab/persistence/jdbc.persist file the Heat_Pump_Total_active_power item, I still couldn’t find this item being stored in postgresql:

openhab_db=# \dt Heat_Pump_Total_active_power
Did not find any relation named "Heat_Pump_Total_active_power".

Contrary to the above output, I can find it when I’m searching for item name with double quotes:

openhab_db=# \dt "Heat_Pump_Total_active_power"
                            List of relations
 Schema |                     Name                     | Type  |  Owner  
--------+----------------------------------------------+-------+---------
 public | Heat_Pump_Total_active_power | table | openhab
(1 row)

Note that \dt output does not contain those double quotes characters which might be very confusing for someone looking for a particular table.

The documentation (HEAD of the main branch at the time of writing) doesn’t mention anything about the fact that extra double quotes characters will be added:

openhab-addons$ git grep -i quot -- bundles/org.openhab.persistence.jdbc/README.md
openhab-addons$ git log --oneline -n 1
379b244b1f (HEAD -> main, origin/main, origin/HEAD) [boschshc] Use MAC address as default bridge thing ID (#18391)

Looking for \" I found:

$ git grep  -A 1 -B 2 '"\\"' -- bundles/org.openhab.persistence.jdbc
bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/db/JdbcPostgresqlDAO.java-    @Override
bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/db/JdbcPostgresqlDAO.java-    protected String formattedIdentifier(String identifier) {
bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/db/JdbcPostgresqlDAO.java:        return "\"" + identifier + "\"";
bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/db/JdbcPostgresqlDAO.java-    }

Unfortunately, it’s hard coded and doesn’t depend on any configuration option.

So far, I exclusively used influxdb persistence and I could connect directly to the database and use exactly those names as specified in the /etc/openhab/persistence/influxdb.persist file.

I wonder, why the jdbc persistence service is so special and why there is no option to disable mapping at all or even better to have it switched off, by default (like in aforementioned influxdb)?

2

I don’t use PostgreSQL myself, but you can find some more details in this issue:

It’s my understanding that you need to quote table names in PostgreSQL when they have mixed case, i.e.:

CREATE TABLE "MyItem"
SELECT * FROM "MyItem";

Without the quotes in the CREATE TABLE, the table would be named “myitem”.

Do you mean using these options per default?

tableUseRealItemNames=true
tableCaseSensitiveItemNames=true

Historically, tableUseRealItemNames was defaulted to false, and the other one didn’t exist yet. As a result, you had the worst from both worlds, i.e. tables named like “myitem_0001”, so you needed a lookup table, and you would have to do something manually when renaming items. When tableCaseSensitiveItemNames was introduced, it was also defaulted to false for backwards compatibility reasons. Also, I was only able to test with MySQL, so there was a risk of other RDBMS’es not working with this configuration. It turned out later, as an example, that PostgreSQL needed those quotes.

Changing the defaults on existing installations will require users to do attended data migration, so the defaults cannot be changed casually. Additionally, in order for this to work correctly, the underlying operating system, database server and configuration must support case sensitive table names, so the configuration can not be guaranteed to work for all users out of the box.

I do agree though that a configuration having real case-sensitive table names is preferable to using item numbering and lookups.

1 Like
3

Do you mean using these options per default?

tableUseRealItemNames=true
tableCaseSensitiveItemNames=true

I wish, but I guess it is not feasible as this would break many existing setups.

Besides, the problem is that none of these options do really what they’re supposed to do (based on their names).

Setting tableUseRealItemNames=true still uses the numeric prefix - what’s worse, the documentation of how it behaves is described in the tableCaseSensitiveItemNames row.

tableCaseSensitiveItemNames:

When set to false, table names are lower cased and a numeric suffix is added.

Which means that if you only set tableUseRealItemNames=true and leave tableCaseSensitiveItemNames with its default value (false) then the tableUseRealItemNames=true will not use the RealItemNames but it will lower case the item name and then append numeric suffix - and this logic which is far from just using plain real item name.

Additionally, there is no option to just get rid of this numeric suffix because if you set tableCaseSensitiveItemNames=true then not only numeric suffix is gone but also double quotes are added. Why does the option called tableCaseSensitiveItemNames touches numeric suffix?

I do agree though that a configuration having real case-sensitive table names is preferable to using item numbering and lookups.

Assuming that SQL is not case-sensitive is probably a much better and closer to reality option.

In a long term the whole configuration would be much easier and more portable across different databases if the jdbc persistence service would:

  • assume that SQL backend, by default, is not case-sensitive,
  • use item names and store them in database without any extra logic (lowercase, prefix, suffix),
  • in the rare cases when two item names become the same after using case-insensitive string comparison - then there could be an option to explicitly set the name which will be used when storing in any case-insensitive persistent service.
4

You are right, it seems the description for these two options could be reworked for clarity. Would you be willing to create a PR proposing such improvement?

I’m not sure what you mean exactly by “touches”, but it mentions those suffixes because the difference it makes is to get rid of them:

tableUseRealItemNames tableCaseSensitiveItemNames Result
false false item0001
true false myitem_0001
true true MyItem

You hit the nail on the head here, as case-sensitive item names is the reason for all this fuzz.
How would you actually manage that? I fear this could quickly turn into a nightmare, but I would be happy review a PR if you could find a proper solution for that.

5

I’ll think of it.

Please have a look at the code:

    private String formatTableName(String itemName, int itemId) {
        if (configuration.getTableCaseSensitiveItemNames()) {
            return itemName;
        } else {
            return itemName.toLowerCase() + "_" + getSuffix(itemId);
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
        }
    }

the underscore and getSuffix() seesm to be unrelated to case-sensitivity.

I’ve prepared a [jdbc] Introduce configurable quotation by dwrobel · Pull Request #18423 · openhab/openhab-addons · GitHub that intorduces new tableDoubleQuotedTableNames configuration option which with the following setup:

tableUseRealItemNames=true
tableCaseSensitiveItemNames=true
tableDoubleQuotedTableNames=false

allow me to get rid of the double quotes (successfully tested with postgresql 16.3 and timescaldb 2.18.2). It’s full backward compatible, so hopefully shouldn’t affect any existing installation.

6

When looking at this table from my last post, we can see the relation:

tableUseRealItemNames tableCaseSensitiveItemNames Result
false false item0001
true false myitem_0001
true true MyItem

We’ll need a few more lines for the complete picture:

So the suffix is only added when tableUseRealItemNames is true and tableCaseSensitiveItemNames is false, i.e. the second row in the table.

To describe it in a different way, it’s the legacy way of solving the case-sensitivity mapping issue.