Mercurial > hg > orthanc

comparison OrthancServer/Database/SQLiteDatabaseWrapper.cpp @ 3093:2e1808b6146a db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
reorganization of folders
author Sebastien Jodogne <s.jodogne@gmail.com>
date Sat, 05 Jan 2019 16:09:21 +0100
parents OrthancServer/SQLiteDatabaseWrapper.cpp@1a75595d8e44
children 2e1711f80f74
comparison
equal deleted inserted replaced
3092:fc57988dbfd8 3093:2e1808b6146a
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 void SQLiteDatabaseWrapper::Open()
333 {
334 db_.Execute("PRAGMA ENCODING=\"UTF-8\";");
335
336 // Performance tuning of SQLite with PRAGMAs
337 // http://www.sqlite.org/pragma.html
338 db_.Execute("PRAGMA SYNCHRONOUS=NORMAL;");
339 db_.Execute("PRAGMA JOURNAL_MODE=WAL;");
340 db_.Execute("PRAGMA LOCKING_MODE=EXCLUSIVE;");
341 db_.Execute("PRAGMA WAL_AUTOCHECKPOINT=1000;");
342 //db_.Execute("PRAGMA TEMP_STORE=memory");
343
344 // Make "LIKE" case-sensitive in SQLite
345 db_.Execute("PRAGMA case_sensitive_like = true;");
346
347 if (!db_.DoesTableExist("GlobalProperties"))
348 {
349 LOG(INFO) << "Creating the database";
350 std::string query;
351 EmbeddedResources::GetFileResource(query, EmbeddedResources::PREPARE_DATABASE);
352 db_.Execute(query);
353 }
354
355 // Check the version of the database
356 std::string tmp;
357 if (!LookupGlobalProperty(tmp, GlobalProperty_DatabaseSchemaVersion))
358 {
359 tmp = "Unknown";
360 }
361
362 bool ok = false;
363 try
364 {
365 LOG(INFO) << "Version of the Orthanc database: " << tmp;
366 version_ = boost::lexical_cast<unsigned int>(tmp);
367 ok = true;
368 }
369 catch (boost::bad_lexical_cast&)
370 {
371 }
372
373 if (!ok)
374 {
375 throw OrthancException(ErrorCode_IncompatibleDatabaseVersion,
376 "Incompatible version of the Orthanc database: " + tmp);
377 }
378
379 // New in Orthanc 1.5.1
380 if (version_ == 6)
381 {
382 if (!LookupGlobalProperty(tmp, GlobalProperty_GetTotalSizeIsFast) ||
383 tmp != "1")
384 {
385 LOG(INFO) << "Installing the SQLite triggers to track the size of the attachments";
386 std::string query;
387 EmbeddedResources::GetFileResource(query, EmbeddedResources::INSTALL_TRACK_ATTACHMENTS_SIZE);
388 db_.Execute(query);
389 }
390 }
391
392 signalRemainingAncestor_ = new Internals::SignalRemainingAncestor;
393 db_.Register(signalRemainingAncestor_);
394 }
395
396
397 static void ExecuteUpgradeScript(SQLite::Connection& db,
398 EmbeddedResources::FileResourceId script)
399 {
400 std::string upgrade;
401 EmbeddedResources::GetFileResource(upgrade, script);
402 db.BeginTransaction();
403 db.Execute(upgrade);
404 db.CommitTransaction();
405 }
406
407
408 void SQLiteDatabaseWrapper::Upgrade(unsigned int targetVersion,
409 IStorageArea& storageArea)
410 {
411 if (targetVersion != 6)
412 {
413 throw OrthancException(ErrorCode_IncompatibleDatabaseVersion);
414 }
415
416 // This version of Orthanc is only compatible with versions 3, 4,
417 // 5 and 6 of the DB schema
418 if (version_ != 3 &&
419 version_ != 4 &&
420 version_ != 5 &&
421 version_ != 6)
422 {
423 throw OrthancException(ErrorCode_IncompatibleDatabaseVersion);
424 }
425
426 if (version_ == 3)
427 {
428 LOG(WARNING) << "Upgrading database version from 3 to 4";
429 ExecuteUpgradeScript(db_, EmbeddedResources::UPGRADE_DATABASE_3_TO_4);
430 version_ = 4;
431 }
432
433 if (version_ == 4)
434 {
435 LOG(WARNING) << "Upgrading database version from 4 to 5";
436 ExecuteUpgradeScript(db_, EmbeddedResources::UPGRADE_DATABASE_4_TO_5);
437 version_ = 5;
438 }
439
440 if (version_ == 5)
441 {
442 LOG(WARNING) << "Upgrading database version from 5 to 6";
443 // No change in the DB schema, the step from version 5 to 6 only
444 // consists in reconstructing the main DICOM tags information
445 // (as more tags got included).
446 db_.BeginTransaction();
447 ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Patient);
448 ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Study);
449 ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Series);
450 ServerToolbox::ReconstructMainDicomTags(*this, storageArea, ResourceType_Instance);
451 db_.Execute("UPDATE GlobalProperties SET value=\"6\" WHERE property=" +
452 boost::lexical_cast<std::string>(GlobalProperty_DatabaseSchemaVersion) + ";");
453 db_.CommitTransaction();
454 version_ = 6;
455 }
456 }
457
458
459 void SQLiteDatabaseWrapper::SetListener(IDatabaseListener& listener)
460 {
461 listener_ = &listener;
462 db_.Register(new Internals::SignalFileDeleted(listener));
463 db_.Register(new Internals::SignalResourceDeleted(listener));
464 }
465
466
467 void SQLiteDatabaseWrapper::ClearTable(const std::string& tableName)
468 {
469 db_.Execute("DELETE FROM " + tableName);
470 }
471
472
473 bool SQLiteDatabaseWrapper::LookupParent(int64_t& parentId,
474 int64_t resourceId)
475 {
476 SQLite::Statement s(db_, SQLITE_FROM_HERE,
477 "SELECT parentId FROM Resources WHERE internalId=?");
478 s.BindInt64(0, resourceId);
479
480 if (!s.Step())
481 {
482 throw OrthancException(ErrorCode_UnknownResource);
483 }
484
485 if (s.ColumnIsNull(0))
486 {
487 return false;
488 }
489 else
490 {
491 parentId = s.ColumnInt(0);
492 return true;
493 }
494 }
495
496
497 ResourceType SQLiteDatabaseWrapper::GetResourceType(int64_t resourceId)
498 {
499 SQLite::Statement s(db_, SQLITE_FROM_HERE,
500 "SELECT resourceType FROM Resources WHERE internalId=?");
501 s.BindInt64(0, resourceId);
502
503 if (s.Step())
504 {
505 return static_cast<ResourceType>(s.ColumnInt(0));
506 }
507 else
508 {
509 throw OrthancException(ErrorCode_UnknownResource);
510 }
511 }
512
513
514 std::string SQLiteDatabaseWrapper::GetPublicId(int64_t resourceId)
515 {
516 SQLite::Statement s(db_, SQLITE_FROM_HERE,
517 "SELECT publicId FROM Resources WHERE internalId=?");
518 s.BindInt64(0, resourceId);
519
520 if (s.Step())
521 {
522 return s.ColumnString(0);
523 }
524 else
525 {
526 throw OrthancException(ErrorCode_UnknownResource);
527 }
528 }
529
530
531 void SQLiteDatabaseWrapper::GetChanges(std::list<ServerIndexChange>& target /*out*/,
532 bool& done /*out*/,
533 int64_t since,
534 uint32_t maxResults)
535 {
536 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes WHERE seq>? ORDER BY seq LIMIT ?");
537 s.BindInt64(0, since);
538 s.BindInt(1, maxResults + 1);
539 GetChangesInternal(target, done, s, maxResults);
540 }
541
542
543 void SQLiteDatabaseWrapper::GetLastChange(std::list<ServerIndexChange>& target /*out*/)
544 {
545 bool done; // Ignored
546 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes ORDER BY seq DESC LIMIT 1");
547 GetChangesInternal(target, done, s, 1);
548 }
549
550
551 class SQLiteDatabaseWrapper::Transaction : public IDatabaseWrapper::ITransaction
552 {
553 private:
554 SQLiteDatabaseWrapper& that_;
555 std::auto_ptr<SQLite::Transaction> transaction_;
556 int64_t initialDiskSize_;
557
558 public:
559 Transaction(SQLiteDatabaseWrapper& that) :
560 that_(that),
561 transaction_(new SQLite::Transaction(that_.db_))
562 {
563 #if defined(NDEBUG)
564 // Release mode
565 initialDiskSize_ = 0;
566 #else
567 // Debug mode
568 initialDiskSize_ = static_cast<int64_t>(that_.GetTotalCompressedSize());
569 #endif
570 }
571
572 virtual void Begin()
573 {
574 transaction_->Begin();
575 }
576
577 virtual void Rollback()
578 {
579 transaction_->Rollback();
580 }
581
582 virtual void Commit(int64_t fileSizeDelta /* only used in debug */)
583 {
584 transaction_->Commit();
585
586 assert(initialDiskSize_ + fileSizeDelta >= 0 &&
587 initialDiskSize_ + fileSizeDelta == static_cast<int64_t>(that_.GetTotalCompressedSize()));
588 }
589 };
590
591
592 IDatabaseWrapper::ITransaction* SQLiteDatabaseWrapper::StartTransaction()
593 {
594 return new Transaction(*this);
595 }
596
597
598 void SQLiteDatabaseWrapper::GetAllMetadata(std::map<MetadataType, std::string>& target,
599 int64_t id)
600 {
601 target.clear();
602
603 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT type, value FROM Metadata WHERE id=?");
604 s.BindInt64(0, id);
605
606 while (s.Step())
607 {
608 MetadataType key = static_cast<MetadataType>(s.ColumnInt(0));
609 target[key] = s.ColumnString(1);
610 }
611 }
612
613
614 void SQLiteDatabaseWrapper::SetGlobalProperty(GlobalProperty property,
615 const std::string& value)
616 {
617 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO GlobalProperties VALUES(?, ?)");
618 s.BindInt(0, property);
619 s.BindString(1, value);
620 s.Run();
621 }
622
623
624 bool SQLiteDatabaseWrapper::LookupGlobalProperty(std::string& target,
625 GlobalProperty property)
626 {
627 SQLite::Statement s(db_, SQLITE_FROM_HERE,
628 "SELECT value FROM GlobalProperties WHERE property=?");
629 s.BindInt(0, property);
630
631 if (!s.Step())
632 {
633 return false;
634 }
635 else
636 {
637 target = s.ColumnString(0);
638 return true;
639 }
640 }
641
642
643 int64_t SQLiteDatabaseWrapper::CreateResource(const std::string& publicId,
644 ResourceType type)
645 {
646 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Resources VALUES(NULL, ?, ?, NULL)");
647 s.BindInt(0, type);
648 s.BindString(1, publicId);
649 s.Run();
650 return db_.GetLastInsertRowId();
651 }
652
653
654 bool SQLiteDatabaseWrapper::LookupResource(int64_t& id,
655 ResourceType& type,
656 const std::string& publicId)
657 {
658 SQLite::Statement s(db_, SQLITE_FROM_HERE,
659 "SELECT internalId, resourceType FROM Resources WHERE publicId=?");
660 s.BindString(0, publicId);
661
662 if (!s.Step())
663 {
664 return false;
665 }
666 else
667 {
668 id = s.ColumnInt(0);
669 type = static_cast<ResourceType>(s.ColumnInt(1));
670
671 // Check whether there is a single resource with this public id
672 assert(!s.Step());
673
674 return true;
675 }
676 }
677
678
679 void SQLiteDatabaseWrapper::AttachChild(int64_t parent,
680 int64_t child)
681 {
682 SQLite::Statement s(db_, SQLITE_FROM_HERE, "UPDATE Resources SET parentId = ? WHERE internalId = ?");
683 s.BindInt64(0, parent);
684 s.BindInt64(1, child);
685 s.Run();
686 }
687
688
689 void SQLiteDatabaseWrapper::SetMetadata(int64_t id,
690 MetadataType type,
691 const std::string& value)
692 {
693 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO Metadata VALUES(?, ?, ?)");
694 s.BindInt64(0, id);
695 s.BindInt(1, type);
696 s.BindString(2, value);
697 s.Run();
698 }
699
700
701 void SQLiteDatabaseWrapper::DeleteMetadata(int64_t id,
702 MetadataType type)
703 {
704 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM Metadata WHERE id=? and type=?");
705 s.BindInt64(0, id);
706 s.BindInt(1, type);
707 s.Run();
708 }
709
710
711 bool SQLiteDatabaseWrapper::LookupMetadata(std::string& target,
712 int64_t id,
713 MetadataType type)
714 {
715 SQLite::Statement s(db_, SQLITE_FROM_HERE,
716 "SELECT value FROM Metadata WHERE id=? AND type=?");
717 s.BindInt64(0, id);
718 s.BindInt(1, type);
719
720 if (!s.Step())
721 {
722 return false;
723 }
724 else
725 {
726 target = s.ColumnString(0);
727 return true;
728 }
729 }
730
731
732 void SQLiteDatabaseWrapper::ListAvailableMetadata(std::list<MetadataType>& target,
733 int64_t id)
734 {
735 target.clear();
736
737 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT type FROM Metadata WHERE id=?");
738 s.BindInt64(0, id);
739
740 while (s.Step())
741 {
742 target.push_back(static_cast<MetadataType>(s.ColumnInt(0)));
743 }
744 }
745
746
747 void SQLiteDatabaseWrapper::AddAttachment(int64_t id,
748 const FileInfo& attachment)
749 {
750 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO AttachedFiles VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
751 s.BindInt64(0, id);
752 s.BindInt(1, attachment.GetContentType());
753 s.BindString(2, attachment.GetUuid());
754 s.BindInt64(3, attachment.GetCompressedSize());
755 s.BindInt64(4, attachment.GetUncompressedSize());
756 s.BindInt(5, attachment.GetCompressionType());
757 s.BindString(6, attachment.GetUncompressedMD5());
758 s.BindString(7, attachment.GetCompressedMD5());
759 s.Run();
760 }
761
762
763 void SQLiteDatabaseWrapper::DeleteAttachment(int64_t id,
764 FileContentType attachment)
765 {
766 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM AttachedFiles WHERE id=? AND fileType=?");
767 s.BindInt64(0, id);
768 s.BindInt(1, attachment);
769 s.Run();
770 }
771
772
773 void SQLiteDatabaseWrapper::ListAvailableAttachments(std::list<FileContentType>& target,
774 int64_t id)
775 {
776 target.clear();
777
778 SQLite::Statement s(db_, SQLITE_FROM_HERE,
779 "SELECT fileType FROM AttachedFiles WHERE id=?");
780 s.BindInt64(0, id);
781
782 while (s.Step())
783 {
784 target.push_back(static_cast<FileContentType>(s.ColumnInt(0)));
785 }
786 }
787
788 bool SQLiteDatabaseWrapper::LookupAttachment(FileInfo& attachment,
789 int64_t id,
790 FileContentType contentType)
791 {
792 SQLite::Statement s(db_, SQLITE_FROM_HERE,
793 "SELECT uuid, uncompressedSize, compressionType, compressedSize, "
794 "uncompressedMD5, compressedMD5 FROM AttachedFiles WHERE id=? AND fileType=?");
795 s.BindInt64(0, id);
796 s.BindInt(1, contentType);
797
798 if (!s.Step())
799 {
800 return false;
801 }
802 else
803 {
804 attachment = FileInfo(s.ColumnString(0),
805 contentType,
806 s.ColumnInt64(1),
807 s.ColumnString(4),
808 static_cast<CompressionType>(s.ColumnInt(2)),
809 s.ColumnInt64(3),
810 s.ColumnString(5));
811 return true;
812 }
813 }
814
815
816 void SQLiteDatabaseWrapper::ClearMainDicomTags(int64_t id)
817 {
818 {
819 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM DicomIdentifiers WHERE id=?");
820 s.BindInt64(0, id);
821 s.Run();
822 }
823
824 {
825 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM MainDicomTags WHERE id=?");
826 s.BindInt64(0, id);
827 s.Run();
828 }
829 }
830
831
832 void SQLiteDatabaseWrapper::SetMainDicomTag(int64_t id,
833 const DicomTag& tag,
834 const std::string& value)
835 {
836 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO MainDicomTags VALUES(?, ?, ?, ?)");
837 s.BindInt64(0, id);
838 s.BindInt(1, tag.GetGroup());
839 s.BindInt(2, tag.GetElement());
840 s.BindString(3, value);
841 s.Run();
842 }
843
844
845 void SQLiteDatabaseWrapper::SetIdentifierTag(int64_t id,
846 const DicomTag& tag,
847 const std::string& value)
848 {
849 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO DicomIdentifiers VALUES(?, ?, ?, ?)");
850 s.BindInt64(0, id);
851 s.BindInt(1, tag.GetGroup());
852 s.BindInt(2, tag.GetElement());
853 s.BindString(3, value);
854 s.Run();
855 }
856
857
858 void SQLiteDatabaseWrapper::GetMainDicomTags(DicomMap& map,
859 int64_t id)
860 {
861 map.Clear();
862
863 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM MainDicomTags WHERE id=?");
864 s.BindInt64(0, id);
865 while (s.Step())
866 {
867 map.SetValue(s.ColumnInt(1),
868 s.ColumnInt(2),
869 s.ColumnString(3), false);
870 }
871 }
872
873
874 void SQLiteDatabaseWrapper::GetChildrenPublicId(std::list<std::string>& target,
875 int64_t id)
876 {
877 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT a.publicId FROM Resources AS a, Resources AS b "
878 "WHERE a.parentId = b.internalId AND b.internalId = ?");
879 s.BindInt64(0, id);
880
881 target.clear();
882
883 while (s.Step())
884 {
885 target.push_back(s.ColumnString(0));
886 }
887 }
888
889
890 void SQLiteDatabaseWrapper::GetChildrenInternalId(std::list<int64_t>& target,
891 int64_t id)
892 {
893 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT a.internalId FROM Resources AS a, Resources AS b "
894 "WHERE a.parentId = b.internalId AND b.internalId = ?");
895 s.BindInt64(0, id);
896
897 target.clear();
898
899 while (s.Step())
900 {
901 target.push_back(s.ColumnInt64(0));
902 }
903 }
904
905
906 void SQLiteDatabaseWrapper::LogChange(int64_t internalId,
907 const ServerIndexChange& change)
908 {
909 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Changes VALUES(NULL, ?, ?, ?, ?)");
910 s.BindInt(0, change.GetChangeType());
911 s.BindInt64(1, internalId);
912 s.BindInt(2, change.GetResourceType());
913 s.BindString(3, change.GetDate());
914 s.Run();
915 }
916
917
918 void SQLiteDatabaseWrapper::LogExportedResource(const ExportedResource& resource)
919 {
920 SQLite::Statement s(db_, SQLITE_FROM_HERE,
921 "INSERT INTO ExportedResources VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?)");
922
923 s.BindInt(0, resource.GetResourceType());
924 s.BindString(1, resource.GetPublicId());
925 s.BindString(2, resource.GetModality());
926 s.BindString(3, resource.GetPatientId());
927 s.BindString(4, resource.GetStudyInstanceUid());
928 s.BindString(5, resource.GetSeriesInstanceUid());
929 s.BindString(6, resource.GetSopInstanceUid());
930 s.BindString(7, resource.GetDate());
931 s.Run();
932 }
933
934
935 void SQLiteDatabaseWrapper::GetExportedResources(std::list<ExportedResource>& target,
936 bool& done,
937 int64_t since,
938 uint32_t maxResults)
939 {
940 SQLite::Statement s(db_, SQLITE_FROM_HERE,
941 "SELECT * FROM ExportedResources WHERE seq>? ORDER BY seq LIMIT ?");
942 s.BindInt64(0, since);
943 s.BindInt(1, maxResults + 1);
944 GetExportedResourcesInternal(target, done, s, maxResults);
945 }
946
947
948 void SQLiteDatabaseWrapper::GetLastExportedResource(std::list<ExportedResource>& target)
949 {
950 bool done; // Ignored
951 SQLite::Statement s(db_, SQLITE_FROM_HERE,
952 "SELECT * FROM ExportedResources ORDER BY seq DESC LIMIT 1");
953 GetExportedResourcesInternal(target, done, s, 1);
954 }
955
956
957 uint64_t SQLiteDatabaseWrapper::GetTotalCompressedSize()
958 {
959 // Old SQL query that was used in Orthanc <= 1.5.0:
960 // SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(compressedSize) FROM AttachedFiles");
961
962 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT value FROM GlobalIntegers WHERE key=0");
963 s.Run();
964 return static_cast<uint64_t>(s.ColumnInt64(0));
965 }
966
967
968 uint64_t SQLiteDatabaseWrapper::GetTotalUncompressedSize()
969 {
970 // Old SQL query that was used in Orthanc <= 1.5.0:
971 // SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(uncompressedSize) FROM AttachedFiles");
972
973 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT value FROM GlobalIntegers WHERE key=1");
974 s.Run();
975 return static_cast<uint64_t>(s.ColumnInt64(0));
976 }
977
978
979 uint64_t SQLiteDatabaseWrapper::GetResourceCount(ResourceType resourceType)
980 {
981 SQLite::Statement s(db_, SQLITE_FROM_HERE,
982 "SELECT COUNT(*) FROM Resources WHERE resourceType=?");
983 s.BindInt(0, resourceType);
984
985 if (!s.Step())
986 {
987 return 0;
988 }
989 else
990 {
991 int64_t c = s.ColumnInt(0);
992 assert(!s.Step());
993 return c;
994 }
995 }
996
997
998 void SQLiteDatabaseWrapper::GetAllPublicIds(std::list<std::string>& target,
999 ResourceType resourceType)
1000 {
1001 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId FROM Resources WHERE resourceType=?");
1002 s.BindInt(0, resourceType);
1003
1004 target.clear();
1005 while (s.Step())
1006 {
1007 target.push_back(s.ColumnString(0));
1008 }
1009 }
1010
1011
1012 void SQLiteDatabaseWrapper::GetAllPublicIds(std::list<std::string>& target,
1013 ResourceType resourceType,
1014 size_t since,
1015 size_t limit)
1016 {
1017 if (limit == 0)
1018 {
1019 target.clear();
1020 return;
1021 }
1022
1023 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1024 "SELECT publicId FROM Resources WHERE "
1025 "resourceType=? LIMIT ? OFFSET ?");
1026 s.BindInt(0, resourceType);
1027 s.BindInt64(1, limit);
1028 s.BindInt64(2, since);
1029
1030 target.clear();
1031 while (s.Step())
1032 {
1033 target.push_back(s.ColumnString(0));
1034 }
1035 }
1036
1037
1038 bool SQLiteDatabaseWrapper::SelectPatientToRecycle(int64_t& internalId)
1039 {
1040 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1041 "SELECT patientId FROM PatientRecyclingOrder ORDER BY seq ASC LIMIT 1");
1042
1043 if (!s.Step())
1044 {
1045 // No patient remaining or all the patients are protected
1046 return false;
1047 }
1048 else
1049 {
1050 internalId = s.ColumnInt(0);
1051 return true;
1052 }
1053 }
1054
1055
1056 bool SQLiteDatabaseWrapper::SelectPatientToRecycle(int64_t& internalId,
1057 int64_t patientIdToAvoid)
1058 {
1059 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1060 "SELECT patientId FROM PatientRecyclingOrder "
1061 "WHERE patientId != ? ORDER BY seq ASC LIMIT 1");
1062 s.BindInt64(0, patientIdToAvoid);
1063
1064 if (!s.Step())
1065 {
1066 // No patient remaining or all the patients are protected
1067 return false;
1068 }
1069 else
1070 {
1071 internalId = s.ColumnInt(0);
1072 return true;
1073 }
1074 }
1075
1076
1077 bool SQLiteDatabaseWrapper::IsProtectedPatient(int64_t internalId)
1078 {
1079 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1080 "SELECT * FROM PatientRecyclingOrder WHERE patientId = ?");
1081 s.BindInt64(0, internalId);
1082 return !s.Step();
1083 }
1084
1085
1086 void SQLiteDatabaseWrapper::SetProtectedPatient(int64_t internalId,
1087 bool isProtected)
1088 {
1089 if (isProtected)
1090 {
1091 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DELETE FROM PatientRecyclingOrder WHERE patientId=?");
1092 s.BindInt64(0, internalId);
1093 s.Run();
1094 }
1095 else if (IsProtectedPatient(internalId))
1096 {
1097 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO PatientRecyclingOrder VALUES(NULL, ?)");
1098 s.BindInt64(0, internalId);
1099 s.Run();
1100 }
1101 else
1102 {
1103 // Nothing to do: The patient is already unprotected
1104 }
1105 }
1106
1107
1108 bool SQLiteDatabaseWrapper::IsExistingResource(int64_t internalId)
1109 {
1110 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1111 "SELECT * FROM Resources WHERE internalId=?");
1112 s.BindInt64(0, internalId);
1113 return s.Step();
1114 }
1115
1116
1117 bool SQLiteDatabaseWrapper::IsDiskSizeAbove(uint64_t threshold)
1118 {
1119 return GetTotalCompressedSize() > threshold;
1120 }
1121
1122
1123
1124 class SQLiteDatabaseWrapper::LookupFormatter : public ISqlLookupFormatter
1125 {
1126 private:
1127 std::list<std::string> values_;
1128
1129 public:
1130 virtual std::string GenerateParameter(const std::string& value)
1131 {
1132 values_.push_back(value);
1133 return "?";
1134 }
1135
1136 virtual std::string FormatResourceType(ResourceType level)
1137 {
1138 return boost::lexical_cast<std::string>(level);
1139 }
1140
1141 virtual std::string FormatWildcardEscape()
1142 {
1143 return "ESCAPE '\\'";
1144 }
1145
1146 void Bind(SQLite::Statement& statement) const
1147 {
1148 size_t pos = 0;
1149
1150 for (std::list<std::string>::const_iterator
1151 it = values_.begin(); it != values_.end(); ++it, pos++)
1152 {
1153 statement.BindString(pos, *it);
1154 }
1155 }
1156 };
1157
1158
1159 static void AnswerLookup(std::list<std::string>& resourcesId,
1160 std::list<std::string>& instancesId,
1161 SQLite::Connection& db,
1162 ResourceType level)
1163 {
1164 resourcesId.clear();
1165 instancesId.clear();
1166
1167 std::auto_ptr<SQLite::Statement> statement;
1168
1169 switch (level)
1170 {
1171 case ResourceType_Patient:
1172 {
1173 statement.reset(
1174 new SQLite::Statement(
1175 db, SQLITE_FROM_HERE,
1176 "SELECT patients.publicId, instances.publicID FROM Lookup AS patients "
1177 "INNER JOIN Resources studies ON patients.internalId=studies.parentId "
1178 "INNER JOIN Resources series ON studies.internalId=series.parentId "
1179 "INNER JOIN Resources instances ON series.internalId=instances.parentId "
1180 "GROUP BY patients.publicId"));
1181
1182 break;
1183 }
1184
1185 case ResourceType_Study:
1186 {
1187 statement.reset(
1188 new SQLite::Statement(
1189 db, SQLITE_FROM_HERE,
1190 "SELECT studies.publicId, instances.publicID FROM Lookup AS studies "
1191 "INNER JOIN Resources series ON studies.internalId=series.parentId "
1192 "INNER JOIN Resources instances ON series.internalId=instances.parentId "
1193 "GROUP BY studies.publicId"));
1194
1195 break;
1196 }
1197
1198 case ResourceType_Series:
1199 {
1200 statement.reset(
1201 new SQLite::Statement(
1202 db, SQLITE_FROM_HERE,
1203 "SELECT series.publicId, instances.publicID FROM Lookup AS series "
1204 "INNER JOIN Resources instances ON series.internalId=instances.parentId "
1205 "GROUP BY series.publicId"));
1206
1207 break;
1208 }
1209
1210 case ResourceType_Instance:
1211 {
1212 statement.reset(
1213 new SQLite::Statement(
1214 db, SQLITE_FROM_HERE, "SELECT publicId, publicId FROM Lookup"));
1215
1216 break;
1217 }
1218
1219 default:
1220 throw OrthancException(ErrorCode_InternalError);
1221 }
1222
1223 assert(statement.get() != NULL);
1224
1225 while (statement->Step())
1226 {
1227 resourcesId.push_back(statement->ColumnString(0));
1228 instancesId.push_back(statement->ColumnString(1));
1229 }
1230 }
1231
1232
1233 void SQLiteDatabaseWrapper::ApplyLookupResources(std::list<std::string>& resourcesId,
1234 std::list<std::string>* instancesId,
1235 const std::vector<DatabaseConstraint>& lookup,
1236 ResourceType queryLevel,
1237 size_t limit)
1238 {
1239 LookupFormatter formatter;
1240
1241 std::string sql;
1242 LookupFormatter::Apply(sql, formatter, lookup, queryLevel, limit);
1243
1244 sql = "CREATE TEMPORARY TABLE Lookup AS " + sql;
1245
1246 {
1247 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup");
1248 s.Run();
1249 }
1250
1251 {
1252 SQLite::Statement statement(db_, sql);
1253 formatter.Bind(statement);
1254 statement.Run();
1255 }
1256
1257 if (instancesId != NULL)
1258 {
1259 AnswerLookup(resourcesId, *instancesId, db_, queryLevel);
1260 }
1261 else
1262 {
1263 resourcesId.clear();
1264
1265 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId FROM Lookup");
1266
1267 while (s.Step())
1268 {
1269 resourcesId.push_back(s.ColumnString(0));
1270 }
1271 }
1272 }
1273 }