Algorithm for formating SQL code

Question

I need a tool (for in house usage) that will format SQL code (SQL Server/MySQL).
There are various 3rd party tools and online web sites that do it but no exactly how I need it.

So I want to write my own tool that will fit my needs.

First question is there any standard or a convention for how the SQL code should be formatted? (the tools that I tried format it differently)

The second question, how should I approach this task? Should at first convert the sql query into some data structure like a Tree?

Answer 1

...is there any standard or a convention for how the SQL code should be formatted?

Standard, no. You can put an entire SQL statement on one line as far as an SQL parser is concerned.

Convention, sure there are lots. It depends whether you're trying to maximize changeability or minimize space. I've written SQL formatters for both cases.

I just used particular character combinations to tell me where to break the SQL statement.

Here's one example from a Java DB2 SQL formatter that I wrote. Another Java program generated the Java code. The SQL came directly from the SYSIBM tables.

protected void prepareIndex00Select(String codeFacl)
        throws SQLException {
    StringBuffer sb = new StringBuffer();
    sb.append("SELECT CODE_FACL, SEQ_FACL, FILLER_TOF ");
    sb.append("    , CODE_TOF, NAME_FACL, NAME_LENGTH ");
    sb.append("    , CODE_FMB, ID_NCIC_ORI, NBR_PRINTER_PREFIX ");
    sb.append("    , ID_PERSONNEL_OFC, COMPLEX_CODE ");
    sb.append("    , PHS_CODE, DESIG_FACL_GRP, IND_DESIG_AUTH ");
    sb.append("    , CODE_FACL_I_T, INTKEY_FACL, IND_CDM_SENTENCING ");
    sb.append("    , MAL_FEM_IND, DEL_AFTER, IND_INMATES ");
    sb.append("    , VALUE_SO_CPU_STD, VALUE_SO_CPU_DAY ");
    sb.append("    , CODE_CAT, VALUE_DCN, XIDBKEY ");
    sb.append("    , FACL_FK_REGN ");
    sb.append("  FROM ");
    sb.append(creator);
    sb.append(".FACL ");
    sb.append("  WHERE CODE_FACL = ? ");
    if (additionalSQL != null) sb.append(additionalSQL);

    psIndex00 = connection.prepareStatement(sb.toString());
    psIndex00.setString(1, codeFacl);

}   // End prepareIndex00Select method

Answer 2

A little late, only just stumbled across this, sorry.

Poor Man's T-SQL Formatter is an open-source T-SQL formatter (library, ssms plugin, command-line file formatter, etc) - the implementation is reasonably modular and it shouldn't be very hard to implement a MySQL tokenizer and formatter to match the T-SQL ones (I haven't done so primarily because I have no experience of or use for MySQL right now, so it's not a good use of my time).

The library is implemented in C# (2.0) with an AGPL license - that means you can't redistribute it commercially or expose as a public service without publishing any modifications, but for in-house user it should present no issues, whether it's customized or not.

As @Gilbert Le Blank already answered, there is definitely no standard on SQL formatting - even the commercial formatters out there, with the different options they provide, don't converge on the same defaults or even necessarily support the same output formats.

With respect to writing your own tool from scratch, I would advise against it if you need to handle a variety of cases: at least for T-SQL, handling SQL multi-statement batches with CTE WITH clauses, MERGE statements, sub-queries and derived tables, etc turns out to be pretty hard :)

In case it's any help: http://www.architectshack.com/PoorMansTSqlFormatter.ashx