Mercurial > hg > orthanc

comparison OrthancServer/DatabaseWrapperBase.cpp @ 1670:16955f8fec4d db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
refactoring: DatabaseWrapperBase
author Sebastien Jodogne <s.jodogne@gmail.com>
date Thu, 01 Oct 2015 14:03:07 +0200
parents
children 2f2e2ec17bc4
comparison
equal deleted inserted replaced
1669:a412ad57f0f9 1670:16955f8fec4d
1 /**
2 * Orthanc - A Lightweight, RESTful DICOM Store
3 * Copyright (C) 2012-2015 Sebastien Jodogne, Medical Physics
4 * Department, University Hospital of Liege, Belgium
5 *
6 * This program is free software: you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License as
8 * published by the Free Software Foundation, either version 3 of the
9 * License, or (at your option) any later version.
10 *
11 * In addition, as a special exception, the copyright holders of this
12 * program give permission to link the code of its release with the
13 * OpenSSL project's "OpenSSL" library (or with modified versions of it
14 * that use the same license as the "OpenSSL" library), and distribute
15 * the linked executables. You must obey the GNU General Public License
16 * in all respects for all of the code used other than "OpenSSL". If you
17 * modify file(s) with this exception, you may extend this exception to
18 * your version of the file(s), but you are not obligated to do so. If
19 * you do not wish to do so, delete this exception statement from your
20 * version. If you delete this exception statement from all source files
21 * in the program, then also delete it here.
22 *
23 * This program is distributed in the hope that it will be useful, but
24 * WITHOUT ANY WARRANTY; without even the implied warranty of
25 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
26 * General Public License for more details.
27 *
28 * You should have received a copy of the GNU General Public License
29 * along with this program. If not, see <http://www.gnu.org/licenses/>.
30 **/
31
32
33 #include "PrecompiledHeadersServer.h"
34 #include "DatabaseWrapperBase.h"
35
36 #include <stdio.h>
37
38 namespace Orthanc
39 {
40 void DatabaseWrapperBase::SetGlobalProperty(GlobalProperty property,
41 const std::string& value)
42 {
43 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO GlobalProperties VALUES(?, ?)");
44 s.BindInt(0, property);
45 s.BindString(1, value);
46 s.Run();
47 }
48
49 bool DatabaseWrapperBase::LookupGlobalProperty(std::string& target,
50 GlobalProperty property)
51 {
52 SQLite::Statement s(db_, SQLITE_FROM_HERE,
53 "SELECT value FROM GlobalProperties WHERE property=?");
54 s.BindInt(0, property);
55
56 if (!s.Step())
57 {
58 return false;
59 }
60 else
61 {
62 target = s.ColumnString(0);
63 return true;
64 }
65 }
66
67 int64_t DatabaseWrapperBase::CreateResource(const std::string& publicId,
68 ResourceType type)
69 {
70 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Resources VALUES(NULL, ?, ?, NULL)");
71 s.BindInt(0, type);
72 s.BindString(1, publicId);
73 s.Run();
74 return db_.GetLastInsertRowId();
75 }
76
77 bool DatabaseWrapperBase::LookupResource(int64_t& id,
78 ResourceType& type,
79 const std::string& publicId)
80 {
81 SQLite::Statement s(db_, SQLITE_FROM_HERE,
82 "SELECT internalId, resourceType FROM Resources WHERE publicId=?");
83 s.BindString(0, publicId);
84
85 if (!s.Step())
86 {
87 return false;
88 }
89 else
90 {
91 id = s.ColumnInt(0);
92 type = static_cast<ResourceType>(s.ColumnInt(1));
93
94 // Check whether there is a single resource with this public id
95 assert(!s.Step());
96
97 return true;
98 }
99 }
100
101 bool DatabaseWrapperBase::LookupParent(int64_t& parentId,
102 int64_t resourceId)
103 {
104 SQLite::Statement s(db_, SQLITE_FROM_HERE,
105 "SELECT parentId FROM Resources WHERE internalId=?");
106 s.BindInt64(0, resourceId);
107
108 if (!s.Step())
109 {
110 throw OrthancException(ErrorCode_UnknownResource);
111 }
112
113 if (s.ColumnIsNull(0))
114 {
115 return false;
116 }
117 else
118 {
119 parentId = s.ColumnInt(0);
120 return true;
121 }
122 }
123
124 std::string DatabaseWrapperBase::GetPublicId(int64_t resourceId)
125 {
126 SQLite::Statement s(db_, SQLITE_FROM_HERE,
127 "SELECT publicId FROM Resources WHERE internalId=?");
128 s.BindInt64(0, resourceId);
129
130 if (!s.Step())
131 {
132 throw OrthancException(ErrorCode_UnknownResource);
133 }
134
135 return s.ColumnString(0);
136 }
137
138
139 ResourceType DatabaseWrapperBase::GetResourceType(int64_t resourceId)
140 {
141 SQLite::Statement s(db_, SQLITE_FROM_HERE,
142 "SELECT resourceType FROM Resources WHERE internalId=?");
143 s.BindInt64(0, resourceId);
144
145 if (!s.Step())
146 {
147 throw OrthancException(ErrorCode_UnknownResource);
148 }
149
150 return static_cast<ResourceType>(s.ColumnInt(0));
151 }
152
153
154 void DatabaseWrapperBase::AttachChild(int64_t parent,
155 int64_t child)
156 {
157 SQLite::Statement s(db_, SQLITE_FROM_HERE, "UPDATE Resources SET parentId = ? WHERE internalId = ?");
158 s.BindInt64(0, parent);
159 s.BindInt64(1, child);
160 s.Run();
161 }
162
163
164 void DatabaseWrapperBase::SetMetadata(int64_t id,
165 MetadataType type,
166 const std::string& value)
167 {
168 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO Metadata VALUES(?, ?, ?)");
169 s.BindInt64(0, id);
170 s.BindInt(1, type);
171 s.BindString(2, value);
172 s.Run();
173 }
174
175 void DatabaseWrapperBase::DeleteMetadata(int64_t id,
176 MetadataType type)
177 {
178 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM Metadata WHERE id=? and type=?");
179 s.BindInt64(0, id);
180 s.BindInt(1, type);
181 s.Run();
182 }
183
184 bool DatabaseWrapperBase::LookupMetadata(std::string& target,
185 int64_t id,
186 MetadataType type)
187 {
188 SQLite::Statement s(db_, SQLITE_FROM_HERE,
189 "SELECT value FROM Metadata WHERE id=? AND type=?");
190 s.BindInt64(0, id);
191 s.BindInt(1, type);
192
193 if (!s.Step())
194 {
195 return false;
196 }
197 else
198 {
199 target = s.ColumnString(0);
200 return true;
201 }
202 }
203
204 void DatabaseWrapperBase::ListAvailableMetadata(std::list<MetadataType>& target,
205 int64_t id)
206 {
207 target.clear();
208
209 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT type FROM Metadata WHERE id=?");
210 s.BindInt64(0, id);
211
212 while (s.Step())
213 {
214 target.push_back(static_cast<MetadataType>(s.ColumnInt(0)));
215 }
216 }
217
218
219 void DatabaseWrapperBase::AddAttachment(int64_t id,
220 const FileInfo& attachment)
221 {
222 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO AttachedFiles VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
223 s.BindInt64(0, id);
224 s.BindInt(1, attachment.GetContentType());
225 s.BindString(2, attachment.GetUuid());
226 s.BindInt64(3, attachment.GetCompressedSize());
227 s.BindInt64(4, attachment.GetUncompressedSize());
228 s.BindInt(5, attachment.GetCompressionType());
229 s.BindString(6, attachment.GetUncompressedMD5());
230 s.BindString(7, attachment.GetCompressedMD5());
231 s.Run();
232 }
233
234
235 void DatabaseWrapperBase::DeleteAttachment(int64_t id,
236 FileContentType attachment)
237 {
238 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM AttachedFiles WHERE id=? AND fileType=?");
239 s.BindInt64(0, id);
240 s.BindInt(1, attachment);
241 s.Run();
242 }
243
244
245
246 void DatabaseWrapperBase::ListAvailableAttachments(std::list<FileContentType>& target,
247 int64_t id)
248 {
249 target.clear();
250
251 SQLite::Statement s(db_, SQLITE_FROM_HERE,
252 "SELECT fileType FROM AttachedFiles WHERE id=?");
253 s.BindInt64(0, id);
254
255 while (s.Step())
256 {
257 target.push_back(static_cast<FileContentType>(s.ColumnInt(0)));
258 }
259 }
260
261 bool DatabaseWrapperBase::LookupAttachment(FileInfo& attachment,
262 int64_t id,
263 FileContentType contentType)
264 {
265 SQLite::Statement s(db_, SQLITE_FROM_HERE,
266 "SELECT uuid, uncompressedSize, compressionType, compressedSize, uncompressedMD5, compressedMD5 FROM AttachedFiles WHERE id=? AND fileType=?");
267 s.BindInt64(0, id);
268 s.BindInt(1, contentType);
269
270 if (!s.Step())
271 {
272 return false;
273 }
274 else
275 {
276 attachment = FileInfo(s.ColumnString(0),
277 contentType,
278 s.ColumnInt64(1),
279 s.ColumnString(4),
280 static_cast<CompressionType>(s.ColumnInt(2)),
281 s.ColumnInt64(3),
282 s.ColumnString(5));
283 return true;
284 }
285 }
286
287
288 void DatabaseWrapperBase::ClearMainDicomTags(int64_t id)
289 {
290 {
291 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM DicomIdentifiers WHERE id=?");
292 s.BindInt64(0, id);
293 s.Run();
294 }
295
296 {
297 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM MainDicomTags WHERE id=?");
298 s.BindInt64(0, id);
299 s.Run();
300 }
301 }
302
303
304 static void SetMainDicomTagsInternal(SQLite::Statement& s,
305 int64_t id,
306 const DicomTag& tag,
307 const std::string& value)
308 {
309 s.BindInt64(0, id);
310 s.BindInt(1, tag.GetGroup());
311 s.BindInt(2, tag.GetElement());
312 s.BindString(3, value);
313 s.Run();
314 }
315
316
317 void DatabaseWrapperBase::SetMainDicomTag(int64_t id,
318 const DicomTag& tag,
319 const std::string& value)
320 {
321 if (tag.IsIdentifier())
322 {
323 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO DicomIdentifiers VALUES(?, ?, ?, ?)");
324 SetMainDicomTagsInternal(s, id, tag, value);
325 }
326 else
327 {
328 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO MainDicomTags VALUES(?, ?, ?, ?)");
329 SetMainDicomTagsInternal(s, id, tag, value);
330 }
331 }
332
333 void DatabaseWrapperBase::GetMainDicomTags(DicomMap& map,
334 int64_t id)
335 {
336 map.Clear();
337
338 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM MainDicomTags WHERE id=?");
339 s.BindInt64(0, id);
340 while (s.Step())
341 {
342 map.SetValue(s.ColumnInt(1),
343 s.ColumnInt(2),
344 s.ColumnString(3));
345 }
346
347 SQLite::Statement s2(db_, SQLITE_FROM_HERE, "SELECT * FROM DicomIdentifiers WHERE id=?");
348 s2.BindInt64(0, id);
349 while (s2.Step())
350 {
351 map.SetValue(s2.ColumnInt(1),
352 s2.ColumnInt(2),
353 s2.ColumnString(3));
354 }
355 }
356
357
358
359 void DatabaseWrapperBase::GetChildrenPublicId(std::list<std::string>& target,
360 int64_t id)
361 {
362 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT a.publicId FROM Resources AS a, Resources AS b "
363 "WHERE a.parentId = b.internalId AND b.internalId = ?");
364 s.BindInt64(0, id);
365
366 target.clear();
367
368 while (s.Step())
369 {
370 target.push_back(s.ColumnString(0));
371 }
372 }
373
374
375 void DatabaseWrapperBase::GetChildrenInternalId(std::list<int64_t>& target,
376 int64_t id)
377 {
378 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT a.internalId FROM Resources AS a, Resources AS b "
379 "WHERE a.parentId = b.internalId AND b.internalId = ?");
380 s.BindInt64(0, id);
381
382 target.clear();
383
384 while (s.Step())
385 {
386 target.push_back(s.ColumnInt64(0));
387 }
388 }
389
390
391 void DatabaseWrapperBase::LogChange(int64_t internalId,
392 const ServerIndexChange& change)
393 {
394 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Changes VALUES(NULL, ?, ?, ?, ?)");
395 s.BindInt(0, change.GetChangeType());
396 s.BindInt64(1, internalId);
397 s.BindInt(2, change.GetResourceType());
398 s.BindString(3, change.GetDate());
399 s.Run();
400 }
401
402
403 void DatabaseWrapperBase::GetChangesInternal(std::list<ServerIndexChange>& target,
404 bool& done,
405 SQLite::Statement& s,
406 uint32_t maxResults)
407 {
408 target.clear();
409
410 while (target.size() < maxResults && s.Step())
411 {
412 int64_t seq = s.ColumnInt64(0);
413 ChangeType changeType = static_cast<ChangeType>(s.ColumnInt(1));
414 ResourceType resourceType = static_cast<ResourceType>(s.ColumnInt(3));
415 const std::string& date = s.ColumnString(4);
416
417 int64_t internalId = s.ColumnInt64(2);
418 std::string publicId = GetPublicId(internalId);
419
420 target.push_back(ServerIndexChange(seq, changeType, resourceType, publicId, date));
421 }
422
423 done = !(target.size() == maxResults && s.Step());
424 }
425
426
427 void DatabaseWrapperBase::GetChanges(std::list<ServerIndexChange>& target,
428 bool& done,
429 int64_t since,
430 uint32_t maxResults)
431 {
432 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes WHERE seq>? ORDER BY seq LIMIT ?");
433 s.BindInt64(0, since);
434 s.BindInt(1, maxResults + 1);
435 GetChangesInternal(target, done, s, maxResults);
436 }
437
438 void DatabaseWrapperBase::GetLastChange(std::list<ServerIndexChange>& target)
439 {
440 bool done; // Ignored
441 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes ORDER BY seq DESC LIMIT 1");
442 GetChangesInternal(target, done, s, 1);
443 }
444
445
446 void DatabaseWrapperBase::LogExportedResource(const ExportedResource& resource)
447 {
448 SQLite::Statement s(db_, SQLITE_FROM_HERE,
449 "INSERT INTO ExportedResources VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?)");
450
451 s.BindInt(0, resource.GetResourceType());
452 s.BindString(1, resource.GetPublicId());
453 s.BindString(2, resource.GetModality());
454 s.BindString(3, resource.GetPatientId());
455 s.BindString(4, resource.GetStudyInstanceUid());
456 s.BindString(5, resource.GetSeriesInstanceUid());
457 s.BindString(6, resource.GetSopInstanceUid());
458 s.BindString(7, resource.GetDate());
459 s.Run();
460 }
461
462
463 void DatabaseWrapperBase::GetExportedResourcesInternal(std::list<ExportedResource>& target,
464 bool& done,
465 SQLite::Statement& s,
466 uint32_t maxResults)
467 {
468 target.clear();
469
470 while (target.size() < maxResults && s.Step())
471 {
472 int64_t seq = s.ColumnInt64(0);
473 ResourceType resourceType = static_cast<ResourceType>(s.ColumnInt(1));
474 std::string publicId = s.ColumnString(2);
475
476 ExportedResource resource(seq,
477 resourceType,
478 publicId,
479 s.ColumnString(3), // modality
480 s.ColumnString(8), // date
481 s.ColumnString(4), // patient ID
482 s.ColumnString(5), // study instance UID
483 s.ColumnString(6), // series instance UID
484 s.ColumnString(7)); // sop instance UID
485
486 target.push_back(resource);
487 }
488
489 done = !(target.size() == maxResults && s.Step());
490 }
491
492
493 void DatabaseWrapperBase::GetExportedResources(std::list<ExportedResource>& target,
494 bool& done,
495 int64_t since,
496 uint32_t maxResults)
497 {
498 SQLite::Statement s(db_, SQLITE_FROM_HERE,
499 "SELECT * FROM ExportedResources WHERE seq>? ORDER BY seq LIMIT ?");
500 s.BindInt64(0, since);
501 s.BindInt(1, maxResults + 1);
502 GetExportedResourcesInternal(target, done, s, maxResults);
503 }
504
505
506 void DatabaseWrapperBase::GetLastExportedResource(std::list<ExportedResource>& target)
507 {
508 bool done; // Ignored
509 SQLite::Statement s(db_, SQLITE_FROM_HERE,
510 "SELECT * FROM ExportedResources ORDER BY seq DESC LIMIT 1");
511 GetExportedResourcesInternal(target, done, s, 1);
512 }
513
514
515
516 uint64_t DatabaseWrapperBase::GetTotalCompressedSize()
517 {
518 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(compressedSize) FROM AttachedFiles");
519 s.Run();
520 return static_cast<uint64_t>(s.ColumnInt64(0));
521 }
522
523
524 uint64_t DatabaseWrapperBase::GetTotalUncompressedSize()
525 {
526 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(uncompressedSize) FROM AttachedFiles");
527 s.Run();
528 return static_cast<uint64_t>(s.ColumnInt64(0));
529 }
530
531 void DatabaseWrapperBase::GetAllPublicIds(std::list<std::string>& target,
532 ResourceType resourceType)
533 {
534 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId FROM Resources WHERE resourceType=?");
535 s.BindInt(0, resourceType);
536
537 target.clear();
538 while (s.Step())
539 {
540 target.push_back(s.ColumnString(0));
541 }
542 }
543
544 void DatabaseWrapperBase::GetAllPublicIds(std::list<std::string>& target,
545 ResourceType resourceType,
546 size_t since,
547 size_t limit)
548 {
549 if (limit == 0)
550 {
551 target.clear();
552 return;
553 }
554
555 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId FROM Resources WHERE resourceType=? LIMIT ? OFFSET ?");
556 s.BindInt(0, resourceType);
557 s.BindInt64(1, limit);
558 s.BindInt64(2, since);
559
560 target.clear();
561 while (s.Step())
562 {
563 target.push_back(s.ColumnString(0));
564 }
565 }
566
567
568 uint64_t DatabaseWrapperBase::GetResourceCount(ResourceType resourceType)
569 {
570 SQLite::Statement s(db_, SQLITE_FROM_HERE,
571 "SELECT COUNT(*) FROM Resources WHERE resourceType=?");
572 s.BindInt(0, resourceType);
573
574 if (!s.Step())
575 {
576 throw OrthancException(ErrorCode_InternalError);
577 }
578
579 int64_t c = s.ColumnInt(0);
580 assert(!s.Step());
581
582 return c;
583 }
584
585
586 bool DatabaseWrapperBase::SelectPatientToRecycle(int64_t& internalId)
587 {
588 SQLite::Statement s(db_, SQLITE_FROM_HERE,
589 "SELECT patientId FROM PatientRecyclingOrder ORDER BY seq ASC LIMIT 1");
590
591 if (!s.Step())
592 {
593 // No patient remaining or all the patients are protected
594 return false;
595 }
596 else
597 {
598 internalId = s.ColumnInt(0);
599 return true;
600 }
601 }
602
603 bool DatabaseWrapperBase::SelectPatientToRecycle(int64_t& internalId,
604 int64_t patientIdToAvoid)
605 {
606 SQLite::Statement s(db_, SQLITE_FROM_HERE,
607 "SELECT patientId FROM PatientRecyclingOrder "
608 "WHERE patientId != ? ORDER BY seq ASC LIMIT 1");
609 s.BindInt64(0, patientIdToAvoid);
610
611 if (!s.Step())
612 {
613 // No patient remaining or all the patients are protected
614 return false;
615 }
616 else
617 {
618 internalId = s.ColumnInt(0);
619 return true;
620 }
621 }
622
623 bool DatabaseWrapperBase::IsProtectedPatient(int64_t internalId)
624 {
625 SQLite::Statement s(db_, SQLITE_FROM_HERE,
626 "SELECT * FROM PatientRecyclingOrder WHERE patientId = ?");
627 s.BindInt64(0, internalId);
628 return !s.Step();
629 }
630
631 void DatabaseWrapperBase::SetProtectedPatient(int64_t internalId,
632 bool isProtected)
633 {
634 if (isProtected)
635 {
636 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM PatientRecyclingOrder WHERE patientId=?");
637 s.BindInt64(0, internalId);
638 s.Run();
639 }
640 else if (IsProtectedPatient(internalId))
641 {
642 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO PatientRecyclingOrder VALUES(NULL, ?)");
643 s.BindInt64(0, internalId);
644 s.Run();
645 }
646 else
647 {
648 // Nothing to do: The patient is already unprotected
649 }
650 }
651
652
653
654 bool DatabaseWrapperBase::IsExistingResource(int64_t internalId)
655 {
656 SQLite::Statement s(db_, SQLITE_FROM_HERE,
657 "SELECT * FROM Resources WHERE internalId=?");
658 s.BindInt64(0, internalId);
659 return s.Step();
660 }
661
662
663 void DatabaseWrapperBase::LookupIdentifier(std::list<int64_t>& target,
664 const DicomTag& tag,
665 const std::string& value)
666 {
667 if (!tag.IsIdentifier())
668 {
669 throw OrthancException(ErrorCode_ParameterOutOfRange);
670 }
671
672 SQLite::Statement s(db_, SQLITE_FROM_HERE,
673 "SELECT id FROM DicomIdentifiers WHERE tagGroup=? AND tagElement=? and value=?");
674
675 s.BindInt(0, tag.GetGroup());
676 s.BindInt(1, tag.GetElement());
677 s.BindString(2, value);
678
679 target.clear();
680
681 while (s.Step())
682 {
683 target.push_back(s.ColumnInt64(0));
684 }
685 }
686
687
688 void DatabaseWrapperBase::LookupIdentifier(std::list<int64_t>& target,
689 const std::string& value)
690 {
691 SQLite::Statement s(db_, SQLITE_FROM_HERE,
692 "SELECT id FROM DicomIdentifiers WHERE value=?");
693
694 s.BindString(0, value);
695
696 target.clear();
697
698 while (s.Step())
699 {
700 target.push_back(s.ColumnInt64(0));
701 }
702 }
703 }