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

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

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

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

• MySQL
• MariaDB
• SQLite
• PostgreSQL
• CUBRID:
• Oracle
• MSSQL

Создание соединений с БД

Чтобы получить доступ к базе данных, сначала необходимо подключиться к ней, создав экземпляр 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',
        ],
    ],
    // ...
];

Вы можете получить доступ к соединению DB с помощью выражения Yii::$app->db. При настройке соединения с БД вы всегда должны указывать свое имя источника данных (DSN) через свойство dsn. Формат DSN различается для разных баз данных. 

• 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-запросов

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

• Создайте команду yii\db\Command с простым запросом SQL;
• Параметры привязки (необязательно);
• Вызовите один из методов выполнения 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 выполняется, а затем изменяйте значение этой переменной перед каждым последующим выполнением (это часто делается с помощью циклов). Выполнение запросов таким способом может быть значительно более эффективным, чем запуск нового запроса для каждого значения другого параметра.

Выполнение не 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 перебрасывает исключение так, как если бы мы его не поймали, поэтому обычный процесс обработки ошибок позаботится об этом.

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

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 были затронуты этим ограничением.

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

Если ваша СУБД поддерживает 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();

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

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

[
    '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 также поддерживает балансировку нагрузки и переключение между мастерами точно так же, как и между подчиненными. Разница в том, что когда ни один из мастеров не будет доступен, будет выбрано исключение.

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

$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, который содержит информацию о столбцах таблицы, первичных ключах, внешних ключах и т. д. Вся эта информация в основном используется построителем запросов и активной записью, чтобы помочь вам написать код агностика базы данных.