com.im.commons.db
Class AbstractDatabasePlatform

java.lang.Object
  org.springframework.dao.support.DaoSupport
      org.springframework.jdbc.core.support.JdbcDaoSupport
          com.im.commons.db.AbstractDatabasePlatform

All Implemented Interfaces:

DatabasePlatform, DatabasePlatformX, InitializingBean

public abstract class AbstractDatabasePlatform
extends JdbcDaoSupport
implements DatabasePlatformX

Provides information for interacting with a database.

This information includes:

1. The correct SQL for performing specific operations
2. Information about the tables, columns, etc. in the database

The responsibility of this class is to give you information about the underlying database. It should not be performing any data manipulation operations on IJC data (this is the responsibility of TableManager).

To support a new database subclass this to provide the appropriate information for your database.

This class also makes available an instance of DBDatabaseInfo which is provides static information about the database that can be used for information that does not need the database to be queries each time.

See Also:

DBDatabaseInfo

Field Summary

Fields inherited from class org.springframework.dao.support.DaoSupport

logger

Fields inherited from interface com.im.commons.db.DatabasePlatformX

IJC_AUTHORITIES_TABLE_NAME, IJC_CHANGE_LOG_TABLE_NAME, IJC_INVOCATION_LOG_TABLE_NAME, IJC_ITEM_INFO_TABLE_NAME, IJC_ITEM_USER_TABLE_NAME, IJC_SCHEMA_AUTHORITIES_TABLE_NAME, IJC_SCHEMA_TABLE_NAME, IJC_SECURITY_AUTHORITIES_TABLE_NAME, IJC_SECURITY_INFO_TABLE_NAME, IJC_SECURITY_USERS_TABLE_NAME, IJC_USER_TABLE_NAME, IJC_VIEWS_SEQUENCE_NAME, IJC_VIEWS_TABLE_NAME

Fields inherited from interface com.im.commons.db.DatabasePlatform

IJC_SCHEMA_OTHER_TABLE_NAME, NAME_PRIMARY_KEY_CONSTRAINT

Constructor Summary

protected

AbstractDatabasePlatform(BaseDataSource dds)

Method Summary

