org.efaps.db.databases.AbstractDatabase 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.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import org.apache.commons.lang.StringEscapeUtils;
import org.efaps.db.Context;
import org.efaps.db.databases.information.TableInformation;
import org.efaps.db.wrapper.SQLDelete;
import org.efaps.db.wrapper.SQLDelete.DeleteDefintion;
import org.efaps.db.wrapper.SQLInsert;
import org.efaps.db.wrapper.SQLPart;
import org.efaps.db.wrapper.SQLSelect;
import org.efaps.db.wrapper.SQLUpdate;
import org.efaps.init.INamingBinds;
import org.efaps.init.IeFapsProperties;
import org.efaps.update.util.InstallationException;
import org.efaps.util.EFapsException;
import org.efaps.util.cache.AbstractCache;
import org.efaps.util.cache.CacheReloadException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Abstract definition of database specific information and methods like alter
* of table columns.
*
* @author The eFaps Team
* @version $Id: AbstractDatabase.java 7483 2012-05-11 16:57:38Z [email protected] $
* @param derived DB class
*/
public abstract class AbstractDatabase>
{
/**
* Logging instance used in this class.
*/
private static final Logger LOG = LoggerFactory.getLogger(AbstractDatabase.class);
/**
* The enumeration defines the known column types in the database.
*/
public enum ColumnType {
/** integer number. */
INTEGER,
/** numeric/decimal numbers. */
DECIMAL,
/** real number. */
REAL,
/** short string. */
STRING_SHORT,
/** long string. */
STRING_LONG,
/** date and time. */
DATETIME,
/** binary large object. */
BLOB,
/** character large object. */
CLOB,
/** boolean. */
BOOLEAN,
}
protected static String SCHEMAPATTERN = null;
protected static String CATALOG = null;
static {
try {
final InitialContext initCtx = new InitialContext();
javax.naming.Context envCtx = null;
try {
envCtx = (javax.naming.Context) initCtx.lookup("java:/comp/env");
} catch (final NamingException e) {
AbstractDatabase.LOG.error("NamingException", e);
}
// for a build in jetty the context is different, try this before
// surrender
if (envCtx == null) {
envCtx = (javax.naming.Context) initCtx.lookup("java:comp/env");
}
final Map props = (Map) envCtx.lookup(INamingBinds.RESOURCE_CONFIGPROPERTIES);
if (props != null) {
SCHEMAPATTERN = (String) props.get(IeFapsProperties.DBSCHEMAPATTERN);
CATALOG = (String) props.get(IeFapsProperties.DBCATALOG);
}
} catch (final NamingException e) {
AbstractDatabase.LOG.error("NamingException", e);
}
}
/**
* The map stores the mapping between the column types used in eFaps and the
* database specific column types.
*
* @see #addMapping(ColumnType, String, String, String...)
* @see #getWriteSQLTypeName(ColumnType)
*/
private final Map writeColTypeMap
= new HashMap();
/**
* The map stores the mapping between column types used in the database and
* eFaps.
*
* @see #addMapping(ColumnType, String, String, String...)
* @see #getReadColumnTypes(String)
*/
private final Map> readColTypeMap
= new HashMap>();
/**
* The map stores the mapping between column types used in eFaps and the
* related null value select statement of the database.
*
* @see #addMapping(ColumnType, String, String, String...)
*/
private final Map nullValueColTypeMap
= new HashMap();
/**
* Caching for table information read from the SQL database.
*
* @see #getTableInformation(Connection, String)
*/
private final TableInfoCache cache = new TableInfoCache();
/**
* Method is used to determine if this DataBase is connected.
* It uses SQL statements against the database to determine if
* it is the right database using unique identifiers e.g. systemtables,
* version info etc.
*
* @param _connection Connection to be used foo analyze
* @return true if this database is connected
* @throws SQLException on error
*/
public abstract boolean isConnected(final Connection _connection)
throws SQLException;
/**
* Initializes the {@link #cache} for the table informations.
*
* @see #cache
*/
public void initialize()
{
initialize(AbstractDatabase.class);
}
/**
* Initializes the {@link #cache} for the table informations
* with given initializer.
* @param _class initializer class
* @see #cache
*/
public void initialize(final Class _class)
{
this.cache.initialize(_class);
}
/**
* Adds a new mapping for given eFaps column type used for mapping from and
* to the SQL database.
*
* @param _columnType column type within eFaps
* @param _writeTypeName SQL type name used to write (create new column
* within a SQL table)
* @param _nullValueSelect null value select used within the query if a
* link target could be a null (and so all
* selected values must null in the SQL statement
* for objects without this link)
* @param _readTypeNames list of SQL type names returned from the
* database meta data reading
* @see #readColTypeMap to map from an eFaps column type to a SQL type name
* @see #writeColTypeMap to map from a SQL type name to possible eFaps
* column types
*/
protected void addMapping(final ColumnType _columnType,
final String _writeTypeName,
final String _nullValueSelect,
final String... _readTypeNames)
{
this.writeColTypeMap.put(_columnType, _writeTypeName);
this.nullValueColTypeMap.put(_columnType, _nullValueSelect);
for (final String readTypeName : _readTypeNames) {
Set colTypes = this.readColTypeMap.get(readTypeName);
if (colTypes == null) {
colTypes = new HashSet();
this.readColTypeMap.put(readTypeName, colTypes);
}
colTypes.add(_columnType);
}
}
/**
*
* @param _tableName name of the table to insert
* @param _idCol column holding the id
* @param _newId true if a new id must be created; otherwise
* false
* @return new SQL insert statement
*/
public SQLInsert newInsert(final String _tableName,
final String _idCol,
final boolean _newId)
{
return new SQLInsert(_tableName, _idCol, _newId);
}
/**
*
* @param _tableName name of the table to insert
* @param _idCol column holding the id
* @param _id id to update
* @return new SQL insert statement
*/
public SQLUpdate newUpdate(final String _tableName,
final String _idCol,
final long _id)
{
return new SQLUpdate(_tableName, _idCol, _id);
}
/**
* @param _definition deleteDefinitions
* @return new SQLDelete
*/
public SQLDelete newDelete(final DeleteDefintion... _definition)
{
return new SQLDelete(_definition);
}
/**
* @return new SQL select statement
*/
public SQLSelect newSelect()
{
return new SQLSelect();
}
/**
* Returns for given column type the database vendor specific type name.
*
* @param _columnType column type for which the vendor specific column
* type should be returned
* @return SQL specific column type name
* @see #writeColTypeMap
* @see #addMapping used to define the map
*/
protected String getWriteSQLTypeName(final ColumnType _columnType)
{
return this.writeColTypeMap.get(_columnType);
}
/**
* Converts given SQL column type name in a set of eFaps column types. If
* no mapping is specified, a null is returned.
*
* @param _readTypeName SQL column type name read from the database
* @return set of eFaps column types (or null if not specified)
* @see #readColTypeMap
* @see #addMapping used to define the map
*/
public Set getReadColumnTypes(final String _readTypeName)
{
return this.readColTypeMap.get(_readTypeName);
}
/**
* Returns for given column type the database vendor specific null value
* select statement.
*
* @param _columnType column type for which the database vendor specific
* null value select is searched
* @return null value select
* @see #nullValueColTypeMap
*/
public String getNullValueSelect(final AbstractDatabase.ColumnType _columnType)
{
return this.nullValueColTypeMap.get(_columnType);
}
/**
* The method returns the database vendor specific value for the current
* time stamp.
*
* @return vendor specific string of the current time stamp
*/
public abstract String getCurrentTimeStamp();
/**
* Get the vendor specific Timestamp cast implementation.
*
* @param _isoDateTime dateTime that will be casted to an timestamp
* @return vendor specific implementation of timestamp
*/
public abstract String getTimestampValue(final String _isoDateTime);
/**
* Get the vendor specific Boolean cast implementation.
*
* @param _value boolean that will be casted to an number for oracle
* @return vendor specific implementation of boolean
*/
public abstract Object getBooleanValue(final Boolean _value);
/**
* The method implements a delete all of database user specific objects
* (e.g. tables, views etc...). The method is called before a complete
* rebuild is done.
*
* @param _con sql connection
* @throws SQLException if delete of the SQL data model failed
*/
public abstract void deleteAll(final Connection _con) throws SQLException;
/**
* The method tests, if a view with given name exists.
*
* @param _con sql connection
* @param _viewName name of view to test
* @return true if view exists, otherwise false
* @throws SQLException if the exist check failed
*/
public boolean existsView(final Connection _con,
final String _viewName)
throws SQLException
{
boolean ret = false;
final DatabaseMetaData metaData = _con.getMetaData();
// first test with lower case
final ResultSet rs = metaData.getTables(null, null, _viewName.toLowerCase(), new String[]{"VIEW"});
if (rs.next()) {
ret = true;
}
rs.close();
// then test with upper case
if (!ret) {
final ResultSet rsUC = metaData.getTables(null, null, _viewName.toUpperCase(), new String[]{"VIEW"});
if (rsUC.next()) {
ret = true;
}
rsUC.close();
}
return ret;
}
/**
* Deletes given view _name in this database.
*
* @param _con SQL connection
* @param _name name of the sequence
* @return this instance
* @throws SQLException if delete of the sequence failed
*/
public abstract T deleteView(final Connection _con,
final String _name)
throws SQLException;
/**
* The method tests, if a view with given name exists.
*
* @param _con sql connection
* @param _tableName name of table to test
* @return true if SQL table exists, otherwise false
* @throws SQLException if the exist check for the table failed
*/
public boolean existsTable(final Connection _con,
final String _tableName)
throws SQLException
{
boolean ret = false;
final DatabaseMetaData metaData = _con.getMetaData();
// first test with lower case
final ResultSet rs = metaData.getTables(null, null, _tableName.toLowerCase(), new String[]{"TABLE"});
if (rs.next()) {
ret = true;
}
rs.close();
// then test with upper case
if (!ret) {
final ResultSet rsUC = metaData.getTables(null, null, _tableName.toUpperCase(), new String[]{"TABLE"});
if (rsUC.next()) {
ret = true;
}
rsUC.close();
}
return ret;
}
/**
* Returns for given table name all information about the table and
* returns them as instance of {@link TableInformation}. The information is
* cached and NOT evaluated directly.
*
* @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
* @see #getRealTableInformation(Connection, String)
* @see #cache
*/
public TableInformation getCachedTableInformation(final String _tableName)
throws SQLException
{
return this.cache.get(_tableName.toUpperCase());
}
/**
* Evaluates for given table name all current information about the table
* and returns them as instance of {@link TableInformation}.
*
* @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
* @see #getCachedTableInformation(String)
*/
public TableInformation getRealTableInformation(final Connection _con,
final String _tableName)
throws SQLException
{
final TableInformation tableInfo = new TableInformation(_tableName.toUpperCase());
final Map tableInfos = new HashMap(1);
tableInfos.put(_tableName.toUpperCase(), tableInfo);
this.initTableInfoColumns(_con, null, tableInfos);
this.initTableInfoUniqueKeys(_con, null, tableInfos);
this.initTableInfoForeignKeys(_con, null, tableInfos);
return tableInfo;
}
/**
* A new SQL view _view is created. To create a correct view
* a dummy select on the value one is done (which will overwritten).
*
* @param _con SQL connection
* @param _view name of the view to create
* @return this instance
* @throws SQLException if the create of the table failed
* TODO: really neeeded? not referenced anymore...
*/
@SuppressWarnings("unchecked")
public T createView(final Connection _con,
final String _view)
throws SQLException
{
final Statement stmt = _con.createStatement();
try {
stmt.executeUpdate(new StringBuilder().append("create view ").append(_view)
.append(" as select 1").toString());
} finally {
stmt.close();
}
return (T) this;
}
/**
* Method to create new sequence _namein this database. The
* next time the value for sequence _name will return
* _value (by calling
* {@link #nextSequence(Connection, String)}).
*
* @param _con SQL connection
* @param _name name of the sequence
* @param _startValue start value for the sequence
* @return this instance
* @throws SQLException on error
*/
public abstract T createSequence(final Connection _con,
final String _name,
final long _startValue)
throws SQLException;
/**
* Deletes given sequence _name in this database.
*
* @param _con SQL connection
* @param _name name of the sequence
* @return this instance
* @throws SQLException if delete of the sequence failed
*/
public abstract T deleteSequence(final Connection _con,
final String _name)
throws SQLException;
/**
* Method to check for an existing Sequence in this Database.
*
* @param _con SQL connection
* @param _name name of the sequence
* @return true if exists, else false
* @throws SQLException on error
*/
public abstract boolean existsSequence(final Connection _con,
final String _name)
throws SQLException;
/**
* Method to get the next value from a given sequence in this database.
*
* @param _con SQL connection
* @param _name name of the sequence
* @return next value in sequence
* @throws SQLException on error
*/
public abstract long nextSequence(final Connection _con,
final String _name)
throws SQLException;
/**
* Method to define current value for sequence _name. The
* next time the value for sequence _name will return
* _value (by calling
* {@link #nextSequence(Connection, String)}).
*
* @param _con SQL connection
* @param _name name of the sequence
* @param _value value for the sequence
* @return this instance
* @throws SQLException on error
*/
public abstract T setSequence(final Connection _con,
final String _name,
final long _value)
throws SQLException;
/**
* A new SQL table with unique column ID is created.
*
* @param _con SQL connection
* @param _table name of the table to create
* @return this instance
* @throws SQLException if the create of the table failed
*/
public abstract T createTable(final Connection _con,
final String _table)
throws SQLException;
/**
* For a new created SQL table the column ID is update with a
* foreign key to a parent table.
*
* @param _con SQL connection
* @param _table name of the SQL table to update
* @param _parentTable name of the parent table
* @return this instance
* @throws InstallationException if the update of the table failed
*/
public T defineTableParent(final Connection _con,
final String _table,
final String _parentTable)
throws InstallationException
{
return addForeignKey(_con, _table, _table + "_FK_ID", "ID", _parentTable + "(ID)", false);
}
/**
* Defines a new created SQL table as auto incremented.
*
* @param _con SQL connection
* @param _table name of the SQL table to update
* @return this instance
* @throws SQLException if the update of the table failed
*/
public abstract T defineTableAutoIncrement(final Connection _con,
final String _table)
throws SQLException;
/**
* Adds a column to a SQL table.
*
* @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 (or 0 if not specified)
* @param _isNotNull true means that the column has no
* null values
* @return this instance
* @throws SQLException if the column could not be added to the tables
*/
//CHECKSTYLE:OFF
public T 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
final StringBuilder cmd = new StringBuilder();
cmd.append("alter table ").append(getTableQuote()).append(_tableName).append(getTableQuote()).append(' ')
.append("add ").append(getColumnQuote()).append(_columnName).append(getColumnQuote()).append(' ')
.append(getWriteSQLTypeName(_columnType));
if (_length > 0) {
cmd.append("(").append(_length);
if (_scale > 0) {
cmd.append(",").append(_scale);
}
cmd.append(")");
}
if (_defaultValue != null) {
cmd.append(" default ").append(_defaultValue);
}
if (_isNotNull) {
cmd.append(" not null");
}
// log statement
if (AbstractDatabase.LOG.isDebugEnabled()) {
AbstractDatabase.LOG.info(" ..SQL> " + cmd.toString());
}
// excecute statement
final Statement stmt = _con.createStatement();
try {
stmt.execute(cmd.toString());
} finally {
stmt.close();
}
@SuppressWarnings("unchecked")
final T ret = (T) this;
return ret;
}
/**
* Adds a new unique key to given table name.
*
* @param _con SQL connection
* @param _tableName name of table for which the unique key must be
* created
* @param _uniqueKeyName name of unique key
* @param _columns comma separated list of column names for which the
* unique key is created
* @return this instance
* @throws SQLException if the unique key could not be created
*/
public T addUniqueKey(final Connection _con,
final String _tableName,
final String _uniqueKeyName,
final String _columns)
throws SQLException
{
final StringBuilder cmd = new StringBuilder();
cmd.append("alter table ").append(_tableName).append(" ")
.append("add constraint ").append(_uniqueKeyName).append(" ")
.append("unique(").append(_columns).append(")");
// log statement
if (AbstractDatabase.LOG.isDebugEnabled()) {
AbstractDatabase.LOG.info(" ..SQL> " + cmd.toString());
}
// execute statement
final Statement stmt = _con.createStatement();
try {
stmt.execute(cmd.toString());
} finally {
stmt.close();
}
@SuppressWarnings("unchecked")
final T ret = (T) this;
return ret;
}
/**
* Adds a foreign key to given SQL table.
*
* @param _con SQL connection
* @param _tableName name of table for which the foreign key must be
* created
* @param _foreignKeyName name of foreign key to create
* @param _key key in the table (column name)
* @param _reference external reference (external table and column name)
* @param _cascade if the value in the external table is deleted,
* should this value also automatically deleted?
* @return this instance
* @throws InstallationException if foreign key could not be defined for
* SQL table
*/
public T addForeignKey(final Connection _con,
final String _tableName,
final String _foreignKeyName,
final String _key,
final String _reference,
final boolean _cascade)
throws InstallationException
{
final StringBuilder cmd = new StringBuilder()
.append("alter table ").append(_tableName).append(" ")
.append("add constraint ").append(_foreignKeyName).append(" ")
.append("foreign key(").append(_key).append(") ")
.append("references ").append(_reference);
if (_cascade) {
cmd.append(" on delete cascade");
}
// log statement
if (AbstractDatabase.LOG.isDebugEnabled()) {
AbstractDatabase.LOG.info(" ..SQL> " + cmd.toString());
}
// execute statement
try {
final Statement stmt = _con.createStatement();
try {
stmt.execute(cmd.toString());
} finally {
stmt.close();
}
} catch (final SQLException e) {
throw new InstallationException("Foreign key could not be created. SQL statement was:\n"
+ cmd.toString(), e);
}
@SuppressWarnings("unchecked")
final T ret = (T) this;
return ret;
}
/**
* Adds a new check key to given SQL table.
*
* @param _con SQL connection
* @param _tableName name of the SQL table for which the check key must
* be created
* @param _checkKeyName name of check key to create
* @param _condition condition of the check key
* @throws SQLException if check key could not be defined for SQL table
*/
public void addCheckKey(final Connection _con,
final String _tableName,
final String _checkKeyName,
final String _condition)
throws SQLException
{
final StringBuilder cmd = new StringBuilder()
.append("alter table ").append(_tableName).append(" ")
.append("add constraint ").append(_checkKeyName).append(" ")
.append("check(").append(_condition).append(")");
// log statement
if (AbstractDatabase.LOG.isDebugEnabled()) {
AbstractDatabase.LOG.info(" ..SQL> " + cmd.toString());
}
// excecute statement
final Statement stmt = _con.createStatement();
try {
stmt.execute(cmd.toString());
} finally {
stmt.close();
}
}
/**
* Returns the quote used to select tables.
*
* @return always empty string as default
*/
public String getTableQuote()
{
return "";
}
/**
* Returns the quote used to select columns.
*
* @return always empty string as default
*/
public String getColumnQuote()
{
return "";
}
/**
* @param _part Part the SQL is needed for
* @return String
*/
public String getSQLPart(final SQLPart _part)
{
return _part.getDefaultValue();
}
/**
* @param _value STring value to be escaped
* @return escaped value in "'"
*/
public String escapeForWhere(final String _value)
{
return "'" + StringEscapeUtils.escapeSql(_value) + "'";
}
/**
* This integer is used for the maximum numbers of Values inside an
* expression.
* The value is used in the OneRounQuery. The SQL statement looks like
* "SELECT...WHERE..IN (val1,val2,val3,...valn)" The integer is the maximum
* value for n before making a new Select.
*
* @return max Number of Value in an Expression, -1 if no max is known
*/
public int getMaxExpressions()
{
return -1;
}
/**
* A new id for given column of a SQL table is returned (e.g. with
* sequences). This abstract class always throws a SQLException, because
* for default, it is not needed to implement (only if the JDBC drive does
* not implement method 'getGeneratedKeys' for java.sql.Statements).
*
* @param _con sql connection
* @param _table sql table for which a new id must returned
* @param _column sql table column for which a new id must returned
* @return new id number
* @throws SQLException always, because method itself is not implemented not
* not allowed to call
*/
public long getNewId(final Connection _con,
final String _table,
final String _column)
throws SQLException
{
throw new SQLException("method 'getNewId' not imlemented");
}
/**
* The method returns if a database implementation supports to get
* generated keys while inserting a new line in a SQL table.
*
* @return always false because not implemented in this class
*/
public boolean supportsGetGeneratedKeys()
{
return false;
}
/**
* The method returns if a database implementation support to get multiple
* auto generated keys. If defined to true, the insert is done with
* defined column names for the auto generated columns. Otherwise only
* {@link java.sql.Statement#RETURN_GENERATED_KEYS} is given for the
* insert.
*
* @return always false because not implemented in this class
* @see #supportsGetGeneratedKeys
*/
public boolean supportsMultiGeneratedKeys()
{
return false;
}
/**
* The method returns if a database implementation supports for blobs
* binary input stream supports the available method or not.
*
* @return always false because not implemented in this class
* @see #supportsBinaryInputStream
*/
public boolean supportsBlobInputStreamAvailable()
{
return false;
}
/**
* The method returns if a database implementation supports directly binary
* stream for result sets (instead of using first blobs).
*
* @return always false because not implemented in this class
* @see #supportsBlobInputStreamAvailable
*/
public boolean supportsBinaryInputStream()
{
return false;
}
/**
* Returns true if a database could handle big transactions used
* within the eFaps updates.
*
* @return always true because normally a database should implement
* big transactions
*/
public boolean supportsBigTransactions()
{
return true;
}
/**
* Instantiate the given DB class name and returns them.
*
* @param _dbClassName name of the class to instantiate
* @return new database definition instance
* @throws ClassNotFoundException if class for the DB is not found
* @throws InstantiationException if DB class could not be instantiated
* @throws IllegalAccessException if DB class could not be accessed
*/
public static AbstractDatabase findByClassName(final String _dbClassName)
throws ClassNotFoundException, InstantiationException, IllegalAccessException
{
return (AbstractDatabase) Class.forName(_dbClassName).newInstance();
}
/**
* Fetches all table name for all tables and views. If a SQL statement
* is given, this SQL statement is used instead of using the JDBC meta data
* methods. The SQL select statement must define this column
*
* TABLE_NAME for the real name of the table.
*
* @param _con SQL connection
* @param _sql SQL statement which must be executed if the JDBC
* functionality does not work (or null if JDBC meta
* data is used to fetch all tables and views)
* @param _cache4Name map used to fetch depending on the table name the
* related table information
* @throws SQLException if information could not be fetched from the data
* base
*/
protected void initTableInfo(final Connection _con,
final String _sql,
final Map _cache4Name)
throws SQLException
{
final ResultSet rs = (_sql == null)
? _con.getMetaData().getTables(null, null, "%", new String[]{"TABLE", "VIEW"})
: _con.createStatement().executeQuery(_sql);
try {
while (rs.next()) {
final String tableName = rs.getString("TABLE_NAME").toUpperCase();
_cache4Name.put(tableName, new TableInformation(tableName));
}
} finally {
rs.close();
}
}
/**
* Fetches all unique keys for all tables. If a SQL statement is given,
* this SQL statement is used instead of using the JDBC meta data methods.
* The SQL select statement must define this four columns
*
* TABLE_NAME for the real name of the table,COLUMN_NAME for the name of a column,TYPE_NAME for the name of the column type,COLUMN_SIZE for the size of the column,DECIMAL_DIGITS for the count of decimal digits
* (if the TYPE_NAME is number) andIS_NULLABLE if the column could have no value
* (with value "NO" if no null value is allowed).
*
* @param _con SQL connection
* @param _sql SQL statement which must be executed if the JDBC
* functionality does not work (or null if JDBC meta
* data is used to fetch the table columns)
* @param _cache4Name map used to cache depending on the table name the
* related table information
* @throws SQLException if column information could not be fetched
*/
protected void initTableInfoColumns(final Connection _con,
final String _sql,
final Map _cache4Name)
throws SQLException
{
final ResultSet rsc = (_sql == null)
? _con.getMetaData().getColumns(null, null, "%", "%")
: _con.createStatement().executeQuery(_sql);
try {
while (rsc.next()) {
final String tableName = rsc.getString("TABLE_NAME").toUpperCase();
if (_cache4Name.containsKey(tableName)) {
final String colName = rsc.getString("COLUMN_NAME").toUpperCase();
final String typeName = rsc.getString("TYPE_NAME").toLowerCase();
final Set colTypes
= AbstractDatabase.this.getReadColumnTypes(typeName);
if (colTypes == null) {
throw new SQLException("read unknown column type '" + typeName + "'");
}
final int size = rsc.getInt("COLUMN_SIZE");
final int scale = rsc.getInt("DECIMAL_DIGITS");
final boolean isNullable = !"NO".equalsIgnoreCase(rsc.getString("IS_NULLABLE"));
_cache4Name.get(tableName).addColInfo(colName, colTypes, size, scale, isNullable);
}
}
} finally {
rsc.close();
}
}
/**
* Fetches all unique keys for all tables. If a SQL statement is given,
* this SQL statement is used instead of using the JDBC meta data methods.
* The SQL select statement must define this four columns
*
* TABLE_NAME for the real name of the table,INDEX_NAME for the real name of the unique key
* name,COLUMN_NAME for the name of a column within the
* unique key andORDINAL_POSITION for the position of the column
* name within the unique key.
* If more than one column is used to define the unique key, one line for
* each column name with same index name must be used.
*
* @param _con SQL connection
* @param _sql SQL statement which must be executed if the JDBC
* functionality does not work (or null if JDBC meta
* data is used to fetch the unique keys)
* @param _cache4Name map used to fetch depending on the table name the
* related table information
* @throws SQLException if unique keys could not be fetched
*/
protected void initTableInfoUniqueKeys(final Connection _con,
final String _sql,
final Map _cache4Name)
throws SQLException
{
final ResultSet rsu = (_sql == null)
? _con.getMetaData().getIndexInfo(null, null, "%", true, false)
: _con.createStatement().executeQuery(_sql);
try {
while (rsu.next()) {
final String tableName = rsu.getString("TABLE_NAME").toUpperCase();
if (_cache4Name.containsKey(tableName)) {
final String ukName = rsu.getString("INDEX_NAME").toUpperCase();
final String colName = rsu.getString("COLUMN_NAME").toUpperCase();
final int colIdx = rsu.getInt("ORDINAL_POSITION");
_cache4Name.get(tableName).addUniqueKeyColumn(ukName, colIdx, colName);
}
}
} finally {
rsu.close();
}
}
/**
* Fetches all foreign keys for all tables. If a SQL statement is given,
* this SQL statement is used instead of using the JDBC meta data methods.
* The SQL select statement must define this six columns
*
* TABLE_NAME for the real name of the table,FK_NAME for the real name of the foreign key
* name,FKCOLUMN_NAME for the name of the column for
* which the foreign key is defined,PKTABLE_NAME for the name of the referenced
* table,PKCOLUMN_NAME for the name of column within the
* referenced table andDELETE_RULE defining the rule what happens in
* the case a row of the table is deleted (with value
* {@link DatabaseMetaData#importedKeyCascade} in the case the delete
* is cascaded).
*
* @param _con SQL connection
* @param _sql SQL statement which must be executed if the JDBC
* functionality does not work (or null if JDBC meta
* data is used to fetch the foreign keys)
* @param _cache4Name map used to fetch depending on the table name the
* related table information
* @throws SQLException if foreign keys could not be fetched
*/
protected void initTableInfoForeignKeys(final Connection _con,
final String _sql,
final Map _cache4Name)
throws SQLException
{
final ResultSet rsf = (_sql == null)
? _con.getMetaData().getImportedKeys(null, null, "%")
: _con.createStatement().executeQuery(_sql);
try {
while (rsf.next()) {
final String tableName = rsf.getString("TABLE_NAME").toUpperCase();
if (_cache4Name.containsKey(tableName)) {
final String fkName = rsf.getString("FK_NAME").toUpperCase();
final String colName = rsf.getString("FKCOLUMN_NAME").toUpperCase();
final String refTableName = rsf.getString("PKTABLE_NAME").toUpperCase();
final String refColName = rsf.getString("PKCOLUMN_NAME").toUpperCase();
final boolean cascade = rsf.getInt("DELETE_RULE") == DatabaseMetaData.importedKeyCascade;
_cache4Name.get(tableName).addForeignKey(fkName, colName, refTableName, refColName, cascade);
}
}
} finally {
rsf.close();
}
}
/**
* Implements the cache for the table information.
*
* @see AbstractDatabase#cache
* @see TableInformation
*/
private class TableInfoCache
extends AbstractCache
{
/**
* {@inheritDoc}
*/
@Override
protected void readCache(final Map _cache4Id,
final Map _cache4Name,
final Map _cache4UUID)
throws CacheReloadException
{
try {
final Connection con = Context.getThreadContext().getConnectionResource().getConnection();
AbstractDatabase.this.initTableInfo(con, null, _cache4Name);
AbstractDatabase.this.initTableInfoColumns(con, null, _cache4Name);
AbstractDatabase.this.initTableInfoUniqueKeys(con, null, _cache4Name);
AbstractDatabase.this.initTableInfoForeignKeys(con, null, _cache4Name);
} catch (final SQLException e) {
throw new CacheReloadException("cache for table information could not be read", e);
} catch (final EFapsException e) {
throw new CacheReloadException("cache for table information could not be read", e);
}
}
}
public String getConstrainName(final String _name)
throws IOException
{
return _name;
}
}