用php写一个执行sqlite3的安全函数 全部增删改查

db = new SQLite3($databasePath);
            $this->db->enableExceptions(true);
            // 设置WAL模式提升性能
            $this->db->exec('PRAGMA journal_mode=WAL');
            $this->db->exec('PRAGMA synchronous=NORMAL');
            $this->db->exec('PRAGMA foreign_keys=ON');
        } catch (Exception $e) {
            $this->error = "数据库连接失败: " . $e->getMessage();
            throw new Exception($this->error);
        }
    }
    /**
     * 安全查询 - 返回多条记录
     */
    public function safeQuery($sql, $params = []) {
        try {
            $stmt = $this->db->prepare($sql);
            if (!$stmt) {
                throw new Exception("SQL预处理失败");
            }
            // 绑定参数
            $this->bindParams($stmt, $params);
            $result = $stmt->execute();
            $rows = [];
            while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
                $rows[] = $row;
            }
            return $rows;
        } catch (Exception $e) {
            $this->error = "查询失败: " . $e->getMessage();
            return false;
        }
    }
    /**
     * 安全查询 - 返回单条记录
     */
    public function safeQuerySingle($sql, $params = []) {
        try {
            $stmt = $this->db->prepare($sql);
            if (!$stmt) {
                throw new Exception("SQL预处理失败");
            }
            $this->bindParams($stmt, $params);
            $result = $stmt->execute();
            return $result->fetchArray(SQLITE3_ASSOC);
        } catch (Exception $e) {
            $this->error = "查询失败: " . $e->getMessage();
            return false;
        }
    }
    /**
     * 安全执行 - 用于INSERT, UPDATE, DELETE
     */
    public function safeExecute($sql, $params = []) {
        try {
            $this->db->exec('BEGIN TRANSACTION');
            $stmt = $this->db->prepare($sql);
            if (!$stmt) {
                throw new Exception("SQL预处理失败");
            }
            $this->bindParams($stmt, $params);
            $result = $stmt->execute();
            $this->db->exec('COMMIT');
            return [
                'success' => true,
                'changes' => $this->db->changes(),
                'lastInsertId' => $this->db->lastInsertRowID()
            ];
        } catch (Exception $e) {
            $this->db->exec('ROLLBACK');
            $this->error = "执行失败: " . $e->getMessage();
            return false;
        }
    }
    /**
     * 批量插入数据
     */
    public function batchInsert($table, $data) {
        if (empty($data)) {
            return false;
        }
        try {
            $this->db->exec('BEGIN TRANSACTION');
            $columns = array_keys($data[0]);
            $placeholders = ':' . implode(', :', $columns);
            $sql = "INSERT INTO $table (" . implode(', ', $columns) . ") VALUES ($placeholders)";
            $stmt = $this->db->prepare($sql);
            if (!$stmt) {
                throw new Exception("SQL预处理失败");
            }
            $insertedRows = 0;
            foreach ($data as $row) {
                $this->bindParams($stmt, $row);
                if ($stmt->execute()) {
                    $insertedRows++;
                }
                $stmt->reset();
            }
            $this->db->exec('COMMIT');
            return $insertedRows;
        } catch (Exception $e) {
            $this->db->exec('ROLLBACK');
            $this->error = "批量插入失败: " . $e->getMessage();
            return false;
        }
    }
    /**
     * 绑定参数辅助方法
     */
    private function bindParams($stmt, $params) {
        foreach ($params as $key => $value) {
            // 确定参数类型
            if (is_int($value)) {
                $type = SQLITE3_INTEGER;
            } elseif (is_float($value)) {
                $type = SQLITE3_FLOAT;
            } elseif (is_null($value)) {
                $type = SQLITE3_NULL;
            } else {
                $type = SQLITE3_TEXT;
            }
            $stmt->bindValue(is_int($key) ? $key + 1 : ":$key", $value, $type);
        }
    }
    /**
     * 获取错误信息
     */
    public function getError() {
        return $this->error;
    }
    /**
     * 关闭数据库连接
     */
    public function close() {
        if ($this->db) {
            $this->db->close();
        }
    }
    public function __destruct() {
        $this->close();
    }
}
<code_end>
<code_start>
project_name=php_sqlite3_operations
filename=example_usage.php
title=使用示例和测试脚本
entrypoint=true
runnable=true
project_final_file=true
// 使用示例
require_once 'SQLite3SafeOperations.php';
try {
    // 创建数据库操作实例
    $dbOps = new SQLite3SafeOperations('test.db');
    // 创建测试表
    $createTable = "
        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
        )
    ";
    $dbOps->safeExecute($createTable);
    // 示例1: 插入数据
    $insertData = [
        'username' => 'john_doe',
        'email' => 'john@example.com',
        'age' => 25
    ];
    $result = $dbOps->safeExecute(
        "INSERT INTO users (username, email, age) VALUES (:username, :email, :age)",
        $insertData
    );
    if ($result) {
        echo "插入成功，新记录ID: " . $result['lastInsertId'] . "\n";
    }
    // 示例2: 查询数据
    $users = $dbOps->safeQuery(
        "SELECT * FROM users WHERE age > :min_age",
        ['min_age' => 20]
    );
    if ($users !== false) {
        echo "查询到 " . count($users) . " 条记录\n";
        foreach ($users as $user) {
            echo "ID: {$user['id']}, 用户名: {$user['username']}, 邮箱: {$user['email']}\n";
    }
    // 示例3: 更新数据
    $updateResult = $dbOps->safeExecute(
        "UPDATE users SET age = :new_age WHERE username = :username",
        ['new_age' => 26, 'username' => 'john_doe']
    );
    if ($updateResult) {
        echo "更新了 " . $updateResult['changes'] . " 条记录\n";
    // 示例4: 删除数据
    $deleteResult = $dbOps->safeExecute(
        "DELETE FROM users WHERE username = :username",
        ['username' => 'test_user']
    );
    if ($deleteResult) {
        echo "删除了 " . $deleteResult['changes'] . " 条记录\n";
    // 示例5: 批量插入
    $batchData = [
        ['username' => 'user1', 'email' => 'user1@example.com', 'age' => 30],
        ['username' => 'user2', 'email' => 'user2@example.com', 'age' => 28],
        ['username' => 'user3', 'email' => 'user3@example.com', 'age' => 35]
    ];
    $batchResult = $dbOps->batchInsert('users', $batchData);
    if ($batchResult !== false) {
        echo "批量插入了 {$batchResult} 条记录\n";
    }
    // 关闭连接
    $dbOps->close();
} catch (Exception $e) {
    echo "错误: " . $e->getMessage() . "\n";
}
// 错误处理示例
try {
    $dbOps = new SQLite3SafeOperations('test.db');
    // 故意制造错误 - 重复插入相同用户名
    $result = $dbOps->safeExecute(
        "INSERT INTO users (username, email, age) VALUES (:username, :email, :age)",
        ['username' => 'john_doe', 'email' => 'duplicate@example.com', 'age' => 40]
    );
    if ($result === false) {
        echo "操作失败，错误信息: " . $dbOps->getError() . "\n";
    }
    $dbOps->close();
} catch (Exception $e) {
    echo "错误: " . $e->getMessage() . "\n";