Today, we’re happy to announce the general availability of Gitora 3.
Gitora 2 enabled developers to manage database objects that support the CREATE OR REPLACE command, namely PACKAGES, PROCEDURES, FUNCTIONS, TRIGGERS, VIEWS, SYNONYMS and TYPES in a Git repository. (In Gitora terminology, we call these “Soft Objects”).
Gitora 3 goes a step further and enables developers to manage every database object in an Oracle Database with Git even if the object does not support the CREATE OR REPLACE command. (In Gitora terminology, we call these “Hard Objects”.)
Specifically, Gitora 3 allows developers to manage TABLES, INDEXES, SEQUENCES, CONSTRAINTS, TABLESPACES, MATERIALIZED VIEWS and every other schema or non-schema objects with Git.
Let’s examine how you can manage these objects with Gitora 3 with an example.
Introducing Managed Schemas
Gitora 3 introduces the concept of a Managed Schema. A managed schema is an Oracle Database Schema that Gitora tracks for execution of Hard Object DDL’s. In other words, you register one or more Oracle Schemas to a Gitora repository. Once a schema is registered to a repository, Gitora will track every hard object DDL executed for that schema. A repository can manage many schemas but a schema can only be managed by one repository.
Adding A Managed Schema to a Gitora Repository
In the Gitora App, select a repository. Click Local Commands –> Add/Remove Managed Schemas menu option. A dialog shows up.
Select the schema(s) you’d like to manage.
Click OK. The dialog closes. The screenshots above show the HR schema being added to a repo as a managed schema.
The Model Package
After a schema becomes managed, Gitora creates a new package in the schema automatically. This package is always called GITORA_MODEL[X] where [X] is a unique number that identifies the package. In our example above, Gitora created the GITORA_MODEL1 package in the HR schema and added this package to the Git repository which is managing the HR schema:
The screenshot above tells us that repo1 is managing the HR schema.
Tracking Soft Objects
There are no changes in Gitora 3 for soft objects. You continue managing soft objects just like you’ve been managing them with Gitora 2. In other words, any repo can manage any number of soft objects and any number of schemas.
To demonstrate this let’s add a dummy package called PACKAGE1 to HR and manage it with the same repo named repo1:
Select Local Commands –> Add/Remove Soft Objects menu item. A dialog shows up where you can select which soft objects you’d like to manage with repo1. Note that, GITORA_MODEL1 package shows up as a managed package because it was automatically created to manage the hard objects in the HR schema.
Add PACKAGE1 as a managed package and click OK. The Gitora App shows the following object list for repo1:
Tracking Hard Object DDL’s
Let’s execute the following DDL statements for the HR schema: (Note that in the database session we first have to log in to Gitora with a valid Gitora user. In this case, we logged in as admin.)
[sql]
ALTER TABLE COUNTRIES MODIFY (COUNTRY_NAME VARCHAR2 (200))
ALTER TABLE EMPLOYEES ADD (MIDDLE_NAME VARCHAR2 (25))
ALTER TABLE COUNTRIES ADD (CURRENCY_NAME VARCHAR2 (200))
[/sql]
Finally, let’s also modify a soft object and add a new dummy function to PACKAGE1 called function1.
To commit our changes, let’s switch to Gitora App and click the commit button. The My Objects Dialog shows up, displaying the list of soft objects and hard object DDL’s ready to be committed to Git. Note that the dialog only shows the changes made by the signed in developer:
In this dialog, you can choose which packages or hard object DDL’s you’d like to include in this commit by checking/unchecking the checkbox in the Include? columns of both grids.
Note that, the commit registers the package update and the table DDL statement as a single logical commit which is a key benefit we’d like to get from the version control system.
After the commit, we receive the following message from Git:
Note that, the Git commit not only included PACKAGE1 but also the GITORA_MODEL1 package. After the Git commit is successful, the new GITORA_MODEL1 package code looks like this:
[sql]
CREATE OR REPLACE
PACKAGE BODY gitora_model1 IS
function doModel return clob is
v_log_cl clob;
v_ddl_cl clob;
v_cursor_int integer;
return_value integer;
procedure log(in_text_cl clob) is
begin
v_log_cl:=v_log_cl||in_text_cl||chr(10);
end;
begin
/*GITORA GENERATED COMMENT. DO NOT EDIT OR REMOVE. ONLY WRITE CODE UNDER THIS LINE.*/
null;
/*SCRIPT FOR HR.COUNTRIES by user:admin */
begin
v_ddl_cl:=’ALTER TABLE COUNTRIES
MODIFY (
COUNTRY_NAME VARCHAR2 (200)
)
‘;
log(v_ddl_cl);
execute immediate v_ddl_cl;
log(‘DDL executed successfully.’);
exception
when others then
log(‘ERROR:’||’ ‘||SQLErrm||’ ‘||dbms_utility.format_error_backtrace);
end;
/*SCRIPT FOR HR.EMPLOYEES by user:admin */
begin
v_ddl_cl:=’ALTER TABLE EMPLOYEES
ADD (
MIDDLE_NAME VARCHAR2 (25)
)
‘;
log(v_ddl_cl);
execute immediate v_ddl_cl;
log(‘DDL executed successfully.’);
exception
when others then
log(‘ERROR:’||’ ‘||SQLErrm||’ ‘||dbms_utility.format_error_backtrace);
end;
/*SCRIPT FOR HR.COUNTRIES by user:admin */
begin
v_ddl_cl:=’ALTER TABLE COUNTRIES
ADD (
CURRENCY_NAME VARCHAR2 (200)
)
‘;
log(v_ddl_cl);
execute immediate v_ddl_cl;
log(‘DDL executed successfully.’);
exception
when others then
log(‘ERROR:’||’ ‘||SQLErrm||’ ‘||dbms_utility.format_error_backtrace);
end;
/*GITORA GENERATED COMMENT. DO NOT EDIT OR REMOVE. ONLY WRITE CODE ABOVE THIS LINE.*/
return v_log_cl;
end;
end;
[/sql]
The GITORA_MODEL Package
The GITORA_MODEL packages contain the DDL statements you commit to your Git repository for the schema they are in. You can edit them any way you’d like to change the DDL statements, call other functions and procedures etc… In other words, they are no different than any other package you are managing with Gitora.
GITORA_MODEL.doModel function executes the DDL statements stored in the package. You can use the doModel function to execute the changes in a target database. For example, after development is completed for a new version, you can pull the new version to from DEV to the TEST database and simply execute the GITORA_MODEL.doModel function to transfer the hard object updates to TEST.
Errors during its execution does not prevent the doModel function from executing remaning DDL statements. The function captures these errors and returns them as a CLOB value. This way, the same GITORA_MODEL package can be executed many times even if contains DDL statements that are previously executed in the target database (because these statements will silently fail). The GITORA_MODEL packages requires minimal maintanance, if any at all.
Non-Schema Objects and GRANTS
Not every database object has a schema. For example, TABLESPACES do not have an owner schema. DDL statements for non-schema objects are tracked by Gitora if the Oracle User(schema) who executed the statement is being managed by Gitora. For example, in the case above, if a DDL statement to create a TABLESPACE is executed by the Oracle User HR, then this DDL will be tracked by repo1.
Similarly, any GRANT and REVOKE statement will be tracked by the Gitora repo which is managing the Oracle User(schema).
Customization
You may not want to manage every object or object type with Gitora. Moreover, you may want to define complex rules to decide which object is managed by which repo. Customizing what types of objects Gitora should manage and in which repo is very easy using PL/SQL. Gitora uses four database level DDL triggers to detect DDL statement execution. By editing these triggers, you can define any kind of rule you like.
For example, a common rule used by Gitora customers is to allow developers to create tables for testing purposes. These tables do not need to be managed by Gitora. One of the easy ways of implementing this rule is to tell developers to use a certain prefix such as Z_ for test tables, and exclude any table DDL statement from executing Gitora API’s in the four DDL triggers if the table name starts with Z_.
Existing Customers
Gitora 3 is a free upgrade for all current customers. Please allow us 2-3 weeks to send us your new license scripts.
We’ll have more tutorials and webinars about Gitora 3 in the near future. Now download the Gitora 3 trial and start playing! 🙂
