FTS3 및 FTS4
FTS3 및 FTS4 (전문 검색 확장)
FTS3 과 FTS4 는 SQLite 에 내장된 전체 텍스트 인덱스(full-text index)를 가진 특별한 테이블("FTS 테이블")을 만들 수 있게 해주는 확장 모듈이에요. 테이블에 많은 대용량 문서가 있더라도 하나 이상의 단어("토큰")를 포함하는 모든 행을 효율적으로 검색할 수 있어요.
본문
1. FTS3 과 FTS4 소개
FTS3 과 FTS4 확장 모듈은 사용자가 내장된 전체 텍스트 인덱스(이하 "FTS 테이블")를 가진 특별한 테이블을 만들 수 있게 해줘요. 전체 텍스트 인덱스는 테이블에 많은 대용량 문서가 있더라도 하나 이상의 단어(이하 "토큰")를 포함하는 모든 행을 데이터베이스에서 효율적으로 쿼리할 수 있게 해줘요.
예를 들어, "Enron E-Mail Dataset"의 517430 개 문서 각각을 다음 SQL 스크립트로 만든 FTS 테이블과 일반 SQLite 테이블에 삽입한다고 가정해볼게요:
CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT); /* FTS3 table */
CREATE TABLE enrondata2(content TEXT); /* Ordinary table */
그러면 "linux"라는 단어를 포함하는 데이터베이스의 문서 수(351)를 찾기 위해 아래 두 쿼리 중 하나를 실행할 수 있어요. 한 데스크톱 PC 하드웨어 구성을 사용하면 FTS3 테이블에 대한 쿼리는 약 0.03 초 만에 반환되지만, 일반 테이블에 대한 쿼리는 22.5 초가 걸려요.
SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux'; /* 0.03 seconds */
SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */
물론 위 두 쿼리는 완전히 동등하지는 않아요. 예를 들어 LIKE 쿼리는 "linuxophobe"나 "EnterpriseLinux" 같은 용어를 포함하는 행과도 일치하지만(실제로 Enron E-Mail Dataset 에는 그런 용어가 없음), FTS3 테이블의 MATCH 쿼리는 "linux"를 개별 토큰으로 포함하는 행만 선택해요. 두 검색 모두 대소문자를 구분하지 않아요. FTS3 테이블은 디스크에서 약 2006 MB 를 차지하는 반면 일반 테이블은 단지 1453 MB 만 차지해요. 위 SELECT 쿼리를 수행하는 데 사용한 것과 같은 하드웨어 구성을 사용하면 FTS3 테이블을 채우는 데 31 분 미만이 걸렸지만, 일반 테이블은 25 분이 걸렸어요.
1.1. FTS3 과 FTS4 의 차이점
FTS3 과 FTS4 는 거의 동일해요. 대부분의 코드를 공유하고 인터페이스도 같아요. 차이점은 다음과 같아요:
- FTS4 는 매우 흔한 용어(테이블 행의 큰 비율에 존재)를 포함하는 전체 텍스트 쿼리의 성능을 크게 개선할 수 있는 쿼리 성능 최적화를 포함해요.
- FTS4 는 matchinfo() 함수와 함께 사용할 수 있는 몇 가지 추가 옵션을 지원해요.
- 성능 최적화와 추가 matchinfo() 옵션을 지원하기 위해 두 개의 새 섀도 테이블에 추가 정보를 디스크에 저장하므로, FTS4 테이블은 FTS3 으로 만든 동등한 테이블보다 더 많은 디스크 공간을 차지할 수 있어요. 보통 오버헤드는 1-2% 이하이지만, FTS 테이블에 저장된 문서가 아주 작으면 10% 까지 높을 수 있어요. FTS4 테이블 선언의 일부로 "matchinfo=fts3" 지시어를 지정하면 오버헤드를 줄일 수 있지만, 지원되는 추가 matchinfo() 옵션 중 일부를 희생하는 대가가 있어요.
- FTS4 는 데이터를 압축된 형태로 저장할 수 있게 해주는 훅(compress 와 uncompress 옵션)을 제공해 디스크 사용량과 IO 를 줄여줘요.
FTS4 는 FTS3 의 개선판이에요. FTS3 은 SQLite 버전 3.5.0 (2007-09-04) 부터 사용 가능해졌고, FTS4 의 개선 사항은 SQLite 버전 3.7.4 (2010-12-07) 에 추가됐어요.
애플리케이션에서 FTS3 과 FTS4 중 어떤 모듈을 사용해야 할까요? FTS4 는 때로 FTS3 보다 상당히 빠르고, 쿼리에 따라 몇 자릿수까지 빠를 수 있지만, 일반적인 경우 두 모듈의 성능은 비슷해요. FTS4 는 또한 MATCH 작업의 결과를 순위 매기는 데 유용할 수 있는 향상된 matchinfo() 출력을 제공해요. 반면 matchinfo=fts3 지시어가 없으면 FTS4 는 FTS3 보다 디스크 공간을 조금 더 필요로 하지만, 대부분의 경우 단지 몇 퍼센트에 불과해요.
최신 애플리케이션에는 FTS4 가 권장돼요. 하지만 이전 SQLite 버전과의 호환성이 중요하다면 FTS3 이 보통 충분히 잘 작동해요.
1.2. FTS 테이블 생성 및 제거
다른 가상 테이블 타입처럼 새 FTS 테이블은 CREATE VIRTUAL TABLE 문으로 만들어요. USING 키워드 뒤에 오는 모듈 이름은 "fts3" 또는 "fts4" 예요. 가상 테이블 모듈 인자는 비워 둘 수 있는데, 이 경우 "content"라는 단일 사용자 정의 컬럼을 가진 FTS 테이블이 만들어져요. 또는 모듈 인자에 쉼표로 구분된 컬럼 이름 목록을 전달할 수도 있어요.
CREATE VIRTUAL TABLE 문의 일부로 FTS 테이블에 대한 컬럼 이름을 명시적으로 제공하면 각 컬럼에 선택적으로 데이터타입 이름을 지정할 수 있어요. 이것은 순수한 구문 설탕이며, 제공된 타입 이름은 FTS 나 SQLite 코어가 어떤 목적으로도 사용하지 않아요. FTS 컬럼 이름과 함께 지정되는 제약 조건도 마찬가지예요. 파싱되지만 시스템이 어떤 방식으로도 사용하거나 기록하지 않아요.
-- Create an FTS table named "data" with one column - "content":
CREATE VIRTUAL TABLE data USING fts3();
-- Create an FTS table named "pages" with three columns:
CREATE VIRTUAL TABLE pages USING fts4(title, keywords, body);
-- Create an FTS table named "mail" with two columns. Datatypes
-- and column constraints are specified along with each column. These
-- are completely ignored by FTS and SQLite.
CREATE VIRTUAL TABLE mail USING fts3(
subject VARCHAR(256) NOT NULL,
body TEXT CHECK(length(body)<10240)
);
FTS 테이블을 만들기 위해 사용되는 CREATE VIRTUAL TABLE 문에 전달되는 모듈 인자는 컬럼 목록뿐만 아니라 토크나이저(tokenizer)를 지정하는 데도 사용될 수 있어요. 이는 컬럼 이름 대신 "tokenize=
-- Create an FTS table named "papers" with two columns that uses
-- the tokenizer "porter".
CREATE VIRTUAL TABLE papers USING fts3(author, document, tokenize=porter);
-- Create an FTS table with a single column - "content" - that uses
-- the "simple" tokenizer.
CREATE VIRTUAL TABLE data USING fts4(tokenize=simple);
-- Create an FTS table with two columns that uses the "icu" tokenizer.
-- The qualifier "en_AU" is passed to the tokenizer implementation
CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU);
FTS 테이블은 일반 DROP TABLE 문으로 데이터베이스에서 제거할 수 있어요. 예를 들어:
-- Create, then immediately drop, an FTS4 table.
CREATE VIRTUAL TABLE data USING fts4();
DROP TABLE data;
1.3. FTS 테이블 채우기
FTS 테이블은 일반 SQLite 테이블과 같은 방식으로 INSERT, UPDATE, DELETE 문으로 채워져요.
사용자가 이름 지은 컬럼(CREATE VIRTUAL TABLE 문의 일부로 모듈 인자가 지정되지 않았다면 "content" 컬럼)뿐만 아니라, 각 FTS 테이블에는 "rowid" 컬럼이 있어요. FTS 테이블의 rowid 는 일반 SQLite 테이블의 rowid 컬럼과 같은 방식으로 동작하지만, FTS 테이블의 rowid 컬럼에 저장된 값은 VACUUM 명령으로 데이터베이스를 다시 만들 때도 변하지 않아요. FTS 테이블의 경우 "docid" 가 일반적인 "rowid", "oid", "oid" 식별자와 함께 별칭으로 허용돼요. 테이블에 이미 존재하는 docid 값으로 행을 삽입하거나 갱신하려는 시도는 일반 SQLite 테이블과 마찬가지로 에러예요.
"docid" 와 rowid 컬럼의 일반 SQLite 별칭 사이에는 또 하나의 미묘한 차이가 있어요. 보통 INSERT 나 UPDATE 문이 rowid 컬럼의 둘 이상의 별칭에 개별 값을 할당하면 SQLite 는 INSERT 나 UPDATE 문에 지정된 그러한 값 중 가장 오른쪽 값을 데이터베이스에 써요. 하지만 FTS 테이블에 삽입하거나 갱신할 때 "docid" 와 하나 이상의 SQLite rowid 별칭 모두에 NULL 이 아닌 값을 할당하는 것은 에러로 간주돼요. 아래 예를 참고하세요.
-- Create an FTS table
CREATE VIRTUAL TABLE pages USING fts4(title, body);
-- Insert a row with a specific docid value.
INSERT INTO pages(docid, title, body) VALUES(53, 'Home Page', 'SQLite is a software...');
-- Insert a row and allow FTS to assign a docid value using the same algorithm as
-- SQLite uses for ordinary tables. In this case the new docid will be 54,
-- one greater than the largest docid currently present in the table.
INSERT INTO pages(title, body) VALUES('Download', 'All SQLite source code...');
-- Change the title of the row just inserted.
UPDATE pages SET title = 'Download SQLite' WHERE rowid = 54;
-- Delete the entire table contents.
DELETE FROM pages;
-- The following is an error. It is not possible to assign non-NULL values to both
-- the rowid and docid columns of an FTS table.
INSERT INTO pages(rowid, docid, title, body) VALUES(1, 2, 'A title', 'A document body');
전체 텍스트 쿼리를 지원하기 위해 FTS 는 데이터 세트에 나타나는 각 고유한 용어 또는 단어를 테이블 내용 내에서 나타나는 위치에 매핑하는 역 인덱스(inverted index)를 유지해요. 궁금한 사람을 위해 이 인덱스를 데이터베이스 파일에 저장하는 데 사용되는 데이터 구조의 완전한 설명이 아래에 나와 있어요. 이 데이터 구조의 특징은 어떤 시점에서든 데이터베이스가 하나의 인덱스 b-tree 가 아니라 여러 개의 다른 b-tree 를 포함할 수 있고, 행이 삽입·갱신·삭제됨에 따라 점진적으로 병합된다는 점이에요. 이 기법은 FTS 테이블에 쓸 때 성능을 개선하지만, 인덱스를 사용하는 전체 텍스트 쿼리에 일부 오버헤드를 발생시켜요. 특별한 "optimize" 명령, 즉 "INSERT INTO
예를 들어 "docs"라는 FTS 테이블의 전체 텍스트 인덱스를 최적화하려면:
-- Optimize the internal structure of FTS table "docs".
INSERT INTO docs(docs) VALUES('optimize');
위 문장은 어떤 사람에게는 구문상 틀린 것처럼 보일 수 있어요. 설명은 simple fts 쿼리를 설명하는 섹션을 참고하세요.
SELECT 문을 사용해 optimize 작업을 호출하는 또 다른, 더 이상 쓰지 않는 방법이 있어요. 새 코드는 위 INSERT 와 유사한 문장을 사용해 FTS 구조를 최적화해야 해요.
1.4. 간단한 FTS 쿼리
다른 모든 SQLite 테이블처럼, 가상이든 아니든, FTS 테이블에서 데이터는 SELECT 문으로 검색돼요.
FTS 테이블은 두 가지 다른 형태의 SELECT 문으로 효율적으로 쿼리할 수 있어요:
- rowid 로 쿼리. SELECT 문의 WHERE 절에 "rowid = ?" 형태의 하위 절이 포함되어 있으면(? 는 SQL 표현식), FTS 는 SQLite INTEGER PRIMARY KEY 인덱스에 해당하는 것을 사용해 요청된 행을 직접 검색할 수 있어요.
- 전체 텍스트 쿼리. SELECT 문의 WHERE 절에 "
MATCH ?" 형태의 하위 절이 포함되어 있으면 FTS 는 내장된 전체 텍스트 인덱스를 사용해 MATCH 절의 오른쪽 피연산자로 지정된 전체 텍스트 쿼리 문자열과 일치하는 문서로 검색을 제한할 수 있어요.
이 두 쿼리 전략 중 어느 것도 사용할 수 없으면 FTS 테이블에 대한 모든 쿼리는 전체 테이블의 선형 스캔으로 구현돼요. 테이블에 많은 양의 데이터가 있으면 이것은 비현실적인 접근일 수 있어요(이 페이지의 첫 예는 현대 PC 를 사용해 1.5 GB 의 데이터 선형 스캔이 약 30 초 걸린다는 것을 보여줘요).
-- The examples in this block assume the following FTS table:
CREATE VIRTUAL TABLE mail USING fts3(subject, body);
SELECT * FROM mail WHERE rowid = 15; -- Fast. Rowid lookup.
SELECT * FROM mail WHERE body MATCH 'sqlite'; -- Fast. Full-text query.
SELECT * FROM mail WHERE mail MATCH 'search'; -- Fast. Full-text query.
SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20; -- Fast. Rowid lookup.
SELECT * FROM mail WHERE subject = 'database'; -- Slow. Linear scan.
SELECT * FROM mail WHERE subject MATCH 'database'; -- Fast. Full-text query.
위의 모든 전체 텍스트 쿼리에서 MATCH 연산자의 오른쪽 피연산자는 단일 용어로 구성된 문자열이에요. 이 경우 MATCH 표현식은 지정된 단어("sqlite", "search", "database" 중 어느 예를 보느냐에 따라)의 인스턴스를 하나 이상 포함하는 모든 문서에 대해 true 로 평가돼요. MATCH 연산자의 오른쪽 피연산자로 단일 용어를 지정하면 가능한 가장 단순하고 가장 흔한 종류의 전체 텍스트 쿼리가 돼요. 하지만 더 복잡한 쿼리도 가능해요. 구문 검색, 용어-접두어 검색, 정의된 근접도 안에서 서로 인접해 발생하는 용어 조합을 포함하는 문서 검색 등이 포함돼요. 전체 텍스트 인덱스를 쿼리할 수 있는 다양한 방법은 아래에 설명돼 있어요.
보통 전체 텍스트 쿼리는 대소문자를 구분하지 않아요. 하지만 이는 조회되는 FTS 테이블이 사용하는 특정 토크나이저에 따라 달라져요. 자세한 내용은 토크나이저 섹션을 참고하세요.
위 문단은 오른쪽 피연산자가 단순 용어인 MATCH 연산자가 지정된 용어를 포함하는 모든 문서에 대해 true 로 평가된다는 것을 언급했어요. 이 맥락에서 "문서"는 MATCH 연산자의 왼쪽 피연산자로 사용된 식별자에 따라 FTS 테이블의 한 행의 단일 컬럼에 저장된 데이터 또는 단일 행의 모든 컬럼 내용을 가리킬 수 있어요. MATCH 연산자의 왼쪽 피연산자로 지정된 식별자가 FTS 테이블 컬럼 이름이면 검색 용어가 포함되어야 하는 문서는 지정된 컬럼에 저장된 값이에요. 하지만 식별자가 FTS 테이블 자체의 이름이면 MATCH 연산자는 어떤 컬럼이라도 검색 용어를 포함하는 FTS 테이블의 각 행에 대해 true 로 평가돼요. 다음 예가 이를 보여줘요:
-- Example schema
CREATE VIRTUAL TABLE mail USING fts3(subject, body);
-- Example table population
INSERT INTO mail(docid, subject, body) VALUES(1, 'software feedback', 'found it too slow');
INSERT INTO mail(docid, subject, body) VALUES(2, 'software feedback', 'no feedback');
INSERT INTO mail(docid, subject, body) VALUES(3, 'slow lunch order', 'was a software problem');
-- Example queries
SELECT * FROM mail WHERE subject MATCH 'software'; -- Selects rows 1 and 2
SELECT * FROM mail WHERE body MATCH 'feedback'; -- Selects row 2
SELECT * FROM mail WHERE mail MATCH 'software'; -- Selects rows 1, 2 and 3
SELECT * FROM mail WHERE mail MATCH 'slow'; -- Selects rows 1 and 3
언뜻 보면 위 예의 마지막 두 전체 텍스트 쿼리가 구문상 틀린 것처럼 보일 수 있어요. SQL 표현식으로 사용된 테이블 이름("mail")이 있기 때문이에요. 이것이 허용되는 이유는 각 FTS 테이블이 실제로 테이블 자체와 같은 이름(이 경우 "mail")의 HIDDEN 컬럼을 가지기 때문이에요. 이 컬럼에 저장된 값은 애플리케이션에 의미가 없지만 MATCH 연산자의 왼쪽 피연산자로 사용될 수 있어요. 이 특별한 컬럼은 FTS 보조 함수에 인자로 전달될 수도 있어요.
다음 예가 위 내용을 보여줘요. "docs", "docs.docs", "main.docs.docs" 표현식은 모두 컬럼 "docs"를 참조해요. 하지만 "main.docs" 표현식은 어떤 컬럼도 참조하지 않아요. 테이블을 참조하는 데 사용될 수 있지만, 아래에 사용된 맥락에서는 테이블 이름이 허용되지 않아요.
-- Example schema
CREATE VIRTUAL TABLE docs USING fts4(content);
-- Example queries
SELECT * FROM docs WHERE docs MATCH 'sqlite'; -- OK.
SELECT * FROM docs WHERE docs.docs MATCH 'sqlite'; -- OK.
SELECT * FROM docs WHERE main.docs.docs MATCH 'sqlite'; -- OK.
SELECT * FROM docs WHERE main.docs MATCH 'sqlite'; -- Error.
1.5. 요약
사용자 관점에서 FTS 테이블은 여러 면에서 일반 SQLite 테이블과 비슷해요. 일반 테이블과 마찬가지로 INSERT, UPDATE, DELETE 명령으로 FTS 테이블에 데이터를 추가하고, 수정하고, 제거할 수 있어요. 마찬가지로 SELECT 명령으로 데이터를 쿼리할 수 있어요. 다음 목록은 FTS 와 일반 테이블의 차이점을 요약해요:
- 모든 가상 테이블 타입과 마찬가지로 FTS 테이블에 첨부된 인덱스나 트리거를 만들 수 없어요. 또한 ALTER TABLE 명령으로 FTS 테이블에 추가 컬럼을 더하는 것도 불가능해요(단, ALTER TABLE 로 FTS 테이블 이름을 바꾸는 것은 가능해요).
- FTS 테이블을 만드는 데 사용되는 "CREATE VIRTUAL TABLE" 문의 일부로 지정된 데이터 타입은 완전히 무시돼요. 삽입된 값에 타입 어피니티를 적용하는 일반 규칙 대신, FTS 테이블 컬럼(특별한 rowid 컬럼 제외)에 삽입되는 모든 값은 저장되기 전에 TEXT 타입으로 변환돼요.
- FTS 테이블은 모든 가상 테이블이 지원하는 rowid 컬럼을 참조하는 특별한 별칭 "docid" 를 허용해요.
- 내장된 전체 텍스트 인덱스에 기반한 쿼리를 위해 FTS MATCH 연산자가 지원돼요.
- 전체 텍스트 쿼리를 지원하기 위해 FTS 보조 함수인 snippet(), offsets(), matchinfo() 를 사용할 수 있어요.
- 모든 FTS 테이블에는 테이블 자체와 같은 이름의 숨은 컬럼이 있어요. 숨은 컬럼의 각 행에 포함된 값은 MATCH 연산자의 왼쪽 피연산자로만, 또는 FTS 보조 함수 중 하나의 가장 왼쪽 인자로만 유용한 blob 이에요.
2. FTS3 과 FTS4 컴파일 및 활성화
FTS3 과 FTS4 는 SQLite 코어 소스 코드에 포함되어 있지만 기본적으로 활성화되지는 않아요. FTS 기능을 활성화해 SQLite 를 빌드하려면 컴파일 시 전처리기 매크로 SQLITE_ENABLE_FTS3 을 정의해요. 새 애플리케이션은 향상된 쿼리 구문(아래 참고)을 활성화하기 위해 SQLITE_ENABLE_FTS3_PARENTHESIS 매크로도 정의해야 해요. 보통은 다음 두 스위치를 컴파일러 명령줄에 추가해 이루어져요:
-DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_FTS3_PARENTHESIS
FTS3 을 활성화하면 FTS4 도 사용 가능해진다는 점에 주의하세요. 별도의 SQLITE_ENABLE_FTS4 컴파일 타임 옵션은 없어요. SQLite 빌드는 FTS3 과 FTS4 를 둘 다 지원하거나 둘 다 지원하지 않아요.
정식 빌드 시스템을 사용한다면 'configure' 스크립트를 실행하면서 CPPFLAGS 환경 변수를 설정하는 것이 이 매크로를 설정하는 쉬운 방법이에요. 예를 들어 다음 명령은:
CPPFLAGS="-DSQLITE_ENABLE_FTS3_PARENTHESIS" ./configure --enable-fts3 <configure options>
여기서
FTS3 과 FTS4 는 가상 테이블이므로 SQLITE_ENABLE_FTS3 컴파일 타임 옵션은 SQLITE_OMIT_VIRTUALTABLE 옵션과 호환되지 않아요.
SQLite 빌드가 FTS 모듈을 포함하지 않으면 FTS3 이나 FTS4 테이블을 만들거나 기존 FTS 테이블을 어떤 방식으로든 드롭하거나 접근하려는 SQL 문을 준비하는 시도는 모두 실패해요. 반환되는 에러 메시지는 "no such module: ftsN"(N 은 3 또는 4)과 비슷할 거예요.
C 버전의 ICU 라이브러리가 있으면 FTS 는 SQLITE_ENABLE_ICU 전처리기 매크로를 정의해 컴파일할 수 있어요. 이 매크로로 컴파일하면 ICU 라이브러리를 사용해 지정된 언어와 로케일의 규칙을 따라 문서를 용어(단어)로 분할하는 FTS 토크나이저가 활성화돼요.
-DSQLITE_ENABLE_ICU
정식 빌드 트리 버전 3.48 이상에서는 configure 스크립트 플래그로 ICU 를 활성화할 수 있어요. 자세한 내용은 ./configure --help 를 참고하세요.
3. 전체 텍스트 인덱스 쿼리
FTS 테이블에서 가장 유용한 것은 내장된 전체 텍스트 인덱스를 사용해 수행할 수 있는 쿼리예요. 전체 텍스트 쿼리는 FTS 테이블에서 데이터를 읽는 SELECT 문의 WHERE 절의 일부로 "
FTS 테이블은 세 가지 기본 쿼리 타입을 지원해요:
- 토큰 또는 토큰 접두어 쿼리. FTS 테이블은 지정된 용어를 포함하는 모든 문서(위에서 설명한 간단한 경우) 또는 지정된 접두어를 가진 용어를 포함하는 모든 문서에 대해 쿼리할 수 있어요. 봤듯이 특정 용어의 쿼리 표현식은 단순히 그 용어 자체예요. 용어 접두어를 검색하는 데 사용되는 쿼리 표현식은 그 뒤에 '*' 문자를 붙인 접두어 자체예요. 예를 들어:
-- Virtual table declaration
CREATE VIRTUAL TABLE docs USING fts3(title, body);
-- Query for all documents containing the term "linux":
SELECT * FROM docs WHERE docs MATCH 'linux';
-- Query for all documents containing a term with the prefix "lin". This will match
-- all documents that contain "linux", but also those that contain terms "linear",
-- "linker", "linguistic" and so on.
SELECT * FROM docs WHERE docs MATCH 'lin*';
- 보통 토큰 또는 토큰 접두어 쿼리는 MATCH 연산자의 왼쪽으로 지정된 FTS 테이블 컬럼에 대해 일치돼요. 또는 FTS 테이블 자체와 같은 이름의 특별한 컬럼을 지정하면 모든 컬럼에 대해 일치돼요. 이는 기본 토큰 쿼리 앞에 컬럼 이름과 그 뒤에 ":" 문자를 지정해 재정의할 수 있어요. ":" 와 쿼리할 토큰 사이에는 공백이 있어도 되지만, 컬럼 이름과 ":" 문자 사이에는 공백이 있으면 안 돼요. 예를 들어:
-- Query the database for documents for which the term "linux" appears in
-- the document title, and the term "problems" appears in either the title
-- or body of the document.
SELECT * FROM docs WHERE docs MATCH 'title:linux problems';
-- Query the database for documents for which the term "linux" appears in
-- the document title, and the term "driver" appears in the body of the document
-- ("driver" may also appear in the title, but this alone will not satisfy the
-- query criteria).
SELECT * FROM docs WHERE body MATCH 'title:linux driver';
- FTS 테이블이 FTS4 테이블이면(FTS3 가 아니라), 토큰 앞에 "^" 문자를 붙일 수도 있어요. 이 경우 일치하려면 토큰이 일치하는 행의 어떤 컬럼에서든 가장 첫 번째 토큰으로 나타나야 해요. 예:
-- All documents for which "linux" is the first token of at least one
-- column.
SELECT * FROM docs WHERE docs MATCH '^linux';
-- All documents for which the first token in column "title" begins with "lin".
SELECT * FROM docs WHERE body MATCH 'title: ^lin*';
- 구문 쿼리(Phrase queries). 구문 쿼리는 개입하는 토큰 없이 지정된 순서로 지정된 용어 또는 용어 접두어 세트를 포함하는 모든 문서를 검색하는 쿼리예요. 구문 쿼리는 공백으로 구분된 용어 또는 용어 접두어 시퀀스를 큰따옴표(")로 감싸서 지정해요. 예를 들어:
-- Query for all documents that contain the phrase "linux applications".
SELECT * FROM docs WHERE docs MATCH '"linux applications"';
-- Query for all documents that contain a phrase that matches "lin* app*". As well as
-- "linux applications", this will match common phrases such as "linoleum appliances"
-- or "link apprentice".
SELECT * FROM docs WHERE docs MATCH '"lin* app*"';
- NEAR 쿼리. NEAR 쿼리는 두 개 이상의 지정된 용어나 구문을 서로 지정된 근접도 안에서(기본적으로 10 개 이하의 개입 용어) 포함하는 문서를 반환하는 쿼리예요. NEAR 쿼리는 두 구문, 토큰 또는 토큰 접두어 쿼리 사이에 키워드 "NEAR" 를 넣어 지정해요. 기본값이 아닌 근접도를 지정하려면 "NEAR/
" 형태의 연산자를 사용할 수 있는데, 여기서 은 허용되는 최대 개입 용어 수예요. 예를 들어:
-- Virtual table declaration.
CREATE VIRTUAL TABLE docs USING fts4();
-- Virtual table data.
INSERT INTO docs VALUES('SQLite is an ACID compliant embedded relational database management system');
-- Search for a document that contains the terms "sqlite" and "database" with
-- not more than 10 intervening terms. This matches the only document in
-- table docs (since there are only six terms between "SQLite" and "database"
-- in the document).
SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR database';
-- Search for a document that contains the terms "sqlite" and "database" with
-- not more than 6 intervening terms. This also matches the only document in
-- table docs. Note that the order in which the terms appear in the document
-- does not have to be the same as the order in which they appear in the query.
SELECT * FROM docs WHERE docs MATCH 'database NEAR/6 sqlite';
-- Search for a document that contains the terms "sqlite" and "database" with
-- not more than 5 intervening terms. This query matches no documents.
SELECT * FROM docs WHERE docs MATCH 'database NEAR/5 sqlite';
-- Search for a document that contains the phrase "ACID compliant" and the term
-- "database" with not more than 2 terms separating the two. This matches the
-- document stored in table docs.
SELECT * FROM docs WHERE docs MATCH 'database NEAR/2 "ACID compliant"';
-- Search for a document that contains the phrase "ACID compliant" and the term
-- "sqlite" with not more than 2 terms separating the two. This also matches
-- the only document stored in table docs.
SELECT * FROM docs WHERE docs MATCH '"ACID compliant" NEAR/2 sqlite';
- 단일 쿼리에 NEAR 연산자가 두 개 이상 나타날 수 있어요. 이 경우 NEAR 연산자로 분리된 각 용어 또는 구문 쌍이 문서 안에서 서로 지정된 근접도 안에 나타나야 해요. 위 예시 블록과 같은 테이블과 데이터를 사용해:
-- The following query selects documents that contains an instance of the term
-- "sqlite" separated by two or fewer terms from an instance of the term "acid",
-- which is in turn separated by two or fewer terms from an instance of the term
-- "relational".
SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational';
-- This query matches no documents. There is an instance of the term "sqlite" with
-- sufficient proximity to an instance of "acid" but it is not sufficiently close
-- to an instance of the term "relational".
SELECT * FROM docs WHERE docs MATCH 'acid NEAR/2 sqlite NEAR/2 relational';
구문 및 NEAR 쿼리는 행 내의 여러 컬럼에 걸칠 수 없어요.
위에서 설명한 세 가지 기본 쿼리 타입을 사용해 지정된 기준과 일치하는 문서 집합에 대해 전체 텍스트 인덱스를 쿼리할 수 있어요. FTS 쿼리 표현식 언어를 사용하면 기본 쿼리의 결과에 다양한 집합 연산을 수행할 수 있어요. 현재 세 가지 연산이 지원돼요:
- AND 연산자는 두 문서 집합의 교집합을 결정해요.
- OR 연산자는 두 문서 집합의 합집합을 계산해요.
- NOT 연산자(또는 표준 구문을 사용하면 단항 "-" 연산자)는 한 문서 집합을 다른 집합에 상대적인 **여집합(relative complement)**으로 계산하는 데 사용될 수 있어요.
FTS 모듈은 전체 텍스트 쿼리 구문의 두 가지 약간 다른 버전, "표준" 쿼리 구문과 "향상된" 쿼리 구문 중 하나를 사용하도록 컴파일될 수 있어요. 위에서 설명한 기본 용어, 용어-접두어, 구문, NEAR 쿼리는 두 구문 버전 모두에서 동일해요. 집합 연산을 지정하는 방식은 약간 달라요. 다음 두 하위 섹션은 두 쿼리 구문 중 집합 연산에 해당하는 부분을 설명해요. 컴파일 방법에 대한 설명은 fts 컴파일 설명을 참고하세요.
3.1. 향상된 쿼리 구문을 사용한 집합 연산
향상된 쿼리 구문은 AND, OR, NOT 이진 집합 연산자를 지원해요. 연산자의 두 피연산자 각각은 기본 FTS 쿼리이거나 다른 AND, OR, NOT 집합 연산의 결과일 수 있어요. 연산자는 대문자로 입력해야 해요. 그렇지 않으면 집합 연산자 대신 기본 용어 쿼리로 해석돼요.
AND 연산자는 암시적으로 지정될 수 있어요. FTS 쿼리 문자열에서 두 기본 쿼리가 연산자 없이 분리되어 나타나면, 결과는 두 기본 쿼리가 AND 연산자로 분리된 것과 같아요. 예를 들어 쿼리 표현식 "implicit operator" 는 "implicit AND operator" 의 더 간결한 버전이에요.
-- Virtual table declaration
CREATE VIRTUAL TABLE docs USING fts3();
-- Virtual table data
INSERT INTO docs(docid, content) VALUES(1, 'a database is a software system');
INSERT INTO docs(docid, content) VALUES(2, 'sqlite is a software system');
INSERT INTO docs(docid, content) VALUES(3, 'sqlite is a database');
-- Return the set of documents that contain the term "sqlite", and the
-- term "database". This query will return the document with docid 3 only.
SELECT * FROM docs WHERE docs MATCH 'sqlite AND database';
-- Again, return the set of documents that contain both "sqlite" and
-- "database". This time, use an implicit AND operator. Again, document
-- 3 is the only document matched by this query.
SELECT * FROM docs WHERE docs MATCH 'database sqlite';
-- Query for the set of documents that contains either "sqlite" or "database".
-- All three documents in the database are matched by this query.
SELECT * FROM docs WHERE docs MATCH 'sqlite OR database';
-- Query for all documents that contain the term "database", but do not contain
-- the term "sqlite". Document 1 is the only document that matches this criteria.
SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite';
-- The following query matches no documents. Because "and" is in lowercase letters,
-- it is interpreted as a basic term query instead of an operator. Operators must
-- be specified using capital letters. In practice, this query will match any documents
-- that contain each of the three terms "database", "and" and "sqlite" at least once.
-- No documents in the example data above match this criteria.
SELECT * FROM docs WHERE docs MATCH 'database and sqlite';
위 예는 모두 집합 연산의 두 피연산자로 기본 전체 텍스트 용어 쿼리를 사용해요. 구문 및 NEAR 쿼리도 사용할 수 있고, 다른 집합 연산의 결과도 사용할 수 있어요. FTS 쿼리에 집합 연산이 둘 이상 있을 때 연산자의 우선순위는 다음과 같아요:
| Operator | Enhanced Query Syntax Precedence |
|---|---|
| NOT | Highest precedence (tightest grouping). |
| AND | (중간) |
| OR | Lowest precedence (loosest grouping). |
향상된 쿼리 구문을 사용할 때 괄호를 사용해 다양한 연산자의 기본 우선순위를 재정의할 수 있어요. 예를 들어:
-- Return the docid values associated with all documents that contain the
-- two terms "sqlite" and "database", and/or contain the term "library".
SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database OR library';
-- This query is equivalent to the above.
SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database'
UNION
SELECT docid FROM docs WHERE docs MATCH 'library';
-- Query for the set of documents that contains the term "linux", and at least
-- one of the phrases "sqlite database" and "sqlite library".
SELECT docid FROM docs WHERE docs MATCH '("sqlite database" OR "sqlite library") AND linux';
-- This query is equivalent to the above.
SELECT docid FROM docs WHERE docs MATCH 'linux'
INTERSECT
SELECT docid FROM (
SELECT docid FROM docs WHERE docs MATCH '"sqlite library"'
UNION
SELECT docid FROM docs WHERE docs MATCH '"sqlite database"'
);
3.2. 표준 쿼리 구문을 사용한 집합 연산
표준 쿼리 구문을 사용한 FTS 쿼리 집합 연산은 향상된 쿼리 구문을 사용한 집합 연산과 비슷하지만 동일하지는 않아요. 차이점은 네 가지가 있어요:
- AND 연산자의 암시적 버전만 지원돼요. 표준 쿼리 구문 쿼리의 일부로 문자열 "AND" 를 지정하는 것은 용어 "and" 를 포함하는 문서 집합에 대한 용어 쿼리로 해석돼요.
- 괄호가 지원되지 않아요.
- NOT 연산자가 지원되지 않아요. NOT 연산자 대신 표준 쿼리 구문은 기본 용어 및 용어-접두어 쿼리(구문이나 NEAR 쿼리는 아님)에 적용될 수 있는 단항 "-" 연산자를 지원해요. 단항 "-" 연산자가 붙은 용어나 용어-접두어는 OR 연산자의 피연산자로 나타날 수 없어요. FTS 쿼리는 단항 "-" 연산자가 붙은 용어 또는 용어-접두어 쿼리로만 구성될 수 없어요.
-- Search for the set of documents that contain the term "sqlite" but do
-- not contain the term "database".
SELECT * FROM docs WHERE docs MATCH 'sqlite -database';
- 집합 연산의 상대적 우선순위가 달라요. 특히 표준 쿼리 구문을 사용할 때 "OR" 연산자가 "AND" 보다 높은 우선순위를 가져요. 표준 쿼리 구문을 사용할 때 연산자의 우선순위는:
| Operator | Standard Query Syntax Precedence |
|---|---|
| Unary "-" | Highest precedence (tightest grouping). |
| OR | (중간) |
| AND | Lowest precedence (loosest grouping). |
- 다음 예는 표준 쿼리 구문을 사용한 연산자 우선순위를 보여줘요:
-- Search for documents that contain at least one of the terms "database"
-- and "sqlite", and also contain the term "library". Because of the differences
-- in operator precedences, this query would have a different interpretation using
-- the enhanced query syntax.
SELECT * FROM docs WHERE docs MATCH 'sqlite OR database library';
4. 보조 함수 - Snippet, Offsets, Matchinfo
FTS3 과 FTS4 모듈은 전체 텍스트 쿼리 시스템의 개발자에게 유용할 수 있는 세 가지 특별한 SQL 스칼라 함수를 제공해요: "snippet", "offsets", "matchinfo". "snippet" 과 "offsets" 함수의 목적은 사용자가 반환된 문서에서 쿼리된 용어의 위치를 식별할 수 있게 하는 것이에요. "matchinfo" 함수는 관련성(relevance)에 따라 쿼리 결과를 필터링하거나 정렬하는 데 유용할 수 있는 지표를 사용자에게 제공해요.
세 특별한 SQL 스칼라 함수 모두의 첫 번째 인자는 함수가 적용되는 FTS 테이블의 FTS 숨은 컬럼이어야 해요. FTS 숨은 컬럼은 모든 FTS 테이블에 있는 자동 생성 컬럼으로, FTS 테이블 자체와 같은 이름을 가져요. 예를 들어 "mail"이라는 FTS 테이블이 주어지면:
SELECT offsets(mail) FROM mail WHERE mail MATCH <full-text query expression>;
SELECT snippet(mail) FROM mail WHERE mail MATCH <full-text query expression>;
SELECT matchinfo(mail) FROM mail WHERE mail MATCH <full-text query expression>;
세 보조 함수는 FTS 테이블의 전체 텍스트 인덱스를 사용하는 SELECT 문 안에서만 유용해요. "query by rowid" 나 "linear scan" 전략을 사용하는 SELECT 안에서 사용하면 snippet 과 offsets 는 모두 빈 문자열을 반환하고 matchinfo 함수는 크기가 0 바이트인 blob 값을 반환해요.
세 보조 함수 모두 FTS 쿼리 표현식에서 "일치 가능한 구문(matchable phrases)" 집합을 추출해 작업해요. 주어진 쿼리의 일치 가능한 구문 집합은 표현식의 모든 구문(따옴표 없는 토큰과 토큰 접두어 포함)으로 구성되며, 단항 "-" 연산자(표준 구문)가 접두된 것이나 NOT 연산자의 오른쪽 피연산자로 사용되는 하위 표현식의 일부는 제외돼요.
다음 조건을 전제로, FTS 테이블에서 쿼리 표현식의 일치 가능한 구문 중 하나와 일치하는 각 토큰 시리즈를 "구문 일치(phrase match)"라고 불러요:
- 일치 가능한 구문이 FTS 쿼리 표현식에서 NEAR 연산자로 연결된 구문 시리즈의 일부라면, 각 구문 일치는 NEAR 조건을 충족하기 위해 관련 타입의 다른 구문 일치와 충분히 가까워야 해요.
- FTS 쿼리의 일치 가능한 구문이 지정된 FTS 테이블 컬럼의 데이터 일치로 제한되면, 그 컬럼 안에서 발생하는 구문 일치만 고려돼요.
4.1. Offsets 함수
전체 텍스트 인덱스를 사용하는 SELECT 쿼리에 대해 offsets() 함수는 공백으로 구분된 일련의 정수를 포함하는 텍스트 값을 반환해요. 현재 행의 각 구문 일치의 각 용어에 대해 반환된 목록에 네 개의 정수가 있어요. 각 네 정수 집합은 다음과 같이 해석돼요:
| Integer | Interpretation |
|---|---|
| 0 | The column number that the term instance occurs in (0 for the leftmost column of the FTS table, 1 for the next leftmost, etc.). |
| 1 | The term number of the matching term within the full-text query expression. Terms within a query expression are numbered starting from 0 in the order that they occur. |
| 2 | The byte offset of the matching term within the column. |
| 3 | The size of the matching term in bytes. |
다음 블록은 offsets 함수를 사용하는 예를 포함해요.
CREATE VIRTUAL TABLE mail USING fts3(subject, body);
INSERT INTO mail VALUES('hello world', 'This message is a hello world message.');
INSERT INTO mail VALUES('urgent: serious', 'This mail is seen as a more serious mail');
-- The following query returns a single row (as it matches only the first
-- entry in table "mail". The text returned by the offsets function is
-- "0 0 6 5 1 0 24 5".
--
-- The first set of four integers in the result indicate that column 0
-- contains an instance of term 0 ("world") at byte offset 6. The term instance
-- is 5 bytes in size. The second set of four integers shows that column 1
-- of the matched row contains an instance of term 0 ("world") at byte offset
-- 24. Again, the term instance is 5 bytes in size.
SELECT offsets(mail) FROM mail WHERE mail MATCH 'world';
-- The following query returns also matches only the first row in table "mail".
-- In this case the returned text is "1 0 5 7 1 0 30 7".
SELECT offsets(mail) FROM mail WHERE mail MATCH 'message';
-- The following query matches the second row in table "mail". It returns the
-- text "1 0 28 7 1 1 36 4". Only those occurrences of terms "serious" and "mail"
-- that are part of an instance of the phrase "serious mail" are identified; the
-- other occurrences of "serious" and "mail" are ignored.
SELECT offsets(mail) FROM mail WHERE mail MATCH '"serious mail"';
4.2. Snippet 함수
snippet 함수는 전체 텍스트 쿼리 결과 보고서의 일부로 표시하기 위해 문서 텍스트의 서식 있는 조각을 만드는 데 사용돼요. snippet 함수에는 1 개에서 6 개 사이의 인자를 전달할 수 있어요.
| Argument | Default Value | Description |
|---|---|---|
| 0 | N/A | The first argument to the snippet function must always be the FTS hidden column of the FTS table being queried and from which the snippet is to be taken. The FTS hidden column is an automatically generated column with the same name as the FTS table itself. |
| 1 | "" | The "start match" text. |
| 2 | "" | The "end match" text. |
| 3 | "..." | The "ellipses" text. |
| 4 | -1 | The FTS table column number to extract the returned fragments of text from. Columns are numbered from left to right starting with zero. A negative value indicates that the text may be extracted from any column. |
| 5 | -15 | The absolute value of this integer argument is used as the (approximate) number of tokens to include in the returned text value. The maximum allowable absolute value is 64. The value of this argument is referred to as N in the discussion below. |
snippet 함수는 먼저 현재 행 안에서 각 일치 가능한 구문에 대해 최소 하나의 구문 일치를 포함하는 |N| 토큰으로 구성된 텍스트 조각을 찾으려고 시도해요. 여기서 |N| 은 snippet 함수에 전달된 여섯 번째 인자의 절대값이에요. 단일 컬럼에 저장된 텍스트가 |N| 토큰보다 적으면 전체 컬럼 값이 고려돼요. 텍스트 조각은 여러 컬럼에 걸칠 수 없어요.
그러한 텍스트 조각을 찾을 수 있으면 다음 수정과 함께 반환돼요:
- 텍스트 조각이 컬럼 값의 시작에서 시작하지 않으면 "ellipses" 텍스트가 앞에 붙어요.
- 텍스트 조각이 컬럼 값의 끝에서 끝나지 않으면 "ellipses" 텍스트가 뒤에 붙어요.
- 구문 일치의 일부인 텍스트 조각의 각 토큰에 대해 "start match" 텍스트가 토큰 앞에 삽입되고, "end match" 텍스트가 바로 뒤에 삽입돼요.
그러한 조각을 둘 이상 찾을 수 있으면 더 많은 수의 "추가" 구문 일치를 포함하는 조각이 선호돼요. 선택된 텍스트 조각의 시작은 구문 일치를 조각의 중앙에 집중시키려고 몇 토큰 앞뒤로 이동될 수 있어요.
N 이 양수라고 가정하면, 각 일치 가능한 구문에 해당하는 구문 일치를 포함하는 조각을 찾을 수 없으면, snippet 함수는 함께 각 일치 가능한 구문에 대해 최소 하나의 구문 일치를 포함하는 약 N/2 토큰의 두 조각을 찾으려고 시도해요. 이것이 실패하면 각각 N/3 토큰의 세 조각, 마지막으로 N/4 토큰의 네 조각을 찾으려고 시도해요. 필요한 구문 일치를 포함하는 네 조각 집합을 찾을 수 없으면 가장 좋은 커버리지를 제공하는 N/4 토큰의 네 조각이 선택돼요.
N 이 음수이고 필요한 구문 일치를 포함하는 단일 조각을 찾을 수 없으면, snippet 함수는 각각 |N| 토큰의 두 조각, 그 다음 세 개, 그 다음 네 개를 검색해요. 즉, 지정된 N 값이 음수이면 원하는 구문 일치 커버리지를 제공하기 위해 둘 이상의 조각이 필요할 때 조각의 크기가 줄어들지 않아요.
M 개의 조각이 찾아진 후(위 문단에서 설명한 대로 M 은 2 와 4 사이), 그것들이 정렬된 순서로 "ellipses" 텍스트로 구분되어 결합돼요. 위에서 열거한 세 가지 수정이 반환되기 전에 텍스트에 수행돼요.
Note: In this block of examples, newlines and whitespace characters have
been inserted into the document inserted into the FTS table, and the expected
results described in SQL comments. This is done to enhance readability only,
they would not be present in actual SQLite commands or output.
-- Create and populate an FTS table.
CREATE VIRTUAL TABLE text USING fts4();
INSERT INTO text VALUES('
During 30 Nov-1 Dec, 2-3oC drops. Cool in the upper portion, minimum temperature 14-16oC
and cool elsewhere, minimum temperature 17-20oC. Cold to very cold on mountaintops,
minimum temperature 6-12oC. Northeasterly winds 15-30 km/hr. After that, temperature
increases. Northeasterly winds 15-30 km/hr.
');
-- The following query returns the text value:
--
-- "<b>...</b>cool elsewhere, minimum temperature 17-20oC. <b>Cold</b> to very
-- <b>cold</b> on mountaintops, minimum temperature 6<b>...</b>".
--
SELECT snippet(text) FROM text WHERE text MATCH 'cold';
-- The following query returns the text value:
--
-- "...the upper portion, [minimum] [temperature] 14-16oC and cool elsewhere,
-- [minimum] [temperature] 17-20oC. Cold..."
--
SELECT snippet(text, '[', ']', '...') FROM text WHERE text MATCH '"min* tem*"'
4.3. Matchinfo 함수
matchinfo 함수는 blob 값을 반환해요. 전체 텍스트 인덱스를 사용하지 않는 쿼리("query by rowid" 나 "linear scan") 안에서 사용되면 blob 은 크기가 0 바이트예요. 그렇지 않으면 blob 은 머신 바이트 순서의 0 개 이상의 32비트 부호 없는 정수로 구성돼요. 반환된 배열의 정확한 정수 수는 쿼리와 matchinfo 함수에 전달된 두 번째 인자(있으면) 값 모두에 따라 달라져요.
matchinfo 함수는 1 개 또는 2 개의 인자로 호출돼요. 모든 보조 함수와 마찬가지로 첫 번째 인자는 특별한 FTS 숨은 컬럼이어야 해요. 두 번째 인자는 지정된다면 'p', 'c', 'n', 'a', 'l', 's', 'x', 'y', 'b' 문자만으로 구성된 텍스트 값이어야 해요. 두 번째 인자를 명시적으로 제공하지 않으면 기본값은 "pcx" 예요. 두 번째 인자를 아래에서 "포맷 문자열"이라고 불러요.
matchinfo 포맷 문자열의 문자는 왼쪽에서 오른쪽으로 처리돼요. 포맷 문자열의 각 문자는 반환된 배열에 하나 이상의 32비트 부호 없는 정수 값을 추가하게 해요. 다음 표의 "Values" 컬럼은 각 지원 포맷 문자열 문자에 대해 출력 버퍼에 추가되는 정수 값의 수를 포함해요. 주어진 공식에서 cols 는 FTS 테이블의 컬럼 수이고 phrases 는 쿼리의 일치 가능한 구문 수예요.
hits_this_row = array[3 * (c + p*cols) + 0]
hits_all_rows = array[3 * (c + p*cols) + 1]
docs_with_hits = array[3 * (c + p*cols) + 2]
a OR (b AND c)
"a c d"
hits_for_phrase_p_column_c = array[c + p*cols]
p_is_in_c = array[p * ((nCol+31)/32)] & (1 << (c % 32))
| Character | Values | Description |
|---|---|---|
| p | 1 | The number of matchable phrases in the query. |
| c | 1 | The number of user defined columns in the FTS table (i.e. not including the docid or the FTS hidden column). |
| x | 3 * cols * phrases | For each distinct combination of a phrase and table column, the following three values: (1) In the current row, the number of times the phrase appears in the column. (2) The total number of times the phrase appears in the column in all rows in the FTS table. (3) The total number of rows in the FTS table for which the column contains at least one instance of the phrase. The first set of three values corresponds to the left-most column of the table (column 0) and the left-most matchable phrase in the query (phrase 0). If the table has more than one column, the second set of three values in the output array correspond to phrase 0 and column 1. Followed by phrase 0, column 2 and so on for all columns of the table. And so on for phrase 1, column 0, then phrase 1, column 1 etc. In other words, the data for occurrences of phrase p in column c may be found using the formula: hits_this_row = array[3 * (c + p*cols) + 0], hits_all_rows = array[3 * (c + p*cols) + 1], docs_with_hits = array[3 * (c + p*cols) + 2]. |
| y | cols * phrases | For each distinct combination of a phrase and table column, the number of usable phrase matches that appear in the column. This is usually identical to the first value in each set of three returned by the matchinfo 'x' flag. However, the number of hits reported by the 'y' flag is zero for any phrase that is part of a sub-expression that does not match the current row. This makes a difference for expressions that contain AND operators that are descendants of OR operators. For example, consider the expression a OR (b AND c). The matchinfo 'x' flag would report a single hit for the phrases "a" and "c". However, the 'y' directive reports the number of hits for "c" as zero, as it is part of a sub-expression that does not match the document - (b AND c). For queries that do not contain AND operators descended from OR operators, the result values returned by 'y' are always the same as those returned by 'x'. The first value in the array of integer values corresponds to the leftmost column of the table (column 0) and the first phrase in the query (phrase 0). The values corresponding to other column/phrase combinations may be located using the formula hits_for_phrase_p_column_c = array[c + p*cols]. For queries that use OR expressions, or those that use LIMIT or return many rows, the 'y' matchinfo option may be faster than 'x'. |
| b | ((cols+31)/32) * phrases | The matchinfo 'b' flag provides similar information to the matchinfo 'y' flag, but in a more compact form. Instead of the precise number of hits, 'b' provides a single boolean flag for each phrase/column combination. If the phrase is present in the column at least once (i.e. if the corresponding integer output of 'y' would be non-zero), the corresponding flag is set. Otherwise cleared. If the table has 32 or fewer columns, a single unsigned integer is output for each phrase in the query. The least significant bit of the integer is set if the phrase appears at least once in column 0. The second least significant bit is set if the phrase appears once or more in column 1. And so on. If the table has more than 32 columns, an extra integer is added to the output of each phrase for each extra 32 columns or part thereof. Integers corresponding to the same phrase are clumped together. For example, if a table with 45 columns is queried for two phrases, 4 integers are output. The first corresponds to phrase 0 and columns 0-31 of the table. The second integer contains data for phrase 0 and columns 32-44, and so on. For example, if nCol is the number of columns in the table, to determine if phrase p is present in column c: p_is_in_c = array[p * ((nCol+31)/32)] & (1 << (c % 32)). |
| n | 1 | The number of rows in the FTS4 table. This value is only available when querying FTS4 tables, not FTS3. |
| a | cols | For each column, the average number of tokens in the text values stored in the column (considering all rows in the FTS4 table). This value is only available when querying FTS4 tables, not FTS3. |
| l | cols | For each column, the length of the value stored in the current row of the FTS4 table, in tokens. This value is only available when querying FTS4 tables, not FTS3. And only if the "matchinfo=fts3" directive was not specified as part of the "CREATE VIRTUAL TABLE" statement used to create the FTS4 table. |
| s | cols | For each column, the length of the longest subsequence of phrase matches that the column value has in common with the query text. For example, if a table column contains the text 'a b c d e' and the query is 'a c "d e"', then the length of the longest common subsequence is 2 (phrase "c" followed by phrase "d e"). |
예를 들어:
-- Create and populate an FTS4 table with two columns:
CREATE VIRTUAL TABLE t1 USING fts4(a, b);
INSERT INTO t1 VALUES('transaction default models default', 'Non transaction reads');
INSERT INTO t1 VALUES('the default transaction', 'these semantics present');
INSERT INTO t1 VALUES('single request', 'default data');
-- In the following query, no format string is specified and so it defaults
-- to "pcx". It therefore returns a single row consisting of a single blob
-- value 80 bytes in size (20 32-bit integers - 1 for "p", 1 for "c" and
-- 3*2*3 for "x"). If each block of 4 bytes in the blob is interpreted
-- as an unsigned integer in machine byte-order, the values will be:
--
-- 3 2 1 3 2 0 1 1 1 2 2 0 1 1 0 0 0 1 1 1
--
-- The row returned corresponds to the second entry inserted into table t1.
-- The first two integers in the blob show that the query contained three
-- phrases and the table being queried has two columns. The next block of
-- three integers describes column 0 (in this case column "a") and phrase
-- 0 (in this case "default"). The current row contains 1 hit for "default"
-- in column 0, of a total of 3 hits for "default" that occur in column
-- 0 of any table row. The 3 hits are spread across 2 different rows.
--
-- The next set of three integers (0 1 1) pertain to the hits for "default"
-- in column 1 of the table (0 in this row, 1 in all rows, spread across
-- 1 rows).
--
SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH 'default transaction "these semantics"';
-- The format string for this query is "ns". The output array will therefore
-- contain 3 integer values - 1 for "n" and 2 for "s". The query returns
-- two rows (the first two rows in the table match). The values returned are:
--
-- 3 1 1
-- 3 2 0
--
-- The first value in the matchinfo array returned for both rows is 3 (the
-- number of rows in the table). The following two values are the lengths
-- of the longest common subsequence of phrase matches in each column.
SELECT matchinfo(t1, 'ns') FROM t1 WHERE t1 MATCH 'default transaction';
matchinfo 함수는 snippet 이나 offsets 함수보다 훨씬 빠르요. 이는 snippet 과 offsets 둘 다 구현이 분석되는 문서를 디스크에서 가져와야 하는 반면, matchinfo 가 필요로 하는 모든 데이터는 전체 텍스트 쿼리 자체를 구현하는 데 필요한 전체 텍스트 인덱스의 같은 부분의 일부로 사용 가능하기 때문이에요. 이는 다음 두 쿼리 중 첫 번째가 두 번째보다 한 자릿수 더 빠를 수 있다는 뜻이에요:
SELECT docid, matchinfo(tbl) FROM tbl WHERE tbl MATCH <query expression>;
SELECT docid, offsets(tbl) FROM tbl WHERE tbl MATCH <query expression>;
matchinfo 함수는 Okapi BM25/BM25F 같은 확률적 "bag-of-words" 관련성 점수를 계산하는 데 필요한 모든 정보를 제공하는데, 이는 전체 텍스트 검색 애플리케이션에서 결과를 정렬하는 데 사용될 수 있어요. 이 문서의 부록 A "검색 애플리케이션 팁"에는 matchinfo() 함수를 효율적으로 사용하는 예가 있어요.
5. Fts4aux - 전체 텍스트 인덱스 직접 접근
버전 3.7.6 (2011-04-12) 부터 SQLite 는 "fts4aux"라는 새 가상 테이블 모듈을 포함하는데, 이를 사용해 기존 FTS 테이블의 전체 텍스트 인덱스를 직접 검사할 수 있어요. 이름과 달리 fts4aux 는 FTS4 테이블뿐만 아니라 FTS3 테이블에서도 똑같이 잘 작동해요. Fts4aux 테이블은 읽기 전용이에요. fts4aux 테이블의 내용을 수정하는 유일한 방법은 연관된 FTS 테이블의 내용을 수정하는 것이에요. fts4aux 모듈은 FTS 를 포함하는 모든 빌드에 자동으로 포함돼요.
fts4aux 가상 테이블은 1 개 또는 2 개의 인자로 구성돼요. 단일 인자로 사용할 때 그 인자는 접근할 FTS 테이블의 비한정 이름이에요. 다른 데이터베이스의 테이블에 접근하려면(예를 들어 MAIN 데이터베이스의 FTS3 테이블에 접근할 TEMP fts4aux 테이블을 만들려면) 두 인자 형태를 사용해 첫 인자에 대상 데이터베이스의 이름(예: "main")을, 두 번째 인자에 FTS3/4 테이블의 이름을 주세요. (fts4aux 의 두 인자 형태는 SQLite 버전 3.7.17 (2013-05-20) 에 추가됐고 이전 릴리스에서는 에러를 던질 거예요.) 예를 들어:
-- Create an FTS4 table
CREATE VIRTUAL TABLE ft USING fts4(x, y);
-- Create an fts4aux table to access the full-text index for table "ft"
CREATE VIRTUAL TABLE ft_terms USING fts4aux(ft);
-- Create a TEMP fts4aux table accessing the "ft" table in "main"
CREATE VIRTUAL TABLE temp.ft_terms_2 USING fts4aux(main,ft);
FTS 테이블에 존재하는 각 용어에 대해 fts4aux 테이블에는 2 와 N+1 사이의 행이 있는데, 여기서 N 은 연관된 FTS 테이블의 사용자 정의 컬럼 수예요. fts4aux 테이블은 항상 다음 네 개의 컬럼을 가져요(왼쪽에서 오른쪽으로):
| Column Name | Column Contents |
|---|---|
| term | Contains the text of the term for this row. |
| col | This column may contain either the text value '*' (i.e. a single character, U+002a) or an integer between 0 and N-1, where N is again the number of user-defined columns in the corresponding FTS table. |
| documents | This column always contains an integer value greater than zero. If the "col" column contains the value '*', then this column contains the number of rows of the FTS table that contain at least one instance of the term (in any column). If col contains an integer value, then this column contains the number of rows of the FTS table that contain at least one instance of the term in the column identified by the col value. As usual, the columns of the FTS table are numbered from left to right, starting with zero. |
| occurrences | This column also always contains an integer value greater than zero. If the "col" column contains the value '*', then this column contains the total number of instances of the term in all rows of the FTS table (in any column). Otherwise, if col contains an integer value, then this column contains the total number of instances of the term that appear in the FTS table column identified by the col value. |
| languageid (hidden) | This column determines which languageid is used to extract vocabulary from the FTS3/4 table. The default value for languageid is 0. If an alternative language is specified in WHERE clause constraints, then that alternative is used instead of 0. There can only be a single languageid per query. In other words, the WHERE clause cannot contain a range constraint or IN operator on the languageid. |
예를 들어, 위에서 만든 테이블을 사용해:
INSERT INTO ft(x, y) VALUES('Apple banana', 'Cherry');
INSERT INTO ft(x, y) VALUES('Banana Date Date', 'cherry');
INSERT INTO ft(x, y) VALUES('Cherry Elderberry', 'Elderberry');
-- The following query returns this data:
--
-- apple | * | 1 | 1
-- apple | 0 | 1 | 1
-- banana | * | 2 | 2
-- banana | 0 | 2 | 2
-- cherry | * | 3 | 3
-- cherry | 0 | 1 | 1
-- cherry | 1 | 2 | 2
-- date | * | 1 | 2
-- date | 0 | 1 | 2
-- elderberry | * | 1 | 2
-- elderberry | 0 | 1 | 1
-- elderberry | 1 | 1 | 1
--
SELECT term, col, documents, occurrences FROM ft_terms;
예에서 "term" 컬럼의 값은 대소문자가 섞인 채로 테이블 "ft" 에 삽입됐음에도 모두 소문자예요. 이는 fts4aux 테이블이 토크나이저가 문서 텍스트에서 추출한 용어를 포함하기 때문이에요. 이 경우 테이블 "ft" 가 simple 토크나이저를 사용하므로 모든 용어가 소문자로 접혔다는 뜻이에요. 또한 (예를 들어) "term" 컬럼이 "apple" 이고 "col" 컬럼이 1 인 행은 없어요. 컬럼 1 에 용어 "apple" 의 인스턴스가 없으므로 fts4aux 테이블에 행이 존재하지 않아요.
트랜잭션 중에 FTS 테이블에 기록된 일부 데이터가 메모리에 캐시되고 트랜잭션이 커밋될 때만 데이터베이스에 기록될 수 있어요. 하지만 fts4aux 모듈의 구현은 데이터베이스에서만 데이터를 읽을 수 있어요. 실제로 이는 연관된 FTS 테이블이 수정된 트랜잭션 안에서 fts4aux 테이블이 쿼리되면, 쿼리 결과가 (가능성은 비어 있는) 변경 사항의 부분집합만 반영할 가능성이 높다는 뜻이에요.
6. FTS4 옵션
"CREATE VIRTUAL TABLE" 문이 모듈 FTS4(FTS3 이 아님)를 지정하면 "tokenize=*" 옵션과 유사한 특별한 지시어 - FTS4 옵션 - 도 컬럼 이름 대신 나타날 수 있어요. FTS4 옵션은 옵션 이름 뒤에 "=" 문자, 그리고 옵션 값으로 구성돼요. 옵션 값은 선택적으로 작은따옴표나 큰따옴표로 감쌀 수 있고, 포함된 따옴표 문자는 SQL 리터럴과 같은 방식으로 이스케이프돼요. "=" 문자의 양쪽에는 공백이 있을 수 없어요. 예를 들어 옵션 "matchinfo" 의 값을 "fts3" 로 설정한 FTS4 테이블을 만들려면:
-- Create a reduced-footprint FTS4 table.
CREATE VIRTUAL TABLE papers USING fts4(author, document, matchinfo=fts3);
FTS4 는 현재 다음 옵션을 지원해요:
| Option | Interpretation |
|---|---|
| compress | The compress option is used to specify the compress function. It is an error to specify a compress function without also specifying an uncompress function. See below for details. |
| content | The content allows the text being indexed to be stored in a separate table distinct from the FTS4 table, or even outside of SQLite. |
| languageid | The languageid option causes the FTS4 table to have an additional hidden integer column that identifies the language of the text contained in each row. The use of the languageid option allows the same FTS4 table to hold text in multiple languages or scripts, each with different tokenizer rules, and to query each language independently of the others. |
| matchinfo | When set to the value "fts3", the matchinfo option reduces the amount of information stored by FTS4 with the consequence that the "l" option of matchinfo() is no longer available. |
| notindexed | This option is used to specify the name of a column for which data is not indexed. Values stored in columns that are not indexed are not matched by MATCH queries. Nor are they recognized by auxiliary functions. A single CREATE VIRTUAL TABLE statement may have any number of notindexed options. |
| order | The "order" option may be set to either "DESC" or "ASC" (in upper or lower case). If it is set to "DESC", then FTS4 stores its data in such a way as to optimize returning results in descending order by docid. If it is set to "ASC" (the default), then the data structures are optimized for returning results in ascending order by docid. In other words, if many of the queries run against the FTS4 table use "ORDER BY docid DESC", then it may improve performance to add the "order=desc" option to the CREATE VIRTUAL TABLE statement. |
| prefix | This option may be set to a comma-separated list of positive non-zero integers. For each integer N in the list, a separate index is created in the database file to optimize prefix queries where the query term is N bytes in length, not including the '*' character, when encoded using UTF-8. See below for details. |
| uncompress | This option is used to specify the uncompress function. It is an error to specify an uncompress function without also specifying a compress function. See below for details. |
FTS4 를 사용할 때 "=" 문자를 포함하고 "tokenize=" 지정도 아니고 인식된 FTS4 옵션도 아닌 컬럼 이름을 지정하는 것은 에러예요. FTS3 에서는 인식되지 않는 지시어의 첫 토큰이 컬럼 이름으로 해석돼요. 마찬가지로 단일 테이블 선언에서 여러 "tokenize=" 지시어를 지정하는 것은 FTS4 를 사용할 때 에러지만, 두 번째 및 이후 "tokenize=*" 지시어는 FTS3 이 컬럼 이름으로 해석해요. 예를 들어:
-- An error. FTS4 does not recognize the directive "xyz=abc".
CREATE VIRTUAL TABLE papers USING fts4(author, document, xyz=abc);
-- Create an FTS3 table with three columns - "author", "document"
-- and "xyz".
CREATE VIRTUAL TABLE papers USING fts3(author, document, xyz=abc);
-- An error. FTS4 does not allow multiple tokenize=* directives
CREATE VIRTUAL TABLE papers USING fts4(tokenize=porter, tokenize=simple);
-- Create an FTS3 table with a single column named "tokenize". The
-- table uses the "porter" tokenizer.
CREATE VIRTUAL TABLE papers USING fts3(tokenize=porter, tokenize=simple);
-- An error. Cannot create a table with two columns named "tokenize".
CREATE VIRTUAL TABLE papers USING fts3(tokenize=porter, tokenize=simple, tokenize=icu);
6.1. compress= 및 uncompress= 옵션
compress 와 uncompress 옵션은 FTS4 내용을 압축된 형태로 데이터베이스에 저장할 수 있게 해줘요. 두 옵션 모두 단일 인자를 받아들이는 sqlite3_create_function() 으로 등록된 SQL 스칼라 함수의 이름으로 설정해야 해요.
compress 함수는 인자로 전달된 값의 압축된 버전을 반환해야 해요. FTS4 테이블에 데이터가 기록될 때마다 각 컬럼 값이 compress 함수에 전달되고 결과 값이 데이터베이스에 저장돼요. compress 함수는 어떤 타입의 SQLite 값(blob, text, real, integer, null)이라도 반환할 수 있어요.
uncompress 함수는 compress 함수가 이전에 압축한 데이터를 압축 해제해야 해요. 즉, 모든 SQLite 값 X 에 대해 uncompress(compress(X)) 가 X 와 같아야 해요. FTS4 가 데이터베이스에서 compress 함수로 압축된 데이터를 읽을 때, 사용되기 전에 uncompress 함수에 전달돼요.
지정된 compress 또는 uncompress 함수가 존재하지 않아도 테이블은 여전히 만들어질 수 있어요. FTS4 테이블이 읽힐 때(uncompress 함수가 존재하지 않을 때)나 기록될 때(compress 함수가 존재하지 않을 때)까지 에러는 반환되지 않아요.
-- Create an FTS4 table that stores data in compressed form. This
-- assumes that the scalar functions zip() and unzip() have been (or
-- will be) added to the database handle.
CREATE VIRTUAL TABLE papers USING fts4(author, document, compress=zip, uncompress=unzip);
compress 와 uncompress 함수를 구현할 때 데이터 타입에 주의를 기울이는 것이 중요해요. 특히 사용자가 압축된 FTS 테이블에서 값을 읽을 때, FTS 가 반환하는 값은 uncompress 함수가 반환하는 값과 데이터 타입을 포함해 정확히 같아요. 그 데이터 타입이 compress 함수에 전달된 원래 값의 데이터 타입과 같지 않으면(예를 들어 compress 가 원래 TEXT 를 받았는데 uncompress 함수가 BLOB 를 반환하면) 사용자의 쿼리가 예상대로 작동하지 않을 수 있어요.
6.2. content= 옵션
content 옵션은 FTS4 가 인덱싱되는 텍스트를 저장하지 않게 할 수 있어요. content 옵션은 두 가지 방식으로 사용될 수 있어요:
- 인덱싱된 문서가 SQLite 데이터베이스 안에 전혀 저장되지 않거나("contentless" FTS4 테이블),
- 인덱싱된 문서가 사용자가 만들고 관리하는 데이터베이스 테이블에 저장되거나("external content" FTS4 테이블).
인덱싱된 문서 자체가 보통 전체 텍스트 인덱스보다 훨씬 크기 때문에, content 옵션은 상당한 공간 절약을 달성하는 데 사용될 수 있어요.
6.2.1. Contentless FTS4 테이블
인덱싱된 문서의 복사본을 전혀 저장하지 않는 FTS4 테이블을 만들기 위해 content 옵션을 빈 문자열로 설정해야 해요. 예를 들어 다음 SQL 은 "a", "b", "c" 세 컬럼을 가진 그러한 FTS4 테이블을 만들어요:
CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, c);
그러한 FTS4 테이블에는 INSERT 문으로 데이터를 삽입할 수 있어요. 하지만 일반 FTS4 테이블과 달리 사용자가 명시적인 정수 docid 값을 제공해야 해요. 예를 들어:
-- This statement is Ok:
INSERT INTO t1(docid, a, b, c) VALUES(1, 'a b c', 'd e f', 'g h i');
-- This statement causes an error, as no docid value has been provided:
INSERT INTO t1(a, b, c) VALUES('j k l', 'm n o', 'p q r');
contentless FTS4 테이블에 저장된 행을 UPDATE 나 DELETE 하는 것은 불가능해요. 그렇게 시도하는 것은 에러예요.
Contentless FTS4 테이블은 SELECT 문도 지원해요. 하지만 docid 컬럼을 제외한 어떤 테이블 컬럼의 값을 검색하려는 시도는 에러예요. 보조 함수 matchinfo() 는 사용할 수 있지만 snippet() 과 offsets() 는 사용할 수 없어요. 예를 들어:
-- The following statements are Ok:
SELECT docid FROM t1 WHERE t1 MATCH 'xxx';
SELECT docid FROM t1 WHERE a MATCH 'xxx';
SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH 'xxx';
-- The following statements all cause errors, as the value of columns
-- other than docid are required to evaluate them.
SELECT * FROM t1;
SELECT a, b FROM t1 WHERE t1 MATCH 'xxx';
SELECT docid FROM t1 WHERE a LIKE 'xxx%';
SELECT snippet(t1) FROM t1 WHERE t1 MATCH 'xxx';
docid 이외의 컬럼 값을 검색하려는 시도와 관련된 에러는 sqlite3_step() 안에서 발생하는 런타임 에러예요. 어떤 경우에는, 예를 들어 SELECT 쿼리의 MATCH 표현식이 0 개의 행과 일치하면, 문장이 docid 이외의 컬럼 값을 참조하더라도 에러가 전혀 없을 수 있어요.
6.2.2. External Content FTS4 테이블
"external content" FTS4 테이블은 contentless 테이블과 비슷하지만, 쿼리 평가에 docid 이외의 컬럼 값이 필요하면 FTS4 가 사용자가 지명한 테이블(또는 뷰, 가상 테이블)(이하 "content table")에서 그 값을 검색하려고 시도한다는 점이 달라요. FTS4 모듈은 content table 에 절대 쓰지 않으며, content table 에 쓰는 것은 전체 텍스트 인덱스에 영향을 주지 않아요. content table 과 전체 텍스트 인덱스가 일관되도록 하는 것은 사용자의 책임이에요.
external content FTS4 테이블은 content 옵션을 필요할 때 컬럼 값을 검색하기 위해 FTS4 가 쿼리할 수 있는 테이블(또는 뷰, 가상 테이블)의 이름으로 설정해 만들어져요. 지명된 테이블이 존재하지 않으면 external content 테이블은 contentless 테이블처럼 동작해요. 예를 들어:
CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
CREATE VIRTUAL TABLE t3 USING fts4(content="t2", a, c);
지명된 테이블이 존재한다고 가정하면, 그 컬럼은 FTS 테이블에 정의된 것과 같거나 그 상위 집합이어야 해요. external table 은 FTS 테이블과 같은 데이터베이스 파일에 있어야 해요. 즉, external table 은 ATTACH 로 연결된 다른 데이터베이스 파일에 있을 수 없고, 하나가 TEMP 데이터베이스에 있고 다른 하나가 MAIN 같은 영구 데이터베이스 파일에 있을 수도 없어요.
사용자의 FTS 테이블 쿼리가 docid 이외의 컬럼 값을 필요로 하면, FTS 는 현재 FTS docid 와 같은 rowid 값을 가진 content table 의 행의 해당 컬럼에서 요청된 값을 읽으려고 시도해요. FTS/34 테이블 선언에 복제된 content-table 컬럼의 부분집합만 쿼리할 수 있어요. 다른 컬럼의 값을 검색하려면 content table 을 직접 쿼리해야 해요. 또는 content table 에서 그러한 행을 찾을 수 없으면 NULL 값이 대신 사용돼요. 예를 들어:
CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
CREATE VIRTUAL TABLE t3 USING fts4(content="t2", b, c);
INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f');
INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l');
INSERT INTO t3(docid, b, c) SELECT id, b, c FROM t2;
-- The following query returns a single row with two columns containing
-- the text values "i j" and "k l".
--
-- The query uses the full-text index to discover that the MATCH
-- term matches the row with docid=3. It then retrieves the values
-- of columns b and c from the row with rowid=3 in the content table
-- to return.
--
SELECT * FROM t3 WHERE t3 MATCH 'k';
-- Following the UPDATE, the query still returns a single row, this
-- time containing the text values "xxx" and "yyy". This is because the
-- full-text index still indicates that the row with docid=3 matches
-- the FTS4 query 'k', even though the documents stored in the content
-- table have been modified.
--
UPDATE t2 SET b = 'xxx', c = 'yyy' WHERE rowid = 3;
SELECT * FROM t3 WHERE t3 MATCH 'k';
-- Following the DELETE below, the query returns one row containing two
-- NULL values. NULL values are returned because FTS is unable to find
-- a row with rowid=3 within the content table.
--
DELETE FROM t2;
SELECT * FROM t3 WHERE t3 MATCH 'k';
external content FTS4 테이블에서 행이 삭제되면, FTS4 는 content table 에서 삭제되는 행의 컬럼 값을 검색해야 해요. 이는 FTS4 가 삭제된 행 안에서 발생하는 각 토큰에 대한 전체 텍스트 인덱스 항목을 갱신해 그 행이 삭제됐음을 나타낼 수 있게 하기 위해서예요. content table 행을 찾을 수 없거나, FTS 인덱스의 내용과 일치하지 않는 값을 포함하면 결과를 예측하기 어려울 수 있어요. FTS 인덱스는 삭제된 행에 해당하는 항목을 포함한 채 남을 수 있고, 이는 이후 SELECT 쿼리가 말이 안 되는 것처럼 보이는 결과를 반환하게 할 수 있어요. 행이 갱신될 때도 마찬가지인데, 내부적으로 UPDATE 는 DELETE 다음에 INSERT 가 오는 것과 같기 때문이에요.
이는 FTS 를 external content table 과 동기화된 상태로 유지하려면, UPDATE 나 DELETE 작업이 먼저 FTS 테이블에 적용된 다음 external content table 에 적용되어야 한다는 뜻이에요. 예를 들어:
CREATE TABLE t1_real(id INTEGER PRIMARY KEY, a, b, c, d);
CREATE VIRTUAL TABLE t1_fts USING fts4(content="t1_real", b, c);
-- This works. When the row is removed from the FTS table, FTS retrieves
-- the row with rowid=123 and tokenizes it in order to determine the entries
-- that must be removed from the full-text index.
--
DELETE FROM t1_fts WHERE rowid = 123;
DELETE FROM t1_real WHERE rowid = 123;
-- This does not work. By the time the FTS table is updated, the row
-- has already been deleted from the underlying content table. As a result
-- FTS is unable to determine the entries to remove from the FTS index and
-- so the index and content table are left out of sync.
--
DELETE FROM t1_real WHERE rowid = 123;
DELETE FROM t1_fts WHERE rowid = 123;
전체 텍스트 인덱스와 content table 에 별도로 쓰는 대신, 일부 사용자는 데이터베이스 트리거를 사용해 content table 에 저장된 문서 집합에 관해 전체 텍스트 인덱스를 최신 상태로 유지하고 싶을 수 있어요. 예를 들어, 이전 예의 테이블을 사용해:
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 BEGIN
DELETE FROM t3 WHERE docid=old.rowid;
END;
CREATE TRIGGER t2_bd BEFORE DELETE ON t2 BEGIN
DELETE FROM t3 WHERE docid=old.rowid;
END;
CREATE TRIGGER t2_au AFTER UPDATE ON t2 BEGIN
INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
END;
CREATE TRIGGER t2_ai AFTER INSERT ON t2 BEGIN
INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
END;
DELETE 트리거는 content table 에서 실제 삭제가 일어나기 전에 발화되어야 해요. 이는 FTS4 가 전체 텍스트 인덱스를 갱신하기 위해 원래 값을 여전히 검색할 수 있도록 하기 위해서예요. 그리고 INSERT 트리거는 새 행이 삽입된 후에 발화되어야 해요. 시스템 내에서 rowid 가 자동으로 할당되는 경우를 처리하기 위해서예요. UPDATE 트리거는 같은 이유로 두 부분으로 나뉘어 content table 갱신 전에 하나, 후에 하나가 발화되어야 해요.
FTS4 "rebuild" 명령은 전체 텍스트 인덱스 전체를 삭제하고 content table 의 현재 문서 집합에 기반해 다시 만드러요. "t3" 이 external content FTS4 테이블의 이름이라고 다시 가정하면, rebuild 명령은 다음과 같아요:
INSERT INTO t3(t3) VALUES('rebuild');
이 명령은 일반 FTS4 테이블에도 사용될 수 있어요. 예를 들어 토크나이저의 구현이 바뀌는 경우에요. contentless FTS4 테이블이 유지하는 전체 텍스트 인덱스를 다시 만들려는 시도는 에러예요. rebuild 를 수행할 내용이 없을 것이기 때문이에요.
6.3. languageid= 옵션
languageid 옵션이 있으면 FTS4 테이블에 추가되는 다른 숨은 컬럼의 이름을 지정하는데, 이 컬럼은 FTS4 테이블의 각 행에 저장된 언어를 지정하는 데 사용돼요. languageid 숨은 컬럼의 이름은 FTS4 테이블의 모든 다른 컬럼 이름과 구별되어야 해요. 예:
CREATE VIRTUAL TABLE t1 USING fts4(x, y, languageid="lid")
languageid 컬럼의 기본값은 0 이에요. languageid 컬럼에 삽입되는 모든 값은 32비트(64 가 아니라) 부호 있는 정수로 변환돼요.
기본적으로 FTS 쿼리(MATCH 연산자를 사용하는 것)는 languageid 컬럼이 0 으로 설정된 행만 고려해요. 다른 languageid 값을 가진 행을 쿼리하려면 "
SELECT * FROM t1 WHERE t1 MATCH 'abc' AND lid=5;
단일 FTS 쿼리가 다른 languageid 값을 가진 행을 반환하는 것은 불가능해요. 다른 연산자(예: lid!=5, lid<=5)를 사용하는 WHERE 절을 추가한 결과는 정의되지 않아요.
content 옵션을 languageid 옵션과 함께 사용하면 지명된 languageid 컬럼이 content= 테이블에 존재해야 해요(보통 규칙을 따르며, 쿼리가 content table 을 읽을 필요가 전혀 없으면 이 제한은 적용되지 않아요).
languageid 옵션을 사용할 때 SQLite 는 sqlite3_tokenizer_module 객체가 만들어진 직후 xLanguageid() 를 호출해 토크나이저가 사용해야 할 언어 id 를 전달해요. xLanguageid() 메서드는 단일 토크나이저 객체에 대해 한 번 이상 호출되지 않을 거예요. 다른 언어가 다르게 토큰화될 수 있다는 사실이 단일 FTS 쿼리가 다른 languageid 값을 가진 행을 반환할 수 없는 한 가지 이유예요.
6.4. matchinfo= 옵션
matchinfo 옵션은 "fts3" 값으로만 설정할 수 있어요. matchinfo 를 "fts3" 이외의 값으로 설정하려는 시도는 에러예요. 이 옵션을 지정하면 FTS4 가 저장하는 추가 정보 중 일부가 생략돼요. 이는 FTS4 테이블이 소비하는 디스크 공간을 줄여 동등한 FTS3 테이블이 사용하는 양과 거의 같게 만들지만, matchinfo() 함수에 'l' 플래그를 전달해 접근하는 데이터는 사용할 수 없게 된다는 뜻이기도 해요.
6.5. notindexed= 옵션
보통 FTS 모듈은 테이블의 모든 컬럼의 모든 용어에 대한 역 인덱스를 유지해요. 이 옵션은 인덱스에 항목이 추가되지 않아야 하는 컬럼의 이름을 지정하는 데 사용돼요. 여러 "notindexed" 옵션을 사용해 여러 컬럼이 인덱스에서 생략되도록 지정할 수 있어요. 예를 들어:
-- Create an FTS4 table for which only the contents of columns c2 and c4
-- are tokenized and added to the inverted index.
CREATE VIRTUAL TABLE t1 USING fts4(c1, c2, c3, c4, notindexed=c1, notindexed=c3);
인덱싱되지 않은 컬럼에 저장된 값은 MATCH 연산자와 일치할 자격이 없어요. 그것들은 offsets() 나 matchinfo() 보조 함수의 결과에 영향을 주지 않아요. 또한 snippet() 함수가 인덱싱되지 않은 컬럼에 저장된 값에 기반한 스니펫을 반환하는 일은 없을 거예요.
6.6. prefix= 옵션
FTS4 prefix 옵션은 FTS 가 지정된 길이의 용어 접두어를 항상 완전한 용어를 인덱싱하는 것과 같은 방식으로 인덱싱하게 해요. prefix 옵션은 양수이고 0 이 아닌 정수의 쉼표로 구분된 목록으로 설정해야 해요. 목록의 각 값 N 에 대해 길이 N 바이트(UTF-8 로 인코딩될 때)의 접두어가 인덱싱돼요. FTS4 는 용어 접두어 인덱스를 사용해 접두어 쿼리를 빠르게 해요. 물론 비용은 완전한 용어뿐만 아니라 용어 접두어도 인덱싱하는 것이 데이터베이스 크기를 늘리고 FTS4 테이블에 대한 쓰기 작업을 느리게 한다는 점이에요.
접두어 인덱스는 두 가지 경우에 접두어 쿼리를 최적화하는 데 사용될 수 있어요. 쿼리가 N 바이트의 접두어에 대한 것이면 "prefix=N" 으로 만든 접두어 인덱스가 최상의 최적화를 제공해요. 또는 "prefix=N" 인덱스를 사용할 수 없으면 "prefix=N+1" 인덱스를 대신 사용할 수 있어요. "prefix=N+1" 인덱스를 사용하는 것은 "prefix=N" 인덱스보다 덜 효율적이지만, 접두어 인덱스가 전혀 없는 것보다는 나아요.
-- Create an FTS4 table with indexes to optimize 2 and 4 byte prefix queries.
CREATE VIRTUAL TABLE t1 USING fts4(c1, c2, prefix="2,4");
-- The following two queries are both optimized using the prefix indexes.
SELECT * FROM t1 WHERE t1 MATCH 'ab*';
SELECT * FROM t1 WHERE t1 MATCH 'abcd*';
-- The following two queries are both partially optimized using the prefix
-- indexes. The optimization is not as pronounced as it is for the queries
-- above, but still an improvement over no prefix indexes at all.
SELECT * FROM t1 WHERE t1 MATCH 'a*';
SELECT * FROM t1 WHERE t1 MATCH 'abc*';
7. FTS3 과 FTS4 용 특별 명령
특별한 INSERT 연산을 사용해 FTS3 과 FTS4 테이블에 명령을 내릴 수 있어요. 모든 FTS3 과 FTS4 에는 테이블 자체와 같은 이름의 숨은 읽기 전용 컬럼이 있어요. 이 숨은 컬럼에 대한 INSERT 는 FTS3/4 테이블에 대한 명령으로 해석돼요. "xyz"라는 이름의 테이블에 대해 다음 명령이 지원돼요:
INSERT INTO xyz(xyz) VALUES('optimize');INSERT INTO xyz(xyz) VALUES('rebuild');INSERT INTO xyz(xyz) VALUES('integrity-check');INSERT INTO xyz(xyz) VALUES('merge=X,Y');INSERT INTO xyz(xyz) VALUES('automerge=N');
7.1. "optimize" 명령
"optimize" 명령은 FTS3/4 가 역 인덱스 b-tree 를 모두 하나의 크고 완전한 b-tree 로 병합하게 해요. optimize 를 실행하면 검색할 b-tree 가 더 적으므로 이후 쿼리가 더 빨라지고, 중복 항목을 병합해 디스크 사용량을 줄일 수 있어요. 하지만 큰 FTS 테이블의 경우 optimize 를 실행하는 것은 VACUUM 을 실행하는 것만큼 비쌀 수 있어요. optimize 명령은 본질적으로 FTS 테이블 전체를 읽고 써야 하므로 큰 트랜잭션이 생겨요.
배치 모드 작업에서 FTS 테이블이 처음에 많은 수의 INSERT 로 구축된 다음 변경 없이 반복적으로 쿼리되는 경우, 마지막 INSERT 후 첫 쿼리 전에 "optimize" 를 실행하는 것이 종종 좋은 생각이에요.
7.2. "rebuild" 명령
"rebuild" 명령은 SQLite 가 FTS3/4 테이블 전체를 버린 다음 원래 텍스트에서 다시 만들게 해요. 개념은 REINDEX 와 유사하지만 일반 인덱스 대신 FTS3/4 테이블에 적용된다는 점만 달라요.
"rebuild" 명령은 사용자 정의 토크나이저의 구현이 바뀔 때마다 실행되어야 해요. 모든 내용이 다시 토큰화될 수 있도록요. "rebuild" 명령은 원래 content table 에 변경이 이루어진 후 FTS4 content 옵션을 사용할 때도 유용해요.
7.3. "integrity-check" 명령
"integrity-check" 명령은 SQLite 가 FTS3/4 테이블의 모든 역 인덱스를 원래 내용과 비교해 정확성을 읽고 검증하게 해요. "integrity-check" 명령은 역 인덱스가 모두 정상이면 조용히 성공하지만, 문제가 발견되면 SQLITE_CORRUPT 에러로 실패해요.
"integrity-check" 명령은 개념적으로 PRAGMA integrity_check 와 비슷해요. 정상 작동 시스템에서 "integrity-check" 명령은 항상 성공해야 해요. integrity-check 실패의 가능한 원인은 다음과 같아요:
- 애플리케이션이 FTS3/4 가상 테이블을 사용하지 않고 FTS 섀도 테이블을 직접 변경해, 섀도 테이블이 서로 동기화되지 않게 만들었다.
- FTS4 content 옵션을 사용하면서 content 를 FTS4 역 인덱스와 수동으로 동기화하지 못했다.
- FTS3/4 가상 테이블의 버그. ("integrity-check" 명령은 원래 FTS3/4 테스트 스위트의 일부로 구상됐어요.)
- 기본 SQLite 데이터베이스 파일의 손상. (추가 정보는 SQLite 데이터베이스를 손상시키는 방법에 대한 문서를 참고하세요.)
7.4. "merge=X,Y" 명령
"merge=X,Y" 명령(X 와 Y 는 정수)은 SQLite 가 FTS3/4 테이블의 다양한 역 인덱스 b-tree 를 하나의 큰 b-tree 로 병합하는 방향으로 제한된 양의 작업을 하게 해요. X 값은 병합할 "블록"의 목표 수이고, Y 는 병합이 그 레벨에 적용되기 전에 한 레벨에 필요한 최소 b-tree 세그먼트 수예요. Y 값은 2 와 16 사이여야 하고 권장값은 8 이에요. X 값은 양의 정수이면 되지만 100 에서 300 정도의 값이 권장돼요.
FTS 테이블이 같은 레벨에 16 개의 b-tree 세그먼트를 쌓으면, 그 테이블에 대한 다음 INSERT 는 16 개 세그먼트가 모두 다음 상위 레벨의 단일 b-tree 세그먼트로 병합되게 해요. 이 레벨 병합의 효과는 FTS 테이블에 대한 대부분의 INSERT 가 매우 빠르고 최소 메모리를 사용하지만, 가끔 INSERT 는 병합을 해야 하므로 느리고 큰 트랜잭션을 생성한다는 것이에요. 이로 인해 INSERT 의 "뾰족한(spiky)" 성능이 생겨요.
뾰족한 INSERT 성능을 피하기 위해 애플리케이션은 주기적으로, 가능하면 유휴 스레드나 유휴 프로세스에서 "merge=X,Y" 명령을 실행해 FTS 테이블이 같은 레벨에 너무 많은 b-tree 세그먼트를 쌓지 않게 할 수 있어요. INSERT 성능 스파이크는 일반적으로 피할 수 있고, 몇 천 개의 문서 삽입마다 "merge=X,Y" 를 실행하면 FTS3/4 의 성능을 최대화할 수 있어요. 각 "merge=X,Y" 명령은 별도의 트랜잭션에서 실행돼요(물론 BEGIN...COMMIT 로 묶지 않았다면). X 값을 100 에서 300 범위로 선택하면 트랜잭션을 작게 유지할 수 있어요. 병합 명령을 실행하는 유휴 스레드는 각 "merge=X,Y" 명령 전후의 sqlite3_total_changes() 차이를 확인해 언제 끝났는지 알 수 있고, 차이가 2 미만으로 떨어지면 루프를 중지해요.
7.5. "automerge=N" 명령
"automerge=N" 명령(N 은 0 과 15 사이의 정수, 양끝 포함)은 FTS3/4 테이블의 "automerge" 매개변수를 구성하는 데 사용되는데, 이 매개변수는 자동 점진적 역 인덱스 병합을 제어해요. 새 테이블의 기본 automerge 값은 0 이며, 이는 자동 점진적 병합이 완전히 비활성화된다는 뜻이에요. automerge 매개변수의 값이 "automerge=N" 명령으로 수정되면 새 매개변수 값이 데이터베이스에 영구히 저장되고, 이후에 수립되는 모든 데이터베이스 연결이 사용해요.
automerge 매개변수를 0 이 아닌 값으로 설정하면 자동 점진적 병합이 활성화돼요. 이는 SQLite 가 모든 INSERT 작업 후에 소량의 역 인덱스 병합을 하게 해요. 수행되는 병합 양은 FTS3/4 테이블이 같은 레벨에 16 개의 세그먼트를 가지는 지점에 도달해 삽입을 완료하기 위해 큰 병합을 해야 하는 일이 없도록 설계돼요. 즉, 자동 점진적 병합은 뾰족한 INSERT 성능을 방지하도록 설계됐어요.
자동 점진적 병합의 단점은 점진적 병합을 하는 데 추가 시간을 사용해야 하므로 FTS3/4 테이블에 대한 모든 INSERT, UPDATE, DELETE 작업이 조금 더 느리다는 것이에요. 최대 성능을 위해 애플리케이션은 자동 점진적 병합을 비활성화하고 대신 유휴 프로세스에서 "merge" 명령을 사용해 역 인덱스를 잘 병합된 상태로 유지하는 것이 권장돼요. 하지만 애플리케이션의 구조가 유휴 프로세스를 쉽게 허용하지 않으면 자동 점진적 병합의 사용은 매우 합리적인 대체 해결책이에요.
automerge 매개변수의 실제 값은 자동 역 인덱스 병합이 동시에 병합하는 인덱스 세그먼트 수를 결정해요. 값이 N 으로 설정되면 시스템은 단일 레벨에 최소 N 개의 세그먼트가 있을 때까지 기다렸다가 점진적으로 병합하기 시작해요. N 의 낮은 값을 설정하면 세그먼트가 더 빨리 병합되어 전체 텍스트 쿼리를 빠르게 하고, 작업 부하에 INSERT 뿐만 아니라 UPDATE 나 DELETE 작업이 포함되어 있으면 전체 텍스트 인덱스가 소비하는 디스크 공간을 줄일 수 있어요. 하지만 디스크에 기록되는 데이터 양도 늘려요.
작업 부하에 UPDATE 나 DELETE 작업이 거의 없는 일반적인 사용에서는 automerge 로 8 이 좋은 선택이에요. 작업 부하에 UPDATE 나 DELETE 명령이 많거나 쿼리 속도가 우려되면 automerge 를 2 로 줄이는 것이 유리할 수 있어요.
하위 호환성 이유로 "automerge=1" 명령은 automerge 매개변수를 1 이 아니라 8 로 설정해요(어차피 1 값은 의미가 없어요. 단일 세그먼트의 데이터를 병합하는 것은 no-op 이기 때문이에요).
8. 토크나이저 (Tokenizers)
FTS 토크나이저는 문서 또는 기본 FTS 전체 텍스트 쿼리에서 용어를 추출하는 일련의 규칙이에요.
FTS 테이블을 만드는 데 사용되는 CREATE VIRTUAL TABLE 문의 일부로 특정 토크나이저가 지정되지 않으면 기본 토크나이저인 "simple" 이 사용돼요. simple 토크나이저는 다음 규칙에 따라 문서 또는 기본 FTS 전체 텍스트 쿼리에서 토큰을 추출해요:
- 용어는 적격 문자의 연속 시퀀스이며, 적격 문자는 모든 영숫자 문자와 Unicode 코드포인트 값이 128 이상인 모든 문자예요. 다른 모든 문자는 문서를 용어로 분할할 때 버려져요. 그것들의 유일한 기여는 인접한 용어를 분리하는 것이에요.
- ASCII 범위(Unicode 코드포인트 128 미만)의 모든 대문자는 토큰화 과정의 일부로 소문자 등가물로 변환돼요. 따라서 simple 토크나이저를 사용할 때 전체 텍스트 쿼리는 대소문자를 구분하지 않아요.
예를 들어 "Right now, they're very frustrated." 텍스트를 포함하는 문서가 있을 때, 문서에서 추출되어 전체 텍스트 인덱스에 추가되는 용어는 순서대로 "right now they re very frustrated" 예요. 그러한 문서는 "MATCH 'Frustrated'" 같은 전체 텍스트 쿼리와 일치할 거예요. simple 토크나이저가 전체 텍스트 인덱스를 검색하기 전에 쿼리의 용어를 소문자로 변환하기 때문이에요.
"simple" 토크나이저 외에도 FTS 소스 코드에는 Porter Stemming 알고리즘을 사용하는 토크나이저가 있어요. 이 토크나이저는 입력 문서를 용어로 분리하는 데 같은 규칙을 사용하고 모든 용어를 소문자로 접지만, Porter Stemming 알고리즘을 사용해 관련된 영어 단어를 공통 어근으로 줄이기도 해요. 예를 들어 위 문단과 같은 입력 문서를 사용하면 porter 토크나이저는 "right now thei veri frustrat" 토큰을 추출해요. 이 용어 중 일부는 영어 단어조차 아니지만, 어떤 경우에는 이것들로 전체 텍스트 인덱스를 구축하는 것이 simple 토크나이저가 만드는 더 알아듣기 쉬운 출력보다 더 유용해요. porter 토크나이저를 사용하면 문서는 "MATCH 'Frustrated'" 같은 전체 텍스트 쿼리뿐만 아니라 "MATCH 'Frustration'" 같은 쿼리와도 일치해요. 용어 "Frustration" 이 Porter stemmer 알고리즘에 의해 "Frustrated" 처럼 "frustrat" 로 줄어들기 때문이에요. 그래서 porter 토크나이저를 사용할 때 FTS 는 쿼리된 용어에 대한 정확한 일치뿐만 아니라 유사한 영어 용어에 대한 일치도 찾을 수 있어요. Porter Stemmer 알고리즘에 대한 자세한 정보는 위에 링크된 페이지를 참고하세요.
"simple" 과 "porter" 토크나이저의 차이를 보여주는 예:
-- Create a table using the simple tokenizer. Insert a document into it.
CREATE VIRTUAL TABLE simple USING fts3(tokenize=simple);
INSERT INTO simple VALUES('Right now they''re very frustrated');
-- The first of the following two queries matches the document stored in
-- table "simple". The second does not.
SELECT * FROM simple WHERE simple MATCH 'Frustrated';
SELECT * FROM simple WHERE simple MATCH 'Frustration';
-- Create a table using the porter tokenizer. Insert the same document into it
CREATE VIRTUAL TABLE porter USING fts3(tokenize=porter);
INSERT INTO porter VALUES('Right now they''re very frustrated');
-- Both of the following queries match the document stored in table "porter".
SELECT * FROM porter WHERE porter MATCH 'Frustrated';
SELECT * FROM porter WHERE porter MATCH 'Frustration';
이 확장이 SQLITE_ENABLE_ICU 전처리기 기호를 정의해 컴파일되면 ICU 라이브러리를 사용해 구현된 "icu"라는 내장 토크나이저가 존재해요. 이 토크나이저의 xCreate() 메서드(fts3_tokenizer.h 참고)에 전달되는 첫 번째 인자는 ICU 로케일 식별자일 수 있어요. 예를 들어 터키어로 사용되는 "tr_TR" 또는 호주 영어로 사용되는 "en_AU" 가 있어요. 예를 들어:
CREATE VIRTUAL TABLE thai_text USING fts3(text, tokenize=icu th_TH)
ICU 토크나이저 구현은 매우 단순해요. ICU 규칙에 따라 단어 경계를 찾아 입력 텍스트를 분할하고 전적으로 공백으로 구성된 모든 토큰을 버려요. 이것은 일부 로케일의 일부 애플리케이션에는 적합할 수 있지만 모두에게 적합하지는 않아요. 스테밍(stemming)이나 문장 부호 버리기 같은 더 복잡한 처리가 필요하면 ICU 토크나이저를 구현의 일부로 사용하는 토크나이저 구현을 만들어 이 작업을 수행할 수 있어요.
"unicode61" 토크나이저는 SQLite 버전 3.7.13 (2012-06-11) 부터 사용 가능해요. Unicode61 은 Unicode 버전 6.1 의 규칙에 따라 간단한 유니코드 대소문자 접기를 하고 유니코드 공백과 문장 부호 문자를 인식해 그것들로 토큰을 분리한다는 점을 제외하면 "simple" 과 매우 유사하게 작동해요. simple 토크나이저는 ASCII 문자의 대소문자 접기만 하고 ASCII 공백과 문장 부호 문자만 토큰 구분자로 인식해요.
기본적으로 "unicode61" 은 라틴 문자에서 분음 부호(diacritics)를 제거하려고 시도해요. 이 동작은 토크나이저 인자 "remove_diacritics=0" 을 추가해 재정의할 수 있어요. 예를 들어:
-- Create tables that remove alldiacritics from Latin script characters
-- as part of tokenization.
CREATE VIRTUAL TABLE txt1 USING fts4(tokenize=unicode61);
CREATE VIRTUAL TABLE txt2 USING fts4(tokenize=unicode61 "remove_diacritics=2");
-- Create a table that does not remove diacritics from Latin script
-- characters as part of tokenization.
CREATE VIRTUAL TABLE txt3 USING fts4(tokenize=unicode61 "remove_diacritics=0");
remove_diacritics 옵션은 "0", "1" 또는 "2" 로 설정할 수 있어요. 기본값은 "1" 이에요. "1" 이나 "2" 로 설정하면 위에서 설명한 대로 라틴 문자에서 분음 부호가 제거돼요. 하지만 "1" 로 설정하면 단일 유니코드 코드포인트가 분음 부호가 둘 이상인 문자를 나타내는 데 사용되는 상당히 드문 경우에는 분음 부호가 제거되지 않아요. 예를 들어 코드포인트 0x1ED9("LATIN SMALL LETTER O WITH CIRCUMFLEX AND DOT BELOW")에서는 분음 부호가 제거되지 않아요. 이것은 기술적으로 버그이지만 하위 호환성 문제를 만들지 않고는 고칠 수 없어요. 이 옵션이 "2" 로 설정되면 모든 라틴 문자에서 분음 부호가 올바르게 제거돼요.
unicode61 이 구분자 문자로 취급하는 코드포인트 집합을 사용자 정의하는 것도 가능해요. "separators=" 옵션은 구분자 문자로 취급되어야 하는 하나 이상의 추가 문자를 지정하는 데 사용될 수 있고, "tokenchars=" 옵션은 구분자 문자 대신 토큰의 일부로 취급되어야 하는 하나 이상의 추가 문자를 지정하는 데 사용될 수 있어요. 예를 들어:
-- Create a table that uses the unicode61 tokenizer, but considers "."
-- and "=" characters to be part of tokens, and capital "X" characters to
-- function as separators.
CREATE VIRTUAL TABLE txt3 USING fts4(tokenize=unicode61 "tokenchars=.=" "separators=X");
-- Create a table that considers space characters (codepoint 32) to be
-- a token character
CREATE VIRTUAL TABLE txt4 USING fts4(tokenize=unicode61 "tokenchars= ");
"tokenchars=" 인자의 일부로 지정된 문자가 기본적으로 토큰 문자로 간주되면 무시돼요. 이전 "separators=" 옵션으로 구분자로 표시됐더라도 마찬가지예요. 마찬가지로 "separators=" 옵션의 일부로 지정된 문자가 기본적으로 구분자 문자로 취급되면 무시돼요. 여러 "tokenchars=" 또는 "separators=" 옵션이 지정되면 모두 처리돼요. 예를 들어:
-- Create a table that uses the unicode61 tokenizer, but considers "."
-- and "=" characters to be part of tokens, and capital "X" characters to
-- function as separators. Both of the "tokenchars=" options are processed
-- The "separators=" option ignores the "." passed to it, as "." is by
-- default a separator character, even though it has been marked as a token
-- character by an earlier "tokenchars=" option.
CREATE VIRTUAL TABLE txt5 USING fts4(
tokenize=unicode61 "tokenchars=." "separators=X." "tokenchars=="
);
"tokenchars=" 또는 "separators=" 옵션에 전달된 인자는 대소문자를 구분해요. 위 예에서 "X" 가 구분자 문자라고 지정하는 것은 "x" 가 처리되는 방식에 영향을 주지 않아요.
8.1. 사용자 정의 (애플리케이션 정의) 토크나이저
내장된 "simple", "porter" 및 (가능하면) "icu" 와 "unicode61" 토크나이저를 제공하는 것 외에도 FTS 는 애플리케이션이 C 로 작성된 사용자 정의 토크나이저를 구현하고 등록할 수 있는 인터페이스를 제공해요. 새 토크나이저를 만드는 데 사용되는 인터페이스는 fts3_tokenizer.h 소스 파일에 정의되고 설명돼 있어요.
새 FTS 토크나이저를 등록하는 것은 SQLite 에 새 가상 테이블 모듈을 등록하는 것과 비슷해요. 사용자는 새 토크나이저 타입의 구현을 구성하는 다양한 콜백 함수에 대한 포인터를 포함하는 구조체에 대한 포인터를 전달해요. 토크나이저의 경우 구조체(fts3_tokenizer.h 에 정의)는 "sqlite3_tokenizer_module"이라고 불려요.
FTS 는 사용자가 데이터베이스 핸들에 새 토크나이저 타입을 등록하기 위해 호출하는 C-함수를 노출하지 않아요. 대신 포인터가 SQL blob 값으로 인코딩되어, 특별한 스칼라 함수 "fts3_tokenizer()" 를 평가함으로써 SQL 엔진을 통해 FTS 에 전달되어야 해요. fts3_tokenizer() 함수는 다음과 같이 1 개 또는 2 개의 인자로 호출될 수 있어요:
SELECT fts3_tokenizer(<tokenizer-name>);
SELECT fts3_tokenizer(<tokenizer-name>, <sqlite3_tokenizer_module ptr>);
여기서
SQLite 버전 3.11.0 (2016-02-15) 이전에는 fts3_tokenizer() 의 인자가 리터럴 문자열이나 BLOB 일 수 있었어요. 바인딩된 매개변수일 필요가 없었어요. 하지만 그것은 SQL 주입의 경우 보안 문제로 이어질 수 있어요. 따라서 레거시 동작은 이제 기본적으로 비활성화돼 있어요. 하지만 정말로 필요한 애플리케이션의 하위 호환성을 위해 sqlite3_db_config(db,SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER,1,0) 을 호출해 이전 레거시 동작을 활성화할 수 있어요.
다음 블록은 C 코드에서 fts3_tokenizer() 함수를 호출하는 예를 포함해요:
/*
** Register a tokenizer implementation with FTS3 or FTS4.
*/
int registerTokenizer(
sqlite3 *db,
char *zName,
const sqlite3_tokenizer_module *p
){
int rc;
sqlite3_stmt *pStmt;
const char *zSql = "SELECT fts3_tokenizer(?1, ?2)";
rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
if( rc!=SQLITE_OK ){
return rc;
}
sqlite3_bind_text(pStmt, 1, zName, -1, SQLITE_STATIC);
sqlite3_bind_blob(pStmt, 2, &p, sizeof(p), SQLITE_STATIC);
sqlite3_step(pStmt);
return sqlite3_finalize(pStmt);
}
/*
** Query FTS for the tokenizer implementation named zName.
*/
int queryTokenizer(
sqlite3 *db,
char *zName,
const sqlite3_tokenizer_module **pp
){
int rc;
sqlite3_stmt *pStmt;
const char *zSql = "SELECT fts3_tokenizer(?)";
*pp = 0;
rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
if( rc!=SQLITE_OK ){
return rc;
}
sqlite3_bind_text(pStmt, 1, zName, -1, SQLITE_STATIC);
if( SQLITE_ROW==sqlite3_step(pStmt) ){
if( sqlite3_column_type(pStmt, 0)==SQLITE_BLOB ){
memcpy(pp, sqlite3_column_blob(pStmt, 0), sizeof(*pp));
}
}
return sqlite3_finalize(pStmt);
}
8.2. 토크나이저 쿼리
"fts3tokenize" 가상 테이블을 사용해 어떤 토크나이저든 직접 접근할 수 있어요. 다음 SQL 은 fts3tokenize 가상 테이블의 인스턴스를 만드는 방법을 보여줘요:
CREATE VIRTUAL TABLE tok1 USING fts3tokenize('porter');
원하는 토크나이저의 이름을 예의 'porter' 자리에 대체해야 해요. 토크나이저가 하나 이상의 인자를 필요로 하면 fts3tokenize 선언에서 쉼표로 구분되어야 해요(일반 fts4 테이블 선언에서는 공백으로 구분되지만). 다음은 같은 토크나이저를 사용하는 fts4 와 fts3tokenize 테이블을 만들어요:
CREATE VIRTUAL TABLE text1 USING fts4(tokenize=icu en_AU);
CREATE VIRTUAL TABLE tokens1 USING fts3tokenize(icu, en_AU);
CREATE VIRTUAL TABLE text2 USING fts4(tokenize=unicode61 "tokenchars=@." "separators=123");
CREATE VIRTUAL TABLE tokens2 USING fts3tokenize(unicode61, "tokenchars=@.", "separators=123");
가상 테이블이 만들어지면 다음과 같이 쿼리할 수 있어요:
SELECT token, start, end, position
FROM tok1
WHERE input='This is a test sentence.';
가상 테이블은 입력 문자열의 각 토큰에 대해 하나의 출력 행을 반환해요. "token" 컬럼은 토큰의 텍스트예요. "start" 와 "end" 컬럼은 원래 입력 문자열에서 토큰의 시작과 끝까지의 바이트 오프셋이에요. "position" 컬럼은 원래 입력 문자열에서 토큰의 시퀀스 번호예요. 또한 WHERE 절에 지정된 입력 문자열의 단순한 복사본인 "input" 컬럼이 있어요. WHERE 절에 "input=?" 형태의 제약 조건이 나타나야 하며, 그렇지 않으면 가상 테이블에 토큰화할 입력이 없어 행을 반환하지 않을 거예요. 위 예는 다음 출력을 생성해요:
thi|0|4|0
is|5|7|1
a|8|9|2
test|10|14|3
sentenc|15|23|4
fts3tokenize 가상 테이블의 결과 집합의 토큰이 토크나이저의 규칙에 따라 변환되었다는 점에 주목하세요. 이 예는 "porter" 토크나이저를 사용했으므로 "This" 토큰이 "thi" 로 변환됐어요. 토큰의 원래 텍스트가 필요하면 "start" 와 "end" 컬럼을 substr() 함수와 함께 사용해 검색할 수 있어요. 예를 들어:
SELECT substr(input, start+1, end-start), token, position
FROM tok1
WHERE input='This is a test sentence.';
fts3tokenize 가상 테이블은 실제로 그 토크나이저를 사용하는 FTS3 이나 FTS4 테이블이 존재하는지 여부와 무관하게 어떤 토크나이저에도 사용될 수 있어요.
9. 데이터 구조
이 개요는 FTS 모듈이 데이터베이스에 인덱스와 내용을 어떻게 저장하는지 설명해요. 애플리케이션에서 FTS 를 사용하기 위해 이 섹션의 내용을 읽거나 이해할 필요는 없어요. 하지만 FTS 성능 특성을 분석하고 이해하려는 애플리케이션 개발자나 기존 FTS 기능 세트의 개선을 고려하는 개발자에게는 유용할 수 있어요.
9.1. 섀도 테이블 (Shadow Tables)
데이터베이스의 각 FTS 가상 테이블에 대해 세 개에서 다섯 개의 실제(비가상) 테이블이 기본 데이터를 저장하기 위해 만들어져요. 이 실제 테이블을 "섀도 테이블"이라고 불러요. 실제 테이블의 이름은 "%_content", "%_segdir", "%_segments", "%_stat", "%_docsize" 이고, 여기서 "%" 는 FTS 가상 테이블의 이름으로 대체돼요.
"%_content" 테이블의 가장 왼쪽 컬럼은 "docid"라는 INTEGER PRIMARY KEY 필드예요. 그 뒤에 FTS 가상 테이블의 각 컬럼에 대해 사용자가 선언한 하나의 컬럼이 오는데, 사용자가 제공한 컬럼 이름 앞에 "cN" 을 붙여 이름 짓고, N 은 테이블 안에서 왼쪽에서 오른쪽으로 0 부터 시작해 번호가 매겨진 컬럼의 인덱스예요. 가상 테이블 선언의 일부로 제공된 데이터 타입은 %_content 테이블 선언의 일부로 사용되지 않아요. 예를 들어:
-- Virtual table declaration
CREATE VIRTUAL TABLE abc USING fts4(a NUMBER, b TEXT, c);
-- Corresponding %_content table declaration
CREATE TABLE abc_content(docid INTEGER PRIMARY KEY, c0a, c1b, c2c);
%_content 테이블은 사용자가 FTS 가상 테이블에 삽입한 정제되지 않은 데이터를 포함해요. 레코드를 삽입할 때 사용자가 "docid" 값을 명시적으로 제공하지 않으면 시스템이 자동으로 하나를 선택해요.
%_stat 과 %_docsize 테이블은 FTS 테이블이 FTS3 이 아닌 FTS4 모듈을 사용할 때만 만들어져요. 게다가 FTS4 테이블이 CREATE VIRTUAL TABLE 문의 일부로 "matchinfo=fts3" 지시어를 지정해 만들어지면 %_docsize 테이블은 생략돼요. 만들어지면 두 테이블의 스키마는 다음과 같아요:
CREATE TABLE %_stat(
id INTEGER PRIMARY KEY,
value BLOB
);
CREATE TABLE %_docsize(
docid INTEGER PRIMARY KEY,
size BLOB
);
FTS 테이블의 각 행에 대해 %_docsize 테이블에는 같은 "docid" 값을 가진 해당 행이 있어요. "size" 필드는 N 개의 FTS varint 로 구성된 blob 을 포함하는데, N 은 테이블의 사용자 정의 컬럼 수예요. "size" blob 의 각 varint 는 FTS 테이블의 연관된 행의 해당 컬럼에 있는 토큰 수예요. %_stat 테이블은 항상 "id" 컬럼이 0 으로 설정된 단일 행을 포함해요. "value" 컬럼은 N+1 개의 FTS varint 로 구성된 blob 을 포함하는데, N 은 역시 FTS 테이블의 사용자 정의 컬럼 수예요. blob 의 첫 varint 는 FTS 테이블의 총 행 수로 설정돼요. 두 번째 및 이후 varint 는 FTS 테이블의 모든 행에 대해 해당 컬럼에 저장된 총 토큰 수를 포함해요.
나머지 두 테이블 %_segments 와 %_segdir 은 전체 텍스트 인덱스를 저장하는 데 사용돼요. 개념적으로 이 인덱스는 각 용어(단어)를 %_content 테이블에서 그 용어의 발생을 하나 이상 포함하는 레코드에 해당하는 docid 값의 집합에 매핑하는 조회 테이블이에요. 지정된 용어를 포함하는 모든 문서를 검색하기 위해 FTS 모듈은 이 인덱스를 쿼리해 그 용어를 포함하는 레코드의 docid 값 집합을 결정한 다음 %_content 테이블에서 필요한 문서를 검색해요. FTS 가상 테이블의 스키마와 무관하게 %_segments 와 %_segdir 테이블은 항상 다음과 같이 만들어져요:
CREATE TABLE %_segments(
blockid INTEGER PRIMARY KEY, -- B-tree node id
block blob -- B-tree node data
);
CREATE TABLE %_segdir(
level INTEGER,
idx INTEGER,
start_block INTEGER, -- Blockid of first node in %_segments
leaves_end_block INTEGER, -- Blockid of last leaf node in %_segments
end_block INTEGER, -- Blockid of last node in %_segments
root BLOB, -- B-tree root node
PRIMARY KEY(level, idx)
);
위에 묘사된 스키마는 전체 텍스트 인덱스를 직접 저장하도록 설계되지 않았어요. 대신 하나 이상의 b-tree 구조를 저장하는 데 사용돼요. %_segdir 테이블의 각 행마다 하나의 b-tree 가 있어요. %_segdir 테이블 행에는 루트 노드와 b-tree 구조와 연관된 다양한 메타데이터가 포함되고, %_segments 테이블은 다른 모든(비루트) b-tree 노드를 포함해요. 각 b-tree 를 "세그먼트(segment)"라고 불러요. 한 번 만들어지면 세그먼트 b-tree 는 절대 갱신되지 않아요(완전히 삭제될 수는 있어요).
각 세그먼트 b-tree 가 사용하는 키는 용어(단어)예요. 키뿐만 아니라 각 세그먼트 b-tree 항목에는 연관된 "doclist"(문서 목록)가 있어요. doclist 는 0 개 이상의 항목으로 구성되고, 각 항목은 다음으로 구성돼요:
- docid(문서 id), 그리고
- 문서 안에서 그 용어의 각 발생에 대해 하나씩 있는 용어 오프셋 목록. 용어 오프셋은 해당 용어 앞에 나타나는 토큰(단어) 수를 나타내며, 문자나 바이트 수가 아니에요. 예를 들어 "Ancestral voices prophesying war!" 구문에서 용어 "war" 의 용어 오프셋은 3 이에요.
doclist 안의 항목은 docid 로 정렬돼요. doclist 항목 안의 위치는 오름차순으로 저장돼요.
논리적 전체 텍스트 인덱스의 내용은 모든 세그먼트 b-tree 의 내용을 병합해 찾아져요. 용어가 둘 이상의 세그먼트 b-tree 에 존재하면 각 개별 doclist 의 합집합에 매핑돼요. 단일 용어에 대해 같은 docid 가 둘 이상의 doclist 에 나타나면, 가장 최근에 만들어진 세그먼트 b-tree 의 일부인 doclist 만 유효한 것으로 간주돼요.
단일 b-tree 대신 여러 b-tree 구조를 사용하는 것은 FTS 테이블에 레코드를 삽입하는 비용을 줄이기 위해서예요. 이미 많은 데이터를 포함하는 FTS 테이블에 새 레코드가 삽입될 때, 새 레코드의 많은 용어가 이미 많은 수의 기존 레코드에 존재할 가능성이 높아요. 단일 b-tree 를 사용하면 큰 doclist 구조를 데이터베이스에서 로드하고, 새 docid 와 용어-오프셋 목록을 포함하도록 수정한 다음, 데이터베이스에 다시 써야 해요. 여러 b-tree 테이블을 사용하면 나중에 기존 b-tree(들)와 병합할 수 있는 새 b-tree 를 만들어 이를 피할 수 있어요. b-tree 구조의 병합은 백그라운드 작업으로, 또는 특정 수의 별도 b-tree 구조가 쌓인 후에 수행될 수 있어요. 물론 이 방식은 쿼리를 더 비싸게 만들지만(FTS 코드가 둘 이상의 b-tree 에서 개별 용어를 조회하고 결과를 병합해야 할 수 있으므로), 실제로 이 오버헤드는 종종 무시할 만하다는 것이 밝혀졌어요.
9.2. 가변 길이 정수 (varint) 형식
세그먼트 b-tree 노드의 일부로 저장된 정수 값은 FTS varint 형식을 사용해 인코딩돼요. 이 인코딩은 SQLite varint 형식과 비슷하지만 동일하지는 않아요.
인코딩된 FTS varint 는 1 에서 10 바이트 사이의 공간을 소비해요. 필요한 바이트 수는 인코딩된 정수 값의 부호와 크기로 결정돼요. 더 정확히, 인코딩된 정수를 저장하는 데 사용되는 바이트 수는 정수 값의 64비트 2의 보수 표현에서 가장 중요한 설정 비트의 위치에 따라 달라져요. 음수 값은 항상 가장 중요한 비트(부호 비트)가 설정되므로 항상 전체 10 바이트를 사용해 저장돼요. 양수 정수 값은 더 적은 공간으로 저장될 수 있어요.
인코딩된 FTS varint 의 마지막 바이트는 가장 중요한 비트가 지워져 있어요. 앞선 모든 바이트는 가장 중요한 비트가 설정돼 있어요. 데이터는 각 바이트의 나머지 7 개의 가장 덜 중요한 비트에 저장돼요. 인코딩된 표현의 첫 번째 바이트는 인코딩된 정수 값의 가장 덜 중요한 7 비트를 포함해요. 인코딩된 표현의 두 번째 바이트(있으면)는 정수 값의 다음 7 개의 가장 덜 중요한 비트를 포함하고, 이런 식이에요. 다음 표는 인코딩된 정수 값의 예를 포함해요:
| Decimal | Hexadecimal | Encoded Representation |
|---|---|---|
| 43 | 0x000000000000002B | 0x2B |
| 200815 | 0x000000000003106F | 0xEF 0xA0 0x0C |
| -1 | 0xFFFFFFFFFFFFFFFF | 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0x01 |
9.3. 세그먼트 B-Tree 형식
세그먼트 b-tree 는 접두어 압축된 b+-tree 예요. %_segdir 테이블의 각 행마다 하나의 세그먼트 b-tree 가 있어요(위 참고). 세그먼트 b-tree 의 루트 노드는 %_segdir 테이블의 해당 행의 "root" 필드에 blob 으로 저장돼요. 다른 모든 노드(존재한다면)는 %_segments 테이블의 "blob" 컬럼에 저장돼요. %_segments 테이블 안의 노드는 해당 행의 blockid 필드의 정수 값으로 식별돼요. 다음 표는 %_segdir 테이블의 필드를 설명해요:
| Column | Interpretation |
|---|---|
| level | Between them, the contents of the "level" and "idx" fields define the relative age of the segment b-tree. The smaller the value stored in the "level" field, the more recently the segment b-tree was created. If two segment b-trees are of the same "level", the segment with the larger value stored in the "idx" column is more recent. The PRIMARY KEY constraint on the %_segdir table prevents any two segments from having the same value for both the "level" and "idx" fields. |
| idx | See above. |
| start_block | The blockid that corresponds to the node with the smallest blockid that belongs to this segment b-tree. Or zero if the entire segment b-tree fits on the root node. If it exists, this node is always a leaf node. |
| leaves_end_block | The blockid that corresponds to the leaf node with the largest blockid that belongs to this segment b-tree. Or zero if the entire segment b-tree fits on the root node. |
| end_block | This field may contain either an integer or a text field consisting of two integers separated by a space character (unicode codepoint 0x20). The first, or only, integer is the blockid that corresponds to the interior node with the largest blockid that belongs to this segment b-tree. Or zero if the entire segment b-tree fits on the root node. If it exists, this node is always an interior node. The second integer, if it is present, is the aggregate size of all data stored on leaf pages in bytes. If the value is negative, then the segment is the output of an unfinished incremental-merge operation, and the absolute value is current size in bytes. |
| root | Blob containing the root node of the segment b-tree. |
루트 노드를 제외하고 단일 세그먼트 b-tree 를 구성하는 노드는 항상 연속적인 blockid 시퀀스로 저장돼요. 게다가 b-tree 의 단일 레벨을 구성하는 노드 자체가 b-tree 순서로 연속 블록으로 저장돼요. b-tree 리프를 저장하는 데 사용되는 연속적인 blockid 시퀀스는 해당 %_segdir 행의 "start_block" 컬럼에 저장된 blockid 값으로 시작해, 같은 행의 "leaves_end_block" 필드에 저장된 blockid 값에서 끝나도록 할당돼요. 따라서 %_segments 테이블을 "start_block" 에서 "leaves_end_block" 까지 blockid 순서로 탐색함으로써 세그먼트 b-tree 의 모든 리프를 키 순서로 반복할 수 있어요.
9.3.1. 세그먼트 B-Tree 리프 노드
다음 다이어그램은 세그먼트 b-tree 리프 노드의 형식을 묘사해요. (그림은 원문 참고)
각 노드에 저장된 첫 번째 용어(위 그림의 "Term 1")는 그대로 저장돼요. 각 후속 용어는 그 이전 용어에 대해 접두어 압축돼요. 용어는 페이지 안에서 정렬(memcmp) 순서로 저장돼요.
9.3.2. 세그먼트 B-Tree 내부 노드
다음 다이어그램은 세그먼트 b-tree 내부(비리프) 노드의 형식을 묘사해요. (그림은 원문 참고)
9.4. Doclist 형식
doclist 는 FTS varint 형식으로 직렬화된 64비트 부호 있는 정수의 배열로 구성돼요. 각 doclist 항목은 다음과 같이 두 개 이상의 정수 시리즈로 만들어져요:
- docid 값. doclist 의 첫 항목은 리터럴 docid 값을 포함해요. 각 후속 doclist 항목의 첫 필드는 새 docid 와 이전 docid 의 차이(항상 양수)를 포함해요.
- 0 개 이상의 용어-오프셋 목록. 용어-오프셋 목록은 그 용어를 포함하는 FTS 가상 테이블의 각 컬럼에 대해 존재해요. 용어-오프셋 목록은 다음으로 구성돼요:
- 상수 값 1. 이 필드는 컬럼 0 과 연관된 용어-오프셋 목록에 대해 생략돼요.
- 컬럼 번호(왼쪽에서 두 번째 컬럼에 대해 1 등). 이 필드는 컬럼 0 과 연관된 용어-오프셋 목록에 대해 생략돼요.
- 가장 작은 것부터 가장 큰 것 순으로 정렬된 용어-오프셋 목록. 용어-오프셋 값을 그대로 저장하는 대신, 각 저장된 정수는 현재 용어-오프셋과 이전 것의 차이(현재 용어-오프셋이 첫 번째면 0) 더하기 2 예요.
- 상수 값 0.
용어가 FTS 가상 테이블의 둘 이상의 컬럼에 나타나는 doclist 의 경우, doclist 안의 용어-오프셋 목록은 컬럼 번호 순서로 저장돼요. 이는 컬럼 0 과 연관된 용어-오프셋 목록(있다면)이 항상 먼저 오도록 보장해, 이 경우 용어-오프셋 목록의 처음 두 필드를 생략할 수 있게 해요.
10. 제한 사항 (Limitations)
10.1. UTF-16 byte-order-mark 문제
UTF-16 데이터베이스에서 "simple" 토크나이저를 사용할 때 잘못된 형식의 유니코드 문자열을 사용해 integrity-check 특별 명령이 손상을 거짓으로 보고하게 하거나 보조 함수가 잘못된 결과를 반환하게 할 수 있어요. 더 구체적으로, 이 버그는 다음 중 어떤 것도 트리거할 수 있어요:
- UTF-16 BOM(byte-order-mark)이 FTS3 테이블에 삽입된 SQL 문자열 리터럴 값의 시작에 내장되어 있다. 예를 들어:
INSERT INTO fts_table(col) VALUES(char(0xfeff)||'text...');
- SQLite 가 UTF-16 BOM 으로 변환하는 잘못된 형식의 UTF-8 이 FTS3 테이블에 삽입된 SQL 문자열 리터럴 값의 시작에 내장되어 있다.
- 0xFF 와 0xFE 의 두 바이트(어느 순서든)로 시작하는 blob 을 캐스팅해 만든 텍스트 값이 FTS3 테이블에 삽입된다. 예를 들어:
INSERT INTO fts_table(col) VALUES(CAST(X'FEFF' AS TEXT));
다음 중 어떤 것이 참이어도 모든 것이 올바르게 작동해요:
-
데이터베이스 인코딩이 UTF-8 이다.
-
모든 텍스트 문자열이 sqlite3_bind_text() 함수 계열 중 하나로 삽입된다.
-
리터럴 문자열에 BOM 이 없다.
-
BOM 을 공백으로 인식하는 토크나이저가 사용된다. (FTS3/4 의 기본 "simple" 토크나이저는 BOM 이 공백이라고 생각하지 않지만, unicode 토크나이저는 그렇게 생각해요.)
위 조건이 모두 거짓이어야 문제가 발생해요. 그리고 위 조건이 모두 거짓이더라도 대부분의 것이 여전히 올바르게 작동해요. 오직 integrity-check 명령과 보조 함수만이 예상치 못한 결과를 줄 수 있어요.
부록 A: 검색 애플리케이션 팁
FTS 는 주로 부울 전체 텍스트 쿼리, 즉 지정된 기준과 일치하는 문서 집합을 찾는 쿼리를 지원하도록 설계됐어요. 하지만 많은(대부분의?) 검색 애플리케이션은 결과를 "관련성(relevance)" 순서로 순위 매겨야 하는데, "관련성"은 검색을 수행한 사용자가 반환된 문서 집합의 특정 요소에 관심이 있을 가능성으로 정의돼요. 월드 와이드 웹에서 문서를 찾기 위해 검색 엔진을 사용할 때 사용자는 가장 유용한, 즉 "관련 있는" 문서가 첫 번째 결과 페이지로 반환되고, 각 후속 페이지에는 점점 덜 관련된 결과가 포함되기를 기대해요. 사용자 쿼리에 기반해 문서 관련성을 머신이 정확히 어떻게 결정할 수 있는지는 복잡한 문제이고 많은 진행 중인 연구의 주제예요.
매우 간단한 방식 하나는 각 결과 문서에서 사용자 검색 용어의 인스턴스 수를 세는 것일 수 있어요. 그 용어의 인스턴스를 많이 포함하는 문서가 각 용어의 인스턴스를 적게 가진 문서보다 더 관련 있는 것으로 간주돼요. FTS 애플리케이션에서 각 결과의 용어 인스턴스 수는 offsets 함수의 반환 값의 정수 수를 세어 결정할 수 있어요. 다음 예는 사용자가 입력한 쿼리에 대해 가장 관련 있는 결과 10 개를 얻는 데 사용될 수 있는 쿼리를 보여줘요:
-- This example (and all others in this section) assumes the following schema
CREATE VIRTUAL TABLE documents USING fts3(title, content);
-- Assuming the application has supplied an SQLite user function named "countintegers"
-- that returns the number of space-separated integers contained in its only argument,
-- the following query could be used to return the titles of the 10 documents that contain
-- the greatest number of instances of the users query terms. Hopefully, these 10
-- documents will be those that the users considers more or less the most "relevant".
SELECT title FROM documents
WHERE documents MATCH <query>
ORDER BY countintegers(offsets(documents)) DESC
LIMIT 10 OFFSET 0
위 쿼리는 FTS matchinfo 함수를 사용해 각 결과에 나타나는 쿼리 용어 인스턴스 수를 결정함으로써 더 빠르게 실행될 수 있어요. matchinfo 함수는 offsets 함수보다 훨씬 효율적이에요. 게다가 matchinfo 함수는 전체 문서 집합(현재 행뿐만 아니라)에서 각 쿼리 용어의 총 발생 수와 각 쿼리 용어가 나타나는 문서 수에 대한 추가 정보를 제공해요. 이것은 (예를 들어) 덜 흔한 용어에 더 높은 가중치를 부여하는 데 사용될 수 있으며, 이는 사용자가 더 흥미롭다고 생각하는 결과의 전체 계산된 관련성을 높일 수 있어요.
-- If the application supplies an SQLite user function called "rank" that
-- interprets the blob of data returned by matchinfo and returns a numeric
-- relevancy based on it, then the following SQL may be used to return the
-- titles of the 10 most relevant documents in the dataset for a users query.
SELECT title FROM documents
WHERE documents MATCH <query>
ORDER BY rank(matchinfo(documents)) DESC
LIMIT 10 OFFSET 0
위 예의 SQL 쿼리는 이 섹션의 첫 예보다 적은 CPU 를 사용하지만, 여전히 눈에 띄지 않는 성능 문제가 있어요. SQLite 는 이 쿼리를 충족하기 위해 사용자 쿼리와 일치하는 모든 행에 대해 "title" 컬럼 값과 FTS 모듈의 matchinfo 데이터를 결과를 정렬하고 제한하기 전에 검색해요. SQLite 의 가상 테이블 인터페이스가 작동하는 방식 때문에 "title" 컬럼 값을 검색하려면 디스크에서 전체 행(꽤 클 수 있는 "content" 필드 포함)을 로드해야 해요. 이는 사용자 쿼리가 수천 개의 문서와 일치하면, 절대 어떤 목적으로도 사용되지 않을 것임에도 불구하고 수 메가바이트의 "title" 과 "content" 데이터가 디스크에서 메모리로 로드될 수 있다는 뜻이에요.
다음 예 블록의 SQL 쿼리는 이 문제에 대한 하나의 해결책이에요. SQLite 에서 조인에 사용되는 하위 쿼리가 LIMIT 절을 포함하면, 하위 쿼리의 결과가 메인 쿼리가 실행되기 전에 임시 테이블에 계산되고 저장돼요. 이는 SQLite 가 사용자 쿼리와 일치하는 각 행에 대해 docid 와 matchinfo 데이터만 메모리에 로드하고, 가장 관련 있는 10 개 문서에 해당하는 docid 값을 결정한 다음, 그 10 개 문서에 대해서만 title 과 content 정보를 로드한다는 뜻이에요. matchinfo 와 docid 값 둘 다 전적으로 전체 텍스트 인덱스에서 얻어지기 때문에, 이는 데이터베이스에서 메모리로 로드되는 데이터를 극적으로 줄여줘요.
SELECT title FROM documents JOIN (
SELECT docid, rank(matchinfo(documents)) AS rank
FROM documents
WHERE documents MATCH <query>
ORDER BY rank DESC
LIMIT 10 OFFSET 0
) AS ranktable USING(docid)
ORDER BY ranktable.rank DESC
다음 SQL 블록은 FTS 를 사용해 검색 애플리케이션을 개발할 때 발생할 수 있는 두 가지 다른 문제에 대한 해결책으로 쿼리를 향상시켜요:
- snippet 함수는 위 쿼리와 함께 사용할 수 없어요. 바깥 쿼리가 "WHERE ... MATCH" 절을 포함하지 않기 때문에 snippet 함수를 함께 사용할 수 없어요. 한 가지 해결책은 하위 쿼리가 사용하는 WHERE 절을 바깥 쿼리에서 복제하는 것이에요. 이와 관련된 오버헤드는 보통 무시할 만해요.
- 문서의 관련성은 matchinfo 의 반환 값에서 사용할 수 있는 데이터 말고도 다른 것에 의존할 수 있어요. 예를 들어 데이터베이스의 각 문서에 내용과 무관한 요소(출처, 저자, 나이, 참조 수 등)에 기반한 정적 가중치가 할당될 수 있어요. 이러한 값은 애플리케이션이 별도 테이블에 저장할 수 있고, rank 함수가 접근할 수 있도록 하위 쿼리에서 documents 테이블과 조인할 수 있어요.
이 버전의 쿼리는 sqlite.org 문서 검색 애플리케이션이 사용하는 것과 매우 비슷해요.
-- This table stores the static weight assigned to each document in FTS table
-- "documents". For each row in the documents table there is a corresponding row
-- with the same docid value in this table.
CREATE TABLE documents_data(docid INTEGER PRIMARY KEY, weight);
-- This query is similar to the one in the block above, except that:
--
-- 1. It returns a "snippet" of text along with the document title for display. So
-- that the snippet function may be used, the "WHERE ... MATCH ..." clause from
-- the sub-query is duplicated in the outer query.
--
-- 2. The sub-query joins the documents table with the document_data table, so that
-- implementation of the rank function has access to the static weight assigned
-- to each document.
SELECT title, snippet(documents) FROM documents JOIN (
SELECT docid, rank(matchinfo(documents), documents_data.weight) AS rank
FROM documents JOIN documents_data USING(docid)
WHERE documents MATCH <query>
ORDER BY rank DESC
LIMIT 10 OFFSET 0
) AS ranktable USING(docid)
WHERE documents MATCH <query>
ORDER BY ranktable.rank DESC
위의 모든 예 쿼리는 가장 관련 있는 10 개의 쿼리 결과를 반환해요. OFFSET 과 LIMIT 절에 사용된 값을 수정하면 (예를 들어) 다음 10 개의 가장 관련 있는 결과를 반환하는 쿼리를 쉽게 만들 수 있어요. 이것은 검색 애플리케이션의 두 번째 및 이후 결과 페이지에 필요한 데이터를 얻는 데 사용될 수 있어요.
다음 블록은 C 로 구현된 matchinfo 데이터를 사용하는 예제 rank 함수를 포함해요. 단일 가중치 대신 각 문서의 각 컬럼에 가중치를 외부적으로 할당할 수 있게 해줘요. 다른 사용자 함수처럼 sqlite3_create_function 으로 SQLite 에 등록할 수 있어요.
** 보안 경고:** rank() 는 단지 일반 SQL 함수이므로 어떤 맥락의 어떤 SQL 쿼리의 일부로도 호출될 수 있어요. 이는 전달되는 첫 인자가 유효한 matchinfo blob 이 아닐 수 있다는 뜻이에요. 구현자는 버퍼 오버런이나 다른 잠재적 보안 문제를 일으키지 않고 이 경우를 처리하도록 주의해야 해요.
/*
** SQLite user defined function to use with matchinfo() to calculate the
** relevancy of an FTS match. The value returned is the relevancy score
** (a real value greater than or equal to zero). A larger value indicates
** a more relevant document.
**
** The overall relevancy returned is the sum of the relevancies of each
** column value in the FTS table. The relevancy of a column value is the
** sum of the following for each reportable phrase in the FTS query:
**
** (<hit count> / <global hit count>) * <column weight>
**
** where <hit count> is the number of instances of the phrase in the
** column value of the current row and <global hit count> is the number
** of instances of the phrase in the same column of all rows in the FTS
** table. The <column weight> is a weighting factor assigned to each
** column by the caller (see below).
**
** The first argument to this function must be the return value of the FTS
** matchinfo() function. Following this must be one argument for each column
** of the FTS table containing a numeric weight factor for the corresponding
** column. Example:
**
** CREATE VIRTUAL TABLE documents USING fts3(title, content)
**
** The following query returns the docids of documents that match the full-text
** query <query> sorted from most to least relevant. When calculating
** relevance, query term instances in the 'title' column are given twice the
** weighting of those in the 'content' column.
**
** SELECT docid FROM documents
** WHERE documents MATCH <query>
** ORDER BY rank(matchinfo(documents), 1.0, 0.5) DESC
*/
static void rankfunc(sqlite3_context *pCtx, int nVal, sqlite3_value **apVal){
int *aMatchinfo; /* Return value of matchinfo() */
int nMatchinfo; /* Number of elements in aMatchinfo[] */
int nCol = 0; /* Number of columns in the table */
int nPhrase = 0; /* Number of phrases in the query */
int iPhrase; /* Current phrase */
double score = 0.0; /* Value to return */
assert( sizeof(int)==4 );
/* Check that the number of arguments passed to this function is correct.
** If not, jump to wrong_number_args. Set aMatchinfo to point to the array
** of unsigned integer values returned by FTS function matchinfo. Set
** nPhrase to contain the number of reportable phrases in the users full-text
** query, and nCol to the number of columns in the table. Then check that the
** size of the matchinfo blob is as expected. Return an error if it is not.
*/
if( nVal<1 ) goto wrong_number_args;
aMatchinfo = (unsigned int *)sqlite3_value_blob(apVal[0]);
nMatchinfo = sqlite3_value_bytes(apVal[0]) / sizeof(int);
if( nMatchinfo>=2 ){
nPhrase = aMatchinfo[0];
nCol = aMatchinfo[1];
}
if( nMatchinfo!=(2+3*nCol*nPhrase) ){
sqlite3_result_error(pCtx,
"invalid matchinfo blob passed to function rank()", -1);
return;
}
if( nVal!=(1+nCol) ) goto wrong_number_args;
/* Iterate through each phrase in the users query. */
for(iPhrase=0; iPhrase<nPhrase; iPhrase++){
int iCol; /* Current column */
/* Now iterate through each column in the users query. For each column,
** increment the relevancy score by:
**
** (<hit count> / <global hit count>) * <column weight>
**
** aPhraseinfo[] points to the start of the data for phrase iPhrase. So
** the hit count and global hit counts for each column are found in
** aPhraseinfo[iCol*3] and aPhraseinfo[iCol*3+1], respectively.
*/
int *aPhraseinfo = &aMatchinfo[2 + iPhrase*nCol*3];
for(iCol=0; iCol<nCol; iCol++){
int nHitCount = aPhraseinfo[3*iCol];
int nGlobalHitCount = aPhraseinfo[3*iCol+1];
double weight = sqlite3_value_double(apVal[iCol+1]);
if( nHitCount>0 ){
score += ((double)nHitCount / (double)nGlobalHitCount) * weight;
}
}
}
sqlite3_result_double(pCtx, score);
return;
/* Jump here if the wrong number of arguments are passed to this function */
wrong_number_args:
sqlite3_result_error(pCtx, "wrong number of arguments to function rank()", -1);
}
더 알아보기 (Learn more)
- FTS5 — 차세대 전문 검색 확장
- 가상 테이블 — CREATE VIRTUAL TABLE
- fts3_tokenizer.h — 토크나이저 인터페이스
- PRAGMA integrity_check — 무결성 검사