Mercurial > hg > orthanc

comparison OrthancServer/SQLiteDatabaseWrapper.cpp @ 3017:517fc4767ae0 db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
renamed class DatabaseWrapper as SQLiteDatabaseWrapper
author Sebastien Jodogne <s.jodogne@gmail.com>
date Fri, 14 Dec 2018 14:55:49 +0100
parents OrthancServer/DatabaseWrapper.cpp@bbfd95a0c429
children e3b5c07146a3
comparison
equal deleted inserted replaced
3016:777762336381 3017:517fc4767ae0
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 General Public License as
9 * published by the Free Software Foundation, either version 3 of the
10 * License, or (at your option) any later version.
11 *
12 * In addition, as a special exception, the copyright holders of this
13 * program give permission to link the code of its release with the
14 * OpenSSL project's "OpenSSL" library (or with modified versions of it
15 * that use the same license as the "OpenSSL" library), and distribute
16 * the linked executables. You must obey the GNU General Public License
17 * in all respects for all of the code used other than "OpenSSL". If you
18 * modify file(s) with this exception, you may extend this exception to
19 * your version of the file(s), but you are not obligated to do so. If
20 * you do not wish to do so, delete this exception statement from your
21 * version. If you delete this exception statement from all source files
22 * in the program, then also delete it here.
23 *
24 * This program is distributed in the hope that it will be useful, but
25 * WITHOUT ANY WARRANTY; without even the implied warranty of
26 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
27 * General Public License for more details.
28 *
29 * You should have received a copy of the GNU General Public License
30 * along with this program. If not, see <http://www.gnu.org/licenses/>.
31 **/
32
33
34 #include "PrecompiledHeadersServer.h"
35 #include "SQLiteDatabaseWrapper.h"
36
37 #include "../Core/DicomFormat/DicomArray.h"
38 #include "../Core/Logging.h"
39 #include "EmbeddedResources.h"
40 #include "ServerToolbox.h"
41
42 #include <stdio.h>
43 #include <boost/lexical_cast.hpp>
44
45 namespace Orthanc
46 {
47 namespace Internals
48 {
49 class SignalFileDeleted : public SQLite::IScalarFunction
50 {
51 private:
52 IDatabaseListener& listener_;
53
54 public:
55 SignalFileDeleted(IDatabaseListener& listener) :
56 listener_(listener)
57 {
58 }
59
60 virtual const char* GetName() const
61 {
62 return "SignalFileDeleted";
63 }
64
65 virtual unsigned int GetCardinality() const
66 {
67 return 7;
68 }
69
70 virtual void Compute(SQLite::FunctionContext& context)
71 {
72 std::string uncompressedMD5, compressedMD5;
73
74 if (!context.IsNullValue(5))
75 {
76 uncompressedMD5 = context.GetStringValue(5);
77 }
78
79 if (!context.IsNullValue(6))
80 {
81 compressedMD5 = context.GetStringValue(6);
82 }
83
84 FileInfo info(context.GetStringValue(0),
85 static_cast<FileContentType>(context.GetIntValue(1)),
86 static_cast<uint64_t>(context.GetInt64Value(2)),
87 uncompressedMD5,
88 static_cast<CompressionType>(context.GetIntValue(3)),
89 static_cast<uint64_t>(context.GetInt64Value(4)),
90 compressedMD5);
91
92 listener_.SignalFileDeleted(info);
93 }
94 };
95
96 class SignalResourceDeleted : public SQLite::IScalarFunction
97 {
98 private:
99 IDatabaseListener& listener_;
100
101 public:
102 SignalResourceDeleted(IDatabaseListener& listener) :
103 listener_(listener)
104 {
105 }
106
107 virtual const char* GetName() const
108 {
109 return "SignalResourceDeleted";
110 }
111
112 virtual unsigned int GetCardinality() const
113 {
114 return 2;
115 }
116
117 virtual void Compute(SQLite::FunctionContext& context)
118 {
119 ResourceType type = static_cast<ResourceType>(context.GetIntValue(1));
120 ServerIndexChange change(ChangeType_Deleted, type, context.GetStringValue(0));
121 listener_.SignalChange(change);
122 }
123 };
124
125 class SignalRemainingAncestor : public SQLite::IScalarFunction
126 {
127 private:
128 bool hasRemainingAncestor_;
129 std::string remainingPublicId_;
130 ResourceType remainingType_;
131
132 public:
133 SignalRemainingAncestor() :
134 hasRemainingAncestor_(false)
135 {
136 }
137
138 void Reset()
139 {
140 hasRemainingAncestor_ = false;
141 }
142
143 virtual const char* GetName() const
144 {
145 return "SignalRemainingAncestor";
146 }
147
148 virtual unsigned int GetCardinality() const
149 {
150 return 2;
151 }
152
153 virtual void Compute(SQLite::FunctionContext& context)
154 {
155 VLOG(1) << "There exists a remaining ancestor with public ID \""
156 << context.GetStringValue(0)
157 << "\" of type "
158 << context.GetIntValue(1);
159
160 if (!hasRemainingAncestor_ ||
161 remainingType_ >= context.GetIntValue(1))
162 {
163 hasRemainingAncestor_ = true;
164 remainingPublicId_ = context.GetStringValue(0);
165 remainingType_ = static_cast<ResourceType>(context.GetIntValue(1));
166 }
167 }
168
169 bool HasRemainingAncestor() const
170 {
171 return hasRemainingAncestor_;
172 }
173
174 const std::string& GetRemainingAncestorId() const
175 {
176 assert(hasRemainingAncestor_);
177 return remainingPublicId_;
178 }
179
180 ResourceType GetRemainingAncestorType() const
181 {
182 assert(hasRemainingAncestor_);
183 return remainingType_;
184 }
185 };
186 }
187
188
189 void SQLiteDatabaseWrapper::GetChangesInternal(std::list<ServerIndexChange>& target,
190 bool& done,
191 SQLite::Statement& s,
192 uint32_t maxResults)
193 {
194 target.clear();
195
196 while (target.size() < maxResults && s.Step())
197 {
198 int64_t seq = s.ColumnInt64(0);
199 ChangeType changeType = static_cast<ChangeType>(s.ColumnInt(1));
200 ResourceType resourceType = static_cast<ResourceType>(s.ColumnInt(3));
201 const std::string& date = s.ColumnString(4);
202
203 int64_t internalId = s.ColumnInt64(2);
204 std::string publicId = GetPublicId(internalId);
205
206 target.push_back(ServerIndexChange(seq, changeType, resourceType, publicId, date));
207 }
208
209 done = !(target.size() == maxResults && s.Step());
210 }
211
212
213 void SQLiteDatabaseWrapper::GetExportedResourcesInternal(std::list<ExportedResource>& target,
214 bool& done,
215 SQLite::Statement& s,
216 uint32_t maxResults)
217 {
218 target.clear();
219
220 while (target.size() < maxResults && s.Step())
221 {
222 int64_t seq = s.ColumnInt64(0);
223 ResourceType resourceType = static_cast<ResourceType>(s.ColumnInt(1));
224 std::string publicId = s.ColumnString(2);
225
226 ExportedResource resource(seq,
227 resourceType,
228 publicId,
229 s.ColumnString(3), // modality
230 s.ColumnString(8), // date
231 s.ColumnString(4), // patient ID
232 s.ColumnString(5), // study instance UID
233 s.ColumnString(6), // series instance UID
234 s.ColumnString(7)); // sop instance UID
235
236 target.push_back(resource);
237 }
238
239 done = !(target.size() == maxResults && s.Step());
240 }
241
242
243 void SQLiteDatabaseWrapper::GetChildren(std::list<std::string>& childrenPublicIds,
244 int64_t id)
245 {
246 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId FROM Resources WHERE parentId=?");
247 s.BindInt64(0, id);
248
249 childrenPublicIds.clear();
250 while (s.Step())
251 {
252 childrenPublicIds.push_back(s.ColumnString(0));
253 }
254 }
255
256
257 void SQLiteDatabaseWrapper::DeleteResource(int64_t id)
258 {
259 signalRemainingAncestor_->Reset();
260
261 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM Resources WHERE internalId=?");
262 s.BindInt64(0, id);
263 s.Run();
264
265 if (signalRemainingAncestor_->HasRemainingAncestor() &&
266 listener_ != NULL)
267 {
268 listener_->SignalRemainingAncestor(signalRemainingAncestor_->GetRemainingAncestorType(),
269 signalRemainingAncestor_->GetRemainingAncestorId());
270 }
271 }
272
273
274 bool SQLiteDatabaseWrapper::GetParentPublicId(std::string& target,
275 int64_t id)
276 {
277 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT a.publicId FROM Resources AS a, Resources AS b "
278 "WHERE a.internalId = b.parentId AND b.internalId = ?");
279 s.BindInt64(0, id);
280
281 if (s.Step())
282 {
283 target = s.ColumnString(0);
284 return true;
285 }
286 else
287 {
288 return false;
289 }
290 }
291
292
293 int64_t SQLiteDatabaseWrapper::GetTableRecordCount(const std::string& table)
294 {
295 char buf[128];
296 sprintf(buf, "SELECT COUNT(*) FROM %s", table.c_str());
297 SQLite::Statement s(db_, buf);
298
299 if (!s.Step())
300 {
301 throw OrthancException(ErrorCode_InternalError);
302 }
303
304 int64_t c = s.ColumnInt(0);
305 assert(!s.Step());
306
307 return c;
308 }
309
310
311 SQLiteDatabaseWrapper::SQLiteDatabaseWrapper(const std::string& path) :
312 listener_(NULL),
313 signalRemainingAncestor_(NULL),
314 version_(0)
315 {
316 db_.Open(path);
317 }
318
319
320 SQLiteDatabaseWrapper::SQLiteDatabaseWrapper() :
321 listener_(NULL),
322 signalRemainingAncestor_(NULL),
323 version_(0)
324 {
325 db_.OpenInMemory();
326 }
327
328
329 void SQLiteDatabaseWrapper::Open()
330 {
331 db_.Execute("PRAGMA ENCODING=\"UTF-8\";");
332
333 // Performance tuning of SQLite with PRAGMAs
334 // http://www.sqlite.org/pragma.html
335 db_.Execute("PRAGMA SYNCHRONOUS=NORMAL;");
336 db_.Execute("PRAGMA JOURNAL_MODE=WAL;");
337 db_.Execute("PRAGMA LOCKING_MODE=EXCLUSIVE;");
338 db_.Execute("PRAGMA WAL_AUTOCHECKPOINT=1000;");
339 //db_.Execute("PRAGMA TEMP_STORE=memory");
340
341 if (!db_.DoesTableExist("GlobalProperties"))
342 {
343 LOG(INFO) << "Creating the database";
344 std::string query;
345 EmbeddedResources::GetFileResource(query, EmbeddedResources::PREPARE_DATABASE);
346 db_.Execute(query);
347 }
348
349 // Check the version of the database
350 std::string tmp;
351 if (!LookupGlobalProperty(tmp, GlobalProperty_DatabaseSchemaVersion))
352 {
353 tmp = "Unknown";
354 }
355
356 bool ok = false;
357 try
358 {
359 LOG(INFO) << "Version of the Orthanc database: " << tmp;
360 version_ = boost::lexical_cast<unsigned int>(tmp);
361 ok = true;
362 }
363 catch (boost::bad_lexical_cast&)
364 {
365 }
366
367 if (!ok)
368 {
369 throw OrthancException(ErrorCode_IncompatibleDatabaseVersion,
370 "Incompatible version of the Orthanc database: " + tmp);
371 }
372
373 signalRemainingAncestor_ = new Internals::SignalRemainingAncestor;
374 db_.Register(signalRemainingAncestor_);
375 }
376
377
378 static void ExecuteUpgradeScript(SQLite::Connection& db,
379 EmbeddedResources::FileResourceId script)
380 {
381 std::string upgrade;
382 EmbeddedResources::GetFileResource(upgrade, script);
383 db.BeginTransaction();
384 db.Execute(upgrade);
385 db.CommitTransaction();
386 }
387
388
389 void SQLiteDatabaseWrapper::Upgrade(unsigned int targetVersion,
390 IStorageArea& storageArea)
391 {
392 if (targetVersion != 6)
393 {
394 throw OrthancException(ErrorCode_IncompatibleDatabaseVersion);
395 }
396
397 // This version of Orthanc is only compatible with versions 3, 4,
398 // 5 and 6 of the DB schema
399 if (version_ != 3 &&
400 version_ != 4 &&
401 version_ != 5 &&
402 version_ != 6)
403 {
404 throw OrthancException(ErrorCode_IncompatibleDatabaseVersion);
405 }
406
407 if (version_ == 3)
408 {
409 LOG(WARNING) << "Upgrading database version from 3 to 4";
410 ExecuteUpgradeScript(db_, EmbeddedResources::UPGRADE_DATABASE_3_TO_4);
411 version_ = 4;
412 }
413
414 if (version_ == 4)
415 {
416 LOG(WARNING) << "Upgrading database version from 4 to 5";
417 ExecuteUpgradeScript(db_, EmbeddedResources::UPGRADE_DATABASE_4_TO_5);
418 version_ = 5;
419 }
420
421 if (version_ == 5)
422 {
423 LOG(WARNING) << "Upgrading database version from 5 to 6";
424 // No change in the DB schema, the step from version 5 to 6 only
425 // consists in reconstructing the main DICOM tags information
426 // (as more tags got included).
427 db_.BeginTransaction();
428 ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Patient);
429 ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Study);
430 ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Series);
431 ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Instance);
432 db_.Execute("UPDATE GlobalProperties SET value=\"6\" WHERE property=" +
433 boost::lexical_cast<std::string>(GlobalProperty_DatabaseSchemaVersion) + ";");
434 db_.CommitTransaction();
435 version_ = 6;
436 }
437 }
438
439
440 void SQLiteDatabaseWrapper::SetListener(IDatabaseListener& listener)
441 {
442 listener_ = &listener;
443 db_.Register(new Internals::SignalFileDeleted(listener));
444 db_.Register(new Internals::SignalResourceDeleted(listener));
445 }
446
447
448 void SQLiteDatabaseWrapper::ClearTable(const std::string& tableName)
449 {
450 db_.Execute("DELETE FROM " + tableName);
451 }
452
453
454 bool SQLiteDatabaseWrapper::LookupParent(int64_t& parentId,
455 int64_t resourceId)
456 {
457 SQLite::Statement s(db_, SQLITE_FROM_HERE,
458 "SELECT parentId FROM Resources WHERE internalId=?");
459 s.BindInt64(0, resourceId);
460
461 if (!s.Step())
462 {
463 throw OrthancException(ErrorCode_UnknownResource);
464 }
465
466 if (s.ColumnIsNull(0))
467 {
468 return false;
469 }
470 else
471 {
472 parentId = s.ColumnInt(0);
473 return true;
474 }
475 }
476
477
478 ResourceType SQLiteDatabaseWrapper::GetResourceType(int64_t resourceId)
479 {
480 SQLite::Statement s(db_, SQLITE_FROM_HERE,
481 "SELECT resourceType FROM Resources WHERE internalId=?");
482 s.BindInt64(0, resourceId);
483
484 if (s.Step())
485 {
486 return static_cast<ResourceType>(s.ColumnInt(0));
487 }
488 else
489 {
490 throw OrthancException(ErrorCode_UnknownResource);
491 }
492 }
493
494
495 std::string SQLiteDatabaseWrapper::GetPublicId(int64_t resourceId)
496 {
497 SQLite::Statement s(db_, SQLITE_FROM_HERE,
498 "SELECT publicId FROM Resources WHERE internalId=?");
499 s.BindInt64(0, resourceId);
500
501 if (s.Step())
502 {
503 return s.ColumnString(0);
504 }
505 else
506 {
507 throw OrthancException(ErrorCode_UnknownResource);
508 }
509 }
510
511
512 void SQLiteDatabaseWrapper::GetChanges(std::list<ServerIndexChange>& target /*out*/,
513 bool& done /*out*/,
514 int64_t since,
515 uint32_t maxResults)
516 {
517 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes WHERE seq>? ORDER BY seq LIMIT ?");
518 s.BindInt64(0, since);
519 s.BindInt(1, maxResults + 1);
520 GetChangesInternal(target, done, s, maxResults);
521 }
522
523
524 void SQLiteDatabaseWrapper::GetLastChange(std::list<ServerIndexChange>& target /*out*/)
525 {
526 bool done; // Ignored
527 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes ORDER BY seq DESC LIMIT 1");
528 GetChangesInternal(target, done, s, 1);
529 }
530
531
532 void SQLiteDatabaseWrapper::GetAllMetadata(std::map<MetadataType, std::string>& target,
533 int64_t id)
534 {
535 target.clear();
536
537 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT type, value FROM Metadata WHERE id=?");
538 s.BindInt64(0, id);
539
540 while (s.Step())
541 {
542 MetadataType key = static_cast<MetadataType>(s.ColumnInt(0));
543 target[key] = s.ColumnString(1);
544 }
545 }
546
547
548 void SQLiteDatabaseWrapper::SetGlobalProperty(GlobalProperty property,
549 const std::string& value)
550 {
551 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO GlobalProperties VALUES(?, ?)");
552 s.BindInt(0, property);
553 s.BindString(1, value);
554 s.Run();
555 }
556
557
558 bool SQLiteDatabaseWrapper::LookupGlobalProperty(std::string& target,
559 GlobalProperty property)
560 {
561 SQLite::Statement s(db_, SQLITE_FROM_HERE,
562 "SELECT value FROM GlobalProperties WHERE property=?");
563 s.BindInt(0, property);
564
565 if (!s.Step())
566 {
567 return false;
568 }
569 else
570 {
571 target = s.ColumnString(0);
572 return true;
573 }
574 }
575
576
577 int64_t SQLiteDatabaseWrapper::CreateResource(const std::string& publicId,
578 ResourceType type)
579 {
580 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Resources VALUES(NULL, ?, ?, NULL)");
581 s.BindInt(0, type);
582 s.BindString(1, publicId);
583 s.Run();
584 return db_.GetLastInsertRowId();
585 }
586
587
588 bool SQLiteDatabaseWrapper::LookupResource(int64_t& id,
589 ResourceType& type,
590 const std::string& publicId)
591 {
592 SQLite::Statement s(db_, SQLITE_FROM_HERE,
593 "SELECT internalId, resourceType FROM Resources WHERE publicId=?");
594 s.BindString(0, publicId);
595
596 if (!s.Step())
597 {
598 return false;
599 }
600 else
601 {
602 id = s.ColumnInt(0);
603 type = static_cast<ResourceType>(s.ColumnInt(1));
604
605 // Check whether there is a single resource with this public id
606 assert(!s.Step());
607
608 return true;
609 }
610 }
611
612
613 void SQLiteDatabaseWrapper::AttachChild(int64_t parent,
614 int64_t child)
615 {
616 SQLite::Statement s(db_, SQLITE_FROM_HERE, "UPDATE Resources SET parentId = ? WHERE internalId = ?");
617 s.BindInt64(0, parent);
618 s.BindInt64(1, child);
619 s.Run();
620 }
621
622
623 void SQLiteDatabaseWrapper::SetMetadata(int64_t id,
624 MetadataType type,
625 const std::string& value)
626 {
627 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO Metadata VALUES(?, ?, ?)");
628 s.BindInt64(0, id);
629 s.BindInt(1, type);
630 s.BindString(2, value);
631 s.Run();
632 }
633
634
635 void SQLiteDatabaseWrapper::DeleteMetadata(int64_t id,
636 MetadataType type)
637 {
638 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM Metadata WHERE id=? and type=?");
639 s.BindInt64(0, id);
640 s.BindInt(1, type);
641 s.Run();
642 }
643
644
645 bool SQLiteDatabaseWrapper::LookupMetadata(std::string& target,
646 int64_t id,
647 MetadataType type)
648 {
649 SQLite::Statement s(db_, SQLITE_FROM_HERE,
650 "SELECT value FROM Metadata WHERE id=? AND type=?");
651 s.BindInt64(0, id);
652 s.BindInt(1, type);
653
654 if (!s.Step())
655 {
656 return false;
657 }
658 else
659 {
660 target = s.ColumnString(0);
661 return true;
662 }
663 }
664
665
666 void SQLiteDatabaseWrapper::ListAvailableMetadata(std::list<MetadataType>& target,
667 int64_t id)
668 {
669 target.clear();
670
671 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT type FROM Metadata WHERE id=?");
672 s.BindInt64(0, id);
673
674 while (s.Step())
675 {
676 target.push_back(static_cast<MetadataType>(s.ColumnInt(0)));
677 }
678 }
679
680
681 void SQLiteDatabaseWrapper::AddAttachment(int64_t id,
682 const FileInfo& attachment)
683 {
684 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO AttachedFiles VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
685 s.BindInt64(0, id);
686 s.BindInt(1, attachment.GetContentType());
687 s.BindString(2, attachment.GetUuid());
688 s.BindInt64(3, attachment.GetCompressedSize());
689 s.BindInt64(4, attachment.GetUncompressedSize());
690 s.BindInt(5, attachment.GetCompressionType());
691 s.BindString(6, attachment.GetUncompressedMD5());
692 s.BindString(7, attachment.GetCompressedMD5());
693 s.Run();
694 }
695
696
697 void SQLiteDatabaseWrapper::DeleteAttachment(int64_t id,
698 FileContentType attachment)
699 {
700 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM AttachedFiles WHERE id=? AND fileType=?");
701 s.BindInt64(0, id);
702 s.BindInt(1, attachment);
703 s.Run();
704 }
705
706
707 void SQLiteDatabaseWrapper::ListAvailableAttachments(std::list<FileContentType>& target,
708 int64_t id)
709 {
710 target.clear();
711
712 SQLite::Statement s(db_, SQLITE_FROM_HERE,
713 "SELECT fileType FROM AttachedFiles WHERE id=?");
714 s.BindInt64(0, id);
715
716 while (s.Step())
717 {
718 target.push_back(static_cast<FileContentType>(s.ColumnInt(0)));
719 }
720 }
721
722 bool SQLiteDatabaseWrapper::LookupAttachment(FileInfo& attachment,
723 int64_t id,
724 FileContentType contentType)
725 {
726 SQLite::Statement s(db_, SQLITE_FROM_HERE,
727 "SELECT uuid, uncompressedSize, compressionType, compressedSize, "
728 "uncompressedMD5, compressedMD5 FROM AttachedFiles WHERE id=? AND fileType=?");
729 s.BindInt64(0, id);
730 s.BindInt(1, contentType);
731
732 if (!s.Step())
733 {
734 return false;
735 }
736 else
737 {
738 attachment = FileInfo(s.ColumnString(0),
739 contentType,
740 s.ColumnInt64(1),
741 s.ColumnString(4),
742 static_cast<CompressionType>(s.ColumnInt(2)),
743 s.ColumnInt64(3),
744 s.ColumnString(5));
745 return true;
746 }
747 }
748
749
750 void SQLiteDatabaseWrapper::ClearMainDicomTags(int64_t id)
751 {
752 {
753 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM DicomIdentifiers WHERE id=?");
754 s.BindInt64(0, id);
755 s.Run();
756 }
757
758 {
759 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM MainDicomTags WHERE id=?");
760 s.BindInt64(0, id);
761 s.Run();
762 }
763 }
764
765
766 void SQLiteDatabaseWrapper::SetMainDicomTag(int64_t id,
767 const DicomTag& tag,
768 const std::string& value)
769 {
770 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO MainDicomTags VALUES(?, ?, ?, ?)");
771 s.BindInt64(0, id);
772 s.BindInt(1, tag.GetGroup());
773 s.BindInt(2, tag.GetElement());
774 s.BindString(3, value);
775 s.Run();
776 }
777
778
779 void SQLiteDatabaseWrapper::SetIdentifierTag(int64_t id,
780 const DicomTag& tag,
781 const std::string& value)
782 {
783 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO DicomIdentifiers VALUES(?, ?, ?, ?)");
784 s.BindInt64(0, id);
785 s.BindInt(1, tag.GetGroup());
786 s.BindInt(2, tag.GetElement());
787 s.BindString(3, value);
788 s.Run();
789 }
790
791
792 void SQLiteDatabaseWrapper::GetMainDicomTags(DicomMap& map,
793 int64_t id)
794 {
795 map.Clear();
796
797 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM MainDicomTags WHERE id=?");
798 s.BindInt64(0, id);
799 while (s.Step())
800 {
801 map.SetValue(s.ColumnInt(1),
802 s.ColumnInt(2),
803 s.ColumnString(3), false);
804 }
805 }
806
807
808 void SQLiteDatabaseWrapper::GetChildrenPublicId(std::list<std::string>& target,
809 int64_t id)
810 {
811 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT a.publicId FROM Resources AS a, Resources AS b "
812 "WHERE a.parentId = b.internalId AND b.internalId = ?");
813 s.BindInt64(0, id);
814
815 target.clear();
816
817 while (s.Step())
818 {
819 target.push_back(s.ColumnString(0));
820 }
821 }
822
823
824 void SQLiteDatabaseWrapper::GetChildrenInternalId(std::list<int64_t>& target,
825 int64_t id)
826 {
827 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT a.internalId FROM Resources AS a, Resources AS b "
828 "WHERE a.parentId = b.internalId AND b.internalId = ?");
829 s.BindInt64(0, id);
830
831 target.clear();
832
833 while (s.Step())
834 {
835 target.push_back(s.ColumnInt64(0));
836 }
837 }
838
839
840 void SQLiteDatabaseWrapper::LogChange(int64_t internalId,
841 const ServerIndexChange& change)
842 {
843 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Changes VALUES(NULL, ?, ?, ?, ?)");
844 s.BindInt(0, change.GetChangeType());
845 s.BindInt64(1, internalId);
846 s.BindInt(2, change.GetResourceType());
847 s.BindString(3, change.GetDate());
848 s.Run();
849 }
850
851
852 void SQLiteDatabaseWrapper::LogExportedResource(const ExportedResource& resource)
853 {
854 SQLite::Statement s(db_, SQLITE_FROM_HERE,
855 "INSERT INTO ExportedResources VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?)");
856
857 s.BindInt(0, resource.GetResourceType());
858 s.BindString(1, resource.GetPublicId());
859 s.BindString(2, resource.GetModality());
860 s.BindString(3, resource.GetPatientId());
861 s.BindString(4, resource.GetStudyInstanceUid());
862 s.BindString(5, resource.GetSeriesInstanceUid());
863 s.BindString(6, resource.GetSopInstanceUid());
864 s.BindString(7, resource.GetDate());
865 s.Run();
866 }
867
868
869 void SQLiteDatabaseWrapper::GetExportedResources(std::list<ExportedResource>& target,
870 bool& done,
871 int64_t since,
872 uint32_t maxResults)
873 {
874 SQLite::Statement s(db_, SQLITE_FROM_HERE,
875 "SELECT * FROM ExportedResources WHERE seq>? ORDER BY seq LIMIT ?");
876 s.BindInt64(0, since);
877 s.BindInt(1, maxResults + 1);
878 GetExportedResourcesInternal(target, done, s, maxResults);
879 }
880
881
882 void SQLiteDatabaseWrapper::GetLastExportedResource(std::list<ExportedResource>& target)
883 {
884 bool done; // Ignored
885 SQLite::Statement s(db_, SQLITE_FROM_HERE,
886 "SELECT * FROM ExportedResources ORDER BY seq DESC LIMIT 1");
887 GetExportedResourcesInternal(target, done, s, 1);
888 }
889
890
891 uint64_t SQLiteDatabaseWrapper::GetTotalCompressedSize()
892 {
893 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(compressedSize) FROM AttachedFiles");
894 s.Run();
895 return static_cast<uint64_t>(s.ColumnInt64(0));
896 }
897
898
899 uint64_t SQLiteDatabaseWrapper::GetTotalUncompressedSize()
900 {
901 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(uncompressedSize) FROM AttachedFiles");
902 s.Run();
903 return static_cast<uint64_t>(s.ColumnInt64(0));
904 }
905
906
907 uint64_t SQLiteDatabaseWrapper::GetResourceCount(ResourceType resourceType)
908 {
909 SQLite::Statement s(db_, SQLITE_FROM_HERE,
910 "SELECT COUNT(*) FROM Resources WHERE resourceType=?");
911 s.BindInt(0, resourceType);
912
913 if (!s.Step())
914 {
915 return 0;
916 }
917 else
918 {
919 int64_t c = s.ColumnInt(0);
920 assert(!s.Step());
921 return c;
922 }
923 }
924
925
926 void SQLiteDatabaseWrapper::GetAllInternalIds(std::list<int64_t>& target,
927 ResourceType resourceType)
928 {
929 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT internalId FROM Resources WHERE resourceType=?");
930 s.BindInt(0, resourceType);
931
932 target.clear();
933 while (s.Step())
934 {
935 target.push_back(s.ColumnInt64(0));
936 }
937 }
938
939
940 void SQLiteDatabaseWrapper::GetAllPublicIds(std::list<std::string>& target,
941 ResourceType resourceType)
942 {
943 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId FROM Resources WHERE resourceType=?");
944 s.BindInt(0, resourceType);
945
946 target.clear();
947 while (s.Step())
948 {
949 target.push_back(s.ColumnString(0));
950 }
951 }
952
953
954 void SQLiteDatabaseWrapper::GetAllPublicIds(std::list<std::string>& target,
955 ResourceType resourceType,
956 size_t since,
957 size_t limit)
958 {
959 if (limit == 0)
960 {
961 target.clear();
962 return;
963 }
964
965 SQLite::Statement s(db_, SQLITE_FROM_HERE,
966 "SELECT publicId FROM Resources WHERE "
967 "resourceType=? LIMIT ? OFFSET ?");
968 s.BindInt(0, resourceType);
969 s.BindInt64(1, limit);
970 s.BindInt64(2, since);
971
972 target.clear();
973 while (s.Step())
974 {
975 target.push_back(s.ColumnString(0));
976 }
977 }
978
979
980 bool SQLiteDatabaseWrapper::SelectPatientToRecycle(int64_t& internalId)
981 {
982 SQLite::Statement s(db_, SQLITE_FROM_HERE,
983 "SELECT patientId FROM PatientRecyclingOrder ORDER BY seq ASC LIMIT 1");
984
985 if (!s.Step())
986 {
987 // No patient remaining or all the patients are protected
988 return false;
989 }
990 else
991 {
992 internalId = s.ColumnInt(0);
993 return true;
994 }
995 }
996
997
998 bool SQLiteDatabaseWrapper::SelectPatientToRecycle(int64_t& internalId,
999 int64_t patientIdToAvoid)
1000 {
1001 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1002 "SELECT patientId FROM PatientRecyclingOrder "
1003 "WHERE patientId != ? ORDER BY seq ASC LIMIT 1");
1004 s.BindInt64(0, patientIdToAvoid);
1005
1006 if (!s.Step())
1007 {
1008 // No patient remaining or all the patients are protected
1009 return false;
1010 }
1011 else
1012 {
1013 internalId = s.ColumnInt(0);
1014 return true;
1015 }
1016 }
1017
1018
1019 bool SQLiteDatabaseWrapper::IsProtectedPatient(int64_t internalId)
1020 {
1021 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1022 "SELECT * FROM PatientRecyclingOrder WHERE patientId = ?");
1023 s.BindInt64(0, internalId);
1024 return !s.Step();
1025 }
1026
1027
1028 void SQLiteDatabaseWrapper::SetProtectedPatient(int64_t internalId,
1029 bool isProtected)
1030 {
1031 if (isProtected)
1032 {
1033 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM PatientRecyclingOrder WHERE patientId=?");
1034 s.BindInt64(0, internalId);
1035 s.Run();
1036 }
1037 else if (IsProtectedPatient(internalId))
1038 {
1039 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO PatientRecyclingOrder VALUES(NULL, ?)");
1040 s.BindInt64(0, internalId);
1041 s.Run();
1042 }
1043 else
1044 {
1045 // Nothing to do: The patient is already unprotected
1046 }
1047 }
1048
1049
1050 bool SQLiteDatabaseWrapper::IsExistingResource(int64_t internalId)
1051 {
1052 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1053 "SELECT * FROM Resources WHERE internalId=?");
1054 s.BindInt64(0, internalId);
1055 return s.Step();
1056 }
1057
1058
1059 void SQLiteDatabaseWrapper::LookupIdentifier(std::list<int64_t>& target,
1060 ResourceType level,
1061 const DicomTag& tag,
1062 IdentifierConstraintType type,
1063 const std::string& value)
1064 {
1065 static const char* COMMON = ("SELECT d.id FROM DicomIdentifiers AS d, Resources AS r WHERE "
1066 "d.id = r.internalId AND r.resourceType=? AND "
1067 "d.tagGroup=? AND d.tagElement=? AND ");
1068
1069 std::auto_ptr<SQLite::Statement> s;
1070
1071 switch (type)
1072 {
1073 case IdentifierConstraintType_GreaterOrEqual:
1074 s.reset(new SQLite::Statement(db_, std::string(COMMON) + "d.value>=?"));
1075 break;
1076
1077 case IdentifierConstraintType_SmallerOrEqual:
1078 s.reset(new SQLite::Statement(db_, std::string(COMMON) + "d.value<=?"));
1079 break;
1080
1081 case IdentifierConstraintType_Wildcard:
1082 s.reset(new SQLite::Statement(db_, std::string(COMMON) + "d.value GLOB ?"));
1083 break;
1084
1085 case IdentifierConstraintType_Equal:
1086 default:
1087 s.reset(new SQLite::Statement(db_, std::string(COMMON) + "d.value=?"));
1088 break;
1089 }
1090
1091 assert(s.get() != NULL);
1092
1093 s->BindInt(0, level);
1094 s->BindInt(1, tag.GetGroup());
1095 s->BindInt(2, tag.GetElement());
1096 s->BindString(3, value);
1097
1098 target.clear();
1099
1100 while (s->Step())
1101 {
1102 target.push_back(s->ColumnInt64(0));
1103 }
1104 }
1105
1106
1107 void SQLiteDatabaseWrapper::LookupIdentifierRange(std::list<int64_t>& target,
1108 ResourceType level,
1109 const DicomTag& tag,
1110 const std::string& start,
1111 const std::string& end)
1112 {
1113 SQLite::Statement statement(db_, SQLITE_FROM_HERE,
1114 "SELECT d.id FROM DicomIdentifiers AS d, Resources AS r WHERE "
1115 "d.id = r.internalId AND r.resourceType=? AND "
1116 "d.tagGroup=? AND d.tagElement=? AND d.value>=? AND d.value<=?");
1117
1118 statement.BindInt(0, level);
1119 statement.BindInt(1, tag.GetGroup());
1120 statement.BindInt(2, tag.GetElement());
1121 statement.BindString(3, start);
1122 statement.BindString(4, end);
1123
1124 target.clear();
1125
1126 while (statement.Step())
1127 {
1128 target.push_back(statement.ColumnInt64(0));
1129 }
1130 }
1131 }