SQLite - PRAGMA: Полное руководство для начинающих

Здравствуйте,野心勃勃的数据库爱好者们！今天，我们将踏上一段激动人心的旅程，探索SQLite PRAGMAs的世界。如果您以前从未听说过PRAGMAs，别担心 - 到了这个教程结束时，您将能够像专业人士一样使用它们！

Что такое PRAGMA?

Прежде чем мы углубимся, начнем с основ. В SQLite PRAGMA - это особая команда, которая позволяет нам изменять поведение библиотеки SQLite или запрашивать ее внутреннее состояние. Представьте PRAGMAs как панель управления вашей базой данных SQLite - они позволяют вам настраивать параметры и получать информацию о том, как работает ваша база данных.

Теперь давайте рассмотрим некоторые из наиболее часто используемых PRAGMAs в SQLite.

PRAGMA auto_vacuum

PRAGMA auto_vacuum - это как иметь в базе данных аккуратного соседа по комнате. Он автоматически очищает и организует файл базы данных по мере удаления данных.

PRAGMA auto_vacuum = FULL;

Эта команда устанавливает режим auto_vacuum на FULL, что означает, что SQLite будет стараться держать ваш файл базы данных как можно меньше.

PRAGMA cache_size

PRAGMA cache_size - это как дать вашей базе данных больше памяти. Он определяет, сколько страниц SQLite держит в памяти для быстрого доступа.

PRAGMA cache_size = 10000;

Эта команда устанавливает размер кэша на 10 000 страниц. Больше кэша означает лучшую производительность, но также используется больше памяти.

PRAGMA case_sensitive_like

Этот PRAGMA определяет, чувствителен ли оператор LIKE к регистру. Это как научить вашу базу данных читать с или без очков.

PRAGMA case_sensitive_like = TRUE;

С этим параметром 'Apple' LIKE 'app%' будет ложно, а 'Apple' LIKE 'App%' - истина.

PRAGMA count_changes

PRAGMA count_changes - это как судья для ваших операций с базой данных. Он сообщает вам, сколько строк было изменено INSERT, UPDATE или DELETE оператором.

PRAGMA count_changes = ON;

После настройки этого, когда вы выполните, например, UPDATE оператор, SQLite сообщит вам, сколько строк было затронуто.

PRAGMA database_list

Этот PRAGMA похож на каталог вашей базы данных. Он перечисляет все подключенные базы данных, включая основную и любую другую, к которой вы подключились.

PRAGMA database_list;

Это покажет вам список всех подключенных баз данных, их пути к файлам и их внутренние имена.

PRAGMA encoding

PRAGMA encoding устанавливает или запрашивает текстовое кодирование, используемое базой данных. Это как выбрать, на каком языке будет говорить ваша база данных.

PRAGMA encoding = "UTF-8";

Эта команда устанавливает кодирование базы данных на UTF-8, что является.common выбором для поддержки нескольких языков.

PRAGMA freelist_count

Этот PRAGMA говорит вам, сколькоunused страниц в файле базы данных. Это как проверить, сколько свободного места у вас есть в хранилище.

PRAGMA freelist_count;

Это вернет количествоunused страниц в файле базы данных.

PRAGMA index_info и PRAGMA index_list

Эти PRAGMAs похожи на библиотекаря для ваших индексов базы данных. Они предоставляют информацию о структуре и содержимом ваших индексов.

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

Первая команда перечисляет все индексы в таблице, а вторая предоставляет детализированную информацию о конкретном индексе.

PRAGMA journal_mode

PRAGMA journal_mode контролирует, как SQLite отслеживает транзакции. Это как выбрать между различными типами систем резервного копирования.

PRAGMA journal_mode = WAL;

Эта команда устанавливает режим журнала на Write-Ahead Logging (WAL), который может улучшить производительность во многих случаях.

PRAGMA max_page_count и PRAGMA page_count

Эти PRAGMAs работают с количеством страниц в вашем файле базы данных. Это как установить и проверить максимальное количество страниц в книге.

PRAGMA max_page_count = 1000000;
PRAGMA page_count;

Первая команда устанавливает максимальное количество страниц, а вторая говорит вам, сколько страниц используется в настоящее время.

PRAGMA page_size

Этот PRAGMA устанавливает или запрашивает размер страницы базы данных. Это как решить, какого размера должна быть каждая страница в вашей книге.

PRAGMA page_size = 4096;

Эта команда устанавливает размер страницы на 4096 байтов, что являетсяcommon выбором.

PRAGMA parser_trace и PRAGMA sql_trace

Эти PRAGMAs похожи на включение режима отладки для вашей базы данных. Они предоставляют детализированную информацию о том, как SQLite обрабатывает ваши команды.

PRAGMA parser_trace = ON;
PRAGMA sql_trace = ON;

Эти команды могут быть очень полезны, когда вы пытаетесь понять, почему определенный запрос не работает так, как expected.

PRAGMA recursive_triggers

Этот PRAGMA контролирует, могут ли триггеры рекурсивно запускать другие триггеры. Это как позволять эффектам domina в вашей базе данных.

PRAGMA recursive_triggers = ON;

С этим параметром триггер может вызвать другой триггер, который может вызвать еще один триггер, и так далее.

PRAGMA schema_version и PRAGMA user_version

Эти PRAGMAs позволяют устанавливать и запрашивать версии для вашей схемы базы данных. Это как метки версий для структуры вашей базы данных.

PRAGMA schema_version;
PRAGMA user_version = 1;

Первая команда запрашивает текущую версию схемы, а вторая устанавливает версию пользователя на 1.

PRAGMA secure_delete

Этот PRAGMA контролирует, насколько тщательно SQLite перезаписывает удаленные данные. Это как выбирать между измельчением ваших документов или просто выбрасыванием их в мусор.

PRAGMA secure_delete = ON;

С этим параметром SQLite будет перезаписывать удаленные данные нулями, делая их сложнее для восстановления.

PRAGMA synchronous

Этот PRAGMA контролирует, как тщательно SQLite записывает данные на диск. Это баланс между безопасностью и скоростью.

PRAGMA synchronous = NORMAL;

Эта команда устанавливает moderate уровень безопасности, балансируя между полной безопасностью (FULL) и максимальной скоростью (OFF).

PRAGMA temp_store и PRAGMA temp_store_directory

Эти PRAGMAs контролируют, где и как SQLite хранит временные данные. Это как выбирать, где положить ваш черновик, когда вы работаете.

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

Первая команда命令 tells SQLite хранить временные данные в памяти, а вторая указывает каталог для временных файлов.

PRAGMA writable_schema

Этот PRAGMA контролирует, можете ли вы изменять таблицу sqlite_master напрямую. Это как иметь master ключ к структуре вашей базы данных.

PRAGMA writable_schema = ON;

Будьте очень осторожны с этим! Он позволяет вам изменять fundamental структуру вашей базы данных, что может быть опасно, если не сделать это правильно.

Вот таблица, резюмирующая все PRAGMAs, которые мы обсуждали:

PRAGMA	Описание
auto_vacuum	Контролирует автоматическое vacuming базы данных
cache_size	Устанавливает количество страниц, которые нужно держать в памяти
case_sensitive_like	Контролирует чувствительность оператора LIKE к регистру
count_changes	Отчет о количестве строк, измененных операциями
database_list	Перечисляет подключенные базы данных
encoding	Устанавливает или запрашивает текстовое кодирование базы данных
freelist_count	Отчет о количествеunused страниц в файле базы данных
index_info	Предоставляет информацию о конкретном индексе
index_list	Перечисляет все индексы в таблице
journal_mode	Контролирует, как отслеживаются транзакции
max_page_count	Устанавливает максимальное количество страниц в базе данных
page_count	Отчет о текущем количестве страниц в базе данных
page_size	Устанавливает или запрашивает размер страницы базы данных
parser_trace	Включает детализированную информацию о парсинге
recursive_triggers	Позволяет триггерам рекурсивно запускать другие триггеры
schema_version	Устанавливает или запрашивает версию схемы
secure_delete	Контролирует, насколько тщательно перезаписываются удаленные данные
sql_trace	Включает детализированную информацию о выполнении SQL
synchronous	Контролирует, как тщательно данные записываются на диск
temp_store	Контролирует, где хранятся временные данные
temp_store_directory	Указывает каталог для временных файлов
user_version	Устанавливает или запрашивает пользовательскую версию
writable_schema	Позволяет прямое изменение таблицы sqlite_master

И вот и все! Вы теперь хорошо подготовлены, чтобы начать экспериментировать с PRAGMAs SQLite. Помните, эти мощные инструменты могут значительно повлиять на поведение и производительность вашей базы данных, поэтому всегда тщательно тестируйте их перед использованием в производственной среде. Счастливого кодирования!

Credits: Image by storyset