Agile Development with Oracle PL/SQL

Here is the number one question I receive from PL/SQL developers, DBA’s and IT managers who are implementing a version control solution for their Oracle Database:

Developer 1 (or Team 1) works on Feature A (or issue A, bug A, project A). Developer 2 works on Feature B. Both developers make many changes to the PL/SQL code and many commits to the version control repository. We move to testing. Based on the test results, even more commits are made by both developers. Now at the last minute, due to some reason (a last minute bug, a tactical change in direction, a change in strategy etc…) we decide to ship Feature B only. How can we remove Feature A from the source code easily? How does version control help in such a situation?

There are multiple answers to this question. Below, I’ll go over two possible solutions for two potential setups:

  1. If you are using one shared development database
  2. If you are using multiple development databases (or at least you are open to the idea.)

If You Are Using One Shared Development Database

Let’s assume that our shared development database is called DEV. In other words, both Feature A and Feature B are being developed in the same code base. Let’s also assume our only test database is called TEST.

Finally, assume that DEV is managed by Gitora DEV and TEST is managed by Gitora TEST.

In this case, the commit history in Gitora DEV has changes both for Feature A and B in a random order and looks something like this:

There is no easy way to remove the commits for Feature B in this setup. If the team decides to deliver Feature A to production but postpone the delivery of Feature B here are the steps they should follow:

  • Go over the commit history in Gitora DEV and find the commits that are related to Feature A.
  • Comment out/revert back/change the edits made for Feature A. Git will help the developers to see what’s changed, added, deleted and what the previous versions of code objects looked like.
  • Commit these new changes to Gitora DEV.

At this point the commit history in Gitora DEV looks something like this:

And finally, the next steps are:

  • Pull the new version from DEV to TEST using Gitora TEST.
  • Run tests on the TEST database. If there are no errors, deliver the new version to production. If there are errors, go to the second step, rinse and repeat until the desired outcome is reached.

In such a setup Gitora provides the following benefits:

  • Gitora keeps track of all changes made to the PL/SQL code in DEV, automatically. Developer 2 does not have to remember which PL/SQL objects she modified and what she modified in those objects.
  • Moving the code between DEV and TEST is as easy as clicking a button or making a PL/SQL function call. The team does not need to manually prepare scripts.

The downside of this approach is that it involves manual work which is error prone. Developer 2 still has to manually go through the commit history and make the changes to the source code in DEV to disable/remove Feature A. With only one shared development database ( i.e. development environment), there is no feasible way to easily remove Feature A.

If You Are Using Multiple Development Databases

Now things get more interesting. Below is a simplified workflow that uses only two development databases. This setup enables the IT team to deliver only Feature B to deployment without manually editing PL/SQL code objects to remove Feature A. (Obviously, giving each developer her own development environment is ideal but I understand that this might not be possible on day one in many IT departments. With that said, the PDB feature in Oracle 12c makes giving each developer her own environment much easier than before.)

Here is our initial set up:

  • Developer 1 works in the database DEV1 and Developer 2 works in the database DEV2 i.e. both developers (teams) have their own private environment while they are developing their respective features.
  • Our only test database is called TEST.
  • DEV1 is managed by Gitora DEV1, DEV2 is managed by Gitora DEV2 and TEST is managed by Gitora TEST.
  • DEV1, DEV2 and TEST start with the same version of the code which is stored in the master branch of their respective Git repositories. (In other words, DEV1/master and DEV2/master are clones of TEST/master)

Developer 1:

  • Using Gitora on DEV1, create a new branch named featureA (DEV1/featureA). Switch DEV1 to use this new branch.
  • Write code in DEV1
  • Test code in DEV1
  • Commit code changes to the DEV1/featureA (using Gitora DEV1). Go back to step 2 as many times as needed.

As an example, the Git history in Gitora DEV1 looks like this:

Developer 2:

  • Using Gitora DEV2, create new branch named featureB (DEV2/featureB). Switch DEV2 to use this new branch.
  • Write code in DEV2
  • Test code in DEV2
  • Commit code changes to the repository to DEV2/featureB (using Gitora DEV2). Go back to step 2 as many times as needed.

The Git commit history in Gitora DEV2 looks like this:

 

Moving Code Between Databases

At any point in time, if Developer 1 or Developer 2 decides to send code to TEST (for example, for integration testing purposes..), they follow the steps below.

