Exemples de Base de Données macOS Swift

// macOS Swift SQLite Connection Examples
// Using SQLite3 C API and wrapper patterns

import Foundation
import SQLite3

// 1. Basic Database Connection
class BasicConnection {

    var db: OpaquePointer?

    func openDatabase(path: String) -> Bool {
        print("\n--- Opening Database ---")

        // Open database
        let result = sqlite3_open(path, &db)

        if result == SQLITE_OK {
            print("Database opened successfully at: \(path)")

            // Get SQLite version
            if let version = sqlite3_libversion() {
                let versionString = String(cString: version)
                print("SQLite version: \(versionString)")
            }

            return true
        } else {
            let error = String(cString: sqlite3_errmsg(db))
            print("Error opening database: \(error)")
            return false
        }
    }

    func closeDatabase() {
        print("\n--- Closing Database ---")

        if db != nil {
            sqlite3_close(db)
            db = nil
            print("Database closed")
        }
    }

    deinit {
        closeDatabase()
    }
}

// 2. Create In-Memory Database
class InMemoryDatabase {

    var db: OpaquePointer?

    func createInMemoryDatabase() -> Bool {
        print("\n--- In-Memory Database ---")

        let result = sqlite3_open_v2(":memory:", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nil)

        if result == SQLITE_OK {
            print("In-memory database created successfully")
            return true
        } else {
            let error = String(cString: sqlite3_errmsg(db))
            print("Error creating in-memory database: \(error)")
            return false
        }
    }

    func getDatabaseInfo() {
        guard let db = db else { return }

        // Get database status
        var curPage: Int32 = 0, pageSizeResult: Int32 = 0
        sqlite3_db_status(db, SQLITE_DBSTATUS_CACHE_USED, &curPage, &pageSizeResult, 0)

        print("Database info:")
        print("  Current page cache: \(curPage)")

        // Get page size via PRAGMA
        var statement: OpaquePointer?
        if sqlite3_prepare_v2(db, "PRAGMA page_size;", -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) == SQLITE_ROW {
                let pageSize = sqlite3_column_int(statement, 0)
                print("  Page size: \(pageSize) bytes")
            }
            sqlite3_finalize(statement)
        }
    }
}

// 3. Connection with Configuration
class ConfiguredConnection {

    var db: OpaquePointer?

    func openWithConfiguration(path: String) -> Bool {
        print("\n--- Opening with Configuration ---")

        // Open with flags
        let flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX
        let result = sqlite3_open_v2(path, &db, flags, nil)

        guard result == SQLITE_OK else {
            let error = String(cString: sqlite3_errmsg(db))
            print("Error opening database: \(error)")
            return false
        }

        print("Database opened with flags")

        // Configure busy timeout (in milliseconds)
        let timeout = 5000 // 5 seconds
        sqlite3_busy_timeout(db, Int32(timeout))
        print("Busy timeout set to \(timeout)ms")

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

        // Set journal mode
        executeSQL("PRAGMA journal_mode = WAL;")
        print("Journal mode set to WAL")

        // Set synchronous mode
        executeSQL("PRAGMA synchronous = NORMAL;")
        print("Synchronous mode set to NORMAL")

        // Set cache size (-2000 = 2MB)
        executeSQL("PRAGMA cache_size = -2000;")
        print("Cache size set to 2MB")

        // Set temp store to memory
        executeSQL("PRAGMA temp_store = MEMORY;")
        print("Temp store set to MEMORY")

        return true
    }

    private func executeSQL(_ sql: String) {
        sqlite3_exec(db, sql, nil, nil, nil)
    }
}

// 4. Connection Pool
class SQLiteConnectionPool {

    private var connections: [OpaquePointer] = []
    private var maxConnections: Int
    private var available: [OpaquePointer] = []
    private let lock = NSLock()

    init(databasePath: String, maxConnections: Int = 5) {
        self.maxConnections = maxConnections

        // Create connections
        for _ in 0..<maxConnections {
            var db: OpaquePointer?
            if sqlite3_open(databasePath, &db) == SQLITE_OK {
                connections.append(db!)
                available.append(db!)
            }
        }

        print("Created connection pool with \(connections.count) connections")
    }

    func getConnection() -> OpaquePointer? {
        lock.lock()
        defer { lock.unlock() }

        if let connection = available.first {
            available.removeFirst()
            return connection
        }

        print("No available connections")
        return nil
    }

    func returnConnection(_ connection: OpaquePointer) {
        lock.lock()
        defer { lock.unlock() }

        available.append(connection)
    }

    func closeAll() {
        for connection in connections {
            sqlite3_close(connection)
        }
        connections.removeAll()
        available.removeAll()
        print("All connections closed")
    }
}

// 5. Database Information
class DatabaseInfo {

    static func getDatabaseInfo(_ db: OpaquePointer?) {
        print("\n--- Database Information ---")

        guard let db = db else {
            print("No database connection")
            return
        }

        // Get filename
        var filename: UnsafeMutablePointer<Int8>?
        sqlite3_file_control(db, "main", SQLITE_FCNTL_FILENAME_POINTER, &filename)
        if let name = filename {
            print("Database file: \(String(cString: name))")
        }

        // Check if read-only
        let isReadOnly = sqlite3_db_readonly(db, "main") == 1
        print("Read-only status: \(isReadOnly)")

        // Get page count
        var statement: OpaquePointer?
        if sqlite3_prepare_v2(db, "PRAGMA page_count;", -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) == SQLITE_ROW {
                let pageCount = sqlite3_column_int64(statement, 0)
                print("Page count: \(pageCount)")
            }
            sqlite3_finalize(statement)
        }

        // Get page size
        if sqlite3_prepare_v2(db, "PRAGMA page_size;", -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) == SQLITE_ROW {
                let pageSize = sqlite3_column_int(statement, 0)
                print("Page size: \(pageSize) bytes")
            }
            sqlite3_finalize(statement)
        }

        // Get encoding
        if sqlite3_prepare_v2(db, "PRAGMA encoding;", -1, &statement, nil) == SQLITE_OK {
            if sqlite3_step(statement) == SQLITE_ROW {
                let encoding = sqlite3_column_text(statement, 0)
                if let enc = encoding {
                    print("Encoding: \(String(cString: enc))")
                }
            }
            sqlite3_finalize(statement)
        }

        // Get list of tables
        print("\nTables:")
        if sqlite3_prepare_v2(db, "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;", -1, &statement, nil) == SQLITE_OK {
            while sqlite3_step(statement) == SQLITE_ROW {
                let tableName = sqlite3_column_text(statement, 0)
                if let name = tableName {
                    print("  - \(String(cString: name))")
                }
            }
            sqlite3_finalize(statement)
        }
    }
}

// 6. Database File Management
class DatabaseFileManager {

