Примеры Базы Данных Windows C++

// Windows C++ SQLite Database Connection Examples

#include <iostream>
#include <sqlite3.h>
#include <string>
#include <fstream>

// 1. Basic SQLite Connection
class SQLiteDatabase {
private:
    sqlite3* db;
    std::string dbPath;

public:
    SQLiteDatabase(const std::string& path) : db(nullptr), dbPath(path) {}

    ~SQLiteDatabase() {
        close();
    }

    // Open database connection
    bool open() {
        int rc = sqlite3_open(dbPath.c_str(), &db);
        if (rc != SQLITE_OK) {
            std::cerr << "Cannot open database: " << sqlite3_errmsg(db) << std::endl;
            return false;
        }
        std::cout << "Database opened successfully: " << dbPath << std::endl;
        return true;
    }

    // Close database connection
    void close() {
        if (db) {
            sqlite3_close(db);
            db = nullptr;
            std::cout << "Database closed" << std::endl;
        }
    }

    // Check if database is open
    bool isOpen() const {
        return db != nullptr;
    }

    // Get database path
    std::string getPath() const {
        return dbPath;
    }

    // Execute simple SQL statement
    bool executeSQL(const std::string& sql) {
        char* errMsg = nullptr;
        int rc = sqlite3_exec(db, sql.c_str(), nullptr, nullptr, &errMsg);
        if (rc != SQLITE_OK) {
            std::cerr << "SQL error: " << errMsg << std::endl;
            sqlite3_free(errMsg);
            return false;
        }
        return true;
    }

    // Get SQLite version
    static std::string getVersion() {
        return sqlite3_libversion();
    }

    // Get last insert row ID
    sqlite3_int64 getLastInsertId() {
        return sqlite3_last_insert_rowid(db);
    }

    // Get number of rows changed
    int getRowsChanged() {
        return sqlite3_changes(db);
    }
};

// 2. Create Database and Tables
bool createDatabaseSchema(SQLiteDatabase& db) {
    std::cout << "\n--- Creating Database Schema ---" << std::endl;

    // Create users table
    std::string createUsersTable = R"(
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            email TEXT NOT NULL,
            age INTEGER,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    )";

    if (!db.executeSQL(createUsersTable)) {
        return false;
    }
    std::cout << "Users table created successfully" << std::endl;

    // Create products table
    std::string createProductsTable = R"(
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            price REAL NOT NULL,
            stock INTEGER DEFAULT 0,
            category TEXT
        )
    )";

    if (!db.executeSQL(createProductsTable)) {
        return false;
    }
    std::cout << "Products table created successfully" << std::endl;

    // Create orders table with foreign key
    std::string createOrdersTable = R"(
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            product_id INTEGER NOT NULL,
            quantity INTEGER NOT NULL,
            total_price REAL NOT NULL,
            order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id),
            FOREIGN KEY (product_id) REFERENCES products(id)
        )
    )";

    if (!db.executeSQL(createOrdersTable)) {
        return false;
    }
    std::cout << "Orders table created successfully" << std::endl;

    return true;
}

// 3. Connection with Options
class SQLiteDatabaseAdvanced : public SQLiteDatabase {
public:
    SQLiteDatabaseAdvanced(const std::string& path) : SQLiteDatabase(path) {}

    // Open with specific options
    bool openWithOptions() {
        // Open in read-write mode, create if not exists
        int rc = sqlite3_open_v2(
            getPath().c_str(),
            &db,
            SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
            nullptr
        );

        if (rc != SQLITE_OK) {
            std::cerr << "Cannot open database with options: " << sqlite3_errmsg(db) << std::endl;
            return false;
        }

        // Enable foreign keys
        executeSQL("PRAGMA foreign_keys = ON");

        // Set busy timeout (5 seconds)
        sqlite3_busy_timeout(db, 5000);

        // Set journal mode to WAL (Write-Ahead Logging)
        executeSQL("PRAGMA journal_mode = WAL");

        // Set synchronous mode to NORMAL (better performance)
        executeSQL("PRAGMA synchronous = NORMAL");

        std::cout << "Database opened with advanced options" << std::endl;
        return true;
    }

    // Get database information
    void displayDatabaseInfo() {
        std::cout << "\n--- Database Information ---" << std::endl;

        // Get page size
        sqlite3_stmt* stmt;
        if (sqlite3_prepare_v2(db, "PRAGMA page_size", -1, &stmt, nullptr) == SQLITE_OK) {
            if (sqlite3_step(stmt) == SQLITE_ROW) {
                std::cout << "Page size: " << sqlite3_column_int(stmt, 0) << " bytes" << std::endl;
            }
            sqlite3_finalize(stmt);
        }

        // Get database size
        if (sqlite3_prepare_v2(db, "PRAGMA page_count", -1, &stmt, nullptr) == SQLITE_OK) {
            if (sqlite3_step(stmt) == SQLITE_ROW) {
                int pageCount = sqlite3_column_int(stmt, 0);
                std::cout << "Page count: " << pageCount << std::endl;
            }
            sqlite3_finalize(stmt);
        }

        // Get journal mode
        if (sqlite3_prepare_v2(db, "PRAGMA journal_mode", -1, &stmt, nullptr) == SQLITE_OK) {
            if (sqlite3_step(stmt) == SQLITE_ROW) {
                const char* mode = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
                std::cout << "Journal mode: " << mode << std::endl;
            }
            sqlite3_finalize(stmt);
        }
    }
};

// 4. In-Memory Database
bool useInMemoryDatabase() {
    std::cout << "\n--- In-Memory Database Example ---" << std::endl;

    SQLiteDatabase db(":memory:");
    if (!db.open()) {
        return false;
    }

    // Create table in memory
    std::string createTable = R"(
        CREATE TABLE temp_data (
            id INTEGER PRIMARY KEY,
            value TEXT
        )
    )";

    if (!db.executeSQL(createTable)) {
        return false;
    }

    // Insert data
    db.executeSQL("INSERT INTO temp_data (value) VALUES ('In-Memory Data 1')");
    db.executeSQL("INSERT INTO temp_data (value) VALUES ('In-Memory Data 2')");

    std::cout << "In-memory database operations completed" << std::endl;
    return true;
}

// 5. Check Database Integrity
bool checkDatabaseIntegrity(SQLiteDatabase& db) {
    std::cout << "\n--- Database Integrity Check ---" << std::endl;

    sqlite3_stmt* stmt;
    std::string sql = "PRAGMA integrity_check";

    if (sqlite3_prepare_v2(db.db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
        std::cerr << "Failed to prepare integrity check" << std::endl;
        return false;
    }

    bool integrityOk = true;
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        const char* result = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
        std::cout << "Integrity check: " << result << std::endl;
        if (std::string(result) != "ok") {
            integrityOk = false;
        }
    }

    sqlite3_finalize(stmt);
    return integrityOk;
}

// 6. Backup Database
bool backupDatabase(const std::string& sourcePath, const std::string& backupPath) {
    std::cout << "\n--- Database Backup ---" << std::endl;

    SQLiteDatabase source(sourcePath);
    SQLiteDatabase backup(backupPath);

    if (!source.open()) {
        return false;
    }

    if (!backup.open()) {
        return false;
    }

    // Use SQLite backup API
    sqlite3_backup* backupHandle = sqlite3_backup_init(
        backup.db, "main", source.db, "main"
    );

    if (!backupHandle) {
        std::cerr << "Failed to initialize backup" << std::endl;
        return false;
    }

    int rc;
    do {
        rc = sqlite3_backup_step(backupHandle, 5); // 5 pages per step
        if (rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED) {
            sqlite3_sleep(100);
        }
    } while (rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED);

    sqlite3_backup_finish(backupHandle);

    if (rc != SQLITE_DONE) {
        std::cerr << "Backup failed: " << rc << std::endl;
        return false;
    }

    std::cout << "Database backed up successfully to: " << backupPath << std::endl;
    return true;
}

// Main demonstration
int main() {
    std::cout << "=== Windows C++ SQLite Database Connection Examples ===" << std::endl;
    std::cout << "SQLite Version: " << SQLiteDatabase::getVersion() << std::endl;

    // 1. Basic connection and schema creation
    std::string dbPath = "test_database.db";
    SQLiteDatabase db(dbPath);

    if (db.open()) {
        createDatabaseSchema(db);
        checkDatabaseIntegrity(db);
    }

    // 2. Advanced connection with options
    std::string advancedDbPath = "advanced_database.db";
    SQLiteDatabaseAdvanced advancedDb(advancedDbPath);

    if (advancedDb.openWithOptions()) {
        createDatabaseSchema(advancedDb);
        advancedDb.displayDatabaseInfo();
    }

    // 3. In-memory database
    useInMemoryDatabase();

    // 4. Backup database
    backupDatabase(dbPath, "backup_database.db");

    // Cleanup
    db.close();
    advancedDb.close();

    std::cout << "\n=== All Database Connection Examples Completed ===" << std::endl;
    return 0;
}

// Windows C++ SQL Query Execution Examples

#include <iostream>
#include <sqlite3.h>
#include <string>
#include <vector>
#include <iomanip>

// 1. INSERT Operation with Parameters
class DatabaseOperations {
private:
    sqlite3* db;

public:
    DatabaseOperations(sqlite3* database) : db(database) {}

    // Insert user with parameter binding (prevents SQL injection)
    bool insertUser(const std::string& username, const std::string& email, int age) {
        sqlite3_stmt* stmt;
        std::string sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            std::cerr << "Failed to prepare statement: " << sqlite3_errmsg(db) << std::endl;
            return false;
        }

