Контент


Объект Database

Работа с базами данных является наверное самой распространенной задачей в web-программировании. Поэтому просто необходимо разобраться с предлагаемым нам набором свойств и методов объекта Database.

Настройка и создание

Прежде всего необходимо сконфигурировать настройки подключения к БД. Это делается в файлах config/database.php (я употребил множественное число, т.к. помимо application в каждом модуле могут быть предложены свои варианты подключения). Обычный пример конфигурации:

$config['default'] = array
(
	'benchmark'     => TRUE,
	'persistent'    => FALSE,
	'connection'    => array
	(
		'type'     => 'mysql',
		'user'     => 'user',
		'pass'     => 'password',
		'host'     => 'localhost',
		'port'     => FALSE,
		'socket'   => FALSE,
		'database' => 'some_db'
	),
	'character_set' => 'utf8',
	'table_prefix'  => '',
	'object'        => TRUE,
	'cache'         => TRUE,
	'escape'        => TRUE
);

Ключ массива означает имя профиля подключения. Это имя может использоваться при создании экземпляра объекта Database, по умолчанию используется default. За что отвечают различные параметры:

  • benchmark — если TRUE, то статистика запросов (сам запрос, время выполнения и количество строк в результате) сохраняется.
  • persistent — использование постоянных соединений.
  • connection — параметры для подключения к БД (хост, логин, пароль и т.д.) либо строка DSN.
  • character_set — используемая кодировка (по сути после подключения выполняется запрос SET NAMES кодировка).
  • table_prefix — префикс, используемый для таблиц. Например, форум phpbb использует такие префиксы.
  • object — если FALSE, то при обработке результата запроса (объект Database Result) будет возвращаться либо массив полей, либо объект (например ORM). По умолчанию TRUE, т.е. объекты.
  • cache — включает/выключает кэширование запросов (только во время выполнения скрипта!). Честно говоря, сомневаюсь, что эта настройка когда-либо пригодится.
  • escape — опция автоматического заключения имен полей в апострофы (по умолчанию TRUE). Имеет смысл выключить, если планируете использовать специфические функции СУБД (типа AVG() или SUM()), так как иначе получите ошибку запроса.

Как видите, параметров довольно много. Главная сложность — создавая дополнительный модуль для уже работающего приложения, нам может потребоваться применить свои отдельные настройки БД. Но ведь мы не можем заранее знать существующие параметры, поэтому я создал свой хэлпер для автоматической подстановки опущенных настроек default‘ного подключения.

Сами настройки доступны через метод config() объекта Kohana, например так можем получить логин, используемый при подключении в профиле default:

Kohana::config('database.default.connection.user')

При создании объекта Database можно передать в качестве параметра имя профиля или массив с настроками, рассмотренный выше. По умолчанию конструктор будет использовать профиль ‘default‘. В моделях ORM (как правило, для работы с БД используются именно они) создано свойство $db, которое предназначено для хранения объекта Database. Изначально оно содержит имя профиля (опять же, по умолчанию ‘default‘), которое при вызове конструктора будет использовано для инициализации объекта Database. Поэтому в конструкторе своей модели мы можем указать другое название профиля до вызова родительского конструктора, либо вообще создать объект Database самостоятельно (тогда в ORM::__constructor() переменная $db не изменится):

class Article_Model extends ORM {
	public function __construct($id = NULL) {
		// 1. Просто меняем имя профиля
		$this->db = 'anotherdb';
		// 2. Сами создаем объект Database
		$this->db = new Database('anotherdb');
		// 3. Запрашиваем объект Database с учетом паттерна Singleton
		$this->db = Database::instance('anotherdb');
		// Вызываем родительский конструктор
		parent::__construct($id);
	}
}

Если вы будете создавать объект вручную, то лучше использовать третий способ, т.к. в этом случае невозможно создать несколько объектов по одному имени профиля (что возможно во втором варианте, если вы будете использовать несколько моделей за раз). Паттерн Singleton как раз и предназначен для создания только одного экземпляра объекта.

Драйверы

В Kohana для максимальной гибкости объект Database реализует только самые общие методы, которые будут одинаковы для различных СУБД. А сами конкретные запросы к выбранной СУБД обрабатывает соответствующий драйвер (они лежат в system/libraries/drivers/Database/). В текущей версии 2.3.1 доступны драйверы для Mysql, Mysqli (расширенная версия библиотеки Mysql), MS SQL Server, SQLite (через PDO) и PostgreSQL. Драйвер подгружается исходя из указанного в конфигурации профиля типа СУБД (параметр $config['имя_профиля']['connection']['type']).

Следует помнить, что в различных СУБД (и даже в разных версиях одной СУБД) некоторые возможности могут отсутствовать, например в MS SQL Server 2000 невозможно указать смещение (offset), часто используемое в paginator‘ах. Поэтому надо четко понимать, какой тип запроса в результате используемых методов получится, и будет ли он выполним на указанной платформе.

Методы объекта Database

Сперва рассмотрим методы, не связанные с формированием и выполнением запросов:

  • instance_name($db) — передаем объект Database, получаем название профиля этого объекта.
  • list_tables() — возвращает массив с именами таблиц в используемой БД.
  • table_exists(string $table_name) — проверка на существование таблицы в БД. Если указанная таблица существует, возвращает TRUE.
  • field_data(([string $table = '']) — получение информации о полях указанной таблицы. В качестве параметров поля возвращает свойства: Field (имя поля), Type (тип данных, например char(50)), Null (поддержка NULL-значения, YES/NO), Key (является ли индексом, например PRI или UNI), Default (значение по умолчанию), Extra (доп. информация, например auto_increment). Все эти свойства вы наверняка видели в программах phpmyadmin или mysql query browser при создании таблиц в редакторе.
  • list_fields([string $table = ''] ) — получение информации о полях указанной таблицы (в терминах php). Для каждого поля указываются характеристики в зависимости от типа (их можно посмотреть в файле system/config/sql_types.php). По сути информация метода field_data() преобразовывается согласно описанным в конфигурации соответствиям.
  • table_prefix() — возвращает префикс для таблиц, используемый данным профилем.
  • clear_cache([string|TRUE $sql = NULL]) — очищает кэш текущего соединения (вспоминаем упомянутый выше параметр $config['default']['cache']). Если передана строка, то очищается кэш для этого запроса, если TRUE — для последнего выполненного запроса, а если параметр не передан (или NULL) — очищается весь кэш.
  • last_query() — возвращает строку с последним использованным запросом.

Далее познакомимся с методами для формирования запросов к БД. В общем виде запрос составляется из вызовов нескольких методов, указывающих различные условия и инструкции, после чего собственно запрос и выполняется. Большинство условий (типа where() или from()) накапливаемые, поэтому можно совершать несколько вызовов одного метода подряд.

  • ([string $sql = '']) — выполнение указанного первым аргументом запроса. При этом указанные ранее условия (from(), where() и т.д.) учитываться не будут. Можно использовать плейсхолдеры (placeholders), т.е. заменять значения полей или условий на знаки вопроса, а сами значения передавать отдельно (пример в документации). При этом переданные значения будут автоматически экранированы.
  • select([string $sql = '*']) — указываем список полей для выборки (массивом или через запятую). Если вызываем без параметров, будут запрошены все поля. Обратите внимание, что данный метод не очищает ранее добавленные в список поля, а также не запрещает другим методам этот список изменить.
  • from(string $sql) — передаем список таблиц, из которых будет производиться выборка. Опять же, можно передать таблицы массивом или через запятую.
  • where(string|array $key, [string $value = NULL], [boolean $quote = TRUE]) — задаем список условий выборки. Условия можно передать массивом (вида поле=>условие, тогда второй параметр NULL) или двумя параметрами — поле и условие соотвественно. Есть еще третий необязательный параметр — включать ли экранирование (может понадобиться, если используете специфические функции СУБД), по умолчанию TRUE. Например:

    $this->db->where(array('id' => 1, 'name' => 'Something'));
    // или
    $this->db->where(array('id>=' => 1, 'count!=' => 10));

    Данный метод соединяет условия оператором AND (т.е. должны выполниться все перечисленные условия).

  • orwhere(string|array $key, [string $value = NULL], [boolean $quote = TRUE]) — данный метод аналогичен предыдущему, но объединение условий происходит с помощью оператора OR (достаточно выполниться одному условию).
  • Учтите, что метод where() добавляет условия к уже существующим с оператором AND, а orwhere() — с OR, поэтому чтобы получить условие A=1 AND B=2 OR C=3 AND D=4, получится такой код:

    $this->db->where(array('A' => 1, 'B' => 2))->orwhere('C', 3)->where('D', 4);

    Читается не очень :(

  • in(string $field, mixed $values, [bool $not = FALSE]) — позволяет указать диапазон значений для поля (IN). Первый параметр — имя поля, второй — диапазон значений (в виде массива или строки с перечислением значений через запятую). Третий параметр позволяет инвертировать условие (т.е. получится NOT IN), по умолчанию FALSE. Также существует метод notin(), имеющий такие же два аргумента на входе.
  • like(string|array $field, [string $match = ''], [boolean $auto = TRUE]) — метод для добавления условия сравнения с шаблоном. Метод похож на where() — первые два параметра имеют одинаковый синтаксис, условия объединяются через AND и добавляются к уже имеющимся аналогично. В роли шаблона выступает текстовая строка, содержащая т.н. wildcards (это символы, заменяющие один или несколько символов в шаблоне). Например, знак процента означает возможное (необязательное) наличие символов до/после строки, а знак подчеркивания — строго один символ. Третий параметр необязателен и определяет, добавлять ли автоматически знак ‘%’ до и после искомой строки.

    Kohana поддерживает только ‘%’, остальные придется указывать вручную. При этом учтите, что если вы забудете передать FALSE в качестве третьего параметра, вокруг шаблона добавятся все те же знаки процента.

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

    // ищем всех пользователей со строкой admin в логине
    $this->db->like(array('username'=>'admin'))->get('users');
    // ищем всем пользователей с логином в виде символ+'dmin' (Admin, Odmin, 6dmin и т.д.)
    $this->db->like(array('username'=>'_dmin'),NULL)->get('users');
    // третий параметр не указан, поэтому под шаблон подойдут даже aadmin и 00dmin
    $this->db->like(array('username'=>'_dmin'),NULL, FALSE)->get('users');
  • Дополнительно к like() имеется целое семейство методов: orlike(), notlike() и ornotlike(). Принципы использования те же самые.
  • regex(string|array $field, [string $match = '']) — поиск с использованием POSIX-выражений. Синтаксис такой же, как у where() (только без третьего параметра). Дополнительно можно использовать методы orregex(), notregex() и ornotregex()
  • groupby(string $by) — группировка выборки (GROUP BY). Можно передавать имя поля или массив полей.
  • having(string|array $key, [string $value = ''], [boolean $quote = TRUE]) — добавление условия HAVING. Синтаксис аналогичен where(), используя третий параметр можно отключить заключение имени поля в кавычки. Существует также orhaving().
  • orderby(string|array $orderby, [string $direction = NULL]) — указание способа сортировки. Первый параметр — имя поля, второй — направление сортировки (‘ASC‘ — по возрастанию, ‘DESC‘ — по убыванию), условия можно объединить в массив. Кроме того, если имя поля NULL, можно использовать типы сортировки ‘RAND()‘ или ‘RANDOM()‘ для случайного порядка записей, или ‘NULL‘ — для упорядочивания по умолчанию (по возрастанию первичного ключа). Если направление сортировки не указано, принимается ‘ASC‘.
  • limit(integer $limit, [integer $offset = NULL]) — определяет границы выборки (LIMIT), т.е. максимальное количество записей и смещение (опционально).
  • offset(integer $value) — устанавливает смещение для запроса. Если вы установите смещение, то используемый в методе limit() второй параметр не будет играть никакой роли.
  • get([string $table = ''], [string $limit = NULL], [string $offset = NULL]) — метод, инициирующий выполнение запроса. Может принять три параметра (все необязательные): имя таблицы (только одно), ограничение на количество записей (LIMIT) и смещение (OFFSET). Т.е. по сути get() может заменить методы from() и limit(). При этом таблицы, указанные ранее методом from(), будут также учитываться, а ограничение записей и смещение будут заменены на новые. Метод возвращает объект Database Result.
  • getwhere([string $table = ''], [array $where = NULL], [string $limit = NULL], [string $offset = NULL]) — метод аналогичен get(), но дополнительно можно указывать условия по синтаксису метода where() (вторым параметром, сразу после имени таблицы). Таким образом, следующие запросы будут эквивалентны:

    	$this->db->from('articles')->where('published', 1)->limit(10, 0)->get();
    	$this->db->getwhere('articles', array('published' => 1), 10, 0);

  • join(string $table, string|array $key, [string $value = NULL], [string $type = '']) — объединение таблиц по указанным условиям. Первый параметр — имя таблицы, которую надо объединить с указанной в from(), далее условия объединения (обычно по равенству внешнего ключа одной таблицы и первичного ключа другой) в виде строк (второй параметр — левая часть условия, третий — правая) либо массива (тогда третий параметр NULL). Четвертый параметр (опциональный) — тип объединения (‘LEFT‘, ‘RIGHT‘, ‘OUTER‘, ‘INNER‘, ‘LEFT OUTER‘, ‘RIGHT OUTER‘), строка не зависит от регистра. Например:

    $this->db->from('users')->join('articles', 'users.id', 'articles.author')->get();
    // можно использовать псевдонимы для таблиц, удобно
    $this->db->from('users as u')->join('articles as a', 'u.id', 'a.author')->get();

  • count_records([string $table = FALSE], [array $where = NULL]) — метод для подсчета количества записей в таблице. Может применяться к подготовленном запросу (вместо get()), или же отдельно — для подсчета общего числа строк в таблице:

    // считаем число опубликованных статей
    $this->db->where(array('published' => 1))->count_records('articles');
    // считаем общее число статей
    $this->db->count_records('articles');

  • set(string|array $key, [string $value = '']) — передаем значения полей для вставки в таблицу. Может быть с одним параметром (массив ‘поле’ => ‘значение’), либо с двумя, если поле только одно (первый аргумент — имя поля, второй — значение). Применяется совместно с методомами insert() и update(). Обратите внимание, что значения полей будут «завернуты» в кавычки, поэтому различные функции и арифметические операции использовать не получится.
  • insert([string $table = ''], [array $set = NULL]) — вставка новой записи в таблицу. Первый параметр — имя таблицы для вставки (если не указали, будет взята первая таблица из from), далее массив записей вида ‘поле’ => ‘значение’. По функциональности позволяет заменить методы from и set. Возвращает объект Database_Result:

    // способ 1
    $res = $this->db->from('table_name')->set(array('field1'=>1, 'field2'=>2))->insert();
    // эквивалентно использованию одного метода:
    $res = $this->db->insert('table_name', array('field1'=>1, 'field2'=>2));
    // смотрим количество добавленных записей
    echo count($res);

  • update([string $table = ''], [array $set = NULL], [array $where = NULL]) — изменение значений полей существующей записи. Первый параметр — имя таблицы, далее массив новых значений (как в методе set()). Третий аргумент (необязательный) — массив условий обновления записей (как в методе where()). Если условия не указаны, обновятся все записи таблицы. Метод эквивалентен последовательному вызову from(), set() и where():

    // способ 1
    $res = $this->db->from('articles')->set(array('published'=>1))->where(array('id<'=>10))->update();
    // эквивалентно использованию одного метода:
    $res = $this->db->update('blog-articles', array('art_published'=>0), array('id<'=>15));
    // смотрим количество обновленных записей
    echo count($res);

  • delete([string $table = ''], [array $where = NULL]) — удаление записей. Первый параметр — имя таблицы, далее условие выбора удаляемых строк (как в методе where()). Если условие не указано, будут удалены все записи. Аналогично insert() и update() эквивалентен методам from() и where().
  • merge([string $table = ''], [array $set = NULL]) — добавление строки с удалением дубликата (REPLACE). Он добавляет строку, удаляя уже существующие, если в них совпадают первичный ключ или значение уникального поля (т.е. как бы комбинация DELETE+INSERT).

    Метод работает только с драйвером Mysql! Для всех остальных драйверов будет сгенерировано исключение database.not_implemented.

Особняком хотелось бы выделить появившиеся в 2.3.1 методы pop() и push(), работающие со свойством $query_history. Они позволяют работать с ним как со стеком запросов, добавляя или извлекая из него запросы. Это сделано для предотвращения возможного взаимного влияния запросов друг на друга.

Также есть методы, которые обычно используются внутри других методов, но тем не менее они доступны (public) и для ручного вызова:

  • connect — вызываем соединение с БД. Если соединение уже было установление, ничего не произойдет. Ничего не возвращает. Метод можно вручную не вызывать, т.к. при любом запросе он будет использован автоматически.
  • Методы escape(), escape_str(), escape_table(), escape_column() предназначены для экранирования различных значений (в имени метода видно, какие значения ожидаются).

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

Заключение

К сожалению, возможности объекта Database ограничены, где-то необходимостью в универсальности (т.е. работоспособности с различными драйверами), где-то избыточностью получаемых запросов (особенно в ORM). Иногда различные комбинации методов Query Builder‘а могут приводить к совершенно непредсказуемым результатам, поэтому мы всегда должны помнить о старом-добром методе query().

Google Bookmarks Digg Reddit del.icio.us Ma.gnolia Technorati Slashdot Yahoo My Web News2.ru БобрДобр.ru RUmarkz Ваау! Memori.ru rucity.com МоёМесто.ru Mister Wong

Опубликовано в cправочник.

Теги: , , .


Комментарии (11)

Будьте в курсе обсуждения, подпишитесь на RSS ленту комментариев к этой записи.

  1. Slaver пишет:

    А я всё-таки прикрутил котеровский DbSimple. Пришлось сделать свой Database-контроллер. Конструкции типа:
    SELECT *
    FROM `posts`
    WHERE 1=1 { AND `category_id` = ? } { AND `author` = ? } { AND DATE_FORMAT(`posts`.`date`, "%Y%m" ) = ? }
    LIMIT ?d, ?d

    меня безумно радуют и помогают максимально абстрагировать создание запросов :)

  2. BIakaVeron пишет:

    А где ж тут, простите, абстрагирование-то? Если Вы используете DATE_FORMAT и LIMIT, то уже привязываетесь к определенному кругу СУБД (подозреваю, что только к MySQL в итоге). А цель Query Builder‘а как раз в работе с СУБД как с неким обобщенным объектом, без специфики.
    А подобные запросы и через метод query() использовать, он плейсхолдеры поддерживает.
    PS. А зачем нужны фигурные скобки?

  3. Slaver пишет:

    Абстракции от БД, действительно, мало. Хотя библиотека и поддерживает MySQL, PostgreSQL и InterBase/FireBird.

    Плейсхолдеры — замечательно, но их недостаточно стало, после того, как я познакомился с фигурными скобками :) Это — макроподстановки в запросах, позволяющие динамически генерировать сложные запросы без использования if-else-конструкций в скрипте. Более понятно будет на примерах.

    Наверное, неправильно это называть абстракцией, это просто возможность писать читабельные запросы. Тем более, имхо :)

  4. BIakaVeron пишет:

    Макроподстановки — штука интересная, но ведь в QB можно сформировать массив вида имя_поля=>условие и его затолкать в метод where() или orwhere(). По сути те же макроподстановки и получатся. Разве что без плейсхолдеров, но в QB они наверное уже не нужны будут.
    Я не критикую DBSimple, слышал о ней множество положительных отзывов, просто интересно, что в ней есть такого хорошего, чтобы отказываться от «родных» механизмов :)

  5. Slaver пишет:

    В имя_поля=>условие нельзя сделать нормального исключения, если не перадан какой-нибудь параметр. В DbSimple часть запроса в скобках не будет выполняться, если параметра нет (DBSIMPLE_SKIP). Как сделать аналог этого в в QB, без проверки в if-else до самого запроса?

    Так что отказываюсь от в QB исключительно из-за нечитабельности запросов :mrgreen:

  6. BIakaVeron пишет:


    $where = array();
    // проверяем переменные, причем можно и в цикл это запихнуть
    isset($_POST['category']) AND $where['category_id'] = intval($_POST['category']);
    $this->db->where($where)->get();

    Возможно, плейсхолдеры тут бы пригодились чтобы избавиться от функций приведения типа, а так вроде очень даже все ничего ;)

  7. Марсель пишет:

    а что лучше использовать в кохана, ORM или Database?

  8. biakaveron пишет:

    Вероятно Вы имеет в виду ORM, Database Query Builder и Database Query? Нет «лучшего» механизма, есть определенные оптимальные условия для использования одного или другого. ORM наиболее тяжелый способ, но зато он автоматизирует почти 100% рутины, позволяя очень быстро создать рабочую версию проекта. Прямые запросы (Database Query) наиболее быстрые (ничего сверху не накручено), но практически все придется делать руками. Query Builder является промежуточным звеном, позволяющим достаточно просто реализовать многие стандартные операции, и при этом модели остаются вполне быстрыми и легкими. Правда, многие бонусы ORMа (например, связи или валидацию) придется писать самостоятельно.

    Обычно проект пишут с ORM, затем определяют узкие места и переделывают с использованием Query Builder’а (или даже прямых запросов).

  9. Олег пишет:

    Здравствуйте!
    У меня ни как не получается сделать случайную выборку одного элемента из БД в Kohana ORM. Вот мой запрос:
    $product = $category->products->where(‘status’, ‘!=’, 0)->order_by(NULL, ‘RAND()’)->find();
    А вот какая выводится ошибка:
    Database_Exception [ 1064 ]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘RAND() LIMIT 1′ at line 1 [ SELECT `me_products`.* FROM `me_products` JOIN `me_products_categories` ON (`me_products_categories`.`product_id` = `me_products`.`id`) WHERE `me_products_categories`.`category_id` = '9' AND `status` != 0 ORDER BY `` RAND() LIMIT 1 ]
    Без order_by(NULL, ‘RAND()’) все работает хорошо, но только выборка не случайная конечно.
    Прошу, кто может — помогите!

  10. biakaveron пишет:

    Видно, что у Вас переданное имя поля (NULL) было заключено в апострофы, поэтому и ошибка. Мне думается, что единственный способ сделать такую выборку — использовать Database_Expression:

    ->order_by(new Database_Expression('RAND()'))

  11. Олег пишет:

    Спасибо, при использовании Database_Expression все получилось.



Можно включить подсветку кода: <code><pre lang="">...</pre></code>
Разрешены некоторые HTML теги

или используйте trackback.