learning-website-django1-9  Exercise 1: A Full-Text Index
==========================================================

Make every page searchable, per site, without a separate search server. Django 6.1.2
with SQLite FTS5. Page text is the stored fragment with its tags removed.

Two FTS5 tables are used, both keyed by the page's own id (rowid = Page.id):
  page_fts      unicode61 tokenizer with remove_diacritics 2: word search, so
                "szeretnek" finds "Szeretnék" and "brotchen" finds "Brötchen"
  page_fts_cjk  trigram tokenizer, only for pages that contain Japanese: Japanese has
                no spaces, so words cannot be found by a word tokenizer

Save as apps/search/text.py:

"""Turn a stored page body into plain text for the search index."""
import re
from html.parser import HTMLParser

# Japanese: hiragana, katakana, and the common kanji blocks
CJK = re.compile("[぀-ヿ㐀-䶿一-鿿豈-﫿]")


class _TextExtractor(HTMLParser):
    SKIP = {"script", "style"}                       # code and styling are not content
    BLOCKS = {"p", "div", "li", "tr", "td", "th", "br", "h1", "h2", "h3", "h4", "section", "details", "summary"}

    def __init__(self):
        super().__init__(convert_charrefs=True)      # & becomes &, é becomes the letter
        self.parts, self._skip = [], 0

    def handle_starttag(self, tag, attrs):
        if tag in self.SKIP:
            self._skip += 1
        elif tag in self.BLOCKS:
            self.parts.append(" ")

    def handle_endtag(self, tag):
        if tag in self.SKIP and self._skip:
            self._skip -= 1
        elif tag in self.BLOCKS:
            self.parts.append(" ")

    def handle_data(self, data):
        if not self._skip:
            self.parts.append(data)


def html_to_text(fragment):
    parser = _TextExtractor()
    parser.feed(fragment)
    parser.close()
    return re.sub(r"\s+", " ", "".join(parser.parts)).strip()


def has_cjk(text):
    return bool(CJK.search(text))

Save as apps/search/migrations/0001_search_tables.py:

"""Two SQLite FTS5 tables. Django has no model for them, so they are created with SQL.

page_fts      word search for every page. unicode61 splits text into words and, with remove_diacritics 2,
              treats "kave" and "kavé" as the same word (Hungarian, German, French).
page_fts_cjk  substring search for pages that contain Japanese. Japanese has no spaces between words, so
              unicode61 sees a whole run of kana as ONE word; the trigram tokenizer indexes every 3-character
              piece instead (so a search needs at least 3 characters).

The row id of each FTS row is the Page's id, so a row can be found, replaced or deleted quickly."""
from django.db import migrations

CREATE = [
    "CREATE VIRTUAL TABLE page_fts USING fts5(title, body, site UNINDEXED, tokenize='unicode61 remove_diacritics 2')",
    "CREATE VIRTUAL TABLE page_fts_cjk USING fts5(title, body, site UNINDEXED, tokenize='trigram')",
]
DROP = ["DROP TABLE page_fts", "DROP TABLE page_fts_cjk"]


def forwards(apps, schema_editor):
    if schema_editor.connection.vendor != "sqlite":
        return                                       # PostgreSQL uses a GIN index instead (see the chapter)
    for sql in CREATE:
        schema_editor.execute(sql)


def backwards(apps, schema_editor):
    if schema_editor.connection.vendor != "sqlite":
        return
    for sql in DROP:
        schema_editor.execute(sql)


class Migration(migrations.Migration):
    dependencies = [("content", "0003_page_summary")]
    operations = [migrations.RunPython(forwards, backwards)]

Save as apps/search/indexing.py:

"""Keep the search tables in step with the pages."""
from django.db import connection

from .text import has_cjk


def _marks(count):
    return ",".join(["%s"] * count)


def index_pages(items):
    """(Re)index pages. items: (page, text) pairs, where text is the page's plain text. A page needs .pk,
    .site and .title, so call this AFTER the pages have been saved."""
    items = list(items)
    if connection.vendor != "sqlite" or not items:
        return
    with connection.cursor() as cursor:
        ids = [page.pk for page, _ in items]
        for start in range(0, len(ids), 500):
            chunk = ids[start:start + 500]
            cursor.execute(f"DELETE FROM page_fts WHERE rowid IN ({_marks(len(chunk))})", chunk)
            cursor.execute(f"DELETE FROM page_fts_cjk WHERE rowid IN ({_marks(len(chunk))})", chunk)
        cursor.executemany("INSERT INTO page_fts(rowid, title, body, site) VALUES (%s, %s, %s, %s)",
                           [(page.pk, page.title, text, page.site) for page, text in items])
        cursor.executemany("INSERT INTO page_fts_cjk(rowid, title, body, site) VALUES (%s, %s, %s, %s)",
                           [(page.pk, page.title, text, page.site) for page, text in items if has_cjk(text)])


def remove_pages(ids):
    ids = list(ids)
    if connection.vendor != "sqlite" or not ids:
        return
    with connection.cursor() as cursor:
        for start in range(0, len(ids), 500):
            chunk = ids[start:start + 500]
            cursor.execute(f"DELETE FROM page_fts WHERE rowid IN ({_marks(len(chunk))})", chunk)
            cursor.execute(f"DELETE FROM page_fts_cjk WHERE rowid IN ({_marks(len(chunk))})", chunk)

The importer calls index_pages() after saving the new and changed pages and
remove_pages() before pruning. PIPELINE_VERSION was raised to "3", so the first import
after this change re-imports every page once (that is what builds the index).

Run on the real content:

  python manage.py migrate
  python manage.py import_content --prune

A bug found on the first real import
------------------------------------
The first run failed with "TypeError: not all arguments converted during string
formatting" from Django's query logging. I had written "?" placeholders (SQLite's
own) in raw SQL sent through Django's cursor, which expects %s. The tests passed
because query logging is off in tests; the development server logs queries, so the
real import failed. Fix: use %s everywhere. Only running the real import with the
real settings found it.

Result of the real import (after the fix):

  created 2, updated 4406, unchanged 0, errors 0  (38.0s)

  content_page rows  4408
  page_fts rows      4408      (every page)
  page_fts_cjk rows   421      (only pages containing Japanese)
  summary filled     4408

Size: the database was about 99 MB before; after a VACUUM it is 162 MB, so the two
indexes add roughly 63 MB. (Before the VACUUM the file was 198 MB because deleted
rows leave free pages inside the file.)

WHY THIS WORKS AS AN ANSWER
---------------------------
A search index is a second copy of the text, so keeping it in step is the real job:
it is rebuilt when a page changes and removed when a page is pruned, in the same
import, and the counts above show the two stayed equal.