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

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