org.openhab.persistence.sql.internal.SqlPersistenceService.java Source code

Java tutorial

Introduction

Here is the source code for org.openhab.persistence.sql.internal.SqlPersistenceService.java

Source

/**
 * openHAB, the open Home Automation Bus.
 * Copyright (C) 2010-2013, openHAB.org <admin@openhab.org>
 *
 * See the contributors.txt file in the distribution for a
 * full listing of individual contributors.
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as
 * published by the Free Software Foundation; either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, see <http://www.gnu.org/licenses>.
 *
 * Additional permission under GNU GPL version 3 section 7
 *
 * If you modify this Program, or any covered work, by linking or
 * combining it with Eclipse (or a modified version of that library),
 * containing parts covered by the terms of the Eclipse Public License
 * (EPL), the licensors of this Program grant you additional permission
 * to convey the resulting work.
 */
package org.openhab.persistence.sql.internal;

import java.text.SimpleDateFormat;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Dictionary;
import java.util.Enumeration;
import java.util.Formatter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.openhab.core.items.Item;
import org.openhab.core.items.ItemRegistry;
import org.openhab.core.library.types.DecimalType;
import org.openhab.core.persistence.FilterCriteria;
import org.openhab.core.persistence.HistoricItem;
import org.openhab.core.persistence.PersistenceService;
import org.openhab.core.persistence.QueryablePersistenceService;
import org.openhab.core.persistence.FilterCriteria.Ordering;
import org.openhab.core.types.State;
import org.osgi.service.cm.ConfigurationException;
import org.osgi.service.cm.ManagedService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * This is the implementation of the SQL {@link PersistenceService}.
 * 
 * @author Henrik Sjstrand
 * @author Thomas.Eichstaedt-Engelen
 * @author Chris Jackson
 * @since 1.1.0
 */
public class SqlPersistenceService implements QueryablePersistenceService, ManagedService {

    private static final Pattern EXTRACT_CONFIG_PATTERN = Pattern.compile("^(.*?)\\.([0-9.a-zA-Z]+)$");

    private static final Logger logger = LoggerFactory.getLogger(SqlPersistenceService.class);

    private String driverClass;
    private String url;
    private String user;
    private String password;

    private boolean initialized = false;
    protected ItemRegistry itemRegistry;

    // Error counter - used to reconnect to database on error
    private int errCnt;
    private int errReconnectThreshold = 0;

    private Connection connection = null;

    private Map<String, String> sqlTables = new HashMap<String, String>();
    private Map<String, String> sqlTypes = new HashMap<String, String>();

    public void activate() {
        // Initialise the type array
        sqlTypes.put("COLORITEM", "VARCHAR(50)");
        sqlTypes.put("CONTACTITEM", "VARCHAR(50)");
        sqlTypes.put("DATETIMEITEM", "DATETIME");
        sqlTypes.put("DIMMERITEM", "DOUBLE");
        sqlTypes.put("GROUPITEM", "VARCHAR(50)");
        sqlTypes.put("NUMBERITEM", "DOUBLE");
        sqlTypes.put("ROLERSHUTTERITEM", "DOUBLE");
        sqlTypes.put("STRINGITEM", "VARCHAR(50)");
        sqlTypes.put("SWITCHITEM", "DOUBLE");
    }

    public void deactivate() {
        logger.debug("SQL persistence bundle stopping. Disconnecting from database.");
        disconnectFromDatabase();
    }

    public void setItemRegistry(ItemRegistry itemRegistry) {
        this.itemRegistry = itemRegistry;
    }

    public void unsetItemRegistry(ItemRegistry itemRegistry) {
        this.itemRegistry = null;
    }

    /**
     * @{inheritDoc
     */
    public String getName() {
        return "sql";
    }

