Работа с БД в CleverStyle Framework

в 7:35, , рубрики: cleverstyle, cmf, cms, DBAL, framework, mysql, php, postgresql, sql, sqlite, transactions, бд, Разработка веб-сайтов, транзакции

На счёт БД на первый взгляд может показаться, что функциональность из коробки весьма скудная. Отчасти это правда, но компенсируется тем, арсенал очень хорошо продуман, решает поставленные задачи и ориентирован на производительность.
А если вам нужны более функциональные инструменты — их всегда можно до установить по вкусу, это гораздо проще чем выпилить сложного медлительного монстра.

Данная статья покажет основные интерфейсы, а трейты csCRUD и csCRUD_helpers останутся на другой раз.

Без ORM и Query builder-а

ORM не вписывается в идеологию фреймворка (пакет doctrine/orm, к примеру, без каких-либо зависимостей, даже без учета doctrine/* пакетов в полтора раза больше всего фреймворка).

Query builder так же слишком далёк, к примеру, я никогда не пойму зачем люди пишут такое (Laravel 5.2):

DB::table('users')->where('name', 'John')->first()

Вместо такого:

SELECT *
FROM `users`
WHERE `name` = 'John'
LIMIT 1

Либо вот ещё (Yii2):

new yiidbQuery())
    ->select(['id', 'email'])
    ->from('user')
    ->where(['last_name' => 'Smith'])
    ->limit(10)

Опять таки вместо:

SELECT `id`, `email`
FROM `user`
WHERE `last_name` = 'Smith'
LIMIT 10

Читабельность (ИМХО) хуже, нет подсветки синтаксиса, проверки синтаксиса, статического анализа и автодополнения команд и полей (при сконфигурированной IDE), а при усложнении запросов всё равно проще будет написать чистый SQL чем разбираться в тонкостях работы Query builder-а.

Итак, с подходом стало ясно — будем писать SQL.

SQL бывает разный

Фреймворк на момент написания статьи (версия 5.32.x) поддерживает 3 движка баз данных: MySQL, SQLite, PostgreSQL.
Проблема здесь в том, что синтаксис, поддерживаемый этими СУБД не пересекается на 100% даже в некоторых достаточно часто используемых вещах.
Фреймворк здесь помогает тем, что прозрачно конвертирует часть диалекта MySQL таким образом, чтобы он подходил для SQLite и PostgreSQL.

Далее примеры по большей части дублируют документацию.

SQLite

Здесь только одна совсем небольшая несовместимость:

-- до
INSERT IGNORE INTO `table_name`
    (
        `text`
    ) VALUES (
        ?
    )
-- после
INSERT OR IGNORE INTO `table_name`
    (
        `text`
    ) VALUES (
        ?
    )

PostgreSQL

Здесь всё сложнее, но тем не менее всё равно достаточно простые преобразования.

Во-первых это кавычки:

-- до
SELECT `id` FROM `table_name`
-- после
SELECT "id" FROM "table_name"

Дальше опять INSERT IGNORE INTO, для PostgreSQL превращается в INSERT INTO ... ON CONFLICT DO NOTHING (и поэтому фреймворк требует для работы PostgreSQL 9.5+):

-- до
INSERT IGNORE INTO "table_name"
    (
        "text"
    ) VALUES (
        ?
    )
-- после
INSERT INTO "table_name"
    (
        "text"
    ) VALUES (
        ?
    )
ON CONFLICT DO NOTHING

Ещё одна похожая команда REPLACE INTO, она переписывается в существенно более длинную INSERT INTO ... ON CONFLICT ON CONSTRAINT "{table_name}_primary" DO UPDATE SET ...:

-- до
REPLACE INTO "table_name"
    (
        "id",
        "item",
        "value"
    ) VALUES (
        ?,
        ?,
        ?
    )
-- после
INSERT INTO "table_name"
    (
        "id",
        "item",
        "value"
    ) VALUES (
        ?,
        ?,
        ?
    )
ON CONFLICT ON CONSTRAINT "table_name_primary" DO UPDATE SET
    "id"    = EXCLUDED."id",
    "item"  = EXCLUDED."item",
    "value" = EXCLUDED."value"

Важно заметить, что в этом случае фреймворк ожидает что для таблицы есть constraint (не знаю как лучше перевести) с именем таблицы и суффиксом _primary, например, для системной таблицы [prefix]users он выглядит следующим образом:

ALTER TABLE ONLY "[prefix]users" ADD CONSTRAINT "[prefix]users_primary" PRIMARY KEY ("id");

Последний нюанс связан с тем, в каком формате PostgreSQL желает получать серверные подготовленные выражения, так что всегда можно использовать ?:

-- до
SELECT "id" FROM "table_name" WHERE `number` > ? AND `age` < ? LIMIT ?
-- после
SELECT "id" FROM "table_name" WHERE `number` > $1 AND `age` < $2 LIMIT $3

Немного об БД в общем

Фреймворк изначально имеет понятие о том, что БД может быть несколько. Каждая БД может использовать разные движки, или одинаковые движки, но с разными конфигурациями. Так же поддерживаются зеркала БД и простое распределение запросов в конфигурациях master-master и master-slave.

Модули, которые используют БД, указывают в своём meta.json 2 ключа, которые относятся к БД (пример из системного модуля):

{
...
    "db"                  : [
        "keys",
        "texts",
        "users"
    ],
    "db_support"          : [
        "MySQLi",
        "PostgreSQL",
        "SQLite"
    ],
...}

В db_support указывается, с какими движками модуль в принципе может работать, в db указываются названия баз данных, которые будут во время установки ассоциированы с какой-либо из существующих БД.
Разные названия используются для того, чтобы иметь возможность выбрать наиболее оптимальную БД под задачу. Само собой, таблицы должны быть распределены таким образом, чтобы не делать JOIN между разными БД.

Позже, когда нужно получить id базы данных, ассоциированной с названием можно таким образом:

$db_id = csConfig::instance()->module('System')->db('users');

Далее идентификатор используется для получение объекта с подключением к нужной БД:

$write_connection = csDB::instance()->db_prime($db_id);
$read_connection = csDB::instance()->db($db_id);

Уже здесь разработчик явно указывает, будет ли он что-то писать в БД, или нет. От этого зависит выбор зеркала при соответственной конфигурации.

DBAL

Здесь всё просто, как только вы уловите принцип — вы сможете очень продуктивно писать запросы с закрытыми глазами.

Простое выполнения запроса

Простое выполнение запроса:

$result = $read_connection->q('SELECT `id` FROM `table_name`');

q это сокращение от query. У метода есть несколько вариантов синтаксиса:

->q($query_string : string)
->q($query_string : string, ...$parameters : array)
->q($query_string : string, $parameters : array)
->q($query_string : string[])
->q($query_string : string[], ...$parameters : array)
->q($query_string : string[], $parameters : array)

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

$write_connection->q(
    [
        'DELETE FROM `items` WHERE `id` = ?'
        'DELETE FROM `items_tags` WHERE `item` = ?'
    ],
    $item_id
);

Так и клиентское форматирование в виде синтаксиса функции sprintf():

$write_connection->q(
    [
        'DELETE FROM `items` WHERE `id` = %d'
        "DELETE FROM `items_tags` WHERE `item` = '%s'"
    ],
    $item_id
);

В последнем примере перед подстановкой данные будут обработаны соответствующим образом, так что в '%s' SQL-инъекции не будет.

Для серверных подготовленных выражений позволяется использовать не все аргументы (в отличии от прямого использования нативных интерфейсов):

$write_connection->q(
    [
        "DELETE FROM FROM `[prefix]articles` WHERE `id` = ?",
        "DELETE FROM FROM `[prefix]articles_comments` WHERE `article` = ? OR `date` < ?",
        "DELETE FROM FROM `[prefix]articles_tags` WHERE `article` = ?"
    ],
    [
        $article_to_delete,
        time() - 24 * 3600
    ]
);

Выборка данных

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

$read_connection->f($result);

f это сокращение от fetch. У метода так же есть несколько необязательных параметров:

->f($query_result, $single_column = false : bool, $array = false : bool, $indexed = false : bool)

$single_column === true вместо массива с колонками вернет скалярное значение первой колонки:

$read_connection->f(
    $read_conenction->q('SELECT `id` FROM `table_name` WHERE `id` = 1')
); // ['id' => 1]
$read_connection->f(
    $read_conenction->q('SELECT `id` FROM `table_name` WHERE `id` = 1'),
    true
); // 1

$array === true вместо одной строки считает все и вернет результат в виде массива:

$read_connection->f(
    $read_conenction->q('SELECT `id` FROM `table_name` WHERE `id` < 3'),
    false,
    true
); // [['id' => 1], ['id' => 2]]
$read_connection->f(
    $read_conenction->q('SELECT `id` FROM `table_name` WHERE `id` = 1'),
    true,
    true
); // [1, 2]

$indexed === true возвращает индексированный массив вместо ассоциативного:

$read_connection->f(
    $read_conenction->q('SELECT `id` FROM `table_name` WHERE `id` < 3'),
    false,
    false,
    true
); // [1]
$read_connection->f(
    $read_conenction->q('SELECT `id` FROM `table_name` WHERE `id` = 1'),
    false,
    true,
    true
); // [[1], [2]]

А теперь интересные сокращения:

->qf() === ->f(->q(...))
->qfa() === ->f(->q(...), false, true)
->qfs() === ->f(->q(...), true)
->qfas() === ->f(->q(...), true, true)

a от array, а s от single.

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

$read_connection->f(
    $read_connenction->q('SELECT `id` FROM `table_name` WHERE `id` = ?', 1),
    true,
    true
); // [1, 2]
$read_connection->qfas(
    'SELECT `id` FROM `table_name` WHERE `id` = ?',
    1
); // [1, 2]

Вставка данных

Ещё есть один иногда полезный метод для вставки данных:

$write_connection->insert(
    'INSERT INTO `table_name`
        (`id`, `value`)
    VALUES
        (?, ?)',
    [
        [1, 12],
        [2, 13],
        [3, 14]
    ]
);

Синтаксис следующий:

->insert($query : string, $parameters : array|array[], $join = true : bool)

Если $join === true, то пример выше будет перед выполнением переписан как:

$write_connection->q(
    'INSERT INTO `table_name`
        (`id`, `value`)
    VALUES
        (?, ?),
        (?, ?),
        (?, ?)',
    [
        1, 12,
        2, 13,
        3, 14
    ]
);

Иначе строки будут вставляться по одной.

Прочие методы

Есть ещё ряд полезных методов, к примеру, ->id() вернет идентификатор последней вставленной строки, ->transaction() позволяет обернуть выполнение операций в транзакцию:

$write_connection->transaction(function ($c) { // `$c` это то же, что и `$write_connection`
    $c->insert(...);
    // Вложенные транзации на самом деле пустышки, всё выполняется в рамках родительской транзакции
    $c->transaction(function ($c) {
        $c->id();
    });
    // Если бросить исключение или вернуть `false` то будет выполнен откат транзакции, исключение будет проброшено дальше
});

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

На этом введение в базовую работу с БД всё

В многих модулях вместо прямых запросов используются удобные трейты csCRUD и csCRUD_helpers.
Первый кроме непосредственно 4 банальных операций с БД под капотом ещё умеет заниматься многоязычностью, нормализацией и некоторой обработкой данных (например, JSON туда и обратно конвертировать при записи и чтении), обработкой загружаемых файлов, а так же поддерживает связанные таблицы (один к одному, один ко многим), тоже с поддержкой всего упомянутого добра.
Второй же трейт имеет метод для поиска (на самом деле это фильтр) элементов, опять таки учитывая многоязычность некоторых полей/таблиц и так же включает поддержку связанных таблиц.

Если добавить описание обоих трейтов в статью, то она будет слишком большая на один раз, поэтому будет в следующий раз.

Мысли по поводу удобства интерфейсов и примеры более удобных (по вашему мнению) альтернатив приветствуются, буду рад обсудить данные моменты в конструктивном ключе и учесть обратную связь в будущих релизах.

» GitHub репозиторий
» Документация по фреймфорку

Автор: nazarpc

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js