Repository: metamodel Updated Branches: refs/heads/master 05bbaf780 -> 95fa617db
METAMODEL-1132: Fixed Fixes #137 Project: http://git-wip-us.apache.org/repos/asf/metamodel/repo Commit: http://git-wip-us.apache.org/repos/asf/metamodel/commit/95fa617d Tree: http://git-wip-us.apache.org/repos/asf/metamodel/tree/95fa617d Diff: http://git-wip-us.apache.org/repos/asf/metamodel/diff/95fa617d Branch: refs/heads/master Commit: 95fa617db5c9c5afe3c63155c64c2cc99f0c832b Parents: 05bbaf7 Author: rposkocil <[email protected]> Authored: Wed Dec 21 08:47:47 2016 +0100 Committer: Dennis Du Krøger <[email protected]> Committed: Wed Dec 21 08:49:47 2016 +0100 ---------------------------------------------------------------------- CHANGES.md | 1 + .../apache/metamodel/jdbc/JdbcDataContext.java | 20 +++- .../jdbc/dialects/AbstractQueryRewriter.java | 22 +++++ .../jdbc/dialects/OffsetFetchQueryRewriter.java | 69 ++++++++++++++ .../jdbc/dialects/OracleQueryRewriter.java | 8 +- .../jdbc/dialects/SQLServerQueryRewriter.java | 11 +-- .../dialects/SQLServerQueryRewriterTest.java | 17 +++- .../jdbc/dialects/OracleQueryRewriterTest.java | 99 +++++++++++++++++++- 8 files changed, 230 insertions(+), 17 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/metamodel/blob/95fa617d/CHANGES.md ---------------------------------------------------------------------- diff --git a/CHANGES.md b/CHANGES.md index 5d8f394..427ac21 100644 --- a/CHANGES.md +++ b/CHANGES.md @@ -1,5 +1,6 @@ ### Apache MetaModel 4.5.5 + * [METAMODEL-1132] - Support native paging on SQL Server and Oracle database. * [METAMODEL-1128] - Fixed bug pertaining to ElasticSearch REST data set scrolling. * [METAMODEL-1118] - Fixed bug pertaining to cloning of FilterItem.LogicalOperator in compiled queries. * [METAMODEL-1111] - Added WHERE rewrite for Oracle when empty strings are considered as NULL. http://git-wip-us.apache.org/repos/asf/metamodel/blob/95fa617d/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataContext.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataContext.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataContext.java index 8cd027b..ef0b549 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataContext.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/JdbcDataContext.java @@ -112,6 +112,7 @@ public class JdbcDataContext extends AbstractDataContext implements UpdateableDa */ private IQueryRewriter _queryRewriter; private final String _databaseProductName; + private final String _databaseVersion; /** * There are some confusion as to the definition of catalogs and schemas. @@ -183,6 +184,7 @@ public class JdbcDataContext extends AbstractDataContext implements UpdateableDa boolean supportsBatchUpdates = false; String identifierQuoteString = null; String databaseProductName = null; + String databaseVersion = null; boolean usesCatalogsAsSchemas = false; final Connection con = getConnection(); @@ -210,8 +212,9 @@ public class JdbcDataContext extends AbstractDataContext implements UpdateableDa usesCatalogsAsSchemas = usesCatalogsAsSchemas(metaData); try { databaseProductName = metaData.getDatabaseProductName(); + databaseVersion = metaData.getDatabaseProductVersion(); } catch (SQLException e) { - logger.warn("Could not retrieve database product name: " + e.getMessage()); + logger.warn("Could not retrieve metadata: " + e.getMessage()); } } catch (SQLException e) { logger.debug("Unexpected exception during JdbcDataContext initialization", e); @@ -219,6 +222,7 @@ public class JdbcDataContext extends AbstractDataContext implements UpdateableDa closeIfNecessary(con); } _databaseProductName = databaseProductName; + _databaseVersion = databaseVersion; logger.debug("Database product name: {}", _databaseProductName); if (DATABASE_PRODUCT_MYSQL.equals(_databaseProductName)) { setQueryRewriter(new MysqlQueryRewriter(this)); @@ -571,7 +575,7 @@ public class JdbcDataContext extends AbstractDataContext implements UpdateableDa // Retrieve catalogs logger.debug("Retrieving catalogs"); - List<String> catalogs = new ArrayList<String>(); + List<String> catalogs = new ArrayList<>(); try { rs = metaData.getCatalogs(); while (rs.next()) { @@ -754,7 +758,7 @@ public class JdbcDataContext extends AbstractDataContext implements UpdateableDa private Set<String> getSchemaSQLServerNames(DatabaseMetaData metaData) throws SQLException { // Distinct schema names. metaData.getTables() is a denormalized // resultset - Set<String> schemas = new HashSet<String>(); + Set<String> schemas = new HashSet<>(); ResultSet rs = metaData.getTables(_catalogName, null, null, JdbcUtils.getTableTypesAsStrings(_tableTypes)); while (rs.next()) { schemas.add(rs.getString("TABLE_SCHEM")); @@ -783,7 +787,7 @@ public class JdbcDataContext extends AbstractDataContext implements UpdateableDa Connection connection = getConnection(); try { DatabaseMetaData metaData = connection.getMetaData(); - Collection<String> result = new ArrayList<String>(); + Collection<String> result = new ArrayList<>(); if (DATABASE_PRODUCT_SQLSERVER.equals(_databaseProductName)) { result = getSchemaSQLServerNames(metaData); @@ -887,4 +891,12 @@ public class JdbcDataContext extends AbstractDataContext implements UpdateableDa public String getCatalogName() { return _catalogName; } + + public String getDatabaseProductName() { + return _databaseProductName; + } + + public String getDatabaseVersion() { + return _databaseVersion; + } } http://git-wip-us.apache.org/repos/asf/metamodel/blob/95fa617d/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/AbstractQueryRewriter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/AbstractQueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/AbstractQueryRewriter.java index 98c8369..7092052 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/AbstractQueryRewriter.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/AbstractQueryRewriter.java @@ -460,4 +460,26 @@ public abstract class AbstractQueryRewriter implements IQueryRewriter { } return resultSet.getObject(columnIndex); } + + protected boolean isSupportedVersion(String databaseProductName, int databaseVersion) { + + if(databaseProductName.equals(_dataContext.getDatabaseProductName()) + && databaseVersion <= getDatabaseMajorVersion(_dataContext.getDatabaseVersion())) { + return true; + } + return false; + } + + private int getDatabaseMajorVersion(String version) { + int firstDot = -1; + if(version != null) { + version = version.replaceAll("[^0-9.]+", ""); + firstDot = version.indexOf('.'); + } + if(firstDot >= 0) { + return Integer.valueOf(version.substring(0, firstDot)); + } else { + return 0; + } + } } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/metamodel/blob/95fa617d/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/OffsetFetchQueryRewriter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/OffsetFetchQueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/OffsetFetchQueryRewriter.java new file mode 100644 index 0000000..9e14243 --- /dev/null +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/OffsetFetchQueryRewriter.java @@ -0,0 +1,69 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you 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 org.apache.metamodel.jdbc.dialects; + +import org.apache.metamodel.jdbc.JdbcDataContext; +import org.apache.metamodel.query.Query; + +/** + * Query rewriter for databases that support OFFSET and FETCH keywords for max + * rows and first row properties. + */ +public abstract class OffsetFetchQueryRewriter extends DefaultQueryRewriter { + + private final String databaseProductName; + private final int databaseSupportedVersion; + + public OffsetFetchQueryRewriter(JdbcDataContext dataContext, int minSupportedVersion) { + super(dataContext); + databaseProductName = dataContext.getDatabaseProductName(); + databaseSupportedVersion = minSupportedVersion; + } + + @Override + public final boolean isFirstRowSupported() { + return true; + } + + @Override + public final boolean isMaxRowsSupported() { + return true; + } + + /** + * {@inheritDoc} + * + * If the Max rows and First row property of the query is set, then we + * will use the database's "OFFSET i ROWS FETCH NEXT j ROWS ONLY" construct. + */ + @Override + public String rewriteQuery(Query query) { + String queryString = super.rewriteQuery(query); + if(isSupportedVersion(databaseProductName, databaseSupportedVersion)) { + Integer maxRows = query.getMaxRows(); + Integer firstRow = query.getFirstRow(); + if (maxRows != null && firstRow != null && queryString.indexOf("ORDER BY") >= 0 ) { + queryString = queryString.replaceAll("TOP [0-9]+", ""); + queryString = queryString + " OFFSET " + (firstRow-1) + " ROWS FETCH NEXT " + maxRows + " ROWS ONLY"; + } + } + return queryString; + } + +} http://git-wip-us.apache.org/repos/asf/metamodel/blob/95fa617d/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/OracleQueryRewriter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/OracleQueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/OracleQueryRewriter.java index 305dbb8..647035e 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/OracleQueryRewriter.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/OracleQueryRewriter.java @@ -18,6 +18,8 @@ */ package org.apache.metamodel.jdbc.dialects; +import static org.apache.metamodel.jdbc.JdbcDataContext.DATABASE_PRODUCT_ORACLE; + import org.apache.metamodel.jdbc.JdbcDataContext; import org.apache.metamodel.query.FilterItem; import org.apache.metamodel.schema.ColumnType; @@ -25,10 +27,12 @@ import org.apache.metamodel.schema.ColumnType; /** * Query rewriter for Oracle */ -public class OracleQueryRewriter extends DefaultQueryRewriter { +public class OracleQueryRewriter extends OffsetFetchQueryRewriter { + + public static final int FIRST_FETCH_SUPPORTING_VERSION = 12; public OracleQueryRewriter(JdbcDataContext dataContext) { - super(dataContext); + super(dataContext, FIRST_FETCH_SUPPORTING_VERSION); } @Override http://git-wip-us.apache.org/repos/asf/metamodel/blob/95fa617d/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/SQLServerQueryRewriter.java ---------------------------------------------------------------------- diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/SQLServerQueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/SQLServerQueryRewriter.java index 88bed85..8e17236 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/SQLServerQueryRewriter.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/SQLServerQueryRewriter.java @@ -31,15 +31,12 @@ import org.apache.metamodel.schema.Column; import org.apache.metamodel.schema.ColumnType; import org.apache.metamodel.util.DateUtils; -public class SQLServerQueryRewriter extends DefaultQueryRewriter { +public class SQLServerQueryRewriter extends OffsetFetchQueryRewriter { - public SQLServerQueryRewriter(JdbcDataContext dataContext) { - super(dataContext); - } + public static final int FIRST_FETCH_SUPPORTING_VERSION = 11; - @Override - public boolean isMaxRowsSupported() { - return true; + public SQLServerQueryRewriter(JdbcDataContext dataContext) { + super(dataContext, FIRST_FETCH_SUPPORTING_VERSION); } /** http://git-wip-us.apache.org/repos/asf/metamodel/blob/95fa617d/jdbc/src/test/java/org/apache/metamodel/dialects/SQLServerQueryRewriterTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/dialects/SQLServerQueryRewriterTest.java b/jdbc/src/test/java/org/apache/metamodel/dialects/SQLServerQueryRewriterTest.java index 7d75dc1..86f9828 100644 --- a/jdbc/src/test/java/org/apache/metamodel/dialects/SQLServerQueryRewriterTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/dialects/SQLServerQueryRewriterTest.java @@ -18,8 +18,14 @@ */ package org.apache.metamodel.dialects; +import static org.apache.metamodel.jdbc.JdbcDataContext.DATABASE_PRODUCT_SQLSERVER; + +import java.sql.Connection; +import java.sql.DatabaseMetaData; + import junit.framework.TestCase; +import org.apache.metamodel.jdbc.JdbcDataContext; import org.apache.metamodel.jdbc.dialects.SQLServerQueryRewriter; import org.apache.metamodel.query.FilterItem; import org.apache.metamodel.query.FromItem; @@ -31,12 +37,13 @@ import org.apache.metamodel.schema.MutableColumn; import org.apache.metamodel.schema.MutableSchema; import org.apache.metamodel.schema.MutableTable; import org.apache.metamodel.util.TimeComparator; +import org.easymock.EasyMock; public class SQLServerQueryRewriterTest extends TestCase { private MutableTable table; private MutableColumn column; - private SQLServerQueryRewriter qr = new SQLServerQueryRewriter(null); + private SQLServerQueryRewriter qr; @Override protected void setUp() throws Exception { @@ -47,6 +54,14 @@ public class SQLServerQueryRewriterTest extends TestCase { column = new MutableColumn("bar"); column.setQuote("\""); column.setTable(table); + + final JdbcDataContext mockContext = EasyMock.createMock(JdbcDataContext.class); + EasyMock.expect(mockContext.getDatabaseProductName()).andReturn(DATABASE_PRODUCT_SQLSERVER).anyTimes(); + EasyMock.expect(mockContext.getDatabaseVersion()).andReturn("12.1.1.1").anyTimes(); + EasyMock.expect(mockContext.getIdentifierQuoteString()).andReturn("quoteString").anyTimes(); + + EasyMock.replay(mockContext); + qr = new SQLServerQueryRewriter(mockContext); } public void testRewriteColumnTypeDouble() throws Exception { http://git-wip-us.apache.org/repos/asf/metamodel/blob/95fa617d/jdbc/src/test/java/org/apache/metamodel/jdbc/dialects/OracleQueryRewriterTest.java ---------------------------------------------------------------------- diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/dialects/OracleQueryRewriterTest.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/dialects/OracleQueryRewriterTest.java index 88f0a50..a36a4ba 100644 --- a/jdbc/src/test/java/org/apache/metamodel/jdbc/dialects/OracleQueryRewriterTest.java +++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/dialects/OracleQueryRewriterTest.java @@ -18,18 +18,35 @@ */ package org.apache.metamodel.jdbc.dialects; +import static org.apache.metamodel.jdbc.JdbcDataContext.DATABASE_PRODUCT_ORACLE; +import static org.junit.Assert.assertEquals; + +import java.sql.Connection; +import java.sql.DatabaseMetaData; +import java.sql.SQLException; + +import org.apache.metamodel.jdbc.JdbcDataContext; import org.apache.metamodel.query.FilterItem; import org.apache.metamodel.query.OperatorType; +import org.apache.metamodel.query.Query; import org.apache.metamodel.query.SelectItem; +import org.easymock.EasyMock; +import org.junit.Assert; +import org.junit.BeforeClass; import org.junit.Test; -import static org.junit.Assert.assertEquals; - public class OracleQueryRewriterTest { + private static final JdbcDataContext mockContext = EasyMock.createMock(JdbcDataContext.class); + + @BeforeClass + public static void initMocks() throws SQLException { + setMetaData(DATABASE_PRODUCT_ORACLE, "R12.1.1.1"); + } + @Test public void testReplaceEmptyStringWithNull() throws Exception { - final OracleQueryRewriter rewriter = new OracleQueryRewriter(null); + final OracleQueryRewriter rewriter = new OracleQueryRewriter(mockContext); final String alias = "alias"; SelectItem selectItem = new SelectItem("expression", alias); final FilterItem filterItem = new FilterItem(selectItem, OperatorType.DIFFERENT_FROM, ""); @@ -38,4 +55,80 @@ public class OracleQueryRewriterTest { assertEquals(expectedValue, rewrittenValue); } + + @Test + public void testOffsetFetchConstruct() { + final OracleQueryRewriter rewriter = new OracleQueryRewriter(mockContext); + final int offset = 1000; + final int rows = 100; + final String table = "table"; + final String where = "x > 1"; + + Query query = new Query(); + query.from(table).orderBy("id"); + final String queryWithoutBoth = query.toSql(); + Assert.assertEquals("Original SQL is not correctly generated.", " FROM table ORDER BY id ASC", queryWithoutBoth); + final String queryWithoutBothRewritten = rewriter.rewriteQuery(query); + Assert.assertEquals("There shouldn't be OFFSET-FETCH clause.", queryWithoutBoth, queryWithoutBothRewritten); + + query.setFirstRow(offset); + final String queryWithoutMax = query.toSql(); + Assert.assertEquals("Original SQL is not correctly generated.", " FROM table ORDER BY id ASC", queryWithoutMax); + final String queryWithoutMaxRewritten = rewriter.rewriteQuery(query); + Assert.assertEquals("There shouldn't be OFFSET-FETCH clause.", queryWithoutMax, queryWithoutMaxRewritten); + + query.setMaxRows(rows).where(where); + final String originalQuery = query.toSql(); + Assert.assertEquals("Original SQL is not correctly generated.", " FROM table WHERE x > 1 ORDER BY id ASC", originalQuery); + + String rewrittenQuery = rewriter.rewriteQuery(query); + final String offsetFetchClause = " OFFSET " + (offset-1) + " ROWS FETCH NEXT " + rows + " ROWS ONLY"; + Assert.assertEquals("Not correctly generated Offset Fetch clouse.", originalQuery + offsetFetchClause, rewrittenQuery); + } + + @Test + public void testOffsetFetchVersionCheck() throws SQLException { + setMetaData(DATABASE_PRODUCT_ORACLE, "10.1.1.1"); + + final int offset = 1000; + final int rows = 100; + final String table = "table"; + + Query query = new Query(); + query.from(table).setFirstRow(offset).setMaxRows(rows); + final String originalQuery = query.toSql(); + Assert.assertEquals("Original SQL is not correctly generated.", " FROM table", originalQuery); + + final OracleQueryRewriter rewriter = new OracleQueryRewriter(mockContext); + String rewrittenQuery = rewriter.rewriteQuery(query); + Assert.assertEquals("The query shouldn't be rewritten.", originalQuery, rewrittenQuery); + } + + @Test + public void testOffsetFetchVersionIsNull() throws SQLException { + setMetaData(DATABASE_PRODUCT_ORACLE, null); + + final int offset = 1000; + final int rows = 100; + final String table = "table"; + + Query query = new Query(); + query.from(table).setFirstRow(offset).setMaxRows(rows); + final String originalQuery = query.toSql(); + Assert.assertEquals("Original SQL is not correctly generated.", " FROM table", originalQuery); + + final OracleQueryRewriter rewriter = new OracleQueryRewriter(mockContext); + String rewrittenQuery = rewriter.rewriteQuery(query); + Assert.assertEquals("The query shouldn't be rewritten.", originalQuery, rewrittenQuery); + } + + private static void setMetaData(String productName, String version) throws SQLException { + EasyMock.reset(mockContext); + + EasyMock.expect(mockContext.getDatabaseProductName()).andReturn(productName).anyTimes(); + EasyMock.expect(mockContext.getDatabaseVersion()).andReturn(version).anyTimes(); + EasyMock.expect(mockContext.getIdentifierQuoteString()).andReturn("quoteString").anyTimes(); + + EasyMock.replay(mockContext); + } } \ No newline at end of file