    static func databaseExists(at path: String) -> Bool {
        return FileManager.default.fileExists(atPath: path)
    }

    static func deleteDatabase(at path: String) throws {
        if databaseExists(at: path) {
            try FileManager.default.removeItem(atPath: path)
            print("Database deleted at: \(path)")
        }

        // Delete associated files
        let pathsToDelete = [
            path + "-wal",
            path + "-shm",
            path + "-journal"
        ]

        for walPath in pathsToDelete {
            if FileManager.default.fileExists(atPath: walPath) {
                try FileManager.default.removeItem(atPath: walPath)
                print("Deleted associated file: \(walPath)")
            }
        }
    }

    static func getDatabaseSize(at path: String) -> UInt64? {
        guard let attributes = try? FileManager.default.attributesOfItem(atPath: path),
              let fileSize = attributes[.size] as? UInt64 else {
            return nil
        }

        return fileSize
    }

    static func copyDatabase(from source: String, to destination: String) throws {
        try FileManager.default.copyItem(atPath: source, toPath: destination)
        print("Database copied from \(source) to \(destination)")
    }

    static func backupDatabase(from source: String, to backupPath: String) -> Bool {
        print("\n--- Database Backup ---")

        var sourceDB: OpaquePointer?
        var backupDB: OpaquePointer?

        // Open source database
        guard sqlite3_open(source, &sourceDB) == SQLITE_OK else {
            print("Failed to open source database")
            return false
        }

        // Open backup database
        guard sqlite3_open(backupPath, &backupDB) == SQLITE_OK else {
            print("Failed to open backup database")
            sqlite3_close(sourceDB)
            return false
        }

        // Perform backup
        var backup = sqlite3_backup_init(backupDB, "main", sourceDB, "main")

        if backup == nil {
            print("Failed to initialize backup")
            sqlite3_close(sourceDB)
            sqlite3_close(backupDB)
            return false
        }

        // Copy remaining pages
        while sqlite3_backup_step(backup, -1) == SQLITE_OK {
            // Continue stepping until done
        }

        sqlite3_backup_finish(backup)
        sqlite3_close(sourceDB)
        sqlite3_close(backupDB)

        print("Backup completed to: \(backupPath)")
        return true
    }
}

// 7. Secure Database
class SecureDatabase {

    var db: OpaquePointer?

    func openEncryptedDatabase(path: String, password: String) -> Bool {
        print("\n--- Opening Encrypted Database ---")

        // Open database with key
        let result = sqlite3_open_v2(path, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nil)

        guard result == SQLITE_OK else {
            print("Failed to open database")
            return false
        }

        // Set key for encrypted database
        let keyResult = sqlite3_key(db, password, Int32(password.count))

        if keyResult == SQLITE_OK {
            // Try to read from database to verify key
            var statement: OpaquePointer?
            let verifyResult = sqlite3_prepare_v2(db, "SELECT count(*) FROM sqlite_master;", -1, &statement, nil)

            if verifyResult == SQLITE_OK {
                sqlite3_finalize(statement)
                print("Encrypted database opened successfully")
                return true
            } else {
                print("Invalid password or corrupted database")
                sqlite3_close(db)
                db = nil
                return false
            }
        }

        return false
    }

    func changePassword(from oldPassword: String, to newPassword: String) -> Bool {
        print("\n--- Changing Database Password ---")

        // Verify old password
        sqlite3_key(db, oldPassword, Int32(oldPassword.count))

        var statement: OpaquePointer?
        if sqlite3_prepare_v2(db, "SELECT count(*) FROM sqlite_master;", -1, &statement, nil) != SQLITE_OK {
            print("Old password is incorrect")
            return false
        }
        sqlite3_finalize(statement)

        // Change password using rekey
        let result = sqlite3_rekey(db, newPassword, Int32(newPassword.count))

        if result == SQLITE_OK {
            print("Password changed successfully")
            return true
        } else {
            print("Failed to change password")
            return false
        }
    }
}

// 8. Connection Health Check
class ConnectionHealthCheck {

    static func checkConnection(_ db: OpaquePointer?) -> Bool {
        guard let db = db else {
            return false
        }

        // Try to execute a simple query
        var statement: OpaquePointer?
        let result = sqlite3_prepare_v2(db, "SELECT 1;", -1, &statement, nil)

        if result == SQLITE_OK {
            sqlite3_finalize(statement)
            return true
        }

        return false
    }

    static func isDatabaseLocked(_ db: OpaquePointer?) -> Bool {
        guard let db = db else { return false }

        var statement: OpaquePointer?
        let result = sqlite3_prepare_v2(db, "BEGIN IMMEDIATE;", -1, &statement, nil)

        if result == SQLITE_BUSY {
            sqlite3_finalize(statement)
            return true
        }

        if result == SQLITE_OK {
            sqlite3_finalize(statement)
            sqlite3_exec(db, "ROLLBACK;", nil, nil, nil)
        }

        return false
    }

    static func getLastError(_ db: OpaquePointer?) -> String {
        guard let db = db else {
            return "No database connection"
        }

        let error = sqlite3_errmsg(db)
        return String(cString: error)
    }

    static func getExtendedErrorCode(_ db: OpaquePointer?) -> Int32 {
        guard let db = db else { return 0 }

        return sqlite3_extended_errcode(db)
    }
}

// 9. Prepared Statement Cache
class StatementCache {

    private var cache: [String: OpaquePointer] = [:]
    private let db: OpaquePointer?
    private let lock = NSLock()

    init(db: OpaquePointer?) {
        self.db = db
    }

    func getStatement(for sql: String) -> OpaquePointer? {
        lock.lock()
        defer { lock.unlock() }

        // Return cached statement if available
        if let statement = cache[sql] {
            sqlite3_reset(statement)
            sqlite3_clear_bindings(statement)
            return statement
        }

        // Prepare new statement
        var statement: OpaquePointer?
        let result = sqlite3_prepare_v2(db, sql, -1, &statement, nil)

        if result == SQLITE_OK {
            cache[sql] = statement
            return statement
        }

        return nil
    }

    func clearCache() {
        lock.lock()
        defer { lock.unlock() }

        for (_, statement) in cache {
            sqlite3_finalize(statement)
        }
        cache.removeAll()
        print("Statement cache cleared")
    }
}

// 10. Database URI Support
class URIConnection {

    func openWithURI(uri: String) -> OpaquePointer? {
        print("\n--- Opening Database with URI ---")

        var db: OpaquePointer?
        let flags = SQLITE_OPEN_READONLY | SQLITE_OPEN_URI

        let result = sqlite3_open_v2(uri, &db, flags, nil)

        if result == SQLITE_OK {
            print("Database opened with URI: \(uri)")
            return db
        } else {
            let error = String(cString: sqlite3_errmsg(db))
            print("Error opening with URI: \(error)")
            return nil
        }
    }

