SQLite 전체 텍스트 인덱싱

소개

이 랩에서는 효율적인 전체 텍스트 검색을 위해 SQLite 의 FTS5 확장 기능을 사용하는 방법을 배우게 됩니다. FTS5 테이블을 생성하고, 데이터를 삽입하고, 검색을 수행하며, MATCH 절을 사용하여 쿼리를 최적화할 것입니다. 이 랩은 SQLite 에서 전체 텍스트 인덱싱에 대한 실습 소개를 제공합니다.

SQLite 데이터베이스 생성 및 FTS5 활성화

이 단계에서는 SQLite 데이터베이스를 생성하고 FTS5 확장 기능을 활성화합니다. FTS5 는 전체 텍스트 검색 기능을 제공하는 모듈입니다.

먼저, LabEx VM 에서 터미널을 엽니다. 기본 경로는 /home/labex/project입니다.

1. SQLite 데이터베이스 생성:

   books.db라는 데이터베이스 파일을 생성하고 sqlite3 명령을 사용하여 연결합니다.

   sqlite3 books.db

   이 명령은 SQL 명령을 실행할 수 있는 SQLite 셸을 시작합니다.

2. FTS5 확장 기능 활성화:

   FTS5 를 사용하기 전에 활성화해야 합니다. 다음 SQL 명령을 실행합니다.

   CREATE VIRTUAL TABLE book_search USING fts5();

   Error: no such module: fts5와 같은 오류가 표시되면 FTS5 확장 기능을 사용할 수 없음을 의미합니다. 대부분의 최신 SQLite 버전에서는 FTS5 가 기본적으로 포함되어 있습니다. 이 오류가 발생하면 SQLite 버전이 최신인지 확인하십시오. 명령이 오류 없이 실행되면 FTS5 확장 기능이 활성화된 것입니다.

   이 명령은 fts5 모듈을 사용하여 book_search라는 가상 테이블을 생성합니다. 가상 테이블은 SQLite 의 기능으로, 사용자 지정 기능으로 데이터베이스를 확장할 수 있습니다.

3. 테이블 스키마 정의:

   이제 book_search 테이블의 열을 정의해 보겠습니다. title, author, content 열을 포함합니다. 다음 SQL 명령을 실행합니다.

   CREATE VIRTUAL TABLE book_search USING fts5(title, author, content);

   이 명령은 지정된 열을 사용하여 book_search 테이블을 생성합니다. title, author, content 열은 FTS5 에 의해 인덱싱되어 전체 텍스트 검색을 수행할 수 있습니다.

4. 토크나이저 구성 (선택 사항):

   토크나이저를 지정하여 FTS5 가 텍스트를 토큰화하는 방식을 사용자 정의할 수 있습니다. unicode61 토크나이저는 훌륭한 유니코드 지원을 제공합니다. 이를 사용하고 발음 구별 기호 (악센트) 를 제거하려면 다음 명령으로 테이블을 다시 생성합니다.

   DROP TABLE IF EXISTS book_search;
   CREATE VIRTUAL TABLE book_search USING fts5(title, author, content, tokenize="unicode61 remove_diacritics 1");

   이 명령은 먼저 기존 book_search 테이블을 삭제하고 (있는 경우) unicode61 토크나이저로 다시 생성합니다. remove_diacritics 1 옵션은 FTS5 에 인덱싱 중에 텍스트에서 발음 구별 기호를 제거하도록 지시합니다.

FTS5 테이블에 데이터 삽입

이 단계에서는 book_search 테이블에 데이터를 삽입합니다. 이 데이터는 이후 단계에서 전체 텍스트 검색에 사용됩니다.

1. 도서 데이터 삽입:

   다음 SQL 명령을 실행하여 샘플 도서 데이터를 book_search 테이블에 삽입합니다.

   INSERT INTO book_search (title, author, content) VALUES (
       'The Lord of the Rings',
       'J.R.R. Tolkien',
       'A fantasy epic about hobbits, elves, and the battle against Sauron.'
   );
   
   INSERT INTO book_search (title, author, content) VALUES (
       'Pride and Prejudice',
       'Jane Austen',
       'A classic novel about love, class, and society in 19th-century England.'
   );
   
   INSERT INTO book_search (title, author, content) VALUES (
       'The Hitchhiker''s Guide to the Galaxy',
       'Douglas Adams',
       'A comedic science fiction series following the misadventures of Arthur Dent.'
   );
   
   INSERT INTO book_search (title, author, content) VALUES (
       'To Kill a Mockingbird',
       'Harper Lee',
       'A powerful story about racial injustice in the American South.'
   );
   
   INSERT INTO book_search (title, author, content) VALUES (
       '1984',
       'George Orwell',
       'A dystopian novel about totalitarianism and surveillance.'
   );

   이 명령은 book_search 테이블에 5 개의 행을 삽입하며, 각 행은 책을 나타냅니다. 각 책에 대해 title, author, content 값이 제공됩니다. 'The Hitchhiker''s Guide to the Galaxy'에서 이스케이프된 작은 따옴표에 유의하십시오. 문자열 내의 작은 따옴표는 두 번 사용하여 이스케이프해야 합니다.

