PATCH: Format SQL (external tool)

From: "J(dot)F(dot) Oster" <jinfroster(at)mail(dot)ru>
To: pgadmin-hackers(at)postgresql(dot)org
Cc: Shane StClair <shane(at)axiomalaska(dot)com>
Subject: PATCH: Format SQL (external tool)
Date: 2015-05-03 07:42:17
Message-ID: 110456625.20150503104217@mail.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-hackers

Hello All!

Per discussion
http://www.postgresql.org/message-id/CAPyomk5NT9Tm-r3wombLzoY60Vqa+QyRDy4u84_2K9UWLbWHTg@mail.gmail.com

It's most useful for making readable queries generated by ORMs such as
Hibernate. But in general, external processing can go far beyond
formatting task.

I've implemented this feature quick-and-dirty long ago. Finally I made
myself clean it up, now it looks better, so please consider a patch.
Tested on Windows 7 and Ubuntu 14.04.

Changes:
* added new setting, ExtFormatCmd, "External formatting utility" in
Options dialogue
* added menu item "Edit - Format - External Format" in
Query editor
* class sysProcess supports UTF-8 and can pass STDIN for a process.

Suggested use scenario:
1. Download and install some SQL formatting utility.
2. Tell pgAdmin where it resides:
File - Options - Query Editor: External formatting utility.
3. Open Query editor. Select a text block to format and press
Ctrl-Shift-F. With no selection the whole text gets formatted.
In case of non-zero exit code, STDERR will be shown in status bar.

Requirements for external formatting utility:
* Accepts a STDIN stream and writes result to STDOUT
* Finishes in less than 3 seconds
* Exits with code 0 on success
Support for UTF-8 multibyte characters is preferable.

To see whether it works well, a test can be done:
C:\> type in.sql |some_formatter >out.sql
C:\> echo %ERRORLEVEL%
or
user(at)linux:~$ cat in.sql |some_formatter >out.sql
user(at)linux:~$ echo $?

There are few available utilities depending on platform:
* Free SQL Formatter (Linux, Windows, Mac OS X(?))
http://fsqlf.sourceforge.net/
* Poor Man's T-SQL Formatter (Windows)
http://architectshack.com/PoorMansTSqlFormatter.ashx
Also it is possible to make a wrapper script for numerous online
formatting services, but it's less secure and less reliable.

Fsqlf is FOSS and seems promising. I think of extending it for
PosgreSQL-specific SQL syntax and probably even PL/pgSQL.

--
Best regards,
J.F.

Attachment Content-Type Size
150503_extformat.patch application/octet-stream 77.7 KB

Responses

Browse pgadmin-hackers by date

  From Date Subject
Next Message Adam Dingle 2015-05-03 14:37:28 bug tracker should be listed on Development page
Previous Message Dave Page 2015-04-28 12:54:47 Re: PGAdmin 4 architecture (Was: [Patch] PGAdmin 4 JSON Handling)