[SQLite3] 05. SQLite3 C API 고급

[SQLite3] 05. SQLite3 C API 고급

이 글은 "빠르게 활용하는 모바일 데이터베이스 SQLite3” 이란 글을 보며 필요한 내용만 정리한 글입니다.

자세한 내용은 책을 구매해서 보세요

1. 잠금 상태 확인 - sqlite3_busy_handler()

-

하나의 스레드나 프로세스에서 exclusive lock 을 걸고 있는 상태에서 다른 스레드나 프로세스가 잠금을 획득하려고 하면 에러가 발생한다.

이 때 sqlite3_busy_handler() 함수를 이용하면 에러가 발생하지 않고 동기화를 진행할 수 있게 추가적인 처리를 위한 콜백 함수를 등록할 수 있다.

int sqlite3_busy_handler(sqlite3*, int(*)(void*, int), void*);

lock 을 잡을 수 없을 때 해당 함수가 호출된다.

-

sqlite3_busy_timeout() 함수를 이용하면 table lock 이 걸린 경우 timeout 값에 도달할 때까지 여러번 busy handler 가 불리는 것을 연기하면서 대기한다

int sqlite3_busy_timeout(sqlite3*, int ms);

-

busy handler 에서 0 을 반환하면 “database is locked” 라는 에러가 발생하지만 0이 아닌 값을 반환할 때는 재시도한다.

또한 콜백 함수의 두 번째 인자는 재시도 횟수를 나타내므로 아래와 같은 코드를 작성하면 10번 재시도한다.

static int busy(void *handle, int nTry){

    Slee(10);

    return 10 - nTry;

}

2. 커밋 여부 확인 - sqlite3_commit_hook()

-

sqlite3_commit_hook() 함수를 이용하면 SQL 문이 커밋되는 시점에 대한 콜백 함수를 등록할 수 있다.

이 API 를 이용하면 커밋되는 시점에 별도의 로그 파일을 기록하는 등의 작업을 추가적으로 수행할 수 있다.

void *sqlite3_commit_hook(sqlite3*, int(*)(void*), void*);

commit hook 에서의 반환값이 0 일 경우 커밋을 그대로 수행하지만, 그 외의 값일 경우 롤백으로 처리한다.

롤백으로 처리될 때는 아래 다루는 롤백 콜백 함수가 불리게 된다.

3. 롤백 여부 확인 - sqlite3_rollback_hook()

-

sqlite3_rollback_hook() 함수를 이용하면 롤백에 대한 콜백 함수도 등록할 수 있다.

void *sqlite3_rollback_hook(sqlite3*, void(*)(void *), void*);

4. 업데이트 여부 확인 - sqlite3_update_hook()

-

sqlite3_update_hook() 함수는 INSERT, UPDATE, DELETE 등으로 테이블에 변경사항이 생겼을 때 콜백 함수를 처리하도록 도와준다.

void *sqlite3_update_hook(sqlite3*,

        void(*)(void* data, int action, char const* db_name, char const* table_name, sqlite3_int64 rowid),

        void* data

);

action 값은 아래 값 중 하나이다.

SQLITE_DELETE : 9

SQLITE_INSERT : 18

SQLITE_UPDATE : 23

5. 권한 체크 - sqlite3_set_authorizer()

-

sqlite3_set_authorizer() 함수를 이용해 모든 동작에 대해 권한을 체크할 수 있다.

int sqlite3_set_authorizer( sqlite3*, 

        int (*xAuth)(void* data, int code, const char* event_arg1, const char* event_arg2, const char* db_name, const char* trigger_or_view_name),

        void *pUserData

);

code 값은 아래와 같다.

SQLITE_CREATE_INDEX : 1

SQLITE_CREATE_TABLE : 2

SQLITE_CREATE_TEMP_INDEX : 3

SQLITE_CREATE_TEMP_TABLE : 4

SQLITE_CREATE_TEMP_TRIGGER : 5

SQLITE_CREATE_TEMP_VIEW : 6

SQLITE_CREATE_TRIGGER : 7

