Writing in the front

SQL formatting, there are a lot of libraries, the more common ones like SQL-Formatter, can meet most of the needs, but not my needs. The reason is that my SQL is dynamic, with parameters and logical judgments. I have tried various modes and found many plug-ins, all of which can effectively format the dynamic SQL part. Here is a small example of dynamic SQL:

    select id, name, des
    from table_user 
    where class='601' 
        #if(${name})
           and name = '${name}'
        #end
    limit 1
Copy the code

${name} and name = ${name} = ${name} and name = ${name}

This dynamic SQL is formatted using standard SQL from SQL-formatter like this:

There are four minor problems with formatting like this:

  • # does not break lines, affecting reading;
  • Dynamic SQL execution is affected by space between # and if;
  • $and {} have Spaces between them, affecting dynamic SQL execution;
  • # and end are split, affecting dynamic SQL execution;

Tried several other SQL modes, there are all kinds of problems, there is no way to change the source code.

SQL – the formatter source code

Open the SQL-formatter code in the editor, first look at the directory structure, to understand the function of the subcontract, but also clear:

  • The core folder is core code
  • The Language folder is a different configuration file for the various SQL statements
  • Sqlformatter.js is the library entry file

This change in view of the standard SQL version, that is, languages/StandardSqlFormatter js file, you can see the standard SQL keywords are classified in configuration files, there are generally keywords, has exclusive line of immediate indentation keywords, have an exclusive line head not indented keywords, There are also keywords that need a new line, which is understandable:

#if, #elseif, #else, #end; #if, #else, #end

// single line
const reservedNewlineWords = [
  'AND'.'ELSE'.'OR'.'WHEN'.// joins
  'JOIN'.'INNER JOIN'.'LEFT JOIN'.'LEFT OUTER JOIN'.'RIGHT JOIN'.'RIGHT OUTER JOIN'.'FULL JOIN'.'FULL OUTER JOIN'.'CROSS JOIN'.'NATURAL JOIN'.New add # ifelse / /
  '#IF'.'#ELSEIF'.'#ELSE'.'#END',];Copy the code

In addition, for Spaces between $and {}, you can add ‘to the end of the parameter Settings section of the standard SQL configuration file.The dollar sign{‘ and ‘}’ will do.

Here is the main change of $$and {the space between, the other is to add a few logic symbol, thus formatting dynamic SQL basic logic is implemented, the only fly in the ointment is the # if… The and in #end is not indented.

conclusion

If #if and #end are indented one space, please let us know in the comment section