SQLite - WindyHana's Solanara

목차

개요

특징

쉽게 말해 어플리케이션 임베디드용 SQL엔진으로는 가장 좋은 조건을 가지고 있다.

지원되는 SQL

설치

root@wl ~ # wget  http://www.sqlite.org/sqlite-autoconf-3070701.tar.gz
root@wl ~ # tar xvfz sqlite-autoconf-3070701.tar.gz
root@wl ~ # cd sqlite-autoconf-3070701
root@wl ~/sqlite-autoconf-3070701 # CFLAGS="-I/usr/local/include" ./configure
root@wl ~/sqlite-autoconf-3070701 # make
root@wl ~/sqlite-autoconf-3070701 # make install

사용

C 예제

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

int main(int argc, char *argv[]) {
	sqlite3* db;
	int rlt;
	char * md5usercontext = "MD5USERDATA";
	char * firstusercontext = "FIRSTUSERDATA";

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

	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;
}
Twitter RSS IconTexto 올바른 XHTML 1.0 Transitional 입니다 올바른 CSS입니다!

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