    func openWithParameters(path: String, readOnly: Bool = false, mode: String = "rwc") -> OpaquePointer? {
        var components = URLComponents()
        components.scheme = "file"
        components.path = path

        var queryItems = [
            URLQueryItem(name: "mode", value: mode)
        ]

        if readOnly {
            queryItems.append(URLQueryItem(name: "readonly", value: "1"))
        }

        components.queryItems = queryItems

        if let uri = components.url?.absoluteString {
            return openWithURI(uri: uri)
        }

        return nil
    }
}

// Main demonstration
func demonstrateSQLiteConnection() {
    print("=== macOS Swift SQLite Connection Examples ===")

    let dbPath = "/tmp/test_database.db"

    // Remove existing database
    try? DatabaseFileManager.deleteDatabase(at: dbPath)

    // 1. Basic connection
    let basicConn = BasicConnection()
    if basicConn.openDatabase(path: dbPath) {
        DatabaseInfo.getDatabaseInfo(basicConn.db)
    }
    basicConn.closeDatabase()

    // 2. In-memory database
    let memDB = InMemoryDatabase()
    if memDB.createInMemoryDatabase() {
        memDB.getDatabaseInfo()
    }

    // 3. Configured connection
    let configuredConn = ConfiguredConnection()
    if configuredConn.openWithConfiguration(path: dbPath) {
        DatabaseInfo.getDatabaseInfo(configuredConn.db)
    }

    // 4. Connection pool
    let pool = SQLiteConnectionPool(databasePath: dbPath, maxConnections: 3)
    if let conn1 = pool.getConnection() {
        print("Got connection 1")
        pool.returnConnection(conn1)
    }

    // 5. Health check
    if let conn2 = pool.getConnection() {
        print("\nConnection healthy: \(ConnectionHealthCheck.checkConnection(conn2))")
        pool.returnConnection(conn2)
    }

    pool.closeAll()

    // 6. Database size
    if let size = DatabaseFileManager.getDatabaseSize(at: dbPath) {
        print("\nDatabase size: \(size) bytes")
    }

    print("\n=== All SQLite Connection Examples Completed ===")
}

// Run demonstration
demonstrateSQLiteConnection()

// macOS Swift SQL Query Execution Examples
// Using SQLite3 C API

import Foundation
import SQLite3

// 1. Execute SQL Statements
class ExecuteSQL {

    func executeStatement(_ db: OpaquePointer?, sql: String) -> Bool {
        print("\n--- Executing SQL ---")
        print("SQL: \(sql)")

        var errMsg: UnsafeMutablePointer<Int8>?

        let result = sqlite3_exec(db, sql, nil, nil, &errMsg)

        if result == SQLITE_OK {
            print("Statement executed successfully")
            return true
        } else {
            if let error = errMsg {
                print("Error: \(String(cString: error))")
                sqlite3_free(errMsg)
            }
            return false
        }
    }

    func executeMultipleStatements(_ db: OpaquePointer?, sql: String) {
        print("\n--- Executing Multiple Statements ---")

        var errMsg: UnsafeMutablePointer<Int8>?
        let result = sqlite3_exec(db, sql, nil, nil, &errMsg)

        if result != SQLITE_OK {
            if let error = errMsg {
                print("Error: \(String(cString: error))")
                sqlite3_free(errMsg)
            }
        } else {
            print("All statements executed successfully")
        }
    }
}

// 2. Create Table
class TableOperations {

    func createUsersTable(_ db: OpaquePointer?) -> Bool {
        let sql = """
        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,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        );
        """

        print("\n--- Creating Users Table ---")
        var statement: OpaquePointer?
        let result = sqlite3_prepare_v2(db, sql, -1, &statement, nil)

        if result == SQLITE_OK {
            if sqlite3_step(statement) == SQLITE_DONE {
                print("Users table created successfully")
                sqlite3_finalize(statement)
                return true
            }
        }

        sqlite3_finalize(statement)
        return false
    }

    func createOrdersTable(_ db: OpaquePointer?) -> Bool {
        let sql = """
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            product_name TEXT NOT NULL,
            quantity INTEGER DEFAULT 1,
            price REAL NOT NULL,
            order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );
        """

        print("\n--- Creating Orders Table ---")
        var statement: OpaquePointer?
        let result = sqlite3_prepare_v2(db, sql, -1, &statement, nil)

        if result == SQLITE_OK {
            if sqlite3_step(statement) == SQLITE_DONE {
                print("Orders table created successfully")
                sqlite3_finalize(statement)
                return true
            }
        }

        sqlite3_finalize(statement)
        return false
    }

    func createIndex(_ db: OpaquePointer?) -> Bool {
        let sql = "CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);"

        print("\n--- Creating Index ---")
        var statement: OpaquePointer?
        let result = sqlite3_prepare_v2(db, sql, -1, &statement, nil)

        if result == SQLITE_OK {
            if sqlite3_step(statement) == SQLITE_DONE {
                print("Index created successfully")
                sqlite3_finalize(statement)
                return true
            }
        }

        sqlite3_finalize(statement)
        return false
    }
}

// 3. INSERT Operations
class InsertOperations {

    func insertUser(_ db: OpaquePointer?, username: String, email: String, age: Int) -> Int64? {
        print("\n--- Inserting User ---")

        let sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?);"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            print("Failed to prepare statement")
            return nil
        }

        // Bind parameters
        sqlite3_bind_text(statement, 1, (username as NSString).utf8String, -1, nil)
        sqlite3_bind_text(statement, 2, (email as NSString).utf8String, -1, nil)
        sqlite3_bind_int(statement, 3, Int32(age))

        // Execute
        let result = sqlite3_step(statement)
        sqlite3_finalize(statement)

        if result == SQLITE_DONE {
            let rowId = sqlite3_last_insert_rowid(db)
            print("User inserted with ID: \(rowId)")
            return rowId
        } else {
            print("Failed to insert user")
            return nil
        }
    }

    func insertUserNamed(_ db: OpaquePointer?, params: [String: Any]) -> Int64? {
        print("\n--- Inserting with Named Parameters ---")

        let sql = "INSERT INTO users (username, email, age) VALUES (:username, :email, :age);"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return nil
        }

        // Bind named parameters
        let usernameIndex = sqlite3_bind_parameter_index(statement, ":username")
        let emailIndex = sqlite3_bind_parameter_index(statement, ":email")
        let ageIndex = sqlite3_bind_parameter_index(statement, ":age")

        if let username = params["username"] as? String {
            sqlite3_bind_text(statement, usernameIndex, (username as NSString).utf8String, -1, nil)
        }
        if let email = params["email"] as? String {
            sqlite3_bind_text(statement, emailIndex, (email as NSString).utf8String, -1, nil)
        }
        if let age = params["age"] as? Int {
            sqlite3_bind_int(statement, ageIndex, Int32(age))
        }

        let result = sqlite3_step(statement)
        sqlite3_finalize(statement)

        if result == SQLITE_DONE {
            return sqlite3_last_insert_rowid(db)
        }

        return nil
    }

    func batchInsert(_ db: OpaquePointer?, users: [(String, String, Int)]) -> Int {
        print("\n--- Batch Insert ---")

        let sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?);"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return 0
        }

        var insertedCount = 0

        // Begin transaction
        sqlite3_exec(db, "BEGIN TRANSACTION;", nil, nil, nil)

        for (username, email, age) in users {
            sqlite3_bind_text(statement, 1, (username as NSString).utf8String, -1, nil)
            sqlite3_bind_text(statement, 2, (email as NSString).utf8String, -1, nil)
            sqlite3_bind_int(statement, 3, Int32(age))

            if sqlite3_step(statement) == SQLITE_DONE {
                insertedCount += 1
            }

            sqlite3_reset(statement)
        }

        // Commit
        sqlite3_exec(db, "COMMIT;", nil, nil, nil)

        sqlite3_finalize(statement)
        print("Batch inserted \(insertedCount) users")

        return insertedCount
    }
}

