Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

99.8 KB
Raw Blame
package pg

import (
	"strings"
	"testing"

	metadatapb "github.com/bytebase/omni/metadata"
	"github.com/stretchr/testify/require"

	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/schema"
	"github.com/bytebase/bytebase/backend/store/model"
)

func TestPGMetadataDiffIdenticalRawViewDoesNotLoadSDL(t *testing.T) {
	source := newPGDatabaseMetadataWithView("SELECT @@@omni_unsupported@@@")
	target := newPGDatabaseMetadataWithView("SELECT @@@omni_unsupported@@@")

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Empty(t, sql)
}

func TestPGMetadataDiffRawViewAlterUsesMetadataDefinition(t *testing.T) {
	source := newPGDatabaseMetadataWithView("SELECT @@@old_definition@@@")
	target := newPGDatabaseMetadataWithView("SELECT @@@new_definition@@@")

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `CREATE OR REPLACE VIEW "public"."v" AS`)
	require.Contains(t, sql, "SELECT @@@new_definition@@@")
}

func TestCockroachMetadataDiffIdenticalRawViewDoesNotLoadSDL(t *testing.T) {
	source := newDatabaseMetadataWithEngine(storepb.Engine_COCKROACHDB, nil, []*metadatapb.ViewMetadata{
		{
			Name:       "v",
			Definition: "SELECT @@@omni_unsupported@@@",
		},
	})
	target := newDatabaseMetadataWithEngine(storepb.Engine_COCKROACHDB, nil, []*metadatapb.ViewMetadata{
		{
			Name:       "v",
			Definition: "SELECT @@@omni_unsupported@@@",
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_COCKROACHDB, source, target)

	require.NoError(t, err)
	require.Empty(t, sql)
}

func TestCockroachMetadataDiffUsesCockroachFunctionComparer(t *testing.T) {
	schema.RegisterFunctionComparer(storepb.Engine_COCKROACHDB, &alwaysEqualFunctionComparer{})
	t.Cleanup(func() {
		schema.RegisterFunctionComparer(storepb.Engine_COCKROACHDB, &schema.DefaultFunctionComparer{})
	})
	source := newCockroachDatabaseMetadataWithFunctions([]*metadatapb.FunctionMetadata{
		newPGFunctionMetadata("SELECT 1"),
	})
	target := newCockroachDatabaseMetadataWithFunctions([]*metadatapb.FunctionMetadata{
		newPGFunctionMetadata("SELECT 2"),
	})

	sql, err := schema.DiffMigration(storepb.Engine_COCKROACHDB, source, target)

	require.NoError(t, err)
	require.Empty(t, sql)
}

func TestPGMetadataDiffCreateTableFromMetadata(t *testing.T) {
	source := newPGDatabaseMetadata(nil, nil)
	target := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{
					Name:     "id",
					Type:     "integer",
					Nullable: false,
				},
				{
					Name:     "name",
					Type:     "text",
					Nullable: true,
				},
			},
		},
	}, nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `CREATE TABLE "public"."users"`)
	require.Contains(t, sql, `"id" integer NOT NULL`)
	require.Contains(t, sql, `"name" text`)
}

func TestPGMetadataDiffCreateTableWithGeneratedAndIdentityColumnsFromMetadata(t *testing.T) {
	source := newPGDatabaseMetadata(nil, nil)
	target := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{
					Name:               "id",
					Type:               "bigint",
					Nullable:           false,
					IdentityGeneration: metadatapb.ColumnMetadata_BY_DEFAULT,
				},
				{
					Name:     "name",
					Type:     "text",
					Nullable: false,
				},
				{
					Name:     "name_key",
					Type:     "text",
					Nullable: false,
					Generation: &metadatapb.GenerationMetadata{
						Type:       metadatapb.GenerationMetadata_TYPE_STORED,
						Expression: "lower(name)",
					},
				},
			},
		},
	}, nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `"id" bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL`)
	require.Contains(t, sql, `"name_key" text GENERATED ALWAYS AS (lower(name)) STORED NOT NULL`)
}

func TestPGMetadataDiffAlterTableAddColumnFromMetadata(t *testing.T) {
	source := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{
					Name:     "id",
					Type:     "integer",
					Nullable: false,
				},
			},
		},
	}, nil)
	target := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{
					Name:     "id",
					Type:     "integer",
					Nullable: false,
				},
				{
					Name:     "name",
					Type:     "text",
					Nullable: true,
				},
			},
		},
	}, nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `ALTER TABLE "public"."users" ADD COLUMN "name" text`)
}

func TestPGMetadataDiffAlterTableAddGeneratedAndIdentityColumnsFromMetadata(t *testing.T) {
	source := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "name", Type: "text", Nullable: false},
			},
		},
	}, nil)
	target := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "name", Type: "text", Nullable: false},
				{
					Name:               "id",
					Type:               "bigint",
					Nullable:           false,
					IdentityGeneration: metadatapb.ColumnMetadata_ALWAYS,
				},
				{
					Name:     "name_key",
					Type:     "text",
					Nullable: false,
					Generation: &metadatapb.GenerationMetadata{
						Type:       metadatapb.GenerationMetadata_TYPE_STORED,
						Expression: "lower(name)",
					},
				},
			},
		},
	}, nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `ADD COLUMN "id" bigint GENERATED ALWAYS AS IDENTITY NOT NULL`)
	require.Contains(t, sql, `ADD COLUMN "name_key" text GENERATED ALWAYS AS (lower(name)) STORED NOT NULL`)
}

func TestPGMetadataDiffAlterColumnTypeUsesUsingForIncompatibleCast(t *testing.T) {
	source := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "events",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "payload", Type: "text", Nullable: true},
			},
		},
	}, nil)
	target := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "events",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "payload", Type: "integer", Nullable: true},
			},
		},
	}, nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `ALTER COLUMN "payload" TYPE integer USING "payload"::integer`)
}

func TestPGMetadataDiffAlterTableDropsColumnOnce(t *testing.T) {
	source := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
				{Name: "legacy", Type: "text", Nullable: true},
			},
		},
	}, nil)
	target := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
			},
		},
	}, nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Equal(t, 1, strings.Count(sql, `DROP COLUMN "legacy"`))
}

func TestPGMetadataDiffAlterTableObjectsFromMetadata(t *testing.T) {
	source := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "accounts",
			Columns: []*metadatapb.ColumnMetadata{
				{
					Name:     "id",
					Type:     "integer",
					Nullable: false,
				},
			},
		},
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{
					Name:     "id",
					Type:     "integer",
					Nullable: false,
				},
				{
					Name:     "account_id",
					Type:     "integer",
					Nullable: false,
				},
				{
					Name:     "email",
					Type:     "text",
					Nullable: false,
				},
			},
		},
	}, nil)
	target := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "accounts",
			Columns: []*metadatapb.ColumnMetadata{
				{
					Name:     "id",
					Type:     "integer",
					Nullable: false,
				},
			},
		},
		{
			Name:    "users",
			Comment: "Application users",
			Columns: []*metadatapb.ColumnMetadata{
				{
					Name:     "id",
					Type:     "integer",
					Nullable: false,
				},
				{
					Name:     "account_id",
					Type:     "integer",
					Nullable: false,
				},
				{
					Name:     "email",
					Type:     "text",
					Nullable: false,
					Comment:  "Login email",
				},
			},
			Indexes: []*metadatapb.IndexMetadata{
				{
					Name:         "users_pkey",
					Expressions:  []string{"id"},
					Primary:      true,
					Unique:       true,
					IsConstraint: true,
				},
				{
					Name:         "users_email_key",
					Expressions:  []string{"email"},
					Unique:       true,
					IsConstraint: true,
				},
				{
					Name:        "idx_users_account_id",
					Expressions: []string{"account_id"},
					Type:        "btree",
				},
			},
			ForeignKeys: []*metadatapb.ForeignKeyMetadata{
				{
					Name:              "users_account_id_fkey",
					Columns:           []string{"account_id"},
					ReferencedSchema:  "public",
					ReferencedTable:   "accounts",
					ReferencedColumns: []string{"id"},
					OnDelete:          "CASCADE",
				},
			},
			CheckConstraints: []*metadatapb.CheckConstraintMetadata{
				{
					Name:       "users_email_check",
					Expression: "(email <> '')",
				},
			},
			Triggers: []*metadatapb.TriggerMetadata{
				{
					Name: "users_touch_updated_at",
					Body: "CREATE TRIGGER users_touch_updated_at BEFORE UPDATE ON public.users " +
						"FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at()",
					Comment: "maintain updated_at",
				},
			},
		},
	}, nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `ALTER TABLE ONLY "public"."users" ADD CONSTRAINT "users_pkey" PRIMARY KEY (id)`)
	require.Contains(t, sql, `ALTER TABLE ONLY "public"."users" ADD CONSTRAINT "users_email_key" UNIQUE (email)`)
	require.Contains(t, sql, `ALTER TABLE "public"."users" ADD CONSTRAINT "users_email_check" CHECK (email <> '')`)
	require.Contains(t, sql, `CREATE INDEX "idx_users_account_id" ON "public"."users" (account_id)`)
	require.Contains(t, sql, `ADD CONSTRAINT "users_account_id_fkey" FOREIGN KEY ("account_id")`)
	require.Contains(t, sql, `ON DELETE CASCADE`)
	require.Contains(t, sql, `CREATE TRIGGER users_touch_updated_at BEFORE UPDATE ON public.users`)
	require.Contains(t, sql, `COMMENT ON TRIGGER "users_touch_updated_at" ON "public"."users" IS 'maintain updated_at'`)
	require.Contains(t, sql, `COMMENT ON TABLE "public"."users" IS 'Application users'`)
	require.Contains(t, sql, `COMMENT ON COLUMN "public"."users"."email" IS 'Login email'`)
}

func TestPGMetadataDiffCreateIndexUsesFullDefinitionFromMetadata(t *testing.T) {
	source := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "email", Type: "text", Nullable: false},
			},
		},
	}, nil)
	target := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "email", Type: "text", Nullable: false},
			},
			Indexes: []*metadatapb.IndexMetadata{
				{
					Name:        "idx_users_email",
					Expressions: []string{"email"},
					Type:        "btree",
					Definition:  `CREATE INDEX idx_users_email ON public.users USING btree (email DESC NULLS LAST) WITH (fillfactor='70')`,
				},
			},
		},
	}, nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `CREATE INDEX idx_users_email ON public.users USING btree (email DESC NULLS LAST) WITH (fillfactor='70');`)
}

func TestPGMetadataDiffCreateTableForeignKeysAfterAllTables(t *testing.T) {
	source := newPGDatabaseMetadata(nil, nil)
	target := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "child",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
				{Name: "parent_id", Type: "integer", Nullable: false},
			},
			ForeignKeys: []*metadatapb.ForeignKeyMetadata{
				{
					Name:              "child_parent_id_fkey",
					Columns:           []string{"parent_id"},
					ReferencedSchema:  "public",
					ReferencedTable:   "parent",
					ReferencedColumns: []string{"id"},
				},
			},
		},
		{
			Name: "parent",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
			},
		},
	}, nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	parentIdx := strings.Index(sql, `CREATE TABLE "public"."parent"`)
	childIdx := strings.Index(sql, `CREATE TABLE "public"."child"`)
	fkIdx := strings.Index(sql, `ADD CONSTRAINT "child_parent_id_fkey"`)
	require.NotEqual(t, -1, parentIdx)
	require.NotEqual(t, -1, childIdx)
	require.NotEqual(t, -1, fkIdx)
	require.Less(t, parentIdx, childIdx)
	require.Less(t, childIdx, fkIdx)
}

func TestPGMetadataDiffDropTablesInForeignKeyDependencyOrder(t *testing.T) {
	source := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "parent",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
			},
		},
		{
			Name: "child",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
				{Name: "parent_id", Type: "integer", Nullable: false},
			},
			ForeignKeys: []*metadatapb.ForeignKeyMetadata{
				{
					Name:              "child_parent_id_fkey",
					Columns:           []string{"parent_id"},
					ReferencedSchema:  "public",
					ReferencedTable:   "parent",
					ReferencedColumns: []string{"id"},
				},
			},
		},
	}, nil)
	target := newPGDatabaseMetadata(nil, nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	childIdx := strings.Index(sql, `DROP TABLE "public"."child"`)
	parentIdx := strings.Index(sql, `DROP TABLE "public"."parent"`)
	require.NotEqual(t, -1, childIdx)
	require.NotEqual(t, -1, parentIdx)
	require.Less(t, childIdx, parentIdx)
}

func TestPGMetadataDiffDropTableSkipsOwnedSequenceDrop(t *testing.T) {
	source := newPGDatabaseMetadataWithTablesAndSequences([]*metadatapb.TableMetadata{
		{
			Name: "orders",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
			},
		},
	}, []*metadatapb.SequenceMetadata{
		{
			Name:        "orders_id_seq",
			DataType:    "bigint",
			Start:       "1",
			Increment:   "1",
			MinValue:    "1",
			MaxValue:    "9223372036854775807",
			CacheSize:   "1",
			OwnerTable:  "orders",
			OwnerColumn: "id",
		},
	})
	target := newPGDatabaseMetadataWithTablesAndSequences(nil, nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `DROP TABLE "public"."orders"`)
	require.NotContains(t, sql, `DROP SEQUENCE "public"."orders_id_seq"`)
}

func TestPGMetadataDiffDropCyclicForeignKeyTablesDropsForeignKeysFirst(t *testing.T) {
	source := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "a",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
				{Name: "b_id", Type: "integer", Nullable: false},
			},
			ForeignKeys: []*metadatapb.ForeignKeyMetadata{
				{
					Name:              "a_b_id_fkey",
					Columns:           []string{"b_id"},
					ReferencedSchema:  "public",
					ReferencedTable:   "b",
					ReferencedColumns: []string{"id"},
				},
			},
		},
		{
			Name: "b",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
				{Name: "a_id", Type: "integer", Nullable: false},
			},
			ForeignKeys: []*metadatapb.ForeignKeyMetadata{
				{
					Name:              "b_a_id_fkey",
					Columns:           []string{"a_id"},
					ReferencedSchema:  "public",
					ReferencedTable:   "a",
					ReferencedColumns: []string{"id"},
				},
			},
		},
	}, nil)
	target := newPGDatabaseMetadata(nil, nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `ALTER TABLE "public"."a" DROP CONSTRAINT "a_b_id_fkey"`)
	require.Contains(t, sql, `ALTER TABLE "public"."b" DROP CONSTRAINT "b_a_id_fkey"`)
	firstDropConstraintIdx := strings.Index(sql, `DROP CONSTRAINT`)
	firstDropTableIdx := strings.Index(sql, `DROP TABLE`)
	require.NotEqual(t, -1, firstDropConstraintIdx)
	require.NotEqual(t, -1, firstDropTableIdx)
	require.Less(t, firstDropConstraintIdx, firstDropTableIdx)
}

func TestPGMetadataDiffDropSchemaDropsViewsBeforeTablesWithoutDependencyMetadata(t *testing.T) {
	source := newPGDatabaseMetadataWithSchemas([]*metadatapb.SchemaMetadata{
		{
			Name: "analytics",
			Tables: []*metadatapb.TableMetadata{
				{Name: "a", Columns: []*metadatapb.ColumnMetadata{{Name: "id", Type: "integer", Nullable: false}}},
			},
			Views: []*metadatapb.ViewMetadata{
				{Name: "z", Definition: "SELECT id FROM analytics.a"},
			},
		},
	})
	target := newPGDatabaseMetadataWithSchemas(nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	viewIdx := strings.Index(sql, `DROP VIEW "analytics"."z"`)
	tableIdx := strings.Index(sql, `DROP TABLE "analytics"."a"`)
	require.NotEqual(t, -1, viewIdx)
	require.NotEqual(t, -1, tableIdx)
	require.Less(t, viewIdx, tableIdx)
}

func TestPGMetadataDiffDropAlteredViewBeforeDependentTableAlter(t *testing.T) {
	source := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
			},
		},
	}, []*metadatapb.ViewMetadata{
		{
			Name:       "v",
			Definition: "SELECT id FROM public.users",
			Comment:    "user ids",
			DependencyColumns: []*metadatapb.DependencyColumn{
				{Schema: "public", Table: "users"},
			},
		},
	})
	target := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "bigint", Nullable: false},
			},
		},
	}, []*metadatapb.ViewMetadata{
		{
			Name:       "v",
			Definition: "SELECT id::bigint AS id FROM public.users",
			Comment:    "user ids",
			DependencyColumns: []*metadatapb.DependencyColumn{
				{Schema: "public", Table: "users"},
			},
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	dropViewIdx := strings.Index(sql, `DROP VIEW "public"."v"`)
	alterTableIdx := strings.Index(sql, `ALTER TABLE "public"."users" ALTER COLUMN "id" TYPE bigint`)
	createViewIdx := strings.Index(sql, `CREATE OR REPLACE VIEW "public"."v"`)
	commentIdx := strings.Index(sql, `COMMENT ON VIEW "public"."v" IS 'user ids'`)
	require.NotEqual(t, -1, dropViewIdx)
	require.NotEqual(t, -1, alterTableIdx)
	require.NotEqual(t, -1, createViewIdx)
	require.NotEqual(t, -1, commentIdx)
	require.Less(t, dropViewIdx, alterTableIdx)
	require.Less(t, alterTableIdx, createViewIdx)
	require.Less(t, createViewIdx, commentIdx)
}

func TestPGMetadataDiffDropUnchangedDependentViewBeforeTableAlter(t *testing.T) {
	source := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
				{Name: "name", Type: "text", Nullable: false},
			},
		},
	}, []*metadatapb.ViewMetadata{
		{
			Name:       "v",
			Definition: "SELECT id FROM public.users",
			Comment:    "user ids",
			DependencyColumns: []*metadatapb.DependencyColumn{
				{Schema: "public", Table: "users", Column: "id"},
			},
		},
	})
	target := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "bigint", Nullable: false},
				{Name: "name", Type: "text", Nullable: false},
			},
		},
	}, []*metadatapb.ViewMetadata{
		{
			Name:       "v",
			Definition: "SELECT id FROM public.users",
			Comment:    "user ids",
			DependencyColumns: []*metadatapb.DependencyColumn{
				{Schema: "public", Table: "users", Column: "id"},
			},
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	dropViewIdx := strings.Index(sql, `DROP VIEW "public"."v"`)
	alterTableIdx := strings.Index(sql, `ALTER TABLE "public"."users" ALTER COLUMN "id" TYPE bigint`)
	createViewIdx := strings.Index(sql, `CREATE OR REPLACE VIEW "public"."v"`)
	commentIdx := strings.Index(sql, `COMMENT ON VIEW "public"."v" IS 'user ids'`)
	require.NotEqual(t, -1, dropViewIdx)
	require.NotEqual(t, -1, alterTableIdx)
	require.NotEqual(t, -1, createViewIdx)
	require.NotEqual(t, -1, commentIdx)
	require.Less(t, dropViewIdx, alterTableIdx)
	require.Less(t, alterTableIdx, createViewIdx)
	require.Less(t, createViewIdx, commentIdx)
}

func TestPGMetadataDiffDropAlteredViewBeforeDroppedReferencedTable(t *testing.T) {
	source := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "legacy_users",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
			},
		},
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
			},
		},
	}, []*metadatapb.ViewMetadata{
		{
			Name:       "v",
			Definition: "SELECT id FROM public.legacy_users",
			DependencyColumns: []*metadatapb.DependencyColumn{
				{Schema: "public", Table: "legacy_users", Column: "id"},
			},
		},
	})
	target := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		{
			Name: "users",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "integer", Nullable: false},
			},
		},
	}, []*metadatapb.ViewMetadata{
		{
			Name:       "v",
			Definition: "SELECT id FROM public.users",
			DependencyColumns: []*metadatapb.DependencyColumn{
				{Schema: "public", Table: "users", Column: "id"},
			},
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	dropViewIdx := strings.Index(sql, `DROP VIEW "public"."v"`)
	dropTableIdx := strings.Index(sql, `DROP TABLE "public"."legacy_users"`)
	createViewIdx := strings.Index(sql, `CREATE OR REPLACE VIEW "public"."v"`)
	require.NotEqual(t, -1, dropViewIdx)
	require.NotEqual(t, -1, dropTableIdx)
	require.NotEqual(t, -1, createViewIdx)
	require.Less(t, dropViewIdx, dropTableIdx)
	require.Less(t, dropTableIdx, createViewIdx)
}

func TestPGMetadataDiffAlterTablePartitionChangesFromMetadata(t *testing.T) {
	source := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		newPGPartitionedOrdersTable([]*metadatapb.TablePartitionMetadata{
			newPGOrdersPartition("orders_2023", "FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')"),
			newPGOrdersPartition("orders_2024", "FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')"),
		}),
	}, nil)
	target := newPGDatabaseMetadata([]*metadatapb.TableMetadata{
		newPGPartitionedOrdersTable([]*metadatapb.TablePartitionMetadata{
			newPGOrdersPartition("orders_2024", "FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')"),
			newPGOrdersPartition("orders_2025", "FOR VALUES FROM ('2025-01-01') TO ('2026-01-01')"),
		}),
	}, nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	dropPartitionIdx := strings.Index(sql, `DROP TABLE "public"."orders_2023"`)
	createPartitionIdx := strings.Index(sql, `CREATE TABLE "public"."orders_2025"`)
	attachPartitionIdx := strings.Index(sql, `ATTACH PARTITION "public"."orders_2025" FOR VALUES FROM ('2025-01-01') TO ('2026-01-01')`)
	require.NotEqual(t, -1, dropPartitionIdx)
	require.NotEqual(t, -1, createPartitionIdx)
	require.NotEqual(t, -1, attachPartitionIdx)
	require.Less(t, dropPartitionIdx, createPartitionIdx)
	require.Less(t, createPartitionIdx, attachPartitionIdx)
}

func TestPGMetadataDiffCreateViewsInDependencyOrder(t *testing.T) {
	source := newPGDatabaseMetadata(nil, nil)
	target := newPGDatabaseMetadata(nil, []*metadatapb.ViewMetadata{
		{
			Name:       "b",
			Definition: "SELECT * FROM a",
			DependencyColumns: []*metadatapb.DependencyColumn{
				{Schema: "public", Table: "a"},
			},
		},
		{
			Name:       "a",
			Definition: "SELECT 1",
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	viewAIdx := strings.Index(sql, `CREATE VIEW "public"."a"`)
	viewBIdx := strings.Index(sql, `CREATE VIEW "public"."b"`)
	require.NotEqual(t, -1, viewAIdx)
	require.NotEqual(t, -1, viewBIdx)
	require.Less(t, viewAIdx, viewBIdx)
}

func TestPGMetadataDiffCreateFunctionAfterNewDependencyView(t *testing.T) {
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{{Name: "public"}},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Views: []*metadatapb.ViewMetadata{
					{
						Name:       "active_users",
						Definition: "SELECT 1 AS id",
					},
				},
				Functions: []*metadatapb.FunctionMetadata{
					{
						Name:      "get_active_users",
						Signature: "get_active_users()",
						Definition: "CREATE FUNCTION public.get_active_users() RETURNS SETOF public.active_users LANGUAGE sql AS $$ " +
							"SELECT * FROM public.active_users $$",
						DependencyTables: []*metadatapb.DependencyTable{
							{Schema: "public", Table: "active_users"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	viewIdx := strings.Index(sql, `CREATE VIEW "public"."active_users"`)
	functionIdx := strings.Index(sql, "CREATE FUNCTION public.get_active_users()")
	require.NotEqual(t, -1, viewIdx)
	require.NotEqual(t, -1, functionIdx)
	require.Less(t, viewIdx, functionIdx)
}

func TestPGMetadataDiffEnumObjectChanges(t *testing.T) {
	tests := []struct {
		name     string
		source   []*metadatapb.EnumTypeMetadata
		target   []*metadatapb.EnumTypeMetadata
		contains []string
	}{
		{
			name:   "create enum",
			source: nil,
			target: []*metadatapb.EnumTypeMetadata{
				{
					Name:   "status",
					Values: []string{"active", "archived"},
				},
			},
			contains: []string{`CREATE TYPE "public"."status" AS ENUM`, `'active'`, `'archived'`},
		},
		{
			name: "drop enum",
			source: []*metadatapb.EnumTypeMetadata{
				{
					Name:   "status",
					Values: []string{"active"},
				},
			},
			target:   nil,
			contains: []string{`DROP TYPE "public"."status"`},
		},
		{
			name: "append enum value",
			source: []*metadatapb.EnumTypeMetadata{
				{
					Name:   "status",
					Values: []string{"active"},
				},
			},
			target: []*metadatapb.EnumTypeMetadata{
				{
					Name:   "status",
					Values: []string{"active", "archived"},
				},
			},
			contains: []string{`ALTER TYPE "public"."status" ADD VALUE 'archived'`},
		},
		{
			name: "insert enum value before existing value",
			source: []*metadatapb.EnumTypeMetadata{
				{
					Name:   "status",
					Values: []string{"active", "archived"},
				},
			},
			target: []*metadatapb.EnumTypeMetadata{
				{
					Name:   "status",
					Values: []string{"active", "pending", "archived"},
				},
			},
			contains: []string{`ALTER TYPE "public"."status" ADD VALUE 'pending' BEFORE 'archived'`},
		},
		{
			name: "removed enum value emits warning",
			source: []*metadatapb.EnumTypeMetadata{
				{
					Name:   "status",
					Values: []string{"active", "archived"},
				},
			},
			target: []*metadatapb.EnumTypeMetadata{
				{
					Name:   "status",
					Values: []string{"active"},
				},
			},
			contains: []string{`-- WARNING: PostgreSQL does not support removing enum values from "public"."status": 'archived'`},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			source := newPGDatabaseMetadataWithEnum(tt.source)
			target := newPGDatabaseMetadataWithEnum(tt.target)

			sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

			require.NoError(t, err)
			for _, want := range tt.contains {
				require.Contains(t, sql, want)
			}
		})
	}
}

func TestPGMetadataDiffSequenceObjectChanges(t *testing.T) {
	tests := []struct {
		name     string
		source   []*metadatapb.SequenceMetadata
		target   []*metadatapb.SequenceMetadata
		contains []string
	}{
		{
			name:   "create sequence",
			source: nil,
			target: []*metadatapb.SequenceMetadata{
				newPGSequenceMetadata(),
			},
			contains: []string{`CREATE SEQUENCE "public"."order_seq"`, "START WITH 1", "INCREMENT BY 1"},
		},
		{
			name: "drop sequence",
			source: []*metadatapb.SequenceMetadata{
				newPGSequenceMetadata(),
			},
			target:   nil,
			contains: []string{`DROP SEQUENCE "public"."order_seq"`},
		},
		{
			name: "alter sequence",
			source: []*metadatapb.SequenceMetadata{
				newPGSequenceMetadata(),
			},
			target: []*metadatapb.SequenceMetadata{
				{
					Name:      "order_seq",
					DataType:  "bigint",
					Start:     "10",
					Increment: "2",
					MinValue:  "1",
					MaxValue:  "9223372036854775807",
					CacheSize: "5",
				},
			},
			contains: []string{`ALTER SEQUENCE "public"."order_seq"`, "START WITH 10", "INCREMENT BY 2", "CACHE 5"},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			source := newPGDatabaseMetadataWithSequences(tt.source)
			target := newPGDatabaseMetadataWithSequences(tt.target)

			sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

			require.NoError(t, err)
			for _, want := range tt.contains {
				require.Contains(t, sql, want)
			}
		})
	}
}

func TestPGMetadataDiffAlterSequenceDropsOwnershipFromMetadata(t *testing.T) {
	source := newPGDatabaseMetadataWithSequences([]*metadatapb.SequenceMetadata{
		{
			Name:        "order_seq",
			DataType:    "bigint",
			Start:       "1",
			Increment:   "1",
			MinValue:    "1",
			MaxValue:    "9223372036854775807",
			CacheSize:   "1",
			OwnerTable:  "orders",
			OwnerColumn: "id",
		},
	})
	target := newPGDatabaseMetadataWithSequences([]*metadatapb.SequenceMetadata{
		newPGSequenceMetadata(),
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `ALTER SEQUENCE "public"."order_seq" OWNED BY NONE`)
}

func TestPGMetadataDiffAlterSequenceAttachesOwnershipAfterCreateTable(t *testing.T) {
	source := newPGDatabaseMetadataWithTablesAndSequences(nil, []*metadatapb.SequenceMetadata{
		newPGSequenceMetadata(),
	})
	target := newPGDatabaseMetadataWithTablesAndSequences([]*metadatapb.TableMetadata{
		{
			Name: "orders",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "bigint", Nullable: false},
			},
		},
	}, []*metadatapb.SequenceMetadata{
		{
			Name:        "order_seq",
			DataType:    "bigint",
			Start:       "1",
			Increment:   "1",
			MinValue:    "1",
			MaxValue:    "9223372036854775807",
			CacheSize:   "1",
			OwnerTable:  "orders",
			OwnerColumn: "id",
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	createTableIdx := strings.Index(sql, `CREATE TABLE "public"."orders"`)
	ownedByIdx := strings.Index(sql, `ALTER SEQUENCE "public"."order_seq" OWNED BY "public"."orders"."id"`)
	require.NotEqual(t, -1, createTableIdx)
	require.NotEqual(t, -1, ownedByIdx)
	require.Less(t, createTableIdx, ownedByIdx)
}

func TestPGMetadataDiffAlterSequenceAttachesOwnershipAfterAddColumn(t *testing.T) {
	source := newPGDatabaseMetadataWithTablesAndSequences([]*metadatapb.TableMetadata{
		{
			Name: "orders",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "name", Type: "text", Nullable: false},
			},
		},
	}, []*metadatapb.SequenceMetadata{
		newPGSequenceMetadata(),
	})
	target := newPGDatabaseMetadataWithTablesAndSequences([]*metadatapb.TableMetadata{
		{
			Name: "orders",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "name", Type: "text", Nullable: false},
				{Name: "id", Type: "bigint", Nullable: false},
			},
		},
	}, []*metadatapb.SequenceMetadata{
		{
			Name:        "order_seq",
			DataType:    "bigint",
			Start:       "1",
			Increment:   "1",
			MinValue:    "1",
			MaxValue:    "9223372036854775807",
			CacheSize:   "1",
			OwnerTable:  "orders",
			OwnerColumn: "id",
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	addColumnIdx := strings.Index(sql, `ALTER TABLE "public"."orders" ADD COLUMN "id" bigint NOT NULL`)
	ownedByIdx := strings.Index(sql, `ALTER SEQUENCE "public"."order_seq" OWNED BY "public"."orders"."id"`)
	require.NotEqual(t, -1, addColumnIdx)
	require.NotEqual(t, -1, ownedByIdx)
	require.Less(t, addColumnIdx, ownedByIdx)
}

func TestPGMetadataDiffAlterSequenceDetachesOwnershipBeforeOwnerTableDrop(t *testing.T) {
	source := newPGDatabaseMetadataWithTablesAndSequences([]*metadatapb.TableMetadata{
		{
			Name: "orders",
			Columns: []*metadatapb.ColumnMetadata{
				{Name: "id", Type: "bigint", Nullable: false},
			},
		},
	}, []*metadatapb.SequenceMetadata{
		{
			Name:        "order_seq",
			DataType:    "bigint",
			Start:       "1",
			Increment:   "1",
			MinValue:    "1",
			MaxValue:    "9223372036854775807",
			CacheSize:   "1",
			OwnerTable:  "orders",
			OwnerColumn: "id",
		},
	})
	target := newPGDatabaseMetadataWithTablesAndSequences(nil, []*metadatapb.SequenceMetadata{
		newPGSequenceMetadata(),
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	detachIdx := strings.Index(sql, `ALTER SEQUENCE "public"."order_seq" OWNED BY NONE`)
	dropTableIdx := strings.Index(sql, `DROP TABLE "public"."orders"`)
	require.NotEqual(t, -1, detachIdx)
	require.NotEqual(t, -1, dropTableIdx)
	require.Less(t, detachIdx, dropTableIdx)
}

func TestPGMetadataDiffMaterializedViewObjectChanges(t *testing.T) {
	tests := []struct {
		name     string
		source   []*metadatapb.MaterializedViewMetadata
		target   []*metadatapb.MaterializedViewMetadata
		contains []string
	}{
		{
			name:   "create materialized view",
			source: nil,
			target: []*metadatapb.MaterializedViewMetadata{
				{Name: "active_users_mv", Definition: "SELECT @@@active_users@@@"},
			},
			contains: []string{`CREATE MATERIALIZED VIEW "public"."active_users_mv" AS`, "SELECT @@@active_users@@@"},
		},
		{
			name: "drop materialized view",
			source: []*metadatapb.MaterializedViewMetadata{
				{Name: "active_users_mv", Definition: "SELECT @@@active_users@@@"},
			},
			target:   nil,
			contains: []string{`DROP MATERIALIZED VIEW "public"."active_users_mv"`},
		},
		{
			name: "alter materialized view",
			source: []*metadatapb.MaterializedViewMetadata{
				{Name: "active_users_mv", Definition: "SELECT @@@old_active_users@@@"},
			},
			target: []*metadatapb.MaterializedViewMetadata{
				{Name: "active_users_mv", Definition: "SELECT @@@new_active_users@@@"},
			},
			contains: []string{
				`DROP MATERIALIZED VIEW "public"."active_users_mv"`,
				`CREATE MATERIALIZED VIEW "public"."active_users_mv" AS`,
				"SELECT @@@new_active_users@@@",
			},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			source := newPGDatabaseMetadataWithMaterializedViews(tt.source)
			target := newPGDatabaseMetadataWithMaterializedViews(tt.target)

			sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

			require.NoError(t, err)
			for _, want := range tt.contains {
				require.Contains(t, sql, want)
			}
		})
	}
}

func TestPGMetadataDiffFunctionObjectChanges(t *testing.T) {
	tests := []struct {
		name     string
		source   []*metadatapb.FunctionMetadata
		target   []*metadatapb.FunctionMetadata
		contains []string
	}{
		{
			name:     "create function",
			source:   nil,
			target:   []*metadatapb.FunctionMetadata{newPGFunctionMetadata("SELECT 1")},
			contains: []string{"CREATE FUNCTION public.calculate_area()", "SELECT 1"},
		},
		{
			name:     "drop function",
			source:   []*metadatapb.FunctionMetadata{newPGFunctionMetadata("SELECT 1")},
			target:   nil,
			contains: []string{`DROP FUNCTION "public"."calculate_area"()`},
		},
		{
			name:     "alter function",
			source:   []*metadatapb.FunctionMetadata{newPGFunctionMetadata("SELECT 1")},
			target:   []*metadatapb.FunctionMetadata{newPGFunctionMetadata("SELECT 2")},
			contains: []string{`DROP FUNCTION "public"."calculate_area"()`, "CREATE FUNCTION public.calculate_area()", "SELECT 2"},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			source := newPGDatabaseMetadataWithRoutines(tt.source, nil)
			target := newPGDatabaseMetadataWithRoutines(tt.target, nil)

			sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

			require.NoError(t, err)
			for _, want := range tt.contains {
				require.Contains(t, sql, want)
			}
		})
	}
}

func TestPGMetadataDiffProcedureObjectChanges(t *testing.T) {
	tests := []struct {
		name     string
		source   []*metadatapb.ProcedureMetadata
		target   []*metadatapb.ProcedureMetadata
		contains []string
	}{
		{
			name:     "create procedure",
			source:   nil,
			target:   []*metadatapb.ProcedureMetadata{newPGProcedureMetadata("NULL")},
			contains: []string{"CREATE PROCEDURE public.refresh_stats()", "NULL"},
		},
		{
			name:     "drop procedure",
			source:   []*metadatapb.ProcedureMetadata{newPGProcedureMetadata("NULL")},
			target:   nil,
			contains: []string{`DROP PROCEDURE "public"."refresh_stats"()`},
		},
		{
			name:     "alter procedure",
			source:   []*metadatapb.ProcedureMetadata{newPGProcedureMetadata("NULL")},
			target:   []*metadatapb.ProcedureMetadata{newPGProcedureMetadata("RAISE NOTICE 'refresh'")},
			contains: []string{`DROP PROCEDURE "public"."refresh_stats"()`, "CREATE PROCEDURE public.refresh_stats()", "RAISE NOTICE 'refresh'"},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			source := newPGDatabaseMetadataWithRoutines(nil, tt.source)
			target := newPGDatabaseMetadataWithRoutines(nil, tt.target)

			sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

			require.NoError(t, err)
			for _, want := range tt.contains {
				require.Contains(t, sql, want)
			}
		})
	}
}

func TestPGMetadataDiffSchemaObjectChanges(t *testing.T) {
	tests := []struct {
		name     string
		source   []*metadatapb.SchemaMetadata
		target   []*metadatapb.SchemaMetadata
		contains []string
	}{
		{
			name:   "create schema",
			source: []*metadatapb.SchemaMetadata{{Name: "public"}},
			target: []*metadatapb.SchemaMetadata{{Name: "public"}, {Name: "analytics"}},
			contains: []string{
				`CREATE SCHEMA IF NOT EXISTS "analytics"`,
			},
		},
		{
			name: "drop schema",
			source: []*metadatapb.SchemaMetadata{
				{Name: "public"},
				{
					Name: "analytics",
					Tables: []*metadatapb.TableMetadata{
						{Name: "events", Columns: []*metadatapb.ColumnMetadata{{Name: "id", Type: "integer", Nullable: false}}},
					},
					Views: []*metadatapb.ViewMetadata{
						{Name: "event_ids", Definition: "SELECT id FROM analytics.events"},
					},
					Sequences: []*metadatapb.SequenceMetadata{
						{Name: "event_id_seq", DataType: "bigint"},
					},
					EnumTypes: []*metadatapb.EnumTypeMetadata{
						{Name: "event_status", Values: []string{"active"}},
					},
				},
			},
			target: []*metadatapb.SchemaMetadata{{Name: "public"}},
			contains: []string{
				`DROP VIEW "analytics"."event_ids"`,
				`DROP TABLE "analytics"."events"`,
				`DROP SEQUENCE "analytics"."event_id_seq"`,
				`DROP TYPE "analytics"."event_status"`,
				`DROP SCHEMA IF EXISTS "analytics"`,
			},
		},
		{
			name:   "drop public schema is ignored",
			source: []*metadatapb.SchemaMetadata{{Name: "public"}},
			target: nil,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			source := newPGDatabaseMetadataWithSchemas(tt.source)
			target := newPGDatabaseMetadataWithSchemas(tt.target)

			sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

			require.NoError(t, err)
			for _, want := range tt.contains {
				require.Contains(t, sql, want)
			}
			require.NotContains(t, sql, "CASCADE")
			require.NotContains(t, sql, `DROP SCHEMA "public"`)
		})
	}
}

func TestPGMetadataDiffExtensionObjectChanges(t *testing.T) {
	tests := []struct {
		name     string
		source   []*metadatapb.ExtensionMetadata
		target   []*metadatapb.ExtensionMetadata
		contains []string
	}{
		{
			name:     "create extension",
			source:   nil,
			target:   []*metadatapb.ExtensionMetadata{{Name: "pg_trgm", Schema: "public", Version: "1.6"}},
			contains: []string{`CREATE EXTENSION IF NOT EXISTS "pg_trgm"`},
		},
		{
			name:     "drop extension",
			source:   []*metadatapb.ExtensionMetadata{{Name: "pg_trgm", Schema: "public", Version: "1.6"}},
			target:   nil,
			contains: []string{`DROP EXTENSION "pg_trgm"`},
		},
		{
			name:     "recreate changed extension",
			source:   []*metadatapb.ExtensionMetadata{{Name: "pg_trgm", Schema: "public", Version: "1.5"}},
			target:   []*metadatapb.ExtensionMetadata{{Name: "pg_trgm", Schema: "public", Version: "1.6"}},
			contains: []string{`DROP EXTENSION "pg_trgm"`, `CREATE EXTENSION IF NOT EXISTS "pg_trgm"`},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			source := newPGDatabaseMetadataWithExtensions(tt.source)
			target := newPGDatabaseMetadataWithExtensions(tt.target)

			sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

			require.NoError(t, err)
			for _, want := range tt.contains {
				require.Contains(t, sql, want)
			}
		})
	}
}

func TestPGMetadataDiffEventTriggerObjectChanges(t *testing.T) {
	tests := []struct {
		name     string
		source   []*metadatapb.EventTriggerMetadata
		target   []*metadatapb.EventTriggerMetadata
		contains []string
	}{
		{
			name:   "create event trigger",
			source: nil,
			target: []*metadatapb.EventTriggerMetadata{
				newPGEventTriggerMetadata("ddl_command_end"),
			},
			contains: []string{`CREATE EVENT TRIGGER "audit_ddl" ON ddl_command_end`},
		},
		{
			name: "drop event trigger",
			source: []*metadatapb.EventTriggerMetadata{
				newPGEventTriggerMetadata("ddl_command_end"),
			},
			target:   nil,
			contains: []string{`DROP EVENT TRIGGER "audit_ddl"`},
		},
		{
			name: "recreate changed event trigger",
			source: []*metadatapb.EventTriggerMetadata{
				newPGEventTriggerMetadata("ddl_command_start"),
			},
			target: []*metadatapb.EventTriggerMetadata{
				newPGEventTriggerMetadata("ddl_command_end"),
			},
			contains: []string{`DROP EVENT TRIGGER "audit_ddl"`, `CREATE EVENT TRIGGER "audit_ddl" ON ddl_command_end`},
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			source := newPGDatabaseMetadataWithEventTriggers(tt.source)
			target := newPGDatabaseMetadataWithEventTriggers(tt.target)

			sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

			require.NoError(t, err)
			for _, want := range tt.contains {
				require.Contains(t, sql, want)
			}
		})
	}
}

func newPGDatabaseMetadataWithView(definition string) *model.DatabaseMetadata {
	return newPGDatabaseMetadata(nil, []*metadatapb.ViewMetadata{
		{
			Name:       "v",
			Definition: definition,
		},
	})
}

func newPGDatabaseMetadataWithEnum(enumTypes []*metadatapb.EnumTypeMetadata) *model.DatabaseMetadata {
	return model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name:      "public",
				EnumTypes: enumTypes,
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
}

func newPGDatabaseMetadataWithSchemas(schemas []*metadatapb.SchemaMetadata) *model.DatabaseMetadata {
	return model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: schemas,
	}, nil, nil, storepb.Engine_POSTGRES, true)
}

