[Tizen] 타이젠 앱에서 SQLite를 이렇게 사용해볼까요?

한밤 중 1시 51분,

잘까 말까 잠시 망설이다가,

DB에 대한 포스팅을 마무리하기 위해 세수를 하고 왔습니다.

이 포스팅에는 대단한 스키마가 나오는 것도 아니고,

어마무시한 쿼리문도 나오지 않습니다.

그저 비몽사몽 간에,

앱단에서 사용할만한 함수 몇가지를 공유하고자 합니다.

타이젠에서 공식적으로 지원하는 DB는 SQLite3입니다.

(참고, "Tizen 플랫폼 DB 엿보기-", http://storycompiler.tistory.com/25)

오래전부터 SQLite였고 그 기조는 당분간 바뀌지 않을겁니다.

따라서 SQLite를 다른 DB로 포팅할 일도 없겠군요.

그렇지만, 순전히 앱사이드에서 sqlite3를 덕지덕지 소스에 붙여 놓고 싶지도 않습니다.

sqlite3_open() / sqlite3_close()를 매번 수행하며,

그 때마다 온갖 예외처리를 하다보면,

분명 비슷한 루틴이 반복될 것이고,

그러는 와중에 버그라는 친숙한 녀석을 만나게 될겁니다.

따라서 sqlite3를 앱 내부 함수에 캡슐로 감춰서

조금이라도 노출을 막고,

조금이라도 쉽게 사용하고자 합니다.

static struct {
    sqlite3 *db;
} db_info = {
    .db = NULL,
};

우선, sqlite3_open()으로 나오는 db 핸들은 static 전역 변수로 선언해볼까요?

전역 변수는 매우 위험하여 가급적이면 사용하진 않지만,

- 개발하고자 하는 앱은 오직 한 프로세스에서만 사용하고,

- 단일스레드로 동작하며,

- reentrant 따위는 일어날리도 없기에,

단일 파일에서만 접근할 수 있는 static 전역 변수로 선언하곤 합니다.

이로써 모든 함수의 첫번째 인자(sqlite3 *db)는 생략할 수 있게 된 셈이지요.

덕분에 API 사용이 간단해졌습니다.

하지만, 이러한 제한적인 상황 외에 전역변수를 남용한다면,

숨겨진 버그를 중요한 순간마다 튀어나와 정신건강에 치명적인 독이 되고 맙니다. :)

특히, 라이브러리에서 전역변수는 고민에 고민을 거듭하여 제거하는게 좋겠지요.

db_open()은 이전 포스팅에서 설명한 바 있습니다.

Tizen 플랫폼에서 앱의 데이터 저장공간을 app_get_data_path()로 얻어와 DB 파일을 저장합니다.

(참고, "Tizen 앱 DB는 언제 어디에 초기화할까", http://storycompiler.tistory.com/29)

HAPI appl_error_e db_open(void)
{
    char *path = NULL;
    char db_file[FILE_LEN] = {0, };
    int ret = SQLITE_OK;

    path = app_get_data_path();
    retv_if(!path, APPL_ERROR_FAIL);
    
    snprintf(db_file, sizeof(db_file), "%s/%s", path, APP_DB_FILE);

    ret = sqlite3_open(db_file, &db_info.db);
    if (SQLITE_OK != ret) {
        _E("%s", sqlite3_errmsg(db_info.db));
        free(path);
        return APPL_ERROR_FAIL;
    }

    free(path);
    return APPL_ERROR_NONE;
}

HAPI void db_close(void)
{
    if (!db_info.db) {
        _D("DB is already NULL");
        return;
    }

    sqlite3_close(db_info.db);
    db_info.db = NULL;
}

db_open()과 db_close()는 db 핸들을 전역으로 뽑아놨기 때문에 함수 패러미터도 없습니다.

따라서 원하는 시점에 인자에 대한 고민없이 open / close 하면 됩니다.

편하군요~

HAPI sqlite3_stmt *db_prepare(const char *query)
{
    sqlite3_stmt *stmt = NULL;
    int ret = SQLITE_OK;

    retv_if(!query, NULL);

    ret = sqlite3_prepare_v2(db_info.db, query, strlen(query), &stmt, NULL);
    if (SQLITE_OK != ret) {
        _E("%s, %s", query, sqlite3_errmsg(db_info.db));
        return NULL;
    }

    return stmt;
}

HAPI appl_error_e db_next(sqlite3_stmt *stmt)

{
    int ret = SQLITE_OK;

    retv_if(!stmt, APPL_ERROR_FAIL);

    ret = sqlite3_step(stmt);
    switch (ret) {
    case SQLITE_ROW:
        return APPL_ERROR_NONE;
    case SQLITE_DONE:
        return APPL_ERROR_NO_DATA;
    default:
        _E("%s", sqlite3_errmsg(db_info.db));
        return APPL_ERROR_FAIL;
    }

    return APPL_ERROR_NONE;
}

HAPI appl_error_e db_reset(sqlite3_stmt *stmt)
{
    int ret = SQLITE_OK;

    retv_if(!stmt, APPL_ERROR_INVALID_PARAMETER);

    ret = sqlite3_reset(stmt);
    if (SQLITE_OK != ret) {
        _E("%s", sqlite3_errmsg(db_info.db));
        return APPL_ERROR_FAIL;
    }

    sqlite3_clear_bindings(stmt);

    return APPL_ERROR_NONE;
}

db_prepare(), db_next(), db_reset()은 sqlite3_prepare_v2(), sqlite3_next(), sqlite3_reset() 기능을 수행하기 위한 함수입니다.

우선, db_prepare()에서 패러미터로 전달받은 쿼리문을 파싱하여 sqlite3 *stmt를 리턴하죠.

stmt는 db_next()에 인자로 들어가서 실행됩니다.

db_reset()에서는 stmt를 재사용하기 위해 bind된 인자가 있으면 clear 합니다.

반드시 clear를 하고 다시 bind를 해야합니다.

어렴풋 reset을 제대로 하지 않아서 삽질을 했던 기억이 떠오르네요.

여기서 bind는 이어서 설명하는 db_bind_xxxx()함수와 연결됩니다.

HAPI appl_error_e db_bind_bool(sqlite3_stmt *stmt, int idx, bool value)
{
    int ret = SQLITE_OK;

    retv_if(!stmt, APPL_ERROR_FAIL);

    ret = sqlite3_bind_int(stmt, idx, (int) value);
    if (SQLITE_OK != ret) {
        _E("%s", sqlite3_errmsg(db_info.db));
        return APPL_ERROR_FAIL;
    }

    return APPL_ERROR_NONE;
}


HAPI appl_error_e db_bind_int(sqlite3_stmt *stmt, int idx, int value)
{
    int ret = SQLITE_OK;

    retv_if(!stmt, APPL_ERROR_FAIL);

    ret = sqlite3_bind_int(stmt, idx, value);
    if (SQLITE_OK != ret) {
        _E("%s", sqlite3_errmsg(db_info.db));
        return APPL_ERROR_FAIL;
    }

    return APPL_ERROR_NONE;
}


HAPI appl_error_e db_bind_double(sqlite3_stmt *stmt, int idx, double value)
{
    int ret = SQLITE_OK;

    retv_if(!stmt, APPL_ERROR_FAIL);

    ret = sqlite3_bind_double(stmt, idx, value);
    if (SQLITE_OK != ret) {
        _E("%s", sqlite3_errmsg(db_info.db));
        return APPL_ERROR_FAIL;
    }

    return APPL_ERROR_NONE;
}


HAPI appl_error_e db_bind_str(sqlite3_stmt *stmt, int idx, const char *str)
{
    int ret = SQLITE_OK;

    retv_if(!stmt, APPL_ERROR_FAIL);
    retv_if(!str, APPL_ERROR_FAIL);

    ret = sqlite3_bind_text(stmt, idx, str, strlen(str), SQLITE_TRANSIENT);
    if (SQLITE_OK != ret) {
        _E("%s", sqlite3_errmsg(db_info.db));
        return APPL_ERROR_FAIL;
    }

    return APPL_ERROR_NONE;
}

바인드는 db_prepare()에서 파싱한 쿼리문 중에 '?'로 처리한 부분에 치환되어 들어갑니다.

문자열 쿼리문을 더 빨리 처리하기 위해,

확정된 부분은 db_prepare()로 먼저 파싱하고,

확정할 수 없는 부분은 sqlite3_bind_xxxx()로 차후에 입력하게 됩니다.

예를 들어,

query = "select sequence from bookmarks where parent=? order by sequence desc";

(참고, git://review.tizen.org/apps/web/browser, src/database/browser-bookmark-db.cpp)

위와 같은 쿼리문의 where 절에 '?'로 처리된 부분은,

sqlite3_bind_int로 차후에 채워넣게 됩니다.

단, 항상 헛갈리는 부분인데 bind에 들어가는 index는 0이 아닌 1로 시작합니다. 응?

위의 예에서 parent의 '?'에 바인드하기 위해서는 '0'이 아니라 '1'을 index로 넣어야 합니다.

오래 전에 SQLite 서적을 보며 1부터 시작하는 납득할 만한 이유를 찾았던 것으로 기억하는데,

더 이상 기억이 나지 않는 것으로 보아 충분히 납득하지 못했었나 봅니다.

HAPI bool db_get_bool(sqlite3_stmt *stmt, int index)
{
    retv_if(!stmt, false);
    return (bool) sqlite3_column_int(stmt, index);
}


HAPI int db_get_int(sqlite3_stmt *stmt, int index)
{
    retv_if(!stmt, 0);
    return sqlite3_column_int(stmt, index);
}


HAPI int db_get_double(sqlite3_stmt *stmt, int index)
{
    retv_if(!stmt, 0);
    return sqlite3_column_double(stmt, index);
}


HAPI const char *db_get_str(sqlite3_stmt *stmt, int index)
{
    retv_if(!stmt, NULL);
    return (const char *) sqlite3_column_text(stmt, index);
}

위의 함수군은 select 쿼리의 결과로 나온 값들을 얻기 위해 사용합니다.

index는 0부터 차례대로 카운팅됩니다.

bind처럼 1부터 시작하지 않고 0부터 시작하니 헛갈리지 마세요.

HAPI appl_error_e db_finalize(sqlite3_stmt *stmt)
{
    int ret = SQLITE_OK;

    retv_if(!stmt, APPL_ERROR_INVALID_PARAMETER);

    ret = sqlite3_finalize(stmt);
    if (SQLITE_OK != ret) {
        _E("%s", sqlite3_errmsg(db_info.db));
        return APPL_ERROR_FAIL;
    }

    return APPL_ERROR_NONE;
}

stmt를 더 이상 사용할 필요가 없다면 db_finalize를 해야합니다.

이로써 db_prepare()에서 생성된 stmt의 라이프사이클이 드디어 종료됩니다.

db_prepare()로 핸들을 생성하였으면,

반드시 db_finalize()까지 진행해주세요.

HAPI appl_error_e db_exec(const char *query)
{
    sqlite3_stmt *stmt = NULL;

    retv_if(!query, APPL_ERROR_INVALID_PARAMETER);

    stmt = db_prepare(query);
    retv_if(!stmt, APPL_ERROR_FAIL);

    goto_if(APPL_ERROR_FAIL == db_next(stmt), ERROR);
    goto_if(APPL_ERROR_FAIL == db_finalize(stmt), ERROR);

    return APPL_ERROR_NONE;

ERROR:
    if (stmt) db_finalize(stmt);
    return APPL_ERROR_FAIL;
}

db_exec()는 create / drop / insert / update / delete 처럼,

유의미한 실행결과를 얻을 필요가 없는 쿼리문에 사용합니다.

쿼리문과 db_exec()만 사용하면 얼마든지 원하는 쿼리문을 수행할 수 있습니다.

하지만, 너무나 쉽게 사용할 수 있다보니 해킹의 위험에 노출될 수도 있죠.

개발자가 최초에 쿼리문을 하나 만들어 sqlite3_exec()에 넣었다고 해보죠.

해커가 쿼리문의 "%s" 같은 부분을 조작하여 다중 쿼리문으로 바꿔치기하면,

입력된 다중 쿼리문이 sqlite3_exec()에 의해 모두 실행됩니다.

난리나게 되는 거죠.

따라서 언제나 오직 하나의 쿼리문만 실행할 수 있도록,

db_exec() 내부에서 prepare, next, finalize를 수행하도록 변경하였습니다.

next는 하나의 쿼리문만 수행하도록 설계가 되어 있답니다.

HAPI appl_error_e db_begin_transaction(void)
{
    int ret = SQLITE_BUSY;

    while (1) {
        ret = sqlite3_exec(db_info.db, "BEGIN IMMEDIATE TRANSACTION", NULL, NULL, NULL);
        if (SQLITE_BUSY != ret) {
            break;
        }
        /* FIXME : we have to fix this sleep */
        sleep(1);
    }

    if (SQLITE_OK != ret) {
        _E("sqlite3_exec() Failed(%d)", ret);
        return APPL_ERROR_FAIL;
    }

    return APPL_ERROR_NONE;
}



HAPI appl_error_e db_end_transaction(void)
{
    int ret = SQLITE_OK;

    while (1) {
        ret = sqlite3_exec(db_info.db, "COMMIT TRANSACTION", NULL, NULL, NULL);
        if (SQLITE_BUSY != ret) {
            break;
        }
        /* FIXME : we have to fix this sleep */
        sleep(1);
    }

    if (SQLITE_OK != ret) {
        _E("sqlite3_exec() Failed(%d)", ret);
        return APPL_ERROR_FAIL;
    }

    return APPL_ERROR_NONE;
}

트랜잭션이 필요할 수도 있겠네요.

BEGIN TRANSACTION - COMMIT TRANSACTION 사이에 벌어지는 디비 루틴은 한 번에 처리됩니다.

다만, 상기 함수에서 트랜잭션 쿼리를 수행할 때,

SQLITE_BUSY가 나는 경우 sleep()을 걸어놓았습니다.

sleep()은 프로세스 전체를 멈추게 할 수도 있으니 신중하게 사용해야 합니다.

프로세스의 디자인에 따라 이 부분은 각자의 mainloop()에 사용할 수 있는 timer 등으로 치환할 수도 있습니다.

이제는 정말 취침에 들어가야겠네요.

집중력이 바닥났습니다.

끝_

* 2015. 6. 2 정신차리고 일어나서 한밤중에 멋대로 휘갈긴 문장을 수정.