Файл: Лабораторные работы по курсу Базы данных Введение.pdf

Пример 6.1:
Создание отношения группы:
CREATE TABLE "group"
(
"name" text,
num integer,
"Fio" text
);
Ограничения в таблицах
Ранее нами уже были рассмотрены некоторые ограничения, используемые в среде MS Access. Теперь рассмотрим ограничения, использование которых предоставляет нам PostgreSQL.
Ограничение (constraint) представляет собой особый атрибут таблицы, который устанавливает критерии допустимости для содержимого ее полей.
Соблюдение этих правил помогает предотвратить заполнение базы ошибочными или неподходящими данными.
Формат установки ограничений следующий:
[ CONSTRAINT ограничение ]
{ NOT NULL | UNIQUE | PRIMARY KEY | DEFAULT значение | CHECK (
условие ) |
REFERENCES таблица [ ( поле ) ]
[ MATCH FULL | MATCH PARTIAL ]
[ ON DELETE операция ]
[ ON UPDATE операция ]
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] }
Определение следует в команде CREATE TABLE сразу же за типом ограничиваемого поля и предшествует запятой, отделяющей его от следующего поля. Ограничения могут устанавливаться для любого количества полей, а ключевое слово CONSTRAINT и идентификатор ограничение не обязательны.
Существует шесть типов ограничений полей, задаваемых при помощи специальных ключевых слов. Некоторые из них косвенно устанавливаются при создании ограничений другого типа. Некоторые из типов ограничений полей перечислены ниже (более подробно [6]):
• NOT NULL. Поле не может содержать псевдозначение NULL.
Ограничение NOT NULL эквивалентно ограничению CHECK (поле NOT
NULL).
• UNIQUE. Поле не может содержать повторяющиеся значения.
Следует учитывать, что ограничение UNIQUE допускает многократное вхождение псевдозначений NULL, поскольку формально NULL не совпадает ни с каким другим значением.

• PRIMARY KEY. Автоматически устанавливает ограничения UNIQUE и NOT NULL, а для заданного поля создается индекс. В таблице может устанавливаться только одно ограничение первичного ключа.
• DEFAULT значение. Пропущенные значения поля заменяются заданной величиной. Значение по умолчанию должно относиться к типу данных, соответствующему типу поля. В PostgreSQL 7.1.х значение по умолчанию не может задаваться при помощи подзапроса.
• CHECK условие. Команда INSERT или UPDATE для записи завершается успешно лишь при выполнении заданного условия (выражения, возвращающего логический результат). При установке ограничения поля в секции CHECK может использоваться только поле, для которого устанавливается ограничение.
• REFERENCES. Это ограничение состоит из нескольких секций, которые перечислены ниже. о REFERENCES таблица [ ( поле ) ]. Входные значения ограничиваемого поля сравниваются со значениями другого поля в заданной таблице. Если совпадения отсутствуют, команда INSERT или
UPDATE завершается неудачей. Если параметр поле не указан, проверка выполняется по первичному ключу. Ограничение
REFERENCES похоже на ограничение таблицы FOREIGN KEY, описанное в следующем пункте этого подраздела. Действительно, между этими ограничениями есть много общего. о ON DELETE операция. При выполнении команды DELETE для заданной таблицы с ограничиваемым полем выполняется одна из следующих операций: NО ACTION (если удаление приводит к нарушению целостности ссылок, происходит ошибка; используется по умолчанию, если операция не указана), RESTRICT (аналогично NО
ACTION), CASCADE (удаление всех записей, содержащих ссылки на удаляемую запись), SET NULL (поля, содержащие ссылки на удаляемую запись, заменяются псевдозначениями NULL), SET
DEFAULT (полям, содержащим ссылки на удаляемую запись, присваивается значение по умолчанию). о ON UPDATE операция. При выполнении команды UPDATE для заданной таблицы выполняется одна из операций, описанных выше. По умолчанию используется значение NО ACTION. Если выбрана операция CASCADE, все записи, содержащие ссылки на обновляемую запись, обновляются новым значением (вместо удаления, как в случае с
ON DELETE CASCADE).
В ограничениях таблиц, в отличие от ограничений полей, могут участвовать сразу несколько полей таблицы. Синтаксис ограничения таблицы:
[ CONSTRAINT ограничение ]
{ UNIQUE ( поле [....]) |
PRIMARY KEY ( поле [. ...] ) |
CHECK ( условие ) |

FOREIGN KEY ( поле [. ... ] )
REFERENCES таблица [ ( поле [. ] ) ]
[ MATCH FULL | MATCH PARTIAL ]
[ ON DELETE операция ]
[ ON UPDATE операция ]
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
Секция CONSTRAINT ограничение определяет необязательное имя.
Ограничениям рекомендуется присваивать содержательные имена вместо автоматически сгенерированных имен, не несущих никакой полезной информации. В будущем имя также может пригодиться и для удаления ограничения.
В примере 6.2 первое ограничение, pkey, относится к типу PRIMARY
KEY и устанавливается для таблицы по полю isbn. Оно практически не отличается от ограничения PRIMARY KEY для поля, поскольку список в данном примере состоит всего из одного поля.
Пример 6.2:
Использование ограниченной таблицы:
CREATE TABLE editions
(isbn text, bookid integer, edition integer, publisherid integer, publication
date, type char,
CONSTRAINT pkey PRIMARY KEY (isbn),
CONSTRAINT integrity CHECK (bookid IS NOT NULL
AND edition IS NOT NULL).
CONSTRAINT book_exists FOREIGN KEY (book_id)
REFERENCES books (id)
ON DELETE CASCADE
ON UPDATE CASCADE):
Ограничение integrity гарантирует, что поля book_id и edition не содержат псевдозначения NULL.
Наконец, ограничение book_exists при помощи конструкций FOREIGN
KEY и REFERENCES гарантирует, что значение поля book_id встречается в поле id таблицы books. Более того, поскольку в секциях ON DELETE и ON
ACTION встречается ключевое слово CASCADE, любые модификации поля id в таблице books приведут к каскадным изменениям записей в таблице editions, а при удалении записей из таблицы books будут удалены соответствующие записи таблицы editions.
Для этих ограничений в базе данных автоматически строится индекс editions_pkey по полю isbn, а также создается триггер. Индекс обеспечивает выполнение ограничения PRIMARY KEY, а триггер относится к ограничению FOREIGN KEY.

Создание объектно-реляционных связей
В PostgreSQL поддерживается механизм создания объектно- реляционных связей, называемый наследованием. Таблица может наследовать некоторые атрибуты своих полей от одной или нескольких других таблиц, что приводит к созданию отношений типа «предок—
потомок». В результате производные таблицы («потомки») обладают теми же полями и ограничениями, что и их базовые таблицы («предки»), а также дополняются собственными полями.
При составлении запроса к базовой таблице можно потребовать, чтобы запрос произвел выборку только из самой таблицы или же просмотрел как таблицу, так и ее производные таблицы. С другой стороны, в результаты запроса к производной таблице никогда не включаются записи из базовой таблицы.
Производная таблица создается командой SQL CREATE TABLE, в которую включается секция INHERITS. Секция состоит из ключевого слова
INHERITS и имени базовой таблицы (или нескольких таблиц).
CREATE TABLE производная_таблица определение
INHERITS ( базовая_таблица [, ...] )
В этом определении производная_таблица — имя создаваемой таблицы, определение — полное определение таблицы со всеми стандартными секциями команды CREATE TABLE, а базовая_таблица — таблица, структура которой наследуется новой таблицей. Дополнительные имена базовых таблиц перечисляются через запятую.
Пример 6.3:
Создание наследования для таблицы B от таблицы A:
CREATE TABLE b (b_text text)
INHERITS (a);
Связь общих полей базовой и производной таблиц не ограничивается чисто косметическими удобствами. Данные, занесенные в производную таблицу, присутствуют и в родительской таблице. Впрочем, в родительской таблице видны только три унаследованных поля. В запрос к базовой таблице можно включить ключевое слово ONLY, которое указывает, что данные производных таблиц исключаются из результатов запроса.
Следует хорошо понимать, что данные в действительности не
заносятся в базовую таблицу, а лишь становятся видимыми в ней
благодаря отношению наследования.
Наследование может приводить к видимому нарушению ограничений.
Например, значение поля, для которого установлено ограничение уникальности, может повторяться в данных производных таблиц.
Применение наследования требует осторожности, поскольку производная таблица формально не нарушает ограничений, хотя при выборке из базовой таблицы без ключевого слова ONLY может показаться обратное.

Массивы
Поля данных PostgreSQL вместо отдельных величин могут содержать конструкции, называемые массивами. Массив сам по себе не является самостоятельным типом данных, а лишь расширяет любой другой тип данных PostgreSQL.
Чтобы создать простейшее поле-массив, включите в команду CREATE
TABLE пару квадратных скобок после имени поля. Квадратные скобки показывают, что вместо одного значения в поле может храниться массив указанного типа. Дополнительные квадратные скобки определяют многомерные массивы, то есть «массивы массивов».
Пример 6.4:
Создание таблицы с полем-массивом:
CREATE TABLE favorite_books (employee_id integer,
books text[]);
В PostgreSQL предусмотрен специальный синтаксис вставки нескольких значений в одно поле. Этот синтаксис основан на определении массивов-констант.
Обобщенная форма массива-константы выглядит так:
'{ "текст" [, ...] }'
-- массив строк
'{ число [, ...]}'
-- числовой массив
В этих примерах использованы строковые и числовые массивы, но поле может определяться как массив произвольного типа (включая типы boolean, date и time). Как правило, если для описания величины в скалярном контексте должны использоваться апострофы (например, в строковых константах или данных типа timestamp), в контексте массива эта величина заключается в кавычки. Даже при вставке одного элемента массив заключается в фигурные скобки.
Пример 6.5:
Вставка с использованием массивов-констант:
INSERT INTO favorite_books VALUES
(102, '{"The Hitchhiker\'s Guide to the Galaxy11”}');
INSERT INTO favorite_books VALUES
(103, '{"The Hobbit", "Kitten. Squared"}');
При выборке из поля-массива весь массив возвращается в формате константы.
Популярность массивов в значительной степени обусловлена тем фактом, что к отдельным элементам можно обращаться при помощи индексов — целых чисел, заключенных в скобки и описывающих позицию искомого элемента в массиве. В отличие от таких языков программирования, как С, в PostgreSQL индексация в массивах начинается с 1, а не с 0.
При указании индекса несуществующего элемента массива выборка возвращает NULL. Обычно для обработки таких ситуаций используется конструкция IS NOT NULL.

Пример 6.6:
Выборка из поля-массива по индексу (для таблицы, полученной из предыдущих примеров):
SELECT books[2] FROM favorite_books;
Вернет 2 строки, одна из которых будет пустой. Для того, чтобы избежать этого, необходимо добавить условие в секцию WHERE.
SELECT books[2] FROM favorite_books
WHERE books[2] IS NOT NULL;
В PostgreSQL также поддерживается возможность создания срезов при выборке из массива. Срез аналогичен обычному обращению к элементам по индексу, но он описывает интервал значений. Срез задается парой целочисленных индексов, разделенных двоеточием и заключенных в квадратные скобки.
Пример 6.7:
Запрос среза с первого по второй элемент (включительно):
SELECT books[l:2] FROM favorite_books;
Чтобы узнать количество значений, хранящихся в массиве, следует воспользоваться функцией array_dims().
Автоматизация стандартных процедур
В лабораторной работе будут рассмотрены две категории расширений, позволяющих автоматизировать часто выполняемых операций с БД: последовательности и триггеры.
1) Последовательности
Последовательностью (sequence) в PostgreSQL называется объект базы данных, который фактически представляет собой автоматически увеличивающееся число. В других СУБД последовательности часто называются счетчиками. Последовательность определяется текущим числовым значением и набором характеристик, определяющих алгоритм автоматического увеличения (или уменьшения) используемых данных.
Наряду с текущим значением в определение последовательности также включается минимальное значение, максимальное значение и приращение.
Обычно приращение равно 1, но оно также может быть любым целым числом.
На практике последовательности не рассчитаны на прямой доступ из программы. Работа с ними осуществляется через специальные функции
PostgreSQL, предназначенные для увеличения, присваивания или получения текущего значения последовательности.
Последовательности создаются командой SQL CREATE SEQUENCE с положительным или отрицательным приращением. В этом определении единственный обязательный параметр последовательность определяет имя создаваемой последовательности.
Значения последовательности представляются типом integer, поэтому максимальное и минимальное

