Monday, November 21, 2016

Start Gitora as a Windows service

In our company, we use Gitora on a shared development database.
So, every developer needs to access this shared Gitora instance.

By default, Gitora does not run as a service, so in order for our developers to access Gitora, we need to log in to the server, start Gitora and leave the session running. Obviously this is not ideal.

So we want to run Gitora as a windows service (we installed Gitora on a Windows 12 server).
This blog shows you how to do this.

Download missing Tomcat files 


In order to create the windows service, we need 2 files that are not present in the Gitora installation.
So first we need to download the correct Tomcat version.
For Gitora 2.0 this is version 7.0.69.
You can download it from here:
http://archive.apache.org/dist/tomcat/tomcat-7/v7.0.69/bin/
Download the 'apache-tomcat-7.0.69-windows-x86.zip' file.

Unzip and copy 2 files from /BIN
service.bat
tomcat7.exe

Copy these 2 files to your Gitora installation \apache-tomcat-7.0.69\bin\ folder.

Modify service.bat


We need to edit the service.bat file we just copied to prepare it for the service creation.

Change the service name for the Gitora service

By default, the service is installed with a name starting with 'Apache Tomcat 7.0 '.
If you want a nicer name for your service, we need to edit the service.bat file,
Find the section :checkUser and remove the text 'Apache Tomcat 7.0' from the DISPLAYNAME, like this:


Add the PATH environment variable

Gitora also needs a correct PATH environment variable, so we need to add an -- Environment parameter to the service.bat file. The PATH points to your Gitora installation \Git\bin folder, in my case it is:


Set JvmMs and JvmMx values

Set the values for  --JvmMs and  --JvmMx parameters to the desired values.
Default for Gitora is 1024 (as in Gitora tomcat-startup.bat).


Auto start service

Add --Startup to have the service start automatically.


With the above changes, the service.bat section with parameters will look like this:

NOTE: Make sure you add the ' ^' after the startup parameter if it is not the last one. 

Create the service


The easiest way to create the service is to make a bat file for the command we need to run.
Copy the tomcat_startup.bat (it already has some commands we need) and rename it for example to install_service.bat

Edit the new install_service.bat file.
Keep the lines that set the CATALINA_HOME and JRE_HOME and remove the rest.

Add the command to install the service to the service.bat file we just edited.
(cmd /k will keep the command window open so you can check the result)
It should result in something like this (modify the service name, i set it to 'Gitora-DEV' in this example):


Double click the install_service.bat file to create the service. You should see something like this:


Start the service


Now the service is running and you can safely exit the server session or reboot the server without having to think about starting Gitora again.

Have fun!

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!

A fast way to load a Gitora repository with many objects

I find creating a Gitora repository for a large schema or for many schemas via the Gitora user interface a little tedious.
Luckily there is the Gitora API to help us out.

So here is a little example of how to bulk load your objects into a gitora repository.

First create your repositories in the Gitora UI..
Ten use this script (and obviously replace 'YOUR_SOURCE_SCHEMAS' and  'TARGET_REPOSITORY_NAME').

begin
   api_gitora.login('username','password');
   
   for i in (select o.object_name
             ,      o.object_type
             ,      o.owner
             from  all_objects o
             where o.object_name not like 'Z_%'         -- some rules for which objects to load.
             and   o.object_name not in ('QMS_EXEC_SQL'
                                        ,'SC_SCRIPTEXECUTER'
                                        ,'CG$ERRORS'
                                        ,'BDF_SCRIPTEXECUTER'
                                        )
             and   o.object_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
                                    )
             and o.owner in ('YOUR_SOURCE_SCHEMAS')
             order by o.owner
             ,        o.object_type
             )
   loop
       begin
          api_gitora.adddbobject(in_schema_cd   => i.owner
                                ,in_type_cd     => i.object_type
                                ,in_name_tx     => i.object_name
                                ,in_reponame_tx => 'TARGET_REPOSITORY_NAME' -- to which respository need the objects go
                                );

          -- If you want lto log what is loaded, remember to increase your dbms_output buffer!
          dbms_output.put_line('inserted: '||i.object_type||' '||i.owner||'.'||i.object_name);

       exception
          when others 
          then 
             dbms_output.put_line('error on: '||i.object_type||' '||i.owner||'.'||i.object_name||'   sqlerrm: '||sqlerrm);
                                                                                        
       end;    
   end loop;
end;
Quick and easy. Have fun!

Saturday, November 19, 2016

Automatically log in to Gitora when connecting to Oracle

In order to modify database objects that are managed by Gitora source control, the user first needs to log in to Gitora with a user name and password.
Usually this is no problem, but we lately encountered a case where this was an problem.

To be more specific, we still use Oracle Designer (yes, i know..) to maintain our Database Models. From Designer we generate database changes to our development database that is managed by Gitora.
Almost all changes from Designer can be generated as scripts and you can add the Gitora login there,
but we also generate TAPI packages from Designer and these are generated directly to the database without the option of generating scripts. So that was a problem.
Also having to manually add the Gitora login to all scripts is quite tedious.

The solution is to use a database after logon trigger.
But we had some more requirement:

  • log in to Gitora as an actual user, not a database user
  • track what applications are used to log on
  • log succesfull and failed login attemps

Here is how we solved it.

