Full-text document indexing - part Ⅰ

Introduction

This article shows a simple way of doing full-text indexing and search of Office Open XML and OpenDocument¹ documents using SQLite.

1. prepare the database
2. insert documents (indexing)
3. query using full-text search

Preparation

Create a virtual table using SQLite's full-text search feature:

CREATE VIRTUAL TABLE documents
USING FTS5 (name, document, size)

Indexing

Because both the Office Open XML and OpenDocument formats are just zip archives containg a bunch of XML this is easy.

Add a single document

INSERT INTO documents (name, document, size)
    SELECT :name, data, LENGTH(readfile(:name))
    FROM zipfile(:name)
    WHERE name = 'content.xml'

Note that this uses parameterized queries, so adapt it to your needs.

Search

SELECT rank, name, size
FROM documents
WHERE documents MATCH :terms
ORDER BY rank

This query could result in something like this:

Next up?

In Full-text document indexing - part Ⅱ I'll dive into more details.

Notes