///<summary>NOT Oracle compatible. This will take a set of rows from the specified table from the current database and insert them into a
///different database. It does NOT create tables nor databases if they don't exist, such things need to already exist. Enter text for the
///"whereClause" parameter to filter what rows to retrieve. Needs to include the "WHERE" statement, if it's being used. (Ex. "WHERE PatNum != 2")
///IMPORTANT NOTE -------------- MAKE SURE YOU SET CLASS-WIDE VARIABLES PRIOR TO RUNNING TABLE COMMANDS. - TableName, TablePriKey at the very least.</summary>
public static void InsertIntoLargeTable(string whereClause)
{
#if DEBUG
Stopwatch s = new Stopwatch();
s.Start();
#endif
SetListPriKeyMaxPerBatch(whereClause);
_tempTableName = _tableName; //Set these to be equal for the Insert function - We aren't renaming tables, we are copying from one to another.
ODThread odThreadQueueData = new ODThread(QueueBatches);
try {
lock (_lockObjQueueBatchQueries) {
_queueBatchQueries = new Queue <BatchQueries>();
}
_areQueueBatchThreadsDone = false;
odThreadQueueData.Name = "QueueDataThread";
odThreadQueueData.AddExceptionHandler(new ODThread.ExceptionDelegate((Exception ex) => { _areQueueBatchThreadsDone = true; }));
odThreadQueueData.Start(true);
InsertBatches();
}
catch (Exception ex) {
ex.DoNothing();
throw;
}
finally {
odThreadQueueData?.QuitAsync();
_areQueueBatchThreadsDone = true;
#if DEBUG
s.Stop();
Console.WriteLine("Total time to insert into table " + _tableName + " with " + string.Format("{0:#,##0.##}", _totalRowCount) + " rows: "
+ (s.Elapsed.Hours > 0?(s.Elapsed.Hours + " hours "):"") + (s.Elapsed.Minutes > 0?(s.Elapsed.Minutes + " min "):"")
+ (s.Elapsed.TotalSeconds - (s.Elapsed.Hours * 60 * 60) - (s.Elapsed.Minutes * 60)) + " sec");
#endif
}
}
private void QuitThread()
{
if (_threadCheckKeyboard != null)
{
_threadCheckKeyboard.QuitAsync();
_threadCheckKeyboard = null;
}
}
private void butCancel_Click(object sender, EventArgs e)
{
if (_odThread != null)
{
_odThread.QuitAsync();
}
DialogResult = DialogResult.Cancel;
Close();
}
private void ODForm_FormClosed(object sender, FormClosedEventArgs e)
{
if (_threadFilter != null)
{
_threadFilter.QuitAsync(); //It's fine if our thread loop finishes, it protects against unhandled exceptions.
_threadFilter = null;
//We don't want an enumeration exception here so don't clear _listFilterControls. It will get garbage collected anyways.
}
DisposeHelpButton();
_hasClosed = true;
}
private void FormConnectionLost_FormClosing(object sender, FormClosingEventArgs e)
{
if (_threadShouldWindowClose != null && !_threadShouldWindowClose.HasQuit)
{
_threadShouldWindowClose.QuitAsync();
}
//Unregister the "Fired" event from this form. The constructor will throw an exception if _eventInfoFired is not valid.
Delegate delegateFired = GetDelegateFired(_eventInfoFired.EventHandlerType);
MethodInfo methodRemoveHandler = _eventInfoFired.GetRemoveMethod();
methodRemoveHandler.Invoke(this, new object[] { delegateFired });
}
private void WorkerParse834(ODThread odThread)
{
Load834_Safe();
odThread.QuitAsync();
}
///<summary>NOT Oracle compatible. Do NOT try-catch this method, even when only adding an index.
///This will rename the table specified to be preceded by "convTmp_"+tableName+"random number", creates a copy of
///the table structure and indexes, adds the column(s) in colNamesAndDefs (Tuples with Item1 the column name and Item2 the column def), and if
///indexColsAndNames is not null adds the index on the comma-delimited list of columns (Item1) with index name (Item2) if included, otherwise
///letting MySQL name the index. Once the empty table clone is made, keys will be disabled (for MyISAM only), all rows will be inserted from the
///renamed convTmp_ table, the keys (indexes) will be enabled (MyISAM only), and the convTmp_ table will be dropped.
///A null or empty colName will cause this method to not add a new column to the table. This is useful for adding an index only.</summary>
public static void AlterLargeTable(string tableName, string priKeyField, List <Tuple <string, string> > colNamesAndDefs = null,
List <Tuple <string, string> > indexColsAndNames = null)
{
if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
{
throw new ODException("AlterLargeTable should only be invoked from convert script classes. Middle Tier not supported.");
}
#if DEBUG
Stopwatch s = new Stopwatch();
s.Start();
#endif
_tableName = tableName;
_tablePriKeyField = priKeyField;
string dbName = GetCurrentDatabase();
string command = "";
List <string> listChanges = new List <string>();
colNamesAndDefs = colNamesAndDefs?.FindAll(x => !string.IsNullOrEmpty(x.Item1) && !string.IsNullOrEmpty(x.Item2)) ?? new List <Tuple <string, string> >();
if (colNamesAndDefs.Count > 0)
{
listChanges.AddRange(colNamesAndDefs.Select(x => (ColumnExists(dbName, x.Item1)?"MODIFY":"ADD")
+ " `" + x.Item1 + "` " + x.Item2));//modify does nothing if data type isn't changed
}
indexColsAndNames = indexColsAndNames?.FindAll(x => !string.IsNullOrEmpty(x.Item1) && !IndexExists(_tableName, x.Item1)) ?? new List <Tuple <string, string> >();
if (indexColsAndNames.Count > 0)
{
//drop any index with different columns but the same name as the index we're adding. Useful if we are modifying the cols of a composite index
listChanges.AddRange(indexColsAndNames.FindAll(x => !string.IsNullOrEmpty(x.Item2) && IndexNameExists(_tableName, x.Item2))
.Select(x => "DROP INDEX `" + x.Item2 + "`"));
listChanges.AddRange(indexColsAndNames.Select(x => "ADD INDEX " + (!string.IsNullOrEmpty(x.Item2)?("`" + x.Item2 + "` "):"") + "(`" + x.Item1.Replace(",", "`,`") + "`)"));
}
if (listChanges.Count < 1)
{
return; //nothing to do, just return
}
string alterTableStmt = "ALTER TABLE `" + _tableName + "` " + string.Join(",", listChanges);
command = "SELECT ValueString FROM preference WHERE PrefName='UpdateAlterLargeTablesDirectly'";
if (Db.GetScalar(command) == "1")
{
Db.NonQ(alterTableStmt); //execute the alter table statement on the original table
return;
}
try {
SetListPriKeyMaxPerBatch();
}
catch (Exception ex) {
//MySQL 5.0 cannot run the command in the above method.
Db.NonQ(alterTableStmt); //execute the alter table statement on the original table
ex.DoNothing();
return;
}
if (_listPriKeyMaxPerBatch.Count <= 1) //if no rows, list will be empty. One row means the table isn't very large. Either way, small enough to run normal script
{
Db.NonQ(alterTableStmt); //execute the alter table statement on the original table
return;
}
//The table is large enough to merit multiple batches.
string rndStr = new Random().Next(1000000).ToString();
_tempTableName = "tempConv_" + _tableName + rndStr;
command = "DROP TABLE IF EXISTS `" + _tempTableName + "`";
Db.NonQ(command);
command = "RENAME TABLE `" + _tableName + "` TO `" + _tempTableName + "`";
Db.NonQ(command);
command = "CREATE TABLE `" + _tableName + "` LIKE `" + _tempTableName + "`";
Db.NonQ(command);
Db.NonQ(alterTableStmt); //execute the alter table statement on the new empty table
command = "SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='" + POut.String(dbName) + "' AND TABLE_NAME='" + POut.String(_tableName) + "'";
string tableEngine = Db.GetScalar(command)?.ToLower();
if (tableEngine == "myisam")
{
command = "ALTER TABLE `" + _tableName + "` DISABLE KEYS";
Db.NonQ(command);
}
ODThread odThreadQueueData = new ODThread(QueueBatches);
try {
lock (_lockObjQueueBatchQueries) {
_queueBatchQueries = new Queue <BatchQueries>();
}
_areQueueBatchThreadsDone = false;
odThreadQueueData.Name = "QueueDataThread";
odThreadQueueData.AddExceptionHandler(new ODThread.ExceptionDelegate((Exception ex) => { _areQueueBatchThreadsDone = true; }));
odThreadQueueData.Start(true);
InsertBatches();
if (tableEngine == "myisam")
{
command = "ALTER TABLE `" + _tableName + "` ENABLE KEYS";
Db.NonQ(command);
}
if (_insertBatchCount == _listPriKeyMaxPerBatch.Count)
{
command = "DROP TABLE IF EXISTS `" + _tempTableName + "`";
Db.NonQ(command);
}
else //if a batch(es) failed to run, keep the original table in case we want to drop the new and rename the original to undo this action
{
try {
command = "DROP TABLE IF EXISTS `" + _tableName + "`";
Db.NonQ(command);
command = "RENAME TABLE `" + _tempTableName + "` TO `" + _tableName + "`";
Db.NonQ(command);
Db.NonQ(alterTableStmt); //execute the alter table statement on the original table
}
catch (Exception ex) {
//Both the batch method of altering the table and the simple query failed. We will display an error message showing the number of rows that
//were copied in the batch method, and we will also include the exception from the simple method as the inner exception.
string colText = "";
if (colNamesAndDefs.Count > 0)
{
colText += "column" + (colNamesAndDefs.Count > 1?"s ":" ")
+ string.Join((colNamesAndDefs.Count == 2?" and ":", "), colNamesAndDefs.Select(x => x.Item1)) + " ";
if (colNamesAndDefs.Count > 2)
{
colText = colText.Insert(colText.LastIndexOf(", ") + 2, "and ");
}
}
string indexText = "";
if (indexColsAndNames.Count > 0)
{
indexText = "index" + (indexColsAndNames.Count > 1?"es ":" ")
+ string.Join((indexColsAndNames.Count == 2?" and ":", ")
//use the index name if provided, otherwise use the column name
, indexColsAndNames.Select(x => !string.IsNullOrEmpty(x.Item2)?x.Item2:("on column " + x.Item1))) + " ";
if (indexColsAndNames.Count > 2)
{
indexText = indexText.Insert(indexText.LastIndexOf(", ") + 2, "and ");
}
}
throw new ApplicationException("Adding the " + string.Join("and the ", (new[] { colText, indexText }).Where(x => !string.IsNullOrEmpty(x)))
+ " to the " + _tableName + " table failed due to incorrect number of rows being copied.", ex);
}
}
}
catch (Exception ex) {
ex.DoNothing();
throw;
}
finally {
odThreadQueueData?.QuitAsync();
_areQueueBatchThreadsDone = true;
#if DEBUG
s.Stop();
Console.WriteLine("Total time to alter table " + _tableName + " with " + string.Format("{0:#,##0.##}", _totalRowCount) + " rows: "
+ (s.Elapsed.Hours > 0?(s.Elapsed.Hours + " hours "):"") + (s.Elapsed.Minutes > 0?(s.Elapsed.Minutes + " min "):"")
+ (s.Elapsed.TotalSeconds - (s.Elapsed.Hours * 60 * 60) - (s.Elapsed.Minutes * 60)) + " sec");
#endif
}
}