SQLite3 在嵌入式C环境中存储音频/视频文件的最优方案

CREATE TABLE media_files (
    id INTEGER PRIMARY KEY,
    file_path TEXT NOT NULL UNIQUE,  -- 文件系统路径
    file_type INTEGER,               -- 1=音频, 2=视频
    duration INTEGER,                -- 时长(ms)
    sample_rate INTEGER,             -- 采样率(Hz)
    created_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_media_type ON media_files(file_type);

// 文件系统布局
/mnt
├── audio   # 音频存储分区
│   ├── sample1.wav
│   └── sample2.mp3
└── video   # 视频存储分区
    ├── clip1.h264
    └── clip2.mjpeg

#include <sqlite3.h>
#include "ff.h"  // FatFS文件系统
// 添加媒体文件记录
int media_add_file(sqlite3 *db, const char *path, int type, int duration, int sampChina编程le_rate) 
{
    sqlite3_stmt *stmt;
    const char *sql = "INSERT INTO media_files(file_path, file_type, duration, sample_rate) VALUES(?,?,?,?)";
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, 0) != SQLITE_OK) 
        return -1;
    sqlite3_bind_text(stmt, 1, path, -1, SQLITE_STATIC);
    sqlite3_bind_int(stmt, 2, type);
    sqlite3_bind_int(stmt, 3, duration);
    sqlite3_bind_int(stmt, 4, sample_rate);
    int rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return (rc == SQLITE_DONE) ? 0 : -1;
}
// 播放媒体文件
void media_play_file(sqlite3 *db, int id)
{
    sqlite3_stmt *stmt;
    const char *sql = "SELECT file_path FROM media_files WHERE id=?";
    if (sqlite3_prepare_v2(db, sql, -1, &stmt, 0) != SQLITE_OK) 
        return;
    sqlite3_bind_int(stmt, 1, id);
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        const char *path = (const char*)sqlite3_column_text(stmt, 0);
        // 使用文件系统API打开文件
        FIL file;
        if (f_open(&file, path, FA_READ) == FR_OK) {
            // 流式读取并解码播放
            uint8_t buffer[4096];
            UINT bytes_read;
            while(f_read(&file, buffer, sizeof(buffer), javascript&bytes_read) == FR_OK && bytes_read > 0) {
                audio_decode_and_play(buffer, bytes_read);
            }
            f_close(&file);
        }
    }
    sqlite3_finalize(stmt);
}

CREATE TABLE media_blobs (
    id INTEGER PRIMARY KEY,
    chunk_index INTEGER,  -- 块索引(0-based)
    total_chunks INTEGER, -- 总块数
    data BLOB,            -- 文件块数据(建议4-16KB/块)
    file_type INTEGER,
    CHECK (chunk_index >= 0 AND chunk_index < total_chunks)
);
CREATE INDEX idx_blob_id ON media_blobs(id, chunk_index);

// 存储文件分块
int store_file_chunks(sqlite3 *db, const char *filename, int file_type)
{
    FIL file;
    if (f_open(&file, filename, FA_READ) != FR_OK)
        return -1;
    // 计算文件大小和块数
    FSIZE_t file_size = f_size(&file);
    const int chunk_size = 16 * 1024; // 16KB/块
    int total_chunks = (file_size + chunk_size - 1) / chunk_size;
    uint8_t *buffer = malloc(chunk_size);
    if (!buffer) {
        f_close(&file);
        return -1;
    }
    sqlite3_exec(db, "BEGIN TRANSACTION;", 0, 0, 0);
    for (int i = 0; i < total_chunks; i++) {
        UINT bytes_read;
        f_read(&file, buffer, chunk_size, &bytes_read);
        sqlite3_stmt *stmt;
        const char *sql = "INSERT INTO media_blobs(id, chunk_index, total_chunks, data, file_type) VALUES(?,?,?,?,?)";
        if (sqlite3_prepare_v2(dbandroid, sql, -1, &stmt, 0) != SQLITE_OK) 
            break;
        // 使用文件哈希作为ID (简化示例)
        int file_id = simple_hash(filename);
        sqlite3_bind_int(stmt, 1, file_id);
        sqlite3_bind_int(stmt, 2, i);
        sqlite3_bind_int(stmt, 3, total_chunks);
        sqlite3_bind_blob(stmt, 4, buffer, bytes_read, SQLITE_STATIC);
        sqlite3_bind_int(stmt, 5, file_type);
        if (sqlite3_step(stmt) != SQLITE_DONE) {
            sqlite3_finalize(stmt);
            break;
        }
        sqlite3_finalize(stmt);
    }
    free(buffer);
    f_close(&file);
    sqlite3_exec(db, "COMMIT;", 0, 0, 0);
    return 0;
}

