func ReplaceBuildEventsIDWithEventID(tx migration.LimitedTx) error {
_, err := tx.Exec(`ALTER TABLE build_events ADD COLUMN event_id integer`)
if err != nil {
return err
}
startIDs := map[int]int{}
rows, err := tx.Query(`
SELECT build_id, min(id)
FROM build_events
GROUP BY build_id
`)
if err != nil {
return err
}
for rows.Next() {
var buildID, id int
err := rows.Scan(&buildID, &id)
if err != nil {
return err
}
startIDs[buildID] = id
}
err = rows.Close()
if err != nil {
return err
}
for buildID, id := range startIDs {
_, err := tx.Exec(`
UPDATE build_events
SET event_id = id - $2
WHERE build_id = $1
`, buildID, id)
if err != nil {
return err
}
}
_, err = tx.Exec(`ALTER TABLE build_events DROP COLUMN id`)
if err != nil {
return err
}
_, err = tx.Exec(`ALTER TABLE build_events ALTER COLUMN event_id SET NOT NULL`)
if err != nil {
return err
}
_, err = tx.Exec(`CREATE UNIQUE INDEX build_events_build_id_event_id ON build_events (build_id, event_id)`)
if err != nil {
return err
}
return nil
}
func AddNameToBuildInputs(tx migration.LimitedTx) error {
_, err := tx.Exec(`ALTER TABLE build_inputs ADD COLUMN name text`)
if err != nil {
return err
}
names := map[int]string{}
rows, err := tx.Query(`
SELECT i.versioned_resource_id, v.resource_name
FROM build_inputs i, versioned_resources v
WHERE v.id = i.versioned_resource_id
`)
if err != nil {
return err
}
defer rows.Close()
for rows.Next() {
var vrID int
var name string
err := rows.Scan(&vrID, &name)
if err != nil {
return err
}
names[vrID] = name
}
for vrID, name := range names {
_, err := tx.Exec(`
UPDATE build_inputs
SET name = $2
WHERE versioned_resource_id = $1
`, vrID, name)
if err != nil {
return err
}
}
_, err = tx.Exec(`ALTER TABLE build_inputs ALTER COLUMN name SET NOT NULL`)
if err != nil {
return err
}
return nil
}
func AddPipelineBuildEventsTables(tx migration.LimitedTx) error {
_, err := tx.Exec(`
ALTER TABLE build_events
DROP CONSTRAINT build_events_build_id_fkey
`)
if err != nil {
return fmt.Errorf("failed to update build events foreign key: %s", err)
}
rows, err := tx.Query(`SELECT id FROM pipelines`)
if err != nil {
return err
}
defer rows.Close()
var pipelineIDs []int
for rows.Next() {
var pipelineID int
err = rows.Scan(&pipelineID)
if err != nil {
return fmt.Errorf("failed to scan pipeline ID: %s", err)
}
pipelineIDs = append(pipelineIDs, pipelineID)
}
for _, pipelineID := range pipelineIDs {
err = createBuildEventsTable(tx, pipelineID)
if err != nil {
return fmt.Errorf("failed to create build events table: %s", err)
}
err = populateBuildEventsTable(tx, pipelineID)
if err != nil {
return fmt.Errorf("failed to populate build events: %s", err)
}
}
_, err = tx.Exec(`
CREATE INDEX build_events_build_id_idx ON build_events (build_id);
CREATE INDEX build_outputs_build_id_idx ON build_outputs (build_id);
CREATE INDEX build_inputs_build_id_idx ON build_inputs (build_id);
CREATE INDEX build_outputs_versioned_resource_id_idx ON build_outputs (versioned_resource_id);
CREATE INDEX build_inputs_versioned_resource_id_idx ON build_inputs (versioned_resource_id);
CREATE INDEX image_resource_versions_build_id_idx ON image_resource_versions (build_id);
CREATE INDEX pipelines_team_id_idx ON pipelines (team_id);
CREATE INDEX resources_pipeline_id_idx ON resources (pipeline_id);
CREATE INDEX jobs_pipeline_id_idx ON jobs (pipeline_id);
CREATE INDEX jobs_serial_groups_job_id_idx ON jobs_serial_groups (job_id);
CREATE INDEX builds_job_id_idx ON builds (job_id);
CREATE INDEX versioned_resources_resource_id_idx ON versioned_resources (resource_id);
`)
if err != nil {
return fmt.Errorf("failed to create indexes: %s", err)
}
_, err = tx.Exec(`
DELETE FROM ONLY build_events
WHERE build_id IN (SELECT id FROM builds WHERE job_id IS NOT NULL)
`)
if err != nil {
return fmt.Errorf("failed to clean up build events: %s", err)
}
return nil
}
