Extract Database Object Header Comment with Powershell

Some database objects in my current project are created by ORM or a PL/SQL-framework and hence need not to be put under source control; All manual created objects are strictly stored in source control.

Every database object definition under source control is stored as DDL file. And every file starts with a comment about the database object properties. Once an object is created in database and it's definition is added to source control it's time to automate some repeating stuff like creating a comment header. For the current project I use Windows Powershell for this. All stuff which needs to be generated is done via PL/SQL. The return value of these functions is very simple : a collection of strings.

Using the Database (only) to generate the Header comment

Running on database the comment is enriched with some database properties of the described object.
In this example a database trigger :

• Does the object exist in the given schema
• additional metadata of the database object like trigger action, and trigger type
• Creation time and last modification time using the Oracle DBA_OBJECTS view
• Getting the next planned application software release - stored in a milestone table

SQL> SELECT * FROM TABLE( Util_Dbbld.Extract_Object_Comment('OASIS', 'TRIGGER', 'CUSTOMER_AUR') );

COLUMN_VALUE
--------------------------------------------------------------------------------
/** DATABASE.SCHEMA.OASIS.TRIGGER
 OBJECT_TYPE      : TRIGGER|AFTER EACH ROW|UPDATE
 OBJECT_NAME      : CUSTOMER_AUR
 CREATOR          : %CREATOR%
 CREATED          : 19.05.2008 08:48 -
 MODIFIER         : %MODIFIER%
 MODIFIED         : 15.01.2010 18:28 -
 OASIS DB VERSION : 4.0.0.0
 RUN AS           : OASIS
 WORKITEM LAST    : %WORKITEM_LAST%
 WORKITEM OWNER   : %WORKITEM_OWNER%
*/

12 rows selected

SQL>

Wow! that looks great - but ... Not all demands for a source code main comment header can be answered by database meta data or is even not meaningful - we only reached 30% of necessary meta data - Some questions needs to be answered still :

• The creation time of the table is meaningless if for example you re-build your development database frequently - when the object was really put into life?
• Who created the object? - you cannot answer this via database metadata - the same issue with object modification
• What about the the task/bug responsible for the object creation/modification?
• What about creation/change comments ?

All these can be answered only with meta data of source control.

Using the Windows Powershell to generate the Header comment

In the background the powershell function Extract-Object_Comment is sampling a lot of metadata from source control system and then calls the same PL/SQL-Routine with additional parameters. Following steps are done via powershell :

• Resolving the source path of the given database object
• Retrieving change history of the DDl file
• Retrieving Creation and last modification meta data processing the change history
• Retrieving WorkItem (Task/Bug) properties of last change

§ NX123321 { D:\Tfs\OASIS_APP\MAIN }
> Extract-Object_Comment OASIS TRIGGER CUSTOMER_AUR

/** DATABASE.SCHEMA.OASIS.TRIGGERS
  OBJECT_TYPE      : TRIGGER|AFTER EACH ROW|UPDATE
  OBJECT_NAME      : CUSTOMER_AUR
  CREATOR          : DOM\EXX88882V
  CREATED          : 01/21/2008 09:43:27 - Initial checkin of OASIS
  MODIFIER         : DOM\EXX77771Z
  MODIFIED         : 01/14/2010 15:28:47 - dito
  OASIS DB VERSION : 4.0.0.0
  RUN AS           : OASIS
  WORKITEM LAST    : WorkItem ( Task:20025 ) [ OASIS DB - Cross section tasks ]
  WORKITEM OWNER   : Reitschuster, Karl
*/

Now the comment is enriched with a lot more useful meta data. A source code system like for example implemented in the Team Foundation Server holds a giant amount of meta data - not all is needed in a Header comment.

Conclusion

As I already mentioned : due to it's perfect integration in operating system and very handy/rich interfaces to Source Control, Database ... via access to .NET assemblies Windows Powershell has become the tool for Database Build automation in our project - flexible and mighty enough to achieve the tasks.

Hopefully I could encourage you to use Powershell for your projects - Next article will handle an Include directive for SQL files which does a little bit more then a simple source include ...

/Karl Reitschuster