sqlite3에서 fts3 사용할 때 not operator 사용법

정말 많이 삽질했다..ㅜ

sqlite3에서 좋은 점은 full-text searching 알고리즘을 제공한다는 점이다. 물론 모든 테이블에 적용 가능한 것은 아니고, virtual table을 memory에 fts3을 적용하여 만들고, 여기서 검색을 해야 한다.

근데 문제가 있는데... python을 설치하면서 따라온 sqlite3의 query syntax가 하필이면(!) "Standard Query Syntax"라는 점이다. 이게 표준이라고 이름이 붙었지만 MATCH 문법을 쓸 때의 operator에 제한이 있어 사용하기가 불편하다. 자세히 살펴보지 않았지만 sqlite3를 컴파일해서 설치할 때 옵션을 바꾸어주면 정말 편리한 "Enhanced Query Syntax (EQS)"를 사용할 수 있다고 한다. 

두 개의 Syntax 차이점 중 이번에 삽질하게 된 것은 바로 not operator이다. 

EQS의 경우 NOT operator를 따로 제공해준다. OR과 AND operator도 제공해주어 직관적으로 MATCH 문법의 쿼리를 짤 수 있다. 예를 들어, a, b, c 중 하나 이상을 포함하면서도 d, e 중 하나라도 들어있으면 안되는 상황을 생각해보자. 이 경우에 쿼리는 다음과 같이 만들어진다. 물론 EQS에서만... 

column_name MATCH '(a OR b OR C) NOT (d OR e)';

게다가 a, b, c..., e 와 같은 키워드들이 phrase여도 문제가 없다. 즉, "sony vaio" 처럼 주어진 구문의 매칭여부까지 포함한다. 

그런데 SQS의 경우 상황이 조금 다르다. 우선 NOT operator가 없다는게 가장 큰 함정. 대신 - 기호의 operator를 제공해준다. 이 놈의 역할은 NOT operator와 비슷한데 다른점이라면 phrase에서 적용되지 않는다는 점이다. -.-;;; 물론 AND operator 역시 없으며, 그냥 띄어쓰기가 이를 대신한다. 

만약 위와 같은 예제에서 매칭되는 키워드들이 모두 진짜 word라면, 

column_name MATCH '"a" OR "b" OR "c" (-d -e)';

뭐 이런식으로 써야 할 것 같다. (sqlite expert 프로그램에서는 안되네요..;;;) 그런데 문제는 d와 e가 word가 아닌 phrase일 경우... 이런 식의 쿼리문은 에러를 낸다. Stackoverflow에서 봤던 것 같은데, 이를 위해서는 결국 내부쿼리를 이용해서 이런 record들을 빼주는 식으로 처리해야 한다.

SELECT * from table WHERE column_name MATCH '"a" OR "b" OR "c"' and id not IN (SELECT id from table WHERE column_name MATCH '"d" OR "e"');

여기서 a, b, c, d, e 모두 phrase라고 생각해야 한다. 그냥 word였으면 위의 방법대로 되니까...ㅎ

이외에도 SQS와 EQS는 연산자의 순서도 다르다. 그래서 쿼리문을 짜놓고 헷갈리면... 엉뚱한 결과가 나올 수 있다. 자세한 것은 아래 링크에서... (영문)

http://www.sqlite.org/fts3.html#section_3_1