Mercurial > hg > orthanc-databases
comparison Framework/Plugins/IndexBackend.cpp @ 306:544e0c943b40
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); |