[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r379351360 ## File path: extensions/sql/processors/ExecuteSQL.cpp ## @@ -0,0 +1,123 @@ +/** + * @file ExecuteSQL.cpp + * ExecuteSQL class declaration + * + * 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. + */ + +#include "ExecuteSQL.h" + +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include + +#include + +#include "io/DataStream.h" +#include "core/ProcessContext.h" +#include "core/ProcessSession.h" +#include "Exception.h" +#include "utils/OsUtils.h" +#include "data/DatabaseConnectors.h" +#include "data/JSONSQLWriter.h" +#include "data/SQLRowsetProcessor.h" +#include "data/WriteCallback.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace processors { + +const std::string ExecuteSQL::ProcessorName("ExecuteSQL"); + +const core::Property ExecuteSQL::s_sqlSelectQuery( + core::PropertyBuilder::createProperty("SQL select query")->isRequired(true)->withDescription( +"The SQL select query to execute. The query can be empty, a constant value, or built from attributes using Expression Language. " +"If this property is specified, it will be used regardless of the content of incoming flowfiles. " +"If this property is empty, the content of the incoming flow file is expected to contain a valid SQL select query, to be issued by the processor to the database. " +"Note that Expression Language is not evaluated for flow file contents.")->supportsExpressionLanguage(true)->build()); + +const core::Property ExecuteSQL::s_maxRowsPerFlowFile( + core::PropertyBuilder::createProperty("Max Rows Per Flow File")->isRequired(true)->withDefaultValue(0)->withDescription( + "The maximum number of result rows that will be included intoi a flow file. If zero then all will be placed into the flow file")->supportsExpressionLanguage(true)->build()); + +const core::Relationship ExecuteSQL::s_success("success", "Successfully created FlowFile from SQL query result set."); + +static const std::string ResultRowCount = "executesql.row.count"; + +ExecuteSQL::ExecuteSQL(const std::string& name, utils::Identifier uuid) + : SQLProcessor(name, uuid), max_rows_(0) { +} + +ExecuteSQL::~ExecuteSQL() { +} + +void ExecuteSQL::initialize() { + //! Set the supported properties + setSupportedProperties( { dbControllerService(), outputFormat(), s_sqlSelectQuery, s_maxRowsPerFlowFile}); + + //! Set the supported relationships + setSupportedRelationships( { s_success }); +} + +void ExecuteSQL::processOnSchedule(const core::ProcessContext ) { + initOutputFormat(context); + + context.getProperty(s_sqlSelectQuery.getName(), sqlSelectQuery_); + context.getProperty(s_maxRowsPerFlowFile.getName(), max_rows_); +} + +void ExecuteSQL::processOnTrigger(core::ProcessSession ) { + auto statement = connection_->prepareStatement(sqlSelectQuery_); + + auto rowset = statement->execute(); + + int count = 0; + size_t rowCount = 0; + sql::JSONSQLWriter sqlWriter(isJSONPretty()); + sql::SQLRowsetProcessor sqlRowsetProcessor(rowset, { }); + + // Process rowset. + do { +rowCount = sqlRowsetProcessor.process(max_rows_ == 0 ? std::numeric_limits::max() : max_rows_); +count++; +if (rowCount == 0) + break; + +const auto& output = sqlWriter.toString(); Review comment: Changed in QueryDatabaseTable as well. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r379345046 ## File path: extensions/sql/processors/ExecuteSQL.cpp ## @@ -0,0 +1,123 @@ +/** + * @file ExecuteSQL.cpp + * ExecuteSQL class declaration + * + * 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. + */ + +#include "ExecuteSQL.h" + +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include + +#include + +#include "io/DataStream.h" +#include "core/ProcessContext.h" +#include "core/ProcessSession.h" +#include "Exception.h" +#include "utils/OsUtils.h" +#include "data/DatabaseConnectors.h" +#include "data/JSONSQLWriter.h" +#include "data/SQLRowsetProcessor.h" +#include "data/WriteCallback.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace processors { + +const std::string ExecuteSQL::ProcessorName("ExecuteSQL"); + +const core::Property ExecuteSQL::s_sqlSelectQuery( + core::PropertyBuilder::createProperty("SQL select query")->isRequired(true)->withDescription( +"The SQL select query to execute. The query can be empty, a constant value, or built from attributes using Expression Language. " +"If this property is specified, it will be used regardless of the content of incoming flowfiles. " +"If this property is empty, the content of the incoming flow file is expected to contain a valid SQL select query, to be issued by the processor to the database. " +"Note that Expression Language is not evaluated for flow file contents.")->supportsExpressionLanguage(true)->build()); + +const core::Property ExecuteSQL::s_maxRowsPerFlowFile( + core::PropertyBuilder::createProperty("Max Rows Per Flow File")->isRequired(true)->withDefaultValue(0)->withDescription( + "The maximum number of result rows that will be included intoi a flow file. If zero then all will be placed into the flow file")->supportsExpressionLanguage(true)->build()); + +const core::Relationship ExecuteSQL::s_success("success", "Successfully created FlowFile from SQL query result set."); + +static const std::string ResultRowCount = "executesql.row.count"; + +ExecuteSQL::ExecuteSQL(const std::string& name, utils::Identifier uuid) + : SQLProcessor(name, uuid), max_rows_(0) { +} + +ExecuteSQL::~ExecuteSQL() { +} + +void ExecuteSQL::initialize() { + //! Set the supported properties + setSupportedProperties( { dbControllerService(), outputFormat(), s_sqlSelectQuery, s_maxRowsPerFlowFile}); + + //! Set the supported relationships + setSupportedRelationships( { s_success }); +} + +void ExecuteSQL::processOnSchedule(const core::ProcessContext ) { + initOutputFormat(context); + + context.getProperty(s_sqlSelectQuery.getName(), sqlSelectQuery_); + context.getProperty(s_maxRowsPerFlowFile.getName(), max_rows_); +} + +void ExecuteSQL::processOnTrigger(core::ProcessSession ) { + auto statement = connection_->prepareStatement(sqlSelectQuery_); + + auto rowset = statement->execute(); + + int count = 0; + size_t rowCount = 0; + sql::JSONSQLWriter sqlWriter(isJSONPretty()); + sql::SQLRowsetProcessor sqlRowsetProcessor(rowset, { }); + + // Process rowset. + do { +rowCount = sqlRowsetProcessor.process(max_rows_ == 0 ? std::numeric_limits::max() : max_rows_); +count++; +if (rowCount == 0) + break; + +const auto& output = sqlWriter.toString(); Review comment: It is an alternative to `move` copy constructor and can be used without knowing if returned object has it or not. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r379060348 ## File path: extensions/sql/data/WriteCallback.h ## @@ -0,0 +1,49 @@ +/** + * + * 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. + */ + +#pragma once + +#include + +#include "FlowFileRecord.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { + +class WriteCallback : public OutputStreamCallback { +public: + WriteCallback(const std::string& data) Review comment: Signature could be WriteCallback(std::string data) and then for optimization caller can call `WriteCallback writer(std::move(output));` and in this particular case it works, but it won't if `output` is used later in the code. `data` is not changed and because of this `WriteCallback(const std::string& data)` has const reference. We need to remove const because `WriteCallback` uses `DataStream::writeData(uint8_t *value, int size);` (which should be `const uint8_t* value` BTW), but this is internals of WriteCallback, and it's callers should not know about it. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378742650 ## File path: extensions/sql/data/DatabaseConnectors.h ## @@ -0,0 +1,106 @@ +/** + * + * 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. + */ + +#ifndef EXTENSIONS_SQL_SERVICES_DATABASECONNECTORS_H_ +#define EXTENSIONS_SQL_SERVICES_DATABASECONNECTORS_H_ + +#include +#include +#include +#include + +#include + +#include "Utils.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace sql { + +/** + * We do not intend to create an abstract facade here. We know that SOCI is the underlying + * SQL library. We only wish to abstract ODBC specific information + */ + +class Statement { + public: + + explicit Statement(const std::unique_ptr& session, const std::string ) Review comment: Fixed. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378742486 ## File path: extensions/sql/processors/QueryDatabaseTable.cpp ## @@ -0,0 +1,475 @@ +/** + * @file QueryDatabaseTable.cpp + * PutSQL class declaration + * + * 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. + */ + +#include "QueryDatabaseTable.h" + +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include + +#include + +#include "io/DataStream.h" +#include "core/ProcessContext.h" +#include "core/ProcessSession.h" +#include "Exception.h" +#include "utils/OsUtils.h" +#include "data/DatabaseConnectors.h" +#include "data/JSONSQLWriter.h" +#include "data/SQLRowsetProcessor.h" +#include "data/WriteCallback.h" +#include "data/MaxCollector.h" +#include "data/Utils.h" +#include "utils/file/FileUtils.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace processors { + +const std::string QueryDatabaseTable::ProcessorName("QueryDatabaseTable"); + +const core::Property QueryDatabaseTable::s_tableName( + core::PropertyBuilder::createProperty("Table Name")->isRequired(true)->withDescription("The name of the database table to be queried.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_columnNames( + core::PropertyBuilder::createProperty("Columns to Return")->isRequired(false)->withDescription( +"A comma-separated list of column names to be used in the query. If your database requires special treatment of the names (quoting, e.g.), each name should include such treatment. " +"If no column names are supplied, all columns in the specified table will be returned. " +"NOTE: It is important to use consistent column names for a given table for incremental fetch to work properly.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_maxValueColumnNames( + core::PropertyBuilder::createProperty("Maximum-value Columns")->isRequired(false)->withDescription( +"A comma-separated list of column names. The processor will keep track of the maximum value for each column that has been returned since the processor started running. " +"Using multiple columns implies an order to the column list, and each column's values are expected to increase more slowly than the previous columns' values. " +"Thus, using multiple columns implies a hierarchical structure of columns, which is usually used for partitioning tables. " +"This processor can be used to retrieve only those rows that have been added/updated since the last retrieval. " +"Note that some ODBC types such as bit/boolean are not conducive to maintaining maximum value, so columns of these types should not be listed in this property, and will result in error(s) during processing. " +"If no columns are provided, all rows from the table will be considered, which could have a performance impact. " +"NOTE: It is important to use consistent max-value column names for a given table for incremental fetch to work properly. " +"NOTE: Because of a limitation of database access library 'soci', which doesn't support milliseconds in it's 'dt_date', " +"there is a possibility that flowfiles might have duplicated records, if a max-value column with 'dt_date' type has value with milliseconds.")-> +supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_whereClause( + core::PropertyBuilder::createProperty("db-fetch-where-clause")->isRequired(false)->withDescription( +"A custom clause to be added in the WHERE condition when building SQL queries.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_sqlQuery( + core::PropertyBuilder::createProperty("db-fetch-sql-query")->isRequired(false)->withDescription( +"A custom SQL query used to retrieve data. Instead of building a SQL query from other properties, this query will be wrapped as a sub-query. " +"Query must have no ORDER BY statement.")->supportsExpressionLanguage(true)->build()); + +const
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378741857 ## File path: win_build_vs.bat ## @@ -77,4 +81,4 @@ goto :eof :usage @echo "Usage: %0 options" -exit /B 1 +exit /B 1 Review comment: Fixed. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378740016 ## File path: extensions/sql/data/WriteCallback.h ## @@ -0,0 +1,47 @@ +/** + * + * 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. + */ + +#pragma once + +#include "FlowFileRecord.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { + +class WriteCallback : public OutputStreamCallback { +public: + WriteCallback(const char *data, uint64_t size) +: _data(const_cast(data)), Review comment: WriteCallback called like WriteCallback(out.data(), ...); `out` is string `out.data()` type is `const char*`. With WriteCallback(char*, ...) , WriteCallback(out.data(), ...) won't compile because cannot convert from `const char* -> char*`. But I'll refactor to WriteCallback(const std::string&). This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378740016 ## File path: extensions/sql/data/WriteCallback.h ## @@ -0,0 +1,47 @@ +/** + * + * 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. + */ + +#pragma once + +#include "FlowFileRecord.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { + +class WriteCallback : public OutputStreamCallback { +public: + WriteCallback(const char *data, uint64_t size) +: _data(const_cast(data)), Review comment: WriteCallback called like WriteCallback(out.data(), ...); out is string out.data() type is const char*. With WriteCallback(char*, ...) , WriteCallback(out.data(), ...) won't compile because cannot convert from const char* -> char*. But I'll refactor to WriteCallback(const std::string&). This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378603638 ## File path: extensions/sql/processors/QueryDatabaseTable.cpp ## @@ -0,0 +1,475 @@ +/** + * @file QueryDatabaseTable.cpp + * PutSQL class declaration + * + * 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. + */ + +#include "QueryDatabaseTable.h" + +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include + +#include + +#include "io/DataStream.h" +#include "core/ProcessContext.h" +#include "core/ProcessSession.h" +#include "Exception.h" +#include "utils/OsUtils.h" +#include "data/DatabaseConnectors.h" +#include "data/JSONSQLWriter.h" +#include "data/SQLRowsetProcessor.h" +#include "data/WriteCallback.h" +#include "data/MaxCollector.h" +#include "data/Utils.h" +#include "utils/file/FileUtils.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace processors { + +const std::string QueryDatabaseTable::ProcessorName("QueryDatabaseTable"); + +const core::Property QueryDatabaseTable::s_tableName( + core::PropertyBuilder::createProperty("Table Name")->isRequired(true)->withDescription("The name of the database table to be queried.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_columnNames( + core::PropertyBuilder::createProperty("Columns to Return")->isRequired(false)->withDescription( +"A comma-separated list of column names to be used in the query. If your database requires special treatment of the names (quoting, e.g.), each name should include such treatment. " +"If no column names are supplied, all columns in the specified table will be returned. " +"NOTE: It is important to use consistent column names for a given table for incremental fetch to work properly.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_maxValueColumnNames( + core::PropertyBuilder::createProperty("Maximum-value Columns")->isRequired(false)->withDescription( +"A comma-separated list of column names. The processor will keep track of the maximum value for each column that has been returned since the processor started running. " +"Using multiple columns implies an order to the column list, and each column's values are expected to increase more slowly than the previous columns' values. " +"Thus, using multiple columns implies a hierarchical structure of columns, which is usually used for partitioning tables. " +"This processor can be used to retrieve only those rows that have been added/updated since the last retrieval. " +"Note that some ODBC types such as bit/boolean are not conducive to maintaining maximum value, so columns of these types should not be listed in this property, and will result in error(s) during processing. " +"If no columns are provided, all rows from the table will be considered, which could have a performance impact. " +"NOTE: It is important to use consistent max-value column names for a given table for incremental fetch to work properly. " +"NOTE: Because of a limitation of database access library 'soci', which doesn't support milliseconds in it's 'dt_date', " +"there is a possibility that flowfiles might have duplicated records, if a max-value column with 'dt_date' type has value with milliseconds.")-> +supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_whereClause( + core::PropertyBuilder::createProperty("db-fetch-where-clause")->isRequired(false)->withDescription( +"A custom clause to be added in the WHERE condition when building SQL queries.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_sqlQuery( + core::PropertyBuilder::createProperty("db-fetch-sql-query")->isRequired(false)->withDescription( +"A custom SQL query used to retrieve data. Instead of building a SQL query from other properties, this query will be wrapped as a sub-query. " +"Query must have no ORDER BY statement.")->supportsExpressionLanguage(true)->build()); + +const
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378603638 ## File path: extensions/sql/processors/QueryDatabaseTable.cpp ## @@ -0,0 +1,475 @@ +/** + * @file QueryDatabaseTable.cpp + * PutSQL class declaration + * + * 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. + */ + +#include "QueryDatabaseTable.h" + +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include + +#include + +#include "io/DataStream.h" +#include "core/ProcessContext.h" +#include "core/ProcessSession.h" +#include "Exception.h" +#include "utils/OsUtils.h" +#include "data/DatabaseConnectors.h" +#include "data/JSONSQLWriter.h" +#include "data/SQLRowsetProcessor.h" +#include "data/WriteCallback.h" +#include "data/MaxCollector.h" +#include "data/Utils.h" +#include "utils/file/FileUtils.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace processors { + +const std::string QueryDatabaseTable::ProcessorName("QueryDatabaseTable"); + +const core::Property QueryDatabaseTable::s_tableName( + core::PropertyBuilder::createProperty("Table Name")->isRequired(true)->withDescription("The name of the database table to be queried.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_columnNames( + core::PropertyBuilder::createProperty("Columns to Return")->isRequired(false)->withDescription( +"A comma-separated list of column names to be used in the query. If your database requires special treatment of the names (quoting, e.g.), each name should include such treatment. " +"If no column names are supplied, all columns in the specified table will be returned. " +"NOTE: It is important to use consistent column names for a given table for incremental fetch to work properly.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_maxValueColumnNames( + core::PropertyBuilder::createProperty("Maximum-value Columns")->isRequired(false)->withDescription( +"A comma-separated list of column names. The processor will keep track of the maximum value for each column that has been returned since the processor started running. " +"Using multiple columns implies an order to the column list, and each column's values are expected to increase more slowly than the previous columns' values. " +"Thus, using multiple columns implies a hierarchical structure of columns, which is usually used for partitioning tables. " +"This processor can be used to retrieve only those rows that have been added/updated since the last retrieval. " +"Note that some ODBC types such as bit/boolean are not conducive to maintaining maximum value, so columns of these types should not be listed in this property, and will result in error(s) during processing. " +"If no columns are provided, all rows from the table will be considered, which could have a performance impact. " +"NOTE: It is important to use consistent max-value column names for a given table for incremental fetch to work properly. " +"NOTE: Because of a limitation of database access library 'soci', which doesn't support milliseconds in it's 'dt_date', " +"there is a possibility that flowfiles might have duplicated records, if a max-value column with 'dt_date' type has value with milliseconds.")-> +supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_whereClause( + core::PropertyBuilder::createProperty("db-fetch-where-clause")->isRequired(false)->withDescription( +"A custom clause to be added in the WHERE condition when building SQL queries.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_sqlQuery( + core::PropertyBuilder::createProperty("db-fetch-sql-query")->isRequired(false)->withDescription( +"A custom SQL query used to retrieve data. Instead of building a SQL query from other properties, this query will be wrapped as a sub-query. " +"Query must have no ORDER BY statement.")->supportsExpressionLanguage(true)->build()); + +const
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378603638 ## File path: extensions/sql/processors/QueryDatabaseTable.cpp ## @@ -0,0 +1,475 @@ +/** + * @file QueryDatabaseTable.cpp + * PutSQL class declaration + * + * 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. + */ + +#include "QueryDatabaseTable.h" + +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include + +#include + +#include "io/DataStream.h" +#include "core/ProcessContext.h" +#include "core/ProcessSession.h" +#include "Exception.h" +#include "utils/OsUtils.h" +#include "data/DatabaseConnectors.h" +#include "data/JSONSQLWriter.h" +#include "data/SQLRowsetProcessor.h" +#include "data/WriteCallback.h" +#include "data/MaxCollector.h" +#include "data/Utils.h" +#include "utils/file/FileUtils.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace processors { + +const std::string QueryDatabaseTable::ProcessorName("QueryDatabaseTable"); + +const core::Property QueryDatabaseTable::s_tableName( + core::PropertyBuilder::createProperty("Table Name")->isRequired(true)->withDescription("The name of the database table to be queried.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_columnNames( + core::PropertyBuilder::createProperty("Columns to Return")->isRequired(false)->withDescription( +"A comma-separated list of column names to be used in the query. If your database requires special treatment of the names (quoting, e.g.), each name should include such treatment. " +"If no column names are supplied, all columns in the specified table will be returned. " +"NOTE: It is important to use consistent column names for a given table for incremental fetch to work properly.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_maxValueColumnNames( + core::PropertyBuilder::createProperty("Maximum-value Columns")->isRequired(false)->withDescription( +"A comma-separated list of column names. The processor will keep track of the maximum value for each column that has been returned since the processor started running. " +"Using multiple columns implies an order to the column list, and each column's values are expected to increase more slowly than the previous columns' values. " +"Thus, using multiple columns implies a hierarchical structure of columns, which is usually used for partitioning tables. " +"This processor can be used to retrieve only those rows that have been added/updated since the last retrieval. " +"Note that some ODBC types such as bit/boolean are not conducive to maintaining maximum value, so columns of these types should not be listed in this property, and will result in error(s) during processing. " +"If no columns are provided, all rows from the table will be considered, which could have a performance impact. " +"NOTE: It is important to use consistent max-value column names for a given table for incremental fetch to work properly. " +"NOTE: Because of a limitation of database access library 'soci', which doesn't support milliseconds in it's 'dt_date', " +"there is a possibility that flowfiles might have duplicated records, if a max-value column with 'dt_date' type has value with milliseconds.")-> +supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_whereClause( + core::PropertyBuilder::createProperty("db-fetch-where-clause")->isRequired(false)->withDescription( +"A custom clause to be added in the WHERE condition when building SQL queries.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_sqlQuery( + core::PropertyBuilder::createProperty("db-fetch-sql-query")->isRequired(false)->withDescription( +"A custom SQL query used to retrieve data. Instead of building a SQL query from other properties, this query will be wrapped as a sub-query. " +"Query must have no ORDER BY statement.")->supportsExpressionLanguage(true)->build()); + +const
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378188642 ## File path: extensions/sql/services/ODBCConnector.h ## @@ -0,0 +1,127 @@ +/** + * + * 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. + */ + +#pragma once + +#include "core/logging/LoggerConfiguration.h" +#include "core/controller/ControllerService.h" + +#include "DatabaseService.h" +#include "core/Resource.h" +#include "data/DatabaseConnectors.h" +#include +#include + +#include +#include + +#include + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace sql { +namespace controllers { + +class ODBCConnection : public sql::Connection { + public: + explicit ODBCConnection(const std::string& connectionString) +: connection_string_(connectionString) { + session_ = std::make_unique(getSessionParameters()); + } + + virtual ~ODBCConnection() { + } + + bool connected(std::string& exception) const override { +try { + exception.clear(); + // According to https://stackoverflow.com/questions/3668506/efficient-sql-test-query-or-validation-query-that-will-work-across-all-or-most by Rob Hruska, + // 'select 1' works for: H2, MySQL, Microsoft SQL Server, PostgreSQL, SQLite. For Orcale 'SELECT 1 FROM DUAL' works. Review comment: Fixed. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378188342 ## File path: extensions/sql/processors/QueryDatabaseTable.cpp ## @@ -0,0 +1,475 @@ +/** + * @file QueryDatabaseTable.cpp + * PutSQL class declaration + * + * 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. + */ + +#include "QueryDatabaseTable.h" + +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include + +#include + +#include "io/DataStream.h" +#include "core/ProcessContext.h" +#include "core/ProcessSession.h" +#include "Exception.h" +#include "utils/OsUtils.h" +#include "data/DatabaseConnectors.h" +#include "data/JSONSQLWriter.h" +#include "data/SQLRowsetProcessor.h" +#include "data/WriteCallback.h" +#include "data/MaxCollector.h" +#include "data/Utils.h" +#include "utils/file/FileUtils.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace processors { + +const std::string QueryDatabaseTable::ProcessorName("QueryDatabaseTable"); + +const core::Property QueryDatabaseTable::s_tableName( + core::PropertyBuilder::createProperty("Table Name")->isRequired(true)->withDescription("The name of the database table to be queried.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_columnNames( + core::PropertyBuilder::createProperty("Columns to Return")->isRequired(false)->withDescription( +"A comma-separated list of column names to be used in the query. If your database requires special treatment of the names (quoting, e.g.), each name should include such treatment. " +"If no column names are supplied, all columns in the specified table will be returned. " +"NOTE: It is important to use consistent column names for a given table for incremental fetch to work properly.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_maxValueColumnNames( + core::PropertyBuilder::createProperty("Maximum-value Columns")->isRequired(false)->withDescription( +"A comma-separated list of column names. The processor will keep track of the maximum value for each column that has been returned since the processor started running. " +"Using multiple columns implies an order to the column list, and each column's values are expected to increase more slowly than the previous columns' values. " +"Thus, using multiple columns implies a hierarchical structure of columns, which is usually used for partitioning tables. " +"This processor can be used to retrieve only those rows that have been added/updated since the last retrieval. " +"Note that some ODBC types such as bit/boolean are not conducive to maintaining maximum value, so columns of these types should not be listed in this property, and will result in error(s) during processing. " +"If no columns are provided, all rows from the table will be considered, which could have a performance impact. " +"NOTE: It is important to use consistent max-value column names for a given table for incremental fetch to work properly. " +"NOTE: Because of a limitation of database access library 'soci', which doesn't support milliseconds in it's 'dt_date', " +"there is a possibility that flowfiles might have duplicated records, if a max-value column with 'dt_date' type has value with milliseconds.")-> +supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_whereClause( + core::PropertyBuilder::createProperty("db-fetch-where-clause")->isRequired(false)->withDescription( +"A custom clause to be added in the WHERE condition when building SQL queries.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_sqlQuery( + core::PropertyBuilder::createProperty("db-fetch-sql-query")->isRequired(false)->withDescription( +"A custom SQL query used to retrieve data. Instead of building a SQL query from other properties, this query will be wrapped as a sub-query. " +"Query must have no ORDER BY statement.")->supportsExpressionLanguage(true)->build()); + +const
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378188464 ## File path: extensions/sql/SQLLoader.h ## @@ -0,0 +1,81 @@ +/** + * + * 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. + */ +#ifndef EXTENSION_SQLLOADER_H +#define EXTENSION_SQLLOADER_H + +#include "core/ClassLoader.h" +#include "processors/ExecuteSQL.h" +#include "processors/PutSQL.h" +#include "processors/QueryDatabaseTable.h" +#include "services/ODBCConnector.h" + +class SQLFactory : public core::ObjectFactory { + public: + SQLFactory() { + + } + + /** + * Gets the name of the object. + * @return class name of processor + */ + virtual std::string getName() override { +return "SQLFactory"; + } + + virtual std::string getClassName() override{ +return "SQLFactory"; + } + /** + * Gets the class name for the object + * @return class name for the processor. + */ + virtual std::vector getClassNames() override{ +std::vector class_names = {"ExecuteSQL", "PutSQL", "QueryDatabaseTable", "ODBCService"}; +return class_names; + } + + template + static std::unique_ptr getObjectFactory() { +return std::unique_ptr(new core::DefautObjectFactory()); Review comment: Fixed. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378188245 ## File path: extensions/sql/data/SQLRowsetProcessor.h ## @@ -0,0 +1,62 @@ +/** + * + * 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. + */ + +#pragma once + +#include + +#include + +#include "SQLRowSubscriber.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace sql { + +class SQLRowsetProcessor +{ + public: + SQLRowsetProcessor(const soci::rowset& rowset, const std::vector& rowSubscribers); + + size_t process(size_t max = 0); Review comment: Fixed. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378188116 ## File path: extensions/sql/data/MaxCollector.h ## @@ -0,0 +1,172 @@ +/** + * + * 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. + */ + +#pragma once + +#include +#include +#include + +#include "SQLRowSubscriber.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace sql { + +class MaxCollector: public SQLRowSubscriber { + void beginProcessRow() override {} + + void endProcessRow() override { +if (columnsVerified_) { + return; +} + +if (countColumns_ != mapState_.size()) + throw minifi::Exception(PROCESSOR_EXCEPTION, "MaxCollector: Column(s) '" + maxValueColumnNames_ + "' are not found in the columns of '" + selectQuery_ + "' result."); + +columnsVerified_ = true; + } + + void processColumnName(const std::string& name) override { +if (columnsVerified_) { + return; +} + +if (mapState_.count(name)) { + countColumns_++; +} + } + + void processColumn(const std::string& name, const std::string& value) override { +updateMaxValue(name, '\'' + value + '\''); + } + + void processColumn(const std::string& name, double value) override { +updateMaxValue(name, value); + } + + void processColumn(const std::string& name, int value) override { +updateMaxValue(name, value); + } + + void processColumn(const std::string& name, long long value) override { +updateMaxValue(name, value); + } + + void processColumn(const std::string& name, unsigned long long value) override { +updateMaxValue(name, value); + } + + void processColumn(const std::string& name, const char* value) override {} + + template + struct MaxValue { +void updateMaxValue(const std::string& name, const T& value) { + const auto it = mapColumnNameValue_.find(name); + if (it == mapColumnNameValue_.end()) { +mapColumnNameValue_.insert({ name, value }); + } else { +if (value > it->second) { + it->second = value; +} + } +} + +std::unordered_map mapColumnNameValue_; + }; + + template + struct TupleIndexByType { +constexpr static int index() { + using tupleElType = typename std::decay(Tuple()))>::type; + + return TupleIndexByType>::value>::index(); +} + }; + + template + struct TupleIndexByType { +constexpr static int index() { + return Index; +} + }; Review comment: Fixed. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378133280 ## File path: win_build_vs.bat ## @@ -22,10 +22,12 @@ if [%1]==[] goto usage set builddir=%1 set skiptests=OFF set cmake_build_type=Release +set build_type=Release Review comment: Fixed. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378131050 ## File path: extensions/sql/processors/QueryDatabaseTable.cpp ## @@ -0,0 +1,475 @@ +/** + * @file QueryDatabaseTable.cpp + * PutSQL class declaration + * + * 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. + */ + +#include "QueryDatabaseTable.h" + +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include + +#include + +#include "io/DataStream.h" +#include "core/ProcessContext.h" +#include "core/ProcessSession.h" +#include "Exception.h" +#include "utils/OsUtils.h" +#include "data/DatabaseConnectors.h" +#include "data/JSONSQLWriter.h" +#include "data/SQLRowsetProcessor.h" +#include "data/WriteCallback.h" +#include "data/MaxCollector.h" +#include "data/Utils.h" +#include "utils/file/FileUtils.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace processors { + +const std::string QueryDatabaseTable::ProcessorName("QueryDatabaseTable"); + +const core::Property QueryDatabaseTable::s_tableName( + core::PropertyBuilder::createProperty("Table Name")->isRequired(true)->withDescription("The name of the database table to be queried.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_columnNames( + core::PropertyBuilder::createProperty("Columns to Return")->isRequired(false)->withDescription( +"A comma-separated list of column names to be used in the query. If your database requires special treatment of the names (quoting, e.g.), each name should include such treatment. " +"If no column names are supplied, all columns in the specified table will be returned. " +"NOTE: It is important to use consistent column names for a given table for incremental fetch to work properly.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_maxValueColumnNames( + core::PropertyBuilder::createProperty("Maximum-value Columns")->isRequired(false)->withDescription( +"A comma-separated list of column names. The processor will keep track of the maximum value for each column that has been returned since the processor started running. " +"Using multiple columns implies an order to the column list, and each column's values are expected to increase more slowly than the previous columns' values. " +"Thus, using multiple columns implies a hierarchical structure of columns, which is usually used for partitioning tables. " +"This processor can be used to retrieve only those rows that have been added/updated since the last retrieval. " +"Note that some ODBC types such as bit/boolean are not conducive to maintaining maximum value, so columns of these types should not be listed in this property, and will result in error(s) during processing. " +"If no columns are provided, all rows from the table will be considered, which could have a performance impact. " +"NOTE: It is important to use consistent max-value column names for a given table for incremental fetch to work properly. " +"NOTE: Because of a limitation of database access library 'soci', which doesn't support milliseconds in it's 'dt_date', " +"there is a possibility that flowfiles might have duplicated records, if a max-value column with 'dt_date' type has value with milliseconds.")-> +supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_whereClause( + core::PropertyBuilder::createProperty("db-fetch-where-clause")->isRequired(false)->withDescription( +"A custom clause to be added in the WHERE condition when building SQL queries.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_sqlQuery( + core::PropertyBuilder::createProperty("db-fetch-sql-query")->isRequired(false)->withDescription( +"A custom SQL query used to retrieve data. Instead of building a SQL query from other properties, this query will be wrapped as a sub-query. " +"Query must have no ORDER BY statement.")->supportsExpressionLanguage(true)->build()); + +const
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378130935 ## File path: extensions/sql/services/ODBCConnector.h ## @@ -0,0 +1,127 @@ +/** + * + * 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. + */ + +#pragma once + +#include "core/logging/LoggerConfiguration.h" +#include "core/controller/ControllerService.h" + +#include "DatabaseService.h" +#include "core/Resource.h" +#include "data/DatabaseConnectors.h" +#include +#include + +#include +#include + +#include + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace sql { +namespace controllers { + +class ODBCConnection : public sql::Connection { + public: + explicit ODBCConnection(const std::string& connectionString) +: connection_string_(connectionString) { + session_ = std::make_unique(getSessionParameters()); + } + + virtual ~ODBCConnection() { + } + + bool connected(std::string& exception) const override { +try { + exception.clear(); + // According to https://stackoverflow.com/questions/3668506/efficient-sql-test-query-or-validation-query-that-will-work-across-all-or-most by Rob Hruska, + // 'select 1' works for: H2, MySQL, Microsoft SQL Server, PostgreSQL, SQLite. For Orcale 'SELECT 1 FROM DUAL' works. + prepareStatement("select 1")->execute(); + return true; +} catch (std::exception& e) { + exception = e.what(); + return false; +} + } + + std::unique_ptr prepareStatement(const std::string& query) const override { +return std::make_unique(session_, query); + } + + std::unique_ptr getSession() const override { +return std::make_unique(session_); + } + + private: + const soci::connection_parameters getSessionParameters() const { Review comment: Fixed. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378129066 ## File path: extensions/sql/data/JSONSQLWriter.h ## @@ -0,0 +1,65 @@ +/** + * + * 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. + */ + +#pragma once + +#include "rapidjson/document.h" + +#include "SQLWriter.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace sql { + +class JSONSQLWriter: public SQLWriter { + public: + JSONSQLWriter(bool pretty); Review comment: Fixed. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378128964 ## File path: extensions/sql/data/DatabaseConnectors.h ## @@ -0,0 +1,106 @@ +/** + * + * 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. + */ + +#ifndef EXTENSIONS_SQL_SERVICES_DATABASECONNECTORS_H_ +#define EXTENSIONS_SQL_SERVICES_DATABASECONNECTORS_H_ + +#include +#include +#include +#include Review comment: Fixed. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378128845 ## File path: extensions/sql/SQLLoader.h ## @@ -0,0 +1,81 @@ +/** + * + * 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. + */ +#ifndef EXTENSION_SQLLOADER_H +#define EXTENSION_SQLLOADER_H + +#include "core/ClassLoader.h" +#include "processors/ExecuteSQL.h" +#include "processors/PutSQL.h" +#include "processors/QueryDatabaseTable.h" +#include "services/ODBCConnector.h" + +class SQLFactory : public core::ObjectFactory { + public: + SQLFactory() { + + } Review comment: Fixed. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378128742 ## File path: extensions/sql/SQLLoader.h ## @@ -0,0 +1,81 @@ +/** + * + * 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. + */ +#ifndef EXTENSION_SQLLOADER_H +#define EXTENSION_SQLLOADER_H + +#include "core/ClassLoader.h" +#include "processors/ExecuteSQL.h" +#include "processors/PutSQL.h" +#include "processors/QueryDatabaseTable.h" +#include "services/ODBCConnector.h" + +class SQLFactory : public core::ObjectFactory { + public: + SQLFactory() { + + } + + /** + * Gets the name of the object. + * @return class name of processor + */ + virtual std::string getName() override { +return "SQLFactory"; + } + + virtual std::string getClassName() override{ +return "SQLFactory"; + } + /** + * Gets the class name for the object + * @return class name for the processor. + */ + virtual std::vector getClassNames() override{ Review comment: Fixed. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378128633 ## File path: extensions/sql/SQLLoader.h ## @@ -0,0 +1,81 @@ +/** + * + * 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. + */ +#ifndef EXTENSION_SQLLOADER_H +#define EXTENSION_SQLLOADER_H + +#include "core/ClassLoader.h" +#include "processors/ExecuteSQL.h" +#include "processors/PutSQL.h" +#include "processors/QueryDatabaseTable.h" +#include "services/ODBCConnector.h" + +class SQLFactory : public core::ObjectFactory { + public: + SQLFactory() { + + } + + /** + * Gets the name of the object. + * @return class name of processor + */ + virtual std::string getName() override { +return "SQLFactory"; + } + + virtual std::string getClassName() override{ +return "SQLFactory"; + } + /** + * Gets the class name for the object + * @return class name for the processor. + */ + virtual std::vector getClassNames() override{ +std::vector class_names = {"ExecuteSQL", "PutSQL", "QueryDatabaseTable", "ODBCService"}; +return class_names; + } + + template + static std::unique_ptr getObjectFactory() { +return std::unique_ptr(new core::DefautObjectFactory()); + } + + virtual std::unique_ptr assign(const std::string _name) override { +if (utils::StringUtils::equalsIgnoreCase(class_name, "ExecuteSQL")) { + return getObjectFactory(); +} +if (utils::StringUtils::equalsIgnoreCase(class_name, "PutSQL")) { + return getObjectFactory(); +} +if (utils::StringUtils::equalsIgnoreCase(class_name, "QueryDatabaseTable")) { + return getObjectFactory(); +} +if (utils::StringUtils::equalsIgnoreCase(class_name, "ODBCService")) { + return getObjectFactory(); +} + +return nullptr; + } + + static bool added; Review comment: Fixed. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
[GitHub] [nifi-minifi-cpp] am-c-p-p commented on a change in pull request #732: MINIFICPP-1013
am-c-p-p commented on a change in pull request #732: MINIFICPP-1013 URL: https://github.com/apache/nifi-minifi-cpp/pull/732#discussion_r378095180 ## File path: extensions/sql/processors/QueryDatabaseTable.cpp ## @@ -0,0 +1,475 @@ +/** + * @file QueryDatabaseTable.cpp + * PutSQL class declaration + * + * 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. + */ + +#include "QueryDatabaseTable.h" + +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include +#include + +#include + +#include "io/DataStream.h" +#include "core/ProcessContext.h" +#include "core/ProcessSession.h" +#include "Exception.h" +#include "utils/OsUtils.h" +#include "data/DatabaseConnectors.h" +#include "data/JSONSQLWriter.h" +#include "data/SQLRowsetProcessor.h" +#include "data/WriteCallback.h" +#include "data/MaxCollector.h" +#include "data/Utils.h" +#include "utils/file/FileUtils.h" + +namespace org { +namespace apache { +namespace nifi { +namespace minifi { +namespace processors { + +const std::string QueryDatabaseTable::ProcessorName("QueryDatabaseTable"); + +const core::Property QueryDatabaseTable::s_tableName( + core::PropertyBuilder::createProperty("Table Name")->isRequired(true)->withDescription("The name of the database table to be queried.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_columnNames( + core::PropertyBuilder::createProperty("Columns to Return")->isRequired(false)->withDescription( +"A comma-separated list of column names to be used in the query. If your database requires special treatment of the names (quoting, e.g.), each name should include such treatment. " +"If no column names are supplied, all columns in the specified table will be returned. " +"NOTE: It is important to use consistent column names for a given table for incremental fetch to work properly.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_maxValueColumnNames( + core::PropertyBuilder::createProperty("Maximum-value Columns")->isRequired(false)->withDescription( +"A comma-separated list of column names. The processor will keep track of the maximum value for each column that has been returned since the processor started running. " +"Using multiple columns implies an order to the column list, and each column's values are expected to increase more slowly than the previous columns' values. " +"Thus, using multiple columns implies a hierarchical structure of columns, which is usually used for partitioning tables. " +"This processor can be used to retrieve only those rows that have been added/updated since the last retrieval. " +"Note that some ODBC types such as bit/boolean are not conducive to maintaining maximum value, so columns of these types should not be listed in this property, and will result in error(s) during processing. " +"If no columns are provided, all rows from the table will be considered, which could have a performance impact. " +"NOTE: It is important to use consistent max-value column names for a given table for incremental fetch to work properly. " +"NOTE: Because of a limitation of database access library 'soci', which doesn't support milliseconds in it's 'dt_date', " +"there is a possibility that flowfiles might have duplicated records, if a max-value column with 'dt_date' type has value with milliseconds.")-> +supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_whereClause( + core::PropertyBuilder::createProperty("db-fetch-where-clause")->isRequired(false)->withDescription( +"A custom clause to be added in the WHERE condition when building SQL queries.")->supportsExpressionLanguage(true)->build()); + +const core::Property QueryDatabaseTable::s_sqlQuery( + core::PropertyBuilder::createProperty("db-fetch-sql-query")->isRequired(false)->withDescription( +"A custom SQL query used to retrieve data. Instead of building a SQL query from other properties, this query will be wrapped as a sub-query. " +"Query must have no ORDER BY statement.")->supportsExpressionLanguage(true)->build()); + +const