River vertical space concept as seen on www.sqlstyle.guide

 
Formatting Ideas, Issues and Questions
Last Post by Guido 3 days ago
8 Posts
2 Users
0 Reactions
359 Views
 sqlslinger2
(@sqlslinger2)
Member
Joined: 4 months ago
Posts: 3
Topic starter 31/07/2024 8:53 pm  

We are attempting to enforce SQL standards mostly based on www.sqlstyle.guide.  In this section the white space "river" concept is described, where the keywords are right-aligned and a consistent vertical space is provided before each expression to the right of the keyword. 

We would love to see the "Right justify" checkbox have this affect on the SQL.

For example, code such as the following almost has a consistent river.  If the "AND" clause was right-justified, the entire vertical river would have been intact up until the GROUP BY. 

Contrary to the sqlstyle guide, my suggestion for certain keywords is that they *can* appear on the other side of the river.  For example, SELECT DISTINCT (DISTINCT appears after the river), LEFT JOIN (JOIN appears after the river), GROUP BY and ORDER BY (BY appears after the river).  The point is that keywords on the left of the river are consistently right-justified so that they end before the vertical river space.

       SELECT b.database_name AS DatabaseName,
              d.recovery_model_desc AS [RecoveryModel],
              b.type,
              MAX(b.backup_set_id) AS max_backup_set_id,
              CASE
                WHEN LEFT(f.physical_device_name, 1) = '{'
                THEN 'N'
                ELSE 'Y'
              END AS SQLNative,
              b.is_copy_only,
              d.is_read_only,
              CONVERT(DATE, d.create_date) AS create_date, 
              REPLACE(CONVERT(VARCHAR, DATABASEPROPERTYEX((d.[Name]), 'LastGoodCheckDbTime'), 20), '-', '') LastCleanDBCC
         FROM msdb.dbo.backupset b
         JOIN msdb.dbo.backupmediafamily f
           ON b.media_set_id = f.media_set_id
           AND b.type IN ('D', 'I', 'L')
         JOIN sys.databases AS d
           ON d.[name] = b.database_name
           AND d.name <> N'tempdb'
           AND d.state_desc = 'ONLINE'
        WHERE 1 = 1
         AND 2 = 2
       GROUP BY b.database_name,
              d.recovery_model_desc,
              b.type,
              CASE
                WHEN LEFT(f.physical_device_name, 1) = '{'
                THEN 'N'
                ELSE 'Y'
              END,
              b.is_copy_only,
              d.is_read_only,
              CONVERT(DATE, d.create_date), 
              REPLACE(CONVERT(VARCHAR, DATABASEPROPERTYEX((d.[Name]), 'LastGoodCheckDbTime'), 20), '-', '')

Do you feel this would be possible to implement?

 


   
Quote
Guido
 Guido
(@admin)
Member Admin
Joined: 3 years ago
Posts: 216
31/07/2024 9:40 pm  

Hi @sqlslinger2,

many thanks for your great feedback. Probably I need a little bit of time to fully understand the "river" concept. I will leave for 3 weeks of holiday and will read your referenced site while being at the sea.

Would the formatting style in below screenshot come close to your river concept by putting all keywords including AND and ORDER BY right-justified in one line ?

grafik

Regards

Guido


   
ReplyQuote
 sqlslinger2
(@sqlslinger2)
Member
Joined: 4 months ago
Posts: 3
Topic starter 31/07/2024 10:35 pm  

@admin Thanks for the super-quick reply!  Certainly enjoy your time at sea, I am just back from a 10-day cruise that I enjoyed very much.

I upgraded to the latest version, 6.24.7.23, and found that the "AND" is now correctly right-justified with these options:

image

But if I use a LEFT JOIN, I need to change the options (Indent JOIN and Indent ON) to get it to appear correctly, and then the "AND" no longer right-justified as it considers it to be indented to same level as the "ON":

image

 

I still find that the GROUP BY breaks the river.

I guess I am wondering if the right-justify option can override the indent option.  Or perhaps a new SQL Indentation option is needed along with Small Keyword Size and Large Keyword Size?


   
ReplyQuote
Guido
 Guido
