git.basschouten.com Git - openhab-addons.git/blob

   1 /**
   2  * Copyright (c) 2010-2024 Contributors to the openHAB project
   3  *
   4  * See the NOTICE file(s) distributed with this work for additional
   5  * information.
   6  *
   7  * This program and the accompanying materials are made available under the
   8  * terms of the Eclipse Public License 2.0 which is available at
   9  * http://www.eclipse.org/legal/epl-2.0
  10  *
  11  * SPDX-License-Identifier: EPL-2.0
  12  */
  13 package org.openhab.persistence.jdbc.internal.db;
  14
  15 import java.math.BigDecimal;
  16 import java.time.Instant;
  17 import java.time.LocalDateTime;
  18 import java.time.ZoneId;
  19 import java.time.ZonedDateTime;
  20 import java.time.format.DateTimeFormatter;
  21 import java.util.HashMap;
  22 import java.util.Iterator;
  23 import java.util.List;
  24 import java.util.Map;
  25 import java.util.Objects;
  26 import java.util.Properties;
  27 import java.util.stream.Collectors;
  28
  29 import javax.measure.Quantity;
  30 import javax.measure.Unit;
  31
  32 import org.eclipse.jdt.annotation.NonNullByDefault;
  33 import org.eclipse.jdt.annotation.Nullable;
  34 import org.knowm.yank.Yank;
  35 import org.knowm.yank.exceptions.YankSQLException;
  36 import org.openhab.core.items.GroupItem;
  37 import org.openhab.core.items.Item;
  38 import org.openhab.core.library.items.ColorItem;
  39 import org.openhab.core.library.items.ContactItem;
  40 import org.openhab.core.library.items.DateTimeItem;
  41 import org.openhab.core.library.items.DimmerItem;
  42 import org.openhab.core.library.items.ImageItem;
  43 import org.openhab.core.library.items.NumberItem;
  44 import org.openhab.core.library.items.PlayerItem;
  45 import org.openhab.core.library.items.RollershutterItem;
  46 import org.openhab.core.library.items.SwitchItem;
  47 import org.openhab.core.library.types.DateTimeType;
  48 import org.openhab.core.library.types.DecimalType;
  49 import org.openhab.core.library.types.HSBType;
  50 import org.openhab.core.library.types.PercentType;
  51 import org.openhab.core.library.types.QuantityType;
  52 import org.openhab.core.library.types.RawType;
  53 import org.openhab.core.library.unit.Units;
  54 import org.openhab.core.persistence.FilterCriteria;
  55 import org.openhab.core.persistence.FilterCriteria.Ordering;
  56 import org.openhab.core.persistence.HistoricItem;
  57 import org.openhab.core.types.State;
  58 import org.openhab.core.types.TypeParser;
  59 import org.openhab.persistence.jdbc.internal.dto.Column;
  60 import org.openhab.persistence.jdbc.internal.dto.ItemVO;
  61 import org.openhab.persistence.jdbc.internal.dto.ItemsVO;
  62 import org.openhab.persistence.jdbc.internal.dto.JdbcHistoricItem;
  63 import org.openhab.persistence.jdbc.internal.exceptions.JdbcSQLException;
  64 import org.openhab.persistence.jdbc.internal.utils.DbMetaData;
  65 import org.openhab.persistence.jdbc.internal.utils.StringUtilsExt;
  66 import org.slf4j.Logger;
  67 import org.slf4j.LoggerFactory;
  68
  69 /**
  70  * Default Database Configuration class.
  71  *
  72  * @author Helmut Lehmeyer - Initial contribution
  73  */
  74 @NonNullByDefault
  75 public class JdbcBaseDAO {
  76     private final Logger logger = LoggerFactory.getLogger(JdbcBaseDAO.class);
  77
  78     public final Properties databaseProps = new Properties();
  79     protected String urlSuffix = "";
  80     public final Map<String, String> sqlTypes = new HashMap<>();
  81
  82     // Get Database Meta data
  83     protected @Nullable DbMetaData dbMeta;
  84
  85     protected String sqlPingDB = "SELECT 1";
  86     protected String sqlGetDB = "SELECT DATABASE()";
  87     protected String sqlIfTableExists = "SHOW TABLES LIKE '#searchTable#'";
  88     protected String sqlCreateNewEntryInItemsTable = "INSERT INTO #itemsManageTable# (ItemName) VALUES ('#itemname#')";
  89     protected String sqlCreateItemsTableIfNot = "CREATE TABLE IF NOT EXISTS #itemsManageTable# (ItemId INT NOT NULL AUTO_INCREMENT,#colname# #coltype# NOT NULL,PRIMARY KEY (ItemId))";
  90     protected String sqlDropItemsTableIfExists = "DROP TABLE IF EXISTS #itemsManageTable#";
  91     protected String sqlDropTable = "DROP TABLE #tableName#";
  92     protected String sqlDeleteItemsEntry = "DELETE FROM #itemsManageTable# WHERE ItemName='#itemname#'";
  93     protected String sqlGetItemIDTableNames = "SELECT ItemId, ItemName FROM #itemsManageTable#";
  94     protected String sqlGetItemTables = "SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='#jdbcUriDatabaseName#' AND NOT table_name='#itemsManageTable#'";
  95     protected String sqlGetTableColumnTypes = "SELECT column_name, column_type, is_nullable FROM information_schema.columns WHERE table_schema='#jdbcUriDatabaseName#' AND table_name='#tableName#'";
  96     protected String sqlCreateItemTable = "CREATE TABLE IF NOT EXISTS #tableName# (time #tablePrimaryKey# NOT NULL, value #dbType#, PRIMARY KEY(time))";
  97     protected String sqlAlterTableColumn = "ALTER TABLE #tableName# MODIFY COLUMN #columnName# #columnType#";
  98     protected String sqlInsertItemValue = "INSERT INTO #tableName# (time, value) VALUES( #tablePrimaryValue#, ? ) ON DUPLICATE KEY UPDATE VALUE= ?";
  99     protected String sqlGetRowCount = "SELECT COUNT(*) FROM #tableName#";
 100
 101     /********
 102      * INIT *
 103      ********/
 104     public JdbcBaseDAO() {
 105         initSqlTypes();
 106         initDbProps();
 107     }
 108
 109     /**
 110      * ## Get high precision by fractal seconds, examples ##
 111      *
 112      * mysql > 5.5 + mariadb > 5.2:
 113      * DROP TABLE FractionalSeconds;
 114      * CREATE TABLE FractionalSeconds (time TIMESTAMP(3), value TIMESTAMP(3));
 115      * INSERT INTO FractionalSeconds (time, value) VALUES( NOW(3), '1999-01-09 20:11:11.126' );
 116      * SELECT time FROM FractionalSeconds ORDER BY time DESC LIMIT 1;
 117      *
 118      * mysql <= 5.5 + mariadb <= 5.2: !!! NO high precision and fractal seconds !!!
 119      * DROP TABLE FractionalSeconds;
 120      * CREATE TABLE FractionalSeconds (time TIMESTAMP, value TIMESTAMP);
 121      * INSERT INTO FractionalSeconds (time, value) VALUES( NOW(), '1999-01-09 20:11:11.126' );
 122      * SELECT time FROM FractionalSeconds ORDER BY time DESC LIMIT 1;
 123      *
 124      * derby:
 125      * DROP TABLE FractionalSeconds;
 126      * CREATE TABLE FractionalSeconds (time TIMESTAMP, value TIMESTAMP);
 127      * INSERT INTO FractionalSeconds (time, value) VALUES( CURRENT_TIMESTAMP, '1999-01-09 20:11:11.126' );
 128      * SELECT time, value FROM FractionalSeconds;
 129      *
 130      * H2 + postgreSQL + hsqldb:
 131      * DROP TABLE FractionalSeconds;
 132      * CREATE TABLE FractionalSeconds (time TIMESTAMP, value TIMESTAMP);
 133      * INSERT INTO FractionalSeconds (time, value) VALUES( NOW(), '1999-01-09 20:11:11.126' );
 134      * SELECT time, value FROM FractionalSeconds;
 135      *
 136      * Sqlite:
 137      * DROP TABLE FractionalSeconds;
 138      * CREATE TABLE FractionalSeconds (time TIMESTAMP, value TIMESTAMP);
 139      * INSERT INTO FractionalSeconds (time, value) VALUES( strftime('%Y-%m-%d %H:%M:%f' , 'now' , 'localtime'),
 140      * '1999-01-09 20:11:11.124' );
 141      * SELECT time FROM FractionalSeconds ORDER BY time DESC LIMIT 1;
 142      *
 143      */
 144
 145     /**
 146      * INFO: http://www.java2s.com/Code/Java/Database-SQL-JDBC/StandardSQLDataTypeswithTheirJavaEquivalents.htm
 147      */
 148     private void initSqlTypes() {
 149         logger.debug("JDBC::initSqlTypes: Initialize the type array");
 150         sqlTypes.put("CALLITEM", "VARCHAR(200)");
 151         sqlTypes.put("COLORITEM", "VARCHAR(70)");
 152         sqlTypes.put("CONTACTITEM", "VARCHAR(6)");
 153         sqlTypes.put("DATETIMEITEM", "TIMESTAMP");
 154         sqlTypes.put("DIMMERITEM", "TINYINT");
 155         sqlTypes.put("IMAGEITEM", "VARCHAR(65500)");// jdbc max 21845
 156         sqlTypes.put("LOCATIONITEM", "VARCHAR(50)");
 157         sqlTypes.put("NUMBERITEM", "DOUBLE");
 158         sqlTypes.put("PLAYERITEM", "VARCHAR(20)");
 159         sqlTypes.put("ROLLERSHUTTERITEM", "TINYINT");
 160         sqlTypes.put("STRINGITEM", "VARCHAR(65500)");// jdbc max 21845
 161         sqlTypes.put("SWITCHITEM", "VARCHAR(6)");
 162         sqlTypes.put("tablePrimaryKey", "TIMESTAMP");
 163         sqlTypes.put("tablePrimaryValue", "NOW()");
 164     }
 165
 166     /**
 167      * INFO: https://github.com/brettwooldridge/HikariCP
 168      *
 169      * driverClassName (used with jdbcUrl):
 170      * Derby: org.apache.derby.jdbc.EmbeddedDriver
 171      * H2: org.h2.Driver
 172      * HSQLDB: org.hsqldb.jdbcDriver
 173      * Jaybird: org.firebirdsql.jdbc.FBDriver
 174      * MariaDB: org.mariadb.jdbc.Driver
 175      * MySQL: com.mysql.cj.jdbc.Driver
 176      * MaxDB: com.sap.dbtech.jdbc.DriverSapDB
 177      * PostgreSQL: org.postgresql.Driver
 178      * SyBase: com.sybase.jdbc3.jdbc.SybDriver
 179      * SqLite: org.sqlite.JDBC
 180      *
 181      * dataSourceClassName (for alternative Configuration):
 182      * Derby: org.apache.derby.jdbc.ClientDataSource
 183      * H2: org.h2.jdbcx.JdbcDataSource
 184      * HSQLDB: org.hsqldb.jdbc.JDBCDataSource
 185      * Jaybird: org.firebirdsql.pool.FBSimpleDataSource
 186      * MariaDB, MySQL: org.mariadb.jdbc.MySQLDataSource
 187      * MaxDB: com.sap.dbtech.jdbc.DriverSapDB
 188      * PostgreSQL: org.postgresql.ds.PGSimpleDataSource
 189      * SyBase: com.sybase.jdbc4.jdbc.SybDataSource
 190      * SqLite: org.sqlite.SQLiteDataSource
 191      *
 192      * HikariPool - configuration Example:
 193      * allowPoolSuspension.............false
 194      * autoCommit......................true
 195      * catalog.........................
 196      * connectionInitSql...............
 197      * connectionTestQuery.............
 198      * connectionTimeout...............30000
 199      * dataSource......................
 200      * dataSourceClassName.............
 201      * dataSourceJNDI..................
 202      * dataSourceProperties............{password=<masked>}
 203      * driverClassName.................
 204      * healthCheckProperties...........{}
 205      * healthCheckRegistry.............
 206      * idleTimeout.....................600000
 207      * initializationFailFast..........true
 208      * isolateInternalQueries..........false
 209      * jdbc4ConnectionTest.............false
 210      * jdbcUrl.........................jdbc:mysql://192.168.0.1:3306/test
 211      * leakDetectionThreshold..........0
 212      * maxLifetime.....................1800000
 213      * maximumPoolSize.................10
 214      * metricRegistry..................
 215      * metricsTrackerFactory...........
 216      * minimumIdle.....................10
 217      * password........................<masked>
 218      * poolName........................HikariPool-0
 219      * readOnly........................false
 220      * registerMbeans..................false
 221      * scheduledExecutorService........
 222      * threadFactory...................
 223      * transactionIsolation............
 224      * username........................xxxx
 225      * validationTimeout...............5000
 226      */
 227     private void initDbProps() {
 228         // databaseProps.setProperty("dataSource.url", "jdbc:mysql://192.168.0.1:3306/test");
 229         // databaseProps.setProperty("dataSource.user", "test");
 230         // databaseProps.setProperty("dataSource.password", "test");
 231
 232         // Most relevant Performance values
 233         // maximumPoolSize to 20, minimumIdle to 5, and idleTimeout to 2 minutes.
 234         // databaseProps.setProperty("maximumPoolSize", ""+maximumPoolSize);
 235         // databaseProps.setProperty("minimumIdle", ""+minimumIdle);
 236         // databaseProps.setProperty("idleTimeout", ""+idleTimeout);
 237         // databaseProps.setProperty("connectionTimeout",""+connectionTimeout);
 238         // databaseProps.setProperty("idleTimeout", ""+idleTimeout);
 239         // databaseProps.setProperty("maxLifetime", ""+maxLifetime);
 240         // databaseProps.setProperty("validationTimeout",""+validationTimeout);
 241     }
 242
 243     public void initAfterFirstDbConnection() {
 244         logger.debug("JDBC::initAfterFirstDbConnection: Initializing step, after db is connected.");
 245         // Initialize sqlTypes, depending on DB version for example
 246         dbMeta = new DbMetaData();// get DB information
 247     }
 248
 249     public Properties getConnectionProperties() {
 250         return new Properties(this.databaseProps);
 251     }
 252
 253     /**************
 254      * ITEMS DAOs *
 255      **************/
 256     public @Nullable Integer doPingDB() throws JdbcSQLException {
 257         try {
 258             return Yank.queryScalar(sqlPingDB, Integer.class, null);
 259         } catch (YankSQLException e) {
 260             throw new JdbcSQLException(e);
 261         }
 262     }
 263
 264     public @Nullable String doGetDB() throws JdbcSQLException {
 265         try {
 266             return Yank.queryScalar(sqlGetDB, String.class, null);
 267         } catch (YankSQLException e) {
 268             throw new JdbcSQLException(e);
 269         }
 270     }
 271
 272     public boolean doIfTableExists(ItemsVO vo) throws JdbcSQLException {
 273         String sql = StringUtilsExt.replaceArrayMerge(sqlIfTableExists, new String[] { "#searchTable#" },
 274                 new String[] { vo.getItemsManageTable() });
 275         logger.debug("JDBC::doIfTableExists sql={}", sql);
 276         try {
 277             final @Nullable String result = Yank.queryScalar(sql, String.class, null);
 278             return Objects.nonNull(result);
 279         } catch (YankSQLException e) {
 280             throw new JdbcSQLException(e);
 281         }
 282     }
 283
 284     public boolean doIfTableExists(String tableName) throws JdbcSQLException {
 285         String sql = StringUtilsExt.replaceArrayMerge(sqlIfTableExists, new String[] { "#searchTable#" },
 286                 new String[] { tableName });
 287         logger.debug("JDBC::doIfTableExists sql={}", sql);
 288         try {
 289             final @Nullable String result = Yank.queryScalar(sql, String.class, null);
 290             return Objects.nonNull(result);
 291         } catch (YankSQLException e) {
 292             throw new JdbcSQLException(e);
 293         }
 294     }
 295
 296     public Long doCreateNewEntryInItemsTable(ItemsVO vo) throws JdbcSQLException {
 297         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateNewEntryInItemsTable,
 298                 new String[] { "#itemsManageTable#", "#itemname#" },
 299                 new String[] { vo.getItemsManageTable(), vo.getItemName() });
 300         logger.debug("JDBC::doCreateNewEntryInItemsTable sql={}", sql);
 301         try {
 302             return Yank.insert(sql, null);
 303         } catch (YankSQLException e) {
 304             throw new JdbcSQLException(e);
 305         }
 306     }
 307
 308     public ItemsVO doCreateItemsTableIfNot(ItemsVO vo) throws JdbcSQLException {
 309         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateItemsTableIfNot,
 310                 new String[] { "#itemsManageTable#", "#colname#", "#coltype#" },
 311                 new String[] { vo.getItemsManageTable(), vo.getColname(), vo.getColtype() });
 312         logger.debug("JDBC::doCreateItemsTableIfNot sql={}", sql);
 313         try {
 314             Yank.execute(sql, null);
 315         } catch (YankSQLException e) {
 316             throw new JdbcSQLException(e);
 317         }
 318         return vo;
 319     }
 320
 321     public ItemsVO doDropItemsTableIfExists(ItemsVO vo) throws JdbcSQLException {
 322         String sql = StringUtilsExt.replaceArrayMerge(sqlDropItemsTableIfExists, new String[] { "#itemsManageTable#" },
 323                 new String[] { vo.getItemsManageTable() });
 324         logger.debug("JDBC::doDropItemsTableIfExists sql={}", sql);
 325         try {
 326             Yank.execute(sql, null);
 327         } catch (YankSQLException e) {
 328             throw new JdbcSQLException(e);
 329         }
 330         return vo;
 331     }
 332
 333     public void doDropTable(String tableName) throws JdbcSQLException {
 334         String sql = StringUtilsExt.replaceArrayMerge(sqlDropTable, new String[] { "#tableName#" },
 335                 new String[] { tableName });
 336         logger.debug("JDBC::doDropTable sql={}", sql);
 337         try {
 338             Yank.execute(sql, null);
 339         } catch (YankSQLException e) {
 340             throw new JdbcSQLException(e);
 341         }
 342     }
 343
 344     public void doDeleteItemsEntry(ItemsVO vo) throws JdbcSQLException {
 345         String sql = StringUtilsExt.replaceArrayMerge(sqlDeleteItemsEntry,
 346                 new String[] { "#itemsManageTable#", "#itemname#" },
 347                 new String[] { vo.getItemsManageTable(), vo.getItemName() });
 348         logger.debug("JDBC::doDeleteItemsEntry sql={}", sql);
 349         try {
 350             Yank.execute(sql, null);
 351         } catch (YankSQLException e) {
 352             throw new JdbcSQLException(e);
 353         }
 354     }
 355
 356     public List<ItemsVO> doGetItemIDTableNames(ItemsVO vo) throws JdbcSQLException {
 357         String sql = StringUtilsExt.replaceArrayMerge(sqlGetItemIDTableNames, new String[] { "#itemsManageTable#" },
 358                 new String[] { vo.getItemsManageTable() });
 359         logger.debug("JDBC::doGetItemIDTableNames sql={}", sql);
 360         try {
 361             return Yank.queryBeanList(sql, ItemsVO.class, null);
 362         } catch (YankSQLException e) {
 363             throw new JdbcSQLException(e);
 364         }
 365     }
 366
 367     public List<ItemsVO> doGetItemTables(ItemsVO vo) throws JdbcSQLException {
 368         String sql = StringUtilsExt.replaceArrayMerge(sqlGetItemTables,
 369                 new String[] { "#jdbcUriDatabaseName#", "#itemsManageTable#" },
 370                 new String[] { vo.getJdbcUriDatabaseName(), vo.getItemsManageTable() });
 371         logger.debug("JDBC::doGetItemTables sql={}", sql);
 372         try {
 373             return Yank.queryBeanList(sql, ItemsVO.class, null);
 374         } catch (YankSQLException e) {
 375             throw new JdbcSQLException(e);
 376         }
 377     }
 378
 379     public List<Column> doGetTableColumns(ItemsVO vo) throws JdbcSQLException {
 380         String sql = StringUtilsExt.replaceArrayMerge(sqlGetTableColumnTypes,
 381                 new String[] { "#jdbcUriDatabaseName#", "#tableName#" },
 382                 new String[] { vo.getJdbcUriDatabaseName(), vo.getTableName() });
 383         logger.debug("JDBC::doGetTableColumns sql={}", sql);
 384         try {
 385             return Yank.queryBeanList(sql, Column.class, null);
 386         } catch (YankSQLException e) {
 387             throw new JdbcSQLException(e);
 388         }
 389     }
 390
 391     /*************
 392      * ITEM DAOs *
 393      *************/
 394     public void doUpdateItemTableNames(List<ItemVO> vol) throws JdbcSQLException {
 395         logger.debug("JDBC::doUpdateItemTableNames vol.size = {}", vol.size());
 396         for (ItemVO itemTable : vol) {
 397             String sql = updateItemTableNamesProvider(itemTable);
 398             try {
 399                 Yank.execute(sql, null);
 400             } catch (YankSQLException e) {
 401                 throw new JdbcSQLException(e);
 402             }
 403         }
 404     }
 405
 406     public void doCreateItemTable(ItemVO vo) throws JdbcSQLException {
 407         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateItemTable,
 408                 new String[] { "#tableName#", "#dbType#", "#tablePrimaryKey#" },
 409                 new String[] { vo.getTableName(), vo.getDbType(), sqlTypes.get("tablePrimaryKey") });
 410         logger.debug("JDBC::doCreateItemTable sql={}", sql);
 411         try {
 412             Yank.execute(sql, null);
 413         } catch (YankSQLException e) {
 414             throw new JdbcSQLException(e);
 415         }
 416     }
 417
 418     public void doAlterTableColumn(String tableName, String columnName, String columnType, boolean nullable)
 419             throws JdbcSQLException {
 420         String sql = StringUtilsExt.replaceArrayMerge(sqlAlterTableColumn,
 421                 new String[] { "#tableName#", "#columnName#", "#columnType#" },
 422                 new String[] { tableName, columnName, nullable ? columnType : columnType + " NOT NULL" });
 423         logger.debug("JDBC::doAlterTableColumn sql={}", sql);
 424         try {
 425             Yank.execute(sql, null);
 426         } catch (YankSQLException e) {
 427             throw new JdbcSQLException(e);
 428         }
 429     }
 430
 431     public void doStoreItemValue(Item item, State itemState, ItemVO vo) throws JdbcSQLException {
 432         ItemVO storedVO = storeItemValueProvider(item, itemState, vo);
 433         String sql = StringUtilsExt.replaceArrayMerge(sqlInsertItemValue,
 434                 new String[] { "#tableName#", "#tablePrimaryValue#" },
 435                 new String[] { storedVO.getTableName(), sqlTypes.get("tablePrimaryValue") });
 436         Object[] params = { storedVO.getValue(), storedVO.getValue() };
 437         logger.debug("JDBC::doStoreItemValue sql={} value='{}'", sql, storedVO.getValue());
 438         try {
 439             Yank.execute(sql, params);
 440         } catch (YankSQLException e) {
 441             throw new JdbcSQLException(e);
 442         }
 443     }
 444
 445     public void doStoreItemValue(Item item, State itemState, ItemVO vo, ZonedDateTime date) throws JdbcSQLException {
 446         ItemVO storedVO = storeItemValueProvider(item, itemState, vo);
 447         String sql = StringUtilsExt.replaceArrayMerge(sqlInsertItemValue,
 448                 new String[] { "#tableName#", "#tablePrimaryValue#" }, new String[] { storedVO.getTableName(), "?" });
 449         java.sql.Timestamp timestamp = new java.sql.Timestamp(date.toInstant().toEpochMilli());
 450         Object[] params = { timestamp, storedVO.getValue(), storedVO.getValue() };
 451         logger.debug("JDBC::doStoreItemValue sql={} timestamp={} value='{}'", sql, timestamp, storedVO.getValue());
 452         try {
 453             Yank.execute(sql, params);
 454         } catch (YankSQLException e) {
 455             throw new JdbcSQLException(e);
 456         }
 457     }
 458
 459     public List<HistoricItem> doGetHistItemFilterQuery(Item item, FilterCriteria filter, int numberDecimalcount,
 460             String table, String name, ZoneId timeZone) throws JdbcSQLException {
 461         String sql = histItemFilterQueryProvider(filter, numberDecimalcount, table, name, timeZone);
 462         logger.debug("JDBC::doGetHistItemFilterQuery sql={}", sql);
 463         List<Object[]> m;
 464         try {
 465             m = Yank.queryObjectArrays(sql, null);
 466         } catch (YankSQLException e) {
 467             throw new JdbcSQLException(e);
 468         }
 469         if (m == null) {
 470             logger.debug("JDBC::doGetHistItemFilterQuery Query failed. Returning an empty list.");
 471             return List.of();
 472         }
 473         // we already retrieve the unit here once as it is a very costly operation
 474         String itemName = item.getName();
 475         Unit<? extends Quantity<?>> unit = item instanceof NumberItem numberItem ? numberItem.getUnit() : null;
 476         return m.stream()
 477                 .map(o -> new JdbcHistoricItem(itemName, objectAsState(item, unit, o[1]), objectAsZonedDateTime(o[0])))
 478                 .collect(Collectors.<HistoricItem> toList());
 479     }
 480
 481     public void doDeleteItemValues(FilterCriteria filter, String table, ZoneId timeZone) throws JdbcSQLException {
 482         String sql = histItemFilterDeleteProvider(filter, table, timeZone);
 483         logger.debug("JDBC::doDeleteItemValues sql={}", sql);
 484         try {
 485             Yank.execute(sql, null);
 486         } catch (YankSQLException e) {
 487             throw new JdbcSQLException(e);
 488         }
 489     }
 490
 491     public long doGetRowCount(String tableName) throws JdbcSQLException {
 492         final String sql = StringUtilsExt.replaceArrayMerge(sqlGetRowCount, new String[] { "#tableName#" },
 493                 new String[] { tableName });
 494         logger.debug("JDBC::doGetRowCount sql={}", sql);
 495         try {
 496             final @Nullable Long result = Yank.queryScalar(sql, Long.class, null);
 497             return Objects.requireNonNullElse(result, 0L);
 498         } catch (YankSQLException e) {
 499             throw new JdbcSQLException(e);
 500         }
 501     }
 502
 503     /*************
 504      * Providers *
 505      *************/
 506     static final DateTimeFormatter JDBC_DATE_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
 507
 508     protected String histItemFilterQueryProvider(FilterCriteria filter, int numberDecimalcount, String table,
 509             String simpleName, ZoneId timeZone) {
 510         logger.debug(
 511                 "JDBC::getHistItemFilterQueryProvider filter = {}, numberDecimalcount = {}, table = {}, simpleName = {}",
 512                 filter, numberDecimalcount, table, simpleName);
 513
 514         String filterString = resolveTimeFilter(filter, timeZone);
 515         filterString += (filter.getOrdering() == Ordering.ASCENDING) ? " ORDER BY time ASC" : " ORDER BY time DESC";
 516         if (filter.getPageSize() != Integer.MAX_VALUE) {
 517             filterString += " LIMIT " + filter.getPageNumber() * filter.getPageSize() + "," + filter.getPageSize();
 518         }
 519         // SELECT time, ROUND(value,3) FROM number_item_0114 ORDER BY time DESC LIMIT 0,1
 520         // rounding HALF UP
 521         String queryString = "NUMBERITEM".equalsIgnoreCase(simpleName) && numberDecimalcount > -1
 522                 ? "SELECT time, ROUND(value," + numberDecimalcount + ") FROM " + table
 523                 : "SELECT time, value FROM " + table;
 524         if (!filterString.isEmpty()) {
 525             queryString += filterString;
 526         }
 527         logger.debug("JDBC::query queryString = {}", queryString);
 528         return queryString;
 529     }
 530
 531     protected String histItemFilterDeleteProvider(FilterCriteria filter, String table, ZoneId timeZone) {
 532         logger.debug("JDBC::histItemFilterDeleteProvider filter = {}, table = {}", filter, table);
 533
 534         String filterString = resolveTimeFilter(filter, timeZone);
 535         String deleteString = filterString.isEmpty() ? "TRUNCATE TABLE " + table
 536                 : "DELETE FROM " + table + filterString;
 537         logger.debug("JDBC::delete deleteString = {}", deleteString);
 538         return deleteString;
 539     }
 540
 541     protected String resolveTimeFilter(FilterCriteria filter, ZoneId timeZone) {
 542         String filterString = "";
 543         ZonedDateTime beginDate = filter.getBeginDate();
 544         if (beginDate != null) {
 545             filterString += filterString.isEmpty() ? " WHERE" : " AND";
 546             filterString += " TIME>='" + JDBC_DATE_FORMAT.format(beginDate.withZoneSameInstant(timeZone)) + "'";
 547         }
 548         ZonedDateTime endDate = filter.getEndDate();
 549         if (endDate != null) {
 550             filterString += filterString.isEmpty() ? " WHERE" : " AND";
 551             filterString += " TIME<='" + JDBC_DATE_FORMAT.format(endDate.withZoneSameInstant(timeZone)) + "'";
 552         }
 553         return filterString;
 554     }
 555
 556     private String updateItemTableNamesProvider(ItemVO itemTable) {
 557         String queryString = "ALTER TABLE " + itemTable.getTableName() + " RENAME TO " + itemTable.getNewTableName();
 558         logger.debug("JDBC::query queryString = {}", queryString);
 559         return queryString;
 560     }
 561
 562     protected ItemVO storeItemValueProvider(Item item, State itemState, ItemVO vo) {
 563         String itemType = getItemType(item);
 564
 565         logger.debug("JDBC::storeItemValueProvider: item '{}' as Type '{}' in '{}' with state '{}'", item.getName(),
 566                 itemType, vo.getTableName(), itemState);
 567
 568         // insertItemValue
 569         logger.debug("JDBC::storeItemValueProvider: itemState: '{}'", itemState);
 570         /*
 571          * !!ATTENTION!!
 572          *
 573          * 1. DimmerItem.getStateAs(PercentType.class).toString() always
 574          * returns 0
 575          * RollershutterItem.getStateAs(PercentType.class).toString() works
 576          * as expected
 577          *
 578          * 2. (item instanceof ColorItem) == (item instanceof DimmerItem) =
 579          * true Therefore for instance tests ColorItem always has to be
 580          * tested before DimmerItem
 581          *
 582          * !!ATTENTION!!
 583          */
 584         switch (itemType) {
 585             case "COLORITEM":
 586                 vo.setValueTypes(getSqlTypes().get(itemType), java.lang.String.class);
 587                 vo.setValue(itemState.toString());
 588                 break;
 589             case "NUMBERITEM":
 590                 State convertedState = itemState;
 591                 if (item instanceof NumberItem numberItem && itemState instanceof QuantityType<?> quantityState) {
 592                     Unit<? extends Quantity<?>> unit = numberItem.getUnit();
 593                     if (unit != null && !Units.ONE.equals(unit)) {
 594                         convertedState = quantityState.toUnit(unit);
 595                         if (convertedState == null) {
 596                             logger.warn(
 597                                     "JDBC::storeItemValueProvider: Failed to convert state '{}' to unit '{}'. Please check your item definition for correctness.",
 598                                     itemState, unit);
 599                             convertedState = itemState;
 600                         }
 601                     }
 602                 }
 603                 String it = getSqlTypes().get(itemType);
 604                 if (it == null) {
 605                     logger.warn("JDBC::storeItemValueProvider: No SQL type defined for item type {}", itemType);
 606                 } else if (it.toUpperCase().contains("DOUBLE") || (it.toUpperCase().contains("FLOAT"))) {
 607                     vo.setValueTypes(it, java.lang.Double.class);
 608                     double value = ((Number) convertedState).doubleValue();
 609                     logger.debug("JDBC::storeItemValueProvider: newVal.doubleValue: '{}'", value);
 610                     vo.setValue(value);
 611                 } else if (it.toUpperCase().contains("DECIMAL") || it.toUpperCase().contains("NUMERIC")) {
 612                     vo.setValueTypes(it, java.math.BigDecimal.class);
 613                     BigDecimal value = BigDecimal.valueOf(((Number) convertedState).doubleValue());
 614                     logger.debug("JDBC::storeItemValueProvider: newVal.toBigDecimal: '{}'", value);
 615                     vo.setValue(value);
 616                 } else if (it.toUpperCase().contains("INT")) {
 617                     vo.setValueTypes(it, java.lang.Integer.class);
 618                     int value = ((Number) convertedState).intValue();
 619                     logger.debug("JDBC::storeItemValueProvider: newVal.intValue: '{}'", value);
 620                     vo.setValue(value);
 621                 } else {// fall back to String
 622                     vo.setValueTypes(it, java.lang.String.class);
 623                     logger.warn("JDBC::storeItemValueProvider: itemState: '{}'", convertedState);
 624                     vo.setValue(convertedState.toString());
 625                 }
 626                 break;
 627             case "ROLLERSHUTTERITEM":
 628             case "DIMMERITEM":
 629                 vo.setValueTypes(getSqlTypes().get(itemType), java.lang.Integer.class);
 630                 int value = ((DecimalType) itemState).intValue();
 631                 logger.debug("JDBC::storeItemValueProvider: newVal.intValue: '{}'", value);
 632                 vo.setValue(value);
 633                 break;
 634             case "DATETIMEITEM":
 635                 vo.setValueTypes(getSqlTypes().get(itemType), java.sql.Timestamp.class);
 636                 java.sql.Timestamp d = new java.sql.Timestamp(
 637                         ((DateTimeType) itemState).getZonedDateTime().toInstant().toEpochMilli());
 638                 logger.debug("JDBC::storeItemValueProvider: DateTimeItem: '{}'", d);
 639                 vo.setValue(d);
 640                 break;
 641             case "IMAGEITEM":
 642                 vo.setValueTypes(getSqlTypes().get(itemType), java.lang.String.class);
 643                 String encodedString = item.getState().toFullString();
 644                 logger.debug("JDBC::storeItemValueProvider: ImageItem: '{}'", encodedString);
 645                 vo.setValue(encodedString);
 646                 break;
 647             default:
 648                 // All other items should return the best format by default
 649                 vo.setValueTypes(getSqlTypes().get(itemType), java.lang.String.class);
 650                 logger.debug("JDBC::storeItemValueProvider: other: itemState: '{}'", itemState);
 651                 vo.setValue(itemState.toString());
 652                 break;
 653         }
 654         return vo;
 655     }
 656
 657     /*****************
 658      * H E L P E R S *
 659      *****************/
 660     protected State objectAsState(Item item, @Nullable Unit<? extends Quantity<?>> unit, Object v) {
 661         logger.debug(
 662                 "JDBC::ItemResultHandler::handleResult getState value = '{}', unit = '{}', getClass = '{}', clazz = '{}'",
 663                 v, unit, v.getClass(), v.getClass().getSimpleName());
 664         if (item instanceof NumberItem) {
 665             String it = getSqlTypes().get("NUMBERITEM");
 666             if (it == null) {
 667                 throw new UnsupportedOperationException("No SQL type defined for item type NUMBERITEM");
 668             }
 669             if (it.toUpperCase().contains("DOUBLE") || (it.toUpperCase().contains("FLOAT"))) {
 670                 return unit == null ? new DecimalType(objectAsNumber(v).doubleValue())
 671                         : QuantityType.valueOf(objectAsNumber(v).doubleValue(), unit);
 672             } else if (it.toUpperCase().contains("DECIMAL") || it.toUpperCase().contains("NUMERIC")) {
 673                 return unit == null ? new DecimalType(objectAsBigDecimal(v))
 674                         : QuantityType.valueOf(objectAsBigDecimal(v).doubleValue(), unit);
 675             } else if (it.toUpperCase().contains("INT")) {
 676                 return unit == null ? new DecimalType(objectAsInteger(v))
 677                         : QuantityType.valueOf(objectAsInteger(v).doubleValue(), unit);
 678             }
 679             return unit == null ? DecimalType.valueOf(objectAsString(v)) : QuantityType.valueOf(objectAsString(v));
 680         } else if (item instanceof DateTimeItem) {
 681             return new DateTimeType(objectAsZonedDateTime(v));
 682         } else if (item instanceof ColorItem) {
 683             return HSBType.valueOf(objectAsString(v));
 684         } else if (item instanceof DimmerItem || item instanceof RollershutterItem) {
 685             return new PercentType(objectAsInteger(v));
 686         } else if (item instanceof ImageItem) {
 687             return RawType.valueOf(objectAsString(v));
 688         } else if (item instanceof ContactItem || item instanceof PlayerItem || item instanceof SwitchItem) {
 689             State state = TypeParser.parseState(item.getAcceptedDataTypes(), objectAsString(v).trim());
 690             if (state == null) {
 691                 throw new UnsupportedOperationException("Unable to parse state for item " + item.toString());
 692             }
 693             return state;
 694         } else {
 695             if (!(v instanceof String objectAsString)) {
 696                 throw new UnsupportedOperationException(
 697                         "Type '" + v.getClass().getName() + "' is not supported for item " + item.toString());
 698             }
 699             State state = TypeParser.parseState(item.getAcceptedDataTypes(), objectAsString);
 700             if (state == null) {
 701                 throw new UnsupportedOperationException("Unable to parse state for item " + item.toString());
 702             }
 703             return state;
 704         }
 705     }
 706
 707     protected ZonedDateTime objectAsZonedDateTime(Object v) {
 708         if (v instanceof Long) {
 709             return ZonedDateTime.ofInstant(Instant.ofEpochMilli(((Number) v).longValue()), ZoneId.systemDefault());
 710         } else if (v instanceof java.sql.Date objectAsDate) {
 711             return ZonedDateTime.ofInstant(Instant.ofEpochMilli(objectAsDate.getTime()), ZoneId.systemDefault());
 712         } else if (v instanceof LocalDateTime objectAsLocalDateTime) {
 713             return objectAsLocalDateTime.atZone(ZoneId.systemDefault());
 714         } else if (v instanceof Instant objectAsInstant) {
 715             return objectAsInstant.atZone(ZoneId.systemDefault());
 716         } else if (v instanceof java.sql.Timestamp objectAsTimestamp) {
 717             return objectAsTimestamp.toInstant().atZone(ZoneId.systemDefault());
 718         } else if (v instanceof java.lang.String objectAsString) {
 719             return ZonedDateTime.ofInstant(java.sql.Timestamp.valueOf(objectAsString).toInstant(),
 720                     ZoneId.systemDefault());
 721         }
 722         throw new UnsupportedOperationException("Date of type '" + v.getClass().getName() + "' is not supported");
 723     }
 724
 725     protected Integer objectAsInteger(Object v) {
 726         if (v instanceof Byte byteValue) {
 727             return byteValue.intValue();
 728         } else if (v instanceof Integer intValue) {
 729             return intValue;
 730         } else if (v instanceof BigDecimal bdValue) {
 731             return bdValue.intValue();
 732         }
 733         throw new UnsupportedOperationException("Integer of type '" + v.getClass().getName() + "' is not supported");
 734     }
 735
 736     protected Number objectAsNumber(Object value) {
 737         if (value instanceof Number valueAsNumber) {
 738             return valueAsNumber;
 739         }
 740         throw new UnsupportedOperationException("Number of type '" + value.getClass().getName() + "' is not supported");
 741     }
 742
 743     protected BigDecimal objectAsBigDecimal(Object value) {
 744         if (value instanceof BigDecimal valueAsBigDecimal) {
 745             return valueAsBigDecimal;
 746         }
 747         throw new UnsupportedOperationException(
 748                 "BigDecimal of type '" + value.getClass().getName() + "' is not supported");
 749     }
 750
 751     protected String objectAsString(Object v) {
 752         if (v instanceof byte[] objectAsBytes) {
 753             return new String(objectAsBytes);
 754         } else if (v instanceof String objectAsString) {
 755             return objectAsString;
 756         }
 757         throw new UnsupportedOperationException("String of type '" + v.getClass().getName() + "' is not supported");
 758     }
 759
 760     public String getItemType(Item i) {
 761         Item item = i;
 762         String def = "STRINGITEM";
 763         if (i instanceof GroupItem groupItem) {
 764             item = groupItem.getBaseItem();
 765             if (item == null) {
 766                 // if GroupItem:<ItemType> is not defined in *.items using StringType
 767                 logger.debug(
 768                         "JDBC::getItemType: Cannot detect ItemType for {} because the GroupItems' base type isn't set in *.items File.",
 769                         i.getName());
 770                 Iterator<Item> iterator = groupItem.getMembers().iterator();
 771                 if (!iterator.hasNext()) {
 772                     logger.debug(
 773                             "JDBC::getItemType: No Child-Members of GroupItem {}, use ItemType for STRINGITEM as Fallback",
 774                             i.getName());
 775                     return def;
 776                 }
 777                 item = iterator.next();
 778             }
 779         }
 780         String itemType = item.getClass().getSimpleName().toUpperCase();
 781         if (sqlTypes.get(itemType) == null) {
 782             logger.warn(
 783                     "JDBC::getItemType: No sqlType found for ItemType {}, use ItemType for STRINGITEM as Fallback for {}",
 784                     itemType, i.getName());
 785             return def;
 786         }
 787         return itemType;
 788     }
 789
 790     /******************************
 791      * public Getters and Setters *
 792      ******************************/
 793     public Map<String, String> getSqlTypes() {
 794         return sqlTypes;
 795     }
 796
 797     public String getDataType(Item item) {
 798         String dataType = sqlTypes.get(getItemType(item));
 799         if (dataType == null) {
 800             throw new UnsupportedOperationException("No data type found for " + getItemType(item));
 801         }
 802         return dataType;
 803     }
 804 }