7/30/2023 0 Comments Sql formatter![]() CommentsĬomments pose very particular and interesting challenges. In particular, the KVP.List class used to manage the formatting options provides extensive functionality based on key-value-pairs but is simply spoofed in LIB.cs to support the one method used in this formatting logic: GetBoolean. ![]() The implementation of the formatter employs a number of routines from unrelated libraried code I've pulled these fragments into an additional file: LIB.cs. Though intended to support multiple dialects, there is currently a very pronounced T-SQL bias. The solution includes a very simple winforms Demo executable along with the Formatter class. When debugging, the formatter prepends an informational header like this example: In time, I'd expect a Dialect option will prove necessary or helpful, but I haven't seen the need yet. The first two options reflect common developer practices of formatting the SQL so that portions are easily commented out for debugging-as opposed to my original intention of making the SQL easier to read. The currently supported options and default values are: Handling the parens as part of the element found seems inelegant, but it actually works quite well-it's a natural consequence of the space-delimited-element strategy and facilitates distinguishing between parentheses which demarcate components of the statement from those which appear in a function call.įormatting options are passed as an equals sign / semi-colon delimited string. Token = (result.Right( 1) != " \t" ? Tabs( true) : " ") " (" Str.NewLine Tabs() " select" Increase tab level - select if (token.Equals( " (select", Str.IgnoreCase)) Keywords inside the CTE definition are formatted as usual, and the first " select" is recognized as requiring leading indentation as it is preceded by an opening parenthesis: The " with" keyword beginning the sample statement indicates that a CTE is being constructed and cte is set to reflect the current tabLevel-information that is required when the second " select" keyword (the reference) is to be formatted. Result.Right( 4) != Str.Repeat(Str.NewLine, 2) ? Str.NewLine : " ") _ The problem turned out to be pretty thorny and left me with the feeling there may be a better (more formal, structured) way to tackle the problem that I'm missing. I'm sharing this in the hope that others may find it useful, but also hoping to learn a bit. I may have missed it but I couldn't find a satisfactory solution so I spent a few nights and weekends and came up with this. I wanted source we could include in the development of our own tools and utilities that I could modify to our preferences that was not dialect-specific-and, of course, it had to be free. In order to make their code easier for me to understand-and in the hopes of moving the group towards more standardized formatting in general, I started looking for a SQL formatter. I recently managed a group of data integration programmers writing a lot of sophisticated SQL. Order by SalesPersonID, SalesYear Background With Sales_CTE(SalesPersonID, SalesOrderID, SalesYear) as
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |