Thread: [pgadmin-hackers] Autoformatting

Hello!

Attached are current designs for SQL autoformatting. This feature is not developed yet, only mockups of a potential solution so technical implementation is still being discussed. Style guide for autoformatting doesn't yet exist either, what's shown is manually formatted by another person.

When a user highlights a section of text, a menu option appears below with the option to autoformat text. 

Not included in this current design:

- other options available when highlighting multiple sections of text

- compacting queries

We're currently testing this design with pgAdmin3 users. Looking to validate with a few more before developing the feature. Let us know your thoughts!

1. Query pasted into text editor

2. Query highlighted and autoformat option appears

3. Query formatted

On Wed, Feb 15, 2017 at 9:40 AM Raffi Holzer <rholzer@pivotal.io> wrote:

Thanks Dave! We came to the same conclusion about a button press. And thank you for the SQL guidelines.

On Wed, Feb 15, 2017 at 4:37 AM, Dave Page <dpage@pgadmin.org> wrote:

Hi

On Tue, Feb 14, 2017 at 3:02 PM, Raffi Holzer <rholzer@pivotal.io> wrote:
> Hi Dave,
>  When we were talking about auto formatting we were actually referring to a
> single button press doing all of these things to a large block of text. The
> use case here would be if you receive a large illegible query and paste it
> into the query editor it would either automatically auto-format or you would
> press a button and it would auto-format.

Oh, OK.

> We are trying to determine a few
> things. One, are there preferred formatting guidelines

Hmm, it seems I missed SQL when I wrote our (basic) coding standards:
https://www.pgadmin.org/docs4/1.x/coding_standards.html

By default (in my opinion), we should have 4 character indents, new
lines for logically distinct objects (e.g. columns or constraints on a
table), commas etc. at the end of the line, keywords in upper case,
e.g.

SELECT
    a,
    b,
FROM
    t
ORDER BY
    a,
    b;

However, that obviously becomes unwieldy in some cases, so common
sense is needed:

SELECT
    a, b,
FROM
    t
ORDER BY
    a, b;

There should of course be a limit on the number of columns listed per
line - but should the limit be a count or max width in chars? With a
CREATE TABLE statement I think it's clear that each column should be
on it's own line:

CREATE TABLE t (
    a serial NOT NULL PRIMARY KEY,
    b text
);

In other words, we need to define (or adopt) a formal standard for
this, and then write a parser/formatter - which seems like a decidedly
non-trivial amount of work (FYI, I tried the Python format-sql module,
and it failed on ~80% of my tests - probably either because it doesn't
understand COPY or pl/pgsql).

> and two, how should
> this autoformatting be implemented? On a button press? Upon pasting in the
> text?

Definitely not on pasting - if you drop in a large script, it could
take far too long, plus you may not care, or be looking at the
original source in another tool and trying to mentally reconcile lines
or statements to each other. I would say it should be a
button/shortcut.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--

Raffi Holzer

Product Manager

Pivotal Labs

Hello!

We're starting development on this soon, so pulling this thread back up.

Here is an update on the design of the pop up, which matches the style we decided for alerts.

Over the weekend I created some options for formatting. certainly this isn't complete but I hope it is a good starting point for the conversation. Copy and paste lost syntax highlighting but please assume that will be included. 

/* Unformatted  */

select distinct dep.deptype,dep.classid,coalesce(coc.relname, clrw.relname) as 

ownertable from pg_depend dep left join pg_class cl on dep.objid = cl.oid 

left join pg_attribute att on dep.objid = att.attrelid and dep.objsubid = att.attnum 

where dep.objid = 16385 :: oid and classid in (select oid from pg_class 

where relname in ( 'pg_class', 'pg_constraint' )) order by classid,cl.relkin;

/* Keywords Upper, Stacked, commas after */

SELECT DISTINCT dep.deptype, 

                dep.classid, 

                Coalesce(coc.relname, clrw.relname) AS ownertable 

