zsong / sqlbeautifier Goto Github PK

Options to "autoformat on save", similar to settings as seen in prettier (or JsPrettier for Sublime Text 3) would be useful. Shown below is a snippet copied from JsPrettier.sublime-settings:

	// Whether or not to automatically format the file on save.
	// ----------------------------------------------------------------------

	"auto_format_on_save": false,

	// ----------------------------------------------------------------------
	// Auto Format on Save Excludes
	// ----------------------------------------------------------------------
	//
	// @param {array} "auto_format_on_save_excludes"
	// @default []
	//
	// Ignore auto formatting when the target file, or its path resides in a
	// particular location, and when `auto_format_on_save` is turned on.
	//
	// Example:
	//
	//     "auto_format_on_save_excludes": [
	//         "*/node_modules/*",
	//         "*/file.js",
	//         "*.json"
	//     ]

When using SQL Beautifier, I'm often using T-SQL scripts that end with GO

Unfortunately when I run SQL Beautifier it puts this "GO" on the same line as the previous line of SQL. This breaks T-SQL

MS documentation https://msdn.microsoft.com/en-us/library/ms188037.aspx

"A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments."

It's easier to demonstrate with an example, instead of the current formatting like below

SELECT col2,
if(y IN ('a', 'b', 'c'), 1, 0) AS col2
FROM TABLE_NAME

I wonder if the list 'a', 'b', 'c' could be aligned like

SELECT col2,
if(y IN (
'a',
'b',
'c'
), 1, 0) AS col2
FROM TABLE_NAME

As the issue #3, may I suggest you to add a Context.sublime-menu file to your plugin. This file controls the right-click menu on a file being edited. This will provide the ability to beautify the current file or selection without the obligation to use the keyboard shortcut.

Sample content tested with Sublime Text 3 Build 3126:

[
    { "caption": "-" },
    {
        "caption": "SQL Beautifier",
        "command": "sql_beautifier"
    },
    { "caption": "-" }
]

Note: I don't know if ST3 is able to display context menu depending on the selected syntax. This would be chic.

consider such a case

SELECT *
FROM
  (SELECT `problem`.`title` AS `title`,
          `problem`.`problem_id` AS `pid`,
          source AS source,
          author AS author,
          contest_problem.num AS pnum
   FROM `contest_problem`,
        `problem`)

when run the beautifier, it becomes:

SELECT *
FROM
  (SELECT `problem`.`title` AS `title`,
          `problem`.`problem_id` AS `pid`,
          SOURCE AS SOURCE,
                    author AS author,
                    contest_problem.num AS pnum
   FROM `contest_problem`,
        `problem`)

the source is changed to uppercase
and one more indentation was add to the line after the source

Not working on Sublime Text 3126

I just installed it, and run the command and nothing changed, and this popped up on the console.

Traceback (most recent call last):
  File "D:\User\Dropbox\Applications\SoftwareVersioning\SublimeText\sublime_plugin.py", line 818, in run_
    return self.run(edit)
  File "sqlbeautifier in D:\User\Dropbox\Applications\SoftwareVersioning\SublimeText\Data\Installed Packages\SqlBeautifier.sublime-package", line 50, in run
  File "sqlbeautifier in D:\User\Dropbox\Applications\SoftwareVersioning\SublimeText\Data\Installed Packages\SqlBeautifier.sublime-package", line 41, in replace_region_with_formatted_sql
  File "D:\User\Dropbox\Applications\SoftwareVersioning\SublimeText\sublime.py", line 823, in replace
    sublime_api.view_replace(self.view_id, edit.edit_token, r, text)
TypeError: String required

Very useful plugin, but a configurable list of additional keywords to fix-case etc. would be handy.

Currently the sqlparse list misses analytical functions (OVER etc) a quick fix to add those would remedy the problem to an extent. Still, a better solution would be to make a configurable list of additional keywords, for custom or platform-specific stored procedures, system schemas/tables and so on.

I love this plugin, it works wonderfully. However it'd be nice to have a way to undo the beautification after I've made edits. Is that possible?

keywords: minify minifier unbeautify unbeautifier

Might I suggest that you add a Default.sublime-commands file to this package, so that its command shows up when you trigger Sublime's command palette (Ctrl-Shift-P on Windows/Linux, Cmd-Shift-P on Mac)

Like this one from a competing SQL formatter:

[
    {
        "caption": "FormatSQL: Format SQL statement",
        "command": "format_sql"
    }
]

For me, unless the command's in the palette, I completely forget that it exists.

Is there a way to change key binding value? I've tried to add a new value at Preferences > Key Binding but this doesn't seems to work

Nice plugin though

How do I make the delimiter comma of a field following a SELECT appear before the field

This SQL fragment

INSERT INTO bla VALUES (1, 2), (3, 4), (5, 6);

gets formatted into this

INSERT INTO bla
VALUES (1,
        2), (3,
             4), (5,
                  6);

Instead I would expect something like:

INSERT INTO bla
VALUES 
    (1, 2),
    (3, 4),
    (5, 6)
;

select *
from users
limit 10

Gets formatted into:

SELECT *
FROM users LIMIT 10

Such as:

SELECT col1
, col2
, col3
, col4
FROM table

I don't know how to set comma before items and blank after each line. Is it possible to do that?

Sometimes code doesn't get indented at all.

For example, this valid Sql statement (MySql):

select *
from setup.trans_bases t
join setup.hier_bases_total b on b.azienda=t.azienda
and b.idhier_bases=t.idhier_bases_start
and year(b.fine_validita)=9999
join setup.hier_bases_total b1 on b1.azienda=t.azienda
and b1.idhier_bases=t.idhier_bases_end
and year(b1.fine_validita)=9999
where t.idtrans_anag=7;

..after SQL Beautifier, remains the same.

My user preferences:

{
    "keyword_case": "lower",
    "identifier_case": "lower",
    "strip_comments": false,
    "indent_tabs": false,
    "indent_width": 4,
    "reindent": true
}

Sublime Text 3, build 3083.

Thank you!

Please add support for wrap_after.

If my sql has many params ('?' chars separated by comma), they will be formatted as ?, on each line.
Not very producting when having 100+ lines almost empty...

Using these settings:
{ "indent_tabs": true, "indent_width": 1, "reindent": true }

The SQL code gets this nice format:

SELECT foo,
       bar AS BAR
FROM TABLE
WHERE stuff = stuff2
 AND other_stuff NOT IN ('TH',
                         'AN',
                         'KS')
 AND blah = bleh

But I want to use tabs instead of spaces, so now with plugin settings:
{ "indent_tabs": true, "indent_width": 1, "reindent": true }

The code gets formatted to (had to use an image):

I guess the problem is that the number of tabs used is the same number of the calculated spaces, is there any way to read the user's customized tab size to do the math?
Congratulations for the great plugin.

Just as previous guy mentioned, great plugin. Thanks for your hard work.
One enhancement request that I have is:
It would be nice to have a setting/option to pick which key words are to not be followed by anything (other than newline).

Note: xxxx replaces spaces (you can't use spaces or tabs in here for some strange reason

Current way:
SELECT A.COL1 AS COL1,
xxxxxxxxA.COL2 AS COL2,
xxxxxxxxB.COL3 AS COL3
FROM APPLES A
INNER JOIN BANANAS B ON A.SOLDTO=B.SOLDTO
WHERE A.SOLDTO = "Peter"

proposed setting/option:
"newLineAfterKeywords": [SELECT,FROM,WHERE]

New formatting:
SELECT
xxxxxxxA.COL1 AS COL1,
xxxxxxxA.COL2 AS COL2,
xxxxxxxB.COL3 AS COL3
FROM
xxxxxxxAPPLES A
INNER JOIN BANANAS B ON A.SOLDTO=B.SOLDTO
WHERE
xxxxxxxA.SOLDTO = "Peter"

bonus points given if you can format the FROM section as following:
FROM
xxxxxxxxxxxxxxxxxAPPLES A
INNER JOINxxxxxBANANAS B ONxxxxxxxxxxxxxxxxxxA.SOLDTO = B.SOLDTO
INNER JOINxxPINEAPPLES P ON A.SOMELONGCOLUMNNAME = P.COLUMN

basically have the table names right aligned and indented so that the "ON" is always in same position. and the same thing for "=" being in the same position

Not seen button in edit bar or any hotkey? After selecting the whole document, how exactly this plugin can be ran?

Wonderful plugin! Would love to see two new config options. One to not split up values inside of an IN clause to new lines but instead just put a space between values.
for example
current method:
IN (1,
2,
3,
4)

option to disable this:
IN (1, 2, 3, 4)

The other requested option would be to indent AND criteria as part of an JOIN xx ON statement the same way that they are indented for WHERE clauses.
For example
current method:
JOIN xxx ON blah = blah
AND foo = foo
AND xyz = xyz

option enabled to indent:
JOIN xxx ON blah = blah
AND foo = foo
AND xyz = xyz

Great package...

Would it be possible to add the option to not strip blank lines?