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:
- 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.
- Pull from HR_HARD_OBJECTS_REPO to HR_JOE_HARD_OBJECTS_REPO and to HR_JANE_HARD_OBJECTS_REPO.
- 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.