Mercurial > hg > orthanc-databases
annotate Framework/MySQL/MySQLStatement.cpp @ 95:aceb0174c4ed db-changes
fix unit test
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Mon, 21 Jan 2019 14:27:40 +0100 |
parents | 714c5d2bee76 |
children | 4cd7e45b671e |
rev | line source |
---|---|
0 | 1 /** |
2 * Orthanc - A Lightweight, RESTful DICOM Store | |
3 * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics | |
4 * Department, University Hospital of Liege, Belgium | |
67 | 5 * Copyright (C) 2017-2019 Osimis S.A., Belgium |
0 | 6 * |
7 * This program is free software: you can redistribute it and/or | |
8 * modify it under the terms of the GNU Affero General Public License | |
9 * as published by the Free Software Foundation, either version 3 of | |
10 * the License, or (at your option) any later version. | |
11 * | |
12 * This program is distributed in the hope that it will be useful, but | |
13 * WITHOUT ANY WARRANTY; without even the implied warranty of | |
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU | |
15 * Affero General Public License for more details. | |
16 * | |
17 * You should have received a copy of the GNU Affero General Public License | |
18 * along with this program. If not, see <http://www.gnu.org/licenses/>. | |
19 **/ | |
20 | |
21 | |
22 #include "MySQLStatement.h" | |
23 | |
24 #include "../Common/BinaryStringValue.h" | |
25 #include "../Common/FileValue.h" | |
26 #include "../Common/Integer64Value.h" | |
27 #include "../Common/NullValue.h" | |
28 #include "../Common/Utf8StringValue.h" | |
29 #include "MySQLResult.h" | |
30 | |
31 #include <Core/Logging.h> | |
32 #include <Core/OrthancException.h> | |
33 | |
34 #include <list> | |
35 #include <memory> | |
36 | |
37 namespace OrthancDatabases | |
38 { | |
39 class MySQLStatement::ResultField : public boost::noncopyable | |
40 { | |
41 private: | |
42 IValue* CreateIntegerValue(MYSQL_BIND& bind) const | |
43 { | |
44 if (length_ != buffer_.size()) | |
45 { | |
46 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); | |
47 } | |
48 | |
49 switch (mysqlType_) | |
50 { | |
51 case MYSQL_TYPE_TINY: | |
52 if (bind.is_unsigned) | |
53 { | |
54 return new Integer64Value(*reinterpret_cast<const uint8_t*>(&buffer_[0])); | |
55 } | |
56 else | |
57 { | |
58 return new Integer64Value(*reinterpret_cast<const int8_t*>(&buffer_[0])); | |
59 } | |
60 | |
61 case MYSQL_TYPE_SHORT: | |
62 if (bind.is_unsigned) | |
63 { | |
64 return new Integer64Value(*reinterpret_cast<const uint16_t*>(&buffer_[0])); | |
65 } | |
66 else | |
67 { | |
68 return new Integer64Value(*reinterpret_cast<const int16_t*>(&buffer_[0])); | |
69 } | |
70 | |
71 break; | |
72 | |
73 case MYSQL_TYPE_LONG: | |
74 if (bind.is_unsigned) | |
75 { | |
76 return new Integer64Value(*reinterpret_cast<const uint32_t*>(&buffer_[0])); | |
77 } | |
78 else | |
79 { | |
80 return new Integer64Value(*reinterpret_cast<const int32_t*>(&buffer_[0])); | |
81 } | |
82 | |
83 break; | |
84 | |
85 case MYSQL_TYPE_LONGLONG: | |
86 if (bind.is_unsigned) | |
87 { | |
88 uint64_t value = *reinterpret_cast<const uint64_t*>(&buffer_[0]); | |
89 if (static_cast<uint64_t>(static_cast<int64_t>(value)) != value) | |
90 { | |
91 LOG(WARNING) << "Overflow in a 64 bit integer"; | |
92 } | |
93 | |
94 return new Integer64Value(static_cast<int64_t>(value)); | |
95 } | |
96 else | |
97 { | |
98 return new Integer64Value(*reinterpret_cast<const int64_t*>(&buffer_[0])); | |
99 } | |
100 | |
101 break; | |
102 | |
103 default: | |
104 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); | |
105 } | |
106 } | |
107 | |
108 | |
109 enum enum_field_types mysqlType_; | |
110 ValueType orthancType_; | |
111 std::string buffer_; | |
112 my_bool isNull_; | |
113 my_bool isError_; | |
114 unsigned long length_; | |
115 | |
116 | |
117 public: | |
118 ResultField(const MYSQL_FIELD& field) : | |
119 mysqlType_(field.type) | |
120 { | |
121 // https://dev.mysql.com/doc/refman/8.0/en/c-api-data-structures.html | |
122 // https://dev.mysql.com/doc/refman/8.0/en/mysql-stmt-fetch.html => size of "buffer_" | |
123 switch (field.type) | |
124 { | |
125 case MYSQL_TYPE_TINY: | |
126 orthancType_ = ValueType_Integer64; | |
127 buffer_.resize(1); | |
128 break; | |
129 | |
130 case MYSQL_TYPE_SHORT: | |
131 orthancType_ = ValueType_Integer64; | |
132 buffer_.resize(2); | |
133 break; | |
134 | |
135 case MYSQL_TYPE_LONG: | |
136 orthancType_ = ValueType_Integer64; | |
137 buffer_.resize(4); | |
138 break; | |
139 | |
140 case MYSQL_TYPE_LONGLONG: | |
141 orthancType_ = ValueType_Integer64; | |
142 buffer_.resize(8); | |
143 break; | |
144 | |
145 case MYSQL_TYPE_STRING: | |
146 case MYSQL_TYPE_VAR_STRING: | |
147 case MYSQL_TYPE_BLOB: | |
148 // https://medium.com/@adamhooper/in-mysql-never-use-utf8-use-utf8mb4-11761243e434 | |
149 switch (field.charsetnr) | |
150 { | |
151 case 45: // utf8mb4_general_ci | |
152 case 46: // utf8mb4_bin | |
153 case 224: // utf8mb4_unicode_ci => RECOMMENDED collation | |
46
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
154 case 255: // utf8mb4_0900_ai_ci => necessary for MySQL 8.0 |
0 | 155 // https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci |
156 orthancType_ = ValueType_Utf8String; | |
157 break; | |
158 | |
159 case 63: | |
160 orthancType_ = ValueType_BinaryString; | |
161 break; | |
162 | |
163 default: | |
164 LOG(ERROR) << "Unsupported MySQL charset: " << field.charsetnr; | |
165 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); | |
166 } | |
167 | |
168 if (field.max_length > 0) | |
169 { | |
170 buffer_.resize(field.max_length); | |
171 } | |
172 | |
173 break; | |
174 | |
175 default: | |
176 LOG(ERROR) << "MYSQL_TYPE not implemented: " << field.type; | |
177 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); | |
178 } | |
179 } | |
180 | |
181 enum enum_field_types GetMysqlType() const | |
182 { | |
183 return mysqlType_; | |
184 } | |
185 | |
186 ValueType GetOrthancType() const | |
187 { | |
188 return orthancType_; | |
189 } | |
190 | |
191 void PrepareBind(MYSQL_BIND& bind) | |
192 { | |
193 memset(&bind, 0, sizeof(bind)); | |
194 | |
9
eff482803d30
fix uninitialized values
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
195 isNull_ = false; |
eff482803d30
fix uninitialized values
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
196 isError_ = false; |
0 | 197 length_ = 0; |
198 | |
199 bind.buffer_length = buffer_.size(); | |
200 bind.buffer_type = mysqlType_; | |
201 bind.is_null = &isNull_; | |
202 bind.length = &length_; | |
203 | |
204 if (buffer_.empty()) | |
205 { | |
206 // Only fetches the actual size of the field (*): | |
207 // mysql_stmt_fetch_column() must be invoked afterward | |
208 bind.buffer = 0; | |
209 } | |
210 else | |
211 { | |
212 bind.buffer = &buffer_[0]; | |
213 bind.error = &isError_; | |
214 } | |
215 } | |
216 | |
217 | |
218 IValue* FetchValue(MySQLDatabase& database, | |
219 MYSQL_STMT& statement, | |
220 MYSQL_BIND& bind, | |
221 unsigned int column) const | |
222 { | |
223 if (isError_) | |
224 { | |
225 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); | |
226 } | |
227 else if (isNull_) | |
228 { | |
229 return new NullValue; | |
230 } | |
231 else if (orthancType_ == ValueType_Integer64) | |
232 { | |
233 return CreateIntegerValue(bind); | |
234 } | |
235 else if (orthancType_ == ValueType_Utf8String || | |
236 orthancType_ == ValueType_BinaryString) | |
237 { | |
238 std::string tmp; | |
239 tmp.resize(length_); | |
240 | |
241 if (!tmp.empty()) | |
242 { | |
243 if (buffer_.empty()) | |
244 { | |
245 bind.buffer = &tmp[0]; | |
246 bind.buffer_length = tmp.size(); | |
247 | |
248 database.CheckErrorCode(mysql_stmt_fetch_column(&statement, &bind, column, 0)); | |
249 } | |
250 else if (tmp.size() <= buffer_.size()) | |
251 { | |
252 memcpy(&tmp[0], &buffer_[0], length_); | |
253 } | |
254 else | |
255 { | |
256 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); | |
257 } | |
258 } | |
259 | |
260 if (orthancType_ == ValueType_Utf8String) | |
261 { | |
262 return new Utf8StringValue(tmp); | |
263 } | |
264 else | |
265 { | |
266 return new BinaryStringValue(tmp); | |
267 } | |
268 } | |
269 else | |
270 { | |
271 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); | |
272 } | |
273 } | |
274 }; | |
275 | |
276 | |
277 class MySQLStatement::ResultMetadata : public boost::noncopyable | |
278 { | |
279 private: | |
280 MYSQL_RES* metadata_; | |
281 | |
282 public: | |
283 ResultMetadata(MySQLDatabase& db, | |
284 MySQLStatement& statement) : | |
285 metadata_(NULL) | |
286 { | |
287 metadata_ = mysql_stmt_result_metadata(statement.GetObject()); | |
288 } | |
289 | |
290 ~ResultMetadata() | |
291 { | |
292 if (metadata_ != NULL) | |
293 { | |
294 mysql_free_result(metadata_); | |
295 } | |
296 } | |
297 | |
298 bool HasFields() const | |
299 { | |
300 return metadata_ != NULL; | |
301 } | |
302 | |
303 size_t GetFieldsCount() | |
304 { | |
305 if (HasFields()) | |
306 { | |
307 return mysql_num_fields(metadata_); | |
308 } | |
309 else | |
310 { | |
311 return 0; | |
312 } | |
313 } | |
314 | |
315 MYSQL_RES* GetObject() | |
316 { | |
317 return metadata_; | |
318 } | |
319 }; | |
320 | |
321 | |
322 void MySQLStatement::Close() | |
323 { | |
324 for (size_t i = 0; i < result_.size(); i++) | |
325 { | |
326 if (result_[i] != NULL) | |
327 { | |
328 delete result_[i]; | |
329 } | |
330 } | |
331 | |
332 if (statement_ != NULL) | |
333 { | |
334 mysql_stmt_close(statement_); | |
335 statement_ = NULL; | |
336 } | |
337 } | |
338 | |
339 | |
340 MySQLStatement::MySQLStatement(MySQLDatabase& db, | |
341 const Query& query) : | |
342 db_(db), | |
343 readOnly_(query.IsReadOnly()), | |
344 statement_(NULL), | |
345 formatter_(Dialect_MySQL) | |
346 { | |
347 std::string sql; | |
348 query.Format(sql, formatter_); | |
349 | |
350 statement_ = mysql_stmt_init(db.GetObject()); | |
351 if (statement_ == NULL) | |
352 { | |
353 db.LogError(); | |
354 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); | |
355 } | |
356 | |
357 LOG(INFO) << "Preparing MySQL statement: " << sql; | |
358 | |
359 db_.CheckErrorCode(mysql_stmt_prepare(statement_, sql.c_str(), sql.size())); | |
360 | |
361 if (mysql_stmt_param_count(statement_) != formatter_.GetParametersCount()) | |
362 { | |
363 Close(); | |
364 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); | |
365 } | |
366 | |
367 try | |
368 { | |
369 ResultMetadata result(db, *this); | |
370 | |
371 if (result.HasFields()) | |
372 { | |
373 MYSQL_FIELD *field; | |
374 while ((field = mysql_fetch_field(result.GetObject()))) | |
375 { | |
376 result_.push_back(new ResultField(*field)); | |
377 } | |
378 } | |
379 | |
380 if (result_.size() != result.GetFieldsCount()) | |
381 { | |
382 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); | |
383 } | |
384 } | |
385 catch (Orthanc::OrthancException&) | |
386 { | |
387 Close(); | |
388 throw; | |
389 } | |
390 | |
391 if (query.IsReadOnly()) | |
392 { | |
393 unsigned long type = (unsigned long) CURSOR_TYPE_READ_ONLY; | |
394 mysql_stmt_attr_set(statement_, STMT_ATTR_CURSOR_TYPE, (void*) &type); | |
395 } | |
396 } | |
397 | |
398 | |
46
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
399 MySQLStatement::~MySQLStatement() |
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
400 { |
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
401 try |
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
402 { |
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
403 Close(); |
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
404 } |
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
405 catch (Orthanc::OrthancException&) |
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
406 { |
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
407 // Ignore possible exceptions due to connection loss |
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
408 } |
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
409 } |
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
410 |
6a574d810b98
Compatibility with MySQL 8.0
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
23
diff
changeset
|
411 |
0 | 412 MYSQL_STMT* MySQLStatement::GetObject() |
413 { | |
414 if (statement_ == NULL) | |
415 { | |
416 throw Orthanc::OrthancException(Orthanc::ErrorCode_BadSequenceOfCalls); | |
417 } | |
418 else | |
419 { | |
420 return statement_; | |
421 } | |
422 } | |
423 | |
424 | |
425 IValue* MySQLStatement::FetchResultField(size_t i) | |
426 { | |
427 if (i >= result_.size()) | |
428 { | |
429 throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); | |
430 } | |
431 else | |
432 { | |
433 assert(result_[i] != NULL); | |
434 return result_[i]->FetchValue(db_, *statement_, outputs_[i], i); | |
435 } | |
436 } | |
437 | |
438 | |
23
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
9
diff
changeset
|
439 IResult* MySQLStatement::Execute(ITransaction& transaction, |
0 | 440 const Dictionary& parameters) |
441 { | |
442 std::list<int> intParameters; | |
443 std::list<long long int> int64Parameters; | |
444 | |
445 std::vector<MYSQL_BIND> inputs(formatter_.GetParametersCount()); | |
446 | |
447 for (size_t i = 0; i < inputs.size(); i++) | |
448 { | |
449 memset(&inputs[i], 0, sizeof(MYSQL_BIND)); | |
450 | |
451 const std::string& name = formatter_.GetParameterName(i); | |
452 if (!parameters.HasKey(name)) | |
453 { | |
454 LOG(ERROR) << "Missing required parameter in a SQL query: " << name; | |
455 throw Orthanc::OrthancException(Orthanc::ErrorCode_InexistentItem); | |
456 } | |
457 | |
458 ValueType type = formatter_.GetParameterType(i); | |
459 | |
460 const IValue& value = parameters.GetValue(name); | |
461 if (value.GetType() != type) | |
462 { | |
463 LOG(ERROR) << "Bad type of argument provided to a SQL query: " << name; | |
464 throw Orthanc::OrthancException(Orthanc::ErrorCode_BadParameterType); | |
465 } | |
466 | |
467 // https://dev.mysql.com/doc/refman/8.0/en/c-api-prepared-statement-type-codes.html | |
468 switch (type) | |
469 { | |
470 case ValueType_Integer64: | |
471 { | |
472 int64Parameters.push_back(dynamic_cast<const Integer64Value&>(value).GetValue()); | |
473 inputs[i].buffer = &int64Parameters.back(); | |
474 inputs[i].buffer_type = MYSQL_TYPE_LONGLONG; | |
475 break; | |
476 } | |
477 | |
478 case ValueType_Utf8String: | |
479 { | |
480 const std::string& utf8 = dynamic_cast<const Utf8StringValue&>(value).GetContent(); | |
481 inputs[i].buffer = const_cast<char*>(utf8.c_str()); | |
482 inputs[i].buffer_length = utf8.size(); | |
483 inputs[i].buffer_type = MYSQL_TYPE_STRING; | |
484 break; | |
485 } | |
486 | |
487 case ValueType_BinaryString: | |
488 { | |
489 const std::string& content = dynamic_cast<const BinaryStringValue&>(value).GetContent(); | |
490 inputs[i].buffer = const_cast<char*>(content.c_str()); | |
491 inputs[i].buffer_length = content.size(); | |
492 inputs[i].buffer_type = MYSQL_TYPE_BLOB; | |
493 break; | |
494 } | |
495 | |
496 case ValueType_File: | |
497 { | |
498 const std::string& content = dynamic_cast<const FileValue&>(value).GetContent(); | |
499 inputs[i].buffer = const_cast<char*>(content.c_str()); | |
500 inputs[i].buffer_length = content.size(); | |
501 inputs[i].buffer_type = MYSQL_TYPE_BLOB; | |
502 break; | |
503 } | |
504 | |
505 case ValueType_Null: | |
506 { | |
507 inputs[i].buffer = NULL; | |
508 inputs[i].buffer_type = MYSQL_TYPE_NULL; | |
509 break; | |
510 } | |
511 | |
512 default: | |
513 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); | |
514 } | |
515 } | |
516 | |
517 if (!inputs.empty()) | |
518 { | |
519 db_.CheckErrorCode(mysql_stmt_bind_param(statement_, &inputs[0])); | |
520 } | |
521 | |
522 db_.CheckErrorCode(mysql_stmt_execute(statement_)); | |
523 | |
524 outputs_.resize(result_.size()); | |
525 | |
526 for (size_t i = 0; i < result_.size(); i++) | |
527 { | |
528 assert(result_[i] != NULL); | |
529 result_[i]->PrepareBind(outputs_[i]); | |
530 } | |
531 | |
532 if (!outputs_.empty()) | |
533 { | |
534 db_.CheckErrorCode(mysql_stmt_bind_result(statement_, &outputs_[0])); | |
535 db_.CheckErrorCode(mysql_stmt_store_result(statement_)); | |
536 } | |
537 | |
538 return new MySQLResult(db_, *this); | |
539 } | |
540 | |
541 | |
23
b2ff1cd2907a
handling of implicit transactions in DatabaseManager
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
9
diff
changeset
|
542 void MySQLStatement::ExecuteWithoutResult(ITransaction& transaction, |
0 | 543 const Dictionary& parameters) |
544 { | |
545 std::auto_ptr<IResult> dummy(Execute(transaction, parameters)); | |
546 } | |
547 } |