C # Source для форматирования SQL
Митчел Вайнс
Вступление
Источник для простого и универсального класса SQL-форматера.
Например:
WITH Sales_CTE(SalesPersonID, SalesOrderID, SalesYear)
AS(SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE GROUP BY SalesYear, SalesPersonID ORDER BY SalesPersonID, SalesYear;
становится:
with Sales_CTE(SalesPersonID, SalesOrderID, SalesYear) as
(
select SalesPersonID,
SalesOrderID,
Year(OrderDate) as SalesYear
from Sales.SalesOrderHeader
where SalesPersonID is not NULL
)
select SalesPersonID,
Count(SalesOrderID) as TotalSales,
SalesYear
from Sales_CTE
group by SalesYear, SalesPersonID
order by SalesPersonID, SalesYear;
Предыстория
Недавно я управлял группой программистов по интеграции данных, которые писали много сложного SQL-кода. Чтобы мне было проще понять их код - и в надежде переместить группу к более стандартизированному форматированию в целом, я начал искать форматировщик SQL. И их много, но ...
Я хотел найти источник, который мы могли бы включить в разработку наших собственных инструментов и утилит; который я мог бы изменить для наших потребностей: он не должен был быть специфическим для диалекта - и, конечно, он должен был быть бесплатным. Возможно, я пропустил это, но я не мог найти удовлетворительное решение, поэтому я провел несколько ночей и выходных и придумал это.
Использование кода
Использование класса очень просто:
var fmtr = new SQL_Formatter.Formatter();
var formattedStatement = fmtr.Format("... unformatted statement..." [, "... options..."]);
if (fmtr.Success)
... use the result ...;
else
throw new exception(fmtr.LastResult);
Вместо того, чтобы выдавать ошибки синтаксического анализа, булевское свойство Success устанавливается в false, а свойство LastResult - в информационное сообщение.
Метод
Метод Format сначала вызывает закрытый метод UnFormat для свертывания и добавления пробела к входным данным, чтобы его можно было проанализировать как набор элементов, разделенных пробелом. Каждый элемент затем извлекается в переменную токена и проверяется на его синтаксическую значимость.
public string Format(string sql)
...
// Remove formatting
try { sql = UnFormat(sql); }
catch (Exception ex)
{ return Fail(ex.Message); }
// Parse the statement
while (stmtIndex < sql.Length)
{
// Skip leading spaces
while (stmtIndex < sql.Length && sql.Substring(stmtIndex, 1) == " ")
stmtIndex++;
// Grab the next token, space delimited
if (sql.IndexOf(" ", stmtIndex) > -1)
token = sql.Substring(stmtIndex, sql.IndexOf(" ", stmtIndex) - stmtIndex);
else
token = sql.Substring(stmtIndex, sql.Length - stmtIndex);
...
Когда элемент идентифицирует начало комментария или цитируемого литерала, он расширяется - все последующие элементы просто добавляются к нему - до тех пор, пока не будет найден конец этой конструкции. Затем он добавляется к результату, и анализ продолжается со следующего элемента.
Элементы, которые вводят ключевые слова SQL из нескольких слов («left», например, в «left join»), сохраняются в переменной previousToken и объединяются с последующими элементами до тех пор, пока ключевое слово не будет завершено.
Затем каждый элемент рассматривается для его роли в форматировании (увеличение или уменьшение отступа, вставка новых строк) перед добавлением в форматированный вывод. Неожиданные элементы, например «when» без предшествующего «case», приводят к сбою форматирования. Вспомогательная функция Fail реализует обработку ошибок, описанную выше.
На уровень отступа влияют ключевые слова, скобки и другие элементы, которые отслеживаются отдельно в локальных переменных: tabLevel, parenLevel и т. д. Функция netParens оценивает влияние каждого элемента на отступы из-за скобок, а функция Tabs считает, что эти переменные возвращаются соответствующие отступы и вертикальные пробелы, поскольку каждый элемент добавляется к результату. Когда достигается конец оператора, любое ненулевое значение в переменных представляет недопустимый синтаксис во входных данных (несбалансированные скобки и т. д.), и форматирование не выполняется.
Стек currentKeyword, индексируемый tabLevel, отслеживает вложение конструкций SQL, что отражается в отступе. CTE и описания случаев также требуют особого рассмотрения.
На практике нужно учитывать множество вариаций и тонкостей, чтобы получить желаемый результат. Например, найдя элемент «select», он может быть частью CTE, начиная новый оператор при условии T-SQL или PL/SQL и т. д. Рассмотрим пример в начале этого текста, вот часть кода, которая обрабатывает «select»:
...
case "select":
// Begin a select statement
// Pushes occur below, see tabLevel
if (cte == tabLevel)
{
// Keep together--prevent the default vertical whitespace
token = Tabs(true) + token.ToLower();
cte = -1;
}
else if (currentKeyword[tabLevel] == "")
// New statement
token = Tabs() + token.ToLower();
else if (currentKeyword[tabLevel] == "if")
// SQL conditional
token = Tabs(true) + "\t" + token.ToLower();
else if (!currentKeyword[tabLevel].In(new string[] _
{ "select", "insert", "insert into", "if" }))
// Force vertical whitespace
token = (result.gtr("") & _
result.Right(4) != Str.Repeat(Str.NewLine, 2) ? Str.NewLine : "") + _
Tabs(true, 1) + token.ToLower();
else
// Newline only
token = Tabs(true) + token.ToLower();
...
Ключевое слово «with», начинающееся с примера оператора, указывает, что создается CTE, и cte настроен для отображения текущего tabLevel - информации, которая требуется, когда необходимо отформатировать второе ключевое слово «select» (ссылка). Ключевые слова внутри определения CTE форматируются как обычно, и первый «select» распознается как требующий начального отступа, так как ему предшествует открывающая скобка:
...
// Increase tab level -- select
if (token.Equals("(select", Str.IgnoreCase))
{
tabLevel++;
token = (result.Right(1) != "\t" ? Tabs(true) : "") + "(" + Str.NewLine + Tabs() + "select";
currentKeyword.Add("select");
currentParens = parenLevel;
}
...
Обращение со скобками как частью найденного элемента кажется неэлегатным, но на самом деле это работает довольно хорошо - это естественное следствие стратегии с разделителями-пробелами и облегчает различие между круглыми скобками, которые разграничивают компоненты выражения и те, которые появляются в функции call.
Параметры форматирования передаются в виде строки, разделенной знаком равенства /точкой с запятой. В настоящее время поддерживаются следующие параметры и значения по умолчанию:
- LeadingCommas = false
- LeadingJoins = true
- RemoveComments = false
Первые два варианта отражают общую практику разработчиков по форматированию SQL, так что части легко комментируются для отладки, в отличие от моего первоначального намерения облегчить чтение SQL.
Отладка
При отладке форматировщик добавляет информационный заголовок, как в этом примере:
/*
Formatted -- https://www.codeproject.com/Articles/1275027/Csharp-source-for-SQL-formatting
Length: 273
Elapsed: 46 milliseconds
*/
Демонстрация
Решение включает в себя очень простой исполняемый файл winforms Demo вместе с классом Formatter.
Точки интереса
Хотя он предназначен для поддержки нескольких диалектов, в настоящее время наблюдается очень выраженный уклон T-SQL.
Реализация форматера использует ряд подпрограмм из несвязанного кода библиотеки: я вытащил эти фрагменты в дополнительный файл: LIB.cs. В частности, класс KVP.List, используемый для управления параметрами форматирования, предоставляет расширенные функциональные возможности, основанные на парах ключ-значение, но просто подделан в LIB.cs для поддержки одного метода, используемого в этой логике форматирования: GetBoolean.
Комментарии
Комментарии ставят очень конкретные и интересные задачи. Этот код не пытается их отформатировать, но проблема, которую они вызывают, заставляет меня задуматься о том, правильный ли это выбор.
Интересным компонентом этой проблемы является то, что форматирование, относящееся к комментарию, часто появляется за пределами разделителей, которые указывают, что это комментарий, который анализируется - как показано символами новой строки в этом примере ниже:
select * /* first comment */
from table
/* second comment */
Кроме того, комментарии, как правило, отформатированы для удобства чтения в неформатированном (или, скорее, в «первоначально отформатированном») коде. Когда вы изменяете форматирование, комментарии появляются случайно отформатированными и, следовательно, отвлекающими и гораздо менее полезными.
Я не нашел хорошего решения этой проблемы и в настоящее время оставляю это в качестве ручного труда очистки.