BE-SQL

BE-SQL — диалект SQL, который поддерживается BeanExplorer SQL Parser. Поддерживаются только запросы SELECT. Больше всего он похож на PostgreSQL, но некоторые конструкции из других диалектов тоже поддерживаются. Он также должен понимать различные BE-специфичные теги вроде <if>, <unless> и т. д. В данный момент находится в разработке: не все реальные запросы из проектов успешно разбираются BE-SQL.

Соответствие FreeMarker-конструкций и BE-SQL конструкций:

• chr(...) — CHR() или CHAR()
• concat(...) — CONCAT() или ||
• coalesce(...) — COALESCE() или IFNULL()
• round(...) — ROUND()
• length(...) — LENGTH() или LEN()
• lpad(...) — LPAD()
• replace(...) — REPLACE()
• substring(...) — SUBSTR() или SUBSTRING()
• if(...) — IF() (может транслироваться в предложение CASE WHEN ...)
•  ?dateFormat — FORMAT_DATE()
•  ?dateTimeFormat — FORMAT_DATETIME()
•  ?dateFormatRus — FORMAT_DATE_RUS()
•  ?asInt — CAST(x AS BIGINT), TO_NUMBER(x)
•  ?asVarChar — CAST(x AS CHAR), CAST(x AS VARCHAR), TO_CHAR(x)
•  ?asDate — CAST(x AS DATE), TO_DATE(x)
•  ?asPK — CAST(x AS KEY), TO_KEY(x)
•  ?lower — LOWER()
•  ?upper — UPPER()
•  ?monthStart — DATE_TRUNC('MONTH', x)
•  ?yearStart — DATE_TRUNC('YEAR', x)
•  ?year — YEAR(), EXTRACT(YEAR FROM x)
•  ?month — MONTH(), EXTRACT(MONTH FROM x)
•  ?day — DAY(), EXTRACT(DAY FROM x)
•  ?indexOf — INSTR(string, subString) или POSITION(subString IN string)
•  ?str — просто пишите в 'одинарных кавычках', экранируйте одинарную кавычку с помощью '\'
•  ?quote — просто пишите в "двойных кавычках" или в `обратных кавычках`, оба варианта должны сработать и транслироваться при необходимости
• currentDateTime — CURRENT_TIMESTAMP или NOW()
• currentDate — CURRENT_DATE
• addMonths(date,mon) — ADD_MONTHS(date, mon)
• addDays(date, days) — ADD_DAYS(date, days)
• addMillis(date, ms) — ADD_MILLIS(date, ms)
• fromFakeTable — FROM DUAL или можно не писать FROM вообще

Для ограничения количества записей можно просто писать SELECT ... LIMIT 1: это транслируется в соответствующий диалект SQL.

Список всех поддерживаемых SQL-функций

В скобках указываются синонимы

CONCAT, COALESCE (IFNULL), COUNT, SUM, MAX, MIN, SUBSTR (SUBSTRING), LENGTH (LEN), UPPER, LOWER, CHR (CHAR), TO_CHAR, TO_NUMBER, TO_DATE, TO_KEY,
REPLACE, LPAD, ROUND, TRUNC (TRUNCATE), IF, NOW, FORMAT_DATE (=TO_CHAR(x, 'YYYY-MM-DD')), FORMAT_DATETIME (=TO_CHAR(x, 'YYYY-MM-DD HH24:MI:SS')),
FORMAT_DATE_RUS (=TO_CHAR(x, 'DD.MM.YYYY')), FORMAT_DATE_RUS_SHORT (=TO_CHAR(x, 'DD.MM.YY')), YEAR, MONTH, DAY, DATE_TRUNC, INSTR (STRPOS), 
POSITION, DATE_ADD, ADD_MONTHS, ADD_DAYS, ADD_MILLIS, LAST_DAY, TIMESTAMPDIFF, YEARDIFF, MONTHDIFF, DAYDIFF, HOURDIFF, MINUTEDIFF, SECONDDIFF.

Форматирование даты

Для форматирования даты в строку можно использовать TO_CHAR(дата, <строка_формата>) или DATE_FORMAT(дата, <строка_формата>). Строка формата должна быть одной из предопределённых констант из списка, иначе будет ошибка:

• 'YYYY-MM-DD' или '%Y-%m-%d' (также можно использовать функцию FORMAT_DATE())
• 'YYYY-MM-DD HH24:MI:SS' или '%Y-%m-%d %H:%i:%S' (также можно использовать функцию FORMAT_DATETIME())
• 'DD.MM.YYYY' или '%d.%m.%Y' (также можно использовать функцию FORMAT_DATE_RUS())
• 'DD.MM.YY' или '%d.%m.%y' (также можно использовать функцию FORMAT_DATE_RUS_SHORT())
• 'YYYY' или '%Y'
• 'MM' или '%m'
• 'DD' или '%d'

Заметьте, что функция YEAR(дата) возвращает число, а TO_CHAR(дата, 'YYYY') — строку. Аналогично MONTH и DAY.

Разница между датами

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

• YEARDIFF(startDate, endDate) или EXTRACT(YEAR FROM AGE(endDate, startDate))
• MONTHDIFF(startDate, endDate) или EXTRACT(MONTH FROM AGE(endDate, startDate)) + 12 * EXTRACT(YEAR FROM AGE(endDate, startDate))
• DAYDIFF(startDate, endDate) или EXTRACT(DAY FROM(endDate - startDate))
• HOURDIFF(startDate, endDate) или FLOOR(EXTRACT(EPOCH FROM(endDate - startDate)) / 3600)
• MINUTEDIFF(startDate, endDate) или FLOOR(EXTRACT(EPOCH FROM(endDate - startDate)) / 60)
• SECONDDIFF(startDate, endDate) или EXTRACT(EPOCH FROM(endDate - startDate))

Также можно использовать функцию TIMESTAMPDIFF(<интервал>, startDate, endDate), где в качестве интервала должно быть YEAR, MONTH, DAY, HOUR, MINUTE или SECOND.

Список всех поддерживаемых операций

+, -, *, /, >, <, >=, <=, = (или ==), != (или <>), || (конкатенация строк), OR, AND (или &&), NOT (или !), LIKE, FLOOR.

Что точно не работает пока

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

• BE-теги <if>/<unless> в JOIN/ORDER BY/GROUP BY и других нетривиальных местах
• genericRef, joinGenericRef

Создание макросов

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

MACRO MY_MACRO(arg1, arg2='000000')
CONCAT(SUBSTR(arg1, 1, 5 ), arg2)
END

Вызываются макросы как обычные функции:

SELECT MY_MACRO(code) FROM table t

Такой запрос будет преобразован в SELECT CONCAT(SUBSTR(code, 1, 5 ), '000000') FROM table t.