База SQLite и основы SQL. Часть 10 (условия IF)

17-11-2021Время чтения ~ 5 мин.SQL 3828

В SQL есть несколько полезных инструкций, которые здорово облегчают жизнь программистам. Одни из них IF EXISTS и IF NOT EXISTS. Их можно применять как условие существования или отсутствия в разных командах, например в CREATE TABLE.

Когда мы будем рассматривать вопросы использования SQLite в PHP, то вы узнаете, что подключение этой базы (фактически файла) происходит всегда: если файл есть, то он законнетится, а если нет, то будет создан новый. Это удобно с точки зрения программирования, но создаёт проблему — нужно убедиться, что база новая и тогда нужно выполнить запросы по созданию таблиц.

Традиционно это делается путем отдельного запроса, по которому мы можем получить список существующих таблиц. Но намного проще сразу использовать возможности SQL.

Делается это путем добавления инструкции IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS table_name (...);

СУБД сама проверит наличие указанной таблицы. Если же условие IF NOT EXISTS убрать, то повторное выполнение запроса приведёт к ошибке «table table_name already exists».

Это же условие можно использовать в запросе CREATE INDEX:

CREATE INDEX IF NOT EXISTS index_name ON table_name (...);

Обратное условие IF EXISTS используется в командах DROP:

DROP TABLE IF EXISTS table_name;
DROP INDEX IF EXISTS index_name;

Команда ALTER TABLE

Иногда (довольно редко) возникает задача изменить таблицу. В SQLite для этого используется команда ALTER TABLE. В отличие от других СУБД, здесь она имеет не очень большие возможности.

Переименовать таблицу можно с помощью RENAME TO:

ALTER TABLE t1 RENAME TO t2;

Если нужно переименовать какой-то столбец в таблице, то используется RENAME COLUMN:

ALTER TABLE t1 RENAME COLUMN name TO title;

Для добавления нового столбца используется ADD COLUMN:

ALTER TABLE t1 ADD COLUMN slug TEXT DEFAULT '';

В данном примере мы добавили новое поле slug типом TEXT и значением по умолчанию DEFAULT.

Ну и с помощью DROP COLUMN можно удалить столбец.

ALTER TABLE t1 DROP COLUMN slug;

В SQLite нет команды MODIFY, с помощью которой можно было бы изменить тип столбца. В других базах, например MySQL есть возможность модифицировать поле, а в SQLite эту же операцию можно сделать, только созданием новое таблицы и копированием в неё данных из старой. Не очень удобно, но в случае необходимости, вопрос решается.

Системные таблицы SQLite

В любой базе будут присутствовать несколько системных таблиц. Одна из них sqlite_master. Попробуйте выполнить запрос:

SELECT * FROM sqlite_master;
 
type   name   tbl_name  rootpage  sql
-------------------------------------
table  t1     t1        2         CREATE TABLE...
... и т.д.

Когда стоит задача узнать какие в базе есть таблицы, то достаточно сделать sql-запрос к sqlite_master.

Другая таблица — sqlite_sequence хранит последнее используемое значение автоинкрементов в таблицах (если они есть).

SELECT * FROM sqlite_sequence;
 
name    seq
-----------
pages   3
ststus  2
tb      5
tGr1    8

В редких случаях может понадобиться узнать следующий номер PRIMARY KEY. Проще всего это узнать в таблице sqlite_sequence.

Ещё одна системная таблица — sqlite_stat1 используется для сбора статистики по индексам и это может быть использовано для оптимизации запросов SQLite. Чтобы таблица появилась, нужно выполнить команду ANALYZE.

ANALYZE; -- для все базы
ANALYZE t1; -- только для определённой таблицы
SELECT * FROM sqlite_stat1; -- можно посмотреть данные

Результат работы ANALYZE используется планировщиком запросов SQLite, что позволяет ускорить их выполнение. Но есть нюанс — работать это будет, только если выполнить команду ANALYZE. Если её не сделать, то информация будет устаревшей или неточной. Я думаю, что ANALYZE будет полезна для профессиональных разработчиков баз данных, где большие объёмы критически сказываются на скорости выполнения запросов, да и сами запросы достаточно сложные.

Представление (VIEW)

Достаточно интересная возможность — это создание представления. Это некая виртуальная таблица, которая создаётся отдельным SELECT запросом. С помощью VIEW можно упростить использование сложных sql-запросов.

Например у нас есть запрос к таблице tSum (раньше мы её делали). Предположим, что нам нужно часто использовать какой-то сложный запрос. Делаем на его основе представление tSum_v1:

CREATE VIEW IF NOT EXISTS tSum_v1 AS
    SELECT a, b, a + b
    FROM tSum;

Теперь оно доступно как отдельная таблица:

SELECT * FROM tSum_v1;

Представления интересны в случаях, когда нужно предоставить базу с уже готовыми SQL-запросами. Например разработчик может заранее добавить в базу представления и передать её клиенту. То есть клиенту достаточно «запустить» представление, чтобы получить нужную выборку.

Внешние ключи FOREIGN KEY

Когда мы связывали несколько таблиц, то использовали какое-то поле, которое ссылается на другую таблицу. Фактически таблицы полностью самостоятельны, а связь мы организуем через sql-запрос. Но SQLite (и другие СУБД) позволяют организовать такую связь прямо при создании таблиц.

Я не буду приводить примеры, поскольку использование FOREIGN KEY в подавляющем большинстве случаев лишено смысла, но вы должны знать об этой возможности.

Когда создаётся FOREIGN KEY для таблиц, то возникают дополнительные условия хранения и изменения данных в связанных полях. Например, если мы попытаемся удалить запись из родительской таблицы, но при этом останется ссылка в дочерней, то это приведёт к ошибке — вначале нужно будет удалить данные из дочерней таблицы. Так проверяется целостность данных.

Таких ситуаций может быть много — они связаны и с добавлением данных, и удалением, и обновлением.

Для чего же тогда были придуманы FOREIGN KEY? В первую очередь для того, чтобы избавиться от «проблемных» данных и проверка на уровне БД позволяет гарантировать целостность и корректность данных. Если мы вручную вносим данные в базу, то логично было бы получить и проверку.

На практике же такие проверки выполняю готовые программы. Скажем при добавлении автора, php-скрипт сделает запрос, чтобы убедиться, что указанный автор действительно существует. Или при удалении пользователя, также вначале нужно убедится, что он есть, а потом скрипт удалит все данные пользователя во всех таблицах базы. Такой алгоритм работы объясняется безопасностью, а также тем правилом, что нельзя доверять любым входящим данным. Поэтому многочисленные проверки стали нормой, что делают не нужными проверки уровня FOREIGN KEY.