Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
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")
}