SQLite - PRAGMA: Ein umfassender Leitfaden für Anfänger

Hallo da draußen, ambitionierte Datenbankenthusiasten! Heute machen wir uns auf eine aufregende Reise in die Welt der SQLite PRAGMAs. Keine Sorge, wenn Sie noch nie von PRAGMAs gehört haben - bis zum Ende dieses Tutorials werden Sie sie wie ein Profi verwenden!

Was ist eine PRAGMA?

Bevor wir eintauchen, beginnen wir mit den Grundlagen. In SQLite ist eine PRAGMA ein spezieller Befehl, der es uns ermöglicht, das Verhalten der SQLite-Bibliothek zu ändern oder deren internen Zustand abzufragen. Stellen Sie sich PRAGMAs als Kontrollpanel für Ihre SQLite-Datenbank vor - sie erlauben es Ihnen, Einstellungen anzupassen und Informationen darüber zu erhalten, wie Ihre Datenbank läuft.

Nun wollen wir einige der am häufigsten verwendeten PRAGMAs in SQLite erkunden.

auto_vacuum Pragma

Die auto_vacuum PRAGMA ist wie ein aufgeräumter Mitbewohner für Ihre Datenbank. Sie reinigt und organisiert die Datenbankdatei automatisch, wenn Sie Daten löschen.

PRAGMA auto_vacuum = FULL;

Dieser Befehl setzt den auto_vacuum-Modus auf FULL, was bedeutet, dass SQLite hart daran arbeitet, Ihre Datenbankdatei so klein wie möglich zu halten.

cache_size Pragma

Die cache_size PRAGMA ist wie das Geben Ihrer Datenbank ein größeres Gehirn. Sie bestimmt, wie viele Seiten SQLite im Speicher hält, für einen schnelleren Zugriff.

PRAGMA cache_size = 10000;

Dies setzt die Cachesize auf 10.000 Seiten. Mehr Cache bedeutet schnellere Leistung, verbraucht aber auch mehr Speicher.

case_sensitive_like Pragma

Diese PRAGMA bestimmt, ob der LIKE-Operator case-sensitive ist oder nicht. Es ist, als ob Sie Ihrer Datenbank beibringen, mit oder ohne Brille zu lesen.

PRAGMA case_sensitive_like = TRUE;

Mit dieser Einstellung wäre 'Apple' LIKE 'app%' falsch, während 'Apple' LIKE 'App%' wahr wäre.

count_changes Pragma

Die count_changes PRAGMA ist wie ein Punktesprecher für Ihre Datenbankoperationen. Sie tells Sie, wie viele Zeilen von einer INSERT-, UPDATE- oder DELETE-Anweisung geändert wurden.

PRAGMA count_changes = ON;

Nachdem Sie dies eingestellt haben, wird SQLite Ihnen beispielsweise mitteilen, wie viele Zeilen von einer UPDATE-Anweisung betroffen waren.

database_list Pragma

Diese PRAGMA ist wie ein Verzeichnis für Ihre Datenbank. Sie lists alle angeschlossenen Datenbanken, einschließlich der Hauptdatenbank und aller anderen, die Sie angeschlossen haben.

PRAGMA database_list;

Dies zeigt Ihnen eine Liste aller angeschlossenen Datenbanken, ihre Dateipfade und ihre internen Namen an.

encoding Pragma

Die encoding PRAGMA setzt oder abfragt die Textkodierung, die von der Datenbank verwendet wird. Es ist, als ob Sie auswählen, welche Sprache Ihre Datenbank spricht.

PRAGMA encoding = "UTF-8";

Dies setzt die Datenbankkodierung auf UTF-8, was eine häufige Wahl für die Unterstützung mehrerer Sprachen ist.

freelist_count Pragma

Diese PRAGMA tells Sie, wie viele ungenutzte Seiten in der Datenbankdatei sind. Es ist, als ob Sie überprüfen, wie viel freien Speicher Sie in Ihrem Lager haben.

PRAGMA freelist_count;

Dies gibt die Anzahl der ungenutzten Seiten in der Datenbankdatei zurück.

index_info und index_list Pragmas

Diese PRAGMAs sind wie eine Bibliothekarin für Ihre Datenbankindizes. Sie bieten Informationen über die Struktur und Inhalte Ihrer Indizes.

PRAGMA index_list(table_name);
PRAGMA index_info(index_name);

Der erste Befehl lists alle Indizes auf einer Tabelle, während der zweite detaillierte Informationen über einen bestimmten Index gibt.

journal_mode Pragma

Die journal_mode PRAGMA kontrolliert, wie SQLite Transaktionen verfolgt. Es ist, als ob Sie zwischen verschiedenen Arten von Backup-Systemen wählen.

PRAGMA journal_mode = WAL;

Dies setzt den Journal-Modus auf Write-Ahead Logging (WAL), was in vielen Fällen die Leistung verbessern kann.

max_page_count und page_count Pragmas

Diese PRAGMAs befassen sich mit der Anzahl der Seiten in Ihrer Datenbankdatei. Sie sind wie das Einstellen und Überprüfen der maximalen Anzahl von Seiten in einem Buch.

PRAGMA max_page_count = 1000000;
PRAGMA page_count;

Der erste Befehl setzt die maximale Anzahl von Seiten, während der zweite tells Sie, wie viele Seiten derzeit in Gebrauch sind.

page_size Pragma

Diese PRAGMA setzt oder abfragt die Seitengröße der Datenbank. Es ist, als ob Sie entscheiden, wie groß jede Seite in Ihrem Buch sein sollte.

PRAGMA page_size = 4096;

Dies setzt die Seitengröße auf 4096 Bytes, was eine häufige Wahl ist.

parser_trace und sql_trace Pragmas

Diese PRAGMAs sind wie das Einschalten des Debug-Modus für Ihre Datenbank. Sie bieten detaillierte Informationen darüber, wie SQLite Ihre Befehle verarbeitet.

PRAGMA parser_trace = ON;
PRAGMA sql_trace = ON;

Diese können sehr hilfreich sein, wenn Sie versucht sind, zu verstehen, warum eine bestimmte Abfrage nicht wie erwartet funktioniert.

recursive_triggers Pragma

Diese PRAGMA kontrolliert, ob Trigger rekursiv andere Trigger auslösen können. Es ist, als ob Sie Domino-Effekte in Ihrer Datenbank erlauben.

PRAGMA recursive_triggers = ON;

Mit dieser Einstellung kann ein Trigger einen anderen Trigger auslösen, der wiederum einen anderen Trigger auslösen kann, und so weiter.

schema_version und user_version Pragmas

Diese PRAGMAs erlauben es Ihnen, Versionsnummern für Ihr Datenbank-Schema zu setzen und abzufragen. Sie sind wie Versionsmarken für Ihre Datenbankstruktur.

PRAGMA schema_version;
PRAGMA user_version = 1;

Der erste Befehl abfragt die aktuelle Schemaversion, während der zweite die Benutzer-Version auf 1 setzt.

secure_delete Pragma

Diese PRAGMA kontrolliert, wie gründlich SQLite gelöschte Daten überschreibt. Es ist, als ob Sie zwischen Schreddern Ihrer Dokumente und sie einfach in den Müll werfen wählen.

PRAGMA secure_delete = ON;

Mit dieser Einstellung überschreibt SQLite gelöschte Daten mit Nullen, was es schwieriger macht, sie wiederherzustellen.

synchronous Pragma

Die synchronous PRAGMA kontrolliert, wie sorgfältig SQLite Daten auf die Festplatte schreibt. Es ist eine Balance zwischen Sicherheit und Geschwindigkeit.

PRAGMA synchronous = NORMAL;

Dies setzt ein mittleres Sicherheitsniveau, das zwischen voller Sicherheit (FULL) und maximaler Geschwindigkeit (OFF) abwägt.

temp_store und temp_store_directory Pragmas

Diese PRAGMAs kontrollieren, wo und wie SQLite temporäre Daten speichert. Es ist, als ob Sie entscheiden, wo Sie Ihr Scratch-Papier während der Arbeit ablegen.

PRAGMA temp_store = MEMORY;
PRAGMA temp_store_directory = '/path/to/directory';

Der erste Befehl tells SQLite, temporäre Daten im Speicher zu speichern, während der zweite ein Verzeichnis für temporäre Dateien festlegt.

writable_schema Pragma

Diese PRAGMA kontrolliert, ob Sie die sqlite_master-Tabelle direkt ändern können. Es ist, als ob Sie einen Master-Schlüssel für Ihre Datenbankstruktur hätten.

PRAGMA writable_schema = ON;

Seien Sie sehr vorsichtig mit diesem! Es ermöglicht Ihnen, die grundlegende Struktur Ihrer Datenbank zu ändern, was gefährlich sein kann, wenn es nicht korrekt durchgeführt wird.

Hier ist eine Tabelle, die alle diskutierten PRAGMAs zusammenfasst:

PRAGMA	Beschreibung
auto_vacuum	Steuert das automatische Reinigen der Datenbank
cache_size	Setzt die Anzahl der Seiten im Speicher
case_sensitive_like	Steuert die Case-Sensitivität des LIKE-Operators
count_changes	Meldet die Anzahl der von Operationen geänderten Zeilen
database_list	Lists alle angeschlossenen Datenbanken
encoding	Setzt oder abfragt die Textkodierung der Datenbank
freelist_count	Meldet die Anzahl der ungenutzten Seiten in der Datenbank
index_info	Bietet Informationen über einen bestimmten Index
index_list	Lists alle Indizes auf einer Tabelle
journal_mode	Steuert, wie Transaktionen verfolgt werden
max_page_count	Setzt die maximale Anzahl von Seiten in der Datenbank
page_count	Meldet die aktuelle Anzahl von Seiten in der Datenbank
page_size	Setzt oder abfragt die Seitengröße der Datenbank
parser_trace	Schaltet detaillierte Parsing-Informationen ein
recursive_triggers	Ermöglicht das rekursive Auslösen von Triggern
schema_version	Setzt oder abfragt die Schemaversion
secure_delete	Steuert, wie gründlich gelöschte Daten überschrieben werden
sql_trace	Schaltet detaillierte SQL-Ausführungsinformationen ein
synchronous	Steuert, wie sorgfältig Daten auf die Festplatte geschrieben werden
temp_store	Steuert, wo temporäre Daten gespeichert werden
temp_store_directory	Setzt das Verzeichnis für temporäre Dateien
user_version	Setzt oder abfragt die benutzerdefinierte Versionsnummer
writable_schema	Ermöglicht die direkte Änderung der sqlite_master-Tabelle

Und das war's! Sie sind jetzt gut gerüstet, um mit SQLite PRAGMAs zu experimentieren. Denken Sie daran, dass diese mächtigen Werkzeuge das Verhalten und die Leistung Ihrer Datenbank erheblich beeinflussen können, daher immer gründlich testen, bevor Sie sie in einer Produktionsumgebung verwenden. Viel Spaß beim Coden!

Credits: Image by storyset