김형훈의 DB이야기

모바일 데이터베이스 SQLite의 세 번째 이야기

모바일 환경은 휴대성을 고려하기 위해서 한정된 자원을 사용해야 하는 많은 제약사항을 가지고 있다. 모바일 컴퓨팅 기기가 발전함에 따라서 기존과 같이 작은 용량을 가진 기기는 더 이상 찾아보기 힘들어졌으며, 메모리 기술이 발전함에 따라서 대용량을 지원하는 기기가 출현하게 되었다. 소용량의 메모리를 가지고 있는 기기의 경우에는 간단한 데이터 관리 시스템을 가지면 되었지만, 메모리가 커짐에 따라서 다루고자 하는 데이터가 많아지는 경우에는 안정성과 활용성을 고려한 데이터베이스 시스템이 사용되어야 한다. 모바일을 위한 요구사항을 만족시키기 위한 데이터베이스 솔루션으로 대표적인 것이 SQLite이다. SQLite는 구글의 안드로이드나 애플의 아이폰 등에서 사용되는 솔루션으로 사용자 측면과 관리자 측면에서 모두 뛰어난 접근성을 가지고 있는 데이터베이스 관리 시스템이다. SQLite에 대해서 현재까지 두 가지 이야기를 공유하였다. 본 세 번째 이야기에서는 검색을 위한 방법에 대해서 다루도록 한다.

모바일 기기에서 검색과 데이터베이스는 어떠한 관계를 가지고 있을까 휴대용 기기에서 대표적으로 사용되고 있는 데이터 관리방법이 데이터베이스를 사용하는 것이다. 다양한 형태를 가지는 데이터를 파일을 이용해서 관리할 수 있지만, 그렇게 될 경우에는 사용하기 번거롭다는 단점을 가진다. 데이터베이스를 사용함으로서 다양한 형태의 데이터를 하나의 테이블 또는 여러 개의 테이블로 관리할 수 있고, 손쉽게 데이터의 업데이트와 변경을 할 수 있다. 이러한 데이터베이스를 사용하는 환경에서는 데이터의 검색을 데이터베이스 내부의 레코드를 질의를 통해서 수행할 수 있다. 하지만, 이러한 경우에 검색을 수행할 때 시간이 많이 걸린다는 단점을 가지기 때문에 이를 해결하기 위한 노력이 행해지고 있다. 이와 같은 노력의 방법으로 데이터베이스 관리시스템의 성능을 최적화하는 것과 질의문 최적화가 있지만, 대표적인 방법으로는 검색할 때 데이터베이스의 내용을 추출하여 인덱스로 만들어 놓고 인덱스 내에서 검색을 수행하는 것이다.

SQLite에서는 이러한 검색방식으로서 FTS(Full Text Search)를 제공하고 있다. 지금부터 SQLite의 FTS(Full Text Search)에 대해서 간략히 살펴보도록 한다. FTS라는 것은 컴퓨터에 저장된 문서 또는 데이터베이스를 검색하기 위한 테크닉이다. FTS의 검색 엔진은 텍스트 문서 내에서 사용자가 요구한 매칭 결과가 있는지를 검색한다. FTS에서 필요한 인덱싱(Indexing)은 문서의 내용을 추출하고 이를 기반으로 검색하기 편리한 형태로 만드는 것이 핵심이다.
이 중에서 인덱싱은 FTS 엔진이 문서에서 질의한 내용을 찾을 때 사용되는 대상을 만들기 위한 과정이다. 일반적으로 문서가 적은 분량을 가지는 경우에는 순차적인 검색을 할 수 있지만, 문서의 양이 매우 많은 경우에는 그런 순차검색을 하게 되면 매우 많은 시간을 필요하게 되고 비효율적이게 된다. 따라서, 문서가 가지고 있는 본문 자체의 내용을 추출하여 하나 또는 여러 개의 리스트를 만들어 사용하는 방법을 사용한다. FTS를 사용하게 되면 데이터베이스에서 질의를 수행하여 데이터를 검색하는 방식과 비교해서 많이 빠르게 될 수 있을까 이렇게 향상된 성능을 가지게 하기 위해서는 사용자가 사용하는 FTS에 정확한 입력(질의)을 하던가, 정확도가 매우 높은 검색 알고리즘을 사용하면 된다. 그리고 인덱싱을 하기 이전에 데이터를 추출할 때 정확도가 높은 추출방법을 사용하면 된다. FTS는 문서로부터 텍스트를 추출하기 때문에 문서에서 추출할 때 문서 안의 텍스트들이 추출하기 쉬운 형태가 아닌 자연어의 연속으로 구성되어 있다면 사용자가 원하는 데이터를 얻고자 할 때 그 결과에 대해 정확도가 낮아질 수 있다. 질의의 정확도를 높이기 위한 방법으로 다음과 같은 방법이 사용된다.


그림 1. 질의 정확도 향상 방법
검색의 정확도를 높이기 위한 알고리즘으로 다음과 같은 시도가 적용될 수 있다.


그림 2. 향상된 검색 알고리즘

SQLite는 엔터프라이즈 급이 아닌 작은 모바일 환경에서도 동작이 원활하도록 만든 데이터베이스 솔루션이며, 사용하고자 하는 목적에 맞게 가볍고 빠른 전문 인덱스를 구현하여 휴대용 임베디드 기기에서 검색엔진으로 사용될 수 있다. 이를 위해서 SQLite에서 제공하는 FTS 솔루션은 다음과 같이 3가지가 존재한다.


그림 3. FTS 솔루션들

FTS1은 SQLite에 전문 인덱싱 기능을 추가한 것이다. 빌드할 때 사용할 수 있는 전처리 심볼로서 SQLITE_CORE와 SQLITE_ENABLE_FTS1을 사용하면 된다. FTS1을 공유 라이브러리 형태로 사용하고자 할 때, 다음과 같이 '.load' 셀 명령어를 사용하거나 SELECT 문을 사용할 수 있다.

sqlite> .load fts1
SELECT load_extension('fts1');

리스트 1. FTS1의 실행 전문 테이블은 완전히 인덱스화된 텍스트를 하나 이상의 칼럼을 통해서 관리하며, 'CREATE VIRTUAL TABLE'문을 사용하여 전문 테이블을 만들 수 있다.

sqlite>CREATE VIRTUAL TABLE recipe USING fts1(name, ingredients);
sqlite>INSERT INTO recipe (name, ingredients) VALUES ('broccoli stew','broccoli peppers cheese tomatoes');
sqlite>INSERT INTO recipe (name, ingredients) VALUES ('pumpkin stew', 'pumpkin onions garlic celery');
sqlite>INSERT INTO recipe (name, ingredients) VALUES ('broccoli pie', 'broccoli cheese onions flour');
sqlite>INSERT INTO recipe (name, ingredients) VALUES ('pumpkin pie', 'pumpkin sugar flour butter');

리스트 2. FTS1에서 전문 테이블의 생성 및 삽입 방법 MATCH 명령어를 사용해서 전문 테이블의 칼럼 내에서 전문 매칭을 수행할 수 있다. 다음은 테이블 내에서 검색한 결과를 나타내었다.

sqlite> SELECT rowid, name, ingredients FROM recipe WHERE name MATCH 'pie';
3|broccoli pie|broccoli cheese onions flour
4|pumpkin pie|pumpkin sugar flour butter

리스트 3. MATCH 명령어를 사용한 결과 OR 연산자를 사용해서 두 개 중에 적어도 하나를 포함하는 데이터를 추출해 낼 수 있으며, 어떤 특정 값을 배제하기 위한 '-' 연산자를 사용해서 제외할 수도 있다. 다음은 MATCH 명령어를 사용하여 어떤 특정 구문을 포함하고 있는 경우에 대한 검색이며, 그 외에 모든 다중 칼럼에 대해 동시에 쿼리를 실행할 수도 있다.

sqlite> SELECT name, ingredients from recipe FROM ingredients MATCH '"broccoli cheese"';
broccoli pie|broccoli cheese onions flour
리스트 4. 특정 구문 검색

전문 테이블은 오직 전문 인덱스된 스트링만을 저장한다. 따라서 이 값들을 다른 값들과 같이 저장하기 위해서는 전문 테이블과 일반 테이블을 함께 저장해야 하고 이를 위해서 rowid에 의한 JOIN 명령이 필요하다.

sqlite> CREATE TABLE email(sender text, priority integer);
sqlite> CREATE VIRTUAL TABLE email_text USING fts1(subject, body);
sqlite> INSERT INTO email (sender, priority) VALUES ('greg@foo.com', 4);
sqlite> INSERT INTO email_text (rowid, subject, body) VALUES (last_insert_rowid(), 'update', 'coming home now');
sqlite> SELECT sender, subject FROM email JOIN email_text ON email.rowid = email_text.rowid WHERE body MATCH 'jam';

리스트 5. 'JOIN'을 이용한 테이블의 결합 모듈을 일련의 대상들을 인덱스화할 때 각 텍스트들을 어떠한 순서를 가진 토큰으로 변환을 해야 한다. 이렇게 만들어진 각 토큰들이 인덱스 내에서의 한 텀이 되고, 전문 쿼리에 의해서 검색될 수 있게 된다. FTS1에서는 일반적인 토큰화 알고리즘으로서 연속적인 ASCII 문자들 (A-Z, a-z and 0-9) 순서대로 하며, ASCII 문자가 아닌 것들은 모두 무시된다. 그리고, 인덱스로 저장되기 이전에 소문자로 변환된다. 따라서 추후에 검색 시에 모든 전문 검색 시에는 대소문자의 구별이 필요 없게 된다. FTS2는 FTS1과 비슷하며 데이터의 삽입 속도와 질의 성능에 있어서 향상되었다. FTS3는 인덱싱 메커니즘을 사용하여 풀-텍스트 검색이 가능하다. SQLite에서 제공하는 FTS3을 통해서 전문 검색과 웹 검색도 가능할 수 있다. 이를 위해서 SQL을 이용한 가상 FTS3 테이블이 이용될 수 있다. 그리고, SQLite3 데이터베이스에서 텍스트 컬럼에 존재하는 모든 단어들을 인덱싱해야 한다. FTS3를 사용하기 위해서 테이블을 아래와 같이 가상 테이블로 생성시킨다. CREATE VIRTUAL TABLE posts USING fts3(title, body); 리스트 6. FTS3를 위한 테이블 생성과정 사용하기 위한 가상 테이블을 생성하고 난 이후에 다른 일반 테이블처럼 사용하기 위해서는 MATCH 키워드를 사용해야 한다. 이 키워드 MATCH는 FTS 기능을 사용하기 위한 필수 단어이다. FTS3 엔진은 'OR' 와 'NOT (-)' 형태 뿐만 아니라 '*'를 활용한 접두어 검색도 가능하다.

SELECT * FROM posts WHERE posts MATCH 'py*';

리스트 7. 일반 표현법을 이용한 FTS 검색방법 SQLite org에서 제공하는 소스는 기본적으로는 FTS3를 활성화시키지 않았다. 따라서, FTS3를 사용하려면, 우선적으로 이를 활성화부터 시켜야만 한다. 그렇지 않고서는 매번 시도해봐야 헛수고이다. 이를 활성화시키기 위해서는 다음과 같은 정의문을 sqlite3.c에 추가해야 만 한다. #define SQLITE_ENABLE_FTS3 리스트 8. FTS3 활성화 정의문 그림 4. sqlite3.c 내의 FTS 활성화 부분 ‘SQLITE_ENABLE_FTS3’를 활성화시키고 난 이후에 생성된 sqlite3 실행파일을 실행시키고 난 이후에 다음의 문장들을 입력하면 FTS3가 동작함을 알 수 있게 된다.

-- 생성

sqlite> CREATE VIRTUAL TABLE mail USING fts3(subject, body);

-- 테이블 추가

sqlite> INSERT INTO mail(docid, subject, body) VALUES(1, 'hardware feedback', 'found it too slow');

sqlite> INSERT INTO mail(docid, subject, body) VALUES(2, 'hardware feedback', 'no feedback');

sqlite> INSERT INTO mail(docid, subject, body) VALUES(3, 'slow lunch order',  'was a hardware problem');

-- 질의 예

sqlite> SELECT * FROM mail WHERE subject MATCH 'hardware';    

sqlite> SELECT * FROM mail WHERE body   MATCH 'feedback';  

리스트 9. FTS3 사용 예

SQLite의 FTS가 가지고 있는 단점은 단일 MATCH 명령문을 여러 개 중첩해서 사용해야 한다는 점이다. SQLite 가상 테이블에 여러 가지 종류의 칼럼들을 저장할 수 있지만, 텍스트에 관한 것은 FTS3를 통해서 MATCH 문법을 사용해서 검색될 수 있다. 참고로, FTS3의 리프 노드 포맷과 내부 노드 포맷은 다음과 같은 구조를 가지고 있다.
그림 5. FTS3의 리프 노드 포맷 그림 6. FTS3의 내부 노드 포맷 SQLite3에서는 FTS(Full Text Search)용으로 다음과 같은 모듈들을 지원한다. 검색을 위한 Create, Connect, Open, Update 함수 이외에 다양한 함수들이 지원됨을 알 수 있다.

