Mercurial > hg > orthanc-postgresql
changeset 76:f6ca4c202c1a
Support version 6 of the database schema
| author | Sebastien Jodogne <s.jodogne@gmail.com> |
|---|---|
| date | Tue, 20 Oct 2015 16:40:33 +0200 |
| parents | d8b0679333d4 |
| children | 913478499a72 |
| files | CMakeLists.txt IndexPlugin/Plugin.cpp IndexPlugin/PostgreSQLPrepare.sql IndexPlugin/PostgreSQLVersion5.sql IndexPlugin/PostgreSQLVersion6.sql IndexPlugin/PostgreSQLWrapper.cpp IndexPlugin/PostgreSQLWrapper.h NEWS StoragePlugin/Plugin.cpp |
| diffstat | 9 files changed, 259 insertions(+), 74 deletions(-) [+] |
line wrap: on
line diff
--- a/CMakeLists.txt Sat Oct 17 12:05:41 2015 +0200 +++ b/CMakeLists.txt Tue Oct 20 16:40:33 2015 +0200 @@ -107,6 +107,8 @@ --system-exception --namespace=OrthancPlugins POSTGRESQL_PREPARE ${CMAKE_CURRENT_SOURCE_DIR}/IndexPlugin/PostgreSQLPrepare.sql + POSTGRESQL_PREPARE_V5 ${CMAKE_CURRENT_SOURCE_DIR}/IndexPlugin/PostgreSQLVersion5.sql + POSTGRESQL_PREPARE_V6 ${CMAKE_CURRENT_SOURCE_DIR}/IndexPlugin/PostgreSQLVersion6.sql ) set(CORE_SOURCES
--- a/IndexPlugin/Plugin.cpp Sat Oct 17 12:05:41 2015 +0200 +++ b/IndexPlugin/Plugin.cpp Tue Oct 20 16:40:33 2015 +0200 @@ -48,20 +48,8 @@ return -1; } - /* Check the expected version of the database */ - if (OrthancPluginGetExpectedDatabaseVersion(context_) != 5) - { - char info[1024]; - sprintf(info, "This database plugin is incompatible with your version of Orthanc " - "expecting the DB schema version %d, but this plugin is compatible with version 5", - OrthancPluginGetExpectedDatabaseVersion(context_)); - OrthancPluginLogError(context_, info); - return -1; - } - OrthancPluginSetDescription(context_, "Stores the Orthanc index into a PostgreSQL database."); - Json::Value configuration; if (!OrthancPlugins::ReadConfiguration(configuration, context)) {
--- a/IndexPlugin/PostgreSQLPrepare.sql Sat Oct 17 12:05:41 2015 +0200 +++ b/IndexPlugin/PostgreSQLPrepare.sql Tue Oct 20 16:40:33 2015 +0200 @@ -1,29 +1,6 @@ -- Table "GlobalProperties" is created by the -- "OrthancPlugins::GlobalProperties" class -CREATE TABLE Resources( - internalId BIGSERIAL NOT NULL PRIMARY KEY, - resourceType INTEGER NOT NULL, - publicId VARCHAR(64) NOT NULL, - parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE - ); - -CREATE TABLE MainDicomTags( - id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, - tagGroup INTEGER, - tagElement INTEGER, - value BYTEA, - PRIMARY KEY(id, tagGroup, tagElement) - ); - -CREATE TABLE DicomIdentifiers( - id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, - tagGroup INTEGER, - tagElement INTEGER, - value BYTEA, - PRIMARY KEY(id, tagGroup, tagElement) - ); - CREATE TABLE Metadata( id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, type INTEGER NOT NULL, @@ -164,9 +141,3 @@ AFTER INSERT ON Resources FOR EACH ROW EXECUTE PROCEDURE PatientAddedFunc(); - - - --- Set the version of the database schema --- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration -INSERT INTO GlobalProperties VALUES (1, '5');
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/IndexPlugin/PostgreSQLVersion5.sql Tue Oct 20 16:40:33 2015 +0200 @@ -0,0 +1,22 @@ +CREATE TABLE Resources( + internalId BIGSERIAL NOT NULL PRIMARY KEY, + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL, + parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE + ); + +CREATE TABLE MainDicomTags( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + tagGroup INTEGER, + tagElement INTEGER, + value BYTEA, + PRIMARY KEY(id, tagGroup, tagElement) + ); + +CREATE TABLE DicomIdentifiers( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + tagGroup INTEGER, + tagElement INTEGER, + value BYTEA, + PRIMARY KEY(id, tagGroup, tagElement) + );
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/IndexPlugin/PostgreSQLVersion6.sql Tue Oct 20 16:40:33 2015 +0200 @@ -0,0 +1,22 @@ +CREATE TABLE Resources( + internalId BIGSERIAL NOT NULL PRIMARY KEY, + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL, + parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE + ); + +CREATE TABLE MainDicomTags( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + tagGroup INTEGER, + tagElement INTEGER, + value TEXT, + PRIMARY KEY(id, tagGroup, tagElement) + ); + +CREATE TABLE DicomIdentifiers( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + tagGroup INTEGER, + tagElement INTEGER, + value TEXT, + PRIMARY KEY(id, tagGroup, tagElement) + );
--- a/IndexPlugin/PostgreSQLWrapper.cpp Sat Oct 17 12:05:41 2015 +0200 +++ b/IndexPlugin/PostgreSQLWrapper.cpp Tue Oct 20 16:40:33 2015 +0200 @@ -110,38 +110,56 @@ void PostgreSQLWrapper::Prepare() { + uint32_t expectedVersion = OrthancPluginGetExpectedDatabaseVersion(context_); + + /* Check the expected version of the database */ + if (expectedVersion != 5 && expectedVersion != 6) + { + char info[1024]; + sprintf(info, "This database plugin is incompatible with your version of Orthanc " + "expecting the DB schema version %d, but this plugin is compatible with versions 5 or 6", + expectedVersion); + OrthancPluginLogError(context_, info); + throw PostgreSQLException(info); + } + + PostgreSQLTransaction t(*connection_); if (!connection_->DoesTableExist("Resources")) { std::string query; - EmbeddedResources::GetFileResource(query, EmbeddedResources::POSTGRESQL_PREPARE); + + if (expectedVersion == 5) + { + EmbeddedResources::GetFileResource(query, EmbeddedResources::POSTGRESQL_PREPARE_V5); + } + else + { + EmbeddedResources::GetFileResource(query, EmbeddedResources::POSTGRESQL_PREPARE_V6); + } connection_->Execute(query); + + // Execute the common initialization between versions 5 and 6 + EmbeddedResources::GetFileResource(query, EmbeddedResources::POSTGRESQL_PREPARE); + connection_->Execute(query); + + // Set the version of the database schema + // The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration + connection_->Execute("INSERT INTO GlobalProperties VALUES (1, '" + + boost::lexical_cast<std::string>(expectedVersion) + "')"); + } - // Check the version of the database - std::string version = "unknown"; - if (!LookupGlobalProperty(version, GlobalProperty_DatabaseSchemaVersion)) - { - throw PostgreSQLException("The database is corrupted. Drop it manually for Orthanc to recreate it"); - } + version_ = GetDatabaseVersion(); - bool ok = false; - - try + if (version_ != 5 && + version_ != 6) { - unsigned int v = boost::lexical_cast<unsigned int>(version); - ok = (v == 5); - } - catch (boost::bad_lexical_cast&) - { - } - - if (!ok) - { - std::string message = "Incompatible version of the Orthanc PostgreSQL database: " + version; + std::string message = ("Incompatible version of the Orthanc PostgreSQL database: " + + boost::lexical_cast<std::string>(version_)); throw PostgreSQLException(message); } @@ -542,8 +560,6 @@ void PostgreSQLWrapper::GetMainDicomTags(int64_t id) { - // TODO DB V6 => Remove "DicomIdentifiers" - if (getMainDicomTags1_.get() == NULL || getMainDicomTags2_.get() == NULL) { @@ -571,6 +587,7 @@ } } + if (version_ == 5) { getMainDicomTags2_->BindInteger64(0, id); PostgreSQLResult result(*getMainDicomTags2_); @@ -874,6 +891,7 @@ } + // Used only if Orthanc <= 0.9.4 void PostgreSQLWrapper::LookupIdentifier(std::list<int64_t>& target, uint16_t group, uint16_t element, @@ -886,7 +904,15 @@ (*connection_, "SELECT id FROM DicomIdentifiers WHERE tagGroup=$1 AND tagElement=$2 and value=$3")); lookupIdentifier1_->DeclareInputInteger(0); lookupIdentifier1_->DeclareInputInteger(1); - lookupIdentifier1_->DeclareInputBinary(2); + + if (version_ == 5) + { + lookupIdentifier1_->DeclareInputBinary(2); + } + else + { + lookupIdentifier1_->DeclareInputString(2); + } } @@ -904,6 +930,8 @@ } } + + // Used only if Orthanc <= 0.9.4 void PostgreSQLWrapper::LookupIdentifier(std::list<int64_t>& target, const char* value) { @@ -912,7 +940,15 @@ lookupIdentifier2_.reset (new PostgreSQLStatement (*connection_, "SELECT id FROM DicomIdentifiers WHERE value=$1")); - lookupIdentifier2_->DeclareInputBinary(0); + + if (version_ == 5) + { + lookupIdentifier2_->DeclareInputBinary(0); + } + else + { + lookupIdentifier2_->DeclareInputString(0); + } } lookupIdentifier2_->BindString(0, value); @@ -928,6 +964,53 @@ } + // Used only if Orthanc >= 0.9.5 + void PostgreSQLWrapper::LookupIdentifierExact(std::list<int64_t>& target, + OrthancPluginResourceType level, + uint16_t group, + uint16_t element, + const char* value) + { + if (lookupIdentifierExact_.get() == NULL) + { + lookupIdentifierExact_.reset + (new PostgreSQLStatement + (*connection_, + "SELECT d.id FROM DicomIdentifiers AS d, Resources AS r WHERE " + "d.id = r.internalId AND r.resourceType=$1 AND d.tagGroup=$2 AND d.tagElement=$3 AND d.value=$4")); + lookupIdentifierExact_->DeclareInputInteger(0); + lookupIdentifierExact_->DeclareInputInteger(1); + lookupIdentifierExact_->DeclareInputInteger(2); + + if (version_ == 5) + { + lookupIdentifierExact_->DeclareInputBinary(3); + } + else + { + lookupIdentifierExact_->DeclareInputString(3); + } + } + + + lookupIdentifierExact_->BindInteger(0, level); + lookupIdentifierExact_->BindInteger(1, group); + lookupIdentifierExact_->BindInteger(2, element); + lookupIdentifierExact_->BindString(3, value); + + PostgreSQLResult result(*lookupIdentifierExact_); + target.clear(); + + while (!result.IsDone()) + { + target.push_back(result.GetInteger64(0)); + result.Step(); + } + } + + + + bool PostgreSQLWrapper::LookupMetadata(std::string& target, int64_t id, int32_t type) @@ -1094,7 +1177,15 @@ setMainDicomTags_->DeclareInputInteger64(0); setMainDicomTags_->DeclareInputInteger(1); setMainDicomTags_->DeclareInputInteger(2); - setMainDicomTags_->DeclareInputBinary(3); + + if (version_ == 5) + { + setMainDicomTags_->DeclareInputBinary(3); + } + else + { + setMainDicomTags_->DeclareInputString(3); + } } SetTagInternal(*setMainDicomTags_, id, group, element, value); @@ -1113,7 +1204,15 @@ setIdentifierTag_->DeclareInputInteger64(0); setIdentifierTag_->DeclareInputInteger(1); setIdentifierTag_->DeclareInputInteger(2); - setIdentifierTag_->DeclareInputBinary(3); + + if (version_ == 5) + { + setIdentifierTag_->DeclareInputBinary(3); + } + else + { + setIdentifierTag_->DeclareInputString(3); + } } SetTagInternal(*setIdentifierTag_, id, group, element, value); @@ -1261,13 +1360,73 @@ uint32_t PostgreSQLWrapper::GetDatabaseVersion() { - return 5; + // Check the version of the database + std::string version = "unknown"; + if (!LookupGlobalProperty(version, GlobalProperty_DatabaseSchemaVersion)) + { + throw PostgreSQLException("The database is corrupted. Drop it manually for Orthanc to recreate it"); + } + + try + { + return boost::lexical_cast<unsigned int>(version); + } + catch (boost::bad_lexical_cast&) + { + throw PostgreSQLException("The database is corrupted. Drop it manually for Orthanc to recreate it"); + } } void PostgreSQLWrapper::UpgradeDatabase(uint32_t targetVersion, OrthancPluginStorageArea* storageArea) { - throw PostgreSQLException("Unsupported call to upgrade"); + unsigned int version = GetDatabaseVersion(); + +#if !(ORTHANC_PLUGINS_MINIMAL_MAJOR_NUMBER <= 0 && ORTHANC_PLUGINS_MINIMAL_MINOR_NUMBER <= 9 && ORTHANC_PLUGINS_MINIMAL_REVISION_NUMBER <= 4) + + if (version == 5 && targetVersion == 6) + { + connection_->Execute("ALTER TABLE MainDicomTags ALTER COLUMN value TYPE TEXT"); + connection_->Execute("ALTER TABLE DicomIdentifiers ALTER COLUMN value TYPE TEXT"); + OrthancPluginReconstructMainDicomTags(context_, storageArea, OrthancPluginResourceType_Patient); + OrthancPluginReconstructMainDicomTags(context_, storageArea, OrthancPluginResourceType_Study); + OrthancPluginReconstructMainDicomTags(context_, storageArea, OrthancPluginResourceType_Series); + OrthancPluginReconstructMainDicomTags(context_, storageArea, OrthancPluginResourceType_Instance); + connection_->Execute("DELETE FROM GlobalProperties WHERE property=1"); + connection_->Execute("INSERT INTO GlobalProperties VALUES (1, '6');"); + version_ = 6; + return; // Success + } + +#endif + + std::string message = ("Unsupported call to upgrade from version " + boost::lexical_cast<std::string>(version) + + " to version " + boost::lexical_cast<std::string>(targetVersion) + " of the database schema"); + throw PostgreSQLException(message.c_str()); + } + + + void PostgreSQLWrapper::ClearMainDicomTags(int64_t id) + { + if (clearMainDicomTags_.get() == NULL || + clearDicomIdentifiers_.get() == NULL) + { + clearMainDicomTags_.reset + (new PostgreSQLStatement + (*connection_, "DELETE FROM MainDicomTags WHERE id=$1")); + clearMainDicomTags_->DeclareInputInteger64(0); + + clearDicomIdentifiers_.reset + (new PostgreSQLStatement + (*connection_, "DELETE FROM DicomIdentifiers WHERE id=$1")); + clearDicomIdentifiers_->DeclareInputInteger64(0); + } + + clearMainDicomTags_->BindInteger64(0, id); + clearMainDicomTags_->Run(); + + clearDicomIdentifiers_->BindInteger64(0, id); + clearDicomIdentifiers_->Run(); } }
--- a/IndexPlugin/PostgreSQLWrapper.h Sat Oct 17 12:05:41 2015 +0200 +++ b/IndexPlugin/PostgreSQLWrapper.h Tue Oct 20 16:40:33 2015 +0200 @@ -36,6 +36,7 @@ { private: OrthancPluginContext* context_; + uint32_t version_; std::auto_ptr<PostgreSQLConnection> connection_; std::auto_ptr<PostgreSQLTransaction> transaction_; @@ -71,6 +72,7 @@ std::auto_ptr<PostgreSQLStatement> lookupAttachment_; std::auto_ptr<PostgreSQLStatement> lookupIdentifier1_; std::auto_ptr<PostgreSQLStatement> lookupIdentifier2_; + std::auto_ptr<PostgreSQLStatement> lookupIdentifierExact_; // New in Orthanc 0.9.5 std::auto_ptr<PostgreSQLStatement> lookupMetadata_; std::auto_ptr<PostgreSQLStatement> lookupParent_; std::auto_ptr<PostgreSQLStatement> lookupResource_; @@ -86,9 +88,12 @@ std::auto_ptr<PostgreSQLStatement> clearDeletedFiles_; std::auto_ptr<PostgreSQLStatement> clearDeletedResources_; std::auto_ptr<PostgreSQLStatement> clearRemainingAncestor_; + std::auto_ptr<PostgreSQLStatement> clearMainDicomTags_; + std::auto_ptr<PostgreSQLStatement> clearDicomIdentifiers_; std::auto_ptr<PostgreSQLStatement> getDeletedFiles_; std::auto_ptr<PostgreSQLStatement> getDeletedResources_; std::auto_ptr<PostgreSQLStatement> getRemainingAncestor_; + void Prepare(); @@ -210,14 +215,23 @@ return globalProperties_.LookupGlobalProperty(target, property); } + // Used only if Orthanc <= 0.9.4 virtual void LookupIdentifier(std::list<int64_t>& result, uint16_t group, uint16_t element, const char* value); + // Used only if Orthanc <= 0.9.4 virtual void LookupIdentifier(std::list<int64_t>& result, const char* value); + // Used only if Orthanc >= 0.9.5 + virtual void LookupIdentifierExact(std::list<int64_t>& result, + OrthancPluginResourceType level, + uint16_t group, + uint16_t element, + const char* value); + virtual bool LookupMetadata(std::string& target, int64_t id, int32_t type); @@ -286,7 +300,11 @@ virtual uint32_t GetDatabaseVersion(); + // Used only if Orthanc >= 0.9.5 virtual void UpgradeDatabase(uint32_t targetVersion, OrthancPluginStorageArea* storageArea); + + // Used only if Orthanc >= 0.9.5 + virtual void ClearMainDicomTags(int64_t id); }; }
--- a/NEWS Sat Oct 17 12:05:41 2015 +0200 +++ b/NEWS Tue Oct 20 16:40:33 2015 +0200 @@ -1,6 +1,9 @@ Pending changes in the mainline =============================== +* Support version 6 of the database schema +* The "value" column of tables "MainDicomTags" and "DicomIdentifiers" are now TEXT instead of BYTEA + Release 1.3 (2015/10/07) ========================
--- a/StoragePlugin/Plugin.cpp Sat Oct 17 12:05:41 2015 +0200 +++ b/StoragePlugin/Plugin.cpp Tue Oct 20 16:40:33 2015 +0200 @@ -29,14 +29,14 @@ static OrthancPlugins::PostgreSQLStorageArea* storage_ = NULL; -#if (ORTHANC_PLUGINS_MINIMAL_MINOR_NUMBER >= 9 && ORTHANC_PLUGINS_MINIMAL_REVISION_NUMBER >= 5) +#if (ORTHANC_PLUGINS_MINIMAL_MAJOR_NUMBER <= 0 && ORTHANC_PLUGINS_MINIMAL_MINOR_NUMBER <= 9 && ORTHANC_PLUGINS_MINIMAL_REVISION_NUMBER <= 4) +# define RETURN_TYPE int32_t +# define RETURN_SUCCESS 0 +# define RETURN_FAILURE -1 +#else # define RETURN_TYPE OrthancPluginErrorCode # define RETURN_SUCCESS OrthancPluginErrorCode_Success # define RETURN_FAILURE OrthancPluginErrorCode_Plugin -#else -# define RETURN_TYPE int32_t -# define RETURN_SUCCESS 0 -# define RETURN_FAILURE -1 #endif