        // Bind parameters
        sqlite3_bind_text(stmt, 1, username.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(stmt, 2, email.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(stmt, 3, age);

        // Execute
        int rc = sqlite3_step(stmt);
        sqlite3_finalize(stmt);

        if (rc != SQLITE_DONE) {
            std::cerr << "Failed to insert user: " << sqlite3_errmsg(db) << std::endl;
            return false;
        }

        std::cout << "User inserted: " << username << " (ID: " << sqlite3_last_insert_rowid(db) << ")" << std::endl;
        return true;
    }

    // Batch insert users
    bool insertUsers(const std::vector<std::tuple<std::string, std::string, int>>& users) {
        sqlite3_stmt* stmt;
        std::string sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            return false;
        }

        // Begin transaction for better performance
        sqlite3_exec(db, "BEGIN TRANSACTION", nullptr, nullptr, nullptr);

        for (const auto& user : users) {
            sqlite3_bind_text(stmt, 1, std::get<0>(user).c_str(), -1, SQLITE_TRANSIENT);
            sqlite3_bind_text(stmt, 2, std::get<1>(user).c_str(), -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(stmt, 3, std::get<2>(user));

            if (sqlite3_step(stmt) != SQLITE_DONE) {
                std::cerr << "Failed to insert user in batch" << std::endl;
                sqlite3_finalize(stmt);
                sqlite3_exec(db, "ROLLBACK", nullptr, nullptr, nullptr);
                return false;
            }

            sqlite3_reset(stmt);
        }

        sqlite3_finalize(stmt);
        sqlite3_exec(db, "COMMIT", nullptr, nullptr, nullptr);

        std::cout << "Batch insert completed: " << users.size() << " users" << std::endl;
        return true;
    }

    // 2. SELECT Operation
    struct User {
        int id;
        std::string username;
        std::string email;
        int age;
    };

    std::vector<User> getAllUsers() {
        std::vector<User> users;
        sqlite3_stmt* stmt;
        std::string sql = "SELECT id, username, email, age FROM users ORDER BY id";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            std::cerr << "Failed to prepare SELECT statement" << std::endl;
            return users;
        }

        std::cout << "\n--- All Users ---" << std::endl;
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            User user;
            user.id = sqlite3_column_int(stmt, 0);
            user.username = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
            user.email = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
            user.age = sqlite3_column_int(stmt, 3);

            users.push_back(user);
            std::cout << "ID: " << user.id << ", Username: " << user.username
                      << ", Email: " << user.email << ", Age: " << user.age << std::endl;
        }