void	addPseudoColumnInfos(List<DBColInfo> cols, String columnPattern) Add any magical pseudo columns such as Oracle's ROWID columns
DBColInfo	buildColumnInfoForColumn(String dbSchemaName, String tableName, String columnName) Build the DBColInfos for the particlaar column.
Map<String,List<DBColInfo>>	buildColumnInfosForSchema(String dbSchemaName)
List<DBColInfo>	buildColumnInfosForTable(String dbSchemaName, String tableName) Build the DBColInfos for the table.
protected Map<String,List<DBConstraintInfo>>	buildConstraintInfosForSchema(String dbSchemaName) Build constraint info for the specified schema.
protected List<DBConstraintInfo>	buildConstraintInfosImpl(String dbSchemaName, String tableName, List<DBColInfo> collsInTable) Build constraint info for the specified table.
List<DBForeignKeyInfo>	buildExportedForeignKeyInfoForTable(String dbSchemaName, String tableName) Build exported foreign key info
List<DBForeignKeyInfo>	buildImportedForeignKeyInfoForTable(String dbSchemaName, String tableName) Build imported foreign key info
List<DBIndexInfo>	buildIndexInfoForTable(String schemaName, String tableName) Build index info for the specified table
protected CaseInsensitiveMap<List<DBIndexInfo>>	buildIndexInfoImpl(String schemaName, String tableName)
protected Map<String,List<DBIndexInfo>>	buildIndexInfosForSchema(String schemaName)
Set<String>	buildJChemPropertyTableNames() Build the known JChem property table names.
List<DBSequenceInfo>	buildSequenceInfo(String schema) Default implementation assumes sequences are not supported and returns an empty list
DBSequenceInfo	buildSequenceInfo(String schema, String seqName) Default implementation assumes sequences are not supported and returns null
DBTableInfo	buildTableInfo(String dbSchemaName, String tableName, LazyLoader lazyLoader, DFFeedback feedback) Build the full table info for this table.
DBTableInfo	buildTableInfoFull(String dbSchemaName, String tableName, LazyLoader lazyLoader, DFFeedback feedback)
Map<String,DBTableInfo>	buildTableInfos(String dbSchemaName, List<String> tablesToInit, LazyLoader lazyLoader, DFFeedback feedback) Build all table infos for the specified schema
Map<String,DBTableInfo>	buildTableInfosEager(String dbSchemaName, DFFeedback feedback)
Map<String,DBTableInfo>	buildTableInfosLazy(String dbSchemaName, LazyLoader lazyLoader, DFFeedback feedback)
void	checkSchemaExistence(String schemaId) Checks whether an schema exists in the IJC metadata registry (IJC_SCHEMA table) or not also checks whether the database/ IJC_SCHEMA table exists or not.
static DatabasePlatform	create(BaseDataSource dds)
static DatabasePlatform	create(BaseDataSource dds, boolean init) Create method with extra init param that can be used from unit tests.
static DatabasePlatform	create(String driver, String url, String user, String password, String schema, String catalog, Properties properties, boolean init)
protected DBDatabaseInfo	createDatabaseInfo(Properties sqlTemplates)
protected abstract DBDatabaseInfo	createDatabaseInfo(Properties sqlTemplates, String dbIdentifierQuoteString, String cartOwner, String cartridgeEnvironment)
static DatabasePlatform	createFromDataSource(BaseDataSource dds)
String	createIdColumnNameFromTableName(String tableName)
protected LobHandler	createLobHandler()
protected NativeJdbcExtractor	createNativeJdbcExtractor()
protected Map<String,List<DBColInfo>>	doBuildColumnInfosImpl(Connection con, String dbSchemaName, String tableName, String columnName)
protected Map<String,List<DBColInfo>>	doBuildColumnInfosImpl(String dbSchemaName, String tableName, String columnName)
protected boolean	excludeTableFromTableNames(String table)
protected boolean	excludeTableFromViewNames(String table)
void	executeDDL(String[] sql, boolean log) Execute these DDL statements
void	executeDDL(String sql, boolean log) Execute this DDL statement
protected String	fetchIdentifierQuoteString()
protected Set<String>	findJChemPropertyTables()
protected String[]	findPrimaryKeyColumnNames(DatabaseMetaData meta, String schema, String table)
String	getAddAuthoritySql()
String	getAddUserSql()
String	getCallType()
String	getCartridgeOwner() Get the user that owns the JChem cartridge functions.
String	getCatalog()
String	getColumnListCommaSeparated(String[] columns)
protected abstract Properties	getCustomSqlTemplates()
DBDatabaseInfo	getDBInfo()
String	getDefaultSchemaName()
String	getDeleteAuthoritiesSql()
String	getDeleteUserSql()
LobHandler	getLobHandler()
Set<String>	getMetaTableNames()
List<String>	getMetaTableNamesSorted()
NativeJdbcExtractor	getNativeJdbcExtractor()
List<String>	getSchemaNames() Get the list of schema names that are accessible to the user.
String	getSetEnabledSql()
protected Properties	getSqlTemplates() The templates with which to generate SQL statements appropriate for your database type.
String[]	getTableNames(String schema) Get the table names for the specified schema
TermEncoder	getTermEncoder()
DataSourceTransactionManager	getTransactionManager()
String	getUpdatePasswordSql()
List<String>	getUsedSchemas() only used on sequences, please do not use this method
String[]	getViewNames(String schema) Get the view names for the specified schema
protected void	initCatalogFromConnection()
protected void	initDao()
boolean	isProjectDatabase()
protected Properties	loadPropertiesFromResource(String resource)
protected void	preInitValidate()
protected abstract void	readPropertyTables(Set<String> tables, ResultSet rs)
protected void	readPropertyTablesNoSchema(Set<String> tables, ResultSet rs)
protected void	readPropertyTablesWithSchema(Set<String> tables, ResultSet rs)
Object	readValue(ResultSet rs, String columnName, Class columnClass, int jdbcType) Read the value from the ResultSet.
void	setCallType(String callType)
void	setUsedSchemas(List<String> usedSchemas)
void	shutdown() Default is to do nothing.
protected void	specifyCartridgePassword(String cartOwner)
String	sqlAddColumn(String schemaPlusTable, String colName, String colClass, String nativeType, String columnDefinition, String defaultValue, boolean isNotNull)
String	sqlAddForeignKey(String fkName, String parentSchemaPlusTable, String dependentSchemaPlusTable, String[] parentCols, String[] dependentCols, String onDeleteRule, String onUpdateRule)
String	sqlAddIndex(String indexName, String indexType, String schemaPlusTable, String[] columns, boolean[] asc) Add an index to the table
String	sqlAddUniqueConstraint(String consName, String schemaPlusTable, String[] columns) Add unique constraint for some columns in the table.
String	sqlAlterColumnName(String schemaPlusTable, String oldName, String newName, String definition)
String	sqlAlterColumnSize(String schemaPlusTable, String columnName, String columnDefinition)
String	sqlAlterTableName(String oldSchemaPlusTable, String newSchemaPlusTable)
String	sqlCount(SchemaQualifiedName schemaPlusTable)
String	sqlCreateIJCAuthoritiesTable()
String[]	sqlCreateIJCChangesLogTable()
String	sqlCreateIJCGenericsTable()
String[]	sqlCreateIJCInvocationLogTable()
String	sqlCreateIJCItemInfoTable()
String	sqlCreateIJCItemUserTable()
String	sqlCreateIJCSchemaAuthoritiesTable()
String	sqlCreateIJCSchemaTable()
String	sqlCreateIJCSecurityAuthoritiesAuthoritiesFK()
String	sqlCreateIJCSecurityInfoTable()
String	sqlCreateIJCUserTable()
String[]	sqlCreateIJCViewsTable()
String[]	sqlCreateSecurityTables() Get the SQL that creates the database-based security tables.
String[]	sqlCreateSecurityTablesExternalConstraints() Constraints from the security tables to the other tables.
String	sqlCreateSequence(String schemaPlusSequenceName)
String	sqlCreateTable(String schemaPlusTable, List<DBDatabaseInfo.NativeType> columnDefs, List<String> columnNames) Creates a table with the given name and the fields.
String	sqlCreateTable(String schemaPlusTable, String idColumnName, String extraCols)
String	sqlCreateView(String schemaPlusView, String sql)
String	sqlDeleteRow(String schemaPlusTable, String whereClause) Generate SQL to delete a row.
String	sqlDropColumn(String schemaPlusTable, String columnName)
protected String	sqlDropConstraint(String schemaPlusTable, String constraintName)
String	sqlDropForeignKey(String schemaPlusTable, String fkName)
String	sqlDropIndex(String schemaPlusTable, String indexName)
String	sqlDropPrimaryConstraint(String schemaPlusTable, String consName, String[] columns)
String	sqlDropSequence(String schemaPlusSequence)
String	sqlDropTable(String schemaPlusTable)
String	sqlDropUniqueConstraint(String schemaPlusTable, String consName, String[] columns)
String	sqlDropView(String schemaPlusView)
String	sqlFullColumnDefinition(String colClassName, String nativeType, String columnDefinition, String defaultValue, boolean isNotNull) Generate the full column definition, including the defintion of any default value and the NULL/NOT NULL.
String	sqlGenericItemDelete()
String	sqlGenericItemInsert()
String	sqlGenericItemUpdate()
String	sqlGetDBSchemaVersion()
String	sqlGetItemsByType()
protected String	sqlGetTablesWithPropertyColumns()
String	sqlInsertRow(SchemaQualifiedName schemaPlusTable, List columnNames) Creates insert row sql for a prepared statement of the form INSERT INTO table_name (col_1, col_2...) VALUES (?,?...)
String	sqlInsertRowDefaults(SchemaQualifiedName schemaPlusTable, String[] idColumnNames) Creates insert row sql that inserts the default value for these columns.
protected String	sqlJChemCartridgeEnvironment()
protected String	sqlJChemCartridgeOwner()
String	sqlLastId(SchemaQualifiedName schemaPlusTable)
String	sqlQueryForIds(String schemaPlusTable, String[] idColumnNames, int limit)
String	sqlQueryForIdsWithSort(String schemaPlusTable, String[] idColumnNames, int limit, SortColumn[] sortCols)
String	sqlRetrieveSequence(String schemPlusSequence)
String	sqlSchemaMetaDataInsert()
String	sqlSchemaMetaDataNextIndex()
String	sqlSelect(String schemaPlusTable, String columns, String idColumnName)
String	sqlSelectDistinct(SchemaQualifiedName schemaPlusTable, String[] columnNames)
String	sqlSelectDuplicateRows(String schemaPlusTable, String[] columns)
SQLSelectStatement	sqlSelectInList(SchemaQualifiedName schemaPlusTable, List<SQLSelectElement> selectElements, String inClauseColName, DBDatabaseInfo.ColumnSQLType inClauseColType, Collection ids)
SQLSelectStatement	sqlSelectPS(SchemaQualifiedName schemaPlusTable, List<SQLSelectElement> selectElements, String idCol) Creates SQL for a select statement that uses a PreparedStatement.
String	sqlSetDBSchemaVersion(boolean insert)
String	sqlSort(List ids, SortColumn[] sortCols, SchemaQualifiedName schemaPlusTable, String selectCol, DBDatabaseInfo.ColumnSQLType selectColType)
String[]	sqlUpdateIJCViewsTable()
String	sqlUpdateRow(SchemaQualifiedName schemaPlusTable, String updateClause, String whereClause)
String	sqlUpgradeCommand(String version, String key)
String	sqlViewMetaDataInsert()
String	sqlViewMetaDataNextIndex()
void	start() Starts the platform by connecting to the database.
static boolean	stringArrayContains(String[] hayStack, String needle)
DataAccessException	translateSQLException(String message, String sql, SQLException ex)
void	updateHeartbeat(String userName, String schemaId) Updates when the last time user accessed the database for purpose of changing the data

