Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/PostgreSQLIndex.cpp @ 97:66fd0d587773

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
integration db-changes->mainline
author Sebastien Jodogne <s.jodogne@gmail.com>
date Mon, 21 Jan 2019 14:38:12 +0100
parents e61587582cef
children cc3dc759c989
comparison
equal deleted inserted replaced
91:1bd538a5a783 97:66fd0d587773
33 33
34 namespace Orthanc 34 namespace Orthanc
35 { 35 {
36 // Some aliases for internal properties 36 // Some aliases for internal properties
37 static const GlobalProperty GlobalProperty_HasTrigramIndex = GlobalProperty_DatabaseInternal0; 37 static const GlobalProperty GlobalProperty_HasTrigramIndex = GlobalProperty_DatabaseInternal0;
38 static const GlobalProperty GlobalProperty_HasCreateInstance = GlobalProperty_DatabaseInternal1;
39 static const GlobalProperty GlobalProperty_HasFastCountResources = GlobalProperty_DatabaseInternal2;
40 static const GlobalProperty GlobalProperty_GetLastChangeIndex = GlobalProperty_DatabaseInternal3;
38 } 41 }
39 42
40 43
41 namespace OrthancDatabases 44 namespace OrthancDatabases
42 { 45 {
124 127
125 { 128 {
126 PostgreSQLTransaction t(*db); 129 PostgreSQLTransaction t(*db);
127 130
128 int hasTrigram = 0; 131 int hasTrigram = 0;
129 if (!LookupGlobalIntegerProperty(hasTrigram, *db, t, Orthanc::GlobalProperty_HasTrigramIndex) || 132 if (!LookupGlobalIntegerProperty(hasTrigram, *db, t,
133 Orthanc::GlobalProperty_HasTrigramIndex) ||
130 hasTrigram != 1) 134 hasTrigram != 1)
131 { 135 {
132 /** 136 /**
133 * Apply fix for performance issue (speed up wildcard search 137 * Apply fix for performance issue (speed up wildcard search
134 * by using GIN trigrams). This implements the patch suggested 138 * by using GIN trigrams). This implements the patch suggested
160 << "not support trigram matching"; 164 << "not support trigram matching";
161 LOG(WARNING) << "-> Consider installing the \"pg_trgm\" extension on the " 165 LOG(WARNING) << "-> Consider installing the \"pg_trgm\" extension on the "
162 << "PostgreSQL server, e.g. on Debian: sudo apt install postgresql-contrib"; 166 << "PostgreSQL server, e.g. on Debian: sudo apt install postgresql-contrib";
163 } 167 }
164 } 168 }
169 else
170 {
171 t.Commit();
172 }
173 }
174
175 {
176 PostgreSQLTransaction t(*db);
177
178 int property = 0;
179 if (!LookupGlobalIntegerProperty(property, *db, t,
180 Orthanc::GlobalProperty_HasCreateInstance) ||
181 property != 2)
182 {
183 LOG(INFO) << "Installing the CreateInstance extension";
184
185 if (property == 1)
186 {
187 // Drop older, experimental versions of this extension
188 db->Execute("DROP FUNCTION CreateInstance("
189 "IN patient TEXT, IN study TEXT, IN series TEXT, in instance TEXT)");
190 }
191
192 std::string query;
193 Orthanc::EmbeddedResources::GetFileResource
194 (query, Orthanc::EmbeddedResources::POSTGRESQL_CREATE_INSTANCE);
195 db->Execute(query);
196
197 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_HasCreateInstance, 2);
198 }
199
200
201 if (!LookupGlobalIntegerProperty(property, *db, t,
202 Orthanc::GlobalProperty_GetTotalSizeIsFast) ||
203 property != 1)
204 {
205 LOG(INFO) << "Installing the FastTotalSize extension";
206
207 std::string query;
208 Orthanc::EmbeddedResources::GetFileResource
209 (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_TOTAL_SIZE);
210 db->Execute(query);
211
212 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_GetTotalSizeIsFast, 1);
213 }
214
215
216 // Installing this extension requires the "GlobalIntegers" table
217 // created by the "FastTotalSize" extension
218 property = 0;
219 if (!LookupGlobalIntegerProperty(property, *db, t,
220 Orthanc::GlobalProperty_HasFastCountResources) ||
221 property != 1)
222 {
223 LOG(INFO) << "Installing the FastCountResources extension";
224
225 std::string query;
226 Orthanc::EmbeddedResources::GetFileResource
227 (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_COUNT_RESOURCES);
228 db->Execute(query);
229
230 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_HasFastCountResources, 1);
231 }
232
233
234 // Installing this extension requires the "GlobalIntegers" table
235 // created by the "GetLastChangeIndex" extension
236 property = 0;
237 if (!LookupGlobalIntegerProperty(property, *db, t,
238 Orthanc::GlobalProperty_GetLastChangeIndex) ||
239 property != 1)
240 {
241 LOG(INFO) << "Installing the GetLastChangeIndex extension";
242
243 std::string query;
244 Orthanc::EmbeddedResources::GetFileResource
245 (query, Orthanc::EmbeddedResources::POSTGRESQL_GET_LAST_CHANGE_INDEX);
246 db->Execute(query);
247
248 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_GetLastChangeIndex, 1);
249 }
250
251 t.Commit();
165 } 252 }
166 253
167 return db.release(); 254 return db.release();
168 } 255 }
169 256
193 280
194 statement.Execute(args); 281 statement.Execute(args);
195 282
196 return ReadInteger64(statement, 0); 283 return ReadInteger64(statement, 0);
197 } 284 }
285
286
287 uint64_t PostgreSQLIndex::GetTotalCompressedSize()
288 {
289 // Fast version if extension "./FastTotalSize.sql" is installed
290 uint64_t result;
291
292 {
293 DatabaseManager::CachedStatement statement(
294 STATEMENT_FROM_HERE, GetManager(),
295 "SELECT value FROM GlobalIntegers WHERE key = 0");
296
297 statement.SetReadOnly(true);
298 statement.Execute();
299
300 result = static_cast<uint64_t>(ReadInteger64(statement, 0));
301 }
302
303 assert(result == IndexBackend::GetTotalCompressedSize());
304 return result;
305 }
306
307
308 uint64_t PostgreSQLIndex::GetTotalUncompressedSize()
309 {
310 // Fast version if extension "./FastTotalSize.sql" is installed
311 uint64_t result;
312
313 {
314 DatabaseManager::CachedStatement statement(
315 STATEMENT_FROM_HERE, GetManager(),
316 "SELECT value FROM GlobalIntegers WHERE key = 1");
317
318 statement.SetReadOnly(true);
319 statement.Execute();
320
321 result = static_cast<uint64_t>(ReadInteger64(statement, 0));
322 }
323
324 assert(result == IndexBackend::GetTotalUncompressedSize());
325 return result;
326 }
327
328
329 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
330 void PostgreSQLIndex::CreateInstance(OrthancPluginCreateInstanceResult& result,
331 const char* hashPatient,
332 const char* hashStudy,
333 const char* hashSeries,
334 const char* hashInstance)
335 {
336 DatabaseManager::CachedStatement statement(
337 STATEMENT_FROM_HERE, GetManager(),
338 "SELECT * FROM CreateInstance(${patient}, ${study}, ${series}, ${instance})");
339
340 statement.SetParameterType("patient", ValueType_Utf8String);
341 statement.SetParameterType("study", ValueType_Utf8String);
342 statement.SetParameterType("series", ValueType_Utf8String);
343 statement.SetParameterType("instance", ValueType_Utf8String);
344
345 Dictionary args;
346 args.SetUtf8Value("patient", hashPatient);
347 args.SetUtf8Value("study", hashStudy);
348 args.SetUtf8Value("series", hashSeries);
349 args.SetUtf8Value("instance", hashInstance);
350
351 statement.Execute(args);
352
353 if (statement.IsDone() ||
354 statement.GetResultFieldsCount() != 8)
355 {
356 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
357 }
358
359 for (size_t i = 0; i < 8; i++)
360 {
361 statement.SetResultFieldType(i, ValueType_Integer64);
362 }
363
364 result.isNewInstance = (ReadInteger64(statement, 3) == 1);
365 result.instanceId = ReadInteger64(statement, 7);
366
367 if (result.isNewInstance)
368 {
369 result.isNewPatient = (ReadInteger64(statement, 0) == 1);
370 result.isNewStudy = (ReadInteger64(statement, 1) == 1);
371 result.isNewSeries = (ReadInteger64(statement, 2) == 1);
372 result.patientId = ReadInteger64(statement, 4);
373 result.studyId = ReadInteger64(statement, 5);
374 result.seriesId = ReadInteger64(statement, 6);
375 }
376 }
377 #endif
378
379
380 uint64_t PostgreSQLIndex::GetResourceCount(OrthancPluginResourceType resourceType)
381 {
382 // Optimized version thanks to the "FastCountResources.sql" extension
383
384 assert(OrthancPluginResourceType_Patient == 0 &&
385 OrthancPluginResourceType_Study == 1 &&
386 OrthancPluginResourceType_Series == 2 &&
387 OrthancPluginResourceType_Instance == 3);
388
389 uint64_t result;
390
391 {
392 DatabaseManager::CachedStatement statement(
393 STATEMENT_FROM_HERE, GetManager(),
394 "SELECT value FROM GlobalIntegers WHERE key = ${key}");
395
396 statement.SetParameterType("key", ValueType_Integer64);
397
398 Dictionary args;
399
400 // For an explanation of the "+ 2" below, check out "FastCountResources.sql"
401 args.SetIntegerValue("key", static_cast<int>(resourceType + 2));
402
403 statement.SetReadOnly(true);
404 statement.Execute(args);
405
406 result = static_cast<uint64_t>(ReadInteger64(statement, 0));
407 }
408
409 assert(result == IndexBackend::GetResourceCount(resourceType));
410 return result;
411 }
412
413
414 int64_t PostgreSQLIndex::GetLastChangeIndex()
415 {
416 DatabaseManager::CachedStatement statement(
417 STATEMENT_FROM_HERE, GetManager(),
418 "SELECT value FROM GlobalIntegers WHERE key = 6");
419
420 statement.SetReadOnly(true);
421 statement.Execute();
422
423 return ReadInteger64(statement, 0);
424 }
425
426
427 void PostgreSQLIndex::TagMostRecentPatient(int64_t patient)
428 {
429 // This behavior is implemented in "CreateInstance()", and no
430 // backward compatibility is necessary
431 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
432 }
198 } 433 }