LXF76:PHP

Материал из Linuxformat.

PHP Транзакции и триггеры

Часть 1. MySQL наконец-то догнал 21 век. Пол Хадсон (Paul Hadson) уже тут и готов помочь вам освоить его.

Я в восторге от баз данных. Всегда был и всегда буду. Это началось ещё в университете, где мы с другом (Шалом!) проводили бесчисленные счастливые часы, загружая тексты в базу данных, а потом выковыривая их при помощи SQL+. Но затем я перешёл в мир открытых исходных кодов, встал на Мистический Путь PHP и вскоре влюбился в MySQL.

Поначалу я был немного расстроен тем, что MySQL не поддерживал подзапросы, но зато он был по-настоящему быстрым! Затем, спустя много месяцев, меня огорчило отсутствие поддержки хранимых процедур в MySQL. Это действительно раздражает, но зато скорость попрежнему делала меня счастливым. Отношения продолжались, и дополнительные проблемы начинали действовать мне на нервы: отсутствие триггеров, представлений, поддержки целостности по внешним ключам, транзакций, наконец! Так продолжалось некоторое время. Я радовался скорости, но в глубине души меня грызло сомнение, что MySQL – это не Настоящая База Данных.

Но сегодня MySQL 5.0 наконец с нами, и моя любовь вновь проснулась благодаря его разработчикам, которые наконец-то добавили все недостающие возможности. Теперь я хочу передать вам страсть к этой базе данных со всем энтузиазмом второго медового месяца. Вооружившись теорией баз данных и самыми крутыми способностями 5.0, за следующие три выпуска я собираюсь превратить вас в SQL-героя.

Вашим первым заданием станет выходить на сцену везде, где ACID-совместимости что-то угрожает. Затем мы создадим несколько триггеров для того, чтобы данные всегда оставались чисты. Но давайте вернёмся на шаг назад. Наверное, сначала лучше ответить на вопрос, который вы только что задали – «Что такое ACID-совместимость?».

Транзакционный анализ

Чтобы стать гуру баз данных, вам придётся изучить немножко теории, а поскольку MySQL наконец-то поддерживает транзакции, сейчас самое время начинать. Базы данных понимают запросы на языке SQL, которые требуют выполнить некоторые действия. Например, строки таблицы можно прочитать, изменить или удалить. Хорошим тоном является выполнять запросы внутри транзакции, то есть сказать серверу предварительно «Эй, я собираюсь выполнить один или несколько запросов», затем передать сами запросы, и в итоге сказать «Я закончил передавать запросы, теперь выполни их!». Вплоть до последнего этапа ваши указания выполняются на какой-то виртуальной копии данных. Вы, и только вы можете увидеть, как изменяется содержимое таблиц. После того, как вы закончили отправлять запросы, вы можете подтвердить сделанные изменения (commit), при этом ваши изменения сохранятся в основной базе и их увидят все. Вы также можете откатить их (roll back), после чего все ваши изменения с самого начала транзакции будут забыты.

Думайте про ACID как про лучший метод организации транзакции (если вы вообще позволяете себе эту ненавистную фразу «лучший метод»). На самом деле это стандартная для баз данных аббревиатура, состоящая из следующих компонентов:

• Atomicity (атомарность). Либо все запросы внутри одной транзакции должны пройти успешно, либо ни одного. Если сервер падает во время выполнения транзакции, то он должен либо потребовать подтверждения транзакций, начавшихся до аварии, либо откатиться до состояния, предшествовавшего началу транзакции.
• Consistency (непротиворечивость). База данных должна контролировать своё состояние и оставаться непротиворечивой как до транзакции, так и после. Если вы задали базе данных специфические ограничения (например, указали, что значения некоторого поля должны лежать в указанном диапазоне), то в конце транзакции эти правила должны быть соблюдены.
• Isolation (изоляция). Сервер баз данных должен гарантировать, что любые изменения, которые вы делаете внутри незавершенной транзакции, остаются невидимы для других пользователей до тех пор, пока вы не выполните commit.
• Durability (долговечность). После того, как вы подтвердите транзакцию, сделанные ею изменения должны остаться навсегда.

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

Для того, чтобы использовать транзакции, вам нужна база данных, которая их поддерживает. MySQL часто по умолчанию создаёт таблицы в формате MyISAM, который не поддерживает транзакционность. Для проверки наберите команду SHOW TABLE STATUS и посмотрите, какой формат использован для вашей таблицы. Если это InnoDB, то всё хорошо. Если это MyISAM, то вы получите хорошую скорость, но не сможете проводить транзакции. Для того, чтобы создать таблицу InnoDB, можно использовать примерно такой запрос:

CREATE TABLE exam_grades (ID INT NOT NULL AUTO_INCREMENT 
PRIMARY KEY, StudentID INT NOT NULL, Percentage TINYINT NOT NULL) 
ENGINE = InnoDB;

Другой вариант – это создать таблицу в формате MyISAM и превратить её в InnoDB при помощи команды ALTER TABLE:

ALTER TABLE exam_grades ENGINE = InnoDB;

Когда у меня есть скрипт, выполняющий вставку большого объёма данных, я предпочитаю создать таблицу в MyISAM-формате, заполнить её данными и потом преобразовать в InnoDB. Так получается гораздо быстрее, чем использовать InnoDB с самого начала.

Триггер счастья

Давайте теперь рассмотрим триггеры, ещё одну новую для MySQL 5.0 вещь. Триггеры – это функции, выполняющиеся в ответ на какие-то изменения в базе данных. Например, вы можете написать триггер, который выполняется при вставке новой строки в какую-либо таблицу. Вы можете использовать этот триггер для проверки содержимого новой строчки и отменить вставку, если данные вас не устраивают. Это самый стандартный способ использования триггеров, в этом случае код выглядит примерно так:

DELIMITER //
CREATE TRIGGER exam_grades_percentage_check BEFORE INSERT ON exam_grades
FOR EACH ROW
BEGIN
IF NEW.Percentage > 100 THEN
SET NEW.Percentage = 100;
END IF;
END//

Вы должны войти в MySQL под учётной записью root, чтобы использовать триггеры ^[1]. В нашем примере триггер проверяет данные перед тем, как они добавляются в таблицу и подправляет оценки студентов до 100 процентов. Созданный триггер имеет имя exam_ grades_percentage_check, длинна которого обусловлена тем, что триггер существует в базе данных как объект первого уровня. Поэтому если вы назовёте триггер insert_check, то без команды SHOW_ TRIGGER будет непонятно, вставку в какую таблицу он проверяет. Триггеры не «прикреплены» к своим таблицам, поэтому имя триггера должно быть уникально в пределах всей базы данных.

В MySQL существует шесть типов триггеров – BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE и AFTER. В нашем примере триггер будет вызван, когда начнётся вставка строки в таблицу, но до того, как она будет окончательно добавлена. Если вы собираетесь менять заносимую в базу информацию, то это лучший вариант. С другой стороны, AFTER INSERT идеально подходит, если вам надо обработать всю таблицу целиком, включая добавленную строчку.

Двигаемся дальше. Мы указали, что наш код надо выполнить для каждой новой строки по-отдельности (при помощи выражения FOR EACH ROW). Важно указать, хотите ли вы запускать триггер для каждой отдельной строчки, или вам достаточно одного запуска на весь блок вставляемых записей. Если вы хотите просто произвести какие-то расчёты после того, как в таблице появились новые данные, вам логичнее использовать FOR EACH STATEMENT.

Итак, дальше мы имеем дело с телом триггера. По сути дела триггер – это всего лишь набор команд SQL, поэтому мы при помощи оператора DELIMITER переопределили строку, по которой MySQL определяет завершение команды. Задав DELIMITER равным //, мы получили возможность использовать точку с запятой внутри кода триггера, не завершая при этом ввод запроса на его создание. Поскольку наш триггер состоит из нескольких строк, нам потребуются операторы BEGIN и END, чтобы обрамить его.

Нам осталось рассмотреть самую важную часть – собственно сам триггер. MySQL использует переменные OLD и NEW для обозначения старого и нового варианта содержимого изменяемой строчки. В нашем примере мы проверяем у новой строчки значение поля Percentage – не превышает ли оно 100 процентов, и в этом случае устанавливаем его равным 100. При помощи переменной OLD вы можете создать триггер BEFORE UPDATE, который делает NEW.SomeValue равным OLD.SomeValue, если значение NEW.SomeValue вас не устраивает.

После создания триггера важно не забыть вызвать DELIMITER; чтобы вернуть MySQL в нормальный режим обработки запросов.

Шифрование на лету.

Хотя PHP – это мощный и гибкий язык программирования, вы можете обнаружить, что вынуждены работать на сервере, на котором PHP скомпилирован с очень небольшим набором поддерживаемых функций. В этой ситуации часто используют имеющийся под рукой MySQL сервер для реализации функций, которые оказались не включены в PHP. Возьмём, для примера, шифрование – MySQL 5.0 поддерживает AES-шифрование любых передаваемых ему данных. Что более важно, мы можем настроить шифрование в триггере, так что пользователи, которые выполняют запись и чтение данных в базе, даже не догадываются, о том, что их информация шифруется, в то время как за кулисами MySQL сохраняет и читает информацию, используя AES. Помните, что задача MySQL – хранить и доставать данные, а задача PHP – обрабатывать их. В терминах ключевой компетенции именно MySQL должен отвечать за шифрование данных.

Вот триггер, которым вы можете автоматически зашифровывать пароли в базе данных пользователей.

DELIMITER //
CREATE TRIGGER users_insert BEFORE INSERT ON users
FOR EACH ROW SET NEW.Password = AES_ENCRYPT(NEW.Password,’ВашСекретныйКлюч’)//

Поскольку весь наш триггер состоит из одной строки, BEGIN и END нам не потребуются, и код выглядит гораздо проще. Если теперь вы попробуете вставить в таблицу новую строчку, то увидите, что значение Password окажется зашифровано, как и планировалось. Ура! Мы сделали ещё один шаг на пути к мастерству.

1. ↑ Это не совсем так. Вы должны иметь права super_priv для создания и удаления триггеров в MySQL, но это точно такие же права, какие имеют администраторы сервера, так что обычно я просто использую учётную запись root для работы с триггерами.