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.time.ZoneId;
  16 import java.util.List;
  17 import java.util.Objects;
  18 import java.util.stream.Collectors;
  19
  20 import javax.measure.Quantity;
  21 import javax.measure.Unit;
  22
  23 import org.eclipse.jdt.annotation.NonNullByDefault;
  24 import org.eclipse.jdt.annotation.Nullable;
  25 import org.knowm.yank.Yank;
  26 import org.knowm.yank.exceptions.YankSQLException;
  27 import org.openhab.core.items.Item;
  28 import org.openhab.core.library.items.NumberItem;
  29 import org.openhab.core.persistence.FilterCriteria;
  30 import org.openhab.core.persistence.FilterCriteria.Ordering;
  31 import org.openhab.core.persistence.HistoricItem;
  32 import org.openhab.core.types.State;
  33 import org.openhab.persistence.jdbc.internal.dto.ItemVO;
  34 import org.openhab.persistence.jdbc.internal.dto.ItemsVO;
  35 import org.openhab.persistence.jdbc.internal.dto.JdbcHistoricItem;
  36 import org.openhab.persistence.jdbc.internal.exceptions.JdbcSQLException;
  37 import org.openhab.persistence.jdbc.internal.utils.StringUtilsExt;
  38 import org.slf4j.Logger;
  39 import org.slf4j.LoggerFactory;
  40
  41 /**
  42  * Extended Database Configuration class. Class represents
  43  * the extended database-specific configuration. Overrides and supplements the
  44  * default settings from JdbcBaseDAO. Enter only the differences to JdbcBaseDAO here.
  45  *
  46  * @author Helmut Lehmeyer - Initial contribution
  47  */
  48 @NonNullByDefault
  49 public class JdbcDerbyDAO extends JdbcBaseDAO {
  50     private static final String DRIVER_CLASS_NAME = org.apache.derby.jdbc.EmbeddedDriver.class.getName();
  51     @SuppressWarnings("unused")
  52     private static final String DATA_SOURCE_CLASS_NAME = org.apache.derby.jdbc.EmbeddedDataSource.class.getName();
  53
  54     private final Logger logger = LoggerFactory.getLogger(JdbcDerbyDAO.class);
  55
  56     /********
  57      * INIT *
  58      ********/
  59     public JdbcDerbyDAO() {
  60         initSqlTypes();
  61         initDbProps();
  62         initSqlQueries();
  63     }
  64
  65     private void initSqlQueries() {
  66         logger.debug("JDBC::initSqlQueries: '{}'", this.getClass().getSimpleName());
  67         sqlPingDB = "values 1";
  68         sqlGetDB = "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY( 'DataDictionaryVersion' )"; // returns version
  69         sqlIfTableExists = "SELECT * FROM SYS.SYSTABLES WHERE TABLENAME='#searchTable#'";
  70         sqlCreateItemsTableIfNot = "CREATE TABLE #itemsManageTable# ( ItemId INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), #colname# #coltype# NOT NULL)";
  71         sqlCreateItemTable = "CREATE TABLE #tableName# (time #tablePrimaryKey# NOT NULL, value #dbType#, PRIMARY KEY(time))";
  72         // Prevent error against duplicate time value (seldom): No powerful Merge found:
  73         // http://www.codeproject.com/Questions/162627/how-to-insert-new-record-in-my-table-if-not-exists
  74         sqlInsertItemValue = "INSERT INTO #tableName# (TIME, VALUE) VALUES( #tablePrimaryValue#, CAST( ? as #dbType#) )";
  75     }
  76
  77     private void initSqlTypes() {
  78         sqlTypes.put("DATETIMEITEM", "TIMESTAMP");
  79         sqlTypes.put("DIMMERITEM", "SMALLINT");
  80         sqlTypes.put("IMAGEITEM", "VARCHAR(32000)");
  81         sqlTypes.put("ROLLERSHUTTERITEM", "SMALLINT");
  82         sqlTypes.put("STRINGITEM", "VARCHAR(32000)");
  83         sqlTypes.put("tablePrimaryValue", "CURRENT_TIMESTAMP");
  84         logger.debug("JDBC::initSqlTypes: Initialized the type array sqlTypes={}", sqlTypes.values());
  85     }
  86
  87     /**
  88      * INFO: https://github.com/brettwooldridge/HikariCP
  89      */
  90     private void initDbProps() {
  91         // Properties for HikariCP
  92         // Use driverClassName
  93         databaseProps.setProperty("driverClassName", DRIVER_CLASS_NAME);
  94         // OR dataSourceClassName
  95         // databaseProps.setProperty("dataSourceClassName", DATA_SOURCE_CLASS_NAME);
  96         databaseProps.setProperty("maximumPoolSize", "1");
  97         databaseProps.setProperty("minimumIdle", "1");
  98     }
  99
 100     @Override
 101     public void initAfterFirstDbConnection() {
 102         logger.debug("JDBC::initAfterFirstDbConnection: Initializing step, after db is connected.");
 103         // Initialize sqlTypes, depending on DB version for example
 104         // derby does not like this... dbMeta = new DbMetaData();// get DB information
 105     }
 106
 107     /**************
 108      * ITEMS DAOs *
 109      **************/
 110     @Override
 111     public @Nullable Integer doPingDB() throws JdbcSQLException {
 112         try {
 113             return Yank.queryScalar(sqlPingDB, Integer.class, null);
 114         } catch (YankSQLException e) {
 115             throw new JdbcSQLException(e);
 116         }
 117     }
 118
 119     @Override
 120     public boolean doIfTableExists(ItemsVO vo) throws JdbcSQLException {
 121         String sql = StringUtilsExt.replaceArrayMerge(sqlIfTableExists, new String[] { "#searchTable#" },
 122                 new String[] { vo.getItemsManageTable().toUpperCase() });
 123         logger.debug("JDBC::doIfTableExists sql={}", sql);
 124         try {
 125             final @Nullable String result = Yank.queryScalar(sql, String.class, null);
 126             return Objects.nonNull(result);
 127         } catch (YankSQLException e) {
 128             throw new JdbcSQLException(e);
 129         }
 130     }
 131
 132     @Override
 133     public Long doCreateNewEntryInItemsTable(ItemsVO vo) throws JdbcSQLException {
 134         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateNewEntryInItemsTable,
 135                 new String[] { "#itemsManageTable#", "#itemname#" },
 136                 new String[] { vo.getItemsManageTable().toUpperCase(), vo.getItemName() });
 137         logger.debug("JDBC::doCreateNewEntryInItemsTable sql={}", sql);
 138         try {
 139             return Yank.insert(sql, null);
 140         } catch (YankSQLException e) {
 141             throw new JdbcSQLException(e);
 142         }
 143     }
 144
 145     @Override
 146     public ItemsVO doCreateItemsTableIfNot(ItemsVO vo) throws JdbcSQLException {
 147         boolean tableExists = doIfTableExists(vo);
 148         if (!tableExists) {
 149             String sql = StringUtilsExt.replaceArrayMerge(sqlCreateItemsTableIfNot,
 150                     new String[] { "#itemsManageTable#", "#colname#", "#coltype#" },
 151                     new String[] { vo.getItemsManageTable().toUpperCase(), vo.getColname(), vo.getColtype() });
 152             logger.debug("JDBC::doCreateItemsTableIfNot tableExists={} therefore sql={}", tableExists, sql);
 153             try {
 154                 Yank.execute(sql, null);
 155             } catch (YankSQLException e) {
 156                 throw new JdbcSQLException(e);
 157             }
 158         } else {
 159             logger.debug("JDBC::doCreateItemsTableIfNot tableExists={}, did not CREATE TABLE", tableExists);
 160         }
 161         return vo;
 162     }
 163
 164     /*************
 165      * ITEM DAOs *
 166      *************/
 167     @Override
 168     public void doCreateItemTable(ItemVO vo) throws JdbcSQLException {
 169         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateItemTable,
 170                 new String[] { "#tableName#", "#dbType#", "#tablePrimaryKey#" },
 171                 new String[] { vo.getTableName(), vo.getDbType(), sqlTypes.get("tablePrimaryKey") });
 172         try {
 173             Yank.execute(sql, null);
 174         } catch (YankSQLException e) {
 175             throw new JdbcSQLException(e);
 176         }
 177     }
 178
 179     @Override
 180     public void doStoreItemValue(Item item, State itemState, ItemVO vo) throws JdbcSQLException {
 181         ItemVO storedVO = storeItemValueProvider(item, itemState, vo);
 182         String sql = StringUtilsExt.replaceArrayMerge(sqlInsertItemValue,
 183                 new String[] { "#tableName#", "#dbType#", "#tablePrimaryValue#" },
 184                 new String[] { storedVO.getTableName().toUpperCase(), storedVO.getDbType(),
 185                         sqlTypes.get("tablePrimaryValue") });
 186         Object[] params = { storedVO.getValue() };
 187         logger.debug("JDBC::doStoreItemValue sql={} value='{}'", sql, storedVO.getValue());
 188         try {
 189             Yank.execute(sql, params);
 190         } catch (YankSQLException e) {
 191             throw new JdbcSQLException(e);
 192         }
 193     }
 194
 195     @Override
 196     public List<HistoricItem> doGetHistItemFilterQuery(Item item, FilterCriteria filter, int numberDecimalcount,
 197             String table, String name, ZoneId timeZone) throws JdbcSQLException {
 198         String sql = histItemFilterQueryProvider(filter, numberDecimalcount, table, name, timeZone);
 199         List<Object[]> m;
 200         try {
 201             m = Yank.queryObjectArrays(sql, null);
 202         } catch (YankSQLException e) {
 203             throw new JdbcSQLException(e);
 204         }
 205         logger.debug("JDBC::doGetHistItemFilterQuery got Array length={}", m.size());
 206         // we already retrieve the unit here once as it is a very costly operation
 207         String itemName = item.getName();
 208         Unit<? extends Quantity<?>> unit = item instanceof NumberItem ? ((NumberItem) item).getUnit() : null;
 209         return m.stream().map(o -> {
 210             logger.debug("JDBC::doGetHistItemFilterQuery 0='{}' 1='{}'", o[0], o[1]);
 211             return new JdbcHistoricItem(itemName, objectAsState(item, unit, o[1]), objectAsZonedDateTime(o[0]));
 212         }).collect(Collectors.<HistoricItem> toList());
 213     }
 214
 215     /****************************
 216      * SQL generation Providers *
 217      ****************************/
 218
 219     @Override
 220     protected String histItemFilterQueryProvider(FilterCriteria filter, int numberDecimalcount, String table,
 221             String simpleName, ZoneId timeZone) {
 222         logger.debug(
 223                 "JDBC::getHistItemFilterQueryProvider filter = {}, numberDecimalcount = {}, table = {}, simpleName = {}",
 224                 StringUtilsExt.filterToString(filter), numberDecimalcount, table, simpleName);
 225
 226         String filterString = "";
 227         if (filter.getBeginDate() != null) {
 228             filterString += filterString.isEmpty() ? " WHERE" : " AND";
 229             filterString += " TIME>'" + JDBC_DATE_FORMAT.format(filter.getBeginDate().withZoneSameInstant(timeZone))
 230                     + "'";
 231         }
 232         if (filter.getEndDate() != null) {
 233             filterString += filterString.isEmpty() ? " WHERE" : " AND";
 234             filterString += " TIME<'" + JDBC_DATE_FORMAT.format(filter.getEndDate().withZoneSameInstant(timeZone))
 235                     + "'";
 236         }
 237         filterString += (filter.getOrdering() == Ordering.ASCENDING) ? " ORDER BY time ASC" : " ORDER BY time DESC";
 238         if (filter.getPageSize() != 0x7fffffff) {
 239             // TODO: TESTING!!!
 240             // filterString += " LIMIT " + filter.getPageNumber() *
 241             // filter.getPageSize() + "," + filter.getPageSize();
 242             // SELECT time, value FROM ohscriptfiles_sw_ace_paths_0001 ORDER BY
 243             // time DESC OFFSET 1 ROWS FETCH NEXT 0 ROWS ONLY
 244             // filterString += " OFFSET " + filter.getPageSize() +" ROWS FETCH
 245             // FIRST||NEXT " + filter.getPageNumber() * filter.getPageSize() + "
 246             // ROWS ONLY";
 247             filterString += " OFFSET " + filter.getPageSize() + " ROWS FETCH FIRST "
 248                     + (filter.getPageNumber() * filter.getPageSize() + 1) + " ROWS ONLY";
 249         }
 250
 251         // http://www.seemoredata.com/en/showthread.php?132-Round-function-in-Apache-Derby
 252         // simulated round function in Derby: CAST(value 0.0005 AS DECIMAL(15,3))
 253         // simulated round function in Derby: "CAST(value 0.0005 AS DECIMAL(15,"+numberDecimalcount+"))"
 254
 255         String queryString = "SELECT time,";
 256         if ("NUMBERITEM".equalsIgnoreCase(simpleName) && numberDecimalcount > -1) {
 257             // rounding HALF UP
 258             queryString += "CAST(value 0.";
 259             for (int i = 0; i < numberDecimalcount; i++) {
 260                 queryString += "0";
 261             }
 262             queryString += "5 AS DECIMAL(31," + numberDecimalcount + "))"; // 31 is DECIMAL max precision
 263                                                                            // https://db.apache.org/derby/docs/10.0/manuals/develop/develop151.html
 264         } else {
 265             queryString += " value FROM " + table.toUpperCase();
 266         }
 267
 268         if (!filterString.isEmpty()) {
 269             queryString += filterString;
 270         }
 271         logger.debug("JDBC::query queryString = {}", queryString);
 272         return queryString;
 273     }
 274
 275     /*****************
 276      * H E L P E R S *
 277      *****************/
 278
 279     /******************************
 280      * public Getters and Setters *
 281      ******************************/
 282 }