SQLite - 윈디하나의 솔라나라

목차

개요

기능과 특징

SQLite 는 어플리케이션 임베디드용 SQL엔진으로는 가장 좋은 조건을 가지고 있다. 더 자세한 사항은 Architecture of SQLite, Features Of SQLite을 읽어보자.

지원 기능

문서를 쓰는 시점에서 지원되는 주요 기능은 아래와 같다. 수시로 업데이트 되기 때문에 Full-Featured SQL를 자주 들려보자.

미 지원 기능

미 지원 기능은 SQL Features That SQLite Does Not Implement에서 확인해볼 수 있다.

설치

사용

BLOB 성능

35% Faster Than The Filesystem에 의하면 SQLite 는 파일 시스템보다 더 빠른 BLOB 성능을 보일 수 있다고 한다. BLOB 성능이 그만큼 좋다는 의미다. kvtest라는 프로그램을 제공하는데, 아래와 같이 테스트해볼 수 있다.

windy@wl ~/src/sqlite-autoconf-3280000 $ wget -O kvtest.c "https://www.sqlite.org/src/raw/test/kvtest.c?name=94da54bb66aae7a54e47cf7e4ea4acecc0f217560f79ad3abfcc0361d6d557ba"
windy@wl ~/src/sqlite-autoconf-3280000 $ cc -DSQLITE_DIRECT_OVERFLOW_READ -lm -m64 -o kvtest kvtest.c sqlite3.c
windy@wl ~/src/sqlite-autoconf-3280000 $ cp kvtest ~/sqlite/
windy@wl ~/src/sqlite-autoconf-3280000 $ cd ~/sqlite/
windy@wl ~/sqlite $ ./kvtest init kvtest.db --count 1k --size 10k --variance 2k 1)
windy@wl ~/sqlite $ ./kvtest stat kvtest.db
Number of entries:      1000
Average value size:     9960
Minimum value size:     8009
Maximum value size:    11999
Page-size:              4096
Page-count:             2607
Freelist-count:            0
Integrity-check:    ok
windy@wl ~/sqlite $ ./kvtest export kvtest.db kvtest.dir
001000
windy@wl ~/sqlite $ ls -al kvtest.dir
...
-rw-r--r--   1 windy    staff      10865  0월  0일  00:00 001000
windy@wl ~/sqlite $ ./kvtest export kvtest.db kvtest.tree --tree
00/10/00
windy@wl ~/sqlite $ ls -al kvtest.tree
drwxr-xr-x  13 windy    staff         13  0월  0일  00:00 001000
1) 사이트에서 제시한 명령은 ./kvtest init test1.db --count 100k --size 10k --variance 2k이다. 이렇게 하면 1GB 정도되는 데이터베이스를 가지고 성능을 테스트하는데, 테스트 시간이 오래 걸려 임의로 1/100 으로 줄여 테스트했다. 따라서 여기 나와있는 실행결과는 큰 의미가 없다.

이제 성능을 측정해보자.

windy@wl ~/sqlite $ ./kvtest run kvtest.db --count 10k --blob-api
SQLite version: 3.28.0
--count 10000 --max-id 1000 --asc
--cache-size 1000 --jmode delete
--mmap 0 --blob-api
Database page size: 4096
Total elapsed time: 0.266
Microseconds per BLOB read: 26.600
Content read rate: 375.0 MB/s
windy@wl ~/sqlite $ ./kvtest run kvtest.dir --count 10k --blob-api
--count 10000 --max-id 1000 --asc
Total elapsed time: 0.363
Microseconds per BLOB read: 36.300
Content read rate: 274.8 MB/s
windy@wl ~/sqlite $ ./kvtest run kvtest.tree --count 10k --blob-api
--count 10000 --max-id 1000 --asc
Total elapsed time: 0.369
Microseconds per BLOB read: 36.900
Content read rate: 270.3 MB/s
업데이트 메소드를 사용해 성능을 측정해보자. 
windy@wl ~/sqlite $ ./kvtest run kvtest.db --count 10k --update
SQLite version: 3.28.0
--count 10000 --max-id 1000 --asc
--cache-size 1000 --jmode delete
--mmap 0
Database page size: 4096
Total elapsed time: 3.789
Microseconds per BLOB write: 378.900
Content write rate: 26.3 MB/s
windy@wl ~/sqlite $ ./kvtest run kvtest.dir --count 10k --update
--count 10000 --max-id 1000 --asc
Total elapsed time: 2.757
Microseconds per BLOB write: 275.700
Content write rate: 36.2 MB/s
windy@wl ~/sqlite $ ./kvtest run kvtest.tree --count 10k --update
--count 10000 --max-id 1000 --asc
Total elapsed time: 2.634
Microseconds per BLOB write: 263.400
Content write rate: 37.9 MB/s

필자의 시스템에서는 성능이 위와 같이 나왔다. (적은 샘플을 가지고 테스트했다는 것을 감안해서 보자) 단순하게 비교하는건 어렵기 때문에, 테스트하고자 하는 시스템에서 성능을 확인해보자. 다른건 몰라도 읽기 성능은 꽤 마음에 들었다.

C 예제

SQLite 의 쿼리에서 MD5 를 계산할 수 있도록, md5()를 생성해 sqlite3_create_function()을 이용해 추가하고, 사용 예제를 보였다. MD5 계산은 솔라리스에 번들된 libmd5라이브러리를 사용했다.
sqlite.c
 (5,506 바이트)
/*
	SQLite Sample for Solaris
	WindyHana's Solanara: SQLite - http://www.solanara.net/solanara/sqlite
	dynamic: cc -lsqlite3 -lmd5 -I/usr/local/include -L/usr/local/lib -o sqlite sqlite.c
	static:  cc -lm -lmd5 -I/usr/local/include -o sqlite sqlite.c /usr/local/lib/libsqlite3.a
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <md5.h>
#include "sqlite3.h"

int exec_callbackcnt;
static int exec_callback(void * userarg, int argc, char** argv, char** azColName){
	if (exec_callbackcnt == 0) {
		printf("SQL: %s\n\t", userarg);
		for (int i = 0; i < argc; i++) {
			printf("%s\t", azColName[i]);
		}
		printf("\n");
	}
	exec_callbackcnt++;
	printf("%d\t", exec_callbackcnt);
	for (int i = 0; i < argc; i++) {
		printf("%s\t", argv[i] ? argv[i]: "NULL");
	}
	printf("\n");
	return 0;
}

char* bin2hex(const unsigned char *bin, size_t len) {
	// original source: https://nachtimwald.com/2017/09/24/hex-encode-and-decode-in-c/
	char * hx = "0123456789abcdef";
	char * rlt;
	size_t i;

	if (bin == NULL || len == 0) {
		return NULL;
	}

	rlt = malloc(len*2+1);
	for (i = 0; i < len; i++) {
		rlt[i*2]   = hx[bin[i] >> 4];
		rlt[i*2+1] = hx[bin[i] & 0x0F];
	}
	rlt[len*2] = '\0';

	return rlt;
}

/**
 * Solaris의 MD5 라이브러리를 사용한 MD5 계산 함수
 */
char* hash_md5_alloc(char* data, int data_len) {
	unsigned char*  auth_buffer = malloc(16);

	md5_calc(auth_buffer, data, data_len);
//	MD5_CTX md5_context;
//	MD5Init(&md5_context);
//	MD5Update(&md5_context, data, data_len);
//	MD5Final(auth_buffer, &md5_context);

	char * rlt = bin2hex(auth_buffer, 16);
	free(auth_buffer);
	return rlt;
}

/**
 * SQLite의 MD5 커스텀 함수을 생성
 */
static void func_md5(sqlite3_context* pContext, int argc, sqlite3_value **argv) {
	void* pUserData = pUserData = sqlite3_user_data(pContext);
	char* rlt = NULL;
	for (int i = 0; i < argc; i++) { // 1개만 있음
		if (sqlite3_value_type(argv[i]) == SQLITE_TEXT) { // SQLITE_TEXT 만 넘김
			char* val = (char *) sqlite3_value_text(argv[i]);
			int len   = strlen(val);
			rlt = hash_md5_alloc(val, len);
		}
	}

	sqlite3_result_text(pContext, rlt, -1, SQLITE_TRANSIENT);
	free(rlt);
}


