Add dialect for SAP MaxDB
A new database dialect is added to support SAP MaxDB
(http://maxdb.sap.com/).
The Dialect suppports special handling for JDBC batching:
SAP MaxDB returns SUCCESS_NO_INFO upon executing a JDBC prepared
statement batch. However, the getUpdateCount method of the prepared
statement returns the total number of rows updated by the batch
execution. This allows for an efficient implementation of the
SQLDialect.executeBatch method. For an UPSERT, the UPDATE operation
needs to be attempted for individual rows.
Change-Id: I9385bafe481152ce0c1471f2a7f89b1db5abac80
Signed-off-by: Adrian Goerler <adrian.goerler@sap.com>
diff --git a/README_MAXDB b/README_MAXDB
new file mode 100644
index 0000000..7230e5b
--- /dev/null
+++ b/README_MAXDB
@@ -0,0 +1,23 @@
+To test DialectMaxDB a SAP MaxDB JDBC driver "sapdbc.jar" is needed. It is
+not available in a public maven repository. However, the driver can be found
+in your MaxDB installation at the following location:
+
+- on Windows 64bit at "C:\Program Files\sdb\MaxDB\runtime\jar\sapdbc.jar"
+- on Linux at "/opt/sdb/MaxDB/runtime/jar/sapdbc.jar"
+
+To execute tests on MaxDB, you firstly need to create a test user with an
+associated empty schema in your database. Then you can execute the tests
+using maven with the profile "maxdb". The following properties need to be set:
+
+maxdb.driver.jar=<path to maxdb jdbc driver>
+maxdb.url=<url of test database>
+maxdb.user=<user name>
+maxdb.password=<password of test user>
+
+So the complete command would be:
+
+mvn package -P maxdb
+ -Dmaxdb.driver.jar=<path to maxdb jdbc driver>
+ -Dmaxdb.url=<url of test database>
+ -Dmaxdb.user=<user name>
+ -Dmaxdb.password=<password of test user>
diff --git a/pom.xml b/pom.xml
index 7a62559..394cadc 100644
--- a/pom.xml
+++ b/pom.xml
@@ -265,6 +265,23 @@
</dependencies>
</profile>
<profile>
+ <id>maxdb</id>
+ <build>
+ <plugins>
+ <plugin>
+ <groupId>org.apache.maven.plugins</groupId>
+ <artifactId>maven-surefire-plugin</artifactId>
+ <version>2.17</version>
+ <configuration>
+ <additionalClasspathElements>
+ <additionalClasspathElement>${maxdb.driver.jar}</additionalClasspathElement>
+ </additionalClasspathElements>
+ </configuration>
+ </plugin>
+ </plugins>
+ </build>
+ </profile>
+ <profile>
<id>skip-proprietary-databases</id>
<activation>
<activeByDefault>true</activeByDefault>
diff --git a/src/main/java/com/google/gwtorm/schema/sql/DialectMaxDB.java b/src/main/java/com/google/gwtorm/schema/sql/DialectMaxDB.java
new file mode 100644
index 0000000..b1cbc07
--- /dev/null
+++ b/src/main/java/com/google/gwtorm/schema/sql/DialectMaxDB.java
@@ -0,0 +1,119 @@
+// Copyright (C) 2014 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;
+
+public class DialectMaxDB extends SqlDialect {
+
+ public DialectMaxDB() {
+ typeNames.put(Types.BIGINT, "FIXED (19,0)");
+ typeNames.put(Types.LONGVARCHAR, "LONG UNICODE");
+ }
+
+ @Override
+ public boolean canDetermineIndividualBatchUpdateCounts() {
+ return false;
+ }
+
+ @Override
+ public boolean canDetermineTotalBatchUpdateCount() {
+ return true;
+ }
+
+ @Override
+ public int executeBatch(PreparedStatement ps) throws SQLException {
+ ps.executeBatch();
+ return ps.getUpdateCount(); // total number of rows updated (on MaxDB)
+ }
+
+ @Override
+ public Set<String> listSequences(Connection conn) throws SQLException {
+ final Statement s = conn.createStatement();
+ try {
+ // lists sequences from schema associated with the current connection only
+ final ResultSet rs =
+ s.executeQuery("SELECT sequence_name FROM sequences");
+ try {
+ HashSet<String> sequences = new HashSet<String>();
+ while (rs.next()) {
+ sequences.add(rs.getString(1).toLowerCase());
+ }
+ return sequences;
+ } finally {
+ rs.close();
+ }
+ } finally {
+ s.close();
+ }
+ }
+
+ @Override
+ public void renameColumn(StatementExecutor e, String tableName,
+ String fromColumn, ColumnModel col) throws OrmException {
+ final StringBuilder s = new StringBuilder();
+ s.append("RENAME COLUMN ");
+ s.append(tableName).append(".").append(fromColumn);
+ s.append(" TO ");
+ s.append(col.getColumnName());
+ e.execute(s.toString());
+ }
+
+ @Override
+ public OrmException convertError(String op, String entity, SQLException err) {
+ int sqlstate = getSQLStateInt(err);
+ if (sqlstate == 23000) { // UNIQUE CONSTRAINT VIOLATION
+ int errorCode = err.getErrorCode();
+ if (errorCode == 200 || errorCode == -20) { // Duplicate Key
+ return new OrmDuplicateKeyException(entity, err);
+ }
+ }
+ return super.convertError(op, entity, err);
+ }
+
+ @Override
+ public String getNextSequenceValueSql(String seqname) {
+ return "SELECT " + seqname + ".nextval FROM dual";
+ }
+
+ @Override
+ public boolean handles(String url, Connection c) throws SQLException {
+ return url.startsWith("jdbc:sapdb:");
+ }
+
+ @Override
+ public void renameTable(StatementExecutor e, String from, String to)
+ throws OrmException {
+ final StringBuilder r = new StringBuilder();
+ r.append("RENAME TABLE ");
+ r.append(from);
+ r.append(" TO ");
+ r.append(to);
+ e.execute(r.toString());
+ }
+
+}
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 47b7204..fdfe6c8 100644
--- a/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java
+++ b/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java
@@ -44,6 +44,7 @@
DIALECTS.add(new DialectPostgreSQL());
DIALECTS.add(new DialectMySQL());
DIALECTS.add(new DialectOracle());
+ DIALECTS.add(new DialectMaxDB());
}
public static void register(SqlDialect dialect) {
diff --git a/src/test/java/com/google/gwtorm/data/Person.java b/src/test/java/com/google/gwtorm/data/Person.java
index 4984802..38644da 100644
--- a/src/test/java/com/google/gwtorm/data/Person.java
+++ b/src/test/java/com/google/gwtorm/data/Person.java
@@ -67,6 +67,10 @@
return age;
}
+ public void setAge(int age) {
+ this.age = age;
+ }
+
public boolean isRegistered() {
return registered;
}
diff --git a/src/test/java/com/google/gwtorm/schema/sql/DialectMaxDBTest.java b/src/test/java/com/google/gwtorm/schema/sql/DialectMaxDBTest.java
new file mode 100644
index 0000000..bea6ae8
--- /dev/null
+++ b/src/test/java/com/google/gwtorm/schema/sql/DialectMaxDBTest.java
@@ -0,0 +1,329 @@
+// Copyright 2014 Google Inc.
+//
+// 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 java.util.Arrays.asList;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertNotNull;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+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.OrmDuplicateKeyException;
+import com.google.gwtorm.server.OrmException;
+
+import org.junit.After;
+import org.junit.Before;
+import org.junit.Test;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.util.Properties;
+import java.util.Set;
+
+public class DialectMaxDBTest {
+ private static final String MAXDB_URL_KEY = "maxdb.url";
+ private static final String MAXDB_USER_KEY = "maxdb.user";
+ private static final String MAXDB_PASSWORD_KEY = "maxdb.password";
+ private static final String MAXDB_DRIVER = "com.sap.dbtech.jdbc.DriverSapDB";
+ private Connection db;
+ private JdbcExecutor executor;
+ private SqlDialect dialect;
+ private Database<PhoneBookDb> phoneBook;
+ private Database<PhoneBookDb2> phoneBook2;
+
+ @Before
+ public void setUp() throws Exception {
+ try {
+ Class.forName(MAXDB_DRIVER);
+ } catch (Exception e) {
+ assumeNoException(e);
+ }
+
+ final String url = System.getProperty(MAXDB_URL_KEY);
+ final String user = System.getProperty(MAXDB_USER_KEY);
+ final String pass = System.getProperty(MAXDB_PASSWORD_KEY);
+
+ db = DriverManager.getConnection(url, user, pass);
+ executor = new JdbcExecutor(db);
+ dialect = new DialectMaxDB().refine(db);
+
+ final Properties p = new Properties();
+ p.setProperty("driver", MAXDB_DRIVER);
+ p.setProperty("url", db.getMetaData().getURL());
+ p.setProperty("user", user);
+ p.setProperty("password", pass);
+ phoneBook =
+ new Database<PhoneBookDb>(new SimpleDataSource(p), PhoneBookDb.class);
+ phoneBook2 =
+ new Database<PhoneBookDb2>(new SimpleDataSource(p), PhoneBookDb2.class);
+
+ drop("SEQUENCE address_id");
+ drop("SEQUENCE cnt");
+
+ drop("TABLE addresses");
+ drop("TABLE foo");
+ drop("TABLE bar");
+ drop("TABLE people");
+ }
+
+ private void drop(String drop) {
+ try {
+ execute("DROP " + drop);
+ } catch (OrmException e) {
+ }
+ }
+
+ @After
+ public void tearDown() {
+ 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 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"));
+ }
+
+ @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 registered");
+ 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");
+ final Person bob = new Person(pk, p.nextAddressId());
+ p.people().insert(asList(bob));
+
+ final Address addr =
+ new Address(new Address.Key(pk, "home"), "some place");
+ p.addresses().insert(asList(addr));
+ } finally {
+ p.close();
+ }
+
+ final PhoneBookDb2 p2 = phoneBook2.open();
+ try {
+ ((JdbcSchema) p2).renameField(executor, "people", "registered",
+ "isRegistered");
+ } finally {
+ p2.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"));
+ }
+
+ @Test
+ public void testInsert() throws OrmException {
+ final PhoneBookDb p = phoneBook.open();
+ try {
+ p.updateSchema(executor);
+
+ final Person.Key pk = new Person.Key("Bob");
+ final Person bob = new Person(pk, p.nextAddressId());
+ p.people().insert(asList(bob));
+
+ try {
+ p.people().insert(asList(bob));
+ fail();
+ } catch (OrmDuplicateKeyException duprec) {
+ // expected
+ }
+ } finally {
+ p.close();
+ }
+ }
+
+ @Test
+ public void testConstraintViolationOnIndex() throws OrmException {
+ final PhoneBookDb p = phoneBook.open();
+ try {
+ p.updateSchema(executor);
+
+ execute("CREATE UNIQUE INDEX idx ON people (age)");
+ try {
+
+ final Person.Key pk = new Person.Key("Bob");
+ final Person bob = new Person(pk, p.nextAddressId());
+ bob.setAge(40);
+ p.people().insert(asList(bob));
+
+ final Person.Key joePk = new Person.Key("Joe");
+ Person joe = new Person(joePk, p.nextAddressId());
+ joe.setAge(40);
+ try {
+ p.people().insert(asList(joe));
+ fail();
+ } catch (OrmDuplicateKeyException duprec) {
+ fail();
+ } catch (OrmException noDuprec) {
+ // expeceted
+ }
+ } finally {
+ execute("DROP INDEX idx ON people");
+ }
+ } finally {
+ p.close();
+ }
+ }
+
+ @Test
+ public void testUpdate() throws OrmException {
+ final PhoneBookDb p = phoneBook.open();
+ try {
+ p.updateSchema(executor);
+
+ final Person.Key pk = new Person.Key("Bob");
+ Person bob = new Person(pk, p.nextAddressId());
+ bob.setAge(40);
+ p.people().insert(asList(bob));
+
+ bob.setAge(50);
+ p.people().update(asList(bob));
+
+ bob = p.people().get(pk);
+ assertEquals(50, bob.age());
+ } finally {
+ p.close();
+ }
+ }
+
+ @Test
+ public void testUpsert() throws OrmException {
+ final PhoneBookDb p = phoneBook.open();
+ try {
+ p.updateSchema(executor);
+
+ final Person.Key bobPk = new Person.Key("Bob");
+ Person bob = new Person(bobPk, p.nextAddressId());
+ bob.setAge(40);
+ p.people().insert(asList(bob));
+
+ final Person.Key joePk = new Person.Key("Joe");
+ Person joe = new Person(joePk, p.nextAddressId());
+ bob.setAge(50);
+ p.people().upsert(asList(bob, joe));
+
+ bob = p.people().get(bobPk);
+ assertEquals(50, bob.age());
+ assertNotNull(p.people().get(joePk));
+ } finally {
+ p.close();
+ }
+ }
+
+}
