ライブラリ

SOCI

概要

SOCIは「Database Access Library for C++」として、C++アプリケーションのための軽量でポータブルなデータベースアクセス層を提供します。複数のデータベースエンジン（PostgreSQL、MySQL、SQLite、Oracle、DB2、Firebird等）に対して統一されたAPIを提供し、C++の型安全性とRAII（Resource Acquisition Is Initialization）パターンを活用した効率的なデータベース操作を実現します。STLコンテナとの自然な統合、準備済みステートメント、接続プール等のモダンなC++開発に必要な機能を包括的にサポートします。

詳細

SOCI 2025年版はC++17標準に対応し、モダンC++の機能（スマートポインタ、move semantics、range-based for loops等）を最大限活用したデータベースプログラミングエクスペリエンスを提供します。型安全なクエリ実行、自動的なリソース管理、例外安全性を基盤とし、エンタープライズアプリケーション開発に求められる信頼性とパフォーマンスを実現。複数データベース間の移植性とバックエンド透過性により、データベース技術の選択に柔軟性を提供します。CMakeサポート、vcpkg・Conan等のパッケージマネージャー対応により、モダンなC++開発環境との統合も容易です。

主な特徴

• マルチデータベース: 主要なRDBMSへの統一API提供
• 型安全性: C++型システムとの完全統合
• RAII設計: 自動的なリソース管理と例外安全性
• STL統合: STLコンテナとの自然な連携
• ポータブル: クロスプラットフォーム対応
• 準備済みステートメント: SQLインジェクション対策とパフォーマンス最適化

メリット・デメリット

メリット

• 複数データベース間の高い移植性と統一的なAPIの提供
• C++らしい型安全で直感的なプログラミングモデル
• RAII設計による確実なリソース管理と例外安全性
• STLとの自然な統合によるC++開発者フレンドリーな設計
• 軽量でオーバーヘッドの少ない実装
• 豊富なデータベースバックエンドサポート

デメリット

• ORM機能がなく、生SQLベースでのプログラミングが必要
• C++特有の学習曲線とメモリ管理の複雑さ
• モダンなWebフレームワークとの統合で追加的な作業が必要
• ドキュメントや日本語リソースの限定性
• 高水準なデータベース抽象化レイヤーの不足
• 複雑なオブジェクトマッピングには向かない

参考ページ

• SOCI 公式サイト
• SOCI GitHub リポジトリ
• SOCI ドキュメント

書き方の例

セットアップ

// CMakeLists.txt
cmake_minimum_required(VERSION 3.16)
project(soci_example)

set(CMAKE_CXX_STANDARD 17)
set(CMAKE_CXX_STANDARD_REQUIRED ON)

# Find SOCI
find_package(SOCI REQUIRED)

# Find database backend (PostgreSQL example)
find_package(PostgreSQL REQUIRED)

add_executable(soci_example main.cpp)

# Link SOCI
target_link_libraries(soci_example 
    SOCI::soci_core 
    SOCI::soci_postgresql
    ${PostgreSQL_LIBRARIES}
)

// vcpkg installation
/*
vcpkg install soci[postgresql,mysql,sqlite3]:x64-linux
vcpkg install soci[postgresql,mysql,sqlite3]:x64-windows
*/

// Conan installation
/*
[requires]
soci/4.0.3

[generators]
cmake_find_package
cmake_paths

[options]
soci:shared=True
soci:with_postgresql=True
soci:with_mysql=True
soci:with_sqlite3=True
*/

基本的な使い方

#include <soci/soci.h>
#include <soci/postgresql/soci-postgresql.h>
#include <iostream>
#include <string>
#include <vector>
#include <memory>

using namespace soci;

// ユーザー構造体
struct User {
    int id;
    std::string name;
    std::string email;
    int age;
    std::string created_at;
    
    User() = default;
    User(int id, const std::string& name, const std::string& email, int age)
        : id(id), name(name), email(email), age(age) {}
};

// ユーザーテーブルの型変換設定
namespace soci {
    template<>
    struct type_conversion<User> {
        typedef values base_type;
        
        static void from_base(const values& v, indicator& ind, User& user) {
            user.id = v.get<int>("id");
            user.name = v.get<std::string>("name");
            user.email = v.get<std::string>("email");
            user.age = v.get<int>("age");
            user.created_at = v.get<std::string>("created_at");
            ind = i_ok;
        }
        
