PHP storage class for DB databases

Create a backup.

After researching Bloom Filters, it was found that they can effectively handle large-scale data and high traffic volumes for small websites.

At its core, this approach still employs text caching – trading inexpensive disk storage for costly memory.

The result is decent – let's back it up.

<?php
/**
 * SQLite 缓存库（已分离，仅供备份或兼容旧代码）
 * 如需使用，请手动包含此文件
 */

// 定义缓存数据库目录
if (!defined('CACHE_DB_DIR')) {
    define('CACHE_DB_DIR', dirname(dirname(__FILE__)) . '/cache_db/');
}

// 性能常量
if (!defined('MAX_MEMORY_USAGE')) {
    define('MAX_MEMORY_USAGE', 64 * 1024 * 1024);
}
if (!defined('MAX_CACHE_SIZE')) {
    define('MAX_CACHE_SIZE', 100 * 1024 * 1024);
}
if (!defined('CACHE_CLEANUP_THRESHOLD')) {
    define('CACHE_CLEANUP_THRESHOLD', 0.7);
}

// 内存使用检查函数
function checkMemoryUsage() {
    $currentUsage = memory_get_usage(true);
    if ($currentUsage > MAX_MEMORY_USAGE * 0.8) {
        if (function_exists('gc_collect_cycles')) {
            gc_collect_cycles();
        }
        return true;
    }
    return false;
}

// 缓存清理函数
function cleanupOldCache() {
    static $lastCleanupCheck = 0;
    if (time() - $lastCleanupCheck < 3600) {
        return;
    }
    $lastCleanupCheck = time();
    $cacheTypes = ['search_cache'];
    foreach ($cacheTypes as $type) {
        $cacheDir = CACHE_DB_DIR . $type;
        $cacheFiles = glob($cacheDir . "/*.db");
        $totalSize = 0;
        foreach ($cacheFiles as $file) {
            $totalSize += filesize($file);
        }
        if ($totalSize > MAX_CACHE_SIZE / 3) {
            $filesWithMtime = [];
            foreach ($cacheFiles as $file) {
                $filesWithMtime[$file] = filemtime($file);
            }
            asort($filesWithMtime);
            $deletedSize = 0;
            $targetSize = (MAX_CACHE_SIZE / 3) * CACHE_CLEANUP_THRESHOLD;
            foreach ($filesWithMtime as $file => $mtime) {
                if ($totalSize - $deletedSize <= $targetSize) break;
                $fileSize = filesize($file);
                if (@unlink($file)) {
                    $deletedSize += $fileSize;
                    DBPool::closeConnection($file);
                }
            }
            error_log("缓存清理完成 ({$type}): 删除了 " . round($deletedSize/1024/1024, 2) . "MB 数据");
        }
    }
}

// 数据库连接池管理类
class DBPool {
    private static $connections = [];
    private static $maxConnections = 10;
    
    public static function getConnection($dbFile) {
        if (isset(self::$connections[$dbFile])) {
            return self::$connections[$dbFile];
        }
        if (count(self::$connections) >= self::$maxConnections) {
            self::cleanup();
        }
        try {
            $db = new PDO("sqlite:{$dbFile}");
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $db->exec("PRAGMA cache_size = -1000");
            $db->exec("PRAGMA temp_store = MEMORY");
            self::$connections[$dbFile] = $db;
            return $db;
        } catch (PDOException $e) {
            error_log("数据库连接失败: " . $e->getMessage());
            return false;
        }
    }
    
    public static function closeConnection($dbFile) {
        if (isset(self::$connections[$dbFile])) {
            self::$connections[$dbFile] = null;
            unset(self::$connections[$dbFile]);
        }
    }
    
    public static function cleanup() {
        $count = count(self::$connections);
        if ($count > self::$maxConnections * 0.7) {
            $keys = array_keys(self::$connections);
            $removeCount = (int)($count * 0.6);
            for ($i = 0; $i < $removeCount; $i++) {
                if (isset($keys[$i])) {
                    self::$connections[$keys[$i]] = null;
                    unset(self::$connections[$keys[$i]]);
                }
            }
        }
    }
    
    public static function shutdown() {
        foreach (self::$connections as $dbFile => $db) {
            self::$connections[$dbFile] = null;
        }
        self::$connections = [];
    }
    
    public static function getStats() {
        return [
            'current_connections' => count(self::$connections),
            'max_connections' => self::$maxConnections,
            'connection_files' => array_keys(self::$connections)
        ];
    }
}

register_shutdown_function(['DBPool', 'shutdown']);

// 确保缓存数据库目录存在
if (!file_exists(CACHE_DB_DIR)) {
    if (!mkdir(CACHE_DB_DIR, 0755, true)) {
        error_log('无法创建缓存数据库目录: ' . CACHE_DB_DIR);
        define('CACHE_DB_DIR', sys_get_temp_dir() . '/cache_db/');
        if (!file_exists(CACHE_DB_DIR)) {
            mkdir(CACHE_DB_DIR, 0755, true);
        }
    }
}

// 创建子目录函数
function createCacheSubdirectories() {
    $subdirs = ['song_cache', 'search_cache', 'searchmore'];
    foreach ($subdirs as $subdir) {
        $dir = CACHE_DB_DIR . $subdir;
        if (!file_exists($dir)) {
            mkdir($dir, 0755, true);
        }
    }
}
createCacheSubdirectories();

// 获取数据库连接（基于MD5前3位分片）
function getCacheDB($type, $key) {
    $shard = substr(md5($key), 0, 3);
    switch ($type) {
        case 'song':
            $dbFile = CACHE_DB_DIR . "song_cache/{$type}_cache_{$shard}.db";
            break;
        case 'search':
            $dbFile = CACHE_DB_DIR . "search_cache/{$type}_cache_{$shard}.db";
            break;
        case 'searchmore':
            $dbFile = CACHE_DB_DIR . "searchmore/{$type}_{$shard}.db";
            break;
        default:
            $dbFile = CACHE_DB_DIR . "{$type}_cache_{$shard}.db";
    }
    $db = DBPool::getConnection($dbFile);
    if (!$db) return false;
    try {
        if ($type === 'song') {
            $db->exec("CREATE TABLE IF NOT EXISTS song_cache (
                song_id INTEGER PRIMARY KEY,
                detail TEXT,
                lyrics TEXT,
                timestamp INTEGER
            )");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_timestamp ON song_cache(timestamp)");
        } elseif ($type === 'search') {
            $db->exec("CREATE TABLE IF NOT EXISTS search_cache (
                key TEXT PRIMARY KEY,
                keyword TEXT,
                data TEXT,
                timestamp INTEGER,
                access_count INTEGER DEFAULT 0,
                last_access INTEGER
            )");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_keyword ON search_cache(keyword)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_timestamp ON search_cache(timestamp)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_access_count ON search_cache(access_count)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_last_access ON search_cache(last_access)");
        } elseif ($type === 'searchmore') {
            $db->exec("CREATE TABLE IF NOT EXISTS searchmore_suggestions (
                md5_key TEXT PRIMARY KEY,
                keyword TEXT NOT NULL,
                create_time INTEGER,
                last_access INTEGER,
                access_count INTEGER DEFAULT 0
            )");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_keyword ON searchmore_suggestions(keyword)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_create_time ON searchmore_suggestions(create_time)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_last_access ON searchmore_suggestions(last_access)");
        }
        return $db;
    } catch (PDOException $e) {
        error_log("数据库初始化失败: " . $e->getMessage());
        DBPool::closeConnection($dbFile);
        return false;
    }
}

// 获取歌曲完整缓存
function getSongCache($songId) {
    checkMemoryUsage();
    $db = getCacheDB('song', $songId);
    if (!$db) return false;
    try {
        $stmt = $db->prepare("SELECT detail, lyrics, timestamp FROM song_cache WHERE song_id = ?");
        $stmt->execute([$songId]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($result) {
            return [
                'detail' => !empty($result['detail']) ? json_decode($result['detail'], true) : null,
                'lyrics' => !empty($result['lyrics']) ? json_decode($result['lyrics'], true) : null,
                'timestamp' => $result['timestamp']
            ];
        }
        return false;
    } catch (PDOException $e) {
        error_log("获取歌曲缓存失败: " . $e->getMessage());
        return false;
    }
}

// 设置歌曲完整缓存
function setSongCache($songId, $detail = null, $lyrics = null) {
    checkMemoryUsage();
    $db = getCacheDB('song', $songId);
    if (!$db) return false;
    $timestamp = time();
    try {
        $stmt = $db->prepare("SELECT detail, lyrics FROM song_cache WHERE song_id = ?");
        $stmt->execute([$songId]);
        $existing = $stmt->fetch(PDO::FETCH_ASSOC);
        $newDetail = $detail;
        $newLyrics = $lyrics;
        if ($existing) {
            if ($newDetail === null && !empty($existing['detail'])) {
                $newDetail = json_decode($existing['detail'], true);
            }
            if ($newLyrics === null && !empty($existing['lyrics'])) {
                $newLyrics = json_decode($existing['lyrics'], true);
            }
        }
        $stmt = $db->prepare("INSERT OR REPLACE INTO song_cache (song_id, detail, lyrics, timestamp) VALUES (?, ?, ?, ?)");
        return $stmt->execute([
            $songId,
            $newDetail ? json_encode($newDetail) : null,
            $newLyrics ? json_encode($newLyrics) : null,
            $timestamp
        ]);
    } catch (PDOException $e) {
        error_log("设置歌曲缓存失败: " . $e->getMessage());
        return false;
    }
}

// 获取搜索缓存
function getSearchCache($keyword, $type = 1) {
    checkMemoryUsage();
    $cacheKey = md5($keyword . '_' . $type);
    $db = getCacheDB('search', $cacheKey);
    if (!$db) return false;
    try {
        $stmt = $db->prepare("SELECT data, timestamp, access_count FROM search_cache WHERE key = ?");
        $stmt->execute([$cacheKey]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($result) {
            $accessCount = $result['access_count'] + 1;
            $currentTime = time();
            $updateStmt = $db->prepare("UPDATE search_cache SET access_count = ?, last_access = ? WHERE key = ?");
            $updateStmt->execute([$accessCount, $currentTime, $cacheKey]);
            return [
                'data' => !empty($result['data']) ? json_decode($result['data'], true) : null,
                'timestamp' => $result['timestamp'],
                'access_count' => $accessCount
            ];
        }
        return false;
    } catch (PDOException $e) {
        error_log("获取搜索缓存失败: " . $e->getMessage());
        return false;
    }
}

// 设置搜索缓存
function setSearchCache($keyword, $type, $data) {
    checkMemoryUsage();
    $cacheKey = md5($keyword . '_' . $type);
    $db = getCacheDB('search', $cacheKey);
    if (!$db) return false;
    $timestamp = time();
    try {
        $stmt = $db->prepare("INSERT OR REPLACE INTO search_cache (key, keyword, data, timestamp, access_count, last_access) VALUES (?, ?, ?, ?, 1, ?)");
        return $stmt->execute([$cacheKey, $keyword, json_encode($data), $timestamp, $timestamp]);
    } catch (PDOException $e) {
        error_log("设置搜索缓存失败: " . $e->getMessage());
        return false;
    }
}

// 获取缓存统计信息
function getCacheStats() {
    $stats = [
        'song' => ['files' => 0, 'total_size' => 0, 'records' => 0],
        'search' => ['files' => 0, 'total_size' => 0, 'records' => 0, 'total_access_count' => 0, 'avg_access_count' => 0],
        'searchmore' => ['files' => 0, 'total_size' => 0, 'records' => 0],
        'pool_stats' => DBPool::getStats()
    ];
    foreach (['song', 'search', 'searchmore'] as $type) {
        $subdir = $type === 'searchmore' ? 'searchmore' : $type . '_cache';
        $files = glob(CACHE_DB_DIR . "{$subdir}/*.db");
        $stats[$type]['files'] = count($files);
        foreach ($files as $dbFile) {
            $stats[$type]['total_size'] += filesize($dbFile);
            try {
                $db = DBPool::getConnection($dbFile);
                if (!$db) continue;
                $table = '';
                switch ($type) {
                    case 'song':
                        $table = 'song_cache';
                        break;
                    case 'search':
                        $table = 'search_cache';
                        break;
                    case 'searchmore':
                        $table = 'searchmore_suggestions';
                        break;
                }
                $stmt = $db->prepare("SELECT COUNT(*) as count FROM {$table}");
                $stmt->execute();
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                $stats[$type]['records'] += $result['count'];
                if ($type === 'search') {
                    $stmt = $db->prepare("SELECT SUM(access_count) as total_access, AVG(access_count) as avg_access FROM search_cache");
                    $stmt->execute();
                    $accessResult = $stmt->fetch(PDO::FETCH_ASSOC);
                    $stats[$type]['total_access_count'] += $accessResult['total_access'] ?? 0;
                    if ($stats[$type]['records'] > 0) {
                        $stats[$type]['avg_access_count'] = round(($accessResult['avg_access'] ?? 0), 2);
                    }
                }
            } catch (PDOException $e) {
                // 忽略统计错误
            }
        }
        $stats[$type]['total_size_mb'] = round($stats[$type]['total_size'] / 1024 / 1024, 2);
    }
    return $stats;
}

// 获取搜索缓存使用情况统计
function getSearchCacheUsageStats($limit = 100) {
    $stats = [
        'most_accessed' => [],
        'least_accessed' => [],
        'oldest_accessed' => [],
        'total_records' => 0,
        'total_access_count' => 0
    ];
    $files = glob(CACHE_DB_DIR . "search_cache/*.db");
    $allRecords = [];
    foreach ($files as $dbFile) {
        try {
            $db = DBPool::getConnection($dbFile);
            if (!$db) continue;
            $stmt = $db->prepare("SELECT key, keyword, access_count, last_access, timestamp FROM search_cache ORDER BY access_count DESC LIMIT ?");
            $stmt->execute([$limit * 2]);
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $allRecords[] = $row;
                $stats['total_records']++;
                $stats['total_access_count'] += $row['access_count'];
            }
        } catch (PDOException $e) {
            // 忽略错误
        }
    }
    usort($allRecords, function($a, $b) {
        return $b['access_count'] - $a['access_count'];
    });
    $stats['most_accessed'] = array_slice($allRecords, 0, $limit);
    $leastAccessed = array_slice($allRecords, -$limit);
    $stats['least_accessed'] = array_reverse($leastAccessed);
    usort($allRecords, function($a, $b) {
        return $a['last_access'] - $b['last_access'];
    });
    $stats['oldest_accessed'] = array_slice($allRecords, 0, $limit);
    return $stats;
}

// 从搜索缓存中随机获取歌曲
function getRandomSongsFromSearchCache($limit = 18) {
    checkMemoryUsage();
    $allSongs = [];
    $searchCacheFiles = glob(CACHE_DB_DIR . "search_cache/*.db");
    if (empty($searchCacheFiles)) {
        return [];
    }
    shuffle($searchCacheFiles);
    $maxAttempts = min(3, count($searchCacheFiles));
    for ($attempt = 0; $attempt < $maxAttempts && count($allSongs) < $limit; $attempt++) {
        $dbFile = $searchCacheFiles[$attempt];
        try {
            $db = DBPool::getConnection($dbFile);
            if (!$db) continue;
            $stmt = $db->prepare("SELECT data FROM search_cache ORDER BY RANDOM() LIMIT 10");
            $stmt->execute();
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
            foreach ($results as $row) {
                if (!empty($row['data'])) {
                    $cacheData = json_decode($row['data'], true);
                    if (isset($cacheData['result']['songs']) && is_array($cacheData['result']['songs'])) {
                        foreach ($cacheData['result']['songs'] as $song) {
                            if (isset($song['id']) && isset($song['name']) && isset($song['artists'])) {
                                $allSongs[] = $song;
                                if (count($allSongs) >= $limit) {
                                    break 3;
                                }
                            }
                        }
                    }
                }
            }
        } catch (PDOException $e) {
            error_log("从搜索缓存获取随机歌曲失败: " . $e->getMessage());
            continue;
        }
    }
    if (count($allSongs) > $limit) {
        shuffle($allSongs);
        $allSongs = array_slice($allSongs, 0, $limit);
    }
    return $allSongs;
}

// 获取 SearchMore 数据库连接
function getSearchMoreDB($keyword) {
    $db = getCacheDB('searchmore', $keyword);
    return $db;
}

// 保存搜索建议到数据库
function saveSearchSuggestion($keyword) {
    checkMemoryUsage();
    $md5Key = md5($keyword);
    $db = getSearchMoreDB($keyword);
    if (!$db) return false;
    $currentTime = time();
    try {
        $stmt = $db->prepare("SELECT access_count FROM searchmore_suggestions WHERE md5_key = ?");
        $stmt->execute([$md5Key]);
        $existing = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($existing) {
            $accessCount = $existing['access_count'] + 1;
            $updateStmt = $db->prepare("UPDATE searchmore_suggestions SET last_access = ?, access_count = ? WHERE md5_key = ?");
            return $updateStmt->execute([$currentTime, $accessCount, $md5Key]);
        } else {
            $stmt = $db->prepare("INSERT INTO searchmore_suggestions (md5_key, keyword, create_time, last_access, access_count) VALUES (?, ?, ?, ?, 1)");
            return $stmt->execute([$md5Key, $keyword, $currentTime, $currentTime]);
        }
    } catch (PDOException $e) {
        error_log("保存搜索建议失败: " . $e->getMessage());
        return false;
    }
}

// 检查搜索建议是否存在
function checkSearchSuggestionExists($keyword) {
    $md5Key = md5($keyword);
    $db = getSearchMoreDB($keyword);
    if (!$db) return false;
    try {
        $stmt = $db->prepare("SELECT keyword FROM searchmore_suggestions WHERE md5_key = ?");
        $stmt->execute([$md5Key]);
        return $stmt->fetch(PDO::FETCH_ASSOC) !== false;
    } catch (PDOException $e) {
        error_log("检查搜索建议失败: " . $e->getMessage());
        return false;
    }
}