`make test` 8 succeed, 1 fails

[christoph-maurer]

The test EnsureDomainModelMatchesDatabaseSchema fails with the following output:

failed EnsureDomainModelMatchesDatabaseSchema (36s 719ms)
  Expected hasErrors to be False because NOT IN DATABASE: DataAccessPolicy->Property 'Version', column name. Expected = xmin
  DIFFERENT: InstitutionAccessPolicy->Property 'DataAccessPolicyId', default value sql. Expected = <null>, found = '00000000-0000-0000-0000-000000000000'::uuid
  DIFFERENT: InstitutionAccessPolicy->Property 'DataAccessPolicyId', value generated. Expected = Never, found = OnAdd
  NOT IN DATABASE: InstitutionAccessPolicy->Property 'Version', column name. Expected = xmin
  DIFFERENT: CountSinceTime->Property 'InstitutionAccessPolicyId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: UpperLimitPerDuration->Property 'InstitutionAccessPolicyId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: OpenIdConnectApplicationAccessPolicy->Property 'DataAccessPolicyId', default value sql. Expected = <null>, found = '00000000-0000-0000-0000-000000000000'::uuid
  DIFFERENT: OpenIdConnectApplicationAccessPolicy->Property 'DataAccessPolicyId', value generated. Expected = Never, found = OnAdd
  NOT IN DATABASE: OpenIdConnectApplicationAccessPolicy->Property 'Version', column name. Expected = xmin
  DIFFERENT: CountSinceTime->Property 'OpenIdConnectApplicationAccessPolicyId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: UpperLimitPerDuration->Property 'OpenIdConnectApplicationAccessPolicyId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: UserAccessPolicy->Property 'DataAccessPolicyId', default value sql. Expected = <null>, found = '00000000-0000-0000-0000-000000000000'::uuid
  DIFFERENT: UserAccessPolicy->Property 'DataAccessPolicyId', value generated. Expected = Never, found = OnAdd
  NOT IN DATABASE: UserAccessPolicy->Property 'Version', column name. Expected = xmin
  DIFFERENT: CountSinceTime->Property 'UserAccessPolicyId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: UpperLimitPerDuration->Property 'UserAccessPolicyId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: CalorimetricData->Property 'PublishingState', default value sql. Expected = <null>, found = 'pending'::database.publishing_state
  DIFFERENT: CalorimetricData->Property 'PublishingState', value generated. Expected = Never, found = OnAdd
  DIFFERENT: CalorimetricData->Property 'UserId', default value sql. Expected = <null>, found = '00000000-0000-0000-0000-000000000000'::uuid
  DIFFERENT: CalorimetricData->Property 'UserId', value generated. Expected = Never, found = OnAdd
  NOT IN DATABASE: CalorimetricData->Property 'Version', column name. Expected = xmin
  DIFFERENT: AppliedMethod->Property 'CalorimetricDataId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: ResponseApproval->Property 'CalorimetricDataId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: ResponseApproval->Property 'Variables', default value sql. Expected = '{}', found = '{}'::jsonb
  DIFFERENT: DataApproval->Property 'Variables', default value sql. Expected = '{}', found = '{}'::jsonb
  DIFFERENT: Reference->Property 'Exists', default value sql. Expected = <null>, found = true
  DIFFERENT: Reference->Property 'Exists', value generated. Expected = Never, found = OnAdd
  DIFFERENT: GeometricData->Property 'Heights', default value sql. Expected = <null>, found = ARRAY[]::double precision[]
  DIFFERENT: GeometricData->Property 'Heights', value generated. Expected = Never, found = OnAdd
  DIFFERENT: GeometricData->Property 'PublishingState', default value sql. Expected = <null>, found = 'pending'::database.publishing_state
  DIFFERENT: GeometricData->Property 'PublishingState', value generated. Expected = Never, found = OnAdd
  DIFFERENT: GeometricData->Property 'UserId', default value sql. Expected = <null>, found = '00000000-0000-0000-0000-000000000000'::uuid
  DIFFERENT: GeometricData->Property 'UserId', value generated. Expected = Never, found = OnAdd
  NOT IN DATABASE: GeometricData->Property 'Version', column name. Expected = xmin
  DIFFERENT: GeometricData->Property 'Widths', default value sql. Expected = <null>, found = ARRAY[]::double precision[]
  DIFFERENT: GeometricData->Property 'Widths', value generated. Expected = Never, found = OnAdd
  DIFFERENT: AppliedMethod->Property 'GeometricDataId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: ResponseApproval->Property 'GeometricDataId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: ResponseApproval->Property 'Variables', default value sql. Expected = '{}', found = '{}'::jsonb
  DIFFERENT: DataApproval->Property 'Variables', default value sql. Expected = '{}', found = '{}'::jsonb
  DIFFERENT: Reference->Property 'Exists', default value sql. Expected = <null>, found = true
  DIFFERENT: Reference->Property 'Exists', value generated. Expected = Never, found = OnAdd
  NOT IN DATABASE: GetHttpsResource->Property 'Version', column name. Expected = xmin
  DIFFERENT: GetHttpsResource->ForeignKey 'FK_get_https_resource_get_https_resource_ParentId', delete behavior. Expected = ClientSetNull, found = Restrict
  DIFFERENT: HygrothermalData->Property 'PublishingState', default value sql. Expected = <null>, found = 'pending'::database.publishing_state
  DIFFERENT: HygrothermalData->Property 'PublishingState', value generated. Expected = Never, found = OnAdd
  DIFFERENT: HygrothermalData->Property 'UserId', default value sql. Expected = <null>, found = '00000000-0000-0000-0000-000000000000'::uuid
  DIFFERENT: HygrothermalData->Property 'UserId', value generated. Expected = Never, found = OnAdd
  NOT IN DATABASE: HygrothermalData->Property 'Version', column name. Expected = xmin
  DIFFERENT: AppliedMethod->Property 'HygrothermalDataId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: ResponseApproval->Property 'HygrothermalDataId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: ResponseApproval->Property 'Variables', default value sql. Expected = '{}', found = '{}'::jsonb
  DIFFERENT: DataApproval->Property 'Variables', default value sql. Expected = '{}', found = '{}'::jsonb
  DIFFERENT: Reference->Property 'Exists', default value sql. Expected = <null>, found = true
  DIFFERENT: Reference->Property 'Exists', value generated. Expected = Never, found = OnAdd
  NOT IN DATABASE: LifeCycleData->Property 'Version', column name. Expected = xmin
  DIFFERENT: AppliedMethod->Property 'LifeCycleDataId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: ResponseApproval->Property 'LifeCycleDataId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: ResponseApproval->Property 'Variables', default value sql. Expected = '{}', found = '{}'::jsonb
  DIFFERENT: DataApproval->Property 'Variables', default value sql. Expected = '{}', found = '{}'::jsonb
  DIFFERENT: OpticalData->Property 'CoatedSide', default value sql. Expected = <null>, found = 'unknown'::database.coated_side
  DIFFERENT: OpticalData->Property 'CoatedSide', value generated. Expected = Never, found = OnAdd
  DIFFERENT: OpticalData->Property 'PublishingState', default value sql. Expected = <null>, found = 'pending'::database.publishing_state
  DIFFERENT: OpticalData->Property 'PublishingState', value generated. Expected = Never, found = OnAdd
  DIFFERENT: OpticalData->Property 'UserId', default value sql. Expected = <null>, found = '00000000-0000-0000-0000-000000000000'::uuid
  DIFFERENT: OpticalData->Property 'UserId', value generated. Expected = Never, found = OnAdd
  NOT IN DATABASE: OpticalData->Property 'Version', column name. Expected = xmin
  DIFFERENT: AppliedMethod->Property 'OpticalDataId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: ResponseApproval->Property 'OpticalDataId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: ResponseApproval->Property 'Variables', default value sql. Expected = '{}', found = '{}'::jsonb
  DIFFERENT: DataApproval->Property 'Variables', default value sql. Expected = '{}', found = '{}'::jsonb
  DIFFERENT: Reference->Property 'Exists', default value sql. Expected = <null>, found = true
  DIFFERENT: Reference->Property 'Exists', value generated. Expected = Never, found = OnAdd
  DIFFERENT: PhotovoltaicData->Property 'PublishingState', default value sql. Expected = <null>, found = 'pending'::database.publishing_state
  DIFFERENT: PhotovoltaicData->Property 'PublishingState', value generated. Expected = Never, found = OnAdd
  DIFFERENT: PhotovoltaicData->Property 'UserId', default value sql. Expected = <null>, found = '00000000-0000-0000-0000-000000000000'::uuid
  DIFFERENT: PhotovoltaicData->Property 'UserId', value generated. Expected = Never, found = OnAdd
  NOT IN DATABASE: PhotovoltaicData->Property 'Version', column name. Expected = xmin
  DIFFERENT: AppliedMethod->Property 'PhotovoltaicDataId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: ResponseApproval->Property 'PhotovoltaicDataId', default value sql. Expected = <null>, found = uuidv7()
  DIFFERENT: ResponseApproval->Property 'Variables', default value sql. Expected = '{}', found = '{}'::jsonb
  DIFFERENT: DataApproval->Property 'Variables', default value sql. Expected = '{}', found = '{}'::jsonb
  DIFFERENT: Reference->Property 'Exists', default value sql. Expected = <null>, found = true
  DIFFERENT: Reference->Property 'Exists', value generated. Expected = Never, found = OnAdd
  DIFFERENT: ToTreeVertexAppliedConversionMethod->Property 'GetHttpsResourceId', default value sql. Expected = <null>, found = uuidv7()
  NOT IN DATABASE: User->Property 'Version', column name. Expected = xmin, but found True.
    at FluentAssertions.Primitives.BooleanAssertions`1.BeFalse(String because, Object[] becauseArgs)
    at Database.Tests.Integration.DatabaseSchemaTests.EnsureDomainModelMatchesDatabaseSchema() in Integration/DatabaseSchemaTests.cs:90

[christoph-maurer]

The test EnsureDomainModelMatchesDatabaseSchema fails because of the limitations of Entity Framework Core and EfCore.SchemaCompare. There are three representations of the postgres database. application.db is a procedural description which is compatible with the domain model and which defines the target how the postgres database should be. snapshot.cs is a declarative description of the current postgres database in C# which is limited in some details like enum which need to be fixed manually. migrate.sql is procedural, it consists of SQL commands and represents exactly the current postgres database. EfCore.SchemaCompare is used to reverse-engineer migrate.sql to create an approximation of snapshot.cs. Both can be compared, but they differ because of the above mentioned limitations. In the future, all errors which are not important could be excluded.

[christoph-maurer]

Now, pg-schema-diff is used instead of EfCore.SchemaCompare. In general, there are two tasks:

1. Check if the domain model has been changed during development. The snapshot is only created when you run ./database.mk migrate. When you change the domain model without running ./database.mk migrate, then the test EnsureMigrationsAreUpToDate fails. For the test, the domain model and the snapshot are used to create two postgres database and they are compared with pg-schema-diff.

2. Check if the domain model and migrate.sql match. Sometimes manual changes in migrate.sql are needed which may lead to difference. In this case, EnsureDomainModelMatchesDatabaseSchema fails.