MFC 带格式文本 RTF 的SQLite数据库存储

MFC MDI框架，CRichEditView 作为视图，显示和编辑带格式的文本。

文本的存储，是将RTF格式的字符串存入 SQLite 数据库，数据表的BLOB字段。

对于每个视图，保存对应的树节点，关联到SQLite中对应的记录。

树节点也保存视图的指针，避免重复创建视图。

 

 

 

 

 

 

封装的SQLite ：

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); //xgz 读一个blob数据
    BOOL     SaveBLOB(char* sql, void* value, int len);
    
    int     ReadBlobRtf(char* sql, CString &str);//xgz 读一个blob数据
    //BOOL    InsertBLOB1(char* sql, void* value, int len);//xgz 插入一个blob数据
    BOOL    UpdateBLOB1(char* sql, void* value, int len);//xgz 更新一个blob数据

    BOOL    InsertBLOB(char* sql, int column_index, void* value, int len); //xgz 插入一个blob数据, 位置

    // Implementation
public:
    
};

 

CSQLite.cpp

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

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;
        
    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;
}

//xgz 只读一个数据
//eg. char* sql = "select data from Table where name = 'blob123';";
//xgz 本打算直接把value传出来，但一直不对，引用，指针都试过，只好用CString传了
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 只UPDATE一个数据,利用参数(:abc)传递 
//eg. char* sql = "UPDATE Table SET data = :abc  WHERE name = 'blob123';";
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;
    }
    
    //index = sqlite3_bind_parameter_index(stmt, ":abc");
    //index = 1; //xgz 直接用第一个参数，不用管名称，也不用去检索
    
    //ret = sqlite3_bind_blob(stmt, index, value, strlen(value), SQLITE_STATIC);
    rc = sqlite3_bind_blob(stmt, 1, (const void*)value, len, SQLITE_STATIC);
    
    //xgz 如果有多个blob字段，多个参数colum号顺序排，
    //xgz 或者一个个用sqlite3_bind_parameter_index根据参数名称找出column序号
    //rc = sqlite3_bind_blob(stmt, 2, (const void*)value2, len, SQLITE_STATIC);
    //rc = sqlite3_bind_blob(stmt, 3, (const void*)value3, len, SQLITE_STATIC);
    //rc = sqlite3_bind_blob(stmt, 4, (const void*)value4, len, SQLITE_STATIC);

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

//xgz 插入语句blob字段的column序号不太好确定，用index查询比较好，或者把column_index作为参数
//char* sql = "insert into TABLE values ('blob123',?);", -1, &stat, 0);
//? 这种不能用 sqlite3_bind_parameter_index 去搜索，上面第二个字段，column_index=1
//搜索只能用":abc"这样的参数
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_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;
    }

    //index = sqlite3_bind_parameter_index(stmt, ":abc");
    //index = 1; //xgz 直接用第一个参数，不用管名称，也不用去检索
    //ret = sqlite3_bind_blob(stmt, index, value, strlen(value), SQLITE_STATIC);

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

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

 

CRE2View.h

// RE2View.h : interface of the CRE2View class
//

#pragma once

#include "sqlite3.h"
#include "FileView.h"
#include "CSQLite.h"
class CRE2CntrItem;

class CRE2View : public CRichEditView
{
protected: // create from serialization only
    CRE2View();
    DECLARE_DYNCREATE(CRE2View)

// Attributes
public:
    CRE2Doc* GetDocument() const;
    void test();
// Operations
public:
    CFileView*         m_pwndFileView;
    CSQLite*           m_pSQLite;
    HTREEITEM           m_MyhItem;
      

    void ReadFromDB();
    void SaveToDB();
    
    long GetRTFText(CString& str);
    
    static DWORD CALLBACK EditStreamOutCallback(DWORD_PTR dwCookie, LPBYTE pbBuff, LONG cb, LONG* pcb);
    
// Overrides
public:
    virtual BOOL PreCreateWindow(CREATESTRUCT& cs);
protected:
    virtual void OnInitialUpdate(); // called first time after construct
    virtual BOOL OnPreparePrinting(CPrintInfo* pInfo);

// Implementation
public:
    virtual ~CRE2View();
#ifdef _DEBUG
    virtual void AssertValid() const;
    virtual void Dump(CDumpContext& dc) const;
#endif

protected:

// Generated message map functions
protected:
    afx_msg void OnDestroy();
    afx_msg void OnFilePrintPreview();
    afx_msg void OnRButtonUp(UINT nFlags, CPoint point);
    afx_msg void OnContextMenu(CWnd* pWnd, CPoint point);
    DECLARE_MESSAGE_MAP()
public:
    afx_msg void OnButtonR();
    afx_msg void OnButtonS();
    afx_msg void OnPopEdFont();
    afx_msg int OnCreate(LPCREATESTRUCT lpCreateStruct);
    afx_msg void OnPopEditSavetodb();
    afx_msg void OnPopEditReadfromdb();
};

#ifndef _DEBUG  // debug version in RE2View.cpp
inline CRE2Doc* CRE2View::GetDocument() const
   { return reinterpret_cast<CRE2Doc*>(m_pDocument); }
#endif

 

CRE2View.cpp

// RE2View.cpp : implementation of the CRE2View class
//

#include "stdafx.h"
// SHARED_HANDLERS can be defined in an ATL project implementing preview, thumbnail
// and search filter handlers and allows sharing of document code with that project.
#ifndef SHARED_HANDLERS
#include "RE2.h"
#endif

#include "RE2Doc.h"
#include "CntrItem.h"
#include "resource.h"
#include "RE2View.h"

#include "MainFrm.h"


#ifdef _DEBUG
#define new DEBUG_NEW
#endif


// CRE2View

IMPLEMENT_DYNCREATE(CRE2View, CRichEditView)

BEGIN_MESSAGE_MAP(CRE2View, CRichEditView)
    ON_WM_DESTROY()
    // Standard printing commands
    ON_COMMAND(ID_FILE_PRINT, &CRichEditView::OnFilePrint)
    ON_COMMAND(ID_FILE_PRINT_DIRECT, &CRichEditView::OnFilePrint)
    ON_COMMAND(ID_FILE_PRINT_PREVIEW, &CRE2View::OnFilePrintPreview)
    ON_WM_CONTEXTMENU()
    ON_WM_RBUTTONUP()
    ON_COMMAND(ID_BUTTON_R, &CRE2View::OnButtonR)
    ON_COMMAND(ID_BUTTON_S, &CRE2View::OnButtonS)
    ON_COMMAND(ID_POP_ED_FONT, &CRE2View::OnPopEdFont)
    ON_WM_CREATE()
    ON_COMMAND(ID_POP_EDIT_SAVETODB, &CRE2View::OnPopEditSavetodb)
    ON_COMMAND(ID_POP_EDIT_READFROMDB, &CRE2View::OnPopEditReadfromdb)
END_MESSAGE_MAP()

// CRE2View construction/destruction

CRE2View::CRE2View()
{
    // TODO: add construction code here

}

CRE2View::~CRE2View()
{
}

BOOL CRE2View::PreCreateWindow(CREATESTRUCT& cs)
{
    // TODO: Modify the Window class or styles here by modifying
    //  the CREATESTRUCT cs

    return CRichEditView::PreCreateWindow(cs);
}

//xgz OnInitialUpdate 是窗口的初始化，在类的OnCreate之后
void CRE2View::OnInitialUpdate()
{
    CRichEditView::OnInitialUpdate();

    
    m_pwndFileView = &(((CMainFrame*)(AfxGetApp()->m_pMainWnd))->m_wndFileView);
    m_pSQLite = &(m_pwndFileView->m_SQLite);
    m_MyhItem = m_pwndFileView->m_wndFileView.GetSelectedItem();
    

    FileTable* pdata = (FileTable*)m_pwndFileView->m_wndFileView.GetItemData(m_MyhItem);
    pdata->pView = this;

    CFrameWnd* pFrame = GetParentFrame();

    pFrame->SetTitle(pdata->name);
    

    ReadFromDB();


    // Set the printing margins (720 twips = 1/2 inch)
    SetMargins(CRect(720, 720, 720, 720));
}


// CRE2View printing


void CRE2View::OnFilePrintPreview()
{
#ifndef SHARED_HANDLERS
    AFXPrintPreview(this);
#endif
}

BOOL CRE2View::OnPreparePrinting(CPrintInfo* pInfo)
{
    // default preparation
    return DoPreparePrinting(pInfo);
}


void CRE2View::OnDestroy()
{
    // Deactivate the item on destruction; this is important
    // when a splitter view is being used
   COleClientItem* pActiveItem = GetDocument()->GetInPlaceActiveItem(this);
   if (pActiveItem != NULL && pActiveItem->GetActiveView() == this)
   {
      pActiveItem->Deactivate();
      ASSERT(GetDocument()->GetInPlaceActiveItem(this) == NULL);
   }
   CRichEditView::OnDestroy();
}


void CRE2View::OnRButtonUp(UINT /* nFlags */, CPoint point)
{
    ClientToScreen(&point);
    OnContextMenu(this, point);
}

void CRE2View::OnContextMenu(CWnd* /* pWnd */, CPoint point)
{
#ifndef SHARED_HANDLERS
    theApp.GetContextMenuManager()->ShowPopupMenu(IDR_POPUP_EDIT, point.x, point.y, this, TRUE);
#endif
}


// CRE2View diagnostics

#ifdef _DEBUG
void CRE2View::AssertValid() const
{
    CRichEditView::AssertValid();
}

void CRE2View::Dump(CDumpContext& dc) const
{
    CRichEditView::Dump(dc);
}

CRE2Doc* CRE2View::GetDocument() const // non-debug version is inline
{
    ASSERT(m_pDocument->IsKindOf(RUNTIME_CLASS(CRE2Doc)));
    return (CRE2Doc*)m_pDocument;
}
#endif //_DEBUG


// CRE2View message handlers



void CRE2View::OnButtonR()
{
    // TODO: Add your command handler code here
 }


void CRE2View::OnButtonS()
{
    // TODO: Add your command handler code here
    
}

DWORD CALLBACK CRE2View::EditStreamOutCallback(DWORD_PTR dwCookie, LPBYTE pbBuff, LONG cb, LONG* pcb)
{
    CString* psEntry = (CString*)dwCookie;

    CString tmpEntry = CString((LPCSTR)pbBuff);

    //m_cstr2 += tmpEntry.GetBuffer(cb);
    *psEntry += tmpEntry.GetBuffer(cb);

    return 0;
}

long CRE2View::GetRTFText(CString& str)
{
  
    EDITSTREAM es = { (DWORD)&str, 0, EditStreamOutCallback };
    // StreamOut(SF_RTF, es);

    int iAttrib = SF_RTF;
    ::SendMessage(m_hWnd, EM_STREAMOUT, (WPARAM)iAttrib, (LPARAM)&es);

    size_t iLen = str.GetLength();
    return iLen;
};

void CRE2View::OnPopEdFont()
{
    // TODO: Add your command handler code here

    //a.设置字体（主要是通过SetSelectionCharFormat）

    CHARFORMAT2 cf;
    ZeroMemory(&cf, sizeof(CHARFORMAT2));
    cf.cbSize = sizeof(CHARFORMAT2);
    cf.dwMask |= CFM_BOLD;
    cf.dwEffects |= CFE_BOLD;//设置粗体，取消用cf.dwEffects&=~CFE_BOLD;
    cf.dwMask |= CFM_ITALIC;
    cf.dwEffects |= CFE_ITALIC;//设置斜体，取消用cf.dwEffects&=~CFE_ITALIC;
    cf.dwMask |= CFM_UNDERLINE;
    cf.dwEffects |= CFE_UNDERLINE;//设置斜体，取消用cf.dwEffects&=~CFE_UNDERLINE;
    cf.dwMask |= CFM_COLOR;
    cf.crTextColor = RGB(255, 0, 0);//设置颜色
    cf.dwMask |= CFM_SIZE;
    cf.yHeight = 500;//设置高度
    cf.dwMask |= CFM_FACE;
    //strcpy(cf.szFaceName, _T("隶书"));//设置字体
    //wcscpy_s(cf.szFaceName, _T("隶书"));//设置字体
    _tcscpy_s(cf.szFaceName, _T("隶书"));//设置字体
   

    cf = GetCharFormatSelection();  //取所选文本的字体

    HDC hDC = ::GetDC(m_hWnd);

    LOGFONT lf;
    _tcscpy_s(lf.lfFaceName, cf.szFaceName);
    lf.lfHeight = (-cf.yHeight / 15); // xgz 为什么是个负数
    //lf.lfHeight = cf.yHeight * (GetDeviceCaps(hDC, LOGPIXELSY) / 1440);

    lf.lfWeight = 0;     //0 - 1000;400 is normal; 700 is bold; 0 is default weight is used.
    lf.lfItalic = 0;       // 斜体
    lf.lfStrikeOut = 0;    //删除线
    lf.lfUnderline = 0;    //下划线

    if (cf.dwEffects & CFE_BOLD) lf.lfWeight = 700;
    if (cf.dwEffects & CFM_ITALIC) lf.lfItalic = 1;
    if (cf.dwEffects & CFM_UNDERLINE) lf.lfUnderline = 1;


    CFontDialog   dlg(&lf);              //用所选文本字体初始化字体对话框
    dlg.m_cf.rgbColors = cf.crTextColor;  //初始化颜色


    if (IDOK == dlg.DoModal())
    {
        static CFont font;
        dlg.GetCurrentFont(&lf);
        font.DeleteObject();
        font.CreateFontIndirect(&lf);

        ZeroMemory(&cf, sizeof(CHARFORMAT2));  //XGZ  改颜色需要

        cf.dwMask |= CFM_FACE | CFM_SIZE | CFM_COLOR;
        cf.dwMask |= CFM_BOLD | CFM_ITALIC | CFM_UNDERLINE;

        _tcscpy_s(cf.szFaceName, lf.lfFaceName);//设置字体
        cf.yHeight = -15 * lf.lfHeight;  // xgz 为什么是个负数
        //cf.yHeight = lf.lfHeight / 72 * 1440;

        cf.dwEffects &= ~CFE_BOLD;
        cf.dwEffects &= ~CFE_ITALIC;
        cf.dwEffects &= ~CFE_UNDERLINE;

        if (dlg.IsBold())        cf.dwEffects |= CFE_BOLD;//设置粗体，取消用cf.dwEffects&=~CFE_BOLD; 
        if (dlg.IsItalic())        cf.dwEffects |= CFE_ITALIC;//设置斜体，取消用cf.dwEffects&=~CFE_ITALIC;
        if (dlg.IsUnderline())    cf.dwEffects |= CFE_UNDERLINE;//设置斜体，取消用cf.dwEffects&=~CFE_UNDERLINE;

        cf.crTextColor = dlg.GetColor();
        SetCharFormat(cf);

        //SetFont(&font, 1);
    }
}

void CRE2View::test()
{
    PRINT("TEST");
//    OnButtonR();

}

//xgz OnCreate 是类的创建，这时还没有创建窗口
int CRE2View::OnCreate(LPCREATESTRUCT lpCreateStruct)
{
    if (CRichEditView::OnCreate(lpCreateStruct) == -1)
        return -1;

    // TODO:  Add your specialized creation code here
    
    return 0;
}


void CRE2View::ReadFromDB()
{

    
    FileTable* pdata = (FileTable*)m_pwndFileView->m_wndFileView.GetItemData(m_MyhItem);

    CString sql;
    sql.Format("select data from notesid where id = %d;", pdata->id);
    

    CString str;
    int len = 0;

    len = m_pSQLite->ReadBlobRtf(sql.GetBuffer(), str);

    SetWindowText(str);

}


void CRE2View::OnPopEditReadfromdb()
{
    // TODO: Add your command handler code here
    ReadFromDB();
}

void CRE2View::SaveToDB()
{
    // TODO: Add your command handler code here
    
    FileTable* pdata = (FileTable*)m_pwndFileView->m_wndFileView.GetItemData(m_MyhItem);

    CString sql;
    sql.Format("UPDATE notesid SET data = :abc  where id = %d;", pdata->id);

    
    CString str;
    void* value;
    int    len;

    GetRTFText(str);

    value = str.GetBuffer();
    len = str.GetLength();

    m_pSQLite->UpdateBLOB1(sql.GetBuffer(), value, len);

}


void CRE2View::OnPopEditSavetodb()
{
    // TODO: Add your command handler code here
    SaveToDB();
}