taok / poormanstsqlformatter Goto Github PK

DDL triggers like "FOR LOGON" are not explicitly handled, so they break to new lines incorrectly

Came across the concept of ODBC escape sequences (eg SELECT {d'9999-12-31'}) in a stackoverflow question:

http://stackoverflow.com/questions/7428538/curly-braces-in-t-sql

Confirmed that they seem to be supported in numerous (if not all?) SQL Server connectivity scenarios, and should therefore probably be parsed/tokenized appropriately.

http://msdn.microsoft.com/en-us/library/ms712360%28v=VS.85%29.aspx
http://msdn.microsoft.com/en-us/library/aa174492(v=sql.80).aspx

Currently, the formatter identifies these constructs as a word, a string constant and another word; the resulting formatted text therefore has spaces before and after the string constant, but this still appears to be parsed/processed correctly by the SQL engine: SELECT {d '9999-12-31' }

As the current behaviour does not appear to introduce any serious visual aberrations or SQL parsing failures, this issue is probably very low priority.

That would be niiice... But there's probably still too many internal changes going on.

Taking a cue from the MarkdownDeep project, set up test project to support optional features on the formatter like HTMLEncoding, "ON" section breaking, etc so that these things can be properly/automatically tested.

Also switch to NUnit instead of VS2008 tests project, to lower the bar to entry / reduce dependency on proprietary tools (this is an AGPL project, after all...)

Can't install 1.3.1 version on Server 2008 R2 SP1 and SQL Server 2008 R2

Error 1001. Unable to get installer types in the c:\Program Files(x86)\PoorMansFormatterSSMSAddIn\PoorManTSqlFormatterSSMSAddIn.dll assembly. -> Unable tp load one or more o fthe requested types. retrieve the LoaderExceptions property for more information.

Screenshot - http://clip2net.com/s/1Hjxp

system has: .NET v2.0.50727 / v3.0 / v3.5 / v4.0.30319

For use by brave souls who consider that a little content mangling isn't too high a price to pay for generally-working formatting :)

Submitted by Francois Chartier ( f_chartier on sourceforge):

I just have a remark about the config box: maybe it's because my computer
is in French and descriptions are longer than in english, but the field
names are longer than the spacing before textboxes, and stick out over
them.Besides, changing the language in notepad++ does not affect the labels
in this config box.

Hi,

i tested your online formatter at http://poorsql.com/ with the following sql example and encountered the following problems:

1. with trailing commas you will get a line that will make the sql invalid, because the comma will be put after
   an inline comment. in this case the comma needs to be put on the next line:

   library.column3 -- inline comment ,

2. if there is a comment block, the next expression is in the line where the comment block ends.
   it would be nice, if the next expression would start on the next line. the indention of the comment block
   could also be optimized, so the indention is more aligned and looks better.

   /*******************

   • This is a block *
     • comment within a *
   • SQL statement *
     *******************/ max(price.col7) AS col7

Sample SQL Snipped showing the problem with trailing blanks.

------------------------------------------
-- This is an example SQL
-- Please click the button <Format>
-- or "ctrl+F"
------------------------------------------


 SELECT  price.col1 AS col1, price.col2 AS col2 , price.col3 AS col3, max(price.col4) AS col4, max(price.col5) AS col5, max(price.col6) AS col6, max(price.col7) AS col7 FROM    table_1 t1, table_2 t2 WHERE   col1 = col2 AND column_1 = small_column AND column_3411 <= column_12_sup and col1 = 'Test Run' AND column_4532 = c1.dert UNION 
SELECT  price.col1 AS col1, price.col2 AS col2 , price.col3 AS col3, max(price.col4) AS col4, max(price.col5) AS col5, max(price.col6) AS col6, 
        /*******************    
    * This is a block  *        
        * comment within a *     
 * SQL statement    *   
        *******************/ 
     max(price.col7) AS col7 
