김형훈의 DB이야기

모바일 데이터베이스 SQLite 여덟번째 이야기(2)

모바일을 위한 다양한 요구사항을 만족시키기 위한 데이터베이스 솔루션으로 대표적인 것이 SQLite이다. SQLite는 구글의 안드로이드나 애플의 아이폰 등에서 사용되는 솔루션으로 사용자 측면과 관리자 측면에서 모두 뛰어난 접근성을 가지고 있는 데이터베이스 관리 시스템이다. SQLite에 대해서 현재까지 기본적인 여섯 가지 이야기를 공유하였는데, 지금의 일곱 번째 이야기에서는 검색을 위한 요소인 R-트리에 대해서 살펴보도록 한다.

SQLite에서 사용하는 오브젝트은 다음과 같다.
각 오픈된 SQLite 데이터베이스는 ‘sqlite3'로 명명된 구조체의 인스턴스에 대한 포인터로 표현된다. 이는 오브젝트에 대한 sqlite3 포인터이다.

typedef struct sqlite3 sqlite3;

오브젝트 sqlite3_stmt의 인스턴스를 통해서 단일 SQL문을 나타낼 수 있다.

typedef struct sqlite3_stmt sqlite3_stmt;

이 오브젝트는 예약문 또는 컴파일된 SQL문, 단순한 문장 중 하나가 된다. 이러한 문장의 생성부터 소멸까지는 다음과 같다.



그림 1. SQL문 사용 방법
오브젝트 sqlite3_vfs의 인스턴스는 SQLite 코어와 하위 운영체제간의 인터페이스를 정의한다. 오브젝트에서 ‘vfs'는 가상 파일 시스템을 의미한다.

typedef struct sqlite3_vfs sqlite3_vfs;
struct sqlite3_vfs {
int iVersion; /* 구조체 버전 넘버 */
int szOsFile; /* 하위 클래스의 sqlite3_file의 크기 */
int mxPathname; /* 최대 파일 경로명 길이 */
sqlite3_vfs *pNext; /* 다음 등록된 VFS */
const char *zName; /* 가상 파일 시스템 이름 */
void *pAppData; /* 특정 어플리케이션 데이터에 대한 포인터 */
int (*xOpen)(sqlite3_vfs*, const char *zName, sqlite3_file*,
int flags, int *pOutFlags);
int (*xDelete)(sqlite3_vfs*, const char *zName, int syncDir);
int (*xAccess)(sqlite3_vfs*, const char *zName, int flags, int *pResOut);
int (*xFullPathname)(sqlite3_vfs*, const char *zName, int nOut, char *zOut);
void *(*xDlOpen)(sqlite3_vfs*, const char *zFilename);
void (*xDlError)(sqlite3_vfs*, int nByte, char *zErrMsg);
void (*(*xDlSym)(sqlite3_vfs*,void*, const char *zSymbol))(void);
void (*xDlClose)(sqlite3_vfs*, void*);
int (*xRandomness)(sqlite3_vfs*, int nByte, char *zOut);
int (*xSleep)(sqlite3_vfs*, int microseconds);
int (*xCurrentTime)(sqlite3_vfs*, double*);
int (*xGetLastError)(sqlite3_vfs*, int, char *);
};

리스트 7. sqlite3_vfs 구조체
SQLite에서 뮤텍스(Mutex) 모듈은 sqlite3_mutex 오브젝트를 다음과 같이 정의한다.

typedef struct sqlite3_mutex sqlite3_mutex;

리스트 8. sqlite3_mutex 오브젝트

SQLite는 데이터베이스에 저장된 모든 값들을 나타내기 위해서 sqlite3_value 오브젝트를 정의한다. SQLite는 저장될 값에 대해서 동적으로 타입을 지정할 수 있다.

typedef struct Mem sqlite3_value;

리스트 9. sqlite3_value 오브젝트 정의

오브젝트 sqlite3_value에 저장되는 값들은 정수형, 부동소수점 값, 문자열, BLOB 또는 널(NULL) 값이 될 수 있다.

참고로 SQLite3는 원래는 여러 개의 복잡한 파일들로 구성된다. 그렇지만, SQLite 홈페이지(www.sqlite.org)에서 다운받을 수 있는 sqlite-amalgamation_xxx.zip 파일을 보면 단 2개의 파일만을 찾을 수 있을 뿐이다. 셀 인터페이스를 제공하기 위한 shell.c와 SQLite3의 모든 기능을 추가한 매우 긴 길이를 가지는 sqlite3.c이다. 파일 sqlite3.c를 보면 다양한 기능들이 모두 이 파일 안에 포함되어 있다.



