Add dialect for SAP HANA A new database dialect is added to support SAP HANA [1]. The dialect uses the column store of the HANA database and hence needs to create tables using a 'CREATE COLUMN TABLE' statement. A new method SQLDialect#getTableTypeSql is introduced to support this: its return value defaults to "TABLE" but is overwritten by "COLUMN TABLE" in the HANA dialect. [1] http://help.sap.com/hana_appliance/ Change-Id: Ib733610cb826d17d72defd71a9e2c5f0fc7ce154 Signed-off-by: Adrian Goerler <adrian.goerler@sap.com>
diff --git a/README_HANA b/README_HANA new file mode 100644 index 0000000..f9d71aa --- /dev/null +++ b/README_HANA
@@ -0,0 +1,22 @@ +To test DialectHANA a SAP HANA JDBC driver "ngdbc.jar" is +needed. It is not available in a public maven repository. However, the driver +can be found as described here: [1]. + +To execute tests on HANA, 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 "hana". The following properties need to be set: + + hana.driver.jar=<path to HANA JDBC driver> + hana.url=<url of test database> + hana.user=<user name> + hana.password=<password of test user> + +So the complete command would be: + + mvn package -P hana + -Dhana.driver.jar=<path to HANA JDBC driver> + -Dhana.url=<url of test database> + -Dhana.user=<user name> + -Dhana.password=<password of test user> + +[1] http://help.sap.com/saphelp_hanaplatform/helpdata/en/ff/15928cf5594d78b841fbbe649f04b4/frameset.htm \ No newline at end of file
diff --git a/pom.xml b/pom.xml index c13dd96..49de1c2 100644 --- a/pom.xml +++ b/pom.xml
@@ -304,6 +304,23 @@ </build> </profile> <profile> + <id>hana</id> + <build> + <plugins> + <plugin> + <groupId>org.apache.maven.plugins</groupId> + <artifactId>maven-surefire-plugin</artifactId> + <version>2.17</version> + <configuration> + <additionalClasspathElements> + <additionalClasspathElement>${hana.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/RelationModel.java b/src/main/java/com/google/gwtorm/schema/RelationModel.java index f5b6c16..ebfb050 100644 --- a/src/main/java/com/google/gwtorm/schema/RelationModel.java +++ b/src/main/java/com/google/gwtorm/schema/RelationModel.java
@@ -205,7 +205,9 @@ public String getCreateTableSql(final SqlDialect dialect) { final StringBuilder r = new StringBuilder(); - r.append("CREATE TABLE "); + r.append("CREATE "); + r.append(dialect.getTableTypeSql()); + r.append(" "); r.append(relationName); r.append(" (\n");
diff --git a/src/main/java/com/google/gwtorm/schema/sql/DialectHANA.java b/src/main/java/com/google/gwtorm/schema/sql/DialectHANA.java new file mode 100644 index 0000000..c67fca7 --- /dev/null +++ b/src/main/java/com/google/gwtorm/schema/sql/DialectHANA.java
@@ -0,0 +1,205 @@ +// Copyright (C) 2016 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.client.Column; +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.Locale; +import java.util.Set; + +public class DialectHANA extends SqlDialect { + + public DialectHANA() { + types.put(String.class, new SqlStringTypeInfo() { + @Override + public String getSqlType(ColumnModel col, SqlDialect dialect) { + Column column = col.getColumnAnnotation(); + StringBuilder r = new StringBuilder(); + + if (column.length() <= 0) { + r.append("NVARCHAR(255)"); + if (col.isNotNull()) { + r.append(" DEFAULT ''"); + } + } else if (column.length() <= 5000) { + r.append("NVARCHAR(" + column.length() + ")"); + if (col.isNotNull()) { + r.append(" DEFAULT ''"); + } + } else { + r.append("NCLOB"); + } + + if (col.isNotNull()) { + r.append(" NOT NULL"); + } + + return r.toString(); + } + }); + types.put(Boolean.TYPE, new SqlBooleanTypeInfo() { + @Override + public String getCheckConstraint(ColumnModel column, SqlDialect dialect) { + // check constraints are not supported by HANA + return null; + } + }); + typeNames.put(Types.INTEGER, "INTEGER"); + } + + @Override + public void addColumn(StatementExecutor e, String tableName, ColumnModel col) + throws OrmException { + StringBuilder r = new StringBuilder(); + r.append("ALTER TABLE "); + r.append(tableName); + r.append(" ADD ("); + r.append(col.getColumnName()); + r.append(" "); + r.append(getSqlTypeInfo(col).getSqlType(col, this)); + r.append(")"); + e.execute(r.toString()); + } + + @Override + public void dropColumn(StatementExecutor e, String tableName, String column) + throws OrmException { + StringBuilder r = new StringBuilder(); + r.append("ALTER TABLE "); + r.append(tableName); + r.append(" DROP ("); + r.append(column); + r.append(")"); + e.execute(r.toString()); + } + + @Override + public boolean handles(String url, Connection c) throws SQLException { + return url.startsWith("jdbc:sap://"); + } + + @Override + public Set<String> listSequences(Connection db) throws SQLException { + return listNamesFromSystemTable(db, "SEQUENCE_NAME", "SEQUENCES"); + } + + @Override + public Set<String> listTables(Connection db) throws SQLException { + return listNamesFromSystemTable(db, "TABLE_NAME", "TABLES"); + } + + @Override + public Set<String> listIndexes(Connection db, String tableName) + throws SQLException { + return listNamesFromSystemTable(db, "INDEX_NAME", "INDEXES"); + } + + @Override + public Set<String> listColumns(Connection db, String tableName) + throws SQLException { + String sql = + "SELECT COLUMN_NAME FROM TABLE_COLUMNS" + + " WHERE SCHEMA_NAME = CURRENT_SCHEMA AND TABLE_NAME = ?"; + try (PreparedStatement s = db.prepareStatement(sql)) { + s.setString(1, tableName.toUpperCase(Locale.US)); + try (ResultSet rs = s.executeQuery()) { + return names(rs); + } + } + } + + @Override + public boolean isStatementDelimiterSupported() { + return false; + } + + private static Set<String> names(ResultSet rs) throws SQLException { + HashSet<String> names = new HashSet<>(); + while (rs.next()) { + names.add(rs.getString(1).toLowerCase(Locale.US)); + } + return names; + } + + private static Set<String> listNamesFromSystemTable(Connection db, + String columnName, String tableName) throws SQLException { + StringBuilder r = new StringBuilder(); + r.append("SELECT "); + r.append(columnName); + r.append(" FROM "); + r.append(tableName); + r.append(" WHERE SCHEMA_NAME = CURRENT_SCHEMA"); + try (Statement s = db.createStatement(); + ResultSet rs = s.executeQuery(r.toString())) { + return names(rs); + } + } + + @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); + r.append(" "); + e.execute(r.toString()); + } + + @Override + public String getTableTypeSql() { + return "COLUMN TABLE"; + } + + @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 == 144 || errorCode == 301) { // Duplicate Key + return new OrmDuplicateKeyException(entity, err); + } + } + return super.convertError(op, entity, err); + } + + @Override + public void renameColumn(StatementExecutor e, String tableName, + String fromColumn, ColumnModel col) throws OrmException { + 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 + protected String getNextSequenceValueSql(String seqname) { + return "SELECT " + seqname + ".nextval FROM dummy"; + } +}
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 5e10369..98f1ae1 100644 --- a/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java +++ b/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java
@@ -47,6 +47,7 @@ DIALECTS.add(new DialectMySQL()); DIALECTS.add(new DialectOracle()); DIALECTS.add(new DialectMaxDB()); + DIALECTS.add(new DialectHANA()); } public static void register(SqlDialect dialect) { @@ -468,11 +469,20 @@ } /** - * get the SQL LIMIT command segment in the given dialect + * Get the SQL LIMIT command segment in the given dialect * @param limit the limit to apply to the result set (either a number or ?) * @return the SQL LIMIT command segment in the given dialect */ public String getLimitSql(String limit) { return "LIMIT " + limit; } + + /** + * Get the driver specific 'table type' to be used in a CREATE TABLE + * statement. When creating a CREATE TABLE statement the 'table type' is + * appended after a blank following the CREATE keyword. + */ + public String getTableTypeSql() { + return "TABLE"; + } }
diff --git a/src/test/java/com/google/gwtorm/schema/sql/DialectHANATest.java b/src/test/java/com/google/gwtorm/schema/sql/DialectHANATest.java new file mode 100644 index 0000000..83f34f4 --- /dev/null +++ b/src/test/java/com/google/gwtorm/schema/sql/DialectHANATest.java
@@ -0,0 +1,297 @@ +// Copyright (C) 2016 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 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.DriverManager; +import java.sql.SQLException; +import java.util.Collections; +import java.util.Properties; +import java.util.Set; + +public class DialectHANATest extends SqlDialectTest { + private static final String HANA_URL_KEY = "hana.url"; + private static final String HANA_USER_KEY = "hana.user"; + private static final String HANA_PASSWORD_KEY = "hana.password"; + private static final String HANA_DRIVER = "com.sap.db.jdbc.Driver"; + + @Before + public void setUp() throws Exception { + try { + Class.forName(HANA_DRIVER); + } catch (Exception e) { + assumeNoException(e); + } + + String url = System.getProperty(HANA_URL_KEY); + String user = System.getProperty(HANA_USER_KEY); + String pass = System.getProperty(HANA_PASSWORD_KEY); + + db = DriverManager.getConnection(url, user, pass); + executor = new JdbcExecutor(db); + dialect = new DialectHANA().refine(db); + + Properties p = new Properties(); + p.setProperty("driver", HANA_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); + } + + private void drop(String drop) { + try { + execute("DROP " + drop); + } catch (OrmException e) { + } + } + + @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 execute(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 { + 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 NVARCHAR(20))"); + execute("ALTER TABLE people DROP (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")); + + Person.Key pk = new Person.Key("Bob"); + Person bob = new Person(pk, p.nextAddressId()); + p.people().insert(asList(bob)); + + Address addr = new Address(new Address.Key(pk, "home"), "some place"); + p.addresses().insert(asList(addr)); + } finally { + p.close(); + } + + 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")); + 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 { + PhoneBookDb p = phoneBook.open(); + try { + p.updateSchema(executor); + + Person.Key pk = new Person.Key("Bob"); + 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 testUpdate() throws OrmException { + PhoneBookDb p = phoneBook.open(); + try { + p.updateSchema(executor); + + 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 { + PhoneBookDb p = phoneBook.open(); + try { + p.updateSchema(executor); + + Person.Key bobPk = new Person.Key("Bob"); + Person bob = new Person(bobPk, p.nextAddressId()); + bob.setAge(40); + p.people().insert(asList(bob)); + + 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(); + } + } +}