83 // currently disabled due to relpipe-in-sql parser issues with last comment without any following expression |
88 // currently disabled due to relpipe-in-sql parser issues with last comment without any following expression |
84 // output << "-- Record count: " << recordCount << std::endl; |
89 // output << "-- Record count: " << recordCount << std::endl; |
85 } |
90 } |
86 |
91 |
87 void endRelation() { |
92 void endRelation() { |
88 |
93 if (getWriteDML()) { |
89 if (configuration.insertMode == Configuration::InsertMode::MULTI) { |
94 if (getInsertMode() == Configuration::InsertMode::MULTI) { |
90 output << std::endl << ";" << std::endl; |
95 output << std::endl << ";" << std::endl; |
91 } |
96 } |
92 |
97 |
93 writeRecordCount(); |
98 writeRecordCount(); |
|
99 } |
|
100 } |
|
101 |
|
102 // TODO: code deduplication |
|
103 |
|
104 Configuration::InsertMode getInsertMode() { |
|
105 for (const Configuration::RelationConfiguration& rc : configuration.relationConfigurations) { |
|
106 if (std::regex_match(currentRelation, rc.relationPattern)) { |
|
107 if (rc.insertMode != Configuration::InsertMode::DEFAULT) return rc.insertMode; |
|
108 } |
|
109 } |
|
110 return Configuration::InsertMode::MULTI; |
|
111 } |
|
112 |
|
113 // TODO: code deduplication |
|
114 |
|
115 bool getWriteDDL() { |
|
116 for (const Configuration::RelationConfiguration& rc : configuration.relationConfigurations) { |
|
117 if (std::regex_match(currentRelation, rc.relationPattern)) return rc.writeDDL; |
|
118 } |
|
119 return true; |
|
120 } |
|
121 |
|
122 // TODO: code deduplication |
|
123 |
|
124 bool getWriteDML() { |
|
125 for (const Configuration::RelationConfiguration& rc : configuration.relationConfigurations) { |
|
126 if (std::regex_match(currentRelation, rc.relationPattern)) return rc.writeDML; |
|
127 } |
|
128 return true; |
|
129 } |
|
130 |
|
131 // TODO: code deduplication |
|
132 |
|
133 bool getWriteColumnNames() { |
|
134 for (const Configuration::RelationConfiguration& rc : configuration.relationConfigurations) { |
|
135 if (std::regex_match(currentRelation, rc.relationPattern)) return rc.writeColumnNames; |
|
136 } |
|
137 return true; |
|
138 } |
|
139 |
|
140 string_t findSQLType(const AttributeMetadata attribute) { |
|
141 // Type casts provided by the user: |
|
142 for (const Configuration::RelationConfiguration& rc : configuration.relationConfigurations) { |
|
143 if (std::regex_match(currentRelation, rc.relationPattern)) { |
|
144 for (const Configuration::TypeCastRule tcr : rc.typeCastRules) { |
|
145 if (std::regex_match(attribute.getAttributeName(), tcr.attribute) && std::regex_match(attribute.getTypeName(), tcr.type)) return tcr.sqlType; |
|
146 } |
|
147 } |
|
148 } |
|
149 |
|
150 // Default mapping: |
|
151 if (attribute.getTypeId() == reader::TypeId::BOOLEAN) return L"integer"; |
|
152 else if (attribute.getTypeId() == reader::TypeId::INTEGER) return L"bigint"; |
|
153 else if (attribute.getTypeId() == reader::TypeId::STRING) return L"text"; |
|
154 else throw RelpipeSQLWriterException(L"Type not yet supported in the SQL output: " + attribute.getTypeName()); |
94 } |
155 } |
95 |
156 |
96 public: |
157 public: |
97 |
158 |
98 SQLHandler(std::ostream& output, Configuration& configuration) : output(output), configuration(configuration) { |
159 SQLHandler(std::ostream& output, Configuration& configuration) : output(output), configuration(configuration) { |
99 } |
160 } |
100 |
161 |
101 void startRelation(string_t name, std::vector<AttributeMetadata> attributes) override { |
162 void startRelation(string_t name, std::vector<AttributeMetadata> attributes) override { |
102 // TODO: ALTER TABLE / add columns on duplicate relation name |
163 // TODO: ALTER TABLE / add columns on duplicate relation name |
103 // TODO: optionally omit CREATE/ALTER table (just INSERT) |
|
104 // TODO: optional transformation to upper/lower case |
164 // TODO: optional transformation to upper/lower case |
105 // TODO: custom data type mapping |
|
106 // TODO: custom primary key or other column properties |
165 // TODO: custom primary key or other column properties |
107 // TODO: custom table properties |
166 // TODO: custom table properties |
108 // TODO: custom SQL script before/after stream/relation/record |
167 // TODO: custom SQL script before/after stream/relation/record |
109 // TODO: comments and/or custom comments + record count of each table as a comment |
168 // TODO: comments and/or custom comments + record count of each table as a comment |
110 // TODO: optional transactions: BEGIN/COMMIT/ROLLBACK for stream/relation/record |
169 // TODO: optional transactions: BEGIN/COMMIT/ROLLBACK for stream/relation/record |
111 // TODO: optional wrapping at certain width (like 80 characters)? |
170 // TODO: optional wrapping at certain width (like 80 characters)? |
112 // TODO: optional syntax highlighting? |
171 // TODO: optional syntax highlighting? |
113 // TODO: share code/behavior with relpipe-tr-sql (but it uses parametrized statements) |
172 // TODO: share code/behavior with relpipe-tr-sql (but it uses parametrized statements) |
114 |
173 |
115 if (currentTable.size()) { |
174 if (currentRelation.size()) { |
116 endRelation(); |
175 endRelation(); |
117 output << std::endl; |
176 if (getWriteDDL() || getWriteDML()) output << std::endl; |
118 } |
177 } |
119 |
178 |
120 currentTable = name; |
179 currentRelation = name; |
121 currentAttributes = attributes; |
180 currentAttributes = attributes; |
122 recordCount = 0; |
181 recordCount = 0; |
123 valueCount = 0; |
182 valueCount = 0; |
124 |
183 |
125 output << "CREATE TABLE "; |
184 if (getWriteDDL()) { |
126 writeIdentifier(output, convertor.to_bytes(currentTable)); |
185 output << "CREATE TABLE "; |
127 output << " (" << std::endl; |
186 writeIdentifier(output, convertor.to_bytes(currentRelation)); |
128 for (size_t i = 0, limit = attributes.size(); i < limit; i++) { |
187 output << " (" << std::endl; |
129 auto attribute = attributes[i]; |
188 for (size_t i = 0, limit = attributes.size(); i < limit; i++) { |
130 output << "\t"; |
189 auto attribute = attributes[i]; |
131 writeIdentifier(output, convertor.to_bytes(attribute.getAttributeName())); |
190 output << "\t"; |
132 // TODO: support all data types + implement RelationalReaderValueHandler |
191 writeIdentifier(output, convertor.to_bytes(attribute.getAttributeName())); |
133 output << " TEXT"; |
192 // TODO: implement RelationalReaderValueHandler |
134 if (i < (limit - 1)) output << ","; |
193 output << " "; |
135 output << std::endl; |
194 writeType(output, convertor.to_bytes(findSQLType(attribute))); |
136 |
195 if (i < (limit - 1)) output << ","; |
137 } |
196 output << std::endl; |
138 output << ");" << std::endl << std::endl; |
197 |
139 |
198 } |
140 if (currentAttributes.empty()) { |
199 output << ");" << std::endl; |
141 //if (configuration.writeHeader) for (auto attr : attributes) attribute(configuration.writeTypes ? attr.getAttributeName() + L"::" + attr.getTypeName() : attr.getAttributeName()); |
200 if (getWriteDML()) output << std::endl; |
142 } else if (matches(currentAttributes, attributes)) { |
|
143 // do UNION ALL – just append the records |
|
144 } else { |
|
145 // 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)."); |
|
146 } |
201 } |
147 } |
202 } |
148 |
203 |
149 void attribute(const string_t& value) override { |
204 void attribute(const string_t& value) override { |
|
205 if (getWriteDML() == false) return; |
150 |
206 |
151 if (valueCount % currentAttributes.size() == 0) { |
207 if (valueCount % currentAttributes.size() == 0) { |
152 // TODO: optional use of function/procedure instead of INSERT |
208 // TODO: optional use of function/procedure instead of INSERT |
153 // TODO: optional INSERT of multiple records |
|
154 // TODO: custom line-ends + indentation |
209 // TODO: custom line-ends + indentation |
155 // TODO: optionally write also the column names |
|
156 recordCount++; |
210 recordCount++; |
157 |
211 |
158 if (configuration.insertMode == Configuration::InsertMode::SINGLE) { |
212 auto insertMode = getInsertMode(); |
|
213 if (insertMode == Configuration::InsertMode::SINGLE) { |
159 output << "INSERT INTO "; |
214 output << "INSERT INTO "; |
160 writeIdentifier(output, convertor.to_bytes(currentTable)); |
215 writeIdentifier(output, convertor.to_bytes(currentRelation)); |
161 |
216 |
162 output << " ("; |
217 if (getWriteColumnNames()) { |
163 for (size_t i = 0, limit = currentAttributes.size(); i < limit; i++) { |
218 output << " ("; |
164 writeIdentifier(output, convertor.to_bytes(currentAttributes[i].getAttributeName())); |
|
165 if (i < (limit - 1)) output << ", "; |
|
166 } |
|
167 output << ")"; |
|
168 |
|
169 output << " VALUES ("; |
|
170 } else if (configuration.insertMode == Configuration::InsertMode::MULTI) { |
|
171 if (recordCount == 1) { |
|
172 // -------- |
|
173 output << "INSERT INTO "; |
|
174 writeIdentifier(output, convertor.to_bytes(currentTable)); |
|
175 |
|
176 output << "\n\t("; |
|
177 for (size_t i = 0, limit = currentAttributes.size(); i < limit; i++) { |
219 for (size_t i = 0, limit = currentAttributes.size(); i < limit; i++) { |
178 writeIdentifier(output, convertor.to_bytes(currentAttributes[i].getAttributeName())); |
220 writeIdentifier(output, convertor.to_bytes(currentAttributes[i].getAttributeName())); |
179 if (i < (limit - 1)) output << ", "; |
221 if (i < (limit - 1)) output << ", "; |
180 } |
222 } |
181 output << ")"; |
223 output << ")"; |
182 // -------- |
224 } |
183 |
225 |
184 output << std::endl << "VALUES" << std::endl; |
226 output << " VALUES ("; |
|
227 } else if (insertMode == Configuration::InsertMode::MULTI) { |
|
228 if (recordCount == 1) { |
|
229 output << "INSERT INTO "; |
|
230 writeIdentifier(output, convertor.to_bytes(currentRelation)); |
|
231 |
|
232 if (getWriteColumnNames()) { |
|
233 output << "\n\t("; |
|
234 for (size_t i = 0, limit = currentAttributes.size(); i < limit; i++) { |
|
235 writeIdentifier(output, convertor.to_bytes(currentAttributes[i].getAttributeName())); |
|
236 if (i < (limit - 1)) output << ", "; |
|
237 } |
|
238 output << ")" << std::endl; |
|
239 } else { |
|
240 output << " "; |
|
241 } |
|
242 |
|
243 output << "VALUES" << std::endl; |
185 } else { |
244 } else { |
186 output << "," << std::endl; |
245 output << "," << std::endl; |
187 } |
246 } |
188 output << "\t("; |
247 output << "\t("; |
189 } else { |
248 } else { |
190 throw RelpipeSQLWriterException(L"Unsupported InsertMode: " + std::to_wstring((int) configuration.insertMode)); |
249 throw RelpipeSQLWriterException(L"Unsupported InsertMode: " + std::to_wstring((int) insertMode)); |
191 } |
250 } |
192 } |
251 } |
193 |
252 |
194 valueCount++; |
253 valueCount++; |
195 |
254 |