…..

콜백함수는 SQLITE_OK, SQLITE_IGNORE, SQLITE_DENY 가운데 하나를 반환한다.

SQLITE_OK 가 반환되면 해당 동작을 수행하며, SQLITE_IGNORE 를 반환하면 동작을 수행하지는 않지만 SQL 문을 계속 수행한다. SQLITE_DENY 를 반환하면 권한 에러를 내며 SQL 문의 수행을 중단한다.

6. 이스케이프 처리 - sqlite3_mprintf()

-

char *sqlite3_mprintf(const char* sql, …);

위 함수는 printf() 와 함수 원형은 물론이고 실제 사용법도 비슷하다.

다만 내부적으로 메모리를 할당하므로 함수를 사용하고 나면 sqlite3_free() 함수로 메모리를 해제해줘야 한다.

그리고 %s 대신 %q 를 사용한다. 따옴표도 생략하려면 %Q 를 사용하면 된다.

위 함수를 통해 escape 를 해주어야 escape 공격을 막을 수 있다.

7. 가변인자를 이용한 이스케이프 처리 - sqlite3_vmprintf()

-

sqlite3_vmprintf() 함수는 sqlite3_mprintf() 함수와 거의 동일하게 동작한다.

차이점은 가변 인자를 처리한다는 것뿐.

char *sqlite3_vmprintf(const char* sql, va_list);

8. 사용자 정의 함수 - sqlite3_create_function()

-

sqlite3_create_function() 을 이용하면 라이브러리 내부에 사용자 정의 함수를 정의하고 사용할 수 있다.

int sqlite3_create_function(sqlite3* db,

        const char *zFunctionName,

        int nArg, // 인자의 수. 인자가 가변인 경우 -1

        int eTextRep, // 인코딩, 문자열 인코딩에 따라 구현이 달라지는 경우 사용, SQLITE_UTF8, SQLITE_UTF16, SQLITE_ANY 등의 값

        void *pApp,

        void (*xFunc)(sqlite3_context*, int, sqlite3_value**), // 수행할 함수. 이 녀석이 정의되면 xStep, xFinal 은 NULL

        void (*xStep)(sqlite3_context*, int, sqlite3_value**), // 여러행에 대해 연산을 해야 하는 경우 사용. 이 녀석이 정의되면 xFunc 는 NULL, xFinal 을 보통 함께 정의한다

        void (*xFinal)(sqlite3_context*) // 모든 행을 수행한 다음 xStep 을 이용해 계산한 결과를 반환할 때 호출되는 함수

);

ex) 

sqlite3_create_function(db, “new”, -1, SQLITE_UTF8, NULL, new_function, NULL, NULL);

void new_function(sqlite3_context* ctx, int nargs, sqlite3_value** values){

    // .. do sth

}

- 

ex)

sqlite3_create_function(db, “total”, -1, SQLITE_UTF8, NULL, NULL, step_function, final_function);

typedef struct{

    int total;

} data;

void step_function(sqlite3_context* ctx, int ncols, sqlite3_value** values){

    int tmp;

    data* pData = (data*)sqlite3_aggregate_context(ctx, sizeof(data));

    if( sqlite3_aggregate_count(ctx) == 1 )

        pData->total = 0;

    if( sqlite3_value_type(values[0]) != SQLITE_INTEGER ){

        char* errmsg = “this function supports only integer”;

        sqlite3_result_error(ctx, errmsg, strlen(errmsg));

        return;

    }

    tmp = sqlite3_value_int(values[0]);

    pData->total += tmp;

}

void final_function(sqlite3_context* ctx){

    data* pData = (data*)sqlite3_aggregate_context(ctx, sizeof(data));

    sqlite3_result_int(ctx, pData->total);

}

xStep 콜백 함수는 각 행마다 호출되어 연산을 수행한다.

매번 호출될 때마다 컨텍스트를 유지하기 위해 sqlite3_aggregate_context() 함수를 사용한다.

이 함수를 처음 호출할 때만 메모리를 할당하고, 그 다음부터는 할당된 메모리를 사용하기만 한다.

여기서 할당된 메모리는 xFinal 콜백 함수가 종료되고 난 후 해제된다.

void *sqlite3_aggregate_context(sqlite3_context* ctx, int nBytes);

사용자 정의 함수에서 모든 경우를 다 처리하면 좋지만, 에러가 발생하는 경우에는 동작을 중단하고 사용자에게 에러 메시지를 보여줘야 한다.

이 때 사용되는 함수가 sqlite3_result_error() 이다.

void sqlite3_result_error(sqlite3_context*, const char*, int);

xFinal 콜백 함수는 모든 행을 순회한 후 불리는 콜백함수이다.

void xFinal(sqlite3_context* ctx);

xFinal() 내부에서는 그동안 컨텍스트를 이용해 연산한 결과를 반환하면 된다.

void sqlite3_result_int(sqlite3_context*, int);

void sqlite3_result_int64(sqlite3_context*, sqlite3_int64);

void sqlite3_result_blob(sqlite3_context*, const void*, int, void(*)(void*));

void sqlite3_result_double(sqlite3_context*, double);

void sqlite3_result_null(sqlite3_context*);

void sqlite3_result_text(sqlite3_context*, const char*, int, void(*)(void*));

9. 사용자 정의 정렬 함수 - sqlite3_create_collation()

-

내장 정렬 함수뿐 아니라 사용자가 직접 사용자 정의 정렬 함수를 지정해 정렬 순서를 조정할 수 있다.

int sqlite3_create_collation(sqlite3*,

        const char* zName,

        int eTextRep, // 데이터 인코딩

        void*, // data, compare 의 첫번째 인자로 전달된다

        int(*xCompare)(void*, int, const void*, int, const void*) // 비교 함수

}

int xCompare(void* data, int len1, const void* in1, int len2, const void* in2);

compare 에서는 첫번째 값이 더 작으면 -1, 값이 같으면 0, 첫번째 값이 더 크면 1을 반환

ex)

int myCompare(void* data, int len1, const void* in1, int len2, const void* in2){

    int i, j, sum1, sum2;

    char* str1 = (char*)in1;

    char* str2 = (char*)int2;

    sum1 = sum2 = 0;

    for(i=0; i < len1-1; i++){

        sum += abs(str1[i] - str1[i+1]);

    }

    for(j=0; j < len2-1; j++){

        sum2 += abs(str2[j] - str2[j+1]);

    }

    return sum1 < sum2 ? -1:1;

}

10. SQL 문의 종료 여부 확인 - sqlite3_complete()

-

sqlite3_complete() 는 SQL 문이 끝났는지, 더 받아들일 필요가 있는지 판단한다.

SQL 문이 끝났을 경우 1을 반환하고 입력이 더 필요한 경우에는 0을 반환한다.

이 함수는 sqlite3_prepare_v2() 나 sqlite3_exec() 와 같은 함수가 실행되기 전에 SQL 문이 정상적으로 종료됐는지 여부를 확인할 수 있다.\

int sqlite3_complete(const char* sql);

세미콜론으로 끝나는 SQL 문의 경우 1이 반환되고, 없는 경우 0이 반환된다.

11. 자동 커밋 모드 확인 - sqlite3_get_autocommit()

-

sqlite3_get_autocommit() 함수는 자동 커밋(auto commit) 모드의 활성화 여부를 알려준다.

자동 커밋 모드인 경우에는 1을 반환하고 자동 커밋 모드가 아닌 경우에는 0을 반환한다.

BEGIN 구문을 만나면 자동 커밋 모드가 해제되며, COMMIT 이나 ROLLBACK 구문에 의해 다시 자동 커밋 모드가 활성화된다.

int sqlite3_get_autocommit(sqlite3*);

12. 최근 ROWID 확인 - sqlite3_last_insert_rowid()

-

sqlite3_last_insert_rowid() 함수는 가장 최근에 입력된 ROWID 를 반한한다.

ROWID 는 sqlite_master 테이블의 OID 혹은 _ROWID_ 로도 확인할 수 있으며, 내부적으로 사용하는 기본키이다.

해당 프로세서나 스레드에서 sqlite3_open() 을 실행한 후부터 (해당 세션)에서 가장 최근에 입력된 값을 반환한다.

sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*);

13. 라이브러리 버전 확인 - sqlite3_libversion()

-

sqlite_libversion() 함수는 버전 정보를 문자열로 반환하고, sqlite3_libversion_number() 함수는 버전 정보를 정수형으로 반환한다.

const char* sqlite3_libversion(void);

int sqlite3_libversion_number(void);

14. 메모리 사용량 체크 - sqlite3_memory_used()

-

sqlite3_int64 sqlite3_memory_used(void);

sqlite3_int64 sqlite3_memory_highwater(int resultFlag);

sqlite3_memory_used() 는 현재 사용중인 메모리 양을 반환한다.

sqlite3_malloc(), sqlite3_realloc() 등으로 메모리 할당하고 나서 sqlite3_free() 로 해제하지 않은 메모리의 양을 알려준다.

sqlite3_memory_highwater() 는 메모리가 가장 많이 사용됐을 때의 양을 알려준다.

resetFlag 를 true 로 주면 최대 사용량을 초기화한다.

15. SQL 문 확인 - sqlite3_sql()

-

sqlite3_step() 함수를 수행하던 도중에 어떤 SQL 문을 수행 중인지 확인해야 할 때가 있다.

이 때 사용하는 함수이다.

prepare 를 수행할 때 복사한 SQL 문이 반환된다.

const char* sqlite3_sql(sqlite3_stmt *pStmt);

16. 연결 공유 - sqlite3_enable_shared_cache()

-

sqlite3_enable_shared_cache() 함수는 공유 캐시 모드(shared cache mode)를 설정하는 함수이다.

인자에 true 를 넣으면 공유 캐시 모드를 설정하고, 기본값인 false 를 전달하면 설정 해제한다.

int sqlite3_enable_shared_cache(int);

이는 한 스레드에서 여러 연결을 사용할 때 유용하다.

여러 개의 연결을 각각 생성하지 않고 공유해서 사용하면 메모리 사용량과 I/O 를 줄일 수 있다.

이 함수는 SQLite3 3.3.0 버전부터 사용 가능해졌으며, 하나의 스레드 내에서만 연결을 공유할 수 있었다.

3.5.0 부터는 여러 스레드 간의 연결 공유가 가능해졌다.

commit callback, commit hook return value, exclusive lock, lock callback, rollback, rowid 확인, shared cache mode, sql 문의 종료 여부 확인, SQLite3 C API 고급, sqlite3_aggregate_context, sqlite3_busy_handler, sqlite3_busy_timeout, sqlite3_commit_hook, sqlite3_complete, sqlite3_create_collation, sqlite3_create_function, sqlite3_enable_shared_cache, sqlite3_free, sqlite3_get_autocommit, sqlite3_last_insert_rowid, sqlite3_libversion, sqlite3_libversion_number, sqlite3_memory_highwater, sqlite3_memory_used, sqlite3_mprintf, sqlite3_result_blob, sqlite3_result_double, sqlite3_result_error, sqlite3_result_int, sqlite3_result_int64, sqlite3_result_null, sqlite3_result_text, sqlite3_rollback_hook, sqlite3_set_authroizer, sqlite3_sql(), sqlite3_update_hook, sqlite3_vmprintf, SQLITE_DENY, SQLITE_IGNORE, sqlite_ok, sql문 확인, 가변인자를 이용한 이스케이프 처리, 공유 캐시 모드, 권한 체크, 내장 정렬 함수, 라이브러리 버전 확인, 롤백 여부 확인, 메모리 사용량 체크, 사용자 정의 정렬 함수, 사용자 정의 함수, 업데이트 여부 확인, 연결 공유, 이스케이프 처리, 자동 커밋 모드 확인, 잠금 상태 확인, 커밋 여부 확인