override internal void LoadForTable()
{
string query = @"SHOW COLUMNS FROM `" + this.Table.Name + "`";
DataTable metaData = new DataTable();
DbDataAdapter adapter = MySql5Databases.CreateAdapter(query, this.dbRoot.ConnectionString);
adapter.Fill(metaData);
if (metaData.Columns.Contains("Extra"))
{
f_Extra = metaData.Columns["Extra"];
}
metaData.Columns["Field"].ColumnName = "COLUMN_NAME";
metaData.Columns["Type"].ColumnName = "DATA_TYPE";
metaData.Columns["Null"].ColumnName = "IS_NULLABLE";
metaData.Columns["Default"].ColumnName = "COLUMN_DEFAULT";
PopulateArray(metaData);
LoadTableColumnDescriptions();
}
private void LoadTableDescriptions()
{
try
{
string query = @"SELECT TABLE_NAME, TABLE_COMMENT, CREATE_TIME, UPDATE_TIME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + this.Database.Name + "'";
DataTable metaData = new DataTable();
DbDataAdapter adapter = MySql5Databases.CreateAdapter(query, this.dbRoot.ConnectionString);
adapter.Fill(metaData);
if (this.Database.Tables.Count > 0)
{
Table t = this.Database.Tables[0] as Table;
if (!t._row.Table.Columns.Contains("DESCRIPTION"))
{
t._row.Table.Columns.Add("DESCRIPTION", Type.GetType("System.String"));
this.f_Description = t._row.Table.Columns["DESCRIPTION"];
}
if (!t._row.Table.Columns.Contains("TABLE_SCHEMA"))
{
t._row.Table.Columns.Add("TABLE_SCHEMA", Type.GetType("System.String"));
this.f_Schema = t._row.Table.Columns["TABLE_SCHEMA"];
}
if (!t._row.Table.Columns.Contains("DATE_CREATED"))
{
t._row.Table.Columns.Add("DATE_CREATED", Type.GetType("System.DateTime"));
this.f_DateCreated = t._row.Table.Columns["DATE_CREATED"];
}
if (!t._row.Table.Columns.Contains("DATE_MODIFIED"))
{
t._row.Table.Columns.Add("DATE_MODIFIED", Type.GetType("System.DateTime"));
this.f_DateModified = t._row.Table.Columns["DATE_MODIFIED"];
}
}
if (metaData.Rows.Count > 0)
{
foreach (DataRow row in metaData.Rows)
{
Table t = this[row["TABLE_NAME"] as string] as Table;
t._row["DESCRIPTION"] = row["TABLE_COMMENT"] as string;
t._row["TABLE_SCHEMA"] = this.Database.Name;
if (row["CREATE_TIME"] != DBNull.Value)
{
t._row["DATE_CREATED"] = (DateTime)row["CREATE_TIME"];
}
if (row["UPDATE_TIME"] != DBNull.Value)
{
t._row["DATE_MODIFIED"] = (DateTime)row["UPDATE_TIME"];
}
}
}
}
catch {}
}
internal void AddMyHalf()
{
string query = @"SHOW CREATE TABLE `" + this.Table.Name + "`";
DataTable dt = new DataTable();
DbDataAdapter adapter = MySql5Databases.CreateAdapter(query, this.dbRoot.ConnectionString);
adapter.Fill(dt);
string text = dt.Rows[0][1] as string;
// CONSTRAINT `FK_mastertypes_3` FOREIGN KEY (`TheINT1`, `TheINT2`) REFERENCES `employee` (`TheInt1`, `TheInt2`),
// CONSTRAINT `FK_mastertypes_1` FOREIGN KEY (`MyPK`) REFERENCES `employee` (`EmployeeID`),
// CONSTRAINT `FK_mastertypes_2` FOREIGN KEY (`TheVARCHAR`) REFERENCES `employee` (`LastName`)
// CONSTRAINT `ShippersOrders` FOREIGN KEY (`ShipVia`) REFERENCES `shippers` (`ShipperID`) ON DELETE NO ACTION ON UPDATE NO ACTION\n)
DataTable metaData = new DataTable();
metaData.Columns.Add("PK_TABLE_CATALOG");
metaData.Columns.Add("PK_TABLE_SCHEMA");
metaData.Columns.Add("PK_TABLE_NAME");
metaData.Columns.Add("FK_TABLE_CATALOG");
metaData.Columns.Add("FK_TABLE_SCHEMA");
metaData.Columns.Add("FK_TABLE_NAME");
metaData.Columns.Add("ORDINAL");
metaData.Columns.Add("UPDATE_RULE");
metaData.Columns.Add("DELETE_RULE");
metaData.Columns.Add("PK_NAME");
metaData.Columns.Add("FK_NAME");
metaData.Columns.Add("DEFERRABILITY");
metaData.Columns.Add("PK_COLUMN_NAME");
metaData.Columns.Add("FK_COLUMN_NAME");
string s = "";
string[] fkRec = null;
string[] pkColumns = null;
string[] fkColumns = null;
int iStart = 0;
while (true)
{
iStart = text.IndexOf("CONSTRAINT", iStart);
if (iStart == -1)
{
break;
}
int iEnd = text.IndexOf('\n', iStart);
string fk = text.Substring(iStart, iEnd - iStart);
iStart = iEnd + 2;
if (-1 != fk.IndexOf("FOREIGN KEY"))
{
// MySQL 5.0 trick !!
int index = fk.IndexOf(")");
index = fk.IndexOf(")", index + 1);
s = fk.Substring(0, index);
//
// Munge it down it a record I can split with a ',' seperator
s = s.Replace("`", "");
s = s.Replace(" ", "");
s = s.Replace("),", "");
s = s.Replace(",", "|");
s = s.Replace("CONSTRAINT", "");
s = s.Replace("FOREIGNKEY", "");
s = s.Replace("REFERENCES", ",");
s = s.Replace("(", ",");
s = s.Replace(")", "");
fkRec = s.Split(',');
fkColumns = fkRec[1].Split('|');
pkColumns = fkRec[3].Split('|');
for (int i = 0; i < pkColumns.Length; i++)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["PK_TABLE_CATALOG"] = this.Table.Database.Name;
row["FK_TABLE_CATALOG"] = this.Table.Database.Name;
row["PK_TABLE_NAME"] = fkRec[2];
row["FK_TABLE_NAME"] = this.Table.Name;
row["FK_NAME"] = fkRec[0];
row["PK_COLUMN_NAME"] = pkColumns[i];
row["FK_COLUMN_NAME"] = fkColumns[i];
row["ORDINAL"] = i;
// ON DELETE NO ACTION ON UPDATE NO ACTION\n)
try
{
row["DELETE_RULE"] = "RESTRICT";
int ond = fk.IndexOf("ON DELETE");
if (-1 != ond)
{
char c = fk[ond + 10];
switch (c)
{
case 'R':
case 'r':
row["DELETE_RULE"] = "RESTRICT";
break;
case 'C':
case 'c':
row["DELETE_RULE"] = "CASCADE";
break;
case 'S':
case 's':
row["DELETE_RULE"] = "SET NULL";
break;
case 'N':
case 'n':
row["DELETE_RULE"] = "NO ACTION";
break;
}
}
row["UPDATE_RULE"] = "RESTRICT";
int onu = fk.IndexOf("ON UPDATE");
if (-1 != onu)
{
char c = fk[onu + 10];
switch (c)
{
case 'R':
case 'r':
row["UPDATE_RULE"] = "RESTRICT";
break;
case 'C':
case 'c':
row["UPDATE_RULE"] = "CASCADE";
break;
case 'S':
case 's':
row["UPDATE_RULE"] = "SET NULL";
break;
case 'N':
case 'n':
row["UPDATE_RULE"] = "NO ACTION";
break;
}
}
}
catch {}
}
}
}
PopulateArray(metaData);
}