func newPGDatabaseMetadataWithExtensions(extensions []*metadatapb.ExtensionMetadata) *model.DatabaseMetadata {
	return model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas:    []*metadatapb.SchemaMetadata{{Name: "public"}},
		Extensions: extensions,
	}, nil, nil, storepb.Engine_POSTGRES, true)
}

func newPGDatabaseMetadataWithEventTriggers(eventTriggers []*metadatapb.EventTriggerMetadata) *model.DatabaseMetadata {
	return model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas:       []*metadatapb.SchemaMetadata{{Name: "public"}},
		EventTriggers: eventTriggers,
	}, nil, nil, storepb.Engine_POSTGRES, true)
}

func newPGEventTriggerMetadata(event string) *metadatapb.EventTriggerMetadata {
	return &metadatapb.EventTriggerMetadata{
		Name:           "audit_ddl",
		Event:          event,
		FunctionSchema: "public",
		FunctionName:   "audit_ddl",
		Enabled:        true,
	}
}

func newPGDatabaseMetadataWithRoutines(functions []*metadatapb.FunctionMetadata, procedures []*metadatapb.ProcedureMetadata) *model.DatabaseMetadata {
	return model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name:       "public",
				Functions:  functions,
				Procedures: procedures,
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
}

func newPGFunctionMetadata(body string) *metadatapb.FunctionMetadata {
	return &metadatapb.FunctionMetadata{
		Name:      "calculate_area",
		Signature: "calculate_area()",
		Definition: "CREATE FUNCTION public.calculate_area() RETURNS integer LANGUAGE sql AS $$ " +
			body + " $$",
	}
}

