Tidy SQL Query

Hi!

I suggest a very easy to implement improvement.

I will love a SQL command tidy option.

As lot of us I use sometimes big queries. They are a single line query and when I paste it to the Query Tab I get only one line and I need to tidy it a bit if I want to see all the query on the screen.

Example of dirty SQL query:

SELECT *** lot of fields ***** FROM table1 INNER JOIN table2 ON (commands) INNER JOIN table3 ON (commands) INNER JOIN table4 ON (commands) WHERE lot_of_fields ORDER BY lot_of_fields

Example of tidy SQL query:

SELECT *** lot of fields *****
FROM table1
INNER JOIN table2 ON (commands)
INNER JOIN table3 ON (commands)
INNER JOIN table4 ON (commands)
WHERE lot_of_fields
ORDER BY lot_of_fields

HeidiSQL should add some line breaks after the SQL reserved words to get a tidy and easy to read Sql Query.

Thanks for your attention and for this excelent SQL Manager.

Byes.

Hi all,

This would be very useful to me, and perhaps we could (choose to) have the SQL code uppercased for readability i.e.

from...

select field1, field2, field3 from table where field4 = 'X' and field5 = 'Y'

to...

SELECT field1, field2, field3
FROM table
WHERE field4 = 'X'
AND field5 = 'Y'

Cheers
Muzza

See issue 428

Hi anse!

Yes, the issue you writed it's what I'm suggesting.

Thx for answer.

In case you didn't notice: HeidiSQL now has a "Reformat SQL" feature. The above issue was closed as "fixed" recently.

Only one remark:
It would be very nice if this feature were implemented also in the "View" tab.
When you open an existing view, the "select" statement is displayed in one line. that makes difficult to read it and to modify it

The "Reformat SQL" menu item is always accessible in the main "Edit" menu, or, by shortcut Ctrl+F8. Applies to the view editor as well as for routines and triggers.

I didn't notice that.

But I am a pretentious nuisance ....
Look at this reformatted code

SELECT EXTRACT(year_month
FROM `aggregato_neg`.`Data`) AS `Mese`,`aggregato_neg`.`Stream` AS `Stream`, SUM(`aggregato_neg`.`Neg`) AS `importo`
FROM `credito_negativo`.`aggregato_neg` GROUP BY EXTRACT(year_month
FROM `aggregato_neg`.`Data`) DESC,`aggregato_neg`.`Stream`

The bold "FROM"s are part of an EXTRACT function and shouldn't be put at the beginning of the line.

Besides, in my opinion, the "GROUP BY" clause looks better at the beginning of the line.

Sorry for my pedantry

That's not pedantry, very ok for me.

Only these different meanings of FROM need much more intelligence to detect than HeidiSQL has for now. Probably you can look at the routine and/or send a patch? I'll paste the code here:

function ReformatSQL(SQL: String): String;
var
AllKeywords, ImportantKeywords: TStringList;
i, Run, KeywordMaxLen: Integer;
IsEsc, IsQuote, InComment, InBigComment, InString, InKeyword, InIdent, LastWasComment: Boolean;
c, p: Char;
Keyword, PreviousKeyword, TestPair: String;
begin
// Known SQL keywords, get converted to UPPERCASE
AllKeywords := TStringList.Create;
AllKeywords.Text := MySQLKeywords.Text;
for i:=Low(MySQLFunctions) to High(MySQLFunctions) do begin
if MySQLFunctions[i].Declaration <> '' then
AllKeywords.Add(MySQLFunctions[i].Name);
end;
for i:=Low(Datatypes) to High(Datatypes) do
AllKeywords.Add(Datatypes[i].Name);
KeywordMaxLen := 0;
for i:=0 to AllKeywords.Count-1 do
KeywordMaxLen := Max(KeywordMaxLen, Length(AllKeywords[i]));
// A subset of the above list, each of them will get a linebreak left to it
ImportantKeywords := Explode(',', 'SELECT,FROM,LEFT,RIGHT,STRAIGHT,NATURAL,INNER,JOIN,WHERE,GROUP,ORDER,HAVING,LIMIT,CREATE,DROP,UPDATE,INSERT,REPLACE,TRUNCATE,DELETE');
IsEsc := False;
InComment := False;
InBigComment := False;
LastWasComment := False;
InString := False;
InIdent := False;
Run := 1;
Result := '';
SQL := SQL + ' ';
SetLength(Result, Length(SQL)*2);
Keyword := '';
PreviousKeyword := '';
for i:=1 to Length(SQL) do begin
c := SQL[i]; // Current char
if i > 1 then p := SQL[i-1] else p := #0; // Previous char
// Detection logic - where are we?
if c = '\' then IsEsc := not IsEsc
else IsEsc := False;
IsQuote := (c = '''') or (c = '"');
if SQL[i] = '`' then InIdent := not InIdent;
if (not IsEsc) and IsQuote then InString := not InString;
if (c = '#') or ((c = '-') and (p = '-')) then InComment := True;
if ((c = #10) or (c = #13)) and InComment then begin
LastWasComment := True;
InComment := False;
end;
if (c = '*') and (p = '/') and (not InComment) and (not InString) then InBigComment := True;
if (c = '/') and (p = '*') and (not InComment) and (not InString) then InBigComment := False;
InKeyword := (not InComment) and (not InBigComment) and (not InString) and (not InIdent) and IsLetter(c);
// Creation of returning text
if InKeyword then begin
Keyword := Keyword + c;
end else begin
if Keyword <> '' then begin
if AllKeywords.IndexOf(KeyWord) > -1 then begin
while (Run > 1) and IsWhitespace(Result[Run-1]) do
Dec(Run);
Keyword := UpperCase(Keyword);
if Run > 1 then begin
// SELECT, WHERE, JOIN etc. get a new line, but don't separate LEFT JOIN with linebreaks
if LastWasComment or ((ImportantKeywords.IndexOf(Keyword) > -1) and (ImportantKeywords.IndexOf(PreviousKeyword) = -1)) then
Keyword := CRLF + Keyword
else if (Result[Run-1] <> '(') then
Keyword := ' ' + Keyword;
end;
LastWasComment := False;
end;
PreviousKeyword := Trim(Keyword);
Insert(Keyword, Result, Run);
Inc(Run, Length(Keyword));
Keyword := '';
end;
if (not InComment) and (not InBigComment) and (not InString) and (not InIdent) then begin
TestPair := Result[Run-1] + c;
if (TestPair = '  ') or (TestPair = '( ') then begin
c := Result[Run-1];
Dec(Run);
end;
if (TestPair = ' )') or (TestPair = ' ,') then
Dec(Run);
end;
Result[Run] := c;
Inc(Run);
end;
end;
// Cut overlength
SetLength(Result, Run-2);
end;

Sorry, I don't know Pascal,
neither would I be able to solve this problem from the logical point of view in pseudo-code.

I knew that was a quibble :-)
But it's not my fault if there exists such a function syntax (extract .. from ..)

I do appreciate your software, it's great.
I did not want to be controversial.
Thanks and regards

But the "GROUP BY" clause could be put at the beginning of the line...