[PIP] Schema compatibility check

No pun intended, PIP stands for "Proposal & Implementation Plan".

Motivation

To allow an application to check that all Entities are compatible with the current database schema, on CI and/or on application startup.

Architecture

We need to introduce a standardized way to define all entities, and I imagine:

trait EntityCollection: Iterable {
    fn entity_schema(&self, db: DbBackend) -> EntitySchema {
        // this will call `create_table_from_entity` etc to construct EntitySchema
    }
}

struct EntitySchema {
    name: DynIden,
    table: TableCreateStatement,
    enums: Vec<TypeCreateStatement>,
    indices: Vec<IndexCreateStatement>,
}

// user space

#[derive(EnumIter, DeriveEntityCollection)]
enum Entities {
    Cake,
    Fruit,
}

And as such, we can have the following API:

impl Schema {
    async fn check_compatibility<C: EntityCollection>(db: &DbConn, entities: &C) -> Result<(), SchemaErrors> {
        let mut errors = Vec::new();
        // discover schema
        for entity in entities::iter() {
            check(entity.entity_schema(db), db_schema);
        }
        if errors.is_empty() {
            Ok(())
        } else {
            Err(SchemaErrors { errors })
        }
    }
}

struct SchemaErrors {
    errors: Vec<SchemaError>,
}

enum SchemaError {
    TableMissing(..),
    ColumnMissing(..),
    ColumnTypeIncompatible(..),
    ..
}

Mode of operation

Under the hood, it will discover the entire schema from the database (just like codegen) and compare it against EntitySchema.

The tricky part is that these two schema will not be exactly the same, so we should be somewhat tolerant to permutations.

The live schema can be a superset of Entity schema, i.e. there can be more tables and columns. So we should start from EntitySchema and check that 1) column exist and 2) the types are inter-operable (i.e. DateTime can work with time::DateTime or chrono::DateTime, to complicate things a bit, numeric can also work with both Decimal and BigDecimal)

The MVP don't have to check absolutely everything, but just some sanity checks in CI would be better than nothing.

Since this depends on SeaSchema, we should place this implementation in sea-orm-migration.

And we need the support of the DeriveEntityCollection macro as well as codegen to generate the Entities enum in the first place.

A bonus would be that we can return all errors instead of failing immediately.

Start by reading the proposed placement in sea-orm-migration, then trace the existing SeaSchema and codegen concepts named in the proposal. Define the MVP around discovering the live schema, comparing required entity columns and interoperable types, and reporting all found errors rather than stopping at the first.