2 Replies Latest reply on Sep 16, 2020 6:29 AM by Philipp Salvisberg

    How to use Arbori rule "dontFormatNode" in SQLDev 20.2?

    Philipp Salvisberg

      Dear SQLDev team,

       

      I tried to extend the query for the Arbori rule "dontFormatNode" as follows:

       

      dontFormatNode: [node) numeric_literal | [node) as_alias
      ->
      ;
      

       

      However, I did not manage to change the the query of "dontFormatNode" to have an effect on the formatting result. So it looks, I do something wrong or don't understand the rule or both.

       

      So, what is the intention of this rule and how does it work?

       

      Thank you,

      Philipp

        • 1. Re: How to use Arbori rule "dontFormatNode" in SQLDev 20.2?
          Vadim Tropashko-Oracle

          The standard format program dontFormatNode rule is:

           

          dontFormatNode: [node) numeric_literal

          ->

          ;

           

          It prescribes not to format any numeric literals. For example, consider the following input

           

          INSERT INTO employees (employee_id, last_name, email, 
                hire_date, job_id, salary, commission_pct) 
             VALUES (207, 'Gregory', 'pgregory@example.com', 
                sysdate, 'PU_CLERK', 1.2E3, NULL);
          

           

          The salary entry is the numeric literal that should not have any accidental spaces (bug previously reported on the forum).

           

          Upon discovering the nodes labeled as numeric literals, it would collect the node positions as follows:

           

          public void dontFormatNode( Parsed target, Map<String,ParseNode> tuple ) {
              ParseNode node = tuple.get("node");
              for (int i = node.from; i < node.to; i++) {
                  unformattedPositions.add(i);
              }
          }
          

           

          In this example the unformattedPositions would become [32, 33, 34, 20, 30, 31]. Consequently, at position 30 the main processing loop would have the following condition satisfied

           

              if( unformattedPositions.contains(pos) && unformattedPositions.contains(pos+1)
                  || root.topLevel != null && root.coveredByOnTopLevel(pos+1) == null ) {
                  output.append(t.content);
                  if( t.type != Token.WS && t.type != Token.INCOMPLETE && t.type != Token.COMMENT  && t.type != Token.LINE_COMMENT 
                   && t.type != Token.MACRO_SKIP && t.type != Token.SQLPLUSLINECONTINUE_SKIP  ) 
                       pos++;
                  prior = t;
                  continue;
              }
          

          and would proceed to the next token without adding any spacing.

           

          Falsifying this rule would produce the following output:

           

          INSERT INTO employees (
              employee_id,
              last_name,
              email,
              hire_date,
              job_id,
              salary,
              commission_pct
          ) VALUES (
              207,
              'Gregory',
              'pgregory@example.com',
              sysdate,
              'PU_CLERK',
              1.2 E 3,
              NULL
          );
          
          
          

           

          Here is a variation of this rule to test on this example:

           

          dontFormatNode: [node) values_clause

          ->

          ;

          • 2. Re: How to use Arbori rule "dontFormatNode" in SQLDev 20.2?
            Philipp Salvisberg

            Hi Vadim,

             

            Thank you very much for your detailed explanation and the examples. I reproduced them and the question is for me fully answered. I understand now how dontFormatNode works.

             

            Thanks again,

            Philipp

             

            P.S. Just some related notes/comments

             

            When I format this (all lowercase, except the numeric_literal)

             

            insert into employees (employee_id, last_name, email,   
                  hire_date, job_id, salary, commission_pct)   
               values (207, 'Gregory', 'pgregory@example.com',   
                  sysdate, 'PU_CLERK', 1.2E3, null); 
            

             

            with this setting (all other values are default)

             

            dontFormatNode: [node) values_clause
            ->
            ;
            

             

            then the formatted result is:

             

            INSERT INTO employees (
                employee_id,
                last_name,
                email,
                hire_date,
                job_id,
                salary,
                commission_pct
            ) VALUES (207, 'Gregory', 'pgregory@example.com',   
                  sysdate, 'PU_CLERK', 1.2e3, null);
            

             

            Interesting are line 9 and 10. The whitespaces within the values_clause are left unchanged, but the values keyword was changed to uppercase and the the numeric_literal 1.2E3 was changed to lowercase. The case of literals can be controlled via the identifiers rule.

             

            It looks like the very first position (in this case 18) is always processed. This is good for processing leading whitespaces, but I'm not so sure for regarding the change case.

             

            Here's another example. When I format

             

            create table t (c1 varchar2(10)) as select DUMMY from DUAL;
            
            select DUMMY from DUAL;
            

             

            with this setting (all other values are default)

             

            dontFormatNode: [node) create_table
            ->
            ;
            

             

            then the formatted result is:

             

            CREATE table t (c1 varchar2(10)) as select DUMMY from DUAL;
            
            SELECT
                dummy
            FROM
                dual;
            

             

            In this case the complete create table statement is left unchanged, except the first position 0. The create keyword changed to uppercase. This can be controlled by changing the keyword case to "keep unchanged".