verifySchema function

Future<SchemaVerification> verifySchema(SqlDatabase<Backend> db, SchemaSnapshot expected)

Compares modeled tables, columns, keys, indexes, and checks with the catalog.

This does not infer or apply a migration. Inspect the returned unmanaged objects as well as the modeled differences before changing an existing table.

Implementation

Future<SchemaVerification> verifySchema(
  SqlDatabase<Backend> db,
  SchemaSnapshot expected,
) async {
  if (isMysqlFamily(db.dialect)) return mysqlVerifySchema(db, expected);
  final differences = <String>[], unmanaged = <CatalogObject>[];
  for (final table in expected.tables) {
    final actual = await inspectTable(db, table.name);
    unmanaged.addAll(actual.unmanaged);
    final columns = {for (final c in actual.columns) c.name: c};
    var checkContextMatches = true;
    for (final column in table.columns) {
      final found = columns.remove(column.name),
          path = '${table.name}.${column.name}';
      if (found == null) {
        checkContextMatches = false;
        differences.add('$path is missing');
        continue;
      }
      if (found.storageType != columnStorageType(column, db.dialect)) {
        checkContextMatches = false;
        differences.add('$path type differs');
      }
      if (found.nullable != column.nullable) {
        differences.add('$path nullability differs');
      }
      if ((found.temporalPrecision ?? 6) != (column.temporalPrecision ?? 6)) {
        differences.add('$path temporal precision differs');
      }
      if (!matchesDecimalDigits(column, found)) {
        differences.add('$path decimal precision/scale differs');
      }
      if (db.dialect == SqlDialect.sqlite && !matchesCollation(column, found)) {
        differences.add('$path collation differs');
      }
      if (column.codec.sqlType == 'integer' &&
          (found.integerBits ?? 64) != (column.integerBits ?? 64)) {
        differences.add('$path integer width differs');
      }
      if (_columnDefault(found.defaultSql, column) !=
          _columnDefault(
            column.defaultSql == null
                ? null
                : coerceColumn(column.defaultSql!, column, db.dialect),
            column,
          )) {
        differences.add('$path default differs');
      }
      if (db.dialect == SqlDialect.postgres &&
          column.computed == null &&
          found.computed == null &&
          found.generated != column.generated) {
        differences.add('$path generation differs');
      }
      if (db.dialect == SqlDialect.sqlite &&
          found.generated &&
          found.computed == null) {
        differences.add('$path uses an unmodeled generated expression');
      }
    }
    for (final name in columns.keys) {
      differences.add('${table.name}.$name is unmanaged');
    }
    differences.addAll(
      await verifyComputed(
        db,
        table,
        actual.columns,
        contextMatches: checkContextMatches,
      ),
    );
    void compare(String kind, Object? desired, Object? found) {
      if (migrationHash(desired) != migrationHash(found)) {
        differences.add('${table.name} $kind differs');
      }
    }

    List<String> set(Iterable<Object?> values) =>
        values.map((v) => jsonEncode(canonicalMigrationValue(v))).toList()
          ..sort();
    compare('primary key', table.primaryKey, actual.primaryKey);
    compare('unique keys', set(table.uniqueKeys), set(actual.uniqueKeys));
    // Expected SQL may no longer resolve when a referenced column/type drifted.
    // Report that schema drift instead of attempting an invalid EXPLAIN.
    final checkMatches = checkContextMatches
        ? await matchChecks(db, table.name, table.checks, actual.checks)
        : List<int?>.filled(table.checks.length, null);
    if (checkMatches.any((i) => i == null) ||
        checkMatches.length != actual.checks.length) {
      differences.add('${table.name} checks differs');
      for (var i = 0; i < actual.checks.length; i++) {
        if (!checkMatches.contains(i)) {
          final c = actual.checks[i];
          unmanaged.add(
            CatalogObject('check', c.name ?? '${table.name}#$i', c.expression),
          );
        }
      }
    }
    compare(
      'foreign keys',
      set(table.foreignKeys.map(foreignKeyJson)),
      set(actual.foreignKeys.map(foreignKeyJson)),
    );
    compare(
      'indexes',
      set(table.indexes.map(indexJson)),
      set(actual.indexes.map(indexJson)),
    );
  }
  return SchemaVerification(
    List.unmodifiable(differences),
    List.unmodifiable(unmanaged),
  );
}