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.time.ZonedDateTime;
  17 import java.util.List;
  18
  19 import org.eclipse.jdt.annotation.NonNullByDefault;
  20 import org.knowm.yank.Yank;
  21 import org.knowm.yank.exceptions.YankSQLException;
  22 import org.openhab.core.items.Item;
  23 import org.openhab.core.persistence.FilterCriteria;
  24 import org.openhab.core.persistence.FilterCriteria.Ordering;
  25 import org.openhab.core.types.State;
  26 import org.openhab.persistence.jdbc.internal.dto.ItemVO;
  27 import org.openhab.persistence.jdbc.internal.dto.ItemsVO;
  28 import org.openhab.persistence.jdbc.internal.exceptions.JdbcSQLException;
  29 import org.openhab.persistence.jdbc.internal.utils.StringUtilsExt;
  30 import org.slf4j.Logger;
  31 import org.slf4j.LoggerFactory;
  32
  33 /**
  34  * Extended Database Configuration class. Class represents
  35  * the extended database-specific configuration. Overrides and supplements the
  36  * default settings from JdbcBaseDAO. Enter only the differences to JdbcBaseDAO here.
  37  *
  38  * @author Helmut Lehmeyer - Initial contribution
  39  */
  40 @NonNullByDefault
  41 public class JdbcPostgresqlDAO extends JdbcBaseDAO {
  42     private static final String DRIVER_CLASS_NAME = org.postgresql.Driver.class.getName();
  43     @SuppressWarnings("unused")
  44     private static final String DATA_SOURCE_CLASS_NAME = org.postgresql.ds.PGSimpleDataSource.class.getName();
  45
  46     private final Logger logger = LoggerFactory.getLogger(JdbcPostgresqlDAO.class);
  47
  48     /********
  49      * INIT *
  50      ********/
  51     public JdbcPostgresqlDAO() {
  52         initSqlQueries();
  53         initSqlTypes();
  54         initDbProps();
  55     }
  56
  57     private void initSqlQueries() {
  58         logger.debug("JDBC::initSqlQueries: '{}'", this.getClass().getSimpleName());
  59         // System Information Functions: https://www.postgresql.org/docs/9.2/static/functions-info.html
  60         sqlGetDB = "SELECT CURRENT_DATABASE()";
  61         sqlIfTableExists = "SELECT * FROM PG_TABLES WHERE TABLENAME='#searchTable#'";
  62         sqlCreateItemsTableIfNot = "CREATE TABLE IF NOT EXISTS #itemsManageTable# (itemid SERIAL NOT NULL, #colname# #coltype# NOT NULL, CONSTRAINT #itemsManageTable#_pkey PRIMARY KEY (itemid))";
  63         sqlCreateNewEntryInItemsTable = "INSERT INTO items (itemname) SELECT itemname FROM #itemsManageTable# UNION VALUES ('#itemname#') EXCEPT SELECT itemname FROM items";
  64         sqlGetItemTables = "SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema=(SELECT table_schema "
  65                 + "FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_name='#itemsManageTable#') AND NOT table_name='#itemsManageTable#'";
  66         // http://stackoverflow.com/questions/17267417/how-do-i-do-an-upsert-merge-insert-on-duplicate-update-in-postgresql
  67         // for later use, PostgreSql > 9.5 to prevent PRIMARY key violation use:
  68         // SQL_INSERT_ITEM_VALUE = "INSERT INTO #tableName# (TIME, VALUE) VALUES( NOW(), CAST( ? as #dbType#) ) ON
  69         // CONFLICT DO NOTHING";
  70         sqlInsertItemValue = "INSERT INTO #tableName# (TIME, VALUE) VALUES( #tablePrimaryValue#, CAST( ? as #dbType#) )";
  71     }
  72
  73     /**
  74      * INFO: http://www.java2s.com/Code/Java/Database-SQL-JDBC/StandardSQLDataTypeswithTheirJavaEquivalents.htm
  75      */
  76     private void initSqlTypes() {
  77         // Initialize the type array
  78         sqlTypes.put("CALLITEM", "VARCHAR");
  79         sqlTypes.put("COLORITEM", "VARCHAR");
  80         sqlTypes.put("CONTACTITEM", "VARCHAR");
  81         sqlTypes.put("DATETIMEITEM", "TIMESTAMP");
  82         sqlTypes.put("DIMMERITEM", "SMALLINT");
  83         sqlTypes.put("IMAGEITEM", "VARCHAR");
  84         sqlTypes.put("LOCATIONITEM", "VARCHAR");
  85         sqlTypes.put("NUMBERITEM", "DOUBLE PRECISION");
  86         sqlTypes.put("PLAYERITEM", "VARCHAR");
  87         sqlTypes.put("ROLLERSHUTTERITEM", "SMALLINT");
  88         sqlTypes.put("STRINGITEM", "VARCHAR");
  89         sqlTypes.put("SWITCHITEM", "VARCHAR");
  90         logger.debug("JDBC::initSqlTypes: Initialized the type array sqlTypes={}", sqlTypes.values());
  91     }
  92
  93     /**
  94      * INFO: https://github.com/brettwooldridge/HikariCP
  95      */
  96     private void initDbProps() {
  97         // Performance:
  98         // databaseProps.setProperty("dataSource.cachePrepStmts", "true");
  99         // databaseProps.setProperty("dataSource.prepStmtCacheSize", "250");
 100         // databaseProps.setProperty("dataSource.prepStmtCacheSqlLimit", "2048");
 101
 102         // Properties for HikariCP
 103         databaseProps.setProperty("driverClassName", DRIVER_CLASS_NAME);
 104         // driverClassName OR BETTER USE dataSourceClassName
 105         // databaseProps.setProperty("dataSourceClassName", DATA_SOURCE_CLASS_NAME);
 106         // databaseProps.setProperty("maximumPoolSize", "3");
 107         // databaseProps.setProperty("minimumIdle", "2");
 108     }
 109
 110     /**************
 111      * ITEMS DAOs *
 112      **************/
 113     @Override
 114     public ItemsVO doCreateItemsTableIfNot(ItemsVO vo) throws JdbcSQLException {
 115         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateItemsTableIfNot,
 116                 new String[] { "#itemsManageTable#", "#colname#", "#coltype#", "#itemsManageTable#" },
 117                 new String[] { vo.getItemsManageTable(), vo.getColname(), vo.getColtype(), vo.getItemsManageTable() });
 118         logger.debug("JDBC::doCreateItemsTableIfNot sql={}", sql);
 119         try {
 120             Yank.execute(sql, null);
 121         } catch (YankSQLException e) {
 122             throw new JdbcSQLException(e);
 123         }
 124         return vo;
 125     }
 126
 127     @Override
 128     public Long doCreateNewEntryInItemsTable(ItemsVO vo) throws JdbcSQLException {
 129         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateNewEntryInItemsTable,
 130                 new String[] { "#itemsManageTable#", "#itemname#" },
 131                 new String[] { vo.getItemsManageTable(), vo.getItemName() });
 132         logger.debug("JDBC::doCreateNewEntryInItemsTable sql={}", sql);
 133         try {
 134             return Yank.insert(sql, null);
 135         } catch (YankSQLException e) {
 136             throw new JdbcSQLException(e);
 137         }
 138     }
 139
 140     @Override
 141     public List<ItemsVO> doGetItemTables(ItemsVO vo) throws JdbcSQLException {
 142         String sql = StringUtilsExt.replaceArrayMerge(this.sqlGetItemTables,
 143                 new String[] { "#itemsManageTable#", "#itemsManageTable#" },
 144                 new String[] { vo.getItemsManageTable(), vo.getItemsManageTable() });
 145         this.logger.debug("JDBC::doGetItemTables sql={}", sql);
 146         try {
 147             return Yank.queryBeanList(sql, ItemsVO.class, null);
 148         } catch (YankSQLException e) {
 149             throw new JdbcSQLException(e);
 150         }
 151     }
 152
 153     /*************
 154      * ITEM DAOs *
 155      *************/
 156     @Override
 157     public void doStoreItemValue(Item item, State itemState, ItemVO vo) throws JdbcSQLException {
 158         ItemVO storedVO = storeItemValueProvider(item, itemState, vo);
 159         String sql = StringUtilsExt.replaceArrayMerge(sqlInsertItemValue,
 160                 new String[] { "#tableName#", "#dbType#", "#tablePrimaryValue#" },
 161                 new String[] { storedVO.getTableName(), storedVO.getDbType(), sqlTypes.get("tablePrimaryValue") });
 162         Object[] params = { storedVO.getValue() };
 163         logger.debug("JDBC::doStoreItemValue sql={} value='{}'", sql, storedVO.getValue());
 164         try {
 165             Yank.execute(sql, params);
 166         } catch (YankSQLException e) {
 167             throw new JdbcSQLException(e);
 168         }
 169     }
 170
 171     @Override
 172     public void doStoreItemValue(Item item, State itemState, ItemVO vo, ZonedDateTime date) throws JdbcSQLException {
 173         ItemVO storedVO = storeItemValueProvider(item, itemState, vo);
 174         String sql = StringUtilsExt.replaceArrayMerge(sqlInsertItemValue,
 175                 new String[] { "#tableName#", "#dbType#", "#tablePrimaryValue#" },
 176                 new String[] { storedVO.getTableName(), storedVO.getDbType(), "?" });
 177         java.sql.Timestamp timestamp = new java.sql.Timestamp(date.toInstant().toEpochMilli());
 178         Object[] params = { timestamp, storedVO.getValue() };
 179         logger.debug("JDBC::doStoreItemValue sql={} timestamp={} value='{}'", sql, timestamp, storedVO.getValue());
 180         try {
 181             Yank.execute(sql, params);
 182         } catch (YankSQLException e) {
 183             throw new JdbcSQLException(e);
 184         }
 185     }
 186
 187     /****************************
 188      * SQL generation Providers *
 189      ****************************/
 190
 191     @Override
 192     protected String histItemFilterQueryProvider(FilterCriteria filter, int numberDecimalcount, String table,
 193             String simpleName, ZoneId timeZone) {
 194         logger.debug(
 195                 "JDBC::getHistItemFilterQueryProvider filter = {}, numberDecimalcount = {}, table = {}, simpleName = {}",
 196                 filter.toString(), numberDecimalcount, table, simpleName);
 197
 198         String filterString = "";
 199         if (filter.getBeginDate() != null) {
 200             filterString += filterString.isEmpty() ? " WHERE" : " AND";
 201             filterString += " TIME>'" + JDBC_DATE_FORMAT.format(filter.getBeginDate().withZoneSameInstant(timeZone))
 202                     + "'";
 203         }
 204         if (filter.getEndDate() != null) {
 205             filterString += filterString.isEmpty() ? " WHERE" : " AND";
 206             filterString += " TIME<'" + JDBC_DATE_FORMAT.format(filter.getEndDate().withZoneSameInstant(timeZone))
 207                     + "'";
 208         }
 209         filterString += (filter.getOrdering() == Ordering.ASCENDING) ? " ORDER BY time ASC" : " ORDER BY time DESC";
 210         if (filter.getPageSize() != 0x7fffffff) {
 211             // see:
 212             // http://www.jooq.org/doc/3.5/manual/sql-building/sql-statements/select-statement/limit-clause/
 213             filterString += " OFFSET " + filter.getPageNumber() * filter.getPageSize() + " LIMIT "
 214                     + filter.getPageSize();
 215         }
 216         String queryString = "NUMBERITEM".equalsIgnoreCase(simpleName) && numberDecimalcount > -1
 217                 ? "SELECT time, ROUND(CAST (value AS numeric)," + numberDecimalcount + ") FROM " + table
 218                 : "SELECT time, value FROM " + table;
 219         if (!filterString.isEmpty()) {
 220             queryString += filterString;
 221         }
 222         logger.debug("JDBC::query queryString = {}", queryString);
 223         return queryString;
 224     }
 225
 226     /*****************
 227      * H E L P E R S *
 228      *****************/
 229
 230     /******************************
 231      * public Getters and Setters *
 232      ******************************/
 233 }