typedef struct _func_first_struct {
	char * rlt;
} func_first_struct;

static void func_first_step(sqlite3_context* pContext, int argc, sqlite3_value **argv) {
	func_first_struct* p = (func_first_struct*) sqlite3_aggregate_context(pContext, sizeof(func_first_struct));
	for (int i = 0; i < argc; i++) { // 1개만 있음
		if (sqlite3_value_type(argv[i]) == SQLITE_TEXT) { // SQLITE_TEXT 만 넘김
			if (p->rlt == NULL) {
				p->rlt = (char *) sqlite3_value_text(argv[i]);
			}
		}
	}
}

static void func_first_final(sqlite3_context* pContext) {
	func_first_struct* p = (func_first_struct *) sqlite3_aggregate_context(pContext, sizeof(func_first_struct));
	sqlite3_result_text(pContext, p->rlt, -1, SQLITE_TRANSIENT);
}

int execSQL(sqlite3* db, char * SQL) {
	char* zErrMsg = NULL;
	int rlt;
	exec_callbackcnt = 0;
	rlt = sqlite3_exec(db, SQL, exec_callback, (void *) SQL, &zErrMsg);
	if (rlt != SQLITE_OK ) {
		fprintf(stderr, "SQL오류: %s\n", zErrMsg);
	}
	return rlt;
}

int progress_handler(void* udp) {
	printf(".");
	return 0;
}

void print_limit(sqlite3* db) {
	printf("\n");
	printf("%s = %d\n", "SQLITE_LIMIT_LENGTH", sqlite3_limit(db, SQLITE_LIMIT_LENGTH, -1));
	printf("%s = %d\n", "SQLITE_LIMIT_COLUMN", sqlite3_limit(db, SQLITE_LIMIT_COLUMN, -1));
	printf("%s = %d\n", "SQLITE_LIMIT_EXPR_DEPTH", sqlite3_limit(db, SQLITE_LIMIT_EXPR_DEPTH, -1));
	printf("%s = %d\n", "SQLITE_LIMIT_COMPOUND_SELECT", sqlite3_limit(db, SQLITE_LIMIT_COMPOUND_SELECT, -1));
	printf("%s = %d\n", "SQLITE_LIMIT_VDBE_OP", sqlite3_limit(db, SQLITE_LIMIT_VDBE_OP, -1));
	printf("%s = %d\n", "SQLITE_LIMIT_FUNCTION_ARG", sqlite3_limit(db, SQLITE_LIMIT_FUNCTION_ARG, -1));
	printf("%s = %d\n", "SQLITE_LIMIT_ATTACHED", sqlite3_limit(db, SQLITE_LIMIT_ATTACHED, -1));
	printf("%s = %d\n", "SQLITE_LIMIT_LIKE_PATTERN_LENGTH", sqlite3_limit(db, SQLITE_LIMIT_LIKE_PATTERN_LENGTH, -1));
	printf("%s = %d\n", "SQLITE_LIMIT_VARIABLE_NUMBER", sqlite3_limit(db, SQLITE_LIMIT_VARIABLE_NUMBER, -1));
	printf("%s = %d\n", "SQLITE_LIMIT_TRIGGER_DEPTH", sqlite3_limit(db, SQLITE_LIMIT_TRIGGER_DEPTH, -1));
	printf("\n");
}

int main(int argc, char *argv[]) {
	sqlite3* db;
	int rlt;
	char * md5usercontext = "MD5USERDATA";
	char * firstusercontext = "FIRSTUSERDATA";
	
	printf("SQLite %s\nSQLite Lib %s\n", SQLITE_VERSION, sqlite3_libversion());
	if (sqlite3_compileoption_used("SQLITE_ENABLE_FTS5")) {
		printf("-- with FTS5\n");
	}

	rlt = sqlite3_open("userdb.sqlite", &db);
	if (rlt != SQLITE_OK ) {
		fprintf(stderr, "데이터베이스를 열 수 없음: %s\n", sqlite3_errmsg(db));
		exit(1);
	}
	
	print_limit(db);

#ifndef SQLITE_OMIT_PROGRESS_CALLBACK
	// 매 10개의 내부 명령셋 호출시 progress_handler 호출됨
	// SQLite 는 내부적으로 Bytecode Engine 이 있어 SQL을 Bytecode 으로 변환시켜 실행한다.
	sqlite3_progress_handler(db, 10, progress_handler, NULL);
#endif

	rlt = sqlite3_create_function(db, "md5", 1, SQLITE_TEXT, md5usercontext, &func_md5, NULL, NULL);
	rlt = sqlite3_create_function(db, "first", 1, SQLITE_TEXT, firstusercontext, NULL, &func_first_step, &func_first_final);

	rlt = execSQL(db, "select md5('한글') \"한글(UTF-8)MD5해시\"");
	rlt = execSQL(db, "select first(username) fun from userinfo");
	rlt = execSQL(db, "select * from userlist");

	sqlite3_close(db);
	return rlt != SQLITE_OK;
}

컴파일 하기

# 동적 컴파일
windy@wl ~/sqlite $ cc -I/usr/local/include -L/usr/local/lib -R/usr/local/lib -lsqlite3 -lmd5 -m64 -o sqlite sqlite.c

# 정적 컴파일
windy@wl ~/sqlite $ cc -I/usr/local/include -lm -lmd5 -m64 -o sqlite sqlite.c /usr/local/lib/libsqlite3.a

#  BerkeyelDB 컴파일
windy@wl ~/sqlite $ cc -I/usr/local/db/include -lmd5 -o sqlite sqlite.c /usr/local/db/lib/libdb_sql-6.1.a -lrt -lsocket -lnsl 1)
1) 이 커맨드는 윈디하나의 솔라나라: BerkeleyDB - SQLite절을 참조하자. 이 커맨드를 사용하기 위해서는 BerkeyelDB가 필요하다.

실행은 아래와 같이 할 수 있다. 실행하기 전에 userdb.sqlite 파일을 생성해놓자.

windy@wl ~/sqlite $ ./sqlite
SQLite 3.28.0
SQLite Lib 3.28.0
-- with FTS5

SQLITE_LIMIT_LENGTH = 1000000000
SQLITE_LIMIT_COLUMN = 2000
SQLITE_LIMIT_EXPR_DEPTH = 1000
SQLITE_LIMIT_COMPOUND_SELECT = 500
SQLITE_LIMIT_VDBE_OP = 250000000
SQLITE_LIMIT_FUNCTION_ARG = 127
SQLITE_LIMIT_ATTACHED = 10
SQLITE_LIMIT_LIKE_PATTERN_LENGTH = 50000
SQLITE_LIMIT_VARIABLE_NUMBER = 999
SQLITE_LIMIT_TRIGGER_DEPTH = 1000

SQL: select md5('한글') "한글(UTF-8)MD5해시"
        한글(UTF-8)MD5해시
1       52b8c54ab4ea672ee6cdfdfef0a31db4
SQL: select first(username) fun from userinfo
        fun
1       루트
SQL: select * from userlist
        userid  username        tel1    grade   rdate
1       root    루트    010-111-1111    1.0     2019-05-07 02:13:37
2       windy   윈디하나        010-000-0000    1.0     2019-05-07 02:13:37

TCL/TK 예제

간단한 TCL/TK 샘플을 보였다. 더 자세한 사항은 The Tcl interface to the SQLite library을 참고하자. 
windy@wl ~/sqlite $ cat sqlite.tcl
package require sqlite3

sqlite3 userdb "userdb.sqlite";
set tableNames [userdb eval {SELECT name FROM sqlite_master WHERE type IN ('table', 'view')}]
puts $tableNames
userdb close
windy@wl ~/sqlite $ tclsh sqlite.tcl
authinfo sqlite_sequence userinfo userlist userjoinstat sqlite_stat1
RSS ATOM XHTML 5 CSS3

Copyright © 2004-2024 Jo HoSeok. All rights reserved.