One place for hosting & domains

      Использование расширения PDO PHP для выполнения транзакций MySQL на PHP в Ubuntu 18.04


      Автор выбрал организацию Open Sourcing Mental Illness для получения пожертвований в рамках программы Write for DOnations.

      Введение

      Транзакция MySQL представляет собой группу логически связанных команд SQL, выполняемых в базе данных как единое целое. Транзакции используются для обеспечения соблюдения приложением принципов ACID (атомарность, единообразие, изоляция и долговечность). Эти принципы лежат в основе стандартов, определяющих надежность операций в базе данных.

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

      Команды SQL в составе транзакции обрабатываются в комплексе с успешным или неуспешным результатом. В случае неудачного результата любого запроса MySQL откатывает изменения, и они не записываются в базу данных.

      В качестве примера работы транзакций MySQL можно рассмотреть сайт электронной коммерции. Когда клиент делает заказ, приложение вставляет записи в несколько таблиц в зависимости от бизнес-логики, например, в таблицы orders и orders_products. Записи в нескольких таблицах, связанные с одним заказом, должны атомарно отправляться в базу данных как одна логическая единица.

      Другой пример использования транзакций — банковское приложение. Когда клиент переводит деньги, в базу данных передается несколько транзакций. Одна транзакция отвечает за списание денежных средств со счета отправителя, а другая — за их начисление на счет получателя. Эти две транзакции должны обрабатываться одновременно. Если одна из транзакций будет неуспешной, база данных вернется в исходное состояние, и на диске не будут сохранены никакие изменения.

      В этом обучающем руководстве мы будем использовать расширение PDO PHP, обеспечивающее интерфейс работы с базами данных в PHP для выполнения транзакций MySQL на сервере Ubuntu 18.04.

      Предварительные требования

      Прежде чем мы начнем, нам потребуется следующее:

      Шаг 1 — Создание тестовой базы данных и таблиц

      Прежде чем начать работу с транзакциями MySQL, мы создадим образец базы данных и добавим несколько таблиц. Вначале войдите на сервер MySQL как root:

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

      • CREATE DATABASE sample_store;

      Результат будет выглядеть следующим образом:

      Output

      Query OK, 1 row affected (0.00 sec)

      Создайте для базы данных пользователя с именем sample_user. Обязательно замените PASSWORD на более надежный пароль:

      • CREATE USER 'sample_user'@'localhost' IDENTIFIED BY 'PASSWORD';

      Предоставьте пользователю полные права доступа к базе данных sample_store:

      • GRANT ALL PRIVILEGES ON sample_store.* TO 'sample_user'@'localhost';

      В заключение перезагрузите права доступа MySQL:

      После создания пользователя вы увидите следующие результаты:

      Output

      Query OK, 0 rows affected (0.01 sec) . . .

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

      Выполните выход из сервера MySQL:

      После выхода из системы вы увидите следующий экран:

      Output

      Bye.

      Войдите в систему с учетными данными пользователя sample_user, которого мы только что создали:

      • sudo mysql -u sample_user -p

      Введите пароль пользователя sample_user и нажмите ENTER, чтобы продолжить.

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

      Выбрав базу данных, вы увидите следующий экран:

      Output

      Database Changed.

      Создайте таблицу products:

      • CREATE TABLE products (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50), price DOUBLE) ENGINE = InnoDB;

      Эта команда создает таблицу products с полем product_id. Мы используем тип данных BIGINT, поддерживающий большие значения до 2^63-1. Для уникальной идентификации продуктов мы используем то же самое поле, что и PRIMARY KEY. Ключевое слово AUTO_INCREMENT предписывает MySQL генерировать следующее числовое значение при вставке новых продуктов.

      Поле product_name относится к типу VARCHAR, который позволяет сохранять до 50 буквенно-цифровых символов. Для поля price продукта мы используем тип данных DOUBLE с плавающей запятой, чтобы в этом поле можно было размещать цены в форме десятичных чисел.

      Мы используем InnoDB как ENGINE, поскольку эта база данных обеспечивает удобную поддержку транзакций MySQL в отличие от других систем хранения, таких как MyISAM.

      После создания таблицы products вы увидите следующий экран:

      Output

      Query OK, 0 rows affected (0.02 sec)

      Добавьте в таблицу products несколько элементов, выполнив следующие команды:

      • INSERT INTO products(product_name, price) VALUES ('WINTER COAT','25.50');
      • INSERT INTO products(product_name, price) VALUES ('EMBROIDERED SHIRT','13.90');
      • INSERT INTO products(product_name, price) VALUES ('FASHION SHOES','45.30');
      • INSERT INTO products(product_name, price) VALUES ('PROXIMA TROUSER','39.95');

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

      Output

      Query OK, 1 row affected (0.02 sec) . . .

      Проверьте добавление данных в таблицу products:

      Вы увидите список из четырех вставленных продуктов:

      Output

      +------------+-------------------+-------+ | product_id | product_name | price | +------------+-------------------+-------+ | 1 | WINTER COAT | 25.5 | | 2 | EMBROIDERED SHIRT | 13.9 | | 3 | FASHION SHOES | 45.3 | | 4 | PROXIMA TROUSER | 39.95 | +------------+-------------------+-------+ 4 rows in set (0.01 sec)

      Далее мы создадим таблицу customers для хранения базовых данных о клиентах:

      • CREATE TABLE customers (customer_id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_name VARCHAR(50) ) ENGINE = InnoDB;

      Как и в случае с таблицей products, мы используем тип данных BIGINT для customer_id, и благодаря этому таблица может поддерживать до 2^63-1 записей о клиентах. Ключевое слово AUTO_INCREMENT инкрементально увеличивает значение columns после вставки нового клиента.

      Поскольку столбец customer_name принимает буквенно-цифровые значения, мы используем тип данных VARCHAR с лимитом 50 символов. Мы снова указываем InnoDB в поле ENGINE как систему хранения для поддержки транзакций.

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

      Output

      Query OK, 0 rows affected (0.02 sec)

      Добавим в таблицу трех клиентов в качестве примера. Запустите следующие команды:

      • INSERT INTO customers(customer_name) VALUES ('JOHN DOE');
      • INSERT INTO customers(customer_name) VALUES ('ROE MARY');
      • INSERT INTO customers(customer_name) VALUES ('DOE JANE');

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

      Output

      Query OK, 1 row affected (0.02 sec) . . .

      Затем проверьте данные в таблице customers:

      Вы увидите список из трех клиентов:

      Output

      +-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | JOHN DOE | | 2 | ROE MARY | | 3 | DOE JANE | +-------------+---------------+ 3 rows in set (0.00 sec)

      Далее мы создадим таблицу orders для записи заказов, размещаемых разными клиентами. Чтобы создать таблицу orders, выполните следующую команду:

      • CREATE TABLE orders (order_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_date DATETIME, customer_id BIGINT, order_total DOUBLE) ENGINE = InnoDB;

      Столбец order_id будет использоваться как PRIMARY KEY. Тип данных BIGINT позволяет размещать до 2^63-1 заказов и автоматически выполняет инкрементальное увеличение после добавления каждого заказа. Поле order_date будет содержать фактические дату и время размещения заказа, и поэтому для него мы используем тип данных DATETIME. Поле customer_id связано с таблицей customers, которую мы создали на предыдущем шаге.

      Результат будет выглядеть следующим образом:

      Output

      Query OK, 0 rows affected (0.02 sec)

      Поскольку заказ одного клиента может содержать несколько товаров, нам нужна таблица orders_products для хранения этой информации.

      Для создания таблицы orders_products запустите следующую команду:

      • CREATE TABLE orders_products (ref_id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT, product_id BIGINT, price DOUBLE, quantity BIGINT) ENGINE = InnoDB;

      Мы используем ref_id как PRIMARY KEY для автоматического инкрементального увеличения после каждой вставки записи. Поля order_id и product_id связаны с таблицами orders и products соответственно. Столбец price относится к типу данных DOUBLE, что позволяет размещать в нем значения в плавающей запятой.

      Система хранения InnoDB должна сопоставлять другие ранее созданные таблицы, поскольку при использовании транзакций один заказ повлияет на несколько таблиц.

      Сообщение на экране подтвердит создание таблицы:

      Output

      Query OK, 0 rows affected (0.02 sec)

      Пока мы не будем добавлять данные в таблицы orders и orders_products, но позднее мы сделаем это с помощью скрипта PHP для выполнения транзакций MySQL.

      Выполните выход из сервера MySQL:

      Наша схема базы данных готова, и мы заполнили ее несколькими записями. Теперь мы создадим класс PHP для обработки соединений базы данных и транзакций MySQL.

      Шаг 2 — Проектирование класса PHP для обработки транзакций MySQL

      На этом шаге мы создадим класс PHP, который будет использовать объекты PDO (объекты данных PHP) для обработки транзакций MySQL. Этот класс будет подключаться к нашей базе данных MySQL и атомарно вставлять данные в базу данных.

      Сохраните файл класса в корневой директории вашего веб-сервера Apache. Для этого создайте файл DBTransaction.php в текстовом редакторе:

      • sudo nano /var/www/html/DBTransaction.php

      Затем добавьте в файл следующий код. Замените PASSWORD значением, созданным на шаге 1:

      /var/www/html/DBTransaction.php

      <?php
      
      class DBTransaction
      {
          protected $pdo;
          public $last_insert_id;
      
          public function __construct()
          {
              define('DB_NAME', 'sample_store');
              define('DB_USER', 'sample_user');
              define('DB_PASSWORD', 'PASSWORD');
              define('DB_HOST', 'localhost');
      
              $this->pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);
              $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
              $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
          }
      

      В начале класса DBTransaction PDO будет использовать константы (DB_HOST, DB_NAME, DB_USER и DB_PASSWORD) для инициализации и подключения к базе данных, созданной нами на шаге 1.

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

      Затем мы зададим два атрибута класса PDO:

      • ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION: этот атрибут предписывает PDO выдать исключение при обнаружении ошибки. Такие ошибки можно регистрировать в журнале для целей отладки.
      • ATTR_EMULATE_PREPARES, false: данная опция отключает эмуляцию подготовленных выражений и позволяет MySQL самостоятельно готовить выражения.

      Добавьте в файл следующий код для создания методов класса:

      /var/www/html/DBTransaction.php

      . . .
          public function startTransaction()
          {
              $this->pdo->beginTransaction();
          }
      
          public function insertTransaction($sql, $data)
          {
              $stmt = $this->pdo->prepare($sql);
              $stmt->execute($data);
              $this->last_insert_id = $this->pdo->lastInsertId();
          }
      
          public function submitTransaction()
          {
              try {
                  $this->pdo->commit();
              } catch(PDOException $e) {
                  $this->pdo->rollBack();
                  return false;
              }
      
                return true;
          }
      }
      

      Сохраните и закройте файл, нажав CTRL + X, Y, а затем ENTER.

      Для работы с транзакциями MySQL мы создаем три основных метода в классе DBTransaction; startTransaction, insertTransaction и submitTransaction.

      • startTransaction: этот метод предписывает PDO запустить транзакцию и отключает автоматическую запись до отправки команды commit.

      • insertTransaction: этот метод принимает два аргумента. Переменная $sql хранит выражение SQL, выполняемое, пока переменная $data имеет значение массива данных, которые требуется привязать к выражению SQL, поскольку вы используете подготовленные выражения. Данные передаются как массив в метод insertTransaction.

      • submitTransaction: этот метод записывает изменения в базу данных на постоянной основе с помощью команды commit(). При обнаружении ошибки и проблемах с транзакциями этот метод вызывает метод rollBack() для возвращения базы данных в первоначальное состояние в случае создания исключения PDO.

      Ваш класс DBTransaction инициализирует транзакцию, готовит разные команды SQL к выполнению и выполняет атомарную запись изменений в базу данных при отсутствии проблем. В противном случае выполняется откат транзакции. Кроме того, этот класс позволяет получить только что созданную запись order_id из общедоступного свойства last_insert_id.

      Теперь мы можем вызывать класс DBTransaction и использовать его в любом коде PHP, о создании которого мы поговорим далее.

      Шаг 3 — Создание скрипта PHP для использования класса DBTransaction

      Мы создадим скрипт PHP, который будет реализовывать класс DBTransaction и отправлять группу команд SQL в базу данных MySQL. Вы сможете имитировать рабочий процесс обработки заказа клиента в онлайн-корзине.

      Эти запросы SQL будут влиять на таблицы orders и orders_products. Ваш класс DBTransaction должен допускать изменения базы данных только в случае выполнения всех запросов без ошибок. В ином случае вы получите сообщение об ошибке и будет выполнен откат всех изменений.

      Вы создаете один заказ для клиента JOHN DOE с идентификатором customer_id 1. Заказ клиента содержит три разных товара в разном количестве из таблицы products. Скрипт PHP берет данные по заказу клиента и отправляет их в класс DBTransaction.

      Создайте файл orders.php:

      • sudo nano /var/www/html/orders.php

      Добавьте в файл следующий код:

      /var/www/html/orders.php

      <?php
      
      require("DBTransaction.php");
      
      $db_host = "database_host";
      $db_name = "database_name";
      $db_user = "database_user";
      $db_password = "PASSWORD";
      
      $customer_id = 2;
      
      $products[] = [
        'product_id' => 1,
        'price' => 25.50,
        'quantity' => 1
      ];
      
      $products[] = [
        'product_id' => 2,
        'price' => 13.90,
        'quantity' => 3
      ];
      
      $products[] = [
        'product_id' => 3,
        'price' => 45.30,
        'quantity' => 2
      ];
      
      $transaction = new DBTransaction($db_host, $db_user, $db_password, $db_name);
      

      Вы создали скрипт PHP, который инициализирует экземпляр класса DBTransaction, созданный вами на шаге 2.

      В этом скрипте мы включаем файл DBTransaction.php и инициализируем класс DBTransaction. Затем мы подготовим многомерный массив из всех товаров, которые клиент заказывает в магазине. Вы также сможете вызывать метод startTransaction() для запуска транзакций.

      Затем добавьте следующий код для завершения скрипта orders.php:

      /var/www/html/orders.php

      . . .
      $order_query = "insert into orders (order_id, customer_id, order_date, order_total) values(:order_id, :customer_id, :order_date, :order_total)";
      $product_query = "insert into orders_products (order_id, product_id, price, quantity) values(:order_id, :product_id, :price, :quantity)";
      
      $transaction->insertQuery($order_query, [
        'customer_id' => $customer_id,
        'order_date' => "2020-01-11",
        'order_total' => 157.8
      ]);
      
      $order_id = $transaction->last_insert_id;
      
      foreach ($products as $product) {
        $transaction->insertQuery($product_query, [
          'order_id' => $order_id,
          'product_id' => $product['product_id'],
          'price' => $product['price'],
          'quantity' => $product['quantity']
        ]);
      }
      
      $result = $transaction->submit();
      
      if ($result) {
          echo "Records successfully submitted";
      } else {
          echo "There was an error.";
      }
      
      

      Сохраните и закройте файл, нажав CTRL + X, Y, а затем ENTER.

      Команда для вставки в таблицу orders готовится с помощью метода insertTransaction. После этого мы получаем значение общедоступного свойства last_insert_id из класса DBTransaction и используем его как $order_id.

      Теперь у нас есть значение $order_id и мы можем использовать уникальный идентификатор для вставки заказанных клиентом товаров в таблицу orders_products.

      В заключение мы вызовем метод submitTransaction для записи всех деталей заказа клиента в базу данных в случае отсутствия проблем. При обнаружении проблем метод submitTransaction произведет откат предлагаемых изменений.

      Запустим скрипт orders.php в браузере. Введите следующий URL, заменив your-server-IP публичным IP-адресом вашего сервера:

      http://your-server-IP/orders.php

      Вы увидите подтверждение успешной отправки записей:

      Вывод PHP из класса MySQL Transactions

      Наш скрипт PHP работает ожидаемым образом и выполнил атомарную отправку в базу данных заказа и товаров в заказе.

      Мы запустили файл orders.php в окне браузера. Скрипт вызвал класс DBTransaction, который отправил детали orders в базу данных. На следующем шаге мы проверим сохранение записей в связанных таблицах базы данных.

      Шаг 4 — Подтверждение записей в базе данных

      На этом шаге мы убедимся, что транзакция заказа клиента, инициированная через браузер, была размещена в таблицах базы данных ожидаемым образом.

      Для этого снова выполним вход в базу данных MySQL:

      • sudo mysql -u sample_user -p

      Введите пароль пользователя sample_user и нажмите ENTER, чтобы продолжить.

      Переключитесь на базу данных sample_store:

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

      Output

      Database Changed.

      Затем используйте следующую команду для извлечения записей из таблицы orders:

      Появится следующий экран с информацией о заказе клиента:

      Output

      +----------+---------------------+-------------+-------------+ | order_id | order_date | customer_id | order_total | +----------+---------------------+-------------+-------------+ | 1 | 2020-01-11 00:00:00 | 2 | 157.8 | +----------+---------------------+-------------+-------------+ 1 row in set (0.00 sec)

      Затем извлеките записи из таблицы orders_products:

      • SELECT * FROM orders_products;

      Вы увидите примерно следующий экран со списком товаров из заказа клиента:

      Output

      +--------+----------+------------+-------+----------+ | ref_id | order_id | product_id | price | quantity | +--------+----------+------------+-------+----------+ | 1 | 1 | 1 | 25.5 | 1 | | 2 | 1 | 2 | 13.9 | 3 | | 3 | 1 | 3 | 45.3 | 2 | +--------+----------+------------+-------+----------+ 3 rows in set (0.00 sec)

      Этот экран подтверждает, что транзакция сохранена в базе данных и что класс-помощник DBTransaction работает ожидаемым образом.

      Заключение

      В этом обучающем руководстве мы использовали PHP PDO для работы с транзакциями MySQL. Хотя это не исчерпывающая статья о разработке ПО для сайта электронной коммерции, здесь содержится пример использования транзакций MySQL в ваших приложениях.

      Чтобы узнать больше о модели MySQL ACID, ознакомьтесь с руководством по InnoDB и модели ACID на официальном сайте MySQL. Посетите страницу материалов по MySQL, чтобы найти другие обучающие руководства, статьи и ответы на вопросы.



      Source link


      Leave a Comment