static const sqlite3_module fts3Module = {
/* iVersion */ 0,
/* xCreate */ fts3CreateMethod,
/* xConnect */ fts3ConnectMethod,
/* xBestIndex */ fts3BestIndexMethod,
/* xDisconnect */ fts3DisconnectMethod,
/* xDestroy */ fts3DestroyMethod,
/* xOpen */ fts3OpenMethod,
/* xClose */ fulltextClose,
/* xFilter */ fts3FilterMethod,
/* xNext */ fts3NextMethod,
/* xEof */ fts3EofMethod,
/* xColumn */ fts3ColumnMethod,
/* xRowid */ fts3RowidMethod,
/* xUpdate */ fts3UpdateMethod,
/* xBegin */ fts3BeginMethod,
/* xSync */ fts3SyncMethod,
/* xCommit */ fts3CommitMethod,
/* xRollback */ fts3RollbackMethod,
/* xFindFunction */ fts3FindFunctionMethod,
/* xRename */ fts3RenameMethod,
};

리스트 10. FTS3 모듈 리스트

FTS3 모듈 중에서 생성과 연결을 담당하는 함수는 fts3CreateMethod()와 fts3ConnectMethod()이다. 이 두 가지 함수의 내부를 보면 fts3InitVtab() 함수를 호출하고 있음을 알 수 있다.

static int fts3ConnectMethod(
sqlite3 *db, /* 데이터베이스 연결 */
void *pAux, /* 토큰나이저 해시 테이블에 대한 포인터 */
int argc, /* argv 배열에서의 요소 개수 */
const char * const *argv, /* xCreate/xConnect 인자 배열 */
sqlite3_vtab **ppVtab, /* OUT: 새로운 sqlite3_vtab 오브젝트 */
char **pzErr /* OUT: sqlite3_malloc의 에러 메시지 */
){
return fts3InitVtab(0, db, pAux, argc, argv, ppVtab, pzErr);
}

리스트 11. fts3ConnectMethod 함수

static int fts3CreateMethod(
sqlite3 *db, /* 데이터베이스 연결 */
void *pAux, /* 토큰나이저 해시 테이블에 대한 포인터 */
int argc, /* argv 배열에서의 요소 개수 */
const char * const *argv, /* xCreate/xConnect 인자 배열 */
sqlite3_vtab **ppVtab, /* OUT: 새로운 sqlite3_vtab 오브젝트 */
char **pzErr /* OUT: sqlite3_malloc의 에러 메시지 */
){
return fts3InitVtab(1, db, pAux, argc, argv, ppVtab, pzErr);
}

리스트 12. fts3CreateMethod 함수



위에서 나타낸 모듈 이외에 FTS에서 사용되는 테이블을 저장하기 위해서 사용되는 함수로서 fts3CreateTables()가 있다.

static int fts3CreateTables(Fts3Table *p){
int rc; /* 리턴 코드 */
int i; /* 반복 변수 */
char *zContentCols; /* %_content 테이블의 칼럼 */
char *zSql; /* 필요한 테이블을 생성하기 위한 SQL 스크립트 */
/* 사용자 칼럼 리스트 생성 */
zContentCols = sqlite3_mprintf("docid INTEGER PRIMARY KEY");
for(i=0; zContentCols && inColumn; i++){
char *z = p->azColumn[i];
zContentCols = sqlite3_mprintf("%z, 'c%d%q'", zContentCols, i, z);
}
/* 전체 SQL 스크립트 생성 */
zSql = sqlite3_mprintf(
"CREATE TABLE %Q.'%q_content'(%s);"
"CREATE TABLE %Q.'%q_segments'(blockid INTEGER PRIMARY KEY, block BLOB);"
"CREATE TABLE %Q.'%q_segdir'("
"level INTEGER,"
"idx INTEGER,"
"start_block INTEGER,"
"leaves_end_block INTEGER,"
"end_block INTEGER,"
"root BLOB,"
"PRIMARY KEY(level, idx)"
");",
p->zDb, p->zName, zContentCols, p->zDb, p->zName, p->zDb, p->zName
);
if( zContentCols==0 || zSql==0 ){
rc = SQLITE_NOMEM;
}else{
rc = sqlite3_exec(p->db, zSql, 0, 0, 0);
}
sqlite3_free(zSql);
sqlite3_free(zContentCols);
return rc;
}
리스트 13. fts3CreateTables 함수

필자소개

김형훈(asinayo73@hotmail.com)