| | | 1 | | using System.Text; |
| | | 2 | | using ValidateLib.ErrorsAndWarnings.Errors; |
| | | 3 | | using ValidateLib.ErrorsAndWarnings.Errors.ValidationErrors; |
| | | 4 | | using ValidateLib.Metadata.Descriptors; |
| | | 5 | | using ValidateLib.Metadata.Validators; |
| | | 6 | | using ValidateLib.TabularData.AnnotatedTabularDataModel; |
| | | 7 | | |
| | | 8 | | namespace ValidateLib.TabularData.Validation.ValidationRules |
| | | 9 | | { |
| | | 10 | | public class FKReferencedValuesMustMatchValidationRule : IValidationRule |
| | | 11 | | { |
| | | 12 | | // validates that the values in the referencing columns match referenced values in the other table |
| | | 13 | | // also validates that the combination of referencing cells are referencing a unique row in referenced table |
| | | 14 | | public List<Error> ValidateReferencedValuesFK(List<Table> tables, TableGroupDescriptor tableGroupDescriptor) |
| | | 15 | | { |
| | 1 | 16 | | List<Error> errors = new List<Error>(); |
| | 1 | 17 | | foreach (var table in tables) |
| | | 18 | | { |
| | 1 | 19 | | if (table.foreignKeys is not null && table.foreignKeys.Count > 0) |
| | | 20 | | { |
| | 1 | 21 | | ResolveReferencedValuesForOneTable(table, tableGroupDescriptor, errors); |
| | | 22 | | } |
| | | 23 | | } |
| | 1 | 24 | | return errors; |
| | | 25 | | } |
| | | 26 | | |
| | | 27 | | private void ResolveReferencedValuesForOneTable(Table referencingTable, TableGroupDescriptor tableGroupDescripto |
| | | 28 | | { |
| | 1 | 29 | | foreach (var FKDescriptor in referencingTable.foreignKeys!) |
| | | 30 | | { |
| | 1 | 31 | | ResolveReferencedValuesForOneTableOneFK(referencingTable, tableGroupDescriptor, FKDescriptor, errors); |
| | | 32 | | } |
| | 1 | 33 | | } |
| | | 34 | | |
| | | 35 | | private void ResolveReferencedValuesForOneTableOneFK(Table referencingTable, TableGroupDescriptor tableGroupDesc |
| | | 36 | | { |
| | | 37 | | // these are the values that need to be unique inside the referenced table |
| | | 38 | | // they are imploded to one string to conserve less space |
| | 1 | 39 | | Dictionary<string, bool> referencingValues = GetReferencingValues(referencingTable, FKDescriptor); |
| | 1 | 40 | | TableDescriptor referencedTableDescriptor = ForeignKeyValidator.FindReferencedTable(tableGroupDescriptor, FK |
| | 1 | 41 | | Table referencedTable = referencedTableDescriptor.table!; |
| | 1 | 42 | | VerifyTheReferencingValuesExist(referencingTable, referencedTable, referencingValues, FKDescriptor, errors); |
| | | 43 | | |
| | 1 | 44 | | } |
| | | 45 | | |
| | | 46 | | private Dictionary<string, bool> GetReferencingValues(Table referencingTable, ForeignKeyDescriptor FKDescriptor) |
| | | 47 | | { |
| | 1 | 48 | | Dictionary<string, bool> referencingValues = new Dictionary<string, bool>(); |
| | | 49 | | |
| | 1 | 50 | | int maxRows = 0; |
| | 1 | 51 | | foreach (var column in referencingTable.columns) |
| | | 52 | | { |
| | 1 | 53 | | maxRows = Math.Max(maxRows, column.cells.Count); |
| | | 54 | | } |
| | | 55 | | |
| | 1 | 56 | | for (int rowNumber = 0; rowNumber < maxRows; rowNumber++) |
| | | 57 | | { |
| | 1 | 58 | | StringBuilder referencingValue = new StringBuilder(); |
| | | 59 | | |
| | 1 | 60 | | foreach (var column in referencingTable.columns) |
| | | 61 | | { |
| | 1 | 62 | | if (column.name is null) |
| | | 63 | | continue; |
| | | 64 | | |
| | 1 | 65 | | if (FKDescriptor.columnReference!._value!.Contains(column.name)) |
| | | 66 | | { |
| | 1 | 67 | | referencingValue.Append(column.cells[rowNumber].stringValue); |
| | | 68 | | } |
| | | 69 | | } |
| | 1 | 70 | | referencingValues[referencingValue.ToString()] = true; |
| | | 71 | | } |
| | 1 | 72 | | return referencingValues; |
| | | 73 | | } |
| | | 74 | | |
| | | 75 | | private void VerifyTheReferencingValuesExist |
| | | 76 | | ( |
| | | 77 | | Table referencingTable, |
| | | 78 | | Table referencedTable, |
| | | 79 | | Dictionary<string, bool> referencingValues, |
| | | 80 | | ForeignKeyDescriptor FKDescriptor, |
| | | 81 | | List<Error> errors |
| | | 82 | | ) |
| | | 83 | | { |
| | 1 | 84 | | int maxRows = 0; |
| | 1 | 85 | | foreach (var column in referencedTable.columns) |
| | | 86 | | { |
| | 1 | 87 | | maxRows = Math.Max(maxRows, column.cells.Count); |
| | | 88 | | } |
| | 1 | 89 | | int numberOfKeys = referencingValues.Count; |
| | | 90 | | |
| | 1 | 91 | | for (int rowNumber = 0; rowNumber < maxRows; rowNumber++) |
| | | 92 | | { |
| | | 93 | | |
| | 1 | 94 | | string referencedValueStr = GetReferencedValue(referencedTable, FKDescriptor, rowNumber); |
| | 1 | 95 | | if (referencingValues.ContainsKey(referencedValueStr)) |
| | | 96 | | { |
| | 1 | 97 | | if (referencingValues[referencedValueStr] == true) |
| | | 98 | | { |
| | 1 | 99 | | numberOfKeys--; |
| | 1 | 100 | | referencingValues[referencedValueStr] = false; |
| | | 101 | | } |
| | | 102 | | else |
| | | 103 | | { |
| | 1 | 104 | | errors.Add(ErrorFactory.GetDuplicateInFKReferencedColumnsValidationError(referencedTable, GetRef |
| | | 105 | | } |
| | | 106 | | } |
| | | 107 | | |
| | | 108 | | |
| | | 109 | | } |
| | | 110 | | |
| | 1 | 111 | | if (numberOfKeys > 0) |
| | | 112 | | { |
| | 1 | 113 | | errors.Add(ErrorFactory.GetNotAllReferencedValuesExistValidationError(referencingTable, referencedTable, |
| | | 114 | | } |
| | 1 | 115 | | } |
| | | 116 | | |
| | | 117 | | string GetReferencedValue(Table referencedTable, ForeignKeyDescriptor FKDescriptor, int rowNumber, string separa |
| | | 118 | | { |
| | 1 | 119 | | StringBuilder referencedValue = new StringBuilder(); |
| | | 120 | | |
| | 1 | 121 | | foreach (var column in referencedTable.columns) |
| | | 122 | | { |
| | 1 | 123 | | if (column.name is null) |
| | | 124 | | continue; |
| | | 125 | | |
| | 1 | 126 | | if (FKDescriptor!.reference!._value!.columnReference!._value!.Contains(column.name)) |
| | | 127 | | { |
| | 1 | 128 | | referencedValue.Append(separator + column.cells[rowNumber].stringValue); |
| | | 129 | | } |
| | | 130 | | } |
| | | 131 | | |
| | 1 | 132 | | return referencedValue.ToString(); |
| | | 133 | | } |
| | | 134 | | } |
| | | 135 | | } |