public void Add(SqlScript item, int deep)
{
if (list == null) list = new List<SqlScript>();
if (item != null)
{
item.Deep = deep;
list.Add(item);
}
}
public ActionResult LoadSqlScript(int id)
{
var sqlScript = new SqlScript(id);
return(Json(new
{
sqlScript.Id,
sqlScript.Name,
sqlScript.Contents,
}, JsonRequestBehavior.AllowGet));
}
public ActionResult GetSqlScripts()
{
var sqlScripts = SqlScript.GetSqlScripts();
return(Json(sqlScripts.Select(x => new
{
x.Id,
x.Name,
x.Contents
}), JsonRequestBehavior.AllowGet));
}
public ActionResult DeleteSqlScript(int id)
{
var sqlScript = new SqlScript(id);
sqlScript.Delete();
return(Json(new
{
Message = $"Successfully deleted script with Id <strong>{id}</strong>."
}, JsonRequestBehavior.AllowGet));
}
public void RunHashedScript_Test()
{
var opts = new SqlScriptOptions {
RunGroupOrder = 10, ScriptType = global::DbUp.Support.ScriptType.RunOnce
};
SqlScript script = new SqlScript("test_script_1", "SELECT GETDATE()", opts);
IEnumerable <SqlScript> before = null;
IEnumerable <SqlScript> after = null;
IEnumerable <SqlScript> afterErr = null;
_exec.Run(script, before, after, afterErr, HashJournal);
}
/// <summary>
/// Gets all scripts that should be executed.
/// </summary>
public IEnumerable <SqlScript> GetScripts(IConnectionManager connectionManager)
{
var files = Directory.GetFiles(directoryPath, "*.sql", ShouldSearchSubDirectories()).AsEnumerable();
if (filter != null)
{
files = files.Where(filter);
}
return(files.Select(x => SqlScript.FromFile(directoryPath, x, encoding))
.OrderBy(x => x.Name)
.ToList());
}
/// <summary>
/// Removes the rolled back script from the database specified in a given connection string.
/// </summary>
/// <param name="script">The script.</param>
public void RemoveExecutedScript(SqlScript script)
{
connectionManager().ExecuteCommandsWithManagedConnection(dbCommandFactory =>
{
using (var command = dbCommandFactory())
{
command.CommandText = string.Format("delete from {0} where ScriptName = '{1}'", CreateTableName(schema, Table), script.Name);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
});
}
public void CreateTable(RelationModel relation)
{
var a = SQL.CreateTable.CreateTb(relation.TbName);
foreach (var c in relation.Columns)
{
a.AddColumn(c.ColumnName, c.TypeName, c.Size, c.ConstraintsStr);
}
SqlScript sql = a.ToSql();
DbServerProvider.ExcuteNonQuery(CommandType.Text, sql.ToString());
}
public void ComplainsAboutCycle()
{
var a = new SqlScript("a.sql", "contents of a #requires b");
var b = new SqlScript("b.sql", "contents of b #requires c");
var c = new SqlScript("c.sql", "contents of c #requires a");
var unsorted = new[] { a, b, c };
Action ex = () => unsorted.OrderByDependency(_prefix);
ex.Should().Throw <InvalidOperationException>();
}
/// <summary>
/// Records a database upgrade for a database specified in a given connection string.
/// </summary>
/// <param name="script">The script.</param>
public void StoreExecutedScript(SqlScript script)
{
var exists = DoesTableExist();
if (!exists)
{
log().WriteInformation(string.Format("Creating the {0} table", CreateTableName(schema, table)));
connectionManager().ExecuteCommandsWithManagedConnection(dbCommandFactory =>
{
using (var command = dbCommandFactory())
{
command.CommandText = CreateTableSql(schema, table);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
log().WriteInformation(string.Format("The {0} table has been created", CreateTableName(schema, table)));
});
}
connectionManager().ExecuteCommandsWithManagedConnection(dbCommandFactory =>
{
using (var command = dbCommandFactory())
{
command.CommandText = string.Format("insert into {0} (ScriptName, ScriptHash, ExecutedOn, ExecutedBy) values (@scriptName, @scriptHash, @executedOn, @executedBy)", CreateTableName(schema, table));
var scriptNameParam = command.CreateParameter();
scriptNameParam.ParameterName = "scriptName";
scriptNameParam.Value = script.Name;
command.Parameters.Add(scriptNameParam);
var scriptHashParam = command.CreateParameter();
scriptHashParam.ParameterName = "scriptHash";
scriptHashParam.Value = script.Hash;
command.Parameters.Add(scriptHashParam);
var executedOnParam = command.CreateParameter();
executedOnParam.ParameterName = "executedOn";
executedOnParam.Value = DateTime.Now;
command.Parameters.Add(executedOnParam);
var executedByParam = command.CreateParameter();
executedByParam.ParameterName = "executedBy";
executedByParam.Value = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
command.Parameters.Add(executedByParam);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
});
}
/// <summary>
/// Check if already executed part of script has changed.
/// </summary>
/// <param name="script">The script.</param>
/// <param name="successfullyExecutedStatements">Collection of already successfull executed statements of scipt</param>
/// <returns>Return true if already executed statements of scripts have not changed.</returns>
public bool ValidateExecutedScript(SqlScript script, IEnumerable <string> successfullyExecutedStatements)
{
if (successfullyExecutedStatements == null)
{
successfullyExecutedStatements = _connectionManager.SplitScriptIntoCommands(script.Contents);
}
var successfullHash = GetFailedStatementHash(script);
var scriptsHash = CalculateHash(successfullyExecutedStatements);
return(successfullHash == scriptsHash);
}
/// <summary>
/// Gets all scripts that should be executed.
/// </summary>
/// <returns></returns>
public IEnumerable <SqlScript> GetScripts(IConnectionManager connectionManager)
{
return(assemblies
.Select(assembly => new
{
Assembly = assembly,
ResourceNames = assembly.GetManifestResourceNames().Where(filter).ToArray()
})
.SelectMany(x => x.ResourceNames.Select(resourceName => SqlScript.FromStream(resourceName, x.Assembly.GetManifestResourceStream(resourceName), encoding, sqlScriptOptions)))
.OrderBy(sqlScript => sqlScript.Name)
.ToList());
}
public DatabaseUpgradeResult Run(SqlScript script, IEnumerable <SqlScript> before = null, IEnumerable <SqlScript> after = null, IEnumerable <SqlScript> afterErr = null, IJournal journal = null)
{
if (script == null)
{
throw new ArgumentNullException(nameof(script));
}
var builder = CreateBuilder(journal);
var result = Run(builder, script, before, after, afterErr);
return(result);
}
public void ComplainsAboutAmbiguous()
{
var b = new SqlScript("bear.sql", "contents of bear");
var d = new SqlScript("fear.sql", "contents of fear");
var c = new SqlScript("um.sql", "contents of um #requires ear");
var unsorted = new[] { c, b, d };
Action ex = () => unsorted.OrderByDependency(_prefix);
ex.Should().Throw <InvalidOperationException>();
}
public void Can_Parse_Version()
{
var path = "test_parse_version.sql";
var content = @"-- 3.2.34
SELECT * FROM test;";
File.WriteAllText(path, content);
var script = SqlScript.FromFile("test_script", path, useSpecifiedDatabase: true, useTransaction: true);
Assert.That(script.Version.CompareTo(new SqlVersion(3, 2, 34)) == 0);
}
private List <DomainKnowledge> GetDomainKnowledge(bool saveIncompleteRows = true)
{
List <int> incompleteRowIdx = new List <int>();
List <DomainKnowledge> listDk = new List <DomainKnowledge>();
foreach (DataGridViewRow row in dataGridViewEvents.Rows)
{
if (row.Index != dataGridViewEvents.NewRowIndex)
{
if ((saveIncompleteRows) || (bool.Parse(row.Cells[5].Value.ToString())))
{
string ev_id = null;
if (row.Cells[0].Value != DBNull.Value)
{
ev_id = (string)row.Cells[0].Value;
}
string ev_label = null;
if (row.Cells[1].Value != DBNull.Value)
{
ev_label = (string)row.Cells[1].Value;
}
Event ev = new Event(ev_id, ev_label);
RegulatoryRule rr = null;
if (row.Cells[2].Value != DBNull.Value)
{
rr = new RegulatoryRule((string)row.Cells[2].Value);
}
if (row.Cells[3].Value is byte[])
{
row.Cells[3].Value = ByteArrayToImage((byte[])row.Cells[3].Value);
}
Screenshot ss = null;
if (row.Cells[3].Value != DBNull.Value)
{
ss = new Screenshot((Bitmap)row.Cells[3].Value);
}
SqlScript sql = null;
if (row.Cells[4].Value != DBNull.Value)
{
sql = new SqlScript((string)row.Cells[4].Value);
}
listDk.Add(new DomainKnowledge(ss, rr, sql, ev));
}
else
{
incompleteRowIdx.Add(row.Index);
}
}
}
return(listDk);
}
protected IEnumerable <SqlScript> GetAllProviderScripts(string path)
{
var encoding = System.Text.Encoding.UTF8;
var sqlScriptOptions = new SqlScriptOptions();
var files = GetFiles(path, true);
return(files.Select(x =>
{
var s = SqlScript.FromFile(path, x.FullName, encoding, sqlScriptOptions);
return new SqlScript(x.Name, s.Contents, s.SqlScriptOptions);
}));
}
/// <summary>
/// 读取模型实例列表。
/// </summary>
/// <param name="sql">SQL语句。</param>
/// <param name="cancellationToken">取消标记。</param>
/// <returns>返回模型实例列表。</returns>
protected async Task <IEnumerable <TModel> > LoadSqlAsync(SqlScript sql, CancellationToken cancellationToken = new CancellationToken())
{
var models = new List <TModel>();
using (var reader = await ExecuteReaderAsync(sql.ToString(), sql.Parameters, cancellationToken: cancellationToken))
{
while (await reader.ReadAsync(cancellationToken))
{
models.Add(Entity.Read <TModel>(reader));
}
}
return(models);
}
public virtual void StoreExecutedScript(SqlScript script, Func <IDbCommand> db)
{
var name = NameWithHash.FromScript(script);
db.Execute(GetDeleteScriptSql(), new { scriptName = name.PlainName });
db.Execute(GetInsertScriptSql(),
new
{
scriptName = name.PlainName,
contentsHash = name.ContentsHash
});
}
public void ShouldScriptDropTable()
{
SetupResult.For(templateManager.CreateTable).Return(@"DropTable.vm");
mocks.ReplayAll();
SqlScript script = scriptBuilder.Create(table);
string sql = script.ToScript();
Console.WriteLine(sql);
Assert.IsTrue(sql.Contains("IF EXISTS"), "Missing IF EXISTS");
Assert.IsTrue(sql.Contains("DROP TABLE [dbo].[Customer]"));
}
/// <summary>
/// 读取模型实例列表。
/// </summary>
/// <param name="sql">SQL语句。</param>
/// <returns>返回模型实例列表。</returns>
protected IEnumerable <TModel> LoadSql(SqlScript sql)
{
var models = new List <TModel>();
using (var reader = ExecuteReader(sql.ToString(), sql.Parameters))
{
while (reader.Read())
{
models.Add(Entity.Read <TModel>(reader));
}
}
return(models);
}
/// <summary>
/// Initializes a new instance of the <see cref="MySqlScriptDialog"/> class.
/// </summary>
/// <param name="wbConnection">The connection to a MySQL server instance selected by users.</param>
/// <param name="sqlScript">The proposed SQL query for user review and possible modification.</param>
/// <param name="operationsInfoText">The text showing original operations information reflected on the SQL script.</param>
/// <param name="useOptimisticUpdate">Flag indicating whether optimistic locking is used for the update of rows.</param>
public MySqlScriptDialog(MySqlWorkbenchConnection wbConnection, string sqlScript, string operationsInfoText, bool useOptimisticUpdate = false)
: this()
{
_errorDialogSummary = Resources.ScriptErrorThrownSummary;
_operationsInfoText = operationsInfoText;
_showOriginalOperationsInformation = !string.IsNullOrEmpty(_operationsInfoText);
_useOptimisticUpdate = useOptimisticUpdate;
_wbConnection = wbConnection;
OriginalSqlScript = sqlScript;
SqlScript = OriginalSqlScript;
CreateOriginalStatementsList();
ApplyButton.Enabled = SqlScript.Trim().Length > 0;
SetOriginalOperationsInfoAvailability();
}
protected override void ExecuteCommandsWithinExceptionHandler(int index, SqlScript script, Action executeCommand)
{
try
{
executeCommand();
}
catch (SnowflakeDbException snowflakeException)
{
Log().WriteInformation("Snowflake exception has occured in script: '{0}'", script.Name);
Log().WriteError("Script block number: {0}; Error code {1}; Message: {2}", index, snowflakeException.ErrorCode, snowflakeException.Message);
Log().WriteError(snowflakeException.ToString());
throw;
}
}
protected override void ExecuteCommandsWithinExceptionHandler(int index, SqlScript script, Action executeCommand)
{
try
{
executeCommand();
}
catch (SqlException sqlException)
{
Log().WriteInformation("SQL exception has occured in script: '{0}'", script.Name);
Log().WriteError("Script block number: {0}; Block line {1}; Procedure {2}; Number {3}; Message: {4}", index, sqlException.LineNumber, sqlException.Procedure, sqlException.Number, sqlException.Message);
Log().WriteError(sqlException.ToString());
throw;
}
}
protected override void ExecuteCommandsWithinExceptionHandler(int index, SqlScript script, Action excuteCommand)
{
try
{
excuteCommand();
}
catch (Sap.Data.SQLAnywhere.SAException sqlException)
{
Log().WriteInformation("SQLAnywhere exception has occured in script: '{0}'", script.Name);
Log().WriteError("Script block number: {0}; Message: {1}", index, sqlException.Message);
Log().WriteError(sqlException.ToString());
throw;
}
}
protected override void ExecuteCommandsWithinExceptionHandler(int index, SqlScript script, Action executeCommand)
{
try
{
executeCommand();
}
catch (SqlCeException exception)
{
Log().WriteInformation("SqlCe exception has occured in script: '{0}'", script.Name);
Log().WriteError("Script block number: {0}; Native Error: {1}; Message: {2}", index, exception.NativeError, exception.Message);
Log().WriteError(exception.ToString());
throw;
}
}
protected override void ExecuteCommandsWithinExceptionHandler(int index, SqlScript script, Action excuteCommand)
{
try
{
excuteCommand();
}
catch (FbException fbException)
{
Log().WriteInformation("Firebird exception has occured in script: '{0}'", script.Name);
Log().WriteError("Firebird error code: {0}; SQLSTATE {1}; Message: {2}", index, fbException.ErrorCode, fbException.SQLSTATE, fbException.Message);
Log().WriteError(fbException.ToString());
throw;
}
}
public long AddSqlScript(SqlScriptSaveModel sqlScript)
{
var model = new SqlScript
{
CategoryId = sqlScript.CategoryId,
Name = sqlScript.Name,
SqlScriptText = JsonConvert.SerializeObject(sqlScript.SqlRequest),
Parameters = JsonConvert.SerializeObject(sqlScript.Parameters)
};
Context.SqlScripts.Add(model);
Context.SaveChanges();
return(model.Id);
}
public void RunScript_Test()
{
var opts = new SqlScriptOptions {
RunGroupOrder = 10, ScriptType = global::DbUp.Support.ScriptType.RunOnce
};
SqlScript script = new SqlScript("test_script_1", "SELECT GETDATE()", opts);
IEnumerable <SqlScript> before = null;
IEnumerable <SqlScript> after = null;
IEnumerable <SqlScript> afterErr = null;
var result = _exec.Run(script, before, after, afterErr);
_connection.Verify(x => x.ExecuteCommandsWithManagedConnection(It.IsAny <Action <Func <IDbCommand> > >()), Times.Exactly(1));
}
/// <summary>
/// 获取Config_Grid的某配置项的脚本
/// </summary>
public void GetGridConfigScript()
{
string script = SqlScript.GetGridConfigScript(ObjName);
bool result = !string.IsNullOrEmpty(script);
if (result)
{
WebHelper.SendFile("sys_Grid配置_" + KeyValueConfig.GetTableDescription(ObjName, TableName) + ".sql", script);
}
else
{
jsonResult = JsonHelper.OutResult(result, script);
}
}
public void SortsByDependencies()
{
var a = new SqlScript("a.sql", "contents of a #requires b");
var b = new SqlScript("b.sql", "contents of b");
var x = new SqlScript("x.sql", "-- #requires b \r\n contents of x");
var y = new SqlScript("y.sql", "contents of y #requires a");
var z = new SqlScript("z.sql", "contents of z");
var unsorted = new[] { y, x, z, a, b };
var sorted = unsorted.OrderByDependency(_prefix).ToList();
sorted.Should().ContainInOrder(b, a, y, x, z);
}
private void RemoveExpiredMessages()
{
int expiration = PortalConfig.MdsDeleteOlderMoreThan;
if (expiration > 0)
{
SqlScript sqlScript = new SqlScript();
sqlScript.AppendLine("DELETE FROM [dbo].[cls_IbnClientMessage] WHERE Created < @ExpirationDate");
sqlScript.AddParameter("@ExpirationDate", DateTime.UtcNow.AddMinutes(-expiration));
sqlScript.Execute();
}
}
public void CanCreateSqlScript()
{
// arrange
var version = 4;
var description = "description";
var sql = "PRINT 'test';";
// act
var sqlScript = new SqlScript(version, description, sql);
// assert
sqlScript.Version.Should().Be(version);
sqlScript.Description.Should().Be(description);
sqlScript.GetSqlBatches("$database", "dbo").Should().ContainSingle(batch => batch == sql);
sqlScript.SupportedInTransaction.Should().BeTrue();
}
public void CanCreateSqlScriptThatIsNotSupportedInTransaction()
{
// arrange
var version = 1;
var description = "description";
var sql = @"ALTER DATABASE [$database] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;";
// act
var sqlScript = new SqlScript(version, description, sql);
// assert
sqlScript.Version.Should().Be(version);
sqlScript.Description.Should().Be(description);
sqlScript.GetSqlBatches("$database", "dbo").Should().ContainSingle(batch => batch == sql);
sqlScript.SupportedInTransaction.Should().BeFalse();
}
public void CanCreateSqlScriptWithSchemaCreation()
{
// arrange
var version = 1;
var description = "description";
var sql = "PRINT 'test';";
// act
var sqlScript = new SqlScript(version, description, sql);
// assert
sqlScript.Version.Should().Be(version);
sqlScript.Description.Should().Be(description);
sqlScript.GetSqlBatches("$database", "dbo").Should().ContainInOrder(
@"IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'dbo')
EXEC sp_executesql N'CREATE SCHEMA [dbo];';",
sql);
sqlScript.SupportedInTransaction.Should().BeTrue();
}
protected override void CopyEntityObjectToMetaObject(EntityObject target, MetaObject metaObject)
{
// Base Copy
base.CopyEntityObjectToMetaObject(target, metaObject);
// Process not updatable field
DocumentContentVersionEntity srcVersion = (DocumentContentVersionEntity)target;
#region Index
// Only if new object
if (metaObject.ObjectState == MetaObjectState.Created)
{
// Calculate max index
int maxIndex = 0;
SqlScript selectMaxIndex = new SqlScript();
selectMaxIndex.Append("SELECT MAX([Index]) AS [Index] FROM [dbo].[cls_DocumentContentVersion] WHERE [OwnerDocumentId] = @OwnerDocumentId");
selectMaxIndex.AddParameter("@OwnerDocumentId", (Guid)srcVersion.OwnerDocumentId);
using (IDataReader reader = SqlHelper.ExecuteReader(SqlContext.Current,
CommandType.Text, selectMaxIndex.ToString(), selectMaxIndex.Parameters.ToArray()))
{
if (reader.Read())
{
object value = reader["Index"];
if (value is int)
{
maxIndex = (int)value;
}
}
}
// update index
metaObject["Index"] = maxIndex + 1;
}
#endregion
// Update State via Custom Method = UpdateState
}
public void Add(SqlScript item)
{
if (list == null) list = new List<SqlScript>();
if (item != null) list.Add(item);
}
/// <summary>
/// Records a database upgrade for a database specified in a given connection string.
/// </summary>
/// <param name="script">The script.</param>
public void StoreExecutedScript(SqlScript script)
{
var exists = DoesTableExist();
if (!exists)
{
log.WriteInformation(string.Format("Creating the {0} table", schemaTableName));
RunCommand(
string.Format(
@"create table {0} (
[SchemaVersionId] int identity(1,1) not null constraint PK_SchemaVersions_Id primary key nonclustered ,
[VersionNumber] int null,
[SourceIdentifier] nvarchar(255) not null,
[ScriptName] nvarchar(255) not null,
[Applied] datetime not null
)", schemaTableName),
cmd => cmd.ExecuteNonQuery());
log.WriteInformation(string.Format("The {0} table has been created", schemaTableName));
}
DealWithLegacyScripts();
RunCommand(
string.Format("insert into {0} (VersionNumber, SourceIdentifier, ScriptName, Applied) values (-1, @sourceIdentifier, @scriptName, (getutcdate()))", schemaTableName),
cmd =>
{
cmd.Parameters.AddWithValue("scriptName", script.Name);
cmd.Parameters.AddWithValue("sourceIdentifier", sourceIdentifier);
cmd.ExecuteNonQuery();
});
}
