# HG changeset patch # User Alain Mazy # Date 1702921801 -3600 # Node ID d979f25e60cf02a7722f8d328440085c920156b0 # Parent f16faa1fdc46373ae7aca89aed98ece706edf2eb Re-organized DB creation/upgrade into standalone files diff -r f16faa1fdc46 -r d979f25e60cf NOTES --- a/NOTES Fri Dec 15 17:11:26 2023 +0100 +++ b/NOTES Mon Dec 18 18:50:01 2023 +0100 @@ -193,78 +193,16 @@ TODO: -- check RETURNS SET OF ! - CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$ - SELECT * FROM foo WHERE fooid = $1; - $$ LANGUAGE SQL; -- have a separate "thread" to increment/decrement statistics because everybody is fighting to modify the GlobalIntegers rows - ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", "i", -> make unique + handle on conflicts - countIdentifierTags, identifierTags); - - ExecuteSetResourcesContentTags(manager, "MainDicomTags", "t", -> make unique + handle on conflicts - countMainDicomTags, mainDicomTags); - - ExecuteSetResourcesContentMetadata(manager, HasRevisionsSupport(), countMetadata, metadata); handle on conflicts - -CREATE OR REPLACE FUNCTION InsertOrReplaceMetadata(VARIADIC params SETOF metadata) -RETURNS VOID AS $$ -BEGIN - - FOR i IN 1..array_length(my_params, 1) LOOP - my_sum := my_sum + my_params[i].my_column; - END LOOP; - RETURN my_sum; -END; -$$ LANGUAGE plpgsql; - -#include - -int main() { - PGconn *conn = PQconnectdb("dbname=mydb user=myuser password=mypassword"); - if (PQstatus(conn) != CONNECTION_OK) { - std::cerr << "Connection to database failed: " << PQerrorMessage(conn) << std::endl; - PQfinish(conn); - return 1; - } - - const char *function_name = "my_function"; - int num_params = 3; - int params[] = {1, 2, 3}; - - char *query = PQescapeLiteral(conn, function_name, strlen(function_name)); - for (int i = 0; i < num_params; i++) { - char *param = PQescapeLiteral(conn, (const char *)¶ms[i], sizeof(int)); - query = (char *)realloc(query, strlen(query) + strlen(param) + 1); - strcat(query, ","); - strcat(query, param); - PQfreemem(param); - } - strcat(query, ")"); - - PGresult *res = PQexec(conn, query); - if (PQresultStatus(res) != PGRES_TUPLES_OK) { - std::cerr << "Function call failed: " << PQerrorMessage(conn) << std::endl; - PQfreemem(query); - PQclear(res); - PQfinish(conn); - return 1; - } - - int result = atoi(PQgetvalue(res, 0, 0)); - std::cout << "Result: " << result << std::endl; - - PQfreemem(query); - PQclear(res); - PQfinish(conn); - - +- reenable PatientRecyclingOrder +- have a separate "thread" to UpdateStatistics ? - test events generation StableSeries .... -- run tests with docker localy + in CI - check https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor-when-using-mysql-mariadb/3820 -- PatientAddedFunc contains an IF - validate upgrade DB from previous Orthanc and from scratch +- check minimal version of PG (9.5 - 9.6 ? for create index if not exists) +- implement a downgrade script ? - test with older version of PG - In Docker images, re-enable MySQL & ODBC plugins + tests DONE: - force the create/update DB transaction to be serializable (not needed: this is handled by POSTGRESQL_LOCK_DATABASE_SETUP) +- PatientAddedFunc contains an IF (check if other IF/THEN/ELSE pattern remains) diff -r f16faa1fdc46 -r d979f25e60cf PostgreSQL/CMakeLists.txt --- a/PostgreSQL/CMakeLists.txt Fri Dec 15 17:11:26 2023 +0100 +++ b/PostgreSQL/CMakeLists.txt Mon Dec 18 18:50:01 2023 +0100 @@ -77,14 +77,9 @@ EmbedResources( - POSTGRESQL_PREPARE_INDEX ${CMAKE_SOURCE_DIR}/Plugins/PrepareIndex.sql - POSTGRESQL_CREATE_INSTANCE ${CMAKE_SOURCE_DIR}/Plugins/CreateInstance.sql - POSTGRESQL_FAST_TOTAL_SIZE ${CMAKE_SOURCE_DIR}/Plugins/FastTotalSize.sql - POSTGRESQL_FAST_COUNT_RESOURCES ${CMAKE_SOURCE_DIR}/Plugins/FastCountResources.sql - POSTGRESQL_GET_LAST_CHANGE_INDEX ${CMAKE_SOURCE_DIR}/Plugins/GetLastChangeIndex.sql - POSTGRESQL_RESOURCE_DELETED_FUNC ${CMAKE_SOURCE_DIR}/Plugins/ResourceDeletedFunc.sql - POSTGRESQL_INSERT_UPDATE_METADATA ${CMAKE_SOURCE_DIR}/Plugins/InsertOrUpdateMetadata.sql - POSTGRESQL_FAST_TOTAL_STATS_2 ${CMAKE_SOURCE_DIR}/Plugins/FastTotalStats2.sql + POSTGRESQL_PREPARE_INDEX_V2 ${CMAKE_SOURCE_DIR}/Plugins/SQL/PrepareIndexV2.sql + POSTGRESQL_UPGRADE_UNKNOWN_TO_V1 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/UnknownToV1.sql + POSTGRESQL_UPGRADE_V1_TO_V2 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/V1ToV2.sql ) diff -r f16faa1fdc46 -r d979f25e60cf PostgreSQL/Plugins/PostgreSQLIndex.cpp --- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp Fri Dec 15 17:11:26 2023 +0100 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp Mon Dec 18 18:50:01 2023 +0100 @@ -61,6 +61,22 @@ return PostgreSQLDatabase::CreateDatabaseFactory(parameters_); } + void PostgreSQLIndex::ApplyPrepareIndex(DatabaseManager::Transaction& t, DatabaseManager& manager) + { + std::string query; + + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX_V2); + t.GetDatabaseTransaction().ExecuteMultiLines(query); + + SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion, 6); + SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, 2); + SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasCreateInstance, 3); // this is the 3rd version ! + SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasTrigramIndex, 1); + SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetTotalSizeIsFast, 1); + SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasFastCountResources, 1); + SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetLastChangeIndex, 1); + } void PostgreSQLIndex::ConfigureDatabase(DatabaseManager& manager, bool hasIdentifierTags, @@ -77,7 +93,7 @@ if (expectedVersion != 6) { LOG(ERROR) << "This database plugin is incompatible with your version of Orthanc " - << "expecting the DB schema version " << expectedVersion + << "expecting the Orthanc DB schema version " << expectedVersion << ", but this plugin is only compatible with version 6"; throw Orthanc::OrthancException(Orthanc::ErrorCode_Plugin); } @@ -90,8 +106,8 @@ } { + // lock the full DB while checking if it needs to be create/ugraded PostgreSQLDatabase::TransientAdvisoryLock lock(db, POSTGRESQL_LOCK_DATABASE_SETUP); - bool shouldInstallFastTotalStats2 = false; if (clearAll_) { @@ -103,262 +119,98 @@ if (!t.GetDatabaseTransaction().DoesTableExist("Resources")) { - std::string query; - - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion, expectedVersion); - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, 1); - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasTrigramIndex, 0); - } - - if (!t.GetDatabaseTransaction().DoesTableExist("Resources")) - { - LOG(ERROR) << "Corrupted PostgreSQL database"; - throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); - } - - int version = 0; - if (!LookupGlobalIntegerProperty(version, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion) || - version != 6) - { - LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema version: " << version; - throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); - } + LOG(WARNING) << "PostgreSQL is creating the database schema"; - int revision; - if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel)) - { - revision = 1; - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); - } - - if (revision == 1) - { - { - LOG(WARNING) << "PostgreSQL plugin: adding UNIQUE(publicId) constraint to the 'Resources' table "; - t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE Resources ADD UNIQUE (publicId);"); - } - - { - LOG(WARNING) << "PostgreSQL plugin: adding or replacing ResourceDeletedFunc"; - - std::string query; - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_RESOURCE_DELETED_FUNC); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - } - - { - LOG(WARNING) << "PostgreSQL plugin: adding or replacing InsertOrUpdateMetadata"; + ApplyPrepareIndex(t, manager); - std::string query; - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_INSERT_UPDATE_METADATA); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - } - - shouldInstallFastTotalStats2 = true; - - revision = 2; - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); - } - - if (revision != 2) - { - LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema revision: " << revision; - throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); - } - - t.Commit(); - } - - { - DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); - - int hasTrigram = 0; - if (!LookupGlobalIntegerProperty(hasTrigram, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_HasTrigramIndex) || - hasTrigram != 1) - { - /** - * Apply fix for performance issue (speed up wildcard search - * by using GIN trigrams). This implements the patch suggested - * in issue #47, BUT we also keep the original - * "DicomIdentifiersIndexValues", as it leads to better - * performance for "strict" searches (i.e. searches involving - * no wildcard). - * https://www.postgresql.org/docs/current/static/pgtrgm.html - * https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=47 - **/ - try + if (!t.GetDatabaseTransaction().DoesTableExist("Resources")) { - // We've observed 9 minutes on DB with 100000 studies - LOG(WARNING) << "Trying to enable trigram matching on the PostgreSQL database " - << "to speed up wildcard searches. This may take several minutes"; - - t.GetDatabaseTransaction().ExecuteMultiLines( - "CREATE EXTENSION IF NOT EXISTS pg_trgm; " - "CREATE INDEX DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);"); - - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasTrigramIndex, 1); - LOG(WARNING) << "Trigram index has been created"; - - t.Commit(); - } - catch (Orthanc::OrthancException&) - { - LOG(WARNING) << "Performance warning: Your PostgreSQL server does " - << "not support trigram matching"; - LOG(WARNING) << "-> Consider installing the \"pg_trgm\" extension on the " - << "PostgreSQL server, e.g. on Debian: sudo apt install postgresql-contrib"; + LOG(ERROR) << "Corrupted PostgreSQL database or failed to create the database schema"; + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); } } else { - t.Commit(); - } - } - - { - DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); + LOG(WARNING) << "The database schema already exists, checking if it needs to be updated"; - int property = 0; - if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_HasCreateInstance) || - property != 3) - { - LOG(INFO) << "Installing the CreateInstance extension"; - - if (property == 1) + int version = 0; + if (!LookupGlobalIntegerProperty(version, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion) || + version != 6) { - // Drop older, experimental versions of this extension - t.GetDatabaseTransaction().ExecuteMultiLines("DROP FUNCTION CreateInstance(" - "IN patient TEXT, IN study TEXT, IN series TEXT, in instance TEXT)"); + LOG(ERROR) << "PostgreSQL plugin is incompatible with Orthanc database schema version: " << version; + throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); } - - // property == 2 was a first version of the CreateInstance -> we need to replace it by the new one - // property == 3 is a new version (in v5.2) with same signature and CREATE OR UPDATE - // -> we can replace the previous one without deleting it - // and we can create it if it has never been created. - std::string query; - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_CREATE_INSTANCE); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasCreateInstance, 3); - } + bool needToRunUpgradeFromUnknownToV1 = false; + bool needToRunUpgradeV1toV2 = false; - if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_GetTotalSizeIsFast) || - property != 1) - { - LOG(INFO) << "Installing the FastTotalSize extension"; - - std::string query; - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_TOTAL_SIZE); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetTotalSizeIsFast, 1); - } - + int revision; + if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel)) + { + LOG(WARNING) << "No DatabasePatchLevel found, assuming it's 1"; + revision = 1; + needToRunUpgradeFromUnknownToV1 = true; + needToRunUpgradeV1toV2 = true; + } + else if (revision == 1) + { + needToRunUpgradeV1toV2 = true; + } - // Installing this extension requires the "GlobalIntegers" table - // created by the "FastTotalSize" extension - property = 0; - if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_HasFastCountResources) || - property != 1) - { - LOG(INFO) << "Installing the FastCountResources extension"; - - std::string query; - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_COUNT_RESOURCES); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasFastCountResources, 1); - } - + int hasTrigram = 0; + if (!LookupGlobalIntegerProperty(hasTrigram, manager, MISSING_SERVER_IDENTIFIER, + Orthanc::GlobalProperty_HasTrigramIndex) || + hasTrigram != 1) + { + // We've observed 9 minutes on DB with 100000 studies + LOG(WARNING) << "The DB schema update will try to enable trigram matching on the PostgreSQL database " + << "to speed up wildcard searches. This may take several minutes"; + needToRunUpgradeV1toV2 = true; + } - // Installing this extension requires the "GlobalIntegers" table - // created by the "GetLastChangeIndex" extension - property = 0; - if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_GetLastChangeIndex) || - property != 1) - { - LOG(INFO) << "Installing the GetLastChangeIndex extension"; - - std::string query; - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_GET_LAST_CHANGE_INDEX); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetLastChangeIndex, 1); - } - - if (shouldInstallFastTotalStats2) - { - LOG(WARNING) << "PostgreSQL plugin: installing FastTotalStats2 to replace FastTotalSize and FastCountResources"; - - std::string query; - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_TOTAL_STATS_2); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - } - - t.Commit(); - } - - - { - // New in release 4.0 to deal with multiple writers - DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); + int property = 0; + if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, + Orthanc::GlobalProperty_HasFastCountResources) || + property != 1) + { + needToRunUpgradeV1toV2 = true; + } + if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, + Orthanc::GlobalProperty_GetTotalSizeIsFast) || + property != 1) + { + needToRunUpgradeV1toV2 = true; + } + if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, + Orthanc::GlobalProperty_GetLastChangeIndex) || + property != 1) + { + needToRunUpgradeV1toV2 = true; + } - if (!t.GetDatabaseTransaction().DoesTableExist("ServerProperties")) - { - t.GetDatabaseTransaction().ExecuteMultiLines("CREATE TABLE ServerProperties(server VARCHAR(64) NOT NULL, " - "property INTEGER, value TEXT, PRIMARY KEY(server, property))"); - } - - /** - * PostgreSQL 9.5: "Adding a column with a default requires - * updating each row of the table (to store the new column - * value). However, if no default is specified, PostgreSQL is - * able to avoid the physical update." => We set no default - * for performance (older entries will be NULL) - * https://www.postgresql.org/docs/9.5/ddl-alter.html - **/ - if (!db.DoesColumnExist("Metadata", "revision")) - { - t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE Metadata ADD COLUMN revision INTEGER"); - } + if (needToRunUpgradeFromUnknownToV1) + { + LOG(WARNING) << "Upgrading DB schema from unknown to revision 1"; + std::string query; - if (!db.DoesColumnExist("AttachedFiles", "revision")) - { - t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE AttachedFiles ADD COLUMN revision INTEGER"); - } - - t.Commit(); - } - + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_UNKNOWN_TO_V1); + t.GetDatabaseTransaction().ExecuteMultiLines(query); + } + + if (needToRunUpgradeV1toV2) + { + LOG(WARNING) << "Upgrading DB schema from revision 1 to revision 2"; - { - // New in release 5.0 to deal with labels - DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); + std::string query; - if (!t.GetDatabaseTransaction().DoesTableExist("Labels")) - { - t.GetDatabaseTransaction().ExecuteMultiLines( - "CREATE TABLE Labels(" - "id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE," - "label TEXT, PRIMARY KEY(id, label));" - "CREATE INDEX LabelsIndex1 ON LABELS(id);" - "CREATE INDEX LabelsIndex2 ON LABELS(label);"); + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_V1_TO_V2); + t.GetDatabaseTransaction().ExecuteMultiLines(query); + + // apply all idempotent changes that are in the PrepareIndexV2 + ApplyPrepareIndex(t, manager); + } } t.Commit(); diff -r f16faa1fdc46 -r d979f25e60cf PostgreSQL/Plugins/PostgreSQLIndex.h --- a/PostgreSQL/Plugins/PostgreSQLIndex.h Fri Dec 15 17:11:26 2023 +0100 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.h Mon Dec 18 18:50:01 2023 +0100 @@ -40,6 +40,7 @@ virtual void ClearRemainingAncestor(DatabaseManager& manager); + void ApplyPrepareIndex(DatabaseManager::Transaction& t, DatabaseManager& manager); public: PostgreSQLIndex(OrthancPluginContext* context, diff -r f16faa1fdc46 -r d979f25e60cf PostgreSQL/Plugins/SQL/PrepareIndexV2.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/PrepareIndexV2.sql Mon Dec 18 18:50:01 2023 +0100 @@ -0,0 +1,548 @@ +-- This SQL file creates a DB in revision 2 directly (version is being used from v 6.0 of the PostgreSQL plugin) +-- It is also run after upgrade scripts to create new tables and or create/replace triggers and functions. +-- This script is self contained, it contains everything that needs to be run to create an Orthanc DB. +-- Note: it is and must be idempotent. + +CREATE TABLE IF NOT EXISTS GlobalProperties( + property INTEGER PRIMARY KEY, + value TEXT + ); + +CREATE TABLE IF NOT EXISTS Resources( + internalId BIGSERIAL NOT NULL PRIMARY KEY, + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL, + parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + CONSTRAINT UniquePublicId UNIQUE (publicId) + ); + +CREATE TABLE IF NOT EXISTS MainDicomTags( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + tagGroup INTEGER, + tagElement INTEGER, + value TEXT, + PRIMARY KEY(id, tagGroup, tagElement) + ); + +CREATE TABLE IF NOT EXISTS DicomIdentifiers( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + tagGroup INTEGER, + tagElement INTEGER, + value TEXT, + PRIMARY KEY(id, tagGroup, tagElement) + ); + +CREATE TABLE IF NOT EXISTS Metadata( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + type INTEGER NOT NULL, + value TEXT, + revision INTEGER, + PRIMARY KEY(id, type) + ); + +CREATE TABLE IF NOT EXISTS AttachedFiles( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + fileType INTEGER, + uuid VARCHAR(64) NOT NULL, + compressedSize BIGINT, + uncompressedSize BIGINT, + compressionType INTEGER, + uncompressedHash VARCHAR(40), + compressedHash VARCHAR(40), + revision INTEGER, + PRIMARY KEY(id, fileType) + ); + +CREATE TABLE IF NOT EXISTS Changes( + seq BIGSERIAL NOT NULL PRIMARY KEY, + changeType INTEGER, + internalId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + resourceType INTEGER, + date VARCHAR(64) + ); + +CREATE TABLE IF NOT EXISTS ExportedResources( + seq BIGSERIAL NOT NULL PRIMARY KEY, + resourceType INTEGER, + publicId VARCHAR(64), + remoteModality TEXT, + patientId VARCHAR(64), + studyInstanceUid TEXT, + seriesInstanceUid TEXT, + sopInstanceUid TEXT, + date VARCHAR(64) + ); + +CREATE TABLE IF NOT EXISTS PatientRecyclingOrder( + seq BIGSERIAL NOT NULL PRIMARY KEY, + patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + CONSTRAINT UniquePatientId UNIQUE (patientId) + ); + +CREATE TABLE IF NOT EXISTS Labels( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + label TEXT, + PRIMARY KEY(id, label) + ); + +CREATE TABLE IF NOT EXISTS GlobalIntegers( + key INTEGER PRIMARY KEY, + value BIGINT); +-- GlobalIntegers keys: +-- 0: CompressedSize +-- 1: UncompressedSize +-- 2: PatientsCount +-- 3: StudiesCount +-- 4: SeriesCount +-- 5: InstancesCount +-- 6: ChangeSeq +-- 7: PatientRecyclingOrderSeq + +CREATE TABLE IF NOT EXISTS ServerProperties( + server VARCHAR(64) NOT NULL, + property INTEGER, value TEXT, + PRIMARY KEY(server, property) + ); + +CREATE INDEX IF NOT EXISTS ChildrenIndex ON Resources(parentId); +CREATE INDEX IF NOT EXISTS PublicIndex ON Resources(publicId); +CREATE INDEX IF NOT EXISTS ResourceTypeIndex ON Resources(resourceType); +CREATE INDEX IF NOT EXISTS PatientRecyclingIndex ON PatientRecyclingOrder(patientId); + +CREATE INDEX IF NOT EXISTS MainDicomTagsIndex ON MainDicomTags(id); +CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex1 ON DicomIdentifiers(id); +CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); +CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues ON DicomIdentifiers(value); + +CREATE INDEX IF NOT EXISTS ChangesIndex ON Changes(internalId); +CREATE INDEX IF NOT EXISTS LabelsIndex1 ON LABELS(id); +CREATE INDEX IF NOT EXISTS LabelsIndex2 ON LABELS(label); + +------------------- Trigram index creation ------------------- + + +-- Apply fix for performance issue (speed up wildcard search by using GIN trigrams). This implements the patch suggested +-- in issue #47, BUT we also keep the original "DicomIdentifiersIndexValues", as it leads to better +-- performance for "strict" searches (i.e. searches involving no wildcard). +-- https://www.postgresql.org/docs/current/static/pgtrgm.html +-- https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=47 + +DO $body$ +begin + IF EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_trgm') THEN + CREATE EXTENSION IF NOT EXISTS pg_trgm; + CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops); + ELSE + RAISE NOTICE 'pg_trgm extension is not available on you system'; + END IF; +END $body$; + + +------------------- PatientAdded trigger & PatientRecyclingOrder ------------------- +DROP TRIGGER IF EXISTS PatientAdded ON Resources; + +CREATE OR REPLACE FUNCTION PatientAddedOrUpdated( + IN patient_id BIGINT, + IN is_update BIGINT + ) +RETURNS VOID AS $body$ +BEGIN + DECLARE + newSeq BIGINT; + BEGIN + UPDATE GlobalIntegers SET value = value + 1 WHERE key = 7 RETURNING value INTO newSeq; + IF is_update > 0 THEN + -- Note: Protected patient are not listed in this table ! So, they won't be updated + UPDATE PatientRecyclingOrder SET seq = newSeq WHERE PatientRecyclingOrder.patientId = patient_id; + ELSE + INSERT INTO PatientRecyclingOrder VALUES (newSeq, patient_id); + -- ON CONFLICT (patientId) DO UPDATE SET seq = newSeq; + END IF; + END; +END; +$body$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION PatientAddedFunc() +RETURNS TRIGGER AS $body$ +BEGIN + -- The "0" corresponds to "OrthancPluginResourceType_Patient" + IF new.resourceType = 0 THEN + PERFORM PatientAddedOrUpdated(new.internalId, 0); + -- UPDATE GlobalIntegers WHERE key = 7 SET value = value + 1 RETURNING value + -- INSERT INTO PatientRecyclingOrder VALUES ((SELECT value FROM GlobalIntegers WHERE key = 7), new.internalId) + -- ON CONFLICT ; + END IF; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE TRIGGER PatientAdded +AFTER INSERT ON Resources +FOR EACH ROW +EXECUTE PROCEDURE PatientAddedFunc(); + +-- initial value for PatientRecyclingOrderSeq +INSERT INTO GlobalIntegers + SELECT 7, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM PatientRecyclingOrder + ON CONFLICT DO NOTHING; + + +------------------- ResourceDeleted trigger ------------------- +DROP TRIGGER IF EXISTS ResourceDeleted ON Resources; + +-- The following trigger combines 2 triggers from SQLite: +-- ResourceDeleted + ResourceDeletedParentCleaning +CREATE OR REPLACE FUNCTION ResourceDeletedFunc() +RETURNS TRIGGER AS $body$ +BEGIN + -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId; + INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); + + -- If this resource is the latest child, delete the parent + DELETE FROM Resources WHERE internalId = old.parentId + AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE TRIGGER ResourceDeleted +AFTER DELETE ON Resources +FOR EACH ROW +EXECUTE PROCEDURE ResourceDeletedFunc(); + + +------------------- DeleteResource function ------------------- + +CREATE OR REPLACE FUNCTION DeleteResource( + IN id BIGINT, + OUT remaining_ancestor_resource_type INTEGER, + OUT remaining_anncestor_public_id TEXT) AS $body$ + +DECLARE + deleted_row RECORD; + +BEGIN + + SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping + + -- note: temporary tables are created at session (connection) level -> they are likely to exist + -- these tables are used by the triggers + CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources( + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL + ); + + RESET client_min_messages; + + -- clear the temporary table in case it has been created earlier in the session + DELETE FROM DeletedResources; + + -- create/clear the DeletedFiles temporary table + PERFORM CreateDeletedFilesTemporaryTable(); + + -- delete the resource itself + DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row; + -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children + + + -- If this resource still has siblings, keep track of the remaining parent + -- (a parent that must not be deleted but whose LastUpdate must be updated) + SELECT resourceType, publicId INTO remaining_ancestor_resource_type, remaining_anncestor_public_id + FROM Resources + WHERE internalId = deleted_row.parentId + AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId); + +END; + +$body$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION CreateDeletedFilesTemporaryTable( +) RETURNS VOID AS $body$ + +BEGIN + + SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping + + -- note: temporary tables are created at session (connection) level -> they are likely to exist + CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles( + uuid VARCHAR(64) NOT NULL, + fileType INTEGER, + compressedSize BIGINT, + uncompressedSize BIGINT, + compressionType INTEGER, + uncompressedHash VARCHAR(40), + compressedHash VARCHAR(40) + ); + + RESET client_min_messages; + + -- clear the temporary table in case it has been created earlier in the session + DELETE FROM DeletedFiles; +END; + +$body$ LANGUAGE plpgsql; + + +DROP TRIGGER IF EXISTS AttachedFileDeleted ON AttachedFiles; + +CREATE OR REPLACE FUNCTION AttachedFileDeletedFunc() +RETURNS TRIGGER AS $body$ +BEGIN + INSERT INTO DeletedFiles VALUES + (old.uuid, old.filetype, old.compressedSize, + old.uncompressedSize, old.compressionType, + old.uncompressedHash, old.compressedHash); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE TRIGGER AttachedFileDeleted +AFTER DELETE ON AttachedFiles +FOR EACH ROW +EXECUTE PROCEDURE AttachedFileDeletedFunc(); + + +------------------- Fast Statistics ------------------- + +-- initialize values if not already theere +INSERT INTO GlobalIntegers + SELECT 0, CAST(COALESCE(SUM(compressedSize), 0) AS BIGINT) FROM AttachedFiles + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 1, CAST(COALESCE(SUM(uncompressedSize), 0) AS BIGINT) FROM AttachedFiles + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 2, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 0 -- Count patients + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 3, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 1 -- Count studies + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 4, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 2 -- Count series + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 5, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 3 -- Count instances + ON CONFLICT DO NOTHING; + + +-- this table stores all changes that needs to be performed to the GlobalIntegers table +-- This way, each transaction can add row independently in this table without having to lock +-- any row (which was the case with previous FastTotalSize). +-- These changes will be applied at regular interval by an external thread or when someone +-- requests the statistics +CREATE TABLE IF NOT EXISTS GlobalIntegersChanges( + key INTEGER, + value BIGINT); + +CREATE OR REPLACE FUNCTION UpdateSingleStatistic( + IN statistics_key INTEGER, + OUT new_value BIGINT +) AS $body$ +BEGIN + + -- Delete the current changes, sum them and update the GlobalIntegers row. + -- New rows can be added in the meantime, they won't be deleted or summed. + WITH deleted_rows AS ( + DELETE FROM GlobalIntegersChanges + WHERE GlobalIntegersChanges.key = statistics_key + RETURNING value + ) + UPDATE GlobalIntegers + SET value = value + ( + SELECT COALESCE(SUM(value), 0) + FROM deleted_rows + ) + WHERE GlobalIntegers.key = statistics_key + RETURNING value INTO new_value; + +END; +$body$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION UpdateStatistics( + OUT patients_cunt BIGINT, + OUT studies_count BIGINT, + OUT series_count BIGINT, + OUT instances_count BIGINT, + OUT total_compressed_size BIGINT, + OUT total_uncompressed_size BIGINT +) AS $body$ +BEGIN + + SELECT UpdateSingleStatistic(0) INTO total_compressed_size; + SELECT UpdateSingleStatistic(1) INTO total_uncompressed_size; + SELECT UpdateSingleStatistic(2) INTO patients_cunt; + SELECT UpdateSingleStatistic(3) INTO studies_count; + SELECT UpdateSingleStatistic(4) INTO series_count; + SELECT UpdateSingleStatistic(5) INTO instances_count; + +END; +$body$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION IncrementResourcesTrackerFunc() +RETURNS TRIGGER AS $$ +BEGIN + INSERT INTO GlobalIntegersChanges VALUES(new.resourceType + 2, 1); + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION DecrementResourcesTrackerFunc() +RETURNS TRIGGER AS $$ +BEGIN + INSERT INTO GlobalIntegersChanges VALUES(old.resourceType + 2, -1); + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc() +RETURNS TRIGGER AS $body$ +BEGIN + INSERT INTO GlobalIntegersChanges VALUES(0, new.compressedSize); + INSERT INTO GlobalIntegersChanges VALUES(1, new.uncompressedSize); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc() +RETURNS TRIGGER AS $body$ +BEGIN + INSERT INTO GlobalIntegersChanges VALUES(0, -old.compressedSize); + INSERT INTO GlobalIntegersChanges VALUES(1, -old.uncompressedSize); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS AttachedFileIncrementSize on AttachedFiles; +CREATE TRIGGER AttachedFileIncrementSize +AFTER INSERT ON AttachedFiles +FOR EACH ROW +EXECUTE PROCEDURE AttachedFileIncrementSizeFunc(); + +DROP TRIGGER IF EXISTS AttachedFileDecrementSize on AttachedFiles; +CREATE TRIGGER AttachedFileDecrementSize +AFTER DELETE ON AttachedFiles +FOR EACH ROW +EXECUTE PROCEDURE AttachedFileDecrementSizeFunc(); + +DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources; +CREATE TRIGGER IncrementResourcesTracker +AFTER INSERT ON Resources +FOR EACH ROW +EXECUTE PROCEDURE IncrementResourcesTrackerFunc(); + +DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources; +CREATE TRIGGER DecrementResourcesTracker +AFTER DELETE ON Resources +FOR EACH ROW +EXECUTE PROCEDURE DecrementResourcesTrackerFunc(); + + +------------------- InsertOrUpdateMetadata function ------------------- +CREATE OR REPLACE FUNCTION InsertOrUpdateMetadata(resource_ids BIGINT[], + metadata_types INTEGER[], + metadata_values TEXT[], + revisions INTEGER[]) +RETURNS VOID AS $body$ +BEGIN + FOR i IN 1 .. ARRAY_LENGTH(resource_ids, 1) LOOP + -- RAISE NOTICE 'Parameter %: % % %', i, resource_ids[i], metadata_types[i], metadata_values[i]; + INSERT INTO Metadata VALUES(resource_ids[i], metadata_types[i], metadata_values[i], revisions[i]) ON CONFLICT DO NOTHING; + END LOOP; + +END; +$body$ LANGUAGE plpgsql; + + +------------------- GetLastChange function ------------------- +DROP TRIGGER IF EXISTS InsertedChange ON Changes; + +-- insert the value if not already there +INSERT INTO GlobalIntegers + SELECT 6, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM Changes + ON CONFLICT DO NOTHING; + +CREATE OR REPLACE FUNCTION InsertedChangeFunc() +RETURNS TRIGGER AS $body$ +BEGIN + UPDATE GlobalIntegers SET value = new.seq WHERE key = 6; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE TRIGGER InsertedChange +AFTER INSERT ON Changes +FOR EACH ROW +EXECUTE PROCEDURE InsertedChangeFunc(); + + +------------------- CreateInstance function ------------------- +CREATE OR REPLACE FUNCTION CreateInstance( + IN patient_public_id TEXT, + IN study_public_id TEXT, + IN series_public_id TEXT, + IN instance_public_id TEXT, + OUT is_new_patient BIGINT, + OUT is_new_study BIGINT, + OUT is_new_series BIGINT, + OUT is_new_instance BIGINT, + OUT patient_internal_id BIGINT, + OUT study_internal_id BIGINT, + OUT series_internal_id BIGINT, + OUT instance_internal_id BIGINT) AS $body$ + +DECLARE + patientSeq BIGINT; + countRecycling BIGINT; + +BEGIN + is_new_patient := 1; + is_new_study := 1; + is_new_series := 1; + is_new_instance := 1; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL); + EXCEPTION + WHEN unique_violation THEN + is_new_patient := 0; + END; + SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id AND resourcetype = 0; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id); + EXCEPTION + WHEN unique_violation THEN + is_new_study := 0; + END; + SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id AND resourcetype = 1; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id); + EXCEPTION + WHEN unique_violation THEN + is_new_series := 0; + END; + SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id AND resourcetype = 2; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id); + EXCEPTION + WHEN unique_violation THEN + is_new_instance := 0; + END; + SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id AND resourcetype = 3; + + IF is_new_instance > 0 THEN + -- Move the patient to the end of the recycling order. + PERFORM PatientAddedOrUpdated(patient_internal_id, 1); + END IF; +END; + +$body$ LANGUAGE plpgsql; diff -r f16faa1fdc46 -r d979f25e60cf PostgreSQL/Plugins/SQL/Upgrades/UnknownToV1.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Upgrades/UnknownToV1.sql Mon Dec 18 18:50:01 2023 +0100 @@ -0,0 +1,18 @@ +-- add the revision columns if not yet done + +DO $body$ +BEGIN + IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='metadata' AND column_name='revision') THEN + ALTER TABLE Metadata ADD COLUMN revision INTEGER; + ELSE + raise notice 'the metadata.revision column already exists'; + END IF; + + IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='attachedfiles' AND column_name='revision') THEN + ALTER TABLE AttachedFiles ADD COLUMN revision INTEGER; + ELSE + raise notice 'the attachedfiles.revision column already exists'; + END IF; + +END $body$; + diff -r f16faa1fdc46 -r d979f25e60cf PostgreSQL/Plugins/SQL/Upgrades/V1ToV2.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Upgrades/V1ToV2.sql Mon Dec 18 18:50:01 2023 +0100 @@ -0,0 +1,45 @@ +-- This file contains part of the changes required to upgrade from revision 1 to revision 2 (v 6.0) +-- It actually contains only the changes that: + -- can not be executed with an idempotent statement in SQL + -- or would polute the PrepareIndexV2.sql +-- This file is executed only if the current schema is in revision 1 and it is executed before PrepareIndexV2.sql +-- that is idempotent. + +-- add unique constraints if they donot exists +DO $body$ +BEGIN + + IF NOT EXISTS ( + SELECT 1 + FROM information_schema.table_constraints + WHERE table_schema = 'public' + AND table_name = 'resources' + AND constraint_name = 'uniquepublicid') + THEN + ALTER TABLE Resources ADD CONSTRAINT UniquePublicId UNIQUE (publicId); + RAISE NOTICE 'UniquePublicId constraint added to Resources.'; + END IF; + + IF NOT EXISTS ( + SELECT 1 + FROM information_schema.table_constraints + WHERE table_schema = 'public' + AND table_name = 'patientrecyclingorder' + AND constraint_name = 'uniquepatientid') + THEN + ALTER TABLE PatientRecyclingOrder ADD CONSTRAINT UniquePatientId UNIQUE (patientId); + RAISE NOTICE 'UniquePatientId constraint added to PatientRecyclingOrder.'; + END IF; + +END $body$ LANGUAGE plpgsql; + + +-- In V2, we'll now use temporary tables so we need to remove the old tables that might have been used in previous revisions ! +-- these statements, although idempotent, are not part of PrepareIndexV2.sql to keep it clean +DROP TABLE IF EXISTS DeletedFiles; +DROP TABLE IF EXISTS RemainingAncestor; +DROP TABLE IF EXISTS DeletedResources; + +-- These triggers disappears and are not replaced in V2 +DROP TRIGGER IF EXISTS CountResourcesTracker ON Resources; +