7 Replies Latest reply on Mar 1, 2017 10:15 PM by Vadim Tropashko-Oracle

    4.2EA2 Formatter: Newline before and/or after parenthesis

    ManishHathi

      Hello,

       

      How can I get a Newline before and/or after parenthesis start/end? In the 4.1 version, under preferences for formatting, there was an option for Line Breaks "For brackets in AND, OR..." as well as "For subselect brackets".

       

      These preference settings are not visible in 4.2EA2 and I don't see another way.

       

      Thanks,

       

      Manish

        • 1. Re: 4.2EA2 Formatter: Newline before and/or after parenthesis
          ManishHathi

          Hello,

           

          Any comments from the product-team?

           

          Thanks,

           

          Manish

          • 2. Re: 4.2EA2 Formatter: Newline before and/or after parenthesis

            Really? You expect a response within 5 minutes?

             

            If you have an urgent problem you need to hire a consultant. The forum is supported by volunteers who are located all around the world.

             

            It is not only a weekend but many of those that might want to help are sound asleep.

             

            Please adjust your expectations to match that above reality.

            • 3. Re: 4.2EA2 Formatter: Newline before and/or after parenthesis
              ManishHathi

              You sir, obviously didn't read the original post. Or are answering to the wrong one.

               

              Because if you did, you would know that the question was asked on  Feb 9, 2017, so NO, it wasn't 5 mins ago. And it wasn't a "consulting" question, merely asking the product team as many others have done before about what they thought about the question. That is all.

               

              Manish

              • 4. Re: 4.2EA2 Formatter: Newline before and/or after parenthesis
                thatJeffSmith-Oracle

                the formatting preferences are pretty much locked in now

                 

                formatter42.png

                 

                if the GUI doesn't provide what you need, you can shape the formatting using the custom formatting interface, which Vadim Tropashko-Oracle  demonstrates here. I'm pretty certain he will be wiling to help those get their formatting just the way they want.

                 

                I realize this marks a drastic chance from the previous version, however:

                 

                • the previous version was unable to cope with many of the community's requests and needs
                • the previous version was unable to handle newer oracle sql and plsql syntaxes
                • the previous version was very difficult to maintain
                • the newer version is much more flexible
                • we'll continue to enhance it as we receive feedback
                • being able to set formatter preferences by supplying your own code samples should make things easier
                • the custom formatting will let change many things not surfaced in the GUI preferences
                • 5. Re: 4.2EA2 Formatter: Newline before and/or after parenthesis
                  Vadim Tropashko-Oracle

                  The 4.2 feature redesign introduced a formal language to specify formatting rules. For example, let's fine tune the code formatted with default settings

                   

                  SELECT

                      x

                  FROM

                      t

                  WHERE

                      (

                          ( 1 + 1 ) = 2

                      ) AND (

                          22222 * 3 = 44

                      ) AND

                          x IN (

                              SELECT

                                  *

                              FROM

                                  dual

                          )

                   

                  First, if we disable all the conditions

                   

                  /*| [node) condition  &  [node-1) '('

                  | [node) condition  &  [node-1) 'WHEN'

                  | [node) condition  &  [node^)  compound_condition & ![node) compound_condition

                  | [node) condition  &  [node^) having_clause & ![node) compound_condition

                  | [node) condition  &  [node^) hierarchical_query_clause & ![node) compound_condition

                  | [node) condition  &  [node^) on_using_condition & ![node) compound_condition

                  | [node) condition  &  [node^) where_clause --& ![node) compound_condition*/

                   

                  then the code formats into

                   

                  SELECT

                      x

                  FROM

                      t

                  WHERE ( ( 1 + 1 ) = 2 ) AND ( 22222 * 3 = 44 ) AND x IN (

                      SELECT

                          *

                      FROM

                          dual

                  )

                   

                  How about not indenting the subquery parts (column and table lists)? The column wildcard is handled by disabling

                   

                  --| [node) '*'  &  [node) select_list

                   

                  while the table reference is indented via

                   

                  | [node) table_reference & ![node^^-1) '('

                   

                  Here the additional condition stipulates the predecessor (formally, "-1") of the grandparent parse node (formally, "node^^") not to be an open parenthesis, so that only table references in the subqueries are affected.

                   

                  Next, the entire subquery

                   

                      SELECT

                          *

                      FROM

                          dual

                   

                  is indented via condition

                   

                  | [node) subquery

                            &  ![node^) sql_statement

                            &  ![node^) stmt

                            &  ![node^) unlabeled_nonblock_stmt 

                            &  ![node^) table_properties --or relational_table?

                            &  ![node^) single_table_insert 

                   

                  We can just require predecessor of subquery (foramlly, "node-1") not to be an open parenthesis.

                   

                  With these changes we get:

                   

                  SELECT

                      x

                  FROM

                      t

                  WHERE ( ( 1 + 1 ) = 2 ) AND ( 22222 * 3 = 44 ) AND  x IN ( SELECT * FROM dual )

                   

                   

                  Finally adding the rule

                   

                  | [node) in_condition

                   

                  produces:

                   

                  SELECT

                      x

                  FROM

                      t

                  WHERE ( ( 1 + 1 ) = 2 ) AND ( 22222 * 3 = 44 ) AND

                      x IN ( SELECT * FROM dual )

                  • 6. Re: 4.2EA2 Formatter: Newline before and/or after parenthesis
                    ManishHathi

                    Vadim,

                     

                    So in your example above, if the only change I wanted to make was to have an  new-line "before" the " (" in the "AND (" text, so that the output becomes:

                     

                    AND (

                        22222*3=44

                      )

                     

                    to

                     

                    AND

                    (

                        22222*3=44

                      )

                     

                    And all nested (, ) pairs should start on a new line. How can that be accomplished in the custom format? I gotta tell you, the custom format syntax is impressively scary.

                     

                    Thanks,

                     

                    Manish

                    • 7. Re: 4.2EA2 Formatter: Newline before and/or after parenthesis
                      Vadim Tropashko-Oracle

                      In 4.2EA2 (the "selNodes" predicate has been renamed since EA2):

                      forum_custom_format.png

                      Here is the code to copy and paste:

                       

                      -- add this

                      compositeExprInWhereClause: ancestor < node &

                          [node) compound_condition & [ancestor) where_clause

                      ;

                      -- add this

                      closestCompositeExprInWhereClause: /\node(/\ancestor(compositeExprInWhereClause))

                      ;

                      -- replace "selNodes" with this:

                      selNodes: isolatedNodes |  descendantNodes

                                 | closestCompositeExprInWhereClause

                      ->

                      ;

                       

                      Some explanation how this code works is warranted.

                       

                      The "compositeExprInWhereClause" predicate specifies all the parse nodes labeled as "compound_condition" together with all the ancestor nodes which are "where_clause". Unfortunately it finds more than we want. For example, the entire expression  "( ( 1 + 1 ) = 2 ) AND ( 22222 * 3 = 44 )" is "compound_condition" too, but we don't want to indent it. Hence, the next step, a SQL aggregate query analog, where we filter only those nodes which are not nested in the other nodes (and all the where clauses which are not nested in the other where clauses). The resulting predicate is binary, but the disjunction in the selNodes intersects the attributes. "isolatedNodes" doesn't have "ancestor" attribute, this is why it is projected away from the result -- "selNodes". This is roughly equivalent to the following SQL query:

                       

                      select node from isolatedNodes

                      union

                      select node, ancestor from descendantNodes

                      union

                      select node, ancestor from closestCompositeExprInWhereClause

                       

                      where we pretend that SQL allows disjunctions with mismatched sets of attributes.