[Solved] River vertical space concept as seen on www.sqlstyle.guide

 
Page 1 / 2
Ideas, Issues and Questions
Last Post by guidomarcel 2 years ago
9 Posts
3 Users
0 Reactions
1,429 Views
Posts: 3
 sqlslinger2
Topic starter
Jul 31, 2024 9:53 pm
(@sqlslinger2)
Member
Joined: 2 years ago
[#123]

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?

 


8 Replies
Guido
Posts: 295
 Guido
Admin
Jul 31, 2024 10:40 pm
(@admin)
Member
Joined: 5 years ago

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


Reply
1 Reply
 sqlslinger2
(@sqlslinger2)
Joined: 2 years ago

Member
Posts: 3
Jul 31, 2024 11:35 pm
Reply toGuidoGuido

@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?


Reply
Guido
Posts: 295
 Guido
Admin
Aug 01, 2024 12:09 am
(@admin)
Member
Joined: 5 years ago

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


Reply
1 Reply
 sqlslinger2
(@sqlslinger2)
Joined: 2 years ago

Member
Posts: 3
Aug 05, 2024 2:11 pm
Reply toGuidoGuido

@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"
}


Reply
Guido
Posts: 295
 Guido
Admin
Aug 10, 2024 10:06 am
(@admin)
Member
Joined: 5 years ago

Thanks


Reply
Guido
Posts: 295
 Guido
Admin
Oct 02, 2024 9:42 pm
(@admin)
Member
Joined: 5 years ago

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


Reply
Page 1 / 2
Forum Jump:
  Previous Topic
Next Topic  
Share: