comparison OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp @ 5090:cc1a8b3ff319

Made the default SQLite DB more robust wrt future updates like adding new columns in DB.
author Alain Mazy <am@osimis.io>
date Fri, 30 Sep 2022 15:17:21 +0200
parents e6f26be401fa
children 0ea402b4d901
comparison
equal deleted inserted replaced
5089:80ac0955e4e7 5090:cc1a8b3ff319
322 virtual void AddAttachment(int64_t id, 322 virtual void AddAttachment(int64_t id,
323 const FileInfo& attachment, 323 const FileInfo& attachment,
324 int64_t revision) ORTHANC_OVERRIDE 324 int64_t revision) ORTHANC_OVERRIDE
325 { 325 {
326 // TODO - REVISIONS 326 // TODO - REVISIONS
327 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO AttachedFiles VALUES(?, ?, ?, ?, ?, ?, ?, ?)"); 327 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO AttachedFiles (id, fileType, uuid, compressedSize, uncompressedSize, compressionType, uncompressedMD5, compressedMD5) VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
328 s.BindInt64(0, id); 328 s.BindInt64(0, id);
329 s.BindInt(1, attachment.GetContentType()); 329 s.BindInt(1, attachment.GetContentType());
330 s.BindString(2, attachment.GetUuid()); 330 s.BindString(2, attachment.GetUuid());
331 s.BindInt64(3, attachment.GetCompressedSize()); 331 s.BindInt64(3, attachment.GetCompressedSize());
332 s.BindInt64(4, attachment.GetUncompressedSize()); 332 s.BindInt64(4, attachment.GetUncompressedSize());
431 431
432 // From the "ICreateInstance" interface 432 // From the "ICreateInstance" interface
433 virtual int64_t CreateResource(const std::string& publicId, 433 virtual int64_t CreateResource(const std::string& publicId,
434 ResourceType type) ORTHANC_OVERRIDE 434 ResourceType type) ORTHANC_OVERRIDE
435 { 435 {
436 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Resources VALUES(NULL, ?, ?, NULL)"); 436 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Resources (internalId, resourceType, publicId, parentId) VALUES(NULL, ?, ?, NULL)");
437 s.BindInt(0, type); 437 s.BindInt(0, type);
438 s.BindString(1, publicId); 438 s.BindString(1, publicId);
439 s.Run(); 439 s.Run();
440 return db_.GetLastInsertRowId(); 440 return db_.GetLastInsertRowId();
441 } 441 }
766 766
767 767
768 virtual void LogChange(int64_t internalId, 768 virtual void LogChange(int64_t internalId,
769 const ServerIndexChange& change) ORTHANC_OVERRIDE 769 const ServerIndexChange& change) ORTHANC_OVERRIDE
770 { 770 {
771 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Changes VALUES(NULL, ?, ?, ?, ?)"); 771 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Changes (seq, changeType, internalId, resourceType, date) VALUES(NULL, ?, ?, ?, ?)");
772 s.BindInt(0, change.GetChangeType()); 772 s.BindInt(0, change.GetChangeType());
773 s.BindInt64(1, internalId); 773 s.BindInt64(1, internalId);
774 s.BindInt(2, change.GetResourceType()); 774 s.BindInt(2, change.GetResourceType());
775 s.BindString(3, change.GetDate()); 775 s.BindString(3, change.GetDate());
776 s.Run(); 776 s.Run();
778 778
779 779
780 virtual void LogExportedResource(const ExportedResource& resource) ORTHANC_OVERRIDE 780 virtual void LogExportedResource(const ExportedResource& resource) ORTHANC_OVERRIDE
781 { 781 {
782 SQLite::Statement s(db_, SQLITE_FROM_HERE, 782 SQLite::Statement s(db_, SQLITE_FROM_HERE,
783 "INSERT INTO ExportedResources VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?)"); 783 "INSERT INTO ExportedResources (seq, resourceType, publicId, remoteModality, patientId, studyInstanceUid, seriesInstanceUid, sopInstanceUid, date) VALUES(NULL, ?, ?, ?, ?, ?, ?, ?, ?)");
784 784
785 s.BindInt(0, resource.GetResourceType()); 785 s.BindInt(0, resource.GetResourceType());
786 s.BindString(1, resource.GetPublicId()); 786 s.BindString(1, resource.GetPublicId());
787 s.BindString(2, resource.GetModality()); 787 s.BindString(2, resource.GetModality());
788 s.BindString(3, resource.GetPatientId()); 788 s.BindString(3, resource.GetPatientId());
972 const std::string& value) ORTHANC_OVERRIDE 972 const std::string& value) ORTHANC_OVERRIDE
973 { 973 {
974 // The "shared" info is not used by the SQLite database, as it 974 // The "shared" info is not used by the SQLite database, as it
975 // can only be used by one Orthanc server. 975 // can only be used by one Orthanc server.
976 976
977 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO GlobalProperties VALUES(?, ?)"); 977 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO GlobalProperties (property, value) VALUES(?, ?)");
978 s.BindInt(0, property); 978 s.BindInt(0, property);
979 s.BindString(1, value); 979 s.BindString(1, value);
980 s.Run(); 980 s.Run();
981 } 981 }
982 982
984 // From the "ISetResourcesContent" interface 984 // From the "ISetResourcesContent" interface
985 virtual void SetIdentifierTag(int64_t id, 985 virtual void SetIdentifierTag(int64_t id,
986 const DicomTag& tag, 986 const DicomTag& tag,
987 const std::string& value) ORTHANC_OVERRIDE 987 const std::string& value) ORTHANC_OVERRIDE
988 { 988 {
989 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO DicomIdentifiers VALUES(?, ?, ?, ?)"); 989 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO DicomIdentifiers (id, tagGroup, tagElement, value) VALUES(?, ?, ?, ?)");
990 s.BindInt64(0, id); 990 s.BindInt64(0, id);
991 s.BindInt(1, tag.GetGroup()); 991 s.BindInt(1, tag.GetGroup());
992 s.BindInt(2, tag.GetElement()); 992 s.BindInt(2, tag.GetElement());
993 s.BindString(3, value); 993 s.BindString(3, value);
994 s.Run(); 994 s.Run();
1004 s.BindInt64(0, internalId); 1004 s.BindInt64(0, internalId);
1005 s.Run(); 1005 s.Run();
1006 } 1006 }
1007 else if (IsProtectedPatient(internalId)) 1007 else if (IsProtectedPatient(internalId))
1008 { 1008 {
1009 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO PatientRecyclingOrder VALUES(NULL, ?)"); 1009 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO PatientRecyclingOrder (seq, patientId) VALUES(NULL, ?)");
1010 s.BindInt64(0, internalId); 1010 s.BindInt64(0, internalId);
1011 s.Run(); 1011 s.Run();
1012 } 1012 }
1013 else 1013 else
1014 { 1014 {
1020 // From the "ISetResourcesContent" interface 1020 // From the "ISetResourcesContent" interface
1021 virtual void SetMainDicomTag(int64_t id, 1021 virtual void SetMainDicomTag(int64_t id,
1022 const DicomTag& tag, 1022 const DicomTag& tag,
1023 const std::string& value) ORTHANC_OVERRIDE 1023 const std::string& value) ORTHANC_OVERRIDE
1024 { 1024 {
1025 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO MainDicomTags VALUES(?, ?, ?, ?)"); 1025 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO MainDicomTags (id, tagGroup, tagElement, value) VALUES(?, ?, ?, ?)");
1026 s.BindInt64(0, id); 1026 s.BindInt64(0, id);
1027 s.BindInt(1, tag.GetGroup()); 1027 s.BindInt(1, tag.GetGroup());
1028 s.BindInt(2, tag.GetElement()); 1028 s.BindInt(2, tag.GetElement());
1029 s.BindString(3, value); 1029 s.BindString(3, value);
1030 s.Run(); 1030 s.Run();
1035 MetadataType type, 1035 MetadataType type,
1036 const std::string& value, 1036 const std::string& value,
1037 int64_t revision) ORTHANC_OVERRIDE 1037 int64_t revision) ORTHANC_OVERRIDE
1038 { 1038 {
1039 // TODO - REVISIONS 1039 // TODO - REVISIONS
1040 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO Metadata VALUES(?, ?, ?)"); 1040 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT OR REPLACE INTO Metadata (id, type, value) VALUES(?, ?, ?)");
1041 s.BindInt64(0, id); 1041 s.BindInt64(0, id);
1042 s.BindInt(1, type); 1042 s.BindInt(1, type);
1043 s.BindString(2, value); 1043 s.BindString(2, value);
1044 s.Run(); 1044 s.Run();
1045 } 1045 }
1070 } 1070 }
1071 } 1071 }
1072 1072
1073 { 1073 {
1074 SQLite::Statement s(db_, SQLITE_FROM_HERE, 1074 SQLite::Statement s(db_, SQLITE_FROM_HERE,
1075 "INSERT INTO PatientRecyclingOrder VALUES(NULL, ?)"); 1075 "INSERT INTO PatientRecyclingOrder (seq, patientId) VALUES(NULL, ?)");
1076 s.BindInt64(0, patient); 1076 s.BindInt64(0, patient);
1077 s.Run(); 1077 s.Run();
1078 } 1078 }
1079 } 1079 }
1080 }; 1080 };
1477 1477
1478 1478
1479 int64_t SQLiteDatabaseWrapper::UnitTestsTransaction::CreateResource(const std::string& publicId, 1479 int64_t SQLiteDatabaseWrapper::UnitTestsTransaction::CreateResource(const std::string& publicId,
1480 ResourceType type) 1480 ResourceType type)
1481 { 1481 {
1482 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Resources VALUES(NULL, ?, ?, NULL)"); 1482 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO Resources (internalId, resourceType, publicId, parentId) VALUES(NULL, ?, ?, NULL)");
1483 s.BindInt(0, type); 1483 s.BindInt(0, type);
1484 s.BindString(1, publicId); 1484 s.BindString(1, publicId);
1485 s.Run(); 1485 s.Run();
1486 return db_.GetLastInsertRowId(); 1486 return db_.GetLastInsertRowId();
1487 } 1487 }
1499 1499
1500 void SQLiteDatabaseWrapper::UnitTestsTransaction::SetIdentifierTag(int64_t id, 1500 void SQLiteDatabaseWrapper::UnitTestsTransaction::SetIdentifierTag(int64_t id,
1501 const DicomTag& tag, 1501 const DicomTag& tag,
1502 const std::string& value) 1502 const std::string& value)
1503 { 1503 {
1504 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO DicomIdentifiers VALUES(?, ?, ?, ?)"); 1504 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO DicomIdentifiers (id, tagGroup, tagElement, value) VALUES(?, ?, ?, ?)");
1505 s.BindInt64(0, id); 1505 s.BindInt64(0, id);
1506 s.BindInt(1, tag.GetGroup()); 1506 s.BindInt(1, tag.GetGroup());
1507 s.BindInt(2, tag.GetElement()); 1507 s.BindInt(2, tag.GetElement());
1508 s.BindString(3, value); 1508 s.BindString(3, value);
1509 s.Run(); 1509 s.Run();
1512 1512
1513 void SQLiteDatabaseWrapper::UnitTestsTransaction::SetMainDicomTag(int64_t id, 1513 void SQLiteDatabaseWrapper::UnitTestsTransaction::SetMainDicomTag(int64_t id,
1514 const DicomTag& tag, 1514 const DicomTag& tag,
1515 const std::string& value) 1515 const std::string& value)
1516 { 1516 {
1517 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO MainDicomTags VALUES(?, ?, ?, ?)"); 1517 SQLite::Statement s(db_, SQLITE_FROM_HERE, "INSERT INTO MainDicomTags (id, tagGroup, tagElement, value) VALUES(?, ?, ?, ?)");
1518 s.BindInt64(0, id); 1518 s.BindInt64(0, id);
1519 s.BindInt(1, tag.GetGroup()); 1519 s.BindInt(1, tag.GetGroup());
1520 s.BindInt(2, tag.GetElement()); 1520 s.BindInt(2, tag.GetElement());
1521 s.BindString(3, value); 1521 s.BindString(3, value);
1522 s.Run(); 1522 s.Run();