cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/H2DbSupport.java

   1 package eu.etaxonomy.cdm.database;
   2 /**
   3  * based on from http://code.google.com/p/java-tester/source/browse/trunk/src/main/java/org/jtester/unitils/database/H2DbSupport.java
   4  */
   5
   6
   7 import java.util.Set;
   8
   9 import org.unitils.core.dbsupport.DbSupport;
  10
  11 public class H2DbSupport extends DbSupport {
  12         /**
  13          * Creates support for HsqlDb databases.
  14          */
  15         public H2DbSupport() {
  16                 super("h2");
  17         }
  18
  19         @Override
  20         public Set<String> getColumnNames(String tableName) {
  21                 return getSQLHandler().getItemsAsStringSet(
  22                                 "select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '" + tableName
  23                                                 + "' AND TABLE_SCHEMA = '" + getSchemaName() + "'");
  24         }
  25
  26         @Override
  27         public Set<String> getTableNames() {
  28                 return getSQLHandler().getItemsAsStringSet(
  29                                 "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'TABLE' AND TABLE_SCHEMA = '"
  30                                                 + getSchemaName() + "'");
  31         }
  32
  33         @Override
  34         public Set<String> getViewNames() {
  35                 return getSQLHandler().getItemsAsStringSet(
  36                                 "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA = '"
  37                                                 + getSchemaName() + "'");
  38         }
  39
  40         @Override
  41         public Set<String> getSequenceNames() {
  42                 return getSQLHandler().getItemsAsStringSet(
  43                                 "select SEQUENCE_NAME from INFORMATION_SCHEMA.SEQUENCES where SEQUENCE_SCHEMA = '"
  44                                                 + getSchemaName() + "'");
  45         }
  46
  47         @Override
  48         public Set<String> getTriggerNames() {
  49                 return getSQLHandler().getItemsAsStringSet(
  50                                 "select TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS where TRIGGER_SCHEMA = '" + getSchemaName()
  51                                                 + "'");
  52         }
  53
  54         @Override
  55         public long getSequenceValue(String sequenceName) {
  56                 return getSQLHandler().getItemAsLong(
  57                                 "select CURRENT_VALUE from INFORMATION_SCHEMA.SEQUENCES where SEQUENCE_SCHEMA = '"
  58                                                 + getSchemaName() + "' and SEQUENCE_NAME = '" + sequenceName + "'");
  59         }
  60
  61         @Override
  62         public boolean supportsSequences() {
  63                 return true;
  64         }
  65
  66         @Override
  67         public boolean supportsTriggers() {
  68                 return true;
  69         }
  70
  71         @Override
  72         public boolean supportsIdentityColumns() {
  73                 return true;
  74         }
  75
  76         @Override
  77         public void incrementSequenceToValue(String sequenceName, long newSequenceValue) {
  78                 getSQLHandler()
  79                                 .executeUpdate("alter sequence " + qualified(sequenceName) + " restart with " + newSequenceValue);
  80         }
  81
  82         @Override
  83         public void incrementIdentityColumnToValue(String tableName, String identityColumnName, long identityValue) {
  84                 getSQLHandler().executeUpdate(
  85                                 "alter table " + qualified(tableName) + " alter column " + quoted(identityColumnName)
  86                                                 + " RESTART WITH " + identityValue);
  87         }
  88
  89         @Override
  90         public void disableReferentialConstraints() {
  91                 Set<String> tableNames = getTableNames();
  92                 for (String tableName : tableNames) {
  93                         disableReferentialConstraints(tableName);
  94                 }
  95         }
  96
  97         @Override
  98         public void disableValueConstraints() {
  99                 Set<String> tableNames = getTableNames();
 100                 for (String tableName : tableNames) {
 101                         disableValueConstraints(tableName);
 102                 }
 103         }
 104
 105         private void disableReferentialConstraints(String tableName) {
 106                 Set<String> constraintNames = this.getForeignKeyConstraintNames(tableName);
 107                 for (String constraintName : constraintNames) {
 108                         this.removeForeignKeyConstraint(tableName, constraintName);
 109                 }
 110         }
 111
 112         private void disableValueConstraints(String tableName) {
 113                 Set<String> primaryKeyColumnNames = this.getPrimaryKeyColumnNames(tableName);
 114
 115                 Set<String> notNullColumnNames = this.getNotNullColummnNames(tableName);
 116                 for (String notNullColumnName : notNullColumnNames) {
 117                         if (primaryKeyColumnNames.contains(notNullColumnName)) {
 118                                 continue;
 119                         }
 120                         this.removeNotNullConstraint(tableName, notNullColumnName);
 121                 }
 122         }
 123
 124         /**
 125          * Gets the names of all identity columns of the given table.
 126          * <p/>
 127          * todo check, at this moment the PK columns are returned
 128          *
 129          * @param tableName The table, not null
 130          * @return The names of the identity columns of the table with the given name
 131          */
 132         @Override
 133         public Set<String> getIdentityColumnNames(String tableName) {
 134             return getPrimaryKeyColumnNames(tableName);
 135         }
 136
 137         private Set<String> getPrimaryKeyColumnNames(String tableName) {
 138                 return getSQLHandler().getItemsAsStringSet(
 139                                 "select COLUMN_NAME from INFORMATION_SCHEMA.INDEXES where PRIMARY_KEY=TRUE AND TABLE_NAME = '"
 140                                                 + tableName + "' AND TABLE_SCHEMA = '" + getSchemaName() + "'");
 141         }
 142
 143         private Set<String> getNotNullColummnNames(String tableName) {
 144                 return getSQLHandler().getItemsAsStringSet(
 145                                 "select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE = 'NO' AND TABLE_NAME = '"
 146                                                 + tableName + "' AND TABLE_SCHEMA = '" + getSchemaName() + "'");
 147         }
 148
 149         private Set<String> getForeignKeyConstraintNames(String tableName) {
 150                 return getSQLHandler().getItemsAsStringSet(
 151                                 "select CONSTRAINT_NAME from INFORMATION_SCHEMA.CONSTRAINTS "
 152                                                 + "where CONSTRAINT_TYPE = 'REFERENTIAL' AND TABLE_NAME = '" + tableName
 153                                                 + "' AND CONSTRAINT_SCHEMA = '" + getSchemaName() + "'");
 154         }
 155
 156         private void removeForeignKeyConstraint(String tableName, String constraintName) {
 157                 getSQLHandler().executeUpdate(
 158                                 "alter table " + qualified(tableName) + " drop constraint " + quoted(constraintName));
 159         }
 160
 161         private void removeNotNullConstraint(String tableName, String columnName) {
 162                 getSQLHandler().executeUpdate(
 163                                 "alter table " + qualified(tableName) + " alter column " + quoted(columnName) + " set null");
 164         }
 165 }