Moti Asayag has uploaded a new change for review. Change subject: engine: Drop network_name and port_mirroring from vm_interface ......................................................................
engine: Drop network_name and port_mirroring from vm_interface The patch removes the columns network_name and port_mirroring from vm_interface since this information should be retrieved via the vnic profile. Change-Id: Ib03c3b3a923cc79f69509258cd45c7457df78c7e Signed-off-by: Moti Asayag <[email protected]> --- M backend/manager/modules/dal/src/test/resources/fixtures.xml M packaging/dbscripts/create_dwh_views.sql M packaging/dbscripts/create_views.sql M packaging/dbscripts/network_sp.sql A packaging/dbscripts/upgrade/03_03_0610_drop_columns_from_vm_interface.sql 5 files changed, 25 insertions(+), 50 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/30/17130/1 diff --git a/backend/manager/modules/dal/src/test/resources/fixtures.xml b/backend/manager/modules/dal/src/test/resources/fixtures.xml index 47f60c1..ea35a88 100644 --- a/backend/manager/modules/dal/src/test/resources/fixtures.xml +++ b/backend/manager/modules/dal/src/test/resources/fixtures.xml @@ -3447,7 +3447,6 @@ <table name="vm_interface"> <column>id</column> - <column>network_name</column> <column>vnic_profile_id</column> <column>vm_guid</column> <column>vmt_guid</column> @@ -3460,7 +3459,6 @@ <column>linked</column> <row> <value>e2817b12-f873-4046-b0da-0098293c14fd</value> - <value>engine</value> <value>fd81f1e1-785b-4579-ab75-1419ebb87052</value> <value>77296e00-0cad-4e5a-9299-008a7b6f4355</value> <null/> @@ -3474,7 +3472,6 @@ </row> <row> <value>e2817b12-f873-4046-b0da-0098293c0000</value> - <value>engine</value> <value>fd81f1e1-785b-4579-ab75-1419ebb87052</value> <null/> <value>1b85420c-b84c-4f29-997e-0eb674b40b79</value> @@ -3488,7 +3485,6 @@ </row> <row> <value>14550e82-1e1f-47b5-ae41-b009348dabfa</value> - <value>engine2</value> <value>d0f2ca62-f564-447c-aa55-ce2aa12ea798</value> <value>77296e00-0cad-4e5a-9299-008a7b6f4355</value> <value>1b85420c-b84c-4f29-997e-0eb674b40b79</value> @@ -3502,7 +3498,6 @@ </row> <row> <value>14550e82-1e1f-47b5-ae41-b009348dabfb</value> - <value>engine4</value> <null /> <value>77296e00-0cad-4e5a-9299-008a7b6f4359</value> <value>1b85420c-b84c-4f29-997e-0eb674b40b79</value> diff --git a/packaging/dbscripts/create_dwh_views.sql b/packaging/dbscripts/create_dwh_views.sql index 9fb2072..cae89b0 100644 --- a/packaging/dbscripts/create_dwh_views.sql +++ b/packaging/dbscripts/create_dwh_views.sql @@ -278,22 +278,24 @@ CREATE OR REPLACE VIEW dwh_vm_interface_configuration_history_view AS -SELECT id AS vm_interface_id, - name AS vm_interface_name, - vm_guid AS vm_id, - cast(type as smallint) as vm_interface_type, - speed as vm_interface_speed_bps, - mac_addr AS mac_address, - network_name, - _create_date AS create_date, - _update_date AS update_date -FROM vm_interface +SELECT vm_interface.id AS vm_interface_id, + vm_interface.name AS vm_interface_name, + vm_interface.vm_guid AS vm_id, + cast(vm_interface.type as smallint) as vm_interface_type, + vm_interface.speed as vm_interface_speed_bps, + vm_interface.mac_addr AS mac_address, + network.name, + vm_interface._create_date AS create_date, + vm_interface._update_date AS update_date +FROM vm_interface +LEFT OUTER JOIN vnic_profiles ON vnic_profiles.id = vm_interface.vnic_profile_id +JOIN network ON network.id = vnic_profiles.network_id WHERE vmt_guid IS NULL AND - ((_create_date > + ((vm_interface._create_date > (SELECT var_datetime FROM dwh_history_timekeeping WHERE (var_name = 'lastSync'))) OR - (_update_date > + (vm_interface._update_date > (SELECT var_datetime FROM dwh_history_timekeeping AS history_timekeeping_1 WHERE (var_name = 'lastSync')))); diff --git a/packaging/dbscripts/create_views.sql b/packaging/dbscripts/create_views.sql index c6aae45..d582449 100644 --- a/packaging/dbscripts/create_views.sql +++ b/packaging/dbscripts/create_views.sql @@ -837,25 +837,29 @@ CREATE OR REPLACE VIEW vm_interface_view AS SELECT vm_interface_statistics.rx_rate, vm_interface_statistics.tx_rate, vm_interface_statistics.rx_drop, vm_interface_statistics.tx_drop, vm_interface_statistics.iface_status, vm_interface.type, vm_interface.speed, - vm_interface.mac_addr, vm_interface.network_name, vm_interface.name, vm_interface.vnic_profile_id, vm_static.vm_guid, vm_interface.vmt_guid, + vm_interface.mac_addr, network.name AS network_name, vm_interface.name, vm_interface.vnic_profile_id, vm_static.vm_guid, vm_interface.vmt_guid, vm_static.vm_name, vm_interface.id, 0 AS boot_protocol, 0 AS is_vds, vm_device.is_plugged, - vm_device.custom_properties, vm_interface.port_mirroring, vm_interface.linked, + vm_device.custom_properties, vnic_profiles.port_mirroring AS port_mirroring, vm_interface.linked, vm_static.vds_group_id AS vds_group_id, vm_static.entity_type AS vm_entity_type FROM vm_interface_statistics JOIN vm_interface ON vm_interface_statistics.id = vm_interface.id JOIN vm_static ON vm_interface.vm_guid = vm_static.vm_guid JOIN vm_device ON vm_interface.vm_guid = vm_device.vm_id AND vm_interface.id = vm_device.device_id + LEFT JOIN vnic_profiles ON vnic_profiles.id = vm_interface.vnic_profile_id + JOIN network ON network.id = vnic_profiles.network_id UNION SELECT vm_interface_statistics.rx_rate, vm_interface_statistics.tx_rate, vm_interface_statistics.rx_drop, vm_interface_statistics.tx_drop, vm_interface_statistics.iface_status, vm_interface.type, vm_interface.speed, - vm_interface.mac_addr, vm_interface.network_name, vm_interface.name, vm_interface.vnic_profile_id, NULL::uuid as vm_guid, + vm_interface.mac_addr, network.name AS network_name, vm_interface.name, vm_interface.vnic_profile_id, NULL::uuid as vm_guid, vm_interface.vmt_guid, vm_templates.vm_name AS vm_name, vm_interface.id, 0 AS boot_protocol, 0 AS is_vds, - vm_device.is_plugged as is_plugged, vm_device.custom_properties as custom_properties, vm_interface.port_mirroring, + vm_device.is_plugged as is_plugged, vm_device.custom_properties as custom_properties, vnic_profiles.port_mirroring AS port_mirroring, vm_interface.linked, vm_templates.vds_group_id AS vds_group_id, vm_templates.entity_type AS vm_entity_type FROM vm_interface_statistics RIGHT JOIN vm_interface ON vm_interface_statistics.id = vm_interface.id JOIN vm_static AS vm_templates ON vm_interface.vmt_guid = vm_templates.vm_guid - JOIN vm_device ON vm_interface.vmt_guid = vm_device.vm_id AND vm_interface.id = vm_device.device_id; + JOIN vm_device ON vm_interface.vmt_guid = vm_device.vm_id AND vm_interface.id = vm_device.device_id + LEFT JOIN vnic_profiles ON vnic_profiles.id = vm_interface.vnic_profile_id + JOIN network ON network.id = vnic_profiles.network_id; ---------------------------------------------- -- Event Notification Views ---------------------------------------------- @@ -1474,19 +1478,6 @@ SELECT device_id, vm_id, type, device, address, boot_order, spec_params, is_managed, is_plugged, is_readonly, alias, custom_properties FROM vm_device; - -CREATE OR REPLACE VIEW vm_interface_ext_view -AS -SELECT vm_interface_view.*, - network.id as network_id, - network_cluster.cluster_id as cluster_id, - network.storage_pool_id as data_center_id -FROM vm_interface_view -INNER JOIN network_cluster -ON network_cluster.cluster_id = vm_interface_view.vds_group_id -INNER JOIN network -ON network.id = network_cluster.network_id -AND network.name = vm_interface_view.network_name; -- Permissions on VNIC Profiles -- The user has permissions on the Profile directly diff --git a/packaging/dbscripts/network_sp.sql b/packaging/dbscripts/network_sp.sql index f1c3b25..9370afb 100644 --- a/packaging/dbscripts/network_sp.sql +++ b/packaging/dbscripts/network_sp.sql @@ -546,21 +546,6 @@ - - -Create or replace FUNCTION Getvm_interfaceByvm_guidAndByvmt_guid(v_vm_guid UUID, - v_vmt_guid UUID) RETURNS SETOF vm_interface - AS $procedure$ -BEGIN - RETURN QUERY SELECT * - FROM vm_interface - WHERE VM_GUID = v_vm_guid AND VMT_GUID = v_vmt_guid; - -END; $procedure$ -LANGUAGE plpgsql; - - - Create or replace FUNCTION GetPluggedVmInterfacesByMac(v_mac_address VARCHAR(20)) RETURNS SETOF vm_interface_view AS $procedure$ diff --git a/packaging/dbscripts/upgrade/03_03_0610_drop_columns_from_vm_interface.sql b/packaging/dbscripts/upgrade/03_03_0610_drop_columns_from_vm_interface.sql new file mode 100644 index 0000000..b707f39 --- /dev/null +++ b/packaging/dbscripts/upgrade/03_03_0610_drop_columns_from_vm_interface.sql @@ -0,0 +1,2 @@ +SELECT fn_db_drop_column('vm_interface', 'network_name'); +SELECT fn_db_drop_column('vm_interface', 'port_mirroring'); -- To view, visit http://gerrit.ovirt.org/17130 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: Ib03c3b3a923cc79f69509258cd45c7457df78c7e Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Moti Asayag <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