// 4. SELECT Operations
class SelectOperations {

    struct User {
        let id: Int64
        let username: String
        let email: String
        let age: Int
        let createdAt: String
    }

    func getAllUsers(_ db: OpaquePointer?) -> [User] {
        print("\n--- Selecting All Users ---")

        let sql = "SELECT id, username, email, age, created_at FROM users;"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return []
        }

        var users: [User] = []

        while sqlite3_step(statement) == SQLITE_ROW {
            let id = sqlite3_column_int64(statement, 0)
            let username = sqlite3_column_text(statement, 1)
            let email = sqlite3_column_text(statement, 2)
            let age = sqlite3_column_int(statement, 3)
            let createdAt = sqlite3_column_text(statement, 4)

            let user = User(
                id: id,
                username: String(cString: username!),
                email: String(cString: email!),
                age: Int(age),
                createdAt: String(cString: createdAt!)
            )

            users.append(user)
            print("  Found: \(user.username) (\(user.email))")
        }

        sqlite3_finalize(statement)
        print("Total users: \(users.count)")

        return users
    }

    func getUserById(_ db: OpaquePointer?, userId: Int64) -> User? {
        print("\n--- Selecting User by ID ---")

        let sql = "SELECT id, username, email, age, created_at FROM users WHERE id = ?;"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return nil
        }

        sqlite3_bind_int64(statement, 1, userId)

        guard sqlite3_step(statement) == SQLITE_ROW else {
            sqlite3_finalize(statement)
            print("User not found")
            return nil
        }

        let id = sqlite3_column_int64(statement, 0)
        let username = sqlite3_column_text(statement, 1)
        let email = sqlite3_column_text(statement, 2)
        let age = sqlite3_column_int(statement, 3)
        let createdAt = sqlite3_column_text(statement, 4)

        let user = User(
            id: id,
            username: String(cString: username!),
            email: String(cString: email!),
            age: Int(age),
            createdAt: String(cString: createdAt!)
        )

        sqlite3_finalize(statement)
        print("Found: \(user.username)")

        return user
    }

    func searchUsers(_ db: OpaquePointer?, searchTerm: String) -> [User] {
        print("\n--- Searching Users ---")
        print("Search term: \(searchTerm)")

        let sql = "SELECT id, username, email, age, created_at FROM users WHERE username LIKE ? OR email LIKE ?;"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return []
        }

        let pattern = "%\(searchTerm)%"

        sqlite3_bind_text(statement, 1, (pattern as NSString).utf8String, -1, nil)
        sqlite3_bind_text(statement, 2, (pattern as NSString).utf8String, -1, nil)

        var users: [User] = []

        while sqlite3_step(statement) == SQLITE_ROW {
            let id = sqlite3_column_int64(statement, 0)
            let username = sqlite3_column_text(statement, 1)
            let email = sqlite3_column_text(statement, 2)
            let age = sqlite3_column_int(statement, 3)
            let createdAt = sqlite3_column_text(statement, 4)

            let user = User(
                id: id,
                username: String(cString: username!),
                email: String(cString: email!),
                age: Int(age),
                createdAt: String(cString: createdAt!)
            )

            users.append(user)
        }

        sqlite3_finalize(statement)
        print("Found \(users.count) users")

        return users
    }
}

// 5. UPDATE Operations
class UpdateOperations {

    func updateUserAge(_ db: OpaquePointer?, userId: Int64, newAge: Int) -> Bool {
        print("\n--- Updating User Age ---")

        let sql = "UPDATE users SET age = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return false
        }

        sqlite3_bind_int(statement, 1, Int32(newAge))
        sqlite3_bind_int64(statement, 2, userId)

        let result = sqlite3_step(statement)
        sqlite3_finalize(statement)

        if result == SQLITE_DONE {
            let changes = sqlite3_changes(db)
            print("Updated \(changes) row(s)")
            return changes > 0
        }

        return false
    }

    func updateUserEmail(_ db: OpaquePointer?, userId: Int64, newEmail: String) -> Bool {
        print("\n--- Updating User Email ---")

        let sql = "UPDATE users SET email = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return false
        }

        sqlite3_bind_text(statement, 1, (newEmail as NSString).utf8String, -1, nil)
        sqlite3_bind_int64(statement, 2, userId)

        let result = sqlite3_step(statement)
        sqlite3_finalize(statement)

        if result == SQLITE_DONE {
            let changes = sqlite3_changes(db)
            print("Updated \(changes) row(s)")
            return changes > 0
        }

        return false
    }

    func bulkUpdate(_ db: OpaquePointer?, minAge: Int, newAge: Int) -> Int {
        print("\n--- Bulk Update ---")

        let sql = "UPDATE users SET age = ?, updated_at = CURRENT_TIMESTAMP WHERE age < ?;"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return 0
        }

        sqlite3_bind_int(statement, 1, Int32(newAge))
        sqlite3_bind_int(statement, 2, Int32(minAge))

        let result = sqlite3_step(statement)
        sqlite3_finalize(statement)

        if result == SQLITE_DONE {
            let changes = sqlite3_changes(db)
            print("Updated \(changes) rows")
            return Int(changes)
        }

        return 0
    }
}

// 6. DELETE Operations
class DeleteOperations {

    func deleteUserById(_ db: OpaquePointer?, userId: Int64) -> Bool {
        print("\n--- Deleting User by ID ---")

        let sql = "DELETE FROM users WHERE id = ?;"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return false
        }

        sqlite3_bind_int64(statement, 1, userId)

        let result = sqlite3_step(statement)
        sqlite3_finalize(statement)

        if result == SQLITE_DONE {
            let changes = sqlite3_changes(db)
            print("Deleted \(changes) row(s)")
            return changes > 0
        }

        return false
    }

    func deleteUsersByAge(_ db: OpaquePointer?, maxAge: Int) -> Int {
        print("\n--- Deleting Users by Age ---")

        let sql = "DELETE FROM users WHERE age > ?;"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return 0
        }

        sqlite3_bind_int(statement, 1, Int32(maxAge))

        let result = sqlite3_step(statement)
        sqlite3_finalize(statement)

        if result == SQLITE_DONE {
            let changes = sqlite3_changes(db)
            print("Deleted \(changes) rows")
            return Int(changes)
        }

        return 0
    }

    func deleteAllUsers(_ db: OpaquePointer?) -> Bool {
        print("\n--- Deleting All Users ---")

        let sql = "DELETE FROM users;"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return false
        }

        let result = sqlite3_step(statement)
        sqlite3_finalize(statement)

        if result == SQLITE_DONE {
            let changes = sqlite3_changes(db)
            print("Deleted \(changes) row(s)")
            return true
        }

        return false
    }
}

// 7. Aggregate Queries
class AggregateQueries {

    func getUserCount(_ db: OpaquePointer?) -> Int {
        print("\n--- Getting User Count ---")

        let sql = "SELECT COUNT(*) FROM users;"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return 0
        }

        guard sqlite3_step(statement) == SQLITE_ROW else {
            sqlite3_finalize(statement)
            return 0
        }

        let count = sqlite3_column_int(statement, 0)
        sqlite3_finalize(statement)

        print("Total users: \(count)")
        return Int(count)
    }

    func getAverageAge(_ db: OpaquePointer?) -> Double {
        print("\n--- Getting Average Age ---")

        let sql = "SELECT AVG(age) FROM users;"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return 0
        }

        guard sqlite3_step(statement) == SQLITE_ROW else {
            sqlite3_finalize(statement)
            return 0
        }

        let avgAge = sqlite3_column_double(statement, 0)
        sqlite3_finalize(statement)

        print("Average age: \(avgAge)")
        return avgAge
    }

    func getAgeStats(_ db: OpaquePointer?) -> (min: Int, max: Int, avg: Double)? {
        print("\n--- Getting Age Statistics ---")

        let sql = "SELECT MIN(age), MAX(age), AVG(age) FROM users;"
        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return nil
        }

        guard sqlite3_step(statement) == SQLITE_ROW else {
            sqlite3_finalize(statement)
            return nil
        }

        let minAge = sqlite3_column_int(statement, 0)
        let maxAge = sqlite3_column_int(statement, 1)
        let avgAge = sqlite3_column_double(statement, 2)

        sqlite3_finalize(statement)

        print("Age stats - Min: \(minAge), Max: \(maxAge), Avg: \(avgAge)")

        return (Int(minAge), Int(maxAge), avgAge)
    }
}

// 8. JOIN Queries
class JoinQueries {

    func getUsersWithOrders(_ db: OpaquePointer?) {
        print("\n--- Users with Orders ---")

        let sql = """
        SELECT u.username, u.email, COUNT(o.id) as order_count
        FROM users u
        LEFT JOIN orders o ON u.id = o.user_id
        GROUP BY u.id;
        """

        var statement: OpaquePointer?

        guard sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK else {
            return
        }

        print("Users and their order counts:")
        while sqlite3_step(statement) == SQLITE_ROW {
            let username = sqlite3_column_text(statement, 0)
            let email = sqlite3_column_text(statement, 1)
            let orderCount = sqlite3_column_int(statement, 2)

            print("  \(String(cString: username!)): \(orderCount) orders")
        }

        sqlite3_finalize(statement)
    }
}

// 9. Parameter Binding
class ParameterBinding {

    func demonstrateBinding(_ db: OpaquePointer?) {
        print("\n--- Parameter Binding Examples ---")

        // TEXT binding
        var statement: OpaquePointer?
        let textSQL = "SELECT * FROM users WHERE username = ?;"
        sqlite3_prepare_v2(db, textSQL, -1, &statement, nil)

        // Bind TEXT with explicit length
        let username = "john_doe"
        sqlite3_bind_text(statement, 1, (username as NSString).utf8String, -1, nil)
        print("Bound TEXT: \(username)")

        // INTEGER binding
        let intSQL = "SELECT * FROM users WHERE age = ?;"
        sqlite3_prepare_v2(db, intSQL, -1, &statement, nil)
        sqlite3_bind_int(statement, 1, 25)
        print("Bound INTEGER: 25")

        // REAL binding
        let realSQL = "SELECT * FROM orders WHERE price > ?;"
        sqlite3_prepare_v2(db, realSQL, -1, &statement, nil)
        sqlite3_bind_double(statement, 1, 99.99)
        print("Bound REAL: 99.99")

        // BLOB binding
        let blobSQL = "INSERT INTO files (name, data) VALUES (?, ?);"
        sqlite3_prepare_v2(db, blobSQL, -1, &statement, nil)

        let data = "Hello World".data(using: .utf8)!
        data.withUnsafeBytes { bytes in
            sqlite3_bind_blob(statement, 2, bytes.baseAddress, Int32(data.count), nil)
        }
        print("Bound BLOB: \(data.count) bytes")

        // NULL binding
        sqlite3_bind_null(statement, 1)
        print("Bound NULL")

        sqlite3_finalize(statement)
    }
}

// 10. Error Handling
class ErrorHandling {

    func executeWithErrorHandling(_ db: OpaquePointer?, sql: String) {
        print("\n--- Executing with Error Handling ---")

        var statement: OpaquePointer?
        let result = sqlite3_prepare_v2(db, sql, -1, &statement, nil)

        if result != SQLITE_OK {
            let error = String(cString: sqlite3_errmsg(db))
            let extendedCode = sqlite3_extended_errcode(db)
            print("Error (\(extendedCode)): \(error)")
            return
        }

        // Execute and check for errors
        let stepResult = sqlite3_step(statement)

        if stepResult != SQLITE_ROW && stepResult != SQLITE_DONE {
            let error = String(cString: sqlite3_errmsg(db))
            print("Step error: \(error)")
        }

        sqlite3_finalize(statement)
        print("Statement executed successfully")
    }

    func checkForErrors(_ db: OpaquePointer?) {
        print("\n--- Checking for Errors ---")

        let result = sqlite3_errcode(db)

        if result != SQLITE_OK {
            let error = String(cString: sqlite3_errmsg(db))
            let extendedResult = sqlite3_extended_errcode(db)

            print("Last error code: \(result)")
            print("Extended error code: \(extendedResult)")
            print("Error message: \(error)")
        } else {
            print("No errors")
        }
    }
}

