SQLite Volltextindizierung

Einführung

In diesem Lab lernen Sie, wie Sie die FTS5-Erweiterung von SQLite für die effiziente Volltextsuche verwenden. Sie erstellen FTS5-Tabellen, fügen Daten ein, führen Suchen durch und optimieren Abfragen mithilfe von MATCH-Klauseln. Dieses Lab bietet eine praktische Einführung in die Volltextindizierung in SQLite.

Erstellen einer SQLite-Datenbank und Aktivieren von FTS5

In diesem Schritt erstellen Sie eine SQLite-Datenbank und aktivieren die FTS5-Erweiterung. FTS5 ist ein Modul, das Volltextsuchfunktionen (full-text search capabilities) bereitstellt.

Öffnen Sie zunächst Ihr Terminal in der LabEx VM. Ihr Standardpfad ist /home/labex/project.

Erstellen einer SQLite-Datenbank:

Erstellen Sie eine Datenbankdatei namens books.db und verbinden Sie sich mit ihr mithilfe des Befehls sqlite3:
```
sqlite3 books.db
```
Dieser Befehl startet die SQLite-Shell, in der Sie SQL-Befehle ausführen können.
Aktivieren der FTS5-Erweiterung:

Bevor Sie FTS5 verwenden können, müssen Sie es aktivieren. Führen Sie den folgenden SQL-Befehl aus:
```
CREATE VIRTUAL TABLE book_search USING fts5();
```
Wenn Sie einen Fehler wie Error: no such module: fts5 sehen, bedeutet dies, dass die FTS5-Erweiterung nicht verfügbar ist. In den meisten modernen SQLite-Versionen ist FTS5 standardmäßig enthalten. Wenn dieser Fehler auftritt, stellen Sie sicher, dass Ihre SQLite-Version auf dem neuesten Stand ist. Wenn der Befehl ohne Fehler ausgeführt wird, ist die FTS5-Erweiterung aktiviert.

Dieser Befehl erstellt eine virtuelle Tabelle (virtual table) namens book_search mithilfe des fts5-Moduls. Virtuelle Tabellen sind eine Funktion von SQLite, mit der Sie die Datenbank um benutzerdefinierte Funktionen erweitern können.
Definieren des Tabellenschemas:

Definieren wir nun die Spalten für unsere book_search-Tabelle. Wir werden Spalten für title (Titel), author (Autor) und content (Inhalt) hinzufügen. Führen Sie den folgenden SQL-Befehl aus:
```
CREATE VIRTUAL TABLE book_search USING fts5(title, author, content);
```
Dieser Befehl erstellt die Tabelle book_search mit den angegebenen Spalten. Die Spalten title, author und content werden von FTS5 indiziert, sodass Sie Volltextsuchen (full-text searches) in ihnen durchführen können.
Konfigurieren des Tokenizers (Optional):

Sie können anpassen, wie FTS5 Text tokenisiert, indem Sie einen Tokenizer angeben. Der unicode61-Tokenizer bietet eine gute Unicode-Unterstützung. Um ihn zu verwenden und Diakritika (Akzente) zu entfernen, erstellen Sie die Tabelle mit dem folgenden Befehl neu:
```
DROP TABLE IF EXISTS book_search;
CREATE VIRTUAL TABLE book_search USING fts5(title, author, content, tokenize="unicode61 remove_diacritics 1");
```
Dieser Befehl löscht zuerst die vorhandene book_search-Tabelle (falls sie existiert) und erstellt sie dann mit dem unicode61-Tokenizer neu. Die Option remove_diacritics 1 weist FTS5 an, Diakritika während der Indizierung aus dem Text zu entfernen.

Daten in die FTS5-Tabelle einfügen

In diesem Schritt fügen Sie Daten in die book_search-Tabelle ein. Diese Daten werden in späteren Schritten für die Volltextsuche (full-text searching) verwendet.

Buchdaten einfügen:

Führen Sie die folgenden SQL-Befehle aus, um Beispielbuchdaten in die book_search-Tabelle einzufügen:

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.'
);

Diese Befehle fügen fünf Zeilen in die book_search-Tabelle ein, die jeweils ein Buch darstellen. Die Werte für title (Titel), author (Autor) und content (Inhalt) werden für jedes Buch angegeben. Beachten Sie das Escape-Zeichen für das einfache Anführungszeichen in 'The Hitchhiker''s Guide to the Galaxy'. Einfache Anführungszeichen innerhalb einer Zeichenkette müssen durch Verdopplung maskiert werden.

Dateninsertion überprüfen:

Um zu bestätigen, dass die Daten korrekt hinzugefügt wurden, führen Sie den folgenden Befehl aus:

SELECT * FROM book_search;

Dieser Befehl ruft alle Zeilen und Spalten aus der book_search-Tabelle ab. Sie sollten die Daten sehen, die Sie gerade eingefügt haben.

Erwartete Ausgabe:

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.

Grundlegende Volltextsuche durchführen

In diesem Schritt führen Sie einfache Volltextsuchen mithilfe des MATCH-Operators durch.

Suche nach einem einzelnen Begriff:

Um Bücher zu finden, die das Wort "fantasy" enthalten, führen Sie den folgenden SQL-Befehl aus:
```
SELECT * FROM book_search WHERE book_search MATCH 'fantasy';
```
Dieser Befehl durchsucht alle Spalten der book_search-Tabelle nach dem Begriff "fantasy". Der MATCH-Operator wird verwendet, um die Volltextsuche durchzuführen.

Erwartete Ausgabe:
```
The Lord of the Rings|J.R.R. Tolkien|A fantasy epic about hobbits, elves, and the battle against Sauron.
```
Suche nach mehreren Begriffen:

Sie können nach mehreren Begriffen suchen, indem Sie diese durch Leerzeichen trennen. Um Bücher zu finden, die sowohl "love" als auch "society" enthalten, führen Sie den folgenden SQL-Befehl aus:
```
SELECT * FROM book_search WHERE book_search MATCH 'love society';
```
Dieser Befehl sucht nach Zeilen, die sowohl "love" als auch "society" in einer der indizierten Spalten enthalten.

Erwartete Ausgabe:
```
Pride and Prejudice|Jane Austen|A classic novel about love, class, and society in 19th-century England.
```
Suche innerhalb einer bestimmten Spalte:

Um innerhalb einer bestimmten Spalte zu suchen, geben Sie den Spaltennamen vor dem MATCH-Operator an. Um beispielsweise Bücher mit "Orwell" in der Spalte author (Autor) zu finden, führen Sie den folgenden SQL-Befehl aus:
```
SELECT * FROM book_search WHERE author MATCH 'Orwell';
```
Dieser Befehl durchsucht nur die Spalte author nach dem Begriff "Orwell".

Erwartete Ausgabe:
```
1984|George Orwell|A dystopian novel about totalitarianism and surveillance.
```

Erweiterte MATCH-Funktionen verwenden

In diesem Schritt werden Sie erweiterte Funktionen des MATCH-Operators untersuchen, einschließlich Präfixsuchen (prefix searches) und boolescher Operatoren (boolean operators).

Präfixsuche:

Verwenden Sie das *-Wildcard-Zeichen, um eine Präfixsuche durchzuführen. Um Bücher zu finden, die Wörter enthalten, die mit "soci" beginnen, führen Sie den folgenden SQL-Befehl aus:
```
SELECT * FROM book_search WHERE book_search MATCH 'soci*';
```
Dieser Befehl sucht nach Begriffen, die mit "soci" beginnen, wie z. B. "society".

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

Boolesche Operatoren:

FTS5 unterstützt boolesche Operatoren wie AND, OR und NOT, um komplexere Suchabfragen zu erstellen.

AND: Findet Dokumente, die beide Begriffe enthalten. Das Standardverhalten, wenn Begriffe durch Leerzeichen getrennt sind, ist AND.
```
SELECT * FROM book_search WHERE book_search MATCH 'love AND society';
```
Dies ist äquivalent zu SELECT * FROM book_search WHERE book_search MATCH 'love society';

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

OR: Findet Dokumente, die einen der beiden Begriffe enthalten.

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

Dieser Befehl findet Bücher, die entweder "love" oder "injustice" (oder beides) enthalten.

Erwartete Ausgabe:

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: Schließt Dokumente aus, die einen bestimmten Begriff enthalten.

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

Dieser Befehl findet Bücher, die das Wort "fantasy" nicht enthalten.

Erwartete Ausgabe:

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.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie die FTS5-Erweiterung von SQLite für die Volltextsuche (full-text searching) verwenden. Sie haben eine FTS5-Tabelle erstellt, Daten eingefügt, grundlegende Suchen mit dem MATCH-Operator durchgeführt und erweiterte Funktionen wie Präfixsuchen (prefix searches) und boolesche Operatoren (boolean operators) erkundet. Diese Fähigkeiten bilden eine Grundlage für den Aufbau leistungsstarker Suchfunktionen in Ihren SQLite-Anwendungen.