org.efaps.db.databases.MySQLDatabase Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of efaps-kernel Show documentation
Show all versions of efaps-kernel Show documentation
eFaps is a framework used to map objects with or without attached files to
a relational database and optional file systems (only for attaches files). Configurable access control can be provided down to object and attribute level depending on implementation and use case. Depending on requirements, events (like triggers) allow to implement business logic and to separate business logic from user interface.
The framework includes integrations (e.g. webdav, full text search) and a web application as 'simple' configurable user interface. Some best practises, example web application modules (e.g. team work module) support administrators and implementers using this framework.
/*
* Copyright 2003 - 2012 The eFaps Team
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
* Revision: $Rev: 7483 $
* Last Changed: $Date: 2012-05-11 11:57:38 -0500 (Fri, 11 May 2012) $
* Last Changed By: $Author: [email protected] $
*/
package org.efaps.db.databases;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import org.efaps.db.databases.information.TableInformation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Database class for the MySQL database.
*
* @author The eFaps Team
* @version $Id: MySQLDatabase.java 7483 2012-05-11 16:57:38Z [email protected] $
*/
public class MySQLDatabase
extends AbstractDatabase
{
/**
* Logging instance used in this class.
*/
private static final Logger LOG = LoggerFactory.getLogger(PostgreSQLDatabase.class);
/**
* Prefix used for tables which simulates sequences.
*
* @see #createSequence(Connection, String, long)
* @see #deleteSequence(Connection, String)
* @see #existsSequence(Connection, String)
* @see #nextSequence(Connection, String)
* @see #setSequence(Connection, String, long)
*/
private static final String PREFIX_SEQUENCE = "seq_";
/**
* Select statement to select all unique keys for current logged in
* MySQL database user.
*
* @see #initTableInfoUniqueKeys(Connection, String, Map)
*/
private static final String SQL_UNIQUE_KEYS = "select "
+ "a.constraint_name as INDEX_NAME, "
+ "a.table_name as TABLE_NAME, "
+ "b.column_name as COLUMN_NAME, "
+ "b.ordinal_position as ORDINAL_POSITION "
+ "from "
+ "information_schema.table_constraints a,"
+ "information_schema.key_column_usage b "
+ "where "
+ "a.constraint_type='UNIQUE' "
+ "and a.table_schema=b.table_schema "
+ "and a.table_name=b.table_name "
+ "and a.constraint_name=b.constraint_name";
/**
* Select statement for all foreign keys for current logged in MySQL
* database user.
*
* @see #initTableInfoForeignKeys(Connection, String, Map)
*/
private static final String SQL_FOREIGN_KEYS = "select "
+ "a.TABLE_NAME as TABLE_NAME, "
+ "a.CONSTRAINT_NAME as FK_NAME, "
+ "b.COLUMN_NAME as FKCOLUMN_NAME, "
+ "'' as DELETE_RULE, "
+ "b.REFERENCED_TABLE_NAME as PKTABLE_NAME, "
+ "b.REFERENCED_COLUMN_NAME as PKCOLUMN_NAME "
+ "from "
+ "information_schema.table_constraints a, "
+ "information_schema.key_column_usage b "
+ "where "
+ "a.constraint_type='FOREIGN KEY' "
+ "and a.CONSTRAINT_SCHEMA=b.CONSTRAINT_SCHEMA "
+ "and a.CONSTRAINT_NAME=b.CONSTRAINT_NAME ";
/**
* Initializes the mapping between the eFaps column types and the MySQL
* specific column types.
*/
public MySQLDatabase()
{
addMapping(ColumnType.INTEGER, "bigint", "null", "bigint", "integer", "int", "mediumint");
addMapping(ColumnType.DECIMAL, "decimal", "null", "decimal", "dec");
addMapping(ColumnType.REAL, "double", "null", "double", "float");
addMapping(ColumnType.STRING_SHORT, "varchar", "null", "text", "tinytext");
addMapping(ColumnType.STRING_LONG, "varchar", "null", "varchar");
addMapping(ColumnType.DATETIME, "datetime", "null", "datetime", "timestamp");
addMapping(ColumnType.BLOB, "longblob", "null", "longblob", "mediumblob", "blob", "tinyblob",
"varbinary", "binary");
addMapping(ColumnType.CLOB, "longtext", "null", "longtext");
addMapping(ColumnType.BOOLEAN, "boolean", "null", "boolean", "bool", "tinyint", "bit");
}
/**
* {@inheritDoc}
*/
@Override
public boolean isConnected(final Connection _connection)
{
// FIXME must be implemented
return false;
}
/**
* @see org.efaps.db.databases.AbstractDatabase#getCurrentTimeStamp()
* @return "current_timestamp"
*/
@Override
public String getCurrentTimeStamp()
{
return "current_timestamp";
}
/**
* {@inheritDoc}
*/
@Override
public String getTimestampValue(final String _isoDateTime)
{
return "timestamp '" + _isoDateTime + "'";
}
/**
* {@inheritDoc}
*/
@Override
public Object getBooleanValue(final Boolean _value)
{
return _value;
}
/**
* This is the MySQL specific implementation of an all deletion.
* Following order is used to remove all eFaps specific information:
*
* - remove all views of the user
* - remove all tables of the user
* - remove all sequences of the user
*
* The table are dropped with cascade, so all depending sequences etc.
* are also dropped automatically.
* Attention! If application specific tables, views or constraints are
* defined, this database objects are also removed!
*
* @param _con sql connection
* @throws SQLException on error while executing sql statements
*/
@Override
public void deleteAll(final Connection _con)
throws SQLException
{
final Statement stmtSel = _con.createStatement();
final Statement stmtExec = _con.createStatement();
try {
if (MySQLDatabase.LOG.isInfoEnabled()) {
MySQLDatabase.LOG.info("Remove all Tables");
}
final DatabaseMetaData metaData = _con.getMetaData();
// delete all views
final ResultSet rsViews = metaData.getTables(null, null, "%", new String[] { "VIEW" });
while (rsViews.next()) {
final String viewName = rsViews.getString("TABLE_NAME");
if (MySQLDatabase.LOG.isDebugEnabled()) {
MySQLDatabase.LOG.debug(" - View '" + viewName + "'");
}
stmtExec.execute("drop view " + viewName);
}
rsViews.close();
// delete all constraints
final ResultSet rsTables = metaData.getTables(null, null, "%", new String[] { "TABLE" });
while (rsTables.next()) {
final String tableName = rsTables.getString("TABLE_NAME");
final ResultSet rsf = _con.getMetaData().getImportedKeys(null, null, tableName);
while (rsf.next()) {
final String fkName = rsf.getString("FK_NAME").toUpperCase();
if (MySQLDatabase.LOG.isDebugEnabled()) {
MySQLDatabase.LOG.debug(" - Foreign Key '" + fkName + "'");
}
stmtExec.execute("alter table " + tableName + " drop foreign key " + fkName);
}
}
// delete all tables
rsTables.beforeFirst();
while (rsTables.next()) {
final String tableName = rsTables.getString("TABLE_NAME");
if (MySQLDatabase.LOG.isDebugEnabled()) {
MySQLDatabase.LOG.debug(" - Table '" + tableName + "'");
}
stmtExec.execute("drop table " + tableName + " cascade");
}
rsTables.close();
} finally {
stmtSel.close();
stmtExec.close();
}
}
/**
* {@inheritDoc}
*/
@Override
public MySQLDatabase deleteView(final Connection _con,
final String _name)
throws SQLException
{
final Statement stmtExec = _con.createStatement();
stmtExec.execute("drop view " + _name);
return this;
}
/**
* For the MySQL database, an eFaps SQL table is created in this steps.
*
* - SQL table itself with column
ID and unique key on the
* column is created
* - if the table is an auto increment table (parent table is
*
null, the column ID is set as auto increment
* column
* - if no parent table is defined, the foreign key to the parent table is
* automatically set
*
*
* @see org.efaps.db.databases.AbstractDatabase#createTable(java.sql.Connection, java.lang.String, java.lang.String)
* @param _con Connection to be used for the SQL statements
* @param _table name for the table
* @return this MySQL DB definition instance
* @throws SQLException if the table could not be created
*/
@Override
public MySQLDatabase createTable(final Connection _con,
final String _table)
throws SQLException
{
final Statement stmt = _con.createStatement();
try {
stmt.executeUpdate(new StringBuilder()
.append("create table `").append(_table).append("` (")
.append("`ID` bigint ")
.append(",").append("constraint `").append(_table).append("_PK_ID` primary key (`ID`)")
.append(") engine InnoDB character set utf8;")
.toString());
} finally {
stmt.close();
}
return this;
}
/**
* {@inheritDoc}
*/
@Override
public MySQLDatabase defineTableAutoIncrement(final Connection _con,
final String _table)
throws SQLException
{
final Statement stmt = _con.createStatement();
try {
// define for ID column the auto increment value
stmt.execute(new StringBuilder()
.append("alter table `").append(_table)
.append("` modify column `ID` bigint not null auto_increment")
.toString());
} finally {
stmt.close();
}
return this;
}
/**
* Overwrites original method because MySQL supports automatically
* generated keys.
*
* @return always true because generated keys are supported by MySQL
* database
* @see AbstractDatabase#supportsGetGeneratedKeys()
*/
@Override
public boolean supportsGetGeneratedKeys()
{
return true;
}
/**
* Overwrites original method because MySQL supports binary input stream.
*
* @return always true because supported by MySQL database
* @see AbstractDatabase#supportsBinaryInputStream()
*/
@Override
public boolean supportsBinaryInputStream()
{
return true;
}
/**
* Returns a single reversed apostrophe ` used to select tables within
* SQL statements for a MySQL database..
*
* @return always single reversed apostrophe
*/
@Override
public String getTableQuote()
{
return "`";
}
/**
* Returns a single reversed apostrophe ` used to select columns within
* SQL statements for a MySQL database..
*
* @return always single reversed apostrophe
*/
@Override
public String getColumnQuote()
{
return "`";
}
/**
* Creates a table with auto generated keys with table name as
* concatenation of the prefix {@link #PREFIX_SEQUENCE} and the lower case
* of _name. This table "simulates" the sequences (which are
* not supported by MySQL).
*
* @param _con SQL connection
* @param _name name of the sequence
* @param _startValue start value of the sequence number
* @return this instance
* @throws SQLException if SQL table could not be created; defined as auto
* increment table or if the sequence number could not
* be defined
* @see #createTable(Connection, String)
* @see #defineTableAutoIncrement(Connection, String)
* @see #setSequence(Connection, String, long)
* @see #PREFIX_SEQUENCE
*/
@Override
public MySQLDatabase createSequence(final Connection _con,
final String _name,
final long _startValue)
throws SQLException
{
final String name = new StringBuilder()
.append(MySQLDatabase.PREFIX_SEQUENCE).append(_name.toLowerCase())
.toString();
createTable(_con, name);
defineTableAutoIncrement(_con, name);
setSequence(_con, _name, _startValue);
return this;
}
/**
* Deletes given sequence _name which is internally
* represented by this MySQL connector as normal SQL table. The name of the
* SQL table to delete is a concatenation of {@link #PREFIX_SEQUENCE} and
* _name in lower case.
*
* @param _con SQL connection
* @param _name name of the sequence
* @return this instance
* @throws SQLException if sequence (simulated by an auto increment SQL
* table) could not be deleted
* @see #PREFIX_SEQUENCE
*/
@Override
public MySQLDatabase deleteSequence(final Connection _con,
final String _name)
throws SQLException
{
final String cmd = new StringBuilder()
.append("DROP TABLE `").append(MySQLDatabase.PREFIX_SEQUENCE).append(_name.toLowerCase()).append("`")
.toString();
final Statement stmt = _con.createStatement();
try {
stmt.executeUpdate(cmd);
} finally {
stmt.close();
}
return this;
}
/**
* Checks if the related table representing sequence _name
* exists.
*
* @param _con SQL connection
* @param _name name of the sequence
* @return true if a table with name as concatenation of
* {@link #PREFIX_SEQUENCE} and _name (in lower case)
* representing the sequence exists; otherwise false
* @throws SQLException if check for the existence of the table
* representing the sequence failed
* @see #existsTable(Connection, String)
* @see #PREFIX_SEQUENCE
*/
@Override
public boolean existsSequence(final Connection _con,
final String _name)
throws SQLException
{
return existsTable(
_con,
new StringBuilder().append(MySQLDatabase.PREFIX_SEQUENCE).append(_name.toLowerCase()).toString());
}
/**
* Fetches next number for sequence _name by inserting new
* row into representing table. The new auto generated key is returned as
* next number of the sequence.
*
* @param _con SQL connection
* @param _name name of the sequence
* @return current inserted value of the table
* @throws SQLException if next number from the sequence could not be
* fetched
* @see #PREFIX_SEQUENCE
*/
@Override
public long nextSequence(final Connection _con,
final String _name)
throws SQLException
{
final long ret;
final Statement stmt = _con.createStatement();
try {
// insert new line
final String insertCmd = new StringBuilder()
.append("INSERT INTO `").append(MySQLDatabase.PREFIX_SEQUENCE).append(_name.toLowerCase())
.append("` VALUES ()")
.toString();
final int row = stmt.executeUpdate(insertCmd, Statement.RETURN_GENERATED_KEYS);
if (row != 1) {
throw new SQLException("no sequence found for '" + _name + "'");
}
// fetch new number
final ResultSet resultset = stmt.getGeneratedKeys();
if (resultset.next()) {
ret = resultset.getLong(1);
} else {
throw new SQLException("no sequence found for '" + _name + "'");
}
} finally {
stmt.close();
}
return ret;
}
/**
* Defines new _value for sequence _name. Because
* in MySQL the sequences are simulated and the values from fetched
* sequence numbers are not deleted, all existing values in the table are
* first deleted (to be sure that the sequence could be reseted to already
* fetched numbers). After the new starting value is defined a first auto
* generated value is fetched from the database so that this value is also
* stored if the MySQL database is restarted.
*
* @param _con SQL connection
* @param _name name of the sequence
* @param _value new value of the sequence
* @return this instance
* @throws SQLException if new number of the sequence could not be defined
* for the table
* @see #PREFIX_SEQUENCE
*/
@Override
public MySQLDatabase setSequence(final Connection _con,
final String _name,
final long _value)
throws SQLException
{
final String name = _name.toLowerCase();
final String lockCmd = new StringBuilder()
.append("LOCK TABLES `").append(MySQLDatabase.PREFIX_SEQUENCE).append(name)
.append("` WRITE")
.toString();
final String deleteCmd = new StringBuilder()
.append("DELETE FROM `").append(MySQLDatabase.PREFIX_SEQUENCE).append(name).append("`")
.toString();
final String alterCmd = new StringBuilder()
.append("ALTER TABLE `").append(MySQLDatabase.PREFIX_SEQUENCE).append(name)
.append("` AUTO_INCREMENT=").append(_value - 1)
.toString();
final String insertCmd = new StringBuilder()
.append("INSERT INTO `").append(MySQLDatabase.PREFIX_SEQUENCE).append(name)
.append("` VALUES ()")
.toString();
final String unlockCmd = new StringBuilder()
.append("UNLOCK TABLES")
.toString();
final Statement stmt = _con.createStatement();
try {
stmt.executeUpdate(lockCmd);
stmt.executeUpdate(deleteCmd);
stmt.executeUpdate(alterCmd);
stmt.executeUpdate(insertCmd);
stmt.executeUpdate(unlockCmd);
} finally {
stmt.close();
}
return this;
}
/**
* Overwrites the original method to specify SQL statement
* {@link #SQL_UNIQUE_KEYS} as replacement because the JDBC driver for
* MySQL does not handle matching table names.
*
* @param _con SQL connection
* @param _sql SQL statement (not used)
* @param _cache4Name map used to fetch depending on the table name the
* related table information
* @throws SQLException if unique keys could not be fetched
* @see #SQL_UNIQUE_KEYS
*/
@Override
protected void initTableInfoUniqueKeys(final Connection _con,
final String _sql,
final Map _cache4Name)
throws SQLException
{
super.initTableInfoUniqueKeys(_con, MySQLDatabase.SQL_UNIQUE_KEYS, _cache4Name);
}
/**
* Overwrites the original method to specify SQL statement
* {@link #SQL_FOREIGN_KEYS} as replacement because the JDBC driver for
* MySQL does not handle matching table names.
*
* @param _con SQL connection
* @param _sql SQL statement (not used)
* @param _cache4Name map used to fetch depending on the table name the
* related table information
* @throws SQLException if foreign keys could not be fetched
* @see #SQL_FOREIGN_KEYS
*/
@Override
protected void initTableInfoForeignKeys(final Connection _con,
final String _sql,
final Map _cache4Name)
throws SQLException
{
super.initTableInfoForeignKeys(_con, MySQLDatabase.SQL_FOREIGN_KEYS, _cache4Name);
}
}