/* * Copyright 2008, Unitils.org * * 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. */ package org.unitils.core.dbsupport; import org.unitils.core.UnitilsException; import static org.unitils.thirdparty.org.apache.commons.dbutils.DbUtils.closeQuietly; import java.sql.*; import java.util.Set; /** * Implementation of {@link DbSupport} for a hsqldb database * * @author Filip Neven * @author Tim Ducheyne */ public class HsqldbDbSupport extends DbSupport { /* The major version number of the hsql database */ private Integer hsqlMajorVersionNumber; /** * Creates support for HsqlDb databases. */ public HsqldbDbSupport() { super("hsqldb"); } /** * Returns the names of all tables in the database. * * @return The names of all tables in the database */ @Override public Set<String> getTableNames() { if (getHsqldbMajorVersionNumber() >= 2) { return getSQLHandler().getItemsAsStringSet("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = '" + getSchemaName() + "'"); } return getSQLHandler().getItemsAsStringSet("select TABLE_NAME from INFORMATION_SCHEMA.SYSTEM_TABLES where TABLE_TYPE = 'TABLE' AND TABLE_SCHEM = '" + getSchemaName() + "'"); } /** * Gets the names of all columns of the given table. * * @param tableName The table, not null * @return The names of the columns of the table with the given name */ @Override public Set<String> getColumnNames(String tableName) { if (getHsqldbMajorVersionNumber() >= 2) { return getSQLHandler().getItemsAsStringSet("select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '" + tableName + "' AND TABLE_SCHEMA = '" + getSchemaName() + "'"); } return getSQLHandler().getItemsAsStringSet("select COLUMN_NAME from INFORMATION_SCHEMA.SYSTEM_COLUMNS where TABLE_NAME = '" + tableName + "' AND TABLE_SCHEM = '" + getSchemaName() + "'"); } /** * Retrieves the names of all the views in the database schema. * * @return The names of all views in the database */ @Override public Set<String> getViewNames() { if(getHsqldbMajorVersionNumber() >= 2){ return getSQLHandler().getItemsAsStringSet("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA = '" + getSchemaName() + "'"); } return getSQLHandler().getItemsAsStringSet("select TABLE_NAME from INFORMATION_SCHEMA.SYSTEM_TABLES where TABLE_TYPE = 'VIEW' AND TABLE_SCHEM = '" + getSchemaName() + "'"); } /** * Retrieves the names of all the sequences in the database schema. * * @return The names of all sequences in the database */ @Override public Set<String> getSequenceNames() { if(getHsqldbMajorVersionNumber() >= 2){ return getSQLHandler().getItemsAsStringSet("select SEQUENCE_NAME from INFORMATION_SCHEMA.SEQUENCES where SEQUENCE_SCHEMA = '" + getSchemaName() + "'"); } return getSQLHandler().getItemsAsStringSet("select SEQUENCE_NAME from INFORMATION_SCHEMA.SYSTEM_SEQUENCES where SEQUENCE_SCHEMA = '" + getSchemaName() + "'"); } /** * Retrieves the names of all the triggers in the database schema. * * @return The names of all triggers in the database */ @Override public Set<String> getTriggerNames() { if(getHsqldbMajorVersionNumber() >= 2){ return getSQLHandler().getItemsAsStringSet("select TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS where TRIGGER_SCHEMA = '" + getSchemaName() + "'"); } return getSQLHandler().getItemsAsStringSet("select TRIGGER_NAME from INFORMATION_SCHEMA.SYSTEM_TRIGGERS where TRIGGER_SCHEM = '" + getSchemaName() + "'"); } /** * Disables all referential constraints (e.g. foreign keys) on all table in the schema */ @Override public void disableReferentialConstraints() { int hsqlMajorVersionNumber = getHsqldbMajorVersionNumber(); Connection connection = null; Statement queryStatement = null; Statement alterStatement = null; ResultSet resultSet = null; try { connection = getSQLHandler().getDataSource().getConnection(); queryStatement = connection.createStatement(); alterStatement = connection.createStatement(); if (hsqlMajorVersionNumber >= 2) { resultSet = queryStatement.executeQuery("select TABLE_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_SCHEMA = '" + getSchemaName() + "'"); } else { resultSet = queryStatement.executeQuery("select TABLE_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.SYSTEM_TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_SCHEMA = '" + getSchemaName() + "'"); } while (resultSet.next()) { String tableName = resultSet.getString("TABLE_NAME"); String constraintName = resultSet.getString("CONSTRAINT_NAME"); alterStatement.executeUpdate("alter table " + qualified(tableName) + " drop constraint " + quoted(constraintName)); } } catch (Exception e) { throw new UnitilsException("Error while disabling not referential constraints on schema " + getSchemaName(), e); } finally { closeQuietly(queryStatement); closeQuietly(connection, alterStatement, resultSet); } } /** * Disables all value constraints (e.g. not null) on all tables in the schema */ @Override public void disableValueConstraints() { disableCheckAndUniqueConstraints(); disableNotNullConstraints(); } /** * Disables all check and unique constraints on all tables in the schema */ protected void disableCheckAndUniqueConstraints() { int hsqlMajorVersionNumber = getHsqldbMajorVersionNumber(); Connection connection = null; Statement queryStatement = null; Statement alterStatement = null; ResultSet resultSet = null; try { connection = getSQLHandler().getDataSource().getConnection(); queryStatement = connection.createStatement(); alterStatement = connection.createStatement(); if (hsqlMajorVersionNumber >= 2) { resultSet = queryStatement.executeQuery("select TABLE_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE IN ('CHECK', 'UNIQUE') AND CONSTRAINT_SCHEMA = '" + getSchemaName() + "'"); } else { resultSet = queryStatement.executeQuery("select TABLE_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.SYSTEM_TABLE_CONSTRAINTS where CONSTRAINT_TYPE IN ('CHECK', 'UNIQUE') AND CONSTRAINT_SCHEMA = '" + getSchemaName() + "'"); } while (resultSet.next()) { String tableName = resultSet.getString("TABLE_NAME"); String constraintName = resultSet.getString("CONSTRAINT_NAME"); alterStatement.executeUpdate("alter table " + qualified(tableName) + " drop constraint " + quoted(constraintName)); } } catch (Exception e) { throw new UnitilsException("Error while disabling check and unique constraints on schema " + getSchemaName(), e); } finally { closeQuietly(queryStatement); closeQuietly(connection, alterStatement, resultSet); } } /** * Disables all not null constraints on all tables in the schema */ protected void disableNotNullConstraints() { int hsqlMajorVersionNumber = getHsqldbMajorVersionNumber(); Connection connection = null; Statement queryStatement = null; Statement alterStatement = null; ResultSet resultSet = null; try { connection = getSQLHandler().getDataSource().getConnection(); queryStatement = connection.createStatement(); alterStatement = connection.createStatement(); // Do not remove PK constraints if(hsqlMajorVersionNumber >= 2){ resultSet = queryStatement.executeQuery("select col.TABLE_NAME, col.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS col where col.IS_NULLABLE = 'NO' and col.TABLE_SCHEMA = '" + getSchemaName() + "' " + "AND NOT EXISTS ( select COLUMN_NAME from INFORMATION_SCHEMA.SYSTEM_PRIMARYKEYS pk where pk.TABLE_NAME = col.TABLE_NAME and pk.COLUMN_NAME = col.COLUMN_NAME and pk.TABLE_SCHEM = '" + getSchemaName() + "' )"); } else { resultSet = queryStatement.executeQuery("select col.TABLE_NAME, col.COLUMN_NAME from INFORMATION_SCHEMA.SYSTEM_COLUMNS col where col.IS_NULLABLE = 'NO' and col.TABLE_SCHEM = '" + getSchemaName() + "' " + "AND NOT EXISTS ( select COLUMN_NAME from INFORMATION_SCHEMA.SYSTEM_PRIMARYKEYS pk where pk.TABLE_NAME = col.TABLE_NAME and pk.COLUMN_NAME = col.COLUMN_NAME and pk.TABLE_SCHEM = '" + getSchemaName() + "' )"); } while (resultSet.next()) { String tableName = resultSet.getString("TABLE_NAME"); String columnName = resultSet.getString("COLUMN_NAME"); alterStatement.executeUpdate("alter table " + qualified(tableName) + " alter column " + quoted(columnName) + " set null"); } } catch (Exception e) { throw new UnitilsException("Error while disabling not null constraints on schema " + getSchemaName(), e); } finally { closeQuietly(queryStatement); closeQuietly(connection, alterStatement, resultSet); } } /** * Returns the value of the sequence with the given name. * <p/> * Note: this can have the side-effect of increasing the sequence value. * * @param sequenceName The sequence, not null * @return The value of the sequence with the given name */ @Override public long getSequenceValue(String sequenceName) { if (getHsqldbMajorVersionNumber() >= 2) { return getSQLHandler().getItemAsLong("select NEXT_VALUE from INFORMATION_SCHEMA.SEQUENCES where SEQUENCE_SCHEMA = '" + getSchemaName() + "' and SEQUENCE_NAME = '" + sequenceName + "'"); } return getSQLHandler().getItemAsLong("select START_WITH from INFORMATION_SCHEMA.SYSTEM_SEQUENCES where SEQUENCE_SCHEMA = '" + getSchemaName() + "' and SEQUENCE_NAME = '" + sequenceName + "'"); } /** * Sets the next value of the sequence with the given sequence name to the given sequence value. * * @param sequenceName The sequence, not null * @param newSequenceValue The value to set */ @Override public void incrementSequenceToValue(String sequenceName, long newSequenceValue) { getSQLHandler().executeUpdate("alter sequence " + qualified(sequenceName) + " restart with " + newSequenceValue); } /** * Gets the names of all identity columns of the given table. * <p/> * todo check, at this moment the PK columns are returned * * @param tableName The table, not null * @return The names of the identity columns of the table with the given name */ @Override public Set<String> getIdentityColumnNames(String tableName) { return getSQLHandler().getItemsAsStringSet("select COLUMN_NAME from INFORMATION_SCHEMA.SYSTEM_PRIMARYKEYS where TABLE_NAME = '" + tableName + "' AND TABLE_SCHEM = '" + getSchemaName() + "'"); } /** * Increments the identity value for the specified identity column on the specified table to the given value. * * @param tableName The table with the identity column, not null * @param identityColumnName The column, not null * @param identityValue The new value */ @Override public void incrementIdentityColumnToValue(String tableName, String identityColumnName, long identityValue) { getSQLHandler().executeUpdate("alter table " + qualified(tableName) + " alter column " + quoted(identityColumnName) + " RESTART WITH " + identityValue); } /** * @return The major version number of the Hsql database server that is used (e.g. for Hsql version 1.8.0, 1 is returned) */ protected Integer getHsqldbMajorVersionNumber() { if (hsqlMajorVersionNumber == null) { Connection connection = null; try { connection = getSQLHandler().getDataSource().getConnection(); DatabaseMetaData metaData = connection.getMetaData(); hsqlMajorVersionNumber = metaData.getDatabaseMajorVersion(); } catch (SQLException e) { throw new UnitilsException("Unable to determine database major version.", e); } finally { closeQuietly(connection); } } return hsqlMajorVersionNumber; } /** * Sequences are supported. * * @return True */ @Override public boolean supportsSequences() { return true; } /** * Triggers are supported. * * @return True */ @Override public boolean supportsTriggers() { return true; } /** * Identity columns are supported. * * @return True */ @Override public boolean supportsIdentityColumns() { return true; } /** * Cascade are supported. * * @return True */ @Override public boolean supportsCascade() { return true; } }