        static void to_base(const User& user, values& v, indicator& ind) {
            v.set("id", user.id);
            v.set("name", user.name);
            v.set("email", user.email);
            v.set("age", user.age);
            v.set("created_at", user.created_at);
            ind = i_ok;
        }
    };
}

class UserDatabase {
private:
    std::unique_ptr<session> sql_;
    
public:
    // データベース接続
    explicit UserDatabase(const std::string& connection_string) {
        try {
            sql_ = std::make_unique<session>(postgresql, connection_string);
            
            // テーブル作成
            createTables();
            
            std::cout << "Database connected successfully" << std::endl;
        } catch (const soci_error& e) {
            std::cerr << "Database connection failed: " << e.what() << std::endl;
            throw;
        }
    }
    
    ~UserDatabase() = default;
    
    // テーブル作成
    void createTables() {
        try {
            *sql_ << R"(
                CREATE TABLE IF NOT EXISTS users (
                    id SERIAL PRIMARY KEY,
                    name VARCHAR(255) NOT NULL,
                    email VARCHAR(255) UNIQUE NOT NULL,
                    age INTEGER NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            )";
            
            *sql_ << R"(
                CREATE TABLE IF NOT EXISTS posts (
                    id SERIAL PRIMARY KEY,
                    title VARCHAR(255) NOT NULL,
                    content TEXT,
                    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
                    published BOOLEAN DEFAULT FALSE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            )";
            
            std::cout << "Tables created successfully" << std::endl;
        } catch (const soci_error& e) {
            std::cerr << "Table creation failed: " << e.what() << std::endl;
            throw;
        }
    }
    
    // ユーザー追加
    int addUser(const std::string& name, const std::string& email, int age) {
        try {
            int user_id;
            *sql_ << "INSERT INTO users (name, email, age) VALUES (:name, :email, :age) RETURNING id",
                use(name), use(email), use(age), into(user_id);
                
            std::cout << "User added with ID: " << user_id << std::endl;
            return user_id;
        } catch (const soci_error& e) {
            std::cerr << "Failed to add user: " << e.what() << std::endl;
            throw;
        }
    }
    
    // ユーザー取得
    std::vector<User> getAllUsers() {
        try {
            std::vector<User> users;
            rowset<User> rs = (sql_->prepare << "SELECT id, name, email, age, created_at FROM users ORDER BY id");
            
            for (const auto& user : rs) {
                users.push_back(user);
            }
            
            return users;
        } catch (const soci_error& e) {
            std::cerr << "Failed to get users: " << e.what() << std::endl;
            throw;
        }
    }
    
    // ID指定でユーザー取得
    std::unique_ptr<User> getUserById(int id) {
        try {
            auto user = std::make_unique<User>();
            indicator ind;
            
            *sql_ << "SELECT id, name, email, age, created_at FROM users WHERE id = :id",
                into(*user, ind), use(id);
                
            if (ind == i_ok) {
                return user;
            } else {
                return nullptr;
            }
        } catch (const soci_error& e) {
            std::cerr << "Failed to get user: " << e.what() << std::endl;
            return nullptr;
        }
    }
    
    // ユーザー更新
    bool updateUser(int id, const std::string& name, const std::string& email, int age) {
        try {
            statement st = (sql_->prepare <<
                "UPDATE users SET name = :name, email = :email, age = :age WHERE id = :id",
                use(name), use(email), use(age), use(id));
            
            st.execute();
            return st.get_affected_rows() > 0;
        } catch (const soci_error& e) {
            std::cerr << "Failed to update user: " << e.what() << std::endl;
            return false;
        }
    }
    
    // ユーザー削除
    bool deleteUser(int id) {
        try {
            statement st = (sql_->prepare << "DELETE FROM users WHERE id = :id", use(id));
            st.execute();
            return st.get_affected_rows() > 0;
        } catch (const soci_error& e) {
            std::cerr << "Failed to delete user: " << e.what() << std::endl;
            return false;
        }
    }
    
