« Include-Directive for SQL-Scripts with Powershell (I) | Main | Access Oracle Database with Windows Powershell 2.0 »
Tuesday
Feb092010

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

References (1)

References allow you to track sources for this article, as well as articles that were written in response to this article.

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>