SimpleForeignKeyDetector { public static List<SimpleForeignKeyDetector.ForeignKeyCandidate> detect( ConstraintCollection<InclusionDependency> indCC, Collection<ConstraintCollection<UniqueColumnCombination>> uniqueCCs, Collection<ConstraintCollection<TupleCount>> tupleCountCCs, Collection<ConstraintCollection<ColumnStatistics>> statsCCs, Collection<ConstraintCollection<TextColumnStatistics>> textStatsCCs, boolean isNeglectEmptyTables) { final MetadataStore metadataStore = indCC.getMetadataStore(); final IdUtils idUtils = metadataStore.getIdUtils(); List<UniqueColumnCombination> uccs = (uniqueCCs == null || uniqueCCs.isEmpty()) ? null : uniqueCCs.stream() .flatMap(c -> c.getConstraints().stream()) .collect(Collectors.toList()); List<TupleCount> tupleCounts = (tupleCountCCs == null || tupleCountCCs.isEmpty()) ? null : tupleCountCCs.stream() .flatMap(c -> c.getConstraints().stream()) .collect(Collectors.toList()); List<ColumnStatistics> stats = (statsCCs == null || statsCCs.isEmpty()) ? null : statsCCs.stream() .flatMap(c -> c.getConstraints().stream()) .collect(Collectors.toList()); List<TextColumnStatistics> textStats = (textStatsCCs == null || textStatsCCs.isEmpty()) ? null : textStatsCCs.stream() .flatMap(c -> c.getConstraints().stream()) .collect(Collectors.toList()); logger.info("Detecting not-null columns..."); IntSet nullableColumns = new IntOpenHashSet(); if (stats != null) stats.stream() .filter(columnStatistics -> columnStatistics.getNumNulls() > 0) .forEach(columnStatistics -> nullableColumns.add(columnStatistics.getColumnId())); logger.info("Identifying PK candidates..."); final Map<Integer, List<UniqueColumnCombination>> tableUccs = (uccs == null || uccs.isEmpty()) ? null : uccs.stream() .filter(ucc -> Arrays.stream(ucc.getColumnIds()).noneMatch(nullableColumns::contains)) .collect(Collectors.groupingBy( ucc -> idUtils.getTableId(ucc.getAllTargetIds()[0]), Collectors.toList())); final IntSet nonEmptyTableIds = new IntOpenHashSet(); if (isNeglectEmptyTables && tupleCounts != null) { logger.info("Loading tuple counts..."); tupleCounts.stream() .filter(tupleCount -> tupleCount.getNumTuples() > 0) .forEach(tupleCount -> nonEmptyTableIds.add(tupleCount.getTableId())); logger.info("Found {} non-empty tables.", nonEmptyTableIds.size()); } logger.info("Filter PK-referencing, non-empty INDs..."); final List<InclusionDependency> relevantInds = indCC.getConstraints().stream() .filter(ind -> { if (!isNeglectEmptyTables) return true; final int depTableId = idUtils.getTableId(ind.getDependentColumnIds()[0]); final int refTableId = idUtils.getTableId(ind.getReferencedColumnIds()[0]); return nonEmptyTableIds.contains(depTableId) && nonEmptyTableIds.contains(refTableId); }) .filter(ind -> { if (tableUccs == null) return true; final int[] refColumnIds = ind.getReferencedColumnIds(); final int tableId = idUtils.getTableId(refColumnIds[0]); final List<UniqueColumnCombination> tableUccList = tableUccs.get(tableId); return tableUccList != null && tableUccList.stream() .anyMatch(ucc -> uccIsReferenced(ucc, ind, true)); }) .collect(Collectors.toList()); logger.info("Detected {} relevant INDs from {} INDs overall.", relevantInds.size(), indCC.getConstraints().size()); logger.info("Creating and running the partial classifiers..."); final Map<UnaryForeignKeyCandidate, ClassificationSet> fkCandidateClassificationSet = relevantInds.stream() .flatMap(SimpleForeignKeyDetector::splitIntoUnaryForeignKeyCandidates) .distinct() .map(ClassificationSet::new) .collect(Collectors.toMap( ClassificationSet::getForeignKeyCandidate, Function.identity() )); final List<PartialForeignKeyClassifier> partialClassifiers = new LinkedList<>(); if (stats != null) { partialClassifiers.add(new CoverageClassifier(1d, 0.9d, 0.9d, stats)); } if (textStats != null) { partialClassifiers.add(new ValueDiffClassifier(1d, textStats, 0.9, 0.5)); partialClassifiers.add(new ValueDiffClassifier(Double.NaN, textStats, 0.01, 0.3)); } double tndWeight = 1.5d / 3; partialClassifiers.add(new TableNameDiffClassifier(tndWeight, 3, metadataStore)); partialClassifiers.add(new TableNameDiffClassifier(tndWeight, 5, metadataStore)); partialClassifiers.add(new TableNameDiffClassifier(tndWeight, 7, metadataStore)); double srtnWeight = 1.5d / 6; partialClassifiers.add(new ShortReferencedTableNameClassifier(srtnWeight, 5, metadataStore)); partialClassifiers.add(new ShortReferencedTableNameClassifier(srtnWeight, 7, metadataStore)); partialClassifiers.add(new ShortReferencedTableNameClassifier(srtnWeight, 9, metadataStore)); partialClassifiers.add(new ShortReferencedTableNameClassifier(srtnWeight, 11, metadataStore)); partialClassifiers.add(new ShortReferencedTableNameClassifier(srtnWeight, 13, metadataStore)); partialClassifiers.add(new ShortReferencedTableNameClassifier(srtnWeight, 15, metadataStore)); partialClassifiers.forEach(classifier -> classifier.classify(fkCandidateClassificationSet.values())); final List<SimpleForeignKeyDetector.ForeignKeyCandidate> indRatings = relevantInds.stream() .map(ind -> { double indScore = splitIntoUnaryForeignKeyCandidates(ind) .map(fkCandidateClassificationSet::get) .mapToDouble(ClassificationSet::getOverallScore) .average().orElse(0d); final Map<UnaryForeignKeyCandidate, List<ClassificationSet>> reasoning = splitIntoUnaryForeignKeyCandidates(ind) .map(fkCandidateClassificationSet::get) .collect(Collectors.groupingBy(ClassificationSet::getForeignKeyCandidate)); return new SimpleForeignKeyDetector.ForeignKeyCandidate(ind, indScore, reasoning); }) .sorted((rating1, rating2) -> Double.compare(rating2.score, rating1.score)) .collect(Collectors.toList()); logger.info("Picking the best INDs as FKs..."); Int2ObjectMap<IntSet> tablePrimaryKeys = new Int2ObjectOpenHashMap<>(); IntSet usedDependentColumns = new IntOpenHashSet(); return indRatings.stream() .filter(indRating -> { if (Arrays.stream(indRating.ind.getDependentColumnIds()) .anyMatch(usedDependentColumns::contains)) return false; final int tableId = idUtils.getTableId(indRating.ind.getReferencedColumnIds()[0]); final IntSet refTablePK = tablePrimaryKeys.get(tableId); if (refTablePK != null && (refTablePK.size() != indRating.ind.getArity() || !Arrays.stream(indRating.ind.getReferencedColumnIds()) .allMatch(refTablePK::contains))) { return false; } Arrays.stream(indRating.ind.getDependentColumnIds()) .forEach(usedDependentColumns::add); if (refTablePK == null) { tablePrimaryKeys.put( tableId, new IntOpenHashSet(indRating.ind.getReferencedColumnIds())); } return true; }) .collect(Collectors.toList()); }  static List<SimpleForeignKeyDetector.ForeignKeyCandidate> detect(
            ConstraintCollection<InclusionDependency> indCC,
            Collection<ConstraintCollection<UniqueColumnCombination>> uniqueCCs,
            Collection<ConstraintCollection<TupleCount>> tupleCountCCs,
            Collection<ConstraintCollection<ColumnStatistics>> statsCCs,
            Collection<ConstraintCollection<TextColumnStatistics>> textStatsCCs,
            boolean isNeglectEmptyTables);  }
@Test public void testDetectDoesNotFail() { MetadataStore metadataStore = new DefaultMetadataStore(); Schema schema = metadataStore.addSchema("schema", null, new DefaultLocation()); for (String tableName : Arrays.asList("table1", "table2")) { Table table = schema.addTable(metadataStore, tableName, null, new DefaultLocation()); for (int i = 0; i < 10; i++) { table.addColumn(metadataStore, String.format("column%d", i), null, i); } } ConstraintCollection<InclusionDependency> indCC = metadataStore.createConstraintCollection( "inds", InclusionDependency.class, schema ); indCC.add(new InclusionDependency( schema.getTargetByName("table2.column1").getId(), schema.getTargetByName("table1.column1").getId() )); indCC.add(new InclusionDependency( schema.getTargetByName("table2.column2").getId(), schema.getTargetByName("table1.column1").getId() )); indCC.add(new InclusionDependency( schema.getTargetByName("table2.column2").getId(), schema.getTargetByName("table1.column5").getId() )); indCC.add(new InclusionDependency( schema.getTargetByName("table2.column5").getId(), schema.getTargetByName("table1.column5").getId() )); ConstraintCollection<UniqueColumnCombination> uniqueCC = metadataStore.createConstraintCollection( "uccs", UniqueColumnCombination.class, schema ); uniqueCC.add(new UniqueColumnCombination(new int[]{schema.getTargetByName("table1.column1").getId()})); uniqueCC.add(new UniqueColumnCombination(new int[]{schema.getTargetByName("table1.column2").getId()})); uniqueCC.add(new UniqueColumnCombination(new int[]{schema.getTargetByName("table1.column5").getId()})); ConstraintCollection<TupleCount> tupleCountCC = metadataStore.createConstraintCollection( "#tuples", TupleCount.class, schema ); tupleCountCC.add(new TupleCount(schema.getTableByName("table1").getId(), 1_000_000)); tupleCountCC.add(new TupleCount(schema.getTableByName("table2").getId(), 100_000)); ConstraintCollection<ColumnStatistics> statsCC = metadataStore.createConstraintCollection( "stats", ColumnStatistics.class, schema ); ConstraintCollection<TextColumnStatistics> textStatsCC = metadataStore.createConstraintCollection( "text stats", TextColumnStatistics.class, schema ); for (String tableName : Arrays.asList("table1", "table2")) { Table table = schema.getTableByName(tableName); for (int i = 0; i < 10; i++) { Column column = table.getColumnByName(String.format("column%d", i)); ColumnStatistics columnStatistics = new ColumnStatistics(column.getId()); columnStatistics.setNumDistinctValues(-1); columnStatistics.setNumNulls(0); columnStatistics.setFillStatus(1); statsCC.add(columnStatistics); TextColumnStatistics textColumnStatistics = new TextColumnStatistics(column.getId()); textColumnStatistics.setShortestValue("bla"); textColumnStatistics.setLongestValue("blubb"); textStatsCC.add(textColumnStatistics); } } List<SimpleForeignKeyDetector.ForeignKeyCandidate> fkCandidates = SimpleForeignKeyDetector.detect( indCC, Collections.singleton(uniqueCC), Collections.singleton(tupleCountCC), Collections.singleton(statsCC), Collections.singleton(textStatsCC), false ); DependencyPrettyPrinter prettyPrinter = new DependencyPrettyPrinter(metadataStore); for (SimpleForeignKeyDetector.ForeignKeyCandidate fkCandidate : fkCandidates) { System.out.printf("* %s\n", fkCandidate.explain(prettyPrinter)); } }