기본코드


개발자에게 그 어떤 설명들보다, 코드가 더 이해하기 쉽다고 생각합니다. :)
기본적인 Insert, Delete, Create 들을 어떻게 사용하는지 아래 코드를 먼저 참고하시기 바랍니다.

추가적인 설명들은 아래에서 따로 하겠습니다.


    [CrystalDB.h]


#pragma once

#include <iostream>
#include <list>
#include "sqlite3.h"

static const std::wstring QUERY_TABLE_CREATE  
	= L"CREATE TABLE CRYSTAL_CUBE (FilePath TEXT PRIMARY KEY NOT NULL, CheckSum TEXT, Image BLOB);" ;

static const std::wstring QUERY_TABLE_EXIST
	= L"SELECT name FROM sqlite_master WHERE type='table' AND name='CRYSTAL_CUBE';";

static const std::wstring QUERY_TABLE_DROP
	= L"DROP TABLE IF EXISTS CRYSTAL_CUBE;";

static const std::wstring QUERY_TABLE_EXIST_RECORD
	= L"SELECT FilePath FROM CRYSTAL_CUBE WHERE FilePath=? AND CheckSum=? AND Version=?;";

static const std::wstring QUERY_TABLE_DELETE_RECORD
	= L"DELETE FROM CRYSTAL_CUBE WHERE FilePath=?;";

static const std::wstring QUERY_TABLE_UPSERT_RECORD
	= L"INSERT OR REPLACE INTO CRYSTAL_CUBE VALUES(?, ?, ?);";

static const std::wstring QUERY_TABLE_SELECT_ALL
	= L"SELECT FilePath FROM CRYSTAL_CUBE;";

static const std::wstring QUERY_DB_CLEAN_UP
	= L"VACUUM;";

static const std::wstring QUERY_DB_BEGIN
	= L"BEGIN;";

static const std::wstring QUERY_DB_COMMIT
	= L"COMMIT;";

static const std::wstring QUERY_DB_ROLLBACK
    = L"ROLLBACK;";


class __declspec(dllexport) CrystalDB
{
private:
	   sqlite3 *db;

public: 
	bool Open(std::wstring dbFileName);
	bool Close();   
	bool Upsert(std::wstring filePath, std::wstring checksum, void * binaryData, int size);  
	bool Delete(std::wstring key);  
	std::list<std::wstring> GetAllRecords();

private:  
	bool DropTable();   
	bool CreateTable(); 
	bool IsTableExist();

private:    
	bool Begin();   
	bool Commit();  
	bool Rollback();
};





    [CrystalDB.cpp]


#include <stdio.h>
#include "sqlite3.h"
#include "CrystalDB.h"


//### public ###

bool CrystalDB::Open(std::wstring dbFileName){
	int rc = sqlite3_open16(dbFileName.c_str(), &db);   
	if(rc != SQLITE_OK) { 
		sqlite3_close(db);
		return false;   
	}   
	
	// table check  
	if(this->IsTableExist() == false) {
		this->CreateTable(); 
	}   
	return true;
}



bool CrystalDB::Close()
{   
	return (sqlite3_close(db) == SQLITE_OK);
}



bool CrystalDB::Upsert(std::wstring filePath, std::wstring checksum, void * binaryData, int size) {
	sqlite3_stmt * stmt;    
	const wchar_t * query = QUERY_TABLE_UPSERT_RECORD.c_str();  
	bool result = true; 

	sqlite3_prepare16_v2(this->db, query, -1, &stmt, NULL);  
	sqlite3_bind_text16(stmt, 1, filePath.c_str(), -1,SQLITE_STATIC);   
	sqlite3_bind_text16(stmt, 22, checksum.c_str(), -1,SQLITE_STATIC);  

	if(binaryData == NULL) {        
		sqlite3_bind_blob(stmt, 23, NULL, -1,SQLITE_STATIC);    
	} else {        
		sqlite3_bind_blob(stmt, 23, binaryData, size, SQLITE_TRANSIENT);    
	}   

	// begin    
	this->Begin();   
	if(sqlite3_step(stmt) != SQLITE_DONE) 
	{ 
		fwprintf(stderr, L"line %d: %s\n", __LINE__, sqlite3_errmsg16(this->db));
		result = false; 
	}  
	// commit   
	this->Commit();  

	sqlite3_reset(stmt);    
	sqlite3_finalize(stmt); 
	return result;
}



bool CrystalDB::Delete(std::wstring filePath) 
{
	sqlite3_stmt * stmt;    
	const wchar_t * query = QUERY_TABLE_DELETE_RECORD.c_str();
	bool result = true; 
	
	sqlite3_prepare16_v2(this->db, query, -1, &stmt, NULL);  
	sqlite3_bind_text16(stmt, 1, filePath.c_str(), -1,SQLITE_STATIC);       
	
	// begin    
	this->Begin();   
	if(sqlite3_step(stmt) != SQLITE_DONE) { 
		fwprintf(stderr, L"line %d: %s\n", __LINE__, sqlite3_errmsg16(this->db)); 
		result = false; 
	}  
	// commit   
	this->Commit();  

	sqlite3_reset(stmt);    
	sqlite3_finalize(stmt); 
	return result;
}



std::list<std::wstring> CrystalDB::GetAllRecords()
{    
	std::list<std::wstring> result;   
	sqlite3_stmt * stmt;    
	const wchar_t * query = QUERY_TABLE_SELECT_ALL.c_str(); 

	sqlite3_prepare16_v2(this->db, query, -1, &stmt, NULL);  
	while(sqlite3_step(stmt) == SQLITE_ROW) 
	{
		int type = sqlite3_column_type(stmt, 0);        
		if(type != SQLITE_TEXT) { continue; }       
		std::wstring fileName((wchar_t *)sqlite3_column_text16(stmt, 0));       
		result.push_back(fileName); 
	}   

	sqlite3_reset(stmt);    
	sqlite3_finalize(stmt); 

	return result;
}





//### private ###
bool CrystalDB::DropTable()
{    
	sqlite3_stmt * stmt;    
	const wchar_t * query = QUERY_TABLE_DROP.c_str();   
	bool result = true; 

	sqlite3_prepare16_v2(this->db, query, -1, &stmt, NULL);    
	  
	// begin    
	this->Begin();   
	if(sqlite3_step(stmt) != SQLITE_DONE) { 
		fwprintf(stderr, L"line %d: %s\n", __LINE__, sqlite3_errmsg16(this->db)); 
		result = false; 
	}  
	// commit   
	this->Commit();      

	sqlite3_reset(stmt);    
	sqlite3_finalize(stmt); 

	return result;
}



bool CrystalDB::CreateTable(){   
	sqlite3_stmt * stmt;    
	const wchar_t * query = QUERY_TABLE_CREATE.c_str(); 
	bool result = true; 

	sqlite3_prepare16_v2(this->db, query, -1, &stmt, NULL);  

	// begin    
	this->Begin();   
	if(sqlite3_step(stmt) != SQLITE_DONE) { 
		fwprintf(stderr, L"line %d: %s\n", __LINE__, sqlite3_errmsg16(this->db)); 
		result = false; 
	}  
	// commit   
	this->Commit();  

	sqlite3_reset(stmt);    
	sqlite3_finalize(stmt); 

	return result;
}



bool CrystalDB::IsTableExist(){  
	sqlite3_stmt * stmt;    
	const wchar_t * query = QUERY_TABLE_EXIST.c_str();  
	bool result = false;    

	sqlite3_prepare16_v2(this->db, query, -1, &stmt, NULL);  
	if(sqlite3_step(stmt) == SQLITE_ROW) {
		result = true; 
	} 
	sqlite3_reset(stmt);    
	sqlite3_finalize(stmt); 

	return result;
}