        sqlite3_finalize(stmt);
        return users;
    }

    // SELECT with WHERE clause
    User getUserById(int userId) {
        User user{};
        sqlite3_stmt* stmt;
        std::string sql = "SELECT id, username, email, age FROM users WHERE id = ?";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
            sqlite3_bind_int(stmt, 1, userId);

            if (sqlite3_step(stmt) == SQLITE_ROW) {
                user.id = sqlite3_column_int(stmt, 0);
                user.username = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
                user.email = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
                user.age = sqlite3_column_int(stmt, 3);
            }

            sqlite3_finalize(stmt);
        }

        return user;
    }

    // SELECT with LIKE pattern matching
    std::vector<User> searchUsersByUsername(const std::string& pattern) {
        std::vector<User> users;
        sqlite3_stmt* stmt;
        std::string sql = "SELECT id, username, email, age FROM users WHERE username LIKE ?";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
            std::string searchPattern = "%" + pattern + "%";
            sqlite3_bind_text(stmt, 1, searchPattern.c_str(), -1, SQLITE_TRANSIENT);

            std::cout << "\n--- Users matching '" << pattern << "' ---" << std::endl;
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                User user;
                user.id = sqlite3_column_int(stmt, 0);
                user.username = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
                user.email = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
                user.age = sqlite3_column_int(stmt, 3);

                users.push_back(user);
                std::cout << "Found: " << user.username << std::endl;
            }

            sqlite3_finalize(stmt);
        }

        return users;
    }

    // 3. UPDATE Operation
    bool updateUserAge(int userId, int newAge) {
        sqlite3_stmt* stmt;
        std::string sql = "UPDATE users SET age = ? WHERE id = ?";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            return false;
        }

        sqlite3_bind_int(stmt, 1, newAge);
        sqlite3_bind_int(stmt, 2, userId);

        int rc = sqlite3_step(stmt);
        sqlite3_finalize(stmt);

        if (rc != SQLITE_DONE) {
            std::cerr << "Failed to update user age" << std::endl;
            return false;
        }

        int changes = sqlite3_changes(db);
        std::cout << "Updated " << changes << " user(s)" << std::endl;
        return changes > 0;
    }

    // Update multiple fields
    bool updateUserEmail(int userId, const std::string& newEmail) {
        sqlite3_stmt* stmt;
        std::string sql = "UPDATE users SET email = ? WHERE id = ?";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            return false;
        }

        sqlite3_bind_text(stmt, 1, newEmail.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(stmt, 2, userId);

        int rc = sqlite3_step(stmt);
        sqlite3_finalize(stmt);

        return rc == SQLITE_DONE;
    }

    // Conditional update with subquery
    bool updateUsersWithCondition(int minAge, int ageIncrement) {
        sqlite3_stmt* stmt;
        std::string sql = "UPDATE users SET age = age + ? WHERE age < ?";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            return false;
        }

        sqlite3_bind_int(stmt, 1, ageIncrement);
        sqlite3_bind_int(stmt, 2, minAge);

        int rc = sqlite3_step(stmt);
        sqlite3_finalize(stmt);

        std::cout << "Updated users under age " << minAge << std::endl;
        return rc == SQLITE_DONE;
    }

    // 4. DELETE Operation
    bool deleteUserById(int userId) {
        sqlite3_stmt* stmt;
        std::string sql = "DELETE FROM users WHERE id = ?";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            return false;
        }

        sqlite3_bind_int(stmt, 1, userId);

        int rc = sqlite3_step(stmt);
        sqlite3_finalize(stmt);

        if (rc != SQLITE_DONE) {
            std::cerr << "Failed to delete user" << std::endl;
            return false;
        }

        int changes = sqlite3_changes(db);
        std::cout << "Deleted " << changes << " user(s)" << std::endl;
        return changes > 0;
    }

    // Delete with condition
    int deleteUsersOlderThan(int maxAge) {
        sqlite3_stmt* stmt;
        std::string sql = "DELETE FROM users WHERE age > ?";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            return 0;
        }

        sqlite3_bind_int(stmt, 1, maxAge);

        int rc = sqlite3_step(stmt);
        sqlite3_finalize(stmt);

        if (rc == SQLITE_DONE) {
            int changes = sqlite3_changes(db);
            std::cout << "Deleted " << changes << " users older than " << maxAge << std::endl;
            return changes;
        }

        return 0;
    }

    // 5. JOIN Queries
    struct OrderWithUserInfo {
        int orderId;
        std::string username;
        std::string productName;
        int quantity;
        double totalPrice;
    };

    std::vector<OrderWithUserInfo> getOrderDetails() {
        std::vector<OrderWithUserInfo> orders;
        sqlite3_stmt* stmt;
        std::string sql = R"(
            SELECT
                o.id as order_id,
                u.username,
                p.name as product_name,
                o.quantity,
                o.total_price
            FROM orders o
            INNER JOIN users u ON o.user_id = u.id
            INNER JOIN products p ON o.product_id = p.id
            ORDER BY o.order_date DESC
        )";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
            std::cout << "\n--- Order Details (JOIN) ---" << std::endl;
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                OrderWithUserInfo order;
                order.orderId = sqlite3_column_int(stmt, 0);
                order.username = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
                order.productName = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
                order.quantity = sqlite3_column_int(stmt, 3);
                order.totalPrice = sqlite3_column_double(stmt, 4);

                orders.push_back(order);
                std::cout << "Order #" << order.orderId
                          << " | User: " << order.username
                          << " | Product: " << order.productName
                          << " | Qty: " << order.quantity
                          << " | Total: $" << std::fixed << std::setprecision(2) << order.totalPrice
                          << std::endl;
            }

            sqlite3_finalize(stmt);
        }

        return orders;
    }

    // 6. Aggregate Functions
    void displayUserStatistics() {
        sqlite3_stmt* stmt;

        // Count users
        std::string countSql = "SELECT COUNT(*) FROM users";
        if (sqlite3_prepare_v2(db, countSql.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
            if (sqlite3_step(stmt) == SQLITE_ROW) {
                std::cout << "\nTotal users: " << sqlite3_column_int(stmt, 0) << std::endl;
            }
            sqlite3_finalize(stmt);
        }

        // Average age
        std::string avgAgeSql = "SELECT AVG(age) FROM users";
        if (sqlite3_prepare_v2(db, avgAgeSql.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
            if (sqlite3_step(stmt) == SQLITE_ROW) {
                std::cout << "Average age: " << std::fixed << std::setprecision(1)
                          << sqlite3_column_double(stmt, 0) << std::endl;
            }
            sqlite3_finalize(stmt);
        }

        // Users by age group
        std::string ageGroupSql = R"(
            SELECT
                CASE
                    WHEN age < 20 THEN 'Under 20'
                    WHEN age < 30 THEN '20-29'
                    WHEN age < 40 THEN '30-39'
                    WHEN age < 50 THEN '40-49'
                    ELSE '50+'
                END as age_group,
                COUNT(*) as count
            FROM users
            GROUP BY age_group
            ORDER BY age_group
        )";

        std::cout << "\n--- Users by Age Group ---" << std::endl;
        if (sqlite3_prepare_v2(db, ageGroupSql.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                const char* group = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
                int count = sqlite3_column_int(stmt, 1);
                std::cout << group << ": " << count << " users" << std::endl;
            }
            sqlite3_finalize(stmt);
        }
    }

    // 7. LIMIT and OFFSET for pagination
    std::vector<User> getUsersPaginated(int page, int pageSize) {
        std::vector<User> users;
        sqlite3_stmt* stmt;
        std::string sql = "SELECT id, username, email, age FROM users ORDER BY id LIMIT ? OFFSET ?";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
            sqlite3_bind_int(stmt, 1, pageSize);
            sqlite3_bind_int(stmt, 2, (page - 1) * pageSize);

            std::cout << "\n--- Page " << page << " (Size: " << pageSize << ") ---" << std::endl;
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                User user;
                user.id = sqlite3_column_int(stmt, 0);
                user.username = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
                user.email = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 2));
                user.age = sqlite3_column_int(stmt, 3);

                users.push_back(user);
                std::cout << user.username << std::endl;
            }

            sqlite3_finalize(stmt);
        }

        return users;
    }
};

