Yii : работа с базами данных

Конфигурация

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

<?php
return array(
 	// ...

 	// application components
 	'components'=>array(

		// Настройки для базы данных
		'db' => array(
			// 127.0.0.1 - это хост СУБД, если она установлена на том же сервере, где и PHP-приложение
			// 5432 - это стандартный порт СУБД
			// postgres - это имя базы данных
			'connectionString' => 'pgsql:host=127.0.0.1;port=5432;dbname=postgres',
			'username' => 'username',
			'password' => 'password', // без пароля у меня подключиться не получилось, даже если в базе данных у этого пользователя username установлен пустой пароль
			'charset' => 'utf8',
		),
	),

	// ...
);

Варианты строки connectionString для других баз данных:

SQLite: sqlite:/path/to/dbfile
MySQL: mysql:host=localhost;dbname=testdb

По-умолчанию, соединение с базой данных выполняется автоматически при создании приложения. Чтобы этого не происходило, в настройках нужно установить опцию ‘autoConnect’ => false. Такое поведение пригодится для уменьшения нагрузки на базу данных, когда для обработки отдельных запросов не требуется читать или писать в базу данных. Например, при отображении статических страниц или если вся страницы целиком была сохранена в кэш на стороне сервера.

<?php
return array(
 	// ...
 	'components'=>array(
		// ...

		// Настройки для базы данных
		'db' => array(
			'connectionString' => 'pgsql:host=127.0.0.1;port=5432;dbname=postgres',
			'username' => 'username',
			'password' => 'password',
			'charset' => 'utf8',
			'autoConnect' => false, // не устанавливать соединение при старте приложения
		),

		// ...
	),
	// ...
);

Обращение к базе данных

Обращение к базе данных производится таким образом:

$connection = Yii::app()->db;

Пример запроса:

$connection = Yii::app()->db;
$sqlCommand = $connection->CreateCommand("SELECT * FROM tbl_user");

$rowCount = $sqlCommand->execute(); // используется для команд CREATE/DROP/INSERT/UPDATE/DELETE . Для команд UPDATE/INSERT/DELETE возвращает, соответственно, количество изменённых, вставленных или удалённых строк

$rows = $command->queryAll(); // запрос SELECT и возврат всех строк результата
$row = $command->queryRow(); // запрос SELECT и возврат первой строки результата
$column = $command->queryColumn(); // запрос SELECT и возврат первого столбца результата
$value = $command->queryScalar(); // запрос SELECT и возврат первого поля в первой строке

Метод $sqlCommand->queryAll() возвращает все строки результата в виде массива, а
метод $sqlCommand->query() возвращает объект типа CDbDataReader для построчного чтения результатов.

Результат можно использовать так:

$connection = Yii::app()->db;
$sqlCommand = $connection->CreateCommand("SELECT * FROM tbl_user");
$dataReader = $sqlCommand->query();
while(($row=$dataReader->read())!==false) {
	// ...
}

или так:

$connection = Yii::app()->db;
$sqlCommand = $connection->CreateCommand("SELECT * FROM tbl_user");
$dataReader = $sqlCommand->query();
foreach($dataReader as $row) {
	// ...
}

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

В Yii реализована поддержка транзакций. Шаблонная схема транзакции выглядит так:

$connection = Yii::app()->db;
$transaction = $connection->beginTransaction(); // открыть (начать) транзакцию
try {
	$connection->createCommand($sql1)->execute();
	$connection->createCommand($sql2)->execute();
	//… прочие SQL запросы
	$transaction->commit(); // закрыть (отменить) транзакцию
} catch(CDbException$e) { // в случае ошибки при выполнении запроса выбрасывается исключение CDbException
	$transaction->rollBack(); // откатить (отменить) транзакцию
}

Транзакции в базах данных используются для того, чтобы несколько операций выполнились одновременно. Не все базы данных поддерживают транзакции (например, MySQL с транзакциями в базах на движке InnoDB работает, а на движке MyISAM — нет). И механизм реализации транзакций в разных СУБД реализован по разному:

• где-то транзакции изолированы, а где-то нет (в не изолированных транзакциях незавершённые изменения можно увидеть через другое соединение с базой данных, например, из другой копии приложения на PHP);
• где-то разрешаются транзакции в транзакциях (многоуровневые транзакции), а где-то только один уровень;
• где-то при rollback производится откат на один уровень многоуровневой транзакции, а где-то откатывается вообще вся транзакция;
• в большинстве случаев изменение модели данных (команды CREATE/DROP) автоматически комитят изменения и последующие команды SELECT/INSERT/UPDATE/DELETE выполняются уже вне транзакции
• некоторые драйверы баз данных умеют автоматически комитить изменения, если была открыта транзакция и не было rollback.

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

Любой запрос SQL можно написать в параметризованном виде, когда некоторая переменная часть запроса заменяется шаблонами подстановки. Замена шаблонов подстановки реальными значениями называется «связывание параметров». Вот несколько причин использования параметризованных запросов:

• в ядре базы данных все запросы с уникальным текстом анализируются (по ним составляется так называемый «план запроса»), компилируются и складываются в кэш запросов, а параметризованные запросы сводятся одинаковые запросы к одному («SELECT * FROM tbl_user WHERE ID=1», «SELECT * FROM tbl_user WHERE ID=2» можно свести к «SELECT * FROM tbl_user WHERE ID=?», где ? — это параметр). При повторном выполнении параметризованного запроса с другим значением параметра анализ и компиляция не производятся, если, конечно, этот запрос ещё в кэше
• ядро СУБД может безопасно обработать связываемые данные, что позволит избежать ошибки, называемой SQL Injection

// выражение SQL с двумя маркерами «:username» и «:email»
$sql = "INSERT INTO tbl_user(username, login) VALUES (:username, :login)";

$command=$connection->createCommand($sql);
$command->bindParam(":username", 'Peter Ivanov', PDO::PARAM_STR); // определить значение параметра «:username»
$command->bindParam(":login", 'ivanov', PDO::PARAM_STR); // определить значение параметра «:login»
$command->execute();

// вставляем следующую строку с новыми параметрами
$command->bindParam(":username", 'Stepan Sidorov', PDO::PARAM_STR);
$command->bindParam(":login", 'sidorov', PDO::PARAM_STR);
$command->execute();