Put your Oracle Database in Source Control

The first time you consider it, it may seem a strange idea to put a database into source control but, on reflection, it makes sense. The reason to do so is the same reason that you put anything else in source control: to be able to revert to previous releases with minimal effort. Imagine that, after you release version 1.0… Continue Reading →

The first time you consider it, it may seem a strange idea to put a database into source control but, on reflection, it makes sense. The reason to do so is the same reason that you put anything else in source control: to be able to revert to previous releases with minimal effort. Imagine that, after you release version 1.0 of your product, you put it in a new feature for version 2.0 that requires you to add a couple of new columns to a few tables and perhaps delete a couple of columns from some others. Once you make these changes to your database, then you can never revert to release 1.0 because the database is now broken from the perspective of the 1.0 codebase. In order to maintain the integrity of each revision in source control, you not only can but you must include your database schema in source control so as to be sure of deploying both application and database in sync.

As to how to put a database in source control, it is easier than you might think, because you are not storing the data contained in the database but only the structure or metadata of the data, i.e. the Data Definition Language (DDL) statements that are used to define the database tables, triggers, views, procedures and functions. You will normally also want to keep the Data Control Language (DCL) statements that define who can access what. Your data does not belong in source control just as, for example, certain files in Visual Studio that store your personal preferences do not belong in source control. In the latter case, those settings belong exclusively to you. In the database case, the number or content of records in your tables has no bearing on the current revision of your software.

Since the database metadata can easily be represented in code, it can be easily maintained under source control. But versioning the database code is only half the issue; you also have to keep this DDL in sync with your actual database. Subversion (or indeed any source control system) is designed to handle only the former. One possibility is the manual approach: whenever you need to revert to a different revision, you first revert the code using TortoiseSVN/Subversion then you drop the changed tables and run the correct DDL to recreate them. Of course, draconian action like that has the unfortunate side effect of losing all your data! There are likely other manual schemes you can devise for source-controlling your schemas, but like most software bookkeeping, it pays to automate whenever possible, and source control is no exception!

Enter Source Control for Oracle

Source Control for Oracle (SoCO) is the latest offering from Red Gate in the source control arena. Just as their SQL Source Control was a breakthrough for managing source control for SQL Server databases, Source Control for Oracle now provides similar automation and power for the Oracle world. SoCO, in contrast to SQL Source Control, is a standalone application, meaning you can use it in conjunction with any SQL editor. You create a project in the application by specifying a database connection, one or more schemas, and a source control type and location.

Once your project is established:

  • Any database changes appear on the Check In tab; commit them to source control with the Check In button.
  • Any changes in the source control repository appear on the Get Latest tab; realize those changes in your database with the Apply Selected Changes to Database button.

The image below shows the realm of SoCO compared to that of a traditional source control client like TortoiseSVN. As illustrated, SoCO operates between the repository and the database, contrasted with traditional source control operating between the repository and the file system. Ultimately you want your database, your Subversion repository, and any working copies on your file system to reflect the same schema. The figure shows how data flows among them with the corresponding actions indicated.

Screenshot: Source Control for Oracle data flow

Data flow between database and repository and between file system and repository.

Thus, if you make changes in the database—through the command line with SQL*Plus, or interactively with SQL Developer, or via some other mechanism—SoCO notices these, analyzes them, and displays them in a list of items to take action on the Check In tab. You may then select any or all available changes and commit those to your source repository. As with conventional source control, you can group changes that are related and so check them in together with a common commit message.

Screenshot: Source Control for Oracle user interface

With Source Control for Oracle, checking in changes is as simple as writing a log message and clicking a button.

Going the other direction, repository changes—made either by you or a colleague perhaps editing DDL files offline and committing with TortoiseSVN, or SVN command line, or another SVN client—are noticed by SoCO, which again displays all those noticed changes in a list of items on the Get Latest tab. You can then take action by applying any or all available changes to the database; that is, update your live DB schema to match the files stored in Subversion.

TortoiseSVN and Subversion Cookbook

Download your free copy of TortoiseSVN and Subversion Cookbook – Oracle Edition and use the recipes to work better, faster, and do things you never knew you could do with SVN. If you’re new to source control, this book provides a concise guide to getting the most out of Subversion.

Both TortoiseSVN and Subversion have very good reference books available from the people who write the software. So why another Subversion book? This book is, like the title says, a cookbook rather than a reference. It is literally a collection of recipes; it is designed to help you work with TortoiseSVN or Subversion. And if you’re new to Subversion or new to source control completely, this book will give you a good perspective on what Subversion as a source control solution has to offer.

Ref: TortoiseSVN and Subversion Cookbook Part 11: Subversion and Oracle