findTableDuplicates method

TableDuplicates findTableDuplicates(TableScheme scheme, { EntityHandler? entityHandler, })

The duplicated indexes (over the same columns, in the same order, of the same kind: TableScheme.indexesKinds) and enum CHECK constraints (over the same column) of scheme: each group with the one to keep first, and the suggested SQLs to drop the others.

• The index kept: the primary key (TableScheme.primaryKeyIndex; if unknown, by its name: PRIMARY, <table>_pkey) or a SQLite constraint's index (sqlite_autoindex_*: can't be dropped), else a unique one (the default name, <table>__<columns>__unique, first), else the default name (<table>__<columns>__idx), else the first by name.
• The CHECK kept: the one with exactly the values of entityHandler's enum field, else one with all of them (and others), else a default name (PostgreSQL's <table>_<column>_check, or the <table>_<column>__check added by checkTables' suggestion), else the first by name.

Implementation

TableDuplicates findTableDuplicates(
  TableScheme scheme, {
  EntityHandler? entityHandler,
}) {
  var table = scheme.name;
  var indexes = scheme.indexes ?? const <String, List<String>>{};
  var uniqueIndexes = scheme.uniqueIndexes ?? const <String>{};
  var primaryKeyIndex = scheme.primaryKeyIndex;

  bool isPrimaryKey(String name) => primaryKeyIndex != null
      ? name == primaryKeyIndex
      : name == 'PRIMARY' || name == '${table}_pkey';

  int indexRank(String name) {
    // The primary key, or a constraint's index (SQLite: can't be dropped):
    if (isPrimaryKey(name) || name.startsWith('sqlite_autoindex_')) {
      return 0;
    }
    var unique = uniqueIndexes.contains(name);
    var isDefaultName =
        name ==
        EntityIndex.defaultName(
          table,
          indexes[name] ?? const <String>[],
          maxLength: dialect.maxIdentifierLength,
          unique: unique,
        );
    if (unique) return isDefaultName ? 1 : 2;
    return isDefaultName ? 3 : 4;
  }

  var dropSQLs = <String>[];

  // MySQL `SERIAL` (`... AUTO_INCREMENT UNIQUE`): with `PRIMARY KEY`, the
  // `PRIMARY` key and a unique index over its column, named as it. Of the
  // generated `CREATE TABLE` itself: not reported.
  bool isSerialUniqueIndex(List<String> group, String name) {
    if (!group.any(isPrimaryKey) ||
        isPrimaryKey(name) ||
        !uniqueIndexes.contains(name)) {
      return false;
    }
    var columns = indexes[name];
    return columns != null && columns.length == 1 && columns[0] == name;
  }

  var duplicatedIndexes = <List<String>>[];
  for (var group in scheme.duplicatedIndexes ?? const <List<String>>[]) {
    var reported = group
        .whereNot((name) => isSerialUniqueIndex(group, name))
        .toList();
    if (reported.length < 2) continue;

    var names = _sortedByRank(reported, indexRank);
    duplicatedIndexes.add(names);

    dropSQLs.add(
      '-- `$table` (${indexes[names.first]?.join(', ')}): '
      'keeping `${names.first}`:',
    );

    for (var name in names.skip(1)) {
      if (name.startsWith('sqlite_autoindex_')) {
        dropSQLs.add("-- `$name`: a constraint's index: can't be dropped");
        continue;
      }

      if (uniqueIndexes.contains(name)) {
        dropSQLs.add(
          "-- `$name` is unique: if a foreign key references it, the drop "
          "fails (drop the foreign key first; `CASCADE` drops it too)",
        );
        // A constraint's index can only be dropped with the constraint:
        if (!dialect.dropIndexOnTable && dialect.alterTableAddConstraint) {
          dropSQLs.add(generateDropConstraintSQL(table, name));
        }
      }

      dropSQLs.add(generateDropIndexSQL(table, name));
    }
  }

  var checkConstraints =
      scheme.checkConstraints ?? const <String, TableEnumConstraint>{};

  var schemeToRepoMap = entityHandler == null
      ? null
      : _schemeToRepoFieldsMap(scheme, entityHandler);

  var duplicatedChecks = <String, List<String>>{};
  for (var e
      in (scheme.duplicatedCheckConstraints ?? const <String, List<String>>{})
          .entries) {
    var column = e.key;

    var entityValues = entityHandler == null
        ? null
        : _columnEnumValues(entityHandler, column, schemeToRepoMap!);

    bool hasAllValues(String name) {
      var values = checkConstraints[name]?.values;
      return entityValues != null &&
          values != null &&
          values.containsAll(entityValues);
    }

    int checkRank(String name) {
      if (hasAllValues(name)) {
        var values = checkConstraints[name]!.values;
        return values.length == entityValues!.length ? 0 : 1;
      }
      var lc = name.toLowerCase();
      var prefix = '${table}_$column'.toLowerCase();
      return lc == '${prefix}_check' || lc == '${prefix}__check' ? 2 : 3;
    }

    var names = _sortedByRank(e.value, checkRank);
    duplicatedChecks[column] = names;

    dropSQLs.add(
      '-- `$table`.`$column`: keeping `${names.first}` '
      '${checkConstraints[names.first]?.values}:',
    );

    if (entityValues != null && !hasAllValues(names.first)) {
      dropSQLs.add(
        "-- none has the entity's values $entityValues: review before "
        "dropping",
      );
    }

    for (var name in names.skip(1)) {
      dropSQLs.add(
        '${generateDropConstraintSQL(table, name)}  -- '
        '${checkConstraints[name]?.values}',
      );
    }
  }

  return TableDuplicates(
    table,
    indexes: duplicatedIndexes,
    checkConstraints: duplicatedChecks,
    dropSQLs: dropSQLs,
  );
}