org.efaps.db.databases.DerbyDatabase 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.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;
/**
* The class implements Apache Derby specific methods for data base access.
*
* @author The eFaps Team
* @version $Id: DerbyDatabase.java 7483 2012-05-11 16:57:38Z [email protected] $
*/
public class DerbyDatabase
extends AbstractDatabase
{
/**
* Logging instance used in this class.
*/
private static final Logger LOG = LoggerFactory.getLogger(DerbyDatabase.class);
/**
* SQL Select statement for all foreign keys and constraints.
*
* @see #deleteAll(Connection)
*/
private static final String SELECT_ALL_KEYS
= "select t.TABLENAME, c.CONSTRAINTNAME "
+ "from SYS.SYSSCHEMAS s, SYS.SYSTABLES t, SYS.SYSCONSTRAINTS c "
+ "where s.AUTHORIZATIONID<>'DBA' "
+ "and s.SCHEMAID=t.SCHEMAID "
+ "and t.TABLEID=c.TABLEID "
+ "and c.TYPE='F'";
/**
* SQL Select statement for all views.
*
* @see #deleteAll(Connection)
*/
private static final String SELECT_ALL_VIEWS
= "select t.TABLENAME "
+ "from SYS.SYSSCHEMAS s, SYS.SYSTABLES t "
+ "where s.AUTHORIZATIONID<>'DBA' "
+ "and s.SCHEMAID=t.SCHEMAID "
+ "and t.TABLETYPE='V'";
/**
* SQL Select statement for all tables.
*
* @see #deleteAll(Connection)
*/
private static final String SELECT_ALL_TABLES
= "select t.TABLENAME "
+ "from SYS.SYSSCHEMAS s, SYS.SYSTABLES t "
+ "where s.AUTHORIZATIONID<>'DBA' "
+ "and s.SCHEMAID=t.SCHEMAID "
+ "and t.TABLETYPE='T'";
/**
* Constructur.
* TODO: specificy real column type
*/
public DerbyDatabase()
{
addMapping(ColumnType.INTEGER, "bigint", "cast(null as bigint)", "bigint");
// this.columnMap.put(ColumnType.REAL, "real");
addMapping(ColumnType.STRING_SHORT, "char", "cast(null as char)", "char");
addMapping(ColumnType.STRING_LONG, "varchar", "cast(null as varchar)", "varchar");
addMapping(ColumnType.DATETIME, "timestamp", "cast(null as timestamp)", "timestamp");
addMapping(ColumnType.BLOB, "blob(2G)", "cast(null as blob)", "blob");
addMapping(ColumnType.CLOB, "clob(2G)", "cast(null as clob)", "clob");
addMapping(ColumnType.BOOLEAN, "smallint", "cast(null as smallint)", "smallint");
}
/**
* {@inheritDoc}
*/
@Override
public boolean isConnected(final Connection _connection)
{
// FIXME must be implemented
return false;
}
/**
* {@inheritDoc}
*/
@Override
public String getCurrentTimeStamp()
{
return "current_timestamp";
}
/**
* {@inheritDoc}
*/
@Override
public String getTimestampValue(final String _isoDateTime)
{
return "timestamp '" + _isoDateTime + "'";
}
@Override
public Object getBooleanValue(final Boolean _value)
{
// TODO Auto-generated method stub
return _value;
}
/**
* This is the Derby specific implementation of an all deletion. Following
* order is used to remove all eFaps specific information:
*
* - remove all foreign keys of the user
* - remove all views of the user
* - remove all tables of the user
*
* Attention! If application specific tables, views or constraints are
* defined, this database objects are also removed!
*
* @param _con sql connection
* @throws SQLException if remove of keys, views or tables failed
*/
@Override
public void deleteAll(final Connection _con)
throws SQLException
{
final Statement stmtSel = _con.createStatement();
final Statement stmtExec = _con.createStatement();
try {
// remove all foreign keys
if (DerbyDatabase.LOG.isInfoEnabled()) {
DerbyDatabase.LOG.info("Remove all Foreign Keys");
}
ResultSet rs = stmtSel.executeQuery(DerbyDatabase.SELECT_ALL_KEYS);
while (rs.next()) {
final String tableName = rs.getString(1);
final String constrName = rs.getString(2);
if (DerbyDatabase.LOG.isDebugEnabled()) {
DerbyDatabase.LOG.debug(" - Table '" + tableName + "' Constraint '" + constrName + "'");
}
stmtExec.execute("alter table " + tableName + " drop constraint " + constrName);
}
rs.close();
// remove all views
if (DerbyDatabase.LOG.isInfoEnabled()) {
DerbyDatabase.LOG.info("Remove all Views");
}
rs = stmtSel.executeQuery(DerbyDatabase.SELECT_ALL_VIEWS);
while (rs.next()) {
final String viewName = rs.getString(1);
if (DerbyDatabase.LOG.isDebugEnabled()) {
DerbyDatabase.LOG.debug(" - View '" + viewName + "'");
}
stmtExec.execute("drop view " + viewName);
}
rs.close();
// remove all tables
if (DerbyDatabase.LOG.isInfoEnabled()) {
DerbyDatabase.LOG.info("Remove all Tables");
}
rs = stmtSel.executeQuery(DerbyDatabase.SELECT_ALL_TABLES);
while (rs.next()) {
final String tableName = rs.getString(1);
if (DerbyDatabase.LOG.isDebugEnabled()) {
DerbyDatabase.LOG.debug(" - Table '" + tableName + "'");
}
stmtExec.execute("drop table " + tableName);
}
rs.close();
} finally {
stmtSel.close();
stmtExec.close();
}
}
/**
* {@inheritDoc}
*/
@Override
public DerbyDatabase deleteView(final Connection _con,
final String _name)
throws SQLException
{
throw new Error("not implemented");
}
/**
* {@inheritDoc}
*/
@Override
public DerbyDatabase createTable(final Connection _con,
final String _table/*,
final String _parentTable*/)
throws SQLException
{
final Statement stmt = _con.createStatement();
try {
// create table itself
final StringBuilder cmd = new StringBuilder()
.append("create table ").append(_table).append(" (")
.append(" ID bigint not null");
/* TODO
// auto increment
if (_parentTable == null) {
cmd.append(" generated always as identity (start with 1, increment by 1)");
}
*/
cmd.append(",")
.append(" constraint ").append(_table).append("_UK_ID unique(ID)");
/* TODO
// foreign key to parent sql table
if (_parentTable != null) {
cmd.append(",")
.append("constraint ").append(_table).append("_FK_ID ")
.append(" foreign key(ID) ")
.append(" references ").append(_parentTable).append("(ID)");
}
*/
cmd.append(")");
stmt.executeUpdate(cmd.toString());
} finally {
stmt.close();
}
return this;
}
/**
* {@inheritDoc}
*
*/
@Override
public DerbyDatabase defineTableAutoIncrement(final Connection _con,
final String _table)
throws SQLException
{
throw new Error("not implemented");
}
/**
* Adds a column to a SQL table. The method overrides the original method,
* because Derby does not allow for NOT NULL columns that no
* default value is defined. Is such column is created, the default value
* for real and integer is 0, for date time, short and long
* string a zero length string.
*
* @param _con SQL connection
* @param _tableName name of table to update
* @param _columnName column to add
* @param _columnType type of column to add
* @param _defaultValue default value of the column (or null if not
* specified)
* @param _length length of column to add (or 0 if not specified)
* @param _scale scale of the column to add (or 0 if not
* specified)
* @param _isNotNull true means that the column has no
* null values
* @throws SQLException if the column could not be added to the tables
* @return this
*/
@Override
//CHECKSTYLE:OFF
public DerbyDatabase addTableColumn(final Connection _con,
final String _tableName,
final String _columnName,
final ColumnType _columnType,
final String _defaultValue,
final int _length,
final int _scale,
final boolean _isNotNull)
throws SQLException
{
//CHECKSTYLE:ON
String defaultValue = _defaultValue;
if (_isNotNull && (defaultValue == null)) {
switch (_columnType) {
case INTEGER:
case REAL:
defaultValue = "0";
break;
case DATETIME:
case STRING_LONG:
case STRING_SHORT:
defaultValue = "''";
break;
default:
break;
}
}
return super.addTableColumn(_con, _tableName, _columnName, _columnType,
defaultValue, _length, _scale, _isNotNull);
}
/**
* @return always true because supported by Derby database
*/
@Override
public boolean supportsGetGeneratedKeys()
{
return true;
}
/**
* @return always true because supported by PostgreSQL database
*/
@Override
public boolean supportsBinaryInputStream()
{
return false;
}
/**
* @return always false because Apache Derby has some problems to
* handle to big transactions
*/
@Override
public boolean supportsBigTransactions()
{
return false;
}
/**
* {@inheritDoc}
*/
@Override
public DerbyDatabase createSequence(final Connection _con,
final String _name,
final long _startValue)
{
throw new Error("not implemented");
}
/**
* {@inheritDoc}
*/
@Override
public DerbyDatabase deleteSequence(final Connection _con,
final String _name)
{
throw new Error("not implemented");
}
/**
* {@inheritDoc}
*/
@Override
public boolean existsSequence(final Connection _con,
final String _name)
{
throw new Error("not implemented");
}
/**
* {@inheritDoc}
*/
@Override
public long nextSequence(final Connection _con,
final String _name)
throws SQLException
{
throw new Error("not implemented");
}
/**
* {@inheritDoc}
*/
@Override
public DerbyDatabase setSequence(final Connection _con,
final String _name,
final long _value)
throws SQLException
{
// TODO Auto-generated method stub
throw new Error("not implemented");
}
/**
* Evaluates for given table name all information about the table and returns
* them as instance of {@link TableInformation}.
* This method overwrites the original method because the standard JDBC
* methods do not work for the Derby database to get unique keys.
*
* @param _con SQL connection
* @param _tableName name of SQL table for which the information is fetched
* @return instance of {@link TableInformation} with table information
* @throws SQLException if information about the table could not be fetched
* @see TableInformation
*/
/**
* Fetches all unique keys for this table. Instead of using the JDBC
* meta data functionality, a SQL statement on system tables are used,
* because the JDBC meta data functionality returns for unique keys
* internal names and not the real names. Also if a unique key includes
* also columns with null values, this unique keys are not included.
*
* @param _con Connection
* @param _sql Statement
* @param _cache4Name cache
* @throws SQLException if unique keys could not be fetched
*/
@Override
protected void initTableInfoUniqueKeys(final Connection _con,
final String _sql,
final Map _cache4Name)
throws SQLException
{
final String sqlStmt = new StringBuilder()
.append("select t.tablename as TABLE_NAME, c.CONSTRAINTNAME as INDEX_NAME, g.DESCRIPTOR as COLUMN_NAME")
.append(" from SYS.SYSTABLES t, SYS.SYSCONSTRAINTS c, SYS.SYSKEYS k, SYS.SYSCONGLOMERATES g ")
.append(" where t.TABLEID=c.TABLEID")
.append(" AND c.TYPE='U'")
.append(" AND c.CONSTRAINTID = k.CONSTRAINTID")
.append(" AND k.CONGLOMERATEID = g.CONGLOMERATEID")
.toString();
super.initTableInfoUniqueKeys(_con, sqlStmt, _cache4Name);
}
}