« Modifying an execution plan inside a View using a Query Block Hint (QB_NAME hint) | Main | Include-Directive for SQL-Scripts with Powershell (I) »
Wednesday
Feb172010

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

In the last recent article I presented the possibilities of the Powershell Import-Schema function; Now we do not want to use the function interactive via Powershell Console but also call it from a SQL-Script; Your SQL-Script will not process Powershell commands but a Powershell script could process the SQL file and execute all Powershell related commands and let SQL commands untouched. The stream then could be redirected to a pure SQL file.

The source file will be called Build file which contains SQL and Powershell commands - the generated SQL file will be called target file which contains extracted/generated SQL code and static SQL code;
an example demo Build file :

§ AP489416 { ~\Migration }
> cat test.bld


-- Move Index CUSTOMER_IX01 to natoher talbspace
ALTER INDEX CUSTOMER_IX01 REBUILD TABLESPACE OASISXAPP;

-- Update State Column
UPDATE CUSTSTATE SET STATE_DESC = 'STATELESS' WHERE ID = 1;
COMMIT WORK;

(* Build Automation Example  *)
!! Import-Schema OASIS VIEW EVENTLOG_TODAY "added eventtime column"


§ AP489416 { ~\Migration }>

 

The Powershell  Approve-File Function is parsing the test.bld file file, the output is redirected to the test.sql file ...

§ AP489416 { ~\Migration }
> Approve-File test.bld > test.sql

PreProcessing test.bld ...
>>: (* Build Automation Example  *)
>>: Import-Schema ISIS VIEW EVENTLOG_TODAY "added eventtime column"
IMP  : D:\Tfs\ISIS_CRM\MAIN\Sources\Database\Schema\ISIS\Views\EVENTLOG_TODAY.sql

§ AP489416 { ~\Migration }
> cat test.sql


-- Move Index CUSTOMER_IX01 to natoher talbspace
ALTER INDEX CUSTOMER_IX01 REBUILD TABLESPACE OASISXAPP;

-- Update State Column
UPDATE CUSTSTATE SET STATE_DESC = 'STATELESS' WHERE ID = 1;
COMMIT WORK;



--
-- D:\Tfs\ISIS_CRM\MAIN\Sources\Database\Schema\OASIS\Views\EVENTLOG_TODAY.sql
--
-- DDL:EVENTLOG_TODAY:VIEW.MOD - added eventtime column
--
PROMPT CREATE  VIEW ISIS.EVENTLOG_TODAY

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

CREATE OR REPLACE
VIEW   EVENTLOG_TODAY AS
SELECT e.Eventnum
      ,e.Eventtime
      ,e.Eventtimestamp
      ,e.Severity
      ,e.Modul
      ,e.Method
      ,e.Section
      ,e.Elapsed
      ,e.Eventmessage
      ,e.Logsql
      ,e.Objectid
      ,e.Objectentity
      ,e.Component
      ,e.Dbcode
      ,e.Appcode
      ,e.Eventtask
      ,e.Lastupdate
      ,e.Id
FROM   Eventlog e
WHERE  e.Eventtime > Trunc(SYSDATE)
ORDER  BY e.Eventnum
;
PROMPT DONE EVENTLOG_TODAY
PROMPT .
SPOOL OFF

§ AP489416 { ~\Migration }>

 

 The Code of the Mini-Powershell Build file parser ...

###############################################################################
## FUNCTION::APPROVE-FILE
##
function Approve-File
        ( [System.IO.FileInfo] $P_Build_File ) {

    if ( ! ( Test-Path $P_Build_File ) ) {
        Log-Error "`nBuild File '${P_Build_File}' not exists!`n"
        return
    }
    Log-Message "PreProcessing $P_Build_File ..."
    
    ## capture target file name for inline-processing of Import-Intro
    $SCRIPT:BLD_SCRIPT  = $P_Build_File.Basename + ".sql"
    $L_Content          = $( Get-Content $P_Build_File )
    
    foreach ( $L_Line IN $L_Content ) {   
        ## Process Preproc-Tokens
        if ( $L_Line.Trim() -like "!!*" -or $L_Line.Trim() -match "^\(\*.*\*\)$" ) {
            
            $L_Command = $L_Line.Trim().TrimStart("!!").Trim()
            Write-Host ">>: ${L_Command}" -ForeGroundColor Yellow
            
            ## execute command if not a comment
            if ( $L_Command -notmatch "^\(\*.*\*\)$" ) {
                ## execute parsed expression in current '.' environment
                try {
                    Invoke-Expression $( .{ $L_Command } )
                } catch {
                    Log-Error "`nError in file [ ${P_Build_File} ]"
                    Log-Error "Line [ ${L_Line} ] could not preprocessed`n"
                    throw
                }
            }
        } else {
            "${L_Line}"
        }
    }
    "SPOOL OFF"
}

 

A more complex example for a build file ...

(* ------------- Database Build Script Automation ------------------------ *)
!! $SCRIPT:BLD_DB_VERSION = "4.0.0.0"
!! $SCRIPT:BLD_DB_SCHEMA = "COMMON"
!! $SCRIPT:BLD_MILESTONE = 111
!! $SCRIPT:BLD_MILESTONE_DESCR = "None Yet ..."
(* ------------------------------------------------------------------------ *)
(* *)
!! Import-Intro "Eventlogger Component DB Objects"
(* *)
(* Create Tables for Eventlogger Component *)
!! Import-Schema COMMON TABLE EVENTLOG "Build ..."
(* *)
(* Create Views for Eventlogger Component *)
!! Import-Schema COMMON VIEW EVENTLOG_LAST25 "Build ..."
!! Import-Schema COMMON VIEW EVENTLOG_TODAY "Build ..."
(* *)
(* Create Sequence for Eventlogger Component *)
!! Import-Schema COMMON SEQUENCE EVENTLOG_SEQ "Build ..."
!! Import-Schema COMMON SEQUENCE EVENTTASK_SEQ "Build ..."
(* *)
(* Create Package for Eventlogger Component *)
!! Import-Schema COMMON PACKAGE EVENTLOGMGR "Build ..."

 

Conclusion

Via Powershell it's possible to write a couple of very useful functions to support Build Automation for Database Scripts.

The only things still is missing is automatic dependency check and automatic rebuild for dependent components.

This would be the next level - we'll see

 

/Karl Reitschuster

References (5)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Include-Directive for SQL-Scripts with Powershell (II)
  • Response
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Include-Directive for SQL-Scripts with Powershell (II)
  • Response
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Include-Directive for SQL-Scripts with Powershell (II)
  • Response
    Response: published here
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Include-Directive for SQL-Scripts with Powershell (II)
  • Response
    Response: SEO Calgary
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Include-Directive for SQL-Scripts with Powershell (II)

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>