FROM    
        (SELECT store.column1, cast (store.column2 AS integer) AS column2, -- inline comment     
                store.columnwe34r3 AS column3, -- inline comment     
                store.column4_prod AS column4, -- inline comment     
    store.column5_pre_prod_first AS column5 , -- inline comment     
        substr(store.column6,11,1) AS column6, -- inline comment     
 store.column7 AS column7 -- inline comment     
   FROM    
        (SELECT library.column1, 
                        ---------------------    
                        -- This is a line  --     
                        -- comment in a    --     
                        -- SQL statement   --    
                        ---------------------     
                        library.column2, library.column3 -- inline comment     
   , CASE library.column4 WHEN cheap THEN digits(library.column27) concat library.column28 ELSE 123456 END AS column4, CASE library.column5 WHEN expensive THEN digits(library.column27) concat library.column28 ELSE 123456 END AS library.column6, CASE column7 WHEN free THEN digits(library.column27) concat library.column28 ELSE 123456 END AS column7, 
 FROM    
       (SELECT integer(substr(onelibrarysales.column1,11,10)) AS column1, substr(onelibrarysales.column2,21,10) AS column2 , onelibrarysales.column3, onelibrarysales.column4, substr(onelibrarysales.column5,31,6) AS column5, substr(onelibrarysales.column6,37,2) AS column6, substr(onelibrarysales.column7,39,6) AS column7, 
    FROM    
               (SELECT alllibrarysales.column1, alllibrarysales.column2, max(alllibrarysales.column3) AS alllibrarysales.column3 , max(char(alllibrarysales.column4,iso) concat char(alllibrarysales.column5,iso) concat digits(alllibrarysales.column6) concat (alllibrarysales.column7)) AS column5 
     FROM 
                                  /*******************     
                                   * This is a block  *    
                                        * comment within a *       
                                        * SQL statement    *  
                                        *******************/ 
                                        (SELECT libraryprod.column1, libraryprod.column2, libraryprod.column3, libraryprod.column4, 
                            /*******************   
                                                * This is a block  *      
                     * comment within a *     
                                                * SQL statement    *     
  *******************/ 
           libraryprod.column5, libraryprod.column6, libraryprod.column7 
   FROM    
      (SELECT tv.column1, tv.column2, max(digits(tv.column3) concat digits(tv.column4) ) AS librarymax 
                                       FROM    db1.v_table1 tv 
WHERE   tv.column1 <> 'Y' AND tv.column1 in ( 'a' , '1' , '12' , '123' , ' 1234' , '12345' , '123456' , '1234567' , '12345678' , '123456789' , '1234567890' , '1 12 123 1234 12345 123456 1234567 12345678' , 'b' , 'c' ) AND tv.column2 >= date(tv.column4) AND tv.column3 < date(tv.column15) 
                                                GROUP BY tv.column1, tv.column2 
       ) AS libraryprod, db1.table2 th 
                                        WHERE   th.column1 = libraryprod.column1 AND th.column2 = libraryprod.column2 
            ) AS alllibrarysales 
                                GROUP BY alllibrarysales.column1, alllibrarysales.column2 
                                ) AS onelibrarysales 
                        ) AS library 
                LEFT OUTER JOIN db1.v_table3 librarystat 
                        ON librarystat.column1 = library.column1 AND librarystat.column2 = library.column2 OR ( librarystat.column4 = library.column4 AND librarystat.column5 = library.column5 ) 
                        /*******************        
                        * This is a block  *       
                        * comment within a *        
                        * SQL statement    *     
                        *******************/ 
            AND ( librarystat.column5 = 'I' OR librarystat.column4 = 'Gold' OR librarystat.column5 = 'Bold' ) AND librarystat.column6 <= 'Z74' 
                ) AS x 
        ) AS price 
WHERE   price.column1 < 'R45' OR ( price.column2= 'R46' 
        /*******************   
        * This is a block  *  
        * comment within a *   
        * SQL statement    *        
        *******************/ 
        AND price.column3 = 6 ) 
GROUP BY price.column1, price.column2, 
        /*******************   
        * This is a block  *  
        * comment within a *   
        * SQL statement    *        
        *******************/ 
        price.column3, price.column4, price.column5, price.column6, price.column7 

Here's an example:

RESTORE DATABASE dbname
FROM DISK = 'path\to\data\logicalfilename' WITH move 'databasename_Data' TO 'path\to\destination\filename.mdf'
            ,move 'databasename_Log' TO 'path\to\destination\filename_log.ldf'

That's the current formatted version. I think it's more readable to make it like this:

RESTORE DATABASE dbname
FROM DISK = 'path\to\data\logicalfilename' WITH 
            move 'databasename_Data' TO 'path\to\destination\filename.mdf'
            ,move 'databasename_Log' TO 'path\to\destination\filename_log.ldf'

OR