Please note that in real world development, the workflows described below can be performed simultaneously in DEV1 and DEV2, in no particular order, any number of times until both features are ready to be deployed to production.

The steps below are merely a simplified example of how this workflow can take place.

Developer 1:

  • In our example , we assume that Developer 1 is the first developer sending her commits to TEST.
  • Using Gitora DEV1, merge DEV1/featureA to DEV1/master. (DEV1/featureA –> DEV1/master) In our workflow, as a general rule merges to the master branch are performed in the development database repositories not in the TEST database.)
    After the merge, commit history in Gitora DEV1 looks like this:

  • Pull master branch from Gitora DEV1 to Gitora TEST. (DEV1/master –> TEST/master). A “Pull” is simply a two step process of fetching a branch from a remote Git repository and then merging it to another local branch. In our example Gitora TEST fetches DEV1/master from Gitora DEV1 and merges it to TEST/master which we simply show as DEV1/master –> TEST/master.
    After the pull, commit history of both TEST and DEV1 looks like this:

Developer 2:

  • Pull master branch from Gitora TEST to the master branch in Gitora DEV2. (TEST/master –> DEV2/master). (In our example, this is necessary, because TEST/master has received new commits from DEV1/master.) After the pull, the commit history looks like this: (Remember, previously we pulled commits from DEV1/master to TEST/master and we also made commits to DEV2/featureB)

  • Using Gitora DEV2, merge DEV2/featureB to DEV2/master. (DEV2/featureB –> DEV2/master) After the merge, the commit history looks like this:

  • Using Gitora TEST, pull master branch from Gitora DEV2 to Gitora TEST. (DEV2/master –> TEST/master). After the pull, the commit history looks like this:

In other words, in its final state, the source code in the TEST database is a merge of Feature A and B where the commits in TEST/master branch are in a random order. In our example, the full commit history of DEV1, DEV2 and TEST repositories look like this:

Note that at all times, DEV1/featureA only contains the code for Feature A. Similarly, DEV2/featureB only contains the code for Feature B.

This workflow enables the IT team to exclude Feature A from the next deployment using Git commands at any point in time before going production.

To achieve this in our example, follow the steps below using Gitora:

  • Revert DEV2/master back to its initial state where no code for Feature B has been committed yet. (This uses the Git reset command.)

  • Merge DEV2/featureB to DEV2/master. (DEV2/featureB –> DEV2/master)

  • Reset TEST/master to its original state.

  • Finally, pull master branch of DEV2 to the master branch of TEST. (DEV2/master –> TEST/master)

After these steps are completed, the TEST database only contains code changes related to Feature B. We can use Gitora to extract a DDL script which contains only the changes made between the initial and final state of TEST/master and use this script to deploy Feature B.

You can also watch the video tutorial for this workflow below: (The only difference is that in the video the TEST database is called PREPROD.)

Gitora helps developers execute this workflow with a point&click GUI and PL/SQL API’s. Specifically:

  • Gitora keeps track of all changes made to the PL/SQL code in DEV1, DEV2 and TEST automatically.
  • Gitora updates the source code in the Oracle Database automatically when the executed Git command changes the files in the working directory.

Therefore:

  • Developers don’t have to remember which PL/SQL objects they modified and what they modified in those objects.
  • Moving the code between DEV1, DEV2 and TEST is as easy as clicking a button or making an PL/SQL function call. The team does not need to manually prepare scripts. Gitora can either generate the scripts or update the databases automatically.
  • Crucially, Gitora enables Developer 1 and Developer 2 switch between code branches automatically. (For example if the DEV1 database switches from featureA branch to the master branch, Gitora automatically updates the code in the DEV1 database to reflect this change.)

Download Gitora now and try this workflow in your environment.

Yalim K. Gerger
Founder

ODTUG Webinar about Version Control for PL/SQL

This week I hosted a webinar for ODTUG members about how to implement a version control solution for the Oracle Database using Git. Many thanks to the 119 attendees who joined the live event. I am very happy to see that version control is now being recognized as a serious pain point among Oracle Database customers.

Please see below for the video and the slides of the presentation:

Webinar Video and Slides

Last week, we hosted another webinar to talk about Gitora and how it helps Oracle developers manage their PL/SQL code base. Below are the slides we used during the webinar:

You can watch the recording of the webinar below:

Gitora in 30 Minutes

