///<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 } }