private void treeListView1_SelectedIndexChanged(object sender, EventArgs e)
{
// this.treeListView1.SelectedObject
MySqlObject mso = (MySqlObject)this.treeListView1.SelectedObject;
expandSelectedObjectToolStripMenuItem1.Enabled = this.treeListView1.SelectedObject != null;
collapseSelectedObjectToolStripMenuItem1.Enabled = this.treeListView1.SelectedObject != null;
//objectScriptToolStripMenuItem1.Enabled = this.treeListView1.SelectedObject != null;
//objectSummaryToolStripMenuItem1.Enabled = this.treeListView1.SelectedObject != null;
//objectPropertiesToolStripMenuItem1.Enabled = this.treeListView1.SelectedObject != null;
//dataPreviewToolStripMenuItem1.Enabled = this.treeListView1.SelectedObject != null; e.Index != -1 && mso != null && (mso.ObjectType == "USER_TABLE" || mso.ObjectType == "VIEW" || mso.ObjectType == "SYNONYM");
expandSelectedObjectToolStripMenuItem.Enabled = this.treeListView1.SelectedObject != null;
collapseSelectedObjectToolStripMenuItem.Enabled = this.treeListView1.SelectedObject != null;
//objectScriptToolStripMenuItem.Enabled = e.Index != -1;
//objectSummaryToolStripMenuItem.Enabled = e.Index != -1;
//objectPropertiesToolStripMenuItem.Enabled = e.Index != -1;
//dataPreviewToolStripMenuItem1.Enabled = e.Index != -1 && mso != null && (mso.ObjectType == "USER_TABLE" || mso.ObjectType == "VIEW" || mso.ObjectType == "SYNONYM");
((MainFormMdi)this.ParentForm).PropertiesDockForm.SetObject(mso, (mso != null) ? GetChildren(mso) : null, "ObjectName");
((MainFormMdi)this.ParentForm).ObjectDefinitionDockForm.SetObject(mso);
((MainFormMdi)this.ParentForm).ChildrenSummaryDockForm.SetObject(mso, (mso != null) ? GetChildren(mso) : null);
((MainFormMdi)this.ParentForm).DataPreviewDockForm.SetObject(mso);
((MainFormMdi)this.ParentForm).ObjectDefinitionDockForm.SetObject(mso);
}
public MsSqlSteps(MySqlObject _mySqlObject)
{
mySqlObject = _mySqlObject;
if (FeatureContext.Current.ContainsKey("mySqlConn"))
{
mySqlObject.MySqlConn = (MySqlConnection)FeatureContext.Current["mySqlConn"];
}
if (FeatureContext.Current.ContainsKey("msSqlConn"))
{
mySqlObject.MsSqlConn = (SqlConnection)FeatureContext.Current["msSqlConn"];
}
}
public IEnumerable <MySqlObject> GetChildren(MySqlObject parent)
{
List <MySqlObject> children = new List <MySqlObject>();
foreach (MySqlObject o in _dependencyObjectList.Where(m => m.SystemParentId == parent.SystemId).OrderBy(m => m.ObjectName))
{
children.Add(o);
children.AddRange(GetChildren(o));
}
return(children);
}
public void SetObject(MySqlObject mySqlObject)
{
this.Enabled = mySqlObject != null;
if (mySqlObject != null)
{
this._mySqlObject = mySqlObject;
richTextBox1.Rtf = mySqlObject.Rtf;
tsbOpenInSSMS.Enabled = tsbShowInThisWindow.Enabled = (mySqlObject.ObjectType == "USER_TABLE" || mySqlObject.ObjectType == "VIEW");
}
else
{
this.richTextBox1.Text = AppSettings.Default.NoObjectSelectedDefaultText;
}
objectListView1.ClearObjects();
objectListView1.Columns.Clear();
}
public void SetObject(MySqlObject mySqlObject)
{
this.Enabled = mySqlObject != null;
if (mySqlObject != null)
{
this._mySqlObject = mySqlObject;
richTextBoxObjectName.Rtf = mySqlObject.Rtf;
//string od = mySqlObject.GetObjectDefinition(ref _mySqlServer);
string od = mySqlObject.ObjectDefinition;
richTextBoxObjectDef.Enabled = !string.IsNullOrEmpty(od);
richTextBoxObjectDef.Text = (!string.IsNullOrEmpty(od)) ? od : "Object definition unknown";
}
else
{
this.richTextBoxObjectName.Text = AppSettings.Default.NoObjectSelectedDefaultText;
richTextBoxObjectDef.Enabled = false;
richTextBoxObjectDef.Text = string.Empty;
}
}
public void SetObject(MySqlObject mo, IEnumerable <MySqlObject> childrenObjects)
{
this.Enabled = mo != null;
if (mo != null)
{
richTextBox1.Rtf = mo.Rtf;
MySqlObjectEqualityComparer moec = new MySqlObjectEqualityComparer();
var resultAll = from o in childrenObjects
group o.SystemId by o.ObjectType into g
orderby g.Key ascending
select new { ObjectType = g.Key, ObjectCount = g.Count() };
var resultUnique = from o in childrenObjects.Distinct(moec)
group o.SystemId by o.ObjectType into g
orderby g.Key ascending
select new { ObjectType = g.Key, UniqueObjectCount = g.Count() };
var result = from r1 in resultAll
join r2 in resultUnique on r1.ObjectType equals r2.ObjectType
select new { r1.ObjectType, r1.ObjectCount, r2.UniqueObjectCount };
var totals = from r in result
select new { ObjectType = totalString, ObjectCount = result.Sum(r1 => r1.ObjectCount), UniqueObjectCount = result.Sum(r2 => r2.UniqueObjectCount) };
objectListView1.SetObjects(result.Union(totals));
tsslObjectCount.Text = string.Format("{0} objects, {1} unique", result.Sum(r1 => r1.ObjectCount), result.Sum(r2 => r2.UniqueObjectCount));
}
else
{
this.richTextBox1.Text = AppSettings.Default.NoObjectSelectedDefaultText;
objectListView1.ClearObjects();
}
}
public void Load(bool forceReload)
{
DateTime dt;
int dbCount;
int counter;
this._status = ESqlServerStatus.REFRESHING;
OnDataChanged?.Invoke(this, null);
// Make backup of all lists in case of use cancells execution
MySqlDatabase[] oldLoadedDatabases = new MySqlDatabase[_sqlLoadedDatabases.Count];
MySqlObject[] oldSqlObjects = new MySqlObject[_sqlObjects.Count];
MySqlExpressionDependency[] oldSqlExpressionDependencies = new MySqlExpressionDependency[_sqlExpressionDependencies.Count];
_sqlLoadedDatabases.CopyTo(oldLoadedDatabases);
_sqlObjects.CopyTo(oldSqlObjects);
_sqlExpressionDependencies.CopyTo(oldSqlExpressionDependencies);
if (forceReload)
{
_sqlLoadedDatabases.Clear();
_sqlObjects.Clear();
_sqlExpressionDependencies.Clear();
}
MySqlDatabase[] newSqlDatabases = SqlCheckedDatabases.Except(SqlLoadedDatabases).ToArray();
MySqlDatabase[] oldSqlDatabases = SqlLoadedDatabases.Except(SqlCheckedDatabases).ToArray();
counter = 0;
dbCount = newSqlDatabases.Length * 2 + oldSqlDatabases.Length;
// Remove unnecessary objects from unused database
if (oldSqlDatabases.Length > 0)
{
foreach (MySqlDatabase oldDb in oldSqlDatabases)
{
if (LongRunningOperationManager.CancellationPending)
{
break;
}
dt = DateTime.Now;
MyOutput.NewMessage(EOutputMessageType.PROGRESS, string.Format("Removing unnecessary objects for server '{0}', database '{1}' from internal database... ", oldDb.ServerObjectName, oldDb.DatabaseName));
int deletedObjectCount = _sqlObjects.RemoveAll(o => o.ServerObjectId == oldDb.ServerObjectId && o.DatabaseId == oldDb.DatabaseId);
MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Removed {0:n0} objects.", deletedObjectCount), (DateTime.Now - dt).TotalSeconds);
dt = DateTime.Now;
MyOutput.NewMessage(EOutputMessageType.PROGRESS, string.Format("Removing unnecessary dependencies for server '{0}', database '{1}' from internal database... ", oldDb.ServerObjectName, oldDb.DatabaseName));
int deletedExpressionDependencyCount = _sqlExpressionDependencies.RemoveAll(o => o.ServerObjectId == oldDb.ServerObjectId && o.DatabaseId == oldDb.DatabaseId);
MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Removed {0:n0} dependencies.", deletedExpressionDependencyCount), (DateTime.Now - dt).TotalSeconds);
LongRunningOperationManager.ReportProgress(100 * ++counter / dbCount);
}
}
if (newSqlDatabases.Length > 0)
{
foreach (MySqlDatabase newDb in newSqlDatabases)
{
if (LongRunningOperationManager.CancellationPending)
{
break;
}
initSqlObjects(newDb.ServerObjectId, newDb.DatabaseId);
//loadDatabaseObjects(newDb.ServerObjectName, newDb.DatabaseName);
LongRunningOperationManager.ReportProgress(100 * ++counter / dbCount);
}
MyOutput.NewMessage(EOutputMessageType.INFORMATION, string.Format("Totally {0:n0} objects loaded from {1:n0} server(s) and {2:n0} database(s).", _sqlObjects.Count, SqlCheckedDatabases.Select(s => s.ServerObjectName).Distinct().Count(), SqlCheckedDatabases.Length));
// ALL OBJECTS FROM ALL SERVERS / DATABASES MUST BE LOADED FIRST!
foreach (MySqlDatabase newDb in newSqlDatabases)
{
if (LongRunningOperationManager.CancellationPending)
{
break;
}
initSqlExpressionDependencies(newDb.ServerObjectId, newDb.DatabaseId);
LongRunningOperationManager.ReportProgress(100 * ++counter / dbCount);
}
MyOutput.NewMessage(EOutputMessageType.INFORMATION, string.Format("Totally {0:n0} dependencies loaded from {1:n0} server(s) and {2:n0} database(s).", _sqlExpressionDependencies.Count, SqlCheckedDatabases.Select(s => s.ServerObjectName).Distinct().Count(), SqlCheckedDatabases.Length));
}
if (!LongRunningOperationManager.CancellationPending)
{
_sqlLoadedDatabases.Clear();
_sqlLoadedDatabases.AddRange(SqlCheckedDatabases);
if (oldSqlDatabases.Length > 0 || newSqlDatabases.Length > 0)
{
this._lastDataLoad = DateTime.Now;
}
}
else
{
_sqlLoadedDatabases.Clear();
_sqlObjects.Clear();
_sqlExpressionDependencies.Clear();
_sqlLoadedDatabases.AddRange(oldLoadedDatabases);
_sqlObjects.AddRange(oldSqlObjects);
_sqlExpressionDependencies.AddRange(oldSqlExpressionDependencies);
}
this._status = ESqlServerStatus.READY;
// Prepare EX lists
/*
* SqlExpressionDependenciesEx.Clear();
* foreach (var ed in _sqlExpressionDependencies)
* {
* var keys = new MyKeyList();
* keys.Add(ed.ParentServerObjectId);
* keys.Add(ed.ParentDatabaseId);
* keys.Add(ed.ParentObjectId);
* keys.Add(ed.ChildServerObjectId);
* keys.Add(ed.ChildDatabaseId);
* keys.Add(ed.ChildObjectId);
* SqlExpressionDependenciesEx.Add(keys, ed);
* }
*/
OnDataChanged?.Invoke(this, null);
}
private int loadDependentObjects(MySqlObject masterParentObject, MySqlObject[] objs, bool isTopDown, ref int refObjectId, int level)
{
int totalMaxLevel = -1;
bool hasNextObjects = false;
int counter = 0;
IEnumerable <MySqlObject> nextObjects;
IEnumerable <MySqlObject> nextSynonymObjects;
foreach (MySqlObject obj in objs)
{
if (LongRunningOperationManager.CancellationPending)
{
//e.Cancel = true;
break;
}
var singleObjectMaxLevel = -1;
// Get next objects which are NOT synonyms
if (isTopDown)
{
nextObjects = from o in _mySqlServer.SqlObjects
join d in _mySqlServer.SqlExpressionDependencies.Where(d => d.ChildObjectExists).Where(d => d.ChildObjectId != d.ParentObjectId)
on new { ServerId = o.ServerObjectId, DatabaseId = o.DatabaseId, ObjectId = o.ObjectId }
equals new { ServerId = d.ChildServerObjectId, DatabaseId = d.ChildDatabaseId, ObjectId = d.ChildObjectId }
where obj.ServerObjectId == d.ParentServerObjectId &&
obj.DatabaseId == d.ParentDatabaseId &&
obj.ObjectId == d.ParentObjectId &&
obj.ObjectType != "SYNONYM"
select o;
var nonExistingNextObjects = from d in _mySqlServer.SqlExpressionDependencies.Where(d => !d.ChildObjectExists).Where(d => d.ChildObjectId != d.ParentObjectId)
where obj.ServerObjectId == d.ParentServerObjectId &&
obj.DatabaseId == d.ParentDatabaseId &&
obj.ObjectId == d.ParentObjectId &&
obj.ObjectType != "SYNONYM"
select new MySqlObject
{
ServerObjectName = d.ChildServerObjectName,
DatabaseName = d.ChildDatabaseName,
SchemaName = d.ChildSchemaName,
ObjectName = d.ChildObjectName,
ServerObjectId = -1,
DatabaseId = -1,
SchemaId = -1,
ObjectId = -1,
ObjectNameFull = string.Format("[{0}].[{1}].[{2}].[{3}]", d.ChildServerObjectName, d.ChildDatabaseName, d.ChildSchemaName, d.ChildObjectName),
ObjectTypeId = "UNRESOLVED_ENTITY",
ObjectType = "UNRESOLVED_ENTITY"
};
nextObjects = nextObjects.Union(nonExistingNextObjects);
if (!string.IsNullOrEmpty(obj.BaseObjectNameFull))
{
string[] bo = obj.BaseObjectNameFull.Replace("[", string.Empty).Replace("]", string.Empty).Split('.');
string serverName = bo[0];
string databaseName = bo[1];
if (
!_mySqlServer.SqlLoadedDatabases.Any(
db => db.ServerObjectName.Equals(serverName, StringComparison.InvariantCultureIgnoreCase) &&
db.DatabaseName.Equals(databaseName, StringComparison.InvariantCultureIgnoreCase)))
{
_recommendedDatabases.Add(new KeyValuePair <MySqlObject, MySqlDatabase>(obj, new MySqlDatabase {
ServerObjectId = -1, ServerObjectName = serverName, DatabaseId = -1, DatabaseName = databaseName
}));
}
}
nextSynonymObjects = from o in _mySqlServer.SqlObjects
where o.ObjectNameFull.Equals(obj.BaseObjectNameFull, StringComparison.InvariantCultureIgnoreCase) &&
obj.ObjectType == "SYNONYM"
select o;
}
else
{
nextObjects = from o in _mySqlServer.SqlObjects
join d in _mySqlServer.SqlExpressionDependencies.Where(d => d.ChildObjectId != d.ParentObjectId) on new { ServerId = o.ServerObjectId, o.DatabaseId, o.ObjectId } equals new { ServerId = d.ParentServerObjectId, DatabaseId = d.ParentDatabaseId, ObjectId = d.ParentObjectId }
where obj.ServerObjectId == d.ChildServerObjectId &&
obj.DatabaseId == d.ChildDatabaseId &&
obj.ObjectId == d.ChildObjectId &&
o.ObjectType != "SYNONYM"
select o;
// non-existing objects
//var a1 = from d in _inMemoryDatabase.SqlDependencyList.Where(d => !d.ChildObjectExists).Where(d => d.ChildObjectId != d.ParentObjectId)
// where obj.ServerName.Equals(d.ChildServerName, StringComparison.InvariantCultureIgnoreCase) &&
// obj.DatabaseName.Equals(d.ChildDatabaseName, StringComparison.InvariantCultureIgnoreCase) &&
// obj.SchemaName.Equals(d.ChildSchemaName, StringComparison.InvariantCultureIgnoreCase) &&
// obj.ObjectName.Equals(d.ChildObjectName, StringComparison.InvariantCultureIgnoreCase)
// select d;
var nonExistingNextObjects = from o in _mySqlServer.SqlObjects
join d in _mySqlServer.SqlExpressionDependencies.Where(d => !d.ChildObjectExists).Where(d => d.ChildObjectId != d.ParentObjectId) on new { ServerId = o.ServerObjectId, o.DatabaseId, o.ObjectId } equals new { ServerId = d.ParentServerObjectId, DatabaseId = d.ParentDatabaseId, ObjectId = d.ParentObjectId }
where obj.ServerObjectName.Equals(d.ChildServerObjectName, StringComparison.InvariantCultureIgnoreCase) &&
obj.DatabaseName.Equals(d.ChildDatabaseName, StringComparison.InvariantCultureIgnoreCase) &&
obj.SchemaName.Equals(d.ChildSchemaName, StringComparison.InvariantCultureIgnoreCase) &&
obj.ObjectName.Equals(d.ChildObjectName, StringComparison.InvariantCultureIgnoreCase) &&
o.ObjectType != "SYNONYM"
select o;
nextObjects = nextObjects.Union(nonExistingNextObjects);
nextSynonymObjects = from o in _mySqlServer.SqlObjects
where obj.ObjectNameFull.Equals(o.BaseObjectNameFull, StringComparison.InvariantCultureIgnoreCase) &&
o.ObjectType == "SYNONYM"
select o;
}
var allNextObjects = nextObjects.Union(nextSynonymObjects);
if (allNextObjects.Any())
{
hasNextObjects = true;
}
if (masterParentObject == null && allNextObjects.Any() || masterParentObject != null)
{
obj.SystemId = ++refObjectId;
obj.SystemParentId = (masterParentObject == null) ? -1 : masterParentObject.SystemId;
obj.HierarchyLevel = (masterParentObject == null) ? 0 : masterParentObject.HierarchyLevel + 1;
obj.IsSelfReferencing = _mySqlServer.SqlExpressionDependencies.Any(d => d.ParentObjectId == obj.ObjectId && d.ParentObjectId == d.ChildObjectId) ? "Yes" : "No";
int nextObjectsMaxLevel = loadDependentObjects(obj, allNextObjects.ToArray(), isTopDown, ref refObjectId, level + 1);
if (nextObjectsMaxLevel > totalMaxLevel)
{
totalMaxLevel = nextObjectsMaxLevel;
}
if (nextObjectsMaxLevel > singleObjectMaxLevel)
{
singleObjectMaxLevel = nextObjectsMaxLevel;
}
obj.MaximumUnderlyingLevels = singleObjectMaxLevel - obj.HierarchyLevel;
_dependencyObjectList.Add((MySqlObject)obj.Clone());
}
if (level == 0)
{
LongRunningOperationManager.ReportProgress(100 * ++counter / objs.Length);
}
}
return(Math.Max(totalMaxLevel, (hasNextObjects) ? level : level - 1));
//MyOutput.NewMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} dependencies in {1:0.00} seconds.", _sqlDependencyList.Count - initialNumberOfRows, (DateTime.Now - dt).TotalSeconds));
}
private void export(object sender, EventArgs e)
{
if (!_dependencyObjectList.Any())
{
return;
}
try
{
string workbookName = TempFolder.GetPath(null, "xlsx", true);
var spreadsheet = Excel.CreateWorkbook(workbookName);
Excel.AddBasicStyles(spreadsheet);
Excel.AddWorksheet(spreadsheet, "Sheet1");
var worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet;
// Excel file ready for the data
//
uint maxDepth = (uint)_dependencyObjectList.Max(o => o.HierarchyLevel) + 1;
// Columns
for (uint i = 1; i <= maxDepth; i++)
{
string text = string.Format("Level {0}", /*this.treeListView1.Columns[0].Text,*/ i);
Excel.SetCellValue(spreadsheet, worksheet, i, 1, text, false, false);
}
for (uint i = maxDepth + 1; i <= (uint)this.treeListView1.Columns.Count + maxDepth - 1; i++)
{
string text = this.treeListView1.Columns[(int)i - (int)maxDepth].Text;
Excel.SetCellValue(spreadsheet, worksheet, i, 1, text, false, false);
}
// data
uint row = 2;
foreach (object obj in this.treeListView1.Objects)
{
MySqlObject sqlObj = (MySqlObject)obj;
Excel.SetCellValue(spreadsheet, worksheet, (uint)sqlObj.HierarchyLevel + 1, row, sqlObj.ObjectName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 1, row, sqlObj.ObjectId, null, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 2, row, sqlObj.ServerObjectName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 3, row, sqlObj.DatabaseName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 4, row, sqlObj.SchemaName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 5, row, sqlObj.ObjectType, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 6, row, sqlObj.MaximumUnderlyingLevels, null, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 7, row, sqlObj.IsSelfReferencing, false, false);
row++;
IEnumerable <MySqlObject> children = GetChildren(sqlObj);
foreach (MySqlObject child in children)
{
Excel.SetCellValue(spreadsheet, worksheet, (uint)child.HierarchyLevel + 1, row, child.ObjectName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 1, row, child.ObjectId, null, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 2, row, child.ServerObjectName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 3, row, child.DatabaseName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 4, row, child.SchemaName, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 5, row, child.ObjectType, false, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 6, row, child.MaximumUnderlyingLevels, null, false);
Excel.SetCellValue(spreadsheet, worksheet, (uint)maxDepth + 7, row, child.IsSelfReferencing, false, false);
row++;
}
}
worksheet.Save();
spreadsheet.Close();
Process.Start(workbookName);
}
catch (Exception ex)
{
}
}
