TortoiseSVN and Subversion Cookbook Part 11: Subversion and Oracle

It is only recently that the tools have existed to make source-control easy for database developers. Although entire build-scripts for databases could be stored in Subversion, tools like Source Control for Oracle (SoCO) now allow for a more effective source-control at object-level.

This is the eleventh installment of the TortoiseSVN and Subversion Cookbook series, a collection of practical recipes to help you navigate through the occasionally subtle complexities of source control with Subversion and its ubiquitous GUI front-end, TortoiseSVN. So far this series has covered:

  • Part 1: Checkouts and commits in a multiple-user environment.
  • Part 2: Adding, deleting, moving, and renaming files, plus filtering what you add.
  • Part 3: Putting things in and taking things out of source control.
  • Part 4: Sharing source-controlled libraries in other source-controlled projects.
  • Part 5: Embedding revision details within your source files.
  • Part 6: Working with tags and snapshots.
  • Part 7: Managing revisions and working copies.
  • Part 8: Getting the most from log messages.
  • Part 9: Setting up a production server, browsing your repository, and viewing statistics on your installation.
  • Part 10: Source control for SQL Server, plus multiple Subversion clients.

Until recently, if you mentioned “database” and “source control” in the same breath, it meant that you were talking about SQL Server. That’s no longer the case since Red Gate released Source Control for Oracle, a great initial offering to bring source control to that “other” database(!).

Reminder: Refer to the Subversion book and the TortoiseSVN book for further reading as needed, and as directed in the recipes below.

Introduction

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 new columns to a few tables and perhaps deleting a couple 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. TortoiseSVN or 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. You create a project in the application by specifying a database connection, one or more schemas, and a source control type and location. SoCO supports only Subversion and TFS at present. Since this is ostensibly a cookbook about Subversion, I am going to focus exclusively on Subversion in the ensuing text, but the principles apply equally to TFS.

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.

Figure 1 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.

1778-domain%20coverage-a8c2b3b4-ae03-4de

Figure 1 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.

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.

Subversion Commands

SoCO’s user interface provides just a small subset of those that are available from TortoiseSVN, but the Pareto principle applies in that the available commands cover 80% of what you need to do. On a day-to-day basis, perhaps even more. When you do need to do something not covered by SoCO’s capabilities, you can always use TortoiseSVN’s assorted GUI tools or the SVN command line tools.

Here are the commands implemented by SoCO with their TortoiseSVN equivalents:

TortoiseSVN Source Control for Oracle
SVN Check for Modifications Get Latest tab
SVN Update Apply selected changes to database button on Get Latest tab
SVN Commit Check In button on Check In tab
SVN Show log History tab
SVN Diff Automatic on each tab
SVN REsolve Resolve Conflict on context menu on Get Latest or Check In tabs

SoCO polls your database every 60 seconds. This polling interval is configurable by setting the <PollingInterval> element in the configuration file (%localappdata%\Red Gate\Source Control for Oracle 1\ConnectionStore.xml) to the number of milliseconds (default is 60000, i.e. 60 seconds).

When changes are discovered, they automatically populate the Check in tab so you can then commit them to source control. SoCO does not, however, automatically report changes in the other direction, i.e. changes in your repository. You can check this any time, though, with the Refresh button on the Get latest tab. This is analogous, if not exactly symmetrical, to how TortoiseSVN operates. TortoiseSVN automatically monitors your file system, updating icon overlays on changed files from a green check mark to a red exclamation point or vice versa. In the other direction, TortoiseSVN similarly requires you to do a manual SVN Update (or SVN Check for Modifications if you prefer to separate the check operation from the pull operation) to see what has been changed in the repository.

Because SoCO separates the check for modifications from the actual pull operation, it adds a convenience: whenever it notices database changes during its regular scan it will, in fact, automatically poll source control as well.

The next few sections detail the commands to use SoCO. Once you open a project in SoCO the interface consists simply of a stylized Windows title bar, a project name, and the three main operation tabs: Get latest, Check in, and History (see Figure 2).

For details on setting up a Source Control for Oracle project , see the wallchart accompanying this article as well as the getting started guide from Red Gate on their website.

SVN Commit

The Check in tab shows the changes in the database that need to flow from your database to the source control repository. Figure 2 is a typical display when you first create a project. Once you specify a connection to your database during project creation, SoCO scans your schema and generates DDL to create every database object. Each of these are listed in the Check in tab with a create action in the upper pane. The lower pane shows a visual differencing though it may not look like it: here you are comparing DDL for the object with nothing (yet) in source control, so the right hand side is empty. The graphic between the two panes acts as a visual cue that you are adding a new table rather than deleting it.

Commit is an atomic operation in Subversion. None of the changes are committed unless all of them are committed. That is, if one object is prevented from being committed for any reason, all of them are rolled back.

1778-1-931d87ed-a9b2-4f09-acb0-09b8e54dc

Figure 2 The Check In tab moves database changes into source control.

SoCO adds one other powerful feature for assisting with commits. The environment upon which SoCO operates is a database and not just a file system. Within a database environment there are, of course, all manner of dependencies between objects. SoCO automatically accounts for this so when you are preparing to commit changes, it warns you if have neglected to include dependent objects. In Figure 3, for example, there are two tables with changes. One has a conflict and is not ready to be committed, so its checkbox is unticked. But that Widgets table is referenced by the other object, the WidgetPrices table, which is ticked. Thus, SoCO provides the warning at the top explaining that you might create an inconsistent state by this partial check in.

1778-dependencies-597eebe6-85f7-43a1-b57

Figure 3 SoCO intelligently provides assistance with dependent objects.

SVN Check for Modifications and SVN Update

The Get latest tab displays changes in much the same way as the Check in tab, but now showing changes that need to flow from source control to database.

In Figure 4 there are two changes detailed on the Get latest tab: the upper pane enumerates the changes, providing checkboxes for you to tick the ones you want to act upon. Here one is an edit action (something changed in an existing DB object) and one is a create action (a new DB object). The lower pane presents a visual differencing window; if you look closely you can observe that the version in source control has a color field not present in the database. Now here is the pop quiz: is this field being added or deleted?

If you do not use source control regularly then you may have to stop each time you see Get latest and mentally map that with sync from source control to database. The graphic bar between the two panes gives you a visual cue to avoid the potential source of confusion: Here you can instantly see that you have changes that need to flow from source control (where the color field exists) to database (where the color field is absent). Thus, you can conclude that the color field is being added, not deleted.

Be aware that, unlike a Subversion commit, the operation of applying database changes is not atomic. If an attempted database change fails for any reason, the objects processed successfully up to that point are not rolled back.

Finally, it is worth contrasting how SoCO selectivity differs from TortoiseSVN. With TortoiseSVN the SVN Update operation always does a blanket update from your current location. If this location is the root of your working copy, it updates the entire tree. If the location is some child, it updates the entire subtree under than child. SoCO, on the other hand, does not have the concept of a ‘current location’; it displays all changed objects on the Get Latest tab, yet it has greater selectivity: it provides check boxes on every item so you be selective down to the level of the individual object as to what you want to update.

1778-get%20latest%20changes-d55b3396-cc0

Figure 4 The Get Latest tab updates database objects with changes from source control.

SVN Show Log

The History tab shows you an audit trail of your source control repository that is similar to the SVN Show Log. However, while they both display three panes of information, the panes are different. The three panes for TortoiseSVN are: list of revisions, log message for current revision, and list of files (objects in this case) for current revision. The three panes for SoCO are: list of revisions, list of objects for current revision, and visual difference for current object. Thus, with SoCO, you can quickly switch between viewing differences for each object just by selecting a different one in the middle pane.

The difference pane always compares the selected revision with the previous revision, i.e. revision nwith revision n minus 1. SoCO gives you a visual cue to indicate this with the before and after indicators at the top of the difference pane. It would be useful to have the option of selecting two revisions in the top pane and have the differences reflected in the difference pane.

While you gain the very handy capability to see differences quickly and efficiently, you lose a little convenience for seeing log messages of any length; there is no way in SoCO to see any more than the first few words. It would be nice if the whole log message could be in a tooltip when you hover over the abbreviated message.

1778-history-1c6c5dcd-1924-43cf-a933-cef

Figure 5 TortoiseSVN (top) shows a list of revisions, the log message for the current revision, and a list of files for the current revision in its log dialog. SoCO (bottom) shows a list of revisions, a list of objects for the current revision, and the visual difference for the current object.

SVN Diff

You’ll have seen that viewing differences in SoCO is automatic-the lower pane in every tab automatically displays a visual differencing for the current object, whether it is added, modified, or deleted. Examining the changes in the next modified object is as simple as selecting it in the object list on any of the Get latest, Check in, or History tabs and you see the differences right there in the bottom pane. In many cases that is quite sufficient, but for large or complex objects it would be nice if you could open a separate, dedicated difference viewer, perhaps by double-clicking on the object.

SVN Resolve

When you have conflicting changes on the same object, where the representation in source control and the object in the database have different changes, SoCO will let you know: both the Get latest tab and the Check in tab will report the conflict. Figure 6 shows on the Check In tab that the WidgetDev.Widgets table has a change in the database and on the Get latest tab that it also has a change in source control, so there is a conflict requiring your attention. (The Get latest tab shows a second, unrelated change in source control to the WidgetTest.Widgets table.)

SoCO does not provide any ‘merge’ capability to resolve the conflict. Pressing the Resolve button simply lets you select whether to use the one in source control or the one in the database. Typically you will want to take your database copy, which is effectively your local copy, and to compare the changes against the source control copy: Then, you’ll want to discuss with your colleagues on your project before creating a modified copy to disseminate to both your database and source control.

1778-conflict-6c6326f4-2818-48a7-b0d3-90

Figure 6 A conflict appears on both the Check in tab and the Get Latest Tab.

Other Considerations

Even with good tools, the task of controlling the source of database objects is not without a few difficulties.

  1. SoCO does not yet provide a mechanism for reverting to a previous revision. However, there is a workaround:
    • Checkout (or update) a working copy in TortoiseSVN to your file system.
    • Revert to the revision desired within TortoiseSVN. This gives you that target revision, but just in your modified working copy.
    • Commit all the resultant changes in TortoiseSVN, effectively rolling back your repository with that earlier revision now in the HEAD position, the only place SoCO can see it.
    • Finally, return to the Get Latest tab in SoCO. It should report the reversion as new changes; apply those changes to the database.

Note that, depending on your environment, you might not have the option of “polluting” your trunk like this. You might need to be a bit more elaborate, doing the revert operation on a branch then creating a new SoCO project pointing at that branch.

  1. Something as simple to a human as renaming a table is difficult for a computer to recognize. In fact, SoCO would see a table rename as dropping one table and creating another. But that will discard a lot of data that you do not want to lose! SQL Source Control introduced custom change scripts (also called migration scripts) to make that task more manageable; likely SoCO will follow suit. (Also see Mark Caldwell’s blog post on migration scripts for SQL Server for more details)
  2. Readers of this series know that I devoted an entire chapter (Part 5) to the Subversion keyword feature. Alas, SoCO does not support keyword expansion and in fact causes problems for any database files you have instrumented with keywords (commonly stored procedures). If, for example, you commit a file with an $Id$ place holder, the commit causes Subversion to update that with an appropriate value but SoCO then considers that a change to the file. The only workaround at present is to remove the keyword place-holders from database files.
  3. SoCO does not support static ( lookup or reference) data, i.e. data that is non-transactional that your application depends on. A simple example of this might be a table of US area codes, which went through a major “rewrite” a few years ago but before that they were probably invariant for fifty years or more. So it might be useful to have the data in this lookup table under source control because your code might actually be hard-coded for specific values. SoCO does not yet support static data; at present it only handles storing the database schema.