vsbabu.org

SQL* Beautifier

I got a big SQL script which was in one line (can you believe that? Some nut did something wrong with FTP settings). Anyway, I put together a small program which will indent SQL, PL/SQL code - a la cb.

Note that it will understand *ONLY* lowercase keywords which decide on the indenting (like "begin", "end" , "then" ...).

See if it is useful. Someone can refine it - I had only 30 minutes to do this. Though it is not elegant, it worked for my script! So,the only keywords are what I wanted(and most comonly used SQL keywords ). If it gets changed or extended, be kind enough to send me the modified version...

The file is attached. A Makefile is added towards the end. If you've GNU-flex, you can make this case-insensitive.

Caution : No guarantees! Make a backup copy for your script first. I'm no LEX cat.

    /*
     * sqlb - quick and dirty SQL beautifier
     * comments and strings are untouched
     * understands only lowercase keywords
     * compile as - "lex sqlb.l, cc -o sqlb lex.yy.c -ll, rm lex.yy.c"
     * Use as - cat sqlfile|sqlb|awk NF
     *          awk NF will remove blank lines
     * Vattekkat Satheesh Babu, on Dec 30, 1998
     */
DIGIT    [0-9]
ID       [:A-Za-z]+[A-Za-z0-9_]*
SPACE    [ \t\n]
%{
int currindent=0;
%}
%%
"/*".*\n.*"*/" { ECHO; } /* multi line comments */
"/*".*"*/" { ECHO; } /* one line comments */
"'".*"'"|"--".*"\n"|"rem"{SPACE}+.*"\n" { ECHO;} /* one line comments */
exists|declare|begin|then|select|delete|update  { 
        /* ex - print token, increase indent, nextline */
            printf( "%s\n", yytext);
        currindent++;
        printm();
            }
end|end{SPACE}+if      {
        /* ex - decrease indent, print token at the next line */
        currindent--;
        printf("\n");
        printm();
            printf( "%s ", yytext );
            }
when|elsif {
        /* ex - decrease indent, print token at the next line */
        currindent--;
        printf("\n");
        printm();
            printf( "%s ", yytext );
            }
exception|else|set|from|where|and|or   {
        /* ex - decrease indent, print token at the next line,inc indent */
        currindent--;
        printf("\n");
        printm();
            printf( "%s\n", yytext );
        currindent++;
        printm();
            }

";"     { printf(";\n");printm(); }
","     { printf(", "); }
"<"|">"|"="|"+"|"-"|"*"|"/"|")"|"("  { printf( "%s ", yytext ); }
":="|">="|"<="|"!="|"<>"  { printf( "%s ", yytext ); }
"%"|"." {ECHO;}
{ID}|{ID}[%@.]{ID}|{DIGIT}+|{DIGIT}+"."{DIGIT}+ {
       /*ints, floats, strings, identifiers */
       /* package members, dblinks.. */
            printf( "%s ", yytext );
            }
{SPACE}+          {;}/* eat up whitespace */

.           printf( "Unrecognized character: %s\n", yytext );
%%
int
printm()
{
    int i=0;
    while(i<currindent)

    {
        printf("\t");
        i++;
    }
}

/*------------ Here's a small Makefile for HP-UX --------------------
sqlb:lex.yy.c
    cc -Ae -o sqlb lex.yy.c -ll
    strip sqlb
lex.yy.c: sqlb.l
    lex sqlb.l
clean:
    rm -f lex.yy.? core sqlb.o
-------------------------------------------------------------------*/