db/migrations/20160914165525_migrate_v2_app_data_to_v3.rb
Sequel.migration do
up do
collate_opts = {}
dbtype = if self.class.name.match?(/mysql/i)
collate_opts[:collate] = :utf8_bin
'mysql'
elsif self.class.name.match?(/postgres/i)
'postgres'
else
raise 'unknown database'
end
####
## App usage events - Insert STOP events for v3 created processes that will be removed
####
transaction do
generate_stop_events_query = <<-SQL.squish
INSERT INTO app_usage_events
(guid, created_at, instance_count, memory_in_mb_per_instance, state, app_guid, app_name, space_guid, space_name, org_guid, buildpack_guid, buildpack_name, package_state, parent_app_name, parent_app_guid, process_type, task_guid, task_name, package_guid, previous_state, previous_package_state, previous_memory_in_mb_per_instance, previous_instance_count)
SELECT %s, now(), p.instances, p.memory, 'STOPPED', p.guid, p.name, s.guid, s.name, o.guid, d.buildpack_receipt_buildpack_guid, d.buildpack_receipt_buildpack, p.package_state, a.name, a.guid, p.type, NULL, NULL, pkg.guid, 'STARTED', p.package_state, p.memory, p.instances
FROM apps as p
INNER JOIN apps_v3 as a ON (a.guid=p.app_guid)
INNER JOIN spaces as s ON (s.guid=a.space_guid)
INNER JOIN organizations as o ON (o.id=s.organization_id)
INNER JOIN packages as pkg ON (a.guid=pkg.app_guid)
INNER JOIN v3_droplets as d ON (a.guid=d.app_guid)
INNER JOIN buildpack_lifecycle_data as l ON (d.guid=l.droplet_guid)
WHERE p.state='STARTED'
SQL
if dbtype == 'mysql'
run generate_stop_events_query % 'UUID()'
elsif dbtype == 'postgres'
run generate_stop_events_query % 'get_uuid()'
end
end
###
## remove V3 data
###
alter_table(:packages) do
drop_foreign_key [:app_guid]
end
alter_table(:apps) do
drop_foreign_key [:app_guid]
drop_index :app_guid
drop_foreign_key [:space_id]
drop_foreign_key [:stack_id], name: :fk_apps_stack_id
drop_index %i[name space_id], name: :apps_space_id_name_nd_idx
end
drop_table(:v3_service_bindings)
drop_table(:tasks)
drop_table(:package_docker_data)
drop_table(:v3_droplets)
drop_table(:route_mappings)
transaction do
run 'DELETE FROM droplets WHERE app_id IN (SELECT id FROM apps WHERE app_guid IS NOT NULL);'
run 'DELETE FROM apps_routes WHERE app_id IN (SELECT id FROM apps WHERE app_guid IS NOT NULL);'
run 'DELETE FROM apps WHERE app_guid IS NOT NULL OR deleted_at IS NOT NULL;'
end
self[:packages].truncate
self[:buildpack_lifecycle_data].truncate
self[:apps_v3].truncate
####
## Alter tables to match v3 format
####
alter_table(:buildpack_lifecycle_data) do
add_column :encrypted_buildpack_url, String
add_column :encrypted_buildpack_url_salt, String
add_column :admin_buildpack_name, String
add_index :admin_buildpack_name, name: :buildpack_lifecycle_data_admin_buildpack_name_index
drop_column :encrypted_buildpack
drop_column :salt
drop_index(:guid)
set_column_allow_null(:guid)
end
rename_table :apps, :processes
rename_table :apps_v3, :apps
alter_table(:processes) do
add_index :app_guid, name: :processes_app_guid_index
add_foreign_key [:app_guid], :apps, key: :guid, name: :fk_processes_app_guid
end
alter_table(:packages) do
add_foreign_key [:app_guid], :apps, key: :guid, name: :fk_packages_app_guid
drop_column :url
add_column :docker_image, String, type: :text
end
alter_table :droplets do
add_column :state, String
add_index :state, name: :droplets_state_index
add_column :process_types, String, type: :text
add_column :error_id, String
add_column :error_description, String, type: :text
add_column :encrypted_environment_variables, String, text: true
add_column :salt, String
add_column :staging_memory_in_mb, Integer
add_column :staging_disk_in_mb, Integer
add_column :buildpack_receipt_stack_name, String
add_column :buildpack_receipt_buildpack, String
add_column :buildpack_receipt_buildpack_guid, String
add_column :buildpack_receipt_detect_output, String
add_column :docker_receipt_image, String
add_column :package_guid, String
add_index :package_guid, name: :package_guid_index
add_column :app_guid, String
add_foreign_key [:app_guid], :apps, key: :guid, name: :fk_droplets_app_guid
set_column_allow_null(:droplet_hash)
end
alter_table(:apps_routes) do
add_column :app_guid, String, collate_opts
add_column :route_guid, String, collate_opts
add_column :process_type, String
add_index :process_type, name: :route_mappings_process_type_index
drop_foreign_key [:route_id]
drop_foreign_key [:app_id]
drop_constraint :apps_routes_app_id_route_id_app_port_key, type: :unique
end
alter_table(:service_bindings) do
add_column :app_guid, String
add_column :service_instance_guid, String
add_column :type, String
drop_foreign_key [:service_instance_id]
drop_foreign_key [:app_id]
end
create_table :tasks do
VCAP::Migration.common(self)
String :name, case_insensitive: true, null: false
index :name, name: :tasks_name_index
String :command, null: false, text: true
String :state, null: false
index :state, name: :tasks_state_index
Integer :memory_in_mb, null: true
String :encrypted_environment_variables, text: true, null: true
String :salt, null: true
String :failure_reason, null: true, size: 4096
String :app_guid, null: false
foreign_key [:app_guid], :apps, key: :guid, name: :fk_tasks_app_guid
String :droplet_guid, null: false
foreign_key [:droplet_guid], :droplets, key: :guid, name: :fk_tasks_droplet_guid
if self.class.name.match?(/mysql/i)
table_name = tables.find { |t| t =~ /tasks/ }
run "ALTER TABLE `#{table_name}` CONVERT TO CHARACTER SET utf8;"
end
end
####
## Migrate data
####
transaction do
####
## Fill in v3 apps table data
###
run <<-SQL.squish
INSERT INTO apps (guid, name, salt, encrypted_environment_variables, created_at, updated_at, space_guid, desired_state)
SELECT p.guid, p.name, p.salt, p.encrypted_environment_json, p.created_at, p.updated_at, s.guid, p.state
FROM processes as p, spaces as s
WHERE p.space_id = s.id
ORDER BY p.id
SQL
run <<-SQL.squish
UPDATE processes SET app_guid=guid
SQL
#####
## Create lifecycle data for buildpack apps
####
run <<-SQL.squish
INSERT INTO buildpack_lifecycle_data (app_guid, stack)
SELECT processes.guid, stacks.name
FROM processes, stacks
WHERE docker_image is NULL AND stacks.id = processes.stack_id
SQL
run <<-SQL.squish
UPDATE buildpack_lifecycle_data
SET
admin_buildpack_name=(
SELECT buildpacks.name
FROM buildpacks
JOIN processes ON processes.admin_buildpack_id = buildpacks.id
WHERE processes.admin_buildpack_id IS NOT NULL AND processes.guid=buildpack_lifecycle_data.app_guid
),
encrypted_buildpack_url=(
SELECT processes.encrypted_buildpack
FROM processes
WHERE processes.admin_buildpack_id IS NULL AND processes.guid=buildpack_lifecycle_data.app_guid
),
encrypted_buildpack_url_salt=(
SELECT processes.buildpack_salt
FROM processes
WHERE processes.admin_buildpack_id IS NULL AND processes.guid=buildpack_lifecycle_data.app_guid
)
SQL
#####
## Fill in packages data
####
run <<-SQL.squish
INSERT INTO packages (guid, type, package_hash, state, error, app_guid)
SELECT guid, 'bits', package_hash, 'READY', NULL, guid
FROM processes
WHERE package_hash IS NOT NULL AND docker_image IS NULL
SQL
run <<-SQL.squish
INSERT INTO packages (guid, type, state, error, app_guid, docker_image)
SELECT guid, 'docker', 'READY', NULL, guid, docker_image
FROM processes
WHERE docker_image IS NOT NULL
SQL
####
## Fill in droplets data
####
# backfill any v2 droplets that do not exist due to lazy backfilling in v2 droplets. it is unlikely there are
# any of these, but possible in very old CF deployments
run <<-SQL.squish
INSERT INTO droplets (guid, app_id, droplet_hash, detected_start_command)
SELECT processes.guid, processes.id, processes.droplet_hash, '' AS detected_start_command
FROM processes
WHERE processes.droplet_hash IS NOT NULL
AND processes.id IN ( SELECT processes.id FROM processes LEFT JOIN droplets ON processes.id = droplets.app_id WHERE droplets.id IS NULL)
SQL
# pruning will not delete from the blobstore
# prune orphaned droplets
run <<-SQL.squish
DELETE FROM droplets WHERE NOT EXISTS (SELECT 1 FROM processes WHERE droplets.app_id = processes.id)
SQL
# prune additional droplets, each app will have only one droplet
postgres_prune_droplets_query = <<-SQL.squish
DELETE FROM droplets
USING droplets as d
JOIN processes ON processes.id = d.app_id
WHERE droplets.id = d.id AND (processes.droplet_hash <> d.droplet_hash OR processes.droplet_hash IS NULL)
SQL
mysql_prune_droplets_query = <<-SQL.squish
DELETE droplets FROM droplets
JOIN processes ON processes.id = droplets.app_id
WHERE processes.droplet_hash <> droplets.droplet_hash OR processes.droplet_hash IS NULL
SQL
if dbtype == 'mysql'
run mysql_prune_droplets_query
run <<-SQL.squish
DELETE a FROM droplets a, droplets b
WHERE a.app_id=b.app_id AND a.id < b.id
SQL
elsif dbtype == 'postgres'
run postgres_prune_droplets_query
run <<-SQL.squish
DELETE FROM droplets a USING droplets b
WHERE a.app_id = b.app_id AND a.id < b.id
SQL
end
# convert to v3 droplets
postgres_convert_to_v3_droplets_query = <<-SQL.squish
UPDATE droplets
SET
guid = v2_app.guid,
state = 'STAGED',
app_guid = v2_app.guid,
package_guid = v2_app.guid,
docker_receipt_image = droplets.cached_docker_image,
process_types = '{"web":"' || droplets.detected_start_command || '"}',
buildpack_receipt_buildpack = v2_app.detected_buildpack_name,
buildpack_receipt_buildpack_guid = v2_app.detected_buildpack_guid,
buildpack_receipt_detect_output = v2_app.detected_buildpack
FROM processes AS v2_app
WHERE v2_app.id = droplets.app_id
SQL
mysql_convert_to_v3_droplets_query = <<-SQL.squish
UPDATE droplets
JOIN processes as v2_app
ON v2_app.id = droplets.app_id
SET
droplets.guid = v2_app.guid,
droplets.state = 'STAGED',
droplets.app_guid = v2_app.guid,
droplets.package_guid = v2_app.guid,
droplets.docker_receipt_image = droplets.cached_docker_image,
droplets.process_types = CONCAT('{"web":"', droplets.detected_start_command, '"}'),
droplets.buildpack_receipt_buildpack = v2_app.detected_buildpack_name,
droplets.buildpack_receipt_buildpack_guid = v2_app.detected_buildpack_guid,
droplets.buildpack_receipt_detect_output = v2_app.detected_buildpack
SQL
if dbtype == 'mysql'
run mysql_convert_to_v3_droplets_query
elsif dbtype == 'postgres'
run postgres_convert_to_v3_droplets_query
end
# add lifecycle data to buildpack droplets
run <<-SQL.squish
INSERT INTO buildpack_lifecycle_data (droplet_guid)
SELECT droplets.guid
FROM processes, droplets
WHERE processes.docker_image is NULL AND droplets.app_guid = processes.guid
SQL
# set current droplet on v3 app
postgres_set_current_droplet_query = <<-SQL.squish
UPDATE apps
SET droplet_guid = droplets.guid
FROM droplets
WHERE droplets.app_guid = apps.guid
SQL
mysql_set_current_droplet_query = <<-SQL.squish
UPDATE apps
JOIN droplets as current_droplet
ON apps.guid = current_droplet.app_guid
JOIN processes as web_process
ON web_process.app_guid = apps.guid AND web_process.type = 'web'
SET apps.droplet_guid = current_droplet.guid
WHERE web_process.droplet_hash IS NOT NULL AND current_droplet.droplet_hash = web_process.droplet_hash
SQL
if dbtype == 'mysql'
run mysql_set_current_droplet_query
elsif dbtype == 'postgres'
run postgres_set_current_droplet_query
end
####
## Fill in guids for buildpack_lifecycle_data inserts done for apps and droplets
####
if self.class.name.match?(/mysql/i)
run 'update buildpack_lifecycle_data set guid=UUID();'
elsif self.class.name.match?(/postgres/i)
run 'update buildpack_lifecycle_data set guid=get_uuid();'
end
####
## Migrate route mappings
####
run <<-SQL.squish
UPDATE apps_routes SET
app_guid = (SELECT processes.guid FROM processes WHERE processes.id=apps_routes.app_id),
route_guid = (SELECT routes.guid FROM routes WHERE routes.id=apps_routes.route_id),
process_type = 'web'
SQL
run <<-SQL.squish
UPDATE apps_routes SET app_port=8080 WHERE app_port IS NULL AND EXISTS (SELECT 1 FROM processes WHERE processes.docker_image IS NULL AND processes.id = apps_routes.app_id)
SQL
if self.class.name.match?(/mysql/i)
run 'update apps_routes set guid=UUID() where guid is NULL;'
elsif self.class.name.match?(/postgres/i)
run 'update apps_routes set guid=get_uuid() where guid is NULL;'
end
####
## Migrate service bindings
####
# Remove duplicate apps_routes to prepare for adding a uniqueness constraint
dup_groups = self[:apps_routes].
select(:app_guid, :route_guid, :app_port, :process_type).
group_by(:app_guid, :route_guid, :app_port, :process_type).
having { count.function.* > 1 }
dup_groups.each do |group|
sorted_ids = self[:apps_routes].
select(:id).
where(app_guid: group[:app_guid], route_guid: group[:route_guid], app_port: group[:app_port], process_type: group[:process_type]).
map(&:values).
flatten.
sort
sorted_ids.shift
ids_to_remove = sorted_ids
self[:apps_routes].where(id: ids_to_remove).delete
end
run <<-SQL.squish
UPDATE service_bindings SET
app_guid = (SELECT processes.guid FROM processes WHERE processes.id=service_bindings.app_id),
service_instance_guid = (SELECT service_instances.guid FROM service_instances WHERE service_instances.id=service_bindings.service_instance_id),
type = 'app'
SQL
end
####
## Remove columns that have moved to other tables
##
## Re-establish foreign key and null constraints
####
alter_table(:buildpack_lifecycle_data) do
set_column_not_null :guid
add_index :guid, unique: true, name: :buildpack_lifecycle_data_guid_index
end
alter_table :droplets do
set_column_not_null(:state)
drop_column :app_id
drop_column :cached_docker_image
drop_column :detected_start_command
end
alter_table(:apps_routes) do
drop_column :route_id
drop_column :app_id
set_column_not_null(:app_guid)
set_column_not_null(:route_guid)
set_column_not_null(:guid)
# for mysql, which loses collation settings when setting not null constraint
set_column_type :app_guid, String, collate_opts
set_column_type :route_guid, String, collate_opts
add_foreign_key [:app_guid], :apps, key: :guid, name: :fk_route_mappings_app_guid
add_foreign_key [:route_guid], :routes, key: :guid, name: :fk_route_mappings_route_guid
add_unique_constraint %i[app_guid route_guid process_type app_port], name: :route_mappings_app_guid_route_guid_process_type_app_port_key
end
rename_table :apps_routes, :route_mappings
alter_table(:service_bindings) do
drop_column :service_instance_id
drop_column :app_id
drop_column :gateway_name
drop_column :gateway_data
drop_column :configuration
drop_column :binding_options
set_column_not_null(:app_guid)
set_column_not_null(:service_instance_guid)
# for mysql, which loses collation settings when setting not null constraint
set_column_type :app_guid, String, collate_opts
set_column_type :service_instance_guid, String, collate_opts
add_foreign_key [:app_guid], :apps, key: :guid, name: :fk_service_bindings_app_guid
add_foreign_key [:service_instance_guid], :service_instances, key: :guid, name: :fk_service_bindings_service_instance_guid
end
alter_table(:processes) do
drop_column :name
drop_column :encrypted_environment_json
drop_column :salt
drop_column :encrypted_buildpack
drop_column :buildpack_salt
drop_column :space_id
drop_column :stack_id
drop_column :admin_buildpack_id
drop_column :docker_image
drop_column :package_hash
drop_column :package_state
drop_column :droplet_hash
drop_column :package_pending_since
drop_column :deleted_at
drop_column :staging_task_id
drop_column :detected_buildpack_guid
drop_column :detected_buildpack_name
drop_column :staging_failed_reason
drop_column :staging_failed_description
end
end
end