|
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
|
|
|
5 * Copyright (C) 2017-2018 Osimis S.A., Belgium
|
|
|
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
|
|
|
154 // https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
|
|
|
155 orthancType_ = ValueType_Utf8String;
|
|
|
156 break;
|
|
|
157
|
|
|
158 case 63:
|
|
|
159 orthancType_ = ValueType_BinaryString;
|
|
|
160 break;
|
|
|
161
|
|
|
162 default:
|
|
|
163 LOG(ERROR) << "Unsupported MySQL charset: " << field.charsetnr;
|
|
|
164 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
|
|
|
165 }
|
|
|
166
|
|
|
167 if (field.max_length > 0)
|
|
|
168 {
|
|
|
169 buffer_.resize(field.max_length);
|
|
|
170 }
|
|
|
171
|
|
|
172 break;
|
|
|
173
|
|
|
174 default:
|
|
|
175 LOG(ERROR) << "MYSQL_TYPE not implemented: " << field.type;
|
|
|
176 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
|
|
|
177 }
|
|
|
178 }
|
|
|
179
|
|
|
180 enum enum_field_types GetMysqlType() const
|
|
|
181 {
|
|
|
182 return mysqlType_;
|
|
|
183 }
|
|
|
184
|
|
|
185 ValueType GetOrthancType() const
|
|
|
186 {
|
|
|
187 return orthancType_;
|
|
|
188 }
|
|
|
189
|
|
|
190 void PrepareBind(MYSQL_BIND& bind)
|
|
|
191 {
|
|
|
192 memset(&bind, 0, sizeof(bind));
|
|
|
193
|
|
|
194 length_ = 0;
|
|
|
195
|
|
|
196 bind.buffer_length = buffer_.size();
|
|
|
197 bind.buffer_type = mysqlType_;
|
|
|
198 bind.is_null = &isNull_;
|
|
|
199 bind.length = &length_;
|
|
|
200
|
|
|
201 if (buffer_.empty())
|
|
|
202 {
|
|
|
203 // Only fetches the actual size of the field (*):
|
|
|
204 // mysql_stmt_fetch_column() must be invoked afterward
|
|
|
205 bind.buffer = 0;
|
|
|
206 isError_ = false;
|
|
|
207 }
|
|
|
208 else
|
|
|
209 {
|
|
|
210 bind.buffer = &buffer_[0];
|
|
|
211 bind.error = &isError_;
|
|
|
212 }
|
|
|
213 }
|
|
|
214
|
|
|
215
|
|
|
216 IValue* FetchValue(MySQLDatabase& database,
|
|
|
217 MYSQL_STMT& statement,
|
|
|
218 MYSQL_BIND& bind,
|
|
|
219 unsigned int column) const
|
|
|
220 {
|
|
|
221 if (isError_)
|
|
|
222 {
|
|
|
223 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
|
|
|
224 }
|
|
|
225 else if (isNull_)
|
|
|
226 {
|
|
|
227 return new NullValue;
|
|
|
228 }
|
|
|
229 else if (orthancType_ == ValueType_Integer64)
|
|
|
230 {
|
|
|
231 return CreateIntegerValue(bind);
|
|
|
232 }
|
|
|
233 else if (orthancType_ == ValueType_Utf8String ||
|
|
|
234 orthancType_ == ValueType_BinaryString)
|
|
|
235 {
|
|
|
236 std::string tmp;
|
|
|
237 tmp.resize(length_);
|
|
|
238
|
|
|
239 if (!tmp.empty())
|
|
|
240 {
|
|
|
241 if (buffer_.empty())
|
|
|
242 {
|
|
|
243 bind.buffer = &tmp[0];
|
|
|
244 bind.buffer_length = tmp.size();
|
|
|
245
|
|
|
246 database.CheckErrorCode(mysql_stmt_fetch_column(&statement, &bind, column, 0));
|
|
|
247 }
|
|
|
248 else if (tmp.size() <= buffer_.size())
|
|
|
249 {
|
|
|
250 memcpy(&tmp[0], &buffer_[0], length_);
|
|
|
251 }
|
|
|
252 else
|
|
|
253 {
|
|
|
254 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
|
|
|
255 }
|
|
|
256 }
|
|
|
257
|
|
|
258 if (orthancType_ == ValueType_Utf8String)
|
|
|
259 {
|
|
|
260 return new Utf8StringValue(tmp);
|
|
|
261 }
|
|
|
262 else
|
|
|
263 {
|
|
|
264 return new BinaryStringValue(tmp);
|
|
|
265 }
|
|
|
266 }
|
|
|
267 else
|
|
|
268 {
|
|
|
269 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
|
|
|
270 }
|
|
|
271 }
|
|
|
272 };
|
|
|
273
|
|
|
274
|
|
|
275 class MySQLStatement::ResultMetadata : public boost::noncopyable
|
|
|
276 {
|
|
|
277 private:
|
|
|
278 MYSQL_RES* metadata_;
|
|
|
279
|
|
|
280 public:
|
|
|
281 ResultMetadata(MySQLDatabase& db,
|
|
|
282 MySQLStatement& statement) :
|
|
|
283 metadata_(NULL)
|
|
|
284 {
|
|
|
285 metadata_ = mysql_stmt_result_metadata(statement.GetObject());
|
|
|
286 }
|
|
|
287
|
|
|
288 ~ResultMetadata()
|
|
|
289 {
|
|
|
290 if (metadata_ != NULL)
|
|
|
291 {
|
|
|
292 mysql_free_result(metadata_);
|
|
|
293 }
|
|
|
294 }
|
|
|
295
|
|
|
296 bool HasFields() const
|
|
|
297 {
|
|
|
298 return metadata_ != NULL;
|
|
|
299 }
|
|
|
300
|
|
|
301 size_t GetFieldsCount()
|
|
|
302 {
|
|
|
303 if (HasFields())
|
|
|
304 {
|
|
|
305 return mysql_num_fields(metadata_);
|
|
|
306 }
|
|
|
307 else
|
|
|
308 {
|
|
|
309 return 0;
|
|
|
310 }
|
|
|
311 }
|
|
|
312
|
|
|
313 MYSQL_RES* GetObject()
|
|
|
314 {
|
|
|
315 return metadata_;
|
|
|
316 }
|
|
|
317 };
|
|
|
318
|
|
|
319
|
|
|
320 void MySQLStatement::Close()
|
|
|
321 {
|
|
|
322 for (size_t i = 0; i < result_.size(); i++)
|
|
|
323 {
|
|
|
324 if (result_[i] != NULL)
|
|
|
325 {
|
|
|
326 delete result_[i];
|
|
|
327 }
|
|
|
328 }
|
|
|
329
|
|
|
330 if (statement_ != NULL)
|
|
|
331 {
|
|
|
332 mysql_stmt_close(statement_);
|
|
|
333 statement_ = NULL;
|
|
|
334 }
|
|
|
335 }
|
|
|
336
|
|
|
337
|
|
|
338 MySQLStatement::MySQLStatement(MySQLDatabase& db,
|
|
|
339 const Query& query) :
|
|
|
340 db_(db),
|
|
|
341 readOnly_(query.IsReadOnly()),
|
|
|
342 statement_(NULL),
|
|
|
343 formatter_(Dialect_MySQL)
|
|
|
344 {
|
|
|
345 std::string sql;
|
|
|
346 query.Format(sql, formatter_);
|
|
|
347
|
|
|
348 statement_ = mysql_stmt_init(db.GetObject());
|
|
|
349 if (statement_ == NULL)
|
|
|
350 {
|
|
|
351 db.LogError();
|
|
|
352 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
|
|
|
353 }
|
|
|
354
|
|
|
355 LOG(INFO) << "Preparing MySQL statement: " << sql;
|
|
|
356
|
|
|
357 db_.CheckErrorCode(mysql_stmt_prepare(statement_, sql.c_str(), sql.size()));
|
|
|
358
|
|
|
359 if (mysql_stmt_param_count(statement_) != formatter_.GetParametersCount())
|
|
|
360 {
|
|
|
361 Close();
|
|
|
362 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
|
|
|
363 }
|
|
|
364
|
|
|
365 try
|
|
|
366 {
|
|
|
367 ResultMetadata result(db, *this);
|
|
|
368
|
|
|
369 if (result.HasFields())
|
|
|
370 {
|
|
|
371 MYSQL_FIELD *field;
|
|
|
372 while ((field = mysql_fetch_field(result.GetObject())))
|
|
|
373 {
|
|
|
374 result_.push_back(new ResultField(*field));
|
|
|
375 }
|
|
|
376 }
|
|
|
377
|
|
|
378 if (result_.size() != result.GetFieldsCount())
|
|
|
379 {
|
|
|
380 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
|
|
|
381 }
|
|
|
382 }
|
|
|
383 catch (Orthanc::OrthancException&)
|
|
|
384 {
|
|
|
385 Close();
|
|
|
386 throw;
|
|
|
387 }
|
|
|
388
|
|
|
389 if (query.IsReadOnly())
|
|
|
390 {
|
|
|
391 unsigned long type = (unsigned long) CURSOR_TYPE_READ_ONLY;
|
|
|
392 mysql_stmt_attr_set(statement_, STMT_ATTR_CURSOR_TYPE, (void*) &type);
|
|
|
393 }
|
|
|
394 }
|
|
|
395
|
|
|
396
|
|
|
397 MYSQL_STMT* MySQLStatement::GetObject()
|
|
|
398 {
|
|
|
399 if (statement_ == NULL)
|
|
|
400 {
|
|
|
401 throw Orthanc::OrthancException(Orthanc::ErrorCode_BadSequenceOfCalls);
|
|
|
402 }
|
|
|
403 else
|
|
|
404 {
|
|
|
405 return statement_;
|
|
|
406 }
|
|
|
407 }
|
|
|
408
|
|
|
409
|
|
|
410 IValue* MySQLStatement::FetchResultField(size_t i)
|
|
|
411 {
|
|
|
412 if (i >= result_.size())
|
|
|
413 {
|
|
|
414 throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
|
|
|
415 }
|
|
|
416 else
|
|
|
417 {
|
|
|
418 assert(result_[i] != NULL);
|
|
|
419 return result_[i]->FetchValue(db_, *statement_, outputs_[i], i);
|
|
|
420 }
|
|
|
421 }
|
|
|
422
|
|
|
423
|
|
|
424 IResult* MySQLStatement::Execute(MySQLTransaction& transaction,
|
|
|
425 const Dictionary& parameters)
|
|
|
426 {
|
|
|
427 std::list<int> intParameters;
|
|
|
428 std::list<long long int> int64Parameters;
|
|
|
429
|
|
|
430 std::vector<MYSQL_BIND> inputs(formatter_.GetParametersCount());
|
|
|
431
|
|
|
432 for (size_t i = 0; i < inputs.size(); i++)
|
|
|
433 {
|
|
|
434 memset(&inputs[i], 0, sizeof(MYSQL_BIND));
|
|
|
435
|
|
|
436 const std::string& name = formatter_.GetParameterName(i);
|
|
|
437 if (!parameters.HasKey(name))
|
|
|
438 {
|
|
|
439 LOG(ERROR) << "Missing required parameter in a SQL query: " << name;
|
|
|
440 throw Orthanc::OrthancException(Orthanc::ErrorCode_InexistentItem);
|
|
|
441 }
|
|
|
442
|
|
|
443 ValueType type = formatter_.GetParameterType(i);
|
|
|
444
|
|
|
445 const IValue& value = parameters.GetValue(name);
|
|
|
446 if (value.GetType() != type)
|
|
|
447 {
|
|
|
448 LOG(ERROR) << "Bad type of argument provided to a SQL query: " << name;
|
|
|
449 throw Orthanc::OrthancException(Orthanc::ErrorCode_BadParameterType);
|
|
|
450 }
|
|
|
451
|
|
|
452 // https://dev.mysql.com/doc/refman/8.0/en/c-api-prepared-statement-type-codes.html
|
|
|
453 switch (type)
|
|
|
454 {
|
|
|
455 case ValueType_Integer64:
|
|
|
456 {
|
|
|
457 int64Parameters.push_back(dynamic_cast<const Integer64Value&>(value).GetValue());
|
|
|
458 inputs[i].buffer = &int64Parameters.back();
|
|
|
459 inputs[i].buffer_type = MYSQL_TYPE_LONGLONG;
|
|
|
460 break;
|
|
|
461 }
|
|
|
462
|
|
|
463 case ValueType_Utf8String:
|
|
|
464 {
|
|
|
465 const std::string& utf8 = dynamic_cast<const Utf8StringValue&>(value).GetContent();
|
|
|
466 inputs[i].buffer = const_cast<char*>(utf8.c_str());
|
|
|
467 inputs[i].buffer_length = utf8.size();
|
|
|
468 inputs[i].buffer_type = MYSQL_TYPE_STRING;
|
|
|
469 break;
|
|
|
470 }
|
|
|
471
|
|
|
472 case ValueType_BinaryString:
|
|
|
473 {
|
|
|
474 const std::string& content = dynamic_cast<const BinaryStringValue&>(value).GetContent();
|
|
|
475 inputs[i].buffer = const_cast<char*>(content.c_str());
|
|
|
476 inputs[i].buffer_length = content.size();
|
|
|
477 inputs[i].buffer_type = MYSQL_TYPE_BLOB;
|
|
|
478 break;
|
|
|
479 }
|
|
|
480
|
|
|
481 case ValueType_File:
|
|
|
482 {
|
|
|
483 const std::string& content = dynamic_cast<const FileValue&>(value).GetContent();
|
|
|
484 inputs[i].buffer = const_cast<char*>(content.c_str());
|
|
|
485 inputs[i].buffer_length = content.size();
|
|
|
486 inputs[i].buffer_type = MYSQL_TYPE_BLOB;
|
|
|
487 break;
|
|
|
488 }
|
|
|
489
|
|
|
490 case ValueType_Null:
|
|
|
491 {
|
|
|
492 inputs[i].buffer = NULL;
|
|
|
493 inputs[i].buffer_type = MYSQL_TYPE_NULL;
|
|
|
494 break;
|
|
|
495 }
|
|
|
496
|
|
|
497 default:
|
|
|
498 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
|
|
|
499 }
|
|
|
500 }
|
|
|
501
|
|
|
502 if (!inputs.empty())
|
|
|
503 {
|
|
|
504 db_.CheckErrorCode(mysql_stmt_bind_param(statement_, &inputs[0]));
|
|
|
505 }
|
|
|
506
|
|
|
507 db_.CheckErrorCode(mysql_stmt_execute(statement_));
|
|
|
508
|
|
|
509 outputs_.resize(result_.size());
|
|
|
510
|
|
|
511 for (size_t i = 0; i < result_.size(); i++)
|
|
|
512 {
|
|
|
513 assert(result_[i] != NULL);
|
|
|
514 result_[i]->PrepareBind(outputs_[i]);
|
|
|
515 }
|
|
|
516
|
|
|
517 if (!outputs_.empty())
|
|
|
518 {
|
|
|
519 db_.CheckErrorCode(mysql_stmt_bind_result(statement_, &outputs_[0]));
|
|
|
520 db_.CheckErrorCode(mysql_stmt_store_result(statement_));
|
|
|
521 }
|
|
|
522
|
|
|
523 return new MySQLResult(db_, *this);
|
|
|
524 }
|
|
|
525
|
|
|
526
|
|
|
527 void MySQLStatement::ExecuteWithoutResult(MySQLTransaction& transaction,
|
|
|
528 const Dictionary& parameters)
|
|
|
529 {
|
|
|
530 std::auto_ptr<IResult> dummy(Execute(transaction, parameters));
|
|
|
531 }
|
|
|
532 }
|
