Saturday, February 4, 2017

How to set up multiple Gitora installations on the same server

When you use Gitora on multiple databases (DEV, CI, TEST) you need to have multiple Gitora installations, one for each database.

There are a few things you need to change in Gitora if you want to have have multiple Gitora client (Tomcat) installations on the same machine.

First is the HTTP port. You can choose the port you want to run Gitora on during the Gitora installation, so that is no problem.



But notice the "Note: Port no 8005 and 8009 must not be used by a service or application".
So we need to change some ports afterwards to make it work.

Here is how to do that.
In this example we will set up 2 Gitora installations, DEV and TEST.

Install Gitora DEV 

DEV will run on port 8080 and will use all default ports, so we do not have to change anything after the Gitora installation wizard.

Tomcat server.xml

Ports 8005 and 8009 are defined in the server.xml file, with me the file is located at:
C:\Program Files (x86)\Gerger\GitoraServer-DEV\apache-tomcat-7.0.69\conf\server.xml

Also, the port 8080 is in here and one more, port 8443 (redirectPort)
..
<Server port="8005" shutdown="SHUTDOWN">
..
<Connector connectionTimeout="20000" port="8080" protocol="HTTP/1.1" redirectPort="8443"/>
..
<Connector port="8009" protocol="AJP/1.3" redirectPort="8443"/>
..

As we run the DEV instance with default, no need to change anything here now.

Gitblit default.properties

Another interesting port is the Git deamon port in the Gitblit default.properties file. With me the file is located at:
C:\Program Files (x86)\Gerger\GitoraServer-DEV\gitblit-config\defaults.properties
# port for serving the Git Daemon service.  <= 0 disables this service.
# On Unix/Linux systems, ports < 1024 require root permissions.
# Recommended value: 9418
#
# SINCE 1.3.0
# RESTART REQUIRED
git.daemonPort = 9418

# The port for serving the SSH service.  <= 0 disables this service.
# On Unix/Linux systems, ports < 1024 require root permissions.
# Recommended value: 29418
#
# SINCE 1.5.0
# RESTART REQUIRED
git.sshPort = 29418

Install Gitora TEST 

TEST will run on http port 8081 (you can specify it during install) and we will manually change the rest of the ports in the config files.
First stop Gitora DEV, otherwise the installer cannot continue when it detects ports 8005 an 8009 are in use.

change Tomcat server.xml

Open server.xml (see file location mentioned above) and change the ports, for example.
port 8005 to 8006
port 8009 to 8010
port 8443 to 8444



change Gitblit default.properties

Open Gitblit default.properties (see file location mentioned above)  and change the Git deamon port and SSH port.
daemonPort from 9418 to 9419
sshPort from 29418 to 29419

At this point you are able to run the 2 Gitora instances at the same time on the same machine without any port conflicts and all standard functions should work.

Remote pull from DEV to TEST

With the setup we did so far you are already able to remote pull from the DEV to the TEST repository.
This works by default, because the Git deamon port on DEV has not been changed.
The pull will look at the default port 9418 (for http connections).

Remote pull from TEST to DEV

But pulling from TEST to DEV will not work, because we changed the default Git deamon port on TEST from 9418 to 9419.

In order to pull from TEST to DEV we must change the Git deamon port in the remote repository definition in Gitora DEV to 9419 (the deamon port we configured in Gitora TEST setup).

Here is how to do that:

1. Create a remote repository in the Gitora DEV installation that looks at the TESTREPO, something like this:



(Note that the Port here is 8081, that is the http port of the Gitora TEST instance)

2. Open a command window in the Git repository directory you created in the DEV Gitora installation. 
For example i created a Git repository named 'DEVREPO' in my Gitora DEV installation.
The path (in my windows installation) would be:
C:\Program Files (x86)\Gerger\GitoraServer-DEV\repositories\gitora\DEVREPO

Now we need to use the Git 'remote' command to change the Git port.

Use 'remote -v' to lists all remote repositories that have been created in the repository.

C:\Program Files (x86)\Gerger\GitoraServer-DEV\repositories\gitora\DEVREPO>
"C:\Program Files (x86)\Gerger\GitoraServer-DEV\Git\bin\git.exe" remote -v

When you do this right after creating the remote repository in Gitora UI, you will not see any remote repository yet. This is because it is not yet created in Git, only configured in Gitora.

The easiest way to create it is just to open the remote pull window in the Gitora UI and fill in the remote repository details you want to pull from. That will create the Git remote definition.

Log into Gitora DEV, open the repository you want to pull to, and open the "Pull from remote" window (via menu "Remote commands - Pull").
Then select the remote Gitora, the Repository and Branch, but do not pull (it will not work anyway). Just cancel the screen after you selected the remote branch.

Now if you do 'remote -v' then you will see it listed, something like this:
TESTREPO_TESTREPO git://10.127.9.114/gitora/TESTREPO (fetch)
TESTREPO_TESTREPO git://10.127.9.114/gitora/TESTREPO (push)

3. To change the port we can use 'remote add'.
First, remove the current repository definition:

C:\Program Files (x86)\Gerger\GitoraServer-DEV\repositories\gitora\DEVREPO>
"C:\Program Files (x86)\Gerger\GitoraServer-DEV\Git\bin\git.exe" remote remove TESTREPO_TESTREPO

Then add the new definition, inluding the port (use the exact repository definition you got from 'remote -v', just add port 9419) like this:

C:\Program Files (x86)\Gerger\GitoraServer-DEV\repositories\gitora\DEVREPO>
"C:\Program Files (x86)\Gerger\GitoraServer-DEV\Git\bin\git.exe" remote add TESTREPO_TESTREPO git://10.127.9.114:9419/gitora/TESTREPO

If you list the remote now, you should see the port number added
TESTREPO_TESTREPO git://10.127.9.114:9419/gitora/TESTREPO (fetch)
TESTREPO_TESTREPO git://10.127.9.114:9419/gitora/TESTREPO (push)

And now you should be able to pull from remote repo TESTREPO in Gitora TEST to the DEVREPO in Gitora DEV.

Have Fun!

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!