/**
* Global Sensor Networks (GSN) Source Code
* Copyright (c) 2006-2014, Ecole Polytechnique Federale de Lausanne (EPFL)
*
* This file is part of GSN.
*
* GSN 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.
*
* GSN 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 GSN. If not, see <http://www.gnu.org/licenses/>.
*
* File: gsn-tiny/src/tinygsn/storage/StorageManager.java
*
* @author Do Ngoc Hoan
*/
package tinygsn.storage;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import tinygsn.beans.DataField;
import tinygsn.beans.DataTypes;
import tinygsn.beans.StreamElement;
import tinygsn.beans.StreamSource;
import tinygsn.model.vsensor.AbstractVirtualSensor;
import tinygsn.utils.GSNRuntimeException;
import android.annotation.SuppressLint;
public abstract class StorageManager {
// deprecated
protected boolean isH2;
protected boolean isSQLite;
private String databaseDriver;
// private BasicDataSource pool;
private Connection con = null;
public void init(String databaseDriver, String username, String password,
String databaseURL, int maxDBConnections) {
this.databaseDriver = databaseDriver;
try {
initDatabaseAccess(con = getConnection());
}
catch (Exception e) {
}
finally {
close(con);
}
}
public void initDatabaseAccess(Connection con) throws Exception {
}
public abstract byte convertLocalTypeToGSN(int jdbcType, int precision);
public abstract String getStatementDropIndex();
public abstract String getStatementDropView();
public abstract int getTableNotExistsErrNo();
public abstract String addLimit(String query, int limit, int offset);
public abstract StringBuilder getStatementUselessDataRemoval(
String virtualSensorName, long storageSize);
public byte convertLocalTypeToGSN(int jdbcType) {
return convertLocalTypeToGSN(jdbcType, 0);
}
/**
* Returns false if the table doesn't exist. Uses the current default
* connection.
*
* @param tableName
* @return False if the table doesn't exist in the current connection.
* @throws SQLException
*/
public boolean tableExists(CharSequence tableName) throws SQLException {
Connection connection = null;
try {
connection = getConnection();
return tableExists(tableName, new DataField[] {}, connection);
}
finally {
close(connection);
}
}
/**
* Checks to see if the given tablename exists using the given connection.
*
* @param tableName
* @param connection
* @return
* @throws SQLException
*/
public boolean tableExists(CharSequence tableName, Connection connection)
throws SQLException {
return tableExists(tableName, new DataField[] {}, connection);
}
public abstract StringBuilder getStatementRemoveUselessDataCountBased(
String virtualSensorName, long storageSize);
public StringBuilder getStatementRemoveUselessDataTimeBased(
String virtualSensorName, long storageSize) {
StringBuilder query = null;
// long timedToRemove = -1;
// Connection conn = null;
// try {
// ResultSet rs =
// Main.getStorage(virtualSensorName).executeQueryWithResultSet(new
// StringBuilder("SELECT MAX(timed) FROM ").append(virtualSensorName), conn
// = Main.getStorage(virtualSensorName).getConnection());
// if (rs.next())
// timedToRemove = rs.getLong(1);
// } catch (SQLException e) {
// logger.error(e.getMessage(), e);
// } finally {
// Main.getStorage(virtualSensorName).close(conn);
// }
// query = new
// StringBuilder().append("delete from ").append(virtualSensorName).append(" where ").append(virtualSensorName).append(".timed < ").append(timedToRemove);
// query.append(" - ").append(storageSize);
return query;
}
public DataField[] tableToStructure(CharSequence tableName,
Connection connection) throws SQLException {
StringBuilder sb = new StringBuilder("select * from ").append(tableName)
.append(" where 1=0 ");
ResultSet rs = null;
DataField[] toReturn = null;
try {
rs = executeQueryWithResultSet(sb, connection);
ResultSetMetaData structure = rs.getMetaData();
ArrayList<DataField> toReturnArr = new ArrayList<DataField>();
for (int i = 1; i <= structure.getColumnCount(); i++) {
String colName = structure.getColumnLabel(i);
if (colName.equalsIgnoreCase("pk"))
continue;
int colType = structure.getColumnType(i);
byte colTypeInGSN = convertLocalTypeToGSN(colType);
toReturnArr.add(new DataField(colName, colTypeInGSN));
}
toReturn = toReturnArr.toArray(new DataField[] {});
}
finally {
if (rs != null)
close(rs);
}
return toReturn;
}
/*
* Alternative method to 'tableToStructure' Useful for correctly creating
* structure for fields with variable length (like char, varchar, binary,
* blob)
*/
public DataField[] tableToStructureByString(String tableName,
Connection connection) throws SQLException {
StringBuilder sb = new StringBuilder("select * from ").append(tableName)
.append(" where 1=0 ");
ResultSet rs = null;
DataField[] toReturn = null;
try {
rs = executeQueryWithResultSet(sb, connection);
ResultSetMetaData structure = rs.getMetaData();
ArrayList<DataField> toReturnArr = new ArrayList<DataField>();
for (int i = 1; i <= structure.getColumnCount(); i++) {
String colName = structure.getColumnLabel(i);
if (colName.equalsIgnoreCase("pk"))
continue;
if (colName.equalsIgnoreCase("timed"))
continue;
int colType = structure.getColumnType(i);
String colTypeName = structure.getColumnTypeName(i);
int precision = structure.getPrecision(i);
byte colTypeInGSN = convertLocalTypeToGSN(colType);
if ((colTypeInGSN == DataTypes.VARCHAR)
|| (colTypeInGSN == DataTypes.CHAR))
toReturnArr.add(new DataField(colName, colTypeName, precision,
colName));
else
toReturnArr.add(new DataField(colName, colTypeInGSN));
}
toReturn = toReturnArr.toArray(new DataField[] {});
}
finally {
if (rs != null)
close(rs);
}
return toReturn;
}
/**
* Returns false if the table doesnt exist. If the table exists but the
* structure is not compatible with the specified fields the method throws
* GSNRuntimeException. Note that this method doesn't close the connection
*
* @param tableName
* @param connection
* (this method will not close it and the caller is responsible for
* closing the connection)
* @return
* @throws SQLException
* @Throws GSNRuntimeException
*/
public boolean tableExists(CharSequence tableName, DataField[] fields,
Connection connection) throws SQLException, GSNRuntimeException {
// if (!ValidityTools.isValidJavaVariable(tableName))
// throw new GSNRuntimeException("Table name is not valid");
// StringBuilder sb = new
// StringBuilder("select * from ").append(tableNameGeneratorInString(tableName)).append(" where 1=0 ");
// ResultSet rs = null;
// try {
// rs = executeQueryWithResultSet(sb, connection);
// ResultSetMetaData structure = rs.getMetaData();
// if (fields != null && fields.length > 0)
// nextField:for (DataField field : fields) {
// for (int i = 1; i <= structure.getColumnCount(); i++) {
// String colName = structure.getColumnLabel(i);
// int colType = structure.getColumnType(i);
// int colTypeScale = structure.getScale(i);
// if (field.getName().equalsIgnoreCase(colName))
// if (field.getDataTypeID() == convertLocalTypeToGSN(colType,
// colTypeScale))
// continue nextField;
// else
// throw new GSNRuntimeException("The column : "
// + colName + " in the >" + tableName
// + "< table is not compatible with type : "
// + field.getType()
// + ". The actual type for this table (currently in the database): " +
// colType);
// }
// throw new GSNRuntimeException("The table " + tableName
// + " in the database, doesn't have the >" + field.getName()
// + "< column.");
// }
// } catch (SQLException e) {
// if (e.getErrorCode() == getTableNotExistsErrNo() ||
// e.getMessage().contains("does not exist"))
// return false;
// else {
// // logger.error(e.getErrorCode());
// throw e;
// }
// } finally {
// close(rs);
// }
return true;
}
public boolean tableExists(CharSequence tableName, DataField[] fields)
throws SQLException {
Connection conn = null;
boolean to_return = true;
try {
conn = getConnection();
to_return = tableExists(tableName, fields, conn);
}
finally {
close(conn);
}
return to_return;
}
/**
* Executes the query of the database. Returns the specified colIndex of the
* first row. Useful for image recovery of the web interface.
*
* @param query
* The query to be executed.
* @return A resultset with only one row and one column. The user of the
* method should first call next on the result set to make sure that
* the row is there and then retrieve the value for the row.
* @throws SQLException
*/
public ResultSet getBinaryFieldByQuery(StringBuilder query, String colName,
long pk, Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(query.toString());
ps.setLong(1, pk);
return ps.executeQuery();
}
public void closeStatement(Statement stmt) {
try {
if (stmt != null) {
stmt.close();
}
}
catch (SQLException e) {
}
}
public void close(ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
}
catch (SQLException e) {
}
}
public void close(PreparedStatement preparedStatement) {
try {
if (preparedStatement != null) {
preparedStatement.close();
}
}
catch (SQLException e) {
}
}
public void close(Connection conn) {
try {
if (conn != null && !conn.isClosed()) {
conn.close();
}
}
catch (SQLException e) {
}
}
/**
* ************************************************************************
* Various Statement Executors.
* ************************************************************************
*/
public void executeRenameTable(String oldName, String newName)
throws SQLException {
Connection conn = null;
try {
conn = getConnection();
executeRenameTable(oldName, newName, conn);
}
finally {
close(conn);
}
}
public void executeRenameTable(String oldName, String newName,
Connection connection) throws SQLException {
PreparedStatement prepareStatement = null;
try {
prepareStatement = connection.prepareStatement(getStatementRenameTable(
oldName, newName));
prepareStatement.execute();
}
finally {
close(prepareStatement);
}
}
public void executeDropTable(CharSequence tableName) throws SQLException {
Connection conn = null;
try {
conn = getConnection();
executeDropTable(tableName, conn);
}
finally {
close(conn);
}
}
public void executeDropTable(CharSequence tableName, Connection connection) {
PreparedStatement prepareStatement = null;
try {
String stmt = getStatementDropTable(tableName, connection).toString();
prepareStatement = connection.prepareStatement(stmt);
prepareStatement.execute();
}
catch (SQLException e) {
}
}
public void executeCreateTable(CharSequence tableName, DataField[] structure,
boolean unique) throws SQLException {
Connection conn = null;
try {
conn = getConnection();
executeCreateTable(tableName, structure, unique, conn);
}
finally {
close(conn);
}
}
/**
* Create a table with a index on the timed field.
*
* @param tableName
* @param structure
* @param unique
* , setting this true cause the system to create a unique index on
* time.
* @param connection
* @throws SQLException
*/
public void executeCreateTable(CharSequence tableName, DataField[] structure,
boolean unique, Connection connection) throws SQLException {
StringBuilder sql = getStatementCreateTable(tableName, structure,
connection);
PreparedStatement prepareStatement = connection.prepareStatement(sql
.toString());
prepareStatement.execute();
prepareStatement.close();
sql = getStatementCreateIndexOnTimed(tableName, unique);
prepareStatement = connection.prepareStatement(sql.toString());
prepareStatement.execute();
}
public ResultSet executeQueryWithResultSet(StringBuilder query,
Connection connection) throws SQLException {
return connection.prepareStatement(query.toString()).executeQuery();
}
// public ResultSet executeQueryWithResultSet(AbstractQuery abstractQuery,
// Connection c) throws SQLException {
// if (abstractQuery.getLimitCriterion() == null) {
// return executeQueryWithResultSet(abstractQuery.getStandardQuery(), c);
// }
// String query = addLimit(abstractQuery.getStandardQuery().toString(),
// abstractQuery.getLimitCriterion().getSize(),
// abstractQuery.getLimitCriterion().getOffset());
// return executeQueryWithResultSet(new StringBuilder(query), c);
// }
// public DataEnumerator executeQuery(StringBuilder query, boolean
// binaryFieldsLinked, Connection connection) throws SQLException {
// // if (logger.isDebugEnabled())
// // logger.debug("Executing query: " + query + "( Binary Field Linked:" +
// binaryFieldsLinked + ")");
// // return new DataEnumerator(this,
// connection.prepareStatement(query.toString()), binaryFieldsLinked);
// }
//
// /**
// * Attention: Caller should close the connection.
// *
// * @param abstractQuery
// * @param binaryFieldsLinked
// * @param connection
// * @return
// * @throws SQLException
// */
// public DataEnumerator executeQuery(AbstractQuery abstractQuery, boolean
// binaryFieldsLinked, Connection connection) throws SQLException {
// if (abstractQuery.getLimitCriterion() == null) {
// return executeQuery(abstractQuery.getStandardQuery(), binaryFieldsLinked,
// connection);
// }
// String query = addLimit(abstractQuery.getStandardQuery().toString(),
// abstractQuery.getLimitCriterion().getSize(),
// abstractQuery.getLimitCriterion().getOffset());
// if (logger.isDebugEnabled())
// logger.debug("Executing query: " + query + "(" + binaryFieldsLinked + ")");
// return new DataEnumerator(this,
// connection.prepareStatement(query.toString()), binaryFieldsLinked);
// }
//
// public DataEnumerator streamedExecuteQuery(AbstractQuery abstractQuery,
// boolean binaryFieldsLinked, Connection connection) throws SQLException {
// if (abstractQuery.getLimitCriterion() == null) {
// return streamedExecuteQuery(abstractQuery.getStandardQuery().toString(),
// binaryFieldsLinked, connection);
// }
// String query = addLimit(abstractQuery.getStandardQuery().toString(),
// abstractQuery.getLimitCriterion().getSize(),
// abstractQuery.getLimitCriterion().getOffset());
// if (logger.isDebugEnabled())
// logger.debug("Executing query: " + query + "(" + binaryFieldsLinked + ")");
// return streamedExecuteQuery(query, binaryFieldsLinked, connection);
// }
//
// public DataEnumerator executeQuery(StringBuilder query, boolean
// binaryFieldsLinked) throws SQLException {
// return executeQuery(query, binaryFieldsLinked, getConnection());
// }
//
// public DataEnumerator streamedExecuteQuery(String query, boolean
// binaryFieldsLinked, Connection conn) throws SQLException {
// return new DataEnumerator(this, conn.prepareStatement(query),
// binaryFieldsLinked);
// }
//
//
// public DataEnumerator streamedExecuteQuery(String query, boolean
// binaryFieldsLinked) throws SQLException {
// return streamedExecuteQuery(query, binaryFieldsLinked, getConnection());
// }
/**
* This method executes the provided statement over the connection. If there
* is an error returns -1 otherwise it returns the output of the executeUpdate
* method on the PreparedStatement class which reflects the number of changed
* rows in the underlying table.
*
* @param sql
* @param connection
* @return Number of effected rows or -1 if there is an error.
*/
@SuppressLint("NewApi")
public void executeCommand(String sql, Connection connection) {
Statement stmt = null;
try {
stmt = connection.createStatement();
stmt.execute(sql);
}
catch (SQLException error) {
}
finally {
try {
if (stmt != null && !stmt.isClosed())
stmt.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
public int executeUpdate(String updateStatement, Connection connection) {
int toReturn = -1;
try {
toReturn = connection.createStatement().executeUpdate(updateStatement);
}
catch (SQLException error) {
}
return toReturn;
}
public int executeUpdate(StringBuilder updateStatement, Connection connection) {
int to_return = executeUpdate(updateStatement.toString(), connection);
return to_return;
}
public int executeUpdate(StringBuilder updateStatement) throws SQLException {
Connection connection = null;
try {
connection = getConnection();
return executeUpdate(updateStatement, connection);
}
finally {
close(connection);
}
}
public void executeInsert(CharSequence tableName, DataField[] fields,
StreamElement se) throws SQLException {
Connection connection = null;
try {
connection = getConnection();
executeInsert(tableName,fields, se, connection);
}
finally {
close(connection);
}
}
public void executeInsert(CharSequence tableName, DataField[] fields,
StreamElement streamElement, Connection connection) throws SQLException {
PreparedStatement ps = null;
String query = getStatementInsert(tableName, fields).toString();
try {
ps = connection.prepareStatement(query);
int counter = 1;
for (DataField dataField : fields) {
if (dataField.getName().equalsIgnoreCase("timed"))
continue;
Serializable value = streamElement.getData(dataField.getName());
switch (dataField.getDataTypeID()) {
case DataTypes.VARCHAR:
if (value == null)
ps.setNull(counter, Types.VARCHAR);
else
ps.setString(counter, value.toString());
break;
case DataTypes.CHAR:
if (value == null)
ps.setNull(counter, Types.CHAR);
else
ps.setString(counter, value.toString());
break;
case DataTypes.INTEGER:
if (value == null)
ps.setNull(counter, Types.INTEGER);
else
ps.setInt(counter, ((Number) value).intValue());
break;
case DataTypes.SMALLINT:
if (value == null)
ps.setNull(counter, Types.SMALLINT);
else
ps.setShort(counter, ((Number) value).shortValue());
break;
case DataTypes.TINYINT:
if (value == null)
ps.setNull(counter, Types.TINYINT);
else
ps.setByte(counter, ((Number) value).byteValue());
break;
case DataTypes.DOUBLE:
if (value == null)
ps.setNull(counter, Types.DOUBLE);
else
ps.setDouble(counter, ((Number) value).doubleValue());
break;
case DataTypes.BIGINT:
if (value == null)
ps.setNull(counter, Types.BIGINT);
else
ps.setLong(counter, ((Number) value).longValue());
break;
case DataTypes.BINARY:
if (value == null)
ps.setNull(counter, Types.BINARY);
else
ps.setBytes(counter, (byte[]) value);
break;
default:
// logger.error("The type conversion is not supported for : "
// + dataField.getName() + "(" + dataField.getDataTypeID() + ") : ");
}
counter++;
}
ps.setLong(counter, streamElement.getTimeStamp());
ps.execute();
}
catch (GSNRuntimeException e) {
// if (e.getType() ==
// GSNRuntimeException.UNEXPECTED_VIRTUAL_SENSOR_REMOVAL) {
// if (logger.isDebugEnabled())
//
// logger
// .debug(
// "An stream element dropped due to unexpected virtual sensor removal. (Stream element: "
// + streamElement.toString() + ")+ Query: " + query, e);
// } else
// logger.warn(
// "Inserting a stream element failed : " + streamElement.toString(), e);
}
catch (SQLException e) {
if (e.getMessage().toLowerCase().contains("duplicate entry"))
// logger
// .info("Error occurred on inserting data to the database, an stream element dropped due to: "
// + e.getMessage()
// + ". (Stream element: "
// + streamElement.toString() + ")+ Query: " + query);
// else
// logger
// .warn("Error occurred on inserting data to the database, an stream element dropped due to: "
// + e.getMessage()
// + ". (Stream element: "
// + streamElement.toString() + ")+ Query: " + query);
throw e;
}
finally {
close(ps);
}
}
/***************************************************************************
* Statement Generators
**************************************************************************/
/**
* Creates a sql statement which can be used for inserting the specified
* stream element in to the specified table.
*
* @param tableName
* The table which the generated sql will pointing to.
* @param fields
* The stream element for which the sql statement is generated.
* @return A sql statement which can be used for inserting the provided stream
* element into the specified table.
*/
public StringBuilder getStatementInsert(CharSequence tableName,
DataField fields[]) {
StringBuilder toReturn = new StringBuilder("insert into ")
.append(tableName).append(" ( ");
int numberOfQuestionMarks = 1; // Timed is always there.
for (DataField dataField : fields) {
if (dataField.getName().equalsIgnoreCase("timed"))
continue;
numberOfQuestionMarks++;
toReturn.append(dataField.getName()).append(" ,");
}
toReturn.append(" timed ").append(" ) values (");
for (int i = 1; i <= numberOfQuestionMarks; i++)
toReturn.append("?,");
toReturn.deleteCharAt(toReturn.length() - 1);
toReturn.append(")");
return toReturn;
}
public String getStatementRenameTable(String oldName, String newName) {
return new StringBuilder("alter table ").append(oldName)
.append(" rename to ").append(newName).toString();
}
public abstract StringBuilder getStatementDropTable(CharSequence tableName,
Connection conn) throws SQLException;
/**
* First detects the appropriate DB Engine to use. Get's the drop index
* statement syntax (which is DB dependent) and executes it.
*
* @param indexName
* @param connection
* @return
* @throws SQLException
*/
public StringBuilder getStatementDropIndex(CharSequence indexName,
CharSequence tableName, Connection connection) throws SQLException {
return new StringBuilder(getStatementDropIndex()
.replace("#NAME", indexName).replace("#TABLE", tableName));
}
public StringBuilder getStatementCreateIndexOnTimed(CharSequence tableName,
boolean unique) throws SQLException {
StringBuilder toReturn = new StringBuilder("CREATE ");
if (unique)
toReturn.append(" UNIQUE ");
toReturn.append(" INDEX ")
.append(tableNamePostFixAppender(tableName, "_INDEX")).append(" ON ")
.append(tableName).append(" (timed DESC)");
return toReturn;
}
public StringBuilder getStatementCreateTable(CharSequence tableName,
DataField[] structure, Connection connection) throws SQLException {
return getStatementCreateTable(tableName.toString(), structure);
}
public abstract StringBuilder getStatementCreateTable(String tableName,
DataField[] structure);
private String driver = null;
/**
* The prefix is in lower case
*
* @return
*/
public abstract String getJDBCPrefix();
public String getJDBCDriverClass() {
return databaseDriver;
}
/*
* Converts from internal GSN data types to a supported DB data type.
*
* @param field The DataField to be converted @return convertedType The
* datatype name used by the target database.
*/
public abstract String convertGSNTypeToLocalType(DataField gsnType);
/**
* Obtains the default database connection. The conneciton comes from the data
* source which is configured through gsn.xml file.
*
* @return
* @throws SQLException
*/
public Connection getConnection() throws SQLException {
return con;
}
/**
* Retruns an approximation of the difference between the current time of the
* DB and that of the local system
*
* @return
*/
public long getTimeDifferenceInMillis() {
String query = getStatementDifferenceTimeInMillis();
Connection connection = null;
try {
connection = getConnection();
PreparedStatement prepareStatement = connection.prepareStatement(query);
long time1 = System.currentTimeMillis();
ResultSet resultSet;
resultSet = prepareStatement.executeQuery();
resultSet.next();
long time2 = System.currentTimeMillis();
return resultSet.getLong(1) - time2 + (time2 - time1) / 2;
}
catch (SQLException error) {
// logger.error(error.getMessage(), error);
}
finally {
close(connection);
}
return 0;
}
public abstract String getStatementDifferenceTimeInMillis();
//
public String randomTableNameGenerator(int length) {
byte oneCharacter;
StringBuffer result = new StringBuffer(length);
for (int i = 0; i < length; i++) {
oneCharacter = (byte) ((Math.random() * ('z' - 'a' + 1)) + 'a');
result.append((char) oneCharacter);
}
return result.toString();
}
public int tableNameGenerator() {
return randomTableNameGenerator(15).hashCode();
}
public StringBuilder tableNameGeneratorInString(CharSequence tableName) {
return new StringBuilder(tableName);
}
public StringBuilder tableNameGeneratorInString(int code) {
StringBuilder sb = new StringBuilder("_");
if (code < 0)
sb.append("_");
sb.append(Math.abs(code));
return tableNameGeneratorInString(sb);
}
public String tableNamePostFixAppender(CharSequence table_name, String postFix) {
String tableName = table_name.toString();
if (tableName.endsWith("\""))
return (tableName.substring(0, tableName.length() - 2)) + postFix + "\"";
else
return tableName + postFix;
}
public boolean isH2() {
return isH2;
}
public boolean isSQLite() {
return isSQLite;
}
public abstract ArrayList<AbstractVirtualSensor> getListofVS();
public abstract ArrayList<StreamSource> getSourcesOfVS(String name);
public abstract HashMap<String,String> getSetting(String keyPrefix);
public abstract void setSetting(String key, String value);
}