Repository: nifi Updated Branches: refs/heads/master c4d0c0bbd -> 37a1e6d07
NIFI-3585: Add DatabaseAdapter impl for MS SQL 2008 This closes #1584 Signed-off-by: Joey Frazee <[email protected]> Project: http://git-wip-us.apache.org/repos/asf/nifi/repo Commit: http://git-wip-us.apache.org/repos/asf/nifi/commit/37a1e6d0 Tree: http://git-wip-us.apache.org/repos/asf/nifi/tree/37a1e6d0 Diff: http://git-wip-us.apache.org/repos/asf/nifi/diff/37a1e6d0 Branch: refs/heads/master Commit: 37a1e6d07f9f8b10201c82f8610897f99142bd97 Parents: c4d0c0b Author: Matt Burgess <[email protected]> Authored: Fri Mar 10 14:16:07 2017 -0500 Committer: Joey Frazee <[email protected]> Committed: Wed Mar 22 14:57:42 2017 +0000 ---------------------------------------------------------------------- .../db/impl/MSSQL2008DatabaseAdapter.java | 91 ++++++++++++++++++++ ....nifi.processors.standard.db.DatabaseAdapter | 3 +- .../db/impl/TestMSSQL2008DatabaseAdapter.java | 82 ++++++++++++++++++ 3 files changed, 175 insertions(+), 1 deletion(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/nifi/blob/37a1e6d0/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/db/impl/MSSQL2008DatabaseAdapter.java ---------------------------------------------------------------------- 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 new file mode 100644 index 0000000..719f357 --- /dev/null +++ b/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/db/impl/MSSQL2008DatabaseAdapter.java @@ -0,0 +1,91 @@ +/* + * 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.nifi.processors.standard.db.impl; + +import org.apache.commons.lang3.StringUtils; +import org.apache.nifi.processors.standard.db.DatabaseAdapter; + +/** + * A database adapter that generates MS SQL Compatible SQL for version 2008. + */ +public class MSSQL2008DatabaseAdapter implements DatabaseAdapter { + @Override + public String getName() { + return "MS SQL 2008"; + } + + @Override + public String getDescription() { + return "Generates MS SQL Compatible SQL for version 2008"; + } + + @Override + public String getSelectStatement(String tableName, String columnNames, String whereClause, String orderByClause, Long limit, Long offset) { + if (StringUtils.isEmpty(tableName)) { + throw new IllegalArgumentException("Table name cannot be null or empty"); + } + + final StringBuilder query = new StringBuilder("SELECT "); + + // If this is a limit query and not a paging query then use TOP in MS SQL + if (limit != null) { + + if (offset != null) { + query.append("* FROM (SELECT "); + } + final long effectiveOffset = (offset == null) ? 0 : offset; + if (effectiveOffset + limit > 0) { + query.append("TOP "); + query.append(effectiveOffset + limit); + query.append(" "); + } + } + + if (StringUtils.isEmpty(columnNames) || columnNames.trim().equals("*")) { + query.append("*"); + } else { + query.append(columnNames); + } + + if (limit != null && offset != null && orderByClause != null) { + query.append(", ROW_NUMBER() OVER(ORDER BY "); + query.append(orderByClause); + query.append(" asc) rnum"); + } + query.append(" FROM "); + query.append(tableName); + + if (!StringUtils.isEmpty(whereClause)) { + query.append(" WHERE "); + query.append(whereClause); + } + + if (!StringUtils.isEmpty(orderByClause)) { + query.append(" ORDER BY "); + query.append(orderByClause); + } + + if (limit != null && offset != null) { + query.append(") A WHERE rnum > "); + query.append(offset); + query.append(" AND rnum <= "); + query.append(offset + limit); + } + + return query.toString(); + } +} http://git-wip-us.apache.org/repos/asf/nifi/blob/37a1e6d0/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/resources/META-INF/services/org.apache.nifi.processors.standard.db.DatabaseAdapter ---------------------------------------------------------------------- diff --git a/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/resources/META-INF/services/org.apache.nifi.processors.standard.db.DatabaseAdapter b/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/resources/META-INF/services/org.apache.nifi.processors.standard.db.DatabaseAdapter index 5700df0..33c7706 100644 --- a/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/resources/META-INF/services/org.apache.nifi.processors.standard.db.DatabaseAdapter +++ b/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/resources/META-INF/services/org.apache.nifi.processors.standard.db.DatabaseAdapter @@ -14,4 +14,5 @@ # limitations under the License. org.apache.nifi.processors.standard.db.impl.GenericDatabaseAdapter org.apache.nifi.processors.standard.db.impl.OracleDatabaseAdapter -org.apache.nifi.processors.standard.db.impl.MSSQLDatabaseAdapter \ No newline at end of file +org.apache.nifi.processors.standard.db.impl.MSSQLDatabaseAdapter +org.apache.nifi.processors.standard.db.impl.MSSQL2008DatabaseAdapter \ No newline at end of file http://git-wip-us.apache.org/repos/asf/nifi/blob/37a1e6d0/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/db/impl/TestMSSQL2008DatabaseAdapter.java ---------------------------------------------------------------------- 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 new file mode 100644 index 0000000..1390072 --- /dev/null +++ b/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/db/impl/TestMSSQL2008DatabaseAdapter.java @@ -0,0 +1,82 @@ +/* + * 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.nifi.processors.standard.db.impl; + +import org.apache.nifi.processors.standard.db.DatabaseAdapter; +import org.junit.Assert; +import org.junit.Test; + +public class TestMSSQL2008DatabaseAdapter { + private final DatabaseAdapter db = new MSSQL2008DatabaseAdapter(); + + @Test + public void testGeneration() throws Exception { + String sql = db.getSelectStatement("database.tablename", "some(set),of(columns),that,might,contain,methods,a.*", "", "", null, null); + String expected1 = "SELECT some(set),of(columns),that,might,contain,methods,a.* FROM database.tablename"; + Assert.assertEquals(expected1, sql); + + sql = db.getSelectStatement("database.tablename", "some(set),of(columns),that,might,contain,methods,a.*", "that=\'some\"\' value\'", "", null, null); + String expected2 = "SELECT some(set),of(columns),that,might,contain,methods,a.* FROM database.tablename WHERE that=\'some\"\' value\'"; + Assert.assertEquals(expected2, sql); + + sql = db.getSelectStatement("database.tablename", "some(set),of(columns),that,might,contain,methods,a.*", "that=\'some\"\' value\'", "might DESC", null, null); + String expected3 = "SELECT some(set),of(columns),that,might,contain,methods,a.* FROM database.tablename WHERE that=\'some\"\' value\' ORDER BY might DESC"; + Assert.assertEquals(expected3, sql); + + sql = db.getSelectStatement("database.tablename", "", "that=\'some\"\' value\'", "might DESC", null, null); + String expected4 = "SELECT * FROM database.tablename WHERE that=\'some\"\' value\' ORDER BY might DESC"; + Assert.assertEquals(expected4, sql); + } + + @Test(expected = IllegalArgumentException.class) + public void testNoTableName() throws Exception { + db.getSelectStatement("", "some(set),of(columns),that,might,contain,methods,a.*", "", "", null, null); + } + + @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"; + Assert.assertEquals(expected1, sql); + + sql = db.getSelectStatement("database.tablename", "some(set),of(columns),that,might,contain,methods,a.*", "", "contain", 100L, null); + String expected2 = "SELECT TOP 100 some(set),of(columns),that,might,contain,methods,a.* FROM database.tablename ORDER BY contain"; + Assert.assertEquals(expected2, sql); + + sql = db.getSelectStatement("database.tablename", "", "that=\'some\"\' value\'", "might DESC", 123456L, null); + String expected4 = "SELECT TOP 123456 * FROM database.tablename WHERE that=\'some\"\' value\' ORDER BY might DESC"; + Assert.assertEquals(expected4, sql); + } + + @Test + public void testPagingQuery() throws Exception { + String sql = db.getSelectStatement("database.tablename", "some(set),of(columns),that,might,contain,methods,a.*", "", "contain", 100L, 0L); + String expected1 = "SELECT * FROM (SELECT TOP 100 some(set),of(columns),that,might,contain,methods,a.*, ROW_NUMBER() OVER(ORDER BY contain asc) " + + "rnum FROM database.tablename ORDER BY contain) A WHERE rnum > 0 AND rnum <= 100"; + Assert.assertEquals(expected1, sql); + + sql = db.getSelectStatement("database.tablename", "some(set),of(columns),that,might,contain,methods,a.*", "", "contain", 10000L, 123456L); + String expected2 = "SELECT * FROM (SELECT TOP 133456 some(set),of(columns),that,might,contain,methods,a.*, ROW_NUMBER() OVER(ORDER BY contain asc) " + + "rnum FROM database.tablename ORDER BY contain) A WHERE rnum > 123456 AND rnum <= 133456"; + Assert.assertEquals(expected2, sql); + + sql = db.getSelectStatement("database.tablename", "some(set),of(columns),that,might,contain,methods,a.*", "methods='strange'", "contain", 10000L, 123456L); + String expected3 = "SELECT * FROM (SELECT TOP 133456 some(set),of(columns),that,might,contain,methods,a.*, ROW_NUMBER() OVER(ORDER BY contain asc) rnum FROM database.tablename " + + "WHERE methods='strange' ORDER BY contain) A WHERE rnum > 123456 AND rnum <= 133456"; + Assert.assertEquals(expected3, sql); + } +}
