# HG changeset patch # User František Kučera # Date 1661702593 -7200 # Node ID a1775ba6d0569d5ddfa20d8f07147aa71749f6ae # Parent a4907b207f0c464120daa38efa2d92ca60bd06a1 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) diff -r a4907b207f0c -r a1775ba6d056 bash-completion.sh --- 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" diff -r a4907b207f0c -r a1775ba6d056 src/CLIParser.h --- 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"; diff -r a4907b207f0c -r a1775ba6d056 src/Configuration.h --- 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 statements; diff -r a4907b207f0c -r a1775ba6d056 src/SqlHandler.h --- 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& 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 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 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",";