author | František Kučera <franta-hg@frantovo.cz> |
Thu, 04 Jun 2020 00:03:37 +0200 | |
branch | v_0 |
changeset 46 | 85e6dc1853ee |
parent 43 | 7f396cdb9628 |
permissions | -rw-r--r-- |
0 | 1 |
/** |
2 |
* Relational pipes |
|
3 |
* Copyright © 2019 František Kučera (Frantovo.cz, GlobalCode.info) |
|
4 |
* |
|
5 |
* This program is free software: you can redistribute it and/or modify |
|
6 |
* it under the terms of the GNU General Public License as published by |
|
10
7da7173d84b0
fix license version: GNU GPLv3
František Kučera <franta-hg@frantovo.cz>
parents:
8
diff
changeset
|
7 |
* the Free Software Foundation, version 3. |
0 | 8 |
* |
9 |
* This program is distributed in the hope that it will be useful, |
|
10 |
* but WITHOUT ANY WARRANTY; without even the implied warranty of |
|
11 |
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
|
12 |
* GNU General Public License for more details. |
|
13 |
* |
|
14 |
* You should have received a copy of the GNU General Public License |
|
15 |
* along with this program. If not, see <http://www.gnu.org/licenses/>. |
|
16 |
*/ |
|
17 |
||
36
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
18 |
#include <memory> |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
19 |
#include <iostream> |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
20 |
|
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
21 |
#include <sql.h> |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
22 |
#include <sqlext.h> |
29
b0ef1e1dc9c8
keep sqlite3.h dependency in Connection.cpp and PreparedStatement.cpp only
František Kučera <franta-hg@frantovo.cz>
parents:
27
diff
changeset
|
23 |
|
27
9441a517fa1f
move Connection class to separate .h and .cpp files
František Kučera <franta-hg@frantovo.cz>
parents:
26
diff
changeset
|
24 |
#include "Connection.h" |
36
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
25 |
#include "OdbcCommon.h" |
0 | 26 |
|
27 |
namespace relpipe { |
|
28 |
namespace tr { |
|
29 |
namespace sql { |
|
30 |
||
36
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
31 |
Connection::Connection(void* db) : connection(db) { |
46
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
32 |
setAutoCommit(false); |
27
9441a517fa1f
move Connection class to separate .h and .cpp files
František Kučera <franta-hg@frantovo.cz>
parents:
26
diff
changeset
|
33 |
} |
24
884ece10575d
add --type-cast to allow explicit specification of type for given output attributes
František Kučera <franta-hg@frantovo.cz>
parents:
23
diff
changeset
|
34 |
|
27
9441a517fa1f
move Connection class to separate .h and .cpp files
František Kučera <franta-hg@frantovo.cz>
parents:
26
diff
changeset
|
35 |
Connection::~Connection() { |
46
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
36 |
// If an exception was thrown somewhere, there might be a transaction in progress, so we will cancel this transaction. |
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
37 |
// If there was no exception, commit() has already been called and this rollback() will not affect any (desired) transaction. |
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
38 |
// Without this rollback(), SQLDisconnect() will fail and then will fail also freeHandle() which will throw exception. |
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
39 |
// And throwing exception while throwing exception will lead to a catastrophic failure: SIGABRT, core dump. |
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
40 |
rollback(); |
36
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
41 |
SQLRETURN result = SQLDisconnect(connection); |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
42 |
OdbcCommon::freeHandle(SQL_HANDLE_DBC, connection); |
27
9441a517fa1f
move Connection class to separate .h and .cpp files
František Kučera <franta-hg@frantovo.cz>
parents:
26
diff
changeset
|
43 |
} |
14
eacacf060755
add --dump option: allow pass through of relation specified by a regular expression
František Kučera <franta-hg@frantovo.cz>
parents:
12
diff
changeset
|
44 |
|
36
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
45 |
PreparedStatement* Connection::prepareStatement(relpipe::reader::string_t sql) { |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
46 |
SQLHSTMT statement = OdbcCommon::allocateHandle(SQL_HANDLE_STMT, connection); |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
47 |
SQLRETURN result = SQLPrepare(statement, (SQLCHAR*) convertor.to_bytes(sql).c_str(), SQL_NTS); |
39
b4af13653313
improved exception handling: diagnostics of prepare statement error
František Kučera <franta-hg@frantovo.cz>
parents:
38
diff
changeset
|
48 |
if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to prepare statement", result, SQL_HANDLE_STMT, statement, true); |
36
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
49 |
return new PreparedStatement(statement); |
27
9441a517fa1f
move Connection class to separate .h and .cpp files
František Kučera <franta-hg@frantovo.cz>
parents:
26
diff
changeset
|
50 |
} |
12
0b38339b871b
improve --keep-file option: default is auto = file will be kept, if it was present before the transformation
František Kučera <franta-hg@frantovo.cz>
parents:
11
diff
changeset
|
51 |
|
27
9441a517fa1f
move Connection class to separate .h and .cpp files
František Kučera <franta-hg@frantovo.cz>
parents:
26
diff
changeset
|
52 |
bool Connection::getAutoCommit() { |
46
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
53 |
SQLULEN autoCommit = SQL_AUTOCOMMIT_DEFAULT; |
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
54 |
SQLRETURN result = SQLGetConnectOption(connection, SQL_AUTOCOMMIT, &autoCommit); |
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
55 |
if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to get auto-commit status", result, SQL_HANDLE_DBC, connection); |
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
56 |
|
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
57 |
if (autoCommit == SQL_AUTOCOMMIT_ON) return true; |
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
58 |
else if (autoCommit == SQL_AUTOCOMMIT_OFF) return false; |
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
59 |
else throw SqlException(L"Unexpected value of auto-commit status"); |
27
9441a517fa1f
move Connection class to separate .h and .cpp files
František Kučera <franta-hg@frantovo.cz>
parents:
26
diff
changeset
|
60 |
} |
7 | 61 |
|
27
9441a517fa1f
move Connection class to separate .h and .cpp files
František Kučera <franta-hg@frantovo.cz>
parents:
26
diff
changeset
|
62 |
void Connection::setAutoCommit(bool autoCommit) { |
46
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
63 |
SQLRETURN result = SQLSetConnectOption(connection, SQL_AUTOCOMMIT, autoCommit ? SQL_AUTOCOMMIT_ON : SQL_AUTOCOMMIT_OFF); |
85e6dc1853ee
transaction control: disable auto-commit, run all statements in a single transaction, do rollback on exception
František Kučera <franta-hg@frantovo.cz>
parents:
43
diff
changeset
|
64 |
if (OdbcCommon::isNotSuccessful(result)) throw SqlException(std::wstring(L"Unable to set auto-commit to ") + (autoCommit ? L"enabled" : L"disabled"), result, SQL_HANDLE_DBC, connection); |
27
9441a517fa1f
move Connection class to separate .h and .cpp files
František Kučera <franta-hg@frantovo.cz>
parents:
26
diff
changeset
|
65 |
} |
8 | 66 |
|
27
9441a517fa1f
move Connection class to separate .h and .cpp files
František Kučera <franta-hg@frantovo.cz>
parents:
26
diff
changeset
|
67 |
void Connection::commit() { |
36
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
68 |
SQLRETURN result = SQLEndTran(SQL_HANDLE_DBC, connection, SQL_COMMIT); |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
69 |
if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to COMMIT: " + std::to_wstring(result)); |
27
9441a517fa1f
move Connection class to separate .h and .cpp files
František Kučera <franta-hg@frantovo.cz>
parents:
26
diff
changeset
|
70 |
} |
12
0b38339b871b
improve --keep-file option: default is auto = file will be kept, if it was present before the transformation
František Kučera <franta-hg@frantovo.cz>
parents:
11
diff
changeset
|
71 |
|
27
9441a517fa1f
move Connection class to separate .h and .cpp files
František Kučera <franta-hg@frantovo.cz>
parents:
26
diff
changeset
|
72 |
void Connection::rollback() { |
36
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
73 |
SQLRETURN result = SQLEndTran(SQL_HANDLE_DBC, connection, SQL_ROLLBACK); |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
74 |
if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to ROLLBACK: " + std::to_wstring(result)); |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
75 |
} |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
76 |
|
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
77 |
std::vector<Connection::TablePrivilege> Connection::getTablePrivileges() { |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
78 |
std::vector<TablePrivilege> tables; |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
79 |
SQLHSTMT statementHandle = OdbcCommon::allocateHandle(SQL_HANDLE_STMT, connection); |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
80 |
SQLRETURN result = SQLTablePrivileges(statementHandle, nullptr, 0, nullptr, 0, nullptr, 0); |
43
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
81 |
if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable get tables (prepare)", result, SQL_HANDLE_STMT, statementHandle, true); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
82 |
|
36
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
83 |
ResultSet resultSet(statementHandle); |
43
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
84 |
std::shared_ptr<ResultSet::MetaData> metaData(resultSet.getMetaData()); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
85 |
SQLUSMALLINT catalogColumn = metaData->getColumnNumber(L"TABLE_CAT"); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
86 |
SQLUSMALLINT schemaColumn = metaData->getColumnNumber(L"TABLE_SCHEM"); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
87 |
SQLUSMALLINT nameColumn = metaData->getColumnNumber(L"TABLE_NAME"); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
88 |
SQLUSMALLINT grantorColumn = metaData->getColumnNumber(L"GRANTOR"); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
89 |
SQLUSMALLINT granteeColumn = metaData->getColumnNumber(L"GRANTEE"); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
90 |
SQLUSMALLINT privilegeColumn = metaData->getColumnNumber(L"PRIVILEGE"); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
91 |
SQLUSMALLINT isGrantableColumn = metaData->getColumnNumber(L"IS_GRANTABLE"); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
92 |
|
36
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
93 |
while (resultSet.next()) { |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
94 |
TablePrivilege tp; |
43
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
95 |
tp.catalog = resultSet.getString(catalogColumn); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
96 |
tp.schema = resultSet.getString(schemaColumn); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
97 |
tp.name = resultSet.getString(nameColumn); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
98 |
tp.grantor = resultSet.getString(grantorColumn); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
99 |
tp.grantee = resultSet.getString(granteeColumn); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
100 |
tp.privilege = resultSet.getString(privilegeColumn); |
7f396cdb9628
getTablePrivileges() finds columns by names instead of numbers
František Kučera <franta-hg@frantovo.cz>
parents:
39
diff
changeset
|
101 |
tp.isGrantable = resultSet.getString(isGrantableColumn) == L"YES"; |
36
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
102 |
tables.emplace_back(tp); |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
103 |
} |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
104 |
|
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
105 |
return tables; |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
106 |
} |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
107 |
|
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
108 |
relpipe::reader::string_t Connection::getUserName() { |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
109 |
std::vector<char> buffer(100); |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
110 |
SQLSMALLINT stringLength; |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
111 |
SQLRETURN result = SQLGetInfo(connection, SQL_USER_NAME, buffer.data(), buffer.size(), &stringLength); |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
112 |
if (OdbcCommon::isNotSuccessful(result)) throw SqlException(L"Unable to get user name: " + std::to_wstring(result)); |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
113 |
if (stringLength >= buffer.size()) throw SqlException(L"Unable to get user name: too long" + std::to_wstring(stringLength)); |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
114 |
return convertor.from_bytes(buffer.data(), buffer.data() + stringLength); |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
115 |
} |
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
116 |
|
91cb012d779a
use ODBC, avoid direct dependency on SQLite
František Kučera <franta-hg@frantovo.cz>
parents:
32
diff
changeset
|
117 |
|
0 | 118 |
} |
119 |
} |
|
120 |
} |