Add support for DB2 database Change-Id: Id7d68d36e5bc8d891c093499fef732fe7e971e43
diff --git a/README_DB2 b/README_DB2 new file mode 100644 index 0000000..5097b0f --- /dev/null +++ b/README_DB2
@@ -0,0 +1,18 @@ +DB2 has an issue with availability of jdbc driver in public maven repository. + +To test DialectDB2 JDBC Type 4 driver is needed. + +1. Obtain the driver either from your local installation or download it from +IBM DB2 site +2. Install the driver: + +mvn install:install-file -DgroupId=com.ibm.db2 -DartifactId=db2jcc4 \ + -Dversion=10.5 -Dpackaging=jar -Dfile=<path to db2jcc4.jar> -DgeneratePom=true + +mvn install:install-file -DgroupId=com.ibm.db2 -DartifactId=db2jcc_license_cu \ + -Dversion=10.5 -Dpackaging=jar -Dfile=<path to db2jcc_license_cu.jar> \ + -DgeneratePom=true + +3. Use specific maven profile to run DB2 related unit tests: + +mvn package -P db2
diff --git a/pom.xml b/pom.xml index 80e19cc..b900340 100644 --- a/pom.xml +++ b/pom.xml
@@ -259,6 +259,23 @@ <profiles> <profile> + <id>db2</id> + <dependencies> + <dependency> + <groupId>com.ibm.db2</groupId> + <artifactId>db2jcc4</artifactId> + <version>10.5</version> + <scope>provided</scope> + </dependency> + <dependency> + <groupId>com.ibm.db2</groupId> + <artifactId>db2jcc_license_cu</artifactId> + <version>10.5</version> + <scope>provided</scope> + </dependency> + </dependencies> + </profile> + <profile> <id>oracle</id> <dependencies> <dependency>
diff --git a/src/main/java/com/google/gwtorm/jdbc/JdbcAccess.java b/src/main/java/com/google/gwtorm/jdbc/JdbcAccess.java index ac4595a..a97cfef 100644 --- a/src/main/java/com/google/gwtorm/jdbc/JdbcAccess.java +++ b/src/main/java/com/google/gwtorm/jdbc/JdbcAccess.java
@@ -16,6 +16,7 @@ import com.google.common.base.Preconditions; import com.google.gwtorm.client.Key; +import com.google.gwtorm.schema.sql.DialectDB2; import com.google.gwtorm.server.AbstractAccess; import com.google.gwtorm.server.Access; import com.google.gwtorm.server.ListResultSet; @@ -452,9 +453,15 @@ } } - protected OrmException convertError(final String op, final SQLException err) { + protected OrmException convertError(final String op, SQLException err) { if (err.getCause() == null && err.getNextException() != null) { - err.initCause(err.getNextException()); + // special case for IBM DB2. Exception cause is null: + // http://paste.openstack.org/show/193304/ + if (schema.getDialect() instanceof DialectDB2) { + err = err.getNextException(); + } else { + err.initCause(err.getNextException()); + } } return schema.getDialect().convertError(op, getRelationName(), err); }
diff --git a/src/main/java/com/google/gwtorm/schema/sql/DialectDB2.java b/src/main/java/com/google/gwtorm/schema/sql/DialectDB2.java new file mode 100644 index 0000000..b45da82 --- /dev/null +++ b/src/main/java/com/google/gwtorm/schema/sql/DialectDB2.java
@@ -0,0 +1,153 @@ +// Copyright 2015 The Android Open Source Project +// +// 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 com.google.gwtorm.schema.sql; + +import com.google.gwtorm.schema.ColumnModel; +import com.google.gwtorm.server.OrmDuplicateKeyException; +import com.google.gwtorm.server.OrmException; +import com.google.gwtorm.server.StatementExecutor; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.sql.Types; +import java.util.HashSet; +import java.util.Set; + +/** Dialect for DB2 */ +public class DialectDB2 extends SqlDialect { + + public DialectDB2() { + typeNames.put(Types.LONGVARCHAR, "CLOB"); + } + + @Override + public boolean handles(String url, Connection c) { + return url.startsWith("jdbc:db2:"); + } + + @Override + public OrmException convertError(String op, String entity, + SQLException err) { + switch (getSQLStateInt(err)) { + case 23505: // DUPLICATE_KEY_1 + return new OrmDuplicateKeyException(entity, err); + + default: + return super.convertError(op, entity, err); + } + } + + @Override + protected String getNextSequenceValueSql(String seqname) { + return "VALUES NEXT VALUE FOR " + seqname; + } + + @Override + public Set<String> listSequences(Connection db) throws SQLException { + Statement s = db.createStatement(); + try { + ResultSet rs = + s.executeQuery("SELECT SEQNAME" + + " FROM SYSCAT.SEQUENCES" + + " WHERE SEQSCHEMA = CURRENT_SCHEMA"); + try { + HashSet<String> sequences = new HashSet<>(); + while (rs.next()) { + sequences.add(rs.getString(1).toLowerCase()); + } + return sequences; + } finally { + rs.close(); + } + } finally { + s.close(); + } + } + + @Override + public Set<String> listTables(Connection db) throws SQLException { + Statement s = db.createStatement(); + try { + ResultSet rs = s.executeQuery("SELECT TABNAME" + + " FROM SYSCAT.TABLES" + + " WHERE TABSCHEMA = CURRENT_SCHEMA"); + try { + Set<String> tables = new HashSet<>(); + while (rs.next()) { + tables.add(rs.getString(1).toLowerCase()); + } + return tables; + } finally { + rs.close(); + } + } finally { + s.close(); + } + } + + @Override + public Set<String> listIndexes(final Connection db, String tableName) + throws SQLException { + PreparedStatement s = db.prepareStatement("SELECT distinct INDNAME" + + " FROM syscat.indexes WHERE TABNAME = ? AND TABSCHEMA = CURRENT_SCHEMA"); + try { + s.setString(1, tableName.toUpperCase()); + ResultSet rs = s.executeQuery(); + try { + Set<String> indexes = new HashSet<>(); + while (rs.next()) { + indexes.add(rs.getString(1).toLowerCase()); + } + return indexes; + } finally { + rs.close(); + } + } finally { + s.close(); + } + } + + @Override + public void renameTable(StatementExecutor e, String from, String to) + throws OrmException { + StringBuilder r = new StringBuilder(); + r.append("RENAME TABLE "); + r.append(from); + r.append(" TO "); + r.append(to); + e.execute(r.toString()); + } + + @Override + public void renameColumn(StatementExecutor stmt, String tableName, + String fromColumn, ColumnModel col) throws OrmException { + StringBuilder r = new StringBuilder(); + r.append("ALTER TABLE "); + r.append(tableName); + r.append(" RENAME COLUMN "); + r.append(fromColumn); + r.append(" TO "); + r.append(col.getColumnName()); + stmt.execute(r.toString()); + } + + @Override + public boolean isStatementDelimiterSupported() { + return false; + } +}
diff --git a/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java b/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java index de48b7c..3c68117 100644 --- a/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java +++ b/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java
@@ -40,6 +40,7 @@ new CopyOnWriteArrayList<>(); static { + DIALECTS.add(new DialectDB2()); DIALECTS.add(new DialectH2()); DIALECTS.add(new DialectPostgreSQL()); DIALECTS.add(new DialectMySQL());
diff --git a/src/test/java/com/google/gwtorm/schema/sql/DialectDB2Test.java b/src/test/java/com/google/gwtorm/schema/sql/DialectDB2Test.java new file mode 100644 index 0000000..82e2330 --- /dev/null +++ b/src/test/java/com/google/gwtorm/schema/sql/DialectDB2Test.java
@@ -0,0 +1,224 @@ +// Copyright 2015 The Android Open Source Project +// +// 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 com.google.gwtorm.schema.sql; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertTrue; +import static org.junit.Assume.assumeNoException; + +import com.google.gwtorm.data.Address; +import com.google.gwtorm.data.Person; +import com.google.gwtorm.data.PhoneBookDb; +import com.google.gwtorm.data.PhoneBookDb2; +import com.google.gwtorm.jdbc.Database; +import com.google.gwtorm.jdbc.JdbcExecutor; +import com.google.gwtorm.jdbc.JdbcSchema; +import com.google.gwtorm.jdbc.SimpleDataSource; +import com.google.gwtorm.server.OrmException; + +import org.junit.After; +import org.junit.Before; +import org.junit.Test; + +import java.sql.DriverManager; +import java.sql.SQLException; +import java.util.Collections; +import java.util.Properties; +import java.util.Set; + +public class DialectDB2Test extends SqlDialectTest { + private final static String DB2_DRIVER = "com.ibm.db2.jcc.DB2Driver"; + @Before + public void setUp() throws Exception { + try { + Class.forName(DB2_DRIVER); + } catch (Exception e) { + assumeNoException(e); + } + + final String database = "GERRIT"; // database + final String user = "gwtorm"; // user + final String pass = "gwtorm"; // pwd + + db = DriverManager.getConnection("jdbc:db2://127.0.0.1:50001/" + + database, user, pass); + executor = new JdbcExecutor(db); + dialect = new DialectDB2().refine(db); + + final Properties p = new Properties(); + p.setProperty("driver", DB2_DRIVER); + p.setProperty("url", db.getMetaData().getURL()); + p.setProperty("user", user); + p.setProperty("password", pass); + phoneBook = + new Database<>(new SimpleDataSource(p), PhoneBookDb.class); + phoneBook2 = + new Database<>(new SimpleDataSource(p), PhoneBookDb2.class); + } + + @After + public void tearDown() { + if (executor == null) { + return; + } + + // Database content must be flushed because + // tests assume that the database is empty + drop("SEQUENCE address_id"); + drop("SEQUENCE cnt"); + + drop("TABLE addresses"); + drop("TABLE foo"); + drop("TABLE bar"); + drop("TABLE people"); + + if (executor != null) { + executor.close(); + } + executor = null; + + if (db != null) { + try { + db.close(); + } catch (SQLException e) { + throw new RuntimeException("Cannot close database", e); + } + } + db = null; + } + + private void drop(String drop) { + try { + execute("DROP " + drop); + } catch (OrmException e) { + } + } + + private void execute(final String sql) throws OrmException { + executor.execute(sql); + } + + @Test + public void testListSequences() throws OrmException, SQLException { + assertTrue(dialect.listSequences(db).isEmpty()); + + execute("CREATE SEQUENCE cnt"); + execute("CREATE TABLE foo (cnt INT)"); + + Set<String> s = dialect.listSequences(db); + assertEquals(1, s.size()); + assertTrue(s.contains("cnt")); + assertFalse(s.contains("foo")); + } + + @Test + public void testListTables() throws OrmException, SQLException { + assertTrue(dialect.listTables(db).isEmpty()); + + execute("CREATE SEQUENCE cnt"); + execute("CREATE TABLE foo (cnt INT)"); + + Set<String> s = dialect.listTables(db); + assertEquals(1, s.size()); + assertFalse(s.contains("cnt")); + assertTrue(s.contains("foo")); + } + + @Test + public void testListIndexes() throws OrmException, SQLException { + assertTrue(dialect.listTables(db).isEmpty()); + + execute("CREATE SEQUENCE cnt"); + execute("CREATE TABLE foo (cnt INT, bar INT, baz INT)"); + execute("CREATE UNIQUE INDEX FOO_PRIMARY_IND ON foo(cnt)"); + execute("CREATE INDEX FOO_SECOND_IND ON foo(bar, baz)"); + + Set<String> s = dialect.listIndexes(db, "foo"); + assertEquals(2, s.size()); + assertTrue(s.contains("foo_primary_ind")); + assertTrue(s.contains("foo_second_ind")); + + dialect.dropIndex(executor, "foo", "foo_primary_ind"); + dialect.dropIndex(executor, "foo", "foo_second_ind"); + assertEquals(Collections.emptySet(), dialect.listIndexes(db, "foo")); + } + + @Test + public void testUpgradeSchema() throws SQLException, OrmException { + final PhoneBookDb p = phoneBook.open(); + try { + p.updateSchema(executor); + + execute("CREATE SEQUENCE cnt"); + execute("CREATE TABLE foo (cnt INT)"); + + execute("ALTER TABLE people ADD fake_name VARCHAR(20)"); + execute("ALTER TABLE people DROP COLUMN fake_name"); + // This is needed because table is put in maintenance mode + // when columns were dropped in DDL statement + execute("call sysproc.admin_cmd ('reorg table people')"); + execute("DROP TABLE addresses"); + execute("DROP SEQUENCE address_id"); + + Set<String> sequences, tables; + + p.updateSchema(executor); + sequences = dialect.listSequences(db); + tables = dialect.listTables(db); + + assertTrue(sequences.contains("cnt")); + assertTrue(tables.contains("foo")); + + assertTrue(sequences.contains("address_id")); + assertTrue(tables.contains("addresses")); + + p.pruneSchema(executor); + sequences = dialect.listSequences(db); + tables = dialect.listTables(db); + assertFalse(sequences.contains("cnt")); + assertFalse(tables.contains("foo")); + + final Person.Key pk = new Person.Key("Bob"); + int nextAddressId = p.nextAddressId(); + final Person bob = new Person(pk, nextAddressId); + p.people().insert(Collections.singleton(bob)); + + final Address addr = + new Address(new Address.Key(pk, "home"), "some place"); + p.addresses().insert(Collections.singleton(addr)); + } finally { + p.close(); + } + } + + @Test + public void testRenameTable() throws SQLException, OrmException { + assertTrue(dialect.listTables(db).isEmpty()); + execute("CREATE TABLE foo (cnt INT)"); + Set<String> s = dialect.listTables(db); + assertEquals(1, s.size()); + assertTrue(s.contains("foo")); + final PhoneBookDb p = phoneBook.open(); + try { + ((JdbcSchema) p).renameTable(executor, "foo", "bar"); + } finally { + p.close(); + } + s = dialect.listTables(db); + assertTrue(s.contains("bar")); + assertFalse(s.contains("for")); + } +}