SimpleQueryBuilder - простой Х++ конструктор читабельных T-SQL запросов

[Ruff]

Предлагаю на суд общественности реализацию наглядного построителя текстовых запросов к внешним БД.

Преамбула

Наверняка многим приходилось сталкиваться с задачей формирования внешних SQL-запросов, и наверняка мало кто назовет такую задачу приятной. Особенно, если запрос длинный, "многоэтажный", а еще хуже - если приходится многократно дорабатывать такой хардкод. Разбираться во всех этих strfmt(...) (иногда вложенных) с десятками параметров - муторно. Того и гляди накосячишь.
Для пущей безопасности этого процесса в Аксапте есть семейство классов SQLBuilder*. Но на мой взгляд, код, написанный с его помощью, тоже получается громоздким.
В итоге появилась идея, набросок которой я тут публикую.

Что умеет SimpleQueryBuilder (SQB)?

1. Формировать строку SQL-запроса путем последовательного вызова методов, соответствующих ключевым словам SQL. Например,
   

   selectStr = ...select(...).from(...).where(...)...toString(); 
   insertStr = ...insert(...).into(...).values(...)...toString();

   При этом никаких соединений с БД он не устанавливает, сформированный запрос не выполняет. Только конкатенация текста.
2. Умеет "подсказывать" синтаксис запроса с помощью IntelliSense (см. скриншот), ограничивая текущий контекст через набор интерфейсов. Например, после метода select() обязательно должен быть вызван from() (без этого просто не получится "достать" финальную строку). Аналогично, where() не получится впихнуть после order_by(), и т.п.
3. В текущей версии в общих чертах реализован только оператор SELECT (но далеко не все его нюансы). INSERT, UPDATE и DELETE - отсутствуют.

Основные плюсы

• Для относительно простых запросов код выглядит кучеряво Для сложных - пока не пробовал.
• Легко начать пользоваться: пишем SimplyQueryBuilder::construct(), ставим точку, и дальше - все интуитивно понятно.
• Неожиданный бонус от редактора Х++ - подсветка "синтаксиса" (опять см. скриншот).
• Снижается риск ошибиться в "скелете" запроса (забыть "кусок", не туда скопипастить), так как он весь выписывается, по сути, в одну строку кода, которая либо скомпилируется, либо нет.
• За счет того, что практически все параметры - строковые, в конечном итоге можно получить любой синтаксис (не предусмотренный в наборе методов).

Минусы

• Строки, переданные в качестве параметров никак не проверяются. То есть нет никаких гарантий валидности итогового запроса.
• Вложенные выражения (условия, подзапросы) не поддаются красивому выстраиванию в цепочку методов, следовательно их придется конструировать отдельными частями.
• Предполагаю, что дорабатывать SQB под свои нужды будет неудобно из-за кучи переплетенных интерфейсов, которые в ряде случаев придется развязывать и связывать по-новому.
• Ваши замечания? (критика приветствуется)

Во вложении - проект с набором классов и интерфейсов (у интерфейсов префикс SQB_*). Примеры использования - в классе SQBTutorial. Делалось и проверялось на AX2009, в 2012 скорее всего тоже заработает.

PS. Еще точнее - изначально делалось даже на Java для других нужд, а потом любопытство заставило попробовать адаптировать под Аксапту)

[Logger]

А вы не пробовали внутри генерить аксаптовский Query ?
Мне кажется было бы удобно.

Запрос писать в виде select но потом на выходе получать Query или QueryRun, который можно передать дальше в другие методы для дальнейшей модификации или на форме использовать с возможностью добавлять фильтры по желанию пользователя. Было бы удобно.
Непонятно почему ядро такой возможности не предоставляет. Все равно внутри под капотом в ядре что select что QueryRun должны превратиться в один объект после обработки.

[Ruff]

Цитата:

Сообщение от Logger

А вы не пробовали внутри генерить аксаптовский Query ?
Мне кажется было бы удобно.

Эта мысль была с самого начала Хотел сделать следующим шагом после текстового, даже название было придумано: NQB_* (от слова Nativ). Но когда прикидывал реализацию, останавливала мысль о том, что DataSource-ы в Query могут быть вложены в произвольном порядке (то есть образуют дерево), а предложенная в SQB цепочка вызовов - она ж плоская, или даже одномерная Наверно, это тоже как-нибудь решаемо, но пока руки не дошли всерьез подумать... Спасибо за проявленный интерес)

[Logger]

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

[mazzy]

Цитата:

Сообщение от Ruff

текстовых запросов

прикольная лекция, красивая постановка задачи.
+ kotlin - 10 минутный интеллектуальный оргазм. c 54 минуты лекции идет генерация HTML в коде
)))))

Цитата:

Сообщение от Ruff

Для относительно простых запросов ... Для сложных - пока не пробовал.

Попробуйте.
Практически все движки сайтов содержат подобные вещи.
в том числе vbulletin, xenforo, ipboard, википедия и прочие CMS.
просто посмотрите как там делается.

я напишу общий вывод.
не геморройтесь с текстовым видом, создайте нормальные объекты с подобъектами, похожие на аксаптовские семейства классов Query* и Dict*.
с объектами работать из кода на порядки проще!
и только в конце генерите строку.

опять же: проверить идею просто - берете любой cms и разбираетесь как делаются запросы к mySQL в этом CMS.

DML в целом (и обертка вокруг текстовых запросов, в частности) - своеобразный ослиный мостик нашей профессии ))))
эта задача решается почти всеми и по-разному.

[mazzy]

Цитата:

Сообщение от Ruff

Умеет "подсказывать" синтаксис запроса с помощью IntelliSense

и еще. если останетесь в текущей концепции...

генерировать запрос каждый раз - неудобно.
как правило, нужны некие темплейты:
= это выборка по клиентам/поставщикам
= это выборка по платежам
= это выборка по СФ
= это выборка по складским проводкам
= это выборка по складским остаткам и проводками

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

в Аксапте - это Query, запомненный в AOT, и который превращается в объект одной строкой кода

Query q = new Query(querystr(MyAOTquery));

и Query в Аксапте - это далеко не лучший образец для подражания.
в частности, Query в аксапте не имеет механизма контроля обязательных условий и полей, непротиворечивых условий, отвратительно работает со ссылками таблиц на себя (привет сопоставлению проводок по клиенту/поставщику через Query)

[belugin]

Цитата:

Сообщение от mazzy

и Query в Аксапте - это далеко не лучший образец для подражания.
в частности, Query в аксапте не имеет механизма контроля обязательных условий и полей, непротиворечивых условий, отвратительно работает со ссылками таблиц на себя (привет сопоставлению проводок по клиенту/поставщику через Query)

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

[mazzy]

Цитата:

Сообщение от Ruff

на Java

и еще одно... уж, извините.
знающим java будет понятно.

мне всегда режет глаз сложение строк.
в старых java vm это приводит к дикой сборке мусора.
См. холивары на тему StringBuilder vs конкатенация строк.

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

еще один повод все перевести в объекты, а строку генерировать только в самом конце )

[Ruff]

Цитата:

Сообщение от mazzy

c 54 минуты лекции идет генерация HTML в коде

Нууу... это ж котлин, там действительно всё красиво
Но имеем то, что имеем (Х++)...

Цитата:

Сообщение от mazzy

режет глаз сложение строк.

Про конкатенацию почему-то ожидал вопроса)
Мне тоже режет. Хотя какое-нибудь "((%1.%4==%2.%5)||(%2.%6!=%3.%5))" режет не меньше), но холиварить не стану - я тоже против длинных сложений.
В моем случае перевесил критерий переносимости кода (Java -> X++ -> etc.). Речь ведь о прототипе.

Цитата:

Сообщение от mazzy

не геморройтесь с текстовым видом, создайте нормальные объекты с подобъектами, похожие на аксаптовские семейства классов Query* и Dict*.
с объектами работать из кода на порядки проще!
и только в конце генерите строку.

Цитата:

Сообщение от mazzy

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

Шаблоны - это конечно нужно и удобно.
Но тут я вижу немного другое назначение - улучшить читабельность кода, когда все-таки (так сложились звезды) запрос конструируется программно, и потом может подвергаться доработкам. При этом не важно, как он формируется и хранится внутри - текстом или объектами (в примере ниже - на выходе получаем QueryRun).

Цитата:

Сообщение от Logger

генерить аксаптовский Query ?

Вот, для затравки, первые наброски:

    queryRun = SimpleQueryBuilder::newQuery().	// здесь параметром вполне может выступить querystr(MyAOTquery)
        select  (tablenum(CustTable)).
                groupBy     (fieldnum(CustTable,        AccountNum)).
                groupBy     (fieldnum(CustTable,        PartyId)).
            join (tablenum(CustTransOpen)). 
                relations   (true).
                sumField    (fieldnum(CustTransOpen,    AmountCur)).
        parent().
            join (tablenum(CustGroup)).
                relations   (true).
                mode        (JoinMode::ExistsJoin).
                range       (fieldnum(CustGroup, PaymIdType),  paymIdType).

        queryRun();

    while (queryRun.next())
    { ... }

Пример - рабочий, запрос - просто иллюстрация, код SQB пока не публикую, ибо сыр и мало что умеет.

Удобно ли? Или те же буквы, только в профиль?)

[mazzy]

Цитата:

Сообщение от Ruff

Но тут я вижу немного другое назначение - улучшить читабельность кода

хотел удержаться... но не удержался...
обещал не использовать термин "программистский подход"...
Про программистский подход, программистское мышление и стереотипы
но ту тему тоже создал некий пользователь Ruff )))))

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

1.
писать запросы в коде, со всеми полям, join'ами, условиями на поля... само по себе неблагодарное дело. программист тратит свое время не на "читабельность", а на то, чтобы вспомнить обязательные таблицы в join, обязательные связи между таблицами, обязательные поля группировок, непустые поля...

пример? до 2009 включительно номенклатура состоит из 8 таблиц. 3 из которых обязательны, а одна из них должна джойнится три раза с разными условиями.
навскидку запрос напишите?

а в 2012 и дальше ввели общие для всех компаний продукты и вместо номенклатуры "используемые продукты" по отдельным компаниям. там порядка 20 таблиц.
навскидку запрос напишите?

"читабельность кода" - очень малая часть трудозатрат.
нужны пресеты для работы с логическими сущностями.

2.
все становится намного хуже, если идет работа с внешними системами. в 1С например, таблицы и поля имеют числовые идентификаторы вместо названий. а в SAP часто используются немецкие названия полей или еще хуже - английская транскрипция немецких терминов.

читабельность кода? ха-ха-ха-ха!!!!
нужны удобные алиасы для полей и таблиц (особенно внешних).
нужны пресеты для сущностей вместо обращения к отдельным таблицам

например, сущность "номенклатура" - состоит из...
с сущностью "номенклатура" можно работать так то...

3.
во внешних системах и внешних программах часто используются дефиниции таблиц и связей в json- или xml-файлах. (см. например, java-проект Hibernate, http://devcolibri.com/1394 ).
использование таких дефиниций на порядки более упросит жизнь программиста, нежели пресловутая "читаемость кода"

4.
реально упросить жизнь программисту можно одним способом - снизить сложность используемых программистом сущностей.

другими словами,
что нужно:
= чтение уже готовых дефиниций таблиц и связей в объект
= работа с пресетами сущностей, вместо работы с отдельными нормализованными(!) таблицами (как целиком, так и с частью запроса. см. например, InventSum::initQueryFrom)
= пресет должен подсказывать программисту какие компоненты пресета являются обязательными для заполнения по бизнес-логике (поля, условия, группировки, агрегатные функции)
= пресет должен подсказывать программисту когда он нарушает целостность данных из-за отсутствующего компонента запроса (группировка, условие, агрегатная функция)
= пресет должен подсказывать программисту когда он может нарушить целостность данных из-за возможного дублирования уникальных полей

далее нужен код типа:

Query q1 = new QueryBuilder::constructFromPreset(mySuperPreset1);
Query q2 = new QueryBuilder::addFromPreset(q1, mySuperPreset2);

примерно так.

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

но на практике программисту проще написать
str s = strfmt("select %1 from %2 %3 %4 where %5", fields, tableWithAlias, orders, groups, whereclause);
чем разбираться с очередным билдером, который занимается только "читабельностью кода"

)))

[belugin]

Цитата:

Сообщение от mazzy

но на практике программисту проще написать
str s = strfmt("select %1 from %2 %3 %4 where %5", fields, tableWithAlias, orders, groups, whereclause);
чем разбираться с очередным билдером, который занимается только "читабельностью кода"

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

Если это умозрительная конструкция, хотелось бы в этой фразе заменить "на практике" на "в теории".

[belugin]

Цитата:

Сообщение от mazzy

что нужно:
= работа с пресетами сущностей, вместо работы с отдельными нормализованными(!) таблицами (как целиком, так и с частью запроса. см. например, InventSum::initQueryFrom)

См. также DataEntities в Ax7

Цитата:

Сообщение от документация

...
A data entity is an abstraction from the physical implementation of database tables. For example, in normalized tables, a lot of the data for each customer might be stored in a customer table, and then the rest might be spread across a small set of related tables. In this case, the data entity for the customer concept appears as one de-normalized view, in which each row contains all the data from the customer table and its related tables.

A data entity encapsulates a business concept into a format that makes development and integration easier. The abstracted nature of a data entity can simplify application development and customization. Later, the abstraction also insulates application code from the inevitable churn of the physical tables from version to version of Microsoft Dynamics AX.

To summarize: Data entity provides conceptual abstraction and encapsulation (de-normalized view) of underlying table schemas to represent key data concepts and functionalities.
...

[mazzy]

Цитата:

Сообщение от Ruff

Вот, для затравки, первые наброски:

    queryRun = SimpleQueryBuilder::newQuery().	// здесь параметром вполне может выступить querystr(MyAOTquery)
        select  (tablenum(CustTable)).
                groupBy     (fieldnum(CustTable,        AccountNum)).
                groupBy     (fieldnum(CustTable,        PartyId)).
            join (tablenum(CustTransOpen)). 
                relations   (true).
                sumField    (fieldnum(CustTransOpen,    AmountCur)).
        parent().
            join (tablenum(CustGroup)).
                relations   (true).
                mode        (JoinMode::ExistsJoin).
                range       (fieldnum(CustGroup, PaymIdType),  paymIdType).

        queryRun();

    while (queryRun.next())
    { ... }

Пример - рабочий, запрос - просто иллюстрация, код SQB пока не публикую, ибо сыр и мало что умеет.

Удобно ли? Или те же буквы, только в профиль?)

Атличный пример!

mode        (JoinMode::ExistsJoin).

поставлен на группу клиентов, которая в целостной базе всегда есть - в стандартном функционале включен validate.
и не поставлен на открытые проводки, которые вполне штатно могут отсутствовать.

почему выбираются клиенты только с открытыми проводками?
это логика такая? или это логическая ошибка?

почему в запросе читаются только открытые проводки без самих проводок? открытые проводки сами по себе используются крайне редко, поскольку это логически "подчиненная таблица". отсутствие проводок в запросе - это логическая ошибка или так задумано? что вы дальше будете делать с этой несопоставленной суммой по всему клиенту?

прекрасная ошибка - суммирование AmountCur без группировки по валютам. запрос вернет сумму рублей с долларами, еврами, гривнами, тугриками и остальными валютами.
правило: если выполняется агрегирование по AmounCur, то группировка по валютам является обязательной. именно такая информация и должна присутствовать в пресете. подумайте о таких логических взаимосвязях только один раз - при создании пресета, а потом пусть программа думает и предупреждает. тогда и будет "удобно".
))))

упростите именно логическую часть, упростите бизнес-логику для программиста.
программисты даже купят у вас такое решение )

а упрощение синтаксиса... ну, да... оно конечно тоже приятно. )))

==========================
добавил, подумав над запросом:
PaymIdType есть в группе, а есть в самом клиенте. кроме того, он есть в paymMode, который присутствует в проводке.
мне кажется, что фильтрация по полю PaymIdType в группе - всегда логически ошибочна.
мне кажется, что нет случаев, когда фильтрация по полю PaymIdType в группе была бы логически оправдана.

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

а упрощение синтаксиса... ну, да... оно конечно тоже приятно. )))

=================
трудозатраты:
время последней правки 13:00 - время создания готового поста с первоначальным текстом 12:25 ~= 0:35 минут потрачено на обдумывание логики запроса
сам пост написан минуты за 2 )))

и это очень типичное соотношение трудозатрат между логикой и синтаксисом.

[Logger]

Удобно.
Только я бы сразу добавил методы
Join
OuterJoin
ExistsJoin
Бантик, но приятно.

[S.Kuskov]

Дежавю http://www.axaptapedia.com/SYS_ExpressionQueryBuilder

[mazzy]

Цитата:

Сообщение от S.Kuskov

Дежавю http://www.axaptapedia.com/SYS_ExpressionQueryBuilder

SYS_ExpressionQueryBuilder - для аксаптовских таблиц,
а у автора для внешних.

[S.Kuskov]

Цитата:

Сообщение от mazzy

SYS_ExpressionQueryBuilder - для аксаптовских таблиц,
а у автора для внешних.

В первом сообщении - да. А в следующих речь пошла уже про такой же но только для аксаптовских.

[Napalm]

Преимуществ не вижу, одни недостатки - например риск SQL injection-a.

Подход с использованием System.Data.SqlClient на мой взгляд намого лучше. Если код правильно отформатировать - и читать проще и скопировать для отладки в SQL Server Management Studio можно.

[Ruff]

Тема получилась интересной, узнал много нового, всем спасибо Жаль, что оказалась баяном (прошу прощения у Максима Белугина за случайный "плагиат")... По этой причине "нативный" билдер дописывать, думаю, смысла нет, а с "внешним", наверно еще поковыряюсь на досуге.