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

   1 /**
   2  * Copyright (c) 2010-2023 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             final @Nullable Integer result = Yank.queryScalar(sqlPingDB, Integer.class, null);
 259             return result;
 260         } catch (YankSQLException e) {
 261             throw new JdbcSQLException(e);
 262         }
 263     }
 264
 265     public @Nullable String doGetDB() throws JdbcSQLException {
 266         try {
 267             final @Nullable String result = Yank.queryScalar(sqlGetDB, String.class, null);
 268             return result;
 269         } catch (YankSQLException e) {
 270             throw new JdbcSQLException(e);
 271         }
 272     }
 273
 274     public boolean doIfTableExists(ItemsVO vo) throws JdbcSQLException {
 275         String sql = StringUtilsExt.replaceArrayMerge(sqlIfTableExists, new String[] { "#searchTable#" },
 276                 new String[] { vo.getItemsManageTable() });
 277         logger.debug("JDBC::doIfTableExists sql={}", sql);
 278         try {
 279             final @Nullable String result = Yank.queryScalar(sql, String.class, null);
 280             return Objects.nonNull(result);
 281         } catch (YankSQLException e) {
 282             throw new JdbcSQLException(e);
 283         }
 284     }
 285
 286     public boolean doIfTableExists(String tableName) throws JdbcSQLException {
 287         String sql = StringUtilsExt.replaceArrayMerge(sqlIfTableExists, new String[] { "#searchTable#" },
 288                 new String[] { tableName });
 289         logger.debug("JDBC::doIfTableExists sql={}", sql);
 290         try {
 291             final @Nullable String result = Yank.queryScalar(sql, String.class, null);
 292             return Objects.nonNull(result);
 293         } catch (YankSQLException e) {
 294             throw new JdbcSQLException(e);
 295         }
 296     }
 297
 298     public Long doCreateNewEntryInItemsTable(ItemsVO vo) throws JdbcSQLException {
 299         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateNewEntryInItemsTable,
 300                 new String[] { "#itemsManageTable#", "#itemname#" },
 301                 new String[] { vo.getItemsManageTable(), vo.getItemName() });
 302         logger.debug("JDBC::doCreateNewEntryInItemsTable sql={}", sql);
 303         try {
 304             return Yank.insert(sql, null);
 305         } catch (YankSQLException e) {
 306             throw new JdbcSQLException(e);
 307         }
 308     }
 309
 310     public ItemsVO doCreateItemsTableIfNot(ItemsVO vo) throws JdbcSQLException {
 311         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateItemsTableIfNot,
 312                 new String[] { "#itemsManageTable#", "#colname#", "#coltype#" },
 313                 new String[] { vo.getItemsManageTable(), vo.getColname(), vo.getColtype() });
 314         logger.debug("JDBC::doCreateItemsTableIfNot sql={}", sql);
 315         try {
 316             Yank.execute(sql, null);
 317         } catch (YankSQLException e) {
 318             throw new JdbcSQLException(e);
 319         }
 320         return vo;
 321     }
 322
 323     public ItemsVO doDropItemsTableIfExists(ItemsVO vo) throws JdbcSQLException {
 324         String sql = StringUtilsExt.replaceArrayMerge(sqlDropItemsTableIfExists, new String[] { "#itemsManageTable#" },
 325                 new String[] { vo.getItemsManageTable() });
 326         logger.debug("JDBC::doDropItemsTableIfExists sql={}", sql);
 327         try {
 328             Yank.execute(sql, null);
 329         } catch (YankSQLException e) {
 330             throw new JdbcSQLException(e);
 331         }
 332         return vo;
 333     }
 334
 335     public void doDropTable(String tableName) throws JdbcSQLException {
 336         String sql = StringUtilsExt.replaceArrayMerge(sqlDropTable, new String[] { "#tableName#" },
 337                 new String[] { tableName });
 338         logger.debug("JDBC::doDropTable sql={}", sql);
 339         try {
 340             Yank.execute(sql, null);
 341         } catch (YankSQLException e) {
 342             throw new JdbcSQLException(e);
 343         }
 344     }
 345
 346     public void doDeleteItemsEntry(ItemsVO vo) throws JdbcSQLException {
 347         String sql = StringUtilsExt.replaceArrayMerge(sqlDeleteItemsEntry,
 348                 new String[] { "#itemsManageTable#", "#itemname#" },
 349                 new String[] { vo.getItemsManageTable(), vo.getItemName() });
 350         logger.debug("JDBC::doDeleteItemsEntry sql={}", sql);
 351         try {
 352             Yank.execute(sql, null);
 353         } catch (YankSQLException e) {
 354             throw new JdbcSQLException(e);
 355         }
 356     }
 357
 358     public List<ItemsVO> doGetItemIDTableNames(ItemsVO vo) throws JdbcSQLException {
 359         String sql = StringUtilsExt.replaceArrayMerge(sqlGetItemIDTableNames, new String[] { "#itemsManageTable#" },
 360                 new String[] { vo.getItemsManageTable() });
 361         logger.debug("JDBC::doGetItemIDTableNames sql={}", sql);
 362         try {
 363             return Yank.queryBeanList(sql, ItemsVO.class, null);
 364         } catch (YankSQLException e) {
 365             throw new JdbcSQLException(e);
 366         }
 367     }
 368
 369     public List<ItemsVO> doGetItemTables(ItemsVO vo) throws JdbcSQLException {
 370         String sql = StringUtilsExt.replaceArrayMerge(sqlGetItemTables,
 371                 new String[] { "#jdbcUriDatabaseName#", "#itemsManageTable#" },
 372                 new String[] { vo.getJdbcUriDatabaseName(), vo.getItemsManageTable() });
 373         logger.debug("JDBC::doGetItemTables sql={}", sql);
 374         try {
 375             return Yank.queryBeanList(sql, ItemsVO.class, null);
 376         } catch (YankSQLException e) {
 377             throw new JdbcSQLException(e);
 378         }
 379     }
 380
 381     public List<Column> doGetTableColumns(ItemsVO vo) throws JdbcSQLException {
 382         String sql = StringUtilsExt.replaceArrayMerge(sqlGetTableColumnTypes,
 383                 new String[] { "#jdbcUriDatabaseName#", "#tableName#" },
 384                 new String[] { vo.getJdbcUriDatabaseName(), vo.getTableName() });
 385         logger.debug("JDBC::doGetTableColumns sql={}", sql);
 386         try {
 387             return Yank.queryBeanList(sql, Column.class, null);
 388         } catch (YankSQLException e) {
 389             throw new JdbcSQLException(e);
 390         }
 391     }
 392
 393     /*************
 394      * ITEM DAOs *
 395      *************/
 396     public void doUpdateItemTableNames(List<ItemVO> vol) throws JdbcSQLException {
 397         logger.debug("JDBC::doUpdateItemTableNames vol.size = {}", vol.size());
 398         for (ItemVO itemTable : vol) {
 399             String sql = updateItemTableNamesProvider(itemTable);
 400             try {
 401                 Yank.execute(sql, null);
 402             } catch (YankSQLException e) {
 403                 throw new JdbcSQLException(e);
 404             }
 405         }
 406     }
 407
 408     public void doCreateItemTable(ItemVO vo) throws JdbcSQLException {
 409         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateItemTable,
 410                 new String[] { "#tableName#", "#dbType#", "#tablePrimaryKey#" },
 411                 new String[] { vo.getTableName(), vo.getDbType(), sqlTypes.get("tablePrimaryKey") });
 412         logger.debug("JDBC::doCreateItemTable sql={}", sql);
 413         try {
 414             Yank.execute(sql, null);
 415         } catch (YankSQLException e) {
 416             throw new JdbcSQLException(e);
 417         }
 418     }
 419
 420     public void doAlterTableColumn(String tableName, String columnName, String columnType, boolean nullable)
 421             throws JdbcSQLException {
 422         String sql = StringUtilsExt.replaceArrayMerge(sqlAlterTableColumn,
 423                 new String[] { "#tableName#", "#columnName#", "#columnType#" },
 424                 new String[] { tableName, columnName, nullable ? columnType : columnType + " NOT NULL" });
 425         logger.debug("JDBC::doAlterTableColumn sql={}", sql);
 426         try {
 427             Yank.execute(sql, null);
 428         } catch (YankSQLException e) {
 429             throw new JdbcSQLException(e);
 430         }
 431     }
 432
 433     public void doStoreItemValue(Item item, State itemState, ItemVO vo) throws JdbcSQLException {
 434         ItemVO storedVO = storeItemValueProvider(item, itemState, vo);
 435         String sql = StringUtilsExt.replaceArrayMerge(sqlInsertItemValue,
 436                 new String[] { "#tableName#", "#tablePrimaryValue#" },
 437                 new String[] { storedVO.getTableName(), sqlTypes.get("tablePrimaryValue") });
 438         Object[] params = { storedVO.getValue(), storedVO.getValue() };
 439         logger.debug("JDBC::doStoreItemValue sql={} value='{}'", sql, storedVO.getValue());
 440         try {
 441             Yank.execute(sql, params);
 442         } catch (YankSQLException e) {
 443             throw new JdbcSQLException(e);
 444         }
 445     }
 446
 447     public void doStoreItemValue(Item item, State itemState, ItemVO vo, ZonedDateTime date) throws JdbcSQLException {
 448         ItemVO storedVO = storeItemValueProvider(item, itemState, vo);
 449         String sql = StringUtilsExt.replaceArrayMerge(sqlInsertItemValue,
 450                 new String[] { "#tableName#", "#tablePrimaryValue#" }, new String[] { storedVO.getTableName(), "?" });
 451         java.sql.Timestamp timestamp = new java.sql.Timestamp(date.toInstant().toEpochMilli());
 452         Object[] params = { timestamp, storedVO.getValue(), storedVO.getValue() };
 453         logger.debug("JDBC::doStoreItemValue sql={} timestamp={} value='{}'", sql, timestamp, storedVO.getValue());
 454         try {
 455             Yank.execute(sql, params);
 456         } catch (YankSQLException e) {
 457             throw new JdbcSQLException(e);
 458         }
 459     }
 460
 461     public List<HistoricItem> doGetHistItemFilterQuery(Item item, FilterCriteria filter, int numberDecimalcount,
 462             String table, String name, ZoneId timeZone) throws JdbcSQLException {
 463         String sql = histItemFilterQueryProvider(filter, numberDecimalcount, table, name, timeZone);
 464         logger.debug("JDBC::doGetHistItemFilterQuery sql={}", sql);
 465         List<Object[]> m;
 466         try {
 467             m = Yank.queryObjectArrays(sql, null);
 468         } catch (YankSQLException e) {
 469             throw new JdbcSQLException(e);
 470         }
 471         if (m == null) {
 472             logger.debug("JDBC::doGetHistItemFilterQuery Query failed. Returning an empty list.");
 473             return List.of();
 474         }
 475         // we already retrieve the unit here once as it is a very costly operation
 476         String itemName = item.getName();
 477         Unit<? extends Quantity<?>> unit = item instanceof NumberItem ? ((NumberItem) item).getUnit() : null;
 478         return m.stream()
 479                 .map(o -> new JdbcHistoricItem(itemName, objectAsState(item, unit, o[1]), objectAsZonedDateTime(o[0])))
 480                 .collect(Collectors.<HistoricItem> toList());
 481     }
 482
 483     public void doDeleteItemValues(FilterCriteria filter, String table, ZoneId timeZone) throws JdbcSQLException {
 484         String sql = histItemFilterDeleteProvider(filter, table, timeZone);
 485         logger.debug("JDBC::doDeleteItemValues sql={}", sql);
 486         try {
 487             Yank.execute(sql, null);
 488         } catch (YankSQLException e) {
 489             throw new JdbcSQLException(e);
 490         }
 491     }
 492
 493     public long doGetRowCount(String tableName) throws JdbcSQLException {
 494         final String sql = StringUtilsExt.replaceArrayMerge(sqlGetRowCount, new String[] { "#tableName#" },
 495                 new String[] { tableName });
 496         logger.debug("JDBC::doGetRowCount sql={}", sql);
 497         try {
 498             final @Nullable Long result = Yank.queryScalar(sql, Long.class, null);
 499             return Objects.requireNonNullElse(result, 0L);
 500         } catch (YankSQLException e) {
 501             throw new JdbcSQLException(e);
 502         }
 503     }
 504
 505     /*************
 506      * Providers *
 507      *************/
 508     static final DateTimeFormatter JDBC_DATE_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
 509
 510     protected String histItemFilterQueryProvider(FilterCriteria filter, int numberDecimalcount, String table,
 511             String simpleName, ZoneId timeZone) {
 512         logger.debug(
 513                 "JDBC::getHistItemFilterQueryProvider filter = {}, numberDecimalcount = {}, table = {}, simpleName = {}",
 514                 filter, numberDecimalcount, table, simpleName);
 515
 516         String filterString = resolveTimeFilter(filter, timeZone);
 517         filterString += (filter.getOrdering() == Ordering.ASCENDING) ? " ORDER BY time ASC" : " ORDER BY time DESC";
 518         if (filter.getPageSize() != Integer.MAX_VALUE) {
 519             filterString += " LIMIT " + filter.getPageNumber() * filter.getPageSize() + "," + filter.getPageSize();
 520         }
 521         // SELECT time, ROUND(value,3) FROM number_item_0114 ORDER BY time DESC LIMIT 0,1
 522         // rounding HALF UP
 523         String queryString = "NUMBERITEM".equalsIgnoreCase(simpleName) && numberDecimalcount > -1
 524                 ? "SELECT time, ROUND(value," + numberDecimalcount + ") FROM " + table
 525                 : "SELECT time, value FROM " + table;
 526         if (!filterString.isEmpty()) {
 527             queryString += filterString;
 528         }
 529         logger.debug("JDBC::query queryString = {}", queryString);
 530         return queryString;
 531     }
 532
 533     protected String histItemFilterDeleteProvider(FilterCriteria filter, String table, ZoneId timeZone) {
 534         logger.debug("JDBC::histItemFilterDeleteProvider filter = {}, table = {}", filter, table);
 535
 536         String filterString = resolveTimeFilter(filter, timeZone);
 537         String deleteString = filterString.isEmpty() ? "TRUNCATE TABLE " + table
 538                 : "DELETE FROM " + table + filterString;
 539         logger.debug("JDBC::delete deleteString = {}", deleteString);
 540         return deleteString;
 541     }
 542
 543     protected String resolveTimeFilter(FilterCriteria filter, ZoneId timeZone) {
 544         String filterString = "";
 545         ZonedDateTime beginDate = filter.getBeginDate();
 546         if (beginDate != null) {
 547             filterString += filterString.isEmpty() ? " WHERE" : " AND";
 548             filterString += " TIME>='" + JDBC_DATE_FORMAT.format(beginDate.withZoneSameInstant(timeZone)) + "'";
 549         }
 550         ZonedDateTime endDate = filter.getEndDate();
 551         if (endDate != null) {
 552             filterString += filterString.isEmpty() ? " WHERE" : " AND";
 553             filterString += " TIME<='" + JDBC_DATE_FORMAT.format(endDate.withZoneSameInstant(timeZone)) + "'";
 554         }
 555         return filterString;
 556     }
 557
 558     private String updateItemTableNamesProvider(ItemVO itemTable) {
 559         String queryString = "ALTER TABLE " + itemTable.getTableName() + " RENAME TO " + itemTable.getNewTableName();
 560         logger.debug("JDBC::query queryString = {}", queryString);
 561         return queryString;
 562     }
 563
 564     protected ItemVO storeItemValueProvider(Item item, State itemState, ItemVO vo) {
 565         String itemType = getItemType(item);
 566
 567         logger.debug("JDBC::storeItemValueProvider: item '{}' as Type '{}' in '{}' with state '{}'", item.getName(),
 568                 itemType, vo.getTableName(), itemState);
 569
 570         // insertItemValue
 571         logger.debug("JDBC::storeItemValueProvider: itemState: '{}'", itemState);
 572         /*
 573          * !!ATTENTION!!
 574          *
 575          * 1. DimmerItem.getStateAs(PercentType.class).toString() always
 576          * returns 0
 577          * RollershutterItem.getStateAs(PercentType.class).toString() works
 578          * as expected
 579          *
 580          * 2. (item instanceof ColorItem) == (item instanceof DimmerItem) =
 581          * true Therefore for instance tests ColorItem always has to be
 582          * tested before DimmerItem
 583          *
 584          * !!ATTENTION!!
 585          */
 586         switch (itemType) {
 587             case "COLORITEM":
 588                 vo.setValueTypes(getSqlTypes().get(itemType), java.lang.String.class);
 589                 vo.setValue(itemState.toString());
 590                 break;
 591             case "NUMBERITEM":
 592                 State convertedState = itemState;
 593                 if (item instanceof NumberItem && itemState instanceof QuantityType) {
 594                     Unit<? extends Quantity<?>> unit = ((NumberItem) item).getUnit();
 595                     if (unit != null && !Units.ONE.equals(unit)) {
 596                         convertedState = ((QuantityType<?>) itemState).toUnit(unit);
 597                         if (convertedState == null) {
 598                             logger.warn(
 599                                     "JDBC::storeItemValueProvider: Failed to convert state '{}' to unit '{}'. Please check your item definition for correctness.",
 600                                     itemState, unit);
 601                             convertedState = itemState;
 602                         }
 603                     }
 604                 }
 605                 String it = getSqlTypes().get(itemType);
 606                 if (it == null) {
 607                     logger.warn("JDBC::storeItemValueProvider: No SQL type defined for item type {}", itemType);
 608                 } else if (it.toUpperCase().contains("DOUBLE")) {
 609                     vo.setValueTypes(it, java.lang.Double.class);
 610                     double value = ((Number) convertedState).doubleValue();
 611                     logger.debug("JDBC::storeItemValueProvider: newVal.doubleValue: '{}'", value);
 612                     vo.setValue(value);
 613                 } else if (it.toUpperCase().contains("DECIMAL") || it.toUpperCase().contains("NUMERIC")) {
 614                     vo.setValueTypes(it, java.math.BigDecimal.class);
 615                     BigDecimal value = BigDecimal.valueOf(((Number) convertedState).doubleValue());
 616                     logger.debug("JDBC::storeItemValueProvider: newVal.toBigDecimal: '{}'", value);
 617                     vo.setValue(value);
 618                 } else if (it.toUpperCase().contains("INT")) {
 619                     vo.setValueTypes(it, java.lang.Integer.class);
 620                     int value = ((Number) convertedState).intValue();
 621                     logger.debug("JDBC::storeItemValueProvider: newVal.intValue: '{}'", value);
 622                     vo.setValue(value);
 623                 } else {// fall back to String
 624                     vo.setValueTypes(it, java.lang.String.class);
 625                     logger.warn("JDBC::storeItemValueProvider: itemState: '{}'", convertedState);
 626                     vo.setValue(convertedState.toString());
 627                 }
 628                 break;
 629             case "ROLLERSHUTTERITEM":
 630             case "DIMMERITEM":
 631                 vo.setValueTypes(getSqlTypes().get(itemType), java.lang.Integer.class);
 632                 int value = ((DecimalType) itemState).intValue();
 633                 logger.debug("JDBC::storeItemValueProvider: newVal.intValue: '{}'", value);
 634                 vo.setValue(value);
 635                 break;
 636             case "DATETIMEITEM":
 637                 vo.setValueTypes(getSqlTypes().get(itemType), java.sql.Timestamp.class);
 638                 java.sql.Timestamp d = new java.sql.Timestamp(
 639                         ((DateTimeType) itemState).getZonedDateTime().toInstant().toEpochMilli());
 640                 logger.debug("JDBC::storeItemValueProvider: DateTimeItem: '{}'", d);
 641                 vo.setValue(d);
 642                 break;
 643             case "IMAGEITEM":
 644                 vo.setValueTypes(getSqlTypes().get(itemType), java.lang.String.class);
 645                 String encodedString = item.getState().toFullString();
 646                 logger.debug("JDBC::storeItemValueProvider: ImageItem: '{}'", encodedString);
 647                 vo.setValue(encodedString);
 648                 break;
 649             default:
 650                 // All other items should return the best format by default
 651                 vo.setValueTypes(getSqlTypes().get(itemType), java.lang.String.class);
 652                 logger.debug("JDBC::storeItemValueProvider: other: itemState: '{}'", itemState);
 653                 vo.setValue(itemState.toString());
 654                 break;
 655         }
 656         return vo;
 657     }
 658
 659     /*****************
 660      * H E L P E R S *
 661      *****************/
 662     protected State objectAsState(Item item, @Nullable Unit<? extends Quantity<?>> unit, Object v) {
 663         logger.debug(
 664                 "JDBC::ItemResultHandler::handleResult getState value = '{}', unit = '{}', getClass = '{}', clazz = '{}'",
 665                 v, unit, v.getClass(), v.getClass().getSimpleName());
 666         if (item instanceof NumberItem) {
 667             String it = getSqlTypes().get("NUMBERITEM");
 668             if (it == null) {
 669                 throw new UnsupportedOperationException("No SQL type defined for item type NUMBERITEM");
 670             }
 671             if (it.toUpperCase().contains("DOUBLE")) {
 672                 return unit == null ? new DecimalType(((Number) v).doubleValue())
 673                         : QuantityType.valueOf(((Number) v).doubleValue(), unit);
 674             } else if (it.toUpperCase().contains("DECIMAL") || it.toUpperCase().contains("NUMERIC")) {
 675                 return unit == null ? new DecimalType((BigDecimal) v)
 676                         : QuantityType.valueOf(((BigDecimal) v).doubleValue(), unit);
 677             } else if (it.toUpperCase().contains("INT")) {
 678                 return unit == null ? new DecimalType(objectAsInteger(v))
 679                         : QuantityType.valueOf(((Integer) v).doubleValue(), unit);
 680             }
 681             return unit == null ? DecimalType.valueOf(objectAsString(v)) : QuantityType.valueOf(objectAsString(v));
 682         } else if (item instanceof DateTimeItem) {
 683             return new DateTimeType(objectAsZonedDateTime(v));
 684         } else if (item instanceof ColorItem) {
 685             return HSBType.valueOf(objectAsString(v));
 686         } else if (item instanceof DimmerItem || item instanceof RollershutterItem) {
 687             return new PercentType(objectAsInteger(v));
 688         } else if (item instanceof ImageItem) {
 689             return RawType.valueOf(objectAsString(v));
 690         } else if (item instanceof ContactItem || item instanceof PlayerItem || item instanceof SwitchItem) {
 691             State state = TypeParser.parseState(item.getAcceptedDataTypes(), ((String) v).toString().trim());
 692             if (state == null) {
 693                 throw new UnsupportedOperationException("Unable to parse state for item " + item.toString());
 694             }
 695             return state;
 696         } else {
 697             State state = TypeParser.parseState(item.getAcceptedDataTypes(), ((String) v).toString());
 698             if (state == null) {
 699                 throw new UnsupportedOperationException("Unable to parse state for item " + item.toString());
 700             }
 701             return state;
 702         }
 703     }
 704
 705     protected ZonedDateTime objectAsZonedDateTime(Object v) {
 706         if (v instanceof Long) {
 707             return ZonedDateTime.ofInstant(Instant.ofEpochMilli(((Number) v).longValue()), ZoneId.systemDefault());
 708         } else if (v instanceof java.sql.Date) {
 709             return ZonedDateTime.ofInstant(Instant.ofEpochMilli(((java.sql.Date) v).getTime()), ZoneId.systemDefault());
 710         } else if (v instanceof LocalDateTime) {
 711             return ((LocalDateTime) v).atZone(ZoneId.systemDefault());
 712         } else if (v instanceof Instant) {
 713             return ((Instant) v).atZone(ZoneId.systemDefault());
 714         } else if (v instanceof java.sql.Timestamp) {
 715             return ((java.sql.Timestamp) v).toInstant().atZone(ZoneId.systemDefault());
 716         } else if (v instanceof java.lang.String) {
 717             return ZonedDateTime.ofInstant(java.sql.Timestamp.valueOf(v.toString()).toInstant(),
 718                     ZoneId.systemDefault());
 719         }
 720         throw new UnsupportedOperationException("Date of type " + v.getClass().getName() + " is not supported");
 721     }
 722
 723     protected Integer objectAsInteger(Object v) {
 724         if (v instanceof Byte) {
 725             return ((Byte) v).intValue();
 726         }
 727         return ((Integer) v).intValue();
 728     }
 729
 730     protected String objectAsString(Object v) {
 731         if (v instanceof byte[]) {
 732             return new String((byte[]) v);
 733         }
 734         return ((String) v).toString();
 735     }
 736
 737     public String getItemType(Item i) {
 738         Item item = i;
 739         String def = "STRINGITEM";
 740         if (i instanceof GroupItem) {
 741             item = ((GroupItem) i).getBaseItem();
 742             if (item == null) {
 743                 // if GroupItem:<ItemType> is not defined in *.items using StringType
 744                 logger.debug(
 745                         "JDBC::getItemType: Cannot detect ItemType for {} because the GroupItems' base type isn't set in *.items File.",
 746                         i.getName());
 747                 Iterator<Item> iterator = ((GroupItem) i).getMembers().iterator();
 748                 if (!iterator.hasNext()) {
 749                     logger.debug(
 750                             "JDBC::getItemType: No Child-Members of GroupItem {}, use ItemType for STRINGITEM as Fallback",
 751                             i.getName());
 752                     return def;
 753                 }
 754                 item = iterator.next();
 755             }
 756         }
 757         String itemType = item.getClass().getSimpleName().toUpperCase();
 758         if (sqlTypes.get(itemType) == null) {
 759             logger.warn(
 760                     "JDBC::getItemType: No sqlType found for ItemType {}, use ItemType for STRINGITEM as Fallback for {}",
 761                     itemType, i.getName());
 762             return def;
 763         }
 764         return itemType;
 765     }
 766
 767     /******************************
 768      * public Getters and Setters *
 769      ******************************/
 770     public Map<String, String> getSqlTypes() {
 771         return sqlTypes;
 772     }
 773
 774     public String getDataType(Item item) {
 775         String dataType = sqlTypes.get(getItemType(item));
 776         if (dataType == null) {
 777             throw new UnsupportedOperationException("No data type found for " + getItemType(item));
 778         }
 779         return dataType;
 780     }
 781 }