(@admin)
Member Admin
Joined: 3 years ago
Posts: 216
31/07/2024 11:09 pm  

Would you mind sending me your exported profile at Info @ sqlinform.com


   
ReplyQuote
 sqlslinger2
(@sqlslinger2)
Member
Joined: 4 months ago
Posts: 3
Topic starter 05/08/2024 1:11 pm  

@admin Here are the settings.  I could not attach them as a txt file.

 

{
"RELEASE_GUI" : "SQLinForm 6.22.12.13",
"OUTPUT_LANGUAGE" : "SQL",
"GEN_NBR_SPACES_INDENT" : "2",
"CASE_KEYWORDS" : "UPPER",
"GEN_NBR_SP_TAB" : "4",
"GEN_SQL_AUTO_IND" : "SMALL",
"EDIT_DEL_SPACES" : "True",
"SRCE_QUOTECHAR" : "'",
"IND_DOUBLEMASTERKEYWORD" : "False",
"GEN_ALWAYS_EXTRACT" : "False",
"GEN_ALWAYS_GENERATE" : "False",
"LB_DOUBLEUNION" : "False",
"LBB_COMMA" : "False",
"LBA_COMMA" : "True",
"LBB_AND" : "True",
"LBA_AND" : "False",
"LBA_SELECTBRACKET" : "True",
"LBA_CONDITIONBRACKET" : "True",
"GEN_DECIMALPOINT_IS_COMMA" : "True",
"EDIT_GENERATE_CODE" : "False",
"SRCE_IS_ENCLOSED" : "False",
"SRCE_IS_ENCLOSED_CHAR" : "''",
"LB_PAGEWIDTH" : "132",
"COLOR_SQL" : "True",
"IND_IF_THEN" : "False",
"IND_IF_ELSE" : "False",
"LBB_CASE" : "False",
"LB_WIDTH_SMALL_SQL" : "10",
"IND_AFT_SELECTBR" : "True",
"EDIT_USE_TABS_INDENT" : "False",
"IND_BEGIN_END" : "True",
"ALI_EQUAL" : "False",
"ALI_OPERATOR" : "False",
"SRCE_DB" : "SQL Server",
"ALI_RIGHTKEYWORD" : "True",
"EDIT_DEL_COMMENT" : "False",
"WHSP_BRACKET" : "NONE",
"LBA_IF" : "False",
"LBB_IF_THEN" : "True",
"LBA_IF_THEN" : "True",
"WHSP_COMMA" : "ONEAFTER",
"LBA_IF_ELSE" : "True",
"LBA_CASE" : "False",
"LBA_CASE_WHEN" : "False",
"LBB_CASE_END" : "True",
"WHSP_EQUAL" : "ONEAROUND",
"LBA_CASE_THEN" : "False",
"LBA_CASE_ELSE" : "False",
"IND_CASE_WHEN" : "True",
"ALI_AS" : "False",
"ALI_COMMENT" : "True",
"ALI_DECLARATION" : "True",
"LBB_CASE_THEN" : "True",
"LBB_CASE_ELSE" : "True",
"MY_VARIABLENAME" : "SQL",
"LBB_CASE_WHEN" : "True",
"LBB_CASE_ANDOR" : "True",
"EDIT_REPL_COMMENT" : "False",
"LBB_CONCAT" : "True",
"LBA_CONCAT" : "False",
"ALI_CONCAT" : "False",
"LBA_CASE_ANDOR" : "False",
"LBB_JOIN_ON" : "True",
"LBB_JOIN" : "True",
"SP_LB_NBR_COMMASPERLINE" : "1",
"LB_NBR_COMMASPERLINE" : "1",
"ALI_COMMA" : "False",
"LB_WIDTH_SMALL_CASE" : "10",
"IND_JOIN" : "True",
"IND_AFT_CONDITIONBR" : "True",
"IND_INITIALVALUE" : "0",
"SRCE_USE_ESCAPECHARACTER" : "True",
"EDIT_DELETE_EMPTYLINES" : "NONE",
"ALI_ASPOSITION" : "False",
"ALI_ASPOSITIONVALUE" : "60",
"LBB_LINECOMMENT" : "False",
"LBB_BLOCKCOMMENT" : "True",
"SP_LBB_COMMA" : "False",
"SP_LBA_COMMA" : "True",
"LB_NBR_COMMASINLIST" : "6",
"EDIT_REPL_HOSTVARIABLE" : "False",
"EDIT_REMOVEBRACKETS" : "False",
"LB_ONELINESQL" : "False",
"IND_AUTO_1STLINE" : "True",
"IND_CASE_THEN" : "False",
"IND_CASE_ELSE" : "False",
"LBB_SELECTBRACKET" : "True",
"LBB_CONDITIONBRACKET" : "False",
"LBB_CLOSESELECTBRACKET" : "True",
"LBA_CLOSESELECTBRACKET" : "False",
"LBB_CLOSECONDITIONBRACKET" : "False",
"LBA_CLOSECONDITIONBRACKET" : "False",
"SP_LBB_OPENCONDITIONBR" : "True",
"SP_LBA_OPENCONDITIONBR" : "True",
"SP_IND_CONDITIONBR" : "True",
"SP_LBB_CLOSECONDITIONBR" : "True",
"SP_LBA_CLOSECONDITIONBR" : "True",
"SP_LBB_OPENPARMBRACKET" : "False",
"SP_LBA_OPENPARMBRACKET" : "False",
"SP_IND_PARMBRACKET" : "True",
"SP_LBB_CLOSEPARMBRACKET" : "False",
"SP_LBA_CLOSEPARMBRACKET" : "False",
"NOFORMATBETWEEN" : "True",
"NOFORMATSTART" : "FORMAT_OFF",
"NOFORMATEND" : "FORMAT_ON",
"LBB_OPENINSERTBRACKET" : "True",
"LBA_OPENINSERTBRACKET" : "True",
"LBB_CLOSEINSERTBRACKET" : "True",
"LBA_CLOSEINSERTBRACKET" : "True",
"IND_INSERTBRACKET" : "True",
"CASE_KEYWORDLIST" : "",
"VARIABLETYPES" : "",
"ALI_LEFTPARACOMMENT" : "True",
"IND_PARACOMMENT" : "True",
"LBB_OPENCASEBRACKET" : "False",
"LBA_OPENCASEBRACKET" : "True",
"LBB_CLOSECASEBRACKET" : "False",
"LBA_CLOSECASEBRACKET" : "False",
"IND_CASEBRACKET" : "False",
"ALI_MOVE_COMMALEFT" : "False",
"WHSP_1AFTERCOMMA" : "False",
"COLOR_MASTERKEYWORD_RGB" : "#0000FF",
"COLOR_LINECOMMENT_RGB" : "#008000",
"COLOR_BLOCKCOMMENT_RGB" : "#008000",
"COLOR_CASE_RGB" : "#00008B",
"COLOR_LITERAL_RGB" : "#FF0000",
"COLOR_RESERVEDWORD_RGB" : "#00008B",
"COLOR_STRUCTUREKEYWORD_RGB" : "#008000",
"IND_JOINON" : "True",
"COMMENTHOSTVARIABLE" : "False",
"COLOR_WHITESPACES" : "True",
"COLOR_IF_RGB" : "#FF0000",
"LB_STACKLISTGROUPBY" : "True",
"LB_STACKLISTORDERBY" : "True",
"ALI_ASSOCIATIONOPERATOR" : "True",
"SP_LBB_HEADER" : "True",
"SP_ALI_EQUAL" : "True",
"SP_ALI_ASSIGNMENT" : "False",
"EDIT_REPL_SINGLEQUOTE" : "False",
"LBA_WHERE" : "False",
"LBA_FROM" : "False",
"LBA_ORDERBY" : "False",
"LBA_GROUPBY" : "False",
"LBA_JOIN" : "False",
"LBA_JOIN_ON" : "False",
"LBA_SELECT" : "False",
"LB_NBR_COMMASINSERTPERLINE" : "1",
"LBA_UPDATE" : "True",
"LBA_DELETE" : "True",
"LBB_SEMIKOLON" : "False",
"LBA_HAVING" : "False",
"EDIT_REPL_PARACOMMENT" : "False",
"LBA_SEMIKOLON_DOUBLE" : "False",
"CASE_STATEMENT" : "NONE",
"CASE_NONKEYWORDS" : "NONE",
"LBA_INTO" : "False",
"DELIMITER1" : ";",
"FIRSTLINEPREFIX" : "StringBuffer sql = new StringBuffer();",
"FIRSTLINESUFFIX" : "",
"BODYLINEPREFIX" : "sql.append(''",
"BODYLINESUFFIX" : "'');",
"LASTLINEPREFIX" : "",
"LASTLINESUFFIX" : "'');",
"LBB_LASTLINESUFFIX" : "False",
"DELIMITER2" : "",
"DELIMITER3" : "",
"NOFORMATWHOLESQL" : "False",
"COLOR_STANDARD_RGB" : "#000000",
"LBB_WHERE_AND" : "True",
"LBA_WHERE_AND" : "False",
"IND_WHERE_AND" : "False",
"LBB_HAVING_AND" : "True",
"LBA_HAVING_AND" : "False",
"IND_HAVING_AND" : "False",
"LBB_JOINON_AND" : "True",
"LBA_JOINON_AND" : "False",
"IND_JOINON_AND" : "False",
"ALI_MOVE_COMMALEFTBORDER" : "False",
"LBB_FROM" : "True",
"LBB_WHERE" : "True",
"LBB_HAVING" : "True",
"LBB_INTO" : "True",
"LBB_GROUPBY" : "True",
"LBB_ORDERBY" : "True",
"LBB_OPENCREATEBRACKET" : "True",
"LBA_OPENCREATEBRACKET" : "True",
"LBB_CLOSECREATEBRACKET" : "True",
"LBA_CLOSECREATEBRACKET" : "True",
"IND_CREATEBRACKETINSIDE" : "True",
"IND_CREATEBRACKETOUTSIDE" : "True",
"MY_FUNCTIONKEYWORDLIST" : "in",
"MY_CREATEKEYWORDLIST" : "",
"LB_NBR_COMMASINFUNCTIONLIST_L1" : "6",
"LB_NBR_COMMASINFUNCTIONLIST" : "6",
"ALI_COMMA_FUNCTIONLIST" : "False",
"MY_FUNCTIONKEYWORDLIST2" : "",
"LB_NBR_COMMASINFUNCTIONLIST2_L1" : "1",
"LB_NBR_COMMASINFUNCTIONLIST2" : "1",
"ALI_COMMA_FUNCTIONLIST2" : "False",
"MY_FUNCTIONKEYWORDLIST3" : "",
"LB_NBR_COMMASINFUNCTIONLIST3_L1" : "1",
"LB_NBR_COMMASINFUNCTIONLIST3" : "1",
"ALI_COMMA_FUNCTIONLIST3" : "False",
"LBB_COMMAINSERT" : "False",
"LBA_COMMAINSERT" : "True",
"LBB_COMMAUPDATE" : "False",
"LBA_COMMAUPDATE" : "True",
"LBB_SET_UPDATE" : "True",
"LBA_SET_UPDATE" : "True"
}


   
ReplyQuote
Guido
 Guido
(@admin)
Member Admin
Joined: 3 years ago
Posts: 216
10/08/2024 9:06 am  

Thanks


   
ReplyQuote
Guido
 Guido
(@admin)
Member Admin
Joined: 3 years ago
Posts: 216
02/10/2024 8:42 pm  

could you please increase the number of indent spaces from "2" to "6" or "8" and give it a try


   
ReplyQuote
Guido
 Guido
(@admin)
Member Admin
Joined: 3 years ago
Posts: 216
17/11/2024 10:44 pm  

Hi, finally I go some time to study the white space "river" concept. I will create a beta version. Would you be able to do some beta testing?

Regards

Guido


   
ReplyQuote
Forum Jump:
  Previous Topic
Next Topic  
Share: