SD 4.2 formatter - too many line breaks

sarlaac

I just downloaded the 4.2 version and didn't import my settings from previous. I'm trying to setup the formatter to produce similar results to what I got in 4.1.5 but it seems to be doing a worse job of formatting the query. I have provided a example query below with a desired and actual results. In the actual results I have almost all line breaks off except before comma, but the formatted result breaks NVL or DECODE. Additionally the formatter indents 4 spaces after the comma. I would like it not to, and can't find a setting to achieve this.

--unformatted

SELECT nvl('this','that') col_name, 'Column1', 'Col2' FROM dual;

--desired format

--NVL, DECODES on one line

--break before comma, no space after.

--align columns and KEYWORDS

SELECT nvl('this', 'that') col_name

      ,'Column1'

      ,'Col2'

  FROM dual;

--SD 4.2 actual format

-- break before comma. everything else unchecked.

SELECT nvl(

        'this'

       ,'that'

    ) col_name

   ,   'Column1'

   ,   'Col2'

FROM dual;

Vadim Tropashko-Oracle

Custom format:

/*| [node) expr    &  [ancestor) "(x,y,z)"                         & ![node^) comparison_condition

                                                                  & ![node^) compound_expression

                                                                  & ![node^) interval_expression*/

You might actually want somewhat more sophisticated formatting rule, e.g. "Indent arguments for function when there are more than 4 of them"

In that case please disregard my earlier suggestion but modify the following:

complexIndentConditions: 

    closestAncestorDescendent.ancestor = ancestor4FirstAndLastDescendantsWithGap.ancestor

  & closestAncestorDescendent.node = closestAncestorDescendent.node   -- vacuous condition ...

  & [closestAncestorDescendent.ancestor+10 < closestAncestorDescendent.ancestor)

; 

Here the magic number 10 includes commas and parenthesis contributing to the length of the ancestor parse tree node. The output:

SELECT

    nvl('this','that','3',4) col_name,

    nvl(

        'this',

        'that',

        '3',

        4,

        5

    ) col_name2

FROM

    dual;

sarlaac

While I appreciate there is a solution to adjust the formatting. I don't see this as a good user friendly solution. Previous versions of SD and other formatting tools just handle this and format SQL much closer to what I'm looking for without a lot of tinkering. I'm happy to click a few checkboxes to tailor the output, but having to understand and edit this syntax is too much to ask. Are the format settings still under development, and can I expect some easier to use settings, or is the intent to shift to using these advanced options going forward?

Vadim Tropashko-Oracle

We already have some options on Advanced format panel which influence the rules on Custom panel. For example, flipping Breaks->Case->After WHEN results in

/*+breakAfterWhen | [node) condition  &  [node-1) 'WHEN'*/

being rewritten into

/*+breakAfterWhen*/ | [node) condition  &  [node-1) 'WHEN'

Therefore, new options can be introduced relatively easily. The problem is naming them intuitively. Also, to implement your request, would you be OK with one long line

function_call(argument1, argument2, argument3, argument4, argument5, argument6+argument7, argument8, argument9)

or is it better to indent arguments if some threshold is exceeded?

sarlaac

I think if you put an option on the advanced panel it would be better to take an argument on how many to break after. This way users can choose 0 or blank to just break on everything, or X so NVL or NVL2 doesn't break while longer calls do break out. I would usually want NVL and NVL2 and other smaller functions to not break, but I would want a function with more arguments to have breaks, especially when it gets so long I have to scroll over on the line to read it all.