author | František Kučera <franta-hg@frantovo.cz> |
Wed, 07 Dec 2022 00:41:11 +0100 | |
branch | v_0 |
changeset 0 | 7727b4d5560d |
child 1 | 4c0366e1b4df |
permissions | -rw-r--r-- |
0
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
1 |
/** |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
2 |
* Relational pipes |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
3 |
* Copyright © 2022 František Kučera (Frantovo.cz, GlobalCode.info) |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
4 |
* |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
5 |
* This program is free software: you can redistribute it and/or modify |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
6 |
* it under the terms of the GNU General Public License as published by |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
7 |
* the Free Software Foundation, version 3 of the License. |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
8 |
* |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
9 |
* This program is distributed in the hope that it will be useful, |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
10 |
* but WITHOUT ANY WARRANTY; without even the implied warranty of |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
11 |
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
12 |
* GNU General Public License for more details. |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
13 |
* |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
14 |
* You should have received a copy of the GNU General Public License |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
15 |
* along with this program. If not, see <http://www.gnu.org/licenses/>. |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
16 |
*/ |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
17 |
#pragma once |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
18 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
19 |
#include <memory> |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
20 |
#include <string> |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
21 |
#include <vector> |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
22 |
#include <iostream> |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
23 |
#include <sstream> |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
24 |
#include <locale> |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
25 |
#include <codecvt> |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
26 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
27 |
#include <relpipe/reader/typedefs.h> |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
28 |
#include <relpipe/reader/TypeId.h> |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
29 |
#include <relpipe/reader/handlers/RelationalReaderStringHandler.h> |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
30 |
#include <relpipe/reader/handlers/AttributeMetadata.h> |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
31 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
32 |
#include "Configuration.h" |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
33 |
#include "RelpipeSQLWriterException.h" |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
34 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
35 |
namespace relpipe { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
36 |
namespace out { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
37 |
namespace sql { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
38 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
39 |
using namespace relpipe; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
40 |
using namespace relpipe::reader; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
41 |
using namespace relpipe::reader::handlers; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
42 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
43 |
class SQLHandler : public RelationalReaderStringHandler { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
44 |
private: |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
45 |
std::ostream& output; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
46 |
Configuration& configuration; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
47 |
const char QUOTE = '"'; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
48 |
std::wstring_convert<std::codecvt_utf8<wchar_t>> convertor; // generate SQL always in UTF-8 |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
49 |
std::vector<AttributeMetadata> firstAttributes; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
50 |
integer_t valueCount = 0; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
51 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
52 |
/** |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
53 |
* @param a |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
54 |
* @param b |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
55 |
* @return true if relations have same number and types of attributes (names may differ) |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
56 |
*/ |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
57 |
bool matches(const std::vector<AttributeMetadata>& a, const std::vector<AttributeMetadata>& b) { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
58 |
if (a.size() != b.size()) return false; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
59 |
for (int i = 0, limit = a.size(); i < limit; i++) if (a[i].getTypeId() != b[i].getTypeId()) return false; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
60 |
return true; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
61 |
} |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
62 |
public: |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
63 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
64 |
SQLHandler(std::ostream& output, Configuration& configuration) : output(output), configuration(configuration) { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
65 |
} |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
66 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
67 |
void startRelation(string_t name, std::vector<AttributeMetadata> attributes) override { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
68 |
// TODO: CREATE TABLE |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
69 |
// TODO: escape identifiers |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
70 |
// TODO: ALTER TABLE / add columns on duplicate relation name |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
71 |
// TODO: custom data type mapping |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
72 |
// TODO: custom name transformation |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
73 |
// TODO: custom SQL script before/after stream/relation/record |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
74 |
// TODO: comments and/or custom comments |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
75 |
// TODO: optional transactions: BEGIN/COMMIT/ROLLBACK for stream/relation/record |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
76 |
output << "CREATE TABLE " << convertor.to_bytes(name) << " ( ... );" << std::endl; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
77 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
78 |
if (firstAttributes.empty()) { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
79 |
firstAttributes = attributes; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
80 |
if (configuration.writeHeader) for (auto attr : attributes) attribute(configuration.writeTypes ? attr.getAttributeName() + L"::" + attr.getTypeName() : attr.getAttributeName()); |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
81 |
} else if (matches(firstAttributes, attributes)) { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
82 |
// do UNION ALL – just append the records |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
83 |
} else { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
84 |
// throw RelpipeSQLWriterException(L"To the SQL format we can convert only one relation or multiple relations that have same number of attributes of same types (relation and attribute names may differ – result is named after the first one)."); |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
85 |
} |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
86 |
} |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
87 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
88 |
void attribute(const string_t& value) override { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
89 |
valueCount++; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
90 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
91 |
if (value.size() > 0) { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
92 |
output << QUOTE; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
93 |
for (auto ch : convertor.to_bytes(value)) { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
94 |
if (ch == QUOTE) output << QUOTE << QUOTE; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
95 |
else output << ch; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
96 |
} |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
97 |
output << QUOTE; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
98 |
} |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
99 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
100 |
if (valueCount % firstAttributes.size()) { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
101 |
output << ","; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
102 |
} else { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
103 |
// TODO: INSERT INTO ... |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
104 |
// TODO: escape identifiers and values |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
105 |
// TODO: optional use of function/procedure instead of INSERT |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
106 |
// TODO: optional INSERT of multiple records |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
107 |
output << std::endl << "INSERT INTO ... VALUES ... ;" << std::endl;; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
108 |
valueCount = 0; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
109 |
} |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
110 |
} |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
111 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
112 |
void endOfPipe() { |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
113 |
output.flush(); |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
114 |
} |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
115 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
116 |
}; |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
117 |
|
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
118 |
} |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
119 |
} |
7727b4d5560d
new module: relpipe-out-sql
František Kučera <franta-hg@frantovo.cz>
parents:
diff
changeset
|
120 |
} |