// 使用LZ4压缩
#include "lz4.h"
void store_compressed_blob(sqlite3_stmt *stmt, int col, void *data, int size)
{
    int max_compressed = LZ4_compressBound(size);
    void *compressed = malloc(max_compressed);
    int compressed_size = LZ4_compress_default(data, compressed, size, max_compressed);
    sqlite3_bind_blob(stmt, col, compressed, compressed_size, SQLITE_STATIC);
    free(compressed);
}

// 自定义SQLite内存分配
void* sqlite_malloc(int size) {
    return my_malloc(MEM_SQLITE, size); // 使用专用内存池
}
void sqlite_free(void *ptr) {
    my_free(MEM_SQLITE, ptr);
}
// 初始化配置
sqliChina编程te3_config(SQLITE_CONFIG_MALLOC, sqlite_malloc, sqlite_free);

// YAFFS2专用配置
struct yaffs_dev *dev = yaffsfs_GetDevicePointer("/media");
dev->param.n_caches = 32;          // 增加缓存块
dev->param.gc_control = 1;         // 积极垃圾回收
dev->param.chunks_per_block = 64;  // 匹配NAND块大小

// SQLite配置
PRAGMA journal_mode = WAL;     // phpWrite-Ahead Logging
PRAGMA synchronous = NORMAL;   // 平衡安全与性能
// 文件系统防护
void media_safe_save(const char *path, void *data, size_t size)
{
    // 1. 写入临时文件
    char temp_path[MAX_PATH];
    snprintf(temp_path, sizeof(temp_path), "%s.tmp", path);
    save_to_file(temp_path, data, size);
    // 2. 同步文件系统
    f_sync(&file);
    // 3. 重命名为正式文件
    f_rename(temp_path, path);
}

// 定期优化数据库
void db_maintenance(sqlite3 *db)
{
    // 重建索引
    sqlite3_exec(db, "REINDEX;", 0, 0, 0);
    // 清理碎片
    sqlite3_exec(db, "VACUUM;", 0, 0, 0);
    // 检查完整性
    sqlite3_stmt *stmt;
    sqlite3_prepare_v2(db, "PRAGMA integrity_check;", -1, &stmt, 0);
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        const char *result = (const char*)sqlite3_column_text(stmt, 0);
        if (strcmp(result, "ok") != 0) {
            log_error("Database corruption: %s", result);
        }
    }
    sqlite3_finalize(stmt);
}

// 嵌入式媒体管理系统初始化
void media_system_init(void)
{
    // 1. 挂载文件系统
    yaffs_mount("/media");
    // 2. 初始化数据库
    sqlite3 *db;
    sqlite3_open("/media/media.db", &db);
    sqlite3_exec(db, "PRAGMA journal_mode=WAL;", 0, 0, 0);
    // 3. 创建媒体表
    const char *schema = "CREATE TABLE IF NOT EXISTS media_files(...)";
    sqlite3_exec(db, schema, 0, 0, 0);
    // 4. 注册媒体播放器
    media_player_init(db, "/media/audio");
}