Oracle database: schema versioning

When working on a project where the data model isn’t completely stable, you want to be able to run scripts changing one or more database schemas. But how do you make sure that all updates run successfully. And how do you avoid running the same update script twice one a schema.

One a project some years back I had the opportunity to work out a way to handle this.

The project required a lot of database changes – lets called them patches for short.

The challenge was further that a patch procedure would be needed on the production environment as well. My client had offices worldwide, but very few (if any) had IT-staff available.

So when sending a new release to the offices the process has to be very simple to follow (this was before remote administration was truly feasible).

Anyway, the same idea can be used to simplify applying changes to a database on a build or test server.

The main idea is to have one big script that calls all patches ever applied to the database (the “all-script”).
This script will, for all patches, call a script the determines if the patch need to be applied or not. To that end we invent a database schema version.

So the “all-script” will logically contain something like this:

start patch1-1 if database schema version is "1.0"
start patch1-2 if database schema version is "1.1"
....

The patches are required to set the database schema version to a new value upon completion. We’ll get back to that.

The script used to run all the patches look like this:
dbvers-runif.sql

define v_script="&1"
define v_model="&2"
define v_version="&3"
set serverout on
set termout off
set feed off
spool dbvers.tmp
exec dbvers.util.makestart('&v_script','&v_model','&v_version');
spool off
set termout on
start dbvers.tmp
undefine v_script v_model v_version

Notice the call to dbvers.util.makestart

The all script could look like this:
all.sql

@@dbvers-runif patch1 DUMMY 1.0
@@dbvers-runif patch2 DUMMY 1.1
@@dbvers-runif patch3 DUMMY 1.2
@@dbvers-runif patch4 DUMMY 1.3
exit

When this is run on a database where the schema has been updated already to version 1.2. This happens.

dbvers IGNORED: patch1 will not start. Model (DUMMY) version was 1.2. Expected 1.0
dbvers IGNORED: patch2 will not start. Model (DUMMY) version was 1.2. Expected 1.1
Patch 3: This one fails
....

The UTIL-package is where all the good stuff is. Here are the procedures CRUD methods for the database versionings (expect for delete – haven’t implemented that one).

CREATE OR REPLACE
PACKAGE BODY UTIL
AS
  FUNCTION GetVersion(p_model VARCHAR2)    RETURN VARCHAR2
  IS
  BEGIN
    for a in (
      select version from MODELVERSIONS where modelid = p_model)
    loop
      return a.version;
    end loop;
    
    raise model_not_found;
  END;
  
  PROCEDURE SetVersion(
              p_model VARCHAR2, 
              p_version VARCHAR2) 
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_version varchar2(10);
  BEGIN 
    v_version := getversion(p_model);
    
    update modelversions set version = p_version 
    where modelid = p_model;

    if sql%rowcount = 0 then
      insert into modelversions values (p_model, p_version);
    end if;

    commit;

    dbms_output.put_line('DBVERS : '||p_model || ' = '||p_version);
  END;
  
  PROCEDURE FailIfNotVersion(p_model   VARCHAR2,p_version VARCHAR2) 
  IS
    v_version varchar2(10);
  BEGIN
    v_version := getversion(p_model);
    if v_version <> p_version then
      raise_application_error(-20001,p_model||': model version was '||v_version|| '. Expected '||p_version);
    end if;
  END;
  procedure makestart(p_script varchar2, p_model varchar2, p_version varchar2)
  is
    v_version_found varchar2(10);
  begin
    v_version_found := getversion(p_model);

    if (p_version = v_version_found) then
	dbms_output.put_line('start '|| p_script );
    else
        dbms_output.put_line('prompt dbvers IGNORED: '||p_script||' will not start. Model ('||p_model||') version was '||v_version_found|| '. Expected '||p_version);
    end if;
  end makestart;
END UTIL;
/

Simple patch example:
Here is a simple patch example. The idea is to stop processing if an error occurs (if you have some DDL that might fail – and failing is OK – then use WHENEVER SQLERROR CONTINUE in that part of the script.

whenever sqlerror exit
REM Clearly something should happen here.
REM And if any of it fails ... the script will exit without setting the version.
prompt Patch1: doing some work
@dbvers-set DUMMY 1.1

Table definition – in my database:
Finally the table used.

CREATE TABLE "DBVERS"."MODELVERSIONS"
  (
    "MODELID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "VERSION" VARCHAR2(10 BYTE) NOT NULL ENABLE,
    CONSTRAINT "MODELVERSIONS_PK" PRIMARY KEY ("MODELID")
  );

I hope you find it usable.

This entry was posted in Software development and tagged , . Bookmark the permalink.

Leave a Reply