/// <summary>
/// Creates a list of statements to apply to the database based on the script editable by the user.
/// </summary>
private void CreateActualStatementsList()
{
ActualStatementRowsList = null;
if (string.IsNullOrEmpty(SqlScript))
{
return;
}
if (_userChangedOriginalQuery && string.Compare(SqlScript, OriginalSqlScript, StringComparison.InvariantCultureIgnoreCase) != 0)
{
// The user modified the original query and it is no longer the same as the original one, so the actual statements list is built from the modified SQL script text.
ActualStatementRowsList = new List <IMySqlDataRow>(_originalStatementRowsList.Count);
var statementsList = SqlScript.SplitInSqlStatements();
if (statementsList == null)
{
return;
}
foreach (var statementText in statementsList.Where(statementText => !string.IsNullOrEmpty(statementText)))
{
IMySqlDataRow rowToAdd;
if (MySqlStatement.GetSqlStatementType(statementText) == MySqlStatement.SqlStatementType.Set)
{
// If we find a SET statement then assign it to a new MySqlDummyRow since the Statement.SetVariablesSqlQuery will be set to null for non-SET statements.
// The reason for this is that we do not know what SET statements were changed by the user, there is no point into trying to find out which ones were
// changed, it is faster to always assign them to MySqlDummyRow and not process them in the actual IMySqlDataRow.Statement.
rowToAdd = new MySqlDummyRow(statementText);
}
else
{
// Try to find the IMySqlDataRow whose Statement.SqlQuery matches the current one, if found it means the user did not change it so use that one.
// If not found it means the user changed it so we assign it to a new MySqlDummyRow.
var originalRow = _originalStatementRowsList.FirstOrDefault(iMySqlRow => iMySqlRow.Statement.SqlQuery.Equals(statementText, StringComparison.InvariantCultureIgnoreCase));
if (originalRow != null)
{
originalRow.Statement.SetVariablesSqlQuery = null;
rowToAdd = originalRow;
}
else
{
rowToAdd = new MySqlDummyRow(statementText);
}
}
if (!ActualStatementRowsList.Contains(rowToAdd))
{
ActualStatementRowsList.Add(rowToAdd);
}
}
}
else
{
// The original query did not change so it is safe to assume the actual statements list is the same as the original one.
ActualStatementRowsList = _originalStatementRowsList;
}
}
/// <summary>
/// Creates the list of original statements this script contains before the user makes any changes.
/// </summary>
private void CreateOriginalStatementsList()
{
if (_originalStatementRowsList == null)
{
_originalStatementRowsList = new List <IMySqlDataRow>();
}
if (_mySqlTable != null)
{
_createdTable = false;
_lockedTable = false;
_originalStatementRowsList.Clear();
bool isForExport = _mySqlTable.OperationType.IsForExport();
bool isForAppend = _mySqlTable.OperationType.IsForAppend();
bool createTableOnly = isForExport && _mySqlTable.CreateTableWithoutData;
if (!createTableOnly && _mySqlTable.ChangedOrDeletedRows == 0)
{
return;
}
try
{
// Calculate the StringBuilder initial length to avoid its size to be internally doubled each time an Append to it is done to increase performance.
int builderLength = createTableOnly ? MiscUtilities.STRING_BUILDER_DEFAULT_CAPACITY : _mySqlTable.MaxQueryLength;
var sqlScript = new StringBuilder(builderLength);
IList <MySqlDummyRow> dummyRows;
bool createTableDummyRows = isForExport || isForAppend;
if (createTableDummyRows)
{
// Create optimization statements for INSERTS that disable key constraints and lock table.
// Also incluldes a CREATE statement if table on Export mode.
dummyRows = _mySqlTable.GetDummyRowsForTableCreationAndIndexOptimization(true);
if (dummyRows != null)
{
foreach (var dummyRow in dummyRows)
{
if (dummyRow.Statement.StatementType == MySqlStatement.SqlStatementType.CreateTable)
{
_createdTable = true;
}
if (dummyRow.Statement.StatementType == MySqlStatement.SqlStatementType.LockTables)
{
_lockedTable = true;
}
_originalStatementRowsList.Add(dummyRow);
sqlScript.AppendFormat("{0};{1}", dummyRow.Statement.SqlQuery, Environment.NewLine);
}
}
}
// Create DELETE, INSERT and UPDATE statements for table rows
// Do not change this code to get changed rows via the GetChanges method since the references to the MySqlDataTable and MySqlDataTable objects will be broken.
if (!createTableOnly)
{
bool isBulkInsert = (isForExport && !Settings.Default.ExportGenerateMultipleInserts) ||
(isForAppend && !Settings.Default.AppendGenerateMultipleInserts);
if (isBulkInsert)
{
// Generate INSERT statements as a bulk operations for all rows being processed
// Maybe more than 1 INSERT statement is generated depending on the size of the data and the value of the max allowed packet.
dummyRows = _mySqlTable.GetBulkInsertDummyRows();
foreach (var dummyRow in dummyRows)
{
_originalStatementRowsList.Add(dummyRow);
sqlScript.AppendFormat("{0};{1}", dummyRow.Statement.SqlQuery, Environment.NewLine);
}
}
else
{
// Generate a statement for each data row being processed
DataRowState[] rowStatesWithChanges = { DataRowState.Deleted, DataRowState.Added, DataRowState.Modified };
foreach (var mySqlRow in rowStatesWithChanges.SelectMany(rowState => _mySqlTable.Rows.Cast <MySqlDataRow>().Where(dr => !dr.IsHeadersRow && dr.RowState == rowState)))
{
_originalStatementRowsList.Add(mySqlRow);
string mainSqlQuery = mySqlRow.Statement.SqlQuery;
if (!string.IsNullOrEmpty(mySqlRow.Statement.SetVariablesSqlQuery))
{
sqlScript.AppendFormat("{0};{1}", mySqlRow.Statement.SetVariablesSqlQuery, Environment.NewLine);
}
sqlScript.AppendFormat("{0};{1}", mainSqlQuery, Environment.NewLine);
}
}
}
// Create optimization statements for INSERTS that re-enable key constraints and unlock table.
if (createTableDummyRows)
{
dummyRows = _mySqlTable.GetDummyRowsForTableCreationAndIndexOptimization(false);
if (dummyRows != null)
{
foreach (var dummyRow in dummyRows)
{
_originalStatementRowsList.Add(dummyRow);
sqlScript.AppendFormat("{0};{1}", dummyRow.Statement.SqlQuery, Environment.NewLine);
}
}
}
OriginalSqlScript = sqlScript.ToString();
}
catch (OutOfMemoryException oomEx)
{
MySqlSourceTrace.WriteAppErrorToLog(oomEx);
MiscUtilities.ShowCustomizedErrorDialog(Resources.SqlScriptTooBigErrorDetail, oomEx.Message, true);
}
}
else if (!string.IsNullOrEmpty(OriginalSqlScript) && _originalStatementRowsList.Count == 0)
{
var statementsList = SqlScript.SplitInSqlStatements();
if (statementsList == null)
{
return;
}
foreach (var statementText in statementsList.Where(statementText => !string.IsNullOrEmpty(statementText)))
{
_originalStatementRowsList.Add(new MySqlDummyRow(statementText));
}
}
}