From 175905892122864991b7833485372b229b97dd68 Mon Sep 17 00:00:00 2001 From: Wouter Born Date: Tue, 10 Oct 2023 13:14:48 +0200 Subject: [PATCH] [jdbc] Upgrade H2 to 2.2.224 (#15726) Upgrades H2 database from 1.4.191 to 2.2.224. Add ';NON_KEYWORDS=VALUE' to the JDBC URL because the add-on uses 'value' (which is also a keyword) as column name. Also fixes some queries used by the command extension because H2 always uses upper case table names. For the change log, see: https://h2database.com/html/changelog.html For migration notes, see: https://h2database.com/html/migration-to-v2.html Fixes #13115 Signed-off-by: Wouter Born --- bundles/org.openhab.persistence.jdbc/README.md | 18 +++++++++--------- bundles/org.openhab.persistence.jdbc/pom.xml | 2 +- .../src/main/feature/feature.xml | 2 +- .../jdbc/internal/JdbcConfiguration.java | 2 +- .../jdbc/internal/db/JdbcH2DAO.java | 4 ++-- .../main/resources/OH-INF/config/config.xml | 4 ++-- .../main/resources/OH-INF/i18n/jdbc.properties | 2 +- .../src/main/resources/conf/jdbc.cfg | 2 +- 8 files changed, 18 insertions(+), 18 deletions(-) diff --git a/bundles/org.openhab.persistence.jdbc/README.md b/bundles/org.openhab.persistence.jdbc/README.md index 5634566501..2f6f4daebe 100644 --- a/bundles/org.openhab.persistence.jdbc/README.md +++ b/bundles/org.openhab.persistence.jdbc/README.md @@ -7,16 +7,16 @@ It is designed for a maximum of scalability, to store very large amounts of data The generic design makes it relatively easy for developers to integrate other databases that have JDBC drivers. The following databases are currently supported and tested: -| Database | Tested Driver / Version | -| -------------------------------------------- | ------------------------------------------------------------ | -| [Apache Derby](https://db.apache.org/derby/) | [derby-10.14.2.0.jar](https://mvnrepository.com/artifact/org.apache.derby/derby) | -| [H2](https://www.h2database.com/) | [h2-1.4.191.jar](https://mvnrepository.com/artifact/com.h2database/h2) | -| [HSQLDB](http://hsqldb.org/) | [hsqldb-2.3.3.jar](https://mvnrepository.com/artifact/org.hsqldb/hsqldb) | +| Database | Tested Driver / Version | +| -------------------------------------------- |----------------------------------------------------------------------------------------------------------| +| [Apache Derby](https://db.apache.org/derby/) | [derby-10.14.2.0.jar](https://mvnrepository.com/artifact/org.apache.derby/derby) | +| [H2](https://www.h2database.com/) | [h2-2.2.224.jar](https://mvnrepository.com/artifact/com.h2database/h2) | +| [HSQLDB](http://hsqldb.org/) | [hsqldb-2.3.3.jar](https://mvnrepository.com/artifact/org.hsqldb/hsqldb) | | [MariaDB](https://mariadb.org/) | [mariadb-java-client-3.0.8.jar](https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client) | -| [MySQL](https://www.mysql.com/) | [mysql-connector-j-8.0.33.jar](https://mvnrepository.com/artifact/com.mysql/mysql-connector-j) | -| [PostgreSQL](https://www.postgresql.org/) | [postgresql-42.4.3.jar](https://mvnrepository.com/artifact/org.postgresql/postgresql) | -| [SQLite](https://www.sqlite.org/) | [sqlite-jdbc-3.42.0.0.jar](https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc) | -| [TimescaleDB](https://www.timescale.com/) | [postgresql-42.4.3.jar](https://mvnrepository.com/artifact/org.postgresql/postgresql) | +| [MySQL](https://www.mysql.com/) | [mysql-connector-j-8.0.33.jar](https://mvnrepository.com/artifact/com.mysql/mysql-connector-j) | +| [PostgreSQL](https://www.postgresql.org/) | [postgresql-42.4.3.jar](https://mvnrepository.com/artifact/org.postgresql/postgresql) | +| [SQLite](https://www.sqlite.org/) | [sqlite-jdbc-3.42.0.0.jar](https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc) | +| [TimescaleDB](https://www.timescale.com/) | [postgresql-42.4.3.jar](https://mvnrepository.com/artifact/org.postgresql/postgresql) | ## Table of Contents diff --git a/bundles/org.openhab.persistence.jdbc/pom.xml b/bundles/org.openhab.persistence.jdbc/pom.xml index d3a6a3bae9..3e29ab7cbf 100644 --- a/bundles/org.openhab.persistence.jdbc/pom.xml +++ b/bundles/org.openhab.persistence.jdbc/pom.xml @@ -26,7 +26,7 @@ 10.14.2.0 - 1.4.191 + 2.2.224 2.3.3 3.0.8 8.0.33 diff --git a/bundles/org.openhab.persistence.jdbc/src/main/feature/feature.xml b/bundles/org.openhab.persistence.jdbc/src/main/feature/feature.xml index df454e5035..14c7724c54 100644 --- a/bundles/org.openhab.persistence.jdbc/src/main/feature/feature.xml +++ b/bundles/org.openhab.persistence.jdbc/src/main/feature/feature.xml @@ -13,7 +13,7 @@ mvn:org.openhab.addons.features.karaf/org.openhab.addons.features.karaf.openhab-addons-external/${project.version}/cfg/jdbc openhab-runtime-base - mvn:com.h2database/h2/1.4.191 + mvn:com.h2database/h2/2.2.224 mvn:org.openhab.addons.bundles/org.openhab.persistence.jdbc/${project.version} diff --git a/bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/JdbcConfiguration.java b/bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/JdbcConfiguration.java index e39dca680c..cfea39bd63 100644 --- a/bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/JdbcConfiguration.java +++ b/bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/JdbcConfiguration.java @@ -321,7 +321,7 @@ public class JdbcConfiguration { warn += "\tDerby: version >= 10.14.2.0 from https://mvnrepository.com/artifact/org.apache.derby/derby\n"; break; case "h2": - warn += "\tH2: version >= 1.4.189 from https://mvnrepository.com/artifact/com.h2database/h2\n"; + warn += "\tH2: version >= 2.2.224 from https://mvnrepository.com/artifact/com.h2database/h2\n"; break; case "hsqldb": warn += "\tHSQLDB: version >= 2.3.3 from https://mvnrepository.com/artifact/org.hsqldb/hsqldb\n"; diff --git a/bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/db/JdbcH2DAO.java b/bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/db/JdbcH2DAO.java index 78f41cb205..cd8b955f4f 100644 --- a/bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/db/JdbcH2DAO.java +++ b/bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/internal/db/JdbcH2DAO.java @@ -52,6 +52,8 @@ public class JdbcH2DAO extends JdbcBaseDAO { private void initSqlQueries() { logger.debug("JDBC::initSqlQueries: '{}'", this.getClass().getSimpleName()); sqlIfTableExists = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='#searchTable#'"; + sqlGetItemTables = "SELECT LOWER(table_name) AS table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='PUBLIC' AND NOT table_name=UPPER('#itemsManageTable#')"; + sqlGetTableColumnTypes = "SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_schema='#jdbcUriDatabaseName#' AND table_name='#tableName#'"; // SQL_INSERT_ITEM_VALUE = "INSERT INTO #tableName# (TIME, VALUE) VALUES( NOW(), CAST( ? as #dbType#) )"; // http://stackoverflow.com/questions/19768051/h2-sql-database-insert-if-the-record-does-not-exist sqlInsertItemValue = "MERGE INTO #tableName# (TIME, VALUE) VALUES( #tablePrimaryValue#, CAST( ? as #dbType#) )"; @@ -69,8 +71,6 @@ public class JdbcH2DAO extends JdbcBaseDAO { private void initDbProps() { // Properties for HikariCP databaseProps.setProperty("driverClassName", DRIVER_CLASS_NAME); - // driverClassName OR BETTER USE dataSourceClassName - // databaseProps.setProperty("dataSourceClassName", DATA_SOURCE_CLASS_NAME); } /************** diff --git a/bundles/org.openhab.persistence.jdbc/src/main/resources/OH-INF/config/config.xml b/bundles/org.openhab.persistence.jdbc/src/main/resources/OH-INF/config/config.xml index a91244bdd9..9f83d63524 100644 --- a/bundles/org.openhab.persistence.jdbc/src/main/resources/OH-INF/config/config.xml +++ b/bundles/org.openhab.persistence.jdbc/src/main/resources/OH-INF/config/config.xml @@ -26,7 +26,7 @@ # Some URL-Examples, 'service' identifies and activates internally the correct jdbc driver. # required database url like 'jdbc::[:;]' # jdbc:url=jdbc:derby:./testDerby;create=true - # jdbc:url=jdbc:h2:./testH2 + # jdbc:url=jdbc:h2:./testH2;NON_KEYWORDS=VALUE # jdbc:url=jdbc:hsqldb:./testHsqlDb # jdbc:url=jdbc:mariadb://192.168.0.1:3306/testMariadb # jdbc:url=jdbc:mysql://192.168.0.1:3306/testMysql @@ -40,7 +40,7 @@ Parameter 'service' is used as identifier for the selected jdbc driver. URL-Examples:
jdbc:derby:./testDerby;create=true
- jdbc:h2:./testH2
+ jdbc:h2:./testH2;NON_KEYWORDS=VALUE
jdbc:hsqldb:./testHsqlDb
jdbc:mariadb://192.168.0.1:3306/testMariadb
jdbc:mysql://192.168.0.1:3306/testMysql
diff --git a/bundles/org.openhab.persistence.jdbc/src/main/resources/OH-INF/i18n/jdbc.properties b/bundles/org.openhab.persistence.jdbc/src/main/resources/OH-INF/i18n/jdbc.properties index 670c072cca..b30f16ea61 100644 --- a/bundles/org.openhab.persistence.jdbc/src/main/resources/OH-INF/i18n/jdbc.properties +++ b/bundles/org.openhab.persistence.jdbc/src/main/resources/OH-INF/i18n/jdbc.properties @@ -49,7 +49,7 @@ persistence.config.jdbc.tableUseRealItemNames.description = Enables Tablename pr persistence.config.jdbc.tableUseRealItemNames.option.true = Enable persistence.config.jdbc.tableUseRealItemNames.option.false = Disable persistence.config.jdbc.url.label = Database URL -persistence.config.jdbc.url.description = Defines required database URL and optional path and parameters.
Required database url like 'jdbc::[:;]'
Parameter 'service' is used as identifier for the selected jdbc driver. URL-Examples:
jdbc:derby:./testDerby;create=true
jdbc:h2:./testH2
jdbc:hsqldb:./testHsqlDb
jdbc:mariadb://192.168.0.1:3306/testMariadb
jdbc:mysql://192.168.0.1:3306/testMysql
jdbc:postgresql://192.168.0.1:5432/testPostgresql
jdbc:sqlite:./testSqlite.db +persistence.config.jdbc.url.description = Defines required database URL and optional path and parameters.
Required database url like 'jdbc::[:;]'
Parameter 'service' is used as identifier for the selected jdbc driver. URL-Examples:
jdbc:derby:./testDerby;create=true
jdbc:h2:./testH2;NON_KEYWORDS=VALUE
jdbc:hsqldb:./testHsqlDb
jdbc:mariadb://192.168.0.1:3306/testMariadb
jdbc:mysql://192.168.0.1:3306/testMysql
jdbc:postgresql://192.168.0.1:5432/testPostgresql
jdbc:sqlite:./testSqlite.db persistence.config.jdbc.user.label = Database User persistence.config.jdbc.user.description = Defines the database user. diff --git a/features/openhab-addons-external/src/main/resources/conf/jdbc.cfg b/features/openhab-addons-external/src/main/resources/conf/jdbc.cfg index 730a0bd513..bb74d2c6c9 100644 --- a/features/openhab-addons-external/src/main/resources/conf/jdbc.cfg +++ b/features/openhab-addons-external/src/main/resources/conf/jdbc.cfg @@ -15,7 +15,7 @@ # Some URL-Examples, 'service' identifies and activates internally the correct jdbc driver. # required database url like 'jdbc::[:;]' # url=jdbc:derby:./testDerby;create=true -# url=jdbc:h2:./testH2 +# url=jdbc:h2:./testH2;NON_KEYWORDS=VALUE # url=jdbc:hsqldb:./testHsqlDb # url=jdbc:mariadb://192.168.0.1:3306/testMariadb # url=jdbc:mysql://192.168.0.1:3306/testMysql -- 2.47.3