/// <summary>
/// export the sequences
/// </summary>
/// <param name="ARootNode"></param>
/// <param name="AReadTransaction">Instantiated DB Transaction.</param>
private static void ExportSequences(XmlNode ARootNode, TDBTransaction AReadTransaction)
{
XmlElement sequencesNode = ARootNode.OwnerDocument.CreateElement("Sequences");
ARootNode.AppendChild(sequencesNode);
foreach (string seq in TTableList.GetDBSequenceNames())
{
XmlElement sequenceNode = ARootNode.OwnerDocument.CreateElement(StringHelper.UpperCamelCase(seq, false, false));
sequencesNode.AppendChild(sequenceNode);
sequenceNode.SetAttribute("value", AReadTransaction.DataBaseObj.GetCurrentSequenceValue(seq, AReadTransaction).ToString());
}
}
/// <summary>
/// export the sequences
/// </summary>
/// <param name="ARootNode"></param>
private static void ExportSequences(XmlNode ARootNode)
{
XmlElement sequencesNode = ARootNode.OwnerDocument.CreateElement("Sequences");
ARootNode.AppendChild(sequencesNode);
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction();
foreach (string seq in TTableList.GetDBSequenceNames())
{
XmlElement sequenceNode = ARootNode.OwnerDocument.CreateElement(StringHelper.UpperCamelCase(seq, false, false));
sequencesNode.AppendChild(sequenceNode);
sequenceNode.SetAttribute("value", DBAccess.GDBAccessObj.GetCurrentSequenceValue(seq, Transaction).ToString());
}
DBAccess.GDBAccessObj.RollbackTransaction();
}
/// <summary>
/// export the sequences
/// </summary>
/// <param name="ARootNode"></param>
private static void ExportSequences(XmlNode ARootNode)
{
XmlElement sequencesNode = ARootNode.OwnerDocument.CreateElement("Sequences");
TDBTransaction ReadTransaction = null;
ARootNode.AppendChild(sequencesNode);
DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref ReadTransaction,
delegate
{
foreach (string seq in TTableList.GetDBSequenceNames())
{
XmlElement sequenceNode = ARootNode.OwnerDocument.CreateElement(StringHelper.UpperCamelCase(seq, false, false));
sequencesNode.AppendChild(sequenceNode);
sequenceNode.SetAttribute("value", DBAccess.GDBAccessObj.GetCurrentSequenceValue(seq, ReadTransaction).ToString());
}
});
}
public static bool ResetDatabase(string AZippedNewDatabaseData)
{
List <string> tables = TTableList.GetDBNames();
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(),
Catalog.GetString("Importing database"),
tables.Count + 3);
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
try
{
tables.Reverse();
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("deleting current data"),
0);
foreach (string table in tables)
{
DBAccess.GDBAccessObj.ExecuteNonQuery("DELETE FROM pub_" + table, Transaction);
}
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
return(false);
}
TSimpleYmlParser ymlParser = new TSimpleYmlParser(PackTools.UnzipString(AZippedNewDatabaseData));
ymlParser.ParseCaptions();
tables.Reverse();
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("loading initial tables"),
1);
// one transaction to import the user table and user permissions. otherwise logging in will not be possible if other import fails?
bool success = true;
success = success && LoadTable("s_user", ymlParser, Transaction);
success = success && LoadTable("s_module", ymlParser, Transaction);
success = success && LoadTable("s_user_module_access_permission", ymlParser, Transaction);
success = success && LoadTable("s_system_defaults", ymlParser, Transaction);
success = success && LoadTable("s_system_status", ymlParser, Transaction);
// make sure we have the correct database version
TFileVersionInfo serverExeInfo = new TFileVersionInfo(TSrvSetting.ApplicationVersion);
DBAccess.GDBAccessObj.ExecuteNonQuery(String.Format(
"UPDATE PUB_s_system_defaults SET s_default_value_c = '{0}' WHERE s_default_code_c = 'CurrentDatabaseVersion'",
serverExeInfo.ToString()), Transaction);
if (!success)
{
DBAccess.GDBAccessObj.RollbackTransaction();
return(false);
}
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
return(false);
}
DBAccess.GDBAccessObj.CommitTransaction();
tables.Remove("s_user");
tables.Remove("s_module");
tables.Remove("s_user_module_access_permission");
tables.Remove("s_system_defaults");
tables.Remove("s_system_status");
FCurrencyPerLedger = new SortedList <int, string>();
Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
int tableCounter = 2;
foreach (string table in tables)
{
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
String.Format(Catalog.GetString("loading table {0}"), table),
tableCounter);
tableCounter++;
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
DBAccess.GDBAccessObj.RollbackTransaction();
return(false);
}
LoadTable(table, ymlParser, Transaction);
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("loading sequences"),
tables.Count + 5 + 3);
// set sequences appropriately, not lagging behind the imported data
foreach (string seq in TTableList.GetDBSequenceNames())
{
LoadSequence(seq, ymlParser, Transaction);
}
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("finish import"),
tables.Count + 5 + 4);
DBAccess.GDBAccessObj.CommitTransaction();
// reset all cached tables
TCacheableTablesManager.GCacheableTablesManager.MarkAllCachedTableNeedsRefreshing();
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
}
catch (Exception e)
{
TLogging.Log("Problem in ResetDatabase: " + e.Message);
TLogging.Log(e.StackTrace);
DBAccess.GDBAccessObj.RollbackTransaction();
return(false);
}
return(true);
}
public static bool ResetDatabase(string AZippedNewDatabaseData)
{
List <string> tables = TTableList.GetDBNames();
bool SubmissionResult = false;
TDBTransaction Transaction = null;
string ClientID = "ClientID";
try
{
ClientID = DomainManager.GClientID.ToString();
}
catch (Exception)
{
}
TProgressTracker.InitProgressTracker(ClientID,
Catalog.GetString("Restoring Database..."),
tables.Count + 3);
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction,
ref SubmissionResult,
delegate
{
try
{
tables.Reverse();
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Deleting current data..."),
0);
foreach (string table in tables)
{
DBAccess.GDBAccessObj.ExecuteNonQuery("DELETE FROM pub_" + table, Transaction);
}
if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
{
TProgressTracker.FinishJob(ClientID);
// As SubmissionResult is still false, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
TSimpleYmlParser ymlParser = new TSimpleYmlParser(PackTools.UnzipString(AZippedNewDatabaseData));
ymlParser.ParseCaptions();
tables.Reverse();
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Loading initial tables..."),
1);
// one transaction to import the user table and user permissions. otherwise logging in will not be possible if other import fails?
bool success = true;
success = success && LoadTable("s_user", ymlParser, Transaction);
success = success && LoadTable("s_module", ymlParser, Transaction);
success = success && LoadTable("s_user_module_access_permission", ymlParser, Transaction);
success = success && LoadTable("s_system_defaults", ymlParser, Transaction);
success = success && LoadTable("s_system_status", ymlParser, Transaction);
// make sure we have the correct database version
TFileVersionInfo serverExeInfo = new TFileVersionInfo(TSrvSetting.ApplicationVersion);
DBAccess.GDBAccessObj.ExecuteNonQuery(String.Format(
"UPDATE PUB_s_system_defaults SET s_default_value_c = '{0}' WHERE s_default_code_c = 'CurrentDatabaseVersion'",
serverExeInfo.ToString()), Transaction);
if (!success)
{
// As SubmissionResult is still TSubmitChangesResult.scrError, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
{
TProgressTracker.FinishJob(ClientID);
// As SubmissionResult is still false, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
tables.Remove("s_user");
tables.Remove("s_module");
tables.Remove("s_user_module_access_permission");
tables.Remove("s_system_defaults");
tables.Remove("s_system_status");
FCurrencyPerLedger = new SortedList <int, string>();
int tableCounter = 2;
foreach (string table in tables)
{
TProgressTracker.SetCurrentState(ClientID,
String.Format(Catalog.GetString("Loading Table {0}..."), table),
tableCounter);
tableCounter++;
if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
{
TProgressTracker.FinishJob(ClientID);
// As SubmissionResult is still false, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
LoadTable(table, ymlParser, Transaction);
}
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Loading Sequences..."),
tables.Count + 5 + 3);
// set sequences appropriately, not lagging behind the imported data
foreach (string seq in TTableList.GetDBSequenceNames())
{
LoadSequence(seq, ymlParser, Transaction);
}
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Finishing Restore..."),
tables.Count + 5 + 4);
SubmissionResult = true;
// reset all cached tables
TCacheableTablesManager.GCacheableTablesManager.MarkAllCachedTableNeedsRefreshing();
TProgressTracker.FinishJob(ClientID);
}
catch (Exception e)
{
TLogging.Log("Problem in ResetDatabase: " + e.ToString());
TLogging.LogStackTrace(TLoggingType.ToLogfile);
throw;
}
});
return(SubmissionResult);
}
internal virtual string remove(TCustomSqlStatement stat, LinkedHashMap <string, string> conditionMap)
{
if (stat.ResultColumnList != null)
{
for (int j = 0; j < stat.ResultColumnList.size(); j++)
{
TResultColumn column = stat.ResultColumnList.getResultColumn(j);
if (column.Expr != null && column.Expr.SubQuery is TCustomSqlStatement)
{
TCustomSqlStatement query = (TCustomSqlStatement)column.Expr.SubQuery;
getParserString(query, conditionMap);
}
}
}
if (stat.CteList != null)
{
for (int i = 0; i < stat.CteList.size(); i++)
{
TCTE cte = stat.CteList.getCTE(i);
if (cte.Subquery != null)
{
getParserString(cte.Subquery, conditionMap);
}
if (cte.InsertStmt != null)
{
getParserString(cte.InsertStmt, conditionMap);
}
if (cte.UpdateStmt != null)
{
getParserString(cte.UpdateStmt, conditionMap);
}
if (cte.PreparableStmt != null)
{
getParserString(cte.PreparableStmt, conditionMap);
}
if (cte.DeleteStmt != null)
{
getParserString(cte.DeleteStmt, conditionMap);
}
}
}
if (stat is TSelectSqlStatement && ((TSelectSqlStatement)stat).SetOperator != TSelectSqlStatement.setOperator_none)
{
TSelectSqlStatement select = ((TSelectSqlStatement)stat);
getParserString(select.LeftStmt, conditionMap);
getParserString(select.RightStmt, conditionMap);
return(select.ToScript());
}
if (stat.Statements != null && stat.Statements.size() > 0)
{
for (int i = 0; i < stat.Statements.size(); i++)
{
getParserString(stat.Statements.get(i), conditionMap);
}
}
if (stat.ReturningClause != null)
{
if (stat.ReturningClause.ColumnValueList != null)
{
for (int i = 0; i < stat.ReturningClause.ColumnValueList.size(); i++)
{
if (stat.ReturningClause.ColumnValueList.getExpression(i).SubQuery != null)
{
getParserString(stat.ReturningClause.ColumnValueList.getExpression(i).SubQuery, conditionMap);
}
}
}
if (stat.ReturningClause.VariableList != null)
{
for (int i = 0; i < stat.ReturningClause.VariableList.size(); i++)
{
if (stat.ReturningClause.VariableList.getExpression(i).SubQuery != null)
{
getParserString(stat.ReturningClause.VariableList.getExpression(i).SubQuery, conditionMap);
}
}
}
}
if (stat is TSelectSqlStatement)
{
TTableList list = ((TSelectSqlStatement)stat).tables;
for (int i = 0; i < list.size(); i++)
{
TTable table = list.getTable(i);
if (table.Subquery != null)
{
getParserString(table.Subquery, conditionMap);
}
if (table.FuncCall != null)
{
ExpressionChecker w = new ExpressionChecker(this);
w.checkFunctionCall(table.FuncCall, conditionMap);
}
}
}
if (stat is TSelectSqlStatement)
{
TJoinList list = ((TSelectSqlStatement)stat).joins;
for (int i = 0; i < list.size(); i++)
{
TJoin join = list.getJoin(i);
for (int j = 0; j < join.JoinItems.size(); j++)
{
TJoinItem joinItem = join.JoinItems.getJoinItem(j);
if (joinItem.Table != null)
{
if (joinItem.Table.Subquery != null)
{
getParserString(joinItem.Table.Subquery, conditionMap);
}
if (joinItem.Table.FuncCall != null)
{
ExpressionChecker w = new ExpressionChecker(this);
w.checkFunctionCall(joinItem.Table.FuncCall, conditionMap);
}
if (joinItem.OnCondition != null)
{
ExpressionChecker w = new ExpressionChecker(this);
w.checkExpression(joinItem.OnCondition, conditionMap);
}
}
}
}
}
if (stat is TSelectSqlStatement)
{
TSelectSqlStatement select = (TSelectSqlStatement)stat;
for (int i = 0; i < select.ResultColumnList.size(); i++)
{
TResultColumn field = select.ResultColumnList.getResultColumn(i);
TExpression expr = field.Expr;
if (expr != null && expr.ExpressionType == EExpressionType.subquery_t)
{
getParserString(expr.SubQuery, conditionMap);
}
}
}
if (stat.WhereClause != null && stat.WhereClause.Condition != null && stat.WhereClause.Condition.ToScript().Trim().Length > 0)
{
TExpression whereExpression = stat.Gsqlparser.parseExpression(stat.WhereClause.Condition.ToScript());
if (string.ReferenceEquals(whereExpression.ToString(), null))
{
removeCondition removeCondition = new removeCondition(stat.ToString(), stat.dbvendor, conditionMap);
return(removeCondition.result);
}
else
{
string oldString = stat.ToScript();
conditionBuffer.Remove(0, conditionBuffer.Length);
ExpressionChecker w = new ExpressionChecker(this);
w.checkExpression(whereExpression, conditionMap);
stat.WhereClause.Condition = stat.Gsqlparser.parseExpression(whereExpression.ToScript());
}
}
if ((stat is TSelectSqlStatement) && ((TSelectSqlStatement)stat).GroupByClause != null && ((TSelectSqlStatement)stat).GroupByClause.HavingClause != null)
{
TExpression havingExpression = ((TSelectSqlStatement)stat).GroupByClause.HavingClause;
if (havingExpression == null)
{
removeCondition removeCondition = new removeCondition(stat.ToScript(), stat.dbvendor, conditionMap);
return(removeCondition.result);
}
else
{
string oldString = stat.ToScript();
conditionBuffer.Remove(0, conditionBuffer.Length);
ExpressionChecker w = new ExpressionChecker(this);
w.checkExpression(havingExpression, conditionMap);
string newString = stat.ToScript();
if (!oldString.Equals(newString))
{
if (havingExpression != null && havingExpression.ToScript().Trim().Length == 0)
{
((TSelectSqlStatement)stat).GroupByClause = null;
}
}
}
}
return(stat.ToScript());
}
public static bool ResetDatabase(string AZippedNewDatabaseData)
{
TDataBase DBConnectionObj = null;
List <string> tables = TTableList.GetDBNames();
bool SubmissionResult = false;
TDBTransaction ReadWriteTransaction = new TDBTransaction();
SortedList <int, string> CurrencyPerLedger = new SortedList <int, string>();
string ClientID = "ClientID";
try
{
ClientID = DomainManager.GClientID.ToString();
}
catch (Exception)
{
}
try
{
// Open a separate DB Connection for the importing of the data...
DBConnectionObj = DBAccess.Connect("ExportAllTables");
// ...and start a DB Transaction on that separate DB Connection
DBConnectionObj.WriteTransaction(ref ReadWriteTransaction, ref SubmissionResult, delegate
{
try
{
TProgressTracker.InitProgressTracker(ClientID,
Catalog.GetString("Restoring Database..."),
tables.Count + 3);
tables.Reverse();
// ignore s_session table, to avoid locking during the restore
tables.Remove("s_session");
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Deleting current data..."),
0);
// need to reset connection between s_user and p_partner to avoid broken foreign key
ReadWriteTransaction.DataBaseObj.ExecuteNonQuery("UPDATE pub_s_user SET p_partner_key_n = NULL", ReadWriteTransaction);
foreach (string table in tables)
{
ReadWriteTransaction.DataBaseObj.ExecuteNonQuery("DELETE FROM pub_" + table, ReadWriteTransaction);
}
if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
{
TProgressTracker.FinishJob(ClientID);
// As SubmissionResult is still false, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
TSimpleYmlParser ymlParser = new TSimpleYmlParser(PackTools.UnzipString(AZippedNewDatabaseData));
ymlParser.ParseCaptions();
tables.Reverse();
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Loading initial tables..."),
1);
// one transaction to import the user table and user permissions. otherwise logging in will not be possible if other import fails?
bool success = true;
success = success && LoadTable("s_user", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger);
success = success && LoadTable("s_module", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger);
success = success && LoadTable("s_user_module_access_permission", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger);
success = success && LoadTable("s_system_defaults", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger);
success = success && LoadTable("s_system_status", ymlParser, ReadWriteTransaction, ref CurrencyPerLedger);
if (!success)
{
// As SubmissionResult is still TSubmitChangesResult.scrError, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
{
TProgressTracker.FinishJob(ClientID);
// As SubmissionResult is still false, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
tables.Remove("s_user");
tables.Remove("s_module");
tables.Remove("s_user_module_access_permission");
tables.Remove("s_system_defaults");
tables.Remove("s_system_status");
int tableCounter = 2;
foreach (string table in tables)
{
TProgressTracker.SetCurrentState(ClientID,
String.Format(Catalog.GetString("Loading Table {0}..."), table),
tableCounter);
tableCounter++;
if (TProgressTracker.GetCurrentState(ClientID).CancelJob == true)
{
TProgressTracker.FinishJob(ClientID);
// As SubmissionResult is still false, a DB Transaction Rollback will get
// executed automatically and the Method will be exited with return value 'false'!
return;
}
LoadTable(table, ymlParser, ReadWriteTransaction, ref CurrencyPerLedger);
}
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Loading Sequences..."),
tables.Count + 5 + 3);
// set sequences appropriately, not lagging behind the imported data
foreach (string seq in TTableList.GetDBSequenceNames())
{
LoadSequence(seq, ymlParser, ReadWriteTransaction);
}
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Finishing Restore..."),
tables.Count + 5 + 4);
SubmissionResult = true;
// reset all cached tables
TCacheableTablesManager.GCacheableTablesManager.MarkAllCachedTableNeedsRefreshing();
}
catch (Exception e)
{
TLogging.Log("Problem in ResetDatabase: " + e.ToString());
TLogging.LogStackTrace(TLoggingType.ToLogfile);
throw;
}
});
}
finally
{
if (DBConnectionObj != null)
{
DBConnectionObj.CloseDBConnection();
}
}
// if we import an older database, we need to upgrade it.
// this happens in a separate database connection and database transactions.
TProgressTracker.SetCurrentState(ClientID,
Catalog.GetString("Updating the database..."),
tables.Count + 5 + 5);
TDBUpgrades upgrades = new TDBUpgrades();
upgrades.UpgradeDatabase();
TProgressTracker.FinishJob(ClientID);
return(SubmissionResult);
}