// Main demonstration
int main() {
    std::cout << "=== Windows C++ SQL Query Execution Examples ===" << std::endl;

    sqlite3* db;
    int rc = sqlite3_open("query_examples.db", &db);

    if (rc != SQLITE_OK) {
        std::cerr << "Cannot open database: " << sqlite3_errmsg(db) << std::endl;
        return 1;
    }

    // Create tables
    const char* createUsersTable = R"(
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            email TEXT NOT NULL,
            age INTEGER,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    )";

    const char* createProductsTable = R"(
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            price REAL NOT NULL
        )
    )";

    const char* createOrdersTable = R"(
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            product_id INTEGER NOT NULL,
            quantity INTEGER NOT NULL,
            total_price REAL NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id),
            FOREIGN KEY (product_id) REFERENCES products(id)
        )
    )";

    sqlite3_exec(db, createUsersTable, nullptr, nullptr, nullptr);
    sqlite3_exec(db, createProductsTable, nullptr, nullptr, nullptr);
    sqlite3_exec(db, createOrdersTable, nullptr, nullptr, nullptr);

    DatabaseOperations ops(db);

    // 1. Insert single user
    ops.insertUser("alice", "[email protected]", 28);
    ops.insertUser("bob", "[email protected]", 35);
    ops.insertUser("charlie", "[email protected]", 22);

    // 2. Batch insert
    std::vector<std::tuple<std::string, std::string, int>> users = {
        {"david", "[email protected]", 30},
        {"emma", "[email protected]", 27},
        {"frank", "[email protected]", 45}
    };
    ops.insertUsers(users);

    // 3. SELECT queries
    ops.getAllUsers();

    User user = ops.getUserById(1);
    if (user.id > 0) {
        std::cout << "\nFound user by ID: " << user.username << std::endl;
    }

    ops.searchUsersByUsername("a");

    // 4. UPDATE queries
    ops.updateUserAge(1, 29);
    ops.updateUserEmail(2, "[email protected]");
    ops.updateUsersWithCondition(30, 1);

    // 5. DELETE queries
    ops.deleteUserById(6);
    ops.deleteUsersOlderThan(50);

    // 6. Statistics
    ops.displayUserStatistics();

    // 7. Pagination
    ops.getUsersPaginated(1, 3);
    ops.getUsersPaginated(2, 3);

    sqlite3_close(db);
    std::cout << "\n=== All SQL Query Examples Completed ===" << std::endl;

    return 0;
}

// Windows C++ Transaction Handling Examples

#include <iostream>
#include <sqlite3.h>
#include <string>
#include <vector>
#include <stdexcept>

// 1. Basic Transaction Management
class TransactionManager {
private:
    sqlite3* db;
    bool inTransaction;

public:
    TransactionManager(sqlite3* database) : db(database), inTransaction(false) {}

    // Begin transaction
    bool begin() {
        if (inTransaction) {
            std::cerr << "Already in transaction" << std::endl;
            return false;
        }

        char* errMsg = nullptr;
        int rc = sqlite3_exec(db, "BEGIN TRANSACTION", nullptr, nullptr, &errMsg);

        if (rc != SQLITE_OK) {
            std::cerr << "Failed to begin transaction: " << errMsg << std::endl;
            sqlite3_free(errMsg);
            return false;
        }

        inTransaction = true;
        std::cout << "Transaction started" << std::endl;
        return true;
    }

    // Commit transaction
    bool commit() {
        if (!inTransaction) {
            std::cerr << "No active transaction to commit" << std::endl;
            return false;
        }

        char* errMsg = nullptr;
        int rc = sqlite3_exec(db, "COMMIT", nullptr, nullptr, &errMsg);

        if (rc != SQLITE_OK) {
            std::cerr << "Failed to commit transaction: " << errMsg << std::endl;
            sqlite3_free(errMsg);
            return false;
        }

        inTransaction = false;
        std::cout << "Transaction committed successfully" << std::endl;
        return true;
    }

    // Rollback transaction
    bool rollback() {
        if (!inTransaction) {
            std::cerr << "No active transaction to rollback" << std::endl;
            return false;
        }

        char* errMsg = nullptr;
        int rc = sqlite3_exec(db, "ROLLBACK", nullptr, nullptr, &errMsg);

        if (rc != SQLITE_OK) {
            std::cerr << "Failed to rollback transaction: " << errMsg << std::endl;
            sqlite3_free(errMsg);
            return false;
        }

        inTransaction = false;
        std::cout << "Transaction rolled back" << std::endl;
        return true;
    }

    // Check if in transaction
    bool isActive() const {
        return inTransaction;
    }

    // RAII-style transaction guard
    class TransactionGuard {
    private:
        TransactionManager& manager;
        bool committed;

    public:
        TransactionGuard(TransactionManager& mgr) : manager(mgr), committed(false) {
            manager.begin();
        }

        ~TransactionGuard() {
            if (!committed && manager.isActive()) {
                manager.rollback();
            }
        }

        void commit() {
            if (manager.commit()) {
                committed = true;
            }
        }

        void rollback() {
            manager.rollback();
            committed = true;
        }
    };