RESTORE DATABASE dbname
FROM DISK = 'path\to\data\logicalfilename' 
WITH move 'databasename_Data' TO 'path\to\destination\filename.mdf'
            ,move 'databasename_Log' TO 'path\to\destination\filename_log.ldf'

OR

RESTORE DATABASE dbname
FROM DISK = 'path\to\data\logicalfilename' 
    WITH move 'databasename_Data' TO 'path\to\destination\filename.mdf'
            ,move 'databasename_Log' TO 'path\to\destination\filename_log.ldf'

Apparently C# pluygin should be reasonably straightforward using an available template:

http://sourceforge.net/projects/sourcecookifier/files/other%20plugins/NppPlugin.NET.v0.5.zip/download

DLLs are not placed into the correct folder, and even after moving them manually things don't work (permissions issue, COM registration or interop issue, or 64-bit-specific issue)

Some (at least one) users are using the Winforms demo app as part of their workflow - should make it a little more usable:

• Persist Settings (default .Net settings provider / simplest possible implementation)
• Make the token list and parse tree xml output optional (by setting), to speed up rendering in regular use

eg: DECLARE @Test1 INT SET @Test1 += 1

User Farzad Jalali suggests:

• Make the number of line breaks between Statements configurable (right now is always 2 linebreaks / one empty line)
• Make the number of line breaks between Clauses configurable (right now is always 1 linebreak)

This could be used to do something like:

SELECT b.b

FROM a

INNER JOIN b
    ON a.a = b.a

WHERE a.a = 100



SELECT *

FROM b

Deserialization plugin (customized) does not handle equals signs in data - incorrectly truncates/discards all content after the first equals in the data.

This plugin should, in any case, be properly forked and modified in github, explaining the intent of the changes, and encapsulated into its own script.

Similarly, other cobbled-together pieces should be properly logged in github.

Currently the following sample gets split into two statements (a blank line is inserted between the two clauses):

INSERT INTO #Test
EXEC Tst2

There is no reparsing problem with this, but it's incorrect formatting.

Because inconsistency sucks.

