Базы данных

Kohana предоставляет мощное средство взаимодействия с базами данных. В списке функциональных возможностей:

  • Конструктор запросов (Query builder).
  • Поддержка нескольких одновременных сессий с БД.
  • Поддержка MySQL и PDO.
  • Профилирование и кеширование запросов.

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

Пример конфигурации находится в файле modules/database/config/database.php. Скопируйте его в application/config и откройте. Файл представляет собой список групп конфигураций (в примере это default и alternate), каждая из которых содержит настройки подключения для отдельной БД.

MySQL

Пример конфигурации:

'type'       => 'mysql',
'connection' => array(
	'hostname'   => 'localhost',
	'database'   => 'kohana',
	'username'   => 'root',
	'password'   => 'passw0rd',
	'persistent' => FALSE,
),
'table_prefix' => 'ko3_',
'charset'      => 'utf8',
'caching'      => TRUE,
'profiling'    => TRUE,

Установка соединения

Соединение с нужной БД устанавливается автоматически как только вы посылаете запрос:

Database::instance('group')->query(Database::SELECT, 'SELECT * FROM `table`')->execute();

Либо же можно установить соединение принудительно:

Database::instance('group')->connect();

Выполнение обычного запроса к БД

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

  • type — тип запроса, может принимать следующие значения: Database::SELECT, Database::INSERT, Database::UPDATE, Database::DELETE.
  • sql — текст запроса.
Database::instance('alternate')->query(Database::DELETE, 'DELETE FROM `table`')->execute();

Если вы обращаетесь через стандартную (default) группу конфигурации, то можно написать запрос компактнее:

DB::query(Database::UPDATE, 'UPDATE `table` SET `price`=`price`*1.1');

Параметры

Параметры — это значения (либо переменные), привязанные к запросу. Параметры позволяют создавать более читаемый код, а также снимают с вас задачу обработки этих переменных (Kohana сама обрабатывает их, исключая возможность SQL-инъекции). Используется это так:

DB::query(Database::SELECT, 'SELECT `id` FROM `users` WHERE `email`=:email AND `password`=:password')
	->param(':email', $_POST['email'])
	->param(':password', 'p4ssw0rd');

Либо так:

DB::query(Database::SELECT, 'DELETE FROM `users` WHERE `email`=:email AND `password`=:password')->parameters(array(
	':email' => $_POST['email'],
	':password' => 'p4ssw0rd',
));

Также возможно привязать переменные к запросу:

DB::query(Database::SELECT, 'SELECT * FROM `users` WHERE `email`=:email LIMIT 1')->bind(':email', $email);

Это особенно удобно, когда требуется запустить одинаковый запрос много раз с разными параметрами:

$emails = array('test@gmail.com', 'root@delphist.net', 'admin@site.com');
$query = DB::query(Database::DELETE, 'DELETE FROM `users` WHERE `email`=:email')->bind(':email', $email);
foreach($emails as $email)
{
	$query->execute();
}

Составление запросов с помощью Query Builder

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

$query = DB::select('*')->from('product')->where('id', '=', 3);

Сгененерирует запрос

SELECT * FROM `product` WHERE `id`=3

SELECT

SELECT-запрос генерируется методом DB::select. В качестве аргументов метода нужно перечислить искомые колонки. Если аргументы не передавать, то искомая колонка установится в * Например:

DB::select('NOW("")');

Сгенерирует запрос:

SELECT NOW("")

SQL-конструкция AS тоже доступна — для этого нужно вместо имени колонки передать массив array($column_name, $alias), где $column_name — имя колонки, а $alias — псевдоним, например:

DB::select(
	array('NOW("")', 'current_date'),
	array(DB::expr(1), 'number')
);

Сгенерирует запрос:

SELECT NOW("") AS `current_date`, 1 AS `number`

Чтобы добавить или убрать DISTINCT для условия, необходимо вызвать метод distinct($value):

DB::select('firstname', 'lastname')->distinct(TRUE)->from('authors');

Это сгенерирует запрос:

SELECT DISTINCT `firstname`, `lastname` FROM `authors`

FROM

Чтобы указать таблицу, из который будет идти выборка SELECT, используется метод from($table), который принимает одно значение — название таблицы, либо массив из названия таблицы и псевдонима (для конструкции `table` AS `alias`), либо объект, содержащий еще один SELECT (для создания конструкций вида SELECT * FROM (SELECT `id` FROM `posts`)):

DB::select()->from('table');
DB::select()->from(array('table', 't1'));
DB::select()->from(DB::select()->from(array('table2', 't2')));

Это сгенерирует три запроса:

SELECT * FROM `table`;
SELECT * FROM `table` AS `t1`;
SELECT * FROM (SELECT * FROM `table2` AS `t2`);

WHERE

Для добавления условия WHERE существует два метода and_where($column, $op, $value) и or_where($column, $op, $value). При вызове метода, в текущее тело WHERE добавляется условие AND $column $op $value либо OR $column $op $value, где $column — название столбца, $op — функция сравнивания, $value — значение. Например:

$id = array(4, 56, 55);
DB::select()->from('table')->where('category', '=', 1)->or_where('id', 'IN', $id);

Сгенерирует запрос:

SELECT * FROM `table` WHERE `category` = 1 OR `id` IN (4, 56, 55)

Нередко требуются вложенные условия. Они реализуются методами and_where_open(), and_where_close() и or_where_open(), or_where_close(), например:

$id = array(4, 56, 55);
DB::select()->from('table')
	->and_where_open()
		->and_where('category', '=', 1)->or_where('id', 'IN', $id)
	->and_where_close()
	->and_where('disabled', '=', 0);

Сгенерирует запрос:

SELECT * FROM `table` WHERE (`category` = 1 OR `id` IN (4, 56, 55)) AND `disabled` = 0

HAVING

Принцип добавления условий HAVING полностью идентичен принципу добавления WHERE-условий, только методы называются and_having(), or_having(), and_having_open(), and_having_close(), or_having_open() и and_having_close(). Например:

DB::select()->from('table')
	->and_having_open()
		->and_having('category', '=', 1)->or_having('brand', '=', 4)
	->and_having_close()
	->and_having('disabled', '=', 1);

Сгенерирует запрос:

SELECT * FROM `table` HAVING (`category` = 1 OR `brand` = 4) AND `disabled` = 1

ORDER BY

Чтобы отсортировать результаты запроса по нужному вами параметру, следует использовать функцию order_by($column, $direction), где $column — выражение, по которому нужно сортировать результаты, а $direction — направление сортировки (ASC или DESC), например:

DB::select()->from('table')->where('disabled', '=', 0)->order_by('timestamp', 'DESC');

Сгенерирует запрос:

SELECT * FROM `table` WHERE `disabled` = 0 ORDER BY `timestamp` DESC

LIMIT … OFFSET

Чтобы выбрать определенную область записей, нужно использовать методы limit() и offset(). Первый метод ограничивает количество записей, а второй устанавливает смещение. Например:

DB::select()->from('table')->limit(30)->offset(10);

Сгенерирует запрос:

SELECT * FROM `table` LIMIT 30 OFFSET 10

INSERT

Для вставки данных в БД используйте DB::insert($table, $columns), где $table — имя таблицы, а $columns — список колонок (необязательно). Для того, чтобы установить значения, используйте метод values($values), где $values — массив значений для вставки. Например:

DB::insert('users', array('username', 'password'))->values(array('login', 'password'))->values(array('login2', 'password2'));

Сгенерирует запрос:

INSERT INTO `users` (`username`, `password`) VALUES ('login', 'password'), ('login2', 'password2')

Также доступны методы table($table) и columns($columns), первый меняет текущую таблицу, а второй — список колонок. Например, можно использовать так:

$insert = DB::insert('users', array('username', 'password'))->values(array('login', 'password'))->values(array('login2', 'password2'));
$insert->execute();
$insert->table('users2')->columns(array('login', 'pass'))->execute();

В этом случае сгенерируется два запроса:

INSERT INTO `users` (`username`, `password`) VALUES ('login', 'password'), ('login2', 'password2')
INSERT INTO `users2` (`login`, `pass`) VALUES ('login', 'password'), ('login2', 'password2')

INSERT … SELECT

Для составления конструкции вида INSERT … SELECT существует метод select($query), где $query — объект класса Database_Query с запросом SELECT. Используется это так:

$select = DB::select('username', 'password')->from('old_users');
$insert = DB::insert('users', array('username', 'password'))->select($select);

В этом случае, $insert сгенерирует запрос:

INSERT INTO `users` (`username`, `password`) SELECT `username`, `password` FROM `old_users`

UPDATE

Для составления конструкций UPDATE используется метод DB::update($table), где $table — имя обновляемой таблицы. Чтобы установить значения, нужно вызывать метод set($pairs), где $pairs — список записей колонка-значение. Пример использования:

DB::update('product')->set(array('price' => 1000));

Сгенерирует запрос:

UPDATE `product` SET `price` = 1000

Помимо set(), вы можете использовать метод value($column, $value), где $column — имя колонки, а $value — значение. Также доступны следующие методы: order_by(), limit(), and_where(), or_where(), and_where_open(), and_where_close(), or_where_open(), or_where_close(), применение которых описывается в разделе SELECT. Пример:

DB::update('product')->value('price', 1000)->value('category', 5)->where('brand', '=', 5)->limit(10)->order_by('brand', 'ASC');

Сгенерирует запрос:

UPDATE `product` SET `price` = 1000, `category` = 5 WHERE `brand` = 5 ORDER BY `brand` ASC LIMIT 10

ON DUPLICATE KEY

К сожалению, Kohana не предоставляет встроенных методов для создания такой конструкции. Но это можно обойти, объединив два запроса:

// Основной запрос
$insert = DB::insert('vote', array('user', 'product', 'rate'))->values(array(1, 3, 4))->values(array(1, 2, 5));
// Выражение ON DUPLICATE KEY
$on_duplicate = ' ON DUPLICATE KEY UPDATE `rate`=VALUES(`rate`)';
// Объединение строк и запуск запроса
$insert_on_duplicate = DB::query(Database::INSERT, $insert.$on_duplicate);

После этого, $insert_on_duplicate будет содержать следующий sql-код:

INSERT INTO `vote` (`user`, `product`, `rate`) VALUES (1, 3, 4), (1, 2, 5) ON DUPLICATE KEY UPDATE `rate`=VALUES(`rate`)

DELETE

Для удаления записей из БД (конструкция DELETE) используется метод DB::delete($table), где $table — таблица. Остальные методы order_by(), limit(), and_where(), or_where(), and_where_open(), and_where_close(), or_where_open(), or_where_close() подробно описываются в разделе SELECT. Пример создания конструкции DELETE:

DB::delete('user')->where('id', '=', 123)->limit(1);

Сгенерирует запрос:

DELETE FROM `user` WHERE `id` = 123 LIMIT 1

Выражения

Те выражения, которые не могут быть составлены с помощью Query Builder’а, можно вручную составить через метод DB::expr($string, $parameters), где $string — выражение, $parameters (опционально) — параметры.

Выражения, составленные через DB::expr не проверяются на SQL-инъекции, поэтому убедитесь, что делаете проверку всех вводимых данных, либо используйте параметры.

Пример использования:

DB::update('products')->set('price', '=', DB::expr('(`price`*1.1)+100'));

Сгенерирует запрос:

UPDATE `products` SET `price` = (`price`*1.1)+100

Получение результатов запроса

Чтобы выполнить запрос, необходимо вызвать метод execute($group), который опционально принимает один аргумент — название группы конфигурации. Таким образом, можно быстро выполнить один и тот же запрос для разных подключений:

$query = DB::query(NULL, 'TRUNCATE TABLE `logs`');
foreach(array('default', 'backup') as $group)
{
	$query->execute($group);
}

В зависимости от типа запроса, метод execute возвращает следующее:

SELECT

Успешный SELECT-запрос возвращает объект типа Database_MySQL_Result, над которым можно произвести следующие действия:

Получить список всех найденных строк:

$rows = DB::query(Database::SELECT, 'SELECT * FROM `table` WHERE `price` > 1000')->execute()->as_array();

Опционально, метод as_array может принимать один или два параметра — $key и $value. Значение колонки $key будет использоваться как ключ массива, а если указать параметр $value, то элементом массива будет значение колонки $value.

$rows_by_id = DB::query(Database::SELECT, 'SELECT * FROM `table` WHERE `price` > 1000')->execute()->as_array('id');
$id_title = DB::query(Database::SELECT, 'SELECT `id`, `title` FROM `table` WHERE `price` > 1000')->execute()->as_array('id', 'title');

После выполнения этого примера, $rows_by_id будет содержать массив записей, ключами которых будет id записи, а $id_title — массив значений idtitle.

Подсчитать количество найденных строк:

$rows = DB::query(Database::SELECT, 'SELECT * FROM `table` WHERE `price` > 1000')->execute()->count();

Получить отдельную колонку из текущей строки:

$count = DB::query(Database::SELECT, 'SELECT COUNT(*) AS `records_found` FROM `table` WHERE `category`=100')->execute()->get('records_found', 0);

INSERT

Запрос на INSERT возвращает массив из двух элементов: id последней вставленной строки и количество вставленных строк

list($insert_id, $affected_rows) = DB::query(Database::INSERT, 'INSERT INTO `offer` (`price`, `product`) VALUES(1500, 1)')->execute();

UPDATE

Возвращает количество обновленных строк.

$rows = DB::update('offer')->set(array('title' => 'Новый заголовок'))->where('id', '<', 13)->execute();

DELETE

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

$rows = DB::delete('offer')->where('id', '=', 13)->execute();

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *