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:
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:
@@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.