T-SQL contains many redundant keywords, such as PROC/PROCEDURE, TRANS/TRANSACTION, "INSERT"/"INSERT INTO", "JOIN"/"INNER JOIN", "LEFT OUTER JOIN"/"LEFT JOIN", etc. It would be a nice optional feature to apply one consistent set of keywords. Ideally, that set of keywords would be customizable (but that's very long-term).

I think it would be useful to have separate option to disable formatting for enumerations like
where col in (1,2,3,4)

This would help for queries with many values in enumerations - now every value goes to new line after formatting.

Fun side-project: just how ugly/illegible can you make T-SQL and still have it run successfully?

• SSMS "Denali" Add-in support (no longer uses obscure registry keys but now standard VS add-in system instead)

These generally don't break to a new line - should have special parse rules

In the following example, the comment is incorrectly tacked on to the end of the previous line:

SELECT 1 from (select 2
) mtgcnt
OPTION (RECOMPILE)
--#endregion

Commercial products such as Red Gate's SQL Prompt not only implement the SSMS Add-In, but also a VS(2005/2008/2010) add-in - this shouldn't be too hard to do (?)

A German user had difficulty in getting the hotkey binding to work (all hotkey combinations seemed to fail), and was able to resolve the issue by using "Text-Editor" (apparently the partially-adapted German version of the English phrase "Text Editor") instead of the default.

This might be difficult to fix properly, as I haven't been able to find any acknowledgement of this localization mechanism in my brief searches.

Affected users, if they also have Visual Studio, can determine the appropriate phrase / keyword by running a macro to list keyboard shortcuts, as outlined here: http://redhead-it.com/blog/2007/05/visual_studio_2005_keyboard_mappings/

UPDATE: It seems that a fix (workaround) was proposed in the following forum exchange: http://social.msdn.microsoft.com/Forums/en-US/vsx/thread/b4930827-4647-435a-b46e-95b7cafabd02, and another workaround on someone's blog (http://weblogs.asp.net/rweigelt/archive/2006/07/16/458634.aspx) but I still haven't found a listing of localized scope names that users could refer to.

Hi there,

I installed version 1.3.1 of the ssms addin, when i start ssms i dont see any formatting options. This seems to be related to the locatin of the .addin file. when i move the addin does work

The default location is:
C:\Users\Public\Documents\microsoft\MSEnvShared\AddIns

When i use:
C:\Users\joost\AppData\Roaming\Microsoft\MSEnvShared\Addins
it works

I did a quick check with process monitor, and it seems ssms doesnt even look for C:\Users\Public\Documents\microsoft\MSEnvShared\AddIns, even though it seems to be a standard location for addins. Not sure if this is a problem on my machine only since i dont have any other machines with 2012 installed yet. But i just wanted to let you know

UPDATE (TaoK): This issue seems to be caused by the recent microsoft updates; the first boot of SSMS 2012 after the updates seems to change the Add-in folder search path specification ("HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0_Config\AutomationOptions\LookInFolders"), and always force it to a set of values that don't include the path the current SSMS Add-In setup package uses.

To work around this for the moment, manually move the Addin file from "C:\Users\Public\Documents\microsoft\MSEnvShared\AddIns" to "C:\ProgramData\Microsoft\SQL Server Management Studio\11.0\Addins" (you may have to create this folder if you haven't already installed an add-in that uses it).

For XP, move from "C:\Documents and Settings\All Users\Documents\Microsoft\MSEnvShared\Addins" to "C:\Documents and Settings\All Users\Application Data\Microsoft\SQL Server Management Studio\11.0\Addins".

This issue should be fixed in the installer within a few days.

Error messsage is something like:

The type 'System.Func<T,TResult>' exists in both 'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework.NETFramework\v4.0\mscorlib.dll' and 'SomePath\LinqBridge.dll' SomePath\SomeFile.cs

(where "SomePath\SomeFile.cs" references Linq types like 'System.Func<T,TResult>')

EG:

BEGIN
    WITH Test1
    AS (
        SELECT 1 AS One
        )
        ,Test2
AS (
        SELECT 2 AS Two
        )
    SELECT One
    FROM Test1

    UNION

    SELECT Two
    FROM Test2
END

The second CTE definition "AS" keyword should be indented to the same level as the first one.

For linux users, cygwin users and other pipe-savy folks

The following sample does not parse correctly, reason unknown:

CREATE INDEX [IX_Table1] ON [dbo].[Table1] 
(
                [Col1] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON ) ON [PRIMARY]
GO

See StackOverflow: http://stackoverflow.com/questions/1483732/set-nocount-on-usage/6789054#6789054

A minus sign has two possible contexts, in which it has the same meaning but is usually formatted differently:

• SELECT -1: as a unary operator, stuck to its operand
• SELECT 1 - 1: as a binary operator, separated from both its operands
• SELECT 1 + -1: as a unary operator again

Currently we always add a space between it and ite subsequent (and preceding) token, so as not to need to worry about unary/binary distinctions. This is a visual discrepancy only, and never (afaik?) causes any errors.

The "OVER" keyword is not specifically handled, so ranking functions are sometimes hard to read (all on one line); also, the "ORDER BY" clause within an OVER clause always forces breaking out of the OVER parens - not sure that makes sense.

The Auto-Casing features of the formatter don't currently specify a locale/culture for the casing operations, but what they operate on are always English keywords - therefore, local culture-specific casing may be incorrect.

The reported issue relates to Turkish, where datetime is being incorrectly uppercased to DATETİME.

All keyword auto-casing features should use the Invariant Culture, or maybe US-English (should make no difference).

Request/suggestion from Sijin Joseph:

Semi-compact CASE statement form, breaking on WHEN but not THEN:

case
    when a then b
    when c then d
    end as [col]

Wrapping is all over the place with these, no parsing rules have been applied to this keyword yet

The following "nested join" syntax is parsed incorrectly. The resulting formatted SQL is still 100% correct and equivalent, but the formatting is not correct:

DECLARE @TestTable TABLE (TestColumn1 Int, TestColumn2 Int)
INSERT INTO @TestTable VALUES (1, 1), (1, 2), (2, 2)

SELECT [1].*, [2].*
FROM @TestTable [1]
LEFT JOIN @TestTable [2]
INNER JOIN @TestTable [3] ON [2].TestColumn2 = [3].TestColumn2
      AND 1 < 2
      ON [1].TestColumn1 = [2].TestColumn1

When you add parens the meaning becomes slightly clearer, but the formatting is even more whacky, with the inner join going undetected:

DECLARE @TestTable TABLE (TestColumn1 Int, TestColumn2 Int)
INSERT INTO @TestTable VALUES (1, 1), (1, 2), (2, 2)

SELECT [1].*, [2].*
FROM @TestTable [1]
LEFT JOIN (@TestTable [2]
INNER JOIN @TestTable [3] ON [2].TestColumn2 = [3].TestColumn2
      AND 1 < 2)
      ON [1].TestColumn1 = [2].TestColumn1

Original example from Hugo Kornelis' QOTD, possibly for test suite:

SELECT     e.Name AS SalesRep, c.Name AS Customer, o.OrderDate
FROM       dbo.Employees AS  e
LEFT  JOIN dbo.Customers AS  c
INNER JOIN dbo.Orders    AS  o
      ON   o.CustomerID   =  c.CustomerID
      AND  o.OrderDate    >  DATEADD(month, -4, CURRENT_TIMESTAMP)
      ON   c.SalesRep     =  e.EmployeeID
WHERE      e.Position     = 'SalesRep';

I often have tight blocks of code that are used in multiple scripts and are rarely changed. I would be nice if I could put something like ---- comments wrapping these chunks of code (probably on seperate lines before/after the code) that your formatter interpreter would catch and know not to perform any formatting on the code in between the wrapper keywords.

Formatter doesn't currently check for/expect SELECT, UPDATE etc keywords in GRANT DDL, so these break out into separate statements. No data correctness problems, but ugly/misleading formatting

• SSMS plugin?
• Demo Prog
• Cmdline Utility? What language environment info does it have access to?
• PoorSQL Site (using jquery-localize)

Request from Shihab Hassan:

Sample:

-- Select with trailing Logical operator
Select ColumnOne,
    ColumnTwo,
    ColumnThree
From PRF_TableOne as T1
Where T1.ColumnFour = 5 AND 
    T1.ColumnFive = 27

-- Select with Trailing ON with join
Select ColumnOne,
    ColumnTwo,
    ColumnThree
From PRF_TableOne as T1
Join PRF_TableTwo as T2 ON
    T2.ColumnOne = T1.ColumnTwo AND
    T2.ColumnThree = T1.ColumnThree
Where T1.ColumnFour = 5 AND 
    T1.ColumnFive = 27

• T-SQL enhancements:
  • OFFSET and FETCH NEXT/FETCH FIRST paging
  • THROW statement in exception handling
  • new SEQUENCE object
    • Related DDL, eg CREATE SEQUENCE
    • Related DML, eg NEXT VALUE FOR SomeSequence
  • Windowing ROW and RANGE keywords
    • Related PRECEDING/FOLLOWING, UNBOUNDED PRECEDING/UNBOUNDED FOLLOWING and CURRENT ROW specifiers
  • Windowing LAG() and LEAD()
  • Execute... WITH RESULT SETS clause
  • FORCESCAN and FORCESEEK hint keywords
  • String manipulation functions Format() and Concat()
  • Logical functions Iif(), and Choose()
  • Type conversion functions Parse(), Try_Parse(), and Try_Convert()
  • New DateTime functions (EOMonth(), etc)
  • Other new analytical functions
    • Percent_Rank()
    • Cume_Dist()
    • First_Value()
    • Last_Value()

The winforms demo appears to have some resx localised properties with inconsistent values between English and French & Spanish; in English, scrolling the options area works correctly, but in French and Spanish content disappears off the bottom of the panel/screen.

Finally received some feedback from a user who actually wants this - need to bite the bullet.

When output is set to HTML, should HTMLEncode all output content; is actually HTMLDecoding instead!

Input:

IF 1 <> 2 SELECT 1    

Output:

<pre class="SQLCode"><span class="SQLKeyword">IF</span> 1 <span class="SQLOperator"><></span> 2
    <span class="SQLKeyword">SELECT</span> 1
</pre>

Expected output:

<pre class="SQLCode"><span class="SQLKeyword">IF</span> 1 <span class="SQLOperator">&lt;&gt;</span> 2
    <span class="SQLKeyword">SELECT</span> 1
</pre>

The demo project uses a WebBrowser control, and loads the content from a stream to avoid creating temporary files - I haven't been able to figure out how to make this work under Mono project (in fact, not sure I've managed to get the WebBrowser control to work at all - I get misc firefox/mozilla errors)

Currently WITH clauses get no special treatment, but in DDL code you would typically want them to break to the next line.