Include-Directive for SQL-Scripts with Powershell (I)
Monday, February 15, 2010 at 3:35PM 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;
Karl Reitschuster
The defintion of the Import-Schema Powershell function;
There also exists an Import-Interface, Import-Utility function calling all the same generic function Import-Item;
/Karl
#######################################################################################################################################
## FUNCTION::IMPORT-SCHEMA
##
function Import-Schema
( [parameter(Mandatory=$true)] [String] [ValidateNotNullOrEmpty()]
$P_Schema
, [parameter(Mandatory=$true)] [String] [ValidateNotNullOrEmpty()]
$P_Object_Type
, [parameter(Mandatory=$true)] [String] [ValidateNotNullOrEmpty()]
$P_Object_Name
, [parameter(Mandatory=$false)] [String]
$P_Comment )
{
## check if Schema is valid
if ( $DBBLD_SCHEMAS -notcontains $p_Schema.ToUpper().Trim() ) {
Log-Error "`nSchema '$p_Schema' is not supported!`n"
return
}
## Call Generic Variant ...
Import-Item "Schema" $P_Schema $P_Object_Type $P_Object_Name "${P_Comment}"
}
Reader Comments