[SQLite3] 04. SQLite3 C API 기초

[SQLite3] 04. SQLite3 C API 기초

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

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

1. 데이터베이스 연결

-

SQLIte3 을 사용하려면 연결(connection)을 여는(open) 과정이 필요하다.

이를 위해 제공되는 함수는 sqlite3_open() 이다.

첫번째 인자는 db name, 두번째 인자는 출력인자로 db handle 을 전달한다.

성공시 SQLITE_OK 가 그렇지 않으면 에러값이 발생한다.

-

연결을 닫는 함수는 sqlite3_close() 이다.

인자로 입력인자로 db handle 을 넣는다.

성공시 SQLITE_OK 가 그렇지 않으면 에러값이 발생한다.

-

sqlite3_errmsg() 함수는 db 에서 가장 최근에 발생한 에러 메시지를 출력한다.

-

파일명에 UTF-16 을 사용하려면 sqlite3_open16() 을 사용해야 한다.

2. SQL 구문 실행 - sqlite3_exec() 활용

-

sqlite3_exec(sqlite3*, // db handle

                const char* sql, // sql 문

                int (*callback)(void* data, int ncols, char** values, char** headers), // callback 함수

                void* data, // callback 함수의 첫 번째 인자

                char** errmsg // 에러메시지

                );

3. SQL 구문 실행 -  sqlite3_get_table() 활용

-

sqlite3_exec() 함수를 이용해 SELECT 문을 처리하려면 콜백 함수도 등록해야 하는 등 번거롭다.

이런 문제를 해결해주는 함수가 sqlite3_get_table() 이다.

int sqlite3_get_table(sqlite3* db,

                            const char* zSql, // sql문

                            char*** pazResult, // 결과

                            int* pnRow, // 결과 행 개수

                            int* pnColumn, // 결과 열 개수

                            char** pzErrmsg // 에러 메시지

                            );

-

sqlite3_get_table() 함수를 실행하면 내부적으로 pazResult 인자에 대해 동적으로 메모리를 할당하므로 결과를 처리하고 나면 반드시 sqlite3_free_table() 함수로 사용한 메모리를 해제해야 한다.

sqlite3_free_table(char** result)

4. SQL 구문 실행 - sqlite3_prepare(), sqlite3_step(), sqlite3_finalize() 활용

-

sqlite3_exec() 함수나 sqlite3_get_table() 함수만으로 거의 모든 일을 처리할 수 있지만, 결과집합이 많은 경우에는 비효율적일 수 있다.

특히 콜백 함수의 사용법 자체도 번거롭거니와 동일한 SQL 문을 계속해서 사용하는 경우에는 동일한 문장을 컴파일해야 하는데, 이는 성능상 비효율적인 면이 있다.

SQL 문을 한번만 컴파일한 후 이를 계속 재활용하면 효율이 좀 더 좋다.

SQL 문을 컴파일하는 sqlite3_prepare() 함수와 SQL 문을 실제로 수행하는 sqlite3_step() 함수, 수행하던 구문을 종료하는 sqlite3_finalize() 함수 등 sqlite3_exec() 보다 좀 더 세분화된 함수를 제공한다.

-

sqlite3_step() 을 수행했을 때, 구문이 성공적으로 수행되면 SQLITE_DONE 이 반환되고,

SELECT 문과 같이 결과집합이 있는 경우에는 SQLITE_ROW 가 반환된다.

SQLITE_ROW 가 반환되었을 때는 sqlite3_step() 을 계속 수행하면서 결과값을 가져올 수 있으며, SQLITE_ROW 이외의 값이 나오면 sqlite3_step() 루프를 빠져 나와 sqlite3_finalize() 로 내부적으로 사용했던 메모리 공간을 해제한다.

-

int sqlite3_prepare(sqlite3 *db,

                        const char *zSql, // SQL 문

                        int nByte, // SQL 문 길이

                        sqlite3_stmt **ppStmt, // 출력인자, stmt 구조체

                        const char **pzTail // 출력인자, 수행되고 남은 SQL 문

                        );

