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 JdbcPostgresqlDAO extends JdbcBaseDAO {
  39     private final Logger logger = LoggerFactory.getLogger(JdbcPostgresqlDAO.class);
  40
  41     /********
  42      * INIT *
  43      ********/
  44     public JdbcPostgresqlDAO() {
  45         super();
  46         initSqlQueries();
  47         initSqlTypes();
  48         initDbProps();
  49     }
  50
  51     private void initSqlQueries() {
  52         logger.debug("JDBC::initSqlQueries: '{}'", this.getClass().getSimpleName());
  53         // System Information Functions: https://www.postgresql.org/docs/9.2/static/functions-info.html
  54         sqlGetDB = "SELECT CURRENT_DATABASE()";
  55         sqlIfTableExists = "SELECT * FROM PG_TABLES WHERE TABLENAME='#searchTable#'";
  56         sqlCreateItemsTableIfNot = "CREATE TABLE IF NOT EXISTS #itemsManageTable# (itemid SERIAL NOT NULL, #colname# #coltype# NOT NULL, CONSTRAINT #itemsManageTable#_pkey PRIMARY KEY (itemid))";
  57         sqlCreateNewEntryInItemsTable = "INSERT INTO items (itemname) SELECT itemname FROM #itemsManageTable# UNION VALUES ('#itemname#') EXCEPT SELECT itemname FROM items";
  58         sqlGetItemTables = "SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='public' AND NOT table_name='#itemsManageTable#'";
  59         // http://stackoverflow.com/questions/17267417/how-do-i-do-an-upsert-merge-insert-on-duplicate-update-in-postgresql
  60         // for later use, PostgreSql > 9.5 to prevent PRIMARY key violation use:
  61         // SQL_INSERT_ITEM_VALUE = "INSERT INTO #tableName# (TIME, VALUE) VALUES( NOW(), CAST( ? as #dbType#) ) ON
  62         // CONFLICT DO NOTHING";
  63         sqlInsertItemValue = "INSERT INTO #tableName# (TIME, VALUE) VALUES( #tablePrimaryValue#, CAST( ? as #dbType#) )";
  64     }
  65
  66     /**
  67      * INFO: http://www.java2s.com/Code/Java/Database-SQL-JDBC/StandardSQLDataTypeswithTheirJavaEquivalents.htm
  68      */
  69     private void initSqlTypes() {
  70         // Initialize the type array
  71         sqlTypes.put("CALLITEM", "VARCHAR");
  72         sqlTypes.put("COLORITEM", "VARCHAR");
  73         sqlTypes.put("CONTACTITEM", "VARCHAR");
  74         sqlTypes.put("DATETIMEITEM", "TIMESTAMP");
  75         sqlTypes.put("DIMMERITEM", "SMALLINT");
  76         sqlTypes.put("IMAGEITEM", "VARCHAR");
  77         sqlTypes.put("LOCATIONITEM", "VARCHAR");
  78         sqlTypes.put("NUMBERITEM", "DOUBLE PRECISION");
  79         sqlTypes.put("PLAYERITEM", "VARCHAR");
  80         sqlTypes.put("ROLLERSHUTTERITEM", "SMALLINT");
  81         sqlTypes.put("STRINGITEM", "VARCHAR");
  82         sqlTypes.put("SWITCHITEM", "VARCHAR");
  83         logger.debug("JDBC::initSqlTypes: Initialized the type array sqlTypes={}", sqlTypes.values());
  84     }
  85
  86     /**
  87      * INFO: https://github.com/brettwooldridge/HikariCP
  88      */
  89     private void initDbProps() {
  90         // Performance:
  91         // databaseProps.setProperty("dataSource.cachePrepStmts", "true");
  92         // databaseProps.setProperty("dataSource.prepStmtCacheSize", "250");
  93         // databaseProps.setProperty("dataSource.prepStmtCacheSqlLimit", "2048");
  94
  95         // Properties for HikariCP
  96         databaseProps.setProperty("driverClassName", "org.postgresql.Driver");
  97         // driverClassName OR BETTER USE dataSourceClassName
  98         // databaseProps.setProperty("dataSourceClassName", "org.postgresql.ds.PGSimpleDataSource");
  99         // databaseProps.setProperty("maximumPoolSize", "3");
 100         // databaseProps.setProperty("minimumIdle", "2");
 101     }
 102
 103     /**************
 104      * ITEMS DAOs *
 105      **************/
 106     @Override
 107     public ItemsVO doCreateItemsTableIfNot(ItemsVO vo) {
 108         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateItemsTableIfNot,
 109                 new String[] { "#itemsManageTable#", "#colname#", "#coltype#", "#itemsManageTable#" },
 110                 new String[] { vo.getItemsManageTable(), vo.getColname(), vo.getColtype(), vo.getItemsManageTable() });
 111         logger.debug("JDBC::doCreateItemsTableIfNot sql={}", sql);
 112         Yank.execute(sql, null);
 113         return vo;
 114     }
 115
 116     @Override
 117     public Long doCreateNewEntryInItemsTable(ItemsVO vo) {
 118         String sql = StringUtilsExt.replaceArrayMerge(sqlCreateNewEntryInItemsTable,
 119                 new String[] { "#itemsManageTable#", "#itemname#" },
 120                 new String[] { vo.getItemsManageTable(), vo.getItemname() });
 121         logger.debug("JDBC::doCreateNewEntryInItemsTable sql={}", sql);
 122         return Yank.insert(sql, null);
 123     }
 124
 125     @Override
 126     public List<ItemsVO> doGetItemTables(ItemsVO vo) {
 127         String sql = StringUtilsExt.replaceArrayMerge(sqlGetItemTables, new String[] { "#itemsManageTable#" },
 128                 new String[] { vo.getItemsManageTable() });
 129         logger.debug("JDBC::doGetItemTables sql={}", sql);
 130         return Yank.queryBeanList(sql, ItemsVO.class, null);
 131     }
 132
 133     /*************
 134      * ITEM DAOs *
 135      *************/
 136     @Override
 137     public void doStoreItemValue(Item item, ItemVO vo) {
 138         vo = storeItemValueProvider(item, vo);
 139         String sql = StringUtilsExt.replaceArrayMerge(sqlInsertItemValue,
 140                 new String[] { "#tableName#", "#dbType#", "#tablePrimaryValue#" },
 141                 new String[] { vo.getTableName(), vo.getDbType(), sqlTypes.get("tablePrimaryValue") });
 142         Object[] params = new Object[] { vo.getValue() };
 143         logger.debug("JDBC::doStoreItemValue sql={} value='{}'", sql, vo.getValue());
 144         Yank.execute(sql, params);
 145     }
 146
 147     @Override
 148     public List<HistoricItem> doGetHistItemFilterQuery(Item item, FilterCriteria filter, int numberDecimalcount,
 149             String table, String name) {
 150         String sql = histItemFilterQueryProvider(filter, numberDecimalcount, table, name);
 151         logger.debug("JDBC::doGetHistItemFilterQuery sql={}", sql);
 152         List<Object[]> m = Yank.queryObjectArrays(sql, null);
 153
 154         List<HistoricItem> items = new ArrayList<>();
 155         for (int i = 0; i < m.size(); i++) {
 156             items.add(new JdbcHistoricItem(item.getName(), getState(item, m.get(i)[1]), objectAsDate(m.get(i)[0])));
 157         }
 158         return items;
 159     }
 160
 161     /****************************
 162      * SQL generation Providers *
 163      ****************************/
 164     static final DateTimeFormatter JDBC_DATE_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
 165
 166     /**
 167      * @param filter
 168      * @param numberDecimalcount
 169      * @param table
 170      * @return
 171      */
 172     private String histItemFilterQueryProvider(FilterCriteria filter, int numberDecimalcount, String table,
 173             String simpleName) {
 174         logger.debug(
 175                 "JDBC::getHistItemFilterQueryProvider filter = {}, numberDecimalcount = {}, table = {}, simpleName = {}",
 176                 filter.toString(), numberDecimalcount, table, simpleName);
 177
 178         String filterString = "";
 179         if (filter.getBeginDate() != null) {
 180             filterString += filterString.isEmpty() ? " WHERE" : " AND";
 181             filterString += " TIME>'" + JDBC_DATE_FORMAT.format(filter.getBeginDate()) + "'";
 182         }
 183         if (filter.getEndDate() != null) {
 184             filterString += filterString.isEmpty() ? " WHERE" : " AND";
 185             filterString += " TIME<'" + JDBC_DATE_FORMAT.format(filter.getEndDate()) + "'";
 186         }
 187         filterString += (filter.getOrdering() == Ordering.ASCENDING) ? " ORDER BY time ASC" : " ORDER BY time DESC";
 188         if (filter.getPageSize() != 0x7fffffff) {
 189             // see:
 190             // http://www.jooq.org/doc/3.5/manual/sql-building/sql-statements/select-statement/limit-clause/
 191             filterString += " OFFSET " + filter.getPageNumber() * filter.getPageSize() + " LIMIT "
 192                     + filter.getPageSize();
 193         }
 194         String queryString = "NUMBERITEM".equalsIgnoreCase(simpleName) && numberDecimalcount > -1
 195                 ? "SELECT time, ROUND(CAST (value AS numeric)," + numberDecimalcount + ") FROM " + table
 196                 : "SELECT time, value FROM " + table;
 197         if (!filterString.isEmpty()) {
 198             queryString += filterString;
 199         }
 200         logger.debug("JDBC::query queryString = {}", queryString);
 201         return queryString;
 202     }
 203
 204     /*****************
 205      * H E L P E R S *
 206      *****************/
 207
 208     /******************************
 209      * public Getters and Setters *
 210      ******************************/
 211 }