Skip to content

Commit 8e552f2

Browse files
stehesselstehessel
committed
ROX-33816: add db migration for soft deletes
1 parent 9aec835 commit 8e552f2

File tree

9 files changed

+290
-39
lines changed

9 files changed

+290
-39
lines changed

‎central/graphql/resolvers/generated.go‎

Lines changed: 12 additions & 16 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎migrator/migrations/m_223_to_m_224_add_deleted_at_index_and_set_deployment_state/migration.go‎

Lines changed: 24 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎migrator/migrations/m_223_to_m_224_add_deleted_at_index_and_set_deployment_state/migration_impl.go‎

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
package m223tom224
2+
3+
import (
4+
"context"
5+
6+
"github.com/stackrox/rox/migrator/migrations/m_223_to_m_224_add_deleted_at_index_and_set_deployment_state/schema"
7+
"github.com/stackrox/rox/migrator/types"
8+
"github.com/stackrox/rox/pkg/postgres/pgutils"
9+
"github.com/stackrox/rox/pkg/sac"
10+
)
11+
12+
const (
13+
addIndexStmt = "CREATE INDEX IF NOT EXISTS deployments_deletedat ON deployments (deletedat)"
14+
setActiveStateStmt = "UPDATE deployments SET state = 1 WHERE state IS NULL OR state = 0"
15+
)
16+
17+
func migrate(database *types.Databases) error {
18+
ctx := sac.WithAllAccess(context.Background())
19+
20+
// Add deletedat and state columns if they do not already exist.
21+
pgutils.CreateTableFromModel(ctx, database.GormDB, schema.CreateTableDeploymentsStmt)
22+
23+
// Add an index on deletedat for efficient soft-delete queries.
24+
if _, err := database.PostgresDB.Exec(database.DBCtx, addIndexStmt); err != nil {
25+
return err
26+
}
27+
28+
// Set all existing deployments with STATE_UNSPECIFIED (0) to STATE_ACTIVE (1).
29+
if _, err := database.PostgresDB.Exec(database.DBCtx, setActiveStateStmt); err != nil {
30+
return err
31+
}
32+
33+
return nil
34+
}

