You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@nifi.apache.org by bb...@apache.org on 2019/04/29 17:15:10 UTC
[nifi] branch master updated: NIFI-6244: Provide default ORDER BY
clause in MSSQL adapters
This is an automated email from the ASF dual-hosted git repository.
bbende pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/nifi.git
The following commit(s) were added to refs/heads/master by this push:
new 7fc49c2 NIFI-6244: Provide default ORDER BY clause in MSSQL adapters
7fc49c2 is described below
commit 7fc49c2cb80ce765afd940d1e4209a8677e5d3f2
Author: Matthew Burgess <ma...@apache.org>
AuthorDate: Thu Apr 25 15:45:26 2019 -0400
NIFI-6244: Provide default ORDER BY clause in MSSQL adapters
This closes #3454.
Signed-off-by: Bryan Bende <bb...@apache.org>
---
.../processors/standard/db/impl/MSSQL2008DatabaseAdapter.java | 9 +++++++--
.../nifi/processors/standard/db/impl/MSSQLDatabaseAdapter.java | 3 ++-
.../standard/db/impl/TestMSSQL2008DatabaseAdapter.java | 8 ++++++++
.../processors/standard/db/impl/TestMSSQLDatabaseAdapter.java | 6 ++++--
4 files changed, 21 insertions(+), 5 deletions(-)
diff --git a/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/db/impl/MSSQL2008DatabaseAdapter.java b/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/db/impl/MSSQL2008DatabaseAdapter.java
index 610b34d..fed4dfa 100644
--- a/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/db/impl/MSSQL2008DatabaseAdapter.java
+++ b/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/db/impl/MSSQL2008DatabaseAdapter.java
@@ -65,9 +65,14 @@ public class MSSQL2008DatabaseAdapter extends MSSQLDatabaseAdapter {
query.append(columnNames);
}
- if (limit != null && offset != null && orderByClause != null && !useColumnForPartitioning) {
+ if (limit != null && offset != null && !useColumnForPartitioning) {
query.append(", ROW_NUMBER() OVER(ORDER BY ");
- query.append(orderByClause);
+ if (orderByClause != null && !orderByClause.isEmpty()) {
+ query.append(orderByClause);
+ } else {
+ // Add a default ORDER BY clause using the newid() function
+ query.append("newid()");
+ }
query.append(" asc) rnum");
}
query.append(" FROM ");
diff --git a/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/db/impl/MSSQLDatabaseAdapter.java b/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/db/impl/MSSQLDatabaseAdapter.java
index ec276fc..8740302 100644
--- a/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/db/impl/MSSQLDatabaseAdapter.java
+++ b/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/db/impl/MSSQLDatabaseAdapter.java
@@ -83,7 +83,8 @@ public class MSSQLDatabaseAdapter implements DatabaseAdapter {
if (StringUtils.isEmpty(columnForPartitioning)) {
if (offset != null && limit != null && limit > 0) {
if (StringUtils.isEmpty(orderByClause)) {
- throw new IllegalArgumentException("Order by clause cannot be null or empty when using row paging");
+ // Add a default ORDER BY clause using the newid() function
+ query.append(" ORDER BY newid()");
}
query.append(" OFFSET ");
diff --git a/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/db/impl/TestMSSQL2008DatabaseAdapter.java b/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/db/impl/TestMSSQL2008DatabaseAdapter.java
index 912a720..6cc4eb3 100644
--- a/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/db/impl/TestMSSQL2008DatabaseAdapter.java
+++ b/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/db/impl/TestMSSQL2008DatabaseAdapter.java
@@ -48,6 +48,14 @@ public class TestMSSQL2008DatabaseAdapter {
}
@Test
+ public void testPagingNoOrderBy() throws Exception {
+ String sql1 = db.getSelectStatement("database.tablename", "some(set),of(columns),that,might,contain,methods,a.*","",null,10L,0L);
+ String expected1 = "SELECT * FROM (SELECT TOP 10 some(set),of(columns),that,might,contain,methods,a.*, ROW_NUMBER() OVER(ORDER BY newid() asc) rnum "
+ + "FROM database.tablename) A WHERE rnum > 0 AND rnum <= 10";
+ Assert.assertEquals(sql1,expected1);
+ }
+
+ @Test
public void testTOPQuery() throws Exception {
String sql = db.getSelectStatement("database.tablename", "some(set),of(columns),that,might,contain,methods,a.*", "", "", 100L, null);
String expected1 = "SELECT TOP 100 some(set),of(columns),that,might,contain,methods,a.* FROM database.tablename";
diff --git a/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/db/impl/TestMSSQLDatabaseAdapter.java b/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/db/impl/TestMSSQLDatabaseAdapter.java
index de33753..c760052 100644
--- a/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/db/impl/TestMSSQLDatabaseAdapter.java
+++ b/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/db/impl/TestMSSQLDatabaseAdapter.java
@@ -47,9 +47,11 @@ public class TestMSSQLDatabaseAdapter {
db.getSelectStatement("", "some(set),of(columns),that,might,contain,methods,a.*","","",null,null);
}
- @Test(expected = IllegalArgumentException.class)
+ @Test
public void testPagingNoOrderBy() throws Exception {
- db.getSelectStatement("database.tablename", "some(set),of(columns),that,might,contain,methods,a.*","","",10L,0L);
+ String sql1 = db.getSelectStatement("database.tablename", "some(set),of(columns),that,might,contain,methods,a.*","","",10L,0L);
+ String expected1 = "SELECT some(set),of(columns),that,might,contain,methods,a.* FROM database.tablename ORDER BY newid() OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY";
+ Assert.assertEquals(sql1,expected1);
}
@Test