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"));
+ }
+}
