Implement schema upgrading
Change-Id: I3a82db2e8a2f446477806e93b236d5bd77957660
Signed-off-by: Shawn O. Pearce <sop@google.com>
diff --git a/README_ECLIPSE b/README_ECLIPSE
index 9b7cc01..8e3c06d 100644
--- a/README_ECLIPSE
+++ b/README_ECLIPSE
@@ -17,3 +17,25 @@
- Import... and select GoogleFormat.xml
+
+Unit Tests
+==========
+
+The unit tests assume there is a local PostgreSQL and MySQL server
+listening on their respective default ports, with the following
+database configuration:
+
+PostgreSQL:
+
+ createuser -S -D -R -E -P gwtorm
+ Password: gwtorm
+
+ createdb -E UTF-8 -O gwtorm gwtorm
+
+MySQL:
+
+ create user 'gwtorm'@'localhost' identified by 'gwtorm';
+ create database gwtorm;
+ alter database gwtorm charset=utf8;
+ grant all on gwtorm.* to 'gwtorm'@'localhost';
+ flush privileges;
diff --git a/pom.xml b/pom.xml
index 387b44b..de1e587 100644
--- a/pom.xml
+++ b/pom.xml
@@ -346,7 +346,21 @@
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
- <version>1.1.104</version>
+ <version>1.2.125</version>
+ <scope>test</scope>
+ </dependency>
+
+ <dependency>
+ <groupId>postgresql</groupId>
+ <artifactId>postgresql</artifactId>
+ <version>8.4-701.jdbc4</version>
+ <scope>test</scope>
+ </dependency>
+
+ <dependency>
+ <groupId>mysql</groupId>
+ <artifactId>mysql-connector-java</artifactId>
+ <version>5.1.10</version>
<scope>test</scope>
</dependency>
</dependencies>
diff --git a/src/main/java/com/google/gwtorm/client/Schema.java b/src/main/java/com/google/gwtorm/client/Schema.java
index 13def7c..09c4ba9 100644
--- a/src/main/java/com/google/gwtorm/client/Schema.java
+++ b/src/main/java/com/google/gwtorm/client/Schema.java
@@ -56,11 +56,24 @@
*/
public interface Schema {
/**
- * Automatically create the database tables.
+ * Add any missing columns, create any missing tables or sequences.
+ * <p>
+ * This method does not drop any unused columns or tables, leaving them intact
+ * for applications to continue to query after the update. Any unused columns
+ * that are NOT NULL are altered to accept NULL.
*
- * @throws OrmException tables already exist or create permission is denied.
+ * @throws OrmException one or more objects could not be added to the schema.
*/
- void createSchema() throws OrmException;
+ void updateSchema() throws OrmException;
+
+ /**
+ * Drop any unused columns, tables, or sequences.
+ * <p>
+ * This method destroys data, as columns may be removed entirely.
+ *
+ * @throws OrmException one or more drops could not be completed.
+ */
+ void pruneSchema() throws OrmException;
/**
* Begin a new transaction.
diff --git a/src/main/java/com/google/gwtorm/jdbc/JdbcSchema.java b/src/main/java/com/google/gwtorm/jdbc/JdbcSchema.java
index 9eba791..007d5f4 100644
--- a/src/main/java/com/google/gwtorm/jdbc/JdbcSchema.java
+++ b/src/main/java/com/google/gwtorm/jdbc/JdbcSchema.java
@@ -19,6 +19,7 @@
import com.google.gwtorm.client.OrmRunnable;
import com.google.gwtorm.client.Schema;
import com.google.gwtorm.client.Transaction;
+import com.google.gwtorm.schema.ColumnModel;
import com.google.gwtorm.schema.RelationModel;
import com.google.gwtorm.schema.SchemaModel;
import com.google.gwtorm.schema.SequenceModel;
@@ -27,6 +28,8 @@
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
+import java.util.HashSet;
+import java.util.Set;
/** Internal base class for implementations of {@link Schema}. */
public abstract class JdbcSchema implements Schema {
@@ -68,25 +71,136 @@
}
}
- public void createSchema() throws OrmException {
- final SqlDialect dialect = dbDef.getDialect();
- final SchemaModel model = dbDef.getSchemaModel();
+ public void updateSchema() throws OrmException {
try {
- final Statement stmt;
+ createSequences();
+ createRelations();
- stmt = getConnection().createStatement();
- try {
- for (final SequenceModel s : model.getSequences()) {
- stmt.execute(s.getCreateSequenceSql(dialect));
- }
- for (final RelationModel r : model.getRelations()) {
- stmt.execute(r.getCreateTableSql(dialect));
- }
- } finally {
- stmt.close();
+ for (final RelationModel rel : dbDef.getSchemaModel().getRelations()) {
+ addColumns(rel);
}
} catch (SQLException e) {
- throw new OrmException("Schema creation failure", e);
+ throw new OrmException("Schema update failure", e);
+ }
+ }
+
+ private void createSequences() throws SQLException {
+ final SqlDialect dialect = dbDef.getDialect();
+ final SchemaModel model = dbDef.getSchemaModel();
+ final Statement stmt = getConnection().createStatement();
+ try {
+ Set<String> have = dialect.listSequences(getConnection());
+ for (final SequenceModel s : model.getSequences()) {
+ if (!have.contains(s.getSequenceName().toLowerCase())) {
+ stmt.execute(s.getCreateSequenceSql(dialect));
+ }
+ }
+ } finally {
+ stmt.close();
+ }
+ }
+
+ private void createRelations() throws SQLException {
+ final SqlDialect dialect = dbDef.getDialect();
+ final SchemaModel model = dbDef.getSchemaModel();
+ final Statement stmt = getConnection().createStatement();
+ try {
+ Set<String> have = dialect.listTables(getConnection());
+ for (final RelationModel r : model.getRelations()) {
+ if (!have.contains(r.getRelationName().toLowerCase())) {
+ stmt.execute(r.getCreateTableSql(dialect));
+ }
+ }
+ } finally {
+ stmt.close();
+ }
+ }
+
+ private void addColumns(final RelationModel rel) throws SQLException {
+ final SqlDialect dialect = dbDef.getDialect();
+ final SchemaModel model = dbDef.getSchemaModel();
+ final Statement stmt = getConnection().createStatement();
+ try {
+ Set<String> have = dialect.listColumns( //
+ getConnection(), rel.getRelationName().toLowerCase());
+ for (final ColumnModel c : rel.getColumns()) {
+ if (!have.contains(c.getColumnName().toLowerCase())) {
+ dialect.addColumn(stmt, rel.getRelationName(), c);
+ }
+ }
+ } finally {
+ stmt.close();
+ }
+ }
+
+ public void pruneSchema() throws OrmException {
+ try {
+ pruneSequences();
+ pruneRelations();
+
+ for (final RelationModel rel : dbDef.getSchemaModel().getRelations()) {
+ pruneColumns(rel);
+ }
+ } catch (SQLException e) {
+ throw new OrmException("Schema prune failure", e);
+ }
+ }
+
+ private void pruneSequences() throws SQLException {
+ final SqlDialect dialect = dbDef.getDialect();
+ final SchemaModel model = dbDef.getSchemaModel();
+ final Statement stmt = getConnection().createStatement();
+ try {
+ HashSet<String> want = new HashSet<String>();
+ for (final SequenceModel s : model.getSequences()) {
+ want.add(s.getSequenceName().toLowerCase());
+ }
+ for (final String sequence : dialect.listSequences(getConnection())) {
+ if (!want.contains(sequence)) {
+ stmt.execute(dialect.getDropSequenceSql(sequence));
+ }
+ }
+ } finally {
+ stmt.close();
+ }
+ }
+
+ private void pruneRelations() throws SQLException {
+ final SqlDialect dialect = dbDef.getDialect();
+ final SchemaModel model = dbDef.getSchemaModel();
+ final Statement stmt = getConnection().createStatement();
+ try {
+ HashSet<String> want = new HashSet<String>();
+ for (final RelationModel r : model.getRelations()) {
+ want.add(r.getRelationName().toLowerCase());
+ }
+ for (final String table : dialect.listTables(getConnection())) {
+ if (!want.contains(table)) {
+ stmt.execute("DROP TABLE " + table);
+ }
+ }
+ } finally {
+ stmt.close();
+ }
+ }
+
+ private void pruneColumns(final RelationModel rel) throws SQLException {
+ final SqlDialect dialect = dbDef.getDialect();
+ final SchemaModel model = dbDef.getSchemaModel();
+ final Statement stmt = getConnection().createStatement();
+ try {
+ HashSet<String> want = new HashSet<String>();
+ for (final ColumnModel c : rel.getColumns()) {
+ want.add(c.getColumnName().toLowerCase());
+ }
+ for (String column : dialect.listColumns( //
+ getConnection(), rel.getRelationName().toLowerCase())) {
+ if (!want.contains(column)) {
+ dialect.dropColumn(stmt, rel.getRelationName(), column);
+ }
+ }
+ } finally {
+ stmt.close();
}
}
diff --git a/src/main/java/com/google/gwtorm/schema/RelationModel.java b/src/main/java/com/google/gwtorm/schema/RelationModel.java
index 06edd1d..726ebbf 100644
--- a/src/main/java/com/google/gwtorm/schema/RelationModel.java
+++ b/src/main/java/com/google/gwtorm/schema/RelationModel.java
@@ -209,6 +209,13 @@
r.append(col.getColumnName());
r.append(" ");
r.append(dialect.getSqlTypeInfo(col).getSqlType(col, dialect));
+
+ String check =
+ dialect.getSqlTypeInfo(col).getCheckConstraint(col, dialect);
+ if (check != null) {
+ r.append(' ');
+ r.append(check);
+ }
if (i.hasNext()) {
r.append(",");
}
diff --git a/src/main/java/com/google/gwtorm/schema/sql/DialectH2.java b/src/main/java/com/google/gwtorm/schema/sql/DialectH2.java
index 04380a1..8b79dd3 100644
--- a/src/main/java/com/google/gwtorm/schema/sql/DialectH2.java
+++ b/src/main/java/com/google/gwtorm/schema/sql/DialectH2.java
@@ -16,8 +16,14 @@
import com.google.gwtorm.client.OrmDuplicateKeyException;
import com.google.gwtorm.client.OrmException;
+import com.google.gwtorm.schema.ColumnModel;
+import java.sql.Connection;
+import java.sql.ResultSet;
import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.HashSet;
+import java.util.Set;
/** Dialect for <a href="http://www.h2database.com/">H2</a> */
public class DialectH2 extends SqlDialect {
@@ -38,4 +44,51 @@
public String getNextSequenceValueSql(final String seqname) {
return "SELECT NEXT VALUE FOR " + seqname;
}
+
+ @Override
+ public Set<String> listSequences(Connection db) throws SQLException {
+ Statement s = db.createStatement();
+ try {
+ ResultSet rs =
+ s.executeQuery("SELECT SEQUENCE_NAME"
+ + " FROM INFORMATION_SCHEMA.SEQUENCES"
+ + " WHERE SEQUENCE_SCHEMA = 'PUBLIC'");
+ 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 addColumn(Statement stmt, String tableName, ColumnModel col)
+ throws SQLException {
+ final 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));
+ stmt.execute(r.toString());
+
+ String check = getSqlTypeInfo(col).getCheckConstraint(col, this);
+ if (check != null) {
+ r.setLength(0);
+ r.append("ALTER TABLE ");
+ r.append(tableName);
+ r.append(" ADD CONSTRAINT ");
+ r.append(col.getColumnName() + "_check");
+ r.append(' ');
+ r.append(check);
+ stmt.execute(r.toString());
+ }
+ }
}
diff --git a/src/main/java/com/google/gwtorm/schema/sql/DialectMySQL.java b/src/main/java/com/google/gwtorm/schema/sql/DialectMySQL.java
index 0fa42cf..047e2fd 100644
--- a/src/main/java/com/google/gwtorm/schema/sql/DialectMySQL.java
+++ b/src/main/java/com/google/gwtorm/schema/sql/DialectMySQL.java
@@ -16,15 +16,17 @@
import com.google.gwtorm.client.Column;
import com.google.gwtorm.client.OrmException;
-import com.google.gwtorm.client.Sequence;
import com.google.gwtorm.schema.ColumnModel;
import com.google.gwtorm.schema.SequenceModel;
import java.sql.Connection;
+import java.sql.DatabaseMetaData;
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="http://www.mysql.com/">MySQL</a> */
public class DialectMySQL extends SqlDialect {
@@ -61,7 +63,6 @@
@Override
public String getCreateSequenceSql(final SequenceModel seq) {
- final Sequence s = seq.getSequence();
final StringBuilder r = new StringBuilder();
r.append("CREATE TABLE ");
r.append(seq.getSequenceName());
@@ -70,6 +71,11 @@
}
@Override
+ public String getDropSequenceSql(String name) {
+ return "DROP TABLE " + name;
+ }
+
+ @Override
public String getNextSequenceValueSql(final String seqname) {
return seqname;
}
@@ -101,4 +107,66 @@
throw convertError("sequence", seqname, e);
}
}
+
+ @Override
+ public Set<String> listTables(final Connection db) throws SQLException {
+ final String[] types = new String[] {"TABLE"};
+ final ResultSet rs = db.getMetaData().getTables(null, null, null, types);
+ try {
+ HashSet<String> tables = new HashSet<String>();
+ while (rs.next()) {
+ final String name = rs.getString("TABLE_NAME");
+ if (!isSequence(db, name)) {
+ tables.add(name.toLowerCase());
+ }
+ }
+ return tables;
+ } finally {
+ rs.close();
+ }
+ }
+
+ @Override
+ public Set<String> listSequences(final Connection db) throws SQLException {
+ final String[] types = new String[] {"TABLE"};
+ final ResultSet rs = db.getMetaData().getTables(null, null, null, types);
+ try {
+ HashSet<String> sequences = new HashSet<String>();
+ while (rs.next()) {
+ final String name = rs.getString("TABLE_NAME");
+ if (isSequence(db, name)) {
+ sequences.add(name.toLowerCase());
+ }
+ }
+ return sequences;
+ } finally {
+ rs.close();
+ }
+ }
+
+ private boolean isSequence(Connection db, String tableName)
+ throws SQLException {
+ final DatabaseMetaData meta = db.getMetaData();
+ if (meta.storesUpperCaseIdentifiers()) {
+ tableName = tableName.toUpperCase();
+ } else if (meta.storesLowerCaseIdentifiers()) {
+ tableName = tableName.toLowerCase();
+ }
+
+ ResultSet rs = meta.getColumns(null, null, tableName, null);
+ try {
+ int cnt = 0;
+ boolean serial = false;
+ while (rs.next()) {
+ cnt++;
+ if (rs.getInt("DATA_TYPE") == Types.BIGINT
+ && "YES".equalsIgnoreCase(rs.getString("IS_AUTOINCREMENT"))) {
+ serial = true;
+ }
+ }
+ return cnt == 1 && serial;
+ } finally {
+ rs.close();
+ }
+ }
}
diff --git a/src/main/java/com/google/gwtorm/schema/sql/DialectPostgreSQL.java b/src/main/java/com/google/gwtorm/schema/sql/DialectPostgreSQL.java
index 560b70a..bffc7ad 100644
--- a/src/main/java/com/google/gwtorm/schema/sql/DialectPostgreSQL.java
+++ b/src/main/java/com/google/gwtorm/schema/sql/DialectPostgreSQL.java
@@ -19,8 +19,12 @@
import com.google.gwtorm.schema.RelationModel;
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="http://www.postgresql.org/>PostgreSQL</a> */
public class DialectPostgreSQL extends SqlDialect {
@@ -66,6 +70,26 @@
sqlBuffer.append("WITH (OIDS = FALSE)");
}
+ @Override
+ public Set<String> listSequences(Connection db) throws SQLException {
+ Statement s = db.createStatement();
+ try {
+ ResultSet rs =
+ s.executeQuery("SELECT relname FROM pg_class WHERE relkind = 'S'");
+ try {
+ HashSet<String> sequences = new HashSet<String>();
+ while (rs.next()) {
+ sequences.add(rs.getString(1).toLowerCase());
+ }
+ return sequences;
+ } finally {
+ rs.close();
+ }
+ } finally {
+ s.close();
+ }
+ }
+
private static class Pre82 extends DialectPostgreSQL {
@Override
public void appendCreateTableStorage(final StringBuilder sqlBuffer,
diff --git a/src/main/java/com/google/gwtorm/schema/sql/SqlBooleanTypeInfo.java b/src/main/java/com/google/gwtorm/schema/sql/SqlBooleanTypeInfo.java
index 9828f4a..afea6b3 100644
--- a/src/main/java/com/google/gwtorm/schema/sql/SqlBooleanTypeInfo.java
+++ b/src/main/java/com/google/gwtorm/schema/sql/SqlBooleanTypeInfo.java
@@ -35,11 +35,19 @@
r.append(" DEFAULT " + f);
r.append(" NOT NULL");
}
- r.append(" CHECK (" + name + " IN (" + t + "," + f + "))");
return r.toString();
}
@Override
+ public String getCheckConstraint(final ColumnModel column,
+ final SqlDialect dialect) {
+ final String name = column.getColumnName();
+ final String t = getTrueLiteralValue();
+ final String f = getFalseLiteralValue();
+ return " CHECK (" + name + " IN (" + t + "," + f + "))";
+ }
+
+ @Override
protected String getJavaSqlTypeAlias() {
return "String";
}
diff --git a/src/main/java/com/google/gwtorm/schema/sql/SqlByteArrayTypeInfo.java b/src/main/java/com/google/gwtorm/schema/sql/SqlByteArrayTypeInfo.java
index 24b60c1..12ed53a 100644
--- a/src/main/java/com/google/gwtorm/schema/sql/SqlByteArrayTypeInfo.java
+++ b/src/main/java/com/google/gwtorm/schema/sql/SqlByteArrayTypeInfo.java
@@ -103,7 +103,7 @@
if (raw != null) {
ps.setBinaryStream(col, new ByteArrayInputStream(raw), raw.length);
} else {
- ps.setNull(col, Types.LONGVARCHAR);
+ ps.setNull(col, Types.VARBINARY);
}
}
}
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 7a8d243..540b334 100644
--- a/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java
+++ b/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java
@@ -21,12 +21,15 @@
import com.google.gwtorm.schema.SequenceModel;
import java.sql.Connection;
+import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.HashMap;
+import java.util.HashSet;
import java.util.Map;
+import java.util.Set;
public abstract class SqlDialect {
protected final Map<Class<?>, SqlTypeInfo> types;
@@ -163,6 +166,10 @@
return r.toString();
}
+ public String getDropSequenceSql(final String name) {
+ return "DROP SEQUENCE " + name;
+ }
+
/**
* Append driver specific storage parameters to a CREATE TABLE statement.
*
@@ -174,5 +181,108 @@
final RelationModel relationModel) {
}
+ /**
+ * List all tables in the current database schema.
+ *
+ * @param db connection to the schema.
+ * @return set of declared tables, in lowercase.
+ * @throws SQLException the tables cannot be listed.
+ */
+ public Set<String> listTables(final Connection db) throws SQLException {
+ final String[] types = new String[] {"TABLE"};
+ final ResultSet rs = db.getMetaData().getTables(null, null, null, types);
+ try {
+ HashSet<String> tables = new HashSet<String>();
+ while (rs.next()) {
+ tables.add(rs.getString("TABLE_NAME").toLowerCase());
+ }
+ return tables;
+ } finally {
+ rs.close();
+ }
+ }
+
+ /**
+ * List all sequences in the current database schema.
+ *
+ * @param db connection to the schema.
+ * @return set of declared sequences, in lowercase.
+ * @throws SQLException the sequence objects cannot be listed.
+ */
+ public abstract Set<String> listSequences(final Connection db)
+ throws SQLException;
+
+ /**
+ * List all columns in the given table name.
+ *
+ * @param db connection to the schema.
+ * @param tableName the table to list columns from, in lowercase.
+ * @return set of declared columns, in lowercase.
+ * @throws SQLException the columns cannot be listed from the relation.
+ */
+ public Set<String> listColumns(final Connection db, String tableName)
+ throws SQLException {
+ final DatabaseMetaData meta = db.getMetaData();
+ if (meta.storesUpperCaseIdentifiers()) {
+ tableName = tableName.toUpperCase();
+ } else if (meta.storesLowerCaseIdentifiers()) {
+ tableName = tableName.toLowerCase();
+ }
+
+ ResultSet rs = meta.getColumns(null, null, tableName, null);
+ try {
+ HashSet<String> columns = new HashSet<String>();
+ while (rs.next()) {
+ columns.add(rs.getString("COLUMN_NAME").toLowerCase());
+ }
+ return columns;
+ } finally {
+ rs.close();
+ }
+ }
+
+ /**
+ * Add one column to an existing table.
+ *
+ * @param stmt statement to use to execute the SQL command(s).
+ * @param tableName table to add the column onto.
+ * @param col definition of the column.
+ * @throws SQLException the column could not be added.
+ */
+ public void addColumn(Statement stmt, String tableName, ColumnModel col)
+ throws SQLException {
+ final 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));
+ String check = getSqlTypeInfo(col).getCheckConstraint(col, this);
+ if (check != null) {
+ r.append(' ');
+ r.append(check);
+ }
+ stmt.execute(r.toString());
+ }
+
+ /**
+ * Drop one column from an existing table.
+ *
+ * @param stmt statement to use to execute the SQL command(s).
+ * @param tableName table to add the column onto.
+ * @param column name of the column to drop.
+ * @throws SQLException the column could not be added.
+ */
+ public void dropColumn(Statement stmt, String tableName, String column)
+ throws SQLException {
+ final StringBuilder r = new StringBuilder();
+ r.append("ALTER TABLE ");
+ r.append(tableName);
+ r.append(" DROP COLUMN ");
+ r.append(column);
+ stmt.execute(r.toString());
+ }
+
public abstract String getNextSequenceValueSql(String seqname);
}
diff --git a/src/main/java/com/google/gwtorm/schema/sql/SqlTypeInfo.java b/src/main/java/com/google/gwtorm/schema/sql/SqlTypeInfo.java
index 35ed96a..ba9064e 100644
--- a/src/main/java/com/google/gwtorm/schema/sql/SqlTypeInfo.java
+++ b/src/main/java/com/google/gwtorm/schema/sql/SqlTypeInfo.java
@@ -29,6 +29,11 @@
public abstract String getSqlType(ColumnModel column, SqlDialect dialect);
+ public String getCheckConstraint(final ColumnModel column,
+ final SqlDialect dialect) {
+ return null;
+ }
+
protected abstract String getJavaSqlTypeAlias();
protected abstract int getSqlTypeConstant();
diff --git a/src/test/java/com/google/gwtorm/schema/sql/DialectH2Test.java b/src/test/java/com/google/gwtorm/schema/sql/DialectH2Test.java
new file mode 100644
index 0000000..d79bfcb
--- /dev/null
+++ b/src/test/java/com/google/gwtorm/schema/sql/DialectH2Test.java
@@ -0,0 +1,139 @@
+// Copyright 2009 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 com.google.gwtorm.client.OrmException;
+import com.google.gwtorm.data.PhoneBookDb;
+import com.google.gwtorm.data.TestAddress;
+import com.google.gwtorm.data.TestPerson;
+import com.google.gwtorm.jdbc.Database;
+import com.google.gwtorm.jdbc.SimpleDataSource;
+
+import junit.framework.TestCase;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Collections;
+import java.util.Properties;
+import java.util.Set;
+
+public class DialectH2Test extends TestCase {
+ private Connection db;
+ private SqlDialect dialect;
+ private Database<PhoneBookDb> phoneBook;
+
+ @Override
+ protected void setUp() throws Exception {
+ super.setUp();
+ org.h2.Driver.load();
+ db = DriverManager.getConnection("jdbc:h2:mem:DialectH2Test");
+ dialect = new DialectH2().refine(db);
+
+ final Properties p = new Properties();
+ p.setProperty("driver", org.h2.Driver.class.getName());
+ p.setProperty("url", db.getMetaData().getURL());
+ phoneBook =
+ new Database<PhoneBookDb>(new SimpleDataSource(p), PhoneBookDb.class);
+ }
+
+ @Override
+ protected void tearDown() {
+ 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 SQLException {
+ final Statement stmt = db.createStatement();
+ try {
+ stmt.execute(sql);
+ } finally {
+ stmt.close();
+ }
+ }
+
+ public void testListSequences() throws 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"));
+ }
+
+ public void testListTables() throws 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"));
+ }
+
+ public void testUpgradeSchema() throws SQLException, OrmException {
+ final PhoneBookDb p = phoneBook.open();
+ try {
+ p.updateSchema();
+
+ 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");
+ execute("DROP TABLE addresses");
+ execute("DROP SEQUENCE address_id");
+
+ Set<String> sequences, tables;
+
+ p.updateSchema();
+ 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();
+ sequences = dialect.listSequences(db);
+ tables = dialect.listTables(db);
+ assertFalse(sequences.contains("cnt"));
+ assertFalse(tables.contains("foo"));
+
+ final TestPerson.Key pk = new TestPerson.Key("Bob");
+ final TestPerson bob = new TestPerson(pk, p.nextAddressId());
+ p.people().insert(Collections.singleton(bob));
+
+ final TestAddress addr =
+ new TestAddress(new TestAddress.Key(pk, "home"), "some place");
+ p.addresses().insert(Collections.singleton(addr));
+ } finally {
+ p.close();
+ }
+ }
+}
diff --git a/src/test/java/com/google/gwtorm/schema/sql/DialectMySQLTest.java b/src/test/java/com/google/gwtorm/schema/sql/DialectMySQLTest.java
new file mode 100644
index 0000000..22a8bd3
--- /dev/null
+++ b/src/test/java/com/google/gwtorm/schema/sql/DialectMySQLTest.java
@@ -0,0 +1,162 @@
+// Copyright 2009 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 com.google.gwtorm.client.OrmException;
+import com.google.gwtorm.data.PhoneBookDb;
+import com.google.gwtorm.data.TestAddress;
+import com.google.gwtorm.data.TestPerson;
+import com.google.gwtorm.jdbc.Database;
+import com.google.gwtorm.jdbc.SimpleDataSource;
+
+import junit.framework.TestCase;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Collections;
+import java.util.Properties;
+import java.util.Set;
+
+public class DialectMySQLTest extends TestCase {
+ private Connection db;
+ private SqlDialect dialect;
+ private Database<PhoneBookDb> phoneBook;
+
+ @Override
+ protected void setUp() throws Exception {
+ super.setUp();
+ Class.forName(com.mysql.jdbc.Driver.class.getName());
+
+ final String host = "localhost";
+ final String database = "gwtorm";
+ final String user = "gwtorm";
+ final String pass = "gwtorm";
+
+ final String url = "jdbc:mysql://" + host + "/" + database;
+ db = DriverManager.getConnection(url, user, pass);
+ dialect = new DialectMySQL().refine(db);
+
+ final Properties p = new Properties();
+ p.setProperty("driver", com.mysql.jdbc.Driver.class.getName());
+ p.setProperty("url", db.getMetaData().getURL());
+ p.setProperty("user", user);
+ p.setProperty("password", pass);
+ phoneBook =
+ new Database<PhoneBookDb>(new SimpleDataSource(p), PhoneBookDb.class);
+
+ drop("TABLE address_id");
+ drop("TABLE addresses");
+ drop("TABLE cnt");
+ drop("TABLE foo");
+ drop("TABLE people");
+ }
+
+ private void drop(String drop) {
+ try {
+ execute("DROP " + drop);
+ } catch (SQLException e) {
+ }
+ }
+
+ @Override
+ protected void tearDown() {
+ 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 SQLException {
+ final Statement stmt = db.createStatement();
+ try {
+ stmt.execute(sql);
+ } finally {
+ stmt.close();
+ }
+ }
+
+ public void testListSequences() throws SQLException {
+ assertTrue(dialect.listSequences(db).isEmpty());
+
+ execute("CREATE TABLE cnt (s SERIAL)");
+ execute("CREATE TABLE foo (cnt INT)");
+
+ Set<String> s = dialect.listSequences(db);
+ assertEquals(1, s.size());
+ assertTrue(s.contains("cnt"));
+ assertFalse(s.contains("foo"));
+ }
+
+ public void testListTables() throws SQLException {
+ assertTrue(dialect.listTables(db).isEmpty());
+
+ execute("CREATE TABLE cnt (s SERIAL)");
+ execute("CREATE TABLE foo (cnt INT)");
+
+ Set<String> s = dialect.listTables(db);
+ assertEquals(1, s.size());
+ assertFalse(s.contains("cnt"));
+ assertTrue(s.contains("foo"));
+ }
+
+ public void testUpgradeSchema() throws SQLException, OrmException {
+ final PhoneBookDb p = phoneBook.open();
+ try {
+ p.updateSchema();
+
+ execute("CREATE TABLE cnt (s SERIAL)");
+ execute("CREATE TABLE foo (cnt INT)");
+
+ execute("ALTER TABLE people ADD COLUMN fake_name VARCHAR(20)");
+ execute("ALTER TABLE people DROP COLUMN registered");
+ execute("DROP TABLE addresses");
+ execute("DROP TABLE address_id");
+
+ Set<String> sequences, tables;
+
+ p.updateSchema();
+ 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();
+ sequences = dialect.listSequences(db);
+ tables = dialect.listTables(db);
+ assertFalse(sequences.contains("cnt"));
+ assertFalse(tables.contains("foo"));
+
+ final TestPerson.Key pk = new TestPerson.Key("Bob");
+ final TestPerson bob = new TestPerson(pk, p.nextAddressId());
+ p.people().insert(Collections.singleton(bob));
+
+ final TestAddress addr =
+ new TestAddress(new TestAddress.Key(pk, "home"), "some place");
+ p.addresses().insert(Collections.singleton(addr));
+ } finally {
+ p.close();
+ }
+ }
+}
diff --git a/src/test/java/com/google/gwtorm/schema/sql/DialectPostgreSQLTest.java b/src/test/java/com/google/gwtorm/schema/sql/DialectPostgreSQLTest.java
new file mode 100644
index 0000000..fe6440a
--- /dev/null
+++ b/src/test/java/com/google/gwtorm/schema/sql/DialectPostgreSQLTest.java
@@ -0,0 +1,161 @@
+// Copyright 2009 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 com.google.gwtorm.client.OrmException;
+import com.google.gwtorm.data.PhoneBookDb;
+import com.google.gwtorm.data.TestAddress;
+import com.google.gwtorm.data.TestPerson;
+import com.google.gwtorm.jdbc.Database;
+import com.google.gwtorm.jdbc.SimpleDataSource;
+
+import junit.framework.TestCase;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Collections;
+import java.util.Properties;
+import java.util.Set;
+
+public class DialectPostgreSQLTest extends TestCase {
+ private Connection db;
+ private SqlDialect dialect;
+ private Database<PhoneBookDb> phoneBook;
+
+ @Override
+ protected void setUp() throws Exception {
+ super.setUp();
+ Class.forName(org.postgresql.Driver.class.getName());
+
+ final String database = "gwtorm";
+ final String user = "gwtorm";
+ final String pass = "gwtorm";
+
+ db = DriverManager.getConnection("jdbc:postgresql:" + database, user, pass);
+ dialect = new DialectPostgreSQL().refine(db);
+
+ final Properties p = new Properties();
+ p.setProperty("driver", org.postgresql.Driver.class.getName());
+ p.setProperty("url", db.getMetaData().getURL());
+ p.setProperty("user", user);
+ p.setProperty("password", pass);
+ phoneBook =
+ new Database<PhoneBookDb>(new SimpleDataSource(p), PhoneBookDb.class);
+
+ drop("SEQUENCE address_id");
+ drop("SEQUENCE cnt");
+
+ drop("TABLE addresses");
+ drop("TABLE foo");
+ drop("TABLE people");
+ }
+
+ private void drop(String drop) {
+ try {
+ execute("DROP " + drop);
+ } catch (SQLException e) {
+ }
+ }
+
+ @Override
+ protected void tearDown() {
+ 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 SQLException {
+ final Statement stmt = db.createStatement();
+ try {
+ stmt.execute(sql);
+ } finally {
+ stmt.close();
+ }
+ }
+
+ public void testListSequences() throws 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"));
+ }
+
+ public void testListTables() throws 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"));
+ }
+
+ public void testUpgradeSchema() throws SQLException, OrmException {
+ final PhoneBookDb p = phoneBook.open();
+ try {
+ p.updateSchema();
+
+ 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");
+ execute("DROP TABLE addresses");
+ execute("DROP SEQUENCE address_id");
+
+ Set<String> sequences, tables;
+
+ p.updateSchema();
+ 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();
+ sequences = dialect.listSequences(db);
+ tables = dialect.listTables(db);
+ assertFalse(sequences.contains("cnt"));
+ assertFalse(tables.contains("foo"));
+
+ final TestPerson.Key pk = new TestPerson.Key("Bob");
+ final TestPerson bob = new TestPerson(pk, p.nextAddressId());
+ p.people().insert(Collections.singleton(bob));
+
+ final TestAddress addr =
+ new TestAddress(new TestAddress.Key(pk, "home"), "some place");
+ p.addresses().insert(Collections.singleton(addr));
+ } finally {
+ p.close();
+ }
+ }
+}
diff --git a/src/test/java/com/google/gwtorm/server/PhoneBookDbTestCase.java b/src/test/java/com/google/gwtorm/server/PhoneBookDbTestCase.java
index f9afcfe..da242bc 100644
--- a/src/test/java/com/google/gwtorm/server/PhoneBookDbTestCase.java
+++ b/src/test/java/com/google/gwtorm/server/PhoneBookDbTestCase.java
@@ -71,7 +71,7 @@
protected PhoneBookDb openAndCreate() throws OrmException {
final PhoneBookDb schema = open();
- schema.createSchema();
+ schema.updateSchema();
return schema;
}
@@ -104,7 +104,7 @@
public void testCreateSchema() throws Exception {
final PhoneBookDb schema = open();
- schema.createSchema();
+ schema.updateSchema();
}
public void testNextAddressId() throws Exception {
