06 August 2004 6 comments Python
In my latest work stuff I have a custom debugger module that prints the SQL statements used to stdout. To make the debug output more readable I whipped together this quick script that pretty prints SQL statements with hopefully correct case and indentation. It converts something ugly like this:
select * from foo order by bar;
into this:
SELECT
*
FROM
foo
ORDER BY bar;
Try with your own SQL statements
select distinct cl.nom_cli, c.tkt_cor, c.cod_cor, b.nom_bas, r.nom_reg ,l.cod_loc ,l.nom_loc,
T.DSC_TIP_ORS, eee.dsc_tip_eqp, p.dsc_prb, cp.srv_exe,
c.dta_ini, c.hor_ini, c.dta_ter, c.hor_ter,
c.srv_exe, c.dta_abr_cor, c.dsc_his, s.dsc_sta_ors, F.NOM_FUN,
l.id_reg_fat
from corretiva c, cliente cl, statusordemservico s, localidade l,
regiao r, base b, EQUIPE E, FUNCIONARIO F, TIPOORDEMSERVICO T, CORRETIVAEQUIPAMENTO CC,
corretivaproblema cp, problema p, tipoequipamento eee
where cl.id_cli = 16 AND c.id_cli = cl.id_cli and c.id_sta_ors = s.id_sta_ors and c.id_sta_ors = 12 and
c.id_loc = l.id_loc and r.id_reg = c.id_reg and b.id_bas = c.id_bas AND L.COD_EQU = E.COD_EQU
AND F.ID_FUN = E.ID_FUN AND C.ID_TIP_ORS = T.ID_TIP_ORS AND C.COD_COR *= CC.COD_COR
and cp.cod_cor = c.cod_cor and p.id_prb = cp.id_prb and p.id_tip_eqp = eee.id_tip_eqp and c.dta_abr_cor between '2004-05-25 00:00:00' AND '2004-06-25 23:59:59'
order by cl.nom_cli,b.nom_bas, r.nom_reg, s.dsc_sta_ors, C.DTA_ABR_COR
At least my script got something right, but it just isn't good enough on all the AND and ',' separated constraint and fields. How do you think it should look?
select distinct cl.nom_cli,
c.tkt_cor,
c.cod_cor,
b.nom_bas,
r.nom_reg,
l.cod_loc,
l.nom_loc,
T.DSC_TIP_ORS,
eee.dsc_tip_eqp,
p.dsc_prb,
cp.srv_exe,
c.dta_ini,
c.hor_ini,
c.dta_ter,
c.hor_ter,
c.srv_exe,
c.dta_abr_cor,
c.dsc_his,
s.dsc_sta_ors,
F.NOM_FUN,
l.id_reg_fat
from corretiva c,
cliente cl,
statusordemservico s,
localidade l,
regiao r,
base b,
EQUIPE E,
FUNCIONARIO F,
TIPOORDEMSERVICO T,
CORRETIVAEQUIPAMENTO CC,
corretivaproblema cp,
problema p,
tipoequipamento eee
where cl.id_cli = 16
AND c.id_cli = cl.id_cli
and c.id_sta_ors = s.id_sta_ors
and c.id_sta_ors = 12
and c.id_loc = l.id_loc
and r.id_reg = c.id_reg
and b.id_bas = c.id_bas
AND L.COD_EQU = E.COD_EQU
AND F.ID_FUN = E.ID_FUN
AND C.ID_TIP_ORS = T.ID_TIP_ORS
AND C.COD_COR * = CC.COD_COR
and cp.cod_cor = c.cod_cor
and p.id_prb = cp.id_prb
and p.id_tip_eqp = eee.id_tip_eqp
and c.dta_abr_cor between '2004-05-25 00:00:00' AND
'2004-06-25 23:59:59'
order by cl.nom_cli, b.nom_bas, r.nom_reg, s.dsc_sta_ors, C.DTA_ABR_COR
and some tabs because they are wiped out in the comments.
This is a poser for you: an MS SQL view with lots of CASE statements. It's ugly as hell, but that's exactly why I need to pretty-print it. I'm not the original author :-)
SELECT TOP (100) PERCENT SEG.segID, SEG.segEndDate, SEG.segTypeID, SEG.segStartDate, SEG.segItemID, SEG.segQuoteCost, SEG.segNetCost,
SEG.segActualCost, SEG.segBookConfDate, SEG.segBookConfID, SEG.TripID, SEG.operatorID, SEG.segFld1, SEG.segStartTime, SEG.segEndTime,
SEG.attractID, SEG.segStartOfficeID, SEG.segEndOfficeID, SEG.segDays, SEG.segPaymentInfo, SEG.segAdminInfo, SEG.routeID, SEG.segAdults,
SEG.segChildren, SEG.segBabies, SEG.segFamilies, SEG.segPensioners, SEG.segBookRef, SEG.segDesc, SEG.segDateEmailed,
SEG.segDateConfirmed, SEG.segAmended, SEG.passID, SEG.segConfirmed, SEG.segConfComments, SEG.segCommPaid, SEG.segError,
SEG.segStartLocID, SEG.segEndLocID, SEG.segLength, SEG.segPriceStartDate, SEG.segPriceEndDate, SEG.segPriceInfo, SEG.segPricedByAgent,
SEG.segPriceSimple, CASE WHEN SEG.segTypeID = 3 THEN Air.name ELSE OPER.operatorName END AS operatorName,
CASE WHEN SEG.segTypeID = 3 THEN segAdminInfo ELSE CASE WHEN SEG.segItemID IS NOT NULL AND
SEG.segItemID != 0 THEN ITEM.segItemTitle WHEN SEG.routeID IS NOT NULL AND
SEG.routeID != 0 THEN ROUTE.routeTitle WHEN SEG.attractID IS NOT NULL AND
SEG.attractID != 0 THEN ATTRACT.attractTitle WHEN SEG.specialID != 0 AND SEG.specialID IS NOT NULL THEN SPECIAL.Title END END AS itemTitle,
SEGTYPE.segTypeName, CASE WHEN SEG.segTypeID = 4 OR
SEG.segTypeID = 5 OR
SEG.segTypeID = 12 THEN ROUTEOFFSTART.officeNameText ELSE OFFSTART.officeNameText END AS startOfficeText,
CASE WHEN SEG.segTypeID = 4 OR
SEG.segTypeID = 5 OR
SEG.segTypeID = 12 THEN ROUTEOFFEND.officeNameText ELSE OFFEND.officeNameText END AS endOfficeText, dbo.tbltrips.tripNetCost AS tripCost,
LOCSTART.locTitle AS startLocTitle, LOCEND.locTitle AS endLocTitle, CASE WHEN SEG.segTypeID = 3 THEN 0 ELSE 1 END AS IsFlight,
OFFSTART.officePhone, OFFSTART.officeAddress, SEG.segPrevCost, SEG.specialID
FROM dbo.tblsegtypes AS SEGTYPE RIGHT OUTER JOIN
dbo.OfficeList AS ROUTEOFFSTART INNER JOIN
dbo.tblRoute AS ROUTE ON ROUTEOFFSTART.officeID = ROUTE.routeStartOfficeID INNER JOIN
dbo.OfficeList AS ROUTEOFFEND ON ROUTE.routeEndOfficeID = ROUTEOFFEND.officeID RIGHT OUTER JOIN
dbo.tbloperator AS OPER RIGHT OUTER JOIN
dbo.tblsegment AS SEG INNER JOIN
dbo.tbltrips ON SEG.TripID = dbo.tbltrips.tripID LEFT OUTER JOIN
dbo.tblSpecial AS SPECIAL ON SEG.specialID = SPECIAL.specialID LEFT OUTER JOIN
dbo.tbllocation AS LOCEND ON SEG.segEndLocID = LOCEND.locID LEFT OUTER JOIN
dbo.tbllocation AS LOCSTART ON SEG.segStartLocID = LOCSTART.locID LEFT OUTER JOIN
dbo.tblsegitem AS ITEM ON SEG.segItemID = ITEM.segItemID ON OPER.operatorID = SEG.operatorID ON ROUTE.routeID = SEG.routeID ON
SEGTYPE.segTypeID = SEG.segTypeID LEFT OUTER JOIN
dbo.OfficeList AS OFFSTART ON SEG.segStartOfficeID = OFFSTART.officeID LEFT OUTER JOIN
dbo.OfficeList AS OFFEND ON SEG.segEndOfficeID = OFFEND.officeID LEFT OUTER JOIN
dbo.tblattractitem AS ATTRACT ON SEG.attractID = ATTRACT.attractID LEFT OUTER JOIN
dbo.tblGalileoAirline AS AIR ON AIR.code = { fn LEFT(SEG.segFld1, 2) }
WHERE (SEG.segID <> 0) AND (ITEM.segItemNonActive = 0)
ORDER BY SEG.segStartDate, SEG.segEndDate, IsFlight, SEG.segStartTime, SEG.segEndTime