Continuous Integration for Databases with Visual Studio

// <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
    }
}