Enhancement Request: Beautifier

[d27111]

I see some very strange requests for beautifier enhancements (some of which aren't even consistent within the request). Figure I'll throw mine in and hope for the best.

Select statement: Align equal signs in where clause, left align keywords, argument lists follow keywords on new line and indent two spaces, DO NOT PUT COMMA IN FRONT! PLEASE! Why people are still using capital letters is beyond me when IDE's utilize syntax highlighting (we're not in kindergarten anymore people).

select
  column1,
  column2,
  column3,
  column4
from
  table1,
  table2,
  table3
where
  table2.column2     = table1.column1
  and table3.column3 = table2.column2

Decode statements: Align beginning and ending parenthesis in same column. One space should always follow a comma.

select
  decode(column1, '1', 'January',
                  '2', 'February',
                  '3', 'March',
                  '4', 'April',
                  '5', 'May',
                  '6', 'June',
                  '7', 'July',
                  '8', 'August',
                  '9', 'September',
                  '10', 'October',
                  '11', 'November',
                  '12', 'December',
                  null
        )
from
  table1

Procedure/Function declarations: Always align parenthesis in same column as the letter "p" in procedure. Parameter list starts on new line with one space beyond first parenthesis. Align the modes. Align the datatypes. Keyword "is" follows last parenthesis on new line. DO NOT PUT COMMA IN FRONT! PLEASE!

procedure upd_beautifier
(
 pi_parameter1  in     varchar2,
 pi_parameter2  in     varchar2,
 pi_parameter3  in     varchar2,
 po_parameter4  out    varchar2,
 pio_parameter5 in out varchar2
)
is
...

 

[vladzak]

Why not putting commas in front? I find this very helpful when you need to add or remove columns or arguments to/from a long vertical list of elements. Since no element has trailing comma, you can add new ones anywhere and don't bother fixing commas (with trailing commas, the last element in the list does NOT have it, but all others should have at most one. With leading commas you can insert new element in any position but first without having to check/fix anything.)

 

[wbennett]

I agree with vladzak.

I use commas in the front even in procedure calls in Delphi.

I find that a lot of developers don't fully understand the benefits of block select and paste. Combine commas in front + block select + lining up common elements, you save loads and LOADs of time!

 

[rbrooker]

commas in front drive me crazy. you say that it makes copy and paste easier in that every line has a comma in front, whereas commas at the end mean that the last line does not have one.

If you think about it, the same is true for the first line when the commas are in front.

commas on the back - last line is different
commas on the front - first line is different.

also, IMHO, commas on the front look stupid.

here ends my2cents....

 

[null0000]

Originally posted by rbrooker:
commas in front drive me crazy. ...

also, IMHO, commas on the front look stupid.

here ends my2cents....

commas on the front not just don't look stupid, in fact they look LOGICAL.
in select statement as well as in insert, update
you put comma when you HAVE nTH ELEMENT .
that is of course different from the 1st element.
And as result , your comma belongs to the nth element whether it in list of columns in select or insert statements or list of pairs of column = value in update statement.
And the opposite: you do not have comma(s) if you have just 1 element.

 

[wbennett]

D27111:

Why people are still using capital letters is beyond me when IDE's utilize syntax highlighting (we're not in kindergarten anymore people).

I have put messages on this board with SQL in caps. If you have an opinion that is fine. Do not try to insult someone else while expressing your opinion, however. There are actually very valid reasons for using all caps in some cases.

In Delphi 4, and probably some other versions, The BDE insists on putting column names in quotes. This changes 'select fname from dual' to 'select "fname" from dual'. When Oracle sees the quotes, it takes the name literally (case sensitive). Since the column names by default are stored all upper, the query fails.

The funny thing is that I actually agree with you about the caps issue, and do not code that way in my DOA applications. I posted it here in caps because I did not know about the UBB stuff, and it was the only way I could get the letters to line up correctly. You will notice there are dots where spaces should be.

rbrooker:

Your point about the missing comma is very valid and true. However, I think you misunderstand what I meant by the copying and pasting.

I make it a point to always add columns to the end of my SQLs and to keep the same order as the order in the table. This makes it easier to spot when a column is missing, etc. (we have some stored procedures whose sole purpose is to insert into a table).

As for the copy paste, I like to keep everything the same name except for prefixes. This makes coding much faster (and easier to read, IMHO). Block copying and pasting and a little macro makes this trivial.

e.g.

cursor c_emp is
   select fname
         ,lname
         ,address
     from emp;

 begin
   ..
   v_fname   := c_emp.fname;
   v_lname   := c_emp.lname;
   v_address := c_emp.address;
   ..
 end;

 ** Delphi **
 begin
   q_fname   := qryEmp['fname'  ];  //for brevity
   q_lname   := qryEmp['lname'  ];  //in this post
   q_address := qryEmp['address'];
 end;

For me, doing the above code is more simple than with commas at the end. I block select the columns from the SQL, and then just paste it where I need it. Then use block paste or a macro to do the prefix. No need to remove commas (manually, search and replace, or macro). Saves a bunch of time, especially if you are doing this kind of thing a lot, like me. Especially great for maintenance and making sure that columns/parameters are in the right order when debugging.

BTW

I too thought the commas in front looked stupid at first. After seeing how much time it saved though, I converted. I can't tell you how many times I help another developer out only to find they copy and pasted a bunch of columns to a SQL but forgot to put the comma in the "previous last" column (especially when doing UGH! dynamic SQL UGH!). Since using this format, I have never had those kinds of problems.

Try it out. I just may grow on you.

 

[townsendEbmud]

Lid up or down, toilet paper this way or that, now commas front and back. Sheesh. Just give us a preference setting so we can all have it the way we like it.

 

[vladzak]

Originally posted by townsendEbmud:
Just give us a preference setting so we can all have it the way we like it.

There ARE preferences for comma placement in the Beautifier already.

 

[Marco van der Linden]

yes there are, but only before or after. Personally I'd like to have the comma's left aligned with the key words

select col1
   ,      col2
   ,      col3

instead of

select col1
         ,col2
         ,col3

Maybe it is possible to have some sort of 'freestyle' template.

 

[William Robertson]

The first line of a multi-row comma-separated list is different, but not all that different because it has the opening bracket:

blah
( blah
, blah
, blah );

I thought leading commas were weird at first but once you think of them as bullet points they make sense. We're not in kindergarten any more