public static List <Anomaly> detectAnomaliesWithNoData() { List <Anomaly> anomalies = new List <Anomaly>(); List <ForeignKey> foreignKeys = ForeignKey.getAllForeignKeys(); List <TableDB> tables = TableDB.getAllTables(); List <PrimaryKey> primaryKeys = PrimaryKey.getAllPrimaryKeys(); // Rule#1: Una tabla no posee una relación con ninguna otra tabla (está solitaria), y posiblemente exista una relación cortada for (int index_table = 0; index_table < tables.Count; index_table++) { bool table_has_foreign_key_of_other = false; bool table_is_a_foreign_key_in_other = false; for (int index_fk = 0; index_fk < foreignKeys.Count; index_fk++) { if (foreignKeys[index_fk].parent_object_id == tables[index_table].object_id) { table_has_foreign_key_of_other = true; } if (foreignKeys[index_fk].referenced_object_id == tables[index_table].object_id) { table_is_a_foreign_key_in_other = true; } } if (!table_has_foreign_key_of_other && !table_is_a_foreign_key_in_other) { // table is alone and not conected string extra_summary = Trigger.getExtraSummary(tables[index_table].object_id); anomalies.Add(new Anomaly(tables[index_table].object_id, Anomaly.TYPE_NO_DATA, tables[index_table].name + "|" + tables[index_table].object_id + ": tabla aislada, posible anomalía." + extra_summary)); } } // Rule#2: Una tabla no posee clave primaria pero si posee clave foránea de otra tabla. // ¿Es así o se requiere una clave primaria? List <TableDB> tablesWithPrimaryKey = new List <TableDB>(); List <TableDB> tablesWithOutPrimaryKey = new List <TableDB>(); for (int index_table = 0; index_table < tables.Count; index_table++) { for (int index_pk = 0; index_pk < primaryKeys.Count; index_pk++) { if (primaryKeys[index_pk].parent_object_id == tables[index_table].object_id) { tablesWithPrimaryKey.Add(tables[index_table]); } } } for (int index_table = 0; index_table < tables.Count; index_table++) { if (!tablesWithPrimaryKey.Contains(tables[index_table])) { tablesWithOutPrimaryKey.Add(tables[index_table]); } } for (int index_table_no_pk = 0; index_table_no_pk < tablesWithOutPrimaryKey.Count; index_table_no_pk++) { bool table_has_foreign_key_of_other = false; bool table_is_a_foreign_key_in_other = false; for (int index_fk = 0; index_fk < foreignKeys.Count; index_fk++) { if (foreignKeys[index_fk].parent_object_id == tablesWithOutPrimaryKey[index_table_no_pk].object_id) { table_has_foreign_key_of_other = true; } if (foreignKeys[index_fk].referenced_object_id == tablesWithOutPrimaryKey[index_table_no_pk].object_id) { table_is_a_foreign_key_in_other = true; } } if (table_has_foreign_key_of_other) { string extra_summary = Trigger.getExtraSummary(tablesWithOutPrimaryKey[index_table_no_pk].object_id); // table has FK but this FK are not primary key anomalies.Add(new Anomaly(tablesWithOutPrimaryKey[index_table_no_pk].object_id, Anomaly.TYPE_NO_DATA, tablesWithOutPrimaryKey[index_table_no_pk].name + "|" + tablesWithOutPrimaryKey[index_table_no_pk].object_id + ": tabla con FK pero sin PK. ¿Esto es a propósito o un error?\n" + extra_summary)); } if (table_is_a_foreign_key_in_other) { string extra_summary = Trigger.getExtraSummary(tablesWithOutPrimaryKey[index_table_no_pk].object_id); // table has FK but this FK are not primary key anomalies.Add(new Anomaly(tablesWithOutPrimaryKey[index_table_no_pk].object_id, Anomaly.TYPE_NO_DATA, tablesWithOutPrimaryKey[index_table_no_pk].name + "|" + tablesWithOutPrimaryKey[index_table_no_pk].object_id + ": Gran error, la tabla no tiene PK y es FK en otra tabla\n" + extra_summary)); } } Console.WriteLine(tablesWithOutPrimaryKey.Count); return(anomalies); }
public static List <Anomaly> detectAnomaliesWithData() { List <Anomaly> anomalies = new List <Anomaly>(); List <ForeignKey> foreignKeys = ForeignKey.getAllForeignKeys(); List <PrimaryKey> primaryKeys = PrimaryKey.getAllPrimaryKeys(); for (int i = 0; i < foreignKeys.Count; i++) { SqlCommand sqlCommand; SqlDataReader dataReader; SqlConnection connection = MyDB.getConnection(); try { connection.Open(); string sql = "DBCC CHECKCONSTRAINTS('" + foreignKeys[i].name + "')"; sqlCommand = new SqlCommand(sql, connection); dataReader = sqlCommand.ExecuteReader(); string output = "ForeignKey: "; bool exist_data = false; while (dataReader.Read()) { int number_fields = dataReader.FieldCount; for (int j = 0; j < number_fields; j++) { output = output + dataReader.GetValue(j); exist_data = true; if (j != number_fields - 1) { output = output + "|"; } } output = output + "\n"; } if (exist_data) { anomalies.Add(new Anomaly(foreignKeys[i].parent_object_id, Anomaly.TYPE_WITH_DATA, output)); // Console.WriteLine("\nANOMALLY DBCC"); // Console.WriteLine(output); } sqlCommand.Dispose(); dataReader.Close(); } catch (Exception error) { Console.WriteLine("Error: " + error.StackTrace); } finally { connection.Close(); } } for (int i = 0; i < primaryKeys.Count; i++) { SqlCommand sqlCommand; SqlDataReader dataReader; SqlConnection connection = MyDB.getConnection(); try { connection.Open(); string sql = "DBCC CHECKCONSTRAINTS('" + primaryKeys[i].name + "')"; sqlCommand = new SqlCommand(sql, connection); dataReader = sqlCommand.ExecuteReader(); string output = "PrimaryKey: "; bool exist_data = false; while (dataReader.Read()) { int number_fields = dataReader.FieldCount; for (int j = 0; j < number_fields; j++) { output = output + dataReader.GetValue(j); exist_data = true; if (j != number_fields - 1) { output = output + "|"; } } output = output + "\n"; } if (exist_data) { anomalies.Add(new Anomaly(primaryKeys[i].parent_object_id, Anomaly.TYPE_WITH_DATA, output)); // Console.WriteLine("\nANOMALLY DBCC"); // Console.WriteLine(output); } sqlCommand.Dispose(); dataReader.Close(); } catch (Exception error) { Console.WriteLine("Error: " + error.StackTrace); } finally { connection.Close(); } } //TODO: agregar checkconstraints return(anomalies); }