    private String getTable(Item item) {
        Statement statement = null;
        String sqlCmd = null;
        int rowId = 0;

        String itemName = item.getName();

        String tableName = sqlTables.get(itemName);

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

        // Create a new entry in the Items table. This is the translation of
        // item name to table
        try {
            sqlCmd = new String("INSERT INTO Items (ItemName) VALUES ('" + itemName + "')");

            statement = connection.createStatement();
            statement.executeUpdate(sqlCmd, Statement.RETURN_GENERATED_KEYS);

            ResultSet resultSet = statement.getGeneratedKeys();
            if (resultSet != null && resultSet.next()) {
                rowId = resultSet.getInt(1);
            }

            if (rowId == 0) {
                throw new SQLException("SQL: Creating table for item '" + itemName + "' failed.");
            }

            // Create the table name
            tableName = new String("Item" + rowId);
            logger.debug("SQL: new item " + itemName + " is Item" + rowId);
        } catch (SQLException e) {
            logger.error("SQL: Could not create table for item '" + itemName + "': " + e.getMessage());
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException logOrIgnore) {
                }
            }
        }

        // An error occurred!
        if (tableName == null)
            return null;

        // Default the type to double
        String mysqlType = new String("DOUBLE");
        String itemType = item.getClass().toString().toUpperCase();
        itemType = itemType.substring(itemType.lastIndexOf('.') + 1);
        if (sqlTypes.get(itemType) != null) {
            mysqlType = sqlTypes.get(itemType);
        }

        // We have a rowId, create the table for the data
        sqlCmd = new String(
                "CREATE TABLE " + tableName + " (Time DATETIME, Value " + mysqlType + ", PRIMARY KEY(Time));");
        logger.debug("SQL: " + sqlCmd);

        try {
            statement = connection.createStatement();
            statement.executeUpdate(sqlCmd);

            logger.debug("SQL: Table created for item '" + itemName + "' with datatype " + mysqlType
                    + " in SQL database.");
            sqlTables.put(itemName, tableName);
        } catch (Exception e) {
            logger.error("SQL: Could not create table for item '" + itemName + "' with statement '" + sqlCmd + "': "
                    + e.getMessage());
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception hidden) {
                }
            }
        }

        return tableName;
    }

    /**
     * @{inheritDoc
     */
    public void store(Item item, String alias) {
        if (initialized) {

            if (!isConnected())
                connectToDatabase();

            if (isConnected()) {

                String tableName = getTable(item);
                if (tableName == null) {
                    logger.error("Unable to store item '{}'.", item.getName());
                    return;
                }

                String sqlCmd = null;
                Statement statement = null;
                try {
                    statement = connection.createStatement();
                    sqlCmd = new String("INSERT INTO " + tableName + " (TIME, VALUE) VALUES(NOW(),'"
                            + item.getState().toString() + "');");
                    statement.executeUpdate(sqlCmd);

                    logger.debug("SQL: Stored item '{}' as '{}'[{}] in SQL database at {}.", item.getName(),
                            item.getState().toString(), item.getState().toString(),
                            (new java.util.Date()).toString());
                    logger.debug("SQL: {}", sqlCmd);

                    // Success
                    errCnt = 0;
                } catch (Exception e) {
                    errCnt++;

                    logger.error("SQL: Could not store item '{}' in database with statement '{}': {}",
                            item.getName(), sqlCmd, e.getMessage());
                } finally {
                    if (statement != null) {
                        try {
                            statement.close();
                        } catch (Exception hidden) {
                        }
                    }
                }
            } else {
                logger.warn(
                        "SQL: No connection to database. Can not persist item '{}'! Will retry connecting to database next time.",
                        item);
            }
        }
    }

    /**
     * @{inheritDoc
     */
    public void store(Item item) {
        store(item, null);
    }

    /**
     * Checks if we have a database connection
     * 
     * @return true if connection has been established, false otherwise
     */
    private boolean isConnected() {
        // Error check. If we have 'errReconnectThreshold' errors in a row, then reconnect to the database
        if (errReconnectThreshold != 0 && errCnt > errReconnectThreshold) {
            logger.debug("SQL: Error count exceeded " + errReconnectThreshold + ". Disconnecting database.");
            disconnectFromDatabase();
        }
        return connection != null;
    }

    /**
     * Connects to the database
     */
    private void connectToDatabase() {
        try {
            // Reset the error counter
            errCnt = 0;

            logger.debug("SQL: Attempting to connect to database " + url);
            Class.forName(driverClass).newInstance();
            connection = DriverManager.getConnection(url, user, password);
            logger.debug("SQL: Connected to database " + url);

            Statement st = connection.createStatement();
            int result = st.executeUpdate("SHOW TABLES LIKE 'Items'");
            st.close();
            if (result == 0) {
                st = connection.createStatement();
                st.executeUpdate(
                        "CREATE TABLE Items (ItemId INT NOT NULL AUTO_INCREMENT,ItemName VARCHAR(200) NOT NULL,PRIMARY KEY (ItemId));",
                        Statement.RETURN_GENERATED_KEYS);
                st.close();
            }

            // Retrieve the table array
            st = connection.createStatement();

            // Turn use of the cursor on.
            st.setFetchSize(50);
            ResultSet rs = st.executeQuery("SELECT ItemId, ItemName FROM Items");
            while (rs.next()) {
                sqlTables.put(rs.getString(2), "Item" + rs.getInt(1));
            }
            rs.close();
            st.close();
        } catch (Exception e) {
            logger.error("SQL: Failed connecting to the SQL database using: driverClass=" + driverClass + ", url="
                    + url + ", user=" + user + ", password=" + password, e);
        }
    }

    /**
     * Disconnects from the database
     */
    private void disconnectFromDatabase() {
        if (isConnected()) {
            try {
                connection.close();
                logger.debug("SQL: Disconnected from database " + url);
            } catch (Exception e) {
                logger.warn("SQL: Failed disconnecting from the SQL database", e);
            }
            connection = null;
        }
    }

    /**
     * Formats the given <code>alias</code> by utilizing {@link Formatter}.
     * 
     * @param alias
     *            the alias String which contains format strings
     * @param values
     *            the values which will be replaced in the alias String
     * 
     * @return the formatted value. All format strings are replaced by
     *         appropriate values
     * @see java.util.Formatter for detailed information on format Strings.
     */
    protected String formatAlias(String alias, Object... values) {
        return String.format(alias, values);
    }

    /**
     * @{inheritDoc
     */
    public void updated(Dictionary<String, ?> config) throws ConfigurationException {
        if (config != null) {
            Enumeration<String> keys = config.keys();

            while (keys.hasMoreElements()) {
                String key = (String) keys.nextElement();

                Matcher matcher = EXTRACT_CONFIG_PATTERN.matcher(key);

                if (!matcher.matches()) {
                    continue;
                }

                matcher.reset();
                matcher.find();

                if (!matcher.group(1).equals("sqltype"))
                    continue;

                String itemType = matcher.group(2).toUpperCase() + "ITEM";
                String value = (String) config.get(key);

                sqlTypes.put(itemType, value);
            }

            driverClass = (String) config.get("driverClass");
            if (StringUtils.isBlank(driverClass)) {
                throw new ConfigurationException("sql:driverClass",
                        "The SQL driver class is missing - please configure the sql:driverClass parameter in openhab.cfg");
            }

            url = (String) config.get("url");
            if (StringUtils.isBlank(url)) {
                throw new ConfigurationException("sql:url",
                        "The SQL database URL is missing - please configure the sql:url parameter in openhab.cfg");
            }

            user = (String) config.get("user");
            if (StringUtils.isBlank(user)) {
                throw new ConfigurationException("sql:user",
                        "The SQL user is missing - please configure the sql:user parameter in openhab.cfg");
            }

            password = (String) config.get("password");
            if (StringUtils.isBlank(password)) {
                throw new ConfigurationException("sql:password",
                        "The SQL password is missing. Attempting to connect without password. To specify a password configure the sql:password parameter in openhab.cfg.");
            }

            String errorThresholdString = (String) config.get("reconnectCnt");
            if (StringUtils.isNotBlank(errorThresholdString)) {
                errReconnectThreshold = Integer.parseInt(errorThresholdString);
            }

            disconnectFromDatabase();
            connectToDatabase();

            // connection has been established ... initialization completed!
            initialized = true;
        }

    }

    @Override
    public Iterable<HistoricItem> query(FilterCriteria filter) {
        if (initialized) {
            if (!isConnected()) {
                connectToDatabase();
            }

            SimpleDateFormat mysqlDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            if (isConnected()) {
                String itemName = filter.getItemName();

                String table = sqlTables.get(itemName);
                if (table == null) {
                    logger.error("SQL: Unable to find table for query '" + itemName + "'.");
                    return Collections.emptyList();
                }

                String filterString = new String();

                if (filter.getBeginDate() != null) {
                    if (filterString.isEmpty())
                        filterString += " WHERE";
                    else
                        filterString += " AND";
                    filterString += " TIME>'" + mysqlDateFormat.format(filter.getBeginDate()) + "'";
                }
                if (filter.getEndDate() != null) {
                    if (filterString.isEmpty())
                        filterString += " WHERE";
                    else
                        filterString += " AND";
                    filterString += " TIME<'" + mysqlDateFormat.format(filter.getEndDate().getTime()) + "'";
                }

                if (filter.getOrdering() == Ordering.ASCENDING) {
                    filterString += " ORDER BY 'Time' ASC";
                } else {
                    filterString += " ORDER BY Time DESC";
                }

                if (filter.getPageSize() != 0x7fffffff)
                    filterString += " LIMIT " + filter.getPageNumber() * filter.getPageSize() + ","
                            + filter.getPageSize();

                try {
                    long timerStart = System.currentTimeMillis();

                    // Retrieve the table array
                    Statement st = connection.createStatement();

                    String queryString = new String();
                    queryString = "SELECT Time, Value FROM " + table;
                    if (!filterString.isEmpty())
                        queryString += filterString;

                    logger.debug("SQL: " + queryString);

                    // Turn use of the cursor on.
                    st.setFetchSize(50);

                    ResultSet rs = st.executeQuery(queryString);

                    long count = 0;
                    double value;
                    List<HistoricItem> items = new ArrayList<HistoricItem>();
                    while (rs.next()) {
                        count++;

                        //TODO: Make this type specific ???
                        value = rs.getDouble(2);
                        State v = new DecimalType(value);

                        SqlItem sqlItem = new SqlItem(itemName, v, rs.getTimestamp(1));
                        items.add(sqlItem);
                    }

                    rs.close();
                    st.close();

                    long timerStop = System.currentTimeMillis();
                    logger.debug("SQL: query returned {} rows in {}ms", count, timerStop - timerStart);

                    // Success
                    errCnt = 0;

                    return items;
                } catch (SQLException e) {
                    errCnt++;
                    logger.error("SQL: Error running querying : " + e.getMessage());
                }
            }
        }
        return Collections.emptyList();
    }
}