public IPromise <IEnumerable <IEditorTreeNode> > GetNodes()
{
var metadata = SqlMetadata.Cached(_conn);
return(metadata.Objects
.Convert(RootFolder));
}
public static SqlMetadata Cached(SqlConnection conn)
{
SqlMetadata result;
if (!_cache.TryGetValue(conn.ConnectionString, out result))
{
result = new SqlMetadata(conn);
_cache[conn.ConnectionString] = result;
}
return(result);
}
public SqlEditorHelper(SqlConnection conn)
{
_sql = new SqlCompletionHelper(SqlMetadata.Cached(conn));
}
private IEditorTreeNode GetNode(SqlObject obj)
{
var metadata = SqlMetadata.Cached(_conn);
if (string.Equals(obj.Type, "table", StringComparison.OrdinalIgnoreCase) ||
string.Equals(obj.Type, "view", StringComparison.OrdinalIgnoreCase))
{
return(new EditorTreeNode()
{
Name = obj.Schema + "." + obj.Name,
Image = Icons.Class16,
HasChildren = true,
Children = new IEditorTreeNode[] {
new EditorTreeNode()
{
Name = "Columns",
Image = Icons.Folder16,
HasChildren = true,
ChildGetter = () => metadata.GetColumns(obj.Name, obj.Schema).Wait()
.Select(c => new EditorTreeNode()
{
Name = c.Name,
Description = c.Type,
Image = Icons.Property16,
}).OrderBy(n => n.Name)
},
new EditorTreeNode()
{
Name = "Keys",
Image = Icons.Folder16,
HasChildren = metadata.Objects.Wait().Any(o => o.ParentId == obj.Id && o.Type == "Key"),
ChildGetter = () => metadata.Objects.Wait().Where(o => o.ParentId == obj.Id && o.Type == "Key")
.Select(c => new EditorTreeNode()
{
Name = c.Name,
Image = Icons.Method16,
}).OrderBy(n => n.Name)
},
new EditorTreeNode()
{
Name = "Constraints",
Image = Icons.Folder16,
HasChildren = metadata.Objects.Wait().Any(o => o.ParentId == obj.Id && o.Type == "Constraint"),
ChildGetter = () => metadata.Objects.Wait().Where(o => o.ParentId == obj.Id && o.Type == "Constraint")
.Select(c => new EditorTreeNode()
{
Name = c.Name,
Image = Icons.Method16,
}).OrderBy(n => n.Name)
}
,
new EditorTreeNode()
{
Name = "Indexes",
Image = Icons.Folder16,
HasChildren = metadata.Objects.Wait().Any(o => o.ParentId == obj.Id && (o.Type == "Index" || o.SubType == SqlSubType.PrimaryKey)),
ChildGetter = () => metadata.Objects.Wait().Where(o => o.ParentId == obj.Id && (o.Type == "Index" || o.SubType == SqlSubType.PrimaryKey))
.Select(c => new EditorTreeNode()
{
Name = c.Name,
Image = Icons.Method16,
Scripts = new IEditorScript[] {
new EditorScript()
{
Name = "Stats",
Script = string.Format(@"SELECT
ind.name AS IndexName
, OBJECT_NAME(ind.OBJECT_ID) AS TableName
, indexstats.index_type_desc AS IndexType
, indexstats.avg_fragmentation_in_percent TotalFragmentation
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE ind.index_id = {0} and ind.object_id = {1};", c.Id, c.ParentId)
},
new EditorScript()
{
Name = "Rebuild",
Script = string.Format("ALTER INDEX [{0}] ON {1} REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95);", c.Name, c.Schema)
},
new EditorScript()
{
Name = "Reorganize",
Script = string.Format("ALTER INDEX [{0}] ON {1} REORGANIZE WITH ( LOB_COMPACTION = ON );", c.Name, c.Schema)
},
new EditorScript()
{
Name = "Disable",
Script = string.Format("ALTER INDEX [{0}] ON {1} DISABLE;", c.Name, c.Schema)
}
}
}).OrderBy(n => n.Name)
}
},
Scripts = GetScripts(obj)
});
}
else
{
return(new EditorTreeNode()
{
Name = obj.Schema + "." + obj.Name,
Image = Icons.XmlTag16,
HasChildren = false,
Scripts = GetScripts(obj)
});
}
}
public static SqlMetadata Cached(SqlConnection conn)
{
SqlMetadata result;
if (!_cache.TryGetValue(conn.ConnectionString, out result))
{
result = new SqlMetadata(conn);
_cache[conn.ConnectionString] = result;
}
return result;
}
