Mercurial > hg > orthanc

comparison OrthancServer/Database/SQLiteDatabaseWrapper.cpp @ 3160:fc9a4a2dad63

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merge
author Alain Mazy <alain@mazy.be>
date Thu, 24 Jan 2019 10:55:19 +0100
parents c0d7aee8c3f8
children 4bbadcd03966
comparison
equal deleted inserted replaced
3159:4cfed5c2eacd 3160:fc9a4a2dad63
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-2019 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 "../../Core/SQLite/Transaction.h"
40 #include "../Search/ISqlLookupFormatter.h"
41 #include "../ServerToolbox.h"
42
43 #include <EmbeddedResources.h>
44
45 #include <stdio.h>
46 #include <boost/lexical_cast.hpp>
47
48 namespace Orthanc
49 {
50 namespace Internals
51 {
52 class SignalFileDeleted : public SQLite::IScalarFunction
53 {
54 private:
55 IDatabaseListener& listener_;
56
57 public:
58 SignalFileDeleted(IDatabaseListener& listener) :
59 listener_(listener)
60 {
61 }
62
63 virtual const char* GetName() const
64 {
65 return "SignalFileDeleted";
66 }
67
68 virtual unsigned int GetCardinality() const
69 {
70 return 7;
71 }
72
73 virtual void Compute(SQLite::FunctionContext& context)
74 {
75 std::string uncompressedMD5, compressedMD5;
76
77 if (!context.IsNullValue(5))
78 {
79 uncompressedMD5 = context.GetStringValue(5);
80 }
81
82 if (!context.IsNullValue(6))
83 {
84 compressedMD5 = context.GetStringValue(6);
85 }
86
87 FileInfo info(context.GetStringValue(0),
88 static_cast<FileContentType>(context.GetIntValue(1)),
89 static_cast<uint64_t>(context.GetInt64Value(2)),
90 uncompressedMD5,
91 static_cast<CompressionType>(context.GetIntValue(3)),
92 static_cast<uint64_t>(context.GetInt64Value(4)),
93 compressedMD5);
94
95 listener_.SignalFileDeleted(info);
96 }
97 };
98
99 class SignalResourceDeleted : public SQLite::IScalarFunction
100 {
101 private:
102 IDatabaseListener& listener_;
103
104 public:
105 SignalResourceDeleted(IDatabaseListener& listener) :
106 listener_(listener)
107 {
108 }
109
110 virtual const char* GetName() const
111 {
112 return "SignalResourceDeleted";
113 }
114
115 virtual unsigned int GetCardinality() const
116 {
117 return 2;
118 }
119
120 virtual void Compute(SQLite::FunctionContext& context)
121 {
122 ResourceType type = static_cast<ResourceType>(context.GetIntValue(1));
123 ServerIndexChange change(ChangeType_Deleted, type, context.GetStringValue(0));
124 listener_.SignalChange(change);
125 }
126 };
127
128 class SignalRemainingAncestor : public SQLite::IScalarFunction
129 {
130 private:
131 bool hasRemainingAncestor_;
132 std::string remainingPublicId_;
133 ResourceType remainingType_;
134
135 public:
136 SignalRemainingAncestor() :
137 hasRemainingAncestor_(false)
138 {
139 }
140
141 void Reset()
142 {
143 hasRemainingAncestor_ = false;
144 }
145
146 virtual const char* GetName() const
147 {
148 return "SignalRemainingAncestor";
149 }
150
151 virtual unsigned int GetCardinality() const
152 {
153 return 2;
154 }
155
156 virtual void Compute(SQLite::FunctionContext& context)
157 {
158 VLOG(1) << "There exists a remaining ancestor with public ID \""
159 << context.GetStringValue(0)
160 << "\" of type "
161 << context.GetIntValue(1);
162
163 if (!hasRemainingAncestor_ ||
164 remainingType_ >= context.GetIntValue(1))
165 {
166 hasRemainingAncestor_ = true;
167 remainingPublicId_ = context.GetStringValue(0);
168 remainingType_ = static_cast<ResourceType>(context.GetIntValue(1));
169 }
170 }
171
172 bool HasRemainingAncestor() const
173 {
174 return hasRemainingAncestor_;
175 }
176
177 const std::string& GetRemainingAncestorId() const
178 {
179 assert(hasRemainingAncestor_);
180 return remainingPublicId_;
181 }
182
183 ResourceType GetRemainingAncestorType() const
184 {
185 assert(hasRemainingAncestor_);
186 return remainingType_;
187 }
188 };
189 }
190
191
192 void SQLiteDatabaseWrapper::GetChangesInternal(std::list<ServerIndexChange>& target,
193 bool& done,
194 SQLite::Statement& s,
195 uint32_t maxResults)
196 {
197 target.clear();
198
199 while (target.size() < maxResults && s.Step())
200 {
201 int64_t seq = s.ColumnInt64(0);
202 ChangeType changeType = static_cast<ChangeType>(s.ColumnInt(1));
203 ResourceType resourceType = static_cast<ResourceType>(s.ColumnInt(3));
204 const std::string& date = s.ColumnString(4);
205
206 int64_t internalId = s.ColumnInt64(2);
207 std::string publicId = GetPublicId(internalId);
208
209 target.push_back(ServerIndexChange(seq, changeType, resourceType, publicId, date));
210 }
211
212 done = !(target.size() == maxResults && s.Step());
213 }
214
215
216 void SQLiteDatabaseWrapper::GetExportedResourcesInternal(std::list<ExportedResource>& target,
217 bool& done,
218 SQLite::Statement& s,
219 uint32_t maxResults)
220 {
221 target.clear();
222
223 while (target.size() < maxResults && s.Step())
224 {
225 int64_t seq = s.ColumnInt64(0);
226 ResourceType resourceType = static_cast<ResourceType>(s.ColumnInt(1));
227 std::string publicId = s.ColumnString(2);
228
229 ExportedResource resource(seq,
230 resourceType,
231 publicId,
232 s.ColumnString(3), // modality
233 s.ColumnString(8), // date
234 s.ColumnString(4), // patient ID
235 s.ColumnString(5), // study instance UID
236 s.ColumnString(6), // series instance UID
237 s.ColumnString(7)); // sop instance UID
238
239 target.push_back(resource);
240 }
241
242 done = !(target.size() == maxResults && s.Step());
243 }
244
245
246 void SQLiteDatabaseWrapper::GetChildren(std::list<std::string>& childrenPublicIds,
247 int64_t id)
248 {
249 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId FROM Resources WHERE parentId=?");
250 s.BindInt64(0, id);
251
252 childrenPublicIds.clear();
253 while (s.Step())
254 {
255 childrenPublicIds.push_back(s.ColumnString(0));
256 }
257 }
258
259
260 void SQLiteDatabaseWrapper::DeleteResource(int64_t id)
261 {
262 signalRemainingAncestor_->Reset();
263
264 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM Resources WHERE internalId=?");
265 s.BindInt64(0, id);
266 s.Run();
267
268 if (signalRemainingAncestor_->HasRemainingAncestor() &&
269 listener_ != NULL)
270 {
271 listener_->SignalRemainingAncestor(signalRemainingAncestor_->GetRemainingAncestorType(),
272 signalRemainingAncestor_->GetRemainingAncestorId());
273 }
274 }
275
276
277 bool SQLiteDatabaseWrapper::GetParentPublicId(std::string& target,
278 int64_t id)
279 {
280 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT a.publicId FROM Resources AS a, Resources AS b "
281 "WHERE a.internalId = b.parentId AND b.internalId = ?");
282 s.BindInt64(0, id);
283
284 if (s.Step())
285 {
286 target = s.ColumnString(0);
287 return true;
288 }
289 else
290 {
291 return false;
292 }
293 }
294
295
296 int64_t SQLiteDatabaseWrapper::GetTableRecordCount(const std::string& table)
297 {
298 char buf[128];
299 sprintf(buf, "SELECT COUNT(*) FROM %s", table.c_str());
300 SQLite::Statement s(db_, buf);
301
302 if (!s.Step())
303 {
304 throw OrthancException(ErrorCode_InternalError);
305 }
306
307 int64_t c = s.ColumnInt(0);
308 assert(!s.Step());
309
310 return c;
311 }
312
313
314 SQLiteDatabaseWrapper::SQLiteDatabaseWrapper(const std::string& path) :
315 listener_(NULL),
316 signalRemainingAncestor_(NULL),
317 version_(0)
318 {
319 db_.Open(path);
320 }
321
322
323 SQLiteDatabaseWrapper::SQLiteDatabaseWrapper() :
324 listener_(NULL),
325 signalRemainingAncestor_(NULL),
326 version_(0)
327 {
328 db_.OpenInMemory();
329 }
330
331
332 int SQLiteDatabaseWrapper::GetGlobalIntegerProperty(GlobalProperty property,
333 int defaultValue)
334 {
335 std::string tmp;
336
337 if (!LookupGlobalProperty(tmp, GlobalProperty_DatabasePatchLevel))
338 {
339 return defaultValue;
340 }
341 else
342 {
343 try
344 {
345 return boost::lexical_cast<int>(tmp);
346 }
347 catch (boost::bad_lexical_cast&)
348 {
349 throw OrthancException(ErrorCode_ParameterOutOfRange,
350 "Global property " + boost::lexical_cast<std::string>(property) +
351 " should be an integer, but found: " + tmp);
352 }
353 }
354 }
355
356
357 void SQLiteDatabaseWrapper::Open()
358 {
359 db_.Execute("PRAGMA ENCODING=\"UTF-8\";");
360
361 // Performance tuning of SQLite with PRAGMAs
362 // http://www.sqlite.org/pragma.html
363 db_.Execute("PRAGMA SYNCHRONOUS=NORMAL;");
364 db_.Execute("PRAGMA JOURNAL_MODE=WAL;");
365 db_.Execute("PRAGMA LOCKING_MODE=EXCLUSIVE;");
366 db_.Execute("PRAGMA WAL_AUTOCHECKPOINT=1000;");
367 //db_.Execute("PRAGMA TEMP_STORE=memory");
368
369 // Make "LIKE" case-sensitive in SQLite
370 db_.Execute("PRAGMA case_sensitive_like = true;");
371
372 {
373 SQLite::Transaction t(db_);
374 t.Begin();
375
376 if (!db_.DoesTableExist("GlobalProperties"))
377 {
378 LOG(INFO) << "Creating the database";
379 std::string query;
380 EmbeddedResources::GetFileResource(query, EmbeddedResources::PREPARE_DATABASE);
381 db_.Execute(query);
382 }
383
384 // Check the version of the database
385 std::string tmp;
386 if (!LookupGlobalProperty(tmp, GlobalProperty_DatabaseSchemaVersion))
387 {
388 tmp = "Unknown";
389 }
390
391 bool ok = false;
392 try
393 {
394 LOG(INFO) << "Version of the Orthanc database: " << tmp;
395 version_ = boost::lexical_cast<unsigned int>(tmp);
396 ok = true;
397 }
398 catch (boost::bad_lexical_cast&)
399 {
400 }
401
402 if (!ok)
403 {
404 throw OrthancException(ErrorCode_IncompatibleDatabaseVersion,
405 "Incompatible version of the Orthanc database: " + tmp);
406 }
407
408 // New in Orthanc 1.5.1
409 if (version_ == 6)
410 {
411 if (!LookupGlobalProperty(tmp, GlobalProperty_GetTotalSizeIsFast) ||
412 tmp != "1")
413 {
414 LOG(INFO) << "Installing the SQLite triggers to track the size of the attachments";
415 std::string query;
416 EmbeddedResources::GetFileResource(query, EmbeddedResources::INSTALL_TRACK_ATTACHMENTS_SIZE);
417 db_.Execute(query);
418 }
419 }
420
421 t.Commit();
422 }
423
424 signalRemainingAncestor_ = new Internals::SignalRemainingAncestor;
425 db_.Register(signalRemainingAncestor_);
426 }
427
428
429 static void ExecuteUpgradeScript(SQLite::Connection& db,
430 EmbeddedResources::FileResourceId script)
431 {
432 std::string upgrade;
433 EmbeddedResources::GetFileResource(upgrade, script);
434 db.BeginTransaction();
435 db.Execute(upgrade);
436 db.CommitTransaction();
437 }
438
439
440 void SQLiteDatabaseWrapper::Upgrade(unsigned int targetVersion,
441 IStorageArea& storageArea)
442 {
443 if (targetVersion != 6)
444 {
445 throw OrthancException(ErrorCode_IncompatibleDatabaseVersion);
446 }
447
448 // This version of Orthanc is only compatible with versions 3, 4,
449 // 5 and 6 of the DB schema
450 if (version_ != 3 &&
451 version_ != 4 &&
452 version_ != 5 &&
453 version_ != 6)
454 {
455 throw OrthancException(ErrorCode_IncompatibleDatabaseVersion);
456 }
457
458 if (version_ == 3)
459 {
460 LOG(WARNING) << "Upgrading database version from 3 to 4";
461 ExecuteUpgradeScript(db_, EmbeddedResources::UPGRADE_DATABASE_3_TO_4);
462 version_ = 4;
463 }
464
465 if (version_ == 4)
466 {
467 LOG(WARNING) << "Upgrading database version from 4 to 5";
468 ExecuteUpgradeScript(db_, EmbeddedResources::UPGRADE_DATABASE_4_TO_5);
469 version_ = 5;
470 }
471
472 if (version_ == 5)
473 {
474 LOG(WARNING) << "Upgrading database version from 5 to 6";
475 // No change in the DB schema, the step from version 5 to 6 only
476 // consists in reconstructing the main DICOM tags information
477 // (as more tags got included).
478 db_.BeginTransaction();
479 ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Patient);
480 ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Study);
481 ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Series);
482 ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Instance);
483 db_.Execute("UPDATE GlobalProperties SET value=\"6\" WHERE property=" +
484 boost::lexical_cast<std::string>(GlobalProperty_DatabaseSchemaVersion) + ";");
485 db_.CommitTransaction();
486 version_ = 6;
487 }
488 }
489
490
491 void SQLiteDatabaseWrapper::SetListener(IDatabaseListener& listener)
492 {
493 listener_ = &listener;
494 db_.Register(new Internals::SignalFileDeleted(listener));
495 db_.Register(new Internals::SignalResourceDeleted(listener));
496 }
497
498
499 void SQLiteDatabaseWrapper::ClearTable(const std::string& tableName)
500 {
501 db_.Execute("DELETE FROM " + tableName);
502 }
503
504
505 bool SQLiteDatabaseWrapper::LookupParent(int64_t& parentId,
506 int64_t resourceId)
507 {
508 SQLite::Statement s(db_, SQLITE_FROM_HERE,
509 "SELECT parentId FROM Resources WHERE internalId=?");
510 s.BindInt64(0, resourceId);
511
512 if (!s.Step())
513 {
514 throw OrthancException(ErrorCode_UnknownResource);
515 }
516
517 if (s.ColumnIsNull(0))
518 {
519 return false;
520 }
521 else
522 {
523 parentId = s.ColumnInt(0);
524 return true;
525 }
526 }
527
528
529 ResourceType SQLiteDatabaseWrapper::GetResourceType(int64_t resourceId)
530 {
531 SQLite::Statement s(db_, SQLITE_FROM_HERE,
532 "SELECT resourceType FROM Resources WHERE internalId=?");
533 s.BindInt64(0, resourceId);
534
535 if (s.Step())
536 {
537 return static_cast<ResourceType>(s.ColumnInt(0));
538 }
539 else
540 {
541 throw OrthancException(ErrorCode_UnknownResource);
542 }
543 }
544
545
546 std::string SQLiteDatabaseWrapper::GetPublicId(int64_t resourceId)
547 {
548 SQLite::Statement s(db_, SQLITE_FROM_HERE,
549 "SELECT publicId FROM Resources WHERE internalId=?");
550 s.BindInt64(0, resourceId);
551
552 if (s.Step())
553 {
554 return s.ColumnString(0);
555 }
556 else
557 {
558 throw OrthancException(ErrorCode_UnknownResource);
559 }
560 }
561
562
563 void SQLiteDatabaseWrapper::GetChanges(std::list<ServerIndexChange>& target /*out*/,
564 bool& done /*out*/,
565 int64_t since,
566 uint32_t maxResults)
567 {
568 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes WHERE seq>? ORDER BY seq LIMIT ?");
569 s.BindInt64(0, since);
570 s.BindInt(1, maxResults + 1);
571 GetChangesInternal(target, done, s, maxResults);
572 }
573
574
575 void SQLiteDatabaseWrapper::GetLastChange(std::list<ServerIndexChange>& target /*out*/)
576 {
577 bool done; // Ignored
578 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes ORDER BY seq DESC LIMIT 1");
579 GetChangesInternal(target, done, s, 1);
580 }
581
582
583 class SQLiteDatabaseWrapper::Transaction : public IDatabaseWrapper::ITransaction
584 {
585 private:
586 SQLiteDatabaseWrapper& that_;
587 std::auto_ptr<SQLite::Transaction> transaction_;
588 int64_t initialDiskSize_;
589
590 public:
591 Transaction(SQLiteDatabaseWrapper& that) :
592 that_(that),
593 transaction_(new SQLite::Transaction(that_.db_))
594 {
595 #if defined(NDEBUG)
596 // Release mode
597 initialDiskSize_ = 0;
598 #else
599 // Debug mode
600 initialDiskSize_ = static_cast<int64_t>(that_.GetTotalCompressedSize());
601 #endif
602 }
603
604 virtual void Begin()
605 {
606 transaction_->Begin();
607 }
608
609 virtual void Rollback()
610 {
611 transaction_->Rollback();
612 }
613
614 virtual void Commit(int64_t fileSizeDelta /* only used in debug */)
615 {
616 transaction_->Commit();
617
618 assert(initialDiskSize_ + fileSizeDelta >= 0 &&
619 initialDiskSize_ + fileSizeDelta == static_cast<int64_t>(that_.GetTotalCompressedSize()));
620 }
621 };
622
623
624 IDatabaseWrapper::ITransaction* SQLiteDatabaseWrapper::StartTransaction()
625 {
626 return new Transaction(*this);
627 }
628
629
630 void SQLiteDatabaseWrapper::GetAllMetadata(std::map<MetadataType, std::string>& target,
631 int64_t id)
632 {
633 target.clear();
634
635 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT type, value FROM Metadata WHERE id=?");
636 s.BindInt64(0, id);
637
638 while (s.Step())
639 {
640 MetadataType key = static_cast<MetadataType>(s.ColumnInt(0));
641 target[key] = s.ColumnString(1);
642 }
643 }
644
645
646 void SQLiteDatabaseWrapper::SetGlobalProperty(GlobalProperty property,
647 const std::string& value)
648 {
649 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO GlobalProperties VALUES(?, ?)");
650 s.BindInt(0, property);
651 s.BindString(1, value);
652 s.Run();
653 }
654
655
656 bool SQLiteDatabaseWrapper::LookupGlobalProperty(std::string& target,
657 GlobalProperty property)
658 {
659 SQLite::Statement s(db_, SQLITE_FROM_HERE,
660 "SELECT value FROM GlobalProperties WHERE property=?");
661 s.BindInt(0, property);
662
663 if (!s.Step())
664 {
665 return false;
666 }
667 else
668 {
669 target = s.ColumnString(0);
670 return true;
671 }
672 }
673
674
675 int64_t SQLiteDatabaseWrapper::CreateResource(const std::string& publicId,
676 ResourceType type)
677 {
678 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Resources VALUES(NULL, ?, ?, NULL)");
679 s.BindInt(0, type);
680 s.BindString(1, publicId);
681 s.Run();
682 return db_.GetLastInsertRowId();
683 }
684
685
686 bool SQLiteDatabaseWrapper::LookupResource(int64_t& id,
687 ResourceType& type,
688 const std::string& publicId)
689 {
690 SQLite::Statement s(db_, SQLITE_FROM_HERE,
691 "SELECT internalId, resourceType FROM Resources WHERE publicId=?");
692 s.BindString(0, publicId);
693
694 if (!s.Step())
695 {
696 return false;
697 }
698 else
699 {
700 id = s.ColumnInt(0);
701 type = static_cast<ResourceType>(s.ColumnInt(1));
702
703 // Check whether there is a single resource with this public id
704 assert(!s.Step());
705
706 return true;
707 }
708 }
709
710
711 void SQLiteDatabaseWrapper::AttachChild(int64_t parent,
712 int64_t child)
713 {
714 SQLite::Statement s(db_, SQLITE_FROM_HERE, "UPDATE Resources SET parentId = ? WHERE internalId = ?");
715 s.BindInt64(0, parent);
716 s.BindInt64(1, child);
717 s.Run();
718 }
719
720
721 void SQLiteDatabaseWrapper::SetMetadata(int64_t id,
722 MetadataType type,
723 const std::string& value)
724 {
725 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO Metadata VALUES(?, ?, ?)");
726 s.BindInt64(0, id);
727 s.BindInt(1, type);
728 s.BindString(2, value);
729 s.Run();
730 }
731
732
733 void SQLiteDatabaseWrapper::DeleteMetadata(int64_t id,
734 MetadataType type)
735 {
736 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM Metadata WHERE id=? and type=?");
737 s.BindInt64(0, id);
738 s.BindInt(1, type);
739 s.Run();
740 }
741
742
743 bool SQLiteDatabaseWrapper::LookupMetadata(std::string& target,
744 int64_t id,
745 MetadataType type)
746 {
747 SQLite::Statement s(db_, SQLITE_FROM_HERE,
748 "SELECT value FROM Metadata WHERE id=? AND type=?");
749 s.BindInt64(0, id);
750 s.BindInt(1, type);
751
752 if (!s.Step())
753 {
754 return false;
755 }
756 else
757 {
758 target = s.ColumnString(0);
759 return true;
760 }
761 }
762
763
764 void SQLiteDatabaseWrapper::ListAvailableMetadata(std::list<MetadataType>& target,
765 int64_t id)
766 {
767 target.clear();
768
769 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT type FROM Metadata WHERE id=?");
770 s.BindInt64(0, id);
771
772 while (s.Step())
773 {
774 target.push_back(static_cast<MetadataType>(s.ColumnInt(0)));
775 }
776 }
777
778
779 void SQLiteDatabaseWrapper::AddAttachment(int64_t id,
780 const FileInfo& attachment)
781 {
782 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO AttachedFiles VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
783 s.BindInt64(0, id);
784 s.BindInt(1, attachment.GetContentType());
785 s.BindString(2, attachment.GetUuid());
786 s.BindInt64(3, attachment.GetCompressedSize());
787 s.BindInt64(4, attachment.GetUncompressedSize());
788 s.BindInt(5, attachment.GetCompressionType());
789 s.BindString(6, attachment.GetUncompressedMD5());
790 s.BindString(7, attachment.GetCompressedMD5());
791 s.Run();
792 }
793
794
795 void SQLiteDatabaseWrapper::DeleteAttachment(int64_t id,
796 FileContentType attachment)
797 {
798 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM AttachedFiles WHERE id=? AND fileType=?");
799 s.BindInt64(0, id);
800 s.BindInt(1, attachment);
801 s.Run();
802 }
803
804
805 void SQLiteDatabaseWrapper::ListAvailableAttachments(std::list<FileContentType>& target,
806 int64_t id)
807 {
808 target.clear();
809
810 SQLite::Statement s(db_, SQLITE_FROM_HERE,
811 "SELECT fileType FROM AttachedFiles WHERE id=?");
812 s.BindInt64(0, id);
813
814 while (s.Step())
815 {
816 target.push_back(static_cast<FileContentType>(s.ColumnInt(0)));
817 }
818 }
819
820 bool SQLiteDatabaseWrapper::LookupAttachment(FileInfo& attachment,
821 int64_t id,
822 FileContentType contentType)
823 {
824 SQLite::Statement s(db_, SQLITE_FROM_HERE,
825 "SELECT uuid, uncompressedSize, compressionType, compressedSize, "
826 "uncompressedMD5, compressedMD5 FROM AttachedFiles WHERE id=? AND fileType=?");
827 s.BindInt64(0, id);
828 s.BindInt(1, contentType);
829
830 if (!s.Step())
831 {
832 return false;
833 }
834 else
835 {
836 attachment = FileInfo(s.ColumnString(0),
837 contentType,
838 s.ColumnInt64(1),
839 s.ColumnString(4),
840 static_cast<CompressionType>(s.ColumnInt(2)),
841 s.ColumnInt64(3),
842 s.ColumnString(5));
843 return true;
844 }
845 }
846
847
848 void SQLiteDatabaseWrapper::ClearMainDicomTags(int64_t id)
849 {
850 {
851 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM DicomIdentifiers WHERE id=?");
852 s.BindInt64(0, id);
853 s.Run();
854 }
855
856 {
857 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM MainDicomTags WHERE id=?");
858 s.BindInt64(0, id);
859 s.Run();
860 }
861 }
862
863
864 void SQLiteDatabaseWrapper::SetMainDicomTag(int64_t id,
865 const DicomTag& tag,
866 const std::string& value)
867 {
868 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO MainDicomTags VALUES(?, ?, ?, ?)");
869 s.BindInt64(0, id);
870 s.BindInt(1, tag.GetGroup());
871 s.BindInt(2, tag.GetElement());
872 s.BindString(3, value);
873 s.Run();
874 }
875
876
877 void SQLiteDatabaseWrapper::SetIdentifierTag(int64_t id,
878 const DicomTag& tag,
879 const std::string& value)
880 {
881 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO DicomIdentifiers VALUES(?, ?, ?, ?)");
882 s.BindInt64(0, id);
883 s.BindInt(1, tag.GetGroup());
884 s.BindInt(2, tag.GetElement());
885 s.BindString(3, value);
886 s.Run();
887 }
888
889
890 void SQLiteDatabaseWrapper::GetMainDicomTags(DicomMap& map,
891 int64_t id)
892 {
893 map.Clear();
894
895 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM MainDicomTags WHERE id=?");
896 s.BindInt64(0, id);
897 while (s.Step())
898 {
899 map.SetValue(s.ColumnInt(1),
900 s.ColumnInt(2),
901 s.ColumnString(3), false);
902 }
903 }
904
905
906 void SQLiteDatabaseWrapper::GetChildrenPublicId(std::list<std::string>& target,
907 int64_t id)
908 {
909 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT a.publicId FROM Resources AS a, Resources AS b "
910 "WHERE a.parentId = b.internalId AND b.internalId = ?");
911 s.BindInt64(0, id);
912
913 target.clear();
914
915 while (s.Step())
916 {
917 target.push_back(s.ColumnString(0));
918 }
919 }
920
921
922 void SQLiteDatabaseWrapper::GetChildrenInternalId(std::list<int64_t>& target,
923 int64_t id)
924 {
925 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT a.internalId FROM Resources AS a, Resources AS b "
926 "WHERE a.parentId = b.internalId AND b.internalId = ?");
927 s.BindInt64(0, id);
928
929 target.clear();
930
931 while (s.Step())
932 {
933 target.push_back(s.ColumnInt64(0));
934 }
935 }
936
937
938 void SQLiteDatabaseWrapper::LogChange(int64_t internalId,
939 const ServerIndexChange& change)
940 {
941 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Changes VALUES(NULL, ?, ?, ?, ?)");
942 s.BindInt(0, change.GetChangeType());
943 s.BindInt64(1, internalId);
944 s.BindInt(2, change.GetResourceType());
945 s.BindString(3, change.GetDate());
946 s.Run();
947 }
948
949
950 void SQLiteDatabaseWrapper::LogExportedResource(const ExportedResource& resource)
951 {
952 SQLite::Statement s(db_, SQLITE_FROM_HERE,
953 "INSERT INTO ExportedResources VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?)");
954
955 s.BindInt(0, resource.GetResourceType());
956 s.BindString(1, resource.GetPublicId());
957 s.BindString(2, resource.GetModality());
958 s.BindString(3, resource.GetPatientId());
959 s.BindString(4, resource.GetStudyInstanceUid());
960 s.BindString(5, resource.GetSeriesInstanceUid());
961 s.BindString(6, resource.GetSopInstanceUid());
962 s.BindString(7, resource.GetDate());
963 s.Run();
964 }
965
966
967 void SQLiteDatabaseWrapper::GetExportedResources(std::list<ExportedResource>& target,
968 bool& done,
969 int64_t since,
970 uint32_t maxResults)
971 {
972 SQLite::Statement s(db_, SQLITE_FROM_HERE,
973 "SELECT * FROM ExportedResources WHERE seq>? ORDER BY seq LIMIT ?");
974 s.BindInt64(0, since);
975 s.BindInt(1, maxResults + 1);
976 GetExportedResourcesInternal(target, done, s, maxResults);
977 }
978
979
980 void SQLiteDatabaseWrapper::GetLastExportedResource(std::list<ExportedResource>& target)
981 {
982 bool done; // Ignored
983 SQLite::Statement s(db_, SQLITE_FROM_HERE,
984 "SELECT * FROM ExportedResources ORDER BY seq DESC LIMIT 1");
985 GetExportedResourcesInternal(target, done, s, 1);
986 }
987
988
989 uint64_t SQLiteDatabaseWrapper::GetTotalCompressedSize()
990 {
991 // Old SQL query that was used in Orthanc <= 1.5.0:
992 // SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(compressedSize) FROM AttachedFiles");
993
994 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT value FROM GlobalIntegers WHERE key=0");
995 s.Run();
996 return static_cast<uint64_t>(s.ColumnInt64(0));
997 }
998
999
1000 uint64_t SQLiteDatabaseWrapper::GetTotalUncompressedSize()
1001 {
1002 // Old SQL query that was used in Orthanc <= 1.5.0:
1003 // SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(uncompressedSize) FROM AttachedFiles");
1004
1005 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT value FROM GlobalIntegers WHERE key=1");
1006 s.Run();
1007 return static_cast<uint64_t>(s.ColumnInt64(0));
1008 }
1009
1010
1011 uint64_t SQLiteDatabaseWrapper::GetResourceCount(ResourceType resourceType)
1012 {
1013 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1014 "SELECT COUNT(*) FROM Resources WHERE resourceType=?");
1015 s.BindInt(0, resourceType);
1016
1017 if (!s.Step())
1018 {
1019 return 0;
1020 }
1021 else
1022 {
1023 int64_t c = s.ColumnInt(0);
1024 assert(!s.Step());
1025 return c;
1026 }
1027 }
1028
1029
1030 void SQLiteDatabaseWrapper::GetAllPublicIds(std::list<std::string>& target,
1031 ResourceType resourceType)
1032 {
1033 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId FROM Resources WHERE resourceType=?");
1034 s.BindInt(0, resourceType);
1035
1036 target.clear();
1037 while (s.Step())
1038 {
1039 target.push_back(s.ColumnString(0));
1040 }
1041 }
1042
1043
1044 void SQLiteDatabaseWrapper::GetAllPublicIds(std::list<std::string>& target,
1045 ResourceType resourceType,
1046 size_t since,
1047 size_t limit)
1048 {
1049 if (limit == 0)
1050 {
1051 target.clear();
1052 return;
1053 }
1054
1055 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1056 "SELECT publicId FROM Resources WHERE "
1057 "resourceType=? LIMIT ? OFFSET ?");
1058 s.BindInt(0, resourceType);
1059 s.BindInt64(1, limit);
1060 s.BindInt64(2, since);
1061
1062 target.clear();
1063 while (s.Step())
1064 {
1065 target.push_back(s.ColumnString(0));
1066 }
1067 }
1068
1069
1070 bool SQLiteDatabaseWrapper::SelectPatientToRecycle(int64_t& internalId)
1071 {
1072 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1073 "SELECT patientId FROM PatientRecyclingOrder ORDER BY seq ASC LIMIT 1");
1074
1075 if (!s.Step())
1076 {
1077 // No patient remaining or all the patients are protected
1078 return false;
1079 }
1080 else
1081 {
1082 internalId = s.ColumnInt(0);
1083 return true;
1084 }
1085 }
1086
1087
1088 bool SQLiteDatabaseWrapper::SelectPatientToRecycle(int64_t& internalId,
1089 int64_t patientIdToAvoid)
1090 {
1091 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1092 "SELECT patientId FROM PatientRecyclingOrder "
1093 "WHERE patientId != ? ORDER BY seq ASC LIMIT 1");
1094 s.BindInt64(0, patientIdToAvoid);
1095
1096 if (!s.Step())
1097 {
1098 // No patient remaining or all the patients are protected
1099 return false;
1100 }
1101 else
1102 {
1103 internalId = s.ColumnInt(0);
1104 return true;
1105 }
1106 }
1107
1108
1109 bool SQLiteDatabaseWrapper::IsProtectedPatient(int64_t internalId)
1110 {
1111 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1112 "SELECT * FROM PatientRecyclingOrder WHERE patientId = ?");
1113 s.BindInt64(0, internalId);
1114 return !s.Step();
1115 }
1116
1117
1118 void SQLiteDatabaseWrapper::SetProtectedPatient(int64_t internalId,
1119 bool isProtected)
1120 {
1121 if (isProtected)
1122 {
1123 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM PatientRecyclingOrder WHERE patientId=?");
1124 s.BindInt64(0, internalId);
1125 s.Run();
1126 }
1127 else if (IsProtectedPatient(internalId))
1128 {
1129 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO PatientRecyclingOrder VALUES(NULL, ?)");
1130 s.BindInt64(0, internalId);
1131 s.Run();
1132 }
1133 else
1134 {
1135 // Nothing to do: The patient is already unprotected
1136 }
1137 }
1138
1139
1140 bool SQLiteDatabaseWrapper::IsExistingResource(int64_t internalId)
1141 {
1142 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1143 "SELECT * FROM Resources WHERE internalId=?");
1144 s.BindInt64(0, internalId);
1145 return s.Step();
1146 }
1147
1148
1149 bool SQLiteDatabaseWrapper::IsDiskSizeAbove(uint64_t threshold)
1150 {
1151 return GetTotalCompressedSize() > threshold;
1152 }
1153
1154
1155
1156 class SQLiteDatabaseWrapper::LookupFormatter : public ISqlLookupFormatter
1157 {
1158 private:
1159 std::list<std::string> values_;
1160
1161 public:
1162 virtual std::string GenerateParameter(const std::string& value)
1163 {
1164 values_.push_back(value);
1165 return "?";
1166 }
1167
1168 virtual std::string FormatResourceType(ResourceType level)
1169 {
1170 return boost::lexical_cast<std::string>(level);
1171 }
1172
1173 virtual std::string FormatWildcardEscape()
1174 {
1175 return "ESCAPE '\\'";
1176 }
1177
1178 void Bind(SQLite::Statement& statement) const
1179 {
1180 size_t pos = 0;
1181
1182 for (std::list<std::string>::const_iterator
1183 it = values_.begin(); it != values_.end(); ++it, pos++)
1184 {
1185 statement.BindString(pos, *it);
1186 }
1187 }
1188 };
1189
1190
1191 static void AnswerLookup(std::list<std::string>& resourcesId,
1192 std::list<std::string>& instancesId,
1193 SQLite::Connection& db,
1194 ResourceType level)
1195 {
1196 resourcesId.clear();
1197 instancesId.clear();
1198
1199 std::auto_ptr<SQLite::Statement> statement;
1200
1201 switch (level)
1202 {
1203 case ResourceType_Patient:
1204 {
1205 statement.reset(
1206 new SQLite::Statement(
1207 db, SQLITE_FROM_HERE,
1208 "SELECT patients.publicId, instances.publicID FROM Lookup AS patients "
1209 "INNER JOIN Resources studies ON patients.internalId=studies.parentId "
1210 "INNER JOIN Resources series ON studies.internalId=series.parentId "
1211 "INNER JOIN Resources instances ON series.internalId=instances.parentId "
1212 "GROUP BY patients.publicId"));
1213
1214 break;
1215 }
1216
1217 case ResourceType_Study:
1218 {
1219 statement.reset(
1220 new SQLite::Statement(
1221 db, SQLITE_FROM_HERE,
1222 "SELECT studies.publicId, instances.publicID FROM Lookup AS studies "
1223 "INNER JOIN Resources series ON studies.internalId=series.parentId "
1224 "INNER JOIN Resources instances ON series.internalId=instances.parentId "
1225 "GROUP BY studies.publicId"));
1226
1227 break;
1228 }
1229
1230 case ResourceType_Series:
1231 {
1232 statement.reset(
1233 new SQLite::Statement(
1234 db, SQLITE_FROM_HERE,
1235 "SELECT series.publicId, instances.publicID FROM Lookup AS series "
1236 "INNER JOIN Resources instances ON series.internalId=instances.parentId "
1237 "GROUP BY series.publicId"));
1238
1239 break;
1240 }
1241
1242 case ResourceType_Instance:
1243 {
1244 statement.reset(
1245 new SQLite::Statement(
1246 db, SQLITE_FROM_HERE, "SELECT publicId, publicId FROM Lookup"));
1247
1248 break;
1249 }
1250
1251 default:
1252 throw OrthancException(ErrorCode_InternalError);
1253 }
1254
1255 assert(statement.get() != NULL);
1256
1257 while (statement->Step())
1258 {
1259 resourcesId.push_back(statement->ColumnString(0));
1260 instancesId.push_back(statement->ColumnString(1));
1261 }
1262 }
1263
1264
1265 void SQLiteDatabaseWrapper::ApplyLookupResources(std::list<std::string>& resourcesId,
1266 std::list<std::string>* instancesId,
1267 const std::vector<DatabaseConstraint>& lookup,
1268 ResourceType queryLevel,
1269 size_t limit)
1270 {
1271 LookupFormatter formatter;
1272
1273 std::string sql;
1274 LookupFormatter::Apply(sql, formatter, lookup, queryLevel, limit);
1275
1276 sql = "CREATE TEMPORARY TABLE Lookup AS " + sql;
1277
1278 {
1279 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup");
1280 s.Run();
1281 }
1282
1283 {
1284 SQLite::Statement statement(db_, sql);
1285 formatter.Bind(statement);
1286 statement.Run();
1287 }
1288
1289 if (instancesId != NULL)
1290 {
1291 AnswerLookup(resourcesId, *instancesId, db_, queryLevel);
1292 }
1293 else
1294 {
1295 resourcesId.clear();
1296
1297 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId FROM Lookup");
1298
1299 while (s.Step())
1300 {
1301 resourcesId.push_back(s.ColumnString(0));
1302 }
1303 }
1304 }
1305
1306
1307 int64_t SQLiteDatabaseWrapper::GetLastChangeIndex()
1308 {
1309 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1310 "SELECT seq FROM sqlite_sequence WHERE name='Changes'");
1311
1312 if (s.Step())
1313 {
1314 int64_t c = s.ColumnInt(0);
1315 assert(!s.Step());
1316 return c;
1317 }
1318 else
1319 {
1320 // No change has been recorded so far in the database
1321 return 0;
1322 }
1323 }
1324
1325
1326 void SQLiteDatabaseWrapper::TagMostRecentPatient(int64_t patient)
1327 {
1328 {
1329 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1330 "DELETE FROM PatientRecyclingOrder WHERE patientId=?");
1331 s.BindInt64(0, patient);
1332 s.Run();
1333
1334 assert(db_.GetLastChangeCount() == 0 ||
1335 db_.GetLastChangeCount() == 1);
1336
1337 if (db_.GetLastChangeCount() == 0)
1338 {
1339 // The patient was protected, there was nothing to delete from the recycling order
1340 return;
1341 }
1342 }
1343
1344 {
1345 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1346 "INSERT INTO PatientRecyclingOrder VALUES(NULL, ?)");
1347 s.BindInt64(0, patient);
1348 s.Run();
1349 }
1350 }
1351 }