/// <summary>
/// Gets a list of all applied versions.
/// </summary>
private List <ExecutedFile> GetExecutedFiles()
{
var sql = SQLTemplates.GetAppliedVersions();
var files = this.ExecuteCommand <ExecutedFile>(sql);
return(files);
}
/// <summary>
/// Sets the database to an empty state (Version 0).
/// </summary>
public void Reset()
{
var managementDatabase = new Database(this.connectionManager, this.fileSystemAccess, this.processManager)
{
Name = PostgresDefaultDatabaseName
};
try
{
// disconnect old database and delete it afterwards
managementDatabase.ExecuteCommandNonQuery(SQLTemplates.DisconnectDatabase(this.Name));
managementDatabase.ExecuteCommandNonQuery(SQLTemplates.DropDatabase(this.Name));
managementDatabase.ExecuteCommandNonQuery(SQLTemplates.CreateDatabase(this.Name));
// clear the old pool so no connection is reused!
this.connectionManager.ClearPools();
this.CreateTeamworkSchema();
this.UpdateVersion();
}
catch (NpgsqlException ex)
{
Log.Error(ex.Message, ex);
throw new TeamworkParserException($"Error while resetting database. {ex.Message}");
}
}
/// <summary>
/// Removes the entries in executed statements.
/// </summary>
/// <remarks>IMPORTANT: This will NOT execute any undo diff files.</remarks>
public void ReduceVersion()
{
// ensure that the version is
this.UpdateVersion();
var sql = SQLTemplates.RemoveVersion(this.CurrentVersion);
this.ExecuteCommandNonQuery(sql);
this.UpdateVersion();
}
/// <summary>
/// Creates the schema for the teamwork including a table for all executed files.
/// </summary>
public void CreateTeamworkSchema()
{
// check if schema exists
var schemas = this.ExecuteCommand <int>(SQLTemplates.GetTeamworkSchemaSQL(this.Name))[0];
var schemaExists = schemas > 0;
// create schema and table if not already there
if (!schemaExists)
{
this.ExecuteCommandNonQuery(SQLTemplates.CreateTeamworkSchemaSQL());
}
}
public void MarkAsExecutedTest()
{
var databaseMock = new Mock <IDatabase>();
var fileMock = new Mock <IFileSystemAccess>();
fileMock.Setup(f => f.FileExists(It.IsAny <string>())).Returns(true);
var file = new SQLFile("\\0001.diff.sql", databaseMock.Object, fileMock.Object);
databaseMock.Setup(c => c.ExecuteCommandNonQuery(SQLTemplates.AddExecutedFileSql(new DatabaseVersion("\\Test\\0001" + SQLTemplates.DiffFile), FileType.Diff, "Exported from this database"))).Verifiable();
file.MarkAsExecuted();
databaseMock.VerifyAll();
}
private void Disconnect()
{
this.ExecuteInTask(() =>
{
try
{
var name = DatabaseSetting.GetDatabaseSetting(this.Id).Name;
this.connectionManager.ExecuteCommandNonQuery(SQLTemplates.DisconnectDatabase(name));
}
catch (Exception ex)
{
Log.Error("Exception while executing Action in Task", ex);
}
});
}
/// <summary>
/// Executes all statements in an transaction.
/// </summary>
/// <exception cref="TeamworkConnectionException">Is thrown when an error occurred while executing the SQL Statements.</exception>
public void ExecuteInTransaction()
{
try
{
// execute statements which dont support transaction at the beginning
foreach (var statement in this.SQLStatements.Where(s => !s.SupportsTransaction && !s.IsTeamworkSchema))
{
statement.Execute();
}
var sb = new StringBuilder();
foreach (var statement in this.SQLStatements.Where(s => s.SupportsTransaction && !s.IsTeamworkSchema))
{
sb.AppendLine(statement.SQL);
}
// execute other statements in transaction
var sql = sb.ToString();
this.database.ExecuteCommandNonQuery(sql);
if (this.FileType == FileType.UndoDiff)
{
this.database.ExecuteCommandNonQuery(SQLTemplates.RemoveVersion(this.Version));
}
this.database.ExecuteCommandNonQuery(SQLTemplates.AddExecutedFileSql(this.Version, this.FileType));
}
catch (NpgsqlException ex)
{
try
{
this.database.ExecuteCommandNonQuery(SQLTemplates.AddExecutionHistorySql(this.Version, this.FileType, ex.Message));
}
catch (Exception innerEx)
{
Log.Warn("Error while inserting execution history", innerEx);
}
Log.Warn(string.Format("File {0} contains errors", this.FileName));
throw new TeamworkConnectionException(this, ex.Message, ex);
}
Log.Info(string.Format("File {0} executed successfully", this.FileName));
}
private void TestSQLFiles(int progressStart, int progressEnd)
{
var databaseName = $"APE.PostgreSQL.Teamwork.Test.{this.Name}";
var progressDifference = progressEnd - progressStart;
var progressStepSmall = (double)progressDifference / 100 * 5; // 5 %
var progressStepBig = (double)progressDifference / 100 * 40; // 40 %
try
{
this.SetProgress(progressStart, "Testing the new files");
// create temp database
this.ExecuteCommandNonQuery(SQLTemplates.CreateDatabase(databaseName));
this.SetProgress(this.Progress + progressStepSmall);
var managementDatabase = new Database(databaseName, this.Path, this.IgnoredSchemas.ToList(), this.connectionManager, this.fileSystemAccess, this.processManager, this.differenceCreator, this.sqlFileTester, true);
managementDatabase.UpdateData();
var updateProgress = new Action <IEnumerable <SQLFile>, SQLFile>((files, file) =>
{
// todo db file tester implement
////this.sqlFileTester.CreateData(managementDatabase, file);
////this.sqlFileTester.TestEmptyMethods(managementDatabase, file);
this.SetProgress(this.Progress + (progressStepBig / files.Count()), string.Format("Testing File {0}", file.FileName));
});
// execute all statements
managementDatabase.UpdateToVersion(managementDatabase.LastApplicableVersion, updateProgress);
if (managementDatabase.UndoDiffFiles.Count > 0)
{
managementDatabase.UpdateToVersion(managementDatabase.UndoDiffFiles.Last().Version, updateProgress);
}
}
finally
{
this.SetProgress(this.Progress + progressStepSmall, "Disconnecting Database");
this.ExecuteCommandNonQuery(SQLTemplates.DisconnectDatabase(databaseName));
this.SetProgress(this.Progress + progressStepSmall, "Dropping old Database");
this.ExecuteCommandNonQuery(SQLTemplates.DropDatabase(databaseName));
this.SetProgress(progressEnd);
}
}
partial void AddDatabaseViewModelCtor()
{
this.InitializeCommands();
this.ExecuteInTask(this.SearchDatabaseDirectories, (exec) => this.Loading = exec);
this.Databases = this.connectionManager.ExecuteCommand <string>(SQLTemplates.GetAllTables());
this.Databases.Remove(SettingsManager.Get().Setting.Id);
// remove databases which are already added
foreach (var db in DatabaseSetting.GetDatabaseSettings())
{
this.Databases.Remove(db.Name);
}
if (this.Databases.Count == 1)
{
this.DatabaseName = this.Databases.Single();
}
}
private void CreateDatabase()
{
new Task(() =>
{
try
{
var name = DatabaseSetting.GetDatabaseSetting(this.Id).Name;
this.connectionManager.ExecuteCommandNonQuery(SQLTemplates.CreateDatabase(name));
// connect to database and update default data
this.ConnectDatabase();
this.UpdateData(); // do not remove this, else the edit mode will be finished when something else calls this
this.StartImport();
}
catch (Exception ex)
{
Log.Error("Exception while executing Action in Task", ex);
}
}).Start();
}
public void ResetTest()
{
var name = "TestDB";
var path = "testpfad";
var fileMock = new Mock <IFileSystemAccess>();
var connectionManagerMock = new Mock <IConnectionManager>();
var processMock = new Mock <IProcessManager>();
var sqlFileMock = new Mock <ISQLFile>();
var diffCreatorMock = new Mock <IDifferenceCreator>();
var sqlFileTester = new Mock <ISQLFileTester>();
connectionManagerMock.Setup(c => c.ExecuteCommand <int>(It.IsAny <IDatabase>(), It.IsAny <string>()))
.Returns(new List <int>()
{
0
})
.Verifiable();
connectionManagerMock.Setup(c => c.ExecuteCommand <ExecutedFile>(It.IsAny <IDatabase>(), It.IsAny <string>()))
.Returns(new List <ExecutedFile>())
.Verifiable();
// UpdateData is called internally when bool is set to true
var database = new Database(name, path, new List <string>(), connectionManagerMock.Object, fileMock.Object, processMock.Object, diffCreatorMock.Object, sqlFileTester.Object, initializeData: false);
connectionManagerMock.Setup(c => c.ExecuteCommandNonQuery(It.IsAny <IDatabase>(), SQLTemplates.CreateDatabase(name)));
database.Reset();
connectionManagerMock.VerifyAll();
}
/// <summary>
/// 加载模版.
/// </summary>
/// <returns></returns>
public string SettingTemplate_Init()
{
//类型.
string templateType = this.GetRequestVal("TemplateType");
string condType = this.GetRequestVal("CondType");
BP.WF.Template.SQLTemplates sqls = new SQLTemplates();
//sqls.Retrieve(BP.WF.Template.SQLTemplateAttr.SQLType, sqlType);
DataTable dt = null;
string sql = "";
#region 节点方向条件模版.
if (templateType == "CondBySQL")
{
/*方向条件, 节点方向条件.*/
sql = "SELECT MyPK,Note,OperatorValue FROM WF_Cond WHERE CondType=" + condType + " AND DataFrom=" + (int)ConnDataFrom.SQL;
}
if (templateType == "CondByUrl")
{
/*方向条件, 节点方向url条件.*/
sql = "SELECT MyPK,Note,OperatorValue FROM WF_Cond WHERE CondType=" + condType + " AND DataFrom=" + (int)ConnDataFrom.Url;
}
if (templateType == "CondByPara")
{
/*方向条件, 节点方向url条件.*/
sql = "SELECT MyPK,Note,OperatorValue FROM WF_Cond WHERE CondType=" + condType + " AND DataFrom=" + (int)ConnDataFrom.Paras;
}
#endregion 节点方向条件模版.
#region 表单扩展设置.
string add = "+";
if (SystemConfig.AppCenterDBType == DBType.Oracle)
{
add = "||";
}
if (templateType == "DDLFullCtrl")
{
sql = "SELECT MyPK, '下拉框:'" + add + " a.AttrOfOper as Name,Doc FROM Sys_MapExt a WHERE ExtType='DDLFullCtrl'";
}
if (templateType == "ActiveDDL")
{
sql = "SELECT MyPK, '下拉框:'" + add + " a.AttrOfOper as Name,Doc FROM Sys_MapExt a WHERE ExtType='ActiveDDL'";
}
//显示过滤.
if (templateType == "AutoFullDLL")
{
sql = "SELECT MyPK, '下拉框:'" + add + " a.AttrOfOper as Name,Doc FROM Sys_MapExt a WHERE ExtType='AutoFullDLL'";
}
//文本框自动填充..
if (templateType == "TBFullCtrl")
{
sql = "SELECT MyPK, '文本框:'" + add + " a.AttrOfOper as Name,Doc FROM Sys_MapExt a WHERE ExtType='TBFullCtrl'";
}
//自动计算.
if (templateType == "AutoFull")
{
sql = "SELECT MyPK, 'ID:'" + add + " a.AttrOfOper as Name,Doc FROM Sys_MapExt a WHERE ExtType='AutoFull'";
}
#endregion 表单扩展设置.
#region 节点属性的模版.
//自动计算.
if (templateType == "NodeAccepterRole")
{
sql = "SELECT NodeID, FlowName +' - '+Name, a.DeliveryParas as Docs FROM WF_Node a WHERE a.DeliveryWay=" + (int)DeliveryWay.BySQL;
}
#endregion 节点属性的模版.
if (sql == "")
{
return("err@没有涉及到的标记[" + templateType + "].");
}
dt = DBAccess.RunSQLReturnTable(sql);
string strs = "";
foreach (DataRow dr in dt.Rows)
{
BP.WF.Template.SQLTemplate en = new SQLTemplate();
en.No = dr[0].ToString();
en.Name = dr[1].ToString();
en.Docs = dr[2].ToString();
if (strs.Contains(en.Docs.Trim() + ";") == true)
{
continue;
}
strs += en.Docs.Trim() + ";";
sqls.AddEntity(en);
}
return(sqls.ToJson());
}
public void MarkAsExecuted()
{
this.database.ExecuteCommandNonQuery(SQLTemplates.AddExecutedFileSql(this.Version, this.FileType, "Exported from this database"));
this.database.UpdateData();
}
