数据库 --> sqlite3之api使用
创建
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
if [ ! -d /opt/dbspace ] then mkdir /opt/dbspace fi if [ -f /opt/dbspace/.memo.db ] then rm /opt/dbspace/.memo.db; fi if [ -f /opt/dbspace/.memo.db-journal ] then rm /opt/dbspace/.memo.db-journal fi sqlite3 /opt/dbspace/.memo.db 'PRAGMA journal_mode = PERSIST; create table memo_list ( memo_id INTEGER PRIMARY KEY AUTOINCREMENT, is_selected INTEGER default 0, time INTEGER default 0, year INTEGER default 0, month INTEGER default 0, day INTEGER default 0, hour INTEGER default 0, minute INTEGER default 0, wday INTEGER default 0, thumb_path TEXT NOT NULL, memo_text TEXT, image_path TEXT, image_x INTEGER, image_y INTEGER, image_w INTEGER, image_h INTEGER, handwrite_path TEXT, record_path TEXT, record_length INTEGER, UNIQUE (memo_id) ); ' chown :5000 /opt/dbspace/.memo.db chown :5000 /opt/dbspace/.memo.db-journal chmod 660 /opt/dbspace/.memo.db chmod 660 /opt/dbspace/.memo.db-journal chsmack -a 'memo::db' /opt/dbspace/.memo.db*
cmake
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
CMAKE_MINIMUM_REQUIRED(VERSION 2.6) PROJECT(memo-database CXX) INCLUDE(FindPkgConfig) pkg_check_modules(pkgs REQUIRED sqlite3 dlog ) INCLUDE_DIRECTORIES(${pkgs_INCLUDE_DIRS}) LINK_DIRECTORIES(${pkgs_LIBRARY_DIRS}) FOREACH(flag ${pkgs_CFLAGS}) SET(EXTRA_CFLAGS "${EXTRA_CFLAGS} ${flag}") ENDFOREACH(flag) SET(CMAKE_CXX_FLAGS "${EXTRA_CFLAGS} -O2 -Wall -Wcast-align -Wcast-qual -Werror -Wextra -Wnon-virtual-dtor -Wno-unused-parameter -Wshadow -Wwrite-strings -fno-omit-frame-pointer -fno-optimize-sibling-calls -fno-strict-aliasing -fsigned-char -fstrict-overflow -Wno-ignored-qualifiers -Wno-array-bounds -Wno-empty-body -Wno-error") SET(LIB_NAME "${PROJECT_NAME}") SET(LIB_PREFIX ${CMAKE_INSTALL_PREFIX}) SET(LIB_DIR "${LIB_PREFIX}/lib") SET(LIB_INCLUDE_DIR "${LIB_PREFIX}/include") SET(LIB_PKGCONFIG_DIR "/usr/lib/pkgconfig") SET(SRCS src/MemoDb.cpp ) INCLUDE_DIRECTORIES(${CMAKE_CURRENT_SOURCE_DIR}/include) ADD_LIBRARY( ${LIB_NAME} SHARED ${SRCS} ) TARGET_LINK_LIBRARIES(${LIB_NAME} ${pkgs_LDFLAGS}) CONFIGURE_FILE(${LIB_NAME}.pc.in ${LIB_NAME}.pc @ONLY) INSTALL(TARGETS ${LIB_NAME} DESTINATION ${LIB_DIR}) INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/${LIB_NAME}.pc DESTINATION ${LIB_PKGCONFIG_DIR}) INSTALL(FILES ${CMAKE_CURRENT_SOURCE_DIR}/include/MemoDb.h DESTINATION ${LIB_INCLUDE_DIR} )
spec
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Name: memo-database Summary: app control api Version: 0.1.1 Release: 1 License: Flora License Source0: %{name}-%{version}.tar.gz BuildRequires: cmake BuildRequires: pkgconfig(sqlite3) BuildRequires: pkgconfig(dlog) Provides: libmemo-database.so %description App Control API Library %package devel Summary: app control api (devel) Requires: %{name} = %{version} %description devel App Control API devel package %prep %setup -q %build cmake . -DVERSION=%{version} \ -DCMAKE_INSTALL_PREFIX=%{_prefix} make %{?jobs:-j%jobs} %install %make_install %files %{_libdir}/*.so %manifest memo-database.manifest %files devel %{_libdir}/pkgconfig/%{name}.pc %{_includedir}/* %post /sbin/ldconfig if [ ! -d /opt/dbspace ] then mkdir /opt/dbspace fi if [ -f /opt/dbspace/.memo.db ] then rm /opt/dbspace/.memo.db; fi if [ -f /opt/dbspace/.memo.db-journal ] then rm /opt/dbspace/.memo.db-journal fi sqlite3 /opt/dbspace/.memo.db 'PRAGMA journal_mode = PERSIST; create table memo_list ( memo_id INTEGER PRIMARY KEY AUTOINCREMENT, is_selected INTEGER default 0, time INTEGER default 0, year INTEGER default 0, month INTEGER default 0, day INTEGER default 0, hour INTEGER default 0, minute INTEGER default 0, wday INTEGER default 0, thumb_path TEXT NOT NULL, memo_text TEXT, image_path TEXT, image_x INTEGER, image_y INTEGER, image_w INTEGER, image_h INTEGER, handwrite_path TEXT, record_path TEXT, record_length INTEGER, UNIQUE (memo_id) ); ' chown :5000 /opt/dbspace/.memo.db chown :5000 /opt/dbspace/.memo.db-journal chmod 660 /opt/dbspace/.memo.db chmod 660 /opt/dbspace/.memo.db-journal chsmack -a 'memo::db' /opt/dbspace/.memo.db*
头文件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#ifndef __MEMO_DB_H__ #define __MEMO_DB_H__ #include <errno.h> #include <unistd.h> #include <stdio.h> #include <string.h> #include <vector> #include <sqlite3.h> using std::vector; #define DBDIR "/opt/dbspace" #define DBFILE ".memo.db" #define DBPATH DBDIR"/"DBFILE #define MEMO_EMPTY_STR "" #define MEMO_CHECK_STR(p) ((p) ? (p) : MEMO_EMPTY_STR) typedef enum _memo_db_error { MEMO_ERROR_NONE = 0,/**< Success */ MEMO_ERROR_INVALID_DATA = -1,/**< Invalid parameter */ MEMO_ERROR_NO_MEMORY = -2,/**< No memory */ MEMO_ERROR_FROM_DB = -3,/**< Error from DB query */ MEMO_ERROR_ALREADY_EXIST_ID = -4,/**< Already exist private ID */ MEMO_ERROR_FROM_DBUS = -5,/**< Error from DBus */ MEMO_ERROR_NOT_EXIST_ID = -6,/**< Not exist private ID */ MEMO_ERROR_IO = -7,/**< disk i/o error */ MEMO_ERROR_SERVICE_NOT_READY = -8,/**< no reponse from master */ } memo_db_error_e; typedef struct { int nMemoId; bool bIsSelected; long int nTime; int nYear; int nMonth; int nDay; int nHour; int nMinute; int nWeekday; const char *strThumbPath; const char *strMemoText; char *strImagePath; int nImageX; int nImageY; int nImageW; int nImageH; const char *strHandwritePath; const char *strRecordPath; int nRecordLength; }MemoDbData; class CMemoDb { public: CMemoDb() {}; virtual ~CMemoDb() {}; int OpenMemoDb(); int CloseMemoDb(); int InsertMemoDb(MemoDbData *MemoData); int UpdateMemoDb(MemoDbData *MemoData); int DelMemoDb(int nMemoId); int GetDataFromDb(vector<MemoDbData> *MemoVector); int GetDataById(int nMemoId, MemoDbData *MemoData); static CMemoDb* GetInstance(); int GetFirstData(MemoDbData *MemoData); int GetTop3Memo(vector<MemoDbData> *MemoVector); private: int m_ExecMemoDb(const char *query); char *m_GetColumnText(sqlite3_stmt *stmt, int col); static CMemoDb* m_pMemoDbInstance; sqlite3 *db; }; #endif/* __EPG_DB_H__ */
src文件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#include "MemoDb.h" #include "dbg.h" CMemoDb* CMemoDb::m_pMemoDbInstance = NULL; CMemoDb* CMemoDb::GetInstance() { if(m_pMemoDbInstance == NULL) { m_pMemoDbInstance = new CMemoDb(); } return m_pMemoDbInstance; } int CMemoDb::OpenMemoDb() { _DBG("OpenMemoDb------------------------------0"); int ret = 0; ret = sqlite3_open(DBPATH, &db); if (ret != SQLITE_OK) { _ERR("DB open error(%d), %s", ret, DBPATH); return -1; } _DBG("OpenMemoDb------------------------------1"); return 0; } int CMemoDb::CloseMemoDb() { int ret = 0; if (!db) { return MEMO_ERROR_INVALID_DATA; } ret = sqlite3_close(db); if (ret != SQLITE_OK) { _ERR("DB close error(%d)", ret); return MEMO_ERROR_FROM_DB; } db = NULL; _DBG("CloseMemoDb################################"); return MEMO_ERROR_NONE; } int CMemoDb::m_ExecMemoDb( const char *query) { _DBG("m_ExecMemoDb, query is %s", query); int ret = 0; char *err_msg = NULL; if (!db) { return MEMO_ERROR_INVALID_DATA; } ret = sqlite3_exec(db, query, NULL, NULL, &err_msg); if (ret != SQLITE_OK) { _ERR("SQL error(%d) : %s", ret, err_msg); sqlite3_free(err_msg); return MEMO_ERROR_FROM_DB; } _DBG("m_ExecMemoDb finish"); return MEMO_ERROR_NONE; } char *CMemoDb::m_GetColumnText(sqlite3_stmt *stmt, int col) { const unsigned char *col_text = NULL; col_text = sqlite3_column_text(stmt, col); if (!col_text || col_text[0] == '\0') { return NULL; } return strdup((char *)col_text); } int CMemoDb::InsertMemoDb(MemoDbData *MemoData) { _DBG("InsertMemoDb"); char *query = NULL; if (!db) { return MEMO_ERROR_FROM_DB; } query = sqlite3_mprintf("insert into memo_list(" "memo_id, is_selected, " "time, year, month, day, hour, minute, wday, " "thumb_path, memo_text, image_path, " "image_x, image_y, image_w, image_h, " "handwrite_path, " "record_path,record_length) values (" "null, %d, " "%ld, %d, %d, %d, %d, %d, %d, " "'%s', '%s', '%s', " "%d, %d, %d, %d, " "'%s', " "'%s', %d)", MemoData->bIsSelected, MemoData->nTime, MemoData->nYear, MemoData->nMonth, MemoData->nDay, MemoData->nHour, MemoData->nMinute, MemoData->nWeekday, MemoData->strThumbPath, MemoData->strMemoText, MemoData->strImagePath, MemoData->nImageX, MemoData->nImageY, MemoData->nImageW, MemoData->nImageH, MemoData->strHandwritePath, MemoData->strRecordPath, MemoData->nRecordLength); if (m_ExecMemoDb((const char *)query) != MEMO_ERROR_NONE) { return MEMO_ERROR_FROM_DB; } return MEMO_ERROR_NONE; } int CMemoDb::UpdateMemoDb(MemoDbData *MemoData) { _DBG("here should implement"); char *query = NULL; if (!db) { return MEMO_ERROR_FROM_DB; } query = sqlite3_mprintf("update memo_list set " "is_selected = %d, " "time = %ld, year = %d, month = %d, day = %d, hour = %d, minute = %d, wday = %d, " "thumb_path = '%s', memo_text = '%s', image_path = '%s', " "image_x = %d, image_y = %d, image_w = %d, image_h = %d, " "handwrite_path = '%s', " "record_path = '%s', record_length = %d " "where memo_id = %d", MemoData->bIsSelected, MemoData->nTime, MemoData->nYear, MemoData->nMonth, MemoData->nDay, MemoData->nHour, MemoData->nMinute, MemoData->nWeekday, MemoData->strThumbPath, MemoData->strMemoText, MemoData->strImagePath, MemoData->nImageX, MemoData->nImageY, MemoData->nImageW, MemoData->nImageH, MemoData->strHandwritePath, MemoData->strRecordPath, MemoData->nRecordLength, MemoData->nMemoId); if (m_ExecMemoDb((const char *)query) != MEMO_ERROR_NONE) { return MEMO_ERROR_FROM_DB; } return MEMO_ERROR_NONE; } int CMemoDb::DelMemoDb(int nMemoId) { char *query = NULL; if (!db) { return MEMO_ERROR_FROM_DB; } query = sqlite3_mprintf("delete from memo_list where memo_id = %d", nMemoId); if (m_ExecMemoDb( query) != MEMO_ERROR_NONE) { return MEMO_ERROR_FROM_DB; } return MEMO_ERROR_NONE; } int CMemoDb::GetDataById(int nMemoId, MemoDbData *MemoData) { sqlite3_stmt *stmt; char *query = NULL; int ret; if (!db) { return MEMO_ERROR_FROM_DB; } query = sqlite3_mprintf("select memo_id, is_selected, " "time, year, month, day, hour, minute, wday, " "thumb_path, " "memo_text, " "image_path, " "image_x, " "image_y, " "image_w, " "image_h, " "handwrite_path, " "record_path, " "record_length " "from memo_list where memo_id = %d", nMemoId); ret = sqlite3_prepare(db, query, strlen(query), &stmt, NULL); if (ret != SQLITE_OK) { _ERR("Get count DB err(%d) : %s", ret, sqlite3_errmsg(db)); return MEMO_ERROR_FROM_DB; } if (sqlite3_step(stmt) == SQLITE_ROW) { MemoData->bIsSelected = sqlite3_column_int(stmt, 1); MemoData->nTime = sqlite3_column_int(stmt, 2); MemoData->nYear = sqlite3_column_int(stmt, 3); MemoData->nMonth = sqlite3_column_int(stmt, 4); MemoData->nDay = sqlite3_column_int(stmt, 5); MemoData->nHour = sqlite3_column_int(stmt, 6); MemoData->nMinute = sqlite3_column_int(stmt, 7); MemoData->nWeekday = sqlite3_column_int(stmt, 8); MemoData->strThumbPath = m_GetColumnText(stmt, 9); MemoData->strMemoText= m_GetColumnText(stmt, 10); MemoData->strImagePath = m_GetColumnText(stmt, 11); MemoData->nImageX = sqlite3_column_int(stmt, 12); MemoData->nImageY = sqlite3_column_int(stmt, 13); MemoData->nImageW = sqlite3_column_int(stmt, 14); MemoData->nImageH = sqlite3_column_int(stmt, 15); MemoData->strHandwritePath = m_GetColumnText(stmt, 16); MemoData->strRecordPath = m_GetColumnText(stmt, 17); MemoData->nRecordLength= sqlite3_column_int(stmt, 18); } sqlite3_finalize(stmt); return MEMO_ERROR_NONE; } int CMemoDb::GetDataFromDb(vector<MemoDbData> *MemoVector) { sqlite3_stmt *stmt; char *query = NULL; MemoDbData MemoData; int ret; if (!db) { return MEMO_ERROR_FROM_DB; } query = sqlite3_mprintf("select memo_id, is_selected, " "time, year, month, day, hour, minute, wday, " "thumb_path, " "memo_text, " "image_path, " "image_x, " "image_y, " "image_w, " "image_h, " "handwrite_path, " "record_path, " "record_length " "from memo_list order by time desc"); ret = sqlite3_prepare(db, query, strlen(query), &stmt, NULL); if (ret != SQLITE_OK) { _ERR("Get count DB err(%d) : %s", ret, sqlite3_errmsg(db)); return MEMO_ERROR_FROM_DB; } while(sqlite3_step(stmt) == SQLITE_ROW) { MemoData.nMemoId = sqlite3_column_int(stmt , 0); MemoData.bIsSelected = sqlite3_column_int(stmt, 1); MemoData.nTime = sqlite3_column_int(stmt, 2); MemoData.nYear = sqlite3_column_int(stmt, 3); MemoData.nMonth = sqlite3_column_int(stmt, 4); MemoData.nDay = sqlite3_column_int(stmt, 5); MemoData.nHour = sqlite3_column_int(stmt, 6); MemoData.nMinute = sqlite3_column_int(stmt, 7); MemoData.nWeekday = sqlite3_column_int(stmt, 8); MemoData.strThumbPath = m_GetColumnText(stmt, 9); MemoData.strMemoText= m_GetColumnText(stmt, 10); MemoData.strImagePath = m_GetColumnText(stmt, 11); MemoData.nImageX = sqlite3_column_int(stmt, 12); MemoData.nImageY = sqlite3_column_int(stmt, 13); MemoData.nImageW = sqlite3_column_int(stmt, 14); MemoData.nImageH = sqlite3_column_int(stmt, 15); MemoData.strHandwritePath = m_GetColumnText(stmt, 16); MemoData.strRecordPath = m_GetColumnText(stmt, 17); MemoData.nRecordLength= sqlite3_column_int(stmt, 18); MemoVector->push_back(MemoData); } sqlite3_finalize(stmt); return MEMO_ERROR_NONE; } int CMemoDb::GetFirstData(MemoDbData *MemoData) { sqlite3_stmt *stmt; char *query = NULL; int ret; if (!db) { return MEMO_ERROR_FROM_DB; } query = sqlite3_mprintf("select memo_id, is_selected, " "time, year, month, day, hour, minute, wday, " "thumb_path, " "memo_text, " "image_path, " "image_x, " "image_y, " "image_w, " "image_h, " "handwrite_path, " "record_path, " "record_length " "from memo_list order by time desc"); ret = sqlite3_prepare(db, query, strlen(query), &stmt, NULL); if (ret != SQLITE_OK) { _ERR("Get count DB err(%d) : %s", ret, sqlite3_errmsg(db)); return MEMO_ERROR_FROM_DB; } if (sqlite3_step(stmt) == SQLITE_ROW) { MemoData->bIsSelected = sqlite3_column_int(stmt, 1); MemoData->nTime = sqlite3_column_int(stmt, 2); MemoData->nYear = sqlite3_column_int(stmt, 3); MemoData->nMonth = sqlite3_column_int(stmt, 4); MemoData->nDay = sqlite3_column_int(stmt, 5); MemoData->nHour = sqlite3_column_int(stmt, 6); MemoData->nMinute = sqlite3_column_int(stmt, 7); MemoData->nWeekday = sqlite3_column_int(stmt, 8); MemoData->strThumbPath = m_GetColumnText(stmt, 9); MemoData->strMemoText= m_GetColumnText(stmt, 10); MemoData->strImagePath = m_GetColumnText(stmt, 11); MemoData->nImageX = sqlite3_column_int(stmt, 12); MemoData->nImageY = sqlite3_column_int(stmt, 13); MemoData->nImageW = sqlite3_column_int(stmt, 14); MemoData->nImageH = sqlite3_column_int(stmt, 15); MemoData->strHandwritePath = m_GetColumnText(stmt, 16); MemoData->strRecordPath = m_GetColumnText(stmt, 17); MemoData->nRecordLength= sqlite3_column_int(stmt, 18); } sqlite3_finalize(stmt); return MEMO_ERROR_NONE; } int CMemoDb::GetTop3Memo(vector<MemoDbData> *MemoVector) { sqlite3_stmt *stmt; char *query = NULL; MemoDbData MemoData; int ret; if (!db) { return MEMO_ERROR_FROM_DB; } query = sqlite3_mprintf("select memo_id, is_selected, " "time, year, month, day, hour, minute, wday, " "thumb_path, " "memo_text, " "image_path, " "image_x, " "image_y, " "image_w, " "image_h, " "handwrite_path, " "record_path, " "record_length " "from memo_list where is_selected = 1 order by time desc"); ret = sqlite3_prepare(db, query, strlen(query), &stmt, NULL); if (ret != SQLITE_OK) { _ERR("Get count DB err(%d) : %s", ret, sqlite3_errmsg(db)); return MEMO_ERROR_FROM_DB; } while(sqlite3_step(stmt) == SQLITE_ROW) { MemoData.nMemoId = sqlite3_column_int(stmt , 0); MemoData.bIsSelected = sqlite3_column_int(stmt, 1); MemoData.nTime = sqlite3_column_int(stmt, 2); MemoData.nYear = sqlite3_column_int(stmt, 3); MemoData.nMonth = sqlite3_column_int(stmt, 4); MemoData.nDay = sqlite3_column_int(stmt, 5); MemoData.nHour = sqlite3_column_int(stmt, 6); MemoData.nMinute = sqlite3_column_int(stmt, 7); MemoData.nWeekday = sqlite3_column_int(stmt, 8); MemoData.strThumbPath = m_GetColumnText(stmt, 9); MemoData.strMemoText= m_GetColumnText(stmt, 10); MemoData.strImagePath = m_GetColumnText(stmt, 11); MemoData.nImageX = sqlite3_column_int(stmt, 12); MemoData.nImageY = sqlite3_column_int(stmt, 13); MemoData.nImageW = sqlite3_column_int(stmt, 14); MemoData.nImageH = sqlite3_column_int(stmt, 15); MemoData.strHandwritePath = m_GetColumnText(stmt, 16); MemoData.strRecordPath = m_GetColumnText(stmt, 17); MemoData.nRecordLength= sqlite3_column_int(stmt, 18); MemoVector->push_back(MemoData); } sqlite3_finalize(stmt); return MEMO_ERROR_NONE; }