// <copyright file="SQLExtract.cs" company="Adam Nachman">
// Copyright (c) 2009 All Right Reserved Adam Nachman
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
// </copyright>
// <author>Adam Nachman</author>
namespace AdamNachman.Build.SqlExtract
{
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml.Serialization;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
/// <summary>
/// The extractor class that parses the sql files and generates a deploy script
/// </summary>
public class Extractor
{
#region Private Variables
private string sqlInstanceName;
private string projectGUID;
private string buildPath;
private List<string> sqlScripts;
private List<string> customFiles;
private bool dropToSeperateFile = false;
private bool extractData = false;
private string extractFromServer = string.Empty;
private string extractFromDatabase = string.Empty;
private string extractTargetFile = string.Empty;
private string extractCommands = string.Empty;
private string appendDatabaseName = string.Empty;
private bool appendDatabaseNameToScripts = false;
private Database database;
private Dictionary<SqlFileTypes, ProcessDatabase> scriptProcessors;
private bool scriptPermissions = false;
private string permissionsTemplate = string.Empty;
private string permissionsTargetFile = string.Empty;
private string sqlUsername = string.Empty;
private string sqlPassword = string.Empty;
private string extractUsername = string.Empty;
private string extractPassword = string.Empty;
/// <summary>
/// Setting this flag to "true" instructs the
/// </summary>
internal static bool CheckExistenceOfObjects = false;
/// <summary>
/// This is the string representation of the type used to check for existence on an existing schema/manifest/etc ...
/// </summary>
private string existenceCheckerProvider = string.Empty;
/// <summary>
/// This is the configuration required for an "existenceChecker" object. TODO - move the "existence checker" to a standard IOC pattern, and initialize from config.
/// </summary>
private string existenceCheckerConfiguration = string.Empty;
#endregion Private Variables
#region Private Enums
/// <summary>
/// The types of sql files currently supported by the extraction utility
/// </summary>
private enum SqlFileTypes
{
/// <summary>
/// Table schema objects
/// </summary>
Tables,
/// <summary>
/// View schema objects
/// </summary>
Views,
/// <summary>
/// Function schema objects
/// </summary>
Functions,
/// <summary>
/// Procedure schema objects
/// </summary>
Procedures,
/// <summary>
/// Clustered constraint schema objects
/// </summary>
ClusteredConstraints,
/// <summary>
/// CLR Assembly schema objects
/// </summary>
CLRAssemblies,
/// <summary>
/// Constraint schema objects
/// </summary>
Constraints,
/// <summary>
/// Trigger schema objects
/// </summary>
Triggers,
/// <summary>
/// Circular references among schema objects
/// </summary>
Circular,
/// <summary>
/// Schema schema objects
/// </summary>
Schemas,
/// <summary>
/// Role schema objects
/// </summary>
Roles,
/// <summary>
/// Filegroup schema objects
/// </summary>
Filegroups,
/// <summary>
/// Route schema objects
/// </summary>
Routes,
/// <summary>
/// Message schema objects
/// </summary>
Messages,
/// <summary>
/// Contract schema objects
/// </summary>
Contracts,
/// <summary>
/// BrokerQueue schema objects
/// </summary>
BrokerQueue,
/// <summary>
/// Service schema objects
/// </summary>
Services,
/// <summary>
/// FullTextCatalog schema objects
/// </summary>
FullTextCatalogs,
/// <summary>
/// FullTextIndexes schema objects
/// </summary>
FullTextIndexes,
/// <summary>
/// PartitionFunctions schema objects
/// </summary>
PartitionFunctions,
/// <summary>
/// PartitionSchemes schema objects
/// </summary>
PartitionSchemes,
}
#endregion Private Enums
#region Properties
/// <summary>
/// Sets the SQL Server instance where build can check dependencies.
/// </summary>
public string SqlInstance
{
set
{
this.sqlInstanceName = value;
}
}
/// <summary>
/// Gets or sets the sql user that will be used to connect to the database. Leave blank to use windows authentication
/// </summary>
public string SqlUsername
{
get
{
return this.sqlUsername;
}
set
{
this.sqlUsername = value;
}
}
/// <summary>
/// Gets or s the password that will be used to connect to the database
/// </summary>
public string SqlPassword
{
get
{
return this.sqlPassword;
}
set
{
this.sqlPassword = value;
}
}
/// <summary>
/// Sets the project GUID for the test deploy databaseName
/// </summary>
public string ProjectGUID
{
set
{
this.projectGUID = value;
}
}
/// <summary>
/// Sets the path of the output files.
/// </summary>
public string BuildPath
{
set
{
this.buildPath = value;
}
}
/// <summary>
/// Sets the schema scripts to process
/// </summary>
public List<string> SchemaScripts
{
set
{
this.sqlScripts = value;
}
}
/// <summary>
/// Sets the static files to include in the build output
/// </summary>
public List<string> StaticFiles
{
set
{
this.customFiles = value;
}
}
/// <summary>
/// Sets a value indicating whether or not the drop files should be separate from the parent file
/// </summary>
public bool SplitDropToSeperateFile
{
set
{
this.dropToSeperateFile = value;
}
}
/// <summary>
/// Sets a name to prepend to output scripts
/// </summary>
public string AppendDatabaseNameToScripts
{
set
{
this.appendDatabaseName = value.Trim();
this.appendDatabaseNameToScripts = !string.IsNullOrEmpty(this.appendDatabaseName);
}
}
/// <summary>
/// Sets a value indicating whether or not static data should be extracted
/// </summary>
public bool ExtractData
{
set
{
this.extractData = value;
}
}
/// <summary>
/// Sets the server from which static data should be extracted
/// </summary>
public string ExtractFromServer
{
set
{
this.extractFromServer = value;
}
}
/// <summary>
/// Gets or sets the sql user that will be used to connect to the reference data database. Leave blank to use windows authentication
/// </summary>
public string ExtractUsername
{
get
{
return this.extractUsername;
}
set
{
this.extractUsername = value;
}
}
/// <summary>
/// Gets or s the password that will be used to connect to the reference data database
/// </summary>
public string ExtractPassword
{
get
{
return this.extractPassword;
}
set
{
this.extractPassword = value;
}
}
/// <summary>
/// Sets the database from which static data should be extracted
/// </summary>
public string ExtractFromDatabase
{
set
{
this.extractFromDatabase = value;
}
}
/// <summary>
/// Sets the file to where the output of the command extractor is to be saved
/// </summary>
public string ExtractTargetFile
{
set
{
this.extractTargetFile = value;
}
}
/// <summary>
/// Sets the file enumerating the SQL commands to be executed
/// </summary>
public string ExtractCommandsFile
{
set
{
this.extractCommands = value;
}
}
/// <summary>
/// Sets the permissions template file containing the configuration for scripting permissions
/// </summary>
public string PermissionsTemplate
{
set
{
this.permissionsTemplate = value;
}
}
/// <summary>
/// Sets a value indicating whether or not permissions are to be scripted
/// </summary>
public bool ScriptPermissions
{
set
{
this.scriptPermissions = value;
}
}
/// <summary>
/// Sets the output file for the generated permissions
/// </summary>
public string PermissionsTargetFile
{
set
{
this.permissionsTargetFile = value;
}
}
/// <summary>
/// Gets or sets a value indicating whether or not the SqlExtract class is to use the ExistenceCheckerProvider object to determine whether or not an
/// object already exists in a "source" schema (or manifest), and if so, do not create a drop statement, but
/// instead modify the script to perform an ALTER
/// <para></para>
/// Currently, this check is only performed on stored procedures, functions and views.
/// </summary>
public bool AlterIfExists
{
get
{
return CheckExistenceOfObjects;
}
set
{
CheckExistenceOfObjects = value;
}
}
/// <summary>
/// Gets or sets the IExistenceChecker type string that will be created to perform an existence check on an object, as
/// per the "AlterIfExists" property description
/// </summary>
public string ExistenceCheckerProvider
{
get
{
return this.existenceCheckerProvider;
}
set
{
this.existenceCheckerProvider = value;
}
}
/// <summary>
/// Gets or sets the configuration parameter used for the IExistenceChecker.
/// </summary>
public string ExistenceCheckerConfiguration
{
get
{
return this.existenceCheckerConfiguration;
}
set
{
this.existenceCheckerConfiguration = value;
}
}
#endregion Properties
#region Public Methods
/// <summary>
/// Executes the task
/// </summary>
/// <param name="buildLogger">The logger instance to be used for all logging</param>
/// <returns>False if unsuccessful</returns>
public bool Execute(SqlDeployment.Interfaces.ILogger buildLogger)
{
Assembly thisDLL = Assembly.GetExecutingAssembly();
AssemblyName an = thisDLL.GetName();
Utilities.Logger = buildLogger;
Utilities.Logger.LogInformation("Extract Database task version: {0}...", an.Version);
ServerConnection connection = null;
Server sqlServer;
string db;
db = String.Format("SQL_Extract_{0}", this.projectGUID);
try
{
Utilities.Logger.LogInformation(String.Format("Attempting to connect to {0}...", this.sqlInstanceName));
InitializeExistenceChecker();
connection = new ServerConnection(this.sqlInstanceName);
if (string.IsNullOrEmpty(this.sqlUsername))
{
connection.LoginSecure = true;
}
else
{
connection.LoginSecure = false;
connection.Login = this.sqlUsername;
connection.Password = this.sqlPassword;
}
connection.Connect();
sqlServer = new Server(connection);
this.CreateDatabase(sqlServer, db);
this.ExtractDatabase();
this.ExtractReferenceData();
this.GeneratePermissions();
this.CopyCustomFiles();
Utilities.Logger.LogInformation("Extract complete...");
this.DropDatabase();
Utilities.Logger.LogInformation("Dropping build database...");
}
catch (Exception ex)
{
if (Utilities.Logger.LogErrorFromException(ex))
{
throw;
}
return false;
}
finally
{
connection.Disconnect();
}
return true;
}
#endregion Public Methods
#region Internal Methods
/// <summary>
/// Determines the object type and other info from the script file
/// </summary>
/// <param name="script">The script to be parsed</param>
/// <param name="fileName">The name of the file</param>
internal void DeriveObjectInformation(string script, string fileName)
{
bool matched = false;
bool fullText = false;
string parent = string.Empty;
string name = string.Empty;
string typeName = string.Empty;
string schemaName = string.Empty;
// Match on Messages
Regex createObject = new Regex(@"CREATE\s+(?<type>MESSAGE)\s+TYPE\s+(?<schema>\[?([A-Za-z_]*)\]?\.)?\[?(?<name>[\w.]+)\]?(\s+ON\s+(\[?dbo\]?\.)?\[?(?<parent>\w+)\]?)?");
Match m = createObject.Match(script);
matched = m.Success;
if (!matched)
{
createObject = new Regex(@"CREATE\s+(?<type>FULLTEXT\s+CATALOG)\s?\[?(?<name>[\w.]+)\]");
m = createObject.Match(script);
matched = m.Success;
}
if (!matched)
{
createObject = new Regex(@"CREATE\s+(?<type>FULLTEXT\s+INDEX)\s+ON\s+(?<schema>\[?([A-Za-z_]*)\]?\.)?\[?(?<name>[\w.]+)\]?\s+(?<definition>\(?\[?\w.*?\))\s+(KEY\s+INDEX)\s+?\[?(?<keyindex>\w.*)?\]\s+ON\s+?\[?(?<catalog>\w.*)?\]");
m = createObject.Match(script);
matched = m.Success;
fullText = matched;
}
if (!matched)
{
createObject = new Regex(@"CREATE\s+(?<type>PARTITION FUNCTION|PARTITION SCHEME)\s+?\[?(?<name>[\w.]+)\]?");
m = createObject.Match(script);
matched = m.Success;
}
if (!matched)
{
createObject = new Regex(@"CREATE\s+((NON|MESSAGE\s+TYPE|UNIQUE )?CLUSTERED\s+)?(?<type>[A-Za-z]*)\s+(?<schema>\[?([A-Za-z_]*)\]?\.)?\[?(?<name>[\w.\-]+)\]?(\s+ON\s+(\[?dbo\]?\.)?\[?(?<parent>\w+)\]?)?", RegexOptions.ExplicitCapture | RegexOptions.IgnoreCase | RegexOptions.Multiline);
m = createObject.Match(script);
matched = m.Success;
}
if (!matched || (m.Groups["name"].Value == "CLUSTERED") || (m.Groups["name"].Value == "NONCLUSTERED"))
{
createObject = new Regex(@"CREATE\s+(NONCLUSTERED|CLUSTERED|UNIQUE\s+CLUSTERED|UNIQUE\s+NONCLUSTERED)\s+?(?<type>[A-Za-z]*)\s+(?<schema>\[?([A-Za-z_]*)\]?\.)?\[?(?<name>[\w.]+)\]?(\s+ON\s+(\[?dbo\]?\.)?\[?(?<parent>\w+)\]?)?", RegexOptions.ExplicitCapture | RegexOptions.IgnoreCase | RegexOptions.Multiline);
m = createObject.Match(script);
matched = m.Success;
}
if (!matched)
{
createObject = new Regex(@"ALTER\s+TABLE\s+(?<schema>\[?([A-Za-z_]*)\]?\.)?\[?(?<parent>\w+)\]?\s+(WITH\s+(NO)?CHECK\s+)?ADD\s+CONSTRAINT\s+\[?(?<name>\w+)\]?\s+(?<type>DEFAULT|CHECK|FOREIGN\s+KEY|PRIMARY\s+KEY|UNIQUE)", RegexOptions.ExplicitCapture | RegexOptions.IgnoreCase | RegexOptions.Multiline);
m = createObject.Match(script);
matched = m.Success;
}
if (!matched)
{
createObject = new Regex(@"(ALTER+.+DATABASE)+.+()+\s+ADD+\s+(?<type>FILEGROUP)+\s+\[?(?<name>\w+)\]", RegexOptions.ExplicitCapture | RegexOptions.IgnoreCase | RegexOptions.Multiline);
m = createObject.Match(script);
matched = m.Success;
}
if (!matched)
{
Utilities.Logger.LogWarning("Unable to derive the base object information from " + fileName + ". Please check the defintion.");
return;
}
typeName = m.Groups["type"].Value.ToUpper();
parent = m.Groups["parent"].Value;
name = fullText ? m.Groups["name"].Value + m.Groups["keyindex"].Value : m.Groups["name"].Value;
schemaName = m.Groups["schema"].Value;
this.AddObject(script, this.SanitizeSchemaName(schemaName), this.SanitizeTypeName(typeName), name, parent);
}
#endregion Internal Methods
#region Private Methods
/// <summary>
/// Extracts the schema from files
/// </summary>
private void ExtractDatabase()
{
this.SetupDatabaseExtract();
Utilities.Logger.LogInformation("Loading source files...");
foreach (string fileName in this.sqlScripts)
{
if ((fileName.Contains("\\Storage\\Files") || fileName.Contains(".sqlpermissions") || fileName.Contains(".rolememberships.sql")) && !fileName.Contains(".Filegroups") && !fileName.Contains(".fulltext"))
{
continue;
}
else
{
string script = File.ReadAllText(fileName).Trim();
Utilities.Logger.LogInformation("Processing " + fileName);
this.DeriveObjectInformation(script, fileName);
}
}
foreach (ProcessDatabase es in this.scriptProcessors.Values)
{
es.CheckDependencies();
}
foreach (ProcessDatabase es in this.scriptProcessors.Values)
{
string fileName = Path.Combine(this.buildPath, es.FileName);
using (FileStream fs = File.Open(fileName, FileMode.Create, FileAccess.ReadWrite))
{
using (BinaryWriter bw = new BinaryWriter(fs))
{
bw.Write(es.RetrieveScript());
bw.Close();
}
fs.Close();
}
if (this.dropToSeperateFile)
{
if (es.DropFileName.Length > 0)
{
fileName = Path.Combine(this.buildPath, es.DropFileName);
using (FileStream fs = File.Open(fileName, FileMode.Create, FileAccess.ReadWrite))
{
using (BinaryWriter bw = new BinaryWriter(fs))
{
bw.Write(es.RetrieveDropScript());
bw.Close();
}
fs.Close();
}
}
}
}
foreach (string fileName in this.customFiles)
{
FileInfo f = new FileInfo(fileName);
File.Copy(fileName, Path.Combine(this.buildPath, f.Name), true);
}
foreach (ProcessDatabase es in this.scriptProcessors.Values)
{
es.CheckIfSkipped();
}
}
/// <summary>
/// Configures the database extract
/// </summary>
private void SetupDatabaseExtract()
{
Utilities.Logger.LogInformation("Initializing database extract...");
this.scriptProcessors = new Dictionary<SqlFileTypes, ProcessDatabase>();
this.scriptProcessors.Add(SqlFileTypes.Filegroups, new ProcessFilegroups(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName));
this.scriptProcessors.Add(SqlFileTypes.PartitionFunctions, new ProcessPartitionFunctions(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName));
this.scriptProcessors.Add(SqlFileTypes.PartitionSchemes, new ProcessPartitionScheme(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName));
this.scriptProcessors.Add(SqlFileTypes.FullTextCatalogs, new ProcessFullTextCatalogs(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName));
this.scriptProcessors.Add(SqlFileTypes.Schemas, new ProcessSchema(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName));
ProcessTables et = new ProcessTables(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName);
this.scriptProcessors.Add(SqlFileTypes.Tables, et);
this.scriptProcessors.Add(SqlFileTypes.Routes, new ProcessRoutes(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName));
this.scriptProcessors.Add(SqlFileTypes.Messages, new ProcessMessages(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName));
this.scriptProcessors.Add(SqlFileTypes.Contracts, new ProcessContracts(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName));
this.scriptProcessors.Add(SqlFileTypes.BrokerQueue, new ProcessQueues(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName));
this.scriptProcessors.Add(SqlFileTypes.Services, new ProcessServices(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName));
ProcessFunctions ef = new ProcessFunctions(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName);
this.scriptProcessors.Add(SqlFileTypes.Functions, ef);
ProcessViews ev = new ProcessViews(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName);
this.scriptProcessors.Add(SqlFileTypes.Views, ev);
ProcessClusteredConstraints ecc = new ProcessClusteredConstraints(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName);
this.scriptProcessors.Add(SqlFileTypes.ClusteredConstraints, ecc);
ecc.Tables = et;
ecc.Views = ev;
ProcessConstraints ec = new ProcessConstraints(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName);
this.scriptProcessors.Add(SqlFileTypes.Constraints, ec);
ec.Tables = et;
ec.Views = ev;
this.scriptProcessors.Add(SqlFileTypes.FullTextIndexes, new ProcessFullTextIndexes(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName));
ProcessRoles er = new ProcessRoles(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName);
this.scriptProcessors.Add(SqlFileTypes.Roles, er);
this.scriptProcessors.Add(SqlFileTypes.Circular, new ProcessCircular(this.database, ef, ev, this.appendDatabaseNameToScripts, this.appendDatabaseName));
this.scriptProcessors.Add(SqlFileTypes.Procedures, new ProcessProcedures(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName));
this.scriptProcessors.Add(SqlFileTypes.Triggers, new ProcessTriggers(this.database, this.appendDatabaseNameToScripts, this.appendDatabaseName));
foreach (ProcessDatabase es in this.scriptProcessors.Values)
{
es.SplitDropToSeperateFile = this.dropToSeperateFile;
}
}
/// <summary>
/// Creates the build database
/// </summary>
/// <param name="sqlServer">The server instance against which the build database must be deployed</param>
/// <param name="databaseName">The name of the new database</param>
private void CreateDatabase(Server sqlServer, string databaseName)
{
if (sqlServer.Databases.Contains(databaseName))
{
// assume the previous extract crashed without clean up -> drop the databaseName and allow clean create:
this.database = sqlServer.Databases[databaseName];
this.DropDatabase();
}
Utilities.Logger.LogInformation("Creating temporary build database {0}...", databaseName);
this.database = new Database(sqlServer, databaseName);
this.database.CompatibilityLevel = CompatibilityLevel.Version90;
FileGroup fg = new FileGroup(this.database, "PRIMARY");
DataFile df = new DataFile(fg, string.Concat("DB_Build", databaseName, "_Data"), Path.Combine(sqlServer.Information.MasterDBPath, databaseName + ".mdf"));
df.Size = 20480;
df.GrowthType = FileGrowthType.KB;
df.Growth = 20480;
fg.Files.Add(df);
this.database.FileGroups.Add(fg);
LogFile lf = new LogFile(this.database, string.Concat("DB_Build", databaseName, "_Log"), Path.Combine(sqlServer.Information.MasterDBPath, databaseName + ".ldf"));
lf.Size = 10240;
lf.GrowthType = FileGrowthType.KB;
lf.Growth = 10240;
this.database.LogFiles.Add(lf);
this.database.Create();
}
/// <summary>
/// Drops the build database
/// </summary>
private void DropDatabase()
{
if (this.database != null)
{
if (this.database.ActiveConnections > 1)
{
throw new Exception("Unable to drop the build database. Please ensure that there are no active connections!");
}
this.database.Drop();
}
}
/// <summary>
/// Adds a new object
/// </summary>
/// <param name="script">The ddl of the object</param>
/// <param name="schema">The schema name</param>
/// <param name="typeName">The type of object</param>
/// <param name="name">The name of the object</param>
/// <param name="parent">The objects parent</param>
private void AddObject(string script, string schema, string typeName, string name, string parent)
{
bool preExists = Utilities.ExistenceChecker.DoesDBObjectExists(schema, name);
switch (typeName)
{
case "CHECK":
this.scriptProcessors[SqlFileTypes.Constraints].AddObject(new CheckConstraint(schema, parent, name, script));
break;
case "FOREIGN KEY":
this.scriptProcessors[SqlFileTypes.Constraints].AddObject(new ForeignKey(schema, parent, name, script));
break;
case "FUNCTION":
this.scriptProcessors[SqlFileTypes.Functions].AddObject(new UDFunction(schema, name, script, preExists));
break;
case "INDEX":
this.AddClusterableConstraint(new Index(schema, parent, name, script));
break;
case "PRIMARY KEY":
this.AddClusterableConstraint(new PrimaryKey(schema, parent, name, script));
break;
case "UNIQUE":
this.AddClusterableConstraint(new UniqueConstraint(schema, parent, name, script));
break;
case "PROC":
case "PROCEDURE":
this.scriptProcessors[SqlFileTypes.Procedures].AddObject(new StoredProcedure(schema, name, script, preExists));
break;
case "TABLE":
this.scriptProcessors[SqlFileTypes.Tables].AddObject(new Table(schema, name, script));
break;
case "TRIGGER":
this.scriptProcessors[SqlFileTypes.Triggers].AddObject(new Trigger(schema, parent, name, script));
break;
case "VIEW":
this.scriptProcessors[SqlFileTypes.Views].AddObject(new View(schema, name, script, preExists));
break;
case "DEFAULT":
this.scriptProcessors[SqlFileTypes.Constraints].AddObject(new DefaultConstraint(schema, parent, name, script));
break;
case "SCHEMA":
this.scriptProcessors[SqlFileTypes.Schemas].AddObject(new DatabaseSchema(name, script));
break;
case "ROLE":
this.scriptProcessors[SqlFileTypes.Roles].AddObject(new Role(name, script));
break;
case "FILEGROUP":
this.scriptProcessors[SqlFileTypes.Filegroups].AddObject(new Filegroup(name, script));
break;
case "ROUTE":
this.scriptProcessors[SqlFileTypes.Routes].AddObject(new Route(name, script));
break;
case "MESSAGE":
this.scriptProcessors[SqlFileTypes.Messages].AddObject(new Message(name, script));
break;
case "CONTRACT":
this.scriptProcessors[SqlFileTypes.Contracts].AddObject(new Contract(name, script));
break;
case "QUEUE":
this.scriptProcessors[SqlFileTypes.BrokerQueue].AddObject(new BrokerQueue(name, script));
break;
case "SERVICE":
this.scriptProcessors[SqlFileTypes.Services].AddObject(new Service(name, script));
break;
case "FULLTEXT CATALOG":
this.scriptProcessors[SqlFileTypes.FullTextCatalogs].AddObject(new FullTextCatalog(name, script));
break;
case "FULLTEXT INDEX":
this.scriptProcessors[SqlFileTypes.FullTextIndexes].AddObject(new FullTextIndex(name, script));
break;
case "PARTITION FUNCTION":
this.scriptProcessors[SqlFileTypes.PartitionFunctions].AddObject(new PartitionFunction(name, script));
break;
case "PARTITION SCHEME":
this.scriptProcessors[SqlFileTypes.PartitionSchemes].AddObject(new PartitionScheme(name, script));
break;
default:
Utilities.Logger.LogInformation("Unhandled type {0} found for object {1}", typeName, name);
break;
}
}
/// <summary>
/// Adds a clusterable constraint
/// </summary>
/// <param name="obj">The constraint to add</param>
private void AddClusterableConstraint(ClusteredConstraint obj)
{
if (obj.Clustered)
{
this.scriptProcessors[SqlFileTypes.ClusteredConstraints].AddObject(obj);
}
else
{
this.scriptProcessors[SqlFileTypes.Constraints].AddObject(obj);
}
}
/// <summary>
/// Cleans up the schema to ensure consistency
/// </summary>
/// <param name="schema">The schema to "sanitize"</param>
/// <returns>The sanitized schema</returns>
private string SanitizeSchemaName(string schema)
{
if (string.IsNullOrEmpty(schema))
{
return SystemConstants.DefaultSchema;
}
else
{
schema = schema.Replace("]", string.Empty);
schema = schema.Replace("[", string.Empty);
schema = schema.Replace(".", string.Empty);
}
return schema;
}
/// <summary>
/// Loads the data extraction procedure into the static data source database
/// </summary>
/// <param name="extractDatabase">The database against which the scripts will be executed</param>
/// <returns>False if unsuccessful</returns>
private bool PrepareDatabaseExtract(Database extractDatabase)
{
try
{
Utilities.Logger.LogInformation("Creating data extraction procedure on target database ...");
string sqlText = string.Empty;
Assembly assembly = Assembly.GetExecutingAssembly();
using (StreamReader sr = new StreamReader(assembly.GetManifestResourceStream("AdamNachman.Build.SqlExtract.Resources.CreateMergeProc.sql")))
{
sqlText = sr.ReadToEnd();
sr.Close();
}
extractDatabase.ExecuteNonQuery(sqlText);
if (!string.IsNullOrEmpty(this.extractUsername))
{
sqlText = string.Format("GRANT EXECUTE ON [dbo].[spGenerateMergeData] TO [{0}]", extractDatabase.UserName);
extractDatabase.ExecuteNonQuery(sqlText);
}
}
catch (Exception ex)
{
if (Utilities.Logger.LogErrorFromException(ex))
{
throw;
}
return false;
}
return true;
}
/// <summary>
/// Extracts reference data from the static source database
/// </summary>
private void ExtractReferenceData()
{
if (this.extractData)
{
Utilities.Logger.LogInformation("Extracting merge data from source database");
ServerConnection connection = null;
Server sqlServer = null;
Database sourceDatabase = null;
try
{
Utilities.Logger.LogInformation(String.Format("Attempting to connect to {0}...", this.extractFromServer));
connection = new ServerConnection(this.extractFromServer);
if (string.IsNullOrEmpty(this.extractUsername))
{
connection.LoginSecure = true;
}
else
{
connection.LoginSecure = false;
connection.Login = this.extractUsername;
connection.Password = this.extractPassword;
}
connection.Connect();
sqlServer = new Server(connection);
sourceDatabase = sqlServer.Databases[this.extractFromDatabase];
this.PrepareDatabaseExtract(sourceDatabase);
this.ExecuteExtractCommands(sourceDatabase);
}
catch (Exception ex)
{
if (Utilities.Logger.LogErrorFromException(ex))
{
throw;
}
}
finally
{
this.CleanupTarget(sourceDatabase);
connection.Disconnect();
}
}
}
/// <summary>
/// Cleas up the extraction scripts from the source database
/// </summary>
/// <param name="extractDatabase">The database against which the commans will be executed</param>
private void CleanupTarget(Database extractDatabase)
{
try
{
Utilities.Logger.LogInformation("Cleaning up data extraction scripts ...");
string sqlText = string.Empty;
Assembly assembly = Assembly.GetExecutingAssembly();
using (StreamReader sr = new StreamReader(assembly.GetManifestResourceStream("AdamNachman.Build.SqlExtract.Resources.CleanupMergeProc.sql")))
{
sqlText = sr.ReadToEnd();
sr.Close();
}
extractDatabase.ExecuteNonQuery(sqlText);
}
catch (Exception ex)
{
if (Utilities.Logger.LogErrorFromException(ex))
{
throw;
}
}
}
/// <summary>
/// Executes the extract commands againts the database containing static data
/// </summary>
/// <param name="extractDatabase">The database against which the commans should be executed</param>
private void ExecuteExtractCommands(Database extractDatabase)
{
string commandLine = string.Empty;
StringBuilder sb = new StringBuilder();
using (StreamReader sr = new StreamReader(this.extractCommands))
{
while (sr.Peek() >= 0)
{
commandLine = sr.ReadLine();
if (!String.IsNullOrEmpty(commandLine.Trim()))
{
DataSet ds = extractDatabase.ExecuteWithResults(commandLine);
if (ds != null)
{
foreach (DataTable dt in ds.Tables)
{
foreach (DataRow dr in dt.Rows)
{
sb.AppendLine(dr[0].ToString());
}
}
}
}
}
sr.Close();
}
using (StreamWriter sw = new StreamWriter(this.extractTargetFile))
{
sw.Write(sb.ToString());
sw.Flush();
sw.Close();
}
}
/// <summary>
/// Generates permissions if the flag is set to true, does nothing when false
/// </summary>
private void GeneratePermissions()
{
if (this.scriptPermissions)
{
try
{
this.PreparePermissionProcedures();
this.ExecutePermissionsProcedures();
}
catch (Exception ex)
{
if (Utilities.Logger.LogErrorFromException(ex))
{
throw;
}
}
finally
{
this.CleanupPermissionsProcedures();
}
}
}
/// <summary>
/// Loads the permissions procedure into the build database
/// </summary>
private void PreparePermissionProcedures()
{
try
{
Utilities.Logger.LogInformation("Creating permission extraction procedure on target database ...");
string sqlText = string.Empty;
Assembly assembly = Assembly.GetExecutingAssembly();
using (StreamReader sr = new StreamReader(assembly.GetManifestResourceStream("AdamNachman.Build.SqlExtract.Resources.CreatePermissionsProc.sql")))
{
sqlText = sr.ReadToEnd();
sr.Close();
}
this.database.ExecuteNonQuery(sqlText);
}
catch (Exception ex)
{
if (Utilities.Logger.LogErrorFromException(ex))
{
throw;
}
}
}
/// <summary>
/// Gets the permission pattern from the permissions Template
/// </summary>
/// <returns>The permissions pattern to apply when generating permissions</returns>
private PermissionPattern GetPermissions()
{
PermissionPattern p = null;
if (File.Exists(this.permissionsTemplate))
{
using (StreamReader sr = new StreamReader(this.permissionsTemplate))
{
XmlSerializer xs = new XmlSerializer(typeof(PermissionPattern));
p = (PermissionPattern)xs.Deserialize(sr.BaseStream);
sr.Close();
}
}
return p;
}
/// <summary>
/// Execures the permissions procedure for each template entry
/// </summary>
private void ExecutePermissionsProcedures()
{
string commandLine = string.Empty;
StringBuilder sb = new StringBuilder();
Utilities.Logger.LogInformation("Executing permissions procedures ...");
using (StreamReader sr = new StreamReader(this.extractCommands))
{
PermissionPattern permissions = this.GetPermissions();
foreach (PermissionMap map in permissions.PermissionMaps)
{
string objectTypes = string.Concat("'", String.Join("'',''", map.ObjectTypes.Split(new char[] { ',' })), "'");
commandLine = string.Format("exec spGeneratePermissions @Principal='{0}',@Permissions='{1}',@ObjectTypes=''{2}''", map.PrincipalName, map.GrantType, objectTypes);
if (!String.IsNullOrEmpty(commandLine.Trim()))
{
DataSet ds = this.database.ExecuteWithResults(commandLine);
if (ds != null)
{
foreach (DataTable dt in ds.Tables)
{
foreach (DataRow dr in dt.Rows)
{
sb.AppendLine(dr[0].ToString());
}
}
}
}
}
sr.Close();
}
using (StreamWriter sw = new StreamWriter(this.permissionsTargetFile))
{
sw.Write(sb.ToString());
sw.Flush();
sw.Close();
}
}
/// <summary>
/// Removes the autogenerated permissions procedure from the build database
/// </summary>
private void CleanupPermissionsProcedures()
{
try
{
Utilities.Logger.LogInformation("Cleaning up permission extraction procedure on target database ...");
string sqlText = string.Empty;
Assembly assembly = Assembly.GetExecutingAssembly();
using (StreamReader sr = new StreamReader(assembly.GetManifestResourceStream("AdamNachman.Build.SqlExtract.Resources.CleanupPermissionsProc.sql")))
{
sqlText = sr.ReadToEnd();
sr.Close();
}
this.database.ExecuteNonQuery(sqlText);
}
catch (Exception ex)
{
if (Utilities.Logger.LogErrorFromException(ex))
{
throw;
}
return;
}
}
/// <summary>
/// Initializes the ExistenceChecker class
/// </summary>
private bool InitializeExistenceChecker()
{
if (AlterIfExists)
{
Utilities.Logger.LogInformation("Initializing existence checker");
Type t = null;
t = Type.GetType(this.existenceCheckerProvider);
IExistenceChecker checker = (IExistenceChecker)Activator.CreateInstance(t);
checker.Logger = Utilities.Logger;
checker.ConfigurationString = this.existenceCheckerConfiguration;
checker.Initialize();
Utilities.ExistenceChecker = checker;
return true;
}
else
{
Utilities.ExistenceChecker = new ExistenceCheckerDefault();
return true;
}
}
/// <summary>
/// Removes whitespace from the string
/// </summary>
/// <param name="typeName">The string to process</param>
/// <returns>A cleaned string</returns>
private string SanitizeTypeName(string typeName)
{
return typeName.Trim(); ;
}
private void CopyCustomFiles()
{
foreach (string fileName in this.customFiles)
{
FileInfo f = new FileInfo(fileName);
File.Copy(fileName, Path.Combine(this.buildPath, f.Name), true);
}
}
#endregion Private Methods
}
}