optional UNION ALL: CLI option '--on-duplicate-relation' with values 'fail' and 'insert' (later more modes)
examples, shortcuts:
relpipe-tr-unionall() { relpipe-tr-sql --copy '.+' --on-duplicate-relation insert; } # does UNION ALL for all tables with same name (including already existing ones – when non-empty database is used)
relpipe-tr-unionall() { relpipe-tr-serialize | relpipe-tr-deserialize; } # does UNION ALL only for tables with same name immediately following each other (interleaved duplicates will stay unaffected)
--- a/bash-completion.sh Sat Apr 23 18:49:25 2022 +0200
+++ b/bash-completion.sh Sun Aug 28 18:03:13 2022 +0200
@@ -38,6 +38,16 @@
"false"
)
+ ON_DUPLICATE_RELATION=(
+ "fail"
+ "insert"
+ # "extend"
+ # "drop"
+ # "skip"
+ # "rename-new"
+ # "rename-old"
+ )
+
DATA_SOURCE_STRING=(
"Driver=SQLite3;Database=file::memory:"
"Driver=SQLite3;Database=file:temp-relpipe.sqlite"
@@ -50,6 +60,7 @@
elif [[ "$w2" == "--type-cast" ]]; then COMPREPLY=($(compgen -W "${DATA_TYPE[*]}" -- "$w0"))
elif [[ "$w1" == "--list-data-sources" ]]; then COMPREPLY=($(compgen -W "${BOOLEAN_VALUES[*]}" -- "$w0"))
elif [[ "$w1" == "--parameter" && "x$w0" == "x" ]]; then COMPREPLY=("''")
+ elif [[ "$w1" == "--on-duplicate-relation" ]]; then COMPREPLY=($(compgen -W "${ON_DUPLICATE_RELATION[*]}" -- "$w0"))
elif [[ "$w1" == "--copy" && "x$w0" == "x" ]]; then COMPREPLY=("'.+'")
elif [[ "$w1" == "--copy-renamed" && "x$w0" == "x" ]]; then COMPREPLY=("'.+'")
elif [[ "$w2" == "--copy-renamed" && "x$w0" == "x" ]]; then COMPREPLY=("'copy_of_\$0'")
@@ -60,6 +71,7 @@
"--relation"
"--type-cast"
"--parameter"
+ "--on-duplicate-relation"
"--copy"
"--copy-renamed"
"--list-data-sources"
--- a/src/CLIParser.h Sat Apr 23 18:49:25 2022 +0200
+++ b/src/CLIParser.h Sun Aug 28 18:03:13 2022 +0200
@@ -46,6 +46,12 @@
else if (value == L"false") return false;
else throw relpipe::cli::RelpipeCLIException(L"Unable to parse boolean value: " + value + L" (expecting true or false)", relpipe::cli::CLI::EXIT_CODE_BAD_CLI_ARGUMENTS);
}
+
+ OnDuplicateRelation parseOnDuplicateRelation(const string_t& value) {
+ if (value == L"insert") return OnDuplicateRelation::Insert;
+ else if (value == L"fail") return OnDuplicateRelation::Fail;
+ else throw relpipe::cli::RelpipeCLIException(L"Unable to parse OnDuplicateRelation value: " + value + L" (expecting fail or insert)", relpipe::cli::CLI::EXIT_CODE_BAD_CLI_ARGUMENTS);
+ }
void addQuery(Configuration& c, Statement& currentQuery) {
if (currentQuery.sql.size()) {
@@ -59,6 +65,7 @@
static const string_t OPTION_RELATION;
static const string_t OPTION_TYPE_CAST;
static const string_t OPTION_PARAMETER;
+ static const string_t OPTION_ON_DUPLICATE_RELATION;
static const string_t OPTION_COPY;
static const string_t OPTION_COPY_RENAMED;
static const string_t OPTION_DATA_SOURCE_NAME;
@@ -85,6 +92,8 @@
Parameter parameter;
parameter.value = readNext(arguments, i);
currentQuery.parameters.push_back(parameter);
+ } else if (option == OPTION_ON_DUPLICATE_RELATION) {
+ c.onDuplicateRelation = parseOnDuplicateRelation(readNext(arguments, i));
} else if (option == OPTION_COPY) {
c.copyRelations.push_back({readNext(arguments, i), L"", false});
} else if (option == OPTION_COPY_RENAMED) {
@@ -115,6 +124,7 @@
const string_t CLIParser::OPTION_RELATION = L"--relation";
const string_t CLIParser::OPTION_TYPE_CAST = L"--type-cast";
const string_t CLIParser::OPTION_PARAMETER = L"--parameter";
+const string_t CLIParser::OPTION_ON_DUPLICATE_RELATION = L"--on-duplicate-relation";
const string_t CLIParser::OPTION_COPY = L"--copy";
const string_t CLIParser::OPTION_COPY_RENAMED = L"--copy-renamed";
const string_t CLIParser::OPTION_LIST_DATA_SOURCES = L"--list-data-sources";
--- a/src/Configuration.h Sat Apr 23 18:49:25 2022 +0200
+++ b/src/Configuration.h Sun Aug 28 18:03:13 2022 +0200
@@ -83,6 +83,16 @@
const relpipe::writer::boolean_t replace;
};
+enum class OnDuplicateRelation {
+ Fail,
+ Insert,
+ // Extend,
+ // Drop,
+ // Skip,
+ // RenameNew,
+ // RenameOld,
+};
+
class Configuration {
public:
@@ -90,6 +100,8 @@
relpipe::writer::string_t dataSourceName;
relpipe::writer::string_t dataSourceString;
+
+ OnDuplicateRelation onDuplicateRelation = OnDuplicateRelation::Fail;
std::vector<Statement> statements;
--- a/src/SqlHandler.h Sat Apr 23 18:49:25 2022 +0200
+++ b/src/SqlHandler.h Sun Aug 28 18:03:13 2022 +0200
@@ -160,6 +160,11 @@
else return L"text";
}
+ bool isInsertable(const relpipe::writer::string_t& tableName) {
+ for (Connection::TablePrivilege tp : connection->getTablePrivileges()) if (tp.name == tableName && tp.privilege == L"INSERT") return true;
+ return false;
+ }
+
void writeIdentifier(std::wstringstream& output, relpipe::writer::string_t identifier) {
output << L'"';
for (auto & ch : identifier) {
@@ -169,6 +174,23 @@
output << L'"';
}
+ void createTable(const string_t& name, const std::vector<AttributeMetadata>& attributes) {
+ std::wstringstream sql;
+ sql << L"CREATE TABLE ";
+ writeIdentifier(sql, name);
+ sql << L" (\n";
+ for (int i = 0; i < attributes.size(); i++) {
+ sql << L"\t";
+ writeIdentifier(sql, attributes[i].getAttributeName());
+ sql << L" " << toSQLType(attributes[i].getTypeId());
+ if (i < attributes.size() - 1) sql << L",\n";
+ }
+ sql << L"\n)";
+
+ std::unique_ptr<PreparedStatement> createTable(connection->prepareStatement(sql.str()));
+ createTable->executeUpdate();
+ }
+
Connection* getConnection() {
if (configuration.dataSourceName.size()) return driverManager->getConnectionByDSN(configuration.dataSourceName);
else if (configuration.dataSourceString.size()) return driverManager->getConnectionByString(configuration.dataSourceString);
@@ -191,27 +213,25 @@
currentReaderMetadata = attributes;
// CREATE TABLE:
- std::wstringstream sql;
- // TODO: if already exist just append new columns
- sql << L"CREATE TABLE ";
- writeIdentifier(sql, name);
- sql << L" (\n";
- for (int i = 0; i < attributes.size(); i++) {
- sql << L"\t";
- writeIdentifier(sql, attributes[i].getAttributeName());
- sql << L" " << toSQLType(attributes[i].getTypeId());
- if (i < attributes.size() - 1) sql << L",\n";
+ if (configuration.onDuplicateRelation == OnDuplicateRelation::Fail) {
+ createTable(name, attributes); // duplicate will cause exception
+ } else if (configuration.onDuplicateRelation == OnDuplicateRelation::Insert) {
+ if (isInsertable(name)); // nothing needs to be created; we will just append new records to the existing table
+ else createTable(name, attributes);
+ } else {
+ throw SqlException(L"Unsupported OnDuplicateRelation mode: " + std::to_wstring((int) configuration.onDuplicateRelation));
}
- sql << L"\n)";
-
- std::unique_ptr<PreparedStatement> createTable(connection->prepareStatement(sql.str()));
- createTable->executeUpdate();
// prepare INSERT:
- sql = wstringstream();
+ std::wstringstream sql;
sql << L"INSERT INTO ";
writeIdentifier(sql, name);
- sql << L" VALUES (";
+ sql << L" (";
+ for (int i = 0; i < attributes.size(); i++) {
+ writeIdentifier(sql, attributes[i].getAttributeName());
+ if (i < attributes.size() - 1) sql << L",";
+ }
+ sql << L") VALUES (";
for (int i = 0; i < attributes.size(); i++) {
sql << L"?";
if (i < attributes.size() - 1) sql << L",";