From f31cdf4e67a2465db20e661d3f406e24b792ec06 Mon Sep 17 00:00:00 2001 From: Henri Sara Date: Thu, 25 Apr 2013 09:18:51 +0300 Subject: [PATCH] Support schemas and catalogs in TableQuery (#7827) Change-Id: Ib8282dc77e3d06d49ce8815a3f4b036541d9acea --- server/ivy.xml | 6 + .../util/sqlcontainer/query/TableQuery.java | 200 +++++++++++++++--- .../util/sqlcontainer/SQLTestsConstants.java | 11 + .../sqlcontainer/query/TableQueryTest.java | 73 +++++++ 4 files changed, 257 insertions(+), 33 deletions(-) diff --git a/server/ivy.xml b/server/ivy.xml index 451e0bcf6c..46d9e4c9f5 100644 --- a/server/ivy.xml +++ b/server/ivy.xml @@ -70,6 +70,12 @@ conf="test -> default" /> + + + diff --git a/server/src/com/vaadin/data/util/sqlcontainer/query/TableQuery.java b/server/src/com/vaadin/data/util/sqlcontainer/query/TableQuery.java index caed5526e3..39c8365076 100644 --- a/server/src/com/vaadin/data/util/sqlcontainer/query/TableQuery.java +++ b/server/src/com/vaadin/data/util/sqlcontainer/query/TableQuery.java @@ -50,9 +50,23 @@ import com.vaadin.data.util.sqlcontainer.query.generator.StatementHelper; public class TableQuery extends AbstractTransactionalQuery implements QueryDelegate, QueryDelegate.RowIdChangeNotifier { - /** Table name, primary key column name(s) and version column name */ + /** + * Table name (without catalog or schema information). + */ private String tableName; + private String catalogName; + private String schemaName; + /** + * Cached concatenated version of the table name. + */ + private String fullTableName; + /** + * Primary key column name(s) in the table. + */ private List primaryKeyColumns; + /** + * Version column name in the table. + */ private String versionColumn; /** Currently set Filters and OrderBys */ @@ -70,15 +84,15 @@ public class TableQuery extends AbstractTransactionalQuery implements /** Set to true to output generated SQL Queries to System.out */ private final boolean debug = false; - /** Prevent no-parameters instantiation of TableQuery */ - @SuppressWarnings("unused") - private TableQuery() { - } - /** * Creates a new TableQuery using the given connection pool, SQL generator * and table name to fetch the data from. All parameters must be non-null. * + * The table name must be a simple name with no catalog or schema + * information. If those are needed, use + * {@link #TableQuery(String, String, String, JDBCConnectionPool, SQLGenerator)} + * . + * * @param tableName * Name of the database table to connect to * @param connectionPool @@ -88,15 +102,30 @@ public class TableQuery extends AbstractTransactionalQuery implements */ public TableQuery(String tableName, JDBCConnectionPool connectionPool, SQLGenerator sqlGenerator) { - super(connectionPool); - if (tableName == null || tableName.trim().length() < 1 - || connectionPool == null || sqlGenerator == null) { - throw new IllegalArgumentException( - "All parameters must be non-null and a table name must be given."); - } - this.tableName = tableName; - this.sqlGenerator = sqlGenerator; - fetchMetaData(); + this(null, null, tableName, connectionPool, sqlGenerator); + } + + /** + * Creates a new TableQuery using the given connection pool, SQL generator + * and table name to fetch the data from. Catalog and schema names can be + * null, all other parameters must be non-null. + * + * @param catalogName + * Name of the database catalog (can be null) + * @param schemaName + * Name of the database schema (can be null) + * @param tableName + * Name of the database table to connect to + * @param connectionPool + * Connection pool for accessing the database + * @param sqlGenerator + * SQL query generator implementation + * @since 7.1 + */ + public TableQuery(String catalogName, String schemaName, String tableName, + JDBCConnectionPool connectionPool, SQLGenerator sqlGenerator) { + this(catalogName, schemaName, tableName, connectionPool, sqlGenerator, + true); } /** @@ -104,6 +133,11 @@ public class TableQuery extends AbstractTransactionalQuery implements * to fetch the data from. All parameters must be non-null. The default SQL * generator will be used for queries. * + * The table name must be a simple name with no catalog or schema + * information. If those are needed, use + * {@link #TableQuery(String, String, String, JDBCConnectionPool, SQLGenerator)} + * . + * * @param tableName * Name of the database table to connect to * @param connectionPool @@ -113,6 +147,48 @@ public class TableQuery extends AbstractTransactionalQuery implements this(tableName, connectionPool, new DefaultSQLGenerator()); } + /** + * Creates a new TableQuery using the given connection pool, SQL generator + * and table name to fetch the data from. Catalog and schema names can be + * null, all other parameters must be non-null. + * + * @param catalogName + * Name of the database catalog (can be null) + * @param schemaName + * Name of the database schema (can be null) + * @param tableName + * Name of the database table to connect to + * @param connectionPool + * Connection pool for accessing the database + * @param sqlGenerator + * SQL query generator implementation + * @param escapeNames + * true to escape special characters in catalog, schema and table + * names, false to use the names as-is + * @since 7.1 + */ + protected TableQuery(String catalogName, String schemaName, + String tableName, JDBCConnectionPool connectionPool, + SQLGenerator sqlGenerator, boolean escapeNames) { + super(connectionPool); + if (tableName == null || tableName.trim().length() < 1 + || connectionPool == null || sqlGenerator == null) { + throw new IllegalArgumentException( + "Table name, connection pool and SQL generator parameters must be non-null and non-empty."); + } + if (escapeNames) { + this.catalogName = SQLUtil.escapeSQL(catalogName); + this.schemaName = SQLUtil.escapeSQL(schemaName); + this.tableName = SQLUtil.escapeSQL(tableName); + } else { + this.catalogName = catalogName; + this.schemaName = schemaName; + this.tableName = tableName; + } + this.sqlGenerator = sqlGenerator; + fetchMetaData(); + } + /* * (non-Javadoc) * @@ -121,8 +197,8 @@ public class TableQuery extends AbstractTransactionalQuery implements @Override public int getCount() throws SQLException { getLogger().log(Level.FINE, "Fetching count..."); - StatementHelper sh = sqlGenerator.generateSelectQuery(tableName, - filters, null, 0, 0, "COUNT(*)"); + StatementHelper sh = sqlGenerator.generateSelectQuery( + getFullTableName(), filters, null, 0, 0, "COUNT(*)"); boolean shouldCloseTransaction = false; if (!isInTransaction()) { shouldCloseTransaction = true; @@ -167,11 +243,11 @@ public class TableQuery extends AbstractTransactionalQuery implements for (int i = 0; i < primaryKeyColumns.size(); i++) { ob.add(new OrderBy(primaryKeyColumns.get(i), true)); } - sh = sqlGenerator.generateSelectQuery(tableName, filters, ob, - offset, pagelength, null); + sh = sqlGenerator.generateSelectQuery(getFullTableName(), filters, + ob, offset, pagelength, null); } else { - sh = sqlGenerator.generateSelectQuery(tableName, filters, orderBys, - offset, pagelength, null); + sh = sqlGenerator.generateSelectQuery(getFullTableName(), filters, + orderBys, offset, pagelength, null); } return executeQuery(sh); } @@ -204,11 +280,11 @@ public class TableQuery extends AbstractTransactionalQuery implements int result = 0; if (row.getId() instanceof TemporaryRowId) { setVersionColumnFlagInProperty(row); - sh = sqlGenerator.generateInsertQuery(tableName, row); + sh = sqlGenerator.generateInsertQuery(getFullTableName(), row); result = executeUpdateReturnKeys(sh, row); } else { setVersionColumnFlagInProperty(row); - sh = sqlGenerator.generateUpdateQuery(tableName, row); + sh = sqlGenerator.generateUpdateQuery(getFullTableName(), row); result = executeUpdate(sh); } if (versionColumn != null && result == 0) { @@ -244,7 +320,8 @@ public class TableQuery extends AbstractTransactionalQuery implements /* Set version column, if one is provided */ setVersionColumnFlagInProperty(row); /* Generate query */ - StatementHelper sh = sqlGenerator.generateInsertQuery(tableName, row); + StatementHelper sh = sqlGenerator.generateInsertQuery( + getFullTableName(), row); Connection connection = null; PreparedStatement pstmt = null; ResultSet generatedKeys = null; @@ -371,10 +448,61 @@ public class TableQuery extends AbstractTransactionalQuery implements versionColumn = column; } + /** + * Returns the table name for the query without catalog and schema + * information. + * + * @return table name, not null + */ public String getTableName() { return tableName; } + /** + * Returns the catalog name for the query. + * + * @return catalog name, can be null + * @since 7.1 + */ + public String getCatalogName() { + return catalogName; + } + + /** + * Returns the catalog name for the query. + * + * @return catalog name, can be null + * @since 7.1 + */ + public String getSchemaName() { + return schemaName; + } + + /** + * Returns the complete table name obtained by concatenation of the catalog + * and schema names (if any) and the table name. + * + * This method can be overridden if customization is needed. + * + * @return table name in the form it should be used in query and update + * statements + * @since 7.1 + */ + protected String getFullTableName() { + if (fullTableName == null) { + StringBuilder sb = new StringBuilder(); + if (catalogName != null) { + sb.append(catalogName).append("."); + } + if (schemaName != null) { + sb.append(schemaName).append("."); + } + sb.append(tableName); + fullTableName = sb.toString(); + } + return fullTableName; + } + public SQLGenerator getSqlGenerator() { return sqlGenerator; } @@ -480,22 +608,28 @@ public class TableQuery extends AbstractTransactionalQuery implements connection = getConnection(); DatabaseMetaData dbmd = connection.getMetaData(); if (dbmd != null) { - tableName = SQLUtil.escapeSQL(tableName); - tables = dbmd.getTables(null, null, tableName, null); + tables = dbmd.getTables(catalogName, schemaName, tableName, + null); if (!tables.next()) { - tables = dbmd.getTables(null, null, + String catalog = (catalogName != null) ? catalogName + .toUpperCase() : null; + String schema = (schemaName != null) ? schemaName + .toUpperCase() : null; + tables = dbmd.getTables(catalog, schema, tableName.toUpperCase(), null); if (!tables.next()) { throw new IllegalArgumentException( "Table with the name \"" - + tableName + + getFullTableName() + "\" was not found. Check your database contents."); } else { + catalogName = catalog; + schemaName = schema; tableName = tableName.toUpperCase(); } } tables.close(); - rs = dbmd.getPrimaryKeys(null, null, tableName); + rs = dbmd.getPrimaryKeys(catalogName, schemaName, tableName); List names = new ArrayList(); while (rs.next()) { names.add(rs.getString("COLUMN_NAME")); @@ -507,7 +641,7 @@ public class TableQuery extends AbstractTransactionalQuery implements if (primaryKeyColumns == null || primaryKeyColumns.isEmpty()) { throw new IllegalArgumentException( "Primary key constraints have not been defined for the table \"" - + tableName + + getFullTableName() + "\". Use FreeFormQuery to access this table."); } for (String colName : primaryKeyColumns) { @@ -592,7 +726,7 @@ public class TableQuery extends AbstractTransactionalQuery implements getLogger().log(Level.FINE, "Removing row with id: {0}", row.getId().getId()[0]); } - if (executeUpdate(sqlGenerator.generateDeleteQuery(getTableName(), + if (executeUpdate(sqlGenerator.generateDeleteQuery(getFullTableName(), primaryKeyColumns, versionColumn, row)) == 1) { return true; } @@ -622,8 +756,8 @@ public class TableQuery extends AbstractTransactionalQuery implements filtersAndKeys.add(new Equal(colName, keys[ix])); ix++; } - StatementHelper sh = sqlGenerator.generateSelectQuery(tableName, - filtersAndKeys, orderBys, 0, 0, "*"); + StatementHelper sh = sqlGenerator.generateSelectQuery( + getFullTableName(), filtersAndKeys, orderBys, 0, 0, "*"); boolean shouldCloseTransaction = false; if (!isInTransaction()) { 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 -- 2.39.5