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