2. 데이터 삽입 확인:

   데이터가 올바르게 추가되었는지 확인하려면 다음 명령을 실행합니다.

   SELECT * FROM book_search;

   이 명령은 book_search 테이블에서 모든 행과 열을 검색합니다. 방금 삽입한 데이터를 볼 수 있습니다.

   예상 출력:

   The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.
   Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
   The Hitchhiker's Guide to the Galaxy|Douglas Adams|A comedic science fiction series following the misadventures of Arthur Dent.
   To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.
   1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

기본적인 전체 텍스트 검색 수행

이 단계에서는 MATCH 연산자를 사용하여 기본 전체 텍스트 검색을 수행합니다.

1. 단일 용어 검색:

   "fantasy"라는 단어가 포함된 책을 찾으려면 다음 SQL 명령을 실행합니다.

   SELECT * FROM book_search WHERE book_search MATCH 'fantasy';

   이 명령은 book_search 테이블의 모든 열에서 "fantasy"라는 용어를 검색합니다. MATCH 연산자는 전체 텍스트 검색을 수행하는 데 사용됩니다.

   예상 출력:

   The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.

2. 여러 용어 검색:

   공백으로 구분하여 여러 용어를 검색할 수 있습니다. "love"와 "society"가 모두 포함된 책을 찾으려면 다음 SQL 명령을 실행합니다.

   SELECT * FROM book_search WHERE book_search MATCH 'love society';

   이 명령은 인덱싱된 열 중 하나에 "love"와 "society"가 모두 포함된 행을 검색합니다.

   예상 출력:

   Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.

3. 특정 열 내에서 검색:

   특정 열 내에서 검색하려면 MATCH 연산자 앞에 열 이름을 지정합니다. 예를 들어, author 열에 "Orwell"이 있는 책을 찾으려면 다음 SQL 명령을 실행합니다.

   SELECT * FROM book_search WHERE author MATCH 'Orwell';

   이 명령은 "Orwell"이라는 용어에 대해 author 열만 검색합니다.

   예상 출력:

   1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

고급 MATCH 기능 사용

이 단계에서는 접두사 검색 및 부울 연산자를 포함하여 MATCH 연산자의 고급 기능을 살펴봅니다.

1. 접두사 검색:

   * 와일드카드 (wildcard) 를 사용하여 접두사 검색을 수행합니다. "soci"로 시작하는 단어가 포함된 책을 찾으려면 다음 SQL 명령을 실행합니다.

   SELECT * FROM book_search WHERE book_search MATCH 'soci*';

   이 명령은 "soci"로 시작하는 용어, 예를 들어 "society"를 검색합니다.

   예상 출력:

   Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.

2. 부울 연산자:

   FTS5 는 AND, OR, NOT과 같은 부울 연산자를 지원하여 보다 복잡한 검색 쿼리를 만들 수 있습니다.

   • AND: 두 용어를 모두 포함하는 문서를 찾습니다. 용어가 공백으로 구분될 때의 기본 동작은 AND입니다.

     SELECT * FROM book_search WHERE book_search MATCH 'love AND society';

     이는 SELECT * FROM book_search WHERE book_search MATCH 'love society';와 동일합니다.

     예상 출력:

     Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.

   • OR: 두 용어 중 하나를 포함하는 문서를 찾습니다.

     SELECT * FROM book_search WHERE book_search MATCH 'love OR injustice';

     이 명령은 "love" 또는 "injustice"(또는 둘 다) 를 포함하는 책을 찾습니다.

     예상 출력:

     Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
     To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.

   • NOT: 특정 용어를 포함하는 문서를 제외합니다.

     SELECT * FROM book_search WHERE book_search MATCH 'NOT fantasy';

     이 명령은 "fantasy"라는 단어를 포함하지 않는 책을 찾습니다.

     예상 출력:

     Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
     The Hitchhiker's Guide to the Galaxy|Douglas Adams|A comedic science fiction series following the misadventures of Arthur Dent.
     To Kill a Mockingbird|Harper Lee|A powerful story about racial injustice in the American South.
     1984|George Orwell|A dystopian novel about totalitarianism and surveillance.

요약

이 랩에서는 전체 텍스트 검색을 위해 SQLite 의 FTS5 확장 기능을 사용하는 방법을 배웠습니다. FTS5 테이블을 생성하고, 데이터를 삽입하고, MATCH 연산자를 사용하여 기본 검색을 수행했으며, 접두사 검색 및 부울 연산자와 같은 고급 기능을 탐색했습니다. 이러한 기술은 SQLite 애플리케이션에 강력한 검색 기능을 구축하기 위한 기반을 제공합니다.