private void GetColumnConstraints(TableMap map)
{
// primary key
string sql = String.Format(selectPrimaryKeyConstraints, map.TableId);
SqlResult sr = broker.Execute(sql);
for (int i = 0; i < sr.Rows.Count; i++)
{
int columnId = sr.GetInt(i, "column_id");
FieldMap fm = map.Fields.FindColumnById(columnId);
if (fm != null)
{
fm.SetIsPrimaryKey(true);
}
}
// foreign key
sql = String.Format(selectForeignKeyConstraints, map.TableId);
sr = broker.Execute(sql);
for (int i = 0; i < sr.Rows.Count; i++)
{
int columnId = sr.GetInt(i, "column_id");
FieldMap fm = map.Fields.FindColumnById(columnId);
if (fm != null)
{
fm.SetForeignKeyTableName(sr.GetString(i, "PT"));
fm.SetForeignKeyColumnName(sr.GetString(i, "PK"));
}
}
}
private void GetForeignKeyData()
{
// get FKs for all tables
DataTable dt = GetForeignKeys(null);
foreach (DataRow row in dt.Rows)
{
// PK_TABLE_CATALOG, PK_TABLE_SCHEMA, PK_TABLE_NAME, FK_TABLE_CATALOG, FK_TABLE_SCHEMA, FK_TABLE_NAME,
// FK_COLUMN_NAME, FK_COLUMN_GUID, FK_COLUMN_PROPID, UPDATE_RULE, DELETE_RULE,
// PK_NAME, FK_NAME, DEFERRABILITY
string fkTableName = (string)row["FK_TABLE_NAME"];
string fkColumnName = (string)row["FK_COLUMN_NAME"];
string pkTableName = (string)row["PK_TABLE_NAME"];
string pkColumnName = (string)row["PK_COLUMN_NAME"];
TableMap map = GetTableMap(fkTableName);
if (map != null)
{
FieldMap fm = map.GetFieldMapFromColumn(fkColumnName);
if (fm != null)
{
fm.SetForeignKeyTableName(pkTableName);
fm.SetForeignKeyColumnName(pkColumnName);
}
}
}
}
private void GetConstraintData(TableMap map)
{
SqlStatement stmt = broker.GetStatement(String.Format(selectConstraints, map.TableName));
stmt.StatementType = StatementType.Select;
SqlResult sr = stmt.Execute();
if (sr.Rows.Count == 1)
{
// returns columns: Name, Type, Row_format, Rows, Avg_row_length, Data_length, Max_data_length,
// Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Create_options,
// Comment (as "InnoDB free: 3072 kB; (ListId) REFER test/List(ListId)"
string comment = sr.GetString(0, 1); // column 1 is either "Create table" or "Create view"
if (comment != null && comment.Length > 5)
{
string[] comments = comment.Split(',');
foreach (string cmt in comments)
{
string tmp = cmt.Trim();
// samples:
// "(Column) REFER database/Table(Column)"
// "(`Column`) REFER `database/Table`(`Column`)"
string pattern = @"\(`?(?<column>\w+)`?\) REFER .*/(?<fkTable>\w+)[`\s]+\(`?(?<fkColumn>\w+)`?\)";
Regex regex = new Regex(pattern, RegexOptions.ExplicitCapture | RegexOptions.Compiled);
Match m = regex.Match(tmp);
if (m.Success)
{
FieldMap fm = map.GetFieldMapFromColumn(m.Groups["column"].Value);
if (fm != null)
{
fm.SetForeignKeyTableName(m.Groups["fkTable"].Value);
fm.SetForeignKeyColumnName(m.Groups["fkColumn"].Value);
}
}
else
{
//CONSTRAINT `fk_employee_type_employee` FOREIGN KEY (`employee_type_id`) REFERENCES `employee_type` (`employee_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
pattern = @"[\s\w]FOREIGN KEY\s\(`?(?<column>\w+)`?\) REFERENCES `?(?<fkTable>\w+)`? \(`?(?<fkColumn>\w+)`?\)[\s\w]+";
Regex regexNew = new Regex(pattern, RegexOptions.ExplicitCapture | RegexOptions.Compiled);
Match mNew = regexNew.Match(tmp);
if (mNew.Success)
{
// string constraintPart = mNew.Groups["constraint"].Value;
FieldMap fm = map.GetFieldMapFromColumn(mNew.Groups["column"].Value);
if (fm != null)
{
fm.SetForeignKeyTableName(mNew.Groups["fkTable"].Value);
fm.SetForeignKeyColumnName(mNew.Groups["fkColumn"].Value);
}
}
else if (tmp != null)
{
int index = tmp.IndexOf("REFER");
if (index > 0)
{
string columnName = ExtractColumn(tmp.Substring(0, index - 1));
tmp = tmp.Substring(index + 5, tmp.Length - index - 5).Trim();
index = tmp.IndexOf("/");
int start = tmp.IndexOf("(");
int end = tmp.IndexOf(")");
if (index > 0 && start > 0 && end > start)
{
string foreignTable = tmp.Substring(index + 1, start - index - 1);
string foreignColumn = tmp.Substring(start + 1, end - start - 1);
FieldMap fm = map.GetFieldMapFromColumn(columnName);
fm.SetForeignKeyTableName(foreignTable);
fm.SetForeignKeyColumnName(foreignColumn);
}
}
}
}
}
}
}
}