    TransactionGuard createGuard() {
        return TransactionGuard(*this);
    }
};

// 2. Bank Transfer Example (Classic Transaction Use Case)
class BankAccount {
private:
    sqlite3* db;

public:
    BankAccount(sqlite3* database) : db(database) {}

    bool createAccountsTable() {
        const char* sql = R"(
            CREATE TABLE IF NOT EXISTS accounts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                account_number TEXT NOT NULL UNIQUE,
                balance REAL NOT NULL DEFAULT 0.0,
                owner_name TEXT NOT NULL
            )
        )";

        char* errMsg = nullptr;
        int rc = sqlite3_exec(db, sql, nullptr, nullptr, &errMsg);

        if (rc != SQLITE_OK) {
            std::cerr << "Failed to create accounts table: " << errMsg << std::endl;
            sqlite3_free(errMsg);
            return false;
        }

        return true;
    }

    bool createAccount(const std::string& accountNum, double balance, const std::string& owner) {
        sqlite3_stmt* stmt;
        std::string sql = "INSERT INTO accounts (account_number, balance, owner_name) VALUES (?, ?, ?)";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            return false;
        }

        sqlite3_bind_text(stmt, 1, accountNum.c_str(), -1, SQLITE_TRANSIENT);
        sqlite3_bind_double(stmt, 2, balance);
        sqlite3_bind_text(stmt, 3, owner.c_str(), -1, SQLITE_TRANSIENT);

        int rc = sqlite3_step(stmt);
        sqlite3_finalize(stmt);

        return rc == SQLITE_DONE;
    }

    // Transfer funds with transaction
    bool transferFunds(const std::string& fromAccount, const std::string& toAccount, double amount) {
        if (amount <= 0) {
            std::cerr << "Transfer amount must be positive" << std::endl;
            return false;
        }

        TransactionManager transaction(db);
        auto guard = transaction.createGuard();

        std::cout << "\n--- Transferring $" << amount << " from " << fromAccount
                  << " to " << toAccount << " ---" << std::endl;

        // Check sender's balance
        sqlite3_stmt* stmt;
        std::string checkSql = "SELECT balance FROM accounts WHERE account_number = ?";

        if (sqlite3_prepare_v2(db, checkSql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            std::cerr << "Failed to prepare balance check" << std::endl;
            return false;
        }

        sqlite3_bind_text(stmt, 1, fromAccount.c_str(), -1, SQLITE_TRANSIENT);

        if (sqlite3_step(stmt) != SQLITE_ROW) {
            std::cerr << "Sender account not found" << std::endl;
            sqlite3_finalize(stmt);
            return false;
        }

        double senderBalance = sqlite3_column_double(stmt, 0);
        sqlite3_finalize(stmt);

        if (senderBalance < amount) {
            std::cerr << "Insufficient funds. Balance: $" << senderBalance << std::endl;
            return false;
        }

        // Deduct from sender
        std::string deductSql = "UPDATE accounts SET balance = balance - ? WHERE account_number = ?";
        if (sqlite3_prepare_v2(db, deductSql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            return false;
        }

        sqlite3_bind_double(stmt, 1, amount);
        sqlite3_bind_text(stmt, 2, fromAccount.c_str(), -1, SQLITE_TRANSIENT);

        if (sqlite3_step(stmt) != SQLITE_DONE) {
            std::cerr << "Failed to deduct from sender" << std::endl;
            sqlite3_finalize(stmt);
            return false;
        }

        int changes = sqlite3_changes(db);
        sqlite3_finalize(stmt);

        if (changes != 1) {
            std::cerr << "Failed to update sender account" << std::endl;
            return false;
        }

        std::cout << "Deducted $" << amount << " from " << fromAccount << std::endl;

        // Add to receiver
        std::string addSql = "UPDATE accounts SET balance = balance + ? WHERE account_number = ?";
        if (sqlite3_prepare_v2(db, addSql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            return false;
        }

        sqlite3_bind_double(stmt, 1, amount);
        sqlite3_bind_text(stmt, 2, toAccount.c_str(), -1, SQLITE_TRANSIENT);

        if (sqlite3_step(stmt) != SQLITE_DONE) {
            std::cerr << "Failed to add to receiver" << std::endl;
            sqlite3_finalize(stmt);
            return false;
        }

        changes = sqlite3_changes(db);
        sqlite3_finalize(stmt);

        if (changes != 1) {
            std::cerr << "Failed to update receiver account" << std::endl;
            return false;
        }

        std::cout << "Added $" << amount << " to " << toAccount << std::endl;

        // Commit transaction
        guard.commit();
        std::cout << "Transfer completed successfully!" << std::endl;

        return true;
    }

    void displayAllAccounts() {
        sqlite3_stmt* stmt;
        std::string sql = "SELECT account_number, owner_name, balance FROM accounts ORDER BY account_number";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) == SQLITE_OK) {
            std::cout << "\n--- All Accounts ---" << std::endl;
            while (sqlite3_step(stmt) == SQLITE_ROW) {
                const char* accNum = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0));
                const char* owner = reinterpret_cast<const char*>(sqlite3_column_text(stmt, 1));
                double balance = sqlite3_column_double(stmt, 2);

                std::cout << accNum << " - " << owner << ": $" << balance << std::endl;
            }
            sqlite3_finalize(stmt);
        }
    }
};

