5.4. Представления
изменить имя столбца в представлении, нам придется сначала удалить это представ- ление, а затем создать его заново.
DROP VIEW seats_by_fare_cond;
CREATE OR REPLACE VIEW seats_by_fare_cond
AS
SELECT aircraft_code,
fare_conditions,
count( * ) AS num_seats
FROM seats
GROUP BY aircraft_code, fare_conditions
ORDER BY aircraft_code, fare_conditions;
А вот и второй способ задания имен столбцов в представлении — с помощью списка их имен, заключенного в скобки:
DROP VIEW seats_by_fare_cond;
CREATE OR REPLACE VIEW seats_by_fare_cond ( code, fare_cond, num_seats )
AS
SELECT aircraft_code,
fare_conditions,
count( * )
FROM seats
GROUP BY aircraft_code, fare_conditions
ORDER BY aircraft_code, fare_conditions;
Представления позволяют облегчить развитие и модификацию базы данных, потому что они могут позволить сохранить интерфейс неизменным, но сам запрос, кото- рый лежит в основе конкретного представления, может измениться. При этом для прикладного программиста представление останется неизменным, поэтому не по- требуется переделывать запросы к этому представлению в прикладной программе.
В базе данных «Авиаперевозки» создано представление «Рейсы» (flights_v), скон- струированное на основе таблицы «Рейсы» (flights), но содержащее дополнитель- ную информацию, а именно:
– подробные сведения об аэропорте вылета
(departure_airport, departure_airport_name, departure_city);
– подробные сведения об аэропорте прибытия
(arrival_airport, arrival_airport_name, arrival_city);
125
Глава 5. Основы языка определения данных
– местное время вылета, как плановое, так и фактическое
(scheduled_departure_local, actual_departure_local);
– местное время прибытия, как плановое, так и фактическое
(scheduled_arrival_local, actual_arrival_local);
– продолжительность полета, как плановая, так и фактическая
(scheduled_duration, actual_duration).
Мы только опишем все столбцы представления, а SQL-команду для его создания при- ведем в главе 6.
Описание атрибута
Имя атрибута
Тип PostgreSQL
Идентификатор рейса flight_id integer
Номер рейса flight_no char(6)
Время вылета по расписанию scheduled_departure timestamptz
Время вылета по расписанию,
местное время в пункте отправления scheduled_departure_local timestamp
Время прилета по расписанию scheduled_arrival timestamptz
Время прилета по расписанию,
местное время в пункте прибытия scheduled_arrival_local timestamp
Планируемая продолжительность полета scheduled_duration interval
Код аэропорта отправления departure_airport char(3)
Название аэропорта отправления departure_airport_name text
Город отправления departure_city text
Код аэропорта прибытия arrival_airport char(3)
Название аэропорта прибытия arrival_airport_name text
Город прибытия arrival_city text
Статус рейса status varchar(20)
Код самолета, IATA
aircraft_code char(3)
Фактическое время вылета actual_departure timestamptz
Фактическое время вылета,
местное время в пункте отправления actual_departure_local timestamp
Фактическое время прилета actual_arrival timestamptz
Фактическое время прилета,
местное время в пункте прибытия actual_arrival_local timestamp
Фактическая продолжительность полета actual_duration interval
Известно, что в сфере железнодорожных пассажирских перевозок время в расписа- нии движения поездов и в билетах указывается московское. А в пассажирских авиа-
126
5.4. Представления
перевозках, напротив, время в билетах указывается местное. Это касается и времени вылета и времени прилета. Если пункты отправления и назначения находятся в раз- личных часовых поясах, то время вылета будет привязано к одному часовому поясу,
а время прилета — к другому.
Поэтому в нашем представлении «Рейсы» (flights_v) предусмотрены четыре столб- ца, отображающие местное время: два из них относятся к пункту отправления —
scheduled_departure_local и actual_departure_local, а два других относят- ся к пункту прибытия — scheduled_arrival_local и actual_arrival_local.
В качестве типа данных для этих четырех столбцов выбран тип timestamp without time zone (сокращенно — просто timestamp), а не timestamp with time zone
(timestamptz). Причина в том, что при выборе timestamptz время автоматически преобразовывалось бы при выводе данных к текущему часовому поясу, установлен- ному на компьютере пользователя, а нам нужно сохранить его значения такими, ка- кими они являются в пункте отправления и пункте назначения.
Для перевода значения типа timestamptz (с часовым поясом) в значение типа timestamp (без часового пояса) служит конструкция AT TIME ZONE, подробно рас- смотренная в разделе 9.9 «Операторы и функции даты/времени» документации. Так- же существует и эквивалентная функция timezone, которая и используется здесь для пересчета московского времени в местное.
Если вы испытываете затруднения с пониманием операций преобразования значе- ний типа timestamptz в значения типа timestamp, рекомендуем вам обратиться к разделу документации 8.5.1.3 «Даты и время».
Посмотреть описание представления в базе данных можно с помощью команды
\d flights_v
В представлении «Рейсы» много столбцов, поэтому при выводе информации из него в виде таблицы каждая строка на экране будет сворачиваться «змейкой», что не очень наглядно. Утилита psql предлагает альтернативный — расширенный — способ вывода информации, который включается с помощью команды
\x
Для возвращения к табличному формату вывода нужно выполнить эту же команду еще раз.
Включив расширенный вывод, выполните команду для выборки данных из представ- ления «Рейсы».
127
Глава 5. Основы языка определения данных
SELECT * FROM flights_v;
-[ RECORD 1 ]--------------+------------------------- flight_id
| 1
flight_no
| PG0405
scheduled_departure
| 2016-09-13 13:35:00+08
scheduled_departure_local | 2016-09-13 08:35:00
scheduled_arrival
| 2016-09-13 14:30:00+08
scheduled_arrival_local
| 2016-09-13 09:30:00
scheduled_duration
| 00:55:00
departure_airport
| DME
departure_airport_name
| Домодедово departure_city
| Москва arrival_airport
| LED
arrival_airport_name
| Пулково arrival_city
| Санкт-Петербург status
| Arrived aircraft_code
| 321
actual_departure
| 2016-09-13 13:44:00+08
actual_departure_local
| 2016-09-13 08:44:00
actual_arrival
| 2016-09-13 14:39:00+08
actual_arrival_local
| 2016-09-13 09:39:00
actual_duration
| 00:55:00
Бывают ситуации, когда заранее известно, что возможна попытка удаления несуще- ствующего представления. В таких случаях обычно стараются избежать ненужных сообщений об ошибке отсутствия представления. Для этого в команду DROP VIEW до- бавляют фразу IF EXISTS. Например:
DROP VIEW IF EXISTS flights_v;
Как мы уже говорили ранее, представление является фактически сохраненным за- просом к базе данных. Этот запрос получает имя, которым можно воспользоваться в предложении FROM команды SELECT для получения результатов этого запроса.
PostgreSQL предлагает свое расширение — так называемое материализованное пред- ставление. Упрощенный синтаксис команды CREATE MATERIALIZED VIEW, предна- значенной для создания материализованных представлений, таков:
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] имя-мат-представления
[ ( имя-столбца [, ...] ) ]
AS запрос
[ WITH [ NO ] DATA ];
128
5.4. Представления
В момент выполнения команды создания материализованного представления оно заполняется данными, но только если в команде не было фразы WITH NO DATA. Ес- ли же она была включена в команду, тогда в момент своего создания представле- ние остается пустым, а для заполнения его данными нужно использовать команду
REFRESH MATERIALIZED VIEW.
Материализованное представление очень похоже на обычную таблицу. Однако оно отличается от таблицы тем, что не только сохраняет данные, но также запоминает запрос, с помощью которого эти данные были собраны.
В нашей учебной базе данных «Авиаперевозки» имеется материализованное пред- ставление — «Маршруты» (routes). Как вы могли заметить, таблица «Рейсы» содер- жит избыточность: для одного и того же номера рейса, отправляющегося в различные дни, повторяются коды аэропортов отправления и назначения, а также код самоле- та. Таким образом, из этой таблицы можно извлечь информацию о маршруте, т. е.
номер рейса, аэропорты отправления и назначения. Эта информация не зависит от конкретной даты вылета.
Опишем все столбцы представления «Маршруты», а SQL-команду для его создания приведем в главе 6.
Описание атрибута
Имя атрибута
Тип PostgreSQL
Номер рейса flight_no char(6)
Код аэропорта отправления departure_airport char(3)
Название аэропорта отправления departure_airport_name text
Город отправления departure_city text
Код аэропорта прибытия arrival_airport char(3)
Название аэропорта прибытия arrival_airport_name text
Город прибытия arrival_city text
Код самолета, IATA
aircraft_code char(3)
Продолжительность полета duration interval
Дни недели, когда выполняются рейсы days_of_week integer[ ]
Обратите внимание на тип данных последнего столбца — «Дни недели, когда выпол- няются рейсы». Это массив целых чисел.
Если впоследствии вам потребуется обновить данные в материализованном пред- ставлении, то выполните команду
REFRESH MATERIALIZED VIEW routes;
129
Глава 5. Основы языка определения данных
Кончено, как и любой другой объект базы данных, материализованное представле- ние можно удалить.
DROP MATERIALIZED VIEW routes;
Подводя итог раздела, назовем положительные стороны использования представле- ний.
1. Упрощение разграничения полномочий пользователей на доступ к хранимым данным.
Разным типам пользователей могут требоваться различные данные, хранящие- ся в одних и тех же таблицах. Это касается как столбцов, так и строк таблиц. Со- здание различных представлений для разных пользователей избавляет от необ- ходимости создавать дополнительные таблицы, дублируя данные, и упрощает организацию системы управления доступом к данным.
2. Упрощение запросов к базе данных.
Запросы к базе данных могут включать несколько таблиц и быть весьма слож- ными и громоздкими, при этом такие запросы могут выполняться часто. Ис- пользование представлений позволяет скрыть эти сложности от прикладного программиста и сделать запросы более простыми и наглядными.
3. Снижение зависимости прикладных программ от изменений структуры таблиц базы данных.
В процессе развития информационной системы структура таблиц базы данных может изменяться. Столбцы представления, т. е. их имена, типы данных и по- рядок следования, — это, образно говоря, интерфейс к запросу, который реа- лизуется данным представлением. Если этот интерфейс остается неизменным,
то SQL-запросы, в которых используется данное представление, корректиро- вать не потребуется. Нужно будет лишь в ответ на изменение структуры базовых таблиц, на основе которых представление сконструировано, соответствующим образом перестроить запрос, выполняемый данным представлением.
4. Снижение времени выполнения сложных запросов за счет использования мате- риализованных представлений.
В материализованных представлениях можно сохранять результаты выполне- ния запросов, которые формируются длительное время, но при этом допускают их формирование заранее, а не обязательно в момент возникновения потребно- сти в результатах этого запроса. Если, например, какой-нибудь сводный отчет формируется длительное время, а запросы к отчету будут неоднократными, то
130
5.5. Схемы базы данных
может оказаться целесообразным сформировать его заранее и сохранить в ма- териализованном представлении.
Тем не менее нужно учитывать, что применимость материализованных пред- ставлений весьма ограничена. Не следует заменять ими все сложные запросы.
Одним из недостатков является то, что их необходимо своевременно обновлять с помощью команды REFRESH, чтобы они содержали актуальные данные.
5.5. Схемы базы данных
Схема — это логический фрагмент базы данных, в котором могут содержаться раз- личные объекты: таблицы, представления, индексы и др. В базе данных обязательно есть хотя бы одна схема. При создании базы данных в ней автоматически создается схема с именем public. Когда мы с вами создавали таблицы в базе данных edu, они создавались именно в этой схеме.
В каждой базе данных может содержаться более одной схемы. Их имена должны быть уникальными в пределах конкретной базы данных. Имена объектов базы дан- ных (таблиц, представлений, последовательностей и др.) должны быть уникальными в пределах конкретной схемы, но в разных схемах имена объектов могут повторять- ся. Таким образом, можно сказать, что схема образует так называемое пространство
имен
Посмотреть список схем в базе данных можно так:
\dn
Список схем
Имя
| Владелец
----------+---------- bookings | postgres public
| postgres
(2 строки)
В учебной базе данных demo есть схема bookings. Все таблицы созданы именно в этой схеме. Для организации доступа к ней вы уже выполняли команду
SET search_path = bookings;
131
Глава 5. Основы языка определения данных
Теперь объясним подробнее, что эта команда делает.
Если в базе данных создано более одной схемы, то доступ к объектам, содержащимся в конкретной схеме, можно организовать разными способами. Первый заключается в том, чтобы имена объектов предварять именем схемы. Например, для обращения к таблице aircrafts нужно сделать так:
SELECT * FROM bookings.aircrafts;
Однако такой способ не очень удобен. Другой способ заключается в том, чтобы одну из схем сделать текущей. Среди параметров времени исполнения, которые преду- смотрены в конфигурации сервера PostgreSQL, есть параметр search_path. Его зна- чение по умолчанию можно изменить в конфигурационном файле postgresql.conf. Он содержит имена схем, которые PostgreSQL просматривает при поиске конкретного объекта базы данных, когда имя схемы в команде не указано. Посмотреть значение этого параметра можно с помощью команды SHOW:
SHOW search_path;
search_path
-----------------
"$user", public
(1 строка)
Схема "$user" присутствует в этом параметре на тот случай, если будут созданы схе- мы с именами, совпадающими с именами пользователей. Тогда могут упроститься некоторые операции с базой данных. Однако в базе данных demo нет таких схем, по- этому первый элемент параметра search_path фактически не участвует в работе,
в результате все обращения к объектам базы данных без указания имени схемы бу- дут адресоваться схеме public.
Чтобы изменить порядок просмотра схем при поиске объектов в базе данных, нуж- но воспользоваться командой SET. При этом первой в списке схем следует указать именно ту, которую СУБД должна просматривать первой. Эта схема и станет теку- щей. Конечно, такой список может состоять и всего из одной схемы.
Давайте выполним команду
SET search_path = bookings;
А теперь посмотрим, что получилось:
SHOW search_path;
132
Контрольные вопросы и задания
search_path
------------- bookings
(1 строка)
Да, действительно, теперь первой будет просматриваться схема bookings. А для об- ращения к объектам, например, таблицам, в схеме public (если бы они в ней были)
нам пришлось бы указывать имя схемы public перед именами этих объектов. Ес- ли бы мы решили добавить схему public в список просматриваемых схем, то нужно было бы включить ее в команду SET:
SET search_path = bookings, public;
Узнать имя текущей схемы можно с помощью встроенной функции current_schema
(обратите внимание на отсутствие скобок при вызове функции в команде SELECT).
SELECT current_schema;
current_schema
---------------- bookings
(1 строка)
При создании объектов базы данных, например таблиц, необходимо учитывать сле- дующее: если имя схемы в команде не указано, то объект будет создан в текущей схеме. Если же вы хотите создать объект в конкретной схеме, которая не является текущей, то нужно указать ее имя перед именем создаваемого объекта, разделив их точкой. Например, для создания таблицы airports в схеме my_schema следует сде- лать так:
CREATE TABLE my_schema.airports
...
Контрольные вопросы и задания
1. При использовании значений по умолчанию с ключевым словом DEFAULT воз- можны и ситуации, когда типичным будет не конкретное значение данных,
а способ его получения. Например, если мы захотим фиксировать в каждой строке таблицы «Студенты» имя пользователя базы данных, добавившего эту строку в таблицу, тогда необходимо в определение таблицы добавить еще один
133
Глава 5. Основы языка определения данных
столбец. Этот столбец по умолчанию будет получать значение, возвращаемое функцией current_user.
CREATE TABLE students
( record_book numeric( 5 ) NOT NULL,
name text NOT NULL,
doc_ser numeric( 4 ),
doc_num numeric( 6 ),
who_adds_row text DEFAULT current_user, -- добавленный столбец
PRIMARY KEY ( record_book )
);
Эта функция — current_user — будет вызываться не при создании таблицы,
а при вставке каждой строки. При этом в команде INSERT не требуется указы- вать значение для столбца who_adds_row, поскольку функция current_user будет вызываться самой СУБД PostgreSQL:
INSERT INTO students ( record_book, name, doc_ser, doc_num )
VALUES ( 12300, 'Иванов Иван Иванович', 0402, 543281 );
Давайте пойдем дальше и пожелаем фиксировать не только имя пользователя базы данных, добавившего строку в таблицу, но также и момент времени, когда это было сделано. Самостоятельно внесите модификацию в определение табли- цы students для решения этой задачи, а затем выполните команду INSERT для проверки полученного решения.
Если до выполнения этого упражнения вы еще не ознакомились с командой
ALTER TABLE, то вместо модифицирования определения таблицы сначала уда- лите ее, а затем создайте заново:
DROP TABLE students;
CREATE TABLE students ...
2. Посмотрите, какие ограничения уже наложены на атрибуты таблицы «Успевае- мость» (progress). Воспользуйтесь командой \d утилиты psql. А теперь пред- ложите для этой таблицы ограничение уровня таблицы.
В качестве примера рассмотрим такой вариант. Добавьте в таблицу progress еще один атрибут — «Форма проверки знаний» (test_form), который может принимать только два значения: «экзамен» или «зачет». Тогда набор допусти- мых значений атрибута «Оценка» (mark) будет зависеть от того, экзамен или за- чет предусмотрены по данной дисциплине. Если предусмотрен экзамен, тогда допускаются значения 3, 4, 5, если зачет — тогда 0 (не зачтено) или 1 (зачтено).
134