Mobile version of this page
Previous:
Integer division in programming languages
Next:
Psychiatric med student Michelle's story
Zope in DevelopmentMode
Save this page in del.icio.usInteger division in programming languages
Next:
Psychiatric med student Michelle's story
Related blogs
Python inspect moduleZope in DevelopmentMode
Related by category
Pretty print SQL script
debug, indentation, printsql, sql, pretty print
6th of August 2004
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;
*
FROM
foo
ORDER BY bar;
Try with your own SQL statements
Comment
diego (semco) -
6th August 2004
[«« Reply to this]
it didn't work when i tried some of my scripts ;)
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
it didn't work when i tried some of my scripts ;)
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
Peter -
7th August 2004
[«« Reply to this]
That's one ugly SQL statement. What would Ricardo say about that?
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?
That's one ugly SQL statement. What would Ricardo say about that?
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?
Cristian -
26th May 2006
[«« Reply to this]
It should look like this!
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.
It should look like this!
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.
Syntax highlighting!
I've put in SilverCity's syntax highlighting (http://silvercity.sourceforge.net/) but it's pretty crap for SQL.