First we need a table to match the Gitora username and passwords with the possible Operating System user names.
(I will not secure the passwords in this example, i leave that up to you if you need it)

create table GITORA_USERS_MATCH
(
  os_user     VARCHAR2(64) not null,
  gitora_user VARCHAR2(64) not null,
  gitora_pw   VARCHAR2(64) not null
);

alter table GITORA_USERS_MATCH
  add constraint GUH_PK primary key (OS_USER);

Then a second table to log successfull and failed login attempts.

create table GITORA_LOGON_TRG_LOG
(
  logon_message VARCHAR2(512) not null,
  logon_date    DATE not null
);

Now we can create a trigger.

create or replace trigger gitora_auto_logon_trg
   after logon on database
declare
   l_program     varchar2(100);
   l_gitora_user gitora_users_match.gitora_user%type;
   l_gitora_pw   gitora_users_match.gitora_pw%type;

   cursor c_gitora_user
   is
      select guh.gitora_user
      ,      guh.gitora_pw
      from tmi_sys.gitora_users_match guh
      where upper(guh.os_user) = upper(sys_context('userenv','OS_USER'))
      ;

begin
   /* Get the program that is connecting.
      For example:
     'dwfde61.exe'      -- Oracle Designer Generator
     'sqlplusw.exe'     -- Oracle sqlplus
     'sqlplus.exe'      -- Oracle sqlplus
     'SQL Developer'    -- Oracle SQL developer
     'Toad.exe'         -- Toad
     'plsqldev.exe'     -- PL/SQL Developer
     'JDBC Thin Client' 
   */
   select program
   into   l_program
   from   v$session
   where  audsid=sys_context('USERENV','SESSIONID');
   
   insert into tmi_sys.gitora_logon_trg_log 
      (logon_date
      ,logon_message
      ) 
   values 
      (sysdate
      ,'logon: os_user: '''||sys_context('userenv','OS_USER')||''' program: '||l_program
      );

   open  c_gitora_user;
   fetch c_gitora_user
   into  l_gitora_user
   ,     l_gitora_pw;
   close c_gitora_user;

   if l_gitora_user is null
   then
      -- log failure to match os_usr to Gitora user
      insert into tmi_sys.gitora_logon_trg_log 
         (logon_date
         ,logon_message
         )
      values 
         (sysdate
         ,'os_user name '''||sys_context('userenv','OS_USER')||''' cannot be matched to a Gitora user)'
         );
   else
      -- try to log on to Gitora. 
      begin
         gitora.api_gitora.login(l_gitora_user,l_gitora_pw);
      exception
         when others
         then
            -- just log, no need to raise
            insert into tmi_sys.gitora_logon_trg_log 
               (logon_date
               ,logon_message
               ) 
            values 
               (sysdate
               ,'Login to Gitora failed for username '||l_gitora_user
               );
         end;
   end if;
exception
  when others
  then
     -- this trigger should never cause a user to not be able to log in to the database.
     -- It only needs to check if an os_name can be linked to a Gitora user.
     null; 
end; 

With this in place, you can connect to the database with any tool you want and log in to Gitora automatically. This makes working with Gitora much easier.

Have fun!

Using Gitora in PL/SQL Developer dual or multi session mode

While browsing around today i saw the same question about how to use Gitora in PL/SQL Developer on the Allround Automations forum as well as on the Gitora Q&A site.

Besides answering this question on both sites, as i just started this blog, i thought i could make a blog about it to get some practice, so  here it is.

For those not familiar with Gitora, it s a version control system for PL/SQL.
You can read more about it on www.gitora.com

PL/SQL Developer single, dual or multi session mode

You can run PL/SQL Developer in 3 modes:  single session, dual session or multi session.

In single session mode, all program windows use the same database connection, so using Gitora in this mode is straightforward. just open a command window and execute
api_gitora.login('username','password');

In dual-session or multi-session mode, windows use different connections, so we need another approach. There are three ways to use Gitora in dual or multi-session.

1) Use Afterconnect.sql

In the PL/SQL Developer installation directory there is a file called Afterconnect.sql.
This script is executed whenever a new connection to the database is made.
By default the sql file is empty, so just add something like:
begin
   api_gitora.login('username','password');
end;

2) Use a database after logon trigger

You can create a database after logon trigger that will log you on to Gitora when a connection is made. This approach will not only work for PL/SQL developer but for any application that can make a connection to the database. I use this option for our development team using Gitora.
Check how to do this here.

3) Create a custom menu button

You can create a menu button that will execute a script in the active connection.
Here are the steps how to do this:
- create a script file with api_gitora.login, for example on d:\Gitora_login.sql
- in menu 'Tools' choose 'Configure Tools'
- in the dialog, click the little button n the right side 'New'
- name your tool, for example 'Gitora login'
- fill in some other details like below


- close the dialog

The second tab 'Menu' should allow you to place the tool in a specific menu directly, but it did not work for me, but there is another option.
Right click anywhere on the gray area next to a menu, you will get a popup dialog.
Choose 'Customize'



The new tool you created is under 'Commands', and scroll to the bottom to see 'User defined tools'
No you can drag the 'Gitora logon' tool from the right onto a menu in PL/SQL developer.



Now, when you need to logon to Gitora, you can just click the button.

Have fun using Gitora!