« 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

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>