Mercurial > hg > orthanc-databases

comparison Framework/MySQL/MySQLStatement.cpp @ 0:7cea966b6829

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
initial commit
author Sebastien Jodogne <s.jodogne@gmail.com>
date Wed, 04 Jul 2018 08:16:29 +0200
parents
children eff482803d30
comparison
equal deleted inserted replaced
-1:000000000000 0:7cea966b6829
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 }