Add support for Apache Derby database

Renaming of a column isn't supported as long as a constraint exists on
the column. See DialectDerbyTest.testUpgradeSchema() for a workaround.

Change-Id: I2dd50e3122f5ca59c6712dec49cdd004c84173a4
Bug: Issue 3441
diff --git a/pom.xml b/pom.xml
index 4023d0c..c13dd96 100644
--- a/pom.xml
+++ b/pom.xml
@@ -433,6 +433,13 @@
     </dependency>
 
     <dependency>
+      <groupId>org.apache.derby</groupId>
+      <artifactId>derby</artifactId>
+      <version>10.11.1.1</version>
+      <scope>test</scope>
+    </dependency>
+
+    <dependency>
       <groupId>com.h2database</groupId>
       <artifactId>h2</artifactId>
       <version>1.3.168</version>
diff --git a/src/main/java/com/google/gwtorm/schema/QueryModel.java b/src/main/java/com/google/gwtorm/schema/QueryModel.java
index 9fa3485..149e3d7 100644
--- a/src/main/java/com/google/gwtorm/schema/QueryModel.java
+++ b/src/main/java/com/google/gwtorm/schema/QueryModel.java
@@ -341,8 +341,8 @@
           final Tree p = node.getChild(0);
           if (p.getType() == QueryParser.CONSTANT_INTEGER
               || p.getType() == QueryParser.PLACEHOLDER) {
-            fmt.buf.append(" LIMIT ");
-            fmt.buf.append(p.getText());
+            fmt.buf.append(' ');
+            fmt.buf.append(fmt.dialect.getLimitSql(p.getText()));
           }
         }
         break;
