Formatting SQL code made easy

I was just taking a quick glance at AMIS technology blog. This blog aggregate contains quite a few high quality Oracle related entries, as well as some entries relating to eclipse and some other open source developments.

There's an entry by Aino Andriessen referring to a an entry to Eddie Awad's blog. Eddie's been searching the web for SQL pretty printers/formatters, and he has come up with a few interesting ones.

Now, I've been needing such a utility ever since I'm using MySQL 5.0 views. Suppose you've made a complex view using a CREATE VIEW statement, and you want to review your code afterwards.

You could of course maintain the source code to your view outside the database and review that, but why not use the information_schema? The VIEWS system view has a column, VIEW_DEFINITION, that contains the SQL SELECT expression underlying your view. That way, you'd be sure to review the actual code: mistakes are out of the question.

Yes, that's very true: you actually are reviewing exactly the code underlying the view...wich is *NOT* the code you entered in your CREATE VIEW statement; actually, it's not at all the code you entered. This feature request (bug #11082) and the link in that bug report to this message on the MySQL internals list illustrate this perfectly.

I'll just paste the CREATE VIEW statement appearing in that message here:

CREATE VIEW DEBITEUR_NAW
(
 RELATIEID,
 NAAM,
 ADRES,
 POSTCODE,
 PLAATS,
 LAND,
 DEBITEURNUMMER,
 TAV,
 BTWNUMMER,
 BETAALTERMIJN,
 TELEFOON,
 FAX,
 ACTUEEL,
 EMAIL,
 KVKNUMMER
) AS
select d.relatieID,
       r.naam,
       a.adres,
       a.postcode,
       a.plaats,
       l.omschrijving,
       d.debiteurnummer,
       d.Tav,
       d.btwnummer,
       d.betaaltermijn,
       (select telefoonnummer from relatie_telefoon
         where relatieid = r.relatieid and telefooncode = 1 and nummer = 1),
       (select telefoonnummer from relatie_telefoon
         where relatieid = r.relatieid and telefooncode = 2 and nummer = 1),
       r.actueel, r.email, r.kvknummer
from debiteur d
join adres a on (d.relatieid = a.relatieid and a.adrescode = 1)
join relatie r on (d.relatieid = r.relatieid)
left join land l on (a.landcode = l.landcode)
;

Ok, everything's hunky dory here. Now, let's ask the information schema for the view definition:

SELECT  view_definition
FROM    information_schema.views
WHERE   table_schema = schema()
AND     view_name = 'DEBITEUR_NAW'
;

And, suprise, surprise:

select `d`.`relatieid` AS `RELATIEID`,`r`.`naam` AS `NAAM`,`a`.`adres` AS
`ADRES`,`a`.`postcode` AS `POSTCODE`,`a`.`plaats` AS
`PLAATS`,`l`.`omschrijving` AS `LAND`,`d`.`debiteurnummer` AS
`DEBITEURNUMMER`,`d`.`tav` AS `TAV`,`d`.`btwnummer` AS
`BTWNUMMER`,`d`.`betaaltermijn` AS `BETAALTERMIJN`,(select
`serp`.`relatie_telefoon`.`telefoonnummer` AS `telefoonnummer` from
`serp`.`relatie_telefoon` where ((`serp`.`relatie_telefoon`.`relatieid` =
`r`.`relatieid`) and (`serp`.`relatie_telefoon`.`telefooncode` = 1) and
(`serp`.`relatie_telefoon`.`nummer` = 1))) AS `TELEFOON`,(select
`serp`.`relatie_telefoon`.`telefoonnummer` AS `telefoonnummer` from
`serp`.`relatie_telefoon` where ((`serp`.`relatie_telefoon`.`relatieid` =
`r`.`relatieid`) and (`serp`.`relatie_telefoon`.`telefooncode` = 2) and
(`serp`.`relatie_telefoon`.`nummer` = 1))) AS `FAX`,`r`.`actueel` AS
`ACTUEEL`,`r`.`email` AS `EMAIL`,`r`.`kvknummer` AS `KVKNUMMER` from
(((`serp`.`debiteur` `d` join `serp`.`adres` `a` on(((`d`.`relatieid` =
`a`.`relatieid`) and (`a`.`adrescode` = 1)))) join `serp`.`relatie` `r`
on((`d`.`relatieid` = `r`.`relatieid`))) left join `serp`.`land` `l`
on((`a`.`landcode` = `l`.`landcode`)))

Is what is returned. Nasty, eh? Actually, this pretty much forces you to maintain a copy of the source outside the database if you're actively maintaining this view.

Suppose you didn't. Or suppose you're not in control of that: you could be in a position where you arrive at a customer that needs a problem fixed, and you're confronted with just the database. What will you do then huh?

Well, now you can just have your code formatted online. This is what it did:

SELECT `D`.`RELATIEID` AS `RELATIEID`,
       `R`.`NAAM` AS `NAAM`,
       `A`.`ADRES` AS `ADRES`,
       `A`.`POSTCODE` AS `POSTCODE`,
       `A`.`PLAATS` AS `PLAATS`,
       `L`.`OMSCHRIJVING` AS `LAND`,
       `D`.`DEBITEURNUMMER` AS `DEBITEURNUMMER`,
       `D`.`TAV` AS `TAV`,
       `D`.`BTWNUMMER` AS `BTWNUMMER`,
       `D`.`BETAALTERMIJN` AS `BETAALTERMIJN`,
       (SELECT `SERP`.`RELATIE_TELEFOON`.`TELEFOONNUMMER` AS `TELEFOONNUMMER`
        FROM   `SERP`.`RELATIE_TELEFOON`
        WHERE  ((`SERP`.`RELATIE_TELEFOON`.`RELATIEID` = `R`.`RELATIEID`)
                AND (`SERP`.`RELATIE_TELEFOON`.`TELEFOONCODE` = 1)
                AND (`SERP`.`RELATIE_TELEFOON`.`NUMMER` = 1))) AS `TELEFOON`,
       (SELECT `SERP`.`RELATIE_TELEFOON`.`TELEFOONNUMMER` AS `TELEFOONNUMMER`
        FROM   `SERP`.`RELATIE_TELEFOON`
        WHERE  ((`SERP`.`RELATIE_TELEFOON`.`RELATIEID` = `R`.`RELATIEID`)
                AND (`SERP`.`RELATIE_TELEFOON`.`TELEFOONCODE` = 2)
                AND (`SERP`.`RELATIE_TELEFOON`.`NUMMER` = 1))) AS `FAX`,
       `R`.`ACTUEEL` AS `ACTUEEL`,
       `R`.`EMAIL` AS `EMAIL`,
       `R`.`KVKNUMMER` AS `KVKNUMMER`
FROM   (((`SERP`.`DEBITEUR` `D`
          JOIN `SERP`.`ADRES` `A`
            ON (((`D`.`RELATIEID` = `A`.`RELATIEID`)
                 AND (`A`.`ADRESCODE` = 1))))
         JOIN `SERP`.`RELATIE` `R`
           ON ((`D`.`RELATIEID` = `R`.`RELATIEID`)))
        LEFT JOIN `SERP`.`LAND` `L`
               ON ((`A`.`LANDCODE` = `L`.`LANDCODE`)))

Pretty cool huh? I just chose the default options, but you can do stuff like control the letter case and the position of the comma's in lists etc.

It doesn't finish here though: This online tool can take on several input dialects (MS SQL, Oracle, Access, MySQL and generic) and, dig this, several output formats in addition to SQL: PHP, Java, VB and lots and lots more. (In those cases, the code for a string expression in the pertinent language is returned). You can also control

In addition to the online tool, Eddie Awad's blog entry mentions an applet, and a downloadable tool to. I haven't tried those, but you can check em out for yourselves of course.