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.sql.SQLException;
  16 import java.time.ZoneId;
  17 import java.time.ZonedDateTime;
  18 import java.util.List;
  19
  20 import org.eclipse.jdt.annotation.NonNullByDefault;
  21 import org.knowm.yank.Yank;
  22 import org.knowm.yank.exceptions.YankSQLException;
  23 import org.openhab.core.items.Item;
  24 import org.openhab.core.persistence.FilterCriteria;
  25 import org.openhab.core.persistence.FilterCriteria.Ordering;
  26 import org.openhab.core.types.State;
  27 import org.openhab.persistence.jdbc.internal.dto.ItemVO;
  28 import org.openhab.persistence.jdbc.internal.dto.ItemsVO;
  29 import org.openhab.persistence.jdbc.internal.exceptions.JdbcSQLException;
  30 import org.openhab.persistence.jdbc.internal.utils.StringUtilsExt;
  31 import org.slf4j.Logger;
  32 import org.slf4j.LoggerFactory;
  33
  34 import oracle.sql.TIMESTAMP;
  35
  36 /**
  37  * Extended Database Configuration class for Oracle Database. Class represents
  38  * the extended database-specific configuration. Overrides and supplements the
  39  * default settings from JdbcBaseDAO. Enter only the differences to JdbcBaseDAO here.
  40  *
  41  * @author Helmut Lehmeyer - Initial contribution
  42  * @author Mark Herwege - Implemented for Oracle DB
  43  */
  44 @NonNullByDefault
  45 public class JdbcOracleDAO extends JdbcBaseDAO {
  46     @SuppressWarnings("unused")
  47     private static final String DRIVER_CLASS_NAME = oracle.jdbc.driver.OracleDriver.class.getName();
  48     private static final String DATA_SOURCE_CLASS_NAME = oracle.jdbc.datasource.impl.OracleDataSource.class.getName();
  49
  50     private final Logger logger = LoggerFactory.getLogger(JdbcOracleDAO.class);
  51
  52     protected String sqlGetItemTableID = "SELECT itemId FROM #itemsManageTable# WHERE #colname# = ?";
  53
  54     /********
  55      * INIT *
  56      ********/
  57
  58     public JdbcOracleDAO() {
  59         initSqlTypes();
  60         initDbProps();
  61         initSqlQueries();
  62     }
  63
  64     private void initSqlQueries() {
  65         logger.debug("JDBC::initSqlQueries: '{}'", this.getClass().getSimpleName());
  66
  67         sqlPingDB = "SELECT 1 FROM DUAL";
  68         sqlGetDB = "SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL"; // Not needed, just query schema that
  69                                                                                 // will be used
  70         sqlIfTableExists = "SELECT * FROM USER_TABLES WHERE TABLE_NAME = UPPER('#searchTable#')";
  71         sqlCreateNewEntryInItemsTable = "INSERT INTO #itemsManageTable# (ItemId, #colname#) VALUES (DEFAULT, ?)";
  72         sqlCreateItemsTableIfNot = """
  73                 DECLARE
  74                   table_exists NUMBER;
  75                 BEGIN
  76                   SELECT COUNT(*) INTO table_exists FROM USER_TABLES WHERE TABLE_NAME = UPPER('#itemsManageTable#');
  77                   IF table_exists = 0 THEN
  78                     EXECUTE IMMEDIATE 'CREATE TABLE #itemsManageTable#
  79                        ( ItemId NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL
  80                        , #colname# #coltype# NOT NULL
  81                        , CONSTRAINT #itemsManageTable#_PKEY PRIMARY KEY (ItemId)
  82                        )';
  83                   END IF;
  84                 END;""";
  85         sqlDropItemsTableIfExists = """
  86                 DECLARE
  87                   table_exists NUMBER;
  88                 BEGIN
  89                   SELECT COUNT(*) INTO table_exists FROM USER_TABLES WHERE TABLE_NAME = UPPER('#itemsManageTable#');
  90                   IF table_exists = 0 THEN
  91                     EXECUTE IMMEDIATE 'DROP TABLE #itemsManageTable#';
  92                   END IF;
  93                 END;""";
  94         sqlGetItemTables = "SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME != UPPER('#itemsManageTable#')";
  95         sqlGetTableColumnTypes = "SELECT COLUMN_NAME, DATA_TYPE, NULLABLE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = UPPER('#tableName#')";
  96         sqlCreateItemTable = """
  97                 DECLARE
  98                   table_exists NUMBER;
  99                 BEGIN
 100                   SELECT COUNT(*) INTO table_exists FROM USER_TABLES WHERE TABLE_NAME = UPPER('#tableName#');
 101                   IF table_exists = 0 THEN
 102                     EXECUTE IMMEDIATE 'CREATE TABLE #tableName#
 103                       ( time #tablePrimaryKey# NOT NULL
 104                       , value #dbType#
 105                       , CONSTRAINT #tableName#_PKEY PRIMARY KEY (time)
 106                       )';
 107                   END IF;
 108                 END;""";
 109         sqlAlterTableColumn = "ALTER TABLE #tableName# MODIFY (#columnName# #columnType#)";
 110         sqlInsertItemValue = """
 111                 MERGE INTO #tableName# tgt
 112                   USING (SELECT CAST(? AS TIMESTAMP) AS TIME, CAST(? AS #dbType#) AS VALUE FROM DUAL) src ON (tgt.TIME = src.TIME)
 113                   WHEN MATCHED THEN
 114                       UPDATE SET tgt.VALUE = src.VALUE
 115                   WHEN NOT MATCHED THEN
 116                       INSERT (TIME, VALUE) VALUES (src.TIME, src.VALUE)""";
 117     }
 118
 119     /**
 120      * INFO: http://www.java2s.com/Code/Java/Database-SQL-JDBC/StandardSQLDataTypeswithTheirJavaEquivalents.htm
 121      */
 122     private void initSqlTypes() {
 123         sqlTypes.put("CALLITEM", "VARCHAR2(200 CHAR)");
 124         sqlTypes.put("COLORITEM", "VARCHAR2(70)");
 125         sqlTypes.put("CONTACTITEM", "VARCHAR2(6)");
 126         sqlTypes.put("DATETIMEITEM", "TIMESTAMP");
 127         sqlTypes.put("DIMMERITEM", "NUMBER(3)");
 128         sqlTypes.put("IMAGEITEM", "CLOB");
 129         sqlTypes.put("LOCATIONITEM", "VARCHAR2(50)");
 130         sqlTypes.put("NUMBERITEM", "FLOAT");
 131         sqlTypes.put("PLAYERITEM", "VARCHAR2(20)");
 132         sqlTypes.put("ROLLERSHUTTERITEM", "NUMBER(3)");
 133         // VARCHAR2 max length 32767 bytes for MAX_STRING_SIZE=EXTENDED, only 4000 bytes when MAX_STRING_SIZE=STANDARD
 134         // (EXTENDED is default for ADB). As default character set for ADB is AL32UTF8, it takes between 1 and 4 bytes
 135         // per character, where most typical characters will only take one. Therefore use a maximum of 16000 characters.
 136         sqlTypes.put("STRINGITEM", "VARCHAR2(16000 CHAR)");
 137         sqlTypes.put("SWITCHITEM", "VARCHAR2(6)");
 138         sqlTypes.put("tablePrimaryKey", "TIMESTAMP");
 139         sqlTypes.put("tablePrimaryValue", "CURRENT_TIMESTAMP");
 140         logger.debug("JDBC::initSqlTypes: Initialized the type array sqlTypes={}", sqlTypes.values());
 141     }
 142
 143     /**
 144      * INFO: https://github.com/brettwooldridge/HikariCP
 145      */
 146     private void initDbProps() {
 147         // Tuning for performance and draining connection on ADB
 148         // See https://blogs.oracle.com/developers/post/hikaricp-best-practices-for-oracle-database-and-spring-boot
 149         System.setProperty("com.zaxxer.hikari.aliveBypassWindowMs", "-1");
 150         // Setting as system property because HikariCP as instantiated through Yank does not pass on these connection
 151         // properties from dataSource properties to the connection
 152         System.setProperty("oracle.jdbc.defaultConnectionValidation", "LOCAL");
 153         System.setProperty("oracle.jdbc.defaultRowPrefetch", "20");
 154
 155         // Properties for HikariCP
 156         databaseProps.setProperty("dataSourceClassName", DATA_SOURCE_CLASS_NAME);
 157         databaseProps.setProperty("maximumPoolSize", "3");
 158         databaseProps.setProperty("minimumIdle", "2");
 159     }
 160
 161     /**************
 162      * ITEMS DAOs *
 163      **************/
 164
 165     @Override
 166     public Long doCreateNewEntryInItemsTable(ItemsVO vo) throws JdbcSQLException {
 167         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateNewEntryInItemsTable,
 168                 new String[] { "#itemsManageTable#", "#colname#" },
 169                 new String[] { vo.getItemsManageTable(), vo.getColname() });
 170         Object[] params = { vo.getItemName() };
 171         logger.debug("JDBC::doCreateNewEntryInItemsTable sql={} item={}", sql, vo.getItemName());
 172         try {
 173             Yank.execute(sql, params);
 174         } catch (YankSQLException e) {
 175             throw new JdbcSQLException(e);
 176         }
 177         // We need to return the itemId, but Yank.insert does not retrieve the value from Oracle. So do an explicit
 178         // query
 179         // for it.
 180         sql = StringUtilsExt.replaceArrayMerge(sqlGetItemTableID, new String[] { "#itemsManageTable#", "#colname#" },
 181                 new String[] { vo.getItemsManageTable(), vo.getColname() });
 182         logger.debug("JDBC::doGetEntryIdInItemsTable sql={}", sql);
 183         try {
 184             return Yank.queryScalar(sql, Long.class, params);
 185         } catch (YankSQLException e) {
 186             throw new JdbcSQLException(e);
 187         }
 188     }
 189
 190     @Override
 191     public ItemsVO doCreateItemsTableIfNot(ItemsVO vo) throws JdbcSQLException {
 192         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateItemsTableIfNot,
 193                 new String[] { "#itemsManageTable#", "#colname#", "#coltype#" },
 194                 new String[] { vo.getItemsManageTable(), vo.getColname(), "VARCHAR2(500)" });
 195         logger.debug("JDBC::doCreateItemsTableIfNot sql={}", sql);
 196         try {
 197             Yank.execute(sql, null);
 198         } catch (YankSQLException e) {
 199             throw new JdbcSQLException(e);
 200         }
 201         return vo;
 202     }
 203
 204     /*************
 205      * ITEM DAOs *
 206      *************/
 207
 208     @Override
 209     public void doUpdateItemTableNames(List<ItemVO> vol) throws JdbcSQLException {
 210         logger.debug("JDBC::doUpdateItemTableNames vol.size = {}", vol.size());
 211         for (ItemVO itemTable : vol) {
 212             String sql = "RENAME " + itemTable.getTableName() + "  TO " + itemTable.getNewTableName();
 213             logger.debug("JDBC::updateTableName sql={} oldValue='{}' newValue='{}'", sql, itemTable.getTableName(),
 214                     itemTable.getNewTableName());
 215             try {
 216                 Yank.execute(sql, null);
 217             } catch (YankSQLException e) {
 218                 throw new JdbcSQLException(e);
 219             }
 220         }
 221     }
 222
 223     @Override
 224     public void doStoreItemValue(Item item, State itemState, ItemVO vo) throws JdbcSQLException {
 225         doStoreItemValue(item, itemState, vo, ZonedDateTime.now());
 226     }
 227
 228     @Override
 229     public void doStoreItemValue(Item item, State itemState, ItemVO vo, ZonedDateTime date) throws JdbcSQLException {
 230         ItemVO storedVO = storeItemValueProvider(item, itemState, vo);
 231         String sql = StringUtilsExt.replaceArrayMerge(sqlInsertItemValue, new String[] { "#tableName#", "#dbType#" },
 232                 new String[] { storedVO.getTableName(), storedVO.getDbType() });
 233         java.sql.Timestamp timestamp = new java.sql.Timestamp(date.toInstant().toEpochMilli());
 234         Object[] params = { timestamp, storedVO.getValue() };
 235         logger.debug("JDBC::doStoreItemValue sql={} value='{}'", sql, storedVO.getValue());
 236         try {
 237             Yank.execute(sql, params);
 238         } catch (YankSQLException e) {
 239             throw new JdbcSQLException(e);
 240         }
 241     }
 242
 243     /****************************
 244      * SQL generation Providers *
 245      ****************************/
 246
 247     @Override
 248     protected String histItemFilterQueryProvider(FilterCriteria filter, int numberDecimalcount, String table,
 249             String simpleName, ZoneId timeZone) {
 250         logger.debug(
 251                 "JDBC::getHistItemFilterQueryProvider filter = {}, numberDecimalcount = {}, table = {}, simpleName = {}",
 252                 filter, numberDecimalcount, table, simpleName);
 253
 254         String filterString = resolveTimeFilter(filter, timeZone);
 255         filterString += (filter.getOrdering() == Ordering.ASCENDING) ? " ORDER BY time ASC" : " ORDER BY time DESC";
 256         if (filter.getPageSize() != Integer.MAX_VALUE) {
 257             filterString += " OFFSET " + filter.getPageNumber() * filter.getPageSize() + " ROWS FETCH NEXT "
 258                     + filter.getPageSize() + " ROWS ONLY";
 259         }
 260         // SELECT time, ROUND(value,3) FROM number_item_0114 ORDER BY time DESC OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
 261         // rounding HALF UP
 262         String queryString = "NUMBERITEM".equalsIgnoreCase(simpleName) && numberDecimalcount > -1
 263                 ? "SELECT time, ROUND(value," + numberDecimalcount + ") FROM " + table
 264                 : "SELECT time, value FROM " + table;
 265         if (!filterString.isEmpty()) {
 266             queryString += filterString;
 267         }
 268         logger.debug("JDBC::query queryString = {}", queryString);
 269         return queryString;
 270     }
 271
 272     @Override
 273     protected String resolveTimeFilter(FilterCriteria filter, ZoneId timeZone) {
 274         String filterString = "";
 275         ZonedDateTime beginDate = filter.getBeginDate();
 276         if (beginDate != null) {
 277             filterString += filterString.isEmpty() ? " WHERE" : " AND";
 278             filterString += " TIME>=TO_TIMESTAMP('" + JDBC_DATE_FORMAT.format(beginDate.withZoneSameInstant(timeZone))
 279                     + "', 'YYYY-MM-dd HH24:MI:SS')";
 280         }
 281         ZonedDateTime endDate = filter.getEndDate();
 282         if (endDate != null) {
 283             filterString += filterString.isEmpty() ? " WHERE" : " AND";
 284             filterString += " TIME<=TO_TIMESTAMP('" + JDBC_DATE_FORMAT.format(endDate.withZoneSameInstant(timeZone))
 285                     + "', 'YYYY-MM-dd HH24:MI:SS')";
 286         }
 287         return filterString;
 288     }
 289
 290     @Override
 291     protected ZonedDateTime objectAsZonedDateTime(Object v) {
 292         if (v instanceof TIMESTAMP objectAsOracleTimestamp) {
 293             try {
 294                 return objectAsOracleTimestamp.timestampValue().toInstant().atZone(ZoneId.systemDefault());
 295             } catch (SQLException e) {
 296                 throw new UnsupportedOperationException("Date of type '" + v.getClass().getName()
 297                         + "', no Timestamp representation exists for '" + objectAsOracleTimestamp.toString() + "'");
 298             }
 299         } else {
 300             return super.objectAsZonedDateTime(v);
 301         }
 302     }
 303 }