public void Initialize()
{
_log.Info("Initializing Auditing Databases.");
var publicCredentials = BuildPublicCredentials();
var creatorCredentials = new DatabaseCredentials(_configuration.DatabaseCreatorUserName, _configuration.DatabaseCreatorPassword);
foreach (var siteSubscriptionId in _configuration.SiteSubscriptionIds)
{
ServiceResponse response;
var builder = new AuditingConnectionStringBuilder(_configuration.DatabaseInstanceName, siteSubscriptionId);
var databaseInfo = new DatabaseInfo(_configuration.DatabaseInstanceName, builder.InitialCatalog, builder.UserId, builder.Password);
var databaseManager = new DatabaseManager(_log, null);
if (!DatabaseExists(builder.InitialCatalog))
{
_log.Info("Creating database " + builder.InitialCatalog);
response = databaseManager.CreateDatabase(databaseInfo, publicCredentials.UserName, publicCredentials.Password, _scriptResourceName, new AuditingScriptLoader(), creatorCredentials);
}
else
{
_log.Info("Updating database " + builder.InitialCatalog);
response = databaseManager.UpdateDatabaseSchema(databaseInfo, publicCredentials.UserName, _scriptResourceName, new AuditingScriptLoader());
}
CheckServiceResponse(response);
}
_log.Info("Auditing Databases Initialized.");
}
/// <summary>
/// Determines if the specified databases already exists.
/// </summary>
/// <param name="info">Information describing the datbase to inspect.</param>
/// <returns>Returns <c>true</c> if database exists, otherwise <c>false</c>.</returns>
public static bool DatabaseExists(DatabaseInfo info)
{
using (var database = new DatabaseManipulator(info))
{
return database.Exists();
}
}
/// <summary>
/// Creates the script version table.
/// </summary>
/// <param name="info">The info describing the database to version.</param>
public void CreateScriptVersionTable(DatabaseInfo info)
{
using (var database = new DatabaseManipulator(info))
{
if (!database.SqlServer.Databases[info.DatabaseName].Tables.Contains(_scriptVersionTableName))
{
string sqlText = @"CREATE TABLE [dbo].[" + _scriptVersionTableName + @"]
(
[Component] [varchar](100) NOT NULL,
[Version] [varchar](7) NOT NULL,
CONSTRAINT [PK_SqlScriptVersionInfo] PRIMARY KEY CLUSTERED
(
[Component] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]";
database.SqlServer.Databases[info.DatabaseName].ExecuteNonQuery(sqlText);
}
}
}
/// <summary>
/// Creates a new database.
/// </summary>
/// <param name="info">Information describing the database to affect.</param>
/// <param name="databaseUserNamePublic">Name of a public database user to create.</param>
/// <param name="databasePasswordPublic">The database password for the new public user.</param>
/// <param name="scriptResourceNames">A list of the names of the resource files containing the DDL scripts.</param>
/// <param name="scriptLoader">Class that can load assemblies containing SQL script resource files.</param>
/// <param name="creatorCredentials">The credentials used to create the database.</param>
/// <returns>
/// An instance of the <see cref="ServiceResponse"/> class.
/// </returns>
public ServiceResponse CreateDatabase(DatabaseInfo info, string databaseUserNamePublic, string databasePasswordPublic, IEnumerable<string> scriptResourceNames, ScriptLoader scriptLoader, DatabaseCredentials creatorCredentials)
{
return CreateDatabase(info, databaseUserNamePublic, databasePasswordPublic, null, scriptResourceNames, scriptLoader, creatorCredentials);
}
private ServiceResponse UpdateDatabaseSchema(DatabaseInfo info, string databaseUserNamePublic, string scriptResourceName, IEnumerable<string> scriptResourceNames, ScriptLoader scriptLoader)
{
// Create temp App Domain for loading assemblies
AppDomain tempAppDomain = AppDomain.CreateDomain("TempAppDomain");
try
{
using (var ts = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 5, 0)))
{
using (var database = new DatabaseManipulator(info))
{
smo.Server sqlServer = database.SqlServer;
// Create the script version info table if needed
var versionInfoDao = new SqlScriptVersionInfoDao(info.ConnectionString);
versionInfoDao.CreateScriptVersionTable(info);
_logger.Info("Verifying database schema...", "Database Name", info.DatabaseName);
// Get script execution history as a dictionary
var sqlScriptVersionInfoDict = new Dictionary<string, string>();
foreach (IVersionInfo sqlScriptVersionInfo in versionInfoDao.ReadList())
{
sqlScriptVersionInfoDict.Add(sqlScriptVersionInfo.Component, sqlScriptVersionInfo.Version);
}
// Get all assembly names in the specified directory
IEnumerable<string> files = scriptLoader.GetSortedScriptAssemblyList();
foreach (string file in files)
{
// Delegate assembly loading/processing to temp App Domain
tempAppDomain.SetData("FileName", file);
tempAppDomain.SetData("ResourceFileName", scriptResourceName);
tempAppDomain.SetData("ResourceFileNames", scriptResourceNames);
var dbScriptsToExecute = new Dictionary<string, string>();
try
{
tempAppDomain.DoCallBack(new CrossAppDomainDelegate(ScriptLoader.GetScriptsFromAssembly));
dbScriptsToExecute = (Dictionary<string, string>)tempAppDomain.GetData("DbScriptsToExecute");
}
catch (Exception)
{
_logger.Error("Error loading assembly!", "FileName", file);
}
// Sort the scripts by key
string[] sortedScriptKeys = new string[dbScriptsToExecute.Keys.Count];
dbScriptsToExecute.Keys.CopyTo(sortedScriptKeys, 0);
Array.Sort(sortedScriptKeys);
foreach (string scriptKey in sortedScriptKeys)
{
string scriptValue = dbScriptsToExecute[scriptKey];
string[] keyParts = scriptKey.Split("_".ToCharArray());
if (keyParts.GetUpperBound(0) != 1)
{
throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, "Invalid script key format ({0})", scriptKey));
}
string component = Path.GetFileNameWithoutExtension(file);
string version = keyParts[1];
// If component has no script history, or current script hasn't been executed, execute it
if (!sqlScriptVersionInfoDict.ContainsKey(component) ||
(int.Parse(version, CultureInfo.InvariantCulture) >
int.Parse(sqlScriptVersionInfoDict[component], CultureInfo.InvariantCulture)))
{
ConsoleUtils.WriteLine(string.Format(CultureInfo.InvariantCulture,
" Executing\n Component: {0}\n Script: {1}\n", component, scriptKey), ConsoleColor.Green);
scriptValue = ApplyScriptTokens(scriptValue, databaseUserNamePublic);
sqlServer.Databases[info.DatabaseName].ExecuteNonQuery(scriptValue);
// Check if script version information already exists for this component
IVersionInfo versionInfo = versionInfoDao.Read(component);
AddOrUpdateVersionInfo(component, version, versionInfo, versionInfoDao);
}
}
}
ts.Complete();
}
}
}
catch (Exception ex)
{
_logger.Error("Update of database failed. Please check that you have configured the database server to allow both SQL Server authentication and Windows authentication.", ex, info.GenerateCustomLoggingProperties());
_response.AddError("Update of database ({0}) on server ({1}) failed.", info.DatabaseName, info.ServerName);
}
finally
{
// Unload the temp App Domain, which unloads any loaded assemblies
AppDomain.Unload(tempAppDomain);
}
return _response;
}
private ServiceResponse CreateDatabase(DatabaseInfo info, string databaseUserNamePublic, string databasePasswordPublic, string scriptResourceName, IEnumerable<string> scriptResourceNames, ScriptLoader scriptLoader, DatabaseCredentials creatorCredentials)
{
var creatorInfo = info.CloneWithNewCredentials(creatorCredentials);
using (var database = new DatabaseManipulator(creatorInfo))
{
try
{
smo.Server sqlServer = database.SqlServer;
// Make sure SQL Server allows SQL logins if needed
if (creatorInfo.HasUserName())
{
if (database.LoginMode == smo.ServerLoginMode.Integrated)
{
_logger.Error("Database Server is not configured for mixed-mode authentication.", "Database Server", creatorInfo.ServerName);
_response.AddError(string.Format(CultureInfo.InvariantCulture, "Database Server ({0}) is not configured for mixed-mode authentication.", creatorInfo.ServerName));
}
}
if (!_response.HasErrors)
{
if (!database.Exists())
{
_logger.Info("Creating database.", "Database name", info.DatabaseName, "Database Server", info.ServerName);
// Create new database
var newDatabase = new smo.Database(sqlServer, info.DatabaseName);
newDatabase.Create();
database.CreateLogin(info.UserName, info.Password, true);
database.CreateLogin(databaseUserNamePublic, databasePasswordPublic, false);
// I don't know why we have to dispose this here, but I was afraid to remove it
database.Dispose();
UpdateDatabaseSchema(info, databaseUserNamePublic, scriptResourceName, scriptResourceNames, scriptLoader);
_logger.Info("Database created successfully.", info.GenerateCustomLoggingProperties());
}
else
{
_logger.Error("Database already exists.", info.GenerateCustomLoggingProperties());
_response.AddError(string.Format(CultureInfo.InvariantCulture, "Database ({0}) on server ({1}) already exists.", info.DatabaseName, info.ServerName));
}
}
}
catch (Exception ex)
{
_logger.Error("Creation of Database failed.", ex, info.GenerateCustomLoggingProperties());
_response.AddError(string.Format(CultureInfo.InvariantCulture, "Creation of Database ({0}) on server ({1}) failed: {2}", info.DatabaseName, info.ServerName, ex.Message));
}
}
return _response;
}
public ServiceResponse UpdateDatabaseSchema(DatabaseInfo info, string databaseUserNamePublic, string component, string scriptPath)
{
try
{
using (var ts = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 5, 0)))
{
using (var database = new DatabaseManipulator(info))
{
smo.Server sqlServer = database.SqlServer;
var versionInfoDao = new SqlScriptVersionInfoDao(info.ConnectionString);
versionInfoDao.CreateScriptVersionTable(info);
_logger.Info("Verifying database schema...", "Database Name", info.DatabaseName);
int currentVersion = 0;
IVersionInfo sqlScriptVersionInfo = versionInfoDao.ReadList()
.Where(v => v.Component == component)
.OrderBy(v => v.Version)
.LastOrDefault();
if (sqlScriptVersionInfo != null)
{
currentVersion = int.Parse(sqlScriptVersionInfo.Version, CultureInfo.InvariantCulture);
}
IEnumerable<string> files = Directory.GetFiles(scriptPath, "script_*.sql");
foreach (string file in files)
{
string scriptName = Path.GetFileNameWithoutExtension(file);
string scriptValue = File.ReadAllText(file);
string[] keyParts = scriptName.Split("_".ToCharArray());
string versionString = keyParts[1];
int scriptVersion = int.Parse(versionString, CultureInfo.InvariantCulture);
if (scriptVersion > currentVersion)
{
ConsoleUtils.WriteLine(string.Format(CultureInfo.InvariantCulture,
" Executing Component: {0} Script: {1}\n", component, scriptName), ConsoleColor.Green);
scriptValue = ApplyScriptTokens(scriptValue, databaseUserNamePublic);
sqlServer.Databases[info.DatabaseName].ExecuteNonQuery(scriptValue);
// Check if script version information already exists for this component
IVersionInfo versionInfo = versionInfoDao.Read(component);
AddOrUpdateVersionInfo(component, versionString, versionInfo, versionInfoDao);
}
}
ts.Complete();
}
}
}
catch (Exception ex)
{
_logger.Error("Update of database failed. Please check that you have configured the database server to allow both SQL Server authentication and Windows authentication.", ex, info.GenerateCustomLoggingProperties());
_response.AddError("Update of database ({0}) on server ({1}) failed.", info.DatabaseName, info.ServerName);
}
return _response;
}
/// <summary>
/// Updates the schema of the specified database by scanning for new SQL scripts
/// within embedded resource files, and executing those scripts.
/// </summary>
/// <param name="info">The information describing the database to update.</param>
/// <param name="databaseUserNamePublic">The public database user name.</param>
/// <param name="scriptResourceNames">A list of the names of the resource files containing the DDL scripts.</param>
/// <param name="scriptLoader">Class that can load assemblies containing SQL script resource files.</param>
/// <returns>
/// An instance of the <see cref="ServiceResponse"/> class.
/// </returns>
public ServiceResponse UpdateDatabaseSchema(DatabaseInfo info, string databaseUserNamePublic, IEnumerable<string> scriptResourceNames, ScriptLoader scriptLoader)
{
return UpdateDatabaseSchema(info, databaseUserNamePublic, null, scriptResourceNames, scriptLoader);
}
public ServiceResponse DestroyDatabase(DatabaseInfo info, DatabaseCredentials destroyerCredentials, string databaseUserNamePublic)
{
var creatorInfo = info.CloneWithNewCredentials(destroyerCredentials);
using (var database = new DatabaseManipulator(creatorInfo))
{
try
{
if (database.Exists())
{
_logger.Info("Destroying database.", info.GenerateCustomLoggingProperties());
database.Destroy();
database.RemoveLogin();
database.RemoveLogin(databaseUserNamePublic);
_logger.Info("Database destroyed successfully.", info.GenerateCustomLoggingProperties());
}
else
{
_logger.Info("Database does not exist.", info.GenerateCustomLoggingProperties());
_response.AddInfo("Database ({0}) on server ({1}) does not exist.", info.DatabaseName, info.ServerName);
}
}
catch (Exception ex)
{
_logger.Error("Destruction of database failed.", ex, info.GenerateCustomLoggingProperties());
_response.AddError("Destruction of database ({0}) on server ({1}) failed.", info.DatabaseName, info.ServerName);
}
}
return _response;
}
private static DatabaseInfo CreateDatabaseInfo()
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionStrings.TestDatabase);
var databaseInfo = new DatabaseInfo(builder.DataSource, builder.InitialCatalog, builder.UserID, builder.Password);
return databaseInfo;
}
/// <summary>
/// Initializes a new instance of the <see cref="DatabaseManipulator"/> class.
/// </summary>
/// <param name="info">The info.</param>
public DatabaseManipulator(DatabaseInfo info)
{
_info = info;
}
private bool DatabaseExists(string databaseName)
{
var databaseInfo = new DatabaseInfo(_configuration.DatabaseInstanceName, databaseName, _configuration.DatabaseCreatorUserName, _configuration.DatabaseCreatorPassword);
return DatabaseManager.DatabaseExists(databaseInfo);
}