Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/PostgreSQLIndex.cpp @ 79:cb0aac9bbada db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
optimization for /statistics URI
author Sebastien Jodogne <s.jodogne@gmail.com>
date Tue, 08 Jan 2019 14:37:41 +0100
parents 2ee166f77501
children 1012fe77241c
comparison
equal deleted inserted replaced
78:2ee166f77501 79:cb0aac9bbada
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; 38 static const GlobalProperty GlobalProperty_HasCreateInstance = GlobalProperty_DatabaseInternal1;
39 static const GlobalProperty GlobalProperty_HasFastCountResources = GlobalProperty_DatabaseInternal2;
39 } 40 }
40 41
41 42
42 namespace OrthancDatabases 43 namespace OrthancDatabases
43 { 44 {
210 } 211 }
211 212
212 t.Commit(); 213 t.Commit();
213 } 214 }
214 215
216 {
217 PostgreSQLTransaction t(*db);
218
219 // Installing this extension requires the "GlobalIntegers" table
220 // created by the "FastTotalSize" extension
221 int property = 0;
222 if (!LookupGlobalIntegerProperty(property, *db, t,
223 Orthanc::GlobalProperty_HasFastCountResources) ||
224 property != 1)
225 {
226 LOG(INFO) << "Installing the FastCountResources extension";
227
228 std::string query;
229 Orthanc::EmbeddedResources::GetFileResource
230 (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_COUNT_RESOURCES);
231 db->Execute(query);
232
233 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_HasFastCountResources, 1);
234 }
235
236 t.Commit();
237 }
238
215 return db.release(); 239 return db.release();
216 } 240 }
217 241
218 242
219 PostgreSQLIndex::PostgreSQLIndex(const PostgreSQLParameters& parameters) : 243 PostgreSQLIndex::PostgreSQLIndex(const PostgreSQLParameters& parameters) :
246 270
247 271
248 uint64_t PostgreSQLIndex::GetTotalCompressedSize() 272 uint64_t PostgreSQLIndex::GetTotalCompressedSize()
249 { 273 {
250 // Fast version if extension "./FastTotalSize.sql" is installed 274 // Fast version if extension "./FastTotalSize.sql" is installed
251 DatabaseManager::CachedStatement statement( 275 uint64_t result;
252 STATEMENT_FROM_HERE, GetManager(), 276
253 "SELECT value FROM GlobalIntegers WHERE key = 0"); 277 {
254 278 DatabaseManager::CachedStatement statement(
255 statement.SetReadOnly(true); 279 STATEMENT_FROM_HERE, GetManager(),
256 statement.Execute(); 280 "SELECT value FROM GlobalIntegers WHERE key = 0");
257 281
258 return static_cast<uint64_t>(ReadInteger64(statement, 0)); 282 statement.SetReadOnly(true);
283 statement.Execute();
284
285 result = static_cast<uint64_t>(ReadInteger64(statement, 0));
286 }
287
288 assert(result == IndexBackend::GetTotalCompressedSize());
289 return result;
259 } 290 }
260 291
261 292
262 uint64_t PostgreSQLIndex::GetTotalUncompressedSize() 293 uint64_t PostgreSQLIndex::GetTotalUncompressedSize()
263 { 294 {
264 // Fast version if extension "./FastTotalSize.sql" is installed 295 // Fast version if extension "./FastTotalSize.sql" is installed
265 DatabaseManager::CachedStatement statement( 296 uint64_t result;
266 STATEMENT_FROM_HERE, GetManager(), 297
267 "SELECT value FROM GlobalIntegers WHERE key = 1"); 298 {
268 299 DatabaseManager::CachedStatement statement(
269 statement.SetReadOnly(true); 300 STATEMENT_FROM_HERE, GetManager(),
270 statement.Execute(); 301 "SELECT value FROM GlobalIntegers WHERE key = 1");
271 302
272 return static_cast<uint64_t>(ReadInteger64(statement, 0)); 303 statement.SetReadOnly(true);
304 statement.Execute();
305
306 result = static_cast<uint64_t>(ReadInteger64(statement, 0));
307 }
308
309 assert(result == IndexBackend::GetTotalUncompressedSize());
310 return result;
273 } 311 }
274 312
275 313
276 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 314 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
277 void PostgreSQLIndex::CreateInstance(OrthancPluginCreateInstanceResult& result, 315 void PostgreSQLIndex::CreateInstance(OrthancPluginCreateInstanceResult& result,
320 result.studyId = ReadInteger64(statement, 5); 358 result.studyId = ReadInteger64(statement, 5);
321 result.seriesId = ReadInteger64(statement, 6); 359 result.seriesId = ReadInteger64(statement, 6);
322 } 360 }
323 } 361 }
324 #endif 362 #endif
363
364
365 uint64_t PostgreSQLIndex::GetResourceCount(OrthancPluginResourceType resourceType)
366 {
367 // Optimized version thanks to the "FastCountResources.sql" extension
368
369 assert(OrthancPluginResourceType_Patient == 0 &&
370 OrthancPluginResourceType_Study == 1 &&
371 OrthancPluginResourceType_Series == 2 &&
372 OrthancPluginResourceType_Instance == 3);
373
374 uint64_t result;
375
376 {
377 DatabaseManager::CachedStatement statement(
378 STATEMENT_FROM_HERE, GetManager(),
379 "SELECT value FROM GlobalIntegers WHERE key = ${key}");
380
381 statement.SetParameterType("key", ValueType_Integer64);
382
383 Dictionary args;
384
385 // For an explanation of the "+ 2" below, check out "FastCountResources.sql"
386 args.SetIntegerValue("key", static_cast<int>(resourceType + 2));
387
388 statement.SetReadOnly(true);
389 statement.Execute(args);
390
391 result = static_cast<uint64_t>(ReadInteger64(statement, 0));
392 }
393
394 assert(result == IndexBackend::GetResourceCount(resourceType));
395 return result;
396 }
325 } 397 }