Тема 14: VIEW в SQL на примере базы данных SQLite: CREATE, DROP, UPDATE

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В этой записи мы с вами разберемся с представлениями и их использованием в реляционных базах данных. Вообще VIEW в SQL довольно полезная штука, которая позволяет упростить SQL запросы SELECT, а также скрыть логику базы данных от пользователей и программного кода, тем самым создав дополнительный уровень абстракции, который защищает наши базы данных от вредоносного вмешательства. Многие считают VIEW виртуальными таблицами, что не совсем правильно, так как представление — это запрос хранимый в базе данных и доступный по его имени (VIEW это такой же объект базы данных, как скажем, триггер или таблица). Делать выборку данных из VIEW во многих СУБД намного быстрее, например, MySQL сервер любит кэшировать результаты запросов, а VIEW, как вы поняли, есть ни что иное, как запрос.

VIEW в SQL на примере базы данных SQLite: CREATE, DROP, UPDATE.

VIEW в SQL на примере базы данных SQLite: CREATE, DROP, UPDATE.

В этой записи мы с вами будем разбираться с использованием VIEW в SQL и реляционных базах данных на пример библиотеки SQLite. Сначала мы поговорим о том, что собой представляют VIEW в базах данных и разберемся с тем, как мы можем использовать представления. Затем поговорим про особенности работы представлений в SQLite3 и разберем SQL синтаксис VIEW, реализованный в данной СУБД. И затем попробуем поработать с VIEW в базах данных под управлением SQLite.


Что такое VIEW в контексте языка SQL и баз данных?

Прежде чем ответить на вопрос зачем нужны VIEW в SQL и реляционных базах данных давайте ответим на вопрос: «что такое VIEW в языке запросов SQL?». В Википедии, на мой взгляд, формулировка определения VIEW в SQL написана неправильно. Так как представление не является виртуальной таблицей (как минимум, для создания виртуальных таблиц в SQLite предусмотрен отдельный синтаксис).

Документация MySQL говорит нам о том, что представление можно рассматривать, как виртуальную таблицу, но не утверждает, что VIEW – это VIRTUAL TABLE. В разделе VIEW документации Oracle упоминаний про виртуальную таблицу при беглом чтении я не встретил. Конечно, кто-то со мной может не согласиться, но я считаю, что VIEW – это не виртуальная таблица. Итак, мы разобрались с тем, чем не является VIEW в SQL и реляционных базах данных.

Теперь давайте дадим правильное определение термину VIEW в контексте языка SQL. VIEW – это хранимый запрос в базе данных. Возможно, представление называют виртуальной таблицей (virtual table) по той причине, что структура VIEW полностью повторяет структуру результирующей таблицы запроса SELECT, но опять же, это не повод называть VIEW виртуальной таблицей.

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

Вместо термина VIEW в различных источниках вы можете встретит термины представления или просмотры. Мне удобнее использовать термин представление. Давайте вернемся к определению термина представления в базах данных. Итак, представление – это хранимый в базе данных запрос, которому нужно дать имя. Когда мы создали представление, мы можем обращаться к нему, как к обычной таблице базы данных, используя то имя, которое мы написали после команды CREATE VIEW.

Единственная команда языка SQL, возвращающая в результате своей работы таблицу – это команда SELECT, с помощь которой мы не только делаем выборку данных, но и создаем VIEW в базе данных. Практически в любой СУБД для работы с представлениями доступны все команды манипуляции данными, но в библиотеки SQLite3 это утверждение не верно, об этом мы поговорим чуть ниже.

Напомним себе, что команды: UPDATE, SELECT, INSERT, DELETE, относятся к командам манипуляции данными. VIEW создается на основе запроса SELECT. Но, например, в базах данных MySQL, вы не сможете использовать команды UPDATE, INSERT, DELETE, если SQL запрос создающий VIEW содержит:

  1. Функции агрегации.
  2. Ключевое слово LIMIT.
  3. Клаузулу GROUP BY, позволяющую сделать группировку данных.
  4. Клаузула HAVING, фильтрующая данные после группировки.
  5. Операторы UNION и UNION ALL, объединяющие результаты двух запросов.
  6. Любой подзапрос SELECT, даже подзапрос JOIN, объединяющий две таблицы.
  7. Если запрос содержит пользовательские переменные.
  8. Если нет базовой таблицы.

Поэтому рекомендую вам сперва ознакомиться с документацией той или иной СУБД, прежде чем начать создавать представления в базе данных. Например, документация MySQL так и говорит, что команды манипуляции данными (за исключением SELECT, который можно применять к любому представлению) можно применять к VIEW в том случае, когда строки VIEW совпадают со строками таблицы в базе данных (это несколько вольный и не совсем точный перевод).

Мы разобрались с тем, что VIEW – это именованный запрос SELECT, который хранится в базе данных. Каждый раз, когда мы обращаемся к VIEW, СУБД выполняет этот запрос SELECT, а следом за ним, она выполняет наш запрос. Думаю, ничего сложно в понимание того, что такое VIEW нет, давайте теперь разберемся для чего мы можем использовать VIEW.

Использование представлений в SQL и реляционных базах данных

Первое и очевидное применение VIEW в базах данных заключается в том, чтобы упростить запросы на выборку данных. Ведь нам же не хочется писать полотно SELECT, которое объединяет три-четыре таблицы каждый раз, а потом еще задавать какие-нибудь условия выборки данных клаузулой WHERE? Итак, первое, для чего мы можем использовать представление – это для упрощения запросов выборки данных.

Второй пункт можно назвать безопасность. Во-первых, при помощи VIEW можно скрыть бизнес-логику и архитектуру базы данных от прикладных приложений, сделав так, что программа будет обращаться не к таблицам базы данных, а к представлениям. Во-вторых, так вы избавитесь от некоторых видов SQL-инъекций, плюсом к этому, особо талантливые программисты лишаться «чудесной возможности» конкатенировать SQL запросы (как только вы увидите, что программист конкатенирует SQL запрос, можете зарядить ему в щи с вертушки и прокричать: я угорел по базам данных, а ты не знаешь даже таких простых вещей), тем самым вы еще уменьшите вариативность атак на вашу базу данных.

Третий вариант применения VIEW сводится к обновлениям. Вы редко можете встретить базу данных без прикладного приложения. Мир не стоит на месте, всё летит, всё развивается, компании растут и объединяются, у клиентов появляются всё новые потребности и рано или поздно старые приложения становятся неудобными и возникает потребность в их модификации. Мы уже говорили, что VIEW позволяют скрывать бизнес-логику базы данных, но не всегда, создавая базу данных, вы создаете представления. Поэтому если у вас возникла потребность в обновлении программного кода, работающего с базой данных, вы можете создать новую структуру базы данных в виде представлений, с которой будет работать новый программный код, тем самым вы разделите схему хранения данных и схему представления данных. Если потребности в разделении схем нет, то в дальнейшем вы можете отказаться от использования VIEW и вернуться к таблицам, после того, как код приложения будет обновлен.

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

Особенности работы с VIEW в базах данных SQLite

Теперь поговорим про особенности VIEW в базах данных SQLite, мы уже говорили о том, что представления в SQLite нельзя редактировать, их можно только создавать, удалять и делать выборку из VIEW, в то время, как другие СУБД позволяют выполнять другие команды манипуляции данными представлений.

Но это не совсем так, все дело в том, что SQLite не дает возможность редактировать представления при помощи обычных SQL запросов. Но в базах данных есть триггеры, которые успешно эмитируют работу команд UPDATE, INSERT и DELETE. Соответственно, если мы можем:

  1. Добавлять строки в таблицы базы данных.
  2. Удалять данные из таблицы базы данных.
  3. Модифицировать данные в таблице.

То ничто нам не помешает выполнить те же самые операции с VIEW в SQLite, правда они будут немного сложнее из-за того, что нам придется использовать триггеры.

SQL синтаксис VIEW в базах данных SQLite

Давайте разберемся с синтаксисом SQL, реализованным в SQLite, который позволяет нам создавать и удалять представления из базы данных. Начнем мы с синтаксиса создания представлений в SQLite, он показан на рисунке ниже.

SQL синтаксис создания VIEW в базах данных SQLite

SQL синтаксис создания VIEW в базах данных SQLite

Отметим, что создание VIEW начинается с той же команды, что и создание таблицы в базе данных: с команды CREATE. Это обусловлено тем, что VIEW – это такой же объект базы данных, как и таблица. Далее мы указываем, что хотим создать представление при помощи ключевого слова VIEW. Представление может быть временным, поэтому после ключевого слова CREATE вы можете использовать слово TEMP или TEMPORARY. Если вы не уверены, что создаете представление с уникальным именем и не хотите возникновения ошибок при создании VIEW в базе данных, то можете использовать ключевую фразу IF NOT EXIST (кстати, оператор EXISTS может быть использован для создания подзапроса SELECT). Далее вам необходимо указать имя представления, которое должно быть уникальным, в качестве имени можно использовать квалификатор, в том случае, если вы работаете с несколькими базами данных и хотите быть уверенным в том, что создаете VIEW для нужной базы данных.

После имени представления идет ключевое слово AS и запрос SELECT, который как раз-таки и будет храниться в файле базы данных SQLite и к которому SQLite будет обращаться по тому имени, которое вы указали при создании VIEW.

Теперь рассмотрим SQL синтаксис удаления VIEW из базы данных под управлением SQLite3. Он показан на рисунке ниже.

SQL синтаксис удаления VIEW из базы данных под управлением SQLite3

SQL синтаксис удаления VIEW из базы данных под управлением SQLite3

Хоть обычное представление, хоть временное, удаляются из базы данных под управлением SQLite одинаково: ключевое слово DROP, за которым следует VIEW, говорит SQLite о том, что вы хотите удалить из базы данных не просто объект, а представление. Далее следует конструкция IF EXISTS, которая осуществляет проверку наличия представления в базе данных, чтобы SQLite не возвращала ошибки в том случае, если представление,  которое вы хотите удалить, уже удалено. После чего идет имя представления или квалификатор.

Отметим, что для представлений в SQLite команда ALTER не реализована. Если вам нужно изменить структуру VIEW, то вам нужно удалить старое представление, а затем создать новой и с новой структурой.

Итак, мы разобрались с SQL синтаксисом VIEW в базах данных SQLite и можем начинать работать с представлениями.

Создание представлений в SQL. Команда CREATE VIEW в SQLite3

Давно мы не использовали тестовую базу данных World.db3, давайте к ней вернемся и создадим несколько представлений в данной базе данных. Если вы откроете базу данных World.db3 и воспользуетесь командой .schema, то увидите, что в ней уже есть два VIEW, в одном представление хранятся записи обо всех столицах всех стран, а вторая VIEW хранит строки, в которых содержится информация об официальных языка стран, эти представления были созданы следующими запросами:

Из этих примеров видно, как при помощи команды CREATE VIEW можно создать представление в базе данных SQLite3. Запрос CREATE VIEW похож на запрос создания таблицы на основе команды SELECT. Обратите внимание, что для команды CREATE VIEW не требуется перечисление столбцов, которые будут в представлении, опять же, это всё упирается в то, что VIEW не является виртуальной таблицей, а является хранимым запросом в базе данных.

Команда CREATE VIEW допускает использование различных вариаций запросов SELECT. В примерах авторы базы данных создавали представления с использованием клаузулы WHERE, задающей условие выборки данных, также были использован SQL оператор AND, который делает условие выборки данных более точным и клаузула ORDER BY, упорядочивающая выборку данных.

Но никто нам не запрещает, например, использовать клаузулу LIMIT, чтобы ограничит кооличество строк в выборке, давайте создадим свое представление на основе уже существующего с использованием LIMIT. Воспользуемся командой CREATE VIEW:

Проверим, что команда CREATE VIEW создала представление, в котором только десять строк:

Действительно, в получившемся представление только первых десять строк. Представления, созданные выше, создавались на основе двух таблиц, давайте напишем запрос с использованием CREATE VIEW, который создаст представления на основе запроса к одной таблице. Допустим я хочу, чтобы VIEW хранила записи, состоящие из двух столбцов таблицы City: name и population, и при этом в таблице хранилась информация о тех городах, где население больше пяти миллионов.

Таким несложным образом мы можем создавать представления в базах данных SQLite.

Удаление представлений в SQL. Команда DROP VIEW в SQLite

Теперь давайте удалим представление из базы данных SQLite при помощи команды DROP VIEW. Синтаксис команды DROP VIEW мы ранее рассмотрели. Сначала мы попробуем удалить представление, которого нет в базе данных, командой DROP VIEW :

SQLite выдала ошибку и сообщила, что такого представления в базе данных нет. Если бы к базе данных с таким запросом обратилось приложение, то, не исключено, что работа приложения могла быть прервана из-за возникшей ошибки. Поэтому не ленитесь использовать конструкцию IF EXISTS, в этом случае ошибки никакой не будет:

Давайте удалим представление, в котором мы использовали клаузулу LIMIT, командой DROP VIEW:

Проверить тот факт, что представление было удалено, можно при помощи команды .tables, данная команда показывает не только все таблицы в базе данных, но и все созданные VIEW. Таким образом мы разобрались с удалением представлений из базы данных под управлением библиотеки SQLite3.

Манипуляция данными в представлениях SQLite. Редактирование представлений

Мы уже получали строки из представления командой SELECT, давайте закрепим эту операцию:

Мы получили первых пять строк из VIEW, заметим, что для выборки данных из представления можно использовать любые доступные клаузулы и SQL операторы. Теперь давайте добавим в таблицу City город с населением тридцать миллионов:

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

Итак, мы видим, что добавив строку в таблицу, мы автоматически добавили строку в VIEW. Соответственно, если мы удалим строку из таблицы или, если мы ее модифицируем, то и строка VIEW будет изменена или удалена, это естественно и понятно. Но, как мы можем модифицировать значения непосредственно в представлении, ведь SQLite не дает возможность редактировать VIEW.

В этом нам помогут триггеры, с триггерами в SQL и базах данных SQLite мы разбирались в отдельной теме, можете с ней ознакомиться. Сейчас же попробуем применить триггеры для редактирования данных VIEW. Заметим, что для работы с представлениями в SQLite реализован специальный INSTEAD OF триггер, который можно реализовать для одной из трех команды манипуляции данными: UPDATE, INSERT и DELETE.

Мы сейчас рассмотрим INSTEAD OF триггер, который даст на возможность выполнять команду UPDATE VIEW. Общий синтаксис этого триггера выглядит следующим образом:

Давайте напишем триггер, который будет модифицировать данные представления vw_city:

Пример не самый удачный с точки зрения здравого смысла, намного удобнее было бы, если бы VIEW имела индексный столбец таблицы city, тогда условие WHERE было бы намного эффективное, но пример демонстрирует триггер, который редактирует данные представления.

Как видим, команда UPDATE была выполнена, как для представления, так и для таблицы, на основе которое была создана VIEW. По аналогии вы можете сделать триггеры, которые будет выполнять команды: INSERT VIEW и DELETE VIEW.

Временные представления в SQLite. TEMPORARY VIEW

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

Другими словами: временную VIEW видит и может с ней работать только тот пользователь, который ее создал, и временное представление будет удалено автоматически, как только пользователь создавший ее, прервет соединение с базой данных. Чтобы создать временное представление вы можете использовать команду CREATE TEMP VIEW или CREATE TEMPORARY VIEW.

Для удаления временного представления используйте команду DROP без слов TEMP или TEMPORARY, то есть для удаления временной VIEW не используйте: DROP TEMP VIEW или DROP TEMPORARY VIEW, это не сработает. Заметим, что имя временного представления также, как и имя обычного представления, должно быть уникально во всей базе данных. Других особенностей у временных представлений в базах данных SQLite3 нет.

Текст комментария: