5 Replies Latest reply on Feb 13, 2020 6:26 PM by thatJeffSmith-Oracle

    Request for additional automatic refactoring capabilities

    mathew-butler
    mathew-butler Feb 1, 2020 2:56 PM

      I like using SQLDeveloper. To make it a more powerful tool I’d like to see some enhancement to the tools automatic refactoring capabilities.

       

      “Refactoring (noun): a change made to the internal structure of software to make it easier to understand and cheaper to modify without changing its observable behavior.” - Martin Fowler https://martinfowler.com/bliki/DefinitionOfRefactoring.html

       

      The value of automatic refactoring is that it is automated and so low risk. As a result a developer can have high confidence that she will not change program behaviour as a result of careful changes.

       

      Refactoring is useful when looking to improve a programs structure to make it more readable; separating out dependencies to reduce cognitive load; and when moving an implementation towards a more loosely coupled, highly cohesive technical architecture.

       

      Personally, I also find I regularly restructure and re-organise my automated test suites - moving tests between packages and renaming functions and procedures. I use a programmatic test framework called utPLSQL (http://utplsql.org) so test and treated as just more PLSQL code that automatic refactoring rules could be applied to.

       

      Automated refactoring can also be useful when dealing with existing code that has no automated tests. A first step to introduce a test suite can sometimes be to apply refactoring steps to the code to expose testable pieces. Without automated refactoring this can be a risky manual task. It’s better to rely on an IDE with good automated refactoring support and significantly reduced regression risk.

       

       

      For all refactoring, I’d like SQLDeveloper to understand my project (all types of database objects with DDL capture in files held in a local filesystem based workspace, with each files revision history stored in configuration control e.g. subversion or git ) and make any refactoring change consistent throughout my project.

       

      Here are the refactoring last I have come up with - I’m sure there are more, but these are the one I think the SQLDeveloper community might find most useful;

       

      1. Extract selected functions and procedures to new package, or type
      2. Move selected functions and procedures to an existing package, or type
      3. Create a new type from selected text
      4. Rename procedure or function (and have all references in project updated)
      5. Change procedure/ function signature to enable adding of additional parameters with default value and addressiall call sites across project
      6. Introduce Indirection - creates a new function/ procedure that delegates to the highlighted procedure/ function and amends all call sites in project

       

      What does the community think? Would they find the above useful? Does the SQLDeveloper team have appetite to support extending this capability?

       

      Hi thatJeffSmith-Oracle - this follows a brief twitter exchange we had. Please let me know if there's any more clarification needed and I’ll come back and update.

       

      Thanks in advance for any responses. Also thanks to the SQLDeveloper team for producing such a great tool, and having such a great focus on the developer user community.

       

      Mat.

      • 284 Views
      • Tags:
        • 1. Re: Request for additional automatic refactoring capabilities
          Philipp Salvisberg
          Philipp Salvisberg Feb 3, 2020 8:50 AM (in response to mathew-butler)

          Hi Mat,

           

          I agree. However, to make the proposed refactorings successful we have to agree on some basic conditions.

           

          a) the source

           

          What is used as source for the code analysis? For example, to apply the refactoring #4 (rename procedure or function with all its dependencies within the project scope) we have to find the affected views, triggers, functions, procedures, package units. The Oracle Data Dictionary can provide the data for this analysis (if PL/Scope is not enabled it's just a bit more work for the built-in parsers of SQL Developer). But the Oracle Data Dictionary has no idea what our project scope is. A schema? A part of a schema? A set of schemas? A set of schema parts? There is no mapping of "project" to "oracle database objects" within Oracle SQL Developer. Something other IDEs manage with the help of workspaces and projects.

           

          Without a project/workspace concept for SQL Developer the only viable option for the source is probably the code stored in the Oracle database.

           

          b) the target

           

          Assuming that every object that can be updated via a DDL with the current connection is part of the refactoring scope could be a solution. But this leads to the next problem. What do I have to extract from the database to update my files managed via a VCS such as Git. - I know there is software to manage that such as GitOra, but this is more a workaround than a solution IMHO.

           

          But again, without a project/workspace concept for SQL Developer there is no way to define a sensible scope for the refactoring. Ok, you could restrict the scope to the current user (or current schema) and I'm sure that this will good enough for some applications, but certainly not for all.

           

          As long as the target of a refactoring is the current editor, refactoring is quite simple to implement in SQL Developer. But for a wider scope SQL Developer requires a project/workspace concept probably with some integration with custom or common database migration frameworks such as Flyway or Liquibase.

           

          IMHO a project/workspace concept for SQL Developer is a prerequisite for the refactorings you are suggesting. I'd really love to see that happening.

           

          Cheers,

          Philipp

          • 2. Re: Request for additional automatic refactoring capabilities
            mathew-butler
            mathew-butler Feb 4, 2020 10:34 AM (in response to Philipp Salvisberg)

            Hi Phillip - thanks for engaging with this post.

             

            You make some very good points. I can only give my view, and I'll leave it to the SQLDeveloper team to comment on the practicalities.

             

            For a):

             

            I'd imagined a model similar to that used for other refactoring IDE, where the refactorings are applied the source files that represent the deployed schema. If this isn't an existing capability, then SQLDeveloper could maybe restrict the scope of impact e.g. define a list of items against which a target refactoring should apply. In the case of moving items between packages or to a new package this seems to be a much smaller scope.

             

            For b)

             

            Maybe this exposes some assumptions on my part in terms of how developers work. For me, all code change is made in the master files and deployed. So my mental model is that any refactoring would do the same.

             

            I'd lean away from bringing in concerns for how to migrate change, and leave this up to the developer. Not everyone uses an external tool to support migration, and I wouldn't like SQLDevleoper to introduce a restriction on how migration should work. As an aside, and as an example, for my project we have something we've written ourselves that achieves this. I wouldn't want my IDE to dictate how migration should be implemented.

             

            I'd also like to see more of the project/workspace concept in SQL Developer - this happens to be how I have always worked; in files and then apply changes to database. It would be great if this was a supported concept in the broadest sense irrespective of refactoring capability.

             

            I wonder if there is anyone from the SQL Developer team who might be able to comment on the practicalities of i) project/workspace support and ii) extending refactoring capabilities (even if this means adding some restrictions to simplify the implentation.

             

            Cheers,

            Mat.

            • 3. Re: Request for additional automatic refactoring capabilities
              thatJeffSmith-Oracle
              thatJeffSmith-Oracle Feb 4, 2020 2:37 PM (in response to mathew-butler)

              adding additional refactoring, definitely doable in short term

               

              adding a project interface, that would be a long term project, that's not currently on our release calendar

              • 4. Re: Request for additional automatic refactoring capabilities
                Paavo
                Paavo Feb 13, 2020 3:32 PM (in response to thatJeffSmith-Oracle)

                Hi

                 

                How about having refactoring in Datamodeler which has sort of "project" in place?
                I would love to be able to show e.g. Apex gui pages (should be fetchable) with their dependencies to underlying db objects: gui pkgs and finally db pkgs + tapis before the actual views and tables.

                Then it would give the scope for the refactoring to be done per model?

                 

                rgrds Paavo