*이 포스트는 원작자인 Jan Bodnar 님의 허락을 받아 Zetcode.com 의 글 “SQLite C tutorial”을 번역한 것입니다. (원문보기 original) 저작권에 유의하시기 바랍니다.
*글이 길기 때문에 내용을 1: 기본, 2: 심화로 나눠 연재합니다. 이미지 같은 blob 데이터 다루는 법, 메타 데이터, 트랜잭션 등 말그대로 심화적인 내용을 분리한 것입니다.
SQLite 데이터베이스 심화편
SQLite 데이터베이스 C/C++에서 사용하기 시리즈의 이전 편에서는 기본적인 내용에 대해서 알아봤습니다. SQLite 툴의 사용법과 C 코드로 쿼리를 실행하는 방법 정도만 알아도 사용하는데 큰 문제는 없죠. 이번 편은 약간 심화적인 부분에 대해서 다룹니다. 정확히는 이미지같은 Blob(Binary Large OBject)을 다루는 방법, 메타데이터, 트랜잭션에 대한 내용입니다. 바로 시작합니다.
이미지 데이터 쓰기
이제 SQLite 데이터베이스에 이미지 데이터를 넣어보겠습니다. 그런데 먼저 시작하기 전에 언급할 것이 있습니다. 데이터베이스로 이미지 데이터를 다루는 것이 옳은가에 대해서는 논란이 있다는 점입니다. 여기서는 이미지 데이터를 다루면서 일어날 수 있는 기술적 문제들에 대해서는 다루지 않겠습니다. 대신 그저 어떻게 하는지에 대해서만 설명합니다.
sqlite> CREATE TABLE Images(Id INTEGER PRIMARY KEY, Data BLOB);먼저 예제를 위해 SQLite 툴에서 Images 라는 테이블을 하나 만들어줍니다. 이미지 데이터를 위해서는 BLOB 이라는 데이터 타입을 사용하는데, Binary Large OBject의 준말로 큰 바이너리 데이터를 뜻합니다.
#include <sqlite3.h>
#include <stdio.h>
int main(int argc, char **argv)
{
FILE *fp = fopen("woman.jpg", "rb");
if (fp == NULL)
{
fprintf(stderr, "Cannot open image file\n");
return 1;
}
fseek(fp, 0, SEEK_END);
if (ferror(fp))
{
fprintf(stderr, "fseek() failed\n");
int r = fclose(fp);
if (r == EOF)
{
fprintf(stderr, "Cannot close file handler\n");
}
return 1;
}
int flen = ftell(fp);
if (flen == -1)
{
perror("error occurred");
int r = fclose(fp);
if (r == EOF)
{
fprintf(stderr, "Cannot close file handler\n");
}
return 1;
}
fseek(fp, 0, SEEK_SET);
if (ferror(fp))
{
fprintf(stderr, "fseek() failed\n");
int r = fclose(fp);
if (r == EOF)
{
fprintf(stderr, "Cannot close file handler\n");
}
return 1;
}
char data[flen+1];
int size = fread(data, 1, flen, fp);
if (ferror(fp))
{
fprintf(stderr, "fread() failed\n");
int r = fclose(fp);
if (r == EOF)
{
fprintf(stderr, "Cannot close file handler\n");
}
return 1;
}
int r = fclose(fp);
if (r == EOF)
{
fprintf(stderr, "Cannot close file handler\n");
}
sqlite3 *db;
char *err_msg = 0;
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK)
{
fprintf(
stderr,
"Cannot open database: %s\n",
sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
sqlite3_stmt *pStmt;
char *sql = "INSERT INTO Images(Data) VALUES(?)";
rc = sqlite3_prepare(db, sql, -1, &pStmt, 0);
if (rc != SQLITE_OK)
{
fprintf(
stderr,
"Cannot prepare statement: %s\n",
sqlite3_errmsg(db));
return 1;
}
sqlite3_bind_blob(pStmt, 1, data, size, SQLITE_STATIC);
rc = sqlite3_step(pStmt);
if (rc != SQLITE_DONE)
{
printf("execution failed: %s", sqlite3_errmsg(db));
}
sqlite3_finalize(pStmt);
sqlite3_close(db);
return 0;
}이 프로그램은, 현재 디렉토리의 이미지 파일을 읽어 test.db 데이터베이스의 Images 테이블에 쓰는 작업을 합니다.
FILE *fp = fopen("woman.jpg", "rb");
if (fp == NULL)
{
fprintf(stderr, "Cannot open image file\n");
return 1;
}먼저 파일시스템에서 바이너리 데이터를 읽습니다. woman.jpg 라는 JPG 파일입니다. fopen() 함수로 파일을 읽기 모드로 엽니다. 성공적으로 열렸다면 FILE 을 반환하고, 실패했다면 NULL을 반환합니다.
fseek(fp, 0, SEEK_END);
if (ferror(fp))
{
fprintf(stderr, "fseek() failed\n");
int r = fclose(fp);
if (r == EOF)
{
fprintf(stderr, "Cannot close file handler\n");
}
return 1;
} fseek() 함수를 이용해 파일 포인터를 파일의 맨 끝으로 이동합니다. 이미지 파일의 크기를 알아내기 위함입니다. 오류가 발생하면 오류 지시자(error indicator)가 설정됩니다. ferror() 함수로 오류 지시자를 확인해 처리해줍니다.
int flen = ftell(fp);
if (flen == -1)
{
perror("error occurred");
int r = fclose(fp);
if (r == EOF)
{
fprintf(stderr, "Cannot close file handler\n");
}
return 1;
}ftell() 함수는 파일의 시작부터 현재 파일 포인터가 가리키고 있는 지점까지의 바이트 수를 반환합니다. 즉, 이미지 파일의 크기입니다. 오류가 발생하면 -1을 반환하고, errno 변수를 설정합니다. perror() 함수는 errno 변수 값을 오류 메세지로 해석해 stderr 스트림으로 출력합니다.
char data[flen+1];이 배열에 이미지 데이터를 저장할 겁니다.
int size = fread(data, 1, flen, fp);fread() 함수는 파일 포인터로부터 데이터를 읽어 배열에 저장합니다. 성공적으로 읽은 요소의 수를 반환합니다.
int r = fclose(fp);
if (r == EOF)
{
fprintf(stderr, "Cannot close file handler\n");
} 데이터를 다 읽었으니, 파일 핸들을 닫아줍니다.
char *sql = "INSERT INTO Images(Data) VALUES(?)";이 SQL 쿼리가 이미지를 데이터베이스에 넣는데 사용됩니다.
rc = sqlite3_prepare(db, sql, -1, &pStmt, 0);쿼리를 컴파일합니다.
sqlite3_bind_blob(pStmt, 1, data, size, SQLITE_STATIC);sqlite3_bind_blob() 함수는 컴파일된 쿼리의 물음표 위치에 BLOB 데이터를 연결하는 역할을 합니다. SQLITE_STATIC 인자는 연결하는 데이터가 정적인 데이터이므로 해제될 필요가 없다는 것을 명시해줍니다.
rc = sqlite3_step(pStmt);쿼리가 실행되고, 테이블에 이미지가 쓰여집니다.
이미지 데이터 읽기
이번 예제는 이전 예제와 반대되는 기능을 가지는 프로그램입니다. 데이터베이스 테이블에서 이미지를 읽어보겠습니다.
#include <sqlite3.h>
#include <stdio.h>
int main(void)
{
FILE *fp = fopen("woman2.jpg", "wb");
if (fp == NULL)
{
fprintf(stderr, "Cannot open image file\n");
return 1;
}
sqlite3 *db;
char *err_msg = 0;
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK)
{
fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
char *sql = "SELECT Data FROM Images WHERE Id = 1";
sqlite3_stmt *pStmt;
rc = sqlite3_prepare_v2(db, sql, -1, &pStmt, 0);
if (rc != SQLITE_OK)
{
fprintf(stderr, "Failed to prepare statement\n");
fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
rc = sqlite3_step(pStmt);
int bytes = 0;
if (rc == SQLITE_ROW)
{
bytes = sqlite3_column_bytes(pStmt, 0);
}
fwrite(sqlite3_column_blob(pStmt, 0), bytes, 1, fp);
if (ferror(fp))
{
fprintf(stderr, "fwrite() failed\n");
return 1;
}
int r = fclose(fp);
if (r == EOF)
{
fprintf(stderr, "Cannot close file handler\n");
}
rc = sqlite3_finalize(pStmt);
sqlite3_close(db);
return 0;
} Images 테이블에서 이미지를 읽고, woman2.jpg 라는 다른 파일에 쓰는 작업입니다.
FILE *fp = fopen("woman2.jpg", "wb");
if (fp == NULL)
{
fprintf(stderr, "Cannot open image file\n");
return 1;
} 먼저 파일을 읽기 모드로 엽니다.
char *sql = "SELECT Data FROM Images WHERE Id = 1";테이블에서 이미지 데이터를 읽는 쿼리입니다. 첫 번째 행의 데이터를 읽습니다.
if (rc == SQLITE_ROW)
{
bytes = sqlite3_column_bytes(pStmt, 0);
}sqlite3_column_bytes() 함수는 얻어온 BLOB 이 몇 바이트인지 반환합니다.
fwrite(sqlite3_column_blob(pStmt, 0), bytes, 1, fp);fwrite() 함수를 이용해 파일에 데이터를 씁니다. 쓰는 데이터의 포인터는 sqlite3_column_blob() 함수로 얻었습니다.
if (ferror(fp))
{
fprintf(stderr, "fwrite() failed\n");
return 1;
}ferror() 함수로 오류 지시자가 설정되었는지 확인합니다.
메타데이터 (Metadata)
메타데이터는 데이터베이스의 데이터들에 대한 정보입니다. SQLite 에서 메타데이터는 우리가 데이터를 저장하는 테이블과 컬럼에 대한 정보들을 포함합니다. 반환된 결과값의 행과 컬럼 수도 마찬가지로 메타데이터에 해당합니다.
SQLite의 메타데이터는 PRAGMA 명령으로 얻을 수 있습니다. SQLite의 특정 개체가 메타데이터를 나타내는 값을 가지고 있기도 합니다. sqlite_master 테이블이 그런 경우로, 쿼리를 통해 얻어낼 수 있습니다.
#include <sqlite3.h>
#include <stdio.h>
int callback(void *, int, char **, char **);
int main(void)
{
sqlite3 *db;
char *err_msg = 0;
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK)
{
fprintf(
stderr,
"Cannot open database: %s\n",
sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
char *sql = "PRAGMA table_info(Cars)";
rc = sqlite3_exec(db, sql, callback, 0, &err_msg);
if (rc != SQLITE_OK )
{
fprintf(stderr, "Failed to select data\n");
fprintf(stderr, "SQL error: %s\n", err_msg);
sqlite3_free(err_msg);
sqlite3_close(db);
return 1;
}
sqlite3_close(db);
return 0;
}
int callback(
void *NotUsed,
int argc,
char **argv,
char **azColName)
{
NotUsed = 0;
for (int i = 0; i < argc; i++)
{
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}위 예제는, PRAGMA table_info(tableName) 명령을 사용해서 Cars 테이블의 메타데이터를 얻습니다.
char *sql = "PRAGMA table_info(Cars)";PRAGMA table_info(tableName) 명령은 Cars 테이블의 컬럼 하나당 하나의 행을 반환합니다. 반환되는 행들의 컬럼은 컬럼 번호, 이름, 데이터 타입, NULL 값이 될 수 있는지, 기본값이 무엇인지 등을 나타냅니다.
$ ./column_names
cid = 0
name = Id
type = INT
notnull = 0
dflt_value = NULL
pk = 0
...예제를 실행한 결과입니다.
메타데이터와 관련된 다음 예제로, test.db 데이터베이스의 모든 테이블을 나타내는 프로그램을 보겠습니다.
#include <sqlite3.h>
#include <stdio.h>
int callback(void *, int, char **, char **);
int main(void)
{
sqlite3 *db;
char *err_msg = 0;
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK)
{
fprintf(
stderr,
"Cannot open database: %s\n",
sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
char *sql = "SELECT name FROM sqlite_master WHERE type='table'";
rc = sqlite3_exec(db, sql, callback, 0, &err_msg);
if (rc != SQLITE_OK)
{
fprintf(stderr, "Failed to select data\n");
fprintf(stderr, "SQL error: %s\n", err_msg);
sqlite3_free(err_msg);
sqlite3_close(db);
return 1;
}
sqlite3_close(db);
return 0;
}
int callback(
void *NotUsed,
int argc,
char **argv,
char **azColName)
{
NotUsed = 0;
for (int i = 0; i < argc; i++)
{
printf("%s\n", argv[i] ? argv[i] : "NULL");
}
return 0;
}현재 데이터베이스의 모든 테이블을 터미널에 출력하는 프로그램입니다.
char *sql = "SELECT name FROM sqlite_master WHERE type='table'";테이블의 이름들은 sqlite_master 테이블에 저장되어 있습니다.
$ ./list_tables
Cars
Images예제를 실행한 결과입니다.
트랜잭션 (Transactions)
트랜잭션은 데이터베이스 하나 또는 여러 개에서 일어나는 데이터베이스 연산의 원자적 단위입니다. 하나의 트랜잭션에 포함된 SQL 쿼리들은 데이터베이스에 한 번에 커밋되거나, 롤백 될 수 있습니다.
(역자주: 쉽게 말해 여러 개의 명령을 한 덩어리로 묶어서, 한꺼번에 실행하거나 되돌릴 수 있게 하는 것을 뜻합니다. 명령어 하나는 성공하고 하나는 실패하는 식의 현상이 발생하지 않도록 보장합니다.)
SQLite 에서는, SELECT 이외의 명령은 암시적으로 트랜잭션을 시작합니다. 또 트랜잭션 내부에서 CREATE TABLE …, VACUUM, PRAGMA 같은 명령어들은 실행 직전 이전 변경들을 커밋합니다.
수동 트랜잭션은 BEGIN TRANSACTION 구문으로 시작하고, COMMIT 또는 ROLLBACK 구문으로 끝냅니다.
SQLite 는 DEFERRED, IMMEDIATE, EXCLUSIVE 세 가지 트랜잭션 레벨을 지원합니다.
자동 커밋 (Autocommit)
기본적으로 SQLite 3는 자동 커밋 모드로 동작합니다. 자동 커밋 모드에서는 명령이 실행되자마자 데이터베이스의 모든 변경이 알아서 커밋됩니다. BEGIN 구문으로 자동 커밋 모드를 끄고, COMMIT 또는 ROLLBACK 구문으로 다시 켤 수 있습니다.
#include <sqlite3.h>
#include <stdio.h>
int main()
{
sqlite3 *db;
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK)
{
fprintf(
stderr,
"Cannot open database: %s\n",
sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
printf("Autocommit: %d\n", sqlite3_get_autocommit(db));
sqlite3_close(db);
return 0;
}이 예제는 현재 자동 커밋 모드가 켜져있는지 확인합니다.
printf("Autocommit: %d\n", sqlite3_get_autocommit(db));sqlite3_get_autocommit() 함수는 자동 커밋 모드가 켜져있다면 0이 아닌 수를, 꺼져있다면 0을 반환합니다.
$ ./get_ac_mode
Autocommit: 1예제를 실행한 결과로 자동 커밋 모드가 기본적으로 켜져있는 것을 확인할 수 있습니다.
다음 예제에서는 자동 커밋 모드가 더 정확히 어떤 것인지 보여줍니다. SELECT 이외의 명령은 각각이 바로 커밋되는 작은 트랜잭션이라고 이야기 했었죠.
#include <sqlite3.h>
#include <stdio.h>
int main(void)
{
sqlite3 *db;
char *err_msg = 0;
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK)
{
fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
char *sql = "DROP TABLE IF EXISTS Friends;"
"CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT);"
"INSERT INTO Friends(Name) VALUES ('Tom');"
"INSERT INTO Friends(Name) VALUES ('Rebecca');"
"INSERT INTO Friends(Name) VALUES ('Jim');"
"INSERT INTO Friend(Name) VALUES ('Robert');";
rc = sqlite3_exec(db, sql, 0, 0, &err_msg);
if (rc != SQLITE_OK)
{
fprintf(stderr, "SQL error: %s\n", err_msg);
sqlite3_free(err_msg);
sqlite3_close(db);
return 1;
}
sqlite3_close(db);
return 0;
}Friends 테이블을 만들고, 데이터를 채워 넣는 작업입니다.
char *sql = "DROP TABLE IF EXISTS Friends;"
"CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT);"
"INSERT INTO Friends(Name) VALUES ('Tom');"
"INSERT INTO Friends(Name) VALUES ('Rebecca');"
"INSERT INTO Friends(Name) VALUES ('Jim');"
"INSERT INTO Friend(Name) VALUES ('Robert');";마지막 부분에 오류가 있습니다. Friend 테이블은 존재하지 않죠.
$ ./autocommit
SQL error: no such table: Friend
$ sqlite3 test.db
sqlite> .tables
Cars Friends Images
sqlite> SELECT * FROM Friends;
1|Tom
2|Rebecca
3|Jim예제를 실행해보면, 테이블도 생성되었고 오류가 있던 구문을 제외한 세 개의 행이 추가된 것을 확인할 수 있습니다.
수동 트랜잭션과 비교
다음 예제는 이전 예제와 같은 동작을 하지만, 명령어들을 수동 트랜잭션으로 묶어 비교해보겠습니다.
#include <sqlite3.h>
#include <stdio.h>
int main(void)
{
sqlite3 *db;
char *err_msg = 0;
int rc = sqlite3_open("test.db", &db);
if (rc != SQLITE_OK)
{
fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
char *sql = "DROP TABLE IF EXISTS Friends;"
"BEGIN TRANSACTION;"
"CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT);"
"INSERT INTO Friends(Name) VALUES ('Tom');"
"INSERT INTO Friends(Name) VALUES ('Rebecca');"
"INSERT INTO Friends(Name) VALUES ('Jim');"
"INSERT INTO Friend(Name) VALUES ('Robert');"
"COMMIT;";
rc = sqlite3_exec(db, sql, 0, 0, &err_msg);
if (rc != SQLITE_OK )
{
fprintf(stderr, "SQL error: %s\n", err_msg);
sqlite3_free(err_msg);
sqlite3_close(db);
return 1;
}
sqlite3_close(db);
return 0;
}똑같이 Friends 테이블에 데이터를 넣는 작업입니다.
char *sql = "DROP TABLE IF EXISTS Friends;"
"BEGIN TRANSACTION;"
"CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT);"
"INSERT INTO Friends(Name) VALUES ('Tom');"
"INSERT INTO Friends(Name) VALUES ('Rebecca');"
"INSERT INTO Friends(Name) VALUES ('Jim');"
"INSERT INTO Friend(Name) VALUES ('Robert');"
"COMMIT;";첫 번째 구문은 Friends 테이블이 이미 있다면 삭제하도록 합니다. 다른 구문들은 트랜잭션으로 묶여 있습니다. 트랜잭션은 “다 실행되던가 다 실행되지 말던가” 모드로 동작합니다.
sqlite> .tables
Cars Images예제를 실행하고 SQLite 툴로 확인해보면, 마지막 구문에 오류가 있었기 때문에 트랜잭션이 롤백되었다는 것을 확인할 수 있습니다. Friends 테이블은 생성조차 되지 않았죠.
참고
튜토리얼 작성에는 SQLite documentation 이 사용되었습니다.
마치며
이번 글로 번역이 끝났네요. 원래는 한 덩어리인 글을 1, 2부로 나눈 것은 분량이 많기도 하고 뒤쪽 내용이 스페셜 케이스들을 다룬다고 생각해서 나눈 것이였는데요. 기분 탓인지는 몰라도 예제나 설명의 퀄리티가 1부보다 떨어지는 것처럼 느껴졌습니다. 사실 뒤로 갈수록 힘이 빠지는 것은 긴 글을 쓸 때 경계해야 할 점이기도 하죠.
그래도 제가 느끼기에 데이터베이스를 처음 접하는 C/C++ 개발자에게 정말 좋은 입문 글이 아닐까 싶습니다. API를 나열해서 용도나 구조를 줄줄이 읊는 것 보다는 역시 실제 사용되는 상황의 예들을 이용해서 설명하는게 이해도 쉽고 기억에도 오래 남는 법이죠.ㅎㅎ
다음에도 웹서핑을 즐기다가 괜찮아보이는 글이 있다면 또 옮겨보도록 하겠습니다!
- SQLite 데이터베이스 C/C++에서 사용하기 1: 기본 (번역)
- SQLite 데이터베이스 C/C++에서 사용하기 2: 심화 (번역)
- SQLite 데이터베이스를 10배 빠르게 만드는 방법들