You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@metamodel.apache.org by lo...@apache.org on 2016/12/21 07:54:06 UTC
metamodel git commit: METAMODEL-1132: Fixed
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 <r....@gmc.net>
Authored: Wed Dec 21 08:47:47 2016 +0100
Committer: Dennis Du Kr�ger <lo...@apache.org>
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