Views and formatting SQL
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.
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.
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).
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:
a as a1,
b as b1,
c as c1,
from ... < - here everything is ok
select a as a1, b as b1, c as c1
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;
After_AS_KeyWord, After_Identifier: Boolean;
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;
// but i think you not you code better :)
I described it like a special case, but i think you can integrate more seamlessly it in you flow
Basically I'm looking for a nice reformatting based on how I'm currently using Aqua Data Studio (http://www.aquafold.com/):
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...
Thank you for this information!
I would like to add that there is one more great tool that can speed up SQL code writing, offer context-based smart suggestions, perform automatic formatting and refactoring, improve code readability, increase productivity, lower costs. It's SQL COmplete by Devart, I think this tools is worth to be mentioned
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.
Please login to leave a reply, or register at first.