-

int sqlite3_step(sqlite3_stmt*);

정상적인 반환값은 SQLITE_ROW, SQLITE_DONE 이며, SQLITE_ROW 는 처리할 결과집합이 더 있으니 sqlite3_step() 함수를 계속 수행해도 된다는 뜻이다. SQLITE_DONE 은 결과집합을 모두 순회했거나 기타 SQL 구문이 정상적으로 수행되었음을 의미한다.

-

int sqlit3_finalize(sqlite3_stmt *pStmt);

-

sqlite3_prepare_v2() 함수가 있다.

sqlite3_prepare() 는 호환성을 위해 남겨둔 함수이고, sqlite3_prepare_v2() 를 사용하는 것이 권장된다.

sqlite3_prepare_v2() 는 SQL 문을 복사해서 유지하고 있기 때문에 sqlite3_finalize() 를 실행하기 전에 언제든지 SQL 문을 확인할 수 있으며, DB 스키마가 변경된 경우에도 sqlite3_step() 에서 자동으로 처리되며, sqlite3_step() 에서 에러가 발생한 경우 SQLITE_ERROR 와 같은 모호한 에러 코드가 아닌 상세한 에러 코드를 받아 볼 수 있다.

-

sqlite3_column_count() 는 결과집합의 컬럼 개수를 반환한다.

int sqlite3_column_count(sqlite3_stmt *pStmt);

-

아래 함수들을 통해 컬럼의 값을 얻어올 수 있다.

컬럼의 index 는 0 부터 시작한다.

const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);

double sqlite3_column_double(sqlite3_stmt*, int iCol);

int sqlite3_column_int(sqlite3_stmt*, int iCol);

const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);

BLOB 의 경우 sqlit3_column_bytes() 함수를 이용해 전달받을 데이터 크기를 파악해야 한다.

int sqlite3_column_bytes(sqlite3_stmt*, int iCol);

그런 다음 파악한 데이터 크기에 따라 메모리를 새로 할당한 후 sqlite3_column_blob() 함수와 memcpy() 등을 이용해 값을 복사해 오면 된다.

수행하지 않은 SQL 구문이 남은 경우

SQL 문이 한 statement 가 아닌 여러 statement 가 있는 경우에는 한 개의 statement 만 처리되고, 나머지는 sql문은 tail 로 전달된다.

컬럼 정보 파악

-

const char *sqlite3_column_name(sqlite3_stmt*, int); // 컬럼 이름 반환

int *sqlite3_column_type(sqlite3_stmt*, int); // 컬럼의 내부 자료형을 반환

const char *sqlite3_column_decltype(sqlite3_stmt*, int); // 컬럼에 정의된 자료형을 반환한다.

const char *sqlite3_column_database_name(sqlite3_stmt*, int); // 컬럼의 데이터베이스 이름을 반환

const char *sqlite3_column_table_name(sqlite3_stmt*, int); // 컬럼의 테이블 이름을 반환

const char *sqlite3_column_original_name(sqlite3_stmt*, int); // 컬럼의 원래 이름을 반환한다.

int *sqlite3_data_count(sqlite3_stmt*); // 컬럼의 데이터 개수를 반환

매개변수를 이용한 질의문 처리

SQLite3 에서는 인자가 많은 경우 매번 sqlite3_prepare_v2() 함수로 컴파일할 필요 없이 각 인자를 ? 로 대체한 후 한 번만 컴파일하고 컴파일된 문장에 인자 값만 바인딩(bind)해서 SQL 문을 더 효율적으로 사용하는 방법을 제공한다.

아래는 bind 함수들이며, 두번째 int 는 매개변수 위치이며 1부터 시작한다.

int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));

int sqlite3_bind_double(sqlite3_stmt*, int, double);

int sqlite3_bind_int(sqlite3_stmt*, int, int);