значения должны лежать в интервале от 2 147 483 647 до -2 147 483 647.
Синтаксис команды CREATE SEQUENCE:
CREATE SEQUENCE последовательность
[ INCREMENT приращение ]
[ MINVALUE минимум ]
[ MAXVALUE максимум ]
[ START начало ]
[ CACHE кэш ]
[ CYCLE ]
Ниже описаны некоторые необязательные секции команды CREATE
SEQUENCE (полное описание в [6]).
• INCREMENT приращение. Числовое изменение текущего значения последовательности. Отрицательное приращение создает убывающую последовательность. По умолчанию приращение равно 1.
• MINVALUE минимум. Минимальное допустимое значение последовательности.
• MAXVALUE максимум. Максимальное допустимое значение последовательности.
• START начало. Начальное значение последовательности, которым является любое целое число в интервале между минимальным и максимальным значениями.
• CACHE кэш. Возможность предварительного вычисления и хранения значений последовательности в памяти. Кэширование ускоряет доступ к часто используемым последовательностям. Минимальное значение, заданное по умолчанию, равно 1; увеличение объема кэша приводит к увеличению числа кэшируемых значений.
• CYCLE. При достижении нижнего или верхнего порога последовательность продолжает генерировать новые значения. В этом случае она переходит к минимальному значению
(для возрастающих последовательностей) или к максимальному значению (для убывающих последовательностей).
К последовательности можно обратиться командой SELECT, как к таблице или представлению (хотя такая возможность используется относительно редко). При составлении запроса к последовательности в списке выборки вместо полей указываются атрибуты последовательности.
Операции с последовательностями:
• nextval('последовательность') – увеличивает текущее значение последовательности и возвращает новое;
• currval (' последовательность') – возвращает значение, полученное при последнем вызове nextval. Если в текущем сеансе nextval не вызывалась, то currval не сможет вернуть значение;
• setval ('последовательность', n) – присваивает текущее значение последовательности, следующий вызов nextval вернет значение n+приращение;

• setval (' последовательность', n, b) – также присваивает текущее значение последовательности. Если третий параметр (b) равен false, то следующий вызов nextval вернет n, иначе – n+преращение.
Пример 6.8:
Создание отношение с автоматически увеличивающимся полем
(последовательность необходимо создать заранее):
CREATE TABLE shipments (id integer DEFAULT
nextval('shipments_ship_id_seq’) PRIMARY KEY,
customer_id integer, isbn text. ship_date timestamp);
Простая установка ограничения DEFAULT не гарантирует его применения. Пользователь способен вручную задать любое значение, что может привести к потенциальному нарушению уникальности в будущем. Для предотвращения конфликтов можно воспользоваться триггером.
2) Триггеры
Довольно часто перед некоторыми событиями SQL или после них должны выполняться определенные операции — например, проверка логической целостности данных.
В PostgreSQL поддерживаются нестандартные расширения, называемые триггерами (trigger) и упрощающие взаимодействие приложения с базой данных. Триггер определяет функцию, которая должна выполняться до или после некоторой операции с базой данных. Триггеры реализуются на языке С, PL/pgSQL или любом другом функциональном языке (кроме SQL), который может использоваться в
PostgreSQL для определения функций.
Триггер создается на основе существующей функции. Триггеры могут вызывать функции, написанные на любом языке, но за одним исключением: функция не может быть полностью реализована на SQL.
Синтаксис определения триггера выглядит так:
CREATE TRIGGER триггер { BEFORE | AFTER } { событие [ OR
событие ] } ON таблица
FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE функция ( аргументы )
Ниже приводятся краткие описания компонентов этого определения.
• CREATE TRIGGER триггер. В аргументе триггер указывается произвольное имя создаваемого триггера. Имя может совпадать с именем триггера, уже существующего в базе данных — при условии, что этот триггер установлен для другой таблицы.
• { BEFORE | AFTER }. Ключевое слово BEFORE означает, чт.о функция должна выполняться перед попыткой выполнения операции, включая все встроенные проверки ограничений данных, реализуемые при выполнении команд INSERT и DELETE. Ключевое слово AFTER означает, что функция вызывается после завершения операции.
• { событие [ OR событие ... ] }. События SQL, поддерживаемые в
PostgreSQL. При перечислении нескольких событий в качестве разделителя используется ключевое слово OR.

• ON таблица. Имя таблицы, модификация которой заданным событием приводит к срабатыванию триггера.
• FOR EACH { ROW | STATEMENT }. Ключевое слово, следующее за конструкцией FOR EACH и определяющее количество вызовов функции при наступлении указанного события. Ключевое слово ROW означает, что функция вызывается для каждой модифицируемой записи. Если функция должна вызываться всего один раз для всей команды, используется ключевое слово STATEMENT.
• EXECUTE PROCEDURE функция (аргументы). Имя вызываемой функции аргументами.
Механизм ограничений PostgreSQL позволяет реализовать простое сравнение данных со статическими значениями, но иногда проверка входных данных должна производиться по более сложным критериям. Это типичный пример ситуации, в которой удобно воспользоваться триггером.
Пример 6.9:
Создание триггера. Срабатывает при попытке вставить или обновить данные в таблицу shipments и приводит к выполнению функции check_shipment_addition():
CREATE TRIGGER checkshipment
BEFORE INSERT OR UPDATE
ON shipments FOR EACH ROW
EXECUTE PROCEDURE check_shipment_addition();
Удаление триггера происходит с помощью команды Drop Trigger:
DROP TRIGGER имя ON таблица
Работа с триггерами предмет для рассмотрения в следующей лабораторной работе.
Использование PgAdmin для переноса БД
PgAdmin – средство, обеспечивающее удобный оконный интерфейс для работы с базами данных для PostgreSQL. В данной лабораторной работе будут рассмотрены операции создания backup-а (dump-а) базы данных, а также ее восстановления – эти операции необходимо использовать при переносе БД с одного сервера на другой. Для создания backup-а:
1. Запустите PgAdmin и настройте подключение к БД.
2. Затем, выберите БД и вызовите контекстное меню нажатием правой клавиши мыши и выберите «Backup»(рис 6.1).
Рисунок 6.1 – Вызов Backup-а из контекстного меню БД в PgAdmin

3. В появившемся окне выберите путь к файлу, в котором будет сохранен backup, а также (если это необходимо) другие настройки (рис 6.2) и нажмите «OK»
Рисунок 6.2 – Форма для создания backup-а
4. Дождитесь завершения процедуры создания backup-а.
Восстановление БД корректно проходит только в пустую БД, поэтому удалите (или переименуйте) БД, если у вас уже имеется БД с таким именем перед восстановлением, а затем следуйте инструкции:
1. Создайте пустую БД с именем восстанавливаемой БД
2. Для нее вызовите контекстное меню и выберите «Restore»
Рисунок 6.3 – Вызов Restore из контекстного меню БД в PgAdmin
3. В появившемся окне выберите путь к файлу с копией БД и, выбрав необходимые настройки, нажмите «Ok» (рис. 6.4)
Рисунок 6.4 – Форма для восстановления из backup
4. После окончания нажмите «Done»