SQLite CREATE VIEW: создание представлений в базе данных

В теории баз данных представление (view) — это сохранённый набор данных, который получается в результате выполнения запроса. Оно позволяет сохранить сложный запрос под именем для последующего использования

Вы можете получать доступ к данным базовых таблиц через представление. Таблицы, на которые ссылается запрос в определении представления, называются базовыми таблицами (base tables)

Зачем использовать представления

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

Во-вторых, представления позволяют инкапсулировать сложные запросы с объединениями, чтобы упростить доступ к данным

Представление SQLite предназначено только для чтения. Это означает, что операции INSERT, DELETE и UPDATE не могут использоваться для обновления данных в базовых таблицах через представление

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Синтаксис оператора CREATE VIEW

Для создания представления используется оператор CREATE VIEW следующего вида:

CREATE [TEMP] VIEW [IF NOT EXISTS] view_name[(column-name-list)]
AS select-statement;

Сначала укажите имя представления. Параметр IF NOT EXISTS предотвратит создание нового представления, если оно уже существует, что позволяет избежать ошибок

Во-вторых, используйте параметр TEMP или TEMPORARY, если хотите, чтобы представление было видимо только в текущем соединении с базой данных. Такое представление называется временным, и SQLite автоматически удаляет его при закрытии соединения

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

Примеры создания представлений

Рассмотрим несколько конкретных примеров, которые помогают лучше понять, как представления могут использоваться на практике

Создание представления для упрощения сложного запроса

Следующий запрос получает данные из таблиц tracks, albums, media_types и genres в учебной базе данных с использованием предложения INNER JOIN:

SELECT trackid, tracks.name, albums.Title AS album,
       media_types.Name AS media, genres.Name AS genres
FROM tracks
INNER JOIN albums ON Albums.AlbumId = tracks.AlbumId
INNER JOIN media_types ON media_types.MediaTypeId = tracks.MediaTypeId
INNER JOIN genres ON genres.GenreId = tracks.GenreId;

Чтобы создать представление на основе этого запроса, используйте следующий оператор:

CREATE VIEW v_tracks
AS
SELECT trackid, tracks.name, albums.Title AS album,
       media_types.Name AS media, genres.Name AS genres
FROM tracks
INNER JOIN albums ON Albums.AlbumId = tracks.AlbumId
INNER JOIN media_types ON media_types.MediaTypeId = tracks.MediaTypeId
INNER JOIN genres ON genres.GenreId = tracks.GenreId;

Начиная с этого момента, вместо приведённого выше сложного запроса можно использовать простой:

SELECT * FROM v_tracks;

Создание представления с пользовательскими именами столбцов

Следующий оператор создаёт представление с именем v_albums, которое содержит название альбома и его продолжительность в минутах:

CREATE VIEW v_albums (AlbumTitle, Minutes)
AS
SELECT albums.title, SUM(milliseconds) / 60000
FROM tracks
INNER JOIN albums USING (AlbumId)
GROUP BY AlbumTitle;

Здесь мы указали новые имена столбцов для представления: AlbumTitle для столбца albums.title и Minutes для выражения SUM(milliseconds) / 60000

Следующий запрос возвращает данные из представления v_albums:

SELECT * FROM v_albums;

Типичные ошибки при работе с представлениями

На практике я встречаю несколько распространённых ошибок, которые стоит учитывать заранее

Попытка изменить данные через представление. Поскольку представления SQLite доступны только для чтения, любая попытка выполнить INSERT, UPDATE или DELETE через представление завершится ошибкой. Для изменения данных обращайтесь напрямую к базовым таблицам

Создание представления без IF NOT EXISTS. Если представление с таким именем уже существует, оператор CREATE VIEW без этого параметра вернёт ошибку. Мой совет — используйте IF NOT EXISTS по умолчанию, чтобы избежать конфликтов при повторном выполнении скрипта

Путаница с временными представлениями. Временное представление (TEMP VIEW) существует только в рамках текущего соединения. Если вы создали его в одной сессии и ожидаете увидеть в другой — представления там не будет

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

Удаление представления

Для удаления представления используется оператор DROP VIEW:

DROP VIEW [IF EXISTS] view_name;

Параметр IF EXISTS позволяет избежать ошибки, если представление с указанным именем не существует. Удаление представления не затрагивает базовые таблицы и данные в них

Ответы на эти вопросы могут быть для вас полезными

Можно ли обновлять данные через представление в SQLite? Нет. Представления SQLite доступны только для чтения. Операторы INSERT, UPDATE и DELETE через представление не поддерживаются

Чем временное представление отличается от обычного? Временное представление (создаётся с параметром TEMP или TEMPORARY) видимо только в текущем соединении с базой данных. SQLite автоматически удаляет его при закрытии соединения

Что произойдёт, если создать представление с именем уже существующего? Без параметра IF NOT EXISTS SQLite вернёт ошибку. С параметром IF NOT EXISTS оператор просто не выполнит никаких действий

Можно ли задать собственные имена столбцов в представлении? Да. Имена столбцов указываются в скобках после имени представления: CREATE VIEW view_name (col1, col2) AS SELECT .... Это удобно, когда имена столбцов в SELECT-запросе неинформативны или слишком длинны

Влияет ли удаление представления на базовые таблицы? Нет. DROP VIEW удаляет только само представление. Базовые таблицы и данные в них остаются нетронутыми