Mercurial > hg > orthanc-databases
comparison PostgreSQL/Plugins/PostgreSQLIndex.cpp @ 436:f16faa1fdc46 pg-transactions
InsertOrUpdateMetadata function + UpdateAndGetStatistics
author | Alain Mazy <am@osimis.io> |
---|---|
date | Fri, 15 Dec 2023 17:11:26 +0100 |
parents | 326f8304daa1 |
children | d979f25e60cf |
comparison
equal
deleted
inserted
replaced
435:326f8304daa1 | 436:f16faa1fdc46 |
---|---|
28 #include "PostgreSQLDefinitions.h" | 28 #include "PostgreSQLDefinitions.h" |
29 | 29 |
30 #include <EmbeddedResources.h> // Auto-generated file | 30 #include <EmbeddedResources.h> // Auto-generated file |
31 | 31 |
32 #include <Compatibility.h> // For std::unique_ptr<> | 32 #include <Compatibility.h> // For std::unique_ptr<> |
33 #include <Toolbox.h> | |
33 #include <Logging.h> | 34 #include <Logging.h> |
34 #include <OrthancException.h> | 35 #include <OrthancException.h> |
35 | 36 |
36 | 37 |
37 namespace Orthanc | 38 namespace Orthanc |
88 db.AdvisoryLock(POSTGRESQL_LOCK_INDEX); | 89 db.AdvisoryLock(POSTGRESQL_LOCK_INDEX); |
89 } | 90 } |
90 | 91 |
91 { | 92 { |
92 PostgreSQLDatabase::TransientAdvisoryLock lock(db, POSTGRESQL_LOCK_DATABASE_SETUP); | 93 PostgreSQLDatabase::TransientAdvisoryLock lock(db, POSTGRESQL_LOCK_DATABASE_SETUP); |
94 bool shouldInstallFastTotalStats2 = false; | |
93 | 95 |
94 if (clearAll_) | 96 if (clearAll_) |
95 { | 97 { |
96 db.ClearAll(); | 98 db.ClearAll(); |
97 } | 99 } |
133 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); | 135 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); |
134 } | 136 } |
135 | 137 |
136 if (revision == 1) | 138 if (revision == 1) |
137 { | 139 { |
138 LOG(WARNING) << "PostgreSQL plugin: adding UNIQUE(publicId) constraint to the 'Resources' table "; | 140 { |
139 t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE Resources ADD UNIQUE (publicId);"); | 141 LOG(WARNING) << "PostgreSQL plugin: adding UNIQUE(publicId) constraint to the 'Resources' table "; |
142 t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE Resources ADD UNIQUE (publicId);"); | |
143 } | |
144 | |
145 { | |
146 LOG(WARNING) << "PostgreSQL plugin: adding or replacing ResourceDeletedFunc"; | |
147 | |
148 std::string query; | |
149 Orthanc::EmbeddedResources::GetFileResource | |
150 (query, Orthanc::EmbeddedResources::POSTGRESQL_RESOURCE_DELETED_FUNC); | |
151 t.GetDatabaseTransaction().ExecuteMultiLines(query); | |
152 } | |
153 | |
154 { | |
155 LOG(WARNING) << "PostgreSQL plugin: adding or replacing InsertOrUpdateMetadata"; | |
156 | |
157 std::string query; | |
158 Orthanc::EmbeddedResources::GetFileResource | |
159 (query, Orthanc::EmbeddedResources::POSTGRESQL_INSERT_UPDATE_METADATA); | |
160 t.GetDatabaseTransaction().ExecuteMultiLines(query); | |
161 } | |
162 | |
163 shouldInstallFastTotalStats2 = true; | |
140 | 164 |
141 revision = 2; | 165 revision = 2; |
142 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); | 166 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); |
143 } | 167 } |
144 | 168 |
145 if (revision == 2) | 169 if (revision != 2) |
146 { | |
147 LOG(WARNING) << "PostgreSQL plugin: adding or replacing ResourceDeletedFunc"; | |
148 | |
149 std::string query; | |
150 Orthanc::EmbeddedResources::GetFileResource | |
151 (query, Orthanc::EmbeddedResources::POSTGRESQL_RESOURCE_DELETED_FUNC); | |
152 t.GetDatabaseTransaction().ExecuteMultiLines(query); | |
153 | |
154 revision = 3; | |
155 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); | |
156 } | |
157 | |
158 if (revision != 3) | |
159 { | 170 { |
160 LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema revision: " << revision; | 171 LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema revision: " << revision; |
161 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); | 172 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); |
162 } | 173 } |
163 | 174 |
238 t.GetDatabaseTransaction().ExecuteMultiLines(query); | 249 t.GetDatabaseTransaction().ExecuteMultiLines(query); |
239 | 250 |
240 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasCreateInstance, 3); | 251 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasCreateInstance, 3); |
241 } | 252 } |
242 | 253 |
243 | |
244 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, | 254 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, |
245 Orthanc::GlobalProperty_GetTotalSizeIsFast) || | 255 Orthanc::GlobalProperty_GetTotalSizeIsFast) || |
246 property != 1) | 256 property != 1) |
247 { | 257 { |
248 LOG(INFO) << "Installing the FastTotalSize extension"; | 258 LOG(INFO) << "Installing the FastTotalSize extension"; |
287 Orthanc::EmbeddedResources::GetFileResource | 297 Orthanc::EmbeddedResources::GetFileResource |
288 (query, Orthanc::EmbeddedResources::POSTGRESQL_GET_LAST_CHANGE_INDEX); | 298 (query, Orthanc::EmbeddedResources::POSTGRESQL_GET_LAST_CHANGE_INDEX); |
289 t.GetDatabaseTransaction().ExecuteMultiLines(query); | 299 t.GetDatabaseTransaction().ExecuteMultiLines(query); |
290 | 300 |
291 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetLastChangeIndex, 1); | 301 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetLastChangeIndex, 1); |
302 } | |
303 | |
304 if (shouldInstallFastTotalStats2) | |
305 { | |
306 LOG(WARNING) << "PostgreSQL plugin: installing FastTotalStats2 to replace FastTotalSize and FastCountResources"; | |
307 | |
308 std::string query; | |
309 Orthanc::EmbeddedResources::GetFileResource | |
310 (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_TOTAL_STATS_2); | |
311 t.GetDatabaseTransaction().ExecuteMultiLines(query); | |
292 } | 312 } |
293 | 313 |
294 t.Commit(); | 314 t.Commit(); |
295 } | 315 } |
296 | 316 |
463 return statement.ReadInteger64(0); | 483 return statement.ReadInteger64(0); |
464 } | 484 } |
465 } | 485 } |
466 } | 486 } |
467 | 487 |
488 void PostgreSQLIndex::UpdateAndGetStatistics(DatabaseManager& manager, | |
489 int64_t& patientsCount, | |
490 int64_t& studiesCount, | |
491 int64_t& seriesCount, | |
492 int64_t& instancesCount, | |
493 int64_t& compressedSize, | |
494 int64_t& uncompressedSize) | |
495 { | |
496 DatabaseManager::CachedStatement statement( | |
497 STATEMENT_FROM_HERE, manager, | |
498 "SELECT * FROM UpdateStatistics()"); | |
499 | |
500 statement.Execute(); | |
501 | |
502 patientsCount = statement.ReadInteger64(0); | |
503 studiesCount = statement.ReadInteger64(1); | |
504 seriesCount = statement.ReadInteger64(2); | |
505 instancesCount = statement.ReadInteger64(3); | |
506 compressedSize = statement.ReadInteger64(4); | |
507 uncompressedSize = statement.ReadInteger64(5); | |
508 } | |
509 | |
468 void PostgreSQLIndex::ClearDeletedFiles(DatabaseManager& manager) | 510 void PostgreSQLIndex::ClearDeletedFiles(DatabaseManager& manager) |
469 { | 511 { |
470 { // note: the temporary table lifespan is the session, not the transaction -> that's why we need the IF NOT EXISTS | 512 { // note: the temporary table lifespan is the session, not the transaction -> that's why we need the IF NOT EXISTS |
471 DatabaseManager::CachedStatement statement( | 513 DatabaseManager::CachedStatement statement( |
472 STATEMENT_FROM_HERE, manager, | 514 STATEMENT_FROM_HERE, manager, |
594 } | 636 } |
595 } | 637 } |
596 #endif | 638 #endif |
597 | 639 |
598 | 640 |
641 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 | |
642 static void ExecuteSetResourcesContentTags( | |
643 DatabaseManager& manager, | |
644 const std::string& table, | |
645 const std::string& variablePrefix, | |
646 uint32_t count, | |
647 const OrthancPluginResourcesContentTags* tags) | |
648 { | |
649 std::string sql; | |
650 Dictionary args; | |
651 | |
652 for (uint32_t i = 0; i < count; i++) | |
653 { | |
654 std::string name = variablePrefix + boost::lexical_cast<std::string>(i); | |
655 | |
656 args.SetUtf8Value(name, tags[i].value); | |
657 | |
658 std::string insert = ("(" + boost::lexical_cast<std::string>(tags[i].resource) + ", " + | |
659 boost::lexical_cast<std::string>(tags[i].group) + ", " + | |
660 boost::lexical_cast<std::string>(tags[i].element) + ", " + | |
661 "${" + name + "})"); | |
662 | |
663 if (sql.empty()) | |
664 { | |
665 sql = "INSERT INTO " + table + " VALUES " + insert; | |
666 } | |
667 else | |
668 { | |
669 sql += ", " + insert; | |
670 } | |
671 } | |
672 | |
673 if (!sql.empty()) | |
674 { | |
675 DatabaseManager::StandaloneStatement statement(manager, sql); | |
676 | |
677 for (uint32_t i = 0; i < count; i++) | |
678 { | |
679 statement.SetParameterType(variablePrefix + boost::lexical_cast<std::string>(i), | |
680 ValueType_Utf8String); | |
681 } | |
682 | |
683 statement.Execute(args); | |
684 } | |
685 } | |
686 #endif | |
687 | |
688 | |
689 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 | |
690 static void ExecuteSetResourcesContentMetadata( | |
691 DatabaseManager& manager, | |
692 bool hasRevisionsSupport, | |
693 uint32_t count, | |
694 const OrthancPluginResourcesContentMetadata* metadata) | |
695 { | |
696 if (count < 1) | |
697 { | |
698 return; | |
699 } | |
700 | |
701 std::vector<std::string> resourceIds; | |
702 std::vector<std::string> metadataTypes; | |
703 std::vector<std::string> metadataValues; | |
704 std::vector<std::string> revisions; | |
705 | |
706 Dictionary args; | |
707 | |
708 for (uint32_t i = 0; i < count; i++) | |
709 { | |
710 std::string argName = "m" + boost::lexical_cast<std::string>(i); | |
711 | |
712 args.SetUtf8Value(argName, metadata[i].value); | |
713 | |
714 resourceIds.push_back(boost::lexical_cast<std::string>(metadata[i].resource)); | |
715 metadataTypes.push_back(boost::lexical_cast<std::string>(metadata[i].metadata)); | |
716 metadataValues.push_back("${" + argName + "}"); | |
717 revisions.push_back("0"); | |
718 } | |
719 | |
720 std::string joinedResourceIds; | |
721 std::string joinedMetadataTypes; | |
722 std::string joinedMetadataValues; | |
723 std::string joinedRevisions; | |
724 | |
725 Orthanc::Toolbox::JoinStrings(joinedResourceIds, resourceIds, ","); | |
726 Orthanc::Toolbox::JoinStrings(joinedMetadataTypes, metadataTypes, ","); | |
727 Orthanc::Toolbox::JoinStrings(joinedMetadataValues, metadataValues, ","); | |
728 Orthanc::Toolbox::JoinStrings(joinedRevisions, revisions, ","); | |
729 | |
730 std::string sql = std::string("SELECT InsertOrUpdateMetadata(ARRAY[") + | |
731 joinedResourceIds + "], ARRAY[" + | |
732 joinedMetadataTypes + "], ARRAY[" + | |
733 joinedMetadataValues + "], ARRAY[" + | |
734 joinedRevisions + "])"; | |
735 | |
736 DatabaseManager::StandaloneStatement statement(manager, sql); | |
737 | |
738 for (uint32_t i = 0; i < count; i++) | |
739 { | |
740 statement.SetParameterType("m" + boost::lexical_cast<std::string>(i), | |
741 ValueType_Utf8String); | |
742 } | |
743 | |
744 statement.Execute(args); | |
745 } | |
746 #endif | |
747 | |
748 | |
749 void PostgreSQLIndex::SetResourcesContent(DatabaseManager& manager, | |
750 uint32_t countIdentifierTags, | |
751 const OrthancPluginResourcesContentTags* identifierTags, | |
752 uint32_t countMainDicomTags, | |
753 const OrthancPluginResourcesContentTags* mainDicomTags, | |
754 uint32_t countMetadata, | |
755 const OrthancPluginResourcesContentMetadata* metadata) | |
756 { | |
757 ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", "i", | |
758 countIdentifierTags, identifierTags); | |
759 | |
760 ExecuteSetResourcesContentTags(manager, "MainDicomTags", "t", | |
761 countMainDicomTags, mainDicomTags); | |
762 | |
763 ExecuteSetResourcesContentMetadata(manager, HasRevisionsSupport(), countMetadata, metadata); | |
764 | |
765 } | |
766 | |
767 | |
599 uint64_t PostgreSQLIndex::GetResourcesCount(DatabaseManager& manager, | 768 uint64_t PostgreSQLIndex::GetResourcesCount(DatabaseManager& manager, |
600 OrthancPluginResourceType resourceType) | 769 OrthancPluginResourceType resourceType) |
601 { | 770 { |
602 // Optimized version thanks to the "FastCountResources.sql" extension | 771 // Optimized version thanks to the "FastCountResources.sql" extension |
603 | 772 |