Methods inherited from class org.springframework.jdbc.core.support.JdbcDaoSupport

checkDaoConfig, createJdbcTemplate, getConnection, getDataSource, getExceptionTranslator, getJdbcTemplate, initTemplateConfig, releaseConnection, setDataSource, setJdbcTemplate

Methods inherited from class org.springframework.dao.support.DaoSupport

afterPropertiesSet

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Methods inherited from interface com.im.commons.db.DatabasePlatformX

sqlAlterColumnDefault, sqlAlterColumnDefault

Methods inherited from interface com.im.commons.db.DatabasePlatform

buildConstraintInfosForTable, getDataSource, getJdbcTemplate, sqlAlterColumnRequired, sqlNow, sqlRebuildIndex

Constructor Detail

AbstractDatabasePlatform

protected AbstractDatabasePlatform(BaseDataSource dds)

Method Detail

fetchIdentifierQuoteString

protected String fetchIdentifierQuoteString()

create

public static DatabasePlatform create(String driver,
                                      String url,
                                      String user,
                                      String password,
                                      String schema,
                                      String catalog,
                                      Properties properties,
                                      boolean init)

createFromDataSource

public static DatabasePlatform createFromDataSource(BaseDataSource dds)

create

public static DatabasePlatform create(BaseDataSource dds)

create

public static DatabasePlatform create(BaseDataSource dds,
                                      boolean init)

Create method with extra init param that can be used from unit tests.

Parameters:

dds - The BaseDataSource

init - If true DAO is initialized

Returns:

The DatabasePlatform

isProjectDatabase

public final boolean isProjectDatabase()

Specified by:

isProjectDatabase in interface DatabasePlatform

buildJChemPropertyTableNames

public Set<String> buildJChemPropertyTableNames()

Build the known JChem property table names. These are cached until this method is called again. When the property tables change this method should be called to refresh the cache.

Specified by:

buildJChemPropertyTableNames in interface DatabasePlatform

Returns:

The names of JChem property tables.

getMetaTableNames

public Set<String> getMetaTableNames()

Specified by:

getMetaTableNames in interface DatabasePlatform

Returns:

The names of tables, that hold the schema metadata information

getMetaTableNamesSorted

public List<String> getMetaTableNamesSorted()

Specified by:

getMetaTableNamesSorted in interface DatabasePlatformX

shutdown

public void shutdown()

Default is to do nothing. Override if something needs to be done

Specified by:

shutdown in interface DatabasePlatform

getTransactionManager

public DataSourceTransactionManager getTransactionManager()

Specified by:

getTransactionManager in interface DatabasePlatform

getNativeJdbcExtractor

public NativeJdbcExtractor getNativeJdbcExtractor()

Specified by:

getNativeJdbcExtractor in interface DatabasePlatformX

createNativeJdbcExtractor

protected NativeJdbcExtractor createNativeJdbcExtractor()

getLobHandler

public LobHandler getLobHandler()

Specified by:

getLobHandler in interface DatabasePlatform

Returns:

The SpringFramework LobHandler for this type of database

createLobHandler

protected LobHandler createLobHandler()

getDefaultSchemaName

public String getDefaultSchemaName()

Specified by:

getDefaultSchemaName in interface DatabasePlatform

Returns:

The default schema to be used

getCatalog

public String getCatalog()

