Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/PostgreSQLIndex.cpp @ 569:f18e46d7dbf8 attach-custom-data

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged find-refactoring -> attach-custom-data
author Alain Mazy <am@orthanc.team>
date Tue, 24 Sep 2024 15:04:21 +0200
parents cd9521e04249 d8ee2f676a3c
children 7453fc5bef1a
comparison
equal deleted inserted replaced
368:82f73188b58d 569:f18e46d7dbf8
1 /** 1 /**
2 * Orthanc - A Lightweight, RESTful DICOM Store 2 * Orthanc - A Lightweight, RESTful DICOM Store
3 * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics 3 * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
4 * Department, University Hospital of Liege, Belgium 4 * Department, University Hospital of Liege, Belgium
5 * Copyright (C) 2017-2021 Osimis S.A., Belgium 5 * Copyright (C) 2017-2023 Osimis S.A., Belgium
6 * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium
7 * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
6 * 8 *
7 * This program is free software: you can redistribute it and/or 9 * This program is free software: you can redistribute it and/or
8 * modify it under the terms of the GNU Affero General Public License 10 * modify it under the terms of the GNU Affero General Public License
9 * as published by the Free Software Foundation, either version 3 of 11 * as published by the Free Software Foundation, either version 3 of
10 * the License, or (at your option) any later version. 12 * the License, or (at your option) any later version.
27 #include "PostgreSQLDefinitions.h" 29 #include "PostgreSQLDefinitions.h"
28 30
29 #include <EmbeddedResources.h> // Auto-generated file 31 #include <EmbeddedResources.h> // Auto-generated file
30 32
31 #include <Compatibility.h> // For std::unique_ptr<> 33 #include <Compatibility.h> // For std::unique_ptr<>
34 #include <Toolbox.h>
32 #include <Logging.h> 35 #include <Logging.h>
33 #include <OrthancException.h> 36 #include <OrthancException.h>
34 37
35 38
36 namespace Orthanc 39 namespace Orthanc
57 IDatabaseFactory* PostgreSQLIndex::CreateDatabaseFactory() 60 IDatabaseFactory* PostgreSQLIndex::CreateDatabaseFactory()
58 { 61 {
59 return PostgreSQLDatabase::CreateDatabaseFactory(parameters_); 62 return PostgreSQLDatabase::CreateDatabaseFactory(parameters_);
60 } 63 }
61 64
65 void PostgreSQLIndex::ApplyPrepareIndex(DatabaseManager::Transaction& t, DatabaseManager& manager)
66 {
67 std::string query;
68
69 Orthanc::EmbeddedResources::GetFileResource
70 (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX);
71 t.GetDatabaseTransaction().ExecuteMultiLines(query);
72 }
62 73
63 void PostgreSQLIndex::ConfigureDatabase(DatabaseManager& manager) 74 void PostgreSQLIndex::ConfigureDatabase(DatabaseManager& manager,
75 bool hasIdentifierTags,
76 const std::list<IdentifierTag>& identifierTags)
64 { 77 {
65 uint32_t expectedVersion = 6; 78 uint32_t expectedVersion = 6;
66 79
67 if (GetContext()) // "GetContext()" can possibly be NULL in the unit tests 80 if (GetContext()) // "GetContext()" can possibly be NULL in the unit tests
68 { 81 {
71 84
72 // Check the expected version of the database 85 // Check the expected version of the database
73 if (expectedVersion != 6) 86 if (expectedVersion != 6)
74 { 87 {
75 LOG(ERROR) << "This database plugin is incompatible with your version of Orthanc " 88 LOG(ERROR) << "This database plugin is incompatible with your version of Orthanc "
76 << "expecting the DB schema version " << expectedVersion 89 << "expecting the Orthanc DB schema version " << expectedVersion
77 << ", but this plugin is only compatible with version 6"; 90 << ", but this plugin is only compatible with version 6";
78 throw Orthanc::OrthancException(Orthanc::ErrorCode_Plugin); 91 throw Orthanc::OrthancException(Orthanc::ErrorCode_Plugin);
79 } 92 }
80 93
81 PostgreSQLDatabase& db = dynamic_cast<PostgreSQLDatabase&>(manager.GetDatabase()); 94 PostgreSQLDatabase& db = dynamic_cast<PostgreSQLDatabase&>(manager.GetDatabase());
84 { 97 {
85 db.AdvisoryLock(POSTGRESQL_LOCK_INDEX); 98 db.AdvisoryLock(POSTGRESQL_LOCK_INDEX);
86 } 99 }
87 100
88 { 101 {
102 // lock the full DB while checking if it needs to be create/ugraded
89 PostgreSQLDatabase::TransientAdvisoryLock lock(db, POSTGRESQL_LOCK_DATABASE_SETUP); 103 PostgreSQLDatabase::TransientAdvisoryLock lock(db, POSTGRESQL_LOCK_DATABASE_SETUP);
90 104
91 if (clearAll_) 105 if (clearAll_)
92 { 106 {
93 db.ClearAll(); 107 db.ClearAll();
96 { 110 {
97 DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); 111 DatabaseManager::Transaction t(manager, TransactionType_ReadWrite);
98 112
99 if (!t.GetDatabaseTransaction().DoesTableExist("Resources")) 113 if (!t.GetDatabaseTransaction().DoesTableExist("Resources"))
100 { 114 {
101 std::string query; 115 LOG(WARNING) << "PostgreSQL is creating the database schema";
102 116
103 Orthanc::EmbeddedResources::GetFileResource 117 ApplyPrepareIndex(t, manager);
104 (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX); 118
105 t.GetDatabaseTransaction().ExecuteMultiLines(query); 119 if (!t.GetDatabaseTransaction().DoesTableExist("Resources"))
106 120 {
107 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion, expectedVersion); 121 LOG(ERROR) << "Corrupted PostgreSQL database or failed to create the database schema";
108 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, 1); 122 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
109 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasTrigramIndex, 0);
110 }
111
112 if (!t.GetDatabaseTransaction().DoesTableExist("Resources"))
113 {
114 LOG(ERROR) << "Corrupted PostgreSQL database";
115 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
116 }
117
118 int version = 0;
119 if (!LookupGlobalIntegerProperty(version, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion) ||
120 version != 6)
121 {
122 LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema version: " << version;
123 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
124 }
125
126 int revision;
127 if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel))
128 {
129 revision = 1;
130 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision);
131 }
132
133 if (revision != 1)
134 {
135 LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema revision: " << revision;
136 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
137 }
138
139 t.Commit();
140 }
141
142 {
143 DatabaseManager::Transaction t(manager, TransactionType_ReadWrite);
144
145 int hasTrigram = 0;
146 if (!LookupGlobalIntegerProperty(hasTrigram, manager, MISSING_SERVER_IDENTIFIER,
147 Orthanc::GlobalProperty_HasTrigramIndex) ||
148 hasTrigram != 1)
149 {
150 /**
151 * Apply fix for performance issue (speed up wildcard search
152 * by using GIN trigrams). This implements the patch suggested
153 * in issue #47, BUT we also keep the original
154 * "DicomIdentifiersIndexValues", as it leads to better
155 * performance for "strict" searches (i.e. searches involving
156 * no wildcard).
157 * https://www.postgresql.org/docs/current/static/pgtrgm.html
158 * https://bugs.orthanc-server.com/show_bug.cgi?id=47
159 **/
160 try
161 {
162 // We've observed 9 minutes on DB with 100000 studies
163 LOG(WARNING) << "Trying to enable trigram matching on the PostgreSQL database "
164 << "to speed up wildcard searches. This may take several minutes";
165
166 t.GetDatabaseTransaction().ExecuteMultiLines(
167 "CREATE EXTENSION IF NOT EXISTS pg_trgm; "
168 "CREATE INDEX DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);");
169
170 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasTrigramIndex, 1);
171 LOG(WARNING) << "Trigram index has been created";
172
173 t.Commit();
174 }
175 catch (Orthanc::OrthancException&)
176 {
177 LOG(WARNING) << "Performance warning: Your PostgreSQL server does "
178 << "not support trigram matching";
179 LOG(WARNING) << "-> Consider installing the \"pg_trgm\" extension on the "
180 << "PostgreSQL server, e.g. on Debian: sudo apt install postgresql-contrib";
181 } 123 }
182 } 124 }
183 else 125 else
184 { 126 {
185 t.Commit(); 127 LOG(WARNING) << "The database schema already exists, checking if it needs to be updated";
186 } 128
187 } 129 int version = 0;
188 130 if (!LookupGlobalIntegerProperty(version, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion) ||
189 { 131 version != 6)
190 DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); 132 {
191 133 LOG(ERROR) << "PostgreSQL plugin is incompatible with Orthanc database schema version: " << version;
192 int property = 0; 134 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
193 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, 135 }
194 Orthanc::GlobalProperty_HasCreateInstance) || 136
195 property != 2) 137 bool needToRunUpgradeFromUnknownToV1 = false;
196 { 138 bool needToRunUpgradeV1toV2 = false;
197 LOG(INFO) << "Installing the CreateInstance extension"; 139 bool needToRunUpgradeV2toV3 = false;
198 140
199 if (property == 1) 141 int revision;
200 { 142 if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel))
201 // Drop older, experimental versions of this extension 143 {
202 t.GetDatabaseTransaction().ExecuteMultiLines("DROP FUNCTION CreateInstance(" 144 LOG(WARNING) << "No DatabasePatchLevel found, assuming it's 1";
203 "IN patient TEXT, IN study TEXT, IN series TEXT, in instance TEXT)"); 145 revision = 1;
204 } 146 needToRunUpgradeFromUnknownToV1 = true;
205 147 needToRunUpgradeV1toV2 = true;
206 std::string query; 148 }
207 Orthanc::EmbeddedResources::GetFileResource 149 else if (revision == 1)
208 (query, Orthanc::EmbeddedResources::POSTGRESQL_CREATE_INSTANCE); 150 {
209 t.GetDatabaseTransaction().ExecuteMultiLines(query); 151 LOG(WARNING) << "DatabasePatchLevel is 1";
210 152 needToRunUpgradeFromUnknownToV1 = true;
211 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasCreateInstance, 2); 153 needToRunUpgradeV1toV2 = true;
212 } 154 needToRunUpgradeV2toV3 = true;
213 155 }
214 156 else if (revision == 2)
215 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, 157 {
216 Orthanc::GlobalProperty_GetTotalSizeIsFast) || 158 LOG(WARNING) << "DatabasePatchLevel is 2";
217 property != 1) 159 needToRunUpgradeV2toV3 = true;
218 { 160 }
219 LOG(INFO) << "Installing the FastTotalSize extension"; 161
220 162 int hasTrigram = 0;
221 std::string query; 163 if (!LookupGlobalIntegerProperty(hasTrigram, manager, MISSING_SERVER_IDENTIFIER,
222 Orthanc::EmbeddedResources::GetFileResource 164 Orthanc::GlobalProperty_HasTrigramIndex) ||
223 (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_TOTAL_SIZE); 165 hasTrigram != 1)
224 t.GetDatabaseTransaction().ExecuteMultiLines(query); 166 {
225 167 // We've observed 9 minutes on DB with 100000 studies
226 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetTotalSizeIsFast, 1); 168 LOG(WARNING) << "The DB schema update will try to enable trigram matching on the PostgreSQL database "
227 } 169 << "to speed up wildcard searches. This may take several minutes";
228 170 needToRunUpgradeV1toV2 = true;
229 171 }
230 // Installing this extension requires the "GlobalIntegers" table 172
231 // created by the "FastTotalSize" extension 173 int property = 0;
232 property = 0; 174 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
233 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, 175 Orthanc::GlobalProperty_HasFastCountResources) ||
234 Orthanc::GlobalProperty_HasFastCountResources) || 176 property != 1)
235 property != 1) 177 {
236 { 178 needToRunUpgradeV1toV2 = true;
237 LOG(INFO) << "Installing the FastCountResources extension"; 179 }
238 180 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
239 std::string query; 181 Orthanc::GlobalProperty_GetTotalSizeIsFast) ||
240 Orthanc::EmbeddedResources::GetFileResource 182 property != 1)
241 (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_COUNT_RESOURCES); 183 {
242 t.GetDatabaseTransaction().ExecuteMultiLines(query); 184 needToRunUpgradeV1toV2 = true;
243 185 }
244 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasFastCountResources, 1); 186 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
245 } 187 Orthanc::GlobalProperty_GetLastChangeIndex) ||
246 188 property != 1)
247 189 {
248 // Installing this extension requires the "GlobalIntegers" table 190 needToRunUpgradeV1toV2 = true;
249 // created by the "GetLastChangeIndex" extension 191 }
250 property = 0; 192
251 if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, 193 if (needToRunUpgradeFromUnknownToV1)
252 Orthanc::GlobalProperty_GetLastChangeIndex) || 194 {
253 property != 1) 195 LOG(WARNING) << "Upgrading DB schema from unknown to revision 1";
254 { 196 std::string query;
255 LOG(INFO) << "Installing the GetLastChangeIndex extension"; 197
256 198 Orthanc::EmbeddedResources::GetFileResource
257 std::string query; 199 (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_UNKNOWN_TO_REV1);
258 Orthanc::EmbeddedResources::GetFileResource 200 t.GetDatabaseTransaction().ExecuteMultiLines(query);
259 (query, Orthanc::EmbeddedResources::POSTGRESQL_GET_LAST_CHANGE_INDEX); 201 }
260 t.GetDatabaseTransaction().ExecuteMultiLines(query); 202
261 203 if (needToRunUpgradeV1toV2)
262 SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetLastChangeIndex, 1); 204 {
263 } 205 LOG(WARNING) << "Upgrading DB schema from revision 1 to revision 2";
264 206
265 t.Commit(); 207 std::string query;
266 } 208
267 209 Orthanc::EmbeddedResources::GetFileResource
268 210 (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV1_TO_REV2);
269 { 211 t.GetDatabaseTransaction().ExecuteMultiLines(query);
270 // New in release 4.0 to deal with multiple writers 212
271 DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); 213 // apply all idempotent changes that are in the PrepareIndex
272 214 ApplyPrepareIndex(t, manager);
273 if (!t.GetDatabaseTransaction().DoesTableExist("ServerProperties")) 215 }
274 { 216
275 t.GetDatabaseTransaction().ExecuteMultiLines("CREATE TABLE ServerProperties(server VARCHAR(64) NOT NULL, " 217 if (needToRunUpgradeV2toV3)
276 "property INTEGER, value TEXT, PRIMARY KEY(server, property))"); 218 {
277 } 219 LOG(WARNING) << "Upgrading DB schema from revision 2 to revision 3";
278 220
279 /** 221 std::string query;
280 * PostgreSQL 9.5: "Adding a column with a default requires 222
281 * updating each row of the table (to store the new column 223 Orthanc::EmbeddedResources::GetFileResource
282 * value). However, if no default is specified, PostgreSQL is 224 (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV2_TO_REV3);
283 * able to avoid the physical update." => We set no default 225 t.GetDatabaseTransaction().ExecuteMultiLines(query);
284 * for performance (older entries will be NULL) 226
285 * https://www.postgresql.org/docs/9.5/ddl-alter.html 227 // apply all idempotent changes that are in the PrepareIndex (update triggers + set Patch level to 3)
286 **/ 228 ApplyPrepareIndex(t, manager);
287 if (!db.DoesColumnExist("Metadata", "revision")) 229 }
288 {
289 t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE Metadata ADD COLUMN revision INTEGER");
290 }
291
292 if (!db.DoesColumnExist("AttachedFiles", "revision"))
293 {
294 t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE AttachedFiles ADD COLUMN revision INTEGER");
295 }
296
297 // new in v 4.X
298 if (!db.DoesColumnExist("DeletedFiles", "revision"))
299 {
300 t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE DeletedFiles ADD COLUMN revision INTEGER");
301 }
302
303 if (!db.DoesColumnExist("AttachedFiles", "customData"))
304 {
305 t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE AttachedFiles ADD COLUMN customData TEXT");
306 }
307
308 if (!db.DoesColumnExist("DeletedFiles", "customData"))
309 {
310 // add the column and modify the trigger
311 t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE DeletedFiles ADD COLUMN customData TEXT");
312
313 t.GetDatabaseTransaction().ExecuteMultiLines(
314 "DROP TRIGGER AttachedFileDeleted ON AttachedFiles");
315
316 t.GetDatabaseTransaction().ExecuteMultiLines(
317 "DROP FUNCTION AttachedFileDeletedFunc");
318
319 t.GetDatabaseTransaction().ExecuteMultiLines(
320 "CREATE FUNCTION AttachedFileDeletedFunc() "
321 "RETURNS TRIGGER AS $body$"
322 "BEGIN"
323 " INSERT INTO DeletedFiles VALUES"
324 " (old.uuid, old.filetype, old.compressedSize,"
325 " old.uncompressedSize, old.compressionType,"
326 " old.uncompressedHash, old.compressedHash,"
327 " old.revision, old.customData);"
328 " RETURN NULL;"
329 "END;"
330 "$body$ LANGUAGE plpgsql;");
331
332 t.GetDatabaseTransaction().ExecuteMultiLines(
333 "CREATE TRIGGER AttachedFileDeleted "
334 "AFTER DELETE ON AttachedFiles "
335 "FOR EACH ROW "
336 "EXECUTE PROCEDURE AttachedFileDeletedFunc();"
337 );
338 } 230 }
339 231
340 t.Commit(); 232 t.Commit();
341 } 233 }
342 } 234 }
370 uint64_t result; 262 uint64_t result;
371 263
372 { 264 {
373 DatabaseManager::CachedStatement statement( 265 DatabaseManager::CachedStatement statement(
374 STATEMENT_FROM_HERE, manager, 266 STATEMENT_FROM_HERE, manager,
375 "SELECT value FROM GlobalIntegers WHERE key = 0"); 267 "SELECT * FROM UpdateSingleStatistic(0)");
376 268
377 statement.SetReadOnly(true);
378 statement.Execute(); 269 statement.Execute();
379 270
380 result = static_cast<uint64_t>(statement.ReadInteger64(0)); 271 result = static_cast<uint64_t>(statement.ReadInteger64(0));
381 } 272 }
382 273
383 assert(result == IndexBackend::GetTotalCompressedSize(manager)); 274 // disabled because this is not alway true while transactions are being executed in READ COMITTED TRANSACTION. This is however true when no files are being delete/added
275 //assert(result == IndexBackend::GetTotalCompressedSize(manager));
384 return result; 276 return result;
385 } 277 }
386 278
387 279
388 uint64_t PostgreSQLIndex::GetTotalUncompressedSize(DatabaseManager& manager) 280 uint64_t PostgreSQLIndex::GetTotalUncompressedSize(DatabaseManager& manager)
391 uint64_t result; 283 uint64_t result;
392 284
393 { 285 {
394 DatabaseManager::CachedStatement statement( 286 DatabaseManager::CachedStatement statement(
395 STATEMENT_FROM_HERE, manager, 287 STATEMENT_FROM_HERE, manager,
396 "SELECT value FROM GlobalIntegers WHERE key = 1"); 288 "SELECT * FROM UpdateSingleStatistic(1)");
397 289
398 statement.SetReadOnly(true);
399 statement.Execute(); 290 statement.Execute();
400 291
401 result = static_cast<uint64_t>(statement.ReadInteger64(0)); 292 result = static_cast<uint64_t>(statement.ReadInteger64(0));
402 } 293 }
403 294
404 assert(result == IndexBackend::GetTotalUncompressedSize(manager)); 295 // disabled because this is not alway true while transactions are being executed in READ COMITTED TRANSACTION. This is however true when no files are being delete/added
296 // assert(result == IndexBackend::GetTotalUncompressedSize(manager));
405 return result; 297 return result;
406 } 298 }
299
300 int64_t PostgreSQLIndex::IncrementGlobalProperty(DatabaseManager& manager,
301 const char* serverIdentifier,
302 int32_t property,
303 int64_t increment)
304 {
305 if (serverIdentifier == NULL)
306 {
307 throw Orthanc::OrthancException(Orthanc::ErrorCode_NullPointer);
308 }
309 else
310 {
311 if (strlen(serverIdentifier) == 0)
312 {
313 DatabaseManager::CachedStatement statement(
314 STATEMENT_FROM_HERE, manager,
315 "INSERT INTO GlobalProperties (property, value) VALUES(${property}, ${increment}) "
316 " ON CONFLICT (property) DO UPDATE SET value = CAST(GlobalProperties.value AS BIGINT) + ${increment}"
317 " RETURNING CAST(value AS BIGINT)");
318
319 statement.SetParameterType("property", ValueType_Integer64);
320 statement.SetParameterType("increment", ValueType_Integer64);
321
322 Dictionary args;
323 args.SetIntegerValue("property", property);
324 args.SetIntegerValue("increment", increment);
325
326 statement.Execute(args);
327
328 return statement.ReadInteger64(0);
329 }
330 else
331 {
332 DatabaseManager::CachedStatement statement(
333 STATEMENT_FROM_HERE, manager,
334 "INSERT INTO ServerProperties (server, property, value) VALUES(${server}, ${property}, ${increment}) "
335 " ON CONFLICT (server, property) DO UPDATE SET value = CAST(ServerProperties.value AS BIGINT) + ${increment}"
336 " RETURNING CAST(value AS BIGINT)");
337
338 statement.SetParameterType("server", ValueType_Utf8String);
339 statement.SetParameterType("property", ValueType_Integer64);
340 statement.SetParameterType("increment", ValueType_Integer64);
341
342 Dictionary args;
343 args.SetUtf8Value("server", serverIdentifier);
344 args.SetIntegerValue("property", property);
345 args.SetIntegerValue("increment", increment);
346
347 statement.Execute(args);
348
349 return statement.ReadInteger64(0);
350 }
351 }
352 }
353
354 void PostgreSQLIndex::UpdateAndGetStatistics(DatabaseManager& manager,
355 int64_t& patientsCount,
356 int64_t& studiesCount,
357 int64_t& seriesCount,
358 int64_t& instancesCount,
359 int64_t& compressedSize,
360 int64_t& uncompressedSize)
361 {
362 DatabaseManager::CachedStatement statement(
363 STATEMENT_FROM_HERE, manager,
364 "SELECT * FROM UpdateStatistics()");
365
366 statement.Execute();
367
368 patientsCount = statement.ReadInteger64(0);
369 studiesCount = statement.ReadInteger64(1);
370 seriesCount = statement.ReadInteger64(2);
371 instancesCount = statement.ReadInteger64(3);
372 compressedSize = statement.ReadInteger64(4);
373 uncompressedSize = statement.ReadInteger64(5);
374 }
375
376 void PostgreSQLIndex::ClearDeletedFiles(DatabaseManager& manager)
377 {
378 { // note: the temporary table lifespan is the session, not the transaction -> that's why we need the IF NOT EXISTS
379 DatabaseManager::CachedStatement statement(
380 STATEMENT_FROM_HERE, manager,
381 "SELECT CreateDeletedFilesTemporaryTable()"
382 );
383 statement.ExecuteWithoutResult();
384 }
385
386 }
387
388 void PostgreSQLIndex::ClearDeletedResources(DatabaseManager& manager)
389 {
390 { // note: the temporary table lifespan is the session, not the transaction -> that's why we need the IF NOT EXISTS
391 DatabaseManager::CachedStatement statement(
392 STATEMENT_FROM_HERE, manager,
393 "CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources("
394 "resourceType INTEGER NOT NULL,"
395 "publicId VARCHAR(64) NOT NULL"
396 ");"
397 );
398 statement.Execute();
399 }
400 {
401 DatabaseManager::CachedStatement statement(
402 STATEMENT_FROM_HERE, manager,
403 "DELETE FROM DeletedResources;"
404 );
405
406 statement.Execute();
407 }
408
409 }
410
411 void PostgreSQLIndex::ClearRemainingAncestor(DatabaseManager& manager)
412 {
413 }
414
415 void PostgreSQLIndex::DeleteResource(IDatabaseBackendOutput& output,
416 DatabaseManager& manager,
417 int64_t id)
418 {
419 // clearing of temporary table is now implemented in the funcion DeleteResource
420 DatabaseManager::CachedStatement statement(
421 STATEMENT_FROM_HERE, manager,
422 "SELECT * FROM DeleteResource(${id})");
423
424 statement.SetParameterType("id", ValueType_Integer64);
425
426 Dictionary args;
427 args.SetIntegerValue("id", id);
428
429 statement.Execute(args);
430
431 if (statement.IsDone() ||
432 statement.GetResultFieldsCount() != 2)
433 {
434 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
435 }
436
437 statement.SetResultFieldType(0, ValueType_Integer64);
438 statement.SetResultFieldType(1, ValueType_Utf8String);
439
440 if (!statement.IsNull(0))
441 {
442 output.SignalRemainingAncestor(
443 statement.ReadString(1),
444 static_cast<OrthancPluginResourceType>(statement.ReadInteger32(0)));
445 }
446
447 SignalDeletedFiles(output, manager);
448 SignalDeletedResources(output, manager);
449 }
450
407 451
408 452
409 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 453 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
410 void PostgreSQLIndex::CreateInstance(OrthancPluginCreateInstanceResult& result, 454 void PostgreSQLIndex::CreateInstance(OrthancPluginCreateInstanceResult& result,
411 DatabaseManager& manager, 455 DatabaseManager& manager,
426 Dictionary args; 470 Dictionary args;
427 args.SetUtf8Value("patient", hashPatient); 471 args.SetUtf8Value("patient", hashPatient);
428 args.SetUtf8Value("study", hashStudy); 472 args.SetUtf8Value("study", hashStudy);
429 args.SetUtf8Value("series", hashSeries); 473 args.SetUtf8Value("series", hashSeries);
430 args.SetUtf8Value("instance", hashInstance); 474 args.SetUtf8Value("instance", hashInstance);
431 475
432 statement.Execute(args); 476 statement.Execute(args);
433 477
434 if (statement.IsDone() || 478 if (statement.IsDone() ||
435 statement.GetResultFieldsCount() != 8) 479 statement.GetResultFieldsCount() != 8)
436 { 480 {
439 483
440 for (size_t i = 0; i < 8; i++) 484 for (size_t i = 0; i < 8; i++)
441 { 485 {
442 statement.SetResultFieldType(i, ValueType_Integer64); 486 statement.SetResultFieldType(i, ValueType_Integer64);
443 } 487 }
488
489 // LOG(INFO) << statement.ReadInteger64(0) << statement.ReadInteger64(1) << statement.ReadInteger64(2) << statement.ReadInteger64(3);
444 490
445 result.isNewInstance = (statement.ReadInteger64(3) == 1); 491 result.isNewInstance = (statement.ReadInteger64(3) == 1);
446 result.instanceId = statement.ReadInteger64(7); 492 result.instanceId = statement.ReadInteger64(7);
447 493
448 if (result.isNewInstance) 494 if (result.isNewInstance)
456 } 502 }
457 } 503 }
458 #endif 504 #endif
459 505
460 506
507 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
508 static void ExecuteSetResourcesContentTags(
509 DatabaseManager& manager,
510 const std::string& table,
511 uint32_t count,
512 const OrthancPluginResourcesContentTags* tags)
513 {
514 std::string sql;
515
516 std::vector<std::string> resourceIds;
517 std::vector<std::string> groups;
518 std::vector<std::string> elements;
519 std::vector<std::string> values;
520
521 Dictionary args;
522
523 for (uint32_t i = 0; i < count; i++)
524 {
525 std::string resourceArgName = "r" + boost::lexical_cast<std::string>(i);
526 std::string groupArgName = "g" + boost::lexical_cast<std::string>(i);
527 std::string elementArgName = "e" + boost::lexical_cast<std::string>(i);
528 std::string valueArgName = "v" + boost::lexical_cast<std::string>(i);
529
530 args.SetIntegerValue(resourceArgName, tags[i].resource);
531 args.SetInteger32Value(elementArgName, tags[i].element);
532 args.SetInteger32Value(groupArgName, tags[i].group);
533 args.SetUtf8Value(valueArgName, tags[i].value);
534
535 std::string insert = ("(${" + resourceArgName + "}, ${" +
536 groupArgName + "}, ${" +
537 elementArgName + "}, " +
538 "${" + valueArgName + "})");
539
540 if (sql.empty())
541 {
542 sql = "INSERT INTO " + table + " VALUES " + insert;
543 }
544 else
545 {
546 sql += ", " + insert;
547 }
548 }
549
550 if (!sql.empty())
551 {
552 DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql);
553
554 for (uint32_t i = 0; i < count; i++)
555 {
556 statement.SetParameterType("r" + boost::lexical_cast<std::string>(i),
557 ValueType_Integer64);
558 statement.SetParameterType("g" + boost::lexical_cast<std::string>(i),
559 ValueType_Integer32);
560 statement.SetParameterType("e" + boost::lexical_cast<std::string>(i),
561 ValueType_Integer32);
562 statement.SetParameterType("v" + boost::lexical_cast<std::string>(i),
563 ValueType_Utf8String);
564 }
565
566 statement.Execute(args);
567 }
568 }
569 #endif
570
571
572 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
573 static void ExecuteSetResourcesContentMetadata(
574 DatabaseManager& manager,
575 bool hasRevisionsSupport,
576 uint32_t count,
577 const OrthancPluginResourcesContentMetadata* metadata)
578 {
579 if (count < 1)
580 {
581 return;
582 }
583
584 std::vector<std::string> resourceIds;
585 std::vector<std::string> metadataTypes;
586 std::vector<std::string> metadataValues;
587 std::vector<std::string> revisions;
588
589 Dictionary args;
590
591 for (uint32_t i = 0; i < count; i++)
592 {
593 std::string resourceArgName = "r" + boost::lexical_cast<std::string>(i);
594 std::string typeArgName = "t" + boost::lexical_cast<std::string>(i);
595 std::string valueArgName = "v" + boost::lexical_cast<std::string>(i);
596
597 args.SetIntegerValue(resourceArgName, metadata[i].resource);
598 args.SetInteger32Value(typeArgName, metadata[i].metadata);
599 args.SetUtf8Value(valueArgName, metadata[i].value);
600
601 resourceIds.push_back("${" + resourceArgName + "}");
602 metadataTypes.push_back("${" + typeArgName + "}");
603 metadataValues.push_back("${" + valueArgName + "}");
604 revisions.push_back("0");
605 }
606
607 std::string joinedResourceIds;
608 std::string joinedMetadataTypes;
609 std::string joinedMetadataValues;
610 std::string joinedRevisions;
611
612 Orthanc::Toolbox::JoinStrings(joinedResourceIds, resourceIds, ",");
613 Orthanc::Toolbox::JoinStrings(joinedMetadataTypes, metadataTypes, ",");
614 Orthanc::Toolbox::JoinStrings(joinedMetadataValues, metadataValues, ",");
615 Orthanc::Toolbox::JoinStrings(joinedRevisions, revisions, ",");
616
617 std::string sql = std::string("SELECT InsertOrUpdateMetadata(ARRAY[") +
618 joinedResourceIds + "], ARRAY[" +
619 joinedMetadataTypes + "], ARRAY[" +
620 joinedMetadataValues + "], ARRAY[" +
621 joinedRevisions + "])";
622
623 DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql);
624
625 for (uint32_t i = 0; i < count; i++)
626 {
627 statement.SetParameterType("v" + boost::lexical_cast<std::string>(i),
628 ValueType_Utf8String);
629 statement.SetParameterType("r" + boost::lexical_cast<std::string>(i),
630 ValueType_Integer64);
631 statement.SetParameterType("t" + boost::lexical_cast<std::string>(i),
632 ValueType_Integer32);
633 }
634
635 statement.Execute(args);
636 }
637 #endif
638
639
640 void PostgreSQLIndex::SetResourcesContent(DatabaseManager& manager,
641 uint32_t countIdentifierTags,
642 const OrthancPluginResourcesContentTags* identifierTags,
643 uint32_t countMainDicomTags,
644 const OrthancPluginResourcesContentTags* mainDicomTags,
645 uint32_t countMetadata,
646 const OrthancPluginResourcesContentMetadata* metadata)
647 {
648 ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", countIdentifierTags, identifierTags);
649
650 ExecuteSetResourcesContentTags(manager, "MainDicomTags", countMainDicomTags, mainDicomTags);
651
652 ExecuteSetResourcesContentMetadata(manager, HasRevisionsSupport(), countMetadata, metadata);
653
654 }
655
656
461 uint64_t PostgreSQLIndex::GetResourcesCount(DatabaseManager& manager, 657 uint64_t PostgreSQLIndex::GetResourcesCount(DatabaseManager& manager,
462 OrthancPluginResourceType resourceType) 658 OrthancPluginResourceType resourceType)
463 { 659 {
464 // Optimized version thanks to the "FastCountResources.sql" extension 660 // Optimized version thanks to the "FastCountResources.sql" extension
465 661
469 OrthancPluginResourceType_Instance == 3); 665 OrthancPluginResourceType_Instance == 3);
470 666
471 uint64_t result; 667 uint64_t result;
472 668
473 { 669 {
474 DatabaseManager::CachedStatement statement( 670 DatabaseManager::StandaloneStatement statement(
475 STATEMENT_FROM_HERE, manager, 671 manager,
476 "SELECT value FROM GlobalIntegers WHERE key = ${key}"); 672 std::string("SELECT * FROM UpdateSingleStatistic(") + boost::lexical_cast<std::string>(resourceType + 2) + ")"); // For an explanation of the "+ 2" below, check out "PrepareIndex.sql"
477 673
478 statement.SetParameterType("key", ValueType_Integer64); 674 statement.Execute();
479
480 Dictionary args;
481
482 // For an explanation of the "+ 2" below, check out "FastCountResources.sql"
483 args.SetIntegerValue("key", static_cast<int>(resourceType + 2));
484
485 statement.SetReadOnly(true);
486 statement.Execute(args);
487 675
488 result = static_cast<uint64_t>(statement.ReadInteger64(0)); 676 result = static_cast<uint64_t>(statement.ReadInteger64(0));
489 } 677 }
490 678
679 // disabled because this is not alway true while transactions are being executed in READ COMITTED TRANSACTION. This is however true when no files are being delete/added
491 assert(result == IndexBackend::GetResourcesCount(manager, resourceType)); 680 assert(result == IndexBackend::GetResourcesCount(manager, resourceType));
681
492 return result; 682 return result;
493 } 683 }
494 684
495 685
496 int64_t PostgreSQLIndex::GetLastChangeIndex(DatabaseManager& manager) 686 int64_t PostgreSQLIndex::GetLastChangeIndex(DatabaseManager& manager)
511 { 701 {
512 // This behavior is implemented in "CreateInstance()", and no 702 // This behavior is implemented in "CreateInstance()", and no
513 // backward compatibility is necessary 703 // backward compatibility is necessary
514 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); 704 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
515 } 705 }
706
707
708 // #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
709 // bool PostgreSQLIndex::HasFindSupport() const
710 // {
711 // // TODO-FIND
712 // return false;
713 // }
714 // #endif
715
716
717 // #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
718 // void PostgreSQLIndex::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
719 // DatabaseManager& manager,
720 // const Orthanc::DatabasePluginMessages::Find_Request& request)
721 // {
722 // // TODO-FIND
723 // throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
724 // }
725 // #endif
516 } 726 }