public Sql8rServer GetServer()
{
// string sqlDatabases = "SELECT type, name, tbl_name, rootpage, sql FROM main.SQLITE_MASTER;";
var s = new Sql8rServer(_settings);
string dbConn = ConnectionString;
setServerInfo(ref s, dbConn);
var sdaTables = new SQLiteDataAdapter(sqlDatabases, dbConn);
var dtTables = new DataTable("TablesEtc");
sdaTables.Fill(dtTables);
string dbName = _settings.DatabaseName;
if (File.Exists(_settings.DatabaseName))
{
dbName = Path.GetFileName(_settings.DatabaseName);
}
var db = new Sql8rDatabase(dbName, 0);
s.Databases.Add(db);
var systable = new Sql8rTable("SQLITE_MASTER", "main", true, 0);
addColumns(systable);
db.Tables.Add(systable);
foreach (DataRow drTable in dtTables.Select("type = 'table'"))
{
string t_name = drTable.Field <string>("name");
long t_rootpage = drTable.Field <long>("rootpage");
// string sql = drTable.Field<string>("sql");
var table = new Sql8rTable(t_name, "", false, t_rootpage);
addColumns(table);
foreach (DataRow drIndex in dtTables.Select("type = 'index' AND tbl_name = '" + t_name + "'"))
{
string i_name = drIndex.Field <string>("name");
long i_rootpage = drIndex.Field <long>("rootpage");
var index = new Sql8rIndex(i_name, false, false, i_rootpage);
table.Indexes.Add(index.Name, index);
}
db.Tables.Add(table);
//if (type.Equals("trigger", StringComparison.InvariantCultureIgnoreCase))
//{
// var trigger = new Sql8rTrigger(name, "", false);
//}
}
return(s);
}
private Sql8rTable addTable(DataRow drTable, long dbId, string dbName, string dbConn, DataTable dtColumns, DataTable dtIndexes)
{
string tName = drTable["tName"].ToString();
string sName = drTable["sName"].ToString();
bool isSystemObject = bool.Parse(drTable["isSystemObject"].ToString());
long objectId = long.Parse(drTable["objectId"].ToString(), CultureInfo.InvariantCulture);
var t = new Sql8rTable(tName, sName, isSystemObject, objectId);
if (!dbName.Equals("tempdb", StringComparison.OrdinalIgnoreCase))
{
string objectName = string.Format(CultureInfo.InvariantCulture,
"{0}.{1}", sName, tName);
setTableSpaceUsage(ref t, dbConn, dbName, objectName);
}
DataRow[] columnRows = dtColumns.Select(string.Format("objectId = {0}", objectId));
foreach (DataRow drColumn in columnRows)
{
Sql8rColumn c = addColumn(drColumn);
if (!t.Columns.ContainsKey(c.ObjectId))
{
t.Columns.Add(c.ObjectId, c);
}
}
DataRow[] indexRows = dtIndexes.Select(string.Format("objectId = {0}", objectId));
//string iSQL = string.Format(sqlIndexes, dbName, objectId);
//var dtIndexes = new DataTable("Indexes");
//var sdaIndexes = new SqlDataAdapter(iSQL, dbConn);
//sdaIndexes.Fill(dtIndexes);
foreach (DataRow drIndex in indexRows)
{
string iName = drIndex["iName"].ToString();
int indexId = int.Parse(drIndex["indexId"].ToString(), CultureInfo.InvariantCulture);
int iType = int.Parse(drIndex["iType"].ToString(), CultureInfo.InvariantCulture);
bool isPrimaryKey = bool.Parse(drIndex["isPrimaryKey"].ToString());
// 0=HEAP, 1=CLUSTERED, 2=UNCLUSTERED, 3=XML
var i = new Sql8rIndex(iName, iType == 1, isPrimaryKey, indexId);
setIndexSpaceUsage(ref i, dbConn, dbName, dbId, t.ObjectId, indexId);
t.Indexes.Add(i.Name, i);
}
return(t);
}
private IndexAction recommend(Sql8rIndex i)
{
if (i.SpaceUsed > 24 && i.AvgFragmentation > 30)
{
return(IndexAction.Rebuild);
}
if (i.SpaceUsed > 24 && i.AvgFragmentation > 5)
{
return(IndexAction.Reorganize);
}
return(IndexAction.None);
}
private void setIndexSpaceUsage(ref Sql8rIndex i, string dbConn, string dbName, long dbId, long objectId, int indexId)
{
string spaceSQL =
string.Format(sqlIndexSpaceUsed,
dbName, dbId, objectId, indexId);
var dtSpace = new DataTable("Space");
var sdaSpace = new SqlDataAdapter(spaceSQL, dbConn);
sdaSpace.Fill(dtSpace);
if (dtSpace.Rows.Count > 0)
{
//index_id, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count
DataRow drSpace = dtSpace.Rows[0];
double fragmentation = double.Parse(drSpace[1].ToString(), CultureInfo.CurrentCulture);
int spaceUsed = 8 * int.Parse(drSpace[4].ToString(), CultureInfo.InvariantCulture);
i.SetSpaceStats(fragmentation, spaceUsed);
}
}
public new Query GetReorganizeIndexQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rIndex index)
{
string sql = ResourceReader.GetResourceSql("SQL8r.Logic.SqlServer.Queries.UI.ReorganizeIndex.sql");
string head = string.Format(sql, database.Name, index.Name, table.Schema, table.Name, index.ObjectId);
var query = new Query(Settings, server.Name, database.Name, head);
return(query);
}
// private ServerConnectionSettings _settings;
#region IPerformanceManager Members
//public ServerVersionId ManagerName
//{
// get { return ServerVersionId.SqlServer_2008; }
//}
// public ServerConnectionSettings Settings
// {
// set { _settings = value; }
//}
public new Query GetRebuildIndexQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rIndex index)
{
string sql = ResourceReader.GetResourceSql("SQL8r.Logic.SqlServer.Queries.UI.RebuildIndex.sql");
string head = string.Format(sql, database.Name, index.Name, table.Schema, table.Name);
if (server.Edition == ServerEditions.EnterpriseEdition)
{
head += " WITH ONLINE = ON";
}
var query = new Query(Settings, server.Name, database.Name, head);
return(query);
}
public Query GetReorganizeIndexQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rIndex index)
{
throw new System.NotImplementedException();
}
public Query GetRebuildIndexQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rIndex index)
{
throw new NotImplementedException();
}
