[Из песочницы] Jii: Полноценный Query Builder для Node.js с API от Yii 2
Привет всем хабровчанам, любителям Yii и Node.js. Почему объединены любители PHP-фреймворка и серверного JavaScript? Потому что Yii теперь доступен и на JavaScript (как для Node.js, так и для браузера)! В этой статье мы рассмотрим Query Builder, полностью сохранивший API от Yii2 и работающий на Node.js.Конструктор запросов — это лишь одна из реализованных частей Jii (не путать с Yii), в данной статье я специально не буду рассматривать фреймворк в целом, потому что его вполне можно использовать и частями.
Что такое Jii? Jii — это компонентный JavaScript MVC фреймворк, который повторяет архитектурные решения легендарного PHP фреймворка Yii 2, в большинстве случаев сохраняя его API. Отсюда происхождение названия Jii — JavaScript Yii.Установка Jii и его части распространяются как пакеты менеджера npm. Установить его можно одной командой: npm install jii jii-model jii-ar-sql После установки вам доступен пространство имен и класс Jii, это единственная входная точка доступа ко всем классам Jii.Пакет jii — это базовый пакет jii, который как раз объявляет пространство имен и класс Jii и возвращает его. Все остальные части Jii (в том числе jii-ar-sql) ставятся тоже как пакеты, но они лишь наполняют базовый неймспейс.Спойлер Как вы могли заметить, в названии пакета присутствую буквы ar. Да, это Active Record с API от Yii2, он уже написан и покрыт кучей тестов. Его я буду описывать в следующих статьях. А пока мы рассмотрим только его Query Builder.
Все примеры, которые будут описаны ниже предполагают наличие установленного MySQL, Node.js и примерно такого кода: var Jii = require ('jii-ar-sql');
var db = new Jii.sql.mysql.Connection ({ host: '127.0.0.1', database: 'example', username: 'root', password: '', charset: 'utf8' });
db.open ().then (function () {
(new Jii.sql.Query ()) .from ('user') .where ({last_name: 'Smith'}) .count ('*', db) .then (function (count) {
console.log ('Records count:', count); db.close ();
});
}); Эти объекты реализуют интерфейс, через который можно отправлять запросы к базе данных и получать ответы в определенном формате. Они используются конструкторами запросов и Active Record.Каждый из объектов доступа к данным обращается к СУБД через драйвера, которые для каждой базы свои. Все они реализуют единый API, позволяющий сменить СУБД.
На данный момент реализован объект доступа к MySQL, который использует пакет-драйвер изnpm mysql. Поддержка других СУБД предусмотрена и планируется в будущем.
Создание соединения с БД
Для доступа к базе данных необходимо создать экземпляр соединения Jii.sql.Connection. Затем необходимо открыть соединение для загрузки схемы БД и установки постоянного соединения.
var db = new Jii.sql.mysql.Connection ({
host: '127.0.0.1',
database: 'example',
username: 'root',
password: '',
charset: 'utf8'
});
db.open ().then (function () {
// …
});
Если вы создаете приложение Jii, то удобнее это соединение прописать в конфигурации приложения как компонент приложения доступен через `Jii.app.db`.
module.exports = {
// …
components: {
// …
db: {
className: 'Jii.sql.mysql.Connection',
host: '127.0.0.1',
database: 'example',
username: 'root',
password: '',
charset: 'utf8',
}
},
// …
};
Выполнение SQL запросов
Когда у вас есть экземпляр подключения к базе данных, вы можете выполнять SQL запрос, выполнив следующие действия: Создать экземпляр Jii.sql.Command с обычным SQL;
Добавить параметры в запрос, если необходимо;
Вызвать один из методов Jii.sql.Command.
Рассмотрим несколько примеров выборки данных из БД:
var db = new Jii.sql.mysql.Connection (…);
db.open ().then (function () {
// Возвращает массив объектов, каждый из объектов представляет запись в таблице, // где ключи объекта — это названия столбцов, а зачения — их соответствующие значения в // строке таблицы. При пустом ответе будет возвращен пустой массив. db.createCommand ('SELECT * FROM post') .queryAll () .then (function (posts) { });
// Возвращает объект, соответствующей строке в таблице (первой в результатах) // Вернет `null` при пустом результате db.createCommand ('SELECT * FROM post WHERE id=1') .queryOne () .then (function (post) { });
// Возвращает массив, соответствующей колонке в таблице (первой в результатах) // Вернет пустой массив при пустом результате db.createCommand ('SELECT title FROM post') .queryColumn () .then (function (titles) { });
// Возвращает скаляр. `null` при пустом результатае db.createCommand ('SELECT COUNT (*) FROM post') .queryScalar () .then (function (count) { });
}); Добавление параметров При создании команды с параметрами, вы должны всегда добавлять параметры через вызовы методов `bindValue` или `bindValues` для предотвращения атак SQL-инъекции. Например: db.createCommand ('SELECT * FROM post WHERE id=: id AND status=: status') .bindValue (': id', request.id) .bindValue (': status', 1) .queryOne () .then (function (post) { }); Выполнение запросов не на выборку данных Запросы на изменение данных необходимо выполнять с помощью метода `execute ()`: db.createCommand ('UPDATE post SET status=1 WHERE id=1') .execute (); Метод Jii.sql.Command.execute () возвращает объект, в котором находится информация с результатом запросов. Каждый из объектов доступа может добавлять в него свои специфичные параметры, но минимальный набор параметров в нем таков:`affectedRows` — Количество затронутых (измененных) строк `insertId` — уникальный сгенерированный идентификатор. Возвращается для запросов INSERT при наличии в колонке PK с AUTO_INCREMENT. Для INSERT, UPDATE и DELETE запросов, вместо того, чтобы писать обычные SQL запросы, вы можете вызыватьJii.sql.Command.insert (), Jii.sql.Command.update (), Jii.sql.Command.delete () методы для созданиясоответствующих SQL. Эти методы будут правильно экранировать имена таблиц, столбцов и значения параметров. // INSERT (table name, column values) db.createCommand ().insert ('user', { name: 'Sam', age: 30 }).execute ().then (function (result) { // result.affectedRows // result.insertId });
// UPDATE (table name, column values, condition) db.createCommand ().update ('user', {status: 1}, 'age > 30').execute ();
// DELETE (table name, condition)
db.createCommand ().delete ('user', 'status = 0').execute ();
Вы можете так же вызывать Jii.sql.Command.batchInsert () для вставки нескольких строк в один запрос, это будет болееэффективно с точки зрения производительности:
// table name, column names, column values
db.createCommand ().batchInsert ('user', ['name', 'age'], {
['Tom', 30],
['Jane', 20],
['Linda', 25],
}).execute ();
Изменение схемы базы данных
Jii DAO предоставляет набор методов для изменения схемы базы данных: Jii.sql.Command.createTable (): создание таблицы
Jii.sql.Command.renameTable (): переименование таблицы
Jii.sql.Command.dropTable (): удаление таблицы
Jii.sql.Command.truncateTable (): удаление всех строк из табилцы
Jii.sql.Command.addColumn (): добавление колонки
Jii.sql.Command.renameColumn (): переименование колонки
Jii.sql.Command.dropColumn (): удаление колонки
Jii.sql.Command.alterColumn (): изменение колонки
Jii.sql.Command.addPrimaryKey (): добавление первичного ключа
Jii.sql.Command.dropPrimaryKey (): удаление первичного ключа
Jii.sql.Command.addForeignKey (): добавление внешнего ключе
Jii.sql.Command.dropForeignKey (): удаление внешнего ключа
Jii.sql.Command.createIndex (): создание индекса
Jii.sql.Command.dropIndex (): удаление индекса
Пример использования этих методов:
// CREATE TABLE
db.createCommand ().createTable ('post', {
id: 'pk',
title: 'string',
text: 'text'
});
Вы также можете получить информацию о таблице через метод Jii.sql.Connection.getTableSchema ()
table = db.getTableSchema ('post');
Метод возвращает объект Jii.sql.TableSchema, который содержит информацию о столбцах таблицы, первичные ключи, внешние ключи, и т.д. Все эти данные используются главным образом в конструкторе запросов и Active Record для упрощения работы с БД.
Конструктор запросов использует объекты доступа к базе данных (Database Access Objects), что позволяет строить SQL запросы на языке JavaScript. Конструктор запросов повышает читаемость SQL-кода и позволяет генерировать более безопасные запросы в БД.
Использование конструктора запросов делится на 2 этапа:
Создание экземпляра класса Jii.sql.Query для представления различных частей SQL выражения (например, `SELECT`, `FROM`). Вызов методов (например, `all ()`) у эклемпляра Jii.sql.Query для выполнения запроса к базе данных и асинхронного получения данных. Следующий код показывает простейший способ использования конструктора запросов: (new Jii.sql.Query ()) .select (['id', 'email']) .from ('user') .where ({last_name: 'Smith'}) .limit (10) .all () .then (function (rows) { // … }); Приведенный выше код сгенерирует и выполнит следующий SQL код, в котором параметр `: last_name` связан со значением `'Smith'`. SELECT `id`, `email` FROM `user` WHERE `last_name` = : last_name LIMIT 10 Создание запросов Для построения запроса необходимо вызывать различные методы объекта Jii.sql.Query, наполняя тем самым различные части SQL команды. Имена методов схожи с названиями SQL операторов. Например, чтобы указать `FROM`, необходимо вызвать метод `from ()`. Все методы возвращают сам объект запроса, что позволяет объединять несколько вызовов вместе.Далее мы опишем использование каждого метода конструктора запросов.
Jii.sql.Query.select () Метод Jii.sql.Query.select () метод определяет часть `SELECT` SQL запроса. Вы можете указать столбцы, которыебудут выбраны. query.select (['id', 'email']);
// эквивалентно:
query.select ('id, email'); Имена столбцов могут включать в себя названия таблиц и/или псевдонимы столбцов.Например, query.select (['user.id AS user_id', 'email']);
// эквивалентно:
query.select ('user.id AS user_id, email'); Вы можете передаеть объект, где ключами будут псевдонимы столбцов.Например, приведенный выше код можно переписать следующим образом: query.select ({user_id: 'user.id', email: 'email'}); По-умолчанию (даже если не вызывать метод Jii.sql.Query.select ()), в запросе будет сгенерирована звездочка `*`для выбора всех столбцов.Кроме имен столбцов, вы можете также указывать SQL выражения. Например:
query.select ([«CONCAT (first_name, ' ', last_name) AS full_name», 'email']); Так же поддерживаются под-запросы, для этого необходимо передать объект Jii.sql.Query как один из элементов для выборки. var subQuery = (new Jii.sql.Query ()).select ('COUNT (*)').from ('user');
// SELECT `id`, (SELECT COUNT (*) FROM `user`) AS `count` FROM `post` var query = (new Jii.sql.Query ()).select ({id: 'id', count: subQuery}).from ('post'); Для добавления слова `'DISTINCT'` в SQL запрос, необходимо вызвать метод Jii.sql.Query.distinct (): // SELECT DISTINCT `user_id` … query.select ('user_id').distinct (); Вы так же можете вызывать метод Jii.sql.Query.addSelect () для добавления дополнительных колонок. query.select (['id', 'username']) .addSelect (['email']); Jii.sql.Query.from () Метод Jii.sql.Query.from () наполняет фрагмент `FROM` из SQL запроса. Например: // SELECT * FROM `user` query.from ('user'); Имена таблиц могут содержать префиксы и/или псевдонимы таблиц. Например: query.from (['public.user u', 'public.post p']);
// эквивалентно:
query.from ('public.user u, public.post p'); При передаче объекта, ключи объекта будут являться псевдонимами таблиц. query.from ({u: 'public.user', p: 'public.post'}); Кроме того, имена таблиц могут содержать подзапросы — объекты Jii.sql.Query. var subQuery = (new Jii.sql.Query ()).select ('id').from ('user').where ('status=1');
// SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u query.from ({u: subQuery}); Jii.sql.Query.where () Метод Jii.sql.Query.where () наполняет секцию `WHERE` в SQL выражении. Вы можете использовать несколько форматов указания условий SQL выражения: строка, `'status=1'` объект, `{status: 1, type: 2}` с указанием оператора, `['like', 'name', 'test']` Строковый формат Строковый формат очень хорошо подходит для указания простых условий. Указанная строка напрямую записывается в SQL выражение. query.where ('status=1');
// или с указанием параметров query.where ('status=: status', {': status': status}); Вы можете добавлять параметры к запросу через методы Jii.sql.Query.params () или Jii.sql.Query.addParams (). query.where ('status=: status') .addParams ({': status': status}); Условие как объект (хеш) Объект лучше всего использовать, чтобы указать несколько объединенных (`AND`) подусловий, каждый из которых имеетпростое равенство. Ключами объекта являются столбцы, а значения — соответствующие значения, передаваемые в условие.Например: // …WHERE (`status` = 10) AND (`type` IS NULL) AND (`id` IN (4, 8, 15)) query.where ({ status: 10, type: null, id: [4, 8, 15] }); Конструктор запросов достаточно умен, чтобы правильно обрабатывать в качестве значений `NULL` и массивы.Вы также можете использовать подзапросы с хэш-формате:
var userQuery = (new Jii.sql.Query ()).select ('id').from ('user');
// …WHERE `id` IN (SELECT `id` FROM `user`) query.where ({id: userQuery}); Формат с указанием оператора Данный формат позволяет задавать произвольные условия в программном виде. Общий формат таков: [operator, operand1, operand2, …] где операнды могут быть друг указан в формате строки, объекта или с указанием оператора. Оператор может быть однимиз следующих:`and`: операнды должны быть объединены вместе, используя `AND`. Например, `['and', 'ID = 1', 'ID = 2']` будет генерировать `ID = 1 AND ID = 2`. Если операнд массива, он будет преобразован в строку, используя правила, описанные здесь. Например, `['and', 'type=1', ['or', 'id=1', 'id=2']]` сгенерирует `type=1 AND (id=1 OR id=2)`. Метод не выполняет экранирование. `or`: похож на `AND` оператора, заисключением того, что операнды соединяются при помощи `OR`. `between`: операнд 1 — имя столбца, а операнды 2 и 3 — начальные и конечные значения диапазона, в которых находятся значения колонки. Например, `['between', 'ID', 1, 10]` сгенерирует выражение `id BETWEEN 1 AND 10`. `not between`: подобно `between`, но `BETWEEN` заменяется на `NOT BETWEEN` в сгенерированном выражении. `IN`: операнд 1 должен быть столбцом или SQL выражение (expression). Второй операнд может быть либо массивом или объектом `Jii.sql.Query`. Например, `['in', 'id', [1, 2, 3]]` сгенерирует `id IN (1, 2, 3)`. Метод экранирует имя столбца и обрабатывает значения в диапазоне. Оператор `IN` также поддерживает составные столбцы. В этом случае операнд 1 должен быть массивом столбцов, в то время как операнд 2 должен быть массивом массивов или `Jii.sql.Query` объектом, представляющий диапазон столбцов. `NOT IN`: похож на `IN` оператора, за исключением того, что `IN` заменяется на `NOT IN` в созданном выражении. `Like`: Первый операнд должен быть столбцом или SQL выражением, а операнд 2 — строка или массив, представляющий собой значения для поиска. Например, `['like', 'name', 'tester']` сгенерирует `name LIKE '%tester%'`. В случае задания массива будет сгенерировано несколько `LIKE`, объединенные оператором `AND`. Например, `['like', 'name', ['test', 'sample']]` сгенерирует `name LIKE '%test%' AND name LIKE '%sample%'`. `or like`: похож на `like`, но для объединения используется оператор `OR`, когда вторым операндом передан массив. `not like`: похож на оператор `like`, за исключением того, что `LIKE` заменяется на `NOT LIKE` в сгенерированном выражении. `or not like`: похож на `not like`, но для объединения используется оператор `OR`, когда вторым операндом передан массив. `exists`: требуется один операнд, который должен быть экземпляром Jii.sql.Query. Сгенерирует выражение`EXISTS (sub-query)`. `not exists`: похож на оператор `exists`, генерирует выражение `NOT EXISTS (sub-query)`. `>`, `<=`, или любой другой оператор БД. Первый операнд должен быть именем столбца, а второй — значением. Например, `['>', 'age', 10]` сгенерирует `age>10`. Добавление условий Вы можете использовать методы Jii.sql.Query.andWhere () или Jii.sql.Query.orWhere () для добавления условий всуществующий запрос. Вы можете вызывать эти методы несколько раз, например: var status = 10; var search = 'jii';
query.where ({status: status});
if (search) { query.andWhere (['like', 'title', search]); } Если `search` не пуст, то приведенный выше код сгенерирет следующий SQL запрос: … WHERE (`status` = 10) AND (`title` LIKE '%jii%') Фильтрация условий При строительстве `WHERE` условия, основанного на данных пользователя, как правило, необходимо игнорировать пустыезначения. Например, в поисковой форме, которая позволяет осуществлять поиск по имени и по электронной почте, нужноигнорировать поле, если в него пользователь ничего не ввел. Это можно сделать с помощью методаJii.sql.Query.filterWhere (): // Данные полей username и email берутся из формы query.filterWhere ({ username: username, email: email, }); Различия между Jii.sql.Query.filterWhere () и Jii.sql.Query.where () состоит в том, первый будет игнорироватьпустые значения. Значение считается пустым, если это `null`, `false`, пустой массив, пустая строка или строка, состоящая только из пробелов.
Подобно методам Jii.sql.Query.andWhere () и Jii.sql.Query.orWhere (), вы можете использоватьJii.sql.Query.andFilterWhere () и Jii.sql.Query.orFilterWhere () для добавленя дополнительных условий.Jii.sql.Query.orderBy () Метод Jii.sql.Query.orderBy () добавляет часть `ORDER BY` к SQL запросу. Например: // … ORDER BY `id` ASC, `name` DESC query.orderBy ({ id: 'asc', name: 'desc', }); В приведенном выше коде, ключами объекта являются имена столбцов, а значения — соответствующее направления сортировки.Для добавления условий сортировки используйте метод Jii.sql.Query.addOrderBy ().Например:
query.orderBy ('id ASC') .addOrderBy ('name DESC'); Jii.sql.Query.groupBy () Метод Jii.sql.Query.orderBy () добавляет часть `GROUP BY` к SQL запросу. Например, // … GROUP BY `id`, `status` query.groupBy (['id', 'status']); Если `GROUP BY` включает только простые имена столбцов, вы можете указать его, используя строку, как при написании обычного SQL. Например: query.groupBy ('id, status'); Вы можете использовать метод Jii.sql.Query.addGroupBy () для добавления дополнительных колонок к части `GROUP BY`.Например: query.groupBy (['id', 'status']) .addGroupBy ('age'); Jii.sql.Query.having () Метод Jii.sql.Query.having () определяет часть `HAVING` SQL выражения. Этот метод работает так же, как метод Jii.sql.Query.where (). Например, // … HAVING `status` = 1 query.having ({status: 1}); Добавляйте дополнительные условия с помощью методов Jii.sql.Query.andHaving () или Jii.sql.Query.orHaving ().Например: // … HAVING (`status` = 1) AND (`age` > 30) query.having ({status: 1}) .andHaving (['>', 'age', 30]); Jii.sql.Query.limit () and Jii.sql.Query.offset () Методы Jii.sql.Query.limit () и Jii.sql.Query.offset () наполняют части `LIMIT` и `OFFSET` SQL выражения. Например: // … LIMIT 10 OFFSET 20 query.limit (10).offset (20); Если вы передадите неправильные значения `limit` и `offset`, то они будут проигнорированы.Jii.sql.Query.join () Метод Jii.sql.Query.join () наполняет часть `JOIN` SQL выражения. Например: // … LEFT JOIN `post` ON `post`.`user_id` = `user`.`id` query.join ('LEFT JOIN', 'post', 'post.user_id = user.id'); Метод имеет 4 параметра:`type`: тип, например, `'INNER JOIN'`, `'LEFT JOIN'`. `table`: имя присоединяемой таблицы. `on`: (необязательный) условие, часть `ON` SQL выражения. Синтаксис аналогичен методу Jii.sql.Query.where (). `params`: (необязательный), параметры условия (`ON` части). Вы можете использовать следующие методы, для указания `INNER JOIN`, `LEFT JOIN` и `RIGHT JOIN` соответственно.Jii.sql.Query.innerJoin () Jii.sql.Query.leftJoin () Jii.sql.Query.rightJoin () Например, query.leftJoin ('post', 'post.user_id = user.id'); Чтобы присоединить несколько столбцов, необходимо вызвать `join` методы несколько раз.Кроме того, вы можете присоединять под-запросы. В этом случае вам необходимо передать объект, где ключ будет псевдонимом присоединяемого запроса. Например:
var subQuery = (new Jii.sql.Query ()).from ('post'); query.leftJoin ({u: subQuery}, 'u.id = author_id'); Jii.sql.Query.union () Метод Jii.sql.Query.union () наполняет часть `UNION` SQL запроса. Например, var query1 = (new Jii.sql.Query ()) .select ('id, category_id AS type, name') .from ('post') .limit (10);
var query2 = (new Jii.sql.Query ()) .select ('id, type, name') .from ('user') .limit (10);
query1.union (query2); Вы можете вызывать данный метод несколько раз для добавления нескольких `UNION` фрагментов.Методы запроса Класс Jii.sql.Query предоставляет целый набор методов для различных резельтатов запроса: Jii.sql.Query.all (): возвращает массив объектов, где ключами являются названия столбцов. Jii.sql.Query.one (): возвращает первый результат запроса — объект, соответствующий найденной строке. Jii.sql.Query.column (): возвращает массив, соответствующий значениями первого столбца результата запроса. Jii.sql.Query.scalar (): возвращает скалярное значение, расположенное в первой ячейке результата. Jii.sql.Query.exists (): возвращает булевое значение, указывающее содержит ли запрос какой-либо результат. Jii.sql.Query.count (): возвращает количество найденных строк. Другие методы агрегации запрос, в том числе Jii.sql.Query.sum (q), Jii.sql.Query.average (q), Jii.sql.Query.max (q), Jii.sql.Query.min (q). Параметр `q` является обязательным для этих методов и может быть либо именем столбца, либо SQL выражением. Все эти методы возвращают экземпляр `Promise` для обработки асинхронного ответаНапример:
// SELECT `id`, `email` FROM `user` (new Jii.sql.Query ()) .select (['id', 'email']) .from ('user') .all ().then (function (rows) { // … });
// SELECT * FROM `user` WHERE `username` LIKE `%test%`
(new Jii.sql.Query ())
.from ('user')
.where (['like', 'username', 'test'])
.one ().then (function (row) {
// …
});
Все эти методы принимают необязательный параметр `db`, представляющий Jii.sql.Connection. Если этот параметр незадан, будет использоваться компонент приложения `db` для подключения к БД. Ниже еще один пример использования `count ()` метода:
// executes SQL: SELECT COUNT (*) FROM `user` WHERE `last_name`=: last_name
(new Jii.sql.Query ())
.from ('user')
.where ({last_name: 'Smith'})
.count ()
.then (function (count) {
// …
})
Индексы в результатах запроса
Когда вы вызываете Jii.sql.Query.all (), то он вернет массив строк, которые индексируются последовательными целыми числами. Но вы можете индексировать их по-разному, например, конкретным значениям столбца или выражения с помощью метода Jii.sql.Query.indexBy (), вызванного перед методом Jii.sql.Query.all (). В этом случае будет возвращен объект.Например:
// returns {100: {id: 100, username: '…', …}, 101: {…}, 103: {…}, …}
var query = (new Jii.sql.Query ())
.from ('user')
.limit (10)
.indexBy ('id')
.all ();
Для указания сложных индексов, вы можете передать в метод Jii.sql.Query.indexBy () анонимную функцию:
var query = (new Jii.sql.Query ())
.from ('user')
.indexBy (function (row) {
return row.id + row.username;
}).all ();
Анонимная функция принимает параметр `row` которая содержит данные текущей строки и должна вернуть строку или число, которое будет использоваться в качестве значения индекса (ключа объекта) для текущей строки.
Jii — опенсорсный проект, поэтому я буду очень рад, если кто-то присоединится к разработке Jii. Пишите на affka@affka.ru.В Jii уже много чего реализовано и следующей статьей я планирую описать Active Record.
