Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
ALTER TABLE seats ALTER COLUMN fare_conditions SET DATA TYPE integer USING ( CASE WHEN fare_conditions = 'Economy' THEN 1 WHEN fare_conditions = 'Business' THEN 2 ELSE 3 END ); 90 Для замены исходных значений на новые мы используем конструкцию CASE WHEN ... THEN ... ELSE ... END. Выполнить операцию не удастся, СУБД выдаст сообщение об ошибке: ОШИБКА: ограничение-проверку "seats_fare_conditions_check" нарушает некоторая строка ,→ И в самом деле, в определении таблицы есть ограничение CHECK, которое требует, чтобы значение столбца fare_conditions выбиралось из списка: «Economy», «Comfort», «Business». При замене символьных значений на числовые это ограничение будет за- ведомо нарушаться. Следовательно, необходимо в команду ALTER TABLE добавить операцию удаления этого ограничения. Пробуем новый вариант команды: ALTER TABLE seats DROP CONSTRAINT seats_fare_conditions_check, ALTER COLUMN fare_conditions SET DATA TYPE integer USING ( CASE WHEN fare_conditions = 'Economy' THEN 1 WHEN fare_conditions = 'Business' THEN 2 ELSE 3 END ); Проверим результат работы с помощью команды SELECT * FROM seats; aircraft_code | seat_no | fare_conditions ---------------+---------+----------------- 319 | 2A | 2 319 | 2C | 2 319 | 2D | 2 Теперь мы видим, что необходимо связать таблицы «Места» (seats) и «Классы обслу- живания» (fare_conditions) по внешнему ключу. Сделаем это: ALTER TABLE seats ADD FOREIGN KEY ( fare_conditions ) REFERENCES fare_conditions ( fare_conditions_code ); Посмотрев описание таблицы «Места» (seats), увидим, что внешний ключ успешно создан. \d seats "seats_fare_conditions_fkey" FOREIGN KEY (fare_conditions) REFERENCES fare_conditions(fare_conditions_code) Из теории известно, что атрибуты внешнего ключа не обязательно должны ссылать- ся только на одноименные атрибуты ссылочной таблицы. Сейчас мы на практике успешно проверили это утверждение. Однако для удобства сопровождения базы дан- ных имеет смысл переименовать столбец fare_conditions в таблице «Места» (seats), т. е. дать ему имя fare_conditions_code, поскольку в этой таблице хранится именно код класса обслуживания. Давайте так и поступим: 91 ALTER TABLE seats RENAME COLUMN fare_conditions TO fare_conditions_code; Если теперь посмотреть описание таблицы, то можно увидеть, что имя ат- рибута, являющегося внешним ключом, изменилось, а вот имя ограничения seats_fare_conditions_fkey осталось неизменным, хотя оно и было первоначально сформировано самой СУБД. Это шаблонное имя ограничения составляется из име- ни таблицы и имени первого (и единственного в данном случае) атрибута внешнего ключа. "seats_fare_conditions_fkey" FOREIGN KEY (fare_conditions_code) REFERENCES fare_conditions(fare_conditions_code) ,→ Давайте переименуем это ограничение, чтобы поддержать соблюдение правила име- нования ограничений: ALTER TABLE seats RENAME CONSTRAINT seats_fare_conditions_fkey TO seats_fare_conditions_code_fkey; Как всегда, проверим, что получилось: \d seats И в заключение этого параграфа вернемся к таблице «Классы обслуживания» (fare_conditions). Мы предусмотрели в ней первичный ключ, но ведь значения атрибу- та «Наименование класса обслуживания» (fare_conditions_name) также должны быть уникальными, дублирование значений не допускается. Давайте добавим ограниче- ние уникальности по этому столбцу: ALTER TABLE fare_conditions ADD UNIQUE ( fare_conditions_name ); И как всегда, на всякий случай проверим, что получилось: \d fare_conditions 5.4 Представления При работе с базами данных зачастую приходится многократно выполнять одни и те же запросы, которые могут быть весьма сложными и требовать обращения к несколь- ким таблицам. Чтобы избежать необходимости многократного формирования таких запросов, можно использовать так называемые представления (views). Если речь идет о выборке данных, то представления практически неотличимы от таблиц с точки зре- ния обращения к ним в командах SELECT. Упрощенный синтаксис команды CREATE VIEW, предназначенной для создания представлений, таков: CREATE VIEW name [ ( column_name [, ...] ) ] AS query; 92 В этой команде обязательными элементами являются имя представления и запрос к базе данных, который и формирует выборку из нее. Если список имен столбцов не приведен, тогда их имена «вычисляются» (формируются) на основании текста запро- са. Давайте создадим простое представление. В главе 3 мы решали задачу: подсчитать количество мест в салонах для всех моделей самолетов с учетом класса обслуживания (бизнес-класс и экономический класс). Запрос был таким: SELECT aircraft_code, fare_conditions, count( * ) FROM seats GROUP BY aircraft_code, fare_conditions ORDER BY aircraft_code, fare_conditions; На его основе создадим представление и дадим ему имя, отражающее суть этого представления. CREATE VIEW seats_by_fare_cond AS SELECT aircraft_code, fare_conditions, count( * ) FROM seats GROUP BY aircraft_code, fare_conditions ORDER BY aircraft_code, fare_conditions; Теперь мы можем вместо написания сложного первоначального запроса обращаться непосредственно к представлению, как будто это обычная таблица. SELECT * FROM seats_by_fare_cond; В отличие от таблиц, представления не содержат данных. Данные выбираются из таб- лиц, на основе которых представление создано, при каждом обращении к нему в ко- манде SELECT. СУБД PostgreSQL предлагает свое расширение команды CREATE VIEW, а именно: фра- зу OR REPLACE. Если представление уже существует, то можно его не удалять, а про- сто заменить новой версией. Однако нужно помнить о том, что при создании новой версии представления (без явного удаления старой с помощью команды DROP VIEW) должны оставаться неизменными имена столбцов представления. Если же вы хотите изменить имя хотя бы одного столбца, то сначала нужно удалить представление с по- мощью команды DROP VIEW, а уже затем создать его заново. Имена столбцов можно явно указать в команде, но если они не указаны, то СУБД сама «вычислит» эти име- на. В только что созданном нами представлении третий столбец получит имя count. Если мы захотим изменить это имя, то возможны два способа: первый заключается в том, чтобы создать псевдоним для этого столбца с помощью ключевого слова AS, а второй — в использовании списка имен столбцов в начале команды CREATE VIEW. Попробуем воспользоваться первым способом (обратите внимание на добавление фразы OR REPLACE и ключевого слова AS после вызова функции count): CREATE OR REPLACE VIEW seats_by_fare_cond AS SELECT a.model, s.aircraft_code, s.fare_conditions, count( * ) AS num_seats FROM seats GROUP BY aircraft_code, fare_conditions ORDER BY aircraft_code, fare_conditions; 93 Однако СУБД выдаст сообщение об ошибке: ОШИБКА: изменить имя столбца "count" на "num_seats" в представлении нельзя ,→ В чем дело? А дело в том, что при первоначальном создании этого представления третий столбец уже получил имя count (такое имя ему дала СУБД). Поэтому если мы хотим воспользоваться вариантом команды с фразой OR REPLACE, мы не должны из- менять названия столбцов ни путем указания псевдонимов, ни с помощью списка имен столбцов, приводимого в начале команды. Так что если мы все же захотим из- менить имя столбца в представлении, нам придется сначала удалить это представле- ние, а затем создать его заново. DROP VIEW seats_by_fare_cond; CREATE OR REPLACE VIEW seats_by_fare_cond AS SELECT a.model, s.aircraft_code, s.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); – местное время вылета, как плановое, так и фактическое (scheduled_departure_local, actual_departure_local); – местное время прибытия, как плановое, так и фактическое (scheduled_arrival_local, actual_arrival_local); 94 – продолжительность полета, как плановая, так и фактическая (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 Известно, что в сфере железнодорожных пассажирских перевозок время в расписа- нии движения поездов и в билетах указывается московское. А в пассажирских авиа- перевозках, напротив, время в билетах указывается местное. Это касается и времени вылета и времени прилета. Если пункты отправления и назначения находятся в раз- личных часовых поясах, то время вылета будет привязано к одному часовому поясу, а время прилета — к другому. Поэтому в нашем представлении «Рейсы» (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 время автоматически преобразовывалось бы при выводе дан- ных к текущему часовому поясу, установленному на компьютере пользователя, а нам нужно сохранить его значения такими, какими они являются в пункте отправления и пункте назначения. 95 Для перевода значения типа timestamptz в значение типа timestamp служит конструк- ция AT TIME ZONE, подробно рассмотренная в разделе документации 9.9 «Операто- ры и функции даты/времени». Также существует и эквивалентная функция timezone, которая и используется здесь для пересчета московского времени в местное. Если вы испытываете затруднения с пониманием операций преобразования значе- ний типа timestamptz в значения типа timestamp, рекомендуем вам обратиться к раз- делу документации 8.5.1.3 «Даты и время». Посмотреть описание представления в базе данных можно с помощью команды \d flights_v В представлении «Рейсы» (flights_v) много столбцов, поэтому при выводе информа- ции из него в виде таблицы каждая строка на экране будет сворачиваться «змейкой», что не очень наглядно. Утилита psql предлагает альтернативный — расширенный — способ вывода информации, который включается с помощью команды \x Для возвращения к табличному формату вывода нужно выполнить эту же команду еще раз. Включив расширенный вывод, выполните команду для выборки данных из представ- ления «Рейсы» (flights_v): 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... 96 Бывают ситуации, когда заранее известно, что возможна попытка удаления несуще- ствующего представления. В таких случаях обычно стараются избежать ненужных со- общений об ошибке отсутствия представления. Делается это путем добавления в ко- манду DROP VIEW фразы IF EXISTS. Например: DROP VIEW IF EXISTS flights_v; Как мы уже говорили ранее, представление является фактически сохраненным за- просом к базе данных. Этот запрос получает имя, которым можно впоследствии вос- пользоваться в предложении FROM команды SELECT для получения результатов это- го запроса. PostgreSQL предлагает свое расширение — так называемое материализованное пред- ставление. Упрощенный синтаксис команды CREATE MATERIALIZED VIEW, предна- значенной для создания материализованных представлений, таков: CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name [ (column_name [, ...] ) ] AS query [ WITH [ NO ] DATA ]; Материализованное представление заполняется данными в момент выполнения ко- манды для его создания, если только в команде не было фразы WITH NO DATA. Если же она была включена в команду, тогда в момент своего создания представление дан- ными не заполняется, а для заполнения его данными нужно использовать команду REFRESH MATERIALIZED VIEW. Материализованное представление очень похоже на обычную таблицу. Однако оно отличается от таблицы тем, что не только сохраняет данные, но также запоминает запрос, с помощью которого эти данные были собраны. В нашей учебной базе данных «Авиаперевозки» имеется материализованное пред- ставление — «Маршруты» (routes). Как вы могли заметить, таблица «Рейсы» (flights) содержит избыточность: для одного и того же номера рейса, отправляющегося в раз- личные дни, повторяются коды аэропортов отправления и назначения, а также код самолета. Таким образом, из этой таблицы можно извлечь информацию о маршруте, т. е. номер рейса, аэропорты отправления и назначения. Эта информация не зависит от конкретной даты вылета. Опишем все столбцы представления, а 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[] 97 Обратите внимание на тип данных последнего столбца — «Дни недели, когда выпол- няются рейсы». Это массив целых чисел. Если впоследствии вам потребуется обновить данные в материализованном пред- ставлении, то выполните команду REFRESH MATERIALIZED VIEW routes; Кончено, как и любой другой объект базы данных, материализованное представле- ние можно удалить. DROP MATERIALIZED VIEW routes; Подводя итог параграфа, назовем положительные стороны использования представ- лений. 1. Упрощение разграничения полномочий пользователей на доступ к хранимым данным. Разным типам пользователей могут требоваться различные данные, хранящие- ся в одних и тех же таблицах. Это касается как столбцов, так и строк таблиц. Со- здание различных представлений для разных пользователей избавляет от необ- ходимости создавать дополнительные таблицы, дублируя данные, и упрощает организацию системы управления доступом к данным. 2. Упрощение запросов к базе данных. Запросы к базе данных могут включать несколько таблиц и быть весьма слож- ными и громоздкими, при этом такие запросы могут выполняться часто. Ис- пользование представлений позволяет скрыть эти сложности от прикладного программиста и сделать запросы более простыми и наглядными. 3. Снижение зависимости прикладных программ от изменений структуры таблиц базы данных. В процессе развития информационной системы структура таблиц базы данных может изменяться. Столбцы представления, т. е. их имена, типы данных и по- рядок следования, — это, образно говоря, интерфейс к запросу, который реали- зуется данным представлением. Если этот интерфейс остается неизменным, то SQL-запросы, в которых используется данное представление, корректировать не потребуется. Нужно будет лишь в ответ на изменение структуры базовых таб- лиц, на основе которых представление сконструировано, соответствующим об- разом перестроить запрос, выполняемый данным представлением. 4. Снижение времени выполнения сложных запросов за счет использования мате- риализованных представлений. В материализованных представлениях можно сохранять результаты выполне- ния запросов, которые формируются длительное время, но при этом допускают их формирование заранее, а не обязательно в момент возникновения потреб- ности в результатах этого запроса. Если, например, какой-нибудь сводный от- чет формируется длительное время, а запросы к отчету будут неоднократными, то может оказаться целесообразным сформировать его заранее и сохранить в материализованном представлении. 98 Тем не менее, нужно учитывать, что применимость материализованных пред- ставлений весьма ограничена. Не следует заменять ими все сложные запросы. Одним из их недостатков является то, что их необходимо своевременно обнов- лять с помощью команды REFRESH, чтобы они содержали актуальные данные. |