Mercurial > hg > orthanc-databases

comparison Framework/Plugins/IndexBackend.cpp @ 306:544e0c943b40

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
added transact-sql dialect for LIMIT and OFFSET
author Sebastien Jodogne <s.jodogne@gmail.com>
date Mon, 12 Jul 2021 12:03:33 +0200
parents dd4b0edd1661
children 8de3a1ecac11
comparison
equal deleted inserted replaced
305:87f0e29a1dc1 306:544e0c943b40
521 DatabaseManager& manager, 521 DatabaseManager& manager,
522 OrthancPluginResourceType resourceType, 522 OrthancPluginResourceType resourceType,
523 uint64_t since, 523 uint64_t since,
524 uint64_t limit) 524 uint64_t limit)
525 { 525 {
526 std::string suffix;
527 if (manager.GetDialect() == Dialect_MSSQL)
528 {
529 suffix = "OFFSET ${since} ROWS FETCH FIRST ${limit} ROWS ONLY";
530 }
531 else
532 {
533 suffix = "LIMIT ${limit} OFFSET ${since}";
534 }
535
526 DatabaseManager::CachedStatement statement( 536 DatabaseManager::CachedStatement statement(
527 STATEMENT_FROM_HERE, manager, 537 STATEMENT_FROM_HERE, manager,
528 "SELECT publicId FROM (SELECT publicId FROM Resources " 538 "SELECT publicId FROM (SELECT publicId FROM Resources "
529 "WHERE resourceType=${type}) AS tmp " 539 "WHERE resourceType=${type}) AS tmp ORDER BY tmp.publicId " + suffix);
530 "ORDER BY tmp.publicId LIMIT ${limit} OFFSET ${since}");
531 540
532 statement.SetReadOnly(true); 541 statement.SetReadOnly(true);
533 statement.SetParameterType("type", ValueType_Integer64); 542 statement.SetParameterType("type", ValueType_Integer64);
534 statement.SetParameterType("limit", ValueType_Integer64); 543 statement.SetParameterType("limit", ValueType_Integer64);
535 statement.SetParameterType("since", ValueType_Integer64); 544 statement.SetParameterType("since", ValueType_Integer64);
548 bool& done /*out*/, 557 bool& done /*out*/,
549 DatabaseManager& manager, 558 DatabaseManager& manager,
550 int64_t since, 559 int64_t since,
551 uint32_t maxResults) 560 uint32_t maxResults)
552 { 561 {
553 std::unique_ptr<DatabaseManager::CachedStatement> statement; 562 std::string suffix;
554
555 if (manager.GetDialect() == Dialect_MSSQL) 563 if (manager.GetDialect() == Dialect_MSSQL)
556 { 564 {
557 statement.reset( 565 suffix = "OFFSET 0 ROWS FETCH FIRST ${limit} ROWS ONLY";
558 new DatabaseManager::CachedStatement( 566 }
559 STATEMENT_FROM_HERE, manager, 567 else
560 "SELECT TOP(${limit}) * FROM Changes WHERE seq>${since} ORDER BY seq")); 568 {
561 } 569 suffix = "LIMIT ${limit}";
562 else 570 }
563 { 571
564 statement.reset( 572 DatabaseManager::CachedStatement statement(
565 new DatabaseManager::CachedStatement( 573 STATEMENT_FROM_HERE, manager,
566 STATEMENT_FROM_HERE, manager, 574 "SELECT * FROM Changes WHERE seq>${since} ORDER BY seq " + suffix);
567 "SELECT * FROM Changes WHERE seq>${since} ORDER BY seq LIMIT ${limit}")); 575
568 } 576 statement.SetReadOnly(true);
569 577 statement.SetParameterType("limit", ValueType_Integer64);
570 statement->SetReadOnly(true); 578 statement.SetParameterType("since", ValueType_Integer64);
571 statement->SetParameterType("limit", ValueType_Integer64);
572 statement->SetParameterType("since", ValueType_Integer64);
573 579
574 Dictionary args; 580 Dictionary args;
575 args.SetIntegerValue("limit", maxResults + 1); 581 args.SetIntegerValue("limit", maxResults + 1);
576 args.SetIntegerValue("since", since); 582 args.SetIntegerValue("since", since);
577 583
578 ReadChangesInternal(output, done, manager, *statement, args, maxResults); 584 ReadChangesInternal(output, done, manager, statement, args, maxResults);
579 } 585 }
580 586
581 587
582 void IndexBackend::GetChildrenInternalId(std::list<int64_t>& target /*out*/, 588 void IndexBackend::GetChildrenInternalId(std::list<int64_t>& target /*out*/,
583 DatabaseManager& manager, 589 DatabaseManager& manager,
622 bool& done /*out*/, 628 bool& done /*out*/,
623 DatabaseManager& manager, 629 DatabaseManager& manager,
624 int64_t since, 630 int64_t since,
625 uint32_t maxResults) 631 uint32_t maxResults)
626 { 632 {
627 std::unique_ptr<DatabaseManager::CachedStatement> statement; 633 std::string suffix;
628
629 if (manager.GetDialect() == Dialect_MSSQL) 634 if (manager.GetDialect() == Dialect_MSSQL)
630 { 635 {
631 statement.reset( 636 suffix = "OFFSET 0 ROWS FETCH FIRST ${limit} ROWS ONLY";
632 new DatabaseManager::CachedStatement( 637 }
633 STATEMENT_FROM_HERE, manager, 638 else
634 "SELECT TOP(${limit}) * FROM ExportedResources WHERE seq>${since} ORDER BY seq")); 639 {
635 } 640 suffix = "LIMIT ${limit}";
636 else 641 }
637 { 642
638 statement.reset( 643 DatabaseManager::CachedStatement statement(
639 new DatabaseManager::CachedStatement( 644 STATEMENT_FROM_HERE, manager,
640 STATEMENT_FROM_HERE, manager, 645 "SELECT * FROM ExportedResources WHERE seq>${since} ORDER BY seq " + suffix);
641 "SELECT * FROM ExportedResources WHERE seq>${since} ORDER BY seq LIMIT ${limit}")); 646
642 } 647 statement.SetReadOnly(true);
643 648 statement.SetParameterType("limit", ValueType_Integer64);
644 statement->SetReadOnly(true); 649 statement.SetParameterType("since", ValueType_Integer64);
645 statement->SetParameterType("limit", ValueType_Integer64);
646 statement->SetParameterType("since", ValueType_Integer64);
647 650
648 Dictionary args; 651 Dictionary args;
649 args.SetIntegerValue("limit", maxResults + 1); 652 args.SetIntegerValue("limit", maxResults + 1);
650 args.SetIntegerValue("since", since); 653 args.SetIntegerValue("since", since);
651 654
652 ReadExportedResourcesInternal(output, done, *statement, args, maxResults); 655 ReadExportedResourcesInternal(output, done, statement, args, maxResults);
653 } 656 }
654 657
655 658
656 /* Use GetOutput().AnswerChange() */ 659 /* Use GetOutput().AnswerChange() */
657 void IndexBackend::GetLastChange(IDatabaseBackendOutput& output, 660 void IndexBackend::GetLastChange(IDatabaseBackendOutput& output,
658 DatabaseManager& manager) 661 DatabaseManager& manager)
659 { 662 {
660 DatabaseManager::CachedStatement statement( 663 std::string suffix;
661 STATEMENT_FROM_HERE, manager, 664 if (manager.GetDialect() == Dialect_MSSQL)
662 "SELECT * FROM Changes ORDER BY seq DESC LIMIT 1"); 665 {
666 suffix = "OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY";
667 }
668 else
669 {
670 suffix = "LIMIT 1";
671 }
672
673 DatabaseManager::CachedStatement statement(
674 STATEMENT_FROM_HERE, manager,
675 "SELECT * FROM Changes ORDER BY seq DESC " + suffix);
663 676
664 statement.SetReadOnly(true); 677 statement.SetReadOnly(true);
665 678
666 Dictionary args; 679 Dictionary args;
667 680
672 685
673 /* Use GetOutput().AnswerExportedResource() */ 686 /* Use GetOutput().AnswerExportedResource() */
674 void IndexBackend::GetLastExportedResource(IDatabaseBackendOutput& output, 687 void IndexBackend::GetLastExportedResource(IDatabaseBackendOutput& output,
675 DatabaseManager& manager) 688 DatabaseManager& manager)
676 { 689 {
677 DatabaseManager::CachedStatement statement( 690 std::string suffix;
678 STATEMENT_FROM_HERE, manager, 691 if (manager.GetDialect() == Dialect_MSSQL)
679 "SELECT * FROM ExportedResources ORDER BY seq DESC LIMIT 1"); 692 {
693 suffix = "OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY";
694 }
695 else
696 {
697 suffix = "LIMIT 1";
698 }
699
700 DatabaseManager::CachedStatement statement(
701 STATEMENT_FROM_HERE, manager,
702 "SELECT * FROM ExportedResources ORDER BY seq DESC " + suffix);
680 703
681 statement.SetReadOnly(true); 704 statement.SetReadOnly(true);
682 705
683 Dictionary args; 706 Dictionary args;
684 707
1410 1433
1411 1434
1412 bool IndexBackend::SelectPatientToRecycle(int64_t& internalId /*out*/, 1435 bool IndexBackend::SelectPatientToRecycle(int64_t& internalId /*out*/,
1413 DatabaseManager& manager) 1436 DatabaseManager& manager)
1414 { 1437 {
1415 DatabaseManager::CachedStatement statement( 1438 std::string suffix;
1416 STATEMENT_FROM_HERE, manager, 1439 if (manager.GetDialect() == Dialect_MSSQL)
1417 "SELECT patientId FROM PatientRecyclingOrder ORDER BY seq ASC LIMIT 1"); 1440 {
1418 1441 suffix = "OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY";
1442 }
1443 else
1444 {
1445 suffix = "LIMIT 1";
1446 }
1447
1448 DatabaseManager::CachedStatement statement(
1449 STATEMENT_FROM_HERE, manager,
1450 "SELECT patientId FROM PatientRecyclingOrder ORDER BY seq ASC " + suffix);
1451
1419 statement.SetReadOnly(true); 1452 statement.SetReadOnly(true);
1420 statement.Execute(); 1453 statement.Execute();
1421 1454
1422 if (statement.IsDone()) 1455 if (statement.IsDone())
1423 { 1456 {
1433 1466
1434 bool IndexBackend::SelectPatientToRecycle(int64_t& internalId /*out*/, 1467 bool IndexBackend::SelectPatientToRecycle(int64_t& internalId /*out*/,
1435 DatabaseManager& manager, 1468 DatabaseManager& manager,
1436 int64_t patientIdToAvoid) 1469 int64_t patientIdToAvoid)
1437 { 1470 {
1471 std::string suffix;
1472 if (manager.GetDialect() == Dialect_MSSQL)
1473 {
1474 suffix = "OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY";
1475 }
1476 else
1477 {
1478 suffix = "LIMIT 1";
1479 }
1480
1438 DatabaseManager::CachedStatement statement( 1481 DatabaseManager::CachedStatement statement(
1439 STATEMENT_FROM_HERE, manager, 1482 STATEMENT_FROM_HERE, manager,
1440 "SELECT patientId FROM PatientRecyclingOrder " 1483 "SELECT patientId FROM PatientRecyclingOrder "
1441 "WHERE patientId != ${id} ORDER BY seq ASC LIMIT 1"); 1484 "WHERE patientId != ${id} ORDER BY seq ASC " + suffix);
1442 1485
1443 statement.SetReadOnly(true); 1486 statement.SetReadOnly(true);
1444 statement.SetParameterType("id", ValueType_Integer64); 1487 statement.SetParameterType("id", ValueType_Integer64);
1445 1488
1446 Dictionary args; 1489 Dictionary args;
2244 2287
2245 // New primitive since Orthanc 1.5.2 2288 // New primitive since Orthanc 1.5.2
2246 void IndexBackend::TagMostRecentPatient(DatabaseManager& manager, 2289 void IndexBackend::TagMostRecentPatient(DatabaseManager& manager,
2247 int64_t patient) 2290 int64_t patient)
2248 { 2291 {
2292 std::string suffix;
2293 if (manager.GetDialect() == Dialect_MSSQL)
2294 {
2295 suffix = "OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY";
2296 }
2297 else
2298 {
2299 suffix = "LIMIT 2";
2300 }
2301
2249 int64_t seq; 2302 int64_t seq;
2250 2303
2251 { 2304 {
2252 DatabaseManager::CachedStatement statement( 2305 DatabaseManager::CachedStatement statement(
2253 STATEMENT_FROM_HERE, manager, 2306 STATEMENT_FROM_HERE, manager,
2254 "SELECT * FROM PatientRecyclingOrder WHERE seq >= " 2307 "SELECT * FROM PatientRecyclingOrder WHERE seq >= "
2255 "(SELECT seq FROM PatientRecyclingOrder WHERE patientid=${id}) ORDER BY seq LIMIT 2"); 2308 "(SELECT seq FROM PatientRecyclingOrder WHERE patientid=${id}) ORDER BY seq " + suffix);
2256 2309
2257 statement.SetReadOnly(true); 2310 statement.SetReadOnly(true);
2258 statement.SetParameterType("id", ValueType_Integer64); 2311 statement.SetParameterType("id", ValueType_Integer64);
2259 2312
2260 Dictionary args; 2313 Dictionary args;
2298 // Add the patient to the end of the recycling order 2351 // Add the patient to the end of the recycling order
2299 2352
2300 { 2353 {
2301 DatabaseManager::CachedStatement statement( 2354 DatabaseManager::CachedStatement statement(
2302 STATEMENT_FROM_HERE, manager, 2355 STATEMENT_FROM_HERE, manager,
2303 "INSERT INTO PatientRecyclingOrder VALUES({AUTOINCREMENT} ${id})"); 2356 "INSERT INTO PatientRecyclingOrder VALUES(${AUTOINCREMENT} ${id})");
2304 2357
2305 statement.SetParameterType("id", ValueType_Integer64); 2358 statement.SetParameterType("id", ValueType_Integer64);
2306 2359
2307 Dictionary args; 2360 Dictionary args;
2308 args.SetIntegerValue("id", patient); 2361 args.SetIntegerValue("id", patient);