Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/PostgreSQLIndex.cpp @ 436:f16faa1fdc46 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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