Views and formatting SQL

Hello,

When displaying a View, the whole view shows on one very long line (often thousans of columns wide), making it quite hard to do anything with. If I do Ctrl-F8 to format SQL, it does format parts of it, but it does not line-wrap (I tried to click the line-wrap icon, but that does nothing).

Could a setting be added to auto-format the SQL in views (including wrapping lines to fit on the screen)? This way, you just click on the view and you can instantly see what it does and edit it as needed.

Thanks,
Lars

r3543 makes this "Wrap lines" button work globally, for all SQL editors. Also its state is stored across sessions, so you just need to set it once.

Regarding your question to automatically apply a reformat to the VIEW code, I disagree - that's not what every user might want, as this touches the original sources. However, users just have to press Ctrl+F8 to do that manually.

Hello,

Re. r3543: thanks, I'll check it out. Also, I noticed the warning about the nightly builds and bugs, which makes sense of course. But roughly how often do you release new "stable versions?"

I realize that not all users wants formatting, which is why I am suggesting that it be an option - this way you can have it either way. Also, if you implement it, it could maybe be done so that it does not actually consider the view changed by the auto-reformatting (not until the user changes something would it consider the view as changed). Either way, an option for auto-reformatting of views would be very nice (an example of a tool that has this is SQLYog).

Lars

You noticed that there *is* such a reformatting tool via Ctrl+F8? I'm not sure as you mention SQLyog suddenly. Ctrl+F8 just has to be pressed and you're done. There is just no automatism doing the same.

Yes, I know you said it is in the r3543 there is (I have not downloaded that yet - I am not sure how stable the nightly builds are). I agree that with a working Ctrl+F8 for views, it will be easy to reformat when needed. What I was asking for is an option for auto-formatting, which would make it even more user friendly. Just a suggestion for making a great product even better.

Auto-touching the VIEW code is not something HeidiSQL should do ever, not even with an option. We'll stick to need a keypress for that.

Hello

Heidi's formatting feature was very handy, espesially for views, that are always in one line. But when i encountered pretty big views (not sure what "big" means for you but for me it's about 4 -5 tables with 10 - 15 columns), the uncomfortable thing is that all columns are still in one line(veeery long). Since vertical scrollling is more natural, could there be any plans to do something with this, maybe make an option to place all columns on a new line, like:

select
a as a1,
b as b1,
c as c1,
....
from ... < - here everything is ok

instead of

select a as a1, b as b1, c as c1
from ...

Here's the code which does this reformat stuff. Probably we can enhance it here in this forum thread:

function ReformatSQL(SQL: String): String;
var
AllKeywords, ImportantKeywords, PairKeywords: TStringList;
i, Run, KeywordMaxLen: Integer;
IsEsc, IsQuote, InComment, InBigComment, InString, InKeyword, InIdent, LastWasComment: Boolean;
c, p: Char;
Keyword, PreviousKeyword, TestPair: String;
Datatypes: TDBDataTypeArray;
const
WordChars = ['a'..'z', 'A'..'Z', '0'..'9', '_', '.'];
WhiteSpaces = [#9, #10, #13, #32];
begin
// Known SQL keywords, get converted to UPPERCASE
AllKeywords := TStringList.Create;
AllKeywords.Text := MySQLKeywords.Text;
for i:=Low(MySQLFunctions) to High(MySQLFunctions) do begin
// Leave out operator functions like ">>", and the "X()" function so hex values don't get touched
if (MySQLFunctions[i].Declaration <> '') and (MySQLFunctions[i].Name <> 'X') then
AllKeywords.Add(MySQLFunctions[i].Name);
end;
Datatypes := Mainform.ActiveConnection.Datatypes;
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');
// Keywords which followers should not get separated into a new line
PairKeywords := Explode(',', 'LEFT,RIGHT,STRAIGHT,NATURAL,INNER,ORDER,GROUP');
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 c = '`' 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 CharInSet(c, WordChars);
// 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 CharInSet(Result[Run-1], WhiteSpaces) 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 (PairKeywords.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;

thanks for callaborating, it'll take some time for me to think, i hope i will post soon

In somewhat strict terms my idea will look like this:

var
//...
After_AS_KeyWord, After_Identifier: Boolean;
begin
After_AS_KeyWord := False;
After_Identifier : False;
//...
// while walking arount SQL string,
// if we encountered a ',' after AS keyword and after some identifier
// we would like to place a new line after comma, like in here
//
// SELECT -- don't mind about new line here, but others may;
//`epoch`.`comment` AS `year`, -- new line here;
//`parameter`.`value` AS `value` -- this case is already handled by FROM keyword;
// FROM `epoch` JOIN `parameter`
for i:=1 to Length(SQL) do begin
//...
if (not InComment and not InBigComment and After_AS_KeyWord and After_Identifier and c = ',') then
// something like
Result[Run] := c;
Result[Run+1]:= '\n';
// but i think you not you code better :)
//...
end;
end;

I described it like a special case, but i think you can integrate more seamlessly it in you flow

sorry for not wrapping that in {code}

That's perfect to discuss code a bit more abstract than it is actually required by some dumb compiler. Will check your proposal and see if I find the time to dive into that complicated stuff again.

I was wondering if the reformat SQL rules can be customizable through the UI, and indentation support be added?

Basically I'm looking for a nice reformatting based on how I'm currently using Aqua Data Studio (http://www.aquafold.com/):
http://imageshack.us/photo/my-images/638/beautify.jpg/#

Source query:

SELECT `column` FROM `table` WHERE `column`=0 AND `column`>( SELECT `column` FROM `table` WHERE `column`=( SELECT `column` FROM `table WHERE `COLUMN` BETWEEN 0 AND 10 ORDER BY `COLUMN` LIMIT 1 ) AND ` COLUMN`!=(SELECT `COLUMN` FROM `TABLE WHERE `column` BETWEEN 0 AND 10 ORDER BY `column` LIMIT 1 ) ORDER BY `column` LIMIT 1)

Reformatting using HeidiSQL:

SELECT `column`
FROM `table`
WHERE `column`=0 AND `column`>(
SELECT `column`
FROM `table`
WHERE `column`=(
SELECT `column`
FROM `table WHERE ` COLUMN` BETWEEN 0 AND 10 ORDER BY ` COLUMN` LIMIT 1 ) AND ` COLUMN`!=(SELECT ` COLUMN` FROM ` TABLE
WHERE `column` BETWEEN 0 AND 10
ORDER BY `column`
LIMIT 1)
ORDER BY `column`
LIMIT 1)

Reformatting using AquaData Studio, with the above (screenshot) settings:

SELECT
`column` 
FROM
`table` 
WHERE
`column`=0 AND
`column`>(	SELECT
`column` 
FROM
`table` 
WHERE
`column`=(	SELECT
`column` 
FROM
`table WHERE `COLUMN` BETWEEN 0 AND 10 ORDER BY `COLUMN` LIMIT 1 ) AND ` COLUMN`!=(SELECT `COLUMN` FROM `TABLE 
WHERE
`column` BETWEEN 0 AND
10 
ORDER BY
`column` LIMIT 1 
)
ORDER BY
`column` LIMIT 1
)

IMO the latter is far more agreeable on the eyes...

r3941 tries to fetch the very original source SQL the user entered before, and displays this in the VIEW editor. This is done by a quite hackish approach, loading the server's .frm file and extracting the right portion in it.

Create a stored procedure to re create the view. That keeps your formatting and allows you to add comments to help document the view. I name mine as sp_create_viewname

Create a stored procedure to re create the view. That keeps your formatting and allows you to add comments to help document the view. I name mine as sp_create_viewname. If you want to gain more skills then you vcan join MS SQL Training Course.