MFC RTF数据压缩后保存到SQLite，解压还原。

MFC，MDI，VS2022，Multiple-Byte，Win32 Debug， 静态连接， zlib-1.2.11，SQlite

用ZLIB把包含图片的RTF文本压缩后存到SQLite中，然后读出解压还原。

1.下载ZLIB的源文件，解压后用VS编译静态链接库 zlibstat.lib，放到工程目录下。

2.工程加入ZLIB_WINAPI 编译开关。

3.把ZLIB中的两个头文件COPY到工程目录下。

4.抄几个网上的例子，改动改动，写进测试程序中，压缩、解压都OK啦，

    用的默认压缩率，字节数从8,084,414字节 压到 2,615,875字节。

 

需要注意的配置如下：

 

 

 

程序运行界面如下： 

 

 

主要程序：

CSQLite.h

#pragma once
#include "sqlite3.h"

class CSQLite
{
public:
    CSQLite();
    virtual ~CSQLite();
        
public:
    sqlite3*    m_db;
    char**        m_sresult;
    BOOL        m_IsOpen;
    
public:
    BOOL    Open(char* filename);
    void    Close(); 
    BOOL    Query(char* sql, int &nrow, int &ncolum);
    BOOL    OnSqlExec(char* sql);
    static int sqlcallback(void* NotUsed, int argc, char** argv, char** azColName);
    int     ReadBLOB(char* sql, void* value); 
    BOOL     SaveBLOB(char* sql, void* value, int len);
    
    int     ReadBlobRtf(char* sql, CString &str);//读一个blob数据
    int     ReadBlobRtf(char* sql, void* &value); //xgz 没法显示？
    //BOOL    InsertBLOB1(char* sql, void* value, int len);
    BOOL    UpdateBLOB1(char* sql, void* value, int len);//更新一个blob数据
    
    //xgz zlib compress
    BOOL    UpdateBLOB1c(char* sql, void* value, int len);//xgz 压缩后更新一个blob数据
    int     ReadBlobRtf1c(char* sql, CString& str);   //xgz 读出blob数据解压显示

    BOOL    InsertBLOB(char* sql, int column_index, void* value, int len); 

    // Implementation
public:
    
};

 

CSQLite.cpp

有不少之前测试的垃圾代码，懒得清理了，压缩存储，解压还原是最后面的两个函数

有一个问题，解压需要预分配一个缓冲区，这个缓冲区应该分配多大，解压前不知道。

有一个处理方法：增加一个字段段，保存压缩前的数据大小。

#include "StdAfx.h"
#include "CSQLite.h"

//xgz zlib 
#include "zlib.h"
#include "zconf.h"
#pragma comment(lib,"zlibstat.lib")

#include "RE2.h"  //xgz debug print

CSQLite::CSQLite()
{
    // TODO: add member initialization code here
    m_sresult = NULL;
    m_db = NULL;
    m_IsOpen = FALSE;
}

CSQLite::~CSQLite()
{
    if (NULL != m_sresult)
    {
        sqlite3_free_table(m_sresult);
        m_sresult = NULL;
    }

    if (NULL != m_db)
    {
        sqlite3_close(m_db);
        m_db = NULL;
    }

    m_IsOpen = FALSE;
}

BOOL CSQLite::Open(char* filename)
{
    int rc;

    if (NULL != m_db)
    {
        sqlite3_close(m_db);
        m_db = NULL;
        m_IsOpen = FALSE;
    }

    rc = sqlite3_open(filename, &m_db);
    if (rc)
    {
        sqlite3_close(m_db);
        return FALSE;
    }
    else
    {
        //m_IsOpen = TRUE;
    }

    m_IsOpen = TRUE;
    return TRUE;

}
void CSQLite::Close()
{
    if (NULL != m_db)
    {
        sqlite3_close(m_db);
        m_db = NULL;
    }
    
    m_IsOpen = FALSE;
}

//查询SQL
BOOL CSQLite::Query(char* sql, int& nrow, int& ncolum)
{
    char* szErrMsg;
    int rc;

    if (NULL != m_sresult)
    {
        sqlite3_free_table(m_sresult);
        m_sresult = NULL;
    }

    rc = sqlite3_get_table(m_db, sql, &m_sresult, &nrow, &ncolum, &szErrMsg);  /* execute SQL statement */
    if (rc != SQLITE_OK)
    {
        if (NULL != szErrMsg)
        {
            //PRINT(_T("\r\n<ERR>SQL error: %s\n"), szErrMsg);
            sqlite3_free(szErrMsg);
            return FALSE;
        }
        //PRINT(_T("\r\n<OK>select success!"));
    }

    return TRUE;
}

//sql执行回调函数
int  CSQLite::sqlcallback(void* NotUsed, int argc, char** argv, char** azColName)
{
    int i;
    for (i = 0; i < argc; i++)
    {
        //PRINT(_T("%s = %s\n"), azColName[i], argv[i] ? argv[i] : "NULL");
    }
    //PRINT(_T("\r\n"));
    return 0;
}
//执行SQL
BOOL  CSQLite::OnSqlExec(char* sql)
{
    char* szErrMsg;
    int rc;
    //strcpy(sql,"create table  TStock(StockCode text, Time text, RealValue real );");

    rc = sqlite3_exec(m_db, sql, sqlcallback, 0, &szErrMsg);  /* execute SQL statement */
    if (rc != SQLITE_OK) 
    {
        sqlite3_free(szErrMsg);
        return FALSE;
    }
        
    return TRUE;
}

//xgz 读出了两个数据
//eg. char* sql = "select name，data from Table where name = 'blob123';";
int CSQLite::ReadBLOB(char* sql, void* value)  
{
    int i;
    CString str;

    sqlite3_stmt* pstmt = 0;
    const char* error = 0;
    int rc;
    int len;
    
    //char* sql = "select name，data from Table where name = 'blob123';";
    //char* name;  //name    
    //void* value; //data
    
    rc = sqlite3_prepare(m_db, sql, strlen(sql), &pstmt, &error);
    if (rc != SQLITE_OK)
    {
        return 0;
    }
    
    while (1)
    {
        rc = sqlite3_step(pstmt);

        if (rc != SQLITE_ROW) break;

        //name = (char*)sqlite3_column_text(pstmt, 0);  //字段1，不需要
        value = (void*)sqlite3_column_blob(pstmt, 1); //字段2，只需要blob数据
        len = sqlite3_column_bytes(pstmt, 1);  //字段2的长度

        //str += "---";
        //str += (char*)name;
        //str += "---";
        //str += (char*)value;
    }
    //SetWindowText(str);
    sqlite3_finalize(pstmt);
    return len;
}


int CSQLite::ReadBlobRtf(char* sql, CString &str)
{
    sqlite3_stmt* pstmt = 0;
    const char* error = 0;
    int rc;
    int len;

    void* value=NULL;
    
    rc = sqlite3_prepare(m_db, sql, strlen(sql), &pstmt, &error);
    if (rc != SQLITE_OK)
    {
        return 0;
    }

    while (1)
    {
        rc = sqlite3_step(pstmt);   //XGZ 会有多个步吗？
        if (rc != SQLITE_ROW)
            break;
        value = (void*)sqlite3_column_blob(pstmt, 0); //XGZ 只读一个字段
        len = sqlite3_column_bytes(pstmt, 0);  //xgz 第一字段的字节数
        str += (char*)value;
    }
    sqlite3_finalize(pstmt);
    return len;
}
//xgz 无法显示
int CSQLite::ReadBlobRtf(char* sql, void* &value)
{
    sqlite3_stmt* pstmt = 0;
    const char* error = 0;
    int rc;
    int len;

    //void* value = NULL;

    rc = sqlite3_prepare(m_db, sql, strlen(sql), &pstmt, &error);
    if (rc != SQLITE_OK)
    {
        return 0;
    }

    while (1)
    {
        rc = sqlite3_step(pstmt);   //XGZ 会有多个步吗？
        if (rc != SQLITE_ROW)
            break;
        value = (void*)sqlite3_column_blob(pstmt, 0); //XGZ 只读一个字段
        len = sqlite3_column_bytes(pstmt, 0);  //xgz 第一字段的字节数
        //str += (char*)value;
    }
    sqlite3_finalize(pstmt);
    return len;
}


BOOL CSQLite::SaveBLOB(char* sql, void* value, int len)
{
    sqlite3_stmt* stmt = 0;
    //int index;
    int rc;
    const char* error = 0;
    //char* sql = "UPDATE TTestB SET data = :abc  WHERE name = 'blob123';";
    rc = sqlite3_prepare(m_db, sql, strlen(sql), &stmt, &error);
    if (rc != SQLITE_OK)
    {
        return FALSE;
    }
    
    rc = sqlite3_bind_blob(stmt, 1, (const void*)value, len, SQLITE_STATIC);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return TRUE;
}

BOOL CSQLite::InsertBLOB(char* sql, int column_index, void* value, int len)
{
    sqlite3_stmt* stmt = 0;
    //int index;
    int rc;
    const char* error = 0;
    
    rc = sqlite3_prepare(m_db, sql, strlen(sql), &stmt, &error);
    if (rc != SQLITE_OK)
    {
        return FALSE;
    }
    //rc = sqlite3_prepare(db, "insert into TABLE values ('blob123',?);", -1, &stat, 0);
    
    //index = sqlite3_bind_parameter_index(stmt, ":abc");

    rc = sqlite3_bind_blob(stmt, column_index, (const void*)value, len, SQLITE_STATIC);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return TRUE;
}

//xgz
//char* sql = "UPDATE Table SET data = :abc WHERE name = 'blob123';";
BOOL CSQLite::UpdateBLOB1(char* sql, void* value, int len)
{
    sqlite3_stmt* stmt = 0;
    //int index;
    int rc;
    const char* error = 0;
    
    rc = sqlite3_prepare(m_db, sql, strlen(sql), &stmt, &error);
    if (rc != SQLITE_OK)
    {
        return FALSE;
    }

    rc = sqlite3_bind_blob(stmt, 1, (const void*)value, len, SQLITE_STATIC);  //XGZ 只用第一个字段
    
    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    return TRUE;
}


//xgz
//char* sql = "UPDATE Table SET data = :abc WHERE name = 'blob123';";
BOOL CSQLite::UpdateBLOB1c(char* sql, void* value, int len)
{
    sqlite3_stmt* stmt = 0;
    //int index;
    int rc;
    const char* error = 0;

    rc = sqlite3_prepare(m_db, sql, strlen(sql), &stmt, &error);
    if (rc != SQLITE_OK)
    {
        return FALSE;
    }

    Bytef* pdbuf;
    Bytef* psbuf;

    pdbuf = new Bytef[len];
    psbuf = (Bytef*)value;
    uLongf dlength;
    int slength;
    slength = len;
    z_streamp   strm;
    int res;

    res = compress(pdbuf, &dlength, psbuf, slength);
    
    PRINT("comp: res=%d slen=%d, dlen=%d", res, slength, dlength);
    rc = sqlite3_bind_blob(stmt, 1, (const void*)pdbuf, (int)dlength, SQLITE_STATIC);  //XGZ 只用第一个字段

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);

    delete pdbuf;

    return TRUE;
}

int CSQLite::ReadBlobRtf1c(char* sql, CString& str)
{
    sqlite3_stmt* pstmt = 0;
    const char* error = 0;
    int rc;
    int len;

    void* value = NULL;

    Bytef* pdbuf;
    Bytef* psbuf;

    uLongf dlength;
    int slength;
    z_streamp   strm;
    int res;

    rc = sqlite3_prepare(m_db, sql, strlen(sql), &pstmt, &error);
    if (rc != SQLITE_OK)
    {
        return 0;
    }

    while (1)
    {
        rc = sqlite3_step(pstmt);   //XGZ 会有多个步吗？
        if (rc != SQLITE_ROW)
            break;
        value = (void*)sqlite3_column_blob(pstmt, 0); 
        len = sqlite3_column_bytes(pstmt, 0);  //str += (char*)value;

        psbuf = (Bytef*)value;
        slength = len;
        dlength = 1024*1024*10;  //XGZ 10M 这个怎么确定大小？
        pdbuf = new Bytef[dlength];
        res = uncompress(pdbuf, &dlength, psbuf, slength);
        PRINT("uncomp: res=%d slen=%d, dlen=%d", res, slength, dlength);
        str += (char*)pdbuf;
        delete pdbuf;

    }
    sqlite3_finalize(pstmt);
    return len;
}