/// <summary>
/// extracts information from the COLUMNS table of INFORMATION_SCHEMA
/// sets each column`s datatype, default value, nullability, autoincrement, uniqueness
/// and if the datatype is ENUM, sets the extended property as a List<string> labeled Common.Constants.ENUM_COLUMN_VALUES
/// </summary>
private void GetColumnTypes()
{
DataTable stats = driver.fetchAll("SELECT COLUMNS.* FROM COLUMNS JOIN TABLES USING(TABLE_SCHEMA, TABLE_NAME) " +
"WHERE TABLE_TYPE != \"VIEW\" AND TABLES.TABLE_SCHEMA = \"" + webDb +
"\" ORDER BY COLUMNS.TABLE_NAME, ORDINAL_POSITION");
Dictionary <string, DataColumnCollection> res = new Dictionary <string, DataColumnCollection>();
BaseDriverMySql tempWebDb = new BaseDriverMySql(Common.Environment.project.ConnstringWeb);
tempWebDb.BeginTransaction();
tempWebDb.query("SET SQL_SELECT_LIMIT=0");
foreach (string tableName in TableList())
{
DataTable schema = tempWebDb.fetchAll("SELECT * FROM ", dbe.Table(tableName));
res[tableName] = schema.Columns;
}
tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT");
tempWebDb.CommitTransaction();
tempWebDb = null;
foreach (DataRow r in stats.Rows)
{
DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string]; // set ColumnName
string columnType = r["COLUMN_TYPE"] as string;
if (columnType.StartsWith("enum")) // enum type
{
string vals = columnType.Substring(5, columnType.Length - 5 - 1); // the brackets
string[] split = vals.Split(new char[] { ',' });
List <string> EnumValues = new List <string>();
foreach (string s in split)
{
EnumValues.Add(s.Substring(1, s.Length - 2));
}
col.ExtendedProperties.Add(CC.ENUM_COLUMN_VALUES, EnumValues);
}
if (col.DataType == typeof(string))
{
col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
}
string extra = r["EXTRA"] as string; // set AutoIncrement
if (extra == "auto_increment")
{
col.AutoIncrement = true;
}
if (!col.AutoIncrement)
{
col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules...
}
object colDefault = r["COLUMN_DEFAULT"]; // set DefaultValue
// only if there is something sensible has been retrieved from the INFORMATION SCHEMA and no default value has been set
// in the default datatable retrieved in a SELECT
if (!((colDefault is DBNull) || (colDefault.ToString() == String.Empty)) && col.DefaultValue == DBNull.Value)
{
string colDefaltStr = colDefault as string;
if (colDefaltStr == "CURRENT_TIMESTAMP")
{
col.DefaultValue = DBNull.Value; // unneccessary, just for clarity
col.AllowDBNull = true;
}
else if (col.DataType == typeof(string))
{
col.DefaultValue = colDefaltStr;
}
else if (col.DataType == typeof(bool))
{
switch (colDefaltStr)
{
case "0":
col.DefaultValue = false;
break;
case "1":
col.DefaultValue = true;
break;
default:
break;
}
}
else
{
object parsed = null;
if (Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed))
{
col.DefaultValue = parsed;
}
}
}
col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES";
try
{
if (!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull))
{
col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
}
}
catch
{ }
} // for each row in stats
columnTypes = res;
ColumnsToDisplay = new Dictionary <string, List <string> >();
IComparer <DataColumn> comparer = new Common.ColumnDisplayComparer();
foreach (string tableName in columnTypes.Keys)
{
List <DataColumn> innerList = new List <DataColumn>();
foreach (DataColumn col in columnTypes[tableName])
{
innerList.Add(col);
}
innerList.Sort(comparer);
columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList <string>();
}
foreach (string tableName in FKs.Keys)
{
foreach (FK fk in FKs[tableName])
{
columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk;
}
}
}
/// <summary>
/// extracts information from the COLUMNS table of INFORMATION_SCHEMA
/// sets each column`s datatype, default value, nullability, autoincrement, uniqueness
/// and if the datatype is ENUM, sets the extended property as a List<string> labeled Common.Constants.ENUM_COLUMN_VALUES
/// </summary>
private void GetColumnTypes()
{
DataTable stats = driver.fetchAll("SELECT COLUMNS.* FROM COLUMNS JOIN TABLES USING(TABLE_SCHEMA, TABLE_NAME) " +
"WHERE TABLE_TYPE != \"VIEW\" AND TABLES.TABLE_SCHEMA = \"" + webDb +
"\" ORDER BY COLUMNS.TABLE_NAME, ORDINAL_POSITION");
Dictionary<string, DataColumnCollection> res = new Dictionary<string, DataColumnCollection>();
BaseDriverMySql tempWebDb = new BaseDriverMySql(Common.Environment.project.ConnstringWeb);
tempWebDb.BeginTransaction();
tempWebDb.query("SET SQL_SELECT_LIMIT=0");
foreach(string tableName in TableList()){
DataTable schema = tempWebDb.fetchAll("SELECT * FROM ", dbe.Table(tableName));
res[tableName] = schema.Columns;
}
tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT");
tempWebDb.CommitTransaction();
tempWebDb = null;
foreach (DataRow r in stats.Rows) {
DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string]; // set ColumnName
string columnType = r["COLUMN_TYPE"] as string;
if (columnType.StartsWith("enum")) { // enum type
string vals = columnType.Substring(5, columnType.Length - 5 - 1); // the brackets
string[] split = vals.Split(new char[]{','});
List<string> EnumValues = new List<string>();
foreach (string s in split) {
EnumValues.Add(s.Substring(1, s.Length - 2));
}
col.ExtendedProperties.Add(CC.ENUM_COLUMN_VALUES, EnumValues);
}
if (col.DataType == typeof(string)) {
col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
}
string extra = r["EXTRA"] as string; // set AutoIncrement
if (extra == "auto_increment")
col.AutoIncrement = true;
if(!col.AutoIncrement)
col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules...
object colDefault = r["COLUMN_DEFAULT"]; // set DefaultValue
if(!((colDefault is DBNull) || (colDefault.ToString() == String.Empty))){
string colDefaltStr = colDefault as string;
if(colDefaltStr == "CURRENT_TIMESTAMP")
col.ExtendedProperties.Remove(Common.Constants.COLUMN_EDITABLE);
//else{
//object parsed;
//if(Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed)){
// col.DefaultValue = parsed;
//}
//}
}
col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES";
if(!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull)) col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
} // for each row in stats
columnTypes = res;
ColumnsToDisplay = new Dictionary<string, List<string>>();
IComparer<DataColumn> comparer = new Common.ColumnDisplayComparer();
foreach (string tableName in columnTypes.Keys) {
List<DataColumn> innerList = new List<DataColumn>();
foreach (DataColumn col in columnTypes[tableName])
innerList.Add(col);
innerList.Sort(comparer);
columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList<string>();
}
foreach (string tableName in FKs.Keys) {
foreach (FK fk in FKs[tableName]) {
columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk;
}
}
}