FROM   pg_depend dep 

       left join pg_class cl 

              ON dep.objid = cl.oid 

       left join pg_attribute att 

              ON dep.objid = att.attrelid 

                 AND dep.objsubid = att.attnum 

WHERE  dep.objid = 16385 :: oid 

       AND classid IN (SELECT oid 

                       FROM   pg_class 

                       WHERE  relname IN ( 'pg_class', 'pg_constraint' )) 

ORDER  BY classid, 

          cl.relkin; 

Anyhoo, The standard we try to use in pgAdmin at the moment is slightly different from the example you've given. To add it into the mix...

/* Keywords Upper, 4 space indent, commas after, AND/OR after,

 * no spaces after ( or before ), or around :: 

 */

SELECT DISTINCT 

    dep.deptype, 

    dep.classid, 

    coalesce(coc.relname, clrw.relname) AS ownertable 

FROM

    pg_depend dep 

    LEFT JOIN pg_class cl ON dep.objid = cl.oid 

    LEFT JOIN pg_attribute att ON dep.objid = att.attrelid AND dep.objsubid = att.attnum 

WHERE

    dep.objid = 16385::oid AND

    classid IN (

        SELECT 

            oid 

        FROM 

            pg_class 

        WHERE

            relname IN ('pg_class', 'pg_constraint')

    ) 

ORDER BY 

    classid, 

    cl.relkind; 

There are some subtleties that are open to personal taste there;

- Formatting of the sub-select - e.g. should the SELECT directly follow the (, and should the rest be indented accordingly?

- Formatting of multiple quals in the joins; e.g. should the qual following the AND be on the next line, and if so, should it be indented one level, or to align with the qual above? Should the first qual be on the next line?

--

Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Sun, May 28, 2017 at 11:54 AM, Robert Eckhardt <reckhardt@pivotal.io> wrote:
>
>
> On Tue, May 9, 2017 at 4:26 AM, Dave Page <dpage@pgadmin.org> wrote:
>>
>>
>> Anyhoo, The standard we try to use in pgAdmin at the moment is slightly
>> different from the example you've given. To add it into the mix...
>>
>> /* Keywords Upper, 4 space indent, commas after, AND/OR after,
>>  * no spaces after ( or before ), or around ::
>>  */
>> SELECT DISTINCT
>>     dep.deptype,
>>     dep.classid,
>>     coalesce(coc.relname, clrw.relname) AS ownertable
>> FROM
>>     pg_depend dep
>>     LEFT JOIN pg_class cl ON dep.objid = cl.oid
>>     LEFT JOIN pg_attribute att ON dep.objid = att.attrelid AND
>> dep.objsubid = att.attnum
>> WHERE
>>     dep.objid = 16385::oid AND
>>     classid IN (
>>         SELECT
>>             oid
>>         FROM
>>             pg_class
>>         WHERE
>>             relname IN ('pg_class', 'pg_constraint')
>>     )
>> ORDER BY
>>     classid,
>>     cl.relkind;
>>
>> There are some subtleties that are open to personal taste there;
>>
>> - Formatting of the sub-select - e.g. should the SELECT directly follow
>> the (, and should the rest be indented accordingly?
>>
>> - Formatting of multiple quals in the joins; e.g. should the qual
>> following the AND be on the next line, and if so, should it be indented one
>> level, or to align with the qual above? Should the first qual be on the next
>> line?
>>
>> --
>> Dave Page
>> Blog: http://pgsnake.blogspot.com
>> Twitter: @pgsnake
>>
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>
>
> Sorry I let this linger. Let's go with the style you currently have. With
> respect to your additional comments.

To be clear, I wasn't insisting on that style - but it is (roughly)
what we try to use and what I prefer.

>
> - Sub-Select should be a new line and indented as in your example.

Sounds good.

> - For multiple quals in the joins; the qual following the AND should be on
> the next line, and should it be indented one level

OK.

Thanks!


--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers