Sunday, November 20, 2016

Automatically add new database objects to Gitora

When working with Gitora as source control system, you need to add the database objects to Gitora manually either in the Gitora UI, or by using the Gitora PL/SQL API.

The problem with this manual method is that while developing you will at one point forget to add a new object to Gitora. Detecting that you actually have forgotten to add something to Gitora can be difficult, especially when your database has many schema's and objects. You would have to check each Gitora repository for each type of object. 

Another problem with the manual approach is that when you have an automated  process that pulls changes from one database to the other (i.e. Continuous Integration), based on what has been checked in to Gitora, not adding objects to Gitora could cause problems further down the line as well. Either the CI process will detect the problem (compilation issues), or worse, your application will fail at runtime when calling something that isn't there.

A lot better workflow would be that Gitora would recognize new objects being created, check some rules to determine if this object needs to be in version control and if so, add it to Gitora automatically.

We tried to build this using the existing API from version 2.0 but did not quite manage to get it to work. 
So we got in touch with Gitora support to see if they could solve this.
After explaining what we wanted and why, the guys at Gitora delivered a new API which made this possible:
api_gitora.addOrModifyCurrentDBObject

NOTE: 
the new api_gitora.addOrModifyCurrentDBObject procedure is not yet in the current Gitora 2.0.1 download. It probably will be added to the next update of Gitora. If you would like to have it sooner, get in touch with Gitora Support.

With this API in place, the only thing to do is modify the gitora.go_ac trigger (the trigger that tracks the 'after create on database' events)
This is an example.

CREATE OR REPLACE TRIGGER go_ac
  AFTER
   CREATE
  ON DATABASE
declare
  function object_must_be_in_gitora
    return boolean
  is
  begin
    -- This function checks if the object needs to be added to Gitora.
    if  ora_dict_obj_owner in ('SCHEMA_A','SCHEMA_B') -- your list of schemas you want Gitora to monitor
    and ora_dict_obj_name not like 'Z_%'              -- naming convention: objects starting with 'Z_' do not go to version control (temporary / test stuff)
    and ora_dict_obj_name not like 'P$%'              -- some temporary stuff created by Oracle
    and ora_dict_obj_type in (api_gitora.type_function
                             ,api_gitora.type_package
                             ,api_gitora.type_procedure
                             ,api_gitora.type_synonym 
                             ,api_gitora.type_trigger
                             ,api_gitora.type_view
                             ,api_gitora.type_sql_type
                             )
    then 
      return true;
    else
      return false;
    end if;
  end;

begin
  if ora_dict_obj_owner=sys_context('USERENV','CURRENT_SCHEMA') 
  then
    null;
  else
    if bdf_sc_session.isenabled='Y' 
    then
      if object_must_be_in_gitora
      then
          -- add or checkout current object
          -- In our case, each schema has an own Gitora repository with the same name
          -- Otherwise add some logic to match the schema with the correct Gitora repository (in_reponame_tx)
          api_gitora.addormodifycurrentdbobject(in_schema_cd   => ora_dict_obj_owner
                                               ,in_type_cd     => ora_dict_obj_type
                                               ,in_name_tx     => ora_dict_obj_name
                                               ,in_reponame_tx => ora_dict_obj_owner 
                                               );                                    
      else
        -- if any object is not needed according to the object_must_be_in_gitora function, it still can be manually added to Gitora.
        -- In that case we only need to checkout if it exists
        api_gitora.modifydbobject(in_schema_cd   => ora_dict_obj_owner
                                 ,in_type_cd     => ora_dict_obj_type
                                 ,in_name_tx     => ora_dict_obj_name
                                 );
      end if;
    end if;
  end if;
end;

Together with auto logon to Gitora (see my blog here about  how to do that) this makes using Gitora really easy. The only think that you need to do manually is commit your checked out objects in Gitora.

Have fun!

No comments:

Post a Comment