Настройка SQLite PRAGMA

Введение

В этой лабораторной работе вы изучите настройку SQLite PRAGMA для оптимизации производительности и надежности базы данных. Вы узнаете, как настраивать ключевые аспекты поведения SQLite с помощью операторов PRAGMA. Мы рассмотрим настройку режима журнала (journal mode), включение проверок внешних ключей (foreign key checks), выполнение проверок целостности (integrity checks) и настройку размера кэша (cache size).

Skills Graph

Создание базы данных и настройка режима журнала (Journal Mode)

В этом шаге вы создадите базу данных SQLite и настроите ее режим журнала (journal mode). Режим журнала контролирует, как SQLite обрабатывает транзакции и обеспечивает целостность данных.

Сначала откройте свой терминал в LabEx VM. Ваш путь по умолчанию - /home/labex/project.

Создайте базу данных SQLite с именем test.db и войдите в оболочку SQLite, используя следующую команду:

sqlite3 test.db

Эта команда создает файл базы данных test.db (если он не существует) и открывает инструмент командной строки SQLite. Вы увидите приглашение, подобное этому:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Теперь давайте настроим режим журнала. SQLite предлагает несколько режимов журнала, включая DELETE, TRUNCATE, PERSIST, MEMORY, WAL и OFF. WAL (Write-Ahead Logging) обеспечивает хороший баланс между производительностью и надежностью.

Выполните следующую SQL-команду, чтобы установить режим журнала в WAL:

PRAGMA journal_mode=WAL;

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

Чтобы проверить режим журнала, выполните:

PRAGMA journal_mode;

Ожидаемый вывод:

wal

Это подтверждает, что режим журнала установлен в WAL.

Включение поддержки внешних ключей (Foreign Key Support)

В этом шаге вы включите поддержку внешних ключей (foreign key support) в вашей базе данных SQLite. Внешние ключи обеспечивают связи между таблицами и помогают поддерживать целостность данных.

Внутри оболочки SQLite (если вы вышли на предыдущем шаге, переподключитесь с помощью sqlite3 test.db), выполните следующую команду:

PRAGMA foreign_keys = ON;

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

Чтобы проверить, включена ли поддержка внешних ключей, выполните:

PRAGMA foreign_keys;

Ожидаемый вывод:

1

Этот вывод подтверждает, что поддержка внешних ключей включена.

Создание таблиц со связью по внешнему ключу (Foreign Key Relationship)

Теперь, когда поддержка внешних ключей включена, давайте создадим две таблицы со связью по внешнему ключу (foreign key relationship), чтобы продемонстрировать ее функциональность.

Выполните следующие SQL-команды для создания таблицы users и таблицы orders:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Эти команды создают две таблицы:

• users: Хранит информацию о пользователях со столбцами id (первичный ключ) и name.
• orders: Хранит информацию о заказах со столбцами id (первичный ключ), user_id (внешний ключ, ссылающийся на users.id) и amount.

Предложение FOREIGN KEY (user_id) REFERENCES users(id) устанавливает связь между таблицей orders и таблицей users. Оно гарантирует, что user_id в таблице orders должен существовать в столбце id таблицы users.

Проверка ограничения внешнего ключа (Foreign Key Constraint)

В этом шаге вы проверите ограничение внешнего ключа (foreign key constraint), чтобы увидеть, как оно предотвращает вставку недопустимых данных в базу данных.

Сначала вставьте пользователя в таблицу users:

INSERT INTO users (id, name) VALUES (1, 'Alice');

Эта команда вставляет нового пользователя с id 1 и name 'Alice' в таблицу users.

Теперь попробуйте вставить заказ в таблицу orders с user_id, который не существует в таблице users:

INSERT INTO orders (user_id, amount) VALUES (999, 100.0);

Поскольку проверки внешних ключей включены, эта команда завершится с ошибкой:

Error: FOREIGN KEY constraint failed

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

Далее, вставьте допустимый заказ с существующим user_id:

INSERT INTO orders (user_id, amount) VALUES (1, 100.0);

Эта команда выполнится успешно, потому что user_id 1 существует в таблице users.

Выполнение проверки целостности (Integrity Check)

В этом шаге вы выполните проверку целостности (integrity check) вашей базы данных SQLite, чтобы убедиться, что в ней нет несоответствий или ошибок.

Выполните следующую команду в оболочке SQLite (SQLite shell):

PRAGMA integrity_check;

Эта команда проверяет всю базу данных на наличие каких-либо несоответствий или ошибок. Если база данных в порядке, вывод будет ok.

Ожидаемый вывод:

ok

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

Настройка размера кэша и выход

В этом заключительном шаге вы настроите размер кэша, используемый SQLite, а затем выйдете из оболочки SQLite (SQLite shell).

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

Выполните следующую команду, чтобы установить размер кэша в 4 МБ (4096 КБ):

PRAGMA cache_size = 4096;

Эта команда устанавливает размер кэша в 4 МБ для текущего подключения к базе данных.

Чтобы проверить размер кэша, выполните:

PRAGMA cache_size;

Ожидаемый вывод:

4096

Наконец, выйдите из оболочки SQLite:

.exit

Эта команда закрывает соединение с базой данных и возвращает вас в командную строку.

Итог

В этой лабораторной работе вы изучили настройку SQLite PRAGMA. Вы узнали, как настроить режим журнала (journal mode) для повышения производительности и надежности, включить поддержку внешних ключей (foreign key) для обеспечения целостности данных, создавать таблицы со связями по внешним ключам, проверять ограничения внешних ключей, выполнять проверки целостности (integrity check) для выявления повреждений базы данных и настраивать размер кэша для оптимизации использования памяти. Эти навыки обеспечивают прочную основу для эффективной работы с базами данных SQLite.