Expose listIndexes method for supported SQL dialects
During schema migration it is helpfull to be able to retrieve the
list of the existing indexes for specific database table. Implement
it for all supported SQL dialects.
Change-Id: Ide2dc8cea1dbcd698f9ccbb59679d0b012d111b0
diff --git a/src/main/java/com/google/gwtorm/schema/sql/DialectOracle.java b/src/main/java/com/google/gwtorm/schema/sql/DialectOracle.java
index 63227be..827c656 100644
--- a/src/main/java/com/google/gwtorm/schema/sql/DialectOracle.java
+++ b/src/main/java/com/google/gwtorm/schema/sql/DialectOracle.java
@@ -19,6 +19,7 @@
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;
@@ -53,7 +54,7 @@
try {
ResultSet rs = s.executeQuery("SELECT table_name FROM user_tables");
try {
- HashSet<String> tables = new HashSet<String>();
+ Set<String> tables = new HashSet<String>();
while (rs.next()) {
tables.add(rs.getString(1).toLowerCase());
}
@@ -67,12 +68,34 @@
}
@Override
+ public Set<String> listIndexes(final Connection db, String tableName)
+ throws SQLException {
+ PreparedStatement s = db.prepareStatement("SELECT distinct index_name"
+ + " FROM user_indexes WHERE table_name = ?");
+ try {
+ s.setString(1, tableName.toUpperCase());
+ ResultSet rs = s.executeQuery();
+ try {
+ Set<String> indexes = new HashSet<String>();
+ while (rs.next()) {
+ indexes.add(rs.getString(1).toLowerCase());
+ }
+ return indexes;
+ } finally {
+ rs.close();
+ }
+ } finally {
+ s.close();
+ }
+ }
+
+ @Override
public Set<String> listSequences(Connection db) throws SQLException {
Statement s = db.createStatement();
try {
ResultSet rs = s.executeQuery("SELECT sequence_name FROM user_sequences");
try {
- HashSet<String> sequences = new HashSet<String>();
+ Set<String> sequences = new HashSet<String>();
while (rs.next()) {
sequences.add(rs.getString(1).toLowerCase());
}
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 ad1f16b..47b7204 100644
--- a/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java
+++ b/src/main/java/com/google/gwtorm/schema/sql/SqlDialect.java
@@ -224,7 +224,7 @@
final String[] types = new String[] {"TABLE"};
final ResultSet rs = db.getMetaData().getTables(null, null, null, types);
try {
- HashSet<String> tables = new HashSet<String>();
+ Set<String> tables = new HashSet<String>();
while (rs.next()) {
tables.add(rs.getString("TABLE_NAME").toLowerCase());
}
@@ -254,6 +254,35 @@
}
/**
+ * List all indexes for the given table name.
+ *
+ * @param db connection to the schema.
+ * @param tableName the table to list indexes from, in lowercase.
+ * @return set of declared indexes, in lowercase.
+ * @throws SQLException the indexes cannot be listed.
+ */
+ public Set<String> listIndexes(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.getIndexInfo(null, null, tableName, false, true);
+ try {
+ Set<String> indexes = new HashSet<String>();
+ while (rs.next()) {
+ indexes.add(rs.getString("INDEX_NAME").toLowerCase());
+ }
+ return indexes;
+ } finally {
+ rs.close();
+ }
+ }
+
+ /**
* List all sequences in the current database schema.
*
* @param db connection to the schema.
diff --git a/src/test/java/com/google/gwtorm/schema/sql/DialectH2Test.java b/src/test/java/com/google/gwtorm/schema/sql/DialectH2Test.java
index 8e332b4..68ca123 100644
--- a/src/test/java/com/google/gwtorm/schema/sql/DialectH2Test.java
+++ b/src/test/java/com/google/gwtorm/schema/sql/DialectH2Test.java
@@ -110,6 +110,21 @@
}
@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 {
diff --git a/src/test/java/com/google/gwtorm/schema/sql/DialectMySQLTest.java b/src/test/java/com/google/gwtorm/schema/sql/DialectMySQLTest.java
index 89ebeeb..e20435f 100644
--- a/src/test/java/com/google/gwtorm/schema/sql/DialectMySQLTest.java
+++ b/src/test/java/com/google/gwtorm/schema/sql/DialectMySQLTest.java
@@ -79,6 +79,7 @@
drop("TABLE address_id");
drop("TABLE addresses");
drop("TABLE cnt");
+ drop("TABLE bar");
drop("TABLE foo");
drop("TABLE people");
}
@@ -138,6 +139,20 @@
}
@Test
+ public void testListIndexes() throws OrmException, SQLException {
+ assertTrue(dialect.listTables(db).isEmpty());
+
+ 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 {
diff --git a/src/test/java/com/google/gwtorm/schema/sql/DialectOracleSQLTest.java b/src/test/java/com/google/gwtorm/schema/sql/DialectOracleSQLTest.java
index 21a0ac6..43f513d 100644
--- a/src/test/java/com/google/gwtorm/schema/sql/DialectOracleSQLTest.java
+++ b/src/test/java/com/google/gwtorm/schema/sql/DialectOracleSQLTest.java
@@ -139,6 +139,21 @@
}
@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 {
diff --git a/src/test/java/com/google/gwtorm/schema/sql/DialectPostgreSQLTest.java b/src/test/java/com/google/gwtorm/schema/sql/DialectPostgreSQLTest.java
index 2957113..ed20480 100644
--- a/src/test/java/com/google/gwtorm/schema/sql/DialectPostgreSQLTest.java
+++ b/src/test/java/com/google/gwtorm/schema/sql/DialectPostgreSQLTest.java
@@ -79,6 +79,7 @@
drop("TABLE addresses");
drop("TABLE foo");
+ drop("TABLE bar");
drop("TABLE people");
}
@@ -137,6 +138,20 @@
}
@Test
+ public void testListIndexes() throws OrmException, SQLException {
+ assertTrue(dialect.listTables(db).isEmpty());
+
+ 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 {