Gitora is the version control tool for PL/SQL. It hooks Git to the Oracle Database and enables you to manage your PL/SQL code base easily. Below is a 30 minute demo video which explains the benefits of Gitora and how it works.

Would you like to develop and deliver faster with PL/SQL? If yes, then contact us for a free one our review of your software development lifecycle.

Rhenus Uses Gitora

It’s been a little over three months since we released Gitora 2.0 and the first success stories have already started to surface. Here is one of them:

Rhenus Logistics, the leading logistics company from Germany uses Gitora to manage their Oracle Database.

Problem

Rhenus IT uses both Java and PL/SQL to serve their users and customers. They have a team of about 10 PL/SQL developers. The team manages more than 20,000 database packages, views, functions, procedures, object types and triggers spread over 30+ database schemas.

Rhenus IT wanted to move to a continuous delivery environment in which they can be more agile and deliver solutions to the business faster. Managing the PL/SQL code was the hardest piece of the puzzle.

Solution

After experimenting with other solutions in the market, Rhenus decided to move forward with Gitora.

Gitora enabled Rhenus Developers to:

  • Use Git, the prominent open source version control system used by millions of developers.
  • Move their database code between development and various staging databases automatically.
  • Move code between source and target databases very fast because Gitora only executes differences between source and target databases, without comparing the code bases in both databases first (which can be very time consuming).
  • Enforce check-in, check-out of database objects at the database level.
  • Automate build process for the database code using Gitora API’s.
  • Implement an affordable continuous delivery solution compared to alternatives.

Michiel Arentsen, the System Architect at Rhenus who implemented the solution at Rhenus has started an excellent blog in which he writes about his Gitora implementation. We highly recommend you to check it out. Below are the list of blog posts he wrote which should be very useful to anyone who is currently implementing Gitora at his/her company:

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

Automatically log in to Gitora when connecting to Oracle

A fast way to load your database objects to a Gitora repository

Automatically add new database objects to Gitora

Start Gitora as a Windows service

Gitora 2.1 is available with tagging support

We are happy to announce that Gitora 2.1 is available for download. Along with a few bug fixes, Gitora 2.1 enables PL/SQL developers to use Git Tags to label any commit point in the Git repository.

Tags can be used for a variety of use cases but the most common one is to label a Commit ID for specific versions of the code base such as tagging a specific commit with the label “Version 2.0”.

It is very easy to create tags in Gitora. Simply, go to the local commands menu, select “Create Tag” menu item under “Tags”.

gitora-tags-menu

 

A dialog shows up.

Create Tag Dialog

Enter the name of your tag (no spaces). Enter the commit ID you’d like to attach it to. If you’d like to attach your tag to the HEAD i.e. the most recent commit in the current branch, leave the Commit ID field empty. You may also use a branch name instead of an actual Commit ID.

Click OK to create the tag. If the tag is attached to a commit visible in the Gitora repository browser, it will show up in the Gitora Application.

Gitora Repository Browser

To delete a tag, select the Delete a Tag menu item under the Tags menu, enter the name of the Tag you’d like to delete and click OK.

Tags are available in Gitora 2.1 Professional Edition. Enjoy! 🙂

Kind Regards,
The Gitora  Team

Gitora Webinar Video and Slides

We hosted a webinar about Gitora last week. It received more than 400 signups and over 120 people have attended the event. The interest to the webinar was so high that we hit the 100 attendee limit of the webinar software we are using. Thanks to everyone who attended and helped us to do a better webinar with their questions and comments.

If you could not make it to the webinar, you can watch it or view the slides below. The webinar consists of two parts. In the first part, we talk about transition strategies to move an organization from manual version control for PL/SQL to version control with Gitora. In the second part, we showed the capabilities of Gitora with a live demo.

 

Gitora, Version Control for PL/SQL from Gitora

Gitora Webinar

OK, so you read the web site, downloaded Gitora, installed it successfully, created a few test repositories, added a few packages, issued a few Git commands from the Gitora UI. And it all works fine. But now what? How do you actually start using Gitora in your development life cycle? How can you actually benefit from it?

In order to show you how you can use Gitora in your daily work we are hosting a free webinar on November 22nd. You can register at: http://www.prohuddle.com/webinars/Gitora/Version_Control_for_PLSQL_Developers.php

During the webinar we will cover the following topics with a live demo:

  • How can you use Gitora to manage your PL/SQL code base?
  • Why is using version control with PL/SQL is hard and how does Gitora help?
  • Why is Gitora the best solution for the problem?
  • How will using version control with PL/SQL will help you?

The webinar is free but space is limited. Sign up today.

Kind Regards,
Yalim Gerger
Founder

 

Installing Gitora Manually

We created a solid installer for Gitora which has a very high success rate. However, IT environment can be very complex and restrictive (and understandably so). So having an installer does not work for everyone and every organization.

Therefore, today we are making the Gitora 2.0 installation scripts available for download. Below are the steps you can follow to install Gitora 2.0 to your environment manually.

Installation of Middletier Components

  1. For Linux and Mac OS, install Git manually from https://git-scm.com/. For Windows, Git is already included to this setup.
  2. Move Gitora2 folder to the place you want to locate Gitora middle tier components.
    e.g /Users/username/
  3. For Windows, open fix_parameters_WINDOWS.bat to edit.
    For Mac OS, open fix_parameters_MAC.sh to edit.
    For Linux, open fix_parameters_LINUX.sh to edit.
  4. Set GITORA_ROOT parameter to the absolute path of the Gitora2 folder from step 1..
    e.g /Users/username/Gitora2
  5. Set GITORA_DATABASE_CONNECTION_STRING parameter to the correct jdbc connection string to your database.
    e.g jdbc:oracle:thin:@192.168.1.99:1521:orcl
  6. For Mac OS and Linux, set GIT_EXECUTABLE parameter to the path of git executable.
    e.g. /usr/bin/git
  7. Open command line terminal and run fix_parameters_WINDOWS.bat for Windows, fix_parameters_MAC.sh for MAC OS, fix_parameters_LINUX.sh for Linux.

Installation of Database Components

  1. Connect to the Oracle Database with the SYS user
  2. Run 01_SYS.sql
  3. Disconnect from SYS
  4. Connect to the database with the GITORA user
  5. Run 02_GITORA.sql
  6. Ensure that there are no invalid packages in GITORA schema.
  7. Create a row in table GITORA.T_TEMP_APPLICATION
  8. Copy the contents of Gitora.xml to this newly created row
  9. Issue a commit to save this row to the database.
  10. Run 03_GITORA.sql
  11. Disconnect from GITORA user
  12. Connect to the database with SYS user
  13. Open 04_SYS.sql and replace the two occurrences of @VERSIONCONTROL_HOST@ keyword with the IP address of your Gitora middle tier machine, where Gitora web server and middle tier components are installed. You need to ensure that your middle tier machine is accessible from database machine by checking network and firewall settings of both machines.
  14. For Windows, change the value of “folderSeparator” parameter from ‘/’ to ‘\’.
  15. Run 04_SYS.sql
  16. Disconnect from SYS.

Starting Up Gitora

  1. For Windows, run Gitora2/apache-tomcat-7.0.69/bin/startup.bat.
    For Mac OS and Linux, run Gitora2/apache-tomcat-7.0.69/bin/startup.sh
  2. Open your browser and type http://[MIDDLE TIER IP ADDRESS]:7997/gitora/main.html name=Gitora

Generating a Diff Script in Gitora

Gitora 2.0 enables developers to generate a DDL script from the Git repository which can be used to synchronize source code between two databases. For example, this feature can be used to generate a script to syncronize a production database to the latest version of the source code. This tutorial explains how to use this feature in Gitora 2.0.

Open the Gitora Application. Select a Git repository. Next, click the Get Script button either from the menu bar Local Commands –> Utilities –> GetScript or from the tool bar.

Get Script

Alternatively, click the Get Script icon in the Local Commands toolbar section.

Get Script Button

 

The Get Script Dialog shows up.

Get Script Dialog

Enter the Starting Git Commit ID and the Destination Git Commit ID. Use the keyword HEAD if you’d like to use the latest commit in the current active branch as the Destination Commit ID.

Click the Download DDL Script button. Gitora will generate a DDL script which, if executed, will syncronize a schema (or a group of schemas) which is currently at the Starting Commit Point ID, to the Destination Commit Point ID.

For example, this feature can be used to apply the latest version of the source code to a production database. If the production database is at Git Commit Point A and the new production-ready version of the source code is at Git Commit Point B, a developer can enter A as the Starting Commit ID and B as the Destination Commit Point ID and then execute the generated script in the production database.