bool CrystalDB::Begin(){ 
	sqlite3_stmt * stmt;    
	const wchar_t * query = QUERY_DB_BEGIN.c_str(); 
	bool result = true; 

	sqlite3_prepare16_v2(this->db, query, -1, &stmt, NULL);  
	if(sqlite3_step(stmt) != SQLITE_DONE) { 
		fwprintf(stderr, L"line %d: %s\n", __LINE__, sqlite3_errmsg16(this->db)); 
		result = false; 
	}  
	sqlite3_reset(stmt);    
	sqlite3_finalize(stmt); 

	return result;
}



bool CrystalDB::Commit(){    
	sqlite3_stmt * stmt;    
	const wchar_t * query = QUERY_DB_COMMIT.c_str();    
	bool result = true; 

	sqlite3_prepare16_v2(this->db, query, -1, &stmt, NULL);  
	if(sqlite3_step(stmt) != SQLITE_DONE) { 
		fwprintf(stderr, L"line %d: %s\n", __LINE__, sqlite3_errmsg16(this->db)); 
		result = false; 
	}  
	sqlite3_reset(stmt);    
	sqlite3_finalize(stmt); 

	return result;
}



bool CrystalDB::Rollback(){  
	sqlite3_stmt * stmt;    
	const wchar_t * query = QUERY_DB_ROLLBACK.c_str();  
	bool result = true; 

	sqlite3_prepare16_v2(this->db, query, -1, &stmt, NULL);  
	if(sqlite3_step(stmt) != SQLITE_DONE) { 
		fwprintf(stderr, L"line %d: %s\n", __LINE__, sqlite3_errmsg16(this->db)); 
		result = false; 
	}  
	sqlite3_reset(stmt);    
	sqlite3_finalize(stmt); 

	return result;
}








기본적인 동작은 보시는 것과 같습니다.

1. Database 를 Open 한다.
2. query 를 보낸다.
3. Database 를 Close 한다. 

VACUUM 에 대해서 설명드리자면,
sqlite 는 기본적으로 data 를 삭제하더라도, 파일의 크기가 줄어들지 않습니다.
예약 공간으로 놔두고, 다음 사용하게 되어 있지요.

만약 data 삭제 후, 파일 크기를 줄이고 싶다면 VACUUM 명령을 내려주면 됩니다.
단, sqlite 문서를 보면, 이 작업에 오버헤드가 많이 걸린다고 되어 있습니다.
이전 포스팅을 보시면 나와 있는데, 지금 기억으로는 1 메가당 0.5 초인가? 걸린다고 했던것 같네요.

그러므로 query 를 보낼때마다 VACUUM 하는 일은 없어야 합니다.

또 한가지 드릴 말씀은. BLOB 인데,
이 녀석은 binary 를 데이터로 받습니다.(물론 다른 타입도 됩니다. - 자세한건 이전 포스팅 FAQ 참고)
저는 처음에 엄청 고생했습니다.
좋지 않은 방법이지만, 일이 좀 있어서 Database 에 Image 를 통째로 넣어야 했습니다.

그런데 넣고나서 SQLite  Viewer 로 열어보았더니, Image Header 부분(약 10 byte?)만 들어간 것입니다.
이녀석이 내부적으로 \00, \0000 등을 만나면 입력을 더이상 안하는 것이죠.

그런데...알고보니...............
안들어간게 아니라, Viewer 가 표시를 그렇게 하는 거였습니다. orz
실제로 database 에서 꺼내서 Image 로 복원하면 잘 나오더군요.

개인적으로 이런 일이 있은 후에는, FireFox 에서 제공하는 sqlite Manager 플러그인을 사용하고 있습니다. :)

자세히 사용법에 대해서 설명을 드리고 싶었는데,
크게 어려운게 없어서 간단히 여기까지만 하고 마치도록 하겠습니다.

참, 마지막으로 이전 포스팅의 FAQ 를 꼭 읽어 보시기 바랍니다.
도움이 될 만한 많은 내용이 나와 있습니다. ^-^
길이도 않아서 10분이면 다 보실것 같네요.