// 3. Savepoint Management (Nested Transactions)
class SavepointManager {
private:
    sqlite3* db;

public:
    SavepointManager(sqlite3* database) : db(database) {}

    bool createSavepoint(const std::string& name) {
        std::string sql = "SAVEPOINT " + name;
        char* errMsg = nullptr;

        int rc = sqlite3_exec(db, sql.c_str(), nullptr, nullptr, &errMsg);
        if (rc != SQLITE_OK) {
            std::cerr << "Failed to create savepoint: " << errMsg << std::endl;
            sqlite3_free(errMsg);
            return false;
        }

        std::cout << "Savepoint '" << name << "' created" << std::endl;
        return true;
    }

    bool releaseSavepoint(const std::string& name) {
        std::string sql = "RELEASE SAVEPOINT " + name;
        char* errMsg = nullptr;

        int rc = sqlite3_exec(db, sql.c_str(), nullptr, nullptr, &errMsg);
        if (rc != SQLITE_OK) {
            std::cerr << "Failed to release savepoint: " << errMsg << std::endl;
            sqlite3_free(errMsg);
            return false;
        }

        std::cout << "Savepoint '" << name << "' released" << std::endl;
        return true;
    }

    bool rollbackToSavepoint(const std::string& name) {
        std::string sql = "ROLLBACK TO SAVEPOINT " + name;
        char* errMsg = nullptr;

        int rc = sqlite3_exec(db, sql.c_str(), nullptr, nullptr, &errMsg);
        if (rc != SQLITE_OK) {
            std::cerr << "Failed to rollback to savepoint: " << errMsg << std::endl;
            sqlite3_free(errMsg);
            return false;
        }

        std::cout << "Rolled back to savepoint '" << name << "'" << std::endl;
        return true;
    }
};

// 4. Batch Operations with Transaction
class BatchProcessor {
private:
    sqlite3* db;

public:
    BatchProcessor(sqlite3* database) : db(database) {}

    bool insertBatchWithTransaction(const std::vector<std::string>& names) {
        TransactionManager transaction(db);
        auto guard = transaction.createGuard();

        std::cout << "\n--- Batch Insert with Transaction ---" << std::endl;
        std::cout << "Inserting " << names.size() << " records..." << std::endl;

        sqlite3_stmt* stmt;
        std::string sql = "INSERT INTO batch_items (name, status) VALUES (?, 'pending')";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            std::cerr << "Failed to prepare insert statement" << std::endl;
            return false;
        }

        int successCount = 0;
        for (const auto& name : names) {
            sqlite3_bind_text(stmt, 1, name.c_str(), -1, SQLITE_TRANSIENT);

            int rc = sqlite3_step(stmt);
            if (rc == SQLITE_DONE) {
                successCount++;
            } else {
                std::cerr << "Failed to insert: " << name << std::endl;
                sqlite3_finalize(stmt);
                return false;
            }

            sqlite3_reset(stmt);
        }

        sqlite3_finalize(stmt);

        std::cout << "Successfully inserted " << successCount << " records" << std::endl;
        guard.commit();

        return true;
    }

    bool updateBatchStatus(const std::string& newStatus) {
        TransactionManager transaction(db);
        auto guard = transaction.createGuard();

        sqlite3_stmt* stmt;
        std::string sql = "UPDATE batch_items SET status = ? WHERE status = 'pending'";

        if (sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr) != SQLITE_OK) {
            return false;
        }

        sqlite3_bind_text(stmt, 1, newStatus.c_str(), -1, SQLITE_TRANSIENT);

        int rc = sqlite3_step(stmt);
        sqlite3_finalize(stmt);

        if (rc == SQLITE_DONE) {
            int changes = sqlite3_changes(db);
            std::cout << "Updated " << changes << " records to '" << newStatus << "'" << std::endl;
            guard.commit();
            return true;
        }

        return false;
    }
};

// 5. Transaction Isolation Levels
class IsolationLevelDemo {
private:
    sqlite3* db;

public:
    IsolationLevelDemo(sqlite3* database) : db(database) {}

    void setReadUncommitted(bool enabled) {
        std::string sql = enabled ? "PRAGMA read_uncommitted = true" : "PRAGMA read_uncommitted = false";
        sqlite3_exec(db, sql.c_str(), nullptr, nullptr, nullptr);
        std::cout << "Read uncommitted: " << (enabled ? "enabled" : "disabled") << std::endl;
    }

    void setLockingMode(const std::string& mode) {
        std::string sql = "PRAGMA locking_mode = " + mode;
        sqlite3_exec(db, sql.c_str(), nullptr, nullptr, nullptr);
        std::cout << "Locking mode: " << mode << std::endl;
    }

    void setSynchronousMode(const std::string& mode) {
        std::string sql = "PRAGMA synchronous = " + mode;
        sqlite3_exec(db, sql.c_str(), nullptr, nullptr, nullptr);
        std::cout << "Synchronous mode: " << mode << std::endl;
    }
};