int sqlite3_bind_null(sqlite3_stmt*, int);

int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));

sqlite3_bind_blob() 이나 sqlite3_bind_text() 에는 마지막 인자로 메모리 관리 방식을 지정해줘야 한다.

이 인자는 보통 SQLITE_STATIC 이나 SQLITE_TRANSIENT 를 전달한다.

SQLITE_STATIC 은 변경되지 않는 값을 의미하므로 해당 값의 주소만 복사해서 사용하고,

SQLITE_TRANSIENT 는 임시 값을 의미하므로 값을 통째로 복사해서 사용한다.

그 밖에도 개발자가 직접 사용자 정의 메모리 해제 함수를 정의해서 쓸 수도 있다.

-

sqlite3_prepare_v2() 함수를 수행한 SQL 문을 인자만 변경해서 다시 사용하려면 sqlite3_reset() 함수로 해당 sqlite3_stmt 구조체를 초기화해야 한다.

그렇지 않은 경우에 메모리 누수나 오작동이 일어날 수 있다.

int sqlite3_reset(sqlite3_stmt *pStmt);

색인을 이용한 질의문 처리

여러개의 물음표가 있는 경우 매개변수의 순서가 혼동되는 문제가 있어 SQLite3 에서는 매개변수의 색인을 더 자유롭게 지정하는 방법을 제공한다.

물음표 뒤에 자신이 원하는 숫자를 붙이는 것이다.

예를 들어 INSERT INTO test(id) VALUES (?777)” 로 컴파일했다면 sqlite3_bind_int(state, 777); 과 같은 방식으로 매개변수를 바인딩 할 수 있다.

예를 들어 INSERT INTO test(id, value) VALUES (?777, ?777)” 로 컴파일했다면 sqlite3_bind_int(state, 777); 로 2개 값을 한번에 바인딩 할 수도 있다.

이름을 이용한 질의문 처리

매개변수에 이름을 붙일 수 있다. 매개변수 이름을 붙이는 것은 ?777 의 형태 대신 :title 과 같이 : 다음 매개변수 이름을 써주면 된다. 그리고 아래 함수를 통해 해당 매개변수의 index 를 가져올 수 있다.

int sqlite3_bind_parameter_index(sqlite3_stmt*, const char *zName);

ex)

sql = INSERT INTO bookList(ISBN, title, Author, Price) VALUES(:isbn, :title, :author, :price)”;

rc = sqlite3_prepare_v2(db, sql, strlen(sql), &state, &tail);

.. // 에러 코드 처리

sqlite3_bind_text(state, sqlite3_bind_parameter_index(state, “:isbn”), ISBN, strlen(ISBN), SQLITE_STATIC);

…

?, bind, c api, Callback, connection, db handle, memcpy, sqkute3, SQL, sql compile, sql 컴파일, SQLite, sqlite3_bind_blob, sqlite3_bind_double, sqlite3_bind_int, sqlite3_bind_null, sqlite3_bind_text, sqlite3_close, sqlite3_column_blob, sqlite3_column_bytes, sqlite3_column_count, sqlite3_column_database_name, sqlite3_column_decltype, sqlite3_column_double, sqlite3_column_int, sqlite3_column_name, sqlite3_column_original_name, sqlite3_column_table_name, sqlite3_column_text, sqlite3_column_type, sqlite3_data_count, sqlite3_errmsg, sqlite3_exec, sqlite3_finalize, sqlite3_free_table, sqlite3_get_table, sqlite3_open, sqlite3_open16, sqlite3_prepare, sqlite3_prepare_v2, sqlite3_reset, sqlite3_step, sqlite3_stmt, sqlite_done, sqlite_ok, sqlite_row, sqlite_static, sqlite_transient, tail, 데이터베이스 연결, 매개변수 바인딩, 매개변수를 이용한 질의문 처리, 메모리 관리 방식, 바인딩, 색인을 이용한 질의문 처리, 이름을 이용한 질의문 처리