« Include-Directive for SQL-Scripts with Powershell (II) | Main | Extract Database Object Header Comment with Powershell »
Monday
Feb152010

Include-Directive for SQL-Scripts with Powershell (I)

One thing is to write SQL -, PL/SQL- Code for Packages, Trigger, Views ... and to bring every database object as DDL-File under source control. The other thing is to build a deployment package of the modified database objects(*) involved and to ship them to production. At my current customer the production database deployment package is a flat file without folder hierarchy as in development. So you have to merge all affected DDL-Scripts into flat files.

if some of the dependencies of the flat file have changed you have to re-merge running into to danger of too many manual steps. If the number of changes increases you get a bottleneck in you development/release workflow; This situation was the idea for an include directive implemented with powershell. Simply copy DDL scripts into your target DDL source automatically and let other parts of the database changes like ALTER TABLE and DML statements untouched.

if something changed you only would need a rebuild of the target-file - completely done - full automatically.

Prerequisites

You need to have a structure which is based on some constraints ...

  • Naming Conventions of your files : if you don't then every devloper has its own naming schema; your tools won't find the source code files of the corresponding database objects.
  • Folder structure : A structure which is evident in itself. All tables scripts are located in Tables folders, all views in Views folders, the schema is the root folder of all schema objects and so on.

Example : not without checks

Now for example just want to include the View definition of the OASIS.EVENTLOG view using our Powershell function Import-Schema;

§ NP3334546 { D:\Tfs\OASIS_XMM\MAIN }
> Import-Schema OASIS VIEW EVENTLOG

Include List ( D:\Tfs\OASIS_XMM\MAIN\Sources\Database\Schema\OASIS\Views\EVENTLOG.sql ) could not be expanded

Uups!

  • the View definition file did not exist and an error-message is printed on the console ...

Some other checks needs to be done before database or file would be accessed ...

§ NP3334546 { D:\Tfs\OASIS_XMM\MAIN }
> Import-Schema SYS VIEW EVENTLOG_LAST25

Schema 'SYS' is not supported!

§ NP3334546 { D:\Tfs\OASIS_XMM\MAIN }
> Import-Schema OASIS XVIEW EVENTLOG_LAST25

Object Type 'XVIEW' is not supported!

Uups!

  • the SYS schema for example is not allowed ...
  • the database object type must be valid

Example : successful include

Ok now the right view and with the right meta data : OASIS.EVENTLOG_LAST25;

What is our Powershell function Import-Schema doing here?

  • Checking : does the schema exist ( the schema must be defined for the project )
  • Checking : does the database object type exists
  • Checking : Path - if the file does not exists the processing stops with an error message
  • Checking : Latest Version - when the file was found it will check for the latest version. On our computers there are only a local copies (workspace) of the sources
    in a team somebody else could have modified a file - you have to bee sure to get the latest version of the file ...
  • The create-tag 'DDL:EVENTLOG_LAST25:VIEW.MOD' is queried against the database catalog:
    • NEW : means the database object exists in the development database but does not exist in the production database (you could store the empty structure of your current production db in an own schema - to compare structure ...)
    • MOD : means the object exists both in production and development database
    • OBJECT_NO_FOUND : a DDL file of the object exists but the database object is missing in the development database
  • The complete path and the create tag is added as a comment to the file
  • The source file is written to stdout

§ NP3334546 { D:\Tfs\OASIS_XMM\MAIN }
> Import-Schema ISIS VIEW EVENTLOG_LAST25


IMP  : D:\Tfs\OASIS_XMM\MAIN\Sources\Database\Schema\OASIS\Views\EVENTLOG_LAST25.sql

--
-- D:\Tfs\OASIS_XMM\MAIN\Sources\Database\Schema\OASIS\Views\EVENTLOG_LAST25.sql
--
-- DDL:EVENTLOG_LAST25:VIEW.MOD -
--
PROMPT CREATE  VIEW OASIS.EVENTLOG_LAST25

/** DATABASE.SCHEMA.OASIS.VIEWS
  OBJECT_TYPE      : VIEW
  OBJECT_NAME      : EVENTLOG_LAST25
  CREATOR          : XXB\EXX2ZZZ
  CREATED          : 02/12/2010 15:30:13 - views were not in source safe - test of capture-DbItem
  MODIFIER         : XXB\EXX2ZZZ
  MODIFIED         : 02/12/2010 15:30:13 - Added complete Comment Header
  ISIS DB VERSION  : 4.0.0.0
  RUN AS           : OASIS
  SOURCE OWNER     : Some Bank AG 2010
  WORKITEM LAST    : WorkItem ( Task:26271 ) [ ISIS DB - Cross section tasks ]
  WORKITEM OWNER   : Reitschuster, Karl
*/

CREATE OR REPLACE
VIEW   EVENTLOG_LAST25 AS
SELECT E1.Eventnum
      ,E1.Eventtime
      ,E1.Eventtimestamp
      ,E1.Severity
      ,E1.Modul
      ,E1.Method
      ,E1.Section
      ,E1.Elapsed
      ,E1.Eventmessage
      ,E1.Logsql
      ,E1.Objectid
      ,E1.Objectentity
      ,E1.Component´╗┐

 

Example : Wild Card Include

It's possible to use wildcards in the object name.
This allows to include more then one database object definition file with one Include-directive :

§ NP3334546 { D:\Tfs\OASIS_XMM\MAIN }
> Import-Schema OASIS VIEW 'EVENTLOG*' | Out-File -Encoding Default test.sql

IMP  : D:\Tfs\OASIS_XXM\MAIN\Sources\Database\Schema\OASIS\Views\EVENTLOG_LAST25.sql
IMP  : D:\Tfs\OASIS_XXM\MAIN\Sources\Database\Schema\OASIS\Views\EVENTLOG_TODAY.sql
GETL : D:\Tfs\OASIS_XXM\MAIN\Sources\Database\Schema\OASIS\Views\EVENTLOG_TODAY.sql

§ NP3334546 { D:\Tfs\OASIS_XXM\MAIN }
>

Some new Aspects of the example ...

  • The object name must be delimited with "'" to prevent the shell extracting filenames fitting to the given pattern
  • the output this time is written to the test.sql file - Messages to console are not redirected, hence still appeared on the screen
  • A get-latest operation occurred during the include to get the most current version of the EVENTLOG_TODAY.sql file
  • The output is redirected with the Out-File Cmdlet to avoid Unicode encoding as done using the redirect operator '>'

 

Conclusion

An Include-Directive written as Powershell function accessing both database and source control is able to keep flat styled production packages in sync avoiding to many manual steps. Probably in your database development cycle you have to solve similar problems - which could be achieved using Powershell. Now it's the question how to integrate the Include-functionality into SQL-scripts - this is the topic of the next article ...

/Karl Reitschuster

(*) A modification of a table or an index needs DROP or ALTER Statements; The database object defintion file in this case could only applied once - change of structure could als cause additional data migration scripts;

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>