PL/SQL Source Code Control inside the database – After Compile trigger for automatic archiving

Source code control for PL/SQL is often discussed in the organizations that I encounter. The database itself will always hold the latest version of the PL/SQL objects we are using or developing. The sources can of course also be held in files and those files can be managed in Source Code Control systems like CVS, Subversion, ClearCase, Visual Source Safe etc. that do version control.

Many tools for PL/SQL development like TOAD and PL/SQL Developer or even JDeveloper, do not readily integrate with such tools for PL/SQL code. Besides, most of these tools are used by developers directly on the PL/SQL source as it is held in the database. Developers do not write the code to file all the time and they certainly do not check in their code after each change they make. They compile any changes, sending the latest version of their code to be held in the database – overwriting the previous state of their PL/SQL object – and that is that. Perhaps after a number of compilations they may save to a file and at the end of the day or some large task they may check in to the Source Code Control system.

The potential issues with this way of working – and believe me, I have run into them on many occasions – is that developers often lose stages of their code that later on they want to retrieve.

For example: you start working in the morning on a source that you retrieve from the filesystem or source code control environment. You make changes, compiling them as you go along. At some point, you have a pretty good intermediate result. You
then continue with some intricate changes and you make some additional modifications and all of a sudden, you have messed up your code. And you would really like to return to that intermediate result. But: where is it? No longer in the database – overwritten by the next compilation. Not on the file system, as you forgot to save. And certainly not in the Source Code Control repository, as you did not check in.

Then there is the everpresent risk of two developers working on the same package at about the same time. Compilation by the second developer will immediately override and therefore discard any changes made by the first.

In this article I would like to outline a very simple mechanism for using the database itself as archive for all these intermediate versions. The essential pieces for this ‘working archive’ or operational repository: a table, a package and a trigger. The machinery is put into motion by a Oracle 9i and higher DDL Event trigger, fired on the CREATE (or compile) event.
The table PLSQL_ARCHIVE will hold earlier versions of the PL/SQL objects in the schema you are working on. For each previous version, it will
The table is set up to hold a number of values for each Version of PL/SQL Objects: the PL/SQL source, the timestamp for the compilation/creation, the status, size and errors and when provided by the developer through annotations in the PL/SQL source also the Author, the Version, a Branch, the Priority and possibly additional Comments. We define the table like this:

create table plsql_archive
( object_name   varchar2(128)
, object_type   varchar2(19)
, object_owner  varchar2(32)
, creation_time date
, status        varchar2(7)
, source        clob
, errors        clob
, author        varchar2(128)
, version_label varchar2(32)
, seq           number(10)
, branch        varchar2(32)
, source_size   number(10)
, priority      varchar2(32)
, comments      varchar2(4000)
, label         varchar2(4000) 
)
/

Whenever we compile – create or replace – a PL/SQL object, the CREATE DDL event is fired and we can intercept that event with our own trigger. At that point we have various pieces of information that we can use to insert a new record into the PLSQL_ARCHIVE table.

CREATE OR REPLACE TRIGGER plsql_archiver_trg
AFTER CREATE
ON SCHEMA
BEGIN
  if ora_dict_obj_type in ( 'PACKAGE', 'PACKAGE BODY', 'TRIGGER','FUNCTION', 'PROCEDURE')
  then
    plsql_archiver.archive_version
    ( p_object_name   => ora_dict_obj_name 
    , p_object_type   => ora_dict_obj_type
    , p_object_owner  => ora_dict_obj_owner
    , p_creation_time => systimestamp
    );
  end if;
END plsql_archiver_trg;
/  

We need a Nested Table Type defined in the server:

create type string_table as table of varchar2(4000)
/

The package plsql_archiver:

CREATE OR REPLACE package plsql_archiver
as
procedure archive_version
( p_object_name   in  varchar2
, p_object_type   in  varchar2
, p_object_owner  in varchar2
, p_creation_time in date
);
--
procedure revert_to_previous_version -- undo last change; can be called repeatedly
( p_object_name   in varchar2
, p_object_type   in varchar2
, p_object_owner  in varchar2
, p_purge_latest  in varchar2 default 'Y' 
);
--
procedure revert_to_version
( p_object_name   in varchar2
, p_object_type   in varchar2
, p_object_owner  in varchar2
, p_version_label in varchar2
, p_branch        in varchar2
, p_seq           in number
, p_purge_later   in varchar2 default 'N' 
);
-- wildcards allowed
procedure purge
( p_object_name   in varchar2
, p_object_type   in varchar2
, p_object_owner  in varchar2 
, p_status        in varchar2 default null
, p_priority      in varchar2 default null
, p_from_datetime in date     default null
, p_to_datetime   in date     default null
, p_branch        in varchar2 default null
, p_seq_from      in number   default null
, p_seq_to        in number   default null
);
--
/* Not yet implemented:
-- wildcards allowed
-- this procedure will link all specified object versions to the label specified through p_label
procedure create_stripe
( p_label         in varchar2
, p_object_name   in varchar2 
, p_object_type   in varchar2
, p_object_owner  in varchar2 
, p_from_datetime in date     default null
, p_to_datetime   in date     default null
, p_branch        in varchar2 default null
, p_seq           in number   default null
);
*/
function version_graph
( p_object_name   in varchar2
, p_object_type   in varchar2
, p_object_owner  in varchar2 
, p_show_version_label in  varchar2 default 'Y' -- show the version label 
, p_show_seq           in  varchar2 default 'N' -- show the seq value for each version (appended to the version label if that is requested too)
, p_show_datetime      in  varchar2 default 'N' -- display the timestamp of the creation of each version
, p_show_author        in  varchar2 default 'N' -- display the author of each version
, p_show_labels        in  varchar2 default 'N' -- display the labels or stripes a version is associated with
, p_show_status        in  varchar2 default 'N' -- display the status (VALID or INVALID) of the version
, p_show_comments      in  varchar2 default 'N' -- display the Comments for each version
) return string_table
;
end plsql_archiver;
/

We will bother with the actual implementation of this package later on. Let’s first see how we can make use of what we have created thusfar:

• Get an overview of all compilations in our schema, including timestamp and resulting status
• Get the errors for a particular "version" of a PL/SQL object
• Get the source at a particular moment in time, for example to compare with the current version
• Revert to a specifc stage in the development of a PL/SQL object
• Create a Stripe or Label, associating specific versions of all or at least many PL/SQL program units with what could be a release, a patch or whatever the stripe is to signify
• Gather some analytical data on the PL/SQL development process: frequency of compilations, evolution of number of errors and size of the source
• When the developer uses annotations to provide comments, priority, version label and branch we can even retrieve version graphs

Some of the information stored in the PLSQL_ARCHIVE table is inherently available at AFTER CREATE trigger time, such as the source, the errors, the object name, size and type and the compilation time. Others will have to be provided as annotations or meta-data in the source code, specified by the developer him or herself.

In the implementation of the PLSQL_ARCHIVER presented below, we make use of the following annotations, largely following the style used in JavaDoc and also adopted in PLDoc – an open source project for generation of technical documentation for PL/SQL code:

• @author – for the author or developer of the (current version of the) PL/SQL unit
• @version – the version label for the current instance of the PL/SQL object
• @label – the label or stripe that this current instance of the PL/SQL object is associated with; examples of Labels are Releases, Patches, Versions of (reusable) Components etc.
• @branch – an indication of the particular branch or subproject the PL/SQL unit is currently being reworked for; when not explicitly set, the MAIN branch is assumed. Examples of other branches are ADD_SECURITY (a subproject), 1.0PTCH2 (Patch 2 on Release 1.0) and 10gR2_PORT (the collection instances that make use of new Oracle 10gR2 PL/SQL features.
• @priority – an indication of the extent of changes in this version compared to the previous version; typicaly values are Pico, Minor, Normal, Major
• @comments – any additional description of this version of the object, for example the changes with respect to the previous version

The developer would include these keywords or annotations somewhere in a comment section in the PL/SQL object. For example:

create or replace procedure test_archiver
is
  l_t number;
  /****
  *  Annotations for the PLSQL_ARCHIVER framework
  *
  *  @author Lucas Jellema
  *  @version 1.2
  *  @comments Just a simple trial procedure to build some version history for
  *  @priority Normal
  *****/  
begin
  for i in 1..100 loop
    null;
    i=i+1;
  end loop;
end;
/

Some simple examples:
To list the recent history of PL/SQL compilation:

select object_name
,      object_type
,      object_owner
,      source_size
,      creation_time 
,      errors
from   plsql_archive
order
by     creation_time desc
/

To find the most recent compilation errors:

select *
from   ( select errors
         from   plsql_archive
         order
         by     creation_time desc
       )
where  rownum =1 
/

To undo the last "check in" or last compilation of an object:

begin
   plsql_archiver.revert_to_previous_version -- undo last change; can be called repeatedly
  ( p_object_name   => 'TEST_ARCHIVER'
  , p_object_type   => 'PROCEDURE'
  , p_object_owner  => USER
  , p_purge => 'Y' -- delete the most recently compiled code
  ) ;
end;
/

To view the entire version tree for our TEST_ARCHIVER object:

set long 4000
set linesize 300
set pagesize 1000
select *
from   table ( plsql_archiver.version_graph
               ( 'TEST_ARCHIVER'
               , 'PROCEDURE'
               , USER 
               , 'Y' -- version_label
               , 'Y' -- seq
               , 'Y' -- creation datetime
               , 'Y' -- author
               , 'Y' -- labels
               , 'Y' -- status
               , 'Y' -- comments
               )
             ) version_history
/

It is probably wise to start with somewhat less information. Let’s say just the Version Label, the Sequence, Status and the Creation Date Time. The result for our TEST_ARCHIVER object looks something like:

select *
from   table ( plsql_archiver.version_graph
               ( 'TEST_ARCHIVER'
               , 'PROCEDURE'
               , USER
               , 'Y' -- version_label
               , 'Y' -- seq
               , 'Y' -- creation datetime
               , 'N' -- author
               , 'N' -- labels
               , 'Y' -- status
               , 'N' -- comments
               )
             ) version_history
/
COLUMN_VALUE
----------------------------------------------------------------------------------------------------
Version Graph for PROCEDURE TEST_ARCHIVER (schema AGS)
MAIN                                    Patch 3 Release 2.0                     ADD_SECUTIRY                            New_BRANCH
1.1 ()
09-OCT 21:28:33
    |
1.1 ()
09-OCT 21:28:43
    |
1.2 (1)
09-OCT 21:39:03
    |
1.2 (2)
09-OCT 21:40:21
    |
1.2 (3)
09-OCT 21:40:22
    |
1.2 (4)
09-OCT 21:40:23
    |
1.2 (5)
09-OCT 22:11:57                         1.2PTCH203_1.1 (1)
    |                                   09-OCT 22:11:59
                                            |
                                        1.2PTCH203_1.1 (2)
                                        09-OCT 22:12:11
                                            |
                                        1.2PTCH203_1.2 (1)
                                        09-OCT 22:12:20
                                            |
                                        1.2PTCH203_1.3 (1)
                                        09-OCT 22:12:21
                                            |
                                        1.2PTCH203_1.3 (2)
                                        09-OCT 22:14:14                         1.0SECU_1.0 (1)
                                            |                                   09-OCT 22:14:24
                                                                                    |
                                                                                1.0SECU_1.1 (1)
                                                                                09-OCT 22:14:34
                                                                                    |
                                                                                1.0SECU_1.2 (1)
                                                                                09-OCT 22:14:36
                                                                                    |
                                                                                1.0SECU_1.2 (2)
1.3 (1)                                                                         10-OCT 10:34:12
11-OCT 06:14:12                                                                     |                                   1.3NEW_BRANCH1.0 (1)
    |                                                                                                                   11-OCT 06:14:16
                                                                                                                            |
                                                                                                                        1.3NEW_BRANCH1.0 (2)
1.4 (1)                                                                                                                 11-OCT 06:14:56
11-OCT 06:33:27                                                                                                             |
    |
1.5 (1)
11-OCT 06:33:33
    |
1.6 (1) *
12-OCT 16:45:27
    |

Note: the asterisk (*) indicates that the object version is INVALID or its PL/SQL code cannot be compiled!

Next Steps

Things I can envision for next steps with this revolutionary technology framework:

• Include a Compare facility that can report the differences between two versions. If I feel really incredibly brave, I will do Merge as well. Actually, I think there must be Java based Compare and perhaps Merge components for Text; we could probably upload them as Java Stored Procedures and wrap them within PL/SQL wrappers
• Provide a GUI for this database backend or repository
• Provide a migration from CVS to my source code control system (or perhaps the other way round)
• Ensure that a call to REVERT_TO_VERSION does not itself create a new version upon compilation (as it currently probably does).
• Provide automatic version labeling: if I check in the next version after 1.4 the tool will call it 1.5 for me. It could even look at the priority level – major, minor, pico, normal – and decide to go from 1.4 to 1.4.1, 1.5, 2.0 or 1.4.0.1.
• Add support for other database objects such as Tables and Views

Resources

Download the sources for this article: plsql_Archiver.zip 

Implementation of the PLSQL_ARCHIVER

The Package Body for PLSQL_ARCHIVER is on the second page of this article. You can also download it, with all SQL DDL scripts.