--  Licensed under the Apache License, Version 2.0 (the "License");
--  you may not use this file except in compliance with the License.
--  You may obtain a copy of the License at
--
--      http://www.apache.org/licenses/LICENSE-2.0
--
--  Unless required by applicable law or agreed to in writing, software
--  distributed under the License is distributed on an "AS IS" BASIS,
--  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--  See the License for the specific language governing permissions and
--  limitations under the License.
--
COPY (
    SELECT
        vm_static.vm_name AS "Virtual Machine",
        vds.vds_name AS "Hypervisor"
    FROM
        vm_static
    INNER JOIN vds ON vm_static.dedicated_vm_for_vds::uuid=vds.vds_id
) TO STDOUT WITH CSV DELIMITER E'\|' HEADER;

--  Licensed under the Apache License, Version 2.0 (the "License");
--  you may not use this file except in compliance with the License.
--  You may obtain a copy of the License at
--
--      http://www.apache.org/licenses/LICENSE-2.0
--
--  Unless required by applicable law or agreed to in writing, software
--  distributed under the License is distributed on an "AS IS" BASIS,
--  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--  See the License for the specific language governing permissions and
--  limitations under the License.
--
--  Return the virtual machines pinned to run in a specific host.
--
--  The column dedicated_vm_for_vd from vm_static can contain a single
--  hypervisor entry or multiple hypervisors (using delimiter comma).
--  Based on that, we are extacting the hypervisors in the WITH statement
--  and using it in the INNER JOIN.
--
COPY (
    WITH vms_pinned AS (
        SELECT
            vm_name,
            unnest(string_to_array(dedicated_vm_for_vds,',')) AS dedicated_vds
        FROM
            vm_static
        WHERE
            vm_static.dedicated_vm_for_vds IS NOT NULL
        AND vm_static.entity_type='VM'
    )
    SELECT
        vms_pinned.vm_name AS "Virtual Machine",
        vds.vds_name AS "Hypervisor"
    FROM
        vds
    INNER JOIN vms_pinned ON vds.vds_id::text=vms_pinned.dedicated_vds ORDER BY vms_pinned.vm_name
) TO STDOUT WITH CSV DELIMITER E'\|' HEADER;

Improve SQL query for vms pinned
inventory: Improve SQL query for vms pinned

The column dedicated_vm_for_vd from vm_static can contain a single
hypervisor entry or multiple hypervisors (using delimiter comma).
Based on that, we are extacting the hypervisors in the WITH statement
and using it in the INNER JOIN.

Change-Id: Ic6c00c794962687e886f0ab3aef11557965da21b
Signed-off-by: Douglas Schilling Landgraf <cb5f9630225b7afa1e13760d30868c8ce876fc60@redhat.com>
