使用PHP的PDO操作SQLite数据库实现WordPress的object cache

我将展示如何使用PHP的PDO操作SQLite数据库，并以实现WordPress的object cache为例，开启WAL和mmap模式。以下是详细的代码实现：

<?php
class WP_SQLite_Object_Cache {
    private $pdo;
    private $db_file;
    private $cache_table = 'wp_object_cache';
    private $cache = [];
    private $expiration = 3600; // 默认缓存1小时

    public function __construct() {
        $this->db_file = WP_CONTENT_DIR . '/cache/object-cache.db';
        $this->connect();
        $this->init_table();
    }

    private function connect() {
        try {
            // 创建或连接SQLite数据库
            $this->pdo = new PDO("sqlite:{$this->db_file}");
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            
            // 开启WAL模式
            $this->pdo->exec('PRAGMA journal_mode=WAL');
            
            // 启用mmap（最大1GB）
            $this->pdo->exec('PRAGMA mmap_size=1073741824');
            
            // 优化设置
            $this->pdo->exec('PRAGMA synchronous=NORMAL');
            $this->pdo->exec('PRAGMA cache_size=-20000'); // 20MB缓存
        } catch (PDOException $e) {
            error_log('SQLite Cache Connection Error: ' . $e->getMessage());
            $this->pdo = null;
        }
    }