‎migrator/migrations/m_223_to_m_224_add_deleted_at_index_and_set_deployment_state/migration_test.go‎

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,102 @@
1+
//go:build sql_integration
2+
3+
package m223tom224
4+
5+
import (
6+
"context"
7+
"testing"
8+
9+
"github.com/stackrox/rox/generated/storage"
10+
updatedSchema "github.com/stackrox/rox/migrator/migrations/m_223_to_m_224_add_deleted_at_index_and_set_deployment_state/schema"
11+
oldSchema "github.com/stackrox/rox/migrator/migrations/m_223_to_m_224_add_deleted_at_index_and_set_deployment_state/test/schema"
12+
pghelper "github.com/stackrox/rox/migrator/migrations/postgreshelper"
13+
"github.com/stackrox/rox/migrator/types"
14+
"github.com/stackrox/rox/pkg/postgres/pgutils"
15+
"github.com/stackrox/rox/pkg/sac"
16+
"github.com/stackrox/rox/pkg/uuid"
17+
"github.com/stretchr/testify/suite"
18+
)
19+
20+
type migrationTestSuite struct {
21+
suite.Suite
22+
23+
db *pghelper.TestPostgres
24+
ctx context.Context
25+
}
26+
27+
func TestMigration(t *testing.T) {
28+
suite.Run(t, new(migrationTestSuite))
29+
}
30+
31+
func (s *migrationTestSuite) SetupSuite() {
32+
s.ctx = sac.WithAllAccess(context.Background())
33+
s.db = pghelper.ForT(s.T(), false)
34+
}
35+
36+
func (s *migrationTestSuite) TestMigration() {
37+
db := s.db.DB
38+
dbs := &types.Databases{
39+
GormDB: s.db.GetGormDB(),
40+
PostgresDB: db,
41+
DBCtx: s.ctx,
42+
}
43+
44+
// Create the old schema (without deletedat and state columns).
45+
pgutils.CreateTableFromModel(s.ctx, dbs.GormDB, oldSchema.CreateTableDeploymentsStmt)
46+
47+
// Insert test deployments.
48+
numDeployments := 5
49+
deploymentIDs := make([]string, numDeployments)
50+
for i := range numDeployments {
51+
id := uuid.NewV4().String()
52+
deploymentIDs[i] = id
53+
54+
dep := &storage.Deployment{Id: id, Name: "test-deployment"}
55+
serialized, err := dep.MarshalVT()
56+
s.Require().NoError(err)
57+
58+
_, err = db.Exec(s.ctx,
59+
"INSERT INTO deployments (id, name, hash, type, namespace, namespaceid, orchestratorcomponent, created, clusterid, clustername, priority, serviceaccount, serviceaccountpermissionlevel, riskscore, platformcomponent, serialized) VALUES ($1, $2, 0, 'Deployment', 'default', $3, false, now(), $4, 'test-cluster', 0, 'default', 0, 0, false, $5)",
60+
id, dep.GetName(), uuid.NewV4().String(), uuid.NewV4().String(), serialized,
61+
)
62+
s.Require().NoError(err)
63+
}
64+
65+
// Apply the new schema to add deletedat and state columns.
66+
pgutils.CreateTableFromModel(s.ctx, dbs.GormDB, updatedSchema.CreateTableDeploymentsStmt)
67+
68+
// Verify state is 0 (STATE_UNSPECIFIED) before migration.
69+
var unspecifiedCount int
70+
err := db.QueryRow(s.ctx, "SELECT COUNT(*) FROM deployments WHERE state = 0").Scan(&unspecifiedCount)
71+
s.Require().NoError(err)
72+
s.Equal(numDeployments, unspecifiedCount)
73+
74+
// Run migration.
75+
s.Require().NoError(migration.Run(dbs))
76+
77+
// Verify all deployments now have state = 1 (STATE_ACTIVE).
78+
var activeCount int
79+
err = db.QueryRow(s.ctx, "SELECT COUNT(*) FROM deployments WHERE state = 1").Scan(&activeCount)
80+
s.Require().NoError(err)
81+
s.Equal(numDeployments, activeCount)
82+
83+
// Verify no deployments have state = 0 (STATE_UNSPECIFIED).
84+
err = db.QueryRow(s.ctx, "SELECT COUNT(*) FROM deployments WHERE state = 0").Scan(&unspecifiedCount)
85+
s.Require().NoError(err)
86+
s.Equal(0, unspecifiedCount)
87+
88+
// Verify index exists on deletedat.
89+
var indexExists bool
90+
err = db.QueryRow(s.ctx,
91+
"SELECT EXISTS(SELECT 1 FROM pg_indexes WHERE tablename = 'deployments' AND indexname = 'deployments_deletedat')").Scan(&indexExists)
92+
s.Require().NoError(err)
93+
s.True(indexExists, "index deployments_deletedat should exist")
94+
95+
// Run migration again to verify idempotency.
96+
s.Require().NoError(migration.Run(dbs))
97+
98+
// Verify state is still STATE_ACTIVE after second run.
99+
err = db.QueryRow(s.ctx, "SELECT COUNT(*) FROM deployments WHERE state = 1").Scan(&activeCount)
100+
s.Require().NoError(err)
101+
s.Equal(numDeployments, activeCount)
102+
}

‎migrator/migrations/m_223_to_m_224_add_deleted_at_index_and_set_deployment_state/schema/deployments.go‎

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,50 @@
1+
// Code originally generated by pg-bindings generator.
2+
3+
package schema
4+
5+
import (
6+
"time"
7+
8+
"github.com/lib/pq"
9+
"github.com/stackrox/rox/generated/storage"
10+
"github.com/stackrox/rox/pkg/postgres"
11+
)
12+
13+
var (
14+
// CreateTableDeploymentsStmt holds the create statement for table `deployments`.
15+
CreateTableDeploymentsStmt = &postgres.CreateStmts{
16+
GormModel: (*Deployments)(nil),
17+
Children: []*postgres.CreateStmts{},
18+
}
19+
)
20+
21+
const (
22+
// DeploymentsTableName specifies the name of the table in postgres.
23+
DeploymentsTableName = "deployments"
24+
)
25+
26+
// Deployments holds the Gorm model for Postgres table `deployments`.
27+
type Deployments struct {
28+
ID string `gorm:"column:id;type:uuid;primaryKey"`
29+
Name string `gorm:"column:name;type:varchar"`
30+
Hash uint64 `gorm:"column:hash;type:numeric"`
31+
Type string `gorm:"column:type;type:varchar"`
32+
Namespace string `gorm:"column:namespace;type:varchar;index:deployments_sac_filter,type:btree"`
33+
NamespaceID string `gorm:"column:namespaceid;type:uuid"`
34+
OrchestratorComponent bool `gorm:"column:orchestratorcomponent;type:bool"`
35+
Labels map[string]string `gorm:"column:labels;type:jsonb"`
36+
PodLabels map[string]string `gorm:"column:podlabels;type:jsonb"`
37+
Created *time.Time `gorm:"column:created;type:timestamp"`
38+
ClusterID string `gorm:"column:clusterid;type:uuid;index:deployments_sac_filter,type:btree"`
39+
ClusterName string `gorm:"column:clustername;type:varchar"`
40+
Annotations map[string]string `gorm:"column:annotations;type:jsonb"`
41+
Priority int64 `gorm:"column:priority;type:bigint"`
42+
ImagePullSecrets *pq.StringArray `gorm:"column:imagepullsecrets;type:text[]"`
43+
ServiceAccount string `gorm:"column:serviceaccount;type:varchar"`
44+
ServiceAccountPermissionLevel storage.PermissionLevel `gorm:"column:serviceaccountpermissionlevel;type:integer"`
45+
RiskScore float32 `gorm:"column:riskscore;type:numeric;index:deployments_riskscore,type:btree"`
46+
PlatformComponent bool `gorm:"column:platformcomponent;type:bool"`
47+
DeletedAt *time.Time `gorm:"column:deletedat;type:timestamp"`
48+
State storage.DeploymentState `gorm:"column:state;type:integer"`
49+
Serialized []byte `gorm:"column:serialized;type:bytea"`
50+
}

‎migrator/migrations/m_223_to_m_224_add_deleted_at_index_and_set_deployment_state/test/schema/deployments.go‎

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
// Code originally generated by pg-bindings generator.
2+
// This is the frozen schema before the migration (without deletedat and state columns).
3+
4+
package schema
5+
6+
import (
7+
"time"
8+
9+
"github.com/lib/pq"
10+
"github.com/stackrox/rox/generated/storage"
11+
"github.com/stackrox/rox/pkg/postgres"
12+
)
13+
14+
var (
15+
// CreateTableDeploymentsStmt holds the create statement for table `deployments`.
16+
CreateTableDeploymentsStmt = &postgres.CreateStmts{
17+
GormModel: (*Deployments)(nil),
18+
Children: []*postgres.CreateStmts{},
19+
}
20+
)
21+
22+
// Deployments holds the Gorm model for Postgres table `deployments`.
23+
type Deployments struct {
24+
ID string `gorm:"column:id;type:uuid;primaryKey"`
25+
Name string `gorm:"column:name;type:varchar"`
26+
Hash uint64 `gorm:"column:hash;type:numeric"`
27+
Type string `gorm:"column:type;type:varchar"`
28+
Namespace string `gorm:"column:namespace;type:varchar;index:deployments_sac_filter,type:btree"`
29+
NamespaceID string `gorm:"column:namespaceid;type:uuid"`
30+
OrchestratorComponent bool `gorm:"column:orchestratorcomponent;type:bool"`
31+
Labels map[string]string `gorm:"column:labels;type:jsonb"`
32+
PodLabels map[string]string `gorm:"column:podlabels;type:jsonb"`
33+
Created *time.Time `gorm:"column:created;type:timestamp"`
34+
ClusterID string `gorm:"column:clusterid;type:uuid;index:deployments_sac_filter,type:btree"`
35+
ClusterName string `gorm:"column:clustername;type:varchar"`
36+
Annotations map[string]string `gorm:"column:annotations;type:jsonb"`
37+
Priority int64 `gorm:"column:priority;type:bigint"`
38+
ImagePullSecrets *pq.StringArray `gorm:"column:imagepullsecrets;type:text[]"`
39+
ServiceAccount string `gorm:"column:serviceaccount;type:varchar"`
40+
ServiceAccountPermissionLevel storage.PermissionLevel `gorm:"column:serviceaccountpermissionlevel;type:integer"`
41+
RiskScore float32 `gorm:"column:riskscore;type:numeric;index:deployments_riskscore,type:btree"`
42+
PlatformComponent bool `gorm:"column:platformcomponent;type:bool"`
43+
Serialized []byte `gorm:"column:serialized;type:bytea"`
44+
}

‎migrator/runner/all.go‎

Lines changed: 1 addition & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎pkg/migrations/internal/seq_num.go‎

Lines changed: 1 addition & 1 deletion
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

0 commit comments

Comments
 (0)