diff --git a/src/main/java/com/google/gwtorm/schema/sql/DialectDerby.java b/src/main/java/com/google/gwtorm/schema/sql/DialectDerby.java
new file mode 100644
index 0000000..d82a791
--- /dev/null
+++ b/src/main/java/com/google/gwtorm/schema/sql/DialectDerby.java
@@ -0,0 +1,124 @@
+// 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.schema.SequenceModel;
+import com.google.gwtorm.server.OrmDuplicateKeyException;
+import com.google.gwtorm.server.OrmException;
+import com.google.gwtorm.server.Sequence;
+import com.google.gwtorm.server.StatementExecutor;
+
+import java.sql.Connection;
+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 <a href="https://db.apache.org/derby//">Apache Derby</a> */
+public class DialectDerby extends SqlDialect {
+
+  public DialectDerby() {
+    typeNames.put(Types.BIGINT, "BIGINT");
+    typeNames.put(Types.LONGVARCHAR, "LONG VARCHAR");
+
+    /* model doesn't seem to have a length... :-(
+     * Compare SqlByteArrayType and SqlStrinTypeInfo -> getSqlType()
+     */
+    typeNames.put(Types.VARBINARY, "VARCHAR (32672) FOR BIT DATA");
+  }
+
+  @Override
+  public boolean isStatementDelimiterSupported() {
+    return false;
+  }
+
+  @Override
+  public boolean handles(String url, Connection c) {
+    return url.startsWith("jdbc:derby:");
+  }
+
+  @Override
+  public Set<String> listSequences(Connection db) throws SQLException {
+    Statement s = db.createStatement();
+    try {
+      ResultSet rs = s.executeQuery("select sequencename from sys.syssequences");
+      try {
+        Set<String> sequences = new HashSet<>();
+        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 {
+    StringBuffer sb = new StringBuffer();
+    sb.append("RENAME COLUMN ");
+    sb.append(tableName);
+    sb.append('.');
+    sb.append(fromColumn);
+    sb.append(" TO ");
+    sb.append(col.getColumnName());
+    e.execute(sb.toString());
+  }
+
+  @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);
+    r.append(" ");
+    e.execute(r.toString());
+  }
+
+  @Override
+  protected String getNextSequenceValueSql(String seqname) {
+    return "VALUES (NEXT VALUE FOR " + seqname + ")";
+  }
+
+  @Override
+  public String getDropSequenceSql(String name) {
+    return "DROP SEQUENCE " + name + " RESTRICT";
+  }
+
+  @Override
+  public String getLimitSql(String limit) {
+    return "FETCH FIRST " + limit + " ROWS ONLY";
+  }
+
+  @Override
+  public OrmException convertError(String op, String entity, SQLException err) {
+    int state = getSQLStateInt(err);
+    switch (state) {
+      case 23505: // DUPLICATE_KEY_1
+        return new OrmDuplicateKeyException(entity, err);
+      default:
+        return super.convertError(op, entity, err);
+    }
+  }
+}
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 66f444d..5e10369 100644
--- a/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java
+++ b/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java
@@ -41,6 +41,7 @@
 
   static {
     DIALECTS.add(new DialectDB2());
+    DIALECTS.add(new DialectDerby());
     DIALECTS.add(new DialectH2());
     DIALECTS.add(new DialectPostgreSQL());
     DIALECTS.add(new DialectMySQL());
@@ -465,4 +466,13 @@
   public boolean isStatementDelimiterSupported() {
     return true;
   }
+
+  /**
+   * 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;
+  }
 }
diff --git a/src/test/java/com/google/gwtorm/schema/sql/DialectDerbyTest.java b/src/test/java/com/google/gwtorm/schema/sql/DialectDerbyTest.java
new file mode 100644
index 0000000..bbf5c94
--- /dev/null
+++ b/src/test/java/com/google/gwtorm/schema/sql/DialectDerbyTest.java
@@ -0,0 +1,252 @@
+// 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.Assert.fail;
+
+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.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.List;
+import java.util.Properties;
+import java.util.Set;
+
+public class DialectDerbyTest extends SqlDialectTest {
+
+  private static final String CONNECT = "jdbc:derby:memory:DialectDerbyTest";
+
+  @Before
+  public void setUp() throws Exception {
+    db = DriverManager.getConnection(CONNECT + ";create=true");
+    executor = new JdbcExecutor(db);
+    dialect = new DialectDerby().refine(db);
+
+    final Properties p = new Properties();
+    p.setProperty("driver", org.apache.derby.jdbc.EmbeddedDriver.class.getName());
+    p.setProperty("url", db.getMetaData().getURL());
+
+    phoneBook =
+        new Database<>(new SimpleDataSource(p), PhoneBookDb.class);
+    phoneBook2 =
+        new Database<>(new SimpleDataSource(p), PhoneBookDb2.class);
+  }
+
+  @After
+  public void tearDown() {
+    try {
+      DriverManager.getConnection(CONNECT + ";drop=true");
+    } catch (SQLException e) {
+      /* as stated in the derby reference manual,
+       * section "Setting attributes for the database connection URL"
+       * https://db.apache.org/derby/docs/10.11/ref/rrefattribdrop.html
+       * "The attribute generates the SQLException 08006 if successful.
+       *  If the database does not exist, it generates an error
+       *  reporting that the database could not be found."
+       *  So we can safely ignore this SQLException here.
+       */
+    }
+
+    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"));
+
+    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 COLUMN fake_name VARCHAR(20)");
+      execute("ALTER TABLE people DROP COLUMN registered");
+
+      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(Collections.singleton(bob));
+
+      final Address addr =
+          new Address(new Address.Key(pk, "home"), "some place");
+      p.addresses().insert(Collections.singleton(addr));
+    } finally {
+      p.close();
+    }
+
+    /* rename a column in an existing table to match the new schema */
+    final PhoneBookDb2 p2 = phoneBook2.open();
+
+    /* As Apache Derby doesn't support renaming a column as long as a constraint
+     * exists on the column, we need to drop the constraint manually.
+     * Currently a CHECK constraint is created by gwtorm for all fields with
+     * type SqlBooleanTypeInfo.
+     * See also https://db.apache.org/derby/docs/10.11/ref/rrefsqljrenamecolumnstatement.html
+     * for more information about the restriction of the RENAME COLUMN command.
+     */
+    String sqlCatalog =
+          "select c.constraintname, ch.checkdefinition"
+        + " from sys.sysconstraints c, sys.systables t, sys.syschecks ch"
+        + " WHERE c.tableid = t.tableid"
+        + "   and t.tablename = ?"
+        + "   and c.constraintid = ch.constraintid";
+    PreparedStatement ps = db.prepareStatement(sqlCatalog);
+    String tableName = "PEOPLE";
+    ps.setString(1, tableName);
+    try {
+      ResultSet rs = ps.executeQuery();
+      List<String> constraintNames = new ArrayList<>();
+      List<String> checkDefs = new ArrayList<>();
+      while (rs.next()) {
+        if (rs.getString(2).contains("registered")) {
+          constraintNames.add(rs.getString(1));
+          checkDefs.add(rs.getString(2).replace("registered", "is_registered"));
+          break;
+         }
+      }
+      rs.close();
+      ps.close();
+      if (constraintNames.isEmpty()) {
+        fail("Constraint not found");
+      }
+      for (String c: constraintNames) {
+        execute("alter table " + tableName + " drop check " + c);
+      }
+      ((JdbcSchema) p2).renameField(executor, "people", "registered",
+          "isRegistered");
+      for (String cd: checkDefs) {
+        execute("alter table " + tableName + " add check "+ cd);
+      }
+    } catch (OrmException e) {
+      fail(e.getMessage());
+    } 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"));
+  }
+}