Introduction to the PL/SQL Editor

In the Gitora Editor Home, click the Connect button next to an Oracle username to open the Gitora PL/SQL Editor in a new tab.

The PL/SQL Editor shows up with an empty SQL Pad that uses the Oracle user you clicked, as its default connection information. The database object browser on the right shows the database objects in the Oracle user you clicked.

Expand the Packages Accordion in the object navigator. The list of packages in the schema will show up. Click the Edit button next to a package.

The Gitora PL/SQL Editor opens the package spec and body in the current browser tab. The editor will open any subsequent database object in a new browser tab.

Before we start editing, let’s go over the list of buttons and features in the editor.

The Toolbar

The toolbar is always at the top of the screen. Below is the list of buttons in the toolbar from left to right and a brief description for each one:

Show/Hide Method List

The Gitora PL/SQL Editor shows a powerful content navigator when a package or an object type is displayed in the editor. This button hides/shows the content navigator. It can be used to create more space for the PL/SQL editors and the SQL Pads.

Undo/Redo

Use these buttons to undo/redo changes you made in the current editor. Since a package and an object type have two editors, one for the spec and one for the body, these editors have separate undo queues.

However, if an editor is split in two using the split editor feature. The split editors share a single undo queue.

Comment Out/In

Select a block of code and click the Comment button to turn it into a block of comment. Conversely, select a block of comment and click the same button to turn the comment to a code block. This feature uses the — notation to create comments and similarly can reverse comments that start with –.

Indent

Select a code block and click the Indent button to shift the block 1 tab size to the right.

Outdent

Select a code block and click the Outdent button to shift the block 1 tab size to the left.

The Context Label

The context label in the middle of the toolbar displays the Oracle username and the database you clicked to open this tab as well as your Gitora Editor user name.

Save

Used to save the changes made to the database object open in the editor. The icon turns dark if there are changes to save.

Split Editor

Splits both the spec and the body editors in two horizontally. The icon turns dark if it is active.

Back/Forward

These buttons work similar to back and forward buttons in a browser. Gitora tracks the locations your cursor has been to in the editors. Clicking the back button takes your cursor to the previous location it was. Conversely, clicking forward button takes the cursor back to the next location.

Find and Replace

The Gitora PL/SQL Editor has a powerful Find&Replace widget that you can use to search for letters, whole words and replace them. You can even search using regular expressions. Clicking the icon the first time displays the widget. Clicking it the second time hides it. You can also use CTRL-F to show the widget.

Feedback

Please use the feedback button to share you comments or inform us about bugs and issues.

Color Mode

Click the color mode button to switch between light and dark mode.

Settings

Clicking the settings button displays the settings window.

In this window, you can set the font size, font family, tab size values for the Gitora PL/SQL editor.

Max Rows value determines the maximum number of rows a query you execute returns.

Text Length sets the maximum length of the text the editor returns. If set, longer text values will be cut at the length you specify.

Tick the Save Layout checkbox, if you’d like the Gitora PL/SQL editor to open reusing the widths and heights you set for the Method List on the left and the Object Navigator on the right.

Click the Save button to store your changes.

About

Click the icon to display the version and build number of the Gitora PL/SQL Editor.

The Workspace

The middle part of the Gitora PL/SQL Editor is your workspace. It consists of three columns. From left to right: the Method List (Package Navigator), the Editors area and the Database Object Navigator.

Each column is separated with a splitter. You can move these splitters left or right to adjust the width each column covers on the screen.

Method List (Package Navigator)

This column displays the procedures and functions in the package or object type that is open for editing in the editor.

The Search Box can be used to filter the methods in the list.

The Refresh button can be used to update the list if any of the new functions or procedures you created is not shown in the list.

The functions and procedures that are both on the spec and the body have a lighter color than the ones that exists only in the body. If a method only exists in the spec, its color is yellow.

Clicking a method name will take the cursor to its location in the active editor.

The Editors

The center of the workspace is where the code editors are. The editor has two types of editors: The PL/SQL editors and the SQL editors.

The Gitora PL/SQL Editor opens the first database object in the current tab. Subsequent objects are opened in new browser tabs.

Both editors provide auto complete suggestions. The default list of suggestions are all the words in the current open database object. After you type a dot character, the editor reads the word left of the dot and shows relevant suggestions depending on whether the word is a schema, a table, a view, a package or an object type. In the picture below, you see the editor recommending a list of method names because the user typed a package name.

Note the arrow sign > in the upper right corner of the suggestions box. Clicking the sign will expand the highlighted selection to display its full text. Change your selection and the expanded area will change with your selection. This is a toggle you can turn on/off with each editor separately.

Gitora detects [schema].[package]. syntax and can show suggestions for database objects in other schemas. In the example below, Gitora recommends the methods in the twi_call package which is in the HR schema.

The SQL Pad

Clicking the plus button next to the Body tab, creates a new SQL Pad which you can use to execute PL/SQL blocks, SQL queries, DML and DDL statements. You can create as many SQL Pads as you’d like.

SQL Pads consist of four rows. The toolbar, the worksheet, the query results and the messages rows. Let’s go over these rows one bye one:

The SQL Pad Toolbar

Each SQL Pad has its own toolbar with five buttons. From left to right:

Execute

Click this button to run your SQL statements.

Commit

Use this button to commit your changes. This button works only with a persistent database connection.

Rollback

Use this button to roll back your changes. This button works only with a persistent database connection.

DBMS Output

Use this button to toggle the DBMS Output on or off.

Connection

Use this button to change the current database connection of the SQL Pad.

When you create a new SQL Pad, by default it is connected to the same database user you clicked on to open the Gitora PL/SQL Editor. This connection is not persistent i.e. the Gitora Editor creates temporary database connections when you run the SQL statements by clicking the Execute button.

To change the database connection an SQL Pad is using click the Connect button. The database connection selection dialog shows up.

The dialog has the following actions:

No Connection: Select this option if you’d like to disconnect the editor from all database connections.

Persistent Connection: Toggle this button to on, if you’d like the connection you will select to be a persistent connection.

New Database: Click this button if the database you’d like to connect to does not exist as an option. This will take you to the Create New Database screen explained the the Introduction to the Gitora Editor Home tutorial.

Under the New Database button, you will see the list of databases you can connect. Expand the database you’d like to connect to see the available Oracle users that you can connect to.

Select the Oracle user you’d like the SQL Pad to use and the dialog will close. Your SQL Pad is now using your selection as its connection.

Select New User if you’d like to add a new Oracle username to the registered usernames. Your SQL Pad will use the new Oracle user you will register.

Persistent Database Connections

SQL Pads in Gitora PL/SQL Editor support persistent database connections. To create a persistent connection toggle the Persistent Connection button in the Connection Selection dialog as shown above.

After you select a connection, Gitora will create a persistent connection that the SQL Pad will to reuse every time you execute an SQL statement.

You can also share a persistent connection among other SQL Pads you create. To share a persistent connection:

Create a new SQL Pad.

Click the Connection button.

Toggle the Persistent Connection button to on. A new row titled Open Sessions will show up.

Clicking on the Open Sessions row will display existing persistent connections. Click the one you’d like the new SQL Pad to use and the new pad will share the same database session with the previous pad you created.

Gitora manages the persistent database connections automatically. Closing the last SQL Pad that uses an open database session will close it. Closing the browser tab will also close any unused database sessions.

The file at /gitora/datastore/GitoraEditor/default_pool_properties.json manages several properties that you can edit.

{"maxSizePerUser":25,
"timeout":3600,
"timeoutCheck":300}

maxSizePerUser property sets the maximum number of open database sessions per user.

timeout property sets the maximum number of seconds a session can remain idle before being closed by Gitora.

timeoutCheck property sets the number of seconds Gitora waits before checking if any session has timed out.

The Worksheet

The worksheet is where you write your SQL statements.

The Query Result

If you execute an SQL query, the results will show up in this row.

The Messages

The messages row has two tabs. The Errors tab shows the errors received while executing the SQL statement in the pad and the DBMS Output tab shows the results of the DBMS Output if there are any. Each SQL pad has its own message row.

The Database Object Navigator

The Database Object Navigator consists of two tabs. The Database Search tab and the Schema Object Navigator.

The schema object navigator shows the seven most commonly used database objects in the database user you are connected to. Just like in the Method List column, you can filter the lists for each object by its name and click the refresh button to display newly created objects.

Selecting and copying an object’s name with CTRL+C copies the objects name in lowercase to the clipboard. The object name is not clickable to make copying easier.

Tables

Each table has a table widget that shows its columns and the foreign keys. Both the column list and he foreign keys can be filtered and refreshed.

In the Columns tab, the column names with a star * indicate primary key columns.

Views

Each view has a view widget that displays its columns.

Each view also has a dropdown button with a list of actions. Clicking the Edit button opens the view in the Gitora Editor (current browser tab or a new tab). You can also Compile and Drop a view.

Packages (Types)

Package and Types work the same way. So we will only go over packages.

Each package has a widget that displays its procedures and functions. Similar to the Method List column, the private methods are in dark color and the public methods are in a light color.

Uncompiled packages have a red icon.

Each package also has a dropdown button with a list of actions. Clicking the Edit button opens the package spec and body in the Gitora Editor. If the current tab already has an open database object, the object is opened in a new browser tab. Otherwise, it is opened in the current tab. You can also compile or drop the spec and the body of a package.

If a package’s spec or body is in an uncompiled state, the dropdown will show the relevant compile action in red.

If a package is missing the body, the compile and drop actions for the body will not show up in the button dropdown.

Functions

Each function has a dropdown button with a list of actions. Clicking the Edit button opens the function in the Gitora Editor (current browser tab or a new tab). You can also Compile and Drop a function.

Procedures

Each procedure has a dropdown button with a list of actions. Clicking the Edit button opens the procedure in the Gitora Editor (current browser tab or a new tab). You can also Compile and Drop a procedure.

Triggers

Each trigger has a dropdown button with a list of actions. Clicking the Edit button opens the trigger in the Gitora Editor (current browser tab or a new tab). You can also Compile and Drop a trigger.

Messages

The bottom row in the editor shows error messages received from the database when you save a database object. Clicking an error will take the cursor to the location of where the error has occurred and highlight that row for a few seconds.

Database Search

The other tab in the third column of the workspace is the Database Search tab. In this tab, you can search for any text in the entire database code base or only in a schema.

The searches are case insensitive.

The results are grouped by schema and database object.

The icons indicate the type of object that the searched term is found in. The closed package icon indicates a package spec and an open package icon indicates a package body.

Click on an object to view the lines in the object that contain the searched term.

Clicking the line number will open a new Gitora PL/SQL Editor in a new tab. The new tab will have the database object with the search term open in the editor and the line number you clicked on will be the top line in the editor.

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.

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

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.

Agile Development with a Single Database

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 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 schema in a single database
  2. If you are using multiple development schemas in a single database (or at least if you are open to the idea.)

If You Are Using One Shared Development Schema

Let’s assume that our development database is called DEV and the shared development schema in DEV is called HR. In other words, both Feature A and Feature B are being developed in the same code base. Let’s also assume that there is no other database for testing. We test in DEV in the schema called HR_TEST.

Assume that HR and HR_TEST schemas are managed by two Gitora single schema repos (SSR) repos named HR_REPO and HR_TEST_REPO, respectively.

In this case, the commit history in HR_REPO 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 HR_REPO 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 HR_REPO.

At this point the commit history in HR_REPO looks something like this:

And finally, the next steps are:

  • Pull the new version from HR_REPO to HR_TEST_REPO using Gitora.
  • Run tests on the HR_TEST schema. 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 the HR schema, 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 HR and HR_TEST schemas is as easy as clicking a button. 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 the HR schema to disable/remove Feature A. With only one shared development database ( i.e. development environment), there is no easy way to remove Feature A.

If You Are Using Multiple Development Schemas

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.

Here is our initial set up:

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

Obviously, all Git operations mentioned below are executed using Gitora. Gitora takes care of updating the database code after a Git command is executed.

Developer 1:

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

As an example, the commit history of DEV1 looks like this:

Developer 2:

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

The Git commit history in Gitora DEV2 looks like this:

Moving Code Between Schemas

At any point in time, if Developer 1 or Developer 2 decides to send code to the HR_TEST schema (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 repos, 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 takes place.

Developer 1:

  • In our example , we assume that Developer 1 is the first developer sending her commits to the HR_TEST schema.
  • Merge DEV1/featureA to DEV1/master. (DEV1/featureA –> DEV1/master)
    After the merge, commit history in DEV1 looks like this:
  • Pull master branch from DEV1 to 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 a local branch. In our example TEST fetches DEV1/master from the DEV1 repo 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:

Now it is time for Developer 2 to send her changes to the HR_TEST schema. She does the following steps:

  • Pull master branch from the TEST repo to the master branch in 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)
  • Merge DEV2/featureB to DEV2/master. (DEV2/featureB –> DEV2/master) After the merge, the commit history looks like this:
  • Pull master branch from the DEV2 repo to the TEST repo. (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 HR_TEST schema (and in the TEST repo) is a merge of Feature A and B. 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.

Removing Feature A from the Deployment

This workflow enables the IT team to exclude Feature A from the next version of the software at any point in time before deploying it to the production environment.

To exclude Feature A from the deployment in the example above, follow the steps below:

  • 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 HR_TEST schema only contains code changes related to Feature B. We can use Gitora to extract a DDL script from HR_TEST which contains only the changes made between the initial and final state of TEST/master and use this script to deploy Feature B to the production environment.

Gitora helps developers execute this workflow with a point&click GUI:

  • Gitora keeps track of all changes made to the PL/SQL code in HR_DEV1, HR_DEV2 and HR_TEST schemas automatically.
  • Gitora updates the source code in the Oracle Database automatically when the executed Git command changes the files in a Git 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 HR_DEV1, HR_DEV2 and HR_TEST is as easy as clicking a button. 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 to switch between code branches automatically. (For example if the DEV1 repo switches its active branch from featureA to the master branch, Gitora automatically updates the code in the HR_DEV1 schema to reflect this change.)

Download Gitora now and try this workflow in your environment.

How to Create a Single Schema Repo

This article shows how you can create a Single Schema Repo (SSR) wit Gitora 6.

Sign in to Gitora. Next click Git –> Repo –> Create Repo –> Single Schema Repo.

The Single Schema Repo Creation Dialog show up.

  1. Enter a unique repo name. (In this example, we’ll call it SSR_1)
  2. Select the schema the SSR is associated to. (In this example it will be associted to the HR schema)
  3. Leave the Manage Schema Changes unchecked for now.
  4. Click save.

The repo SSR_1 is created. SSR_1 can only manage objects that are in the HR schema. (Other than this limitation, the SSR_1 repo works just like any other repo.)

Adding Database Objects To a Single Schema Repo

This process is the same as adding objects to a regular Gitora repo (which is now called a Multi Schema Repo MSR)

Select the SSR_1 repo from the example above from the combobox if it is not already selected.

In the menu bar, click to Git –> Add/Remove Objects. The Add or Remove Database Objects Dialog shows up.

Select the database objects you’d like to manage with this repo and click Save. Use the Object Type dropdown to switch between different object types.

Note that the schema dropdown is disabled because we are working with a Single Schema Repo.

Commit, Reset, Create/Change/Merge/Delete Branch Operations

These commands work just like they do for regular repos. There are no changes.

Pulling from Other Repos

In Gitora 5, repos could only pull from repos managing objects in other databases. In Gitora 6, single schema repos can also pull from other single schema repos managing objects in the same database.

The example below shows how you can pull from one SSR to another managing objects in the same database. All three pull operations (i.e. Pull, Fetch&Merge, Switch to Branch in Another Repo) work the same way.

Let’s assume we have two schemas named LOGISTICS and LOGISTICS_TEST and two SSR’s named REPO_LOGISTICS and REPO_LOGISTICS_TEST, managing these schemas, respectively. Read this article to learn how you can initialize these repos, correctly.

Here are the steps to pull from REPO_LOGISTICS to REPO_LOGISTICS_TEST.

  1. Open the REPO_LOGISTICS_TEST repo in Gitora.
  2. From the menu, select Git –> Pull

The Pull from Another Repo to Active Branch Dialog shows up.

3. Select the name of the single schema repo you want to pull from. (In our case, it is the LOGISTICS_REPO.)

4. Type the name of the branch you want to pull from.

5. Click the Pull button.

That’s it. All changes in LOGISTICS_REPO are now merged to the LOGISTICS_REPO.

FAQ

Can I create multiple Single Schema Repos that are associated to the same schema?

Yes. You can create multiple SSR’s that are associated to the same schema. This is especially useful in dividing the database objects in the schema into multiple logical groups.

Can Single Schema Repos pull from other databases?

Yes. It works exactly like it does in Gitora 5. In the Gitora app, select Git–>Other Databases–>Pull from the menu bar.

Can I track changes to tables with a Single Schema Repo?

Yes. Just like with regular repos, the same SSR can be used for both managing packages, procedures etc… and tracking table DDL’s. A single schema repo can only track the table changes in the schema it is connected to. Please read this article to learn how Schema Management in Gitora works and read this article to learn how an SSR manages the schema it is assoicated to.