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.
Tidy SQL Query
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
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
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
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:
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
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
Please login to leave a reply, or register at first.