--- a/nbproject/configurations.xml Mon May 25 21:11:17 2020 +0200
+++ b/nbproject/configurations.xml Sun May 31 16:56:07 2020 +0200
@@ -47,6 +47,8 @@
<in>DriverManager.h</in>
<in>OdbcCommon.h</in>
<in>PreparedStatement.cpp</in>
+ <in>ResultSet.cpp</in>
+ <in>ResultSet.h</in>
<in>SqlException.cpp</in>
<in>SqlException.h</in>
<in>relpipe-tr-sql.cpp</in>
@@ -88,6 +90,7 @@
<incDir>
<pElem>../relpipe-lib-reader.cpp/include</pElem>
<pElem>../relpipe-lib-writer.cpp/include</pElem>
+ <pElem>../relpipe-lib-common.cpp/include</pElem>
<pElem>../relpipe-lib-cli.cpp/include</pElem>
<pElem>build/Debug/src</pElem>
</incDir>
@@ -99,21 +102,25 @@
<preBuildFirst>true</preBuildFirst>
</preBuild>
</makefileType>
- <item path="src/Connection.cpp" ex="false" tool="1" flavor2="0">
+ <item path="src/Connection.cpp" ex="false" tool="1" flavor2="11">
<ccTool flags="0">
</ccTool>
</item>
- <item path="src/DriverManager.cpp" ex="false" tool="1" flavor2="0">
+ <item path="src/DriverManager.cpp" ex="false" tool="1" flavor2="11">
<ccTool flags="0">
</ccTool>
</item>
- <item path="src/OdbcCommon.h" ex="false" tool="3" flavor2="0">
- </item>
- <item path="src/PreparedStatement.cpp" ex="false" tool="1" flavor2="0">
+ <item path="src/PreparedStatement.cpp" ex="false" tool="1" flavor2="11">
<ccTool flags="0">
</ccTool>
</item>
- <item path="src/SqlException.cpp" ex="false" tool="1" flavor2="0">
+ <item path="src/ResultSet.cpp" ex="false" tool="1" flavor2="11">
+ <ccTool flags="0">
+ </ccTool>
+ </item>
+ <item path="src/SqlException.cpp" ex="false" tool="1" flavor2="11">
+ <ccTool flags="0">
+ </ccTool>
</item>
<item path="src/relpipe-tr-sql.cpp" ex="false" tool="1" flavor2="11">
<ccTool flags="0">
@@ -161,6 +168,10 @@
</item>
<item path="src/OdbcCommon.h" ex="false" tool="3" flavor2="0">
</item>
+ <item path="src/ResultSet.cpp" ex="false" tool="1" flavor2="0">
+ </item>
+ <item path="src/ResultSet.h" ex="false" tool="3" flavor2="0">
+ </item>
<item path="src/SqlException.cpp" ex="false" tool="1" flavor2="0">
</item>
<item path="src/SqlException.h" ex="false" tool="3" flavor2="0">
--- a/src/CMakeLists.txt Mon May 25 21:11:17 2020 +0200
+++ b/src/CMakeLists.txt Sun May 31 16:56:07 2020 +0200
@@ -21,7 +21,7 @@
# Relpipe libraries:
INCLUDE(FindPkgConfig)
-pkg_check_modules (RELPIPE_LIBS relpipe-lib-reader.cpp relpipe-lib-writer.cpp relpipe-lib-cli.cpp sqlite3)
+pkg_check_modules (RELPIPE_LIBS relpipe-lib-reader.cpp relpipe-lib-writer.cpp relpipe-lib-cli.cpp)
include_directories(${RELPIPE_LIBS_INCLUDE_DIRS} ${ODBC_INCLUDE_DIRS})
link_directories(${RELPIPE_LIBS_LIBRARY_DIRS})
@@ -33,6 +33,7 @@
# Executable output:
add_executable(
${EXECUTABLE_FILE}
+ ResultSet.cpp
PreparedStatement.cpp
Connection.cpp
DriverManager.cpp
--- a/src/Connection.cpp Mon May 25 21:11:17 2020 +0200
+++ b/src/Connection.cpp Sun May 31 16:56:07 2020 +0200
@@ -15,58 +15,114 @@
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
-#include <sqlite3.h>
+#include <memory>
+#include <iostream>
+
+#include <sql.h>
+#include <sqlext.h>
#include "Connection.h"
+#include "OdbcCommon.h"
namespace relpipe {
namespace tr {
namespace sql {
-void Connection::begin() {
- sqlite3_exec((sqlite3*) db, "BEGIN", nullptr, nullptr, nullptr);
-}
-
-Connection::Connection(const char* filename) {
- int result = sqlite3_open(filename, (sqlite3**) & db);
- if (result != SQLITE_OK) {
- sqlite3_close((sqlite3*) db);
- throw SqlException(L"Unable to open SQLite database.");
- }
- sqlite3_enable_load_extension((sqlite3*) db, true);
- begin();
+Connection::Connection(void* db) : connection(db) {
+ // TODO: transaction control
}
Connection::~Connection() {
- sqlite3_close((sqlite3*) db);
+ SQLRETURN result = SQLDisconnect(connection);
+ // FIXME: nevyhazovat výjimky z destruktorů
+ if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to disconnect: " + std::to_wstring(result));
+ OdbcCommon::freeHandle(SQL_HANDLE_DBC, connection);
}
-PreparedStatement* Connection::prepareStatement(const char* sql) {
- const char* remaining;
- sqlite3_stmt *stmt;
- int result = sqlite3_prepare((sqlite3*) db, sql, -1, &stmt, &remaining);
- if (result == SQLITE_OK) return new PreparedStatement(stmt);
- else throw SqlException(L"Unable to prepare SQLite statement.");
+PreparedStatement* Connection::prepareStatement(relpipe::reader::string_t sql) {
+ SQLHSTMT statement = OdbcCommon::allocateHandle(SQL_HANDLE_STMT, connection);
+ SQLRETURN result = SQLPrepare(statement, (SQLCHAR*) convertor.to_bytes(sql).c_str(), SQL_NTS);
+ if (OdbcCommon::isNotSuccessful(result)) {
+ OdbcCommon::freeHandle(SQL_HANDLE_STMT, statement);
+ throw SqlException(L"Unable to prepare statement", result, SQL_HANDLE_DBC, connection); // TODO: SQL_HANDLE_STMT?
+ }
+ return new PreparedStatement(statement);
}
bool Connection::getAutoCommit() {
- return sqlite3_get_autocommit((sqlite3*) db);
+ // TODO: transaction control
+ return false;
}
void Connection::setAutoCommit(bool autoCommit) {
- bool autoCommitOld = getAutoCommit();
- if (autoCommit && !autoCommitOld) commit();
- else if (!autoCommit && autoCommitOld) begin();
+ // TODO: transaction control
}
void Connection::commit() {
- sqlite3_exec((sqlite3*) db, "COMMIT", nullptr, nullptr, nullptr);
+ SQLRETURN result = SQLEndTran(SQL_HANDLE_DBC, connection, SQL_COMMIT);
+ if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to COMMIT: " + std::to_wstring(result));
}
void Connection::rollback() {
- sqlite3_exec((sqlite3*) db, "ROLLBACK", nullptr, nullptr, nullptr);
+ SQLRETURN result = SQLEndTran(SQL_HANDLE_DBC, connection, SQL_ROLLBACK);
+ if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to ROLLBACK: " + std::to_wstring(result));
+}
+
+std::vector<Connection::TableMetaData> Connection::getTables() {
+ std::vector<TableMetaData> tables;
+ SQLHSTMT statementHandle = OdbcCommon::allocateHandle(SQL_HANDLE_STMT, connection);
+ SQLRETURN result = SQLTables(statementHandle, nullptr, 0, nullptr, 0, nullptr, 0, nullptr, 0);
+ if (OdbcCommon::isNotSuccessful(result)) {
+ OdbcCommon::freeHandle(SQL_HANDLE_STMT, statementHandle);
+ throw SqlException(L"Unable get tables (prepare)", result, SQL_HANDLE_DBC, connection); // TODO: SQL_HANDLE_STMT?
+ }
+ ResultSet resultSet(statementHandle);
+ while (resultSet.next()) {
+ TableMetaData tm;
+ tm.catalog = resultSet.getString(1); // FIXME: column name: table_cat
+ tm.schema = resultSet.getString(2); // FIXME: column name: table_schem
+ tm.name = resultSet.getString(3); // FIXME: column name: table_name
+ tm.type = resultSet.getString(4); // FIXME: column name: table_type
+ tables.emplace_back(tm);
+ }
+
+ return tables;
}
+std::vector<Connection::TablePrivilege> Connection::getTablePrivileges() {
+ std::vector<TablePrivilege> tables;
+ SQLHSTMT statementHandle = OdbcCommon::allocateHandle(SQL_HANDLE_STMT, connection);
+ SQLRETURN result = SQLTablePrivileges(statementHandle, nullptr, 0, nullptr, 0, nullptr, 0);
+ if (OdbcCommon::isNotSuccessful(result)) {
+ OdbcCommon::freeHandle(SQL_HANDLE_STMT, statementHandle);
+ throw SqlException(L"Unable get tables (prepare)", result, SQL_HANDLE_DBC, connection); // TODO: SQL_HANDLE_STMT?
+ }
+ ResultSet resultSet(statementHandle);
+ while (resultSet.next()) {
+ TablePrivilege tp;
+ tp.catalog = resultSet.getString(1); // FIXME: column name: table_cat
+ tp.schema = resultSet.getString(2); // FIXME: column name: table_schem
+ tp.name = resultSet.getString(3); // FIXME: column name: table_name
+ tp.grantor = resultSet.getString(4); // FIXME: column name: grantor
+ tp.grantee = resultSet.getString(5); // FIXME: column name: grantee
+ tp.privilege = resultSet.getString(6); // FIXME: column name: privilege
+ tp.isGrantable = resultSet.getString(7) == L"YES"; // FIXME: column name: is_grantable
+ tables.emplace_back(tp);
+ }
+
+ return tables;
+}
+
+relpipe::reader::string_t Connection::getUserName() {
+ std::vector<char> buffer(100);
+ SQLSMALLINT stringLength;
+ SQLRETURN result = SQLGetInfo(connection, SQL_USER_NAME, buffer.data(), buffer.size(), &stringLength);
+ if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to get user name: " + std::to_wstring(result));
+ if (stringLength >= buffer.size()) throw SqlException(L"Unable to get user name: too long" + std::to_wstring(stringLength));
+ return convertor.from_bytes(buffer.data(), buffer.data() + stringLength);
+}
+
+
}
}
}
--- a/src/Connection.h Mon May 25 21:11:17 2020 +0200
+++ b/src/Connection.h Sun May 31 16:56:07 2020 +0200
@@ -16,6 +16,11 @@
*/
#pragma once
+#include <codecvt>
+#include <locale>
+
+#include <relpipe/reader/typedefs.h>
+
#include "SqlException.h"
#include "PreparedStatement.h"
@@ -25,16 +30,37 @@
class Connection {
private:
- void* db;
- void begin();
+ void* connection;
+ std::wstring_convert<std::codecvt_utf8<wchar_t>> convertor; // TODO: support also other encodings
public:
- Connection(const char* filename); // TODO: add DriverManager class + support connecting using a DSN or a connectString
+ Connection(void* connection);
virtual ~Connection();
- PreparedStatement* prepareStatement(const char* sql);
+ PreparedStatement* prepareStatement(relpipe::reader::string_t sql);
bool getAutoCommit();
void setAutoCommit(bool autoCommit);
void commit();
void rollback();
+
+ class TableMetaData {
+ public:
+ relpipe::reader::string_t catalog;
+ relpipe::reader::string_t schema;
+ relpipe::reader::string_t name;
+ relpipe::reader::string_t type;
+ };
+ std::vector<TableMetaData> getTables();
+
+ class TablePrivilege : public TableMetaData {
+ public:
+ relpipe::reader::string_t grantor;
+ relpipe::reader::string_t grantee;
+ relpipe::reader::string_t privilege;
+ relpipe::reader::boolean_t isGrantable;
+
+ };
+ std::vector<TablePrivilege> getTablePrivileges();
+
+ relpipe::reader::string_t getUserName();
};
}
--- a/src/DriverManager.cpp Mon May 25 21:11:17 2020 +0200
+++ b/src/DriverManager.cpp Sun May 31 16:56:07 2020 +0200
@@ -16,6 +16,7 @@
*/
#include <cstring>
+#include <iostream>
#include <sql.h>
#include <sqlext.h>
@@ -56,7 +57,41 @@
return list;
}
+Connection* DriverManager::getConnectionByDSN(relpipe::reader::string_t dataSourceName, relpipe::reader::string_t userName, relpipe::reader::string_t password) {
+ SQLHDBC connection = OdbcCommon::allocateHandle(SQL_HANDLE_DBC, env);
+ std::string dataSourceNameBytes = convertor.to_bytes(dataSourceName);
+ std::string userNameBytes = convertor.to_bytes(userName);
+ std::string passwordBytes = convertor.to_bytes(password);
+ SQLRETURN result = SQLConnect(connection,
+ (SQLCHAR*) dataSourceNameBytes.c_str(), SQL_NTS,
+ (SQLCHAR*) userNameBytes.c_str(), SQL_NTS,
+ (SQLCHAR*) password.c_str(), SQL_NTS);
+ if (OdbcCommon::isNotSuccessful(result)) {
+ OdbcCommon::freeHandle(SQL_HANDLE_DBC, connection);
+ throw SqlException(L"Unable to connect to " + dataSourceName, result, SQL_HANDLE_ENV, env);
+ }
+ return new Connection(connection);
+}
+
+Connection* DriverManager::getConnectionByString(relpipe::reader::string_t connectionString) {
+ SQLHDBC connection = OdbcCommon::allocateHandle(SQL_HANDLE_DBC, env);
+ char completeConnectionString[SQL_MAX_OPTION_STRING_LENGTH];
+ memset(completeConnectionString, 0, sizeof (completeConnectionString));
+ SQLSMALLINT completeConnectionStringLength = -1;
+ SQLRETURN result = SQLDriverConnect(connection, nullptr,
+ (SQLCHAR*) convertor.to_bytes(connectionString).c_str(), SQL_NTS,
+ (SQLCHAR*) completeConnectionString, sizeof (completeConnectionString), &completeConnectionStringLength,
+ SQL_DRIVER_NOPROMPT);
+ if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to connect to " + connectionString, result, SQL_HANDLE_ENV, env);
+ std::wcerr << "ODBC connected to: " << convertor.from_bytes(completeConnectionString) << std::endl; // FIXME: remove
+ return new Connection(connection);
+}
+
+relpipe::reader::string_t DriverManager::buildDSN(const std::map<relpipe::reader::string_t, relpipe::reader::string_t>& parameters) {
+ throw SqlException(L"not yet implemented: buildDSN()"); // FIXME: implement buildDSN()
+}
+
}
}
-}
\ No newline at end of file
+}
--- a/src/DriverManager.h Mon May 25 21:11:17 2020 +0200
+++ b/src/DriverManager.h Sun May 31 16:56:07 2020 +0200
@@ -17,17 +17,26 @@
#pragma once
#include <vector>
+#include <map>
#include <codecvt>
#include <locale>
#include <relpipe/reader/typedefs.h>
#include "SqlException.h"
+#include "Connection.h"
namespace relpipe {
namespace tr {
namespace sql {
+/**
+ * Common principles in this SQL layer:
+ * - Numbering starts with 1, not 0. First parameter or column = 1.
+ * - If you got a pointer returned from a method or function, you are then responsible for given object.
+ * Wrapping it immediatelly in a smart pointer is a convenient way to manage its lifecycle.
+ * Statements should be destroyed before the Connection. And Connection should be destroyed before DriverManager.
+ */
class DriverManager {
private:
void* env;
@@ -44,6 +53,9 @@
DriverManager();
virtual ~DriverManager();
std::vector<DataSource> getDataSources();
+ Connection* getConnectionByDSN(relpipe::reader::string_t dataSourceName, relpipe::reader::string_t userName = L"", relpipe::reader::string_t password = L"");
+ Connection* getConnectionByString(relpipe::reader::string_t connectionString);
+ relpipe::reader::string_t buildDSN(const std::map<relpipe::reader::string_t, relpipe::reader::string_t>& parameters);
};
}
--- a/src/PreparedStatement.cpp Mon May 25 21:11:17 2020 +0200
+++ b/src/PreparedStatement.cpp Sun May 31 16:56:07 2020 +0200
@@ -16,89 +16,77 @@
*/
#include <cstring>
+#include <iostream>
-#include <sqlite3.h>
+#include <sql.h>
+#include <sqlext.h>
#include "PreparedStatement.h"
+#include "ResultSet.h"
+#include "OdbcCommon.h"
namespace relpipe {
namespace tr {
namespace sql {
-PreparedStatement::PreparedStatement(void* stmt) : stmt(stmt) {
+PreparedStatement::PreparedStatement(void* stmt) : statement(stmt) {
}
PreparedStatement::~PreparedStatement() {
- sqlite3_finalize((sqlite3_stmt*) stmt);
+ OdbcCommon::freeHandle(SQL_HANDLE_STMT, statement);
+}
+
+ResultSet* PreparedStatement::executeQuery() {
+ SQLRETURN result = SQLExecute(statement);
+ if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to execute (query) prepared statement", result, SQL_HANDLE_STMT, statement);
+ return new ResultSet(statement);
}
-void PreparedStatement::setBoolean(int parameterIndex, relpipe::reader::boolean_t value) {
- int result = sqlite3_bind_int((sqlite3_stmt*) stmt, parameterIndex, value);
- if (result != SQLITE_OK) throw SqlException(L"Unable to set SQLite parameter.");
-}
+long PreparedStatement::executeUpdate() {
+ SQLRETURN result;
+ SQLLEN count = 0; // returned e.g. on empty statement: ""
-void PreparedStatement::setInteger(int parameterIndex, relpipe::reader::integer_t value) {
- int result = sqlite3_bind_int64((sqlite3_stmt*) stmt, parameterIndex, value);
- if (result != SQLITE_OK) throw SqlException(L"Unable to set SQLite parameter.");
+ result = SQLExecute(statement);
+ // PostgreSQL returns SUCCESS but SQLite returns SQL_NO_DATA_FOUND:
+ if (OdbcCommon::isNotSuccessful(result) && result != SQL_NO_DATA_FOUND) throw SqlException(L"Unable to execute (update) prepared statement", result, SQL_HANDLE_STMT, statement);
+
+ result = SQLRowCount(statement, &count);
+ if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to get updated record count", result, SQL_HANDLE_STMT, statement);
+
+ return count;
}
-void PreparedStatement::setString(int parameterIndex, std::string value) {
- int result = sqlite3_bind_text((sqlite3_stmt*) stmt, parameterIndex, value.c_str(), -1, SQLITE_TRANSIENT);
- if (result != SQLITE_OK) throw SqlException(L"Unable to set SQLite parameter.");
+void PreparedStatement::setBoolean(int parameterNumber, relpipe::reader::boolean_t value) {
+ booleanParameters.emplace_back(value);
+ // TODO: review SQL_C_TINYINT
+ SQLRETURN result = SQLBindParameter(statement, parameterNumber, SQL_PARAM_INPUT, SQL_C_TINYINT, SQL_INTEGER, 0, 0, &booleanParameters.back(), 0, nullptr);
+ if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to set boolean parameter in prepared statement", result, SQL_HANDLE_STMT, statement);
}
-void PreparedStatement::setNull(int parameterIndex) {
- int result = sqlite3_bind_null((sqlite3_stmt*) stmt, parameterIndex);
- if (result != SQLITE_OK) throw SqlException(L"Unable to set SQLite parameter.");
+void PreparedStatement::setInteger(int parameterNumber, relpipe::reader::integer_t value) {
+ integerParameters.emplace_back(value);
+ SQLRETURN result = SQLBindParameter(statement, parameterNumber, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &integerParameters.back(), 0, nullptr);
+ if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to set integer parameter in prepared statement", result, SQL_HANDLE_STMT, statement);
}
-bool PreparedStatement::next() {
- int result = sqlite3_step((sqlite3_stmt*) stmt);
- if (result == SQLITE_ROW) return true;
- else if (result == SQLITE_DONE) return false;
- else throw SqlException(L"Error while iterating over SQLite result.");
+void PreparedStatement::setString(int parameterNumber, relpipe::reader::string_t value) {
+ std::string valueBytes = convertor.to_bytes(value);
+ stringParameters.emplace_back(std::make_pair(valueBytes, valueBytes.size()));
+ SQLRETURN result = SQLBindParameter(statement, parameterNumber, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0, (void *) stringParameters.back().first.c_str(), 0, &stringParameters.back().second);
+ if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to set string parameter in prepared statement", result, SQL_HANDLE_STMT, statement);
+}
+
+void PreparedStatement::setNull(int parameterNumber) {
+ throw SqlException(L"Use ODBC: setNull()");
}
void PreparedStatement::reset() {
- int result = sqlite3_reset((sqlite3_stmt*) stmt);
- if (result != SQLITE_OK) throw SqlException(L"Unable to reset SQLite prepared statement.");
-}
-
-int PreparedStatement::getColumnCount() {
- return sqlite3_column_count((sqlite3_stmt*) stmt);
-}
-
-std::string PreparedStatement::getColumName(int columnIndex) {
- const char* name = sqlite3_column_name((sqlite3_stmt*) stmt, columnIndex);
- if (name) return name;
- else throw SqlException(L"Unable to get SQLite column name.");
-}
-
-relpipe::writer::TypeId PreparedStatement::getColumType(int columnIndex, relpipe::writer::TypeId defaultType) {
- const char* type = sqlite3_column_decltype((sqlite3_stmt*) 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 defaultType;
- // TODO: support also other data types
-}
-
-std::string PreparedStatement::getString(int columnIndex) {
- const char* value = (const char*) sqlite3_column_text((sqlite3_stmt*) stmt, columnIndex);
- return value ? value : ""; // TODO: support NULL values (when supported in relpipe format)
+ // TODO: do also SQL_UNBIND if column binding is used
+ SQLRETURN result = SQLFreeStmt(statement, SQL_RESET_PARAMS);
+ if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to reset prepared statement", result, SQL_HANDLE_STMT, statement);
+ booleanParameters.clear();
+ integerParameters.clear();
+ stringParameters.clear();
}
}
--- a/src/PreparedStatement.h Mon May 25 21:11:17 2020 +0200
+++ b/src/PreparedStatement.h Sun May 31 16:56:07 2020 +0200
@@ -16,10 +16,15 @@
*/
#pragma once
+#include <list>
+#include <string>
+#include <utility>
+
#include <relpipe/reader/typedefs.h>
#include <relpipe/reader/TypeId.h>
#include <relpipe/writer/TypeId.h>
#include "SqlException.h"
+#include "ResultSet.h"
namespace relpipe {
namespace tr {
@@ -27,20 +32,21 @@
class PreparedStatement {
private:
- void* stmt;
+ void* statement;
+ std::wstring_convert<std::codecvt_utf8<wchar_t>> convertor; // TODO: support also other encodings
+ std::list<relpipe::reader::boolean_t> booleanParameters;
+ std::list<relpipe::reader::integer_t> integerParameters;
+ std::list<std::pair<std::string, long>> stringParameters;
public:
PreparedStatement(void* stmt);
virtual ~PreparedStatement();
- void setBoolean(int parameterIndex, relpipe::reader::boolean_t value);
- void setInteger(int parameterIndex, relpipe::reader::integer_t value);
- void setString(int parameterIndex, std::string value);
- void setNull(int parameterIndex);
- bool next();
+ ResultSet* executeQuery();
+ long executeUpdate();
void reset();
- int getColumnCount();
- std::string getColumName(int columnIndex);
- relpipe::writer::TypeId getColumType(int columnIndex, relpipe::writer::TypeId defaultType = relpipe::writer::TypeId::STRING);
- std::string getString(int columnIndex);
+ void setBoolean(int parameterNumber, relpipe::reader::boolean_t value);
+ void setInteger(int parameterNumber, relpipe::reader::integer_t value);
+ void setString(int parameterNumber, relpipe::reader::string_t value);
+ void setNull(int parameterNumber);
};
}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/src/ResultSet.cpp Sun May 31 16:56:07 2020 +0200
@@ -0,0 +1,114 @@
+/**
+ * Relational pipes
+ * Copyright © 2020 František Kučera (Frantovo.cz, GlobalCode.info)
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU General Public License as published by
+ * the Free Software Foundation, version 3.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program. If not, see <http://www.gnu.org/licenses/>.
+ */
+
+#include <sql.h>
+#include <sqlext.h>
+
+#include "ResultSet.h"
+#include "OdbcCommon.h"
+
+namespace relpipe {
+namespace tr {
+namespace sql {
+
+ResultSet::ResultSet(void* statement) : statement(statement) {
+
+}
+
+ResultSet::~ResultSet() {
+ // freeHandle() is called in ~PreparedStatement()
+}
+
+bool ResultSet::next() {
+ SQLRETURN result = SQLFetch(statement);
+ if (OdbcCommon::isSuccessful(result)) return true;
+ else if (result == SQL_NO_DATA) return false;
+ else throw SqlException(L"Unable to fetch next record", result, SQL_HANDLE_STMT, statement);
+}
+
+relpipe::writer::boolean_t ResultSet::getBoolean(unsigned short columnNumber, bool* isNull) {
+ throw SqlException(L"not yet implemented: getBoolean()");
+}
+
+relpipe::writer::integer_t ResultSet::getInteger(unsigned short columnNumber, bool* isNull) {
+ throw SqlException(L"not yet implemented: getInteger()");
+
+}
+
+relpipe::writer::string_t ResultSet::getString(unsigned short columnNumber, bool* isNull) {
+ SQLCHAR uselessBuffer; // just to get stringLength – ODBC does not eat nullptr
+ SQLLEN stringLength = -1;
+ SQLRETURN result = SQLGetData(statement, columnNumber, SQL_C_CHAR, &uselessBuffer, 0, &stringLength);
+ if (isNull) *isNull = stringLength == SQL_NULL_DATA;
+ if (stringLength == SQL_NULL_DATA) {
+ return L"";
+ } else if (stringLength >= 0) {
+ std::string value;
+ value.reserve(stringLength);
+ result = SQLGetData(statement, columnNumber, SQL_C_CHAR, (SQLCHAR*) value.c_str(), value.capacity() + 1, &stringLength); // trailing null byte = + 1
+ if (OdbcCommon::isSuccessful(result)) return convertor.from_bytes(value.c_str());
+ }
+ throw SqlException(L"Unable to get string value", result, SQL_HANDLE_STMT, statement);
+}
+
+ResultSet::MetaData* ResultSet::getMetaData() {
+ SQLRETURN result;
+ SQLSMALLINT columnCount;
+ std::vector<ResultSet::MetaData::ColumnDescriptor> columnDescriptors;
+
+ result = SQLNumResultCols(statement, &columnCount);
+ if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to get column count", result, SQL_HANDLE_STMT, statement);
+
+ for (SQLSMALLINT columnNumber = 1; columnNumber <= columnCount; columnNumber++) {
+ ResultSet::MetaData::ColumnDescriptor columnDescriptor;
+ std::string nameBuffer; // TODO: use rather vector<char> instead of string.c_str()?
+ nameBuffer.reserve(100); // TODO: max column name length?
+ SQLSMALLINT nameLength;
+ SQLSMALLINT dataType;
+ SQLULEN columnSize;
+ SQLSMALLINT decimalDigits;
+ SQLSMALLINT nullable;
+ result = SQLDescribeCol(statement, columnNumber, (SQLCHAR*) nameBuffer.c_str(), nameBuffer.capacity(), &nameLength, &dataType, &columnSize, &decimalDigits, &nullable);
+ if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable describe column", result, SQL_HANDLE_STMT, statement);
+ columnDescriptor.name = convertor.from_bytes(nameBuffer.c_str());
+ //columnDescriptor.type = … // FIXME: support also other types than string
+ columnDescriptors.emplace_back(columnDescriptor);
+ }
+
+ return new MetaData(columnCount, columnDescriptors);
+}
+
+ResultSet::MetaData::MetaData(unsigned short columnCount, std::vector<ColumnDescriptor> columnDescriptors) : columnCount(columnCount), columnDescriptors(columnDescriptors) {
+}
+
+ResultSet::MetaData::~MetaData() {
+}
+
+unsigned short ResultSet::MetaData::getColumnCount() {
+ return columnCount;
+}
+
+ResultSet::MetaData::ColumnDescriptor ResultSet::MetaData::describeColumn(unsigned short columnNumber) {
+ if (columnNumber >= 1 && columnNumber <= columnCount) return columnDescriptors[columnNumber - 1];
+ else throw SqlException(L"Unable to describe column " + std::to_wstring(columnNumber) + L", out of bounds, column count is " + std::to_wstring(columnCount));
+}
+
+
+
+}
+}
+}
\ No newline at end of file
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/src/ResultSet.h Sun May 31 16:56:07 2020 +0200
@@ -0,0 +1,68 @@
+/**
+ * Relational pipes
+ * Copyright © 2020 František Kučera (Frantovo.cz, GlobalCode.info)
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU General Public License as published by
+ * the Free Software Foundation, version 3.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program. If not, see <http://www.gnu.org/licenses/>.
+ */
+#pragma once
+
+#include <codecvt>
+#include <locale>
+#include <vector>
+
+#include <relpipe/writer/typedefs.h>
+#include <relpipe/writer/TypeId.h>
+
+namespace relpipe {
+namespace tr {
+namespace sql {
+
+class ResultSet {
+public:
+
+ class MetaData {
+ public:
+
+ class ColumnDescriptor {
+ public:
+ relpipe::writer::string_t name;
+ relpipe::writer::TypeId type = relpipe::writer::TypeId::STRING;
+ };
+
+ private:
+ unsigned short columnCount;
+ std::vector<ColumnDescriptor> columnDescriptors;
+
+ public:
+ MetaData(unsigned short columnCount, std::vector<ColumnDescriptor> columnDescriptors);
+ virtual ~MetaData();
+ unsigned short getColumnCount();
+ ColumnDescriptor describeColumn(unsigned short columnNumber);
+ };
+private:
+ void* statement;
+ std::wstring_convert<std::codecvt_utf8<wchar_t>> convertor; // TODO: support also other encodings
+
+public:
+ ResultSet(void* statement);
+ virtual ~ResultSet();
+ MetaData* getMetaData();
+ bool next();
+ relpipe::writer::boolean_t getBoolean(unsigned short columnNumber, bool* isNull = nullptr);
+ relpipe::writer::integer_t getInteger(unsigned short columnNumber, bool* isNull = nullptr);
+ relpipe::writer::string_t getString(unsigned short columnNumber, bool* isNull = nullptr);
+};
+
+}
+}
+}
\ No newline at end of file
--- a/src/SqlException.cpp Mon May 25 21:11:17 2020 +0200
+++ b/src/SqlException.cpp Sun May 31 16:56:07 2020 +0200
@@ -18,6 +18,7 @@
#include <cstring>
#include <codecvt>
#include <locale>
+#include <iostream>
#include <sql.h>
#include <sqlext.h>
@@ -29,9 +30,11 @@
namespace sql {
SqlException::SqlException(std::wstring message) : message(message) {
+ std::wcerr << L"XXX SqlException: " << message.c_str() << std::endl << std::flush; // FIXME: remove
}
SqlException::SqlException(std::wstring message, SQLRETURN resultCode, SQLSMALLINT handleType, SQLHANDLE handle) : message(message), resultCode(resultCode) {
+ std::wcerr << L"XXX SqlException: " << message.c_str() << std::endl << std::flush; // FIXME: remove
std::wstring_convert < std::codecvt_utf8<wchar_t>> convertor; // TODO: support also other encodings
SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1];
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
@@ -49,7 +52,8 @@
}
std::wstring SqlException::getMessage() const {
- return message;
+ if (resultCode) return message + L" SQLRETURN=" + std::to_wstring(resultCode);
+ else return message;
}
SQLRETURN SqlException::getResultCode() const {
--- a/src/SqlException.h Mon May 25 21:11:17 2020 +0200
+++ b/src/SqlException.h Sun May 31 16:56:07 2020 +0200
@@ -36,7 +36,7 @@
private:
std::wstring message;
std::vector<SqlDiagnosticsRecord> diagnostics;
- signed short int resultCode;
+ signed short int resultCode = 0;
public:
SqlException(std::wstring message);
--- a/src/SqlHandler.h Mon May 25 21:11:17 2020 +0200
+++ b/src/SqlHandler.h Sun May 31 16:56:07 2020 +0200
@@ -75,6 +75,7 @@
}
}
+ // TODO: support comments at the end of the script (after last ;)
string_t remainingSql = scanner.getAndReset();
for (wchar_t ch : remainingSql) if (ch != L' ' && ch != L'\n' && ch != L'\r' && ch != L'\t') throw SqlException(L"Unexpected EOF, missing „;“ after: „" + remainingSql + L"“");
@@ -85,56 +86,59 @@
if (input == nullptr) return;
*input >> std::ws >> std::noskipws;
for (std::wstringstream sql; readNextSqlStatement(input, &sql);) {
- std::unique_ptr<PreparedStatement> prepared(connection->prepareStatement(convertor.to_bytes(sql.str()).c_str()));
- while (prepared->next());
+ std::unique_ptr<PreparedStatement> prepared(connection->prepareStatement(sql.str()));
+ prepared->executeUpdate();
}
}
- relpipe::writer::TypeId findType(string_t columnName, int columnIndex, const Statement& statement, std::shared_ptr<PreparedStatement> preparedStatement) {
+ relpipe::writer::TypeId findType(string_t columnName, const Statement& statement, relpipe::writer::TypeId typeFromResultSet) {
for (TypeCast typeCast : statement.typeCasts) if (typeCast.name == columnName) return relationalWriter->toTypeId(typeCast.type);
- return preparedStatement->getColumType(columnIndex);
+ return typeFromResultSet;
}
void processStatement(const Statement& statement) {
- std::shared_ptr<PreparedStatement> prepared(connection->prepareStatement(convertor.to_bytes(statement.sql).c_str()));
- int columnCount = prepared->getColumnCount();
+ std::shared_ptr<PreparedStatement> prepared(connection->prepareStatement(statement.sql));
int parameterCount = statement.parameters.size();
for (int i = 0; i < parameterCount; i++) {
- prepared->setString(i + 1, convertor.to_bytes(statement.parameters[i].value));
+ prepared->setString(i + 1, statement.parameters[i].value);
}
+ std::shared_ptr<ResultSet> resultSet(prepared->executeQuery());
+ std::shared_ptr<ResultSet::MetaData> metaData(resultSet->getMetaData());
+
+ auto columnCount = metaData->getColumnCount();
std::vector<relpipe::writer::AttributeMetadata> metadata;
- 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)});
+ for (int columnNumber = 1; columnNumber <= columnCount; columnNumber++) {
+ auto columnDescriptor = metaData->describeColumn(columnNumber);
+ metadata.push_back({columnDescriptor.name, findType(columnDescriptor.name, statement, columnDescriptor.type)});
}
relationalWriter->startRelation(statement.relation, metadata, true);
- while (prepared->next()) {
- for (int i = 0; i < columnCount; i++) {
- relationalWriter->writeAttribute(convertor.from_bytes(prepared->getString(i)));
+ while (resultSet->next()) {
+ for (int columnNumber = 1; columnNumber <= columnCount; columnNumber++) {
+ relationalWriter->writeAttribute(resultSet->getString(columnNumber));
}
}
}
- std::vector<string_t> getAllRelations() {
- std::vector<string_t> relations;
- std::unique_ptr<PreparedStatement> prepared(connection->prepareStatement("SELECT name FROM sqlite_master WHERE type IN ('table', 'view')"));
- while (prepared->next()) relations.push_back(convertor.from_bytes(prepared->getString(0)));
- return relations;
- }
-
void copyRelations(const CopyRelations& copy) {
std::wregex pattern(copy.pattern);
- for (string_t relation : getAllRelations()) {
- if (regex_match(relation, pattern)) {
+ relpipe::writer::string_t userName = connection->getUserName();
+ for (Connection::TablePrivilege tableMetaData : connection->getTablePrivileges()) {
+ if (regex_match(tableMetaData.name, pattern) && tableMetaData.privilege == L"SELECT" && tableMetaData.grantee == userName) {
+ // TODO: May we have multiple SELECT permissions for same table? Copy it only once.
std::wstringstream select;
select << L"SELECT * FROM ";
- writeIdentifier(select, relation);
+ if (tableMetaData.schema.size()) {
+ // TODO: use qualified table name also for regex matching and for relation name
+ writeIdentifier(select, tableMetaData.schema);
+ select << L".";
+ }
+ writeIdentifier(select, tableMetaData.name);
Statement statement;
- statement.relation = copy.replace ? regex_replace(relation, pattern, copy.replacement) : relation;
+ statement.relation = copy.replace ? regex_replace(tableMetaData.name, pattern, copy.replacement) : tableMetaData.name;
statement.sql = select.str();
processStatement(statement);
}
@@ -170,8 +174,9 @@
file = ":memory:";
}
- connection.reset(new Connection(file.c_str()));
- connection->setAutoCommit(false);
+ connection.reset(driverManager->getConnectionByDSN(L"sqlite-memory")); // FIXME: custom DSN and files
+ connection.reset(driverManager->getConnectionByDSN(L"relpipe")); // FIXME: custom DSN and files
+ //connection->setAutoCommit(false);
}
virtual ~SqlHandler() {
@@ -194,8 +199,8 @@
}
sql << L"\n)";
- std::unique_ptr<PreparedStatement> createTable(connection->prepareStatement(convertor.to_bytes(sql.str()).c_str()));
- createTable->next();
+ std::unique_ptr<PreparedStatement> createTable(connection->prepareStatement(sql.str()));
+ createTable->executeUpdate();
// prepare INSERT:
sql = wstringstream();
@@ -207,7 +212,7 @@
if (i < attributes.size() - 1) sql << L",";
}
sql << L")";
- currentInsert.reset(connection->prepareStatement(convertor.to_bytes(sql.str()).c_str()));
+ currentInsert.reset(connection->prepareStatement(sql.str()));
}
void attribute(const void* value, const std::type_info& typeInfo) override {
@@ -233,7 +238,7 @@
{
assert(typeInfo == typeid (string_t));
auto* typedValue = static_cast<const string_t*> (value);
- currentInsert->setString(currentAttributeIndex, convertor.to_bytes(*typedValue).c_str());
+ currentInsert->setString(currentAttributeIndex, *typedValue);
break;
}
default:
@@ -241,7 +246,7 @@
}
if (currentAttributeIndex % currentReaderMetadata.size() == 0) {
- currentInsert->next();
+ currentInsert->executeUpdate();
currentInsert->reset();
currentAttributeIndex = 0;
}
--- a/src/relpipe-tr-sql.cpp Mon May 25 21:11:17 2020 +0200
+++ b/src/relpipe-tr-sql.cpp Sun May 31 16:56:07 2020 +0200
@@ -55,7 +55,7 @@
std::shared_ptr<DriverManager> driverManager = std::make_shared<DriverManager>();
std::shared_ptr<writer::RelationalWriter> writer(writer::Factory::create(std::cout));
-
+
if (configuration.listDataSources) {
// --list-data-sources:
SqlHandler::listDataSources(writer.get(), driverManager.get());