Wiki - Факультет компьютерных наук - Вклад [ru]

Базы данных/Лабораторная работа 5

2016-06-08T10:27:42Z

ADKosm: /* Подокументное преобразование данных */

Задачи лабораторной работы:
* Установить MongoDB
* Рассмотреть различия в моделировании данных для реляционных БД и документоориентированных.
* Попрактиковаться в составлении запросов к MongoDB: добавление, обновление, селекция, фильтрация, агрегация.
* Настроить репликацию данных, инициировать отключение мастер-узла в процессе интенсивного обновления данных, проанализировать действия сервера и проверить целостность измененных данных.

== Установка MongoDB ==

Для лабораторных работ достаточно версии 2.4+

* Ubuntu: sudo apt-get install mongodb
* Mac OS: brew install mongodb [http://www.mongodbspain.com/en/2014/11/06/install-mongodb-on-mac-os-x-yosemite/ Подробнее]
* Windows: Скачать установщик: https://www.mongodb.com/download-center#community

== Импорт данных ==
Коллекции, содержащие фильмы и длинные текстовые факты о фильмах:
* https://yadi.sk/d/Y7r672PvsEyaC

Ссылки на отдельные файлы:
* https://yadi.sk/d/_UIdFUhhsF8SC
* https://yadi.sk/d/sSuDjY3fsF8S7

Для импорта коллекций в базу movies выполните:

bzip2 -dc movies.bson.bz2 | mongoimport -d movies -c Movie
bzip2 -dc moviesdocs.bson.bz2 | mongoimport -d movies -c MovieDoc

Импортированная коллекция займет примерно '''6Гб''' на диске.

Чтобы проверить корректность импорта, зайдите в консоль MongoDB:

mongo

Показать базы данных:

show dbs;

Переключиться на базу данных (если ее нет, то создастся):

use movies

Показать коллекции текущей базы данных:

show collections;

== Экспорт данных ==

В случае, если понадобится экспортировать данные, то сделать это можно (для версий 2.*) по коллекциям, запуская:

mongoexport --db movies -c MovieDoc -o - | bzip2 - > moviesdocs.bson.bz2
mongoexport --db movies -c Movie -o - | bzip2 - > movies.bson.bz2

Если вы создадите индексы, то они будут лежать в отдельной коллекции, ее также нужно экспортировать.

== Моделирование данных в MongoDB ==
Так как MongoDB не стремится экономить место на диске, а наоборот активно резервирует место под обновления данных, итоговая база занимает более 30Гб. В данной лабораторной работе рассматривается только сущность фильмов и связанные с ней факты (информация аналогичная хранимой в title, movie_info, keywords).

Схема модели данных в MongoDB:

https://www.packtpub.com/sites/default/files/new_blog_images/147/1-flatmodel.gif

В дампе базы есть коллекции:
* Movie - для заголовков фильмов, ключевых слов и некоторых фактов
* MovieDoc - для длинных текстовых фактов (описания, цитаты и тд)

=== Коллекция Movie ===

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

db.Movie.find({"_id" : "\"12 oz. Mouse\" (2005)"}) // выбрать сериал или фильм
db.Movie.find({"SeriesID" : "\"12 oz. Mouse\" (2005)"}) // выбрать сериал и все эпизоды
db.Movie.find({"_id" : "\"12 oz. Mouse\" (2005) {Adventure Mouse (#1.7)}"}) // выбрать конкретный эпизод

Для удобства восприятия информации используйте .pretty():

> db.Movie.find({"_id" : "\"12 oz. Mouse\" (2005)"}).pretty()
{
"AltTitles" : [
"\"12 Ouns Mouce\" (2005)",
"\"Oz. Mo\" (2005)"
],
"Countries" : [
"USA"
],
"Genres" : [
"Animation",
"Comedy"
],
"Keywords" : [
"abbreviation-in-title",
"absurdism",
"adult-animation",
"animal-in-title",
"avant-garde",
"beer",
"character-name-in-title",
"digit-in-title",
"drunkenness",
"late-night",
"mouse",
"non-sequitur",
"number-in-title",
"period-in-title",
"surrealism"
],
"Languages" : [
"English"
],
"Locations" : [
"Atlanta, Georgia, USA"
],
"MovieID" : "\"12 oz. Mouse\" (2005)",
"Parental" : {
"Certificates" : [
"Australia:MA15+",
"Canada:14+\t(TV rating)",
"New Zealand:M",
"USA:TV-14",
"USA:TV-MA\t(one episode)"
]
},
"Rating" : {
"RatingDist" : "1000001103",
"Rating" : "6.9",
"RatingVotes" : "1365"
},
"ReleaseYear" : "2005",
"RunningTime" : "15",
"SeriesEndYear" : "",
"SeriesID" : "\"12 oz. Mouse\" (2005)",
"SeriesType" : "S",
"Technical" : {
"Colors" : [
"Color"
]
},
"_id" : "\"12 oz. Mouse\" (2005)"
}

Расшифровка SeriesType:
* F - фильм
* S - сериал
* E - эпизод

=== Индексы ===

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

Просмотр существующих индексов в коллекции:

db.collection_name.getIndexes()

Функция просмотра всех индексов для всех коллекций:

db.getCollectionNames().forEach(function(collection) {
indexes = db[collection].getIndexes();
print("Indexes for " + collection + ":");
printjson(indexes);
});

Создание индексов:

db.collection_name.createIndex( { field_name: 1 } )
db.collection_name.createIndex( { field_name: "hashed" } ) // хэш-индексы

=== Подокументное преобразование данных ===

Недостатком предлагаемой базы является то, что все атрибуты фильмов по-прежнему строковые, хотя некоторые, например RunningTime или Rating.RatingVotes содержат только числа. Это часто встречаемая ситуация, поэтому далее рассматривается пример функции, которая помогает выполнять подобные преобразования.

Конвертировать продолжительность фильма (задана для всех):
db.Movie.find().forEach(function(data) {
db.Movie.update({
"_id": data._id
}, {
"$set": {
"RunningTime": parseInt(data.RunningTime)
}
});
});

Конвертировать рейтинг там, где он задан:

db.Movie.find({"Rating.Rating": {$exists: true}, "Rating.RatingVotes": {$exists: true}}).forEach(function(data) {
db.Movie.update({
"_id": data._id
}, {
"$set": {
"Rating.Rating": parseFloat(data.Rating.Rating),
"Rating.RatingVotes": parseInt(data.Rating.RatingVotes)
}
});
})

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

=== Дополнительно ===
* Подробное описание скрипта и модели данных: https://www.packtpub.com/books/content/mongo-goes-to-the-movies

== Язык запросов MongoDB ==

=== Базовые операции ===

==== Выборка ====

Поиск по текстовым полям поддерживает синтаксис регулярных выражений, для нечеткого поиска нужно заключить выражение в /title/ (аналог "%title%"). Пример:

db.Movie.find({"MovieID": /Matrix.*1999/})

Чтобы вывести только нужные поля, используйте второй параметр функции find:

> db.Movie.find({"_id" : "The Matrix (1999)"}, {Genres: 1, Business: 1}).pretty()

Поиск по значениям в списках не отличается от обычного:

db.Movie.find({"Genres": "Action"}).limit(3)

==== Обновление ====

Добавить новый документ:
db.Movie.insert({_id: "\"How I am getting 10 for db exam\" (2016)", "MovieID" : "\"How I am getting 10 for db exam\" (2016)", "Countries" : [ "Russia" ], "Genres" : [ "Drama", "Comedy" ], "Keywords": [ "study", "working-hard", "success" ], "ReleaseYear" : 2016})

db.Movie.insert({_id: "\"How I am getting 10 for project\" (2016)", "MovieID" : "\"How I am getting 10 for db exam\" (2016)", "Countries" : [ "Russia" ], "Genres" : [ "Drama", "Comedy" ], "Keywords": [ "study", "working-hard", "success" ], "ReleaseYear" : 2016})

Обновить атрибут для всех документов по результатам поиска:

db.Movie.update({MovieID: /How I am getting 10 for/}, {$set: {"Locations" : [ "Moscow, Russia" ]}}, multi=true)

* Первый аргумент - условия выборки обновляемых объектов
* Второй аргумент - что именно делать
* multi=true (по умолчанию false), обновляет все найденные объекты или только первый

Добавить элемент в список документа:

db.Movie.update({MovieID: /How I am getting 10 for/}, {$push: {"Keywords": "true-story"}}, multi=true)

==== Дополнительно ====
* https://docs.mongodb.com/manual/crud/

=== Aggregation Framework ===

Для выполнения агрегирующих запросов в MongoDB используется команда aggregate.

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

https://docs.mongodb.com/manual/_images/aggregation-pipeline.png

Также можно разгруппировать списки в документах ($unwind).

Чтобы отделять переменные внутри шагов от строк используется префикс $.

Найти документы и разгруппировать ($unwind) их по ключевым словам:

db.Movie.aggregate({$match: {MovieID: /How I am getting 10 for/}},
{$unwind: "$Keywords"})

Переформировать список выбираемых атрибутов ($project) аналогично второму параметру в find:

db.Movie.aggregate({$match: {MovieID: /How I am getting 10 for/}},
{$project: {"MovieID": 1, "Keywords": 1, "_id": 0} },
{$unwind: "$Keywords"})

Сгруппировать по атрибуту и применить агрегирующую функцию:

db.Movie.aggregate({$match: {MovieID: /How I am getting 10 for/}},
{$project: {"MovieID": 1, "Keywords": 1, "_id": 0} },
{$unwind: "$Keywords"},
{$group: {_id: "$Keywords", "MoviesList": {$push: "$MovieID"} }})

==== Дополнительно ====
* https://docs.mongodb.com/manual/aggregation/

== Репликации MongoDB ==

=== Принцип работы репликаций в MongoDB ===

=== Конфигурация набора реплик ===

=== Сценарий отказа мастер-узла ===

=== Стартовый скрипт инициализации сбоя во время работы ===

=== Дополнительно ===
* https://docs.mongodb.com/manual/core/replication-introduction/

== Задания на защиту ==
1. Составить запросы:
* Для каждого года из первой декады XXI века посчитать количество снятых фильмов.
* Выбрать топ-10 наиболее популярных ключевых слов для фильмов заданной страны.

2. Составить свою формулу рейтинга (общего для всех фильмов или по какому-либо фильтру), используя параметры словарей Movie.Rating (Rating - среднее значение оценкой, RatingVotes - количество оценок) или любые другие.

Для примера вот формула [http://www.imdb.com/chart/top Top 250 IMDb Movies]

ранг фильма = (v/(v+k))*X + (k/(v+k))*C

где:

X = рейтинг фильма
v = количество голосов
k = минимальное количество голосов, чтобы попасть в список (для данного списка 25000)
C = средний рейтинг для всех фильмов в этом списке (для данного списка 6.90)

MongoDB поддерживает многие арифметические операции: https://docs.mongodb.com/manual/reference/operator/aggregation-arithmetic/

Данное задание можно выполнить не одним запросом через агрегацию, а используя функцию на JavaScript, которая выполняется в консоле MongoDB.

3. Написать скрипт на bash или любом другом скриптовом языке, который запускает сервер MongoDB в режиме реплики, начинает интенсивно изменять данные, определяет мастер-узел, убивает процесс мастер-узла, после этого заканчивает изменения данных и проверяет целостность данных.

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

Базы данных/Лабораторная работа 4

2016-06-07T18:42:02Z

ADKosm: /* Создание таблицы истории */

Цели лабораторной работы:
* Знакомство с языком PL/pgSQL
* Создание процедур, функций и триггеров, обеспечивающих удобство работы с базой и обеспечение целостности
* Использование транзакция для обеспечения целостности

== Зачем использовать PL/pgSQL ==

PL/pgSQL - процедурный язык программирования, позволяющий выполнять несложные скрипты. Язык PL/pgSQL является расширенной версией стандарта языка PL/SQL. В PostgreSQL есть также возможность расширить синтаксис встроенного языка за счет интерпретаторов популярных языков: Python, Perl, Java, Lua, R и тд.

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

К наиболее типичным задачам, реализуемым на PL/SQL относятся:
* Выполнение и контроль целостности операций с высокими требованиями к скорости исполнения (например, финансовые операции, событиями систем мониторинга)
* Аудит изменения данных: созранение истории изменения значений кортежей наиболее важных отношений в системе.
* Генерация разовых периодических отчетов, в которых аналитируется большой объем данных (для экономии времени пересылки данных между клиентом и сервером).

Также к преимуществам использования PL/SQL является использование одного языка программирования для выполнения запросов и для выполнения инструкций.

Как правило, язык PL/pgSQL не используют в задачах, где некритичны его преимущества из-за относительно сложного процесса поддержки и нагрузки на сервер СУБД.

== Процедуры и функции ==

Отличие процедур и функций состоит лишь в том, что процедуры не возвращают никаких значений, в отличии от функций. И процедуры и функции могут принимать входные параметры.

Но в PostgreSQL нет различий в синтаксисе создания функций и процедур, обе создаются с помощью ключевых слов create function.

Пример создания процедуры:

CREATE OR REPLACE FUNCTION add_movie(title VARCHAR(70), production_year INTEGER)
RETURNS void AS $$
DECLARE phonetic_code VARCHAR(70);
BEGIN
SELECT soundex(title) INTO phonetic_code;
RAISE NOTICE 'The phonetic code for % is %', title, phonetic_code;
INSERT INTO title (title, production_year, phonetic_code, kind_id) VALUES (title, production_year, phonetic_code, 1);
COMMIT;
END;
$$ LANGUAGE plpgsql;

Тело процедуры находится между BEGIN и END. При этом внутри тела могут быть транзакции, которые также будут обернуты в BEGIN-END.

В примере также используется soundex, которая доступна после подключения модуля (выполните до создания процедуры):

imdb=# create extension fuzzystrmatch;

Чтобы выполнить созданную процедуру, выберите ее:

select add_movie('How I will pass all the exams', 2016);

Этот пример сейчас скорее всего не сработает с ERROR: duplicate key value violates unique constraint "title_pkey". В конце этой части есть процедура, которая поможет вам добавить фильм.

=== Определение переменных ===

Определение переменных происходит с помощью команды DECLARE, после которой нужно указать имя и тип. Также можно сразу присвоить значение:

DECLARE production_year INTEGER := 2016;

Определять переменные можно между BEGIN и END - в этом случае они действуют только, или сразу после определения процедуры (перед BEGIN как в примере выше), тогда переменная будет глобальной и доступна во всей процедуре.

Чтобы записать в переменную кортеж или одно значение, нужно использовать SELECT ... INTO var:

SELECT count(1) INTO actors_num from cast_info WHERE ...

=== Управляющие конструкции ===

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

BEGIN
SELECT * INTO movie FROM title WHERE title like '%'||movie_name||'%';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'movie % not found', movie_name;
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'movie % not unique', movie_name;
END;

В этом примере также есть конструкция title like '%'||movie_name||'%', в которой movie_name - переменная, хранящая часть название фильма. Конкатенация строк выполняется оператором ||.

Также доступны циклы и условия:

FOR movie_row IN SELECT * FROM title limit 10 LOOP
RAISE NOTICE 'Currently working on movie %s ...', quote_ident(movie_row.title);
IF movie_row.production_year > 2000 THEN
RAISE NOTICE 'This movie is of this century';
END IF;
END LOOP;

Эта конструкция проходит по кортежам, возвращаемым запросом SELECT * FROM title limit 10, для каждого выводит его название и, если фильм выпущен после 2000 года, то сообщение о его новизне.

=== Использование курсоров ===

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

Определения курсора:

DECLARE
curs1 refcursor; -- будет указан в будущем
curs2 CURSOR FOR SELECT * FROM cast_info; -- сразу же указывает на запрос (запрос не выполняется в момент определения)

Чтобы начать пользоваться курсором, его нужно открыть:

OPEN curs1 FOR SELECT * FROM cast_info; -- если курсор не был привязан к запросу ранее
OPEN curs2; -- для запросов, привязанных к запросу

После этого навигация курсора может быть следующей:

FETCH curs1 INTO rowvar; -- получить следующий кортеж запроса
MOVE curs1; -- то же самое
MOVE LAST FROM curs3; -- получить последний кортеж запроса
MOVE RELATIVE -2 FROM curs4; -- сдвинуть относительно текущего положения запроса на 2 кортежа назад
MOVE FORWARD 2 FROM curs4; -- сдвинуть курсор на 2 кортежа вперед.

Когда курсор не нужен или требуется его переоткрыть, то используется команда:

CLOSE curs1;

=== Логирование и обработка ошибок ===

Вывод информационного сообщения (при этом процедура продолжит работу дальше):

RAISE NOTICE 'Data processing...'

Команда завершающая процедуру или транзакцию с ошибкой:

RAISE EXCEPTION 'Something went wrong'

=== Пример полезной для администрирования процедуры ===

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

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

CREATE OR REPLACE FUNCTION fix_sequences()
RETURNS void AS $$
DECLARE max_id INTEGER;
DECLARE t RECORD; -- переменная для обхода кортежей выборки
BEGIN
FOR t IN (select table_name from pg_class s
join information_schema.tables on tables.table_schema = 'public' and table_name=replace(relname, '_id_seq', '' )
where s.relkind='S') LOOP
EXECUTE 'select max(id) from '||t.table_name into max_id; -- выполняет запрос, сформированный в строке и передает результат в max_id
raise notice 'max id for % is %', t.table_name, max_id;
if max_id is not null then
-- выполняет запрос увеличения текущего значения последовательности
EXECUTE 'SELECT setval('''||t.table_name||'_id_seq'', '||max_id||', true)';
end if;
END LOOP;
END;
$$ LANGUAGE plpgsql;

select fix_sequences(); -- вызов процедуры

== Триггеры ==

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

=== Типы триггеров ===

Триггер может быть привязан к командам UPDATE, INSERT и DELETE конкретных отношений. Также триггер может быть запущен как до выполнения указанной операции, так и после. Например:

CREATE TRIGGER cool_trigger_name BEFORE INSERT OR UPDATE ON movie_info
...

Этот триггер выполнится перед операциями добавления и обновления данных в таблице movie_info.

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

CREATE OR REPLACE FUNCTION check_rows() RETURNS trigger AS $$
BEGIN
IF NEW.title is null THEN
RAISE EXCEPTION 'Invalid title';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_check BEFORE INSERT OR UPDATE ON title
FOR EACH ROW EXECUTE PROCEDURE check_rows();

Старые и новые значения данных доступны соответственно через OLD.* и NEW.* для каждого кортежа.

== Транзакции ==

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

Управление транзакцией выполняется с помощью команд BEGIN, SAVEPOINT, ROLLBACK и COMMIT. Пример сценария сложной транзакции:

BEGIN; -- начало транзакции
-- выполнение операций
SAVEPOINT my_savepoint; -- сохранить точку, на которую можно потом вернуться,
-- но изменения в базу все еще не вносятся
-- выполнение операций
ROLLBACK TO my_savepoint; -- после сохранения произошла ошибка, откат на состояние my_savepoint
-- выполнение другого сценария
IF check_ok() THEN:
COMMIT; -- фиксация результата
ELSE
ROLLBACK;
END IF;

=== Создание таблицы истории ===

Для задания понадобится таблица истории, сделать ее проще всего унаследовав от существующей, а затем убрав наследование и добавив колонку для времени изменения:

CREATE TABLE tablename_history () INHERITS (tablename); -- копирует структуру таблицы без записей
ALTER TABLE tablename_history NO INHERIT tablename;

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

2. Создать функцию с входным параметром - имя актера. Если актер с таким именем не найден, функция должна вернуть 0. Если найден, то вычислить через person_info birth date (info_type_id=21) и death date (info_type_id=23) возраст актера. Если актер еще не умер, то вычислить, сколько сейчас лет. Функция должна вернуть целое число прожитых лет.

3. Используя функцию, созданную ранее, создайте процедуру, которая по имени актера выводит в лог текст следующего содержания:
Name: ...
Nicknames: nickname1, nickname2, ... строка выводится только если у человека есть клички, выводятся все клички через запятую
Age: ... вычисляется из функции
First appear: ... фильм и год выхода, в котором актер впервые снялся.

Если актер не найден или не удалось определить его первую роль, то процедура должна завершиться с ошибкой и вывести в лог "Invalid data"

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

== Дополнительно ==
* http://www.postgresql.org/docs/9.3/static/plpgsql.html
* http://postgres.cz/wiki/PL/pgSQL_%28en%29 - примеры процедур, функций и триггеров
* http://www.postgresql.org/docs/9.3/static/tutorial-transactions.html
* http://www.postgresql.org/docs/9.3/static/plpython.html - позволяет писать хранимые процедуры на питоне, нужно включить расширение

Базы данных/Лабораторная работа 4

2016-06-07T16:39:02Z

ADKosm: /* Определение переменных */

Цели лабораторной работы:
* Знакомство с языком PL/pgSQL
* Создание процедур, функций и триггеров, обеспечивающих удобство работы с базой и обеспечение целостности
* Использование транзакция для обеспечения целостности

== Зачем использовать PL/pgSQL ==

PL/pgSQL - процедурный язык программирования, позволяющий выполнять несложные скрипты. Язык PL/pgSQL является расширенной версией стандарта языка PL/SQL. В PostgreSQL есть также возможность расширить синтаксис встроенного языка за счет интерпретаторов популярных языков: Python, Perl, Java, Lua, R и тд.

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

К наиболее типичным задачам, реализуемым на PL/SQL относятся:
* Выполнение и контроль целостности операций с высокими требованиями к скорости исполнения (например, финансовые операции, событиями систем мониторинга)
* Аудит изменения данных: созранение истории изменения значений кортежей наиболее важных отношений в системе.
* Генерация разовых периодических отчетов, в которых аналитируется большой объем данных (для экономии времени пересылки данных между клиентом и сервером).

Также к преимуществам использования PL/SQL является использование одного языка программирования для выполнения запросов и для выполнения инструкций.

Как правило, язык PL/pgSQL не используют в задачах, где некритичны его преимущества из-за относительно сложного процесса поддержки и нагрузки на сервер СУБД.

== Процедуры и функции ==

Отличие процедур и функций состоит лишь в том, что процедуры не возвращают никаких значений, в отличии от функций. И процедуры и функции могут принимать входные параметры.

Но в PostgreSQL нет различий в синтаксисе создания функций и процедур, обе создаются с помощью ключевых слов create function.

Пример создания процедуры:

CREATE OR REPLACE FUNCTION add_movie(title VARCHAR(70), production_year INTEGER)
RETURNS void AS $$
DECLARE phonetic_code VARCHAR(70);
BEGIN
SELECT soundex(title) INTO phonetic_code;
RAISE NOTICE 'The phonetic code for % is %', title, phonetic_code;
INSERT INTO title (title, production_year, phonetic_code, kind_id) VALUES (title, production_year, phonetic_code, 1);
COMMIT;
END;
$$ LANGUAGE plpgsql;

Тело процедуры находится между BEGIN и END. При этом внутри тела могут быть транзакции, которые также будут обернуты в BEGIN-END.

В примере также используется soundex, которая доступна после подключения модуля (выполните до создания процедуры):

imdb=# create extension fuzzystrmatch;

Чтобы выполнить созданную процедуру, выберите ее:

select add_movie('How I will pass all the exams', 2016);

Этот пример сейчас скорее всего не сработает с ERROR: duplicate key value violates unique constraint "title_pkey". В конце этой части есть процедура, которая поможет вам добавить фильм.

=== Определение переменных ===

Определение переменных происходит с помощью команды DECLARE, после которой нужно указать имя и тип. Также можно сразу присвоить значение:

DECLARE production_year INTEGER := 2016;

Определять переменные можно между BEGIN и END - в этом случае они действуют только, или сразу после определения процедуры (перед BEGIN как в примере выше), тогда переменная будет глобальной и доступна во всей процедуре.

Чтобы записать в переменную кортеж или одно значение, нужно использовать SELECT ... INTO var:

SELECT count(1) INTO actors_num from cast_info WHERE ...

=== Управляющие конструкции ===

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

BEGIN
SELECT * INTO movie FROM title WHERE title like '%'||movie_name||'%';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'movie % not found', movie_name;
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'movie % not unique', movie_name;
END;

В этом примере также есть конструкция title like '%'||movie_name||'%', в которой movie_name - переменная, хранящая часть название фильма. Конкатенация строк выполняется оператором ||.

Также доступны циклы и условия:

FOR movie_row IN SELECT * FROM title limit 10 LOOP
RAISE NOTICE 'Currently working on movie %s ...', quote_ident(movie_row.title);
IF movie_row.production_year > 2000 THEN
RAISE NOTICE 'This movie is of this century';
END IF;
END LOOP;

Эта конструкция проходит по кортежам, возвращаемым запросом SELECT * FROM title limit 10, для каждого выводит его название и, если фильм выпущен после 2000 года, то сообщение о его новизне.

=== Использование курсоров ===

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

Определения курсора:

DECLARE
curs1 refcursor; -- будет указан в будущем
curs2 CURSOR FOR SELECT * FROM cast_info; -- сразу же указывает на запрос (запрос не выполняется в момент определения)

Чтобы начать пользоваться курсором, его нужно открыть:

OPEN curs1 FOR SELECT * FROM cast_info; -- если курсор не был привязан к запросу ранее
OPEN curs2; -- для запросов, привязанных к запросу

После этого навигация курсора может быть следующей:

FETCH curs1 INTO rowvar; -- получить следующий кортеж запроса
MOVE curs1; -- то же самое
MOVE LAST FROM curs3; -- получить последний кортеж запроса
MOVE RELATIVE -2 FROM curs4; -- сдвинуть относительно текущего положения запроса на 2 кортежа назад
MOVE FORWARD 2 FROM curs4; -- сдвинуть курсор на 2 кортежа вперед.

Когда курсор не нужен или требуется его переоткрыть, то используется команда:

CLOSE curs1;

=== Логирование и обработка ошибок ===

Вывод информационного сообщения (при этом процедура продолжит работу дальше):

RAISE NOTICE 'Data processing...'

Команда завершающая процедуру или транзакцию с ошибкой:

RAISE EXCEPTION 'Something went wrong'

=== Пример полезной для администрирования процедуры ===

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

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

CREATE OR REPLACE FUNCTION fix_sequences()
RETURNS void AS $$
DECLARE max_id INTEGER;
DECLARE t RECORD; -- переменная для обхода кортежей выборки
BEGIN
FOR t IN (select table_name from pg_class s
join information_schema.tables on tables.table_schema = 'public' and table_name=replace(relname, '_id_seq', '' )
where s.relkind='S') LOOP
EXECUTE 'select max(id) from '||t.table_name into max_id; -- выполняет запрос, сформированный в строке и передает результат в max_id
raise notice 'max id for % is %', t.table_name, max_id;
if max_id is not null then
-- выполняет запрос увеличения текущего значения последовательности
EXECUTE 'SELECT setval('''||t.table_name||'_id_seq'', '||max_id||', true)';
end if;
END LOOP;
END;
$$ LANGUAGE plpgsql;

select fix_sequences(); -- вызов процедуры

== Триггеры ==

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

=== Типы триггеров ===

Триггер может быть привязан к командам UPDATE, INSERT и DELETE конкретных отношений. Также триггер может быть запущен как до выполнения указанной операции, так и после. Например:

CREATE TRIGGER cool_trigger_name BEFORE INSERT OR UPDATE ON movie_info
...

Этот триггер выполнится перед операциями добавления и обновления данных в таблице movie_info.

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

CREATE OR REPLACE FUNCTION check_rows() RETURNS trigger AS $$
BEGIN
IF NEW.title is null THEN
RAISE EXCEPTION 'Invalid title';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_check BEFORE INSERT OR UPDATE ON title
FOR EACH ROW EXECUTE PROCEDURE check_rows();

Старые и новые значения данных доступны соответственно через OLD.* и NEW.* для каждого кортежа.

== Транзакции ==

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

Управление транзакцией выполняется с помощью команд BEGIN, SAVEPOINT, ROLLBACK и COMMIT. Пример сценария сложной транзакции:

BEGIN; -- начало транзакции
-- выполнение операций
SAVEPOINT my_savepoint; -- сохранить точку, на которую можно потом вернуться,
-- но изменения в базу все еще не вносятся
-- выполнение операций
ROLLBACK TO my_savepoint; -- после сохранения произошла ошибка, откат на состояние my_savepoint
-- выполнение другого сценария
IF check_ok() THEN:
COMMIT; -- фиксация результата
ELSE
ROLLBACK;
END IF;

=== Создание таблицы истории ===

Для задания понадобится таблица истории, сделать ее проще всего унаследовав от существующей, а затем убрав наследование и добавив колонку для времени изменения:

CREATE TABLE tablename_history INHERITS (tablename); -- копирует структуру таблицы без записей
ALTER TABLE tablename_history NO INHERIT tablename;

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

2. Создать функцию с входным параметром - имя актера. Если актер с таким именем не найден, функция должна вернуть 0. Если найден, то вычислить через person_info birth date (info_type_id=21) и death date (info_type_id=23) возраст актера. Если актер еще не умер, то вычислить, сколько сейчас лет. Функция должна вернуть целое число прожитых лет.

3. Используя функцию, созданную ранее, создайте процедуру, которая по имени актера выводит в лог текст следующего содержания:
Name: ...
Nicknames: nickname1, nickname2, ... строка выводится только если у человека есть клички, выводятся все клички через запятую
Age: ... вычисляется из функции
First appear: ... фильм и год выхода, в котором актер впервые снялся.

Если актер не найден или не удалось определить его первую роль, то процедура должна завершиться с ошибкой и вывести в лог "Invalid data"

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

== Дополнительно ==
* http://www.postgresql.org/docs/9.3/static/plpgsql.html
* http://postgres.cz/wiki/PL/pgSQL_%28en%29 - примеры процедур, функций и триггеров
* http://www.postgresql.org/docs/9.3/static/tutorial-transactions.html
* http://www.postgresql.org/docs/9.3/static/plpython.html - позволяет писать хранимые процедуры на питоне, нужно включить расширение

Базы данных/Лабораторная работа 4

2016-06-07T16:19:53Z

ADKosm: ALTER SEQUENCE INCREMENT изменяет значение параметра increment_by, а не last_value. Не отображались кавычки в последнем аргументе replace. Не нужно добавлять 1 к max(id)

Цели лабораторной работы:
* Знакомство с языком PL/pgSQL
* Создание процедур, функций и триггеров, обеспечивающих удобство работы с базой и обеспечение целостности
* Использование транзакция для обеспечения целостности

== Зачем использовать PL/pgSQL ==

PL/pgSQL - процедурный язык программирования, позволяющий выполнять несложные скрипты. Язык PL/pgSQL является расширенной версией стандарта языка PL/SQL. В PostgreSQL есть также возможность расширить синтаксис встроенного языка за счет интерпретаторов популярных языков: Python, Perl, Java, Lua, R и тд.

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

К наиболее типичным задачам, реализуемым на PL/SQL относятся:
* Выполнение и контроль целостности операций с высокими требованиями к скорости исполнения (например, финансовые операции, событиями систем мониторинга)
* Аудит изменения данных: созранение истории изменения значений кортежей наиболее важных отношений в системе.
* Генерация разовых периодических отчетов, в которых аналитируется большой объем данных (для экономии времени пересылки данных между клиентом и сервером).

Также к преимуществам использования PL/SQL является использование одного языка программирования для выполнения запросов и для выполнения инструкций.

Как правило, язык PL/pgSQL не используют в задачах, где некритичны его преимущества из-за относительно сложного процесса поддержки и нагрузки на сервер СУБД.

== Процедуры и функции ==

Отличие процедур и функций состоит лишь в том, что процедуры не возвращают никаких значений, в отличии от функций. И процедуры и функции могут принимать входные параметры.

Но в PostgreSQL нет различий в синтаксисе создания функций и процедур, обе создаются с помощью ключевых слов create function.

Пример создания процедуры:

CREATE OR REPLACE FUNCTION add_movie(title VARCHAR(70), production_year INTEGER)
RETURNS void AS $$
DECLARE phonetic_code VARCHAR(70);
BEGIN
SELECT soundex(title) INTO phonetic_code;
RAISE NOTICE 'The phonetic code for % is %', title, phonetic_code;
INSERT INTO title (title, production_year, phonetic_code, kind_id) VALUES (title, production_year, phonetic_code, 1);
COMMIT;
END;
$$ LANGUAGE plpgsql;

Тело процедуры находится между BEGIN и END. При этом внутри тела могут быть транзакции, которые также будут обернуты в BEGIN-END.

В примере также используется soundex, которая доступна после подключения модуля (выполните до создания процедуры):

imdb=# create extension fuzzystrmatch;

Чтобы выполнить созданную процедуру, выберите ее:

select add_movie('How I will pass all the exams', 2016);

Этот пример сейчас скорее всего не сработает с ERROR: duplicate key value violates unique constraint "title_pkey". В конце этой части есть процедура, которая поможет вам добавить фильм.

=== Определение переменных ===

Определение переменных происходит с помощью команды DECLARE, после которой нужно указать имя и тип. Также можно сразу присвоить значение:

DECLARE production_year =: 2016;

Определять переменные можно между BEGIN и END - в этом случае они действуют только, или сразу после определения процедуры (перед BEGIN как в примере выше), тогда переменная будет глобальной и доступна во всей процедуре.

Чтобы записать в переменную кортеж или одно значение, нужно использовать SELECT ... INTO var:

SELECT count(1) INTO actors_num from cast_info WHERE ...

=== Управляющие конструкции ===

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

BEGIN
SELECT * INTO movie FROM title WHERE title like '%'||movie_name||'%';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE EXCEPTION 'movie % not found', movie_name;
WHEN TOO_MANY_ROWS THEN
RAISE EXCEPTION 'movie % not unique', movie_name;
END;

В этом примере также есть конструкция title like '%'||movie_name||'%', в которой movie_name - переменная, хранящая часть название фильма. Конкатенация строк выполняется оператором ||.

Также доступны циклы и условия:

FOR movie_row IN SELECT * FROM title limit 10 LOOP
RAISE NOTICE 'Currently working on movie %s ...', quote_ident(movie_row.title);
IF movie_row.production_year > 2000 THEN
RAISE NOTICE 'This movie is of this century';
END IF;
END LOOP;

Эта конструкция проходит по кортежам, возвращаемым запросом SELECT * FROM title limit 10, для каждого выводит его название и, если фильм выпущен после 2000 года, то сообщение о его новизне.

=== Использование курсоров ===

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

Определения курсора:

DECLARE
curs1 refcursor; -- будет указан в будущем
curs2 CURSOR FOR SELECT * FROM cast_info; -- сразу же указывает на запрос (запрос не выполняется в момент определения)

Чтобы начать пользоваться курсором, его нужно открыть:

OPEN curs1 FOR SELECT * FROM cast_info; -- если курсор не был привязан к запросу ранее
OPEN curs2; -- для запросов, привязанных к запросу

После этого навигация курсора может быть следующей:

FETCH curs1 INTO rowvar; -- получить следующий кортеж запроса
MOVE curs1; -- то же самое
MOVE LAST FROM curs3; -- получить последний кортеж запроса
MOVE RELATIVE -2 FROM curs4; -- сдвинуть относительно текущего положения запроса на 2 кортежа назад
MOVE FORWARD 2 FROM curs4; -- сдвинуть курсор на 2 кортежа вперед.

Когда курсор не нужен или требуется его переоткрыть, то используется команда:

CLOSE curs1;

=== Логирование и обработка ошибок ===

Вывод информационного сообщения (при этом процедура продолжит работу дальше):

RAISE NOTICE 'Data processing...'

Команда завершающая процедуру или транзакцию с ошибкой:

RAISE EXCEPTION 'Something went wrong'

=== Пример полезной для администрирования процедуры ===

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

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

CREATE OR REPLACE FUNCTION fix_sequences()
RETURNS void AS $$
DECLARE max_id INTEGER;
DECLARE t RECORD; -- переменная для обхода кортежей выборки
BEGIN
FOR t IN (select table_name from pg_class s
join information_schema.tables on tables.table_schema = 'public' and table_name=replace(relname, '_id_seq', '' )
where s.relkind='S') LOOP
EXECUTE 'select max(id) from '||t.table_name into max_id; -- выполняет запрос, сформированный в строке и передает результат в max_id
raise notice 'max id for % is %', t.table_name, max_id;
if max_id is not null then
-- выполняет запрос увеличения текущего значения последовательности
EXECUTE 'SELECT setval('''||t.table_name||'_id_seq'', '||max_id||', true)';
end if;
END LOOP;
END;
$$ LANGUAGE plpgsql;

select fix_sequences(); -- вызов процедуры

== Триггеры ==

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

=== Типы триггеров ===

Триггер может быть привязан к командам UPDATE, INSERT и DELETE конкретных отношений. Также триггер может быть запущен как до выполнения указанной операции, так и после. Например:

CREATE TRIGGER cool_trigger_name BEFORE INSERT OR UPDATE ON movie_info
...

Этот триггер выполнится перед операциями добавления и обновления данных в таблице movie_info.

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

CREATE OR REPLACE FUNCTION check_rows() RETURNS trigger AS $$
BEGIN
IF NEW.title is null THEN
RAISE EXCEPTION 'Invalid title';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_check BEFORE INSERT OR UPDATE ON title
FOR EACH ROW EXECUTE PROCEDURE check_rows();

Старые и новые значения данных доступны соответственно через OLD.* и NEW.* для каждого кортежа.

== Транзакции ==

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

Управление транзакцией выполняется с помощью команд BEGIN, SAVEPOINT, ROLLBACK и COMMIT. Пример сценария сложной транзакции:

BEGIN; -- начало транзакции
-- выполнение операций
SAVEPOINT my_savepoint; -- сохранить точку, на которую можно потом вернуться,
-- но изменения в базу все еще не вносятся
-- выполнение операций
ROLLBACK TO my_savepoint; -- после сохранения произошла ошибка, откат на состояние my_savepoint
-- выполнение другого сценария
IF check_ok() THEN:
COMMIT; -- фиксация результата
ELSE
ROLLBACK;
END IF;

=== Создание таблицы истории ===

Для задания понадобится таблица истории, сделать ее проще всего унаследовав от существующей, а затем убрав наследование и добавив колонку для времени изменения:

CREATE TABLE tablename_history INHERITS (tablename); -- копирует структуру таблицы без записей
ALTER TABLE tablename_history NO INHERIT tablename;

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

2. Создать функцию с входным параметром - имя актера. Если актер с таким именем не найден, функция должна вернуть 0. Если найден, то вычислить через person_info birth date (info_type_id=21) и death date (info_type_id=23) возраст актера. Если актер еще не умер, то вычислить, сколько сейчас лет. Функция должна вернуть целое число прожитых лет.

3. Используя функцию, созданную ранее, создайте процедуру, которая по имени актера выводит в лог текст следующего содержания:
Name: ...
Nicknames: nickname1, nickname2, ... строка выводится только если у человека есть клички, выводятся все клички через запятую
Age: ... вычисляется из функции
First appear: ... фильм и год выхода, в котором актер впервые снялся.

Если актер не найден или не удалось определить его первую роль, то процедура должна завершиться с ошибкой и вывести в лог "Invalid data"

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

== Дополнительно ==
* http://www.postgresql.org/docs/9.3/static/plpgsql.html
* http://postgres.cz/wiki/PL/pgSQL_%28en%29 - примеры процедур, функций и триггеров
* http://www.postgresql.org/docs/9.3/static/tutorial-transactions.html
* http://www.postgresql.org/docs/9.3/static/plpython.html - позволяет писать хранимые процедуры на питоне, нужно включить расширение