org.efaps.db.databases.AbstractDatabase Maven / Gradle / Ivy
/*
* 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;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy