Работа с базами данных - energy-coresky/air GitHub Wiki

Определение соединений с базами данных

В SKY-приложениях, конфигурация соединений с реляционными базами данных указывается в файле main/config.yaml. Всегда должно быть определено главное соединение, а так-же можно указать множество второстепенных соединений. Стандартные таблицы memory, visitors, users должны быть определены в БД главного соединения. Соединения определяются во вложенном массиве, где ключом является имя соединения. Ключ главного соединения фиксирован – core или же, для него можно не использовать вложенный массив как в примере ниже:

# нет вложенного массива для соединения core
core: # все данные этого раздела кешируются в sky_plan.php
  databases:
    driver: mysqli
    pref: as_
    dsn: 'ab_sky localhost root ' # db_name db_host db_user db_password
    lite: {driver:sqlite3, dsn:ab.base}
---
# или с вложенным массивом:
core:
  databases:
    core:
      driver: mysqli
      pref: as_
      dsn: 'ab_sky localhost root '
    lite: {driver:sqlite3, dsn:ab.base}

Здесь, определено два соединения: главное и второстепенное с именем lite. Ключ верхнего уровня core, это раздел в конфигурационном файле и не есть имя соединения к БД. Кэш файл sky_plan.php загружается всегда, для всех консольных и web запусков, поэтому в этот раздел следует помещать только информацию, которая действительно используется часто. В инструментах разработчика, главное соединение приложений, всегда представляется как main::core, а например, главное соединение продукта Earth - earth::core. В продуктах, соединения определяются аналогично. Многие веб-приложения имеют только лишь одно главное соединение и возможность не использовать вложенный массив, упрощает конфигурацию. Есть ограничение: именами второстепенных соединений не могут быть идентификаторы driver, pref, dsn, так как эти же идентификаторы имеют специальное значение.

driver - драйвер соединения. Можно использовать: mysqli, sqlite3, pg(Postgres), oci(Oracle), ibase(FireBird)

pref - префикс имен таблиц. Будет добавлен ко всем запросам, которые используют шаблоны для имен таблиц $_, $_tablename, $_` или другие стандартные способы указания имен таблиц. pref может отсутствовать, что равносильно определению пустой строки для него.

dsn - DSN соединения

Главное соединение приложений, выполняется в коде с помощью вызова $sky->open(). Для других соединений, используйте: SQL::open('lite'). Реальное соединение с БД происходит только при первом вызове. Дальнейшее использование этого метода, возвращает объект соединения с БД (прототип интерфейса Database_driver). Дополнительно можно указать продукт (ware) и установить новое соединение по умолчанию указав третьим параметром true: SQL::open('lite', 'main', true). Если ware не указано, используется текущее значение из Plan::$ware, а если не указано имя, используется имя по умолчанию - core.

Класс SQL (см. файл main/w2/sql.php) имеет статическую переменную SQL::$dd и свойство объектов public $_dd;. Первая содержит соединение по умолчанию, которое будет использовано для всех SQL запросов без указания конкретного соединения. Каждый SQL запрос генерирует объект класса SQL, и в $sql->_dd содержится соединение с конкретной БД. Использование соединения по умолчанию: sql('@show tables');. Явное указание соединения: SQL::open('lite')->sql('@pragma pragma_list');

В методах SKY::open(), SKY::shutdown(), главное соединение устанавливается соединением по умолчанию автоматически и содержится также в SKY::$dd.

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

Парсеры SQL запросов

Для обеспечения защиты от SQL инъекций, а так-же решения других востребованных задач, используются парсеры SQL. В коде coresky, для составления SQL запросов, это решение считается более эффективным чем ORM решения или Query Builder. Решения уровня PHP, например PDO слабо функциональны в аспекте возможных шаблонов (placeholders).

$array = sqlf('@select id, title from $_ where id>%d and title>%s', 500, 'sky'); # @ - return result as array
$array = sql('@select id, title from $_ where id>$. and title>$+', 500, 'sky'); # $_ is onduty table name
# both queries will compiled as (table quoted for mysql):
# select id, title from `sometable` where id>500 and title>'sky'

Оба парсера работают в два этапа из которых один этап общий - преобразования без параметров, смотрите SQL::replace_nop(..). На этом этапе производятся замены, для которых не требуются параметры для подстановок. Подставляется дежурная таблица вместо $_, для других определений таблиц подставляется префикс, например $_tablename. Унифицированный синтаксис функций, принимает различную форму, определенную в каждом конкретном драйвере БД, например $cc(x1, x2), преобразуется в concat(x1, x2) для MySQL и в (x1 || x2) для SQLite3.

Второй этап первого парсера основан на использовании функции vsprintf(..) и поддерживает все шаблонные подстановки, присущие семейству функций printf(). Этот парсер предпочтительно использовать в коде приложений из-за высокой скорости выполнения. Но этот парсер не имеет всех необходимых шаблонов (функционально ограничен), поэтому в coresky коде имеется второй парсер, смотрите SQL::parseT().

Функции для работы с SQL

sqlf(..) - Выполняет парсинг SQL шаблона и запрос к БД. Используется парсер SQL::parseF().

qp(..) - (query part, query parse). Выполняет парсинг, но не выполняет запрос. Используется для получения пропарсеных SQL или получения частей запроса для алгоритмического составления полного запроса, путем соединения частей. В том числе с помощью методов SQL::append(..) и SQL::prepend(..). При отсутствии ошибок, всегда возвращает объект типа SQL. Используется парсер SQL::parseT().

SQL qp (string $sql_template [, mixed $arg [, mixed $...  ]] );

$sql->append(..) и $sql->prepend(..) - дополняет (в конец или начало) частично собранный SQL запрос новой частью запроса с возможностью парсинга SQL::parseT().

$sql = qp('select * from $_ where rubr=1');
if ($txt = $_POST['filter_text'])
    $sql->append(' and txt like $+', "%$txt%");
$query = sql('$$ limit 200', $sql); # exec runs here

Пример выше, можно написать и следующим образом:

$where = ['rubr=' => 1];
if ($txt = $_POST['filter_text'])
    $where += ['txt like' => "%$txt%"];
$query = sql('select * from $_ where @@ limit 200', $where);

sql(..) - создает запрос и возможно возвращает ответ из БД. Используется парсер SQL::parseT().

mixed sql ((string|SQL) $sql_template [, mixed $arg [, mixed $...  ]] );
или
mixed sql (int $flags, (string|SQL)  $sql_template [, mixed $arg [, mixed $... ]] );

Если первый параметр функции int - используется вторая форма, иначе первая. Флаг может содержать коррекцию глубины в стеке вызовов для показа места вызова функции в трассировке, может принимать значения 1,2 .. 7, а также флаги (можно соединять посредством | или +):

SQL::_NC - не использовать запятую при соединении элементов массива
SQL::_OR - соединять элементы массива через OR вместо AND (по умолчанию) при составлении условий WHERE запросов (и др.)
SQL::_UPD - генерировать запрос insert в стиле update
SQL::_EQ - зарезервировано
SQL::NO_TRACE - отключить трассировку в режиме DEBUG
SQL::NO_PARSE - отключить парсинг
SQL::NO_EXEC - не выполнять запрос
SQL::NO_FUNC - не производить парсинг функций

Замечание: флаги довольно редко используются в коде приложений.

Шаблоны для SQL::parseT()

Шаблоны внутри запросов, для которых подставляется параметр:

$. - для подстановки чисел

$+ - для подстановки строк. Данные ескейпятся и заключаются в кавычки

$` - для подстановки строк - имен колонок таблиц

$_` - шаблон для подстановки имен таблиц

$$ - шаблон для подстановки пропарсеных SQL (объектов типа SQL), не может создать SQL инъекцию, но может ее пролонгировать

$@ или @@ или !! - шаблоны для подстановки массивов. $@ - все данные ескейпятся, ключи массива считаются целочисленными, можно использовать для непроверенных внешних данных. @@ - для массивов, со строковыми ключами, например для составления запроса INSERT. Данные ескейпятся по умолчанию. !! - данные не эскейпятся по умолчанию. В начале строковых ключей массивов, могут присутствовать специальные символы, изменяющие порядок обработки:

. - число, подстановка безопасна

+ - строка (эскейпится и заключается в кавычки, подстановка безопасна). Для шаблона @@ бессмысленно, так как по умолчанию эскейпинг работает и без +

$ - пропарсеный SQL. (объект SQL. Строка подставляется как есть, но проверяется соответствие типу SQL. И если полагать что ранее не было просчета, то подстановка безопасна)

! - вставить сырую строку (опасно для непроверенных внешних данных)

` (косая кавычка) - заключить имя колонки таблицы в кавычки

Примеры:

sql('insert into $_ @@', [
    '.id' => $_GET['id'], # . is number
    '!time' => 'now()',   # ! raw string (dangerous for unchecked external data!)
    '`concat' => 1,       # column will quoted as "concat" for SQLite
]);

sql('delete from $_ where id in ($@)', $_POST['id']); # array will joined via ","

Шаблоны внутри запросов, для которых не нужен параметр:

\1..\9 - обратная ссылка (backlink) на ранее указанный параметр. Вместо этого указателя, будет продублировано значение, ранее вычисленное для параметра номер которого от 1 до 9.

$_ - подставить дежурную таблицу

$_tblname - к имени таблицы, автоматически будет подставлен префикс, который указан в значении pref соединения с БД

$func или $func(..), где func соответствует регулярному выражению [a-z]+ - унифицированный синтаксис функций SQL.

Префиксы для возвращения результата запроса

Эти префиксы работают в обоих парсерах. Если такой префикс отсутствует в запросе SELECT (или, например SHOW), чтение рядов необходимо выполнить вручную с помощью ->one(..) или ->all(..), а возвращенным из функций sql(..), sqlf(..) значением будет объект класса SQL:

$sql = sql('select id, name from $_tbl');
while ($row = $sql->one()) echo "row: $row[id], $row[name]\n";

+ - возвращает единственное значение

echo sqlf('+select 1+1'); # 2

- - возвращает один ряд результата запроса в виде числового массива

[$id, $name] = sql('-select id, name from ...');

~ - возвращает один ряд результата запроса в виде ассоциативного массива

> - возвращает один ряд результата запроса в виде объекта stdClass

@ - возвращает все ряды результата запроса в виде числового массива

$ary = sql('@select id, name, three from $_tbl');
foreach ($ary as $id => $r) echo "row: $id, $r[0], three=$r[1]\n";

% - возвращает все ряды результата запроса в виде ассоциативного массива

# - возвращает все ряды результата запроса в виде массива объектов

& - возвращает итератор eVar для чтения рядов результата запроса

$e = sql('&select id, name from $_tbl');
foreach ($e as $row) echo "row: $row->id, $row->name\n";

^ - возвращает строку - исполняемый код, который можно использовать по схеме:

$code = sql('^select id, name from $_tbl');
while (eval($code)) echo "row: $r_id, $r_name\n";
# осторожно: в текущей области видимости перезапишутся переменные $q, $r и некоторые с префиксом $r_
# в будущем: можно написать проверку (lint) для автоматического поиска возможных вышеуказанных ошибок

Функционал префиксов, возвращающий все ряды результата запроса, производит smart-заполнение массивов. Если в запросе SELECT одна колонка - ключами массива будут числа (0,1 .. N), а значениями массива - значения колонки каждого ряда запроса. Если в запросе SELECT две колонки, ключами массива будут значения первой колонки (должны быть уникальны), а значениями массива - значения второй колонки каждого ряда запроса. Это отличается от стандартной обработки (например в PDO ->fetchAll(..)), но практически более востребовано. Если вам не привычно такое smart-заполнение, используйте &select. При переборе значений итератора, ключами будут 0,1,2.. и т.д. Для уточнения, смотрите SQL::all(..).

Если таблица в БД имеет авто-инкрементную колонку, в SKY-приложениях настоятельно рекомендуется, делать её первой и называть id. Тогда подобные запросы, в ключах массива, будут содержать уникальные значения: $ary = sqlf('@select * from $_tbl');

⚠️ **GitHub.com Fallback** ⚠️