SQLite - WindyHana's Solanara

목차

개요

기능과 특징

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

지원되는 SQL

SQL92의 기능은 SQL Features That SQLite Does Not Implement을 제외하고는 모두 지원된다고 보면 된다.

설치

root@wl ~/src # wget http://www.sqlite.org/2017/sqlite-autoconf-3160100.tar.gz
root@wl ~/src # tar xvfz sqlite-autoconf-3160100.tar.gz
root@wl ~/src # cd sqlite-autoconf-3160100
root@wl ~/src/sqlite-autoconf-3160100 # CFLAGS="-I/usr/local/include" ./configure --enable-fts5 --enable-json1 --enable-session 1) 2)
root@wl ~/src/sqlite-autoconf-3160100 # make
root@wl ~/src/sqlite-autoconf-3160100 # make install
1) 64비트로 컴파일 하려면 CFLAGS="-m64"를 사용하자.
2) FTS5, JSON1, Session 확장을 활성화 한다.

사용

C 예제

md5()함수를 sqlite3_create_function()을 이용해 추가하고, 사용 예제를 보였다. md5함수는 솔라리스의 번들 라이브러리를 사용했다.
sqlite.c
다운로드 (4,794 바이트)
/*
	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 -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;
}

/**
 * Solaris의 MD5 라이브러리를 사용한 MD5 계산 함수
 */
char* hash_md5_alloc(char* data, int data_len) {
	MD5_CTX         md5_context;
	unsigned char*  auth_buffer = malloc(16);
	char*           hex_output = malloc(16*2 + 1);
	
	MD5Init(&md5_context);
	MD5Update(&md5_context, data, data_len);
	MD5Final(auth_buffer, &md5_context);

	for (int i = 0; i < 16; i++) {
		sprintf(hex_output + i * 2, "%02x", auth_buffer[i]);	
	}
	free(auth_buffer);
	return hex_output;
}

/**
 * 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;
}

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());
#ifdef FTS5_TOKENIZE_QUERY
	printf("-- FTS5 enabled.\n");
#endif

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

	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;
}

root@wl ~ # cc -lsqlite3 -lmd5 -I/usr/local/include -L/usr/local/lib -o sqlite sqlite.c
root@wl ~ # cc -lmd5 -I/usr/local/db/include -o sqlite sqlite.c /usr/local/lib/libsqlite3.a

또는

root@wl ~ # cc -lmd5 -I/usr/local/db/include -o sqlite sqlite.c /usr/local/db/lib/libdb_sql-6.1.a -lrt -lsocket -lnsl 1)
root@wl ~ # ./sqlite
SQLite 3.16.1
SQLite Lib 3.16.1

SQLITE_LIMIT_LENGTH = 1000000000
SQLITE_LIMIT_COLUMN = 2000
SQLITE_LIMIT_EXPR_DEPTH = 1000
SQLITE_LIMIT_COMPOUND_SELECT = 500
SQLITE_LIMIT_VDBE_OP = 25000
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     2017-01-15 16:35:07
2       windy   윈디하나        010-000-0000    1.0     2017-01-15 16:35:07
1) 이 커맨드는 윈디하나의 솔라나라: BerkeleyDB - SQLite절을 참조하자. 이 커맨드를 사용하기 위해서는 BerkeyelDB가 필요하다.
RSS ATOM XHTML 1.0 CSS3