Support schemas and catalogs in TableQuery (#7827)

Change-Id: Ib8282dc77e3d06d49ce8815a3f4b036541d9acea

2 files changed, 84 insertions, 0 deletions

diff --git a/server/tests/src/com/vaadin/data/util/sqlcontainer/SQLTestsConstants.java b/server/tests/src/com/vaadin/data/util/sqlcontainer/SQLTestsConstants.java
index 786903f1d0..1e96d59ed5 100755
--- a/server/tests/src/com/vaadin/data/util/sqlcontainer/SQLTestsConstants.java
+++ b/server/tests/src/com/vaadin/data/util/sqlcontainer/SQLTestsConstants.java
@@ -45,6 +45,10 @@ public class SQLTestsConstants {
     public static String peopleFirst;
     public static String peopleSecond;
     public static String peopleThird;
+    /* Schema test creation statement(s) */
+    public static String createSchema;
+    public static String createProductTable;
+    public static String dropSchema;
     /* Versioned -test table createion statement(s) */
     public static String[] versionStatements;
     /* SQL Generator used during the testing */
@@ -66,6 +70,10 @@ public class SQLTestsConstants {
             versionStatements = new String[] {
                     "create table versioned (id integer generated always as identity, text varchar(255), version tinyint default 0)",
                     "alter table versioned add primary key (id)" };
+            // TODO these should ideally exist for all databases
+            createSchema = "create schema oaas authorization DBA";
+            createProductTable = "create table oaas.product (\"ID\" integer generated always as identity primary key, \"NAME\" VARCHAR(32))";
+            dropSchema = "drop schema if exists oaas cascade";
             break;
         case MYSQL:
             offset = 1;
@@ -104,6 +112,9 @@ public class SQLTestsConstants {
                     "CREATE TRIGGER \"mytable_modify_dt_tr\" BEFORE UPDATE"
                             + "   ON VERSIONED FOR EACH ROW"
                             + "   EXECUTE PROCEDURE \"public\".\"zz_row_version\"();" };
+            createSchema = "create schema oaas";
+            createProductTable = "create table oaas.product (\"ID\" serial primary key, \"NAME\" VARCHAR(32))";
+            dropSchema = "drop schema oaas cascade";
             break;
         case MSSQL:
             offset = 1;
diff --git a/server/tests/src/com/vaadin/data/util/sqlcontainer/query/TableQueryTest.java b/server/tests/src/com/vaadin/data/util/sqlcontainer/query/TableQueryTest.java
index 54db34dfd2..c275cd4363 100644
--- a/server/tests/src/com/vaadin/data/util/sqlcontainer/query/TableQueryTest.java
+++ b/server/tests/src/com/vaadin/data/util/sqlcontainer/query/TableQueryTest.java
@@ -661,4 +661,77 @@ public class TableQueryTest {
         container.commit();
     }
 
+    @Test
+    public void construction_explicitSchema_shouldSucceed() throws SQLException {
+        if (SQLTestsConstants.createSchema == null
+                || SQLTestsConstants.createProductTable == null
+                || SQLTestsConstants.dropSchema == null) {
+            // only perform the test on the databases for which the setup and
+            // cleanup statements are available
+            return;
+        }
+
+        // create schema "oaas" and table "product" in it
+        Connection conn = connectionPool.reserveConnection();
+        Statement statement = conn.createStatement();
+        try {
+            statement.execute(SQLTestsConstants.dropSchema);
+        } catch (SQLException e) {
+            // May fail if schema doesn't exist, which is OK.
+            conn.rollback();
+        }
+        statement.execute(SQLTestsConstants.createSchema);
+        statement.execute(SQLTestsConstants.createProductTable);
+        conn.commit();
+
+        try {
+            // metadata scanning at query creation time should not fail
+            TableQuery tq1 = new TableQuery(null, "oaas", "product",
+                    connectionPool, SQLTestsConstants.sqlGen);
+            Assert.assertNotNull(tq1);
+        } finally {
+            // cleanup - might not be an in-memory DB
+            statement.execute(SQLTestsConstants.dropSchema);
+        }
+    }
+
+    @Test
+    public void construction_explicitCatalogAndSchema_shouldSucceed()
+            throws SQLException {
+        // not all databases support explicit catalogs, test with PostgreSQL
+        // using database name as catalog
+        if (SQLTestsConstants.db != SQLTestsConstants.DB.POSTGRESQL
+                || SQLTestsConstants.createSchema == null
+                || SQLTestsConstants.createProductTable == null
+                || SQLTestsConstants.dropSchema == null) {
+            // only perform the test on the databases for which the setup and
+            // cleanup statements are available
+            return;
+        }
+
+        // create schema "oaas" and table "product" in it
+        Connection conn = connectionPool.reserveConnection();
+        Statement statement = conn.createStatement();
+        try {
+            statement.execute(SQLTestsConstants.dropSchema);
+        } catch (SQLException e) {
+            // May fail if schema doesn't exist, which is OK.
+            conn.rollback();
+        }
+        statement.execute(SQLTestsConstants.createSchema);
+        statement.execute(SQLTestsConstants.createProductTable);
+        conn.commit();
+
+        try {
+            // metadata scanning at query creation time should not fail
+            // note that for most DBMS, catalog is just an optional database
+            // name
+            TableQuery tq1 = new TableQuery("sqlcontainer", "oaas", "product",
+                    connectionPool, SQLTestsConstants.sqlGen);
+            Assert.assertNotNull(tq1);
+        } finally {
+            // cleanup - might not be an in-memory DB
+            statement.execute(SQLTestsConstants.dropSchema);
+        }
+    }
 }
\ No newline at end of file