그림 2. sqlite3.c의 생성 과정(www.sqlite.org)
위의 그림에서 나타낸 바와 같이 SQLite의 여러 가지 기능을 지원하는 여러 개의 c 파일들이 상호유기적으로 동작, 필요한 기능을 제공할 수 있는 하나의 핵심 파일인 sqlite3.c이 생성되고 이 생성된 파일을 가지고 라이브러리나 쉘 어플리케이션으로 사용될 수 있다.

다음의 예제는 SQLite의 CREATE, INSERT, SELECT, DELETE, DROP 등의 기본 기능을 테스트하기 위한 코드이며, 트랜잭션을 위한 BEGIN, COMMT API도 포함되어 있다.

다음의 예제 코드는 다음과 같은 경로를 통해서 빌드될 수 있다(다음은 윈도우즈 환경을 가정으로 구성되었다).

Step 1 ) SQLite 홈페이지인　www.sqlite.org에서 윈도우즈용 SQLite 소스를 다운로드 받는다(이는 zip로 압축되어 있으니 이를 하드디스크 내의 적정한 위치에 풀도록 한다).

Step 2 )　Visual studio를 실행하여 아무것도 포함하지 않은 비어있는 프로젝트를 만들도록 한다. Step 1에서 다운로드 받은 파일의 압축을 풀면, shell.c와 sqlite3.c 두 개의 C 파일을 발견할 수 있다. 이 파일 중 sqlite3.c만 Visual studio 프로젝트에 포함하고, 나머지 헤더들도 포함시킨다.







Step 3 )　이와 같은 작업이 끝나면, 다음에서 제시하는 테스트 코드를 sqlite3_test2.c라는 파일에 저장하고, 저장된 파일을 위의 프로젝트에 포함하면 된다.




Step 4 )　이제는 테스트를 위한 간단한 작업이 끝났다. 빌드하면 sqlite3_test2.exe라는 실행파일을 찾을 수 있다. 이 실행파일을 실행시키면, 테스트가 이루어진다.

다음은 앞서서 언급한 테스트용 파일을 나타낸 것이다.

int test_sqlite3_exec(void)
{
sqlite3 *db;
char *zErr;
int rc = SQLITE_OK;
char *sql;
printf("--- Open sqlite database\n");
rc = sqlite3_open("sample.db", &db);
if(rc != SQLITE_OK) {
printf("Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return rc;
}
printf("--- Create table\n");
sql = "create table dramaheros(id int, name text)";
rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
if (rc == SQLITE_ERROR) {
rc = sqlite3_exec(db, "drop table dramaheros", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
if (zErr != NULL) {
printf("SQL drop error: %s\n", zErr);
}
}
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}else
{
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
}
printf("--- Insert record #1\n");
sql = "insert into dramaheros values (7, 'dr gregory house')";
rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
if (zErr != NULL) {
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
}
printf("--- Insert record #2\n");
sql = "insert into dramaheros values (14, 'dr james wilson')";
rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
if (zErr != NULL) {
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
}
sqlite3_close(db);
printf("--- Close sqlite database\n");
return SQLITE_OK;
}
int test_how_to_query(void)
{
int rc = SQLITE_OK;
int i, j, nrows, ncols;
sqlite3 *db;
sqlite3_stmt *stmt;
char *sql;
const char *tail;
char **result;
char *zErr;
rc = sqlite3_open("sample.db", &db);
if(rc) {
printf("Can't open database\n");
sqlite3_close(db);
return rc;
}
printf("[ Query using sqlite3_prepare() ]\n");
sql = "select * from foxdramas;";
// Prepared Query를 사용한 경우
rc = sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail);
if(rc != SQLITE_OK) {
printf("SQL error: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return rc;
}

rc = sqlite3_step(stmt);
ncols = sqlite3_column_count(stmt);
while(rc == SQLITE_ROW) {
for(i=0; i < ncols; i++) {
printf("'%s' ", sqlite3_column_text(stmt, i));
}
printf("\n");
rc = sqlite3_step(stmt);
}
// Get table query를 사용하여 질의하는 경우
printf("[ Query using get_table_query() ]\n");
rc = sqlite3_get_table(db, sql, &result, &nrows, &ncols, &zErr);
if(rc != SQLITE_OK) {
printf("SQL error: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return rc;
}
printf("** nrows is %d, ncols is %d\n", nrows, ncols);
for(i=0; i < nrows; i++) {
for(j=0; j < ncols; j++) {
printf("%s", result[(i+1)*ncols + j]);
printf(" | ");
}
printf("\n");
}
// Free memory
sqlite3_free_table(result);

sqlite3_finalize(stmt);
sqlite3_close(db);
return SQLITE_OK;
}
int test_simple_scenario1(void)
{
sqlite3 *db;
char *zErr;
int rc = SQLITE_OK;
rc = sqlite3_open("sample.db", &db);
if(rc != SQLITE_OK) {
printf("Can't open database\n");
sqlite3_close(db);
return rc;
}
printf("\n");
// Test table
printf("---> Creating table\n");
rc = sqlite3_exec(db, "create table example (x int, y int, z int)", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
if (rc == SQLITE_ERROR) {
printf("Drop table because it was already existing\n");
rc = sqlite3_exec(db, "drop table dramaheros", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
printf("SQL drop error: %s\n", zErr);
}
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}else
{
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
}
// 트랜잭션 : BEGIN
printf("---> Starting transaction\n");
rc = sqlite3_exec(db, "BEGIN", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
// INSERT 테스트
printf("---> Inserting record\n");
rc = sqlite3_exec(db, "insert into example values (1,2,3)", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
if (zErr != NULL) {
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
}
// 트랜잭션 : COMMIT
printf("---> Commit transaction\n");
rc = sqlite3_exec(db, "COMMIT", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
printf("---> Selecting record \n");
rc = get_table(db, "select * from example");
if(rc != SQLITE_OK) {
printf("SQL get_table error\n");
sqlite3_close(db);
return rc;
}
printf("---> Updating record \n");
rc = sqlite3_exec(db, "update example set x=4, y=5, z=6", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
printf("---> Selecting record \n");
rc = get_table (db, "select * from example");
if(rc != SQLITE_OK) {
printf("SQL get_table error\n");
sqlite3_close(db);
return rc;
}
printf("---> Deleting record\n");
rc = sqlite3_exec(db, "delete from example", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
printf("---> Dropping table\n");
rc = sqlite3_exec(db, "drop table example", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
}
int test_simple_scenario2(void)
{
sqlite3 *db, *db2;
char *zErr;
int rc = SQLITE_OK;
// Test ATTACH/DETACH
printf("---> First DB\n");
rc = sqlite3_open("sample.db", &db);
if(rc != SQLITE_OK) {
printf("Can't open database\n");
sqlite3_close(db);
return rc;
}
printf("---> Second DB\n");
rc = sqlite3_open("test2.db", &db2);
if(rc) {
printf("Can't open database\n");
sqlite3_close(db2);
return rc;
}
printf("---> Create one table\n");
rc = sqlite3_exec(db2, "create table greg(x int, y int, z int)", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
if (rc == SQLITE_ERROR) {
printf("Drop table because it was already existing\n");
rc = sqlite3_exec(db2, "drop table greg", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
printf("SQL drop error: %s\n", zErr);
}
sqlite3_free(zErr);
sqlite3_close(db2);
return rc;
}else
{
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db2);
return rc;
}
}
printf("---> Attaching database foods.db\n");
rc = sqlite3_exec(db, "attach 'test2.db' as test2", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
printf("---> Selecting record from attached database \n");
rc = sqlite3_exec(db, "select * from greg", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
printf("---> Detaching table\n");
rc = sqlite3_exec(db, "detach test2", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
printf("---> Drop one table\n");
rc = sqlite3_exec(db2, "drop table greg", NULL, NULL, &zErr);
if(rc != SQLITE_OK) {
printf("SQL error: %s\n", zErr);
sqlite3_free(zErr);
sqlite3_close(db);
return rc;
}
return SQLITE_OK;
}
int get_table(sqlite3 *db, const char *sql)
{
char **result, *err;
int rc = SQLITE_OK;
int nrows, ncols;
rc = sqlite3_get_table(db,sql,&result,&nrows,&ncols,&err);
if(rc != SQLITE_OK) {
printf("SQL error: %s\n", sqlite3_errmsg(db));
return rc;
}
sqlite3_free_table(result);
return SQLITE_OK;
}

리스트 10. SQLite 테스트 코드
참고로, SQLite C API을 사용했을 때 반환되는 결과 값들은 다음과 같다.
C API를 사용하고 나서, 반환되는 값이 ‘0’이면 오류가 없이 제대로 수행된 것이다.

#define SQLITE_OK 0 /* 성공 */

그러나, ‘0’이 아닌 ‘1’ 이상의 값을 가지면, 다음과 같은 여러 가지 이유의 오류가 발생한 것이므로, 해당 오류에 맞는 예외처리를 해야만 한다.

#define SQLITE_ERROR 1 /* SQL 에러 또는 데이터베이스 연결 실패 */
#define SQLITE_INTERNAL 2 /* SQLite 내부 논리 에러 */
#define SQLITE_PERM 3 /* 액세스 권한 거절 */
#define SQLITE_ABORT 4 /* 어보트(Abort) 에러 */
#define SQLITE_BUSY 5 /* 데이터베이스 파일이 잠김 */
#define SQLITE_LOCKED 6 /* 데이터베이스의 테이블이 잠김 */
#define SQLITE_NOMEM 7 /* 함수 malloc()의 실패 */
#define SQLITE_READONLY 8 /* 읽기 전용 데이터베이스에 쓰기 시도 */
#define SQLITE_INTERRUPT 9 /* sqlite3_interrupt()에 의해서 종료*/
#define SQLITE_IOERR 10 /* 디스크 I/O 에러 발생 */
#define SQLITE_CORRUPT 11 /* 데이터베이스 디스크 이미지 오류 */
#define SQLITE_NOTFOUND 12 /* NOT USED. 테이블이나 레코드 미발견 */
#define SQLITE_FULL 13 /* 데이터베이스가 가득 차서 추가 안 됨 */
#define SQLITE_CANTOPEN 14 /* 데이터베이스 파일을 오픈할 수 없음 */
#define SQLITE_PROTOCOL 15 /* NOT USED. 데이터베이스 잠금 프로토콜 에러 */
#define SQLITE_EMPTY 16 /* 데이터베이스가 비어있음 */
#define SQLITE_SCHEMA 17 /* 데이터베이스 스키마 변경 됨 */
#define SQLITE_TOOBIG 18 /* String 또는 BLOB이 한정된 크기를 벗어남 */
#define SQLITE_CONSTRAINT 19 /* 제약조건 위반에 의한 어보트(Abort) */
#define SQLITE_MISMATCH 20 /* 데이터 타입 불일치 */
#define SQLITE_MISUSE 21 /* 라이브러리가 부정확하게 사용 */
#define SQLITE_NOLFS 22 /* 사용하는 OS 특성이 호스트에서 지원 안 됨 */
#define SQLITE_AUTH 23 /* 인증 거절됨 */
#define SQLITE_FORMAT 24 /* 부가 데이터베이스 포맷 에러 */
#define SQLITE_RANGE 25 /* sqlite3_bind의 2차 파라미터가 범위 벗어남 */
#define SQLITE_NOTADB 26 /* 데이터베이스가 아닌 파일이 오픈 됨 */
#define SQLITE_ROW 100 /* sqlite3_step()가 다른 준비된 행을 가짐 */
#define SQLITE_DONE 101 /* sqlite3_step()가 실행을 종료 함 */

지금까지 살펴본 함수들에 대해서 정리하면 다음과 같다.

？ 데이터베이스 연결과 예약문의 오브젝트들은 C/C++ 인터페이스 루틴들(sqlite3_open(), sqlite3_prepare(), sqlite3_step(), sqlite3_column(), sqlite3_finalize(), sqlite3_close())에 의해서 제어된다.
？ 함수 sqlite3_open()는 SQLite 데이터베이스 파일에 대한 연결을 오픈하고 그 결과로서 데이터벱이스 연결 오브젝트를 반환받는다.
？ 함수 sqlite3_prepare()는 SQL 텍스트를 예약문(Prepared statement) 오브젝트로 변경하고, 그 오브젝트에 대한 포인터를 반환한다.
？ 함수 sqlite3_step()는 sqlite3_prepare() 함수에 의해서 생성된 예약문을 사용하기 위해서 필요하다.
？ 함수 sqlite3_column()는 sqlite3_step() 함수에 의해서 커서가 이동된 예약문의 현재 행의 단일 칼럼 값을 반환한다.
？ 함수 sqlite_finalize()는 sqlite3_prepare() 함수에 의해서 생성된 예약문을 삭제하는 역할을 수행한다.
？ 함수 sqlite3_close()는 sqlite3_open() 함수에 의해서 오픈된 데이터베이스 연결을 종료한다.

필자소개

김형훈(asinayo73@hotmail.com)