index_database.h

Go to the documentation of this file.

// BSD 3-Clause License
// Copyright (c) 2021-2025, 🍀☀🌕🌥 🌊
// See the LICENSE file in the project root for full license information.
 
#pragma once
 
#include "audit_record.h"
#include "instance_record.h"
#include "migration_runner.h"
#include "mpps_record.h"
#include "patient_record.h"
#include "series_record.h"
#include "study_record.h"
#include "ups_workitem.h"
#include "worklist_record.h"
 
#include <kcenon/common/patterns/result.h>
 
#include <chrono>
#include <filesystem>
#include <memory>
#include <optional>
#include <string>
#include <string_view>
#include <vector>
 
#ifdef PACS_WITH_DATABASE_SYSTEM
#include "pacs_database_adapter.h"
#endif
 
// Forward declaration of SQLite handle
struct sqlite3;
 
namespace kcenon::pacs::storage {
 
class patient_repository;
class study_repository;
class series_repository;
class instance_repository;
class mpps_repository;
class worklist_repository;
class ups_repository;
class audit_repository;
 
struct index_config {
    size_t cache_size_mb = 64;
 
    bool wal_mode = true;
 
    bool mmap_enabled = true;
 
    size_t mmap_size = 1024 * 1024 * 1024;
};
 
template <typename T>
using Result = kcenon::common::Result<T>;
 
using VoidResult = kcenon::common::VoidResult;
 
class index_database {
public:
    [[nodiscard]] static auto open(std::string_view db_path)
        -> Result<std::unique_ptr<index_database>>;
 
    [[nodiscard]] static auto open(std::string_view db_path,
                                   const index_config& config)
        -> Result<std::unique_ptr<index_database>>;
 
    ~index_database();
 
    // Non-copyable, movable
    index_database(const index_database&) = delete;
    auto operator=(const index_database&) -> index_database& = delete;
    index_database(index_database&&) noexcept;
    auto operator=(index_database&&) noexcept -> index_database&;
 
    // ========================================================================
    // Patient Operations
    // ========================================================================
 
    [[nodiscard]] auto upsert_patient(std::string_view patient_id,
                                      std::string_view patient_name = "",
                                      std::string_view birth_date = "",
                                      std::string_view sex = "")
        -> Result<int64_t>;
 
    [[nodiscard]] auto upsert_patient(const patient_record& record)
        -> Result<int64_t>;
 
    [[nodiscard]] auto find_patient(std::string_view patient_id) const
        -> std::optional<patient_record>;
 
    [[nodiscard]] auto find_patient_by_pk(int64_t pk) const
        -> std::optional<patient_record>;
 
    [[nodiscard]] auto search_patients(const patient_query& query) const
        -> Result<std::vector<patient_record>>;
 
    [[nodiscard]] auto delete_patient(std::string_view patient_id)
        -> VoidResult;
 
    [[nodiscard]] auto patient_count() const -> Result<size_t>;
 
    // ========================================================================
    // Study Operations
    // ========================================================================
 
    [[nodiscard]] auto upsert_study(int64_t patient_pk,
                                    std::string_view study_uid,
                                    std::string_view study_id = "",
                                    std::string_view study_date = "",
                                    std::string_view study_time = "",
                                    std::string_view accession_number = "",
                                    std::string_view referring_physician = "",
                                    std::string_view study_description = "")
        -> Result<int64_t>;
 
    [[nodiscard]] auto upsert_study(const study_record& record)
        -> Result<int64_t>;
 
    [[nodiscard]] auto find_study(std::string_view study_uid) const
        -> std::optional<study_record>;
 
    [[nodiscard]] auto find_study_by_pk(int64_t pk) const
        -> std::optional<study_record>;
 
    [[nodiscard]] auto list_studies(std::string_view patient_id) const
        -> Result<std::vector<study_record>>;
 
    [[nodiscard]] auto search_studies(const study_query& query) const
        -> Result<std::vector<study_record>>;
 
    [[nodiscard]] auto delete_study(std::string_view study_uid) -> VoidResult;
 
    [[nodiscard]] auto study_count() const -> Result<size_t>;
 
    [[nodiscard]] auto study_count(std::string_view patient_id) const -> Result<size_t>;
 
    [[nodiscard]] auto update_modalities_in_study(int64_t study_pk)
        -> VoidResult;
 
    // ========================================================================
    // Series Operations
    // ========================================================================
 
    [[nodiscard]] auto upsert_series(int64_t study_pk,
                                     std::string_view series_uid,
                                     std::string_view modality = "",
                                     std::optional<int> series_number = std::nullopt,
                                     std::string_view series_description = "",
                                     std::string_view body_part_examined = "",
                                     std::string_view station_name = "")
        -> Result<int64_t>;
 
    [[nodiscard]] auto upsert_series(const series_record& record)
        -> Result<int64_t>;
 
    [[nodiscard]] auto find_series(std::string_view series_uid) const
        -> std::optional<series_record>;
 
    [[nodiscard]] auto find_series_by_pk(int64_t pk) const
        -> std::optional<series_record>;
 
    [[nodiscard]] auto list_series(std::string_view study_uid) const
        -> Result<std::vector<series_record>>;
 
    [[nodiscard]] auto search_series(const series_query& query) const
        -> Result<std::vector<series_record>>;
 
    [[nodiscard]] auto delete_series(std::string_view series_uid) -> VoidResult;
 
    [[nodiscard]] auto series_count() const -> Result<size_t>;
 
    [[nodiscard]] auto series_count(std::string_view study_uid) const -> Result<size_t>;
 
    // ========================================================================
    // Instance Operations
    // ========================================================================
 
    [[nodiscard]] auto upsert_instance(int64_t series_pk,
                                       std::string_view sop_uid,
                                       std::string_view sop_class_uid,
                                       std::string_view file_path,
                                       int64_t file_size,
                                       std::string_view transfer_syntax = "",
                                       std::optional<int> instance_number = std::nullopt)
        -> Result<int64_t>;
 
    [[nodiscard]] auto upsert_instance(const instance_record& record)
        -> Result<int64_t>;
 
    [[nodiscard]] auto find_instance(std::string_view sop_uid) const
        -> std::optional<instance_record>;
 
    [[nodiscard]] auto find_instance_by_pk(int64_t pk) const
        -> std::optional<instance_record>;
 
    [[nodiscard]] auto list_instances(std::string_view series_uid) const
        -> Result<std::vector<instance_record>>;
 
    [[nodiscard]] auto search_instances(const instance_query& query) const
        -> Result<std::vector<instance_record>>;
 
    [[nodiscard]] auto delete_instance(std::string_view sop_uid) -> VoidResult;
 
    [[nodiscard]] auto instance_count() const -> Result<size_t>;
 
    [[nodiscard]] auto instance_count(std::string_view series_uid) const -> Result<size_t>;
 
    // ========================================================================
    // MPPS Operations
    // ========================================================================
 
    [[nodiscard]] auto create_mpps(std::string_view mpps_uid,
                                   std::string_view station_ae = "",
                                   std::string_view modality = "",
                                   std::string_view study_uid = "",
                                   std::string_view accession_no = "",
                                   std::string_view start_datetime = "")
        -> Result<int64_t>;
 
    [[nodiscard]] auto create_mpps(const mpps_record& record) -> Result<int64_t>;
 
    [[nodiscard]] auto update_mpps(std::string_view mpps_uid,
                                   std::string_view new_status,
                                   std::string_view end_datetime = "",
                                   std::string_view performed_series = "")
        -> VoidResult;
 
    [[nodiscard]] auto update_mpps(const mpps_record& record) -> VoidResult;
 
    [[nodiscard]] auto find_mpps(std::string_view mpps_uid) const
        -> std::optional<mpps_record>;
 
    [[nodiscard]] auto find_mpps_by_pk(int64_t pk) const
        -> std::optional<mpps_record>;
 
    [[nodiscard]] auto list_active_mpps(std::string_view station_ae) const
        -> Result<std::vector<mpps_record>>;
 
    [[nodiscard]] auto find_mpps_by_study(std::string_view study_uid) const
        -> Result<std::vector<mpps_record>>;
 
    [[nodiscard]] auto search_mpps(const mpps_query& query) const
        -> Result<std::vector<mpps_record>>;
 
    [[nodiscard]] auto delete_mpps(std::string_view mpps_uid) -> VoidResult;
 
    [[nodiscard]] auto mpps_count() const -> Result<size_t>;
 
    [[nodiscard]] auto mpps_count(std::string_view status) const -> Result<size_t>;
 
    // ========================================================================
    // Worklist Operations
    // ========================================================================
 
    [[nodiscard]] auto add_worklist_item(const worklist_item& item)
        -> Result<int64_t>;
 
    [[nodiscard]] auto update_worklist_status(std::string_view step_id,
                                              std::string_view accession_no,
                                              std::string_view new_status)
        -> VoidResult;
 
    [[nodiscard]] auto query_worklist(const worklist_query& query) const
        -> Result<std::vector<worklist_item>>;
 
    [[nodiscard]] auto find_worklist_item(std::string_view step_id,
                                          std::string_view accession_no) const
        -> std::optional<worklist_item>;
 
    [[nodiscard]] auto find_worklist_by_pk(int64_t pk) const
        -> std::optional<worklist_item>;
 
    [[nodiscard]] auto delete_worklist_item(std::string_view step_id,
                                            std::string_view accession_no)
        -> VoidResult;
 
    [[nodiscard]] auto cleanup_old_worklist_items(std::chrono::hours age)
        -> Result<size_t>;
 
    [[nodiscard]] auto cleanup_worklist_items_before(
        std::chrono::system_clock::time_point before) -> Result<size_t>;
 
    [[nodiscard]] auto worklist_count() const -> Result<size_t>;
 
    [[nodiscard]] auto worklist_count(std::string_view status) const -> Result<size_t>;
 
    // ========================================================================
    // UPS Workitem Operations
    // ========================================================================
 
    [[nodiscard]] auto create_ups_workitem(const ups_workitem& workitem)
        -> Result<int64_t>;
 
    [[nodiscard]] auto update_ups_workitem(const ups_workitem& workitem)
        -> VoidResult;
 
    [[nodiscard]] auto change_ups_state(std::string_view workitem_uid,
                                         std::string_view new_state,
                                         std::string_view transaction_uid = "")
        -> VoidResult;
 
    [[nodiscard]] auto find_ups_workitem(std::string_view workitem_uid) const
        -> std::optional<ups_workitem>;
 
    [[nodiscard]] auto search_ups_workitems(const ups_workitem_query& query) const
        -> Result<std::vector<ups_workitem>>;
 
    [[nodiscard]] auto delete_ups_workitem(std::string_view workitem_uid)
        -> VoidResult;
 
    [[nodiscard]] auto ups_workitem_count() const -> Result<size_t>;
 
    [[nodiscard]] auto ups_workitem_count(std::string_view state) const
        -> Result<size_t>;
 
    // ========================================================================
    // UPS Subscription Operations
    // ========================================================================
 
    [[nodiscard]] auto subscribe_ups(const ups_subscription& subscription)
        -> Result<int64_t>;
 
    [[nodiscard]] auto unsubscribe_ups(std::string_view subscriber_ae,
                                        std::string_view workitem_uid = "")
        -> VoidResult;
 
    [[nodiscard]] auto get_ups_subscriptions(std::string_view subscriber_ae) const
        -> Result<std::vector<ups_subscription>>;
 
    [[nodiscard]] auto get_ups_subscribers(std::string_view workitem_uid) const
        -> Result<std::vector<std::string>>;
 
    // ========================================================================
    // Audit Log Operations
    // ========================================================================
 
    [[nodiscard]] auto add_audit_log(const audit_record& record)
        -> Result<int64_t>;
 
    [[nodiscard]] auto query_audit_log(const audit_query& query) const
        -> Result<std::vector<audit_record>>;
 
    [[nodiscard]] auto find_audit_by_pk(int64_t pk) const
        -> std::optional<audit_record>;
 
    [[nodiscard]] auto audit_count() const -> Result<size_t>;
 
    [[nodiscard]] auto cleanup_old_audit_logs(std::chrono::hours age)
        -> Result<size_t>;
 
    // ========================================================================
    // Database Information
    // ========================================================================
 
    [[nodiscard]] auto path() const -> std::string_view;
 
    [[nodiscard]] auto schema_version() const -> int;
 
    [[nodiscard]] auto is_open() const noexcept -> bool;
 
    // ========================================================================
    // File Path Lookup Operations
    // ========================================================================
 
    [[nodiscard]] auto get_file_path(std::string_view sop_instance_uid) const
        -> Result<std::optional<std::string>>;
 
    [[nodiscard]] auto get_study_files(std::string_view study_instance_uid) const
        -> Result<std::vector<std::string>>;
 
    [[nodiscard]] auto get_series_files(std::string_view series_instance_uid) const
        -> Result<std::vector<std::string>>;
 
    // ========================================================================
    // Database Maintenance Operations
    // ========================================================================
 
    [[nodiscard]] auto vacuum() -> VoidResult;
 
    [[nodiscard]] auto analyze() -> VoidResult;
 
    [[nodiscard]] auto verify_integrity() const -> VoidResult;
 
    [[nodiscard]] auto native_handle() const noexcept -> sqlite3*;
 
#ifdef PACS_WITH_DATABASE_SYSTEM
    [[nodiscard]] auto db_adapter() const noexcept
        -> std::shared_ptr<pacs_database_adapter>;
#endif
 
    [[nodiscard]] auto checkpoint(bool truncate = false) -> VoidResult;
 
    // ========================================================================
    // Storage Statistics
    // ========================================================================
 
    struct storage_stats {
        size_t total_patients = 0;     
        size_t total_studies = 0;      
        size_t total_series = 0;       
        size_t total_instances = 0;    
        int64_t total_file_size = 0;   
        int64_t database_size = 0;     
    };
 
    [[nodiscard]] auto get_storage_stats() const -> Result<storage_stats>;
 
private:
    explicit index_database(sqlite3* db, std::string path);
 
    [[nodiscard]] auto parse_patient_row(void* stmt) const -> patient_record;
 
    [[nodiscard]] auto parse_study_row(void* stmt) const -> study_record;
 
    [[nodiscard]] auto parse_series_row(void* stmt) const -> series_record;
 
    [[nodiscard]] auto parse_instance_row(void* stmt) const -> instance_record;
 
    [[nodiscard]] auto parse_mpps_row(void* stmt) const -> mpps_record;
 
    [[nodiscard]] auto parse_worklist_row(void* stmt) const -> worklist_item;
 
    [[nodiscard]] auto parse_ups_workitem_row(void* stmt) const -> ups_workitem;
 
    [[nodiscard]] auto parse_audit_row(void* stmt) const -> audit_record;
 
    [[nodiscard]] static auto to_like_pattern(std::string_view pattern)
        -> std::string;
 
#ifdef PACS_WITH_DATABASE_SYSTEM
    mutable std::shared_ptr<pacs_database_adapter> db_adapter_;
 
    [[nodiscard]] auto initialize_database_system() -> VoidResult;
 
    [[nodiscard]] auto parse_patient_from_row(
        const std::map<std::string, database::core::database_value>& row) const
        -> patient_record;
 
    [[nodiscard]] auto parse_study_from_row(
        const std::map<std::string, database::core::database_value>& row) const
        -> study_record;
 
    [[nodiscard]] auto parse_series_from_row(
        const std::map<std::string, database::core::database_value>& row) const
        -> series_record;
 
    [[nodiscard]] auto parse_instance_from_row(
        const std::map<std::string, database::core::database_value>& row) const
        -> instance_record;
 
    [[nodiscard]] auto parse_mpps_from_row(
        const std::map<std::string, database::core::database_value>& row) const
        -> mpps_record;
 
    [[nodiscard]] auto parse_worklist_from_row(
        const std::map<std::string, database::core::database_value>& row) const
        -> worklist_item;
 
    [[nodiscard]] auto parse_audit_from_row(
        const std::map<std::string, database::core::database_value>& row) const
        -> audit_record;
 
    [[nodiscard]] auto initialize_database_adapter() -> VoidResult;
 
    [[nodiscard]] auto parse_patient_from_adapter_row(
        const database_row& row) const -> patient_record;
 
    [[nodiscard]] auto parse_study_from_adapter_row(
        const database_row& row) const -> study_record;
 
    [[nodiscard]] auto parse_series_from_adapter_row(
        const database_row& row) const -> series_record;
 
    [[nodiscard]] auto parse_instance_from_adapter_row(
        const database_row& row) const -> instance_record;
 
    [[nodiscard]] auto parse_mpps_from_adapter_row(
        const database_row& row) const -> mpps_record;
 
    [[nodiscard]] auto parse_worklist_from_adapter_row(
        const database_row& row) const -> worklist_item;
#endif
 
    [[nodiscard]] auto initialize_repositories() -> VoidResult;
 
    sqlite3* db_{nullptr};
 
    std::string path_;
 
    bool remove_on_close_{false};
 
    mutable std::shared_ptr<patient_repository> patient_repository_;
    mutable std::shared_ptr<study_repository> study_repository_;
    mutable std::shared_ptr<series_repository> series_repository_;
    mutable std::shared_ptr<instance_repository> instance_repository_;
    mutable std::shared_ptr<mpps_repository> mpps_repository_;
    mutable std::shared_ptr<worklist_repository> worklist_repository_;
    mutable std::shared_ptr<ups_repository> ups_repository_;
    mutable std::shared_ptr<audit_repository> audit_repository_;
 
    migration_runner migration_runner_;
};
 
}  // namespace kcenon::pacs::storage