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

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