[jdbc.mysql] Parameter tableIdDigitCount is ignored when set to 0

[bubuche79]

Hello,

In services/jdbc.cfg, when using 0 for tableIdDigitCount:

# Tablename Prefix generation, using Item real names or "item" (optional, default: false -> "item") 
# If true, 'tableNamePrefix' is ignored.
tableUseRealItemNames=true

# Tablename Suffix length (optional, default: 4 -> 0001-9999) 
# for Migration from MYSQL-Bundle set to 0.
tableIdDigitCount=0

And using this persistence/jdbc.persist file:

Strategies {
	everyHour : "0 0 * * * ?"
	default = everyChange
}

Items {
	Heating_Energy, Total_Energy : strategy = everyHour
}

I am expecting tables heating_energy and total_energy, but I'm getting heating_energy_$id1 and total_energy_$id2 instead. That would avoid dynamic (and unpredictable) tables names.

This is already pointed out by:

• https://community.openhab.org/t/jdbc-persistence-with-tableuserealitemnames-and-tableiddigitcount-0/40552
• https://community.openhab.org/t/tableiddigitcount-0-not-working/40093

In addition, why ignoring tableNamePrefix when tableUseRealItemNames is true? Prefixing all openhab tables (by "items" or whatever) would ensure that they won't be any naming clash with other objects in the schema (if I want to create other tables or views for example).

Thanks!

Note: I am using OpenHAB 2.5.0 M5.

[nimric]

I just encountered the same issue. The tableIdDigitCount should be ignored when set to 0 if tableUseRealItemNames is enabled.

[jlaur]

The behavior is documented:

Property	Default	Required	Description
tableIdDigitCount	4	No	when tableUseRealItemNames is false and thus table names are generated sequentially, this controls how many zero-padded digits are used in the table name. With the default of 4, the first table name will end with 0001. For migration from the MySQL persistence service, set this to 0.

However, with #13544 a new option was introduced:

Property	Default	Required	Description
tableCaseSensitiveItemNames	false	No	table name case when tableUseRealItemNames is true. When set to true, item name case is preserved in table names and no suffix is used. When set to false, table names are lower cased and a numeric suffix is added. Please read this before enabling.

The only thing missing:

In addition, why ignoring tableNamePrefix when tableUseRealItemNames is true? Prefixing all openhab tables (by "items" or whatever) would ensure that they won't be any naming clash with other objects in the schema (if I want to create other tables or views for example).

Currently it is (perhaps implicitly) assumed that JDBC Persistence Service will have its own schema. I will add a note in the README emphasizing this need especially for tableCaseSensitiveItemNames because of increased risk of collisions.

If there are strong arguments/need for co-existence in the same schema when using tableCaseSensitiveItemNames, I would suggest creating a new issue specifically for this. There are some obstacles though:

• Backwards compatibility: We can not redefine the interpretation of the parameters, individually or combined, as this would break existing users' installations.
• Migrations: Currently migrations need to analyze/assume things about the previous configuration, since only the new configuration is available. As an example, if tableNamePrefix would be changed without using digit suffixes, migration would have no way of finding the item tables (without having an index). See also [jdbc.mysql] MySQL migration - Items table is not used for index but creates a new table called items #9637 (comment)