// Main demonstration
func demonstrateSQLQueryExecution() {
    print("=== macOS Swift SQL Query Execution Examples ===")

    let dbPath = "/tmp/test_queries.db"

    // Open database
    var db: OpaquePointer?
    if sqlite3_open(dbPath, &db) == SQLITE_OK {
        print("Database opened")

        // Create tables
        let tableOps = TableOperations()
        tableOps.createUsersTable(db)
        tableOps.createOrdersTable(db)
        tableOps.createIndex(db)

        // Insert users
        let insertOps = InsertOperations()
        _ = insertOps.insertUser(db, username: "john_doe", email: "[email protected]", age: 30)
        _ = insertOps.insertUser(db, username: "jane_smith", email: "[email protected]", age: 25)
        _ = insertOps.insertUser(db, username: "bob_wilson", email: "[email protected]", age: 35)

        // Batch insert
        let batchUsers = [
            ("user1", "[email protected]", 20),
            ("user2", "[email protected]", 22),
            ("user3", "[email protected]", 28)
        ]
        insertOps.batchInsert(db, users: batchUsers)

        // Select operations
        let selectOps = SelectOperations()
        _ = selectOps.getAllUsers(db)
        _ = selectOps.getUserById(db, userId: 1)
        _ = selectOps.searchUsers(db, searchTerm: "john")

        // Update operations
        let updateOps = UpdateOperations()
        updateOps.updateUserAge(db, userId: 1, newAge: 31)
        updateOps.bulkUpdate(db, minAge: 25, newAge: 26)

        // Aggregate queries
        let aggOps = AggregateQueries()
        _ = aggOps.getUserCount(db)
        _ = aggOps.getAverageAge(db)
        _ = aggOps.getAgeStats(db)

        // Delete operations
        let deleteOps = DeleteOperations()
        deleteOps.deleteUserById(db, userId: 3)

        // Cleanup
        sqlite3_close(db)
        print("\nDatabase closed")
    }

    print("\n=== All SQL Query Execution Examples Completed ===")
}

// Run demonstration
demonstrateSQLQueryExecution()

// macOS Swift Transaction Processing Examples
// Using SQLite3 C API

import Foundation
import SQLite3

// 1. Basic Transaction
class BasicTransaction {

    func executeInTransaction(_ db: OpaquePointer?, operations: () -> Bool) -> Bool {
        print("\n--- Basic Transaction ---")

        // Begin transaction
        if sqlite3_exec(db, "BEGIN TRANSACTION;", nil, nil, nil) != SQLITE_OK {
            print("Failed to begin transaction")
            return false
        }

        print("Transaction started")

        // Execute operations
        let success = operations()

        if success {
            // Commit
            if sqlite3_exec(db, "COMMIT;", nil, nil, nil) == SQLITE_OK {
                print("Transaction committed")
                return true
            } else {
                print("Failed to commit transaction")
                return false
            }
        } else {
            // Rollback
            sqlite3_exec(db, "ROLLBACK;", nil, nil, nil)
            print("Transaction rolled back")
            return false
        }
    }
}

// 2. Immediate Transaction
class ImmediateTransaction {

    func executeImmediate(_ db: OpaquePointer?, operations: () -> Bool) -> Bool {
        print("\n--- Immediate Transaction ---")

        // BEGIN IMMEDIATE acquires reserved lock immediately
        if sqlite3_exec(db, "BEGIN IMMEDIATE;", nil, nil, nil) != SQLITE_OK {
            print("Failed to begin immediate transaction")
            return false
        }

        print("Immediate transaction started (reserved lock acquired)")

        let success = operations()

        if success {
            if sqlite3_exec(db, "COMMIT;", nil, nil, nil) == SQLITE_OK {
                print("Transaction committed")
                return true
            }
        } else {
            sqlite3_exec(db, "ROLLBACK;", nil, nil, nil)
            print("Transaction rolled back")
        }

        return false
    }
}

// 3. Exclusive Transaction
class ExclusiveTransaction {

    func executeExclusive(_ db: OpaquePointer?, operations: () -> Bool) -> Bool {
        print("\n--- Exclusive Transaction ---")

        // BEGIN EXCLUSIVE acquires exclusive lock immediately
        if sqlite3_exec(db, "BEGIN EXCLUSIVE;", nil, nil, nil) != SQLITE_OK {
            print("Failed to begin exclusive transaction")
            return false
        }

        print("Exclusive transaction started (exclusive lock acquired)")

        let success = operations()

        if success {
            if sqlite3_exec(db, "COMMIT;", nil, nil, nil) == SQLITE_OK {
                print("Transaction committed")
                return true
            }
        } else {
            sqlite3_exec(db, "ROLLBACK;", nil, nil, nil)
            print("Transaction rolled back")
        }

        return false
    }
}

// 4. Transaction with Savepoints
class SavepointTransaction {

    func executeWithSavepoints(_ db: OpaquePointer?) {
        print("\n--- Transaction with Savepoints ---")

        // Start transaction
        sqlite3_exec(db, "BEGIN;", nil, nil, nil)

        // First savepoint
        print("Creating savepoint 'update_users'")
        sqlite3_exec(db, "SAVEPOINT update_users;", nil, nil, nil)

        // Perform some operations
        // ... (simulated operations)

        // Second savepoint
        print("Creating savepoint 'update_orders'")
        sqlite3_exec(db, "SAVEPOINT update_orders;", nil, nil, nil)

        // More operations
        // ...

        // Release second savepoint
        sqlite3_exec(db, "RELEASE update_orders;", nil, nil, nil)
        print("Released savepoint 'update_orders'")

        // Rollback first savepoint
        sqlite3_exec(db, "ROLLBACK TO update_users;", nil, nil, nil)
        sqlite3_exec(db, "RELEASE update_users;", nil, nil, nil)
        print("Rolled back to 'update_users'")

        // Commit transaction
        sqlite3_exec(db, "COMMIT;", nil, nil, nil)
        print("Transaction committed")
    }
}

// 5. Nested Transactions
class NestedTransactions {

    func executeNestedTransactions(_ db: OpaquePointer?) {
        print("\n--- Nested Transactions ---")

        // Outer transaction
        sqlite3_exec(db, "BEGIN;", nil, nil, nil)
        print("Outer transaction started")

        // Inner transaction using savepoint
        sqlite3_exec(db, "SAVEPOINT inner_trans;", nil, nil, nil)
        print("Inner transaction (savepoint) started")

        // Perform operations
        // ...

        // Commit inner
        sqlite3_exec(db, "RELEASE inner_trans;", nil, nil, nil)
        print("Inner transaction committed")

        // More outer operations
        // ...

        // Commit outer
        sqlite3_exec(db, "COMMIT;", nil, nil, nil)
        print("Outer transaction committed")
    }
}

// 6. Transaction with Retry
class TransactionWithRetry {

    func executeWithRetry(_ db: OpaquePointer?, operations: () -> Bool, maxRetries: Int = 3) -> Bool {
        print("\n--- Transaction with Retry ---")

        for attempt in 1...maxRetries {
            print("Attempt \(attempt)")

            // Begin transaction
            if sqlite3_exec(db, "BEGIN IMMEDIATE;", nil, nil, nil) != SQLITE_OK {
                let error = String(cString: sqlite3_errmsg(db))
                print("Failed to begin: \(error)")

                if attempt < maxRetries {
                    print("Retrying in 100ms...")
                    Thread.sleep(forTimeInterval: 0.1)
                }
                continue
            }

            // Execute operations
            let success = operations()

            if success {
                if sqlite3_exec(db, "COMMIT;", nil, nil, nil) == SQLITE_OK {
                    print("Transaction committed successfully")
                    return true
                }
            } else {
                sqlite3_exec(db, "ROLLBACK;", nil, nil, nil)
                print("Transaction failed, rolled back")
            }

            if attempt < maxRetries {
                print("Retrying in 100ms...")
                Thread.sleep(forTimeInterval: 0.1)
            }
        }

        print("Transaction failed after \(maxRetries) attempts")
        return false
    }
}

// 7. Batch Operations in Transaction
class BatchTransaction {

    func batchInsert(_ db: OpaquePointer?, records: [[String: Any]], tableName: String) -> Int {
        print("\n--- Batch Insert in Transaction ---")

        // Begin transaction
        sqlite3_exec(db, "BEGIN IMMEDIATE;", nil, nil, nil)

        var insertedCount = 0
        var statement: OpaquePointer?

        // Prepare statement
        let sql = "INSERT INTO \(tableName) (username, email, age) VALUES (?, ?, ?);"
        sqlite3_prepare_v2(db, sql, -1, &statement, nil)

        for record in records {
            // Clear previous bindings
            sqlite3_clear_bindings(statement)
            sqlite3_reset(statement)

            // Bind parameters
            if let username = record["username"] as? String {
                sqlite3_bind_text(statement, 1, (username as NSString).utf8String, -1, nil)
            }
            if let email = record["email"] as? String {
                sqlite3_bind_text(statement, 2, (email as NSString).utf8String, -1, nil)
            }
            if let age = record["age"] as? Int {
                sqlite3_bind_int(statement, 3, Int32(age))
            }

            // Execute
            if sqlite3_step(statement) == SQLITE_DONE {
                insertedCount += 1
            }
        }

        sqlite3_finalize(statement)

        // Commit if at least one record inserted
        if insertedCount > 0 {
            sqlite3_exec(db, "COMMIT;", nil, nil, nil)
            print("Batch insert completed: \(insertedCount) records")
        } else {
            sqlite3_exec(db, "ROLLBACK;", nil, nil, nil)
            print("Batch insert failed, rolled back")
        }

        return insertedCount
    }
}

// 8. Transaction Isolation Levels
class IsolationLevels {

    func setReadUncommitted(_ db: OpaquePointer?) {
        print("\n--- Setting Read Uncommitted ---")
        sqlite3_exec(db, "PRAGMA read_uncommitted = true;", nil, nil, nil)
        print("Read uncommitted enabled (dirty reads allowed)")
    }

    func setIsolationLevel(_ db: OpaquePointer?, level: String) {
        print("\n--- Setting Isolation Level ---")

        let levels = [
            "DEFERRED",
            "IMMEDIATE",
            "EXCLUSIVE"
        ]

        if levels.contains(level.uppercased()) {
            let sql = "BEGIN \(level.uppercased()) TRANSACTION;"
            sqlite3_exec(db, sql, nil, nil, nil)
            print("Isolation level set to: \(level.uppercased())")
        }
    }
}

// 9. Transaction Timeout
class TransactionTimeout {

    func executeWithTimeout(_ db: OpaquePointer?, timeout: TimeInterval, operations: @escaping () -> Bool) -> Bool {
        print("\n--- Transaction with Timeout ---")

        // Set busy timeout
        sqlite3_busy_timeout(db, Int32(timeout * 1000))

        // Try to begin transaction
        let startTime = Date()

        while Date().timeIntervalSince(startTime) < timeout {
            if sqlite3_exec(db, "BEGIN IMMEDIATE;", nil, nil, nil) == SQLITE_OK {
                print("Transaction started")

                let success = operations()

                if success {
                    if sqlite3_exec(db, "COMMIT;", nil, nil, nil) == SQLITE_OK {
                        print("Transaction committed")
                        return true
                    }
                } else {
                    sqlite3_exec(db, "ROLLBACK;", nil, nil, nil)
                }

                return false
            }

            Thread.sleep(forTimeInterval: 0.1)
        }

        print("Transaction timeout after \(timeout) seconds")
        return false
    }
}

// 10. Transaction Monitoring
class TransactionMonitor {

    func getTransactionStatus(_ db: OpaquePointer?) -> Bool {
        print("\n--- Transaction Status ---")

        var statement: OpaquePointer?
        let sql = "SELECT COUNT(*) FROM sqlite_master WHERE name = 'sqlite_sequence';"

        if sqlite3_prepare_v2(db, sql, -1, &statement, nil) == SQLITE_OK {
            sqlite3_step(statement)
            sqlite3_finalize(statement)
        }

        // Check if in transaction by attempting a rollback
        // Note: This is a simplified check
        let inTransaction = sqlite3_get_autocommit(db) == 0

        print("In transaction: \(inTransaction)")

        return inTransaction
    }

    func monitorTransaction(_ db: OpaquePointer?) {
        print("\n--- Monitoring Transaction ---")

        var changesBefore = sqlite3_total_changes(db)

        // Begin transaction
        sqlite3_exec(db, "BEGIN;", nil, nil, nil)

        // Perform operations
        // ... (simulated)
        sqlite3_exec(db, "INSERT INTO users (username, email, age) VALUES ('test', '[email protected]', 25);", nil, nil, nil)

        var changesDuring = sqlite3_total_changes(db)

        // Commit
        sqlite3_exec(db, "COMMIT;", nil, nil, nil)

        var changesAfter = sqlite3_total_changes(db)

        print("Changes before transaction: \(changesBefore)")
        print("Changes during transaction: \(changesDuring)")
        print("Changes after transaction: \(changesAfter)")
    }
}

// 11. ACID Properties Demo
class ACIDDemo {

    func demonstrateAtomicity(_ db: OpaquePointer?) {
        print("\n--- Demonstrating Atomicity ---")

        sqlite3_exec(db, "BEGIN;", nil, nil, nil)

        let insertSQL1 = "INSERT INTO users (username, email, age) VALUES ('test1', '[email protected]', 25);"
        let insertSQL2 = "INSERT INTO users (username, email, age) VALUES ('test2', '[email protected]', 30);"

        var success1 = sqlite3_exec(db, insertSQL1, nil, nil, nil) == SQLITE_OK
        var success2 = sqlite3_exec(db, insertSQL2, nil, nil, nil) == SQLITE_OK

        if success1 && success2 {
            sqlite3_exec(db, "COMMIT;", nil, nil, nil)
            print("Both inserts committed (atomic)")
        } else {
            sqlite3_exec(db, "ROLLBACK;", nil, nil, nil)
            print("One or more inserts failed, rolled back (atomic)")
        }
    }

    func demonstrateConsistency(_ db: OpaquePointer?) {
        print("\n--- Demonstrating Consistency ---")

        sqlite3_exec(db, "BEGIN;", nil, nil, nil)

        // Insert valid data
        let validSQL = "INSERT INTO users (username, email, age) VALUES ('valid', '[email protected]', 25);"
        sqlite3_exec(db, validSQL, nil, nil, nil)

        // Try to insert invalid data (violates constraint)
        let invalidSQL = "INSERT INTO users (username, email, age) VALUES ('valid', '[email protected]', 30);"

        if sqlite3_exec(db, invalidSQL, nil, nil, nil) == SQLITE_OK {
            sqlite3_exec(db, "COMMIT;", nil, nil, nil)
        } else {
            print("Constraint violation detected")
            sqlite3_exec(db, "ROLLBACK;", nil, nil, nil)
            print("Transaction rolled back (consistency maintained)")
        }
    }

    func demonstrateIsolation(_ db: OpaquePointer?) {
        print("\n--- Demonstrating Isolation ---")

        // This would require two separate database connections to fully demonstrate
        // Simplified example showing transaction isolation

        sqlite3_exec(db, "BEGIN IMMEDIATE;", nil, nil, nil)

        let selectSQL = "SELECT COUNT(*) FROM users;"
        var statement: OpaquePointer?

        sqlite3_prepare_v2(db, selectSQL, -1, &statement, nil)
        sqlite3_step(statement)
        let count = sqlite3_column_int(statement, 0)
        sqlite3_finalize(statement)

        print("Count in transaction: \(count)")

        sqlite3_exec(db, "COMMIT;", nil, nil, nil)
    }

    func demonstrateDurability(_ db: OpaquePointer?) {
        print("\n--- Demonstrating Durability ---")

        // Sync mode ensures durability
        sqlite3_exec(db, "PRAGMA synchronous = FULL;", nil, nil, nil)

        sqlite3_exec(db, "BEGIN;", nil, nil, nil)

        let insertSQL = "INSERT INTO users (username, email, age) VALUES ('durable', '[email protected]', 25);"
        sqlite3_exec(db, insertSQL, nil, nil, nil)

        if sqlite3_exec(db, "COMMIT;", nil, nil, nil) == SQLITE_OK {
            print("Data committed to disk (durable)")

            // Force flush
            sqlite3_exec(db, "PRAGMA wal_checkpoint(TRUNCATE);", nil, nil, nil)
        }
    }
}

// 12. Error Recovery Transaction
class ErrorRecoveryTransaction {

    func executeWithErrorRecovery(_ db: OpaquePointer?, operations: () -> Bool) -> Bool {
        print("\n--- Transaction with Error Recovery ---")

        // Start with IMMEDIATE to reserve locks
        guard sqlite3_exec(db, "BEGIN IMMEDIATE;", nil, nil, nil) == SQLITE_OK else {
            // If busy, try deferred
            print("IMMEDIATE failed, trying DEFERRED...")
            guard sqlite3_exec(db, "BEGIN DEFERRED;", nil, nil, nil) == SQLITE_OK else {
                print("Failed to begin transaction")
                return false
            }
        }

        print("Transaction started")

        let success = operations()

        if success {
            if sqlite3_exec(db, "COMMIT;", nil, nil, nil) == SQLITE_OK {
                print("Transaction committed")
                return true
            } else {
                // Commit failed, try rollback
                print("Commit failed, rolling back...")
                sqlite3_exec(db, "ROLLBACK;", nil, nil, nil)
                return false
            }
        } else {
            sqlite3_exec(db, "ROLLBACK;", nil, nil, nil)
            print("Operations failed, rolled back")
            return false
        }
    }
}

// Main demonstration
func demonstrateTransactionProcessing() {
    print("=== macOS Swift Transaction Processing Examples ===")

    let dbPath = "/tmp/test_transactions.db"

    // Open database
    var db: OpaquePointer?
    if sqlite3_open(dbPath, &db) == SQLITE_OK {
        // Create table
        let createSQL = """
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            email TEXT NOT NULL,
            age INTEGER
        );
        """
        sqlite3_exec(db, createSQL, nil, nil, nil)

        // 1. Basic transaction
        let basicTrans = BasicTransaction()
        basicTrans.executeInTransaction(db) {
            // Insert user
            var statement: OpaquePointer?
            let sql = "INSERT INTO users (username, email, age) VALUES ('john', '[email protected]', 30);"

            let result = sqlite3_prepare_v2(db, sql, -1, &statement, nil)
            if result == SQLITE_OK {
                let stepResult = sqlite3_step(statement)
                sqlite3_finalize(statement)
                return stepResult == SQLITE_DONE
            }
            return false
        }

        // 2. Immediate transaction
        let immediateTrans = ImmediateTransaction()
        immediateTrans.executeImmediate(db) {
            var statement: OpaquePointer?
            let sql = "INSERT INTO users (username, email, age) VALUES ('jane', '[email protected]', 25);"

            let result = sqlite3_prepare_v2(db, sql, -1, &statement, nil)
            if result == SQLITE_OK {
                let stepResult = sqlite3_step(statement)
                sqlite3_finalize(statement)
                return stepResult == SQLITE_DONE
            }
            return false
        }

        // 3. Savepoints
        let savepointTrans = SavepointTransaction()
        savepointTrans.executeWithSavepoints(db)

        // 4. ACID demo
        let acidDemo = ACIDDemo()
        acidDemo.demonstrateAtomicity(db)
        acidDemo.demonstrateConsistency(db)
        acidDemo.demonstrateIsolation(db)
        acidDemo.demonstrateDurability(db)

        // 5. Batch transaction
        let batchTrans = BatchTransaction()
        let records = [
            ["username": "user1", "email": "[email protected]", "age": 20],
            ["username": "user2", "email": "[email protected]", "age": 22],
            ["username": "user3", "email": "[email protected]", "age": 28]
        ]
        batchTrans.batchInsert(db, records: records, tableName: "users")

        // Cleanup
        sqlite3_close(db)
        print("\nDatabase closed")
    }

    print("\n=== All Transaction Processing Examples Completed ===")
}

// Run demonstration
demonstrateTransactionProcessing()