padding function parameters in 'Custom Format'

vder

    Hi I'm trying to make sql developer to format code my way, but I'm not necessary sure how padding works. Currently function heading are formated like in example below:

    create or replace function mgrname ( p_first        in number%type,

      p_second_arg   number,

      p_third        number ) return name.country%type is

      result   name.country%type;

      i        integer;

    begin

        return(null);

    end;

     

    What i'm trying to achieve  is to align all parameters to the first one so the code after formating should look something like that:

    create or replace function mgrname ( p_first        in number%type,

                                                                p_second_arg   number,

                                                                p_third        number ) return name.country%type is

      result   name.country%type;

      i        integer;

    begin

        return(null);

    end;

     

    I added additional rule to the pairwiseAlignments2 (the one in bold):

     

    pairwiseAlignments2: predecessor=node-1-1  & (

        [predecessor) select_list    & [node) select_term & [node-1) ','

      | [predecessor) cartesian_product      & [node) table_reference   & [node-1) ','

      | [predecessor) condition      & [node) condition   & [node-1) AND_OR

      | [predecessor) prm_spec               & [node) parm_list_opt

    );

     

    but it seems it doesn't work. Any Idea what I should modify to achieve my goal?

     

    Well frankly speaking I don't understand how  sql developer knows that two specific lines should have the same the same indentation, as arbori rules defines only sets of parsed tokens?

      • 1. Re: padding function parameters in 'Custom Format'
        Vadim Tropashko-Oracle

        First, the test code doesn't parse, because of "number%type". Therefore, I changed it to "number1%type". Please advise if this is a parser bug or not.

         

        Second, I dropped the indentation of the first parameter:

         

        simpleIndentConditions:

        ...

        --| [node) prm_spec &      [node^) fml_part

         

        Third, I added

         

        pairwiseAlignments5: (

            predecessor=node^-1

          | predecessor=node^^-1

          | predecessor=node^^^-1

          | predecessor=node^^^^-1

          )

        & [predecessor) prm_spec

        & [node) prm_spec;

         

        and amended pairwiseAlignments to include it

         

        pairwiseAlignments:

            pairwiseAlignments1

          | pairwiseAlignments2

          | pairwiseAlignments3

          | pairwiseAlignments4

          | pairwiseAlignments5

        ->

        ;

         

        The result:

         

        CREATE OR REPLACE FUNCTION mgrname ( p_first        IN number1%TYPE,

                                            p_second_arg   NUMBER,

                                            p_third        NUMBER

        ) RETURN name.country%TYPE IS

            result   name.country%TYPE;

            i        INTEGER;

        BEGIN

            return(NULL);

        END;

         

        Here is an illustration how it works:

        forum.png

        • 2. Re: padding function parameters in 'Custom Format'
          vder

          Thanks a lot Vadim for a reply. It looks likes you are the only person in the whole internet who knows how it works:). In a meantime I found some guides on your blog about alignments and wrote something similar:

           

          pairwiseAlignments5: [predecessor) identifier &  [predecessor^-1) '(' &  [predecessor^^) fml_part

          &  [node) prm_spec;

           

          Ok so now I've got some more questions, first one about your solution: As number of parameters increase the parse tree grows deeper and deeper and I have some doubts if that solution will work when I have  let's say 10 parameters (mostly due to conditions like predecessor=node^^^-1)?

           

          and the second question is there any way to remove that white space before first parameter so instead of:

           

          CREATE OR REPLACE FUNCTION mgrname ( p_first        IN number1%TYPE,

                                              p_second_arg   NUMBER,

                                              p_third        NUMBER

          I will have:

          CREATE OR REPLACE FUNCTION mgrname (p_first        IN number1%TYPE,

                                              p_second_arg   NUMBER,

                                              p_third        NUMBER

           

          I compared two format.arbori files one with 'white space' options from advanced format tab turned on and second file where these options were off, but there wasn't any differences so my  guess is  that I can't control where white spaces are placed?

           

           

          These are the options I'm talking about:

          • 3. Re: padding function parameters in 'Custom Format'
            Vadim Tropashko-Oracle

            The solution that works for any number of parameters is:

             

            pairwiseAlignments5:

              [predecessor) prm_spec

            & [node) prm_spec

            & predecessor=ancestor-1

            & ancestor < node;

             

            The one character misalignment is a bug. Unfortunately a workaround which adds a condition

             

            notPaddedParenthesis:

            ancestor < paren & (

            (   [ancestor) constraint  & [ancestor) paren_expr_list

               | [ancestor) constraint & [ancestor^) constrained_type

               | [ancestor) paren_expr_list & [ancestor^) function_call

               | [ancestor) function_expression

               | [ancestor) model_iterate_clause

               | [ancestor) datetime_literal

               | [ancestor) rollup_cube_clause

               | [ancestor) datatype

               | [ancestor) fml_part

            ) & ( [paren) '(' | [paren) ')' )

            |

            (   [ancestor) model_clause  ) & ( [paren) '[' | [paren) ']' )

            )

            ->

            ;

             

            misaligns it the other way:

             

            CREATE OR REPLACE FUNCTION mgrname(p_first        IN number1%TYPE,

                                                p_second_arg   NUMBER,

                                                p_3            NUMBER,

                                                p_4            NUMBER,

                                                p_5            NUMBER,

                                                p_6            NUMBER,

                                                p_7            NUMBER,

                                                p_8            NUMBER,

                                                p_10           NUMBER,

                                                p_third        NUMBER

             

             

            • 4. Re: padding function parameters in 'Custom Format'
              vder

              Once again Thanks for reply.

              • 5. Re: padding function parameters in 'Custom Format'
                vder

                OK as far as declarations goes everything works ok. But now I get to calling stored procedures and I have similar problems to align all parameters with each other. I prepared rule as follows:

                 

                pairwiseAlignments6:

                  [predecessor) '('

                & [node)  assoc_arg

                & predecessor=ancestor-1-1

                & ancestor < node

                & [predecessor^-1) name_wo_function_call

                & ![node^) paren_expr_list

                ;

                 

                and it works in simple cases, but when I have nested function executions I get something like that:

                 

                z_error(p1           => w_error_message,

                       p2           => w_err_type,

                       p3           => p_item,

                       p4           => p_entity_id,

                       p5           => p_entity_name,

                       p6           => nvl(p_message_id,

                error.get_short_err_id(p_type       => 'V',

                                      p_pkg_name   => 'pkg',

                                      p_no         => 1) ) );

                 

                How can I align the nested function to achieve something like that:

                 

                z_error(p1           => w_error_message,

                       p2           => w_err_type,

                       p3           => p_item,

                       p4           => p_entity_id,

                       p5           => p_entity_name,

                       p6           => nvl(p_message_id,

                                                 error.get_short_err_id(p_type       => 'V',

                                                                                     p_pkg_name   => 'pkg',

                                                                                     p_no         => 1) ) );

                 

                ?

                 

                 

                Coul you provide some more insight how does 'closestAncestorDescendent' works ? I ve got problems with understanding commets in format.arbori  file:

                 

                /**

                * It is possible that a node can have multiple ancestors with same grammatic payload.

                * For example, a column can be located within a subquery, which is nested within larger subquery.

                * The Arbori directive "/\ancestor(ancestorDescendant|[ancestor node])" translated in SQL would have look like this:

                   select node, narrowest(ancestor) from

                       (select ancestor, node from ancestorDescendant)

                   group by node;

                * Here the "narrowest" is aggregate function, which returns the parse node covering the narrowest interval.

                *

                * Likewise, it is possible for a descendant to be nested within other descendant.

                * For example, "a-b" is an expression which has nested expressions "a" and "b".

                * The next operation in the pipeline - "\/node(...)" - is choosing the widest descendant nodes, such as "a-b" in the above example.

                **/

                • 6. Re: padding function parameters in 'Custom Format'
                  Vadim Tropashko-Oracle

                  Here is what I'm getting with the amendment that you suggested:

                   

                  BEGIN

                      z_error(p1          => w_error_message,

                              p2          => w_err_type,

                              p3          => p_item,

                              p4          => p_entity_id,

                              p5          => p_entity_name,

                              p6          => nvl(p_message_id,

                              error.get_short_err_id(p_type      => 'V',

                                                    p_pkg_name  => 'pkg',

                                                    p_no        => 1

                              )

                          )

                      );

                  END;

                   

                  I can see the problem -- the alignment for the error.get_short_err_id...  has been calculated in the entire scope of the outer function (z_error) call scope, and you are absolutely right that one shouldn't just find all ancestor-descendant pairs, but those with closest ancestor which is nvl functuion.

                   

                  To elaborate this idea, if we are looking for ancestors with some criteria, and descendants with some other, then in general case we'd have something like this:

                   

                  ancestor1 <  ancestor2 <  descendant1 < descendant2

                   

                  and arbori query would return all combinations

                   

                  ancestor1, descendant1

                  ancestor1, descendant2

                  ancestor2, descendant1

                  ancestor2, descendant2

                   

                  while we are interesting in the ancestor-descendant pair "closest" to each other. To make it more concrete, in the example that you posted, this chain is

                   

                  [1,41)   function_call     <  [20,39)   arg  function_call  < [29,33)   arg

                   

                  That is the function call parse tree node spanning from token #1 (including) to the token#41 (excluding) is an ancestor of the he function call parse tree node spanning from token #20 to the token#39, which, in turn is an ancestor of the parse tree node labeled with "arg".

                   

                  forum.png

                   

                  This is where aggregate operator (with grouping) is warranted, as we want to filter only those pairs which are closest to each other.

                   

                   

                  forum.png

                   

                  This filtering has to be applied to both pairwiseAlignments5 and pairwiseAlignments6 before the final conjunction...

                  • 7. Re: padding function parameters in 'Custom Format'
                    Vadim Tropashko-Oracle

                    First let's illustrate the node filtering one more time:

                     

                    forum.png

                     

                    One useful syntactic quirk here is projecting away all the unwanted arguments (such as "ancestor"). The resulting predicate has been named conventionally as "PairwiseAlignments(node,predecessor)"; I did this to emphasize the relation signature, but otherwise the name can be anything.

                     

                    We see that the node [34,38) refers to three open parenthesis, and the two of them are unwanted. They are excluded with the aforementioned aggregate operator:

                     

                    forum1.png

                    Please note, that the "predecessor" nodes are not in the ancestor-descendant relation, this is why different aggreagate operator -- // -- has been applied. Just for the reference here is the arbori syntax:

                     

                    aggregate_operator:

                      '\' '/' identifier '(' predicate ')'     -- The node containing other nodes

                                                               -- [[   )  )

                                                               -- ^       ^

                    |

                      '/' '\' identifier '(' predicate ')'     -- The node contained within

                                                               -- [[[   )  ) )

                                                               --   ^   ^

                    |

                      '/' '/' identifier '(' predicate ')'     -- The oldest node

                                                               -- [   )[   )

                                                               --      ^   ^      

                    |

                      '\' '\' identifier '(' predicate ')'     -- The youngest node

                                                               -- [   )[   )

                                                               -- ^   ^      

                    ;

                     

                     

                    At this point you might have noticed that a row is missing due to a subtle error in your version of pairwiseAlignments6:

                     

                    forum3.png

                     

                    The final format program:

                     

                    pairwiseAlignments6:

                      [predecessor) '('

                    & [node)  arg

                    & predecessor=ancestor-1-1

                    & ancestor < node

                    & [predecessor^-1) name_wo_function_call

                    & ![node^) paren_expr_list

                    ;

                    pairwiseAlignments56:

                      //predecessor

                      (pairwiseAlignments5  | pairwiseAlignments6 | [node predecessor])

                    ;

                     

                    begin

                        z_error(p1 => w_error_message,

                                p2 => w_err_type,

                                p6 => nvl(p_message_id,

                                        error.get_short_err_id(p_type => 'V',

                                                              p_pkg_name => 'pkg',

                                                              p_no => 1

                                )

                            )

                        );

                    end;

                     

                    It makes pairwise alignments not knowing about alignments of "=>", this is why I turned the later off (which is a bug). The 2 and 1 character misalignments is a bug that I mentioned earlier, which is fixed for 18.1.

                    • 8. Re: padding function parameters in 'Custom Format'
                      vder

                      heh it would have been a lot of easier if some kind arbori manual had been available:(. I'm almost sure I understand  the first example you gave - the one with call_arguments and filtered_call_arguments, I got the difference between '/\' '\/' '//' '\\'. But I dont know how to interpret the predicate you put in parenthesis (the one in bold):

                      pairwiseAlignments56:

                        //predecessor

                        (pairwiseAlignments5  | pairwiseAlignments6 | [node predecessor])

                      ;

                      Does  pairwiseAlignments5  | pairwiseAlignments6 mean in this case sum of the two sets?

                      What does exactly mean the last factor: [node predecessor]? I haven't seen anywhere notation with two square brackets.

                       

                      • 9. Re: padding function parameters in 'Custom Format'

                        heh it would have been a lot of easier if some kind arbori manual had been available:

                        Gee - I did a simple web search for 'arbori syntax' and the first three links are to references that were written by the person have been talking to.

                        https://vadimtropashko.files.wordpress.com/2017/02/arbori2.pdf

                        • 10. Re: padding function parameters in 'Custom Format'
                          Vadim Tropashko-Oracle

                          As you might have already noticed, the database field heavily influenced the Arbori design. The declarative querying with relational algebra-like operators, the queries manipulating objects which are shaped as relational tables, etc. The square bracket syntax for relational table is immediate from the following analogy

                           

                          [ename job]

                          SCOTT ANALYST

                          FORD  ANALYST

                           

                          Therefore, the "[node predecessor]" is just an empty relation with 2 attributes.

                           

                          Next, database theory strongly suggests that the union operator should applied to a pair of relations with the same signature. What pairwiseAlignments6 | [node predecessor] can possibly be? Or, to return back to database field, what is the result of union of two mismatching relations

                           

                          [ename deptno]

                          SCOTT 10

                          FORD  20

                           

                          and

                           

                          [deptno dname]

                          20     ACCOUNTING

                          30     ANALYST

                           

                          ? It is

                           

                          [deptno]

                          10

                          20

                          30

                           

                          This generalized union operator (AKA inner union), combining the two operations, union and projection, has been rediscovered about a decade ago (it is also known as SQL’s UNION CORRESPONDING, and was available in IBMs ISBL/BS12 as plain UNION). It generated some interest in database and logic research community:

                           

                          https://scholar.google.com/scholar?cites=13940260893242805208&as_sdt=2005&sciodt=0,5&hl=en

                           

                          You are right that some obscure part of the syntax such as the inner union, and aggregate operators are better be documented (they are not yet). On the other hand, introducing the closest ancestor-descendant relation syntax into Arbori would have avoided the usage of aggregate operators, and would be much more intuitive -- in my querying parse trees experience it is always the closest ancestor-descendant that is wanted, and never conventional ancestor-descendant...

                          • 11. Re: padding function parameters in 'Custom Format'
                            vder

                            Hi rp0428, you can believe me or not, but in last few days I've read everything that Vadim published on his blog or here, but from my point of view all these cover mostly basic concepts and nothing as fancy as  /\//\\// .  So if you still like increase your post counter here be my guest, but I would have been more than happy if you had brought any value to the discussion.

                            • 12. Re: padding function parameters in 'Custom Format'
                              vder

                              ok now everything is clear. I know I'm repeating myself, but Thanks a lot for help.