Schema apply fails to delete nested collection if parent is not also deleted

[InkBrownell]

Describe the Bug

When a nested collection is slated to be deleted during a snapshot apply, if its parent is not also deleted, the schema apply will appear to succeed, but the collection will not be deleted.

A concrete example:

Suppose you have two collections, parent and child. These collections are nested so that child is within parent's group, like so:

• parent
  • child

Attempting to apply a snapshot which contains parent, but does not contain child will appear to delete child, but will silently fail to do so, even if there are no database constraints preventing the deletion.

This issue also applies to more deeply nested collections, where the same constraint holds: if a collection's parent is not deleted, then the collection itself will not be deleted. This implies that in order to delete a deeply nested collection, one must also delete all of its ancestors up to the root.

To Reproduce

1. From a fresh database, apply Snapshot A. This will create a pair of nested collections, parent and child.

Snapshot A

version: 1
directus: 9.21.2
collections:
  - collection: child
    meta:
      accountability: all
      archive_app_filter: true
      archive_field: null
      archive_value: null
      collapse: open
      collection: child
      color: null
      display_template: null
      group: parent
      hidden: false
      icon: null
      item_duplication_fields: null
      note: null
      singleton: false
      sort: 1
      sort_field: null
      translations: null
      unarchive_value: null
    schema:
      catalog: directus
      name: child
      schema: dbo
  - collection: parent
    meta:
      accountability: all
      archive_app_filter: true
      archive_field: null
      archive_value: null
      collapse: open
      collection: parent
      color: null
      display_template: null
      group: null
      hidden: false
      icon: null
      item_duplication_fields: null
      note: null
      singleton: false
      sort: 1
      sort_field: null
      translations: null
      unarchive_value: null
    schema:
      catalog: directus
      name: parent
      schema: dbo
fields:
  - collection: child
    field: id
    meta:
      collection: child
      conditions: null
      display: null
      display_options: null
      field: id
      group: null
      hidden: true
      interface: input
      note: null
      options: null
      readonly: true
      required: false
      sort: null
      special: null
      translations: null
      validation: null
      validation_message: null
      width: full
    schema:
      data_type: int
      default_value: null
      foreign_key_column: null
      foreign_key_table: null
      generation_expression: null
      has_auto_increment: true
      is_generated: false
      is_nullable: false
      is_primary_key: true
      is_unique: true
      max_length: 4
      name: id
      numeric_precision: 10
      numeric_scale: null
      table: child
    type: integer
  - collection: parent
    field: id
    meta:
      collection: parent
      conditions: null
      display: null
      display_options: null
      field: id
      group: null
      hidden: true
      interface: input
      note: null
      options: null
      readonly: true
      required: false
      sort: null
      special: null
      translations: null
      validation: null
      validation_message: null
      width: full
    schema:
      data_type: int
      default_value: null
      foreign_key_column: null
      foreign_key_table: null
      generation_expression: null
      has_auto_increment: true
      is_generated: false
      is_nullable: false
      is_primary_key: true
      is_unique: true
      max_length: 4
      name: id
      numeric_precision: 10
      numeric_scale: null
      table: parent
    type: integer
relations: []

2. Apply Snapshot B. This second snapshot includes only the parent collection and should result in the deletion of the child collection. Notice that the "Would you like to continue?" prompt reads:

? The following changes will be applied:

Collections:
  - Delete child

Snapshot B

version: 1
directus: 9.21.2
collections:
  - collection: parent
    meta:
      accountability: all
      archive_app_filter: true
      archive_field: null
      archive_value: null
      collapse: open
      collection: parent
      color: null
      display_template: null
      group: null
      hidden: false
      icon: null
      item_duplication_fields: null
      note: null
      singleton: false
      sort: 1
      sort_field: null
      translations: null
      unarchive_value: null
    schema:
      catalog: directus
      name: parent
      schema: dbo
fields:
  - collection: parent
    field: id
    meta:
      collection: parent
      conditions: null
      display: null
      display_options: null
      field: id
      group: null
      hidden: true
      interface: input
      note: null
      options: null
      readonly: true
      required: false
      sort: null
      special: null
      translations: null
      validation: null
      validation_message: null
      width: full
    schema:
      data_type: int
      default_value: null
      foreign_key_column: null
      foreign_key_table: null
      generation_expression: null
      has_auto_increment: true
      is_generated: false
      is_nullable: false
      is_primary_key: true
      is_unique: true
      max_length: 4
      name: id
      numeric_precision: 10
      numeric_scale: null
      table: parent
    type: integer
relations: []

3. Notice that the snapshot apply exit code is 0, indicating success. No error messages are printed.
4. Examine your database and your Directus GUI. Notice that neither the child table nor the child collection has been deleted.

Errors Shown

No errors are shown despite behavior differing from what was stated would happen.

What version of Directus are you using?

Directus 9.21.2

What version of Node.js are you using?

Node 18.12.1

What database are you using?

SQL Server 15.0.2000 (SQL Server Express)

What browser are you using?

Chrome 107.0.5304.107, though not relevant for this bug

How are you deploying Directus?

Running locally

[rijkvanzanten]

Linear: ENG-124

[jaads]

this is independent of the underlying database, therefore I removed the ms-sql flag