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); } } } } } } } }