private void addColumns(IColumnedObject table)
{
string sql = string.Format(sqlColumns, table.Name);
string dbConn = ConnectionString;
var cn = new SQLiteConnection(dbConn);
cn.Open();
var cmd = new SQLiteCommand(sql, cn);
SQLiteDataReader rd = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable dt = rd.GetSchemaTable();
rd.Close();
foreach (DataRow dr in dt.Rows)
{
string columnName = dr.Field <string>("ColumnName");
bool isKey = dr.Field <bool>("IsKey");
string dataTypeName = dr.Field <string>("DataTypeName");
// string provDataTypeName = dr.Field<string>("ProviderSpecificDataType");
short?numericPrecision = dr.Field <short?>("NumericPrecision");
int id = dr.Field <int>("ColumnOrdinal");
int numPres = -1;
if (numericPrecision.HasValue)
{
numPres = numericPrecision.Value;
}
Sql8rColumn c = new Sql8rColumn(columnName, isKey, dataTypeName, numPres, id);
table.Columns.Add(c.ObjectId, c);
}
}
public Sql8rServer GetServer()
{
var s = new Sql8rServer(_settings);
string dbConn = ConnectionString;
setServerInfo(ref s, dbConn);
OdbcConnection cn = new OdbcConnection(dbConn);
cn.Open();
// DataTable schema = cn.GetSchema(); // collectionName, nbrOfRestriction, nbrOfIden
// foreach (DataRow row in schema.Rows)
// {
// string collection = row["CollectionName"].ToString();
// "MetaDataCollections" "DataSourceInformation"
// "DataTypes" "Restrictions" "ReservedWords"
// "Columns" "Indexes" "Procedures" "ProcedureColumns"
// "ProcedureParameters" "Tables"
// }
Sql8rDatabase db = new Sql8rDatabase("main", 0);
s.Databases.Add(db);
DataTable dtColumns = cn.GetSchema("Columns");
DataTable dtTables = cn.GetSchema("Tables"); // collectionName, nbrOfRestriction, nbrOfIden
foreach (DataRow row in dtTables.Rows)
{
string tCategory = row["TABLE_CAT"].ToString();
string tSchema = row["TABLE_SCHEM"].ToString();
string tName = row["TABLE_NAME"].ToString();
bool tIsSystem = row["TABLE_TYPE"].ToString().ToString().Equals("SYSTEM TABLE", StringComparison.InvariantCultureIgnoreCase) ? true : false;
string tRemarks = row["REMARKS"].ToString();
Sql8rTable t = new Sql8rTable(tName, tSchema, tIsSystem, 0);
int id = 0;
foreach (DataRow column in dtColumns.Select(string.Format("TABLE_NAME = '{0}'", t.Name)))
{
string taSchema = column["TABLE_SCHEM"].ToString();
string taName = column["TABLE_NAME"].ToString();
string cName = column["COLUMN_NAME"].ToString();
string cDataType = column["DATA_TYPE"].ToString();
string cTypeName = column["TYPE_NAME"].ToString();
int cSize = int.Parse(column["COLUMN_SIZE"].ToString());
Sql8rColumn c = new Sql8rColumn(cName, false, cTypeName, cSize, id++);
t.Columns.Add(c.ObjectId, c);
}
db.Tables.Add(t);
}
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 Sql8rColumn addColumn(DataRow drColumn)
{
string cName = drColumn["cName"].ToString();
string typeName = drColumn["typeName"].ToString();
int maxLength = int.Parse(drColumn["maxLength"].ToString(), CultureInfo.InvariantCulture);
bool inPrimaryKey = false;
if (!drColumn.IsNull("isPrimaryKey"))
{
inPrimaryKey = bool.Parse(drColumn["isPrimaryKey"].ToString());
}
int columnId = int.Parse(drColumn["columnId"].ToString(), CultureInfo.InvariantCulture);
var c = new Sql8rColumn(cName, inPrimaryKey, typeName, maxLength, columnId);
return(c);
}
private Sql8rView addView(DataRow drView, long dbId, string dbName, string dbConn, DataTable dtColumns, DataTable dtIndexes)
{
string vName = drView["vName"].ToString();
string sName = drView["sName"].ToString();
bool isSystemObject = bool.Parse(drView["isSystemObject"].ToString());
long objectId = long.Parse(drView["objectId"].ToString(), CultureInfo.InvariantCulture);
var v = new Sql8rView(vName, sName, isSystemObject, objectId);
DataRow[] rows = dtColumns.Select(string.Format("objectId = {0}", objectId));
foreach (DataRow drColumn in rows)
{
Sql8rColumn c = addColumn(drColumn);
if (!v.Columns.ContainsKey(c.ObjectId))
{
v.Columns.Add(c.ObjectId, c);
}
}
return(v);
}
public Sql8rServer GetServer()
{
var myConnection = new OracleConnection();
myConnection.ConnectionString = @"USER ID=hr;PASSWORD=hr;DATA SOURCE=//localhost/xe";
//_settings.GetConnectionString();
var s = new Sql8rServer(_settings);
OracleDataReader myReader = null;
var db = new Sql8rDatabase("OracleTest", 0);
try
{
myConnection.Open();
var myCommand = new OracleCommand();
myCommand.CommandText = "SELECT table_name, owner FROM all_tables";
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var t = new Sql8rTable(myReader.GetString(0), "",
myReader.GetString(1) == "SYSTEM" || myReader.GetString(1) == "SYS", 0);
db.Tables.Add(t);
var myColumnConnection = new OracleConnection();
//myColumnConnection.ConnectionString = _settings.GetConnectionString();
myColumnConnection.ConnectionString = @"USER ID=hr;PASSWORD=hr;DATA SOURCE=//localhost/xe";
OracleDataReader myColumnReader = null;
try
{
myColumnConnection.Open();
var myColumnCommand = new OracleCommand();
myColumnCommand.CommandText = string.Format(CultureInfo.InvariantCulture,
"SELECT column_name FROM user_tab_cols WHERE table_name = '{0}'",
t.Name);
myColumnCommand.CommandType = CommandType.Text;
myColumnCommand.Connection = myColumnConnection;
myColumnReader = myColumnCommand.ExecuteReader();
while (myColumnReader.Read())
{
var c = new Sql8rColumn(myColumnReader.GetString(0), false, "", 0, 0);
t.Columns.Add(c.ObjectId, c);
}
}
finally
{
myColumnReader.Close();
myColumnConnection.Close();
}
}
myReader.Close();
myCommand.CommandText = "SELECT view_name, owner FROM all_views";
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var v = new Sql8rView(myReader.GetString(0), "",
myReader.GetString(1) == "SYSTEM" || myReader.GetString(1) == "SYS", 0);
db.Views.Add(v);
}
myReader.Close();
myCommand.CommandText = "SELECT UNIQUE(object_name) FROM all_procedures";
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var sp = new Sql8rProcedure(myReader.GetString(0), "", false, 0);
db.Procedures.Add(sp.Name, sp);
}
myReader.Close();
s.Databases.Add(db);
}
finally
{
myConnection.Close();
}
return(s);
}
public Query GetAlterColumnQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rColumn column)
{
throw new NotImplementedException();
}
public Sql8rServer GetServer()
{
var myConnection = new MySqlConnection();
myConnection.ConnectionString = ConnectionString;
var s = new Sql8rServer(_settings);
MySqlDataReader myReader = null;
try
{
myConnection.Open();
var dbTable = new DataTable();
dbTable.Locale = CultureInfo.InvariantCulture;
string dbSQL = ResourceReader.GetResourceSql("SQL8r.Logic.MySQL.Queries.UI.GetServerDatabases.sql");
var myAdapter = new MySqlDataAdapter(dbSQL, myConnection);
myAdapter.Fill(dbTable);
foreach (DataRow row in dbTable.Rows)
{
var db = new Sql8rDatabase(row.ItemArray[0].ToString(), 0);
string sql = ResourceReader.GetResourceSql("SQL8r.Logic.MySQL.Queries.UI.GetServerTables.sql");
string tSQL = string.Format(sql, db.Name);
var myCommand = new MySqlCommand();
myCommand.CommandText = tSQL;
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
MySqlDataReader myColumnReader = null;
while (myReader.Read())
{
var t = new Sql8rTable(myReader.GetString(0), "", false, 0);
db.Tables.Add(t);
var myColumnConnection = new MySqlConnection();
myColumnConnection.ConnectionString = String.Format(
"{0};database={1}",
ConnectionString,
db.Name);
try
{
myColumnConnection.Open();
sql = ResourceReader.GetResourceSql("SQL8r.Logic.MySQL.Queries.UI.GetServerColumns.sql");
string cSQL = string.Format(sql, t.Name);
var myColumnCommand = new MySqlCommand();
myColumnCommand.CommandText = cSQL;
myColumnCommand.CommandType = CommandType.Text;
myColumnCommand.Connection = myColumnConnection;
myColumnReader = myColumnCommand.ExecuteReader();
while (myColumnReader.Read())
{
var reg = new Regex(@"[a-z]+");
Match match = reg.Match(myColumnReader.GetString(1));
string colType = match.Value;
reg = new Regex(@"[0-9]+");
match = reg.Match(myColumnReader.GetString(1));
int maxLength = 0;
if (match.Value != "")
{
maxLength = Convert.ToInt32(match.Value);
}
var c = new Sql8rColumn(myColumnReader.GetString(0),
myColumnReader.GetString(3) == "PRI",
colType, maxLength, 0);
t.Columns.Add(c.ObjectId, c);
}
}
finally
{
myColumnReader.Close();
myColumnConnection.Close();
}
}
myReader.Close();
myCommand.CommandText = string.Format(
"SHOW FULL TABLES FROM {0} WHERE Table_type = 'VIEW';",
db.Name);
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var v = new Sql8rView(myReader.GetString(0), "", false, 0);
// hej Johan, breaking change: objectid
db.Views.Add(v);
var myColumnConnection = new MySqlConnection();
myColumnConnection.ConnectionString = String.Format(
"{0};database={1}",
ConnectionString,
db.Name);
try
{
myColumnConnection.Open();
sql = ResourceReader.GetResourceSql("SQL8r.Logic.MySQL.Queries.UI.GetServerColumns.sql");
string cSQL = string.Format(sql, v.Name);
var myColumnCommand = new MySqlCommand();
myColumnCommand.CommandText = cSQL;
myColumnCommand.CommandType = CommandType.Text;
myColumnCommand.Connection = myColumnConnection;
myColumnReader = myColumnCommand.ExecuteReader();
while (myColumnReader.Read())
{
var reg = new Regex(@"[a-z]+");
Match match = reg.Match(myColumnReader.GetString(1));
string colType = match.Value;
reg = new Regex(@"[0-9]+");
match = reg.Match(myColumnReader.GetString(1));
int maxLength = 0;
if (match.Value != "")
{
maxLength = Convert.ToInt32(match.Value);
}
var c = new Sql8rColumn(myColumnReader.GetString(0),
myColumnReader.GetString(3) == "PRI",
colType, maxLength, 0);
v.Columns.Add(c.ObjectId, c);
}
}
finally
{
myColumnReader.Close();
myColumnConnection.Close();
}
}
myReader.Close();
myCommand.CommandText = "SELECT ROUTINE_NAME " +
"FROM INFORMATION_SCHEMA.ROUTINES " +
"WHERE ROUTINE_SCHEMA='" + db.Name + "' " +
"AND ROUTINE_TYPE='PROCEDURE'" + ";";
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var sp = new Sql8rProcedure(myReader.GetString(0), "", false, 0);
db.Procedures.Add(sp.Name, sp);
}
myReader.Close();
myCommand.CommandText = "SELECT ROUTINE_NAME " +
"FROM INFORMATION_SCHEMA.ROUTINES " +
"WHERE ROUTINE_SCHEMA='" + db.Name + "' " +
"AND ROUTINE_TYPE='FUNCTION'" + ";";
myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
var fkn = new Sql8rFunction(myReader.GetString(0), "", false, 0);
db.Functions.Add(fkn.Name, fkn);
}
myReader.Close();
s.Databases.Add(db);
}
}
finally
{
myConnection.Close();
}
return(s);
}
public Query GetAlterColumnQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rColumn column)
{
string sql = string.Format(sqlAlterColumn, database.Name, table.GetFullObjectName(), column.Name);
var query = new Query(_settings, server.Name, database.Name, sql);
return(query);
}
public Query GetAlterColumnQuery(Sql8rServer server, Sql8rDatabase database, Sql8rTable table, Sql8rColumn column)
{
// throw new System.NotImplementedException();
string sql = string.Format(sqlAlterColumn, table.GetFullObjectName(), column.Name);
var query = new Query(_settings, server.Name, database.Name, sql);
return(query);
}