func newPGProcedureMetadata(body string) *metadatapb.ProcedureMetadata {
	return &metadatapb.ProcedureMetadata{
		Name:      "refresh_stats",
		Signature: "refresh_stats()",
		Definition: "CREATE PROCEDURE public.refresh_stats() LANGUAGE plpgsql AS $$ BEGIN " +
			body + "; END $$",
	}
}

func newCockroachDatabaseMetadataWithFunctions(functions []*metadatapb.FunctionMetadata) *model.DatabaseMetadata {
	return model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name:      "public",
				Functions: functions,
			},
		},
	}, nil, nil, storepb.Engine_COCKROACHDB, true)
}

type alwaysEqualFunctionComparer struct{}

func (*alwaysEqualFunctionComparer) Equal(*metadatapb.FunctionMetadata, *metadatapb.FunctionMetadata) bool {
	return true
}

func (*alwaysEqualFunctionComparer) CompareDetailed(*metadatapb.FunctionMetadata, *metadatapb.FunctionMetadata) (*schema.FunctionComparisonResult, error) {
	return nil, nil
}

func newPGDatabaseMetadataWithMaterializedViews(views []*metadatapb.MaterializedViewMetadata) *model.DatabaseMetadata {
	return model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name:              "public",
				MaterializedViews: views,
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
}

func newPGDatabaseMetadataWithSequences(sequences []*metadatapb.SequenceMetadata) *model.DatabaseMetadata {
	return newPGDatabaseMetadataWithTablesAndSequences(nil, sequences)
}

func newPGDatabaseMetadataWithTablesAndSequences(tables []*metadatapb.TableMetadata, sequences []*metadatapb.SequenceMetadata) *model.DatabaseMetadata {
	return model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name:      "public",
				Tables:    tables,
				Sequences: sequences,
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
}

func newPGSequenceMetadata() *metadatapb.SequenceMetadata {
	return &metadatapb.SequenceMetadata{
		Name:      "order_seq",
		DataType:  "bigint",
		Start:     "1",
		Increment: "1",
		MinValue:  "1",
		MaxValue:  "9223372036854775807",
		CacheSize: "1",
	}
}

func newPGPartitionedOrdersTable(partitions []*metadatapb.TablePartitionMetadata) *metadatapb.TableMetadata {
	return &metadatapb.TableMetadata{
		Name: "orders",
		Columns: []*metadatapb.ColumnMetadata{
			{Name: "id", Type: "integer", Nullable: false},
			{Name: "created_at", Type: "date", Nullable: false},
		},
		Partitions: partitions,
	}
}

func newPGOrdersPartition(name string, value string) *metadatapb.TablePartitionMetadata {
	return &metadatapb.TablePartitionMetadata{
		Name:       name,
		Type:       metadatapb.TablePartitionMetadata_RANGE,
		Expression: "RANGE (created_at)",
		Value:      value,
	}
}

func newPGDatabaseMetadata(tables []*metadatapb.TableMetadata, views []*metadatapb.ViewMetadata) *model.DatabaseMetadata {
	return newDatabaseMetadataWithEngine(storepb.Engine_POSTGRES, tables, views)
}

func newDatabaseMetadataWithEngine(engine storepb.Engine, tables []*metadatapb.TableMetadata, views []*metadatapb.ViewMetadata) *model.DatabaseMetadata {
	return model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name:   "public",
				Tables: tables,
				Views:  views,
			},
		},
	}, nil, nil, engine, true)
}

func newPGDatabaseMetadataWithCompositeTypes(compositeTypes []*metadatapb.CompositeTypeMetadata) *model.DatabaseMetadata {
	return model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name:           "public",
				CompositeTypes: compositeTypes,
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
}

func TestPGMetadataDiffCreateCompositeTypesInDependencyOrder(t *testing.T) {
	source := newPGDatabaseMetadataWithCompositeTypes(nil)
	// aa_nested sorts before zz_base alphabetically but depends on it.
	target := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "aa_nested",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "home", Type: "public.zz_base"},
			},
		},
		{
			Name:    "zz_base",
			Comment: "base type",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "street", Type: "text", Collation: `"C"`, Comment: "street line"},
			},
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `CREATE TYPE "public"."zz_base" AS (`)
	require.Contains(t, sql, `"street" text COLLATE "C"`)
	require.Contains(t, sql, `CREATE TYPE "public"."aa_nested" AS (`)
	require.Contains(t, sql, `COMMENT ON TYPE "public"."zz_base" IS 'base type';`)
	require.Contains(t, sql, `COMMENT ON COLUMN "public"."zz_base"."street" IS 'street line';`)
	require.Less(t,
		strings.Index(sql, `CREATE TYPE "public"."zz_base"`),
		strings.Index(sql, `CREATE TYPE "public"."aa_nested"`),
		"referenced composite must be created before its dependent")
}

func TestPGMetadataDiffDropCompositeTypesInReverseDependencyOrder(t *testing.T) {
	source := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "zz_base",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "x", Type: "integer"},
			},
		},
		{
			Name: "aa_nested",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "home", Type: "public.zz_base"},
			},
		},
	})
	target := newPGDatabaseMetadataWithCompositeTypes(nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `DROP TYPE "public"."aa_nested";`)
	require.Contains(t, sql, `DROP TYPE "public"."zz_base";`)
	require.Less(t,
		strings.Index(sql, `DROP TYPE "public"."aa_nested"`),
		strings.Index(sql, `DROP TYPE "public"."zz_base"`),
		"dependent composite must be dropped before the composite it references")
}

func TestPGMetadataDiffCompositeDropsPrecedeEnumDrops(t *testing.T) {
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				EnumTypes: []*metadatapb.EnumTypeMetadata{
					{Name: "status", Values: []string{"a", "b"}},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "uses_status",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "s", Type: "public.status"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := newPGDatabaseMetadataWithCompositeTypes(nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Less(t,
		strings.Index(sql, `DROP TYPE "public"."uses_status"`),
		strings.Index(sql, `DROP TYPE "public"."status"`),
		"composite referencing an enum must be dropped before the enum")
}

func TestPGMetadataDiffAlterCompositeTypeAttributes(t *testing.T) {
	source := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "addr",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "street", Type: "text"},
				{Name: "city", Type: "character varying(50)"},
				{Name: "dropped_attr", Type: "integer"},
			},
		},
	})
	target := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "addr",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "street", Type: "text"},
				{Name: "city", Type: "character varying(100)"},
				{Name: "zip", Type: "text", Collation: `"C"`},
			},
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `ALTER TYPE "public"."addr" DROP ATTRIBUTE "dropped_attr";`)
	require.Contains(t, sql, `ALTER TYPE "public"."addr" ADD ATTRIBUTE "zip" text COLLATE "C";`)
	require.Contains(t, sql, `ALTER TYPE "public"."addr" ALTER ATTRIBUTE "city" TYPE character varying(100);`)
	require.NotContains(t, sql, `"street"`, "unchanged attribute must not produce DDL")
}

func TestPGMetadataDiffCompositeTypeReorderOnlyWarns(t *testing.T) {
	source := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "addr",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "a", Type: "text"},
				{Name: "b", Type: "integer"},
			},
		},
	})
	target := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "addr",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "b", Type: "integer"},
				{Name: "a", Type: "text"},
			},
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `-- WARNING: PostgreSQL does not support reordering the attributes of "public"."addr"`)
	require.NotContains(t, sql, "ALTER TYPE", "reorder-only change must not produce attribute DDL")
	require.NotContains(t, sql, "DROP TYPE")
}

