HOWTO: Implement Versioning via Oracle XMLDB

Since a long time, the database has had some versioning capabilities, long before features like “Edition Based Redefinition” in Oracle 11gR2 appeared. This versioning, via XMLDB functionality, is based on its XDB Repository access to the database. The XDB Repository is a file/folder metaphor that acts as a file server. You can enable this functionality by enabling the XDB Protocol Server while using PL/SQL package DBMS_XDB.

--
# Request current HTTP/WebDAV or FTP settings
SQL> select dbms_xdb.getHTTPPort() from dual;
SQL> select dbms_xdb.getFTPPort() from dual;
--
# Enable HTTP/WebDAV or FTP for a specific port
SQL> select dbms_xdb.setHTTPPort(8080) from dual;
SQL> select dbms_xdb.setFTPPort(2100) from dual;
--
# Disable HTTP/WebDAV or FTP functionality
SQL> select dbms_xdb.setHTTPPort(0) from dual;
SQL> select dbms_xdb.setFTPPort(0) from dual;
--
# Set amount of shared_servers and register the changes
SQL> alter system set shared_servers = 5 scope=both;
SQL> alter system register;
--

In the example above, setting a non zero port number will enable the HTTP(s)/WebDAV functionality of the XDB Repository. If you use port number 0, then you disable the HTTP(s)/WebDAV access to the XDB Repository. You are only allowed to do this if you have the XDBADMIN or DBA role. After you have enabled the XDB Protocol access to the database, you can create new files or folders in the XDB Repository.

Modifications, like the above use via DBMS_XDB, are stored in the xdbconfig.xml configuration file. The behavior of the protocol server functionality can be regulated by means of this XML configuration file xdbconfig.xml like PL/SQL Gateway behavior (one of the possible uses of the embedded XDB “servlets”), security, MIME types definitions, caching and others. Just as you would suspect from a HTTP “daemon” like Apache, although the XDB Protocol Server is based on, extension on, Oracle’s Listener (Shared Server) mechanism. By the way, the naming used, “servlets”, has been derived from the WebDAV standard and those are or can be based on C, Java or PL/SQL.

Every time you create or move a file into the XDB Repository (the database), this file is automatically secured via Access Control Lists. If nothing specific is assigned to this file or folder, then the privileges for this “resource” (as files and folders are refered to in the XMLDB realm), will be set via the bootstrap_acl.xml file.

--
SQL> select xdburitype('/sys/acls/bootstrap_acl.xml').getCLOB() from dual;


  
    dav:owner
    true
    
      
    
  
  
    XDBADMIN
    true
    
      
    
  
  
    PUBLIC
    true
    
      
      
      
      
    
  
 
--

As shown above, in such a case, the file or folder, will assign ALL privileges to the DAV:OWNER, that is the person who created the file/folder, the Oracle database users that were granted the XDBADMIN role and only read privileges to the PUBLIC database “role”. Be aware that DAV users, Database Roles and Users are different things. So, for example, it is very easy to create a file or manipulate this file, via packages like DBMS_XDB or DBMS_XDBRESOURCE, but this created file(=resource) can inaccessible afterwards if your unlucky. Oracle XMLDB Security is implemented via Oracle Virtual Private Database mechanisms, so this might mean that also users with the DBA role might be denied access…

Every file/folder that is created in the XDB Repository will be, at least its metadata, stored in a XMLType table called XDB$RESOURCE, which is part of the XDB Schema. Just like the xdbconfig.xml file, is the XDB$RESOURCE XMLType table restricted by an XML Schema. This XML Schema is called xdb$resource.xsd and is registered in the XMLDB database

Oracle has created, by default, 2 views that enable direct access to the content of this XDB$RESOURCE table: PATH_VIEW and RESOURCE_VIEW. If an, in the XDB Repository inserted or created (eg. via PL/SQL), then its content is stored in a LOB that is part of the XDB$RESOURCE XMLType table, or, if the object is hierarchical enabled, it will have a reference to the actual object that has the content stored. Hierarchical enabled means that this content will also be secured via the XMLDB architecture, as needed, and will have metadata stored in the XDB$RESOURCE XMLType table.

This stored metadata contains information about, among others, creation time, owner, creator, MIME type, stored size, file name, locking status, etc. The metadata contains also version information. By default is an object not versioned.

You will have to follow the next steps to make use of XDB versioning:

1. Create via PL/SQL or via FTP, WebDAV access a new file (“resource”)
2. Use DBMS_XDB.lockResource to secure the file against unexpected alterations
3. Use DBMS_XDB_VERSION.makeVersioned to make the file available for versioning
4. Use DBMS_XDB_VERSION.checkOut to checkout the file
5. Change de contents of the file via FTP, WebDAV or PL/SQL
6. Use DBMS_XDB_VERSION.checkIn to checkin the file, to enable versioning
7. Use DBMS_XDB.unlockResource to make the file available for use

Graphically this sequence can be represented as follows…

Every time from here and afterwards, until you “un-version” the resource, version information will be stored in XDB$RESOURCE including all the other metadata like, who did it, when, etc. Be very aware that you will have to mimic File Server behavior, that is that you will have to lock every each and every time files and commit after every change. Oracle will follow and mimic WebDAV file/folder handling and this is not as you are used in a relational transaction “safe” database environment.

An XMLType Table is not easily accessed via SQL. So make life a bit easier to get the version information of objects, the following helper function, called “getVersionHistory”, can be used.

So after you have created, for example as in the following via PL/SQL, a resource and updated it with new content…

…via the helper function and the use of an XMLTABLE function, you can display the needed information in relational format, without the use of XPath even if you use this statement in a CREATE VIEW (relational) DDL statement.

Direct access via such a view, for example via a GUI like Toad or APEX, to version information and, based on this if needed other XDB$RESOURCE data, is now possible and easy.

For ease, hereby also an SQL example, as given by Mark Drake (OTN Developer Days – Code), demonstrating the steps as described in this post: “Versioning example.txt”

For people how like APEX, there is also a XFILES APEX Community Edition example APEX application (XACE for short) that can be loaded into APEX. The APEX code can be downloaded via http://xace.sourceforge.net

HTH

M.