# HG changeset patch # User František Kučera # Date 1576328436 -3600 # Node ID 884ece10575d338d6353117698d26a601fc4894c # Parent d3bfbce022aa1d762414acea777e61e32b80bcac add --type-cast to allow explicit specification of type for given output attributes diff -r d3bfbce022aa -r 884ece10575d bash-completion.sh --- a/bash-completion.sh Fri Dec 13 22:19:41 2019 +0100 +++ b/bash-completion.sh Sat Dec 14 14:00:36 2019 +0100 @@ -21,9 +21,16 @@ w1=${COMP_WORDS[COMP_CWORD-1]} w2=${COMP_WORDS[COMP_CWORD-2]} + DATA_TYPE=( + "string" + "integer" + "boolean" + ) if [[ "$w1" == "--relation" && "x$w0" == "x" ]]; then COMPREPLY=("''") elif [[ "$w2" == "--relation" && "x$w0" == "x" ]]; then COMPREPLY=('"SELECT * FROM "') + elif [[ "$w1" == "--type-cast" && "x$w0" == "x" ]]; then COMPREPLY=("''") + elif [[ "$w2" == "--type-cast" ]]; then COMPREPLY=($(compgen -W "${DATA_TYPE[*]}" -- "$w0")) elif [[ "$w1" == "--parameter" && "x$w0" == "x" ]]; then COMPREPLY=("''") elif [[ "$w1" == "--copy" && "x$w0" == "x" ]]; then COMPREPLY=("'.+'") elif [[ "$w1" == "--copy-renamed" && "x$w0" == "x" ]]; then COMPREPLY=("'.+'") @@ -33,6 +40,7 @@ else OPTIONS=( "--relation" + "--type-cast" "--parameter" "--copy" "--copy-renamed" diff -r d3bfbce022aa -r 884ece10575d src/CLIParser.h --- a/src/CLIParser.h Fri Dec 13 22:19:41 2019 +0100 +++ b/src/CLIParser.h Sat Dec 14 14:00:36 2019 +0100 @@ -48,6 +48,7 @@ public: static const string_t OPTION_RELATION; + static const string_t OPTION_TYPE_CAST; static const string_t OPTION_PARAMETER; static const string_t OPTION_COPY; static const string_t OPTION_COPY_RENAMED; @@ -65,6 +66,11 @@ addQuery(c, currentQuery); // previous relation currentQuery.relation = readNext(arguments, i); currentQuery.sql = readNext(arguments, i); + } else if (option == OPTION_TYPE_CAST) { + TypeCast typeCast; + typeCast.name = readNext(arguments, i); + typeCast.type = readNext(arguments, i); + currentQuery.typeCasts.push_back(typeCast); } else if (option == OPTION_PARAMETER) { Parameter parameter; parameter.value = readNext(arguments, i); @@ -97,6 +103,7 @@ // TODO: --type string/integer/boolean (default is string) 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_COPY = L"--copy"; const string_t CLIParser::OPTION_COPY_RENAMED = L"--copy-renamed"; diff -r d3bfbce022aa -r 884ece10575d src/Configuration.h --- a/src/Configuration.h Fri Dec 13 22:19:41 2019 +0100 +++ b/src/Configuration.h Sat Dec 14 14:00:36 2019 +0100 @@ -30,6 +30,13 @@ // TODO: relpipe::writer::TypeId type; }; +class TypeCast { +public: + + relpipe::writer::string_t name; + relpipe::writer::string_t type; +}; + class Statement { public: @@ -46,6 +53,14 @@ * might be also DML or DDL */ relpipe::writer::string_t sql; + /** + * Explicitly defined types of particular attributes. + * SQLite uses dynamic typing, so it is often impossible to obtain the proper types from the result set. + */ + std::vector typeCasts; + /** + * Query parameters + */ std::vector parameters; }; diff -r d3bfbce022aa -r 884ece10575d src/SqlHandler.h --- a/src/SqlHandler.h Fri Dec 13 22:19:41 2019 +0100 +++ b/src/SqlHandler.h Sat Dec 14 14:00:36 2019 +0100 @@ -103,21 +103,25 @@ else throw SqlException(L"Unable to get SQLite column name."); } - relpipe::writer::TypeId getColumType(int columnIndex) { + relpipe::writer::TypeId getColumType(int columnIndex, relpipe::writer::TypeId defaultType = relpipe::writer::TypeId::STRING) { const char* type = sqlite3_column_decltype(stmt, columnIndex); // TODO: sqlite3_column_decltype returns value only for columns of existing tables, not for dynamic expressions – SQLite uses dynamic types + // maybe we could write a function/module that returns result set metadata for given query (before executing it) + // or use at least explicit casts in SQL and modify sqlite3_column_decltype() function or add some new one to return such casted type + // // fprintf(stderr, "%d → %s\n", columnIndex, type); // SELECT typeof(1+1); == "integer" // https://www.sqlite.org/c3ref/column_decltype.html – sqlite3_column_decltype // https://www.sqlite.org/c3ref/column_blob.html – sqlite3_column_type // https://www.sqlite.org/datatype3.html – Datatypes In SQLite Version 3 // https://dba.stackexchange.com/questions/203220/sqlite-what-is-the-use-of-specifying-data-types + // https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg118093.html if (type == nullptr) return relpipe::writer::TypeId::STRING; else if (strcmp(type, "integer") == 0) return relpipe::writer::TypeId::INTEGER; else if (strcmp(type, "text") == 0) return relpipe::writer::TypeId::STRING; - else return relpipe::writer::TypeId::STRING; + else return defaultType; // TODO: support also other data types } @@ -202,8 +206,13 @@ } } + relpipe::writer::TypeId findType(string_t columnName, int columnIndex, const Statement& statement, std::shared_ptr preparedStatement) { + for (TypeCast typeCast : statement.typeCasts) if (typeCast.name == columnName) return relationalWriter->toTypeId(typeCast.type); + return preparedStatement->getColumType(columnIndex); + } + void processStatement(const Statement& statement) { - std::unique_ptr prepared(connection->prepareStatement(convertor.to_bytes(statement.sql).c_str())); + std::shared_ptr prepared(connection->prepareStatement(convertor.to_bytes(statement.sql).c_str())); int columnCount = prepared->getColumnCount(); int parameterCount = statement.parameters.size(); @@ -212,7 +221,10 @@ } std::vector metadata; - for (int i = 0; i < columnCount; i++) metadata.push_back({convertor.from_bytes(prepared->getColumName(i).c_str()), prepared->getColumType(i)}); + for (int i = 0; i < columnCount; i++) { + string_t columnName = convertor.from_bytes(prepared->getColumName(i).c_str()); + metadata.push_back({columnName, findType(columnName, i, statement, prepared)}); + } relationalWriter->startRelation(statement.relation, metadata, true); while (prepared->next()) {