Spec-Zone.ru › Yii 2.0

Объекты доступа к базам данных

  1. Создание подключений к БД
  2. Выполнение SQL запросов
  3. Обрамление имен таблиц и столбцов
  4. Выполнение транзакций
  5. Репликация и разделение чтений/записей
  6. Работа со схемой базы данных

Основанный на PDO, Yii DAO (объекты доступа к базам данных) предоставляет объектно-ориентированный API для доступа к реляционным базам данных. Он является основой для других более продвинутых методов доступа к базам данных, включая строитель запросов и активные записи.

При использовании Yii DAO, вам в основном нужно иметь дело с обычными SQL-запросами и PHP-массивами. В результате это наиболее эффективный способ доступа к базам данных. Однако, поскольку синтаксис SQL может различаться для различных баз данных, использование Yii DAO также означает, что вам нужно приложить дополнительные усилия для создания баз данных независимого приложения.

Yii DAO поддерживает следующие базы данных из коробки:

  • MySQL
  • MariaDB
  • SQLite
  • PostgreSQL: версия 8.4 или выше
  • CUBRID: версия 9.3 или выше.
  • Oracle
  • MSSQL: версия 2008 или выше.

Примечание: Новая версия pdo_oci для PHP 7 в настоящее время существует только в виде исходного кода. Следуйте инструкциям сообщества, чтобы скомпилировать её или используйте эмуляцию PDO.

Создание подключений к БД

Для доступа к базе данных, вам сначала необходимо подключиться к ней, создав экземпляр yii\db\Connection:

$db = new yii\db\Connection([
    'dsn' => 'mysql:host=localhost;dbname=example',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8',
]);

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

return [
    // ...
    'components' => [
        // ...
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=example',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8',
        ],
    ],
    // ...
];

Затем вы можете получить доступ к подключению к БД с помощью выражения Yii::$app->db.

Подсказка: Вы можете настроить несколько компонентов приложения БД, если ваше приложение нуждается в доступе к нескольким базам данных.

При настройке подключения к БД всегда необходимо указывать его имя источника данных (DSN) через свойство dsn. Формат DSN варьируется в зависимости от базы данных. Для получения дополнительной информации обратитесь к руководству PHP. Ниже приведены некоторые примеры:

  • MySQL, MariaDB: mysql:host=localhost;dbname=mydatabase
  • SQLite: sqlite:/path/to/database/file
  • PostgreSQL: pgsql:host=localhost;port=5432;dbname=mydatabase
  • CUBRID: cubrid:dbname=demodb;host=localhost;port=33000
  • MS SQL Server (через драйвер sqlsrv): sqlsrv:Server=localhost;Database=mydatabase
  • MS SQL Server (через драйвер dblib): dblib:host=localhost;dbname=mydatabase
  • MS SQL Server (через драйвер mssql): mssql:host=localhost;dbname=mydatabase
  • Oracle: oci:dbname=//localhost:1521/mydatabase

Обратите внимание, что если вы подключаетесь к базе данных через ODBC, вы должны настроить свойство yii\db\Connection::$driverName, чтобы Yii мог определить фактический тип базы данных. Например,

'db' => [
    'class' => 'yii\db\Connection',
    'driverName' => 'mysql',
    'dsn' => 'odbc:Driver={MySQL};Server=localhost;Database=test',
    'username' => 'root',
    'password' => '',
],

Помимо свойства dsn, часто необходимо настроить имя пользователя и пароль. Для получения полного списка настраиваемых свойств обратитесь к yii\db\Connection.

Информация: При создании экземпляра подключения к БД фактическое подключение к базе данных не устанавливается до тех пор, пока вы не выполните первый SQL-запрос или явно не вызовете метод open().

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

'db' => [
    // ...
    'on afterOpen' => function($event) {
        // $event->sender refers to the DB connection
        $event->sender->createCommand("SET time_zone = 'UTC'")->execute();
    }
],

Выполнение SQL запросов

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

  1. Создайте yii\db\Command с обычным SQL-запросом;
  2. Свяжите параметры (необязательно);
  3. Вызовите один из методов выполнения SQL-запросов в yii\db\Command.

Следующий пример демонстрирует различные способы извлечения данных из базы данных:

// return a set of rows. each row is an associative array of column names and values.
// an empty array is returned if the query returned no results
$posts = Yii::$app->db->createCommand('SELECT * FROM post')
            ->queryAll();

// return a single row (the first row)
// false is returned if the query has no result
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=1')
           ->queryOne();

// return a single column (the first column)
// an empty array is returned if the query returned no results
$titles = Yii::$app->db->createCommand('SELECT title FROM post')
             ->queryColumn();

// return a scalar value
// false is returned if the query has no result
$count = Yii::$app->db->createCommand('SELECT COUNT(*) FROM post')
             ->queryScalar();

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

Связывание параметров

При создании объекта команды БД из SQL с параметрами, вы почти всегда должны использовать подход связывания параметров, чтобы предотвратить атаки SQL-инъекции. Например,

$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
           ->bindValue(':id', $_GET['id'])
           ->bindValue(':status', 1)
           ->queryOne();

В SQL-запросе можно встроить один или несколько маркеров параметров (например, :id в приведенном выше примере). Маркер параметра должен быть строкой, начинающейся с двоеточия. Затем вы можете вызвать один из следующих методов связывания параметров, чтобы связать значения параметров:

  • bindValue(): привязка одного значения параметра
  • bindValues(): привязка нескольких значений параметров в одном вызове
  • bindParam(): аналогично bindValue(), но также поддерживает привязку ссылок на параметры.

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

$params = [':id' => $_GET['id'], ':status' => 1];

$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status')
           ->bindValues($params)
           ->queryOne();
           
$post = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id AND status=:status', $params)
           ->queryOne();

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

$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id');

$post1 = $command->bindValue(':id', 1)->queryOne();
$post2 = $command->bindValue(':id', 2)->queryOne();
// ...

Поскольку bindParam() поддерживает привязку параметров по ссылкам, вышеуказанный код также может быть записан следующим образом:

$command = Yii::$app->db->createCommand('SELECT * FROM post WHERE id=:id')
              ->bindParam(':id', $id);

$id = 1;
$post1 = $command->queryOne();

$id = 2;
$post2 = $command->queryOne();
// ...

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

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

Выполнение запросов, не являющихся SELECT

Методы queryXyz() , представленные в предыдущих разделах, все относятся к запросам SELECT, которые извлекают данные из баз данных. Для запросов, не возвращающих данные, вы должны вызвать метод yii\db\Command::execute() вместо этого. Например,

Yii::$app->db->createCommand('UPDATE post SET status=1 WHERE id=1')
   ->execute();

Метод yii\db\Command::execute() возвращает количество изменённых строк в результате выполнения SQL.

Для запросов INSERT, UPDATE и DELETE вместо написания обычных SQL-запросов вы можете вызвать insert(), update(), delete() соответственно, чтобы создать соответствующие SQL-запросы. Эти методы должным образом обрамляют имена таблиц и столбцов и связывают значения параметров. Например,

// INSERT (table name, column values)
Yii::$app->db->createCommand()->insert('user', [
    'name' => 'Sam',
    'age' => 30,
])->execute();

// UPDATE (table name, column values, condition)
Yii::$app->db->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();

// DELETE (table name, condition)
Yii::$app->db->createCommand()->delete('user', 'status = 0')->execute();

Вы также можете вызвать batchInsert() для вставки нескольких строк за один раз, что значительно эффективнее, чем вставка одной строки за раз:

// table name, column names, column values
Yii::$app->db->createCommand()->batchInsert('user', ['name', 'age'], [
    ['Tom', 30],
    ['Jane', 20],
    ['Linda', 25],
])->execute();

Обратите внимание, что указанные выше методы только создают запрос, и вам всегда необходимо вызвать execute(), чтобы фактически запустить их.

Обрамление имен таблиц и столбцов

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

  • [[column name]]: заключите имя столбца, подлежащее обрамлению, в двойные квадратные скобки;
  • {{table name}}: заключите имя таблицы, подлежащее обрамлению, в двойные фигурные скобки.

Yii DAO автоматически преобразует такие конструкции в соответствующие обрамлённые имена столбцов или таблиц, используя синтаксис, специфичный для СУБД. Например,

// executes this SQL for MySQL: SELECT COUNT(`id`) FROM `employee`
$count = Yii::$app->db->createCommand("SELECT COUNT([[id]]) FROM {{employee}}")
            ->queryScalar();

Использование префикса таблицы

Если большинство имён таблиц вашей БД используют общий префикс, вы можете использовать функцию префикса таблицы, предоставляемую Yii DAO.

Сначала укажите префикс таблицы через свойство yii\db\Connection::$tablePrefix в конфигурации приложения:

return [
    // ...
    'components' => [
        // ...
        'db' => [
            // ...
            'tablePrefix' => 'tbl_',
        ],
    ],
];

Затем в вашем коде, когда вам нужно сослаться на таблицу, имя которой содержит такой префикс, используйте синтаксис {{%table_name}}. Символ процента будет автоматически заменён префиксом таблицы, который вы указали при настройке подключения к БД. Например,

// executes this SQL for MySQL: SELECT COUNT(`id`) FROM `tbl_employee`
$count = Yii::$app->db->createCommand("SELECT COUNT([[id]]) FROM {{%employee}}")
            ->queryScalar();

Выполнение транзакций

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

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

Yii::$app->db->transaction(function($db) {
    $db->createCommand($sql1)->execute();
    $db->createCommand($sql2)->execute();
    // ... executing other SQL statements ...
});

Вышеприведённый код эквивалентен следующему, который даёт вам больший контроль над кодом обработки ошибок:

$db = Yii::$app->db;
$transaction = $db->beginTransaction();
try {
    $db->createCommand($sql1)->execute();
    $db->createCommand($sql2)->execute();
    // ... executing other SQL statements ...
    
    $transaction->commit();
} catch(\Exception $e) {
    $transaction->rollBack();
    throw $e;
} catch(\Throwable $e) {
    $transaction->rollBack();
    throw $e;
}

Вызывая метод beginTransaction(), запускается новая транзакция. Транзакция представлена объектом yii\db\Transaction, сохранённым в переменной $transaction. Затем запросы, которые выполняются, заключены в блок try...catch.... Если все запросы выполнены успешно, вызывается метод commit() для подтверждения транзакции. В противном случае, если произойдёт и перехвачена исключительная ситуация, вызывается метод rollBack() для отката изменений, внесённых запросами до возникновения ошибки в транзакции. throw $e затем повторно сгенерирует исключение, как если бы оно не было перехвачено, и стандартная обработка ошибок позаботится о нём.

Примечание: в приведенном выше коде есть два блока catch для совместимости с PHP 5.x и PHP 7.x. \Exception реализует интерфейс \Throwable начиная с PHP 7.0, поэтому вы можете пропустить часть с \Exception , если ваше приложение использует только PHP 7.0 и выше.

Указание уровней изоляции

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

$isolationLevel = \yii\db\Transaction::REPEATABLE_READ;

Yii::$app->db->transaction(function ($db) {
    ....
}, $isolationLevel);
 
// or alternatively

$transaction = Yii::$app->db->beginTransaction($isolationLevel);

Yii предоставляет четыре константы для наиболее распространённых уровней изоляции:

  • yii\db\Transaction::READ_UNCOMMITTED - наименее строгий уровень, возможны грязные чтения, невоспроизводимые чтения и дублирования.
  • yii\db\Transaction::READ_COMMITTED - предотвращает грязные чтения.
  • yii\db\Transaction::REPEATABLE_READ - предотвращает грязные чтения и невоспроизводимые чтения.
  • yii\db\Transaction::SERIALIZABLE - самый строгий уровень, предотвращает все перечисленные выше проблемы.

Помимо использования вышеперечисленных констант для указания уровней изоляции, вы также можете использовать строки с допустимым синтаксисом, поддерживаемым используемой СУБД. Например, в PostgreSQL вы можете использовать "SERIALIZABLE READ ONLY DEFERRABLE".

Обратите внимание, что некоторые СУБД позволяют устанавливать уровень изоляции только для всего соединения. Все последующие транзакции будут получать тот же уровень изоляции, даже если вы ничего не указываете. При использовании этой функции вам может потребоваться явно установить уровень изоляции для всех транзакций, чтобы избежать конфликтов настроек. На момент написания этой статьи, только MSSQL и SQLite подвержены этому ограничению.

Примечание: SQLite поддерживает только два уровня изоляции, поэтому вы можете использовать только READ UNCOMMITTED и SERIALIZABLE. Использование других уровней приведёт к сгенерированию исключения.

Примечание: PostgreSQL не позволяет устанавливать уровень изоляции до начала транзакции, поэтому вы не можете указать уровень изоляции непосредственно при запуске транзакции. В этом случае необходимо вызвать yii\db\Transaction::setIsolationLevel() после начала транзакции.

Вложенные транзакции

Если ваша СУБД поддерживает точки сохранения (Savepoint), вы можете вложить несколько транзакций следующим образом:

Yii::$app->db->transaction(function ($db) {
    // outer transaction
    
    $db->transaction(function ($db) {
        // inner transaction
    });
});

Или альтернативно,

$db = Yii::$app->db;
$outerTransaction = $db->beginTransaction();
try {
    $db->createCommand($sql1)->execute();

    $innerTransaction = $db->beginTransaction();
    try {
        $db->createCommand($sql2)->execute();
        $innerTransaction->commit();
    } catch (\Exception $e) {
        $innerTransaction->rollBack();
        throw $e;
    } catch (\Throwable $e) {
        $innerTransaction->rollBack();
        throw $e;
    }

    $outerTransaction->commit();
} catch (\Exception $e) {
    $outerTransaction->rollBack();
    throw $e;
} catch (\Throwable $e) {
    $outerTransaction->rollBack();
    throw $e;
}

Репликация и разделение чтений и записей

Многие СУБД поддерживают репликацию базы данных для повышения доступности базы данных и ускорения времени отклика сервера. При репликации базы данных данные дублируются с так называемых мастер-серверов на ведомые сервера. Все записи и обновления должны выполняться на мастер-серверах, в то время как чтение также может выполняться на ведомых серверах.

Для использования репликации базы данных и достижения разделения чтений и записей вы можете настроить компонент yii\db\Connection следующим образом:

[
    'class' => 'yii\db\Connection',

    // configuration for the master
    'dsn' => 'dsn for master server',
    'username' => 'master',
    'password' => '',

    // common configuration for slaves
    'slaveConfig' => [
        'username' => 'slave',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],

    // list of slave configurations
    'slaves' => [
        ['dsn' => 'dsn for slave server 1'],
        ['dsn' => 'dsn for slave server 2'],
        ['dsn' => 'dsn for slave server 3'],
        ['dsn' => 'dsn for slave server 4'],
    ],
]

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

// create a Connection instance using the above configuration
Yii::$app->db = Yii::createObject($config);

// query against one of the slaves
$rows = Yii::$app->db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();

// query against the master
Yii::$app->db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();

Информация: Запросы, выполненные при вызове yii\db\Command::execute(), рассматриваются как запросы на запись, в то время как все остальные запросы, выполненные через один из методов "запроса" yii\db\Command, являются запросами на чтение. Текущее активное подключение к ведомому серверу можно получить через Yii::$app->db->slave.

Компонент Connection поддерживает балансировку нагрузки и откат при сбое между ведомыми. При первом выполнении запроса на чтение компонент Connection случайным образом выбирает ведомого и пытается подключиться к нему. Если ведомый сервер считается "мёртвым", он попробует другой. Если ни один из ведомых серверов не доступен, он подключится к мастеру. Настроив кэш состояния сервера, можно запомнить "мёртвый" сервер, чтобы его не пытаться использовать в течение определённого промежутка времени.

Информация: В приведенной выше конфигурации указан тайм-аут подключения 10 секунд для каждого ведомого. Это означает, что если к ведомому невозможно подключиться в течение 10 секунд, он считается "мёртвым". Вы можете настроить этот параметр в зависимости от вашей среды.

Вы также можете настроить несколько мастеров с несколькими ведомыми. Например,

[
    'class' => 'yii\db\Connection',

    // common configuration for masters
    'masterConfig' => [
        'username' => 'master',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],

    // list of master configurations
    'masters' => [
        ['dsn' => 'dsn for master server 1'],
        ['dsn' => 'dsn for master server 2'],
    ],

    // common configuration for slaves
    'slaveConfig' => [
        'username' => 'slave',
        'password' => '',
        'attributes' => [
            // use a smaller connection timeout
            PDO::ATTR_TIMEOUT => 10,
        ],
    ],

    // list of slave configurations
    'slaves' => [
        ['dsn' => 'dsn for slave server 1'],
        ['dsn' => 'dsn for slave server 2'],
        ['dsn' => 'dsn for slave server 3'],
        ['dsn' => 'dsn for slave server 4'],
    ],
]

Вышеуказанная конфигурация задаёт два мастера и четыре ведомых. Компонент Connection также поддерживает балансировку нагрузки и откат при сбое между мастерами, как и между ведомыми. Отличие заключается в том, что если ни один из мастеров недоступен, будет сгенерировано исключение.

Примечание: При использовании свойства masters для настройки одного или нескольких мастеров все остальные свойства для указания подключения к базе данных (например, dsn, username, password) объекта Connection будут проигнорированы.

По умолчанию транзакции используют подключение к мастеру. И внутри транзакции все операции с БД будут использовать подключение к мастеру. Например,

$db = Yii::$app->db;
// the transaction is started on the master connection
$transaction = $db->beginTransaction();

try {
    // both queries are performed against the master
    $rows = $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
    $db->createCommand("UPDATE user SET username='demo' WHERE id=1")->execute();

    $transaction->commit();
} catch(\Exception $e) {
    $transaction->rollBack();
    throw $e;
} catch(\Throwable $e) {
    $transaction->rollBack();
    throw $e;
}

Если вы хотите начать транзакцию с ведомым подключением, вы должны сделать это явно, как показано ниже:

$transaction = Yii::$app->db->slave->beginTransaction();

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

$rows = Yii::$app->db->useMaster(function ($db) {
    return $db->createCommand('SELECT * FROM user LIMIT 10')->queryAll();
});

Вы также можете напрямую установить Yii::$app->db->enableSlaves на false, чтобы направить все запросы на мастер-подключение.

Работа со схемой базы данных

Yii DAO предоставляет целый набор методов для работы со схемой базы данных, таких как создание новых таблиц, удаление столбца из таблицы и т. д. Эти методы перечислены ниже:

  • createTable(): создание таблицы
  • renameTable(): переименование таблицы
  • dropTable(): удаление таблицы
  • truncateTable(): удаление всех строк в таблице
  • addColumn(): добавление столбца
  • renameColumn(): переименование столбца
  • dropColumn(): удаление столбца
  • alterColumn(): изменение столбца
  • addPrimaryKey(): добавление первичного ключа
  • dropPrimaryKey(): удаление первичного ключа
  • addForeignKey(): добавление внешнего ключа
  • dropForeignKey(): удаление внешнего ключа
  • createIndex(): создание индекса
  • dropIndex(): удаление индекса

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

// CREATE TABLE
Yii::$app->db->createCommand()->createTable('post', [
    'id' => 'pk',
    'title' => 'string',
    'text' => 'text',
]);

Вышеупомянутый массив описывает имя и типы столбцов, которые нужно создать. Для типов столбцов Yii предоставляет набор абстрактных типов данных, которые позволяют определять схему, не зависящую от базы данных. Эти типы преобразуются в определения типов, специфичные для СУБД, в зависимости от базы данных, в которой создаётся таблица. Дополнительную информацию см. в документации API метода createTable().

Помимо изменения схемы базы данных, вы также можете получить информацию о таблице с помощью метода getTableSchema() подключения к БД. Например,

$table = Yii::$app->db->getTableSchema('post');

Метод возвращает объект yii\db\TableSchema, содержащий информацию о столбцах таблицы, первичных ключах, внешних ключах и т. д. Вся эта информация в основном используется строителем запросов и активными записями для написания кода, не зависящего от базы данных.

© 2008–2017 by Yii Software LLC
Licensed under the three clause BSD license.
http://www.yiiframework.com/doc-2.0/guide-db-dao.html

Spec-Zone.ru

Настройки Оффлайн Что нового Помощь О нас
Spec-Zone .ru
спецификации, руководства, описания, API