Общие сведения База данных

База данных

Для работы с базой данных используется класс Database, реализующий взаимодействие с СУБД через расширение PDO. Настройки доступа к базе данных указываются в системных настройках с префиксом db..

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

$this->db - в конексте модулей, моделей, плагинов и других классов наследующих базовый класс Component (приоритетный)

bff::database() - в глобальном контексте и классах не наследующих Component

Настройки подключения

Параметры подключения указываются в файле системных настроек /config/sys.php в секции "Доступ к базе данных".

'db.type' => 'mysql', # тип базы данных
'db.host' => 'localhost', # локальное подключение (localhost, 127.0.0.1) или внешний IP адрес
'db.port' => '3306', # порт подключения, для mysql порт по умолчанию - 3306
'db.name' => 'name', # название базы данных
'db.user' => 'user', # логин пользователя
'db.pass' => 'pass', # пароль пользователя

Построение SQL запросов

Для выполнения SQL запросов типа SELECT доступны следующие методы класса Database:

select - получение нескольких строк из таблицы

$data = $this->db->select('SELECT id, title FROM ' . TABLE_TEST . ' WHERE id > 10');
/* пример:
array(2) {
    0 => ['id' => 11, 'title' => 'Title A'],
    1 => ['id' => 12, 'title' => 'Title B']
}
*/

select_key - получение нескольких строк из таблицы с группировкой по ключу (столбцу):

$data = $this->db->select_key('SELECT id, title FROM ' . TABLE_TEST . ' WHERE id > 10', 'id');
/* пример группировки данных по столбцу id:
array(2) {
    11 => ['id' => 11, 'title' => 'Title A'],
    12 => ['id' => 12, 'title' => 'Title B']
}
*/

select_one_column - получение нескольких строк с данными одного столбца:

$data = $this->db->select_one_column('SELECT id FROM ' . TABLE_TEST . ' WHERE id > 10', 'id');
/* пример:
array(2) {
    0 => 11,
    1 => 12
}
*/

one_data - получение одного значения:

$title = $this->db->one_data('SELECT title FROM ' . TABLE_TEST . ' WHERE id = :id', [':id'=>11]);
$count = $this->db->one_data('SELECT COUNT(id) FROM ' . TABLE_TEST . ' WHERE id >= :num', [':num'=>11]);

one_array - получение одной строки:

$data = $this->db->one_array('SELECT id, title FROM ' . TABLE_TEST . ' WHERE id = :id', [':id'=>11]);
/* пример:
array(2) {
    id => 11,
    title => Title A
}
*/

Также доступен ряд методов для простых SELECT запросов:

$data = $this->db->select_rows(TABLE_TEST, ['*'], ['id'=>5]);
# итоговый запрос: SELECT * FROM test WHERE id = 5
# используется для получения информации о нескольких записях

$count = $this->db->select_rows_count(TABLE_TEST, ['status'=>['>', 5]]);
# итоговый запрос: SELECT COUNT(*) FROM test WHERE status > 5
# используется для подсчета количества записей

$data = $this->db->select_row(TABLE_TEST, ['*'], ['id'=>5]);
# итоговый запрос: SELECT * FROM test WHERE id = 5
# используется для получения информации об одной записи

$data = $this->db->select_data(TABLE_TEST, 'title', ['id'=>5]);
# итоговый запрос: SELECT title FROM test WHERE id = 5
# используется для получения информации об одном столбце одной записи

И другие: select_rows_key, select_rows_column, ...

Методы используемые для остальных типов запросов:

insert - вставка строки в таблицу, INSERT запросы

$id = $this->db->insert(TABLE_TEST, ['title'=>'Title A', 'status'=>1]);
# итоговый запрос: INSERT INTO test (title, status) VALUES ('Title A', 1)
# результатом будет ID вновь добавленной строки

multiInsert - вставка нескольких строк в таблицу, INSERT запрос множественной вставки

# пример вставки нескольких строк
$this->db->multiInsert(TABLE_TEST, [
    ['title'=>'Title A', 'status'=>1],
    ['title'=>'Title B', 'status'=>2],
]);
# итоговый запрос: INSERT INTO test (title, status) VALUES ('Title A', 1), ('Title B', 2)

update - обновление строки в таблице, UPDATE запросы

$this->db->update(TABLE_TEST, ['title' => 'Title AA'], ['id'=>11]);
# итоговый запрос: UPDATE test SET title = 'Title AA' WHERE id = 11
# в результате данного запроса строка с id=11 обновит значение столбца title на 'Title AA'

$this->db->update(TABLE_TEST, ['status' => 2], ['status' => ['<=',3]]);
# итоговый запрос: UPDATE test SET status = 2 WHERE status <= 3

$this->db->update(TABLE_TEST, ['status' => 2], ['status' => [2,3], 'id' => ['!=', 2]]);
# итоговый запрос: UPDATE test SET status = 2 WHERE status IN(2,3) AND id != 2

$this->db->update(TABLE_TEST, ['status' => 3, 'num = num + 1'], ['title' => ['LIKE', 'example%']]);
# итоговый запрос: UPDATE test SET status = 3 AND num = num + 1 WHERE title LIKE 'example%'

delete - удаление строки из таблицы, DELETE запросы

$this->db->delete(TABLE_TEST, ['id' => 11]);
# итоговый запрос: DELETE FROM test WHERE id = 11
# в результате данного запроса будет удалена строка с id=11

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

В каждом из перечисленных выше методов возможна передача данных подставляемых вместо placeholders в строку запроса.

Дополнительно есть методы сокращающие построение отдельных частей запроса:

prepareIN - построение IN() условий

$this->db->select('SELECT id, title FROM ' . TABLE_TEST . '
           WHERE '.$this->db->prepareIN('id', [11,12]));
# итоговый запрос: SELECT id, title FROM test WHERE id IN (11,12)

prepareLimit - построение LIMIT

$this->db->select('SELECT id, title FROM ' . TABLE_TEST . ' 
           WHERE id > :id '.$this->db->prepareLimit(0, 2), [':id'=>10]);
# итоговый запрос: SELECT id, title FROM test WHERE id > 10 LIMIT 0, 2

Рекомендации при формировании текста SQL запросов

Разберем простой пример SELECT запроса:

'SELECT COUNT(S.id)
 FROM ' . TABLE_SHOPS . ' S
     LEFT JOIN ' . TABLE_USERS . ' U ON S.id = U.shop_id
 WHERE S.id = :id AND S.moderated = 0
 GROUP BY S.id'

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

  • при объявлении строки запроса обязательно использование одинарных кавычек
  • все ключевые слова пишем большими буквами
  • используем переносы строк для удобства чтения запроса
  • соблюдаем ширину запроса, в случае если список получаемых столбцов уходит за область видимости - переносим на следующую строку
  • параметры подставляем через placehoders, в примере :id, кроме тех, которые можно указать явно (в примере moderated = 0)
  • избегаем использование кавычек вида `` , в случае когда столбец пересекается с зарезервированными переименовываем.
  • сокращение для название таблиц также называем большой буквой, если есть пересечение, двумя и более большими буквами. Сокращение строим на основе название сущности хранимой в данной таблице, либо на основе первых букв слов дефайна (кроме слова TABLE).

Модель

Модель реализуется посредством класса Model и является неотъемлемой составляющей модуля приложения, объединяя в себе все запросы к базе данных, доступные через вызовы её методов.

В модуле вызов методов модели выполняется через $this->model-> где доступ к объекту базы данных доступен посредством $this->db->

В классе модели есть удобный метод prepareFilter, задача которого - формирование WHERE с placeholder'ами и подстановка префикса таблицы (сокращенного названия), если необходимо.

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

<?php
$filter = $this->prepareFilter(array(
    'moderated' => 1,
    ':status' => array('(A.status >= :min OR A.status <= :max)', ':min'=>1, ':max'=>3),
    'id' => array(10,20,30),
    'title IS NOT NULL',
), 'A');

// результат:
array(
    'where' => 'WHERE A.moderated = :moderated AND
               (A.status >= :min OR status <= :max) AND
                A.id IN (10,20,30),
                A.title IS NOT NULL',
    'bind' => array(
        ':moderated' => 1,
        ':min' => 1,
        ':max' => 3,
    ),
);

Именование методов модели всегда выполняется по следующей логике:
{название сущности модуля}{название действия}

Примеры:

itemSave - добавление/ редактирование записи

itemData - получение данных записи

itemsList - получение списка записей

itemsSave - сохранение данных нескольких записей

Именование таблиц и структура

Все таблицы объявляются в виде define в формате TABLE_{название модуля}_{название сущности} либо TABLE_{название сущности}.

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

Объявления данных define констант допустимо в файле модели соответствующего модуля приложения, в начале файла.

При объявлении обязательно использовать общий префикс DB_PREFIX. Пример объявления можно увидеть в файле /config/db.tables.php.

Примеры именования таблиц:

TABLE_USERS - таблица пользователей

TABLE_SHOPS - таблица магазинов

TABLE_BLOG_CATEGORIES - таблица категорий блога (модуль blog)

Примеры таблиц, со связью один ко многим:

TABLE_BBS_ITEMS_IMAGES - таблица изображений привязанных к объявлению (модуль bbs)

TABLE_BBS_ITEMS_COMMENTS - таблица комментариев к объявлению (модуль bbs)

TABLE_BLOG_POSTS_TAGS - таблица связи поста с тегами (модуль blog)

TABLE_SHOPS_IN_CATEGORIES - связь магазинов с категориями

Таблицы с локализируемыми данными всегда заканчиваются на _LANG, примеры:

TABLE_PAGES - таблица статических страниц

TABLE_PAGES_LANG - таблица локализируемых данных статических страниц

TABLE_BLOG_CATEGORIES - таблица категорий блога

TABLE_BLOG_CATEGORIES_LANG - таблица локализируемых данных категорий

Структура таблицы

Тип таблиц MySQL всегда InnoDB, кроме случаев когда необходимо использовать FULLTEXT индексы, либо есть другие аргументы в пользу MyISAM.

Для случаев использования внешних ключей в выгрузке необходимо предусмотреть вариант выполнения корректного последовательного удаления/создания таблиц с учетом внешних ключей, к примеру использовать SET FOREIGN_KEY_CHECKS=0; и SET FOREIGN_KEY_CHECKS=1;.

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

Типы СУБД

На текущий момент поддерживается работа только с MySQL (pdo_mysql)

В приложении все SQL запросы, использующие специфичные конструкции конкректной СУБД помечаются комментарием. Например конструкции GROUP_CONCAT, INSERT ... ON DUPLICATE KEY UPDATE специфичны только для MySQL, в таком случае помечаем запрос комментарием # MySQL only.

Данная метка в дальнейшем поможет выполнить возможный переход на другую СУБД.

Миграции

В основе миграций структуры базы данных лежит библиотека Phinx версии 0.8.x, документацию под данному инструменту и написанию миграций можно найти здесь.

Миграции приложения

Создание миграции доступно из интерфейса в админ-панели в разделе "Настройки сайта / Обновления / Миграции". Предварительно необходимо включить debug-режим.

В случае успешного создания будет создан файл миграции в директории /files/migrations/*.php. В нем вы найдете функцию migrate в которой и следует писать код выполняющий модификацию структуры базы данных, функция rollback отвечает за код выполняющий откат структуры БД.

Запуск миграции выполняется в момент установки обновления или же вручную в том же разделе.

Также доступна возможность запуска миграции в консоли, для этого выполните следующую команду:

php bffc migrations/migrate -t 2.4.0

Версию миграции при этом измените на требуемую.

Миграции дополнений

Создание миграции доступно из интерфейса в админ-панели в настройках плагина, во вкладке "Разработчику / Миграции".

В случае успешного создания будет создан файл миграции в директории плагина
/plugins/название плагина/migrations/*.php

Запуск миграции выполняется в момент установки плагина или же вручную в том же разделе. Также доступна возможность запуска миграции в консоли, для этого выполните следующую команду

php bffc migrations/migrate -x plugin/name -t 2.4.0

Где измените name на название директории плагина, а номер версии на требуемую версию миграции. Для темы используется следующая команда:

php bffc migrations/migrate -x theme/name -t 2.4.0

Доступ к объекту дополнения в классе миграции доступен через $this->extension