eu.etaxonomy.taxeditor.test/src/test/java/eu/etaxonomy/taxeditor/util/H2DbSupport.java

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