func TestPGMetadataDiffCompositeTypeCommentChanges(t *testing.T) {
	source := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name:    "addr",
			Comment: "old type comment",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "street", Type: "text", Comment: "old attr comment"},
			},
		},
	})
	target := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name:    "addr",
			Comment: "new type comment",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "street", Type: "text", Comment: "new attr comment"},
			},
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `COMMENT ON TYPE "public"."addr" IS 'new type comment';`)
	require.Contains(t, sql, `COMMENT ON COLUMN "public"."addr"."street" IS 'new attr comment';`)
	require.NotContains(t, sql, "ALTER TYPE", "comment-only change must not produce attribute DDL")
}

func TestPGMetadataDiffIdenticalCompositeTypesProduceNoMigration(t *testing.T) {
	composites := []*metadatapb.CompositeTypeMetadata{
		{
			Name:    "addr",
			Comment: "c",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "street", Type: "text", Collation: `"C"`, Comment: "s"},
			},
		},
	}
	source := newPGDatabaseMetadataWithCompositeTypes(composites)
	target := newPGDatabaseMetadataWithCompositeTypes(composites)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Empty(t, sql)
}

func TestPGMetadataDiffCompositeDropDeferredAfterColumnRetype(t *testing.T) {
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "public.addr", Nullable: true},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "addr",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "street", Type: "text"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "text", Nullable: true},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	dropIdx := strings.Index(sql, `DROP TYPE "public"."addr"`)
	alterIdx := strings.Index(sql, `ALTER COLUMN "c"`)
	require.GreaterOrEqual(t, dropIdx, 0, "composite type must still be dropped: %s", sql)
	require.GreaterOrEqual(t, alterIdx, 0, "column must be retyped: %s", sql)
	require.Greater(t, dropIdx, alterIdx,
		"composite drop must run after the column retype that releases it: %s", sql)
}

func TestPGMetadataDiffSchemaDropIncludesCompositeTypes(t *testing.T) {
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{Name: "public"},
			{
				Name: "s",
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "addr",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "street", Type: "text"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{Name: "public"},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	dropTypeIdx := strings.Index(sql, `DROP TYPE "s"."addr"`)
	dropSchemaIdx := strings.Index(sql, `DROP SCHEMA`)
	require.GreaterOrEqual(t, dropTypeIdx, 0, "schema drop must drop its composite types first: %s", sql)
	require.GreaterOrEqual(t, dropSchemaIdx, 0)
	require.Less(t, dropTypeIdx, dropSchemaIdx, "composite type drop must precede DROP SCHEMA: %s", sql)
}

func TestPGMetadataDiffCompositeTypeMiddleInsertWarns(t *testing.T) {
	source := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "addr",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "a", Type: "text"},
				{Name: "b", Type: "text"},
			},
		},
	})
	target := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "addr",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "a", Type: "text"},
				{Name: "x", Type: "text"},
				{Name: "b", Type: "text"},
			},
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Contains(t, sql, `ALTER TYPE "public"."addr" ADD ATTRIBUTE "x" text;`)
	require.Contains(t, sql, `-- WARNING: PostgreSQL does not support reordering the attributes of "public"."addr"`,
		"middle insertion is not achievable via ADD ATTRIBUTE and must warn: %s", sql)
}

func TestPGMetadataDiffDeferredCompositeDefersReferencedEnumDrop(t *testing.T) {
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "public.addr", Nullable: true},
						},
					},
				},
				EnumTypes: []*metadatapb.EnumTypeMetadata{
					{Name: "status", Values: []string{"a", "b"}},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "addr",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "s", Type: "public.status"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "text", Nullable: true},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	retypeIdx := strings.Index(sql, `ALTER COLUMN "c"`)
	compositeIdx := strings.Index(sql, `DROP TYPE "public"."addr"`)
	enumIdx := strings.Index(sql, `DROP TYPE "public"."status"`)
	require.GreaterOrEqual(t, retypeIdx, 0, "column must be retyped: %s", sql)
	require.GreaterOrEqual(t, compositeIdx, 0, "composite must be dropped: %s", sql)
	require.GreaterOrEqual(t, enumIdx, 0, "enum must be dropped: %s", sql)
	require.Greater(t, compositeIdx, retypeIdx, "deferred composite drop must follow the retype: %s", sql)
	require.Greater(t, enumIdx, compositeIdx, "enum referenced by the deferred composite must drop after it: %s", sql)
}

func TestPGMetadataDiffSchemaDropDeferredWhenCompositeReleasedByRetype(t *testing.T) {
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "s.addr", Nullable: true},
						},
					},
				},
			},
			{
				Name: "s",
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "addr",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "street", Type: "text"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "text", Nullable: true},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	retypeIdx := strings.Index(sql, `ALTER COLUMN "c"`)
	dropTypeIdx := strings.Index(sql, `DROP TYPE "s"."addr"`)
	dropSchemaIdx := strings.Index(sql, `DROP SCHEMA IF EXISTS "s"`)
	require.GreaterOrEqual(t, retypeIdx, 0, "column must be retyped: %s", sql)
	require.GreaterOrEqual(t, dropTypeIdx, 0, "composite in dropped schema must be dropped: %s", sql)
	require.GreaterOrEqual(t, dropSchemaIdx, 0, "schema must be dropped: %s", sql)
	require.Greater(t, dropTypeIdx, retypeIdx, "schema-owned composite drop must follow the retype releasing it: %s", sql)
	require.Greater(t, dropSchemaIdx, dropTypeIdx, "DROP SCHEMA must follow its composite drop: %s", sql)
}

func TestPGMetadataDiffSchemaDropDeferredWhenDeferredCompositeReferencesItsEnum(t *testing.T) {
	// public.addr is deferred (public.t.c retyped away from it) and references
	// s.status; schema s (containing only that enum) must defer its drop too.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "public.addr", Nullable: true},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "addr",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "st", Type: "s.status"},
						},
					},
				},
			},
			{
				Name: "s",
				EnumTypes: []*metadatapb.EnumTypeMetadata{
					{Name: "status", Values: []string{"a"}},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "text", Nullable: true},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	compositeIdx := strings.Index(sql, `DROP TYPE "public"."addr"`)
	enumIdx := strings.Index(sql, `DROP TYPE "s"."status"`)
	schemaIdx := strings.Index(sql, `DROP SCHEMA IF EXISTS "s"`)
	require.GreaterOrEqual(t, compositeIdx, 0, "deferred composite must be dropped: %s", sql)
	require.GreaterOrEqual(t, enumIdx, 0, "enum in dropped schema must be dropped: %s", sql)
	require.GreaterOrEqual(t, schemaIdx, 0, "schema must be dropped: %s", sql)
	require.Greater(t, enumIdx, compositeIdx,
		"enum referenced by the deferred composite must drop after it, even inside a schema drop: %s", sql)
}

func TestPGMetadataDiffDeferredSchemaCompositeDefersReferencedEnumDrop(t *testing.T) {
	// Schema s is deferred (public.t.c retyped away from s.addr); s.addr
	// references public.status, a top-level dropped enum, which therefore
	// must also defer until after the schema drop removes s.addr.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "s.addr", Nullable: true},
						},
					},
				},
				EnumTypes: []*metadatapb.EnumTypeMetadata{
					{Name: "status", Values: []string{"a"}},
				},
			},
			{
				Name: "s",
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "addr",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "st", Type: "public.status"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "text", Nullable: true},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	retypeIdx := strings.Index(sql, `ALTER COLUMN "c"`)
	schemaCompositeIdx := strings.Index(sql, `DROP TYPE "s"."addr"`)
	enumIdx := strings.Index(sql, `DROP TYPE "public"."status"`)
	require.GreaterOrEqual(t, retypeIdx, 0, "column must be retyped: %s", sql)
	require.GreaterOrEqual(t, schemaCompositeIdx, 0, "deferred schema's composite must be dropped: %s", sql)
	require.GreaterOrEqual(t, enumIdx, 0, "enum must be dropped: %s", sql)
	require.Greater(t, schemaCompositeIdx, retypeIdx, "schema-owned composite drop must follow the retype: %s", sql)
	require.Greater(t, enumIdx, schemaCompositeIdx,
		"enum referenced from the deferred schema's composite must drop last: %s", sql)
}

func TestPGMetadataDiffCyclicDeferredSchemaDropsGlobalizeTypeDrops(t *testing.T) {
	// Schemas a and b reference each other: a.ca uses b.eb, b.cb uses a.ea.
	// Both are deferred (a column retype releases a.ca). Globalized type
	// drops must remove both composites before either enum, regardless of
	// the schema-level cycle.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "a.ca", Nullable: true},
						},
					},
				},
			},
			{
				Name: "a",
				EnumTypes: []*metadatapb.EnumTypeMetadata{
					{Name: "ea", Values: []string{"x"}},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "ca",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "v", Type: "b.eb"},
						},
					},
				},
			},
			{
				Name: "b",
				EnumTypes: []*metadatapb.EnumTypeMetadata{
					{Name: "eb", Values: []string{"y"}},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "cb",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "v", Type: "a.ea"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "text", Nullable: true},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	caIdx := strings.Index(sql, `DROP TYPE "a"."ca"`)
	cbIdx := strings.Index(sql, `DROP TYPE "b"."cb"`)
	eaIdx := strings.Index(sql, `DROP TYPE "a"."ea"`)
	ebIdx := strings.Index(sql, `DROP TYPE "b"."eb"`)
	require.GreaterOrEqual(t, caIdx, 0, "a.ca must be dropped: %s", sql)
	require.GreaterOrEqual(t, cbIdx, 0, "b.cb must be dropped: %s", sql)
	require.GreaterOrEqual(t, eaIdx, 0, "a.ea must be dropped: %s", sql)
	require.GreaterOrEqual(t, ebIdx, 0, "b.eb must be dropped: %s", sql)
	require.Greater(t, eaIdx, cbIdx, "a.ea must drop after b.cb which references it: %s", sql)
	require.Greater(t, ebIdx, caIdx, "b.eb must drop after a.ca which references it: %s", sql)
	dropSchemaAIdx := strings.Index(sql, `DROP SCHEMA IF EXISTS "a"`)
	dropSchemaBIdx := strings.Index(sql, `DROP SCHEMA IF EXISTS "b"`)
	require.Greater(t, dropSchemaAIdx, eaIdx, "DROP SCHEMA a must come after its types: %s", sql)
	require.Greater(t, dropSchemaBIdx, ebIdx, "DROP SCHEMA b must come after its types: %s", sql)
}

func TestPGMetadataDiffImmediateSchemaDropOrdersTypesGlobally(t *testing.T) {
	// Dropping schema s (whose composite references top-level types) together
	// with those top-level types: s.child must drop before public.base and
	// public.status regardless of the schema-drop boundary.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				EnumTypes: []*metadatapb.EnumTypeMetadata{
					{Name: "status", Values: []string{"a"}},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "base",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "x", Type: "integer"},
						},
					},
				},
			},
			{
				Name: "s",
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "child",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "b", Type: "public.base"},
							{Name: "st", Type: "public.status"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{Name: "public"},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	childIdx := strings.Index(sql, `DROP TYPE "s"."child"`)
	baseIdx := strings.Index(sql, `DROP TYPE "public"."base"`)
	statusIdx := strings.Index(sql, `DROP TYPE "public"."status"`)
	schemaIdx := strings.Index(sql, `DROP SCHEMA IF EXISTS "s"`)
	require.GreaterOrEqual(t, childIdx, 0, "schema-owned composite must be dropped: %s", sql)
	require.GreaterOrEqual(t, baseIdx, 0)
	require.GreaterOrEqual(t, statusIdx, 0)
	require.GreaterOrEqual(t, schemaIdx, 0)
	require.Less(t, childIdx, baseIdx, "dependent s.child must drop before public.base: %s", sql)
	require.Less(t, childIdx, statusIdx, "dependent s.child must drop before public.status: %s", sql)
	require.Greater(t, schemaIdx, childIdx, "DROP SCHEMA must come after its composite drop: %s", sql)
}

func TestPGMetadataDiffCompositeCreateWaitsForTableRowType(t *testing.T) {
	// Creating table t and composites c1(public.t) and c2(c1): both
	// composites must be created after the table.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{Name: "public"},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "id", Type: "integer"},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "c1",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "r", Type: "public.t"},
						},
					},
					{
						Name: "c2",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "w", Type: "public.c1"},
						},
					},
					{
						Name: "plain",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "x", Type: "integer"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	tableIdx := strings.Index(sql, `CREATE TABLE "public"."t"`)
	c1Idx := strings.Index(sql, `CREATE TYPE "public"."c1"`)
	c2Idx := strings.Index(sql, `CREATE TYPE "public"."c2"`)
	plainIdx := strings.Index(sql, `CREATE TYPE "public"."plain"`)
	require.GreaterOrEqual(t, tableIdx, 0, "table must be created: %s", sql)
	require.GreaterOrEqual(t, c1Idx, 0)
	require.GreaterOrEqual(t, c2Idx, 0)
	require.GreaterOrEqual(t, plainIdx, 0)
	require.Greater(t, c1Idx, tableIdx, "composite using the table row type must be created after the table: %s", sql)
	require.Greater(t, c2Idx, c1Idx, "dependent of a post-table composite must follow it: %s", sql)
	require.Less(t, plainIdx, tableIdx, "independent composite stays before tables: %s", sql)
}

func TestPGMetadataDiffDeferredCompositeDefersItsCompositeDependency(t *testing.T) {
	// child(public.base) is deferred by a column retype; base, also dropped,
	// must defer too so it drops after child.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "public.child", Nullable: true},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "base",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "x", Type: "integer"},
						},
					},
					{
						Name: "child",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "b", Type: "public.base"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "text", Nullable: true},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	retypeIdx := strings.Index(sql, `ALTER COLUMN "c"`)
	childIdx := strings.Index(sql, `DROP TYPE "public"."child"`)
	baseIdx := strings.Index(sql, `DROP TYPE "public"."base"`)
	require.GreaterOrEqual(t, retypeIdx, 0)
	require.GreaterOrEqual(t, childIdx, 0)
	require.GreaterOrEqual(t, baseIdx, 0)
	require.Greater(t, childIdx, retypeIdx, "deferred child drops after the retype: %s", sql)
	require.Greater(t, baseIdx, childIdx, "base referenced by deferred child must defer and drop after it: %s", sql)
}

func TestPGMetadataDiffRowTypeCompositeDropsBeforeTable(t *testing.T) {
	// Dropping table t and composite c(r public.t): the composite must drop
	// before the table whose row type it references.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "id", Type: "integer"},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "c",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "r", Type: "public.t"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{Name: "public"},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	typeIdx := strings.Index(sql, `DROP TYPE "public"."c"`)
	tableIdx := strings.Index(sql, `DROP TABLE "public"."t"`)
	require.GreaterOrEqual(t, typeIdx, 0, "composite must be dropped: %s", sql)
	require.GreaterOrEqual(t, tableIdx, 0, "table must be dropped: %s", sql)
	require.Less(t, typeIdx, tableIdx, "row-type-referencing composite must drop before the table: %s", sql)
}

func TestPGMetadataDiffCompositeAlterWaitsForNewTableRowType(t *testing.T) {
	// Existing composite gains an attribute typed with a row type of a table
	// created in the same migration; the alter must follow CREATE TABLE.
	source := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "c",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "x", Type: "integer"},
			},
		},
	})
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "id", Type: "integer"},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "c",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "x", Type: "integer"},
							{Name: "r", Type: "public.t"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	tableIdx := strings.Index(sql, `CREATE TABLE "public"."t"`)
	alterIdx := strings.Index(sql, `ALTER TYPE "public"."c" ADD ATTRIBUTE "r" public.t;`)
	require.GreaterOrEqual(t, tableIdx, 0, "table must be created: %s", sql)
	require.GreaterOrEqual(t, alterIdx, 0, "attribute must be added: %s", sql)
	require.Greater(t, alterIdx, tableIdx, "composite alter must follow the table creation it references: %s", sql)
}

func TestPGMetadataDiffCreateSandwichTableCompositeTable(t *testing.T) {
	// r (row-type provider) -> c (composite using r) -> holder (table using c):
	// creation order must interleave through the shared graph.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{{Name: "public"}},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "r",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "id", Type: "integer"},
						},
					},
					{
						Name: "holder",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "x", Type: "public.c", Nullable: true},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "c",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "row", Type: "public.r"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	rIdx := strings.Index(sql, `CREATE TABLE "public"."r"`)
	cIdx := strings.Index(sql, `CREATE TYPE "public"."c"`)
	holderIdx := strings.Index(sql, `CREATE TABLE "public"."holder"`)
	require.GreaterOrEqual(t, rIdx, 0)
	require.GreaterOrEqual(t, cIdx, 0)
	require.GreaterOrEqual(t, holderIdx, 0)
	require.Greater(t, cIdx, rIdx, "composite must follow its row-type provider table: %s", sql)
	require.Greater(t, holderIdx, cIdx, "consumer table must follow the composite: %s", sql)
}

func TestPGMetadataDiffDropSandwichTableCompositeTable(t *testing.T) {
	// holder (table using c) -> c (composite using r) -> r: drop order must
	// interleave through the shared graph.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "r",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "id", Type: "integer"},
						},
					},
					{
						Name: "holder",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "x", Type: "public.c", Nullable: true},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "c",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "row", Type: "public.r"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{{Name: "public"}},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	holderIdx := strings.Index(sql, `DROP TABLE "public"."holder"`)
	cIdx := strings.Index(sql, `DROP TYPE "public"."c"`)
	rIdx := strings.Index(sql, `DROP TABLE "public"."r"`)
	require.GreaterOrEqual(t, holderIdx, 0)
	require.GreaterOrEqual(t, cIdx, 0)
	require.GreaterOrEqual(t, rIdx, 0)
	require.Less(t, holderIdx, cIdx, "table using the composite must drop before it: %s", sql)
	require.Less(t, cIdx, rIdx, "composite must drop before its row-type provider table: %s", sql)
}

func TestPGMetadataDiffDeferredSchemaCompositeDefersTopLevelDependency(t *testing.T) {
	// Schema s is deferred (retype releases s.child); s.child references
	// top-level dropped public.base, which must defer too.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "s.child", Nullable: true},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "base",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "x", Type: "integer"},
						},
					},
				},
			},
			{
				Name: "s",
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "child",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "b", Type: "public.base"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c", Type: "text", Nullable: true},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	retypeIdx := strings.Index(sql, `ALTER COLUMN "c"`)
	childIdx := strings.Index(sql, `DROP TYPE "s"."child"`)
	baseIdx := strings.Index(sql, `DROP TYPE "public"."base"`)
	require.GreaterOrEqual(t, retypeIdx, 0)
	require.GreaterOrEqual(t, childIdx, 0)
	require.GreaterOrEqual(t, baseIdx, 0)
	require.Greater(t, childIdx, retypeIdx, "deferred schema composite drops after the retype: %s", sql)
	require.Greater(t, baseIdx, childIdx, "top-level base referenced by the deferred schema composite must defer and drop after it: %s", sql)
}

func TestPGMetadataDiffCompositeDropDeferredAfterColumnDrop(t *testing.T) {
	// Dropping the only column using a composite (on a surviving table)
	// releases it in writeDropAlterTableObjects; the composite drop must
	// defer past that.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "id", Type: "integer"},
							{Name: "c", Type: "public.addr", Nullable: true},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "addr",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "street", Type: "text"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "id", Type: "integer"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	columnDropIdx := strings.Index(sql, `DROP COLUMN "c"`)
	typeDropIdx := strings.Index(sql, `DROP TYPE "public"."addr"`)
	require.GreaterOrEqual(t, columnDropIdx, 0, "column must be dropped: %s", sql)
	require.GreaterOrEqual(t, typeDropIdx, 0, "composite must be dropped: %s", sql)
	require.Greater(t, typeDropIdx, columnDropIdx,
		"composite drop must follow the column drop that releases it: %s", sql)
}

func TestPGMetadataDiffReleasingCompositeAlterPrecedesTableDrop(t *testing.T) {
	// Retyping c.r away from public.t's row type releases the dropped table;
	// the releasing ALTER must precede DROP TABLE.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "id", Type: "integer"},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "c",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "r", Type: "public.t"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "c",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "r", Type: "text"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	alterIdx := strings.Index(sql, `ALTER TYPE "public"."c" ALTER ATTRIBUTE "r" TYPE text;`)
	dropIdx := strings.Index(sql, `DROP TABLE "public"."t"`)
	require.GreaterOrEqual(t, alterIdx, 0, "releasing alter must be emitted: %s", sql)
	require.GreaterOrEqual(t, dropIdx, 0, "table must be dropped: %s", sql)
	require.Less(t, alterIdx, dropIdx, "releasing alter must precede the table drop: %s", sql)
	require.Equal(t, strings.Count(sql, `ALTER ATTRIBUTE "r"`), 1, "releasing alter must not be emitted twice: %s", sql)
}

func TestPGMetadataDiffCompositeArrayColumnOrdersCreate(t *testing.T) {
	// Column sync renders composite-array columns as the bare "_name" form;
	// the create graph must still order the composite first. zz table name
	// sorts after the composite to defeat accidental ordering.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{{Name: "public"}},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "aa_holder",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "xs", Type: "_zz_addr", Nullable: true},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "zz_addr",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "street", Type: "text"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	typeIdx := strings.Index(sql, `CREATE TYPE "public"."zz_addr"`)
	tableIdx := strings.Index(sql, `CREATE TABLE "public"."aa_holder"`)
	require.GreaterOrEqual(t, typeIdx, 0)
	require.GreaterOrEqual(t, tableIdx, 0)
	require.Less(t, typeIdx, tableIdx, "array-typed column must order the composite before the table: %s", sql)
}

func TestPGMetadataDiffSchemaDropEmitsCompositeDropOnce(t *testing.T) {
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{Name: "public"},
			{
				Name: "s",
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "addr",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "street", Type: "text"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{{Name: "public"}},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Equal(t, 1, strings.Count(sql, `DROP TYPE "s"."addr";`),
		"schema-owned composite must drop exactly once: %s", sql)
}

func TestPGMetadataDiffReleasingRetypeToCreatedTableSplitsDropReadd(t *testing.T) {
	// c.r moves from dropped old_t's row type to created new_t's row type:
	// the drop phase removes the attribute, the create phase re-adds it
	// after the new table exists.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "old_t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "id", Type: "integer"},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "c",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "r", Type: "public.old_t"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "new_t",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "id", Type: "integer"},
						},
					},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "c",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "r", Type: "public.new_t"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	dropAttrIdx := strings.Index(sql, `ALTER TYPE "public"."c" DROP ATTRIBUTE "r";`)
	dropTableIdx := strings.Index(sql, `DROP TABLE "public"."old_t"`)
	createTableIdx := strings.Index(sql, `CREATE TABLE "public"."new_t"`)
	addAttrIdx := strings.Index(sql, `ALTER TYPE "public"."c" ADD ATTRIBUTE "r" public.new_t;`)
	require.GreaterOrEqual(t, dropAttrIdx, 0, "releasing drop must be emitted: %s", sql)
	require.GreaterOrEqual(t, dropTableIdx, 0)
	require.GreaterOrEqual(t, createTableIdx, 0)
	require.GreaterOrEqual(t, addAttrIdx, 0, "attribute must be re-added: %s", sql)
	require.Less(t, dropAttrIdx, dropTableIdx, "attribute drop must precede the table drop: %s", sql)
	require.Greater(t, addAttrIdx, createTableIdx, "re-add must follow the new table: %s", sql)
	require.NotContains(t, sql, `ALTER ATTRIBUTE "r"`, "no premature retype may be emitted: %s", sql)
}

func TestPGMetadataDiffCompositeCreateWaitsForViewRowType(t *testing.T) {
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{{Name: "public"}},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Views: []*metadatapb.ViewMetadata{
					{Name: "v", Definition: "SELECT 1 AS a"},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "c",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "row", Type: "public.v"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	viewIdx := strings.Index(sql, `VIEW "public"."v"`)
	typeIdx := strings.Index(sql, `CREATE TYPE "public"."c"`)
	require.GreaterOrEqual(t, viewIdx, 0, "view must be created: %s", sql)
	require.GreaterOrEqual(t, typeIdx, 0, "composite must be created: %s", sql)
	require.Greater(t, typeIdx, viewIdx, "composite using a view row type must follow the view: %s", sql)
}

func TestPGMetadataDiffCompositeDropReleasedByCompositeAlterDefers(t *testing.T) {
	// parent's alter (child -> text) releases dropped child only in the
	// create phase, so child's drop must defer past it.
	source := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "child",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "x", Type: "integer"},
			},
		},
		{
			Name: "parent",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "c", Type: "public.child"},
			},
		},
	})
	target := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "parent",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "c", Type: "text"},
			},
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	alterIdx := strings.Index(sql, `ALTER TYPE "public"."parent" ALTER ATTRIBUTE "c" TYPE text;`)
	dropIdx := strings.Index(sql, `DROP TYPE "public"."child"`)
	require.GreaterOrEqual(t, alterIdx, 0, "releasing alter must be emitted: %s", sql)
	require.GreaterOrEqual(t, dropIdx, 0, "child must be dropped: %s", sql)
	require.Greater(t, dropIdx, alterIdx, "child drop must follow the alter that releases it: %s", sql)
}

func TestPGMetadataDiffEnumDropReleasedByCompositeAlterDefers(t *testing.T) {
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				EnumTypes: []*metadatapb.EnumTypeMetadata{
					{Name: "status", Values: []string{"a"}},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "addr",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "s", Type: "public.status"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "addr",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "s", Type: "text"},
			},
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	alterIdx := strings.Index(sql, `ALTER TYPE "public"."addr" ALTER ATTRIBUTE "s" TYPE text;`)
	dropIdx := strings.Index(sql, `DROP TYPE "public"."status"`)
	require.GreaterOrEqual(t, alterIdx, 0, "releasing alter must be emitted: %s", sql)
	require.GreaterOrEqual(t, dropIdx, 0, "enum must be dropped: %s", sql)
	require.Greater(t, dropIdx, alterIdx, "enum drop must follow the alter that releases it: %s", sql)
}

func TestPGMetadataDiffReleasingAlterPrecedesViewDrop(t *testing.T) {
	// c.row moves off dropped view v's row type; the early alter must
	// precede DROP VIEW.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Views: []*metadatapb.ViewMetadata{
					{Name: "v", Definition: "SELECT 1 AS a"},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "c",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "row", Type: "public.v"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "c",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "row", Type: "text"},
			},
		},
	})

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	alterIdx := strings.Index(sql, `ALTER TYPE "public"."c" ALTER ATTRIBUTE "row" TYPE text;`)
	dropViewIdx := strings.Index(sql, `DROP VIEW "public"."v"`)
	require.GreaterOrEqual(t, alterIdx, 0, "releasing alter must be emitted: %s", sql)
	require.GreaterOrEqual(t, dropViewIdx, 0, "view must be dropped: %s", sql)
	require.Less(t, alterIdx, dropViewIdx, "releasing alter must precede the view drop: %s", sql)
}

func TestPGMetadataDiffCompositeAlterWaitsForCreatedViewRowType(t *testing.T) {
	source := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name: "c",
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "x", Type: "integer"},
			},
		},
	})
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Views: []*metadatapb.ViewMetadata{
					{Name: "v", Definition: "SELECT 1 AS a"},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "c",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "x", Type: "integer"},
							{Name: "row", Type: "public.v"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	viewIdx := strings.Index(sql, `VIEW "public"."v"`)
	alterIdx := strings.Index(sql, `ALTER TYPE "public"."c" ADD ATTRIBUTE "row" public.v;`)
	require.GreaterOrEqual(t, viewIdx, 0, "view must be created: %s", sql)
	require.GreaterOrEqual(t, alterIdx, 0, "attribute must be added: %s", sql)
	require.Greater(t, alterIdx, viewIdx, "alter gaining the view row type must follow the view: %s", sql)
}

func TestPGMetadataDiffCompositeDropPrecedesReferencedViewDrop(t *testing.T) {
	// Dropping both c(row public.v) and v: the composite must drop first
	// despite the blanket views-before-composites edges.
	source := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Views: []*metadatapb.ViewMetadata{
					{Name: "v", Definition: "SELECT 1 AS a"},
				},
				CompositeTypes: []*metadatapb.CompositeTypeMetadata{
					{
						Name: "c",
						Attributes: []*metadatapb.CompositeTypeAttribute{
							{Name: "row", Type: "public.v"},
						},
					},
				},
			},
		},
	}, nil, nil, storepb.Engine_POSTGRES, true)
	target := model.NewDatabaseMetadata(&metadatapb.DatabaseSchemaMetadata{
		Schemas: []*metadatapb.SchemaMetadata{{Name: "public"}},
	}, nil, nil, storepb.Engine_POSTGRES, true)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	typeIdx := strings.Index(sql, `DROP TYPE "public"."c"`)
	viewIdx := strings.Index(sql, `DROP VIEW "public"."v"`)
	require.GreaterOrEqual(t, typeIdx, 0, "composite must be dropped: %s", sql)
	require.GreaterOrEqual(t, viewIdx, 0, "view must be dropped: %s", sql)
	require.Less(t, typeIdx, viewIdx, "composite must drop before the view whose row type it references: %s", sql)
}

func TestFilterPostgresArchiveSchemaKeepsCompositeTypes(t *testing.T) {
	diff := &schema.MetadataDiff{
		CompositeTypeChanges: []*schema.CompositeTypeDiff{
			{Action: schema.MetadataDiffActionCreate, SchemaName: "public", CompositeTypeName: "addr"},
			{Action: schema.MetadataDiffActionCreate, SchemaName: "bbdataarchive", CompositeTypeName: "archived"},
		},
	}

	filtered := schema.FilterPostgresArchiveSchema(diff)

	require.Len(t, filtered.CompositeTypeChanges, 1)
	require.Equal(t, "public", filtered.CompositeTypeChanges[0].SchemaName)
}

func TestPGMetadataDiffSkipDumpCompositeTypesIgnored(t *testing.T) {
	source := newPGDatabaseMetadataWithCompositeTypes([]*metadatapb.CompositeTypeMetadata{
		{
			Name:     "ext_owned",
			SkipDump: true,
			Attributes: []*metadatapb.CompositeTypeAttribute{
				{Name: "x", Type: "integer"},
			},
		},
	})
	target := newPGDatabaseMetadataWithCompositeTypes(nil)

	sql, err := schema.DiffMigration(storepb.Engine_POSTGRES, source, target)

	require.NoError(t, err)
	require.Empty(t, sql, "skip_dump composite types must not be diffed")
}