Individual SQL formatter

[sonntagd]

I would like to have an option to customize the SQL formatter. Different people have different styles formatter an SQL query and I would like it very much if DBeaver could automatically format SQL statements the way I want them.
An easy way would be to add an option where one can set a program path. This program takes the SQL from STDIN and outputs it formatted on STDOUT. So everybody can use or write his own formatter script.

[raqua]

+1 for this

[serge-rider]

Makes sense. And not hard to do. Will try it after 3.6.2.
Can you name some external formatting tools I can test with?

[r-richmond]

+1

Also does it make sense to "lock" (prevent new posts & maybe new comments) the feature request forum. There is a similar request there http://dbeaver.jkiss.org/forum/viewtopic.php?f=3&t=1343

I know you've posted please post all feature requests on github but users are still posting over there and it seems less than ideal to have to keep track of 2 different areas.

[r-richmond]

Hey Serge,

The above commit appears to be included in 3.6.2. Is this feature live / can you post an example of how to use it?

[serge-rider]

The feature isn't yet ready. Maybe in 3.6.3, but not for sure.

[r-richmond]

Thanks for the update.

Also based on my reading of @Choclin 's suggestion it sounds like way the feature was requested any programming language could be used to format the string. Is this correct? (I plan on using this feature with a python script).

[sonntagd]

Yes, my idea was that you can use any external executable, so everybody could write or use his own formatter. I don't have such a script ready to use here. A simple way would be to use a simple Python script using the sqlparse module. To me, the options of that module are not enough, but it would be enough to get started.

[sonntagd]

More precise:

Write a Perl script like this:

#!/usr/bin/perl

use strict;
use warnings;
use SQL::Beautify;

my $sql_query = <STDIN>;
my $sql = SQL::Beautify->new();
$sql->query($sql_query);
print $sql->beautify;

Now you can call it and send your SQL to STDIN, you will get out the formatted statement.

Or with a Perl one-liner:

perl -MSQL::Beautify -e 'print SQL::Beautify->new(query => join "" => <STDIN>)->beautify'

For example, on a shell:

echo "SELECT * FROM sklhfkajshf                                                                            ✓  08:45:41 
WHERE cxc=98" | perl -MSQL::Beautify -e 'print SQL::Beautify->new(query => join "" => <STDIN>)->beautify'

...outputs:

SELECT
    *
FROM
    sklhfkajshf
WHERE
    cxc = 98

[serge-rider]

Yes, I got the idea.
Will try to add external formatter support in one of the next versions.

[sonntagd]

In SQLyog, I can start writing a SQL statement and it automatically converts keywords to uppercase within the editior. Is there already an option to do the following?

[serge-rider]

Not yet, you could create a feature request. It is possible to make such runtime conversions using Eclipse text editor framework.

[serge-rider]

Well, first version is ready (wait for 3.6.3 or build from sources).

[ctaulien]

Uhm... one thing about it:
It would be great to add an option to only format the current selection? (low prio, of course)

[serge-rider]

This feature is already there - just select a query and press ctrl+alt+f.

[sonntagd]

Thank you for your fast reaction! I tried to build it from sources (via Docker's maven image) and got the following error when running mvn install:

[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 04:24 min
[INFO] Finished at: 2016-03-21T08:22:19+00:00
[INFO] Final Memory: 204M/1495M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.eclipse.tycho:tycho-compiler-plugin:0.23.1:compile (default-compile) on project org.jkiss.dbeaver.core: Compilation failure: Compilation failure:
[ERROR] /usr/src/app/plugins/org.jkiss.dbeaver.core/src/org/jkiss/dbeaver/ui/preferences/PrefPageSQLFormat.java:[39]
[ERROR] import org.jkiss.dbeaver.model.sql.format.external.SQLExternalFormatter;
[ERROR] ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[ERROR] Access restriction: The type 'SQLExternalFormatter' is not API (restriction on classpath entry '/usr/src/app/plugins/org.jkiss.dbeaver.model/target/classes')
[ERROR] /usr/src/app/plugins/org.jkiss.dbeaver.core/src/org/jkiss/dbeaver/ui/preferences/PrefPageSQLFormat.java:[104]
[ERROR] formatterSelector.add(SQLExternalFormatter.FORMATTER_ID);
[ERROR] ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[ERROR] Access restriction: The type 'SQLExternalFormatter' is not API (restriction on classpath entry '/usr/src/app/plugins/org.jkiss.dbeaver.model/target/classes')
[ERROR] /usr/src/app/plugins/org.jkiss.dbeaver.core/src/org/jkiss/dbeaver/ui/preferences/PrefPageSQLFormat.java:[104]
[ERROR] formatterSelector.add(SQLExternalFormatter.FORMATTER_ID);
[ERROR] ^^^^^^^^^^^^
[ERROR] Access restriction: The field 'SQLExternalFormatter.FORMATTER_ID' is not API (restriction on classpath entry '/usr/src/app/plugins/org.jkiss.dbeaver.model/target/classes')
[ERROR] 3 problems (3 errors)
[ERROR] -> [Help 1]
[ERROR] 
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR] 
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoFailureException
[ERROR] 
[ERROR] After correcting the problems, you can resume the build with the command
[ERROR]   mvn <goals> -rf :org.jkiss.dbeaver.core

[serge-rider]

Hmm. Work ok on windows.
Will try it on Ubuntu later today.

[sonntagd]

Thank you.

[serge-rider]

Something is wrong with output reading..
Try to put entire command in a shell script and execute it as "sh yourscript.sh".

[sonntagd]

This works (similar to what I described above). It is okay for me, but it probably should be noted that there may be a problem with this type of commands.

[serge-rider]

Try it now

[sonntagd]

It works, thank you!

[r-richmond]

I'm struggling to get this working what am I doing wrong?

tried
python path\\script.py $(file)
python path\\script.py <STDIN>

for reference my python script im testing is as follows

import sys

message = '# called ' + sys.argv[0] + '\n'
message += sys.argv[1]

print(message)

basically i just want it to return the exact sample sql unformatted with an additional line at the top with the name of the script. instead i'm just getting

-- Edit nvm i got it
python path\\script.py

import sys

message = '--called ' + sys.argv[0] + '\n'

for line in sys.stdin:
    message += line.rstrip() + '\n'

print(message)

[giquieu]

Hello, where meeting the psti.exe that Serge is using?

[serge-rider]

Its is just some PLSQL formatter I found on the Internet.

[giquieu]

Thanks for the answer.
I can not find it psti.exe :(
U could send me? or send me other?

[carlosspohr]

Hi there,

I believe that can be this one here:

http://psti.equinoxbase.com

It's a SQL formatter for pl/sql for windows or Linux trough wine.

Probably you have to install it to get that exe file.

On Jul 21, 2016 5:36 PM, "giquieu" notifications@github.com wrote:

Thanks for the answer.
I can not find it psti.exe :(
U could send me? or send me other?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#267 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAm7RzgwZ3GpgUw92Bwz5UlEGjdZRX9gks5qX9hRgaJpZM4HqHaJ
.

[ravish-tech]

I use the below tool. It is really great.
http://www.architectshack.com/PoorMansTSqlFormatter.ashx

Tried to set it up by <SQLFormatter.exe> ${file}. But doesn't seems to work. No errors, but nothing changes on Apply.

[serge-rider]

This formatter is very slow (it takes more than 1 seconds to format a tiny SQL).
So you need to increase timeout in external formatter preferences (at least to 2 seconds).

[ravish-tech]

Hey, Thanks. Yes. I figured. It works fine in SSMS and Visual Studio. But command line tool just takes too long.

One quick question. When using external formatter with file, passes file as input. But does it expects output at STDOUT or file? Is there a way to specify it? I learned from above that ${file} can be input file. But how to tell DBeaver where to look for output?

[serge-rider]

For now DBeaver always gets output from STDOUT. There is not way to redefine that. Although it is possible to use a batch script which will pipe output from file to STDOUT.

[guidomarcel]

Hello, I wrote a commercial SQL Formatter called SQLinForm and it can be included as external formatter into DBeaver. There are also free versions like the free Notepad++ plugin.

If you think that this message is not OK, as it links to a commercial product then please feel free to remove it.

Regards

[jimpriest]

Anyone got this working with Poor Mans SQL Formatter??
http://architectshack.com/poormanstsqlformatter.ashx

[tbcrawford]

@jimpriest In case you didn't get this solved yet, you should be able to download the command line tool (currently 1.6.10) for Poor Mans SQL Formatter and just link to the executable in the Command Line textbox. The following should work fine and works fine for me.

[jimpriest]

@tbcrawford Tried that but doesn't work for me for some reason. I'm on Windows 10, DBeaver 5.1.2.

I downloaded SQL Formatter 1.6.10, I have to add an extra slash or DBeaver complains but I get no formatting: C:\tools\SqlFormatter\SqlFormatter.exe

If I don't double slash I get:

Cannot run program "C:tools\SqlFormatter\SqlFormatter.exe": CreateProcess error=2, The system cannot find the file specified

I hit Format and nothing happens, no error, no formatting.

[jimpriest]

So with confirmation this works I decided to reinstall DBeaver - I selected to 'reset settings'

I still have to use a double slash in the path but now the formatter actually does something :)

It works!

[michaelmiscanuk]

Hi,

I cannot make this Poor formatter to work. Tried the solution suggested, but when a applying formatting, nothing happens.

Here is my settings:

Can someone help me to set it up?
Thanks

[jimpriest]

I have:

C:\path\sqlformatter\sqlformatter.exe /sac

And "use temp file" is unchecked.

Not sure what the /sac flags do - I can't remember. Could probably run the command with a --help or /? and see what the flags were.

[PaulVMarginean]

Hello,

I am trying to add an external formatter from my Mac and use that as a template for formatting SQL.
On the command line I have the path to my file: /Users/username/format.sql. The file has execution rights and the below content:
SELECT

• FROM Table t;

I select the "Use temp file checkbox" but once I click "Apply" I get the following message in the preview section.
/Users/username/format.sql: line 1: SELECT: command not found
/Users/username/format.sql: line 2: 100: command not found

Can anyone, please help and let me know how should the external file (format.sql) look like?

Thanks,
Paul M.

[sonntagd]

Hi Paul,

a SQL file is not an executable file. You have to provide a shell script or an executable binary that takes SQL as input and outputs formatted SQL.

[PaulVMarginean]

Thanks for your answer Dominic. I'll give it a try using your suggestion.

[adamal]

I am trying to use with the Poor Man's formatter on a mac. I.e. using the npm packages from here.

Verified working from the terminal (echo "select" | sqlformat --> SELECT)
It seems to be an issue with the PATH being different:
In the Command line: field, adding just sqlformat results in an error "No such file..."
Adding the full path (/usr/local/bin/sqlformat) results in env: node: No such file or directory, which (from googling) might be due to node not being found.

Have anyone had success setting up poorman's formatter on mac?

[ugo-buonadonna]

@adamal I've configured it by passing the full paths of node and sqlformat in the command line

You should be able to see the absolute paths by running which node and which sqlformat, then put them together one after the other in the command line

[jimpriest]

This works great! Thanks for the screenshot!