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

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