    // 検索機能
    std::vector<User> searchUsers(const std::string& searchTerm) {
        try {
            std::vector<User> users;
            std::string pattern = "%" + searchTerm + "%";
            
            rowset<User> rs = (sql_->prepare <<
                "SELECT id, name, email, age, created_at FROM users "
                "WHERE name ILIKE :pattern OR email ILIKE :pattern "
                "ORDER BY name",
                use(pattern));
            
            for (const auto& user : rs) {
                users.push_back(user);
            }
            
            return users;
        } catch (const soci_error& e) {
            std::cerr << "Failed to search users: " << e.what() << std::endl;
            throw;
        }
    }
    
    // 年齢範囲でフィルタ
    std::vector<User> getUsersByAgeRange(int minAge, int maxAge) {
        try {
            std::vector<User> users;
            
            rowset<User> rs = (sql_->prepare <<
                "SELECT id, name, email, age, created_at FROM users "
                "WHERE age BETWEEN :min_age AND :max_age "
                "ORDER BY age, name",
                use(minAge), use(maxAge));
            
            for (const auto& user : rs) {
                users.push_back(user);
            }
            
            return users;
        } catch (const soci_error& e) {
            std::cerr << "Failed to get users by age range: " << e.what() << std::endl;
            throw;
        }
    }
    
    // 統計情報取得
    struct UserStats {
        int total_users;
        double average_age;
        int min_age;
        int max_age;
    };
    
    UserStats getUserStatistics() {
        try {
            UserStats stats{};
            
            *sql_ << "SELECT COUNT(*), AVG(age), MIN(age), MAX(age) FROM users",
                into(stats.total_users), into(stats.average_age), 
                into(stats.min_age), into(stats.max_age);
                
            return stats;
        } catch (const soci_error& e) {
            std::cerr << "Failed to get user statistics: " << e.what() << std::endl;
            throw;
        }
    }
};

// 使用例
void demonstrateBasicOperations() {
    try {
        UserDatabase db("postgresql://user:password@localhost/testdb");
        
        // ユーザー追加
        int userId1 = db.addUser("Alice Smith", "[email protected]", 25);
        int userId2 = db.addUser("Bob Johnson", "[email protected]", 30);
        int userId3 = db.addUser("Charlie Brown", "[email protected]", 35);
        
        // 全ユーザー取得
        auto users = db.getAllUsers();
        std::cout << "\n=== All Users ===" << std::endl;
        for (const auto& user : users) {
            std::cout << "ID: " << user.id << ", Name: " << user.name 
                      << ", Email: " << user.email << ", Age: " << user.age << std::endl;
        }
        
        // 特定ユーザー取得
        auto user = db.getUserById(userId1);
        if (user) {
            std::cout << "\n=== User by ID ===" << std::endl;
            std::cout << "Found user: " << user->name << std::endl;
        }
        
        // ユーザー更新
        if (db.updateUser(userId1, "Alice Johnson", "[email protected]", 26)) {
            std::cout << "\n=== User Updated ===" << std::endl;
            std::cout << "User updated successfully" << std::endl;
        }
        
        // 検索
        auto searchResults = db.searchUsers("Alice");
        std::cout << "\n=== Search Results ===" << std::endl;
        for (const auto& user : searchResults) {
            std::cout << "Found: " << user.name << " (" << user.email << ")" << std::endl;
        }
        
        // 統計情報
        auto stats = db.getUserStatistics();
        std::cout << "\n=== Statistics ===" << std::endl;
        std::cout << "Total users: " << stats.total_users << std::endl;
        std::cout << "Average age: " << stats.average_age << std::endl;
        std::cout << "Age range: " << stats.min_age << " - " << stats.max_age << std::endl;
        
    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
}

トランザクション処理

#include <soci/soci.h>
#include <soci/postgresql/soci-postgresql.h>

class TransactionManager {
private:
    std::unique_ptr<session> sql_;
    
public:
    explicit TransactionManager(const std::string& connection_string) 
        : sql_(std::make_unique<session>(postgresql, connection_string)) {}
    
    // トランザクション付きユーザー移行
    bool transferUserPosts(int fromUserId, int toUserId) {
        try {
            transaction tr(*sql_);
            
            // ユーザー存在確認
            int fromUserCount, toUserCount;
            *sql_ << "SELECT COUNT(*) FROM users WHERE id = :id", 
                use(fromUserId), into(fromUserCount);
            *sql_ << "SELECT COUNT(*) FROM users WHERE id = :id", 
                use(toUserId), into(toUserCount);
                
            if (fromUserCount == 0 || toUserCount == 0) {
                std::cerr << "One or both users not found" << std::endl;
                return false;
            }
            
            // 投稿の移行
            statement st = (sql_->prepare << 
                "UPDATE posts SET user_id = :to_user WHERE user_id = :from_user",
                use(toUserId), use(fromUserId));
            st.execute();
            
            int affectedRows = st.get_affected_rows();
            std::cout << "Transferred " << affectedRows << " posts" << std::endl;
            
            // 統計更新（例）
            *sql_ << "UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id IN (:id1, :id2)",
                use(fromUserId), use(toUserId);
            
            tr.commit();
            return true;
            
        } catch (const soci_error& e) {
            std::cerr << "Transaction failed: " << e.what() << std::endl;
            // トランザクションは自動的にロールバックされる
            return false;
        }
    }
    
    // 一括ユーザー作成
    std::vector<int> batchCreateUsers(const std::vector<std::tuple<std::string, std::string, int>>& usersData) {
        std::vector<int> userIds;
        
        try {
            transaction tr(*sql_);
            
            for (const auto& [name, email, age] : usersData) {
                int userId;
                *sql_ << "INSERT INTO users (name, email, age) VALUES (:name, :email, :age) RETURNING id",
                    use(name), use(email), use(age), into(userId);
                userIds.push_back(userId);
            }
            
            tr.commit();
            std::cout << "Batch created " << userIds.size() << " users" << std::endl;
            
        } catch (const soci_error& e) {
            std::cerr << "Batch creation failed: " << e.what() << std::endl;
            userIds.clear();
        }
        
        return userIds;
    }
    
    // 条件付き削除
    int deleteInactiveUsers(int daysSinceLastActivity) {
        try {
            transaction tr(*sql_);
            
            // 非アクティブユーザーの特定
            statement st = (sql_->prepare <<
                "DELETE FROM users WHERE id IN ("
                "SELECT u.id FROM users u "
                "LEFT JOIN posts p ON u.id = p.user_id "
                "WHERE u.created_at < CURRENT_DATE - INTERVAL ':days days' "
                "AND (p.created_at IS NULL OR p.created_at < CURRENT_DATE - INTERVAL ':days days')"
                ")", use(daysSinceLastActivity));
            
            st.execute();
            int deletedCount = st.get_affected_rows();
            
            tr.commit();
            std::cout << "Deleted " << deletedCount << " inactive users" << std::endl;
            return deletedCount;
            
        } catch (const soci_error& e) {
            std::cerr << "Failed to delete inactive users: " << e.what() << std::endl;
            return 0;
        }
    }
    
    // 複雑な統計クエリ
    struct UserEngagementStats {
        std::string user_name;
        int post_count;
        std::string last_post_date;
        double avg_posts_per_month;
    };
    
    std::vector<UserEngagementStats> getUserEngagementStats() {
        try {
            std::vector<UserEngagementStats> stats;
            
            rowset<row> rs = (sql_->prepare <<
                "SELECT "
                "    u.name, "
                "    COUNT(p.id) as post_count, "
                "    MAX(p.created_at)::date as last_post_date, "
                "    COUNT(p.id) / GREATEST(EXTRACT(epoch FROM (CURRENT_DATE - u.created_at::date)) / (30 * 24 * 3600), 1) as avg_posts_per_month "
                "FROM users u "
                "LEFT JOIN posts p ON u.id = p.user_id "
                "GROUP BY u.id, u.name, u.created_at "
                "HAVING COUNT(p.id) > 0 "
                "ORDER BY post_count DESC");
            
            for (const auto& row : rs) {
                UserEngagementStats stat;
                stat.user_name = row.get<std::string>(0);
                stat.post_count = row.get<int>(1);
                
                // NULL チェック
                if (row.get_indicator(2) != i_null) {
                    stat.last_post_date = row.get<std::string>(2);
                } else {
                    stat.last_post_date = "Never";
                }
                
                stat.avg_posts_per_month = row.get<double>(3);
                stats.push_back(stat);
            }
            
            return stats;
            
        } catch (const soci_error& e) {
            std::cerr << "Failed to get engagement stats: " << e.what() << std::endl;
            throw;
        }
    }
};

// 使用例
void demonstrateTransactions() {
    try {
        TransactionManager tm("postgresql://user:password@localhost/testdb");
        
        // 一括ユーザー作成
        std::vector<std::tuple<std::string, std::string, int>> newUsers = {
            {"David Wilson", "[email protected]", 28},
            {"Emma Davis", "[email protected]", 24},
            {"Frank Miller", "[email protected]", 32}
        };
        
        auto userIds = tm.batchCreateUsers(newUsers);
        
        if (!userIds.empty()) {
            std::cout << "Created users with IDs: ";
            for (int id : userIds) {
                std::cout << id << " ";
            }
            std::cout << std::endl;
        }
        
        // エンゲージメント統計
        auto engagementStats = tm.getUserEngagementStats();
        std::cout << "\n=== User Engagement Stats ===" << std::endl;
        for (const auto& stat : engagementStats) {
            std::cout << "User: " << stat.user_name 
                      << ", Posts: " << stat.post_count
                      << ", Last Post: " << stat.last_post_date
                      << ", Avg/Month: " << stat.avg_posts_per_month << std::endl;
        }
        
    } catch (const std::exception& e) {
        std::cerr << "Transaction demo error: " << e.what() << std::endl;
    }
}

プリペアードステートメントとバッチ処理

#include <soci/soci.h>
#include <chrono>
#include <random>

class PerformanceManager {
private:
    std::unique_ptr<session> sql_;
    
public:
    explicit PerformanceManager(const std::string& connection_string) 
        : sql_(std::make_unique<session>(postgresql, connection_string)) {}
    
    // プリペアードステートメントによる高速挿入
    void fastBulkInsert(int userCount) {
        auto start = std::chrono::high_resolution_clock::now();
        
        try {
            transaction tr(*sql_);
            
            // プリペアードステートメント作成
            statement st = (sql_->prepare <<
                "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)");
            
            std::string name, email;
            int age;
            
            // バインド変数設定
            st.exchange(use(name, "name"));
            st.exchange(use(email, "email"));
            st.exchange(use(age, "age"));
            
            // ランダムデータ生成器
            std::random_device rd;
            std::mt19937 gen(rd());
            std::uniform_int_distribution<> ageDist(18, 80);
            
            // バッチ挿入
            for (int i = 0; i < userCount; ++i) {
                name = "User" + std::to_string(i);
                email = "user" + std::to_string(i) + "@example.com";
                age = ageDist(gen);
                
                st.execute();
            }
            
            tr.commit();
            
            auto end = std::chrono::high_resolution_clock::now();
            auto duration = std::chrono::duration_cast<std::chrono::milliseconds>(end - start);
            
            std::cout << "Inserted " << userCount << " users in " 
                      << duration.count() << "ms" << std::endl;
                      
        } catch (const soci_error& e) {
            std::cerr << "Bulk insert failed: " << e.what() << std::endl;
        }
    }
    
    // バッチ更新
    void batchUpdateUsers(const std::vector<std::tuple<int, std::string, int>>& updates) {
        try {
            transaction tr(*sql_);
            
            statement st = (sql_->prepare <<
                "UPDATE users SET name = :name, age = :age WHERE id = :id");
            
            int id, age;
            std::string name;
            
            st.exchange(use(id, "id"));
            st.exchange(use(name, "name"));
            st.exchange(use(age, "age"));
            
            for (const auto& [userId, userName, userAge] : updates) {
                id = userId;
                name = userName;
                age = userAge;
                
                st.execute();
            }
            
            tr.commit();
            std::cout << "Batch updated " << updates.size() << " users" << std::endl;
            
        } catch (const soci_error& e) {
            std::cerr << "Batch update failed: " << e.what() << std::endl;
        }
    }
    
    // 効率的な大量データ取得
    template<typename Callback>
    void processLargeDataset(const std::string& query, Callback callback) {
        try {
            rowset<row> rs = (sql_->prepare << query);
            
            int processedCount = 0;
            const int batchSize = 1000;
            
            for (const auto& row : rs) {
                callback(row);
                processedCount++;
                
                // 進捗表示
                if (processedCount % batchSize == 0) {
                    std::cout << "Processed " << processedCount << " rows..." << std::endl;
                }
            }
            
            std::cout << "Total processed: " << processedCount << " rows" << std::endl;
            
        } catch (const soci_error& e) {
            std::cerr << "Large dataset processing failed: " << e.what() << std::endl;
        }
    }
    
    // インデックス分析
    void analyzeQueryPerformance(const std::string& query) {
        try {
            auto start = std::chrono::high_resolution_clock::now();
            
            // EXPLAIN ANALYZE実行
            rowset<row> rs = (sql_->prepare << ("EXPLAIN ANALYZE " + query));
            
            auto end = std::chrono::high_resolution_clock::now();
            auto duration = std::chrono::duration_cast<std::chrono::microseconds>(end - start);
            
            std::cout << "\n=== Query Performance Analysis ===" << std::endl;
            std::cout << "Query: " << query << std::endl;
            std::cout << "Execution time: " << duration.count() << " microseconds" << std::endl;
            std::cout << "\nExecution plan:" << std::endl;
            
            for (const auto& row : rs) {
                std::cout << row.get<std::string>(0) << std::endl;
            }
            
        } catch (const soci_error& e) {
            std::cerr << "Query analysis failed: " << e.what() << std::endl;
        }
    }
    
    // 接続プール使用例（疑似的な実装）
    class ConnectionPool {
    private:
        std::vector<std::unique_ptr<session>> connections_;
        std::string connection_string_;
        size_t pool_size_;
        size_t current_index_;
        
    public:
        ConnectionPool(const std::string& conn_str, size_t size) 
            : connection_string_(conn_str), pool_size_(size), current_index_(0) {
            
            for (size_t i = 0; i < pool_size_; ++i) {
                connections_.push_back(
                    std::make_unique<session>(postgresql, connection_string_)
                );
            }
        }
        
        session* getConnection() {
            auto* conn = connections_[current_index_].get();
            current_index_ = (current_index_ + 1) % pool_size_;
            return conn;
        }
        
        void executeQuery(const std::string& query) {
            try {
                auto* sql = getConnection();
                *sql << query;
                std::cout << "Executed query using pooled connection" << std::endl;
            } catch (const soci_error& e) {
                std::cerr << "Pooled query failed: " << e.what() << std::endl;
            }
        }
    };
};

// 使用例
void demonstratePerformance() {
    try {
        PerformanceManager pm("postgresql://user:password@localhost/testdb");
        
        // 高速バルク挿入
        std::cout << "=== Fast Bulk Insert Test ===" << std::endl;
        pm.fastBulkInsert(1000);
        
        // バッチ更新
        std::vector<std::tuple<int, std::string, int>> updates = {
            {1, "Updated User 1", 30},
            {2, "Updated User 2", 35},
            {3, "Updated User 3", 40}
        };
        pm.batchUpdateUsers(updates);
        
        // 大量データ処理
        std::cout << "\n=== Large Dataset Processing ===" << std::endl;
        pm.processLargeDataset(
            "SELECT id, name, email FROM users ORDER BY id",
            [](const row& r) {
                // 各行に対する処理
                if (r.get<int>(0) % 100 == 0) {
                    std::cout << "Processing user ID: " << r.get<int>(0) << std::endl;
                }
            }
        );
        
        // クエリパフォーマンス分析
        pm.analyzeQueryPerformance("SELECT * FROM users WHERE age > 25 ORDER BY name");
        
    } catch (const std::exception& e) {
        std::cerr << "Performance demo error: " << e.what() << std::endl;
    }
}

エラーハンドリング

#include <soci/soci.h>
#include <stdexcept>
#include <optional>

// カスタム例外クラス
class DatabaseException : public std::runtime_error {
public:
    explicit DatabaseException(const std::string& message) 
        : std::runtime_error("Database Error: " + message) {}
};

class UserNotFoundException : public DatabaseException {
public:
    explicit UserNotFoundException(int userId) 
        : DatabaseException("User not found with ID: " + std::to_string(userId)) {}
};

class DuplicateEmailException : public DatabaseException {
public:
    explicit DuplicateEmailException(const std::string& email) 
        : DatabaseException("Email already exists: " + email) {}
};

class SafeUserDatabase {
private:
    std::unique_ptr<session> sql_;
    
    // 接続状態確認
    bool isConnectionValid() {
        try {
            int result;
            *sql_ << "SELECT 1", into(result);
            return result == 1;
        } catch (...) {
            return false;
        }
    }
    
    // 再接続試行
    void reconnect(const std::string& connection_string) {
        try {
            sql_ = std::make_unique<session>(postgresql, connection_string);
            std::cout << "Database reconnected successfully" << std::endl;
        } catch (const soci_error& e) {
            throw DatabaseException("Reconnection failed: " + std::string(e.what()));
        }
    }
    
public:
    explicit SafeUserDatabase(const std::string& connection_string) {
        try {
            sql_ = std::make_unique<session>(postgresql, connection_string);
        } catch (const soci_error& e) {
            throw DatabaseException("Initial connection failed: " + std::string(e.what()));
        }
    }
    
    // 安全なユーザー作成
    std::optional<int> createUserSafely(const std::string& name, const std::string& email, int age) {
        // バリデーション
        if (name.empty()) {
            throw std::invalid_argument("Name cannot be empty");
        }
        if (email.find('@') == std::string::npos) {
            throw std::invalid_argument("Invalid email format");
        }
        if (age < 0 || age > 150) {
            throw std::invalid_argument("Invalid age range");
        }
        
        try {
            // 重複チェック
            int existingCount;
            *sql_ << "SELECT COUNT(*) FROM users WHERE email = :email", 
                use(email), into(existingCount);
                
            if (existingCount > 0) {
                throw DuplicateEmailException(email);
            }
            
            // ユーザー作成
            int userId;
            *sql_ << "INSERT INTO users (name, email, age) VALUES (:name, :email, :age) RETURNING id",
                use(name), use(email), use(age), into(userId);
                
            return userId;
            
        } catch (const soci_error& e) {
            throw DatabaseException("User creation failed: " + std::string(e.what()));
        }
    }
    
    // 安全なユーザー取得
    std::optional<User> getUserSafely(int userId) {
        try {
            User user;
            indicator ind;
            
            *sql_ << "SELECT id, name, email, age, created_at FROM users WHERE id = :id",
                into(user, ind), use(userId);
                
            if (ind == i_ok) {
                return user;
            } else {
                return std::nullopt;
            }
            
        } catch (const soci_error& e) {
            throw DatabaseException("User retrieval failed: " + std::string(e.what()));
        }
    }
    
    // 安全なユーザー更新
    bool updateUserSafely(int userId, const std::string& name, const std::string& email, int age) {
        // バリデーション
        if (name.empty()) {
            throw std::invalid_argument("Name cannot be empty");
        }
        if (email.find('@') == std::string::npos) {
            throw std::invalid_argument("Invalid email format");
        }
        
        try {
            transaction tr(*sql_);
            
            // ユーザー存在確認
            int userExists;
            *sql_ << "SELECT COUNT(*) FROM users WHERE id = :id", 
                use(userId), into(userExists);
                
            if (userExists == 0) {
                throw UserNotFoundException(userId);
            }
            
            // メール重複チェック（自分以外）
            int duplicateCount;
            *sql_ << "SELECT COUNT(*) FROM users WHERE email = :email AND id != :id", 
                use(email), use(userId), into(duplicateCount);
                
            if (duplicateCount > 0) {
                throw DuplicateEmailException(email);
            }
            
            // 更新実行
            statement st = (sql_->prepare <<
                "UPDATE users SET name = :name, email = :email, age = :age WHERE id = :id",
                use(name), use(email), use(age), use(userId));
            
            st.execute();
            bool success = st.get_affected_rows() > 0;
            
            if (success) {
                tr.commit();
            }
            
            return success;
            
        } catch (const soci_error& e) {
            throw DatabaseException("User update failed: " + std::string(e.what()));
        }
    }
    
    // 安全なユーザー削除
    bool deleteUserSafely(int userId) {
        try {
            transaction tr(*sql_);
            
            // ユーザー存在確認
            int userExists;
            *sql_ << "SELECT COUNT(*) FROM users WHERE id = :id", 
                use(userId), into(userExists);
                
            if (userExists == 0) {
                throw UserNotFoundException(userId);
            }
            
            // 関連データ確認
            int postCount;
            *sql_ << "SELECT COUNT(*) FROM posts WHERE user_id = :id", 
                use(userId), into(postCount);
                
            if (postCount > 0) {
                std::cout << "User has " << postCount << " posts. Deleting related data..." << std::endl;
                *sql_ << "DELETE FROM posts WHERE user_id = :id", use(userId);
            }
            
            // ユーザー削除
            statement st = (sql_->prepare << "DELETE FROM users WHERE id = :id", use(userId));
            st.execute();
            
            bool success = st.get_affected_rows() > 0;
            
            if (success) {
                tr.commit();
            }
            
            return success;
            
        } catch (const soci_error& e) {
            throw DatabaseException("User deletion failed: " + std::string(e.what()));
        }
    }
    
    // 接続回復機能付きクエリ実行
    template<typename Func>
    auto executeWithRetry(Func&& func, const std::string& connection_string, int maxRetries = 3) -> decltype(func()) {
        int attempts = 0;
        
        while (attempts < maxRetries) {
            try {
                if (!isConnectionValid() && attempts > 0) {
                    reconnect(connection_string);
                }
                
                return func();
                
            } catch (const soci_error& e) {
                attempts++;
                std::cerr << "Attempt " << attempts << " failed: " << e.what() << std::endl;
                
                if (attempts >= maxRetries) {
                    throw DatabaseException("Max retry attempts reached: " + std::string(e.what()));
                }
                
                // 再試行前の待機
                std::this_thread::sleep_for(std::chrono::milliseconds(100 * attempts));
            }
        }
        
        throw DatabaseException("Unexpected error in executeWithRetry");
    }
    
    // 健全性チェック
    struct HealthCheckResult {
        bool connection_ok;
        bool tables_exist;
        int user_count;
        std::string last_error;
    };
    
    HealthCheckResult performHealthCheck() {
        HealthCheckResult result{};
        
        try {
            // 接続チェック
            result.connection_ok = isConnectionValid();
            
            if (result.connection_ok) {
                // テーブル存在確認
                int tableCount;
                *sql_ << "SELECT COUNT(*) FROM information_schema.tables WHERE table_name IN ('users', 'posts')",
                    into(tableCount);
                result.tables_exist = (tableCount == 2);
                
                // ユーザー数取得
                *sql_ << "SELECT COUNT(*) FROM users", into(result.user_count);
            }
            
        } catch (const soci_error& e) {
            result.last_error = e.what();
            result.connection_ok = false;
        }
        
        return result;
    }
};

// エラーハンドリング使用例
void demonstrateErrorHandling() {
    try {
        SafeUserDatabase db("postgresql://user:password@localhost/testdb");
        
        // 健全性チェック
        auto health = db.performHealthCheck();
        std::cout << "=== Health Check ===" << std::endl;
        std::cout << "Connection OK: " << (health.connection_ok ? "Yes" : "No") << std::endl;
        std::cout << "Tables exist: " << (health.tables_exist ? "Yes" : "No") << std::endl;
        std::cout << "User count: " << health.user_count << std::endl;
        
        if (!health.last_error.empty()) {
            std::cout << "Last error: " << health.last_error << std::endl;
        }
        
        // 安全なユーザー操作
        try {
            auto userId = db.createUserSafely("Test User", "[email protected]", 25);
            if (userId) {
                std::cout << "Created user with ID: " << *userId << std::endl;
                
                // 更新テスト
                if (db.updateUserSafely(*userId, "Updated User", "[email protected]", 26)) {
                    std::cout << "User updated successfully" << std::endl;
                }
                
                // 取得テスト
                auto user = db.getUserSafely(*userId);
                if (user) {
                    std::cout << "Retrieved user: " << user->name << std::endl;
                }
            }
            
        } catch (const DuplicateEmailException& e) {
            std::cerr << "Duplicate email error: " << e.what() << std::endl;
        } catch (const UserNotFoundException& e) {
            std::cerr << "User not found error: " << e.what() << std::endl;
        }
        
        // 存在しないユーザーの取得テスト
        auto nonExistentUser = db.getUserSafely(99999);
        if (!nonExistentUser) {
            std::cout << "Non-existent user correctly returned nullopt" << std::endl;
        }
        
    } catch (const DatabaseException& e) {
        std::cerr << "Database error: " << e.what() << std::endl;
    } catch (const std::exception& e) {
        std::cerr << "General error: " << e.what() << std::endl;
    }
}

int main() {
    std::cout << "=== SOCI Database Access Demo ===" << std::endl;
    
    demonstrateBasicOperations();
    demonstrateTransactions();
    demonstratePerformance();
    demonstrateErrorHandling();
    
    return 0;
}