SQLite는 꽤 유연하게 만들어져 있습니다.
일종의 plug-in을 만들어 extension으로 로드도 가능하고, 일반적인 함수(substr) 혹은 aggregate 함수(sum,count,avg..)도 구현해서 붙일 수 있습니다.
근데 너무 유연해서인지 함수가 너무 부족하더군요 -_-;;;
바로 예전 글에서(http://jeminency.tistory.com/114) Perl로 구현하기 전에 Sed와 Python 스크립트로 구현했다고 적었는데 그 때 SQLite에 집어넣었었습니다.
마침 이번에 좀 복잡한 통계 데이터를 뽑을 일이 있어서 새로 Python 프로그램으로 SQLite에 저장된 DB를 활용했는데 그 통계라는 것에 분산이나 (표준)편차가 필요한 실정이었습니다. 다시 말하면 pow나 sqrt가 꼭 필요었는데 SQLite 함수에는 이게 없더군요 -_-;;;
SQLite의 단점이랄까요. 문서화가 잘 되어 있지만 업데이트가 잘 안된다는 점과(현재 버전이랑 내용에 차이가 있습니다) 기본적인 기능은 잘 구현되어 있으나 부가기능은 좀 없다는 거...
물론 파일 DB니까 너무 많은 걸 바라면 안되겠지요 -_-; 하지만 숫자 관련 함수가 abs, round 정도밖에 없다는 건 좀...ㅡㅡㅋ
좌우지간 그래서 pow 함수만이라도 간단하게 구현을 해보았습니다.
일단 함수를 등록하는 SQLite API에 대한 설명부터 간단히 해 보겠습니다.
만든 함수는 위의 함수로 등록합니다.
인자를 알아야겠죠?
db는 SQLite 3의 db 핸들러입니다. zFunctionName은 SQL에서 쓰일 함수의 이름이구요.
nArg는 함수에 들어갈 인자의 갯수입니다. SQL은 일종의 function overloading을 지원하죠. 그러므로 같은 이름의 함수에 인자 수가 다른 버전을 만들어 줄 수 있습니다. 예를 들면 substr(A,B)도 가능하고 substr(A,B,C)도 가능합니다.
eTextRep는 함수 인자가 어떤 텍스트 인코딩으로 들어올 지를 결정합니다. SQLite의 함수들은 UTF-8과 UTF-16을 모두 지원하도록 하여야 하지만 함수의 구현에 따라 특정 인코딩이 더 성능이 좋은 경우가 있으므로 그를 알려주기 위함입니다(제가 만들 함수는 인코딩과 상관없으므로 SQLITE_ANY를 줍니다).
다섯번째의 pApp는 사용자 데이터 포인터입니다. 함수 실행에 인자 외에 다른 데이터가 필요한 경우 저 인자를 통해 함수에 넘겨 줄 수 있습니다.
마지막의 함수 포인터 세 개가 핵심이라고 할 수 있는데요.
xFunc는 실제 함수의 구현입니다.
xStep과 xFinal은 aggregate 함수의 구현입니다. xStep은 매 레코드마다 실행할 함수이며 xFinal은 종료시 사용합니다. 예를 들어 sum이라면 xStep 함수는 더하기만 해주고 xFinal은 결과를 가공하고 판단해서 돌려주거나 에러를 내뱉는 식입니다.
구현 함수의 인자도 알아야겠죠? -_-
첫번째의 sqlite3_context*는 말 그대로 함수를 위한 컨텍스트 정보입니다만 굳이 함수 구현에서는 쓸 일이 없습니다. 함수 결과값을 돌려주기 위한 정도?
그리고 두번째는 받아 온 함수의 인자 갯수, 세번째는 인자의 배열입니다.
실제 구현을 그럼 보시면서...
구현은 SQLite extension으로 했습니다.
보시다시피 SQLite의 SQL 함수 구현이므로 SQLite의 API를 사용하는 것은 필수적입니다.
위에서만 해도 sqlite3_value_int64와 sqlite3_result_int64의 두 가지 api를 사용했죠. 이 두가지 함수는 8 byte int를 DB엔진에서 가져오고 되돌려주는 역할을 합니다. DB 타입이 C 타입과 완전히 똑같지는 않으므로 이런 레이어는 꼭 필요합니다.
처음의 SQLITE_EXTENSION_INIT1과 init안의 SQLITE_EXTENSION_INIT2(pApi); 매크로가 이 api들을 가져오는 역할을 합니다. 외부에서는 이 과정을 거치지 않으면 SQLite의 내부 api를 쓸 수 없으니까요(SQLite 소스에 직접 패치해서 쓴다면 위의 과정은 필요치 않고 func.c에 등록하는 함수들의 배열이 있는데 여기에 추가만 해주시면 됩니다)
그 다음에는 등록을 위해 sqlite3_create_function을 호출하죠.
마지막 함수 포인터 세 개 중 첫 번째만 쓰고 두세번째는 NULL인데 aggregate 함수라면 첫번째를 NULL로 하고 두세번째에 등록을 해주면 됩니다.
그 외 실제 함수 구현은 설명할게 별로 없군요. 보시는 대로입니다.
실행은 다음과 같습니다.
참 쉽죠? -_-
.load 커맨드는 내부적으로 dl_open()을 쓰는데 결국 공유라이브러리 링크와 마찬가지이므로 LD_LIBRARY_PATH에 math.so의 패스가 잡혀 있어야 합니다.
그리고 위의 함수는 그닥 stable 하지 않습니다. 그대로 갖다 쓰는 건 자제해 주세요 -_-;;
SQLite도 DB이므로 당연 데이터 타입들이 있는데 위의 코드는 타입 체킹도 전혀 하지 않고 오버플로우 처리 같은 것도 없기 때문에 문제를 일으킬 소지가 많습니다.
그럼 다음에 또 좀 더 detail한 내용으로...
일종의 plug-in을 만들어 extension으로 로드도 가능하고, 일반적인 함수(substr) 혹은 aggregate 함수(sum,count,avg..)도 구현해서 붙일 수 있습니다.
근데 너무 유연해서인지 함수가 너무 부족하더군요 -_-;;;
바로 예전 글에서(http://jeminency.tistory.com/114) Perl로 구현하기 전에 Sed와 Python 스크립트로 구현했다고 적었는데 그 때 SQLite에 집어넣었었습니다.
마침 이번에 좀 복잡한 통계 데이터를 뽑을 일이 있어서 새로 Python 프로그램으로 SQLite에 저장된 DB를 활용했는데 그 통계라는 것에 분산이나 (표준)편차가 필요한 실정이었습니다. 다시 말하면 pow나 sqrt가 꼭 필요었는데 SQLite 함수에는 이게 없더군요 -_-;;;
SQLite의 단점이랄까요. 문서화가 잘 되어 있지만 업데이트가 잘 안된다는 점과(현재 버전이랑 내용에 차이가 있습니다) 기본적인 기능은 잘 구현되어 있으나 부가기능은 좀 없다는 거...
물론 파일 DB니까 너무 많은 걸 바라면 안되겠지요 -_-; 하지만 숫자 관련 함수가 abs, round 정도밖에 없다는 건 좀...ㅡㅡㅋ
좌우지간 그래서 pow 함수만이라도 간단하게 구현을 해보았습니다.
일단 함수를 등록하는 SQLite API에 대한 설명부터 간단히 해 보겠습니다.
int sqlite3_create_function(
sqlite3 *db,
const char *zFunctionName,
int nArg,
int eTextRep,
void *pApp,
void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
void (*xStep)(sqlite3_context*,int,sqlite3_value**),
void (*xFinal)(sqlite3_context*)
);
sqlite3 *db,
const char *zFunctionName,
int nArg,
int eTextRep,
void *pApp,
void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
void (*xStep)(sqlite3_context*,int,sqlite3_value**),
void (*xFinal)(sqlite3_context*)
);
만든 함수는 위의 함수로 등록합니다.
인자를 알아야겠죠?
db는 SQLite 3의 db 핸들러입니다. zFunctionName은 SQL에서 쓰일 함수의 이름이구요.
nArg는 함수에 들어갈 인자의 갯수입니다. SQL은 일종의 function overloading을 지원하죠. 그러므로 같은 이름의 함수에 인자 수가 다른 버전을 만들어 줄 수 있습니다. 예를 들면 substr(A,B)도 가능하고 substr(A,B,C)도 가능합니다.
eTextRep는 함수 인자가 어떤 텍스트 인코딩으로 들어올 지를 결정합니다. SQLite의 함수들은 UTF-8과 UTF-16을 모두 지원하도록 하여야 하지만 함수의 구현에 따라 특정 인코딩이 더 성능이 좋은 경우가 있으므로 그를 알려주기 위함입니다(제가 만들 함수는 인코딩과 상관없으므로 SQLITE_ANY를 줍니다).
다섯번째의 pApp는 사용자 데이터 포인터입니다. 함수 실행에 인자 외에 다른 데이터가 필요한 경우 저 인자를 통해 함수에 넘겨 줄 수 있습니다.
마지막의 함수 포인터 세 개가 핵심이라고 할 수 있는데요.
xFunc는 실제 함수의 구현입니다.
xStep과 xFinal은 aggregate 함수의 구현입니다. xStep은 매 레코드마다 실행할 함수이며 xFinal은 종료시 사용합니다. 예를 들어 sum이라면 xStep 함수는 더하기만 해주고 xFinal은 결과를 가공하고 판단해서 돌려주거나 에러를 내뱉는 식입니다.
구현 함수의 인자도 알아야겠죠? -_-
첫번째의 sqlite3_context*는 말 그대로 함수를 위한 컨텍스트 정보입니다만 굳이 함수 구현에서는 쓸 일이 없습니다. 함수 결과값을 돌려주기 위한 정도?
그리고 두번째는 받아 온 함수의 인자 갯수, 세번째는 인자의 배열입니다.
실제 구현을 그럼 보시면서...
구현은 SQLite extension으로 했습니다.
#include <stdio.h>
#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1 // api 함수 포인터 배열을 선언하는 매크로
typedef long long i64;
// 실제 pow 함수 구현
static void
powFunc(sqlite3_context *ctx, int argc, sqlite3_value **argv)
{
// 8byte int형 인자 두 개를 받아 옴
int i;
i64 iVal1 = sqlite3_value_int64(argv[0]);
i64 iVal2 = sqlite3_value_int64(argv[1]);
i64 result = 1;
// 곱해서 결과값 구함
for (i=0; i<iVal2; i++)
result *= iVal1;
// context에 결과값 리턴
sqlite3_result_int64(ctx, result);
}
// extension init 함수(실제 함수 구현과는 연관없음)
int
sqlite3_extension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi)
{
// SQLite3 엔진에서 받아 온 API 리스트 등록
SQLITE_EXTENSION_INIT2(pApi);
// 함수 등록
sqlite3_create_function(db, "pow", 2, SQLITE_ANY, NULL, powFunc, NULL, NULL);
return 0;
}
#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1 // api 함수 포인터 배열을 선언하는 매크로
typedef long long i64;
// 실제 pow 함수 구현
static void
powFunc(sqlite3_context *ctx, int argc, sqlite3_value **argv)
{
// 8byte int형 인자 두 개를 받아 옴
int i;
i64 iVal1 = sqlite3_value_int64(argv[0]);
i64 iVal2 = sqlite3_value_int64(argv[1]);
i64 result = 1;
// 곱해서 결과값 구함
for (i=0; i<iVal2; i++)
result *= iVal1;
// context에 결과값 리턴
sqlite3_result_int64(ctx, result);
}
// extension init 함수(실제 함수 구현과는 연관없음)
int
sqlite3_extension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi)
{
// SQLite3 엔진에서 받아 온 API 리스트 등록
SQLITE_EXTENSION_INIT2(pApi);
// 함수 등록
sqlite3_create_function(db, "pow", 2, SQLITE_ANY, NULL, powFunc, NULL, NULL);
return 0;
}
보시다시피 SQLite의 SQL 함수 구현이므로 SQLite의 API를 사용하는 것은 필수적입니다.
위에서만 해도 sqlite3_value_int64와 sqlite3_result_int64의 두 가지 api를 사용했죠. 이 두가지 함수는 8 byte int를 DB엔진에서 가져오고 되돌려주는 역할을 합니다. DB 타입이 C 타입과 완전히 똑같지는 않으므로 이런 레이어는 꼭 필요합니다.
처음의 SQLITE_EXTENSION_INIT1과 init안의 SQLITE_EXTENSION_INIT2(pApi); 매크로가 이 api들을 가져오는 역할을 합니다. 외부에서는 이 과정을 거치지 않으면 SQLite의 내부 api를 쓸 수 없으니까요(SQLite 소스에 직접 패치해서 쓴다면 위의 과정은 필요치 않고 func.c에 등록하는 함수들의 배열이 있는데 여기에 추가만 해주시면 됩니다)
그 다음에는 등록을 위해 sqlite3_create_function을 호출하죠.
마지막 함수 포인터 세 개 중 첫 번째만 쓰고 두세번째는 NULL인데 aggregate 함수라면 첫번째를 NULL로 하고 두세번째에 등록을 해주면 됩니다.
그 외 실제 함수 구현은 설명할게 별로 없군요. 보시는 대로입니다.
실행은 다음과 같습니다.
$ gcc -shared mathfunc.c -o math.so
$ sqlite3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> .load math.so
sqlite> select pow(2,3);
8
sqlite> select pow(2,10);
1024
sqlite> select pow(10,3);
1000
$ sqlite3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> .load math.so
sqlite> select pow(2,3);
8
sqlite> select pow(2,10);
1024
sqlite> select pow(10,3);
1000
참 쉽죠? -_-
.load 커맨드는 내부적으로 dl_open()을 쓰는데 결국 공유라이브러리 링크와 마찬가지이므로 LD_LIBRARY_PATH에 math.so의 패스가 잡혀 있어야 합니다.
그리고 위의 함수는 그닥 stable 하지 않습니다. 그대로 갖다 쓰는 건 자제해 주세요 -_-;;
SQLite도 DB이므로 당연 데이터 타입들이 있는데 위의 코드는 타입 체킹도 전혀 하지 않고 오버플로우 처리 같은 것도 없기 때문에 문제를 일으킬 소지가 많습니다.
그럼 다음에 또 좀 더 detail한 내용으로...
이올린에 북마크하기
이올린에 추천하기'Programming Story > SQLite' 카테고리의 다른 글
| SQLite 사용자 함수 추가 (0) | 2009/04/03 |
|---|---|
| SQLite 페이지 핸들링(3) - 레코드 포맷 (0) | 2009/02/10 |
| SQLite 페이지 핸들링(2) - SQLite의 페이지 포맷 (0) | 2008/09/30 |
| SQLite 페이지 핸들링(1) - SQLite의 구조 (0) | 2008/08/03 |
