Deprecation within Database Schemas

Java developers already know about deprecating API’s – the ability to mark specific classes or methods as “deprecated” thus informing API users that they should stop referencing those parts, because they might be removed in future releases. Introducing such a transition period allows code refactoring being less painful (mainly for the public API parts) and preserve backward compatibility.

Along with the rise of new approaches to more agile database techniques, came the idea of using similar practices also in DB development. That means for example, if you want to remove a column then you don’t drop it immediately but mark the column as deprecated. After a transition period of some weeks or months, the column will be dropped. This strategy is of particular interest to database refactoring, because usually database schemas are highly coupled to a wide variety of things. And unlike Java API’s where we can distinguish between protected and public API parts, items within a database schema are potentially always public.

What to deprecate?

The following database artifacts are items that might be deprecated:

• Tables
• Views
• Single Columns
• Stored Procedures, Functions, Sequences

When to deprecate database items?

• Item will be dropped in future
• Is inefficient or insecure to use

How to deprecate?

A deprecation mechanism should fulfill the following requirements

• Deprecation markers should be attached to their items in a manner that they are preserved even if the items will be moved (import/export etc)
• Markers should refer to an explanation why the item has been deprecated and suggest what to use instead
• Throw warning messages when deprecated items are used

Techniques to deprecate database items

In case of Java, the deprecation is carried out through @deprecation comments within the source code. The Java Compiler throws warning messages when it finds references to deprecated items. Obviously within a database we cannot issue compile warnings. The only way to inform about calls to deprecated items is at runtime. That means every time a deprecated table, column, view or so is accessed, a warning message should be written to a log table. But how can we know when those items are accessed?

Oracle databases are offering the following options:

1) Triggers: It’s the standard way to attach DML monitoring to database tables. But triggers cannot monitor on SELECT’s and they are not working on Stored Procedures, Sequences and things of this nature.

2) Database Auditing: With Oracle’s Regular Auditing one can monitor also the use of Views, Functions and Sequences plus one can even log the SQL statement that causes the audit event. But to monitor table access on column-level, Fine-Grained Auditing (FGA) is needed. FGA is only available with Oracle Enterprise Edition.

Issuing deprecation warnings

Another problem is about the deprecation warning messages content itself. The logged messages should help developers and admins to figure out from where deprecated items are accessed. That means at least the program name and SQL statement should be logged. Unfortunately Oracle doesn’t offer something like a stack trace (as Java).

Conclusion

Wouldn’t it be nice if databases offered a standard mechanism to deprecate all the variety of items within a database? I think that will be a huge help towards achieving a more agile way of database design.