// 6. Error Recovery with Transaction
class ErrorRecoveryDemo {
private:
    sqlite3* db;

public:
    ErrorRecoveryDemo(sqlite3* database) : db(database) {}

    bool safeTransfer(const std::string& from, const std::string& to, double amount) {
        std::cout << "\n--- Safe Transfer with Error Recovery ---" << std::endl;

        try {
            TransactionManager transaction(db);
            auto guard = transaction.createGuard();

            // Simulate potential error condition
            if (amount < 0) {
                throw std::runtime_error("Negative amount not allowed");
            }

            // Perform operations (simplified)
            char* errMsg = nullptr;
            std::string sql = "UPDATE accounts SET balance = balance - " + std::to_string(amount) +
                            " WHERE account_number = '" + from + "'";

            if (sqlite3_exec(db, sql.c_str(), nullptr, nullptr, &errMsg) != SQLITE_OK) {
                std::string error = errMsg;
                sqlite3_free(errMsg);
                throw std::runtime_error("Failed to deduct: " + error);
            }

            sql = "UPDATE accounts SET balance = balance + " + std::to_string(amount) +
                  " WHERE account_number = '" + to + "'";

            if (sqlite3_exec(db, sql.c_str(), nullptr, nullptr, &errMsg) != SQLITE_OK) {
                std::string error = errMsg;
                sqlite3_free(errMsg);
                throw std::runtime_error("Failed to add: " + error);
            }

            guard.commit();
            std::cout << "Transfer completed successfully" << std::endl;
            return true;

        } catch (const std::exception& e) {
            std::cerr << "Error: " << e.what() << " - Transaction rolled back" << std::endl;
            return false;
        }
    }
};

// Main demonstration
int main() {
    std::cout << "=== Windows C++ Transaction Handling Examples ===" << std::endl;

    sqlite3* db;
    int rc = sqlite3_open("transaction_examples.db", &db);

    if (rc != SQLITE_OK) {
        std::cerr << "Cannot open database: " << sqlite3_errmsg(db) << std::endl;
        return 1;
    }

    // 1. Basic transaction management
    std::cout << "\n### 1. Basic Transaction Management ###";
    TransactionManager transaction(db);
    transaction.begin();
    transaction.commit();

    // 2. Bank transfer example
    std::cout << "\n### 2. Bank Transfer Example ###";
    BankAccount bank(db);
    bank.createAccountsTable();

    // Create test accounts
    bank.createAccount("ACC001", 1000.0, "Alice");
    bank.createAccount("ACC002", 500.0, "Bob");
    bank.createAccount("ACC003", 250.0, "Charlie");

    bank.displayAllAccounts();

    // Successful transfer
    bank.transferFunds("ACC001", "ACC002", 200.0);
    bank.displayAllAccounts();

    // Failed transfer (insufficient funds)
    bank.transferFunds("ACC003", "ACC001", 500.0);
    bank.displayAllAccounts();

    // 3. Savepoint management
    std::cout << "\n### 3. Savepoint Management ###";
    SavepointManager savepoint(db);

    sqlite3_exec(db, "BEGIN", nullptr, nullptr, nullptr);
    sqlite3_exec(db, "INSERT INTO accounts (account_number, balance, owner_name) VALUES ('ACC004', 100, 'David')", nullptr, nullptr, nullptr);
    savepoint.createSavepoint("after_acc004");

    sqlite3_exec(db, "INSERT INTO accounts (account_number, balance, owner_name) VALUES ('ACC005', 200, 'Emma')", nullptr, nullptr, nullptr);
    savepoint.createSavepoint("after_acc005");

    // Rollback to first savepoint
    savepoint.rollbackToSavepoint("after_acc004");
    savepoint.releaseSavepoint("after_acc004");

    sqlite3_exec(db, "COMMIT", nullptr, nullptr, nullptr);

    // 4. Batch operations
    std::cout << "\n### 4. Batch Operations ###";
    const char* createBatchTable = R"(
        CREATE TABLE IF NOT EXISTS batch_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            status TEXT NOT NULL
        )
    )";
    sqlite3_exec(db, createBatchTable, nullptr, nullptr, nullptr);

    BatchProcessor batch(db);
    std::vector<std::string> items = {"Item1", "Item2", "Item3", "Item4", "Item5"};
    batch.insertBatchWithTransaction(items);
    batch.updateBatchStatus("completed");

    // 5. Isolation levels
    std::cout << "\n### 5. Isolation Levels ###";
    IsolationLevelDemo isolation(db);
    isolation.setReadUncommitted(true);
    isolation.setLockingMode("NORMAL");
    isolation.setSynchronousMode("NORMAL");

    // 6. Error recovery
    std::cout << "\n### 6. Error Recovery ###";
    ErrorRecoveryDemo recovery(db);
    recovery.safeTransfer("ACC001", "ACC002", 50.0);
    recovery.safeTransfer("ACC002", "ACC001", -100.0); // Will fail

    sqlite3_close(db);
    std::cout << "\n=== All Transaction Examples Completed ===" << std::endl;

    return 0;
}