Eli Mesika has uploaded a new change for review. Change subject: core: DB 3.0 to 3.1 upgrade ......................................................................
core: DB 3.0 to 3.1 upgrade This patch fixes the following errors 1) psql:upgrade/03_01_0410_remove_image_templates_table.sql:2: ERROR: constraint "image_templates_images" of relation "images" does not exist 2)psql:upgrade/03_01_0950_add_permissions_on_storage_to_users.sql:122: ERROR: duplicate key value violates unique constraint "idx_combined_ad_role_object" 1) was solved by adding two functions fn_db_add/drop_constraint that checks if constraint not exists/exists before attempting to add/drop it Then all upgrade scripts adding/dropping table constraints were modified to use those safe functions, this will ensure that in the next round we will not got this type of errors anymore. 2) was solved by adding a missing check for duplicate key. All changes are safe and not change functionality. Change-Id: I7d0a37abb58f080338b3161d0694c8ba30217d99 Signed-off-by: Eli Mesika <emes...@redhat.com> Bug-Url: https://bugzilla.redhat.com/show_bug.cgi?id=892532 --- M backend/manager/dbscripts/common_sp.sql M backend/manager/dbscripts/upgrade/03_01_0100_migrate_templates_to_vm_static.sql M backend/manager/dbscripts/upgrade/03_01_0300_vds_static_unique_fields.sql M backend/manager/dbscripts/upgrade/03_01_0410_remove_image_templates_table.sql M backend/manager/dbscripts/upgrade/03_01_0430_add_image_domain_map_table.sql M backend/manager/dbscripts/upgrade/03_01_0500_add_unique_name_constraint.sql M backend/manager/dbscripts/upgrade/03_01_0720_remove_unused_images_columns.sql M backend/manager/dbscripts/upgrade/03_01_0950_add_permissions_on_storage_to_users.sql M backend/manager/dbscripts/upgrade/03_01_1090_gluster_brick_option_id.sql M backend/manager/dbscripts/upgrade/03_01_1260_remove_time_lease_vm_pool_map_table.sql M backend/manager/dbscripts/upgrade/03_01_1520_add_fk_vm_interface_vm_static_template.sql M backend/manager/dbscripts/upgrade/03_02_0270_gluster_volume_name_unique_constraint.sql 12 files changed, 52 insertions(+), 32 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/35/10735/1 diff --git a/backend/manager/dbscripts/common_sp.sql b/backend/manager/dbscripts/common_sp.sql index e7f5f92..02f6085 100644 --- a/backend/manager/dbscripts/common_sp.sql +++ b/backend/manager/dbscripts/common_sp.sql @@ -143,6 +143,28 @@ END; $procedure$ LANGUAGE plpgsql; +create or replace function fn_db_create_constraint ( + v_table varchar(128), v_constraint varchar(128), v_constraint_sql text) +returns void +AS $procedure$ +begin + if NOT EXISTS (SELECT 1 from pg_constraint where conname = v_constraint) then + execute 'ALTER TABLE ' || v_table || ' ADD CONSTRAINT ' || v_constraint || ' ' || v_constraint_sql; + end if; +END; $procedure$ +LANGUAGE plpgsql; + +create or replace function fn_db_drop_constraint ( + v_table varchar(128), v_constraint varchar(128)) +returns void +AS $procedure$ +begin + if EXISTS (SELECT 1 from pg_constraint where conname = v_constraint) then + execute 'ALTER TABLE ' || v_table || ' DROP CONSTRAINT ' || v_constraint || ' CASCADE'; + end if; +END; $procedure$ +LANGUAGE plpgsql; + -------------------------------------------------- -- End of DB helper functions -------------------------------------------------- diff --git a/backend/manager/dbscripts/upgrade/03_01_0100_migrate_templates_to_vm_static.sql b/backend/manager/dbscripts/upgrade/03_01_0100_migrate_templates_to_vm_static.sql index 8631c9f..a644017 100644 --- a/backend/manager/dbscripts/upgrade/03_01_0100_migrate_templates_to_vm_static.sql +++ b/backend/manager/dbscripts/upgrade/03_01_0100_migrate_templates_to_vm_static.sql @@ -19,9 +19,7 @@ WHERE entity_type IS NULL; ALTER TABLE vm_static ALTER COLUMN entity_type SET NOT NULL; -IF EXISTS (SELECT 1 from pg_constraint where conname = 'vm_templates_vm_static') THEN - ALTER TABLE vm_static DROP CONSTRAINT vm_templates_vm_static; -END IF; +perform fn_db_drop_constraint('vm_static','vm_templates_vm_static'); INSERT INTO vm_static ( vm_guid, @@ -98,9 +96,7 @@ FROM vm_static WHERE entity_type = 'TEMPLATE'); -IF NOT EXISTS (SELECT 1 from pg_constraint where conname = 'vm_templates_vm_static') THEN - ALTER TABLE vm_static ADD CONSTRAINT vm_templates_vm_static FOREIGN KEY (vmt_guid) REFERENCES vm_static (vm_guid); -END IF; +perform fn_db_create_constraint('vm_static', 'vm_templates_vm_static', 'FOREIGN KEY (vmt_guid) REFERENCES vm_static (vm_guid)'); INSERT INTO image_vm_map( diff --git a/backend/manager/dbscripts/upgrade/03_01_0300_vds_static_unique_fields.sql b/backend/manager/dbscripts/upgrade/03_01_0300_vds_static_unique_fields.sql index 954b68a..12842cf 100644 --- a/backend/manager/dbscripts/upgrade/03_01_0300_vds_static_unique_fields.sql +++ b/backend/manager/dbscripts/upgrade/03_01_0300_vds_static_unique_fields.sql @@ -1,4 +1,3 @@ - -ALTER TABLE vds_static ADD CONSTRAINT vds_static_vds_name_unique UNIQUE(vds_name); -ALTER TABLE vds_static ADD CONSTRAINT vds_static_host_name_unique UNIQUE(host_name); +select fn_db_create_constraint('vds_static', 'vds_static_vds_name_unique', 'UNIQUE(vds_name)'); +select fn_db_create_constraint('vds_static', 'vds_static_host_name_unique', 'UNIQUE(host_name)'); diff --git a/backend/manager/dbscripts/upgrade/03_01_0410_remove_image_templates_table.sql b/backend/manager/dbscripts/upgrade/03_01_0410_remove_image_templates_table.sql index 8d28fe5..bf5dec1 100644 --- a/backend/manager/dbscripts/upgrade/03_01_0410_remove_image_templates_table.sql +++ b/backend/manager/dbscripts/upgrade/03_01_0410_remove_image_templates_table.sql @@ -1,4 +1,4 @@ insert into images (image_guid,creation_date,size,description,boot,it_guid,internal_drive_mapping) select '00000000-0000-0000-0000-000000000000','2008/04/01 00:00:00',85899345920,'Blank Image Template',false,'00000000-0000-0000-0000-000000000000','1'; -ALTER TABLE images drop CONSTRAINT image_templates_images; -ALTER TABLE images add CONSTRAINT image_templates_images FOREIGN KEY(it_guid) REFERENCES images(image_guid); +select fn_db_drop_constraint('images','image_templates_images'); +select fn_db_create_constraint('images', 'image_templates_images', 'FOREIGN KEY(it_guid) REFERENCES images(image_guid)'); drop table image_templates cascade; diff --git a/backend/manager/dbscripts/upgrade/03_01_0430_add_image_domain_map_table.sql b/backend/manager/dbscripts/upgrade/03_01_0430_add_image_domain_map_table.sql index dedd5ef..f56f711 100644 --- a/backend/manager/dbscripts/upgrade/03_01_0430_add_image_domain_map_table.sql +++ b/backend/manager/dbscripts/upgrade/03_01_0430_add_image_domain_map_table.sql @@ -15,22 +15,22 @@ ) WITH OIDS; INSERT INTO image_storage_domain_map (image_id,storage_domain_id) - SELECT images.image_guid AS image_id, images.storage_id AS storage_domain_id + SELECT images.image_guid AS image_id, images.storage_id AS storage_domain_id FROM images WHERE images.storage_id IS NOT NULL - UNION + UNION SELECT images.image_guid AS image_id, image_group_storage_domain_map.storage_domain_id AS storage_domain_id - FROM images INNER JOIN image_group_storage_domain_map ON images.image_group_id = image_group_storage_domain_map.image_group_id; + FROM images INNER JOIN image_group_storage_domain_map ON images.image_group_id = image_group_storage_domain_map.image_group_id; -- The following line removes grabage copied from dropped tables delete from image_storage_domain_map where image_id not in (select image_guid from images) or storage_domain_id not in (select id from storage_domain_static); -ALTER TABLE image_storage_domain_map ADD CONSTRAINT fk_image_storage_domain_map_images FOREIGN KEY (image_id) REFERENCES images(image_guid) ON DELETE CASCADE; -ALTER TABLE image_storage_domain_map ADD CONSTRAINT fk_image_storage_domain_map_storage_domain_static FOREIGN KEY (storage_domain_id) REFERENCES storage_domain_static(id) ON DELETE CASCADE; +perform fn_db_create_constraint('image_storage_domain_map', 'fk_image_storage_domain_map_images', 'FOREIGN KEY (image_id) REFERENCES images(image_guid) ON DELETE CASCADE'); +perform fn_db_create_constraint('image_storage_domain_map', 'fk_image_storage_domain_map_storage_domain_static', 'FOREIGN KEY (storage_domain_id) REFERENCES storage_domain_static(id) ON DELETE CASCADE'); DROP TABLE image_group_storage_domain_map; - - ALTER TABLE images DROP CONSTRAINT Fk_images_storage_id; + + perform fn_db_drop_constraint('images','Fk_images_storage_id'); ALTER TABLE images DROP COLUMN storage_id; END; $function$ diff --git a/backend/manager/dbscripts/upgrade/03_01_0500_add_unique_name_constraint.sql b/backend/manager/dbscripts/upgrade/03_01_0500_add_unique_name_constraint.sql index 9755d25..c590836 100644 --- a/backend/manager/dbscripts/upgrade/03_01_0500_add_unique_name_constraint.sql +++ b/backend/manager/dbscripts/upgrade/03_01_0500_add_unique_name_constraint.sql @@ -1 +1 @@ -ALTER TABLE quota ADD CONSTRAINT quota_quota_name_unique UNIQUE(quota_name); \ No newline at end of file +select fn_db_create_constraint('quota', 'quota_quota_name_unique', 'UNIQUE(quota_name)'); diff --git a/backend/manager/dbscripts/upgrade/03_01_0720_remove_unused_images_columns.sql b/backend/manager/dbscripts/upgrade/03_01_0720_remove_unused_images_columns.sql index 5efb6cc..e6b8c64 100644 --- a/backend/manager/dbscripts/upgrade/03_01_0720_remove_unused_images_columns.sql +++ b/backend/manager/dbscripts/upgrade/03_01_0720_remove_unused_images_columns.sql @@ -1,5 +1,4 @@ - -ALTER TABLE disks DROP CONSTRAINT fk_disk_active_image; +SELECT fn_db_drop_constraint('disks','fk_disk_active_image'); SELECT fn_db_drop_column('disks', 'active_image_id'); SELECT fn_db_drop_column('images', 'internal_drive_mapping'); diff --git a/backend/manager/dbscripts/upgrade/03_01_0950_add_permissions_on_storage_to_users.sql b/backend/manager/dbscripts/upgrade/03_01_0950_add_permissions_on_storage_to_users.sql index 15f802e..2ed48bc 100644 --- a/backend/manager/dbscripts/upgrade/03_01_0950_add_permissions_on_storage_to_users.sql +++ b/backend/manager/dbscripts/upgrade/03_01_0950_add_permissions_on_storage_to_users.sql @@ -72,7 +72,11 @@ v_STORAGE_OBJECT_TYPE FROM vds_groups INNER JOIN storage_pool_iso_map ON vds_groups.storage_pool_id = storage_pool_iso_map.storage_pool_id - WHERE vds_groups.vds_group_id = v_permissions.object_id); + WHERE vds_groups.vds_group_id = v_permissions.object_id and + cast(v_DISK_CREATOR_ROLE_ID as VARCHAR) || cast(v_permissions.ad_element_id as VARCHAR) || + cast(storage_pool_iso_map.storage_id as VARCHAR) not in + ( select cast(role_id as VARCHAR) || cast(ad_element_id as VARCHAR) || + cast(object_id as VARCHAR) from permissions)); -- CREATE_VM on Data Center will allow creating Disks on the Storage Domains of the Data Center. ELSIF (v_permissions.object_type_id = v_DATA_CENTER_OBJECT_TYPE) THEN diff --git a/backend/manager/dbscripts/upgrade/03_01_1090_gluster_brick_option_id.sql b/backend/manager/dbscripts/upgrade/03_01_1090_gluster_brick_option_id.sql index 5fdd7e0..44676af 100644 --- a/backend/manager/dbscripts/upgrade/03_01_1090_gluster_brick_option_id.sql +++ b/backend/manager/dbscripts/upgrade/03_01_1090_gluster_brick_option_id.sql @@ -1,14 +1,14 @@ -- Add the "id" column and make it the primary key SELECT fn_db_add_column('gluster_volume_bricks', 'id', 'UUID'); UPDATE gluster_volume_bricks set id = uuid_generate_v1(); -ALTER TABLE gluster_volume_bricks DROP CONSTRAINT pk_gluster_volume_bricks; -ALTER TABLE gluster_volume_bricks ADD CONSTRAINT pk_gluster_volume_bricks PRIMARY KEY(id); -ALTER TABLE gluster_volume_bricks ADD CONSTRAINT IDX_gluster_volume_bricks_volume_server_brickdir UNIQUE(volume_id, server_id, brick_dir); +SELECT fn_db_drop_constraint('gluster_volume_bricks','pk_gluster_volume_bricks'); +SELECT fn_db_create_constraint('gluster_volume_bricks', 'pk_gluster_volume_bricks', 'PRIMARY KEY(id)'); +SELECT fn_db_create_constraint('gluster_volume_bricks', 'IDX_gluster_volume_bricks_volume_server_brickdir', 'UNIQUE(volume_id, server_id, brick_dir)'); -- Add the "id" column and make it the primary key SELECT fn_db_add_column('gluster_volume_options', 'id', 'UUID'); UPDATE gluster_volume_options set id = uuid_generate_v1(); -ALTER TABLE gluster_volume_options DROP CONSTRAINT pk_gluster_volume_options; -ALTER TABLE gluster_volume_options ADD CONSTRAINT pk_gluster_volume_options PRIMARY KEY(id); -ALTER TABLE gluster_volume_options ADD CONSTRAINT IDX_gluster_volume_options_volume_id_option_key UNIQUE(volume_id, option_key); +SELECT fn_db_drop_constraint('gluster_volume_options','pk_gluster_volume_options'); +SELECT fn_db_create_constraint('gluster_volume_options', 'pk_gluster_volume_options', 'PRIMARY KEY(id)'); +SELECT fn_db_create_constraint('gluster_volume_options', 'IDX_gluster_volume_options_volume_id_option_key', 'UNIQUE(volume_id, option_key)'); diff --git a/backend/manager/dbscripts/upgrade/03_01_1260_remove_time_lease_vm_pool_map_table.sql b/backend/manager/dbscripts/upgrade/03_01_1260_remove_time_lease_vm_pool_map_table.sql index 7d594f0..a169abb 100644 --- a/backend/manager/dbscripts/upgrade/03_01_1260_remove_time_lease_vm_pool_map_table.sql +++ b/backend/manager/dbscripts/upgrade/03_01_1260_remove_time_lease_vm_pool_map_table.sql @@ -1,2 +1,2 @@ -ALTER TABLE time_lease_vm_pool_map drop CONSTRAINT pk_time_user_vm_pool_map; +select fn_db_drop_constraint('time_lease_vm_pool_map','pk_time_user_vm_pool_map'); drop table time_lease_vm_pool_map cascade; diff --git a/backend/manager/dbscripts/upgrade/03_01_1520_add_fk_vm_interface_vm_static_template.sql b/backend/manager/dbscripts/upgrade/03_01_1520_add_fk_vm_interface_vm_static_template.sql index f52828d..a6c278e 100644 --- a/backend/manager/dbscripts/upgrade/03_01_1520_add_fk_vm_interface_vm_static_template.sql +++ b/backend/manager/dbscripts/upgrade/03_01_1520_add_fk_vm_interface_vm_static_template.sql @@ -1,2 +1,2 @@ -ALTER TABLE vm_interface ADD CONSTRAINT FK_vm_interface_vm_static_template FOREIGN KEY(vmt_guid) -REFERENCES vm_static(vm_guid) ON DELETE CASCADE; +select fn_db_create_constraint('vm_interface', 'FK_vm_interface_vm_static_template', 'FOREIGN KEY(vmt_guid) +REFERENCES vm_static(vm_guid) ON DELETE CASCADE'); diff --git a/backend/manager/dbscripts/upgrade/03_02_0270_gluster_volume_name_unique_constraint.sql b/backend/manager/dbscripts/upgrade/03_02_0270_gluster_volume_name_unique_constraint.sql index eadc64a..0df623e 100644 --- a/backend/manager/dbscripts/upgrade/03_02_0270_gluster_volume_name_unique_constraint.sql +++ b/backend/manager/dbscripts/upgrade/03_02_0270_gluster_volume_name_unique_constraint.sql @@ -2,4 +2,4 @@ DROP INDEX IDX_gluster_volumes_name_unique; -- Create a unique constraint -ALTER TABLE gluster_volumes ADD CONSTRAINT gluster_volumes_name_unique UNIQUE(cluster_id, vol_name); +select fn_db_create_constraint('gluster_volumes', 'gluster_volumes_name_unique', 'UNIQUE(cluster_id, vol_name)'); -- To view, visit http://gerrit.ovirt.org/10735 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I7d0a37abb58f080338b3161d0694c8ba30217d99 Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Eli Mesika <emes...@redhat.com> _______________________________________________ Engine-patches mailing list Engine-patches@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-patches
