public DbUpgradeScript(SqlMigration migration)
{
Migration = migration;
ScriptType = Migration.GetDdlScriptType();
Sql = migration.Sql;
_creationOrder = migration.CreationOrder;
}
public DbUpgradeScript(SqlMigration migration)
{
Migration = migration;
ScriptType = Migration.GetDdlScriptType();
Sql = migration.Sql;
_creationOrder = migration.CreationOrder;
}
private int _creationOrder; //used for keeping 'natural' (creation order) to later use it when applying scripts
#endregion Fields
#region Constructors
public DbUpgradeScript(DbScriptType scriptType, string sql, DbObjectChange modelChange = null, ApplyPhase phase = ApplyPhase.Default)
{
ScriptType = scriptType;
Sql = sql;
ModelChange = modelChange;
Phase = phase;
_creationOrder = System.Threading.Interlocked.Increment(ref _creationCount);
}
public DbUpgradeScript(DbScriptType scriptType, string sql, DbObjectChange modelChange = null, ApplyPhase phase = ApplyPhase.Default)
{
ScriptType = scriptType;
Sql = sql;
ModelChange = modelChange;
Phase = phase;
_creationOrder = System.Threading.Interlocked.Increment(ref _creationCount);
}
public DbUpgradeScript AddScript(DbScriptType scriptType, ApplyPhase phase, string sqlTemplate, params object[] args)
{
string sql = sqlTemplate;
if(args != null && args.Length > 0)
sql = string.Format(sqlTemplate, args);
var script = new DbUpgradeScript(scriptType, sql, this, phase);
Scripts.Add(script);
return script;
}
private static SortedList <long, string> ExecuteSqlScripts(SortedList <long, string> scriptFilesToRun,
DbScriptType scriptType,
bool useFileNameAsChangeVersion,
string connectionstring, bool failOnException = true,
string dbPrefixToReplace = "",
string dbPrefixReplacement = "")
{
//Execute scripts
var failedScripts = new SortedList <long, string>(scriptFilesToRun); //clone list
Assembly asm = null;
asm = InstallScriptsAssembly;
foreach (long key in scriptFilesToRun.Keys)
{
try
{
var filename = scriptFilesToRun[key];
Debug.WriteLine("Executing sql file " + filename + ".");
string sqlScript = null;
var sr = new StreamReader(asm.GetManifestResourceStream(filename));
sqlScript = sr.ReadToEnd();
if (!string.IsNullOrEmpty(dbPrefixReplacement))
{
sqlScript = sqlScript.Replace(dbPrefixToReplace, dbPrefixReplacement);
}
//if (!ExludeItem(filename))
//{
ExecuteSql(sqlScript, connectionstring, filename);
if (useFileNameAsChangeVersion)
{
InsertDbVersion(key,
new FileInfo(filename).Name.Replace(
asm.InferDefaultNamespace() + "." + "ChangeScripts" + ".", string.Empty),
connectionstring);
}
//}
failedScripts.Remove(key);
}
catch
{
if (failOnException)
{
throw;
}
}
}
return(failedScripts);
}
public DbUpgradeScript AddScript(DbScriptType scriptType, ApplyPhase phase, string sqlTemplate, params object[] args)
{
Util.CheckNotEmpty(sqlTemplate, "Fatal error: attempt to add empty upgrade SQL scrpt; scriptType: {0}", scriptType);
string sql = sqlTemplate;
if (args != null && args.Length > 0)
{
sql = string.Format(sqlTemplate, args);
}
var script = new DbUpgradeScript(scriptType, sql, this, phase);
Scripts.Add(script);
return(script);
}
public DbUpgradeScript AddScript(DbScriptType scriptType, string sqlTemplate, params object[] args)
{
return AddScript(scriptType, ApplyPhase.Default, sqlTemplate, args);
}
public DbUpgradeScript AddScript(DbScriptType scriptType, string sqlTemplate, params object[] args)
{
return(AddScript(scriptType, ApplyPhase.Default, sqlTemplate, args));
}
private static SortedList <long, string> GetScriptFilesToRun(long?dbVersion, DbScriptType scriptType,
ReadKeyFromPosition currentReadKeyFromPosition,
params string[] includeFolders)
{
var scriptFilesToRun = new SortedList <long, string>();
Assembly asm = null;
asm = InstallScriptsAssembly;
string[] files = asm.GetManifestResourceNames();
int index = 1;
foreach (string scriptFile in files)
{
var currentIncludeFolder = string.Empty;
foreach (string includeFolder in includeFolders)
{
if (scriptFile.Contains(string.Format(".{0}.", includeFolder)))
{
currentIncludeFolder = includeFolder;
}
}
if (!string.IsNullOrEmpty(currentIncludeFolder))
{
var keyAsString = string.Empty;
var fileNameWithoutExtension = Path.GetFileNameWithoutExtension(scriptFile);
var indexOfVersionUnderscore = 0;
var tempFileNameForVersionSearch = fileNameWithoutExtension.Replace(" ", "_");
// Remove namespace from filename
//tempFileNameForVersionSearch = tempFileNameForVersionSearch.Replace(String.Format("{0}.{1}.", asm.InferDefaultNamespace(), currentIncludeFolder), string.Empty);
tempFileNameForVersionSearch = tempFileNameForVersionSearch.Replace(String.Format("{0}.{1}.", InstallScriptsAssemblyNamespace, currentIncludeFolder), string.Empty);
if (currentReadKeyFromPosition == ReadKeyFromPosition.Suffix & (scriptType == DbScriptType.CreateDatabase | scriptType == DbScriptType.ChangeScript))
{
indexOfVersionUnderscore = tempFileNameForVersionSearch.LastIndexOf('_');
keyAsString = tempFileNameForVersionSearch.Substring(indexOfVersionUnderscore + 1);
}
else if (currentReadKeyFromPosition == ReadKeyFromPosition.Prefix & (scriptType == DbScriptType.CreateDatabase | scriptType == DbScriptType.ChangeScript))
{
indexOfVersionUnderscore = tempFileNameForVersionSearch.IndexOf('_');
keyAsString = tempFileNameForVersionSearch.Substring(0, indexOfVersionUnderscore);
}
else
{
keyAsString = index.ToString();
}
long key = 0;
bool parseResult = long.TryParse(keyAsString, out key);
if (!parseResult)
{
throw new DatabaseInstallerException(string.Format(CultureInfo.InvariantCulture,
"Failed to extract version number from file {0} ",
scriptFile));
}
if (dbVersion.HasValue == false || (key > dbVersion.Value))
{
scriptFilesToRun.Add(key, scriptFile);
}
}
index += 1;
}
return(scriptFilesToRun);
}
public static void ExecuteSqlScripts(long?dbVersion, DbScriptType scriptType, ReadKeyFromPosition currentReadKeyFromPosition, bool useFileNameAsChangeVersion, string connectionstring, string dbPrefixToReplace, string dbPrefixReplacement, params string[] includeFolders)
{
dynamic scriptFilesToRun = GetScriptFilesToRun(dbVersion, scriptType, currentReadKeyFromPosition, includeFolders);
ExecuteSqlScripts(scriptFilesToRun, scriptType, useFileNameAsChangeVersion, connectionstring, true, dbPrefixToReplace, dbPrefixReplacement);
}
public static void ExecuteSqlScripts(long?dbVersion, DbScriptType scriptType, ReadKeyFromPosition currentReadKeyFromPosition, bool useFileNameAsChangeVersion, string connectionstring, params string[] includeFolders)
{
ExecuteSqlScripts(dbVersion, scriptType, currentReadKeyFromPosition, useFileNameAsChangeVersion, connectionstring, string.Empty, string.Empty, includeFolders);
}
/// <summary>
/// using the rows currently filled in the table, generate an Insert, Update, Delete, DeleteInsert, InsertUpdate script.
/// </summary>
/// <param name="target"></param>
/// <param name="type"></param>
/// <param name="useTransaction"></param>
/// <param name="printStatusGap"></param>
public void GenerateScript(Stream target, DbScriptType type, bool useTransaction, int printStatusGap)
{
using (var writer = new StreamWriter(target))
{
writer.WriteLine($"/** Auto-Generated {type} Script. Created {DateTime.Now} by {Environment.UserName}@{Environment.UserDomainName} on {Environment.MachineName} **/");
writer.WriteLine($"/** {type} {this.Rows.Count} Rows. Table: {this.TableName}{(!string.IsNullOrEmpty(WhereClause) ? $" Where: {WhereClause}" : "")} **/");
if (!string.IsNullOrEmpty(Comment))
{
// insert the table comment:
writer.WriteLine($"/** {Comment} **/");
}
if (useTransaction)
{
writer.WriteLine("BEGIN TRANSACTION");
}
int count = 0;
foreach (var row in Rows)
{
if (!string.IsNullOrEmpty(row.Comment))
{
writer.WriteLine($"/** {row.Comment} **/");
}
writer.WriteLine($"/****** {count} ******/");
switch (type)
{
case DbScriptType.Insert:
writer.WriteLine(row.InsertStatement);
break;
case DbScriptType.Update:
writer.WriteLine(row.UpdateStatement);
break;
case DbScriptType.Delete:
writer.WriteLine(row.DeleteStatement);
break;
case DbScriptType.InsertUpdate:
writer.WriteLine($"IF EXISTS(select * from {TableName} where {row.WhereClause})");
writer.WriteLine($"{row.UpdateStatement}");
writer.WriteLine($"ELSE");
writer.WriteLine($"{row.InsertStatement}");
break;
case DbScriptType.DeleteInsert:
writer.WriteLine(row.DeleteStatement);
writer.WriteLine(row.InsertStatement);
break;
default:
break;
}
count++;
if (printStatusGap > 0 && count > 0)
{
if (count % printStatusGap == 0)
{
writer.WriteLine($"PRINT '{count}/{Rows.Count} COMPLETE'");
}
}
}
if (printStatusGap > 0)
{
writer.WriteLine($"PRINT '{count}/{Rows.Count} COMPLETE'");
}
if (useTransaction)
{
writer.WriteLine("COMMIT;");
}
}
}
/// <summary>
/// using the rows currently filled in the table, generate an Insert, Update, Delete, DeleteInsert, InsertUpdate script.
/// </summary>
/// <param name="target"></param>
/// <param name="type"></param>
/// <param name="useTransaction"></param>
/// <param name="printStatusGap"></param>
public void GenerateScript(Stream target, DbScriptType type, bool useTransaction, int printStatusGap, bool closeStream = false)
{
var writer = new StreamWriter(target);
try
{
writer.WriteLine($"/** Auto-Generated {type} Script. Created {DateTime.Now} by {Environment.UserName}@{Environment.UserDomainName} on {Environment.MachineName} **/");
writer.WriteLine($"/** {type} {this.Rows.Count} Rows. Table: {this.TableName}{(!string.IsNullOrEmpty(WhereClause) ? $" Where: {WhereClause}" : "")} **/");
if (!string.IsNullOrEmpty(Comment))
{
// insert the table comment:
writer.WriteLine($"/** {Comment} **/");
}
if (!String.IsNullOrEmpty(this.UseDatabaseName))
{
// add the use statement
writer.WriteLine($"USE [{this.UseDatabaseName}]");
}
if (useTransaction)
{
// start a transaction
writer.WriteLine("BEGIN TRANSACTION");
}
// pre declare scalar variables for <=2005 compatibility
if (CompatibilityLevel == TSQLCompatibilityLevel.SQL_2005)
{
var declarations = this.Declarations;
if (!string.IsNullOrEmpty(declarations))
{
writer.WriteLine("/** sub query declarations **/");
writer.WriteLine(declarations);
}
}
// pre-delete records for replace script
if (type == DbScriptType.Replace)
{
// add the pre-delete all statement
writer.WriteLine($"/** delete all records from {TableName} **/");
writer.WriteLine($"DELETE FROM [{TableName}]");
}
var subQuerySetList = "";
int count = 0;
// enumerate the rows of data
foreach (var row in Rows)
{
// append the comment
if (!string.IsNullOrEmpty(row.Comment))
{
writer.WriteLine($"/** {row.Comment} **/");
}
// status comment
writer.WriteLine($"/** {TableName}:{type}:{count}/{Rows.Count} **/");
// fetch values into scalar variables for 2005 sub-select scripts
if (CompatibilityLevel == TSQLCompatibilityLevel.SQL_2005)
{
// get the sub-query set value T-SQL
var set = row.SubQueries;
// check it's not null and different to the last one:
if (!string.IsNullOrEmpty(set) && !set.Equals(subQuerySetList))
{
subQuerySetList = set;
// write out the set @a = (select b from c where d) queries that are required to lookup related items
writer.WriteLine(subQuerySetList);
}
}
// write out the correct statement depending on the type:
switch (type)
{
// write insert statements
case DbScriptType.Insert:
case DbScriptType.Replace:
writer.WriteLine(row.InsertStatement);
break;
// write update statements
case DbScriptType.Update:
writer.WriteLine(row.UpdateStatement);
break;
// write delete statements
case DbScriptType.Delete:
writer.WriteLine(row.DeleteStatement);
break;
// write insert/update conditional statements
case DbScriptType.InsertUpdate:
writer.WriteLine($"IF EXISTS(select * from {TableName} where {row.WhereClause})");
writer.WriteLine($"{row.UpdateStatement}");
writer.WriteLine($"ELSE");
writer.WriteLine($"{row.InsertStatement}");
break;
// write delete/insert statements
case DbScriptType.DeleteInsert:
writer.WriteLine(row.DeleteStatement);
writer.WriteLine(row.InsertStatement);
break;
default:
break;
}
// increment
count++;
// write out progress statements
if (printStatusGap > 0 && count > 0)
{
if (count % printStatusGap == 0)
{
writer.WriteLine($"PRINT '{TableName}:{count}/{Rows.Count} COMPLETE'");
}
}
}
if (printStatusGap > 0)
{
writer.WriteLine($"PRINT '{TableName}:{count}/{Rows.Count} COMPLETE'");
}
if (useTransaction)
{
writer.WriteLine("COMMIT;");
writer.WriteLine("-- ROLLBACK;");
}
}
finally
{
// flush the writer.
writer.Flush();
if (closeStream)
{
writer.Dispose();
}
}
}
