Методические указания по дисциплине “Системы управления базами данных”

Введение

СУБД PostgreSQL реализует расширение SQL в виде языка PL/pgSQL. Данный язык обладает определенным сходством с PL/SQL, реализованным в СУБД Oracle и является процедурным в том смысле, что желаемый результат в нем достигается за последовательность шагов (в отличии от SQL).

Язык PL/pgSQL позволяет группировать на сервер БД код SQL запросов и программные команды, что может приводить к снижению затрат сетевых и коммуникационных ресурсов, обусловленных частыми запросами больших объемов данных со стороны клиентских приложений. Кроме того, несколько команд SQL, объединенные внутри одной процедуры выполняются в одной транзакции, что исключает возможность нарушения целостности, связанной с многопользовательским доступом к этим командам.

1 Структура языка

Язык PL/pgSQL имеет относительно простую структуру, что объясняется в основном тем, что каждый логически обособленный фрагмент кода существует в виде функции. Хотя на первый взгляд PL/pgSQL мало похож на другие языки программирования, сходство все же существует: логические фрагменты создаются и выполняются в виде функций, все переменные обязательно объявляются перед использованием, функции получают аргументы при вызове и возвращают некоторое значение в конце своей работы.

Программы PL/pgSQL состоят из блоков. Программные блоки вводятся в командах SQL CREATE FUNCTION, которые используются для определения функций PL/pgSQL. Структура программного блока представлена ниже:

CREATE FUNCTION имя_функции (аргументы)

RETURNS тип_возвращаемого_значения AS

DECLARE

объявление;

[…]

BEGIN

команда;

[…]

END;

LANGUAGE ‘plpgsql’;

Переменные, используемые в функции, объявляются в разделе DECLARE, а тело функции находится между командами BEGIN и END.

Программы PL/pgSQL, как и в большинстве языков программирования, состоят из команд и выражений. Команда выполняет некоторое действие – например, присваивает значение переменной или выполняет запрос. Каждая команда заканчивается символом «;». Выражения представляют собой условную запись последовательности операций, результат которой принадлежит одному из базовых типов данных PostgreSQL. Рассмотрим пример функции sql, возводящей число в квадрат.

CREATE FUNCTION sqr_int(pInt integer)

RETURNS integer AS

$BODY$

DECLARE

pRes integer;

BEGIN

pRes = pInt * pInt;

RETURN pRes;

END;

$BODY$

LANGUAGE plpgsql;

Для того, чтобы вызвать функцию достаточно написать запрос:

SELECT * FROM sqr_int(12);

Ключевое слово $BODY$ используется для выделения в тексте тела объявления функции (хранимой процедуры).

Внутри одной базы данных не может существовать двух функций с одинаковым именем. Под именем функции понимается имя схемы БД, в которой она создается (по-умолчанию, public), идентификатор функции (в примере – sqr_int), а также список типов формальных параметров (в примере – один параметр целого типа). Таким образом, в одной базе могут быть одновременно созданы несколько функций с одинаковым идентификатором, но с разным списком типов формальных значений.

2 Триггеры и триггерные функции

Триггер является расширением стандартных возможностей СУБД, позволяющим определять функцию, которая должна выполняться до или после некоторой операции с базой данных. Триггеры могут быть реализованы на языке С, PL/pgSQL или любом другом процедурном языке (кроме SQL), который может использоваться в PostgreSQL для определения функций.

Создание триггера выполняется при наличии существующий триггерной функции и имеет следующий синтаксис:

CREATE TRIGGER триггер {BEFORE | AFTER}

{событие [ OR событие …] }

ON таблица

FOR EACH {ROW | STATEMENT}

EXECUTE PROCEDURE функция(аргументы)

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

Ключевое слово BEFORE означает, что функция должна выполняться перед попыткой выполнения операции, включая все встроенные проверки ограничений данных, реализуемые при выполнении команд к таблице. Ключевое слово AFTER означает, что функция вызывается после завершения операции, приводящей в действие триггер.

Ключевое слово, следующее за конструкцией FOR EACH определяет количество вызовов функции при наступлении указанного события: ROW означает, что функция вызывается для каждой модифицируемой записи, а STATEMENT – функция вызывается один раз для всей команды.

Иногда требуется реализовать более сложные проверки перед вставкой или обновлением данных в таблице, чем те, которые позволяет реализовать механизм ограничений (см. ключевое слово CONSTRAINT).

Ниже приводится пример создания триггера:

CREATE TRIGGER update_users -- имя триггера

AFTER INSERT OR UPDATE OR DELETE -- типы действия с данными

ON student -- таблица БД

FOR EACH ROW -- метод обработки записей

EXECUTE PROCEDURE user_change(); -- выполняемая функция

Триггерные функции на языке PL/pgSQL синтаксически не отличаются от обычных функций на этом языке. Рассмотрим отрывок примера триггерной функции, используемой для работы с аутентификационными данными пользователей на информационно-образовательном портале ec.dstu.edu.ru:

CREATE OR REPLACE FUNCTION user_change()

RETURNS trigger AS

$BODY$

DECLARE

lId bigint;

sFacultyPrefix varchar;

sCondition varchar;

bAddPrefix boolean;

sOldLogin varchar;

sNewLogin varchar;

dInDate date;

BEGIN

-- формирование префикса для логина

-- tg_op определяет тип операции, для которой был вызван триггер

-- OLD - хранит старое значение записи

IF (tg_op = 'DELETE' or tg_op = 'UPDATE') THEN

-- вызов хранимой процедуры и запись значения в sOldLogin

SELECT user_prefix || OLD.zachnumber INTO sOldLogin FROM user_prefix(OLD.in_fakorg_fk, OLD.in_date);

END IF;

-- NEW - хранит новое значение записи

IF (tg_op = 'INSERT' OR tg_op = 'UPDATE') THEN

-- вызов хранимой процедуры и запись значения в sNewLogin

SELECT user_prefix || NEW.zachnumber INTO sNewLogin FROM user_prefix(NEW.in_fakorg_fk, NEW.in_date);

END IF;

IF (tg_op = 'INSERT' OR tg_op = 'UPDATE') THEN

-- у студента сменилась зачётка

IF (tg_op = 'UPDATE') THEN

IF (sNewLogin <> sOldLogin) THEN

-- если пользователя с таким логином еще нет

IF (sNewLogin NOT IN (SELECT login_name FROM user_account)) THEN

UPDATE user_account SET login_name = sNewLogin

WHERE login_name = sOldLogin;

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

ELSE

-- удаление пользователя заключается в установке атрибута date_dead

UPDATE user_account SET date_dead=now()

WHERE login_name = sOldLogin;

END IF;

END IF;

END IF;

RETURN NEW; -- возвращаем новое значение записи

ELSEIF (tg_op = 'DELETE') THEN

-- проверяем, может, есть ещё студент с этой зачёткой

SELECT id INTO lId FROM student WHERE zachnumber = OLD.zachnumber;

-- если запрос SELECT не вернул ниодной записи

IF NOT FOUND THEN

-- удаление пользователя заключается в установке атрибута date_dead

UPDATE user_account SET date_dead = now()

WHERE login_name = sOldLogin;

END IF;

RETURN OLD; -- возвращаем старое значение записи

END IF;

return NULL;

end

$BODY$

LANGUAGE plpgsql;

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

3 Оконные функции

Оконные функции в PostgreSQL (англ. window function) применяются для выполнения вычислений над значениями атрибутов временного отношения полученного в процессе выполнения запроса SQL. Таким образом, оконные функции представляют удобный для разработчика синтаксис, позволяющий увеличить функциональность обычного SQL запроса.

Вызов оконной функции предполагает использование какой-либо агрегатной функции (например, sum, count, max и т.д.) для некоторой группы кортежей, которая выбирается SQL-запросом. В отличие от вызова обычной агрегатной функции, группировка выбранных строк в одну на выходе запроса не производится — каждая строка остаётся на выходе запроса отдельной строкой. Однако, оконная функция позволяет сканировать все строки, которые могут быть частью группы, в которую входит текущая строка, в соответствии со спецификацией группировки (списка PARTITION BY) вызова оконной функции.

Рассмотрим такой пример: пусть у нас имеется отношение Results с результатами забега участников спартакиады учащихся, представляемых атрибутами fio – фамилия и инициалы участника (PK), kurs – номер курса студента (PK), start_time – время старта, final_time – время окончания забега. Пусть для оперативного формирования данных о результатах забега, нам требуется написать запрос, который представляет список участников с указанием времени, на которое этот студент отстал от лучшего результата на курсе. В этом случае запрос будет выглядеть следующим образом:

SELECT fio, kurs,

date_part('minute',(final_time - start_time)) - min( date_part( 'minute', (final_time - start_time))) OVER (PARTITION BY kurs) as lag_minute

FROM temporary.results

ORDER BY kurs, lag_minute

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

Рассмотрим еще один пример: пусть у нас имеется отношение Person, представляющее объект «Человек» с атрибутами id – идентификатор человека (PK), surname – фамилия, name – имя, patronymic – «отчество» и birthday – «дата рождения». Пусть нам нужно написать запрос, определяющий разницу между возрастом каждого человека и средним возрастом всех сотрудников с такой же фамилией.

SELECT surname, name, patronymic, age_year - age_avg FROM

( SELECT id, surname, name, patronymic, birthday,

extract(year from age(birthday))::int as age_year,

avg(extract(year from age(birthday)))

OVER (PARTITION BY surname) as age_avg

FROM general.person

ORDER BY surname ) as tmp

ORDER BY surname, name, patronymic

4 Пользовательские агрегатные функции

PostgreSQL, как и многие другие реляционные СУБД, поддерживает агрегатные функции. Агрегатная функция производит вычисление над единичным результатом от множества записей. Например, есть агрегаты для вычисления count (количества), sum (суммы), avg (среднего арифметического), max (максимального значения) и min (минимального значения) списка записей. Однако в СУБД PostgreSQL можно не только использовать преопределенные агрегатные функции, но также и создавать свои.

Рассмотрим пример определения нового типа (комплексное число) и агрегатной функции суммы с именем sum для значений этого типа:

-- Создаем тип complex - комплексное число

CREATE TYPE complex AS (

r double precision, -- действительная часть

i double precision -- мнимая часть

);

-- Создание таблицы – список комплексных чисел

CREATE TABLE list (num complex);
-- Инициализация начальных значений в таблице

INSERT INTO list(num) VALUES((1.1,2.2));

INSERT INTO list(num) VALUES((2.2,3.3));

INSERT INTO list(num) VALUES((3.3,4.4));

INSERT INTO list(num) VALUES((4.4,5.5));

INSERT INTO list(num) VALUES((5.5,1.1));

-- Функция complex_add для суммирования комплексных чисел

CREATE FUNCTION complex_add(pFirst complex, pSecond complex)

RETURNS complex AS

$BODY$

DECLARE

pRet complex;

BEGIN

pRet.r = pFirst.r + pSecond.r;

pRet.i = pFirst.i + pSecond.i;

RETURN pRet;

END;

$BODY$

LANGUAGE plpgsql;

-- Создаем агрегатную функцию

CREATE AGGREGATE sum(complex)

(

sfunc = complex_add, -- имя вызываемой хранимой процедуры

stype = complex, -- тип значения

initcond = '(0,0)' -- начальное значение

);

-- Вызываем агрегатную функцию для комплексных значений из таблицы list

SELECT sum(num) FROM list

5 Возвращение множества значений

Рассмотрим пример некоторой функции на языке PL/pgSQL, в качестве возвращаемого значения которой выступает множество кортежей. Пусть нам дано некоторое отношение A, состоящее из атрибутов: a1 (тип integer), a2 (тип date), a3 (тип text), a4 (тип integer). Рассмотрим хранимую процедуру, возвращающую все значения этого отношения, в которых a1 = a4

-- удалить таблицу, если уже имеется

DROP TABLE IF EXISTS A;

-- создать таблицу A

CREATE TABLE A(a1 int,a2 date,a3 text,a4 int);

-- вставить записи в таблицу

insert into A VALUES(1,'10.01.2010','First',1);

insert into A VALUES(2,'12.01.2010','Second',4);

insert into A VALUES(3,'13.01.2010','Third',3);

-- создать хранимую процедуру

CREATE OR REPLACE FUNCTION A_get()

-- возвращаемый тип: множество записей (кортежей)

RETURNS SETOF record AS

$BODY$

DECLARE

-- curVal переменная с типом «запись из таблицы A»

curVal A%ROWTYPE;

BEGIN

-- проход по всем строкам запроса

FOR curVal in SELECT * FROM A LOOP

-- проверка выполнения условия

IF curVal.a1 = curVal.a4 THEN

-- если условие выполняется вернуть кортеж

RETURN NEXT curVal;

END IF;

END LOOP;

END;

$BODY$

LANGUAGE plpgsql;

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

SELECT a2, a3

FROM A_get() as tmp(a1 int,a2 date,a3 text,a4 int)

ORDER BY a3;

Примечание: Очевидно, что для поставленной задачи гораздо проще применить простой SQL запрос SELECT

Задание для самостоятельного выполнения

Для закрепления полученных теоретических знаний по расширенным функциям СУБД PostgreSQL выполнить:

1. 
   Реализовать БД в СУБД PostgreSQL, поддерживающую свойство темпоральности данных:
   

I.1. Реализовать базовое отношение temporary_object, содержащее атрибуты:

• 
  id – идентификатор записи, тип: integer;
  
• 
  time_create – время создания записи (время рождения), тип timestamp;
  
• 
  time_dead – время смерти записи, тип timestamp.
  

Первичный ключ (Primary Key): {id, time_create}

I.2. Создать таблицы, для которых планируется реализовать свойство темпоральности, объявив их наследниками temporary_object;

I.3. Определить триггерную функцию(-и), которая(-ые) при добавлении новой записи в отношение, поддерживающее свойство темпоральности, проверяет наличие такой записи среди существующих объектов (сравнение по id).

Если запись с таким id найдена, то для этой записи устанавливается время смерти, а добавляемая запись содержит в себе соответственные значения атрибутов найденной записи (кроме тех, которые заданы для новой записи), пустое значение времени смерти и новое значение времени рождения, равное текущему моменту. Таким образом, у новой записи время рождения равно, времени смерти ее предка.

Если запись с таким id на найдена, то происходит ее добавление в БД с установлением в качестве времени рождения текущего момента времени.

I.4. Напишите собственную агрегатную функцию, которая для сгруппированных строк производит конкатенацию значений атрибута-аргумента с заданным в качестве параметра функции символом-разделителем. Например, если в качестве значений атрибутов-аргументов агрегатной функции будут выступать строки ‘123’, ‘456’ и ‘789’, а в качестве символа разделителя будет задан ‘_’, то агрегатная функция вернет значение ‘123_456_789’ в результате группирования этих трех кортежей (порядок сортировки по возрастанию);

I.5. Для всех ключевых полей (как первичных ключей, так и внешних) создать индексы командой CREATE INDEX.

2. 
   Разработать программное средство, представляющее пользовательские интерфейсы для взаимодействия с разработанной темпоральной БД:
   

II.1. Интерфейс просмотра кортежей существующих таблиц с возможностью свободного «перемещения» (т.е. отката и возврата) по последним изменениям.

II.2. Интерфейс добавления/изменения данных в БД для каждой таблицы.

Разработку БД необходимо выполнять в СУБД PostgreSQL версии 9 и старше. Разработка программного средства может быть реализована на следующих языках программирования: C++ (QT, MS VS), PHP 5.x, Delphi 7 (и выше).

Список литературы

1. 
   John C. Worsley and Joshua D. Drake Practical PostgreSQL // пер. с английского Е.Матвеев, СПб:Питер, 2003.
   
2. 
   PostgreSQL 9.0 Documentation // [Электронный ресурс]: http://www.postgresql.org/docs, режим доступа – свободный.
   
3. 
   User-defined Aggregates // [Электронный ресурс]: http://postgresql.ru.net/manual/xaggr.html, режим доступа – свободный.
   
4. 
   Подвальный С.Л., Сергеева Т.И., Гранков М.В., Базы данных: модели данных, SQL, проектирование: учеб. пособие – Ростов н/Д: Издательский центр ДГТУ, 2007 – 202с.
   
5. 
   Дейт К.Дж. Введение в системы баз данных. М.: Вильямс, 2000.