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

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