Getting Started With the Gitora Editor Home App

The first time you sign in, the Gitora PL/SQL Editor Home Screen will detect that you do not have any databases saved and ask you to create your first database and the first Oracle username and password for it.

Enter a descriptive name and the JDBC URL to connect to the database as seen in the screenshot above.

If you are a Gitora Version Control (GVC) customer, enter your credentials for this database under the Gitora Version Control Credentials header. The Editor will use this information to automatically sign you in to the GVC every time you open a database session. Otherwise, leave these fields empty.

Enter the Oracle username and password you’d like to use to open a session and select the role you’d like to connect as, if it is different from the default.

If you are a GVC customer and use your Oracle user as your GVC credentials, tick the checkbox otherwise leave it unchecked.

Click save.

The Databases screen shows up.

Please note that Gitora Editor Home does not create databases or database users for you. The screens below are used to add existing databases and database users to your home screen.

The Databases Tab

This is your Gitora Editor Home screen where you can jump to all of your databases with the user you’d like to connect as.

Clicking the Connect button will open a the PL/SQL Editor in a new tab.

Click the Delete button removes the database from your list of databases.

The Create New Database button show you the screen where you created your first database.

Click the Edit button to manage the Oracle users you can use to connect to a database.

The Edit Database screen show up.

The Edit Database Screen

You can edit the name and the JDBC URL on this screen.

If you are a GVC customer, enter your Gitora Version Control user name and password to the credentials fields, if not leave these fields empty.

Click the Create New User button to add a new Oracle user to the database. The Create Database User screen shows up.

Enter your Oracle username and password. Select the role the user should connect as if it is different from the default. If you are a GVC customer, check the Use as Gitora Version Control Credentials checkbox if you are using this Oracle user also as your Gitora User Name.

Click save to add a new user to the database.

Use the Edit button to update the credentials for a user and the Delete button to remove it from the database.

The Users Tab

The Users Tab shows up for users with the admin role and can be used to manage the Gitora Editor users.

You can filter users using the search field. Click the Delete button to remove a user from the application. Click the username to edit a user.

Click the Create New User button, to add a new Gitora Editor user. The Create New User screen shows up.

Enter a username (not to be confused with the an Oracle username. This is your Gitora Editor username.) and a password. Select a role for the user (admin or regular) and click save.

Introducing Gitora 7

We are excited to announce the launch of Gitora 7, which includes a groundbreaking addition to our suite of tools:

The Gitora PL/SQL Editor

The Gitora PL/SQL Editor represents a fresh approach to writing PL/SQL code, drawing on the latest advances in code editors to deliver an unparalleled experience to the PL/SQL community. Packed with modern features that are intuitive and easy to use, this editor represents a significant leap forward in coding in PL/SQL.

Let’s take a quick look at some of the features that make the Gitora PL/SQL Editor so remarkable:

  • Beautiful Design
    The Gitora PL/SQL Editor boasts a beautiful, modern design that makes it easy on the eyes and intuitive to use. With Bootstrap 5 components and Font Awesome icons, you can rest assured that the editor will be both stylish and functional.

  • Dark Mode
    When you’re working long hours on code, the last thing you want is to strain your eyes. That’s why the Gitora PL/SQL Editor comes with a gorgeous dark mode that is easy on the eyes and perfect for long coding sessions.

  • Single Page Web Application
    Designed from the ground up for a multi-monitor, multi-tab world, the Gitora PL/SQL Editor is a single-page web application that allows you to open multiple editors in many browser tabs across multiple monitors. This means that you can work on complicated tasks without losing your place.

  • Auto Complete Suggestions
    The editor’s smart auto-complete suggestions make it easy to find what you’re looking for. Whether you’re typing package, procedure or function names, the editor will suggest relevant options to help speed up your workflow.

  • Package (and Object Type) Content Navigator
    Navigating between package procedures, and functions is a breeze with the package content navigator. With just a single click, you can easily move between different parts of your code.

  • Split Editor
    The split editor lets you work on two sections of your code simultaneously, making it easy to compare code or write related sections side by side.

  • Go Back/Forward in the Editor
    The editor remembers the locations of your cursor, allowing you to easily navigate back and forth between different parts of your code with just a click, much like a browser’s back/forward buttons.

  • Go to Definition
    With the CTRL+click command, you can quickly go to the definition of any database object in the code, allowing you to move between different parts of your project with ease.

  • Code Folding
    Code folding lets you get a better view of the code structure by folding code blocks, making it easier to read and understand large chunks of code.

  • Integrated with the Gitora Version Control
    Finally, the editor is fully integrated with the Gitora Version Control, allowing you to use it independently or from within the Gitora Version Control application. This makes it easy to manage your code changes and keep track of your work as you go.

We hope you’re as excited about the Gitora PL/SQL Editor as we are! With its many powerful features and intuitive design, we’re confident that it will be a game-changer for Oracle database developers everywhere.

Download Gitora 7 today to see what it can do for you!

Gitora 6.5 is available

We are happy to announce that Gitora 6.5 is available for download. This version’s new feature is Jira integration.

Gitora customers can now associate a Git repo with a Jira project and enforce that each commit message in the repo references a Jira issue.

To associate a Jira project with your repo, select the repo from the main screen and select the Repo -> Edit Repo menu option.

Enter your Jira user name (usually an email) your API Token, your Jira domain and the issue key you are using for the project and click save.

From this point on, Gitora will not let developers commit to Git without a reference to a Jira issue in their commit message.

For example, the screenshot below shows a failed commit attempt, because the developer did not reference a Jira issue in the commit message:

Typing GIT-1 in the commit message will reference the Jira issue GIT-1 and the commit will go through. After a push to BitBucket, this reference will show up as a link in BitBucket, pointing back to the referenced Jira issue. Similarly, the Jira issue will show this commit as a related commit.

Gitora will check if the referenced Jira issue in the commit message exists in the Jira issue repository.

To remove the Jira project from a repo, open the repo edit dialog and remove all information related to the Jira project and click save.

You can obtain your Jira API key from your Atlassian account’s security page. Here is a direct link to the API Token management page: https://id.atlassian.com/manage-profile/security/api-tokens

Please note that, your account must have read access to the Jira project so that Gitora can query the Jira issue references.

You can download Gitora 6.5 from this link.

Gitora 6.4 is available

Gitora 6.4 is available for download. This version has the following changes:

  • Many bug fixes
  • Minor UI improvements
  • Completely new internals for the web application.
  • A new repo level security model. Admins can now determine who can access which repo.
  • Redesigned Gitora API’s. (https://blog.gitora.com/gitora-api-documentation/)
  • Enforce Compilation feature.

Enforce Compilation Feature:

Starting with 6.4 you can enforce that database objects developers commit to Git must be in a valid state. To do this, select your repo from the main select box and click the Edit Repo menu option.

The Edit Repo Dialog shows up:

Simply, check the Enforce Compilation checkbox and click save. From this point on, Gitora will ensure that every database object being committed to Git is in a valid state.

What Should be in a Valid State to Commit?

If the Enforce Compilation checkbox is checked, Gitora will follow the rules below to determine if an object can be committed to the repo:

  • The object must be in a valid state
  • If the object is a package or an object type and has a body, the body must also be in a valid state.
  • If the object is being referenced by other database objects, and the references are NOT checked out by other developers, these database objects must also be in a valid state.
  • If Gitora finds an invalid object, it will attempt to compile it and will not allow the developer to commit if the compilation fails.
  • Gitora will not allow developers to add a database object to a repo, if it is in an invalid state.

You can download Gitora 6.4 from this link.

Signing In to Gitora For Database Development

After you add a database object to Gitora, it cannot be edited unless the developer authenticates herself to Gitora from the database session she opened to edit the object.

There are two ways to log in to Gitora:

Signing In with a Gitora User

Use the api_gitora.login procedure to authenticate yourself to Gitora. After a successful sign in you can edit the database objects managed by Gitora (based on your Gitora privileges).

Procedure: api_gitora.login(in_user_cd varchar2, in_password_tx varchar2)

Signing In with a Database User

Gitora can recognize Oracle users as valid Gitora users. This is a quick and easy way to give all your developers a Gitora user if each of them use separate Oracle users to log in to the database. There won’t be additional user names and password for your developers to remember and they will not need to authenticate with Gitora after they open a database session, saving them a step in their workflow. Gitora will automatically recognize their database sessions as authenticated.

To achieve this follow the steps below:

  1. Sign in to the Gitora web app as admin.
  2. Select the menu option Database -> Users. The Users screen shows up.
  3. Click the Create New User button. The Create New User screen shows up.
  4. Enter the Oracle User Name of the developer to the User Name field. This will disable the password fields. Select a role for the user. This role specifies the priviliges the user has in Gitora. Click Save and your Oracle User is ready to be used as a Gitora user.

Gitora API Documentation

This is the Gitora API documentation for version 6.2 and above. For the API documentation of the older versions please go to this link.

Gitora Enterprise has a set of API’s that enables developers to programatically execute tasks that users can perform via the Gitora Web App such as reset, create branch, commit etc…

Below is the list of API’s with their description, input parameters and outputs.

Gitora API’s respect the authorization scheme defined in the Gitora Web App. In other words, if a user lacks the necessary privilege to perform an operation in the Gitora Web App, the corresponding API will also fail for the same user.

Introduction

After the default installation, Gitora API’s can be accesses from the URL http://[IP ADDRESS]:8080/gitora/api.

The method parameter specifies the name of the method you’d like to execute like so:

http://localhost:8080/gitora?method=login.....

Output of API Calls

Each API call returns a JSON Object. This object has the following three properties:

message: The message returned from the API, if any.

result: returns the word success if the API was executed successfully or the word error if there was an error during the execution.

If the result property returns the value error then the message property contains the error message.

token: Returns the token value to be used to call other API’s. This property is only populated after a successful call to the login API.

Developers must receive a token from the login API before executing any other API call. Therefore, it makes sense to describe the login method first.

Method: authenticate

Use to sign in to Gitora. It returns a token that you can use to execute other API’s.

Parameters:

user: A valid Gitora username.

password: Password of the Gitora user.

database: The name of the database to log in to. This name is specified in the Settings->Database Connections screen in Gitora.

Example: http://localhost:8080/gitora/api?method=authenticate&user=admin&password=admin

Method: add

Add a database object to a Gitora repo.

Parameters:

name: Name of the database object

repo: Name of the repo to add the database object to.

schema: Schema of the database object to be added.

token: A valid token string received from a login API call.

type: Type of the database object to be added. Valid values are TABLE, PACKAGE, VIEW, PROCEDURE, FUNCTION, TRIGGER, SYNONYM, TYPE, SEQUENCE, MATERIALIZED_VIEW

Example:

http://localhost:8080/gitora/api?method=add&name=EMP_PKG&repo=HR_REPO&schema=HR&token=YhfhseY45&type=PACKAGE

Method: changebranch

Changes the active branch of a Gitora repo.

Parameters:

branch: Name of the branch to switch to.

repo: Name of the repo this operation will be performed.

token: A valid token string received from a login API call.

Example:

http://localhost:8080/gitora/api?changebranch&branch=dev&repo=HR_REPO&token=Hhjfir7hd87

Method: checkout

Checks out the database object for a specific user.

Parameters:

schema: Schema of the object to check out.

type: Type of the object to check out: Valid values are: TABLE, PACKAGE, VIEW, PROCEDURE, FUNCTION, TRIGGER, SYNONYM, TYPE, SEQUENCE, MATERIALIZED_VIEW.

name: Name of the object to check out.

token: A valid token string received from a login API call.

Example:

http://localhost:8080/gitora/api?checkout&object=1234&token=Hdyhed7645g

Method: reset

Performs the Git reset command on the active branch of the specified repo.

Parameters:

repo: Name of the repo to perform the Git reset.

commitid: Git commit ID or tag name to reset the active branch to.

token: A valid token string received from a login API call.

Output:

Returns the Git message received from Git.

Example:

http://localhost:8080/gitora/api?method=reset&commitid=375hfhd&repo=HR_REPO&token=Hjfnh894jhhnfr

Methodcommit

Commits a list of objects to the specified repo.

Parameters:

message: Commit message.

ddls: A JSON array of DDL statement ID’s captured by Gitora.

objects= A JSON array of database objects. Example: [{“schema”:”DEV”, “type”:”PACKAGE”, “name”:”HR_PKG”},{“schema”:”DEV”, “type”:”PROCEDURE”, “name”:”HIRE_EMP”}] . Valid values for type are: TABLE, PACKAGE, VIEW, PROCEDURE, FUNCTION, TRIGGER, SYNONYM, TYPE, SEQUENCE, MATERIALIZED_VIEW.

repo: Repo name.

token: A valid token string received from a login API call.

Output:

Returns the message received from Git.

Example:

http://localhost:8080/gitora/api?commit&message=Fixed%20the%20employee%20queries&objects= [{"schema":"DEV", "type":"PACKAGE", "name":"HR_PKG"},{"schema":"DEV", "type":"PROCEDURE", "name":"HIRE_EMP"}] &ddls=["3642","7854"&repo=HR_REPO&token=jirHJj8rfnJi

Methodcreatebranch

Creates a new branch in the specified repo.

Parameters:

branch: Name of the new branch.

checkout: Indicates whether the new branch should be set as the active branch. Set to true to make the new branch the active branch, to false if you’d like the keep the current active branch.

commitid: Git commit ID, tag name or branch name to create the new branch from.

repo: Name of the repo to create a branch in.

token: A valid token string received from a login API call.

Example:

http://localhost:8080/gitora/api?createbranch&checkout=true&commitid=Hdjdjhuend8874h29jdij&repo=HR_REPO&token=Kjirjdnc767yHy3

Methodcreaterepo

Creates a Gitora repo.

Parameters:

repo: Name of the repo to be created.

type: Type of the repo to be created. Valid values: msr (Multi Schema Repo), ssr (Single Schema Repo)

schema: This parameter is only valid for single schema repos. It indicated which schema the repo is for.

track: Indicates whether this repo should track hard object changes in this repo. Valid values are: Y (for the schema to track the hard object changes), N (for the schema not to track hard object changes.)

token: A valid token string received from a login API call.

Example:

http://localhost:8080/gitora/api?createrepo&repo=HR_REPO&type=msr&token=HJndnr8783dj

http://localhost:8080/gitora/api?createrepo&repo=HR_REPO2&type=ssr&schema=HR&track=Y&token=HJndnr8783dj

Methoddeletebranch

Deletes the specified branch.

Parameters:

branch: Name of the branch to be deleted.

repo: Name of the repo the branch is in.

token: A valid token string received from a login API call.

Example:

http://localhost:8080/gitora/api?deletebranch&branch=dev&repo=HR_REPO&token=Hjfurns75bdy

Methodfetchfromdb

Fetches a branch from a Gitora repo to another.

Parameters:

fromdatabase: The name of the database to fetch from. (Name of the database connection in Gitora.)

fromrepo: The name of the repo to fetch from.

frombranch: The name of the branch to fetch.

torepo: The name of the repo to fetch to. (The destination repo.)

tobranch: The name of the branch in the destination repo.

token: A valid token string received from a login API call.

Example:

http://localhost:8080/gitora/api?fetchfromdb&frombranch=feature_1&fromrepo=HR_REPO&fromdatabase=DEV&torepo=HR_REPO&tobranch=feature_1&&token=Hjnf74hehd

Methodfetchfromremote

Fetches a branch from a remote repo to a Gitora repo.

Parameters:

torepo: The name of the Gitora repo. to fetch to.

tobranch: The name of the branch to fetch to.

remote: The alias specified for the remote database in Gitora.

remotebranch: The name of the remote branch to fetch from.

user: user name to connect to the remote repo. For example your GitHub user name. This parameter is optional. Do not use it if the remote repo does not need a user name to access.

password: The password or the personal access token to connect to the remote repo. For example your GitHub personal access token. This parameter is optional. Don’t use it if the remote repo does not need a password to access.

token: A valid token string received from a login API call.

Example:

http://localhost:8080/gitora/api?fetchfromremote&torepo=HR_REPO&tobranch=feature_1 &remotebranch=feature_1&password=myPersonalAcessToken1&remote=myRemoteHRRepo&user=myGitHubUser&token=Hjnf74hehd

Methodgetmanagedschemascript

Returns the “managed schemas script” of the specified repo.

Parameters:

repo: Name of the repo of which the developer is getting the managed schemas script.

token: A valid token string received from a login API call.

Example:

http://localhost:8080/gitora/api?getmanagedschemascript&repo=HR_REPO&token=Kjfur03nd8

Methodgetmyobjects

Returns the list of checked out database objects by the signed in user.

Parameters:

repo: The repo for which the API queries the objects the user has checked out.

token: A valid token string received from a login API call.

Output:

Returns a JSON array. Each object in the array represents a database object that is checked out by the user. Each JSON object in the array has the following attributes:

id: ID of the database object in Gitora.

name: Name of the object.

schema: Schema of the object.

type: Type of the object. (PACKAGE, PROCEDURE etc…)

token: A valid token string received from a login API call.

Example:

http://localhost:8080/gitora/api?method=getmyobjects&repo=HR_REPO&token=Jnjf744jn

Methodgetrepoobjects

Returns a JSON array of database objects in the repo.

Parameters:

repo: Name of the repo to return the objects for.

token: A valid token string received from a login API call.

Output:

Returns a JSON array. Each object in the array represents a database object managed by the specified repo. Each JSON object in the array has the following attributes:

id: ID of the database object in Gitora.

name: Name of the object.

schema: Schema of the object.

token: A valid token string received from a login API call.

type: Type of the object. (PACKAGE, PROCEDURE etc…)

user: Name of the user who checked out the database object.

Example:

http://localhost:8080/gitora/api?method=getrepoobjects&repo=HR_REPO&token=Nkjf8nf8

Methodmerge

Performs git merge.

Parameters:

commitid: Git commit ID, branch or tag name to merge to the active branch.

repo: Name of the repo to perform merge on.

token: A valid token string received from a login API call.

Example:

http://localhost:8080/gitora/api?method=merge&repo=HR_REPO&commitid=Uhee8jGdy&token=Hjfur84bnfr

Methodpullfromdb

Performs a Git pull from a Gitora repo for another database to the specified repo’s active branch. For example, you can pull the master branch of HR_REPO in DEV database to the master branch of HR_REPO in TEST database to move your latest code to the testing environment.

Parameters:

fromdatabase: Name of the database the pull from. (Name of the database connection in Gitora.)

fromrepo: Repo to pull from.

frombranch: Branch name to pull.

torepo: Repo to pull to.

token: A valid token string received from a login API call.

Example:

Assume thet the developer is connected to the TEST database. The example below pulls the master branch from the HR_REPO repo for DEV database to the master branch of HR_REPO repo for the TEST database.

http://localhost:8080/gitora/api?method=pullfromdb&fromdatabase=DEV&fromrepo=HR_REPO&frombranch=master&torepo=HR_REPO&token=Hjgg6ghf

Methodpush

Pushes the specified repo’s branch to a remote Git repo (which commonly resides in GitHub, GitBlit, BitBucket or a similar cloud service).

Parameters:

branch: Name of the branch to push.

password: Password or the personal access token to connect to the remote repo.

remote: The alias specified for the remote repo in the Gitora Web App under Settings–>Manage Remote Connections.

repo: Name of the repo to push.

token: A valid token string received from a login API call.

user: User name to connect to the remote repo.

Output:

Returns the message received from Git.

Example:

http://localhost:8080/gitora/api?method=push&branch=master&password=1234&remote=GitHubHRRepo&repo=HR_REPO&user=myGitHubUser1&token=Hjf85jf

Method: setmanagedschemascript

Updates the “Managed Schema Script” content and commits it.

Paramaters:

message: Commit message to be used when the new script is committed to Git.

repo: Name of the repo to set the script for.

script: The new text of the script.

token: A valid token string received from a login API call.

Example:

http://localhost:8080/gitora/api?method=setmanagedschemascript&messageScript%23updated.&repo=HR_REPO&script=ALTER%23EMPLOYEES%23ADD%23COLUMN%23(MIDDLE_NAME%23VARCHAR2(200);&token=Hjhu837h

Methodrelease

Releases the lock placed by a user on a database object. Overwrites the content of the object with the latest version available in the active branch.

Parameters:

object=JSON Object specifying the database object to be released. Example: {“schema”:”HR”, “type”:”PACKAGE”, “name”:”HR_PKG”}. Valid values for type are: TABLE, PACKAGE, VIEW, PROCEDURE, FUNCTION, TRIGGER, SYNONYM, TYPE, SEQUENCE, MATERIALIZED_VIEW.

token: A valid token string received from a login API call.

Example:

http://localhost:8080/gitora/api?method=release&object= {"schema":"HR", "type":"PACKAGE", "name":"HR_PKG"} &token=Hjfu57hf

Methodremove

Removes the specified database object from Gitora.

Parameters:

name: Name of the database object.

repo: Name of the repo the database object will be removed from.

schema: The schema of the object

type: The type of the object. Valid values for type are: TABLE, PACKAGE, VIEW, PROCEDURE, FUNCTION, TRIGGER, SYNONYM, TYPE, SEQUENCE, MATERIALIZED_VIEW.

token: A valid token string received from a login API call.

Example:

http://localhost:8080/gitora/api?method=remove&name=EMP_PKG&repo=HR_REPO&schema=HR&type=PACKAGE&token=Hfjuh784hnd

Are You Really Using a Version Control System for Your Database or Are You Ticking a Box?

You use a version control system to solve certain problems, to accomplish certain tasks. These are:

  • Keep track of who changes what, when and why.
  • If necessary, use previous versions of the code base for testing, development etc…
  • Move changes between environments faster, with fewer errors during deployment.
  • Improve productivity of the team (i.e. less time coordinating manually and more time developing. For example different developers working on the same parts of the code base simultaneously and merging their changes at a later time, hopefully mostly automatically.).

If you are using a version control system but not accomplishing any of the things above, are you really using a version control system?

Here is a perfectly reasonable scenario that I’ve seen in the real world. I’ve seen this scenario with teams using Git with SQL Developer, SVN/Git with another Oracle PL/SQL IDE. Invariably, they all end up here:

  1. In the DEV database, you make changes to the package PKG_HIRING in the HR schema and then commit the package under /HR/PKG_HIRING.sql file in Git.
  2. Next, another developer makes changes to the same package. He commits his changes under /HR/next_version/PKG_HIRING.sql, because… why not? It’s not like there is any enforcement.
  3. A third developer commits his changes to /HR/PKG_HIRING.pkb because for some reason, the tool he uses for editing uses the .pkb suffix for packages.
  4. The fourth developer named John, decides to copy PKG_HIRING to a file and work on it. He thinks he can move his changes to the database later. So he creates /HR/JOHN_DO_NOT_TOUCH/PKG_HIRING.sql
  5. A fifth developer, makes changes to the HR.PKG_HIRING package but forgets to commit the package to Git. Hey it happens…
  6. A sixth developer makes even more changes. He commits the package to Git under HR/PKG_HIRING.sql thereby committing not only his changes but also the fifth developers changes.

Three months later, you are tasked with deploying the latest version of the HR application to the TEST database. Which PKG_HIRING package are you going to deploy to TEST?

I actually know the answer to this question. You will deploy the one in the HR schema. It’s the safest option. I cannot help but wonder tough.. in these past three months, did John save his changes to the database? If so, how did he do it? Did he simply overwrite the package? What if some changes were lost?

At this point, I have to ask… Why do you even have a version control system? Why does that Git/SVN repo even exist? Is it useful at all or are you just doing all this just to tick a box and to be able to say that you are using a version control system?

Gitora solves all these problems above. It creates a version control system around Git and enforces it at the database level.

Let’s repeat the same steps for the six developers but this time let them use Gitora:

  1. In DEV, you make changes to the PKG_HIRING package in the HR schema. When you are done, you open the Gitora web app, you see the list of database objects you changed, you select the ones you want to commit (in this case PKG_HIRING) you add a comment to the commit (such as the ticket number) and click the commit button.
  2. The second developer follows the same process as you. He cannot make any changes to the package until you are done with it. Once you are done, he can make his changes. He opens the Gitora App, commit his changes to Git and is done. Note that, he doesn’t get to choose which folder he saves the PKG_HIRING.sql file to. Gitora creates a well structured working directory with reasonable folder and file names for database objects. The locations of these files are also standard. When there is human decision involved with naming and putting files in the file system, a lot of errors can occur. Gitora prevents these errors.
  3. The third developer’s IDE used the .pkb extension for his file. Since developers don’t get to name the files or choose the folders they save database object scripts to, the third developer also has no choice but to follow the same process as the first two developers. By the way, Gitora works with any IDE. You can use SQL Developer, SQL Navigaor, TOAD or PL/SQL Developer. It doesn’t matter. Everyone in your team can use a different editor if they choose to do so. Gitora still works.
  4. The fourth developer was impatient. He created a new file under JOHN_DO_NOT_TOUCH and saved his changes to that file. With Gitora, he has no direct access to Git and the Git working folder. (Gitora is a server side solution. You don’t install Git to developer’s computers.) So he cannot do that. However, if he wants to do mischief, he still can copy the PKG_HIRING package to a new file in his computer and make changes there. Then, he can simply overwrite everything in the PKG_HIRING package with his version at a later time. This will cause some changes to be lost in the package depending on when he does it. He can even go ahead and commit his changes to Gitora. But hold on a second. At some point, either during development or testing, the mistake he made will be detected. In the first scenario, there is no way to know for certain who erased all the functioning code and exactly what code should be put back to the package. It will be trivial to use Git to figure out who deleted the perfectly functioning code from PKG_HIRING because Gitora always keeps track of who changed what, when and why. Since we can know when the mistake happened, we will know the correct version of the package to put back to. John will take the heat for his egregious mistake and merge his changes to the correct version of the package. No one wants that kind of heat. It is easy to see that Gitora incentivizes the right kind of behavior among teammates by keeping everyone accountable for their actions as well as making it possible to recover from critical mistakes.
  5. The fifth developer simply forgot to commit his changes to Git. With Gitora there are two ways this can be caught. First, the sixth developer who wants to edit the package will contact the fifth developer to ask when the package will be available. Second, Gitora web app always shows which objects are locked by whom. So before preparing a deployment, you can always check if there are any database objects that are locked.
  6. In the first scenario, the sixth developer committed fifth developers changes along with his. With Gitora, the sixth developer will always commit only his changes because there is no way he can gain access to the PKG_HIRING without the fifth developer releasing the package first. Gitora enforces that if an object is being edited by someone, no one else can edit it. With Gitora, version control is not something you do on the side but it is tightly integrated into your workflow and enforced.

And finally, three months later, you are tasked with preparing the deployment to TEST. How are you going to move the changes to the TEST database? With Gitora, all you need to do is to open the Gitora app, connect to the TEST database and pull the HR repo from DEV to TEST and you are done.

Going back to the begining of the article; Gitora makes Git a useful version control system for the Oracle Database.

As shown in the second scenario with Gitora you can:

  • Keep track of who changes what, when and why.
    We caught John misbehaving.
  • If necessary, use previous versions of the code base for testing, development etc…
    We recovered an old version of PKG_HIRING and have John merge his changes to it.
  • Move changes between environments faster, with fewer errors during deployment.
    Done via point and click Gitora Web Application.
  • Improve productivity of the team (i.e. less time coordinating manually and more time developing. For example different developers working on the same parts of the code base simultaneously and merging their changes at a later time, hopefully mostly automatically.).
    The second scenario reduced manual coordination significantly. Gitora can do a lot more. You can have different teams work on the same code base at the same time and merge their changes just like Java, JavaScript developers. However that’s beyond the scope of this article. Please read this article and this article to learn more about how you can implement parallel development with Gitora.

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 a 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 more requirements:

  • 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.

Michiel Arentsen, Rhenus

Managing Tables with Single Schema Repos

This article describes how you can track schema changes with a Single Schema Repo (SSR). If you haven’t done so already, we recommend you to read this article first, which explains how Gitora manages tables.

Terminology

In Gitora parlance, any object that can be created with the CREATE OR REPLACE syntax is called a soft object. Objects such as tables, sequences and materialized views which cannot be created with the CREATE OR REPLACE syntax are called hard objects.

Altough one repo can store both hard and soft objects without any issues, we recommend users to store hard and soft objects of a project in separate repos.

Managing Schema Changes with Gitora

Gitora repos can track all changes to hard objects and SSR’s are no different. However, an SSR can only track the hard objects changes in the schema it is associated to.

You can enable this feature by checking the Manage Schema Changes checkbox while creating or editing the SRR.

If a schema is managed by a Gitora repo, Gitora will not allow any user to create or edit any hard object in the schema without connecting to Gitora first.

An SSR can only manage the changes in the schema it is associated to.

A schema can only be managed by one repo.

The DDL statements captured by an SSR will be committed to Git without the schema prefixes.

In the example below, we add a column named DEPARTMENT_CD to the HR.DEPARTMENTS table. The DDL statement in Gitora does not have the schema prefix HR.

After the commit, the DDL statement shows up in the Git repo. It is appended to the previous list of committed DDL statements. You can access committed DDL statements to Gitora by clicking the Schema Changes tree node:

Moving Hard Object Changes Between Schemas

Single Schema Repos enable developers to move hard object changes among schemas easily. This is best explained with a simplified example. A real world situation will probably be more complex but the principles will remain the same.

Assume that HR is our master schema and HR_HARD_OBJECTS_REPO is the single schema repo where we manage the hard object changes in the HR schema. Further, assume that two developers Joe and Jane are working in the same database but in their own schemas named HR_JOE and HR_JANE respectively. They can manage hard object changes with the following setup:

  1. Create two single schema repos, one for Joe and one for Jane, named HR_JOE_HARD_OBJECTS_REPO and HR_JANE_HARD_OBJECTS_REPO respectively. Associate the HR_JOE_HARD_OBJECTS_REPO with HR_JOE and HR_JANE_HARD_OBJECTS_REPO with HR_JANE. Do NOT check the Manage Schema Changes checkbox for each repo during creation.
  2. Pull from HR_HARD_OBJECTS_REPO to HR_JOE_HARD_OBJECTS_REPO and to HR_JANE_HARD_OBJECTS_REPO.
  3. Open HR_JOE_HARDOBJECTS_REPO in Gitora. From the menu bar, select to Git–>Repo–>Edit Repo menu item and check the Manage Schema Changes checkbox. Repeat this step for HR_JANE_HARDOBJECTS_REPO as well.

Now that our developers’ repos are initialized, they can pull hard object changes from each other’s repos. Not having the schema prefixes in the DDL statements makes executing these DDL statements in their own schema easier.

Obviously, in a real world scenario this flow will be more complex. It will most likely involve pushing to and pulling from repos in GitHub, GitBlit or similar services. Developers may also need to manually edit the document that contains the schema changes.

Fortunately, Gitora works seamlessly with GitHub GitLab and similar services. It also allows developers to edit the schema changes document and commit their changes right within the Gitora app. Finally, after a pull, developers can compare old and new versions of the schema changes document to determine which DDL statements they need to execute in their own schema. This comparison can be done in the Gitora application, as well.

For development teams, Gitora makes managing schema changes much easier. It helps the teams use Git with their code base and implement modern development workflows to increase productivity and stay compliant.

Introducing Gitora 6

We are happy to annouce that Gitora 6 is available for download.

With Gitora 6, developers can work on the same code base (i.e. a package, procedure etc…) in the same database without blocking each other.

Many development teams use a single database for development. Many of them use the same database for testing, as well. They achieve this by using different schemas for different purposes. Gitora 6 enables these teams to create Git repos for these schemas and pull updates between them.

With Gitora 6, you can even create a different schema for every developer and have them merge their code using Git.

Gitora 6 enables you to implement any modern development workflow in a single database.

How does Gitora 6 work?

Gitora 6 introduces a new repo type called Single Schema Repo (SSR). As its name suggests an SSR manages database objects from a single schema. The DDL scripts in SSR’s don’t contain the schema prefixes so that Gitora can execute them in other schemas in the same database.

This enables developers to work on the same package, procedure, function, view etc… (i.e. anything that can be created with the CREATE OR REPLACE command) at the same time, in the same database in different schemas.

An Example

Let’s go through an example: Let’s assume that the team is working on a logistics software and have a schema named LOGISTICS that stores all their database objects. The team can create a schema (or already have one) called LOGISTICS_TEST in the same database. Here are the steps the team needs to follow so that they can pull their changes to LOGISTICS_TEST.

  1. Create a single schema Gitora repo that manages the LOGISTICS schema. Let’s call it the REPO_LOGISTICS.
  2. Add all relevant database objects in the LOGISTICS schema to the LOGISTICS_REPO.
  3. Create another single schema Gitora repo that manages the LOGISTICS_TEST schema. Let’s call it the REPO_LOGISTICS_TEST
  4. Pull from the REPO_LOGISTICS to REPO_LOGISTICS_TEST

That’s it. That’s all there is to it. From this point on, any change you make to the code in the LOGISTICS schema can be pulled to the LOGISTICS_TEST schema using Gitora (and vice versa).

Single Schema Repos can also be used to create separate development environments in the same database for every developer in the team.

Multiple Developers, Same Database

Assuming we already have the LOGISTICS schema and the REPO_LOGISTICS repo from the previous example, here is how that would work:

  1. Create a schema for each developer: LOGISTICS_JOE, LOGISTICS_MARY, LOGISTICS_PAUL.
  2. Create a single schema Gitora repo for each schema. Let’s call them REPO_LOGISTICS_JOE, REPO_LOGISTICS_MARY, REPO_LOGISTICS_PAUL respectively.
  3. Pull from the REPO_LOGISTICS to REPO_LOGISTICS_JOE, REPO_LOGISTICS_MARY and REPO_LOGISTICS_PAUL.

From this point on, all three developers can work in their own schema and edit any package, procedure, view etc… freely, without overwriting each other’s changes or being affected by them. Using Gitora, they can create new branches in their own repo, for features or bugs they work on without affecting anyone else.

When the time comes to put it all together, they can use Gitora to merge their changes.

We wrote a few articles to get you started:

Creating Single Schema Repos.

How to implement agile development in a single database

How to manage changes to tables with Single Schema Repos

Before Gitora 6, agile database development with Gitora required multiple databases to implement. With Gitora 6 you can achieve it in a single database.

We are incredibly excited to release Gitora 6. We believe it will bring a massive productivity boost to Oracle database development teams worldwide. We can’t wait for you to try it.

Gitora 6 is available now. You can download it from this link.