Ubitsoft SQL Enlight - Integrate With Automated Build

SqlCodeAnalyzeFilesTask
SqlCodeAnalyzeServerTask
SqlCodeLayoutFilesTask
Examples

SQL Enlight offers three task that to facilitate integration with automated builds.

SqlCodeAnalyzeFilesTask

The task runs static analysis over specified script files and creates a report.

Task parameters:

Name	Description
Quiet	Suppresses all except error output messages.
InputPath	Required. Input script files location. Example: "c:\my files\*.sql" .
Rules	Analysis rules which to be applied. The rules can be separated with ',',';' or '\|'. Example: rule1,rule2,rule3.
ParametersFile	Path to a XML file containing the values for the analysis rules parameters. Check Schemas\AnalysisParameters.xsd for the parameters file XML schema.
ContextFile	Path to an analysis context file which to be used for analyzing the tagret script files.
TemplateFile	Optional parameter for setting external analysis template. The external template will be used as a source for the analysis rules instead of the default template.
ReportOutputPath	Output location of the analysis XML report. Example: "c:\my reports\analysis1.xml"

SqlCodeAnalyzeServerTask

The task runs static analysis over specified SQL Server objects and creates a report.

Task parameters:

Name

Description

Quiet

Suppresses all except error output messages.

ServerName

Required. Target SQL Server name.

DatabaseName

Target database name. Default is "master".

Username

Username for accessing the database. If omitted integrated security will be used.

Password

User password for accessing the database. If omitted integrated security will be used.

ObjectType

Required. Type of the objects that will be analyzed.
The allowed object types are:

Type	Description
Server	Includes server triggers and all functions,views, procedures and triggers in the server databases.
ServerTrigger	Includes server triggers.
View	Includes all views in specific database.
Database	Includes all functions, views, procedures and triggers in specific database.
DmlTrigger	Includes all DML triggers.
DatabaseTrigger	Includes all DDL triggers.
UserDefinedFunction	Includes all user defined functions.
DatabaseProgrammabilityObject	Includes functions , database triggers and stored procedures.
StoredProcedure	Includes all stored procedures.

ObjectName

Single-part delimited name for servers,databases and triggers ([object_name]).
Two-part delimited name for functions,views and stored procedures ( [schema_name].[object_name] ).
To target all objects of the specified type set this parameter to '*'.
Example: [dbo].[mysp_MyStoredProcedure] or [MyDatabase]

TemplateFile

Optional parameter for setting external analysis template. The external template will be used as a source for the analysis rules instead of the default template.

Rules

List of analysis rules or analysis group names separated with ',',';' or '|'.
Example: rule1,rule2,rule3,Performance,rule12.

ParametersFile

Path to a XML file containing the values for the analysis rules parameters.

Check Schemas\AnalysisParameters.xsd for the parameters file XML schema.

ContextFile

Path to an analysis context file which to be used for analyzing the tagret script files.

ReportOutputPath

Output location of the analysis XML report.
Example: "c:\my reports\analysis1.xml"

SqlCodeLayoutFilesTask

The task reformats T-SQL script files using given layout template.

Task parameters:

Name	Description
Quiet	Suppresses all except error output messages.
InputPath	Required. Source T-SQL files location. Example: "c:\my files\*.sql" .
OutputPath	Required. Output location for the reformatted T-SQL script files. Example: "c:\my files\result\" .
TemplateName	Exact name of the layout template which to be used.If not specified the default layout template will be used. Example: "My Favorite Layout Template" .

Examples

The following examples show a project file invoking the SQL Enlight tasks.

Example using SqlCodeAnalyzeFilesTask:

<?xml version="1.0" encoding="utf-8"?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

    <UsingTask TaskName="Ubitsoft.SqlEnlight.Tasks.SqlCodeAnalyzeFilesTask" 
               AssemblyFile="C:\Program Files\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.dll" />

    <PropertyGroup>
        <AnalysisInputPath>c:\input\analysis\</AnalysisInputPath>
        <AnalysisRules>rule1,rule2,rule5,rule12,Design,Performance</AnalysisRules>
        <ReportOutputPath>c:\output\analysis\analysis.xml</ReportOutputPath>
    </PropertyGroup>

    <Target Name="AnalyzeSqlScriptsTarget">
            <SqlCodeAnalyzeFilesTask
                                      Quiet="false" 
                                      Rules="$(AnalysisRules)" 
                                      ReportOutputPath="$(ReportOutputPath)"/>
    </Target>
</Project>

Example using SqlCodeAnalyzeFilesTask:

<?xml version="1.0" encoding="utf-8"?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <UsingTask TaskName="Ubitsoft.SqlEnlight.Tasks.SqlCodeAnalyzeServerTask" 
               AssemblyFile="C:\Program Files\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.dll" />
    <PropertyGroup>
        <AnalysisRules>rule1,rule2,rule5,rule12,Design,Performance</AnalysisRules>
        <ServerName>.</ServerName>
        <DatabaseName>MyDatabase</DatabaseName>
        <Username></Username>
        <Password></Password>
        <ObjectType>DatabaseProgrammabilityObject</ObjectType>
        <ObjectName>*</ObjectName>
        <ReportOutputPath>c:\output\analysis\analysis.xml</ReportOutputPath>
    </PropertyGroup>

    <Target Name="AnalyzeDatabaseTarget">
            <SqlCodeAnalyzeServerTask  Quiet="true" 
                                      ServerName="$(ServerName)"
                                      DatabaseName="$(DatabaseName)"
                                      ObjectType="$(ObjectType)"
                                      ObjectName="$(ObjectName)"
                                      Rules="$(AnalysisRules)" 
                                      ReportOutputPath="$(ReportOutputPath)"/>
    </Target>
</Project>

Example using SqlCodeLayoutFilesTask:

<?xml version="1.0" encoding="utf-8"?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <UsingTask TaskName="Ubitsoft.SqlEnlight.Tasks.SqlCodeLayoutFilesTask" 
               AssemblyFile="C:\Program Files\Ubitsoft\SQL Enlight\Ubitsoft.SqlEnlight.Tasks.dll" />
    <PropertyGroup>
        <LayoutOutputPath>c:\output\layout\</LayoutOutputPath>
        <LayoutInputPath>c:\input\layout\</LayoutInputPath>
        <LayoutTemplateName>My Favorite SQL Script Layout</LayoutTemplateName>
    </PropertyGroup>
    
    <Target Name="LayoutScriptsTarget">
            <SqlCodeLayoutFilesTask   TemplateName="$(LayoutTemplateName)" 
                                      InputPath="$(LayoutInputPath)" 
                                      OutputPath="$(LayoutOutputPath)" />
    </Target>
</Project>