Specified by:

getCatalog in interface DatabasePlatformX

getSqlTemplates

protected Properties getSqlTemplates()

The templates with which to generate SQL statements appropriate for your database type.

start

public void start()
           throws InitializationException

Starts the platform by connecting to the database. Call to catch errors early. Call shutdown() to release resources allocated by "start".

Specified by:

start in interface DatabasePlatform

Throws:

InitializationException

preInitValidate

protected void preInitValidate()

initDao

protected void initDao()
                throws InitializationException

Overrides:

initDao in class DaoSupport

Throws:

InitializationException

initCatalogFromConnection

protected void initCatalogFromConnection()
                                  throws SQLException

Throws:

SQLException

createDatabaseInfo

protected DBDatabaseInfo createDatabaseInfo(Properties sqlTemplates)

specifyCartridgePassword

protected void specifyCartridgePassword(String cartOwner)

createDatabaseInfo

protected abstract DBDatabaseInfo createDatabaseInfo(Properties sqlTemplates,
                                                     String dbIdentifierQuoteString,
                                                     String cartOwner,
                                                     String cartridgeEnvironment)

getDBInfo

public DBDatabaseInfo getDBInfo()

Specified by:

getDBInfo in interface DatabasePlatform

getCustomSqlTemplates

protected abstract Properties getCustomSqlTemplates()
                                             throws IOException

Throws:

IOException

loadPropertiesFromResource

protected Properties loadPropertiesFromResource(String resource)
                                         throws IOException

Throws:

IOException

createIdColumnNameFromTableName

public String createIdColumnNameFromTableName(String tableName)

Specified by:

createIdColumnNameFromTableName in interface DatabasePlatformX

sqlCreateTable

public String sqlCreateTable(String schemaPlusTable,
                             List<DBDatabaseInfo.NativeType> columnDefs,
                             List<String> columnNames)

Creates a table with the given name and the fields. The list of columnDefs cannot be empty or null; the table must contain at least one column. The list of column names must be the same size as column definitions; name at index "i" will be used for column defined by definition at index "i".

Specified by:

sqlCreateTable in interface DatabasePlatform

Specified by:

sqlCreateTable in interface DatabasePlatformX

columnDefs - The column definitions

columnNames - The column names

Returns:

SQL statement to create the table

getSchemaNames

public List<String> getSchemaNames()

Description copied from interface: DatabasePlatform

Get the list of schema names that are accessible to the user. If DB does not support schemas then an empty list is returned.

Specified by:

getSchemaNames in interface DatabasePlatform

Returns:

The names of the schemas

getUsedSchemas

public List<String> getUsedSchemas()

only used on sequences, please do not use this method

Specified by:

getUsedSchemas in interface DatabasePlatform

setUsedSchemas

public void setUsedSchemas(List<String> usedSchemas)

getTableNames

public String[] getTableNames(String schema)

Description copied from interface: DatabasePlatform

Get the table names for the specified schema

Specified by:

getTableNames in interface DatabasePlatform

Parameters:

schema - The schema to look in. Should be null if the database does not support schemas

Returns:

The tables in this schema.

getViewNames

public String[] getViewNames(String schema)

Description copied from interface: DatabasePlatform

Get the view names for the specified schema

Specified by:

getViewNames in interface DatabasePlatform

Parameters:

schema - The schema to look in. Should be null if the database does not support schemas

Returns:

The views in this schema.

excludeTableFromTableNames

protected boolean excludeTableFromTableNames(String table)

excludeTableFromViewNames

protected boolean excludeTableFromViewNames(String table)

findPrimaryKeyColumnNames

protected String[] findPrimaryKeyColumnNames(DatabaseMetaData meta,
                                             String schema,
                                             String table)
                                      throws SQLException

Throws:

SQLException

addPseudoColumnInfos

public void addPseudoColumnInfos(List<DBColInfo> cols,
                                 String columnPattern)

Add any magical pseudo columns such as Oracle's ROWID columns

Parameters:

cols -

columnPattern - Pattern for the columns. Can be null, in which case all pseudo columns are added. If a pattern is present then only pseudo columns conforming to the pattern are added. NOTE: columnPattern currently only supports a specific column name. Wildcards are not supported

buildColumnInfosForSchema

public Map<String,List<DBColInfo>> buildColumnInfosForSchema(String dbSchemaName)

buildColumnInfosForTable

public List<DBColInfo> buildColumnInfosForTable(String dbSchemaName,
                                                String tableName)

Description copied from interface: DatabasePlatform

Build the DBColInfos for the table.

Specified by:

buildColumnInfosForTable in interface DatabasePlatform

Parameters:

dbSchemaName - The schema name

tableName - The table name

Returns:

A list of DBColInfos for the table

buildColumnInfoForColumn

public DBColInfo buildColumnInfoForColumn(String dbSchemaName,
                                          String tableName,
                                          String columnName)

Description copied from interface: DatabasePlatform

Build the DBColInfos for the particlaar column.

Specified by:

buildColumnInfoForColumn in interface DatabasePlatform

Parameters:

dbSchemaName - The schema name

tableName - The table name

columnName - The column name to match

Returns:

The DBColInfo

doBuildColumnInfosImpl

protected Map<String,List<DBColInfo>> doBuildColumnInfosImpl(String dbSchemaName,
                                                             String tableName,
                                                             String columnName)

doBuildColumnInfosImpl

protected Map<String,List<DBColInfo>> doBuildColumnInfosImpl(Connection con,
                                                             String dbSchemaName,
                                                             String tableName,
                                                             String columnName)
                                                      throws SQLException

Throws:

SQLException

buildSequenceInfo

public List<DBSequenceInfo> buildSequenceInfo(String schema)

Default implementation assumes sequences are not supported and returns an empty list

Specified by:

buildSequenceInfo in interface DatabasePlatform

Parameters:

schema -

Returns:

An empty list

buildSequenceInfo

public DBSequenceInfo buildSequenceInfo(String schema,
                                        String seqName)

Default implementation assumes sequences are not supported and returns null

Specified by:

buildSequenceInfo in interface DatabasePlatform

Parameters:

schema -

seqName -

Returns:

Null

buildImportedForeignKeyInfoForTable

public List<DBForeignKeyInfo> buildImportedForeignKeyInfoForTable(String dbSchemaName,
                                                                  String tableName)

Description copied from interface: DatabasePlatform

Build imported foreign key info

Specified by:

buildImportedForeignKeyInfoForTable in interface DatabasePlatform

Parameters:

dbSchemaName - The schema name

tableName - The table name

Returns:

The imported foreign keys

buildExportedForeignKeyInfoForTable

public List<DBForeignKeyInfo> buildExportedForeignKeyInfoForTable(String dbSchemaName,
                                                                  String tableName)

Description copied from interface: DatabasePlatform

Build exported foreign key info

Specified by:

buildExportedForeignKeyInfoForTable in interface DatabasePlatform

Parameters:

dbSchemaName - The schema name

tableName - The table name

Returns:

The exported foreign keys

buildIndexInfosForSchema

protected Map<String,List<DBIndexInfo>> buildIndexInfosForSchema(String schemaName)

buildIndexInfoForTable

public List<DBIndexInfo> buildIndexInfoForTable(String schemaName,
                                                String tableName)

Description copied from interface: DatabasePlatform

Build index info for the specified table

Specified by:

buildIndexInfoForTable in interface DatabasePlatform

Parameters:

schemaName - The schema name

tableName - The table name

Returns:

The indexes for the specified table

buildIndexInfoImpl

protected CaseInsensitiveMap<List<DBIndexInfo>> buildIndexInfoImpl(String schemaName,
                                                                   String tableName)

readValue

public Object readValue(ResultSet rs,
                        String columnName,
                        Class columnClass,
                        int jdbcType)
                 throws SQLException

Read the value from the ResultSet. The DatabasePlatform method is used so that custom methods of reading the values (e.g from BLOB or CLOB columns) can be implemented or a particular database type

Specified by:

readValue in interface DatabasePlatform

Throws:

SQLException

executeDDL

public void executeDDL(String[] sql,
                       boolean log)

Execute these DDL statements

Specified by:

executeDDL in interface DatabasePlatform

Parameters:

sql - Array of SQL statements

log - Whether to log by default. Note that the user preference might overrride this.

executeDDL

public void executeDDL(String sql,
                       boolean log)

Execute this DDL statement

Specified by:

executeDDL in interface DatabasePlatform

Parameters:

sql - A SQL statement

log - Whether to log by default. Note that the user preference might overrride this.

sqlUpdateIJCViewsTable

public String[] sqlUpdateIJCViewsTable()

Specified by:

sqlUpdateIJCViewsTable in interface DatabasePlatform

Returns:

SQL statements to upgrade the IJC_Views table

sqlCreateSecurityTables

public String[] sqlCreateSecurityTables()

Get the SQL that creates the database-based security tables.

Specified by:

sqlCreateSecurityTables in interface DatabasePlatform

Returns:

An array of SQL statements that will be executed in order.

sqlCreateSecurityTablesExternalConstraints

public String[] sqlCreateSecurityTablesExternalConstraints()

Description copied from interface: DatabasePlatform

Constraints from the security tables to the other tables. Can only be added if the other tables are present

Specified by:

sqlCreateSecurityTablesExternalConstraints in interface DatabasePlatform

sqlGetItemsByType

public String sqlGetItemsByType()

Specified by:

sqlGetItemsByType in interface DatabasePlatformX

sqlGetDBSchemaVersion

public String sqlGetDBSchemaVersion()

Specified by:

sqlGetDBSchemaVersion in interface DatabasePlatformX

sqlSetDBSchemaVersion

public String sqlSetDBSchemaVersion(boolean insert)

Specified by:

sqlSetDBSchemaVersion in interface DatabasePlatformX

sqlUpgradeCommand

public String sqlUpgradeCommand(String version,
                                String key)

Specified by:

sqlUpgradeCommand in interface DatabasePlatform

sqlCreateIJCSchemaTable

public String sqlCreateIJCSchemaTable()

Specified by:

sqlCreateIJCSchemaTable in interface DatabasePlatformX

sqlCreateIJCItemInfoTable

public String sqlCreateIJCItemInfoTable()

Specified by:

sqlCreateIJCItemInfoTable in interface DatabasePlatformX

sqlCreateIJCViewsTable

public String[] sqlCreateIJCViewsTable()

Specified by:

sqlCreateIJCViewsTable in interface DatabasePlatformX

sqlCreateIJCGenericsTable

public String sqlCreateIJCGenericsTable()

Specified by:

sqlCreateIJCGenericsTable in interface DatabasePlatformX

sqlCreateIJCUserTable

public String sqlCreateIJCUserTable()

Specified by:

sqlCreateIJCUserTable in interface DatabasePlatformX

sqlCreateIJCItemUserTable

public String sqlCreateIJCItemUserTable()

Specified by:

sqlCreateIJCItemUserTable in interface DatabasePlatformX

sqlCreateIJCSecurityInfoTable

public String sqlCreateIJCSecurityInfoTable()

Specified by:

sqlCreateIJCSecurityInfoTable in interface DatabasePlatformX

sqlCreateIJCInvocationLogTable

public String[] sqlCreateIJCInvocationLogTable()

Specified by:

sqlCreateIJCInvocationLogTable in interface DatabasePlatformX

sqlCreateIJCChangesLogTable

public String[] sqlCreateIJCChangesLogTable()

Specified by:

sqlCreateIJCChangesLogTable in interface DatabasePlatformX

sqlCreateIJCAuthoritiesTable

public String sqlCreateIJCAuthoritiesTable()

Specified by:

sqlCreateIJCAuthoritiesTable in interface DatabasePlatformX

sqlCreateIJCSchemaAuthoritiesTable

public String sqlCreateIJCSchemaAuthoritiesTable()

Specified by:

sqlCreateIJCSchemaAuthoritiesTable in interface DatabasePlatformX

sqlCreateIJCSecurityAuthoritiesAuthoritiesFK

public String sqlCreateIJCSecurityAuthoritiesAuthoritiesFK()

Specified by:

sqlCreateIJCSecurityAuthoritiesAuthoritiesFK in interface DatabasePlatformX

sqlCreateTable

public String sqlCreateTable(String schemaPlusTable,
                             String idColumnName,
                             String extraCols)

Specified by:

sqlCreateTable in interface DatabasePlatformX

sqlCreateSequence

public String sqlCreateSequence(String schemaPlusSequenceName)

Specified by:

sqlCreateSequence in interface DatabasePlatform

sqlRetrieveSequence

public String sqlRetrieveSequence(String schemPlusSequence)

Specified by:

sqlRetrieveSequence in interface DatabasePlatformX

sqlUpdateRow

public String sqlUpdateRow(SchemaQualifiedName schemaPlusTable,
                           String updateClause,
                           String whereClause)

Specified by:

sqlUpdateRow in interface DatabasePlatform

Parameters:

schemaPlusTable - The schema and name of the table to update

updateClause - The values to update e.g. col1 = 'hello world', col2 = 77

whereClause - The where clause to defeind the row(s) e.g. id = 999

Returns:

The SQL

sqlInsertRow

public String sqlInsertRow(SchemaQualifiedName schemaPlusTable,
                           List columnNames)

Creates insert row sql for a prepared statement of the form INSERT INTO table_name (col_1, col_2...) VALUES (?,?...)

Specified by:

sqlInsertRow in interface DatabasePlatform

Parameters:

schemaPlusTable - The schema and name of the table

columnNames - The names of the columns with data

Returns:

The SQL

sqlInsertRowDefaults

public String sqlInsertRowDefaults(SchemaQualifiedName schemaPlusTable,
                                   String[] idColumnNames)

Creates insert row sql that inserts the default value for these columns. Useful when inserting empty rows when at least one column (the PK column) has to be specified.

Specified by:

sqlInsertRowDefaults in interface DatabasePlatform

Parameters:

schemaPlusTable - The schema and name of the table

idColumnNames - The names of the columns to insert the default

Returns:

The SQL

sqlQueryForIds

public String sqlQueryForIds(String schemaPlusTable,
                             String[] idColumnNames,
                             int limit)

Specified by:

sqlQueryForIds in interface DatabasePlatformX

sqlQueryForIdsWithSort

public String sqlQueryForIdsWithSort(String schemaPlusTable,
                                     String[] idColumnNames,
                                     int limit,
                                     SortColumn[] sortCols)

Specified by:

sqlQueryForIdsWithSort in interface DatabasePlatformX

sqlDropIndex

public String sqlDropIndex(String schemaPlusTable,
                           String indexName)

Specified by:

sqlDropIndex in interface DatabasePlatform

sqlDropColumn

public String sqlDropColumn(String schemaPlusTable,
                            String columnName)

Specified by:

sqlDropColumn in interface DatabasePlatformX

sqlDropTable

public String sqlDropTable(String schemaPlusTable)

Specified by:

sqlDropTable in interface DatabasePlatform

sqlCreateView

public String sqlCreateView(String schemaPlusView,
                            String sql)

Specified by:

sqlCreateView in interface DatabasePlatformX

sqlDropView

public String sqlDropView(String schemaPlusView)

Specified by:

sqlDropView in interface DatabasePlatformX

sqlDropSequence

public String sqlDropSequence(String schemaPlusSequence)

Specified by:

sqlDropSequence in interface DatabasePlatform

sqlCount

public String sqlCount(SchemaQualifiedName schemaPlusTable)

Specified by:

sqlCount in interface DatabasePlatform

sqlSelectInList

public SQLSelectStatement sqlSelectInList(SchemaQualifiedName schemaPlusTable,
                                          List<SQLSelectElement> selectElements,
                                          String inClauseColName,
                                          DBDatabaseInfo.ColumnSQLType inClauseColType,
                                          Collection ids)

Specified by:

sqlSelectInList in interface DatabasePlatform

Parameters:

schemaPlusTable - The schema and name of the table

selectElements - Set of elements to select

inClauseColName -

inClauseColType -

Returns:

The SQLSelectStatement

sqlSelectPS

public SQLSelectStatement sqlSelectPS(SchemaQualifiedName schemaPlusTable,
                                      List<SQLSelectElement> selectElements,
                                      String idCol)

Creates SQL for a select statement that uses a PreparedStatement. e.g. SELECT foo, bar from baz where idCol = ?

Specified by:

sqlSelectPS in interface DatabasePlatform

Parameters:

selectElements - The element that the statement should select

idCol - The column to use for the bound variable in the where clause

schemaPlusTable - The table

sqlSelect

public String sqlSelect(String schemaPlusTable,
                        String columns,
                        String idColumnName)

Specified by:

sqlSelect in interface DatabasePlatformX

sqlSelectDistinct

public String sqlSelectDistinct(SchemaQualifiedName schemaPlusTable,
                                String[] columnNames)

Specified by:

sqlSelectDistinct in interface DatabasePlatform

sqlAddColumn

public String sqlAddColumn(String schemaPlusTable,
                           String colName,
                           String colClass,
                           String nativeType,
                           String columnDefinition,
                           String defaultValue,
                           boolean isNotNull)

Specified by:

sqlAddColumn in interface DatabasePlatform

sqlFullColumnDefinition

public String sqlFullColumnDefinition(String colClassName,
                                      String nativeType,
                                      String columnDefinition,
                                      String defaultValue,
                                      boolean isNotNull)

Generate the full column definition, including the defintion of any default value and the NULL/NOT NULL. e.g. VARCHAR(100) DEFAULT 'foo' NOT NULL

Specified by:

sqlFullColumnDefinition in interface DatabasePlatform

Parameters:

colClassName - The name of the columns data type e.g. java.lang.String

nativeType - THe column type e.g. VARCHAR

columnDefinition -

defaultValue -

isNotNull -

Returns:

The column definition

sqlLastId

public String sqlLastId(SchemaQualifiedName schemaPlusTable)

Specified by:

sqlLastId in interface DatabasePlatform

sqlSort

public String sqlSort(List ids,
                      SortColumn[] sortCols,
                      SchemaQualifiedName schemaPlusTable,
                      String selectCol,
                      DBDatabaseInfo.ColumnSQLType selectColType)

Specified by:

sqlSort in interface DatabasePlatformX

sqlDeleteRow

public String sqlDeleteRow(String schemaPlusTable,
                           String whereClause)

Description copied from interface: DatabasePlatform

Generate SQL to delete a row. The ? placeholder will be used for the ID(s) of the row to delete

Specified by:

sqlDeleteRow in interface DatabasePlatform

Parameters:

schemaPlusTable - The schema and name of the table to delete from

whereClause - The "WHERE" part of the SQL delete command.

Returns:

SQL in the from of DELETE FROM <table> WHERE <col> = ? [AND <filter>]

sqlAlterTableName

public String sqlAlterTableName(String oldSchemaPlusTable,
                                String newSchemaPlusTable)

Specified by:

sqlAlterTableName in interface DatabasePlatformX

sqlAlterColumnName

public String sqlAlterColumnName(String schemaPlusTable,
                                 String oldName,
                                 String newName,
                                 String definition)

Specified by:

sqlAlterColumnName in interface DatabasePlatformX

sqlAlterColumnSize

public String sqlAlterColumnSize(String schemaPlusTable,
                                 String columnName,
                                 String columnDefinition)

Specified by:

sqlAlterColumnSize in interface DatabasePlatformX

sqlAddIndex

public String sqlAddIndex(String indexName,
                          String indexType,
                          String schemaPlusTable,
                          String[] columns,
                          boolean[] asc)

Add an index to the table

Specified by:

sqlAddIndex in interface DatabasePlatform

Parameters:

indexName - The name for the index.

indexType - The type of index. e.g. UNIQUE. Consult the supportedIndexTypes() method to find out which types your DB supports. Can be null for a regular index.

schemaPlusTable - The schema and name of the table for the index

columns - The columns

asc - Is the index for this column ascending or descending. true means ASC, false means DESC. Can be null, but if specified must have the same size as the columns Must be the same number of values as there are columns.

Returns:

The SQL for adding the index

sqlAddUniqueConstraint

public String sqlAddUniqueConstraint(String consName,
                                     String schemaPlusTable,
                                     String[] columns)

Add unique constraint for some columns in the table. Usually that will imply creation of an unique-valued index in the database.

Specified by:

sqlAddUniqueConstraint in interface DatabasePlatform

Parameters:

consName - The c onstraint name

schemaPlusTable - The schema and name of the table

columns - The c olumns which will form the unique key. Order is important.

Returns:

SQL statement to create the unique key

getColumnListCommaSeparated

public String getColumnListCommaSeparated(String[] columns)

Specified by:

getColumnListCommaSeparated in interface DatabasePlatformX

Parameters:

columns -

Returns:

The comma-separated list of column names

sqlDropPrimaryConstraint

public String sqlDropPrimaryConstraint(String schemaPlusTable,
                                       String consName,
                                       String[] columns)

Specified by:

sqlDropPrimaryConstraint in interface DatabasePlatform

sqlDropUniqueConstraint

public String sqlDropUniqueConstraint(String schemaPlusTable,
                                      String consName,
                                      String[] columns)

Specified by:

sqlDropUniqueConstraint in interface DatabasePlatform

stringArrayContains

public static boolean stringArrayContains(String[] hayStack,
                                          String needle)

sqlViewMetaDataNextIndex

public String sqlViewMetaDataNextIndex()

Specified by:

sqlViewMetaDataNextIndex in interface DatabasePlatformX

sqlViewMetaDataInsert

public String sqlViewMetaDataInsert()

Specified by:

sqlViewMetaDataInsert in interface DatabasePlatformX

sqlSchemaMetaDataNextIndex

public String sqlSchemaMetaDataNextIndex()

Specified by:

sqlSchemaMetaDataNextIndex in interface DatabasePlatformX

sqlSchemaMetaDataInsert

public String sqlSchemaMetaDataInsert()

Specified by:

sqlSchemaMetaDataInsert in interface DatabasePlatformX

sqlGenericItemInsert

public String sqlGenericItemInsert()

Specified by:

sqlGenericItemInsert in interface DatabasePlatformX

sqlGenericItemDelete

public String sqlGenericItemDelete()

Specified by:

sqlGenericItemDelete in interface DatabasePlatformX

sqlGenericItemUpdate

public String sqlGenericItemUpdate()

Specified by:

sqlGenericItemUpdate in interface DatabasePlatformX

sqlAddForeignKey

public String sqlAddForeignKey(String fkName,
                               String parentSchemaPlusTable,
                               String dependentSchemaPlusTable,
                               String[] parentCols,
                               String[] dependentCols,
                               String onDeleteRule,
                               String onUpdateRule)

Specified by:

sqlAddForeignKey in interface DatabasePlatform

sqlDropForeignKey

public String sqlDropForeignKey(String schemaPlusTable,
                                String fkName)

Specified by:

sqlDropForeignKey in interface DatabasePlatform

sqlDropConstraint

protected String sqlDropConstraint(String schemaPlusTable,
                                   String constraintName)

translateSQLException

public DataAccessException translateSQLException(String message,
                                                 String sql,
                                                 SQLException ex)

Specified by:

translateSQLException in interface DatabasePlatform

getAddUserSql

public String getAddUserSql()

Specified by:

getAddUserSql in interface DatabasePlatform

getDeleteUserSql

public String getDeleteUserSql()

Specified by:

getDeleteUserSql in interface DatabasePlatform

getUpdatePasswordSql

public String getUpdatePasswordSql()

Specified by:

getUpdatePasswordSql in interface DatabasePlatform

getSetEnabledSql

public String getSetEnabledSql()

Specified by:

getSetEnabledSql in interface DatabasePlatform

getAddAuthoritySql

public String getAddAuthoritySql()

Specified by:

getAddAuthoritySql in interface DatabasePlatform

getDeleteAuthoritiesSql

public String getDeleteAuthoritiesSql()

Specified by:

getDeleteAuthoritiesSql in interface DatabasePlatform

sqlSelectDuplicateRows

public String sqlSelectDuplicateRows(String schemaPlusTable,
                                     String[] columns)

Specified by:

sqlSelectDuplicateRows in interface DatabasePlatform

buildTableInfos

public Map<String,DBTableInfo> buildTableInfos(String dbSchemaName,
                                               List<String> tablesToInit,
                                               LazyLoader lazyLoader,
                                               DFFeedback feedback)

Description copied from interface: DatabasePlatform

Build all table infos for the specified schema

Specified by:

buildTableInfos in interface DatabasePlatform

Returns:

A map of DBTableInfos keyed by the schema qualified table/view name

buildTableInfosLazy

public Map<String,DBTableInfo> buildTableInfosLazy(String dbSchemaName,
                                                   LazyLoader lazyLoader,
                                                   DFFeedback feedback)

Specified by:

buildTableInfosLazy in interface DatabasePlatform

buildTableInfosEager

public Map<String,DBTableInfo> buildTableInfosEager(String dbSchemaName,
                                                    DFFeedback feedback)

Specified by:

buildTableInfosEager in interface DatabasePlatform

sqlGetTablesWithPropertyColumns

protected String sqlGetTablesWithPropertyColumns()

findJChemPropertyTables

protected Set<String> findJChemPropertyTables()

readPropertyTables

protected abstract void readPropertyTables(Set<String> tables,
                                           ResultSet rs)
                                    throws SQLException

Throws:

SQLException

readPropertyTablesNoSchema

protected void readPropertyTablesNoSchema(Set<String> tables,
                                          ResultSet rs)
                                   throws SQLException

Throws:

SQLException

readPropertyTablesWithSchema

protected void readPropertyTablesWithSchema(Set<String> tables,
                                            ResultSet rs)
                                     throws SQLException

Throws:

SQLException

buildTableInfo

public DBTableInfo buildTableInfo(String dbSchemaName,
                                  String tableName,
                                  LazyLoader lazyLoader,
                                  DFFeedback feedback)

Description copied from interface: DatabasePlatform

Build the full table info for this table. The table info contains all the information about the table.

Specified by:

buildTableInfo in interface DatabasePlatform

Parameters:

dbSchemaName - The schema in which the table resides

tableName - The table name

Returns:

The database table info The method can return null if the table does not exist.

buildTableInfoFull

public DBTableInfo buildTableInfoFull(String dbSchemaName,
                                      String tableName,
                                      LazyLoader lazyLoader,
                                      DFFeedback feedback)

Specified by:

buildTableInfoFull in interface DatabasePlatform

buildConstraintInfosForSchema

protected Map<String,List<DBConstraintInfo>> buildConstraintInfosForSchema(String dbSchemaName)

Build constraint info for the specified schema.

Parameters:

dbSchemaName -

Returns:

A Map of constraints, keyed by the table name

buildConstraintInfosImpl

protected List<DBConstraintInfo> buildConstraintInfosImpl(String dbSchemaName,
                                                          String tableName,
                                                          List<DBColInfo> collsInTable)

Build constraint info for the specified table.

Parameters:

dbSchemaName - The schema

tableName - The table

collsInTable - The columns

Returns:

The constraint info

getTermEncoder

public TermEncoder getTermEncoder()

Specified by:

getTermEncoder in interface DatabasePlatformX

getCartridgeOwner

public String getCartridgeOwner()

Get the user that owns the JChem cartridge functions. Will return null if the database is not Oracle or the cartridge is not installed and appropriate grants not made to the current user/schema. To specify that the database type has cartridge owner all you need to do is override the sqlJChemCartridgeOwner() method and specify the SQL that gives you the name of the owner

Specified by:

getCartridgeOwner in interface DatabasePlatformX

getCallType

public String getCallType()

setCallType

public void setCallType(String callType)

sqlJChemCartridgeOwner

protected String sqlJChemCartridgeOwner()

sqlJChemCartridgeEnvironment

protected String sqlJChemCartridgeEnvironment()

checkSchemaExistence

public void checkSchemaExistence(String schemaId)
                          throws SchemaDoesNotExistException

Description copied from interface: DatabasePlatform

Checks whether an schema exists in the IJC metadata registry (IJC_SCHEMA table) or not also checks whether the database/ IJC_SCHEMA table exists or not.

Specified by:

checkSchemaExistence in interface DatabasePlatform

Throws:

SchemaDoesNotExistException

updateHeartbeat

public void updateHeartbeat(String userName,
                            String schemaId)

Description copied from interface: DatabasePlatform

Updates when the last time user accessed the database for purpose of changing the data

Specified by:

updateHeartbeat in interface DatabasePlatform

Parameters:

userName - current logged in use

schemaId - current schema