    private function init_table() {
        if (!$this->pdo) return;
        
        try {
            // 创建缓存表
            $this->pdo->exec("
                CREATE TABLE IF NOT EXISTS {$this->cache_table} (
                    cache_key TEXT PRIMARY KEY,
                    cache_value BLOB,
                    cache_group TEXT,
                    expiry INTEGER
                )
            ");
            // 创建索引
            $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_group ON {$this->cache_table} (cache_group)");
        } catch (PDOException $e) {
            error_log('SQLite Cache Table Creation Error: ' . $e->getMessage());
        }
    }

    public function get($key, $group = 'default', $force = false) {
        if (!$this->pdo) return false;

        // 检查内存缓存
        $cache_key = "{$group}:{$key}";
        if (!$force && isset($this->cache[$cache_key])) {
            return $this->cache[$cache_key];
        }

        try {
            $stmt = $this->pdo->prepare("
                SELECT cache_value 
                FROM {$this->cache_table} 
                WHERE cache_key = :key 
                AND cache_group = :group 
                AND (expiry = 0 OR expiry > :now)
            ");
            $stmt->execute([
                ':key' => $key,
                ':group' => $group,
                ':now' => time()
            ]);
            
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($result) {
                $value = unserialize($result['cache_value']);
                $this->cache[$cache_key] = $value;
                return $value;
            }
        } catch (PDOException $e) {
            error_log('SQLite Cache Get Error: ' . $e->getMessage());
        }
        
        return false;
    }

    public function set($key, $data, $group = 'default', $expire = 0) {
        if (!$this->pdo) return false;

        try {
            $stmt = $this->pdo->prepare("
                INSERT OR REPLACE INTO {$this->cache_table} 
                (cache_key, cache_value, cache_group, expiry)
                VALUES (:key, :value, :group, :expiry)
            ");
            
            $cache_key = "{$group}:{$key}";
            $expire = $expire ? time() + $expire : 0;
            
            $result = $stmt->execute([
                ':key' => $key,
                ':value' => serialize($data),
                ':group' => $group,
                ':expiry' => $expire
            ]);
            
            if ($result) {
                $this->cache[$cache_key] = $data;
            }
            
            return $result;
        } catch (PDOException $e) {
            error_log('SQLite Cache Set Error: ' . $e->getMessage());
            return false;
        }
    }

    public function delete($key, $group = 'default') {
        if (!$this->pdo) return false;

        try {
            $stmt = $this->pdo->prepare("
                DELETE FROM {$this->cache_table} 
                WHERE cache_key = :key 
                AND cache_group = :group
            ");
            
            $result = $stmt->execute([
                ':key' => $key,
                ':group' => $group
            ]);
            
            $cache_key = "{$group}:{$key}";
            unset($this->cache[$cache_key]);
            
            return $result;
        } catch (PDOException $e) {
            error_log('SQLite Cache Delete Error: ' . $e->getMessage());
            return false;
        }
    }

    public function flush() {
        if (!$this->pdo) return false;

        try {
            $this->pdo->exec("DELETE FROM {$this->cache_table}");
            $this->cache = [];
            return true;
        } catch (PDOException $e) {
            error_log('SQLite Cache Flush Error: ' . $e->getMessage());
            return false;
        }
    }

    public function __destruct() {
        // 清理过期缓存
        if ($this->pdo) {
            try {
                $this->pdo->exec("
                    DELETE FROM {$this->cache_table} 
                    WHERE expiry > 0 AND expiry < :now
                ");
                $this->pdo->exec([':now' => time()]);
            } catch (PDOException $e) {
                error_log('SQLite Cache Cleanup Error: ' . $e->getMessage());
            }
        }
    }
}

// WordPress object cache 集成
function wp_cache_init() {
    $GLOBALS['wp_object_cache'] = new WP_SQLite_Object_Cache();
}

function wp_cache_get($key, $group = 'default', $force = false, &$found = null) {
    $found = false;
    $value = $GLOBALS['wp_object_cache']->get($key, $group, $force);
    if ($value !== false) {
        $found = true;
    }
    return $value;
}

function wp_cache_set($key, $data, $group = 'default', $expire = 0) {
    return $GLOBALS['wp_object_cache']->set($key, $data, $group, $expire);
}

function wp_cache_delete($key, $group = 'default') {
    return $GLOBALS['wp_object_cache']->delete($key, $group);
}

function wp_cache_flush() {
    return $GLOBALS['wp_object_cache']->flush();
}
?>

<?php
class WP_SQLite_Object_Cache {
    private $pdo;
    private $db_file;
    private $cache_table = 'wp_object_cache';
    private $cache = [];
    private $expiration = 3600; // 默认缓存1小时

    public function __construct() {
        $this->db_file = WP_CONTENT_DIR . '/cache/object-cache.db';
        $this->connect();
        $this->init_table();
    }

    private function connect() {
        try {
            // 创建或连接SQLite数据库
            $this->pdo = new PDO("sqlite:{$this->db_file}");
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            
            // 开启WAL模式
            $this->pdo->exec('PRAGMA journal_mode=WAL');
            
            // 启用mmap（最大1GB）
            $this->pdo->exec('PRAGMA mmap_size=1073741824');
            
            // 优化设置
            $this->pdo->exec('PRAGMA synchronous=NORMAL');
            $this->pdo->exec('PRAGMA cache_size=-20000'); // 20MB缓存
        } catch (PDOException $e) {
            error_log('SQLite Cache Connection Error: ' . $e->getMessage());
            $this->pdo = null;
        }
    }

    private function init_table() {
        if (!$this->pdo) return;
        
        try {
            // 创建缓存表
            $this->pdo->exec("
                CREATE TABLE IF NOT EXISTS {$this->cache_table} (
                    cache_key TEXT PRIMARY KEY,
                    cache_value BLOB,
                    cache_group TEXT,
                    expiry INTEGER
                )
            ");
            // 创建索引
            $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_group ON {$this->cache_table} (cache_group)");
        } catch (PDOException $e) {
            error_log('SQLite Cache Table Creation Error: ' . $e->getMessage());
        }
    }

    public function get($key, $group = 'default', $force = false) {
        if (!$this->pdo) return false;

        // 检查内存缓存
        $cache_key = "{$group}:{$key}";
        if (!$force && isset($this->cache[$cache_key])) {
            return $this->cache[$cache_key];
        }

        try {
            $stmt = $this->pdo->prepare("
                SELECT cache_value 
                FROM {$this->cache_table} 
                WHERE cache_key = :key 
                AND cache_group = :group 
                AND (expiry = 0 OR expiry > :now)
            ");
            $stmt->execute([
                ':key' => $key,
                ':group' => $group,
                ':now' => time()
            ]);
            
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($result) {
                $value = unserialize($result['cache_value']);
                $this->cache[$cache_key] = $value;
                return $value;
            }
        } catch (PDOException $e) {
            error_log('SQLite Cache Get Error: ' . $e->getMessage());
        }
        
        return false;
    }

    public function set($key, $data, $group = 'default', $expire = 0) {
        if (!$this->pdo) return false;

        try {
            $stmt = $this->pdo->prepare("
                INSERT OR REPLACE INTO {$this->cache_table} 
                (cache_key, cache_value, cache_group, expiry)
                VALUES (:key, :value, :group, :expiry)
            ");
            
            $cache_key = "{$group}:{$key}";
            $expire = $expire ? time() + $expire : 0;
            
            $result = $stmt->execute([
                ':key' => $key,
                ':value' => serialize($data),
                ':group' => $group,
                ':expiry' => $expire
            ]);
            
            if ($result) {
                $this->cache[$cache_key] = $data;
            }
            
            return $result;
        } catch (PDOException $e) {
            error_log('SQLite Cache Set Error: ' . $e->getMessage());
            return false;
        }
    }

    public function delete($key, $group = 'default') {
        if (!$this->pdo) return false;

        try {
            $stmt = $this->pdo->prepare("
                DELETE FROM {$this->cache_table} 
                WHERE cache_key = :key 
                AND cache_group = :group
            ");
            
            $result = $stmt->execute([
                ':key' => $key,
                ':group' => $group
            ]);
            
            $cache_key = "{$group}:{$key}";
            unset($this->cache[$cache_key]);
            
            return $result;
        } catch (PDOException $e) {
            error_log('SQLite Cache Delete Error: ' . $e->getMessage());
            return false;
        }
    }

    public function flush() {
        if (!$this->pdo) return false;

        try {
            $this->pdo->exec("DELETE FROM {$this->cache_table}");
            $this->cache = [];
            return true;
        } catch (PDOException $e) {
            error_log('SQLite Cache Flush Error: ' . $e->getMessage());
            return false;
        }
    }

    public function __destruct() {
        // 清理过期缓存
        if ($this->pdo) {
            try {
                $this->pdo->exec("
                    DELETE FROM {$this->cache_table} 
                    WHERE expiry > 0 AND expiry < :now
                ");
                $this->pdo->exec([':now' => time()]);
            } catch (PDOException $e) {
                error_log('SQLite Cache Cleanup Error: ' . $e->getMessage());
            }
        }
    }
}

// WordPress object cache 集成
function wp_cache_init() {
    $GLOBALS['wp_object_cache'] = new WP_SQLite_Object_Cache();
}

function wp_cache_get($key, $group = 'default', $force = false, &$found = null) {
    $found = false;
    $value = $GLOBALS['wp_object_cache']->get($key, $group, $force);
    if ($value !== false) {
        $found = true;
    }
    return $value;
}

function wp_cache_set($key, $data, $group = 'default', $expire = 0) {
    return $GLOBALS['wp_object_cache']->set($key, $data, $group, $expire);
}

function wp_cache_delete($key, $group = 'default') {
    return $GLOBALS['wp_object_cache']->delete($key, $group);
}

function wp_cache_flush() {
    return $GLOBALS['wp_object_cache']->flush();
}
?>

使用说明

文件放置：将上述代码保存为 wp-content/object-cache.php，WordPress会自动加载此文件作为对象缓存后端。
数据库位置：缓存数据库文件会存储在 wp-content/cache/object-cache.db。
WAL和mmap设置：

PRAGMA journal_mode=WAL 开启写前日志模式，提高并发性能。
PRAGMA mmap_size=1073741824 启用1GB的内存映射，提升读取速度。

功能说明：

实现了WordPress object cache所需的核心方法：get, set, delete, flush。
使用内存缓存（$this->cache）减少数据库查询。
自动清理过期缓存（在析构函数中）。
使用预处理语句防止SQL注入。
支持缓存分组（group）。

优化措施：

创建索引加速查询。
设置合理的缓存大小和同步模式。
错误处理确保健壮性。

使用示例

// 设置缓存
wp_cache_set('my_key', ['data' => 'value'], 'my_group', 3600);

// 获取缓存
$cached = wp_cache_get('my_key', 'my_group');

// 删除缓存
wp_cache_delete('my_key', 'my_group');

// 清空所有缓存
wp_cache_flush();

// 设置缓存
wp_cache_set('my_key', ['data' => 'value'], 'my_group', 3600);

// 获取缓存
$cached = wp_cache_get('my_key', 'my_group');

// 删除缓存
wp_cache_delete('my_key', 'my_group');

// 清空所有缓存
wp_cache_flush();

注意事项

确保 wp-content/cache 目录存在且PHP有写权限。
SQLite扩展必须在PHP中启用。
WAL模式可能增加磁盘空间使用，定期检查数据库文件大小。
大量缓存数据可能需要调整 mmap_size 和 cache_size 参数。

使用说明

使用示例

注意事项

发表评论 取消回复

发表评论取消回复