BE-SQL

2017-02-27T11:37:15Z

Uuinnk:

'''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, RTRIM, LTRIM, TRIM, LPAD, ROUND, TRUNC (TRUNCATE), INSTR (STRPOS), IF, NOW, FORMAT_DATE (=TO_CHAR(x, 'YYYY-MM-DD')), YEAR, MONTH, DAY,
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')),
FORMAT_MONTHYEAR(=TO_CHAR(x, 'month YYYY')), FORMAT_FMDAYMONTH, FORMAT_FMDAYMONTH, FORMAT_HOURMINUTE, FORMAT_DAY_OF_WEEK, FORMAT_YYYYMMDD,
FORMAT_YYYYMM, DATE_TRUNC, POSITION, DATE_ADD, ADD_MONTHS, ADD_DAYS, ADD_MILLIS, LAST_DAY, TIMESTAMPDIFF, YEARDIFF, MONTHDIFF, DAYDIFF, HOURDIFF,
MINUTEDIFF, SECONDDIFF, LEAST, GREATEST, GROUPING, ROW_NUMBER, RANK, RIGHT, LEFT, NULLIF, ABS, AVG, DECODE, TRANSLATE (DB2, Oracle, PostgreSQL),
REGEXP_LIKE (MySQL, Oracle, PostgreSLQ), REGEXP_REPLACE (DB2, Oracle, PostgreSQL), STRING_AGG (с поддержкой DISTINCT: MySQL, PostgreSQL; без: DB2, Oracle).

=== Функции для работы с датами и временем ===

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

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

* '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())
* 'month YYYY' или '%M %Y' (также можно использовать функцию FORMAT_MONTHYEAR())
* 'FMDD.MM' или '%e.%c' (режим заполнения, также можно использовать функцию FORMAT_FMDAYMONTH())
* 'DD.MM' или '%d.%m' (также можно использовать функцию FORMAT_DAYMONTH())
* 'HH24:MI' или '%H:%i' (также можно использовать функцию FORMAT_HOURMINUTE())
* 'YYYYMMDD' или '%Y%m%d' (также можно использовать функцию FORMAT_YYYYMMDD())
* 'YYYYMM' или '%Y%m' (также можно использовать функцию FORMAT_YYYYMM())
* 'D' или '%w' (также можно использовать функцию FORMAT_DAY_OF_WEEK())
* 'YYYY' или '%Y'
* 'MM' или '%m'
* 'DD' или '%d'

SELECT NOW(), FORMAT_DATE(NOW());

NOW FORMAT_DATE
----------------------- -----------
2016-03-04 11:12:38.360 2016-03-04

SELECT NOW(), FORMAT_DATETIME(NOW());

NOW FORMAT_DATETIME
----------------------- -------------------
2016-03-04 11:12:38.360 2016-03-04 11:12:38

SELECT NOW(), FORMAT_DATE_RUS(NOW());

NOW FORMAT_DATE_RUS
----------------------- ---------------
2016-03-04 11:12:38.360 04.03.2016

SELECT NOW(), FORMAT_DATE_RUS_SHORT(NOW());

NOW FORMAT_DATE_RUS_SHORT
----------------------- ---------------------
2016-03-04 11:12:38.360 04.03.16

SELECT TO_CHAR(NOW(), 'YYYY');
-> 2016

Для получения числа, месяца или года заданной даты в виде числа используются функции <code>DAY(дата)</code>, <code>MONTH(дата)</code> или <code>YEAR(дата)</code> соответственно:

SELECT YEAR(CURRENT_DATE);
-> 2016

Аналогично можно использовать <code>EXTRACT(YEAR FROM CURRENT_DATE)</code>.

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

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

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

SELECT EXTRACT(YEAR FROM AGE('2015-05-01', '2013-02-01'));
-> 2

SELECT YEARDIFF('2013-02-01', '2015-05-01');
-> 2

SELECT MONTHDIFF('2013-02-01', '2015-05-01');
-> 27

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

SELECT TIMESTAMPDIFF(DAY, '2016-01-01', '2017-01-01');
-> 366

SELECT TIMESTAMPDIFF(SECOND, '2016-01-01', '2016-01-02' );
-> 86400

==== Добавление интервала к дате ====

Для добавления к дате месяцев, дней или миллисекунд используются функции <code>ADD_MONTHS(дата, количество)</code>, <code>ADD_DAYS(дата, количество)</code>, <code>ADD_MILLIS(дата, количество)</code> соответственно:

SELECT CURRENT_DATE, ADD_MONTHS(CURRENT_DATE, 2);

CURRENT_DATE ADD_MONTHS
------------ ----------
2016-03-04 2016-05-04

SELECT CURRENT_DATE, ADD_DAYS(CURRENT_DATE, 7);

CURRENT_DATE ADD_DAYS
------------ ----------
2016-03-04 2016-03-11

SELECT NOW(), ADD_MILLIS(NOW(), 3000);

NOW ADD_MILLIS
----------------------- -----------------------
2016-03-04 11:12:38.360 2016-03-04 11:12:41.360

==== Последний день месяца ====

Для получения последнего дня месяца используется функция <code>LAST_DAY(дата)</code>:

SELECT CURRENT_DATE, LAST_DAY(CURRENT_DATE) "Last", LAST_DAY(CURRENT_DATE) - CURRENT_DATE "Days Left";

CURRENT_DATE Last Days Left
------------ ---------- ---------
2016-03-04 2016-03-31 27

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

+, -, *, /, >, <, >=, <=, = (или ==), != (или <>), || (конкатенация строк), OR, AND (или &&), NOT (или !), LIKE, FLOOR, MOD (или %),
& (побитовое и), | (побитовое или), ~ (соответствие регулярному выражению; MySQL, Oracle, PostgreSQL).

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

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

* BE-теги <if>/<unless> в некоторых нетривиальных местах. Так как в BE-SQL строится синтаксическое дерево, подобные теги нельзя размещать где угодно. Точно работает <if>/<unless> в следующих местах:
** В списке колонок (<code>SELECT a, <if ...>b,</if> c FROM table</code>)
** Для UNION SELECT (<code>SELECT a FROM table <if ...>UNION SELECT b FROM table</if></code>, а также <code><if ...>SELECT a FROM table UNION</if> SELECT b FROM table</code>)
** Для всего предложения FROM (<code>SELECT a <if ...>FROM table</if></code>)
** Для всего предложения WHERE (<code>SELECT a FROM table <if ...>WHERE b = c</if></code>)
** Для всего и в списке колонок внутри GROUP BY (<code>SELECT * FROM table GROUP BY a,<if ...>b, c,</if> d</code>)
** Для всего и в списке колонок внутри ORDER BY (<code>SELECT * FROM table ORDER BY a DESC <if ...>, b, c</if>, d</code>)
** Для одной (не самой первой) из веток CASE WHEN (<code>SELECT CASE WHEN x > 5 THEN 'a' <if ...>WHEN x > 10 THEN 'b'</if>ELSE 'c' END FROM table</code>)
** Для одного или нескольких предложений JOIN (<code>SELECT a FROM table <if ...>JOIN table2 ON (...) JOIN table3 ON (...)</if></code>)
** Для фрагмента булевого выражения, начинающегося с AND или OR (<code>SELECT a FROM table WHERE x > 2 <if ...>AND y > 3</if><if ...>OR z > 4</if></code>)
** Для фрагмента конкатенации строк через ||, начинающегося с || (<code>SELECT a || b <if ...>|| c || d</if> || e</code>)
** Для простого выражения, обязательно с альтернативой (<code>SELECT a FROM table WHERE x > <if ...>5<else>6</if></code>)
** Внутри строкового литерала (<code>'<a href="/test?<if parameter='xyz'>xyz=true&</if>abc=true">'</code>)
* genericRef, joinGenericRef

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

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

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

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

SELECT MY_MACRO(code) FROM table t;

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

=== Определение СУБД-специфичных функций ===

Определение СУБД-специфичных функций начинается с <code>DBMS_TRANSFORM</code>. За ним идет название функции и список аргументов в скобках. В случае отсутствия аргументов скобки необходимо оставить пустыми. Далее следует выражение <code>CASE</code>, в котором в блоках <code>WHEN</code> перечисляются СУБД, а в блоках <code>THEN</code> указывается возвращаемое выражение при трансляции в соответствующую СУБД. Это выражение может содержать функции, которые не были определены ранее, однако это не сделает возможным их дальнейшее использование вне трансляции. Если функцию необходимо транслировать как есть, после <code>THEN</code> указывается <code>AS IS</code>. В блоке <code>WHEN</code> ожидаются названия из списка: <code>'db2'</code>, <code>'mysql'</code>, <code>'oracle'</code>, <code>'postgres'</code>, <code>'sqlserver'</code>. Если СУБД, в которую необходимо выполнить трансляцию функции, отсутствует во всех условиях <code>WHEN</code>, произойдет ошибка. Заканчивается определение ключевым словом <code>END</code>.

DBMS_TRANSFORM LPAD(str, size, fill)
CASE WHEN 'postgres', 'oracle', 'mysql', 'db2' THEN AS IS
WHEN 'sqlserver' THEN RIGHT(REPLICATE(fill, size) + str, size)
END

Запрос <code>SELECT LPAD('lpad', 8, '0') FROM table t</code> для DB2, MySQL, Oracle и PostgreSQL сохранит свой вид, а при трансляции в Microsoft SQL Server будет преобразован в <code>SELECT RIGHT(REPLICATE('0', 8) + 'lpad', 8)</code> FROM table t.