JdbcMapper.java

/**
 * Copyright (c) 2010-2016 by the respective copyright holders.
 *
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the Eclipse Public License v1.0
 * which accompanies this distribution, and is available at
 * http://www.eclipse.org/legal/epl-v10.html
 */
package org.openhab.persistence.jdbc.internal;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.knowm.yank.Yank;
import org.openhab.core.items.Item;
import org.openhab.core.persistence.FilterCriteria;
import org.openhab.core.persistence.HistoricItem;
import org.openhab.persistence.jdbc.model.ItemVO;
import org.openhab.persistence.jdbc.model.ItemsVO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Helmut Lehmeyer
 * @since 1.8.0
 */
public class JdbcMapper {
    static final Logger logger = LoggerFactory.getLogger(JdbcMapper.class);

    // Error counter - used to reconnect to database on error
    protected int errCnt;
    protected boolean initialized = false;
    protected JdbcConfiguration conf = null;
    protected Map<String, String> sqlTables = new HashMap<String, String>();
    private long afterAccessMin = 10000;
    private long afterAccessMax = 0;
    private static final String ITEM_NAME_PATTERN = "[^a-zA-Z_0-9\\-]";

    /*****************
     * MAPPER ITEMS *
     *****************/
    public boolean pingDB() {
        logger.debug("JDBC::pingDB");
        boolean ret = false;
        long timerStart = System.currentTimeMillis();
        if (openConnection()) {
            if (conf.getDbName() == null) {
                logger.debug(
                        "JDBC::pingDB asking db for name as absolutely first db action, after connection is established.");
                String dbName = conf.getDBDAO().doGetDB();
                conf.setDbName(dbName);
                ret = dbName.length() > 0;
            } else {
                ret = conf.getDBDAO().doPingDB() > 0;
            }
        }
        logTime("pingDB", timerStart, System.currentTimeMillis());
        return ret;
    }

    public String getDB() {
        logger.debug("JDBC::getDB");
        long timerStart = System.currentTimeMillis();
        String res = conf.getDBDAO().doGetDB();
        logTime("pingDB", timerStart, System.currentTimeMillis());
        return res;
    }

    public ItemsVO createNewEntryInItemsTable(ItemsVO vo) {
        logger.debug("JDBC::createNewEntryInItemsTable");
        long timerStart = System.currentTimeMillis();
        Long i = conf.getDBDAO().doCreateNewEntryInItemsTable(vo);
        vo.setItemid(i.intValue());
        logTime("doCreateNewEntryInItemsTable", timerStart, System.currentTimeMillis());
        return vo;
    }

    public boolean createItemsTableIfNot(ItemsVO vo) {
        logger.debug("JDBC::createItemsTableIfNot");
        long timerStart = System.currentTimeMillis();
        conf.getDBDAO().doCreateItemsTableIfNot(vo);
        logTime("doCreateItemsTableIfNot", timerStart, System.currentTimeMillis());
        return true;
    }

    public ItemsVO deleteItemsEntry(ItemsVO vo) {
        logger.debug("JDBC::deleteItemsEntry");
        long timerStart = System.currentTimeMillis();
        conf.getDBDAO().doDeleteItemsEntry(vo);
        logTime("deleteItemsEntry", timerStart, System.currentTimeMillis());
        return vo;
    }

    public List<ItemsVO> getItemIDTableNames() {
        logger.debug("JDBC::getItemIDTableNames");
        long timerStart = System.currentTimeMillis();
        List<ItemsVO> vo = conf.getDBDAO().doGetItemIDTableNames(new ItemsVO());
        logTime("getItemIDTableNames", timerStart, System.currentTimeMillis());
        return vo;
    }

    public List<ItemsVO> getItemTables() {
        logger.debug("JDBC::getItemTables");
        long timerStart = System.currentTimeMillis();
        ItemsVO vo = new ItemsVO();
        vo.setJdbcUriDatabaseName(conf.getDbName());
        List<ItemsVO> vol = conf.getDBDAO().doGetItemTables(vo);
        logTime("getItemTables", timerStart, System.currentTimeMillis());
        return vol;
    }

    /****************
     * MAPPERS ITEM *
     ****************/
    public void updateItemTableNames(List<ItemVO> vol) {
        logger.debug("JDBC::createItemTable");
        long timerStart = System.currentTimeMillis();
        conf.getDBDAO().doUpdateItemTableNames(vol);
        logTime("createItemTable", timerStart, System.currentTimeMillis());
    }

    public ItemVO createItemTable(ItemVO vo) {
        logger.debug("JDBC::createItemTable");
        long timerStart = System.currentTimeMillis();
        conf.getDBDAO().doCreateItemTable(vo);
        logTime("createItemTable", timerStart, System.currentTimeMillis());
        return vo;
    }

    public Item storeItemValue(Item item) {
        logger.debug("JDBC::storeItemValue: item={}", item.toString());
        String tableName = getTable(item);
        if (tableName == null) {
            logger.error("JDBC::store: Unable to store item '{}'.", item.getName());
            return item;
        }
        long timerStart = System.currentTimeMillis();
        conf.getDBDAO().doStoreItemValue(item, new ItemVO(tableName, null));
        logTime("storeItemValue", timerStart, System.currentTimeMillis());
        errCnt = 0;
        return item;
    }

    public List<HistoricItem> getHistItemFilterQuery(FilterCriteria filter, int numberDecimalcount, String table,
            Item item) {
        logger.debug(
                "JDBC::getHistItemFilterQuery filter='{}' numberDecimalcount='{}' table='{}' item='{}' itemName='{}'",
                (filter != null), numberDecimalcount, table, item, item.getName());
        if (table != null) {
            long timerStart = System.currentTimeMillis();
            List<HistoricItem> r = conf.getDBDAO().doGetHistItemFilterQuery(item, filter, numberDecimalcount, table,
                    item.getName());
            logTime("insertItemValue", timerStart, System.currentTimeMillis());
            return r;
        } else {
            logger.error("JDBC::getHistItemFilterQuery: TABLE is NULL; cannot get data from non-existent table.");
        }
        return null;
    }

    /***********************
     * DATABASE CONNECTION *
     ***********************/
    protected boolean openConnection() {
        logger.debug("JDBC::openConnection isDriverAvailable: {}", conf.isDriverAvailable());
        if (conf.isDriverAvailable() && !conf.isDbConnected()) {
            logger.info("JDBC::openConnection: Driver is available::Yank setupDataSource");
            Yank.setupDefaultConnectionPool(conf.getHikariConfiguration());
            conf.setDbConnected(true);
            return true;
        } else if (!conf.isDriverAvailable()) {
            logger.warn("JDBC::openConnection: no driver available!");
            initialized = false;
            return false;
        }
        return true;
    }

    protected void closeConnection() {
        logger.debug("JDBC::closeConnection");
        // Closes all open connection pools
        Yank.releaseDefaultConnectionPool();
        conf.setDbConnected(false);
    }

    protected boolean checkDBAccessability() {
        // Check if connection is valid
        if (initialized) {
            return true;
        }
        // first
        boolean p = pingDB();
        if (p) {
            logger.debug("JDBC::checkDBAcessability, first try connection: {}", p);
            return (p && !(conf.getErrReconnectThreshold() > 0 && errCnt <= conf.getErrReconnectThreshold()));
        } else {
            // second
            p = pingDB();
            logger.debug("JDBC::checkDBAcessability, second try connection: {}", p);
            return (p && !(conf.getErrReconnectThreshold() > 0 && errCnt <= conf.getErrReconnectThreshold()));
        }
    }

    /**************************
     * DATABASE TABLEHANDLING *
     **************************/
    protected void checkDBSchema() {
        // Create Items Table if does not exist
        createItemsTableIfNot(new ItemsVO());
        if (conf.getRebuildTableNames()) {
            formatTableNames();
        } else {
            List<ItemsVO> al;
            // Reset the error counter
            errCnt = 0;
            al = getItemIDTableNames();
            for (int i = 0; i < al.size(); i++) {
                String t = getTableName(al.get(i).getItemid(), al.get(i).getItemname());
                sqlTables.put(al.get(i).getItemname(), t);
            }
        }
    }

    protected String getTable(Item item) {
        int rowId = 0;
        ItemsVO isvo;
        ItemVO ivo;

        String itemName = item.getName();
        String tableName = sqlTables.get(itemName);

        // Table already exists - return the name
        if (tableName != null) {
            return tableName;
        }

        logger.debug("JDBC::getTable: no table found for item '{}' in sqlTables", itemName);

        // Create a new entry in items table
        isvo = new ItemsVO();
        isvo.setItemname(itemName);
        isvo = createNewEntryInItemsTable(isvo);
        rowId = isvo.getItemid();
        if (rowId == 0) {
            logger.error("JDBC::getTable: Creating table for item '{}' failed.", itemName);
        }
        // Create the table name
        logger.debug("JDBC::getTable: getTableName with rowId={} itemName={}", rowId, itemName);
        tableName = getTableName(rowId, itemName);

        // An error occurred adding the item name into the index list!
        if (tableName == null) {
            logger.error("JDBC::getTable: tableName was null; could not create a table for item '{}'", itemName);
            return null;
        }

        // Create table for item
        String dataType = conf.getDBDAO().getDataType(item);
        ivo = new ItemVO(tableName, itemName);
        ivo.setDbType(dataType);
        ivo = createItemTable(ivo);
        logger.debug("JDBC::getTable: Table created for item '{}' with dataType {} in SQL database.", itemName,
                dataType);
        sqlTables.put(itemName, tableName);

        // Check if the new entry is in the table list
        // If it's not in the list, then there was an error and we need to do
        // some tidying up
        // The item needs to be removed from the index table to avoid duplicates
        if (sqlTables.get(itemName) == null) {
            logger.error("JDBC::getTable: Item '{}' was not added to the table - removing index", itemName);
            isvo = new ItemsVO();
            isvo.setItemname(itemName);
            deleteItemsEntry(isvo);
        }

        return tableName;
    }

    private void formatTableNames() {

        boolean tmpinit = initialized;
        if (tmpinit) {
            initialized = false;
        }

        List<ItemsVO> al;
        HashMap<Integer, String> tableIds = new HashMap<Integer, String>();

        //
        al = getItemIDTableNames();
        for (int i = 0; i < al.size(); i++) {
            String t = getTableName(al.get(i).getItemid(), al.get(i).getItemname());
            sqlTables.put(al.get(i).getItemname(), t);
            tableIds.put(al.get(i).getItemid(), t);
        }

        //
        al = getItemTables();

        String oldName = "";
        String newName = "";
        List<ItemVO> oldNewTablenames = new ArrayList<ItemVO>();
        for (int i = 0; i < al.size(); i++) {

            int id = -1;
            oldName = al.get(i).getTable_name();
            logger.warn("JDBC::formatTableNames: found Table Name= {}", oldName);

            if (oldName.startsWith(conf.getTableNamePrefix()) && !oldName.contains("_")) {
                id = Integer.parseInt(oldName.substring(conf.getTableNamePrefix().length()));
                logger.warn("JDBC::formatTableNames: found Table with Prefix '{}' Name= {} id= {}",
                        conf.getTableNamePrefix(), oldName, (id));
            } else if (oldName.contains("_")) {
                id = Integer.parseInt(oldName.substring(oldName.lastIndexOf("_") + 1));
                logger.warn("JDBC::formatTableNames: found Table Name= {} id= {}", oldName, (id));
            }
            logger.warn("JDBC::formatTableNames: found Table id= {}", id);

            newName = tableIds.get(id);
            logger.warn("JDBC::formatTableNames: found Table newName= {}", newName);

            if (newName != null) {
                if (!oldName.equalsIgnoreCase(newName)) {
                    oldNewTablenames.add(new ItemVO(oldName, newName));
                    logger.warn("JDBC::formatTableNames: Table '{}' will be renamed to '{}'", oldName, newName);
                } else {
                    logger.warn("JDBC::formatTableNames: Table oldName='{}' newName='{}' nothing to rename", oldName,
                            newName);
                }
            } else {
                logger.error("JDBC::formatTableNames: Table '{}' could NOT be renamed to '{}'", oldName, newName);
                break;
            }
        }

        //
        updateItemTableNames(oldNewTablenames);
        initialized = tmpinit;
    }

    private String getTableName(int rowId, String itemName) {
        return getTableNamePrefix(itemName) + formatRight(rowId, conf.getTableIdDigitCount());
    }

    private String getTableNamePrefix(String itemName) {
        String name = conf.getTableNamePrefix();
        if (conf.getTableUseRealItemNames()) {
            // Create the table name with real Item Names
            name = (itemName.replaceAll(ITEM_NAME_PATTERN, "") + "_").toLowerCase();
        }
        return name;
    }

    private static String formatRight(final Object value, final int len) {
        final String valueAsString = String.valueOf(value);
        if (valueAsString.length() < len) {
            final StringBuffer result = new StringBuffer(len);
            for (int i = len - valueAsString.length(); i > 0; i--) {
                result.append('0');
            }
            result.append(valueAsString);
            return result.toString();
        } else {
            return valueAsString;
        }
    }

    /*****************
     * H E L P E R S *
     *****************/
    private void logTime(String me, long timerStart, long timerStop) {
        if (conf.enableLogTime) {
            conf.timerCount++;
            int timerDiff = (int) (timerStop - timerStart);
            if (timerDiff < afterAccessMin) {
                afterAccessMin = timerDiff;
            }
            if (timerDiff > afterAccessMax) {
                afterAccessMax = timerDiff;
            }
            conf.timeAverage50arr.add(timerDiff);
            conf.timeAverage100arr.add(timerDiff);
            conf.timeAverage200arr.add(timerDiff);
            if (conf.timerCount == 1) {
                conf.timer1000 = System.currentTimeMillis();
            }
            if (conf.timerCount == 1001) {
                conf.time1000Statements = Math.round(((int) (System.currentTimeMillis() - conf.timer1000)) / 1000);// Seconds
                conf.timerCount = 0;
            }
            logger.info(
                    "JDBC::logTime: '{}':\n afterAccess     = {} ms\n timeAverage50  = {} ms\n timeAverage100 = {} ms\n timeAverage200 = {} ms\n afterAccessMin  = {} ms\n afterAccessMax  = {} ms\n 1000Statements = {} sec\n statementCount = {}\n",
                    me, timerDiff, conf.timeAverage50arr.getAverageInteger(),
                    conf.timeAverage100arr.getAverageInteger(), conf.timeAverage200arr.getAverageInteger(),
                    afterAccessMin, afterAccessMax, conf.time1000Statements, conf.timerCount);
        }
    }

}