-- =========================================================================
-- Copyright 2017-2021 VMware, Inc.   All rights reserved. -- VMware Confidential
-- =========================================================================

-- DO NOT EDIT THIS FILE UNLESS REQUIRED BY YOUR SOFTWARE VENDOR

-- query:kPopulateStatsCounters
select DEVICE_ID, ENTITY_ID, STAT_ID, COUNTER_ID
FROM VPX_STAT_COUNTER

-- query:kPopulateDevices
SELECT DEVICE_ID, DEVICE_NAME
FROM VPX_DEVICE

-- query:kDeleteVflashConfiguration
delete
from vpx_host_vflash
where host_id = ?

-- query:kFetchTimeSamplesByPartition
SELECT SAMPLE_TIME
FROM VPX_SAMPLE_TIME1 ST
WHERE PARTITION_INDEX=?
ORDER BY 1 DESC

-- query:kDeleteTimeSamplesByPartition
DELETE
FROM VPX_SAMPLE_TIME1
WHERE PARTITION_INDEX=?

-- query:kVcResourceInsertStatsDirect
insert into VPX_USAGE_STAT (COUNTER_ID, TIME_ID, STAT_VAL)
values(?,?,?)

-- query:kFetchStatsTimeID
SELECT TIME_ID
FROM VPX_SAMPLE_TIME1
WHERE SAMPLE_TIME = ?

-- query:kInsertStatsTimeID
INSERT INTO VPX_SAMPLE_TIME1 (TIME_ID, SAMPLE_TIME, SAMPLE_INTERVAL, PARTITION_INDEX)
VALUES(?,?,?,?)

-- query:kFetchUsageTimeID
SELECT TIME_ID
FROM VPX_USAGE_SAMPLE_TIME
WHERE SAMPLE_TIME = ?

-- query:kInsertUsageTimeID
INSERT INTO VPX_USAGE_SAMPLE_TIME (TIME_ID, SAMPLE_TIME, SAMPLE_INTERVAL)
VALUES(?,?,?)

-- query:kDeleteStatsId
DELETE
FROM VPX_STAT_ID
WHERE ID=?

-- query:kInsertDeviceId
INSERT INTO VPX_DEVICE(DEVICE_ID, DEVICE_NAME)
VALUES(?,?)

-- query:kQueryDeviceTypeId
SELECT DEVICE_TYPE_ID
FROM VPX_STAT_DEV_TYPE_DEF
WHERE DEVICE_TYPE_NAME = ?

-- query:kInsertStatDevType
INSERT INTO VPX_STAT_DEV_TYPE(DEVICE_ID, DEVICE_TYPE_ID)
VALUES(?,?)

-- query:kInsertStatCounter
INSERT INTO VPX_STAT_COUNTER(COUNTER_ID,ENTITY_ID,STAT_ID,DEVICE_ID)
VALUES (?,?,?,?)

-- query:kDeleteStatsCounters
DELETE
FROM VPX_STAT_COUNTER
WHERE ENTITY_ID=?

-- query:kDeleteFieldVals
DELETE
FROM VPX_FIELD_VAL
WHERE MO_ID=?

-- query:kLoadRollupSettings
SELECT INTERVAL_SEQ_NUM, INTERVAL_DEF_NAME, INTERVAL_VAL, INTERVAL_LENGTH, STATS_LEVEL,
       ROLLUP_ENABLED_FLG
FROM VPX_STAT_INTERVAL_DEF
ORDER BY INTERVAL_SEQ_NUM

-- query:kInsertRollupSetting
INSERT INTO VPX_STAT_INTERVAL_DEF
VALUES (?, ?, ?, ?, ?, ?)

-- query:kUpdateRollupSetting
UPDATE vpx_stat_interval_def
SET interval_def_name = ?, interval_val = ?, interval_length = ?, stats_level = ?, rollup_enabled_flg = ?
WHERE interval_seq_num = ?

-- query:kQueryStatsStatIdDeviceId
SELECT ST.STAT_ID, ST.DEVICE_ID, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VALUE
FROM VPX_HIST_STAT ST, VPX_SAMPLE SM
WHERE ST.SAMPLE_ID = SM.ID
  AND ST.STAT_ID = ?
  AND ST.DEVICE_ID = ?
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
  AND ST.ENTITY_ID = ?
  AND SM.SAMPLE_INTERVAL <= ?
ORDER BY SM.SAMPLE_TIME

-- query:kQueryStatsStatId
SELECT ST.STAT_ID, ST.DEVICE_ID, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VALUE
FROM VPX_HIST_STAT ST, VPX_SAMPLE SM
WHERE ST.SAMPLE_ID = SM.ID
  AND ST.STAT_ID = ?
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
  AND ST.ENTITY_ID = ?
  AND SM.SAMPLE_INTERVAL <= ?
ORDER BY ST.DEVICE_ID, SM.SAMPLE_TIME

-- query:kQueryStats
SELECT ST.STAT_ID, ST.DEVICE_ID, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VALUE
FROM VPX_HIST_STAT ST, VPX_SAMPLE SM
WHERE ST.SAMPLE_ID = SM.ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
  AND ST.ENTITY_ID = ?
  AND SM.SAMPLE_INTERVAL <= ?
ORDER BY ST.STAT_ID, ST.DEVICE_ID, SM.SAMPLE_TIME

-- query:kQuerySampleList1
SELECT sm.sample_time, sm.sample_interval, sm.partition_index
FROM vpx_sample_time1 sm
WHERE sm.sample_time > ?
  AND sm.sample_time <= ?
ORDER BY sm.sample_time

-- query:kQuerySampleList2
SELECT sm.sample_time, sm.sample_interval, sm.partition_index
FROM vpx_sample_time2 sm
WHERE sm.sample_time > ?
  AND sm.sample_time <= ?
ORDER BY sm.sample_time

-- query:kQuerySampleList3
SELECT sm.sample_time, sm.sample_interval, sm.partition_index
FROM vpx_sample_time3 sm
WHERE sm.sample_time > ?
  AND sm.sample_time <= ?
ORDER BY sm.sample_time

-- query:kQuerySampleList4
SELECT sm.sample_time, sm.sample_interval, sm.partition_index
FROM vpx_sample_time4 sm
WHERE sm.sample_time > ?
  AND sm.sample_time <= ?
ORDER BY sm.sample_time

-- query:kSelectHistStat1Part
SELECT *
FROM VPX_HIST_STAT1_%1

-- query:kSelectHistStat1All
SELECT *
FROM VPX_HIST_STAT1

-- query:kQueryStatsEx1
SELECT sc.entity_id, sc.stat_id, d.device_name, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time1 sm, vpx_device d, (%1) st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.entity_id in (%2)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, sc.stat_id, d.device_name, sm.sample_time

-- query:kQueryStatsEx2
SELECT sc.entity_id, sc.stat_id, d.device_name, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time2 sm, vpx_device d, vpx_hist_stat2 st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.entity_id in (%1)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, sc.stat_id, d.device_name, sm.sample_time

-- query:kQueryStatsEx3
SELECT sc.entity_id, sc.stat_id, d.device_name, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time3 sm, vpx_device d, vpx_hist_stat3 st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.entity_id in (%1)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, sc.stat_id, d.device_name, sm.sample_time

-- query:kQueryStatsEx4
SELECT sc.entity_id, sc.stat_id, d.device_name, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time4 sm, vpx_device d, vpx_hist_stat4 st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.entity_id in (%1)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, sc.stat_id, d.device_name, sm.sample_time

-- query:kQueryStatsStatIdEx1
SELECT sc.entity_id, d.device_name, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time1 sm, vpx_device d, (%1) st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.stat_id = ?
  AND sc.entity_id in (%2)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, d.device_name, sm.sample_time

-- query:kQueryStatsStatIdEx2
SELECT sc.entity_id, d.device_name, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time2 sm, vpx_device d, vpx_hist_stat2 st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.stat_id = ?
  AND sc.entity_id in (%1)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, d.device_name, sm.sample_time

-- query:kQueryStatsStatIdEx3
SELECT sc.entity_id, d.device_name, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time3 sm, vpx_device d, vpx_hist_stat3 st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.stat_id = ?
  AND sc.entity_id in (%1)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, d.device_name, sm.sample_time

-- query:kQueryStatsStatIdEx4
SELECT sc.entity_id, d.device_name, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time4 sm, vpx_device d, vpx_hist_stat4 st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.stat_id = ?
  AND sc.entity_id in (%1)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, d.device_name, sm.sample_time

-- query:kQueryStatsStatIdDeviceIdEx1
SELECT sc.entity_id, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time1 sm, vpx_device d, (%1) st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.stat_id = ?
  AND d.device_name = ?
  AND sc.entity_id in (%2)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, sm.sample_time

-- query:kQueryStatsStatIdDeviceIdEx2
SELECT sc.entity_id, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time2 sm, vpx_device d, vpx_hist_stat2 st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.stat_id = ?
  AND d.device_name = ?
  AND sc.entity_id in (%1)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, sm.sample_time

-- query:kQueryStatsStatIdDeviceIdEx3
SELECT sc.entity_id, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time3 sm, vpx_device d, vpx_hist_stat3 st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.stat_id = ?
  AND d.device_name = ?
  AND sc.entity_id in (%1)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, sm.sample_time

-- query:kQueryStatsStatIdDeviceIdEx4
SELECT sc.entity_id, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time4 sm, vpx_device d, vpx_hist_stat4 st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.stat_id = ?
  AND d.device_name = ?
  AND sc.entity_id in (%1)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, sm.sample_time

-- query:kQueryStatsStatIdNullDeviceEx1
SELECT sc.entity_id, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time1 sm, vpx_device d, (%1) st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.stat_id = ?
  AND coalesce(d.device_name, N'') = N''
  AND sc.entity_id in (%2)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, sm.sample_time

-- query:kQueryStatsStatIdNullDeviceEx2
SELECT sc.entity_id, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time2 sm, vpx_device d, vpx_hist_stat2 st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.stat_id = ?
  AND coalesce(d.device_name, N'') = N''
  AND sc.entity_id in (%1)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, sm.sample_time

-- query:kQueryStatsStatIdNullDeviceEx3
SELECT sc.entity_id, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time3 sm, vpx_device d, vpx_hist_stat3 st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.stat_id = ?
  AND coalesce(d.device_name, N'') = N''
  AND sc.entity_id in (%1)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, sm.sample_time

-- query:kQueryStatsStatIdNullDeviceEx4
SELECT sc.entity_id, sm.sample_time, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time4 sm, vpx_device d, vpx_hist_stat4 st
WHERE sm.sample_time >= ?
  AND sm.sample_time <= ?
  AND st.time_id = sm.time_id
  AND sc.stat_id = ?
  AND coalesce(d.device_name, N'') = N''
  AND sc.entity_id in (%1)
  AND sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
ORDER BY sc.entity_id, sm.sample_time

-- query:kQueryAvailableMetric
SELECT ST.STAT_ID, ST.DEVICE_ID
FROM VPX_HIST_STAT ST, VPX_SAMPLE SM
WHERE ST.ENTITY_ID = ?
  AND ST.SAMPLE_ID = SM.ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
GROUP BY ST.STAT_ID, ST.DEVICE_ID
ORDER BY ST.STAT_ID, ST.DEVICE_ID

-- query:kDeleteNetworkAssignment
DELETE
FROM vpx_nw_assignment
WHERE network_id = ?
  and entity_id = ?

-- query:kDeleteDatastoreAssignment
DELETE
FROM vpx_ds_assignment
WHERE ds_id = ?
  and entity_id = ?

-- query:kDeleteComputeResourceDasVm
DELETE
FROM VPX_COMPUTE_RESOURCE_DAS_VM
WHERE COMP_RES_ID = ?

-- query:kDeleteComputeResourceOrcVm
DELETE
FROM VPX_COMPUTE_RESOURCE_ORC_VM
WHERE COMP_RES_ID = ?

-- query:kDeleteComputeResourceDasHbDsSys
DELETE
FROM VPX_DAS_HB_DATASTORE_SYS
WHERE COMP_RES_ID = ?

-- query:kDeleteComputeResourceDasUserHbDs
DELETE
FROM VPX_COMPUTE_RES_USER_HB_DS
WHERE COMP_RES_ID = ?

-- query:kDeleteDasHbDsSysForDs
DELETE
FROM VPX_DAS_HB_DATASTORE_SYS
WHERE DATASTORE_ID = ?

-- query:kDeleteComputeResourceDrsVm
DELETE
FROM VPX_COMPUTE_RESOURCE_DRS_VM
WHERE COMP_RES_ID = ?

-- Add the following query to help debug PR 692907
-- query:kQueryComputeResourceDrsVm
SELECT COMP_RES_ID, VM_ID
FROM VPX_COMPUTE_RESOURCE_DRS_VM
WHERE COMP_RES_ID = ?

-- query:kDeleteComputeResourceDpmHost
DELETE
FROM VPX_COMPUTE_RESOURCE_DPM_HOST
WHERE COMP_RES_ID = ?

-- query:kLookupVmByUuid
SELECT ID
FROM VPX_VM
WHERE LOWER(UUID_BIOS) = LOWER(?)
  AND IS_TEMPLATE = 0

-- query:kLookupVmByUuidIncludeTemplates
SELECT ID
FROM VPX_VM
WHERE LOWER(UUID_BIOS) = LOWER(?)

-- query:kLookupVmByUuidAndDatacenter
SELECT ID
FROM VPX_VM
WHERE LOWER(UUID_BIOS) = LOWER(?)
  AND DATACENTER_ID = ?
  AND IS_TEMPLATE = 0

-- query:kLookupVmByUuidAndDatacenterIncludeTemplates
SELECT ID
FROM VPX_VM
WHERE LOWER(UUID_BIOS) = LOWER(?)
  AND DATACENTER_ID = ?

-- query:kLookupVmByInstanceUuid
SELECT ID
FROM VPX_VM
WHERE UUID_INSTANCE = ?
  AND IS_TEMPLATE = 0

-- query:kLookupVmByInstanceUuidIncludeTemplates
SELECT ID
FROM VPX_VM
WHERE UUID_INSTANCE = ?

-- query:kLookupVmByInstanceUuidAndDatacenter
SELECT ID
FROM VPX_VM
WHERE UUID_INSTANCE = ?
  AND DATACENTER_ID = ?
  AND IS_TEMPLATE = 0

-- query:kLookupVmByInstanceUuidAndDatacenterIncludeTemplates
SELECT ID
FROM VPX_VM
WHERE UUID_INSTANCE = ?
  AND DATACENTER_ID = ?

-- query:kLookupVmByUrl
SELECT ID
FROM VPX_VM
WHERE FILE_NAME = ?

-- query:kLookupVmByUrlAndDatacenter
SELECT ID
FROM VPX_VM
WHERE FILE_NAME = ?
  AND DATACENTER_ID = ?

-- query:kLookupVmByDnsName
SELECT ID
FROM VPX_VM
WHERE LOWER(DNS_NAME) = LOWER(?)
  AND IS_TEMPLATE = 0

-- query:kLookupVmByDnsNameAndDatacenter
SELECT ID
FROM VPX_VM
WHERE LOWER(DNS_NAME) = LOWER(?)
  AND DATACENTER_ID = ?
  AND IS_TEMPLATE = 0

-- query:kLookupVmByIp
SELECT VPX_VM.ID
FROM VPX_VM
LEFT JOIN VPX_IP_ADDRESS ON VPX_VM.ID = VPX_IP_ADDRESS.ENTITY_ID
WHERE (VPX_VM.IP_ADDRESS = ?
       OR VPX_IP_ADDRESS.IP_ADDRESS = ?)
  AND VPX_VM.IS_TEMPLATE = 0

-- query:kLookupVmByIpAndDatacenter
SELECT VPX_VM.ID
FROM VPX_VM
LEFT JOIN VPX_IP_ADDRESS ON VPX_VM.ID = VPX_IP_ADDRESS.ENTITY_ID
WHERE (VPX_VM.IP_ADDRESS = ?
       OR VPX_IP_ADDRESS.IP_ADDRESS = ?)
  AND VPX_VM.DATACENTER_ID = ?
  AND VPX_VM.IS_TEMPLATE = 0

-- query:kLookupHostByUuid
SELECT ID
FROM VPX_HOST
WHERE UUID_BIOS = ?

-- query:kLookupHostByUuidAndDatacenter
SELECT ID
FROM VPX_HOST
WHERE UUID_BIOS = ?
  AND DATACENTER_ID = ?

-- query:kLookupHostByDnsName
SELECT ID
FROM VPX_HOST
WHERE LOWER(DNS_NAME) = LOWER(?)

-- query:kLookupHostByDnsNameAndDatacenter
SELECT ID
FROM VPX_HOST
WHERE LOWER(DNS_NAME) = LOWER(?)
  AND DATACENTER_ID = ?

-- query:kLookupHostByIp
SELECT ID
FROM VPX_HOST
WHERE IP_ADDRESS = ?

-- query:kLookupHostByIpAndDatacenter
SELECT ID
FROM VPX_HOST
WHERE IP_ADDRESS = ?
  AND DATACENTER_ID = ?

-- Datastore related.
-- query:kDeleteVmfsExtents
DELETE
FROM VPX_DS_VMFS_EXTENT
WHERE ID = ?

-- query:kInfraUpdateHaClusterProviders
SELECT t1.PROVIDER_UUID
FROM VPX_INFRA_UPD_HA_PROVIDER t1
INNER JOIN VPX_INFRA_UPD_HA_CP t2 ON t2.PROVIDER_ID = t1.PROVIDER_ID
WHERE t2.CLUSTER_ID = ?

-- query:kDeleteVmDsSpaceValuesForDatastore
DELETE
FROM VPX_VM_DS_SPACE
WHERE DS_ID = ?

-- query:kGetDatacenterList
SELECT ID
FROM VPX_DATACENTER

-- query:kQueryVmOptionDescriptors
SELECT HOST_ID, CONFIG_OPTION_VER, CONFIG_OPTION_DESC, CREATE_SUPPORTED_FLG,
       DEFAULT_CONFIG_OPTION_FLG, RUN_SUPPORTED_FLG, UPGRADE_SUPPORTED_FLG
FROM VPX_HOST_VM_CONFIG_OPTION
WHERE HOST_ID = ?
ORDER BY ARRAY_INDEX

-- query:kQueryVmOptions
SELECT ARRAY_INDEX, CONFIG_OPTION_VER, DATA
FROM VPX_HOST_VM_CONFIG_OPTION
WHERE HOST_ID = ?
ORDER BY ARRAY_INDEX

-- query:kRemoveVmProvPolicy
update VPX_HOST
set VM_PROV_POLICY = NULL
where ID = ?

-- query:kUpdateScsiLunDisplayName
update VPX_HOST_SCSI_LUN
SET DISPLAY_NAME = ?
WHERE HOST_ID = ?
  AND UUID = ?

-- query:kDeleteExtTypeIds
DELETE
FROM VPX_EXT_TYPE_IDS
WHERE ext_id = ?

-- query:kDeleteExtPrivs
DELETE
FROM VPX_EXT_PRIVS
WHERE EXT_ID = ?

-- query:kDeleteExtSolutionTabs
DELETE
FROM VPX_EXT_TABS
WHERE EXT_ID = ?

-- query:kDeleteExtManagedEntities
DELETE
FROM VPX_EXT_MANAGEDBY_INFO
WHERE EXT_ID = ?

-- VMs managedBy an Extension
-- query:kQueryVmsManagedByExtension
SELECT ID
FROM VPX_VM
WHERE MANAGED_BY_EXT_KEY = ?

-- vApps managedBy an Extension
-- query:kQueryVAppsManagedByExtension
SELECT ID
FROM VPX_RESOURCE_POOL
WHERE MANAGED_BY_EXT_KEY = ?

-- Extension data storage sql
-- query:kQueryAllExtensionData
select DATA_KEY, DATA_VALUE
from VPX_EXT_DATA
where EXT_ID = ?

-- query:kQueryExtensionData
select DATA_KEY, DATA_VALUE
from VPX_EXT_DATA
where EXT_ID = ?
  and DATA_KEY = ?

-- query:kQueryExtensionDataKeys
select DATA_KEY
from VPX_EXT_DATA
where EXT_ID = ?

-- query:kClearAllExtensionData
delete
from VPX_EXT_DATA
where EXT_ID = ?

-- query:kClearExtensionData
delete
from VPX_EXT_DATA
where EXT_ID = ?
  and DATA_KEY = ?

-- query:kQueryAllExtensionDataSize
select EXT_ID, count(*), sum(length(DATA_KEY) + length(DATA_VALUE))
from VPX_EXT_DATA
group by EXT_ID

-- query:kQueryExtensionDataSize
select EXT_ID, count(*), sum(length(DATA_KEY) + length(DATA_VALUE))
from VPX_EXT_DATA
where EXT_ID = ?
group by EXT_ID

-- query:kGetPortGroupScope
SELECT SCOPE
FROM VPX_DVS_SCOPE
WHERE DVS_ID = ?
  AND DVPORTGROUP_ID = ?
  AND DVPORT_KEY = ?

-- VDisk IOFilter related queries
-- query:kDeleteVDiskIOFilters
DELETE
FROM VPX_VIRTUAL_DISK_IOFILTERS
WHERE VM_ID = ?

-- query:kInsertVDiskIOFilter
INSERT INTO VPX_VIRTUAL_DISK_IOFILTERS
VALUES (?, ?, ?)

-- virtual disk queries for linked clone used in post-ORM era
-- query:kDeleteVdeviceBackingRelLcValues
DELETE
FROM VPX_VDEVICE_BACKING_REL
WHERE VM_ID = ?

-- query:kDeleteSnVdeviceBackingRelLcValues
DELETE
FROM VPX_SN_VDEVICE_BACKING_REL
WHERE SN_ID = ?

-- virtual disk queries for linked clones
-- query:kGetVmsSharingBackingExcludingInputVm
SELECT 1
FROM VPX_DUAL
WHERE EXISTS
    (SELECT 1
     FROM VPX_SN_VDEVICE_FILE_BACKING T1, VPX_SNAPSHOT T2
     WHERE T1.HARD_DEVICE_BACKING_FILE_NAME = ?
       AND T1.SNAPSHOT_ID = T2.ID
       AND T2.VM_ID <> ?)
  OR EXISTS
    (SELECT 1
     FROM VPX_VDEVICE_FILE_BACKING
     WHERE HARD_DEVICE_BACKING_FILE_NAME = ?
       AND VM_ID <> ?)

-- query:kGetVmsSharingBacking

  (SELECT DISTINCT T2.VM_ID
   FROM VPX_SN_VDEVICE_FILE_BACKING T1, VPX_SNAPSHOT T2
   WHERE T1.HARD_DEVICE_BACKING_FILE_NAME = ?
     AND T1.SNAPSHOT_ID=T2.ID)
UNION ALL
  (SELECT DISTINCT VM_ID
   FROM VPX_VDEVICE_FILE_BACKING
   WHERE HARD_DEVICE_BACKING_FILE_NAME = ?)

-- query:kIsChildMostSnapshotBacking
SELECT 1
FROM VPX_SN_VDEVICE_FILE_BACKING T1, VPX_SNAPSHOT T2
WHERE T1.HARD_DEVICE_BACKING_FILE_NAME = ?
  AND T1.SNAPSHOT_ID=T2.ID
  AND T2.VM_ID = ?
  AND NOT EXISTS
    (SELECT 1
     from VPX_SN_VDEVICE_FILE_BACKING T3
     WHERE T3.FILE_BACKING_PARENT_ID =T1.BACKING_ID)

-- query:kHasChildren
SELECT count(1)
FROM VPX_VDEVICE_FILE_BACKING a, VPX_VDEVICE_FILE_BACKING b
WHERE a.BACKING_ID= b.FILE_BACKING_PARENT_ID
  AND a.HARD_DEVICE_BACKING_FILE_NAME = ?

-- query:kNumDirectVmRefs
SELECT count(1)
FROM VPX_VDEVICE_FILE_BACKING
WHERE HARD_DEVICE_BACKING_FILE_NAME = ?
  AND NOT EXISTS
    (SELECT 1
     from VPX_VDEVICE_FILE_BACKING tmp
     WHERE tmp.FILE_BACKING_PARENT_ID = VPX_VDEVICE_FILE_BACKING.BACKING_ID)

-- query:kGetNonRunningVmBackingWithContentId
SELECT DISTINCT a.HARD_DEVICE_BACKING_FILE_NAME, a.BACKING_ID, b.VPX_TYPE, a.DELTA_DISK_FORMAT
FROM VPX_VDEVICE_FILE_BACKING a, VPX_VDEVICE_BACKING_REL b
WHERE DATASTORE_ID = ?
  AND FILE_BACKING_CONTENT_ID = ?
  AND SECTOR_FORMAT = ?
  AND a.BACKING_ID = b.BACKING_ID
  AND EXISTS
    (SELECT 1
     from VPX_VDEVICE_FILE_BACKING tmp
     WHERE tmp.FILE_BACKING_PARENT_ID = a.BACKING_ID)

-- query:kGetNonRunningSnBackingWithContentId
SELECT DISTINCT d.HARD_DEVICE_BACKING_FILE_NAME, d.BACKING_ID, e.VPX_TYPE, d.DELTA_DISK_FORMAT
FROM VPX_SN_VDEVICE_FILE_BACKING d, VPX_SN_VDEVICE_BACKING_REL e
WHERE DATASTORE_ID = ?
  AND FILE_BACKING_CONTENT_ID = ?
  AND SECTOR_FORMAT = ?
  AND d.BACKING_ID = e.BACKING_ID
  AND EXISTS
    (SELECT 1
     from VPX_SN_VDEVICE_FILE_BACKING tmp
     WHERE tmp.FILE_BACKING_PARENT_ID = d.BACKING_ID)

-- query:kDeleteVmDsSpaceValues
DELETE
FROM VPX_VM_DS_SPACE
WHERE VM_ID = ?

-- query:kInsertVmDsSpaceValues
INSERT INTO VPX_VM_DS_SPACE
VALUES (?, ?, ?, ?, ?, ?)

-- query:kGetVmSpaceUsageByDs
SELECT DS_ID, COMMITED_SPACE, UNCOMMITED_SPACE, UNSHARED_SPACE, UPDATED_TIME
FROM VPX_VM_DS_SPACE
WHERE VM_ID = ?

-- query:kGetDsSpaceUsageByVm
SELECT VM_ID, COMMITED_SPACE, UNCOMMITED_SPACE, UNSHARED_SPACE
FROM VPX_VM_DS_SPACE
WHERE DS_ID = ?

-- query:kGetDsSpaceSummary
SELECT SUM(UNCOMMITED_SPACE)
FROM VPX_VM_DS_SPACE
WHERE DS_ID = ?

-- query:kGetDatacenters
SELECT count(1) as TOTAL
FROM VPX_DATACENTER

-- query:kGetDatastores
SELECT count(1) as TOTAL
FROM VPX_DATASTORE

-- query:kGetClusters
SELECT count(1) as TOTAL
FROM VPX_COMPUTE_RESOURCE

-- query:kGetResourcePools
SELECT count(1) as TOTAL
FROM VPX_RESOURCE_POOL

-- query:kEightHostLimitQuery
select host_id
from vpx_vm, vpx_vdevice_file_backing
where vpx_vm.id = vpx_vdevice_file_backing.vm_id
  and vpx_vdevice_file_backing.hard_device_backing_file_name = ?

-- query:kGetHostInformation
SELECT COUNT(ID)
FROM VPX_HOST

-- query:kGetVmInformation
SELECT COUNT(1) AS TOTALVM, SUM(CASE
                                    WHEN POWER_STATE = ? THEN 1
                                    ELSE 0
                                END) AS POWEREDONVM
FROM VPX_VM

-- query:kGetHostsUsingLun
SELECT S2.HOST_ID, S1.QUALITY
FROM VPX_HOST_SCSILUN_DESCRIPTOR S1, VPX_HOST_SCSI_LUN S2
WHERE S1.DESCRIPTOR_ID_VAL = ?
  and S2.ID=S1.ID

-- query:kGetHostsUsingCanonicalName
SELECT S2.HOST_ID, S1.QUALITY
FROM VPX_HOST_SCSILUN_DESCRIPTOR S1, VPX_HOST_SCSI_LUN S2
WHERE S2.CANONICAL_NAME = ?
  and S2.ID=S1.ID

-- query:kGetHostAndDisplayNameUsingLun
select S2.HOST_ID, S2.DISPLAY_NAME
from VPX_HOST_SCSILUN_DESCRIPTOR S1, VPX_HOST_SCSI_LUN S2
where S1.DESCRIPTOR_ID_VAL = ?
  and S1.QUALITY not in ('lowQuality', 'unknownQuality')
  and S2.ID=S1.ID

-- query:kGetDisplayNameUsingLunAndHost
select DISPLAY_NAME
FROM VPX_HOST_SCSI_LUN
WHERE UUID = ?
  AND HOST_ID = ?

-- query:kGetUsedAsVmfsLuns
select distinct SL.UUID, SLD.QUALITY, SL.CANONICAL_NAME
FROM VPX_HOST_SCSI_LUN SL, VPX_HOST_SCSI_DISK_PARTITION SDP, VPX_HOST_SCSILUN_DESCRIPTOR SLD
WHERE SL.CANONICAL_NAME = SDP.DISK_NAME
  AND SL.HOST_ID = SDP.HOST_ID
  AND SL.ID = SLD.ID
  AND SL.UUID=SLD.DESCRIPTOR_ID_VAL

-- query:kGetUsedAsRdmLuns

SELECT t1.DEVICE_BACKING_LUN_UUID, t2.CANONICAL_NAME
FROM
((SELECT DISTINCT DEVICE_BACKING_LUN_UUID
   FROM VPXV_VM_RDM_LUN_INFO
   WHERE DEVICE_BACKING_LUN_UUID IS NOT NULL)
UNION
  (SELECT DISTINCT DEVICE_BACKING_LUN_UUID
   FROM VPXV_SN_RDM_LUN_INFO
   WHERE DEVICE_BACKING_LUN_UUID IS NOT NULL)) t1 
LEFT JOIN
  (SELECT DISTINCT CANONICAL_NAME, UUID
   FROM VPXV_DS_LUN_INFO) t2
ON t1.DEVICE_BACKING_LUN_UUID = t2.UUID

-- query:kGetLunsSeenBySameHostsAsDs
SELECT DISTINCT T1.DESCRIPTOR_ID_VAL, T2.CANONICAL_NAME
FROM VPX_HOST_SCSILUN_DESCRIPTOR T1,
  (SELECT UUID, CANONICAL_NAME
   FROM VPX_HOST_SCSI_LUN
   WHERE HOST_ID IN
       (SELECT HOST_ID
        FROM VPXV_HOST_DATASTORE
        WHERE DS_ID = ? )
   GROUP BY UUID, CANONICAL_NAME
   HAVING COUNT(1) =
     (SELECT COUNT(1)
      FROM VPXV_HOST_DATASTORE
      WHERE DS_ID = ? )) T2
WHERE T1.DESCRIPTOR_ID_VAL = T2.UUID
  AND T1.QUALITY NOT IN ('lowQuality', 'unknownQuality')

-- query:kGetLunsOnHostWithoutLocalTransport
select UUID, CANONICAL_NAME
from VPX_HOST_SCSI_LUN
where HOST_ID = ?

-- query:kGetLunsUsedByGivenDatastore
select DISTINCT UUID
FROM VPXV_DS_LUN_INFO
WHERE URL = ?

-- query:kGetLunCanonicalNameUsingLun
select DISTINCT CANONICAL_NAME
FROM VPXV_DS_LUN_INFO
WHERE UUID = ?

-- query:kGetLunCanonicalNamesUsedByGivenDatastore
select DISTINCT CANONICAL_NAME
FROM VPXV_DS_LUN_INFO
WHERE URL = ?

-- query:kUpdateDatastoreAssignment
UPDATE VPX_DS_ASSIGNMENT
SET DS_ID = ?
WHERE ENTITY_ID = ?
  AND DS_ID = ?

-- query:kUpdateDatastoreForVmInVmDsSpace
UPDATE VPX_VM_DS_SPACE
SET DS_ID = ?
WHERE VM_ID = ?
  AND DS_ID = ?

-- query:kGetHosts
SELECT ID, ENABLED, PRODUCT_NAME
FROM VPX_HOST

-- query:kGetUsedAsForceMountedVmfsLuns
SELECT DISTINCT UUID, CANONICAL_NAME
FROM VPXV_DS_LUN_INFO
WHERE UUID IS NOT NULL
  AND FORCEMOUNT_FLG = 1

-- query:kGetPoweredOnVmsByHost
SELECT COUNT(1) AS TOTAL
FROM VPX_VM
WHERE POWER_STATE = ?
  and HOST_ID = ?

-- query:kGetPoweredOnNonVclsVmsCountByHost
SELECT COUNT(1) AS TOTAL
FROM VPX_VM
JOIN VPX_ENTITY ON VPX_VM.ID=VPX_ENTITY.ID
WHERE VPX_VM.POWER_STATE = ?
  AND VPX_VM.HOST_ID = ?
  AND (VPX_ENTITY.NAME NOT LIKE 'vCLS%'
    OR ((VPX_VM.MANAGED_BY_EXT_KEY != 'com.vmware.vim.eam' OR VPX_VM.MANAGED_BY_EXT_KEY IS NULL)
        AND (VPX_VM.MANAGED_BY_TYPE != 'cluster-agent' OR VPX_VM.MANAGED_BY_TYPE IS NULL)))

-- query:kGetRenameCapabilityUsingLunUuidAndHost
select UPDATE_DISPLAY_NAME_FLG
FROM VPX_HOST_SCSI_LUN
WHERE UUID = ?
  AND HOST_ID = ?

-- query:kGetRenameCapabilityUsingCanonicalNameAndHost
select UPDATE_DISPLAY_NAME_FLG
FROM VPX_HOST_SCSI_LUN
WHERE CANONICAL_NAME = ?
  AND HOST_ID = ?

-- query:kLoadEventArgsQueryBulk
SELECT EVENT_ID, ARG_ID, ARG_TYPE, ARG_DATA, OBJ_TYPE, OBJ_NAME, VM_ID, HOST_ID,
       COMPUTERESOURCE_ID, DATASTORE_ID, NETWORK_ID, NETWORK_TYPE, DVS_ID, DATACENTER_ID,
       RESOURCEPOOL_ID, FOLDER_ID, ALARM_ID, SCHEDULEDTASK_ID
FROM %1

-- query:kFindUplinkPortInfoInDB
select t1.DVPORT_KEY, t1.CONNECTEE_TYPE, t1.RESERVED_TIME, t1.CONNECTEE_NIC_KEY,
       t2.DVPORTGROUP_ID
from VPX_DVPORT t1, VPX_DVPORT_MEMBERSHIP t2
where t1.DVS_ID = t2.DVS_ID
  and t1.DVPORT_KEY = t2.DVPORT_KEY
  and t1.DVS_ID = ?
  and t2.HOST_ID = ?

-- find the free ports in the portgroup (exclude uplink ports)
-- query:kFindFreePortgroupPorts
select t1.DVPORT_KEY, t2.LAST_CONNECTEE
from VPX_DVPORT_MEMBERSHIP t1
left outer join VPX_DVPORT t2 on t1.DVS_ID = t2.DVS_ID
and t1.DVPORT_KEY = t2.DVPORT_KEY
where t1.DVPORTGROUP_ID = ?
  and t1.HOST_ID = 0
  and t2.CONNECTEE is NULL

-- Find the free ports also unused by Vspan session
-- in the portgroup (exclude uplink ports).
--/
-- query:kFindFreePortgroupPortsNotUsedByVspan
select t1.DVPORT_KEY
from VPX_DVPORT_MEMBERSHIP t1
left outer join VPX_DVPORT t2 on t1.DVS_ID = t2.DVS_ID
and t1.DVPORT_KEY = t2.DVPORT_KEY
left outer join VPX_DVS_VSPAN_PORTS t3 on t1.DVS_ID = t3.DVS_ID
and t3.PORT_IDENTIFIER_VALUE = t1.DVPORT_KEY
and t3.PORT_IDENTIFIER_TYPE = 'PortKey'
where t1.DVPORTGROUP_ID = ?
  and t1.HOST_ID = 0
  and t2.CONNECTEE is NULL
  and t3.PORT_IDENTIFIER_TYPE is NULL

-- Find the number of free ports in the vc for garbage collection(exclude uplink ports)
-- Free ports from dvpg that are auto-expanded will be selected
--/
-- query:kFindNumFreePortsInAutoExpandedDvpg
select t1.DVPORTGROUP_ID, COUNT(t1.DVPORT_KEY) AS NUM_FREE_PORTS
from VPX_DVPORT_MEMBERSHIP t1
left outer join VPX_DVPORT t2 on t1.DVS_ID = t2.DVS_ID
and t1.DVPORT_KEY = t2.DVPORT_KEY
where t1.HOST_ID = 0
  and t2.CONNECTEE is NULL
  and t1.DVPORTGROUP_ID IN
    (select ID
     from VPX_DVPORTGROUP
     where (AUTO_EXPAND = 1)
       and (CONFIG_NUM_PORT is not NULL)
       and (NUM_PORT > CONFIG_NUM_PORT))
group by t1.DVPORTGROUP_ID
having (COUNT(t1.DVPORT_KEY) > ?)

-- query:kFindConnectedPortgroupPorts
select t1.DVPORT_KEY
from VPX_DVPORT t1
left outer join VPX_DVPORT_MEMBERSHIP t2 on t1.DVS_ID = t2.DVS_ID
and t1.DVPORT_KEY = t2.DVPORT_KEY
where t2.DVPORTGROUP_ID = ?
  and t1.CONNECTEE is not NULL

-- query:kFindVspanPortgroupPorts
select t2.DVPORT_KEY
from VPX_DVS_VSPAN_PORTS t1
left outer join VPX_DVPORT_MEMBERSHIP t2 on t1.DVS_ID = t2.DVS_ID
and t1.PORT_IDENTIFIER_VALUE = t2.DVPORT_KEY
and t1.PORT_IDENTIFIER_TYPE = 'PortKey'
where t2.DVPORTGROUP_ID = ?

-- query:kFindFreeStandalonePorts
select t1.DVPORT_KEY
from VPX_DVPORT_MEMBERSHIP t1
left outer join VPX_DVPORT t2 on t1.DVS_ID = t2.DVS_ID
and t1.DVPORT_KEY = t2.DVPORT_KEY
where t1.DVS_ID = ?
  and t1.DVPORTGROUP_ID = ?
  and t2.CONNECTEE is NULL

-- find all ports that are connected but non-uplink
-- query:kFindConnectedPortsInDvs
select t1.DVPORT_KEY, t1.CONNECTEE, t1.CONNECTEE_TYPE, t1.CONNECTEE_NIC_KEY, t1.RESERVED_TIME
from VPX_DVPORT t1
left outer join VPX_DVPORT_MEMBERSHIP t2 on t1.DVS_ID = t2.DVS_ID
and t1.DVPORT_KEY = t2.DVPORT_KEY
where t1.DVS_ID = ?
  and t2.HOST_ID = 0
  and t1.CONNECTEE is not NULL

-- find all ports that are connected but uplink
-- query:kFindConnectedUplinkPortsInDvs
select t1.DVPORT_KEY, t2.HOST_ID, t1.CONNECTEE_TYPE, t1.CONNECTEE_NIC_KEY, t1.CONNECTION_COOKIE
from VPX_DVPORT t1
left outer join VPX_DVPORT_MEMBERSHIP t2 on t1.DVS_ID = t2.DVS_ID
and t1.DVPORT_KEY = t2.DVPORT_KEY
where t1.DVS_ID = ?
  and t2.HOST_ID <> 0
  and t1.CONNECTEE is not NULL

-- find ports in dvs that is uplink, connected or reserved (that will have port data
-- on host)
-- query:kFindRunningPortsInDvs
select DVPORT_KEY
from VPX_DVPORT
where DVS_ID = ?
  and (PROXY_HOST is not NULL
       or CONNECTEE is not NULL)

-- find ports in dvportgroup that is uplink, connected or reserved (that will have
-- port data on host)
-- query:kFindRunningPortsInDvpg
select t1.DVPORT_KEY
from VPX_DVPORT t1
left outer join VPX_DVPORT_MEMBERSHIP t2 on t1.DVS_ID = t2.DVS_ID
and t1.DVPORT_KEY = t2.DVPORT_KEY
where t2.DVPORTGROUP_ID = ?
  and (t1.PROXY_HOST is not NULL
       or t1.CONNECTEE is not NULL)

-- query:kFindPortsOfConnecteeTypeInDvs
select DVPORT_KEY
from VPX_DVPORT
where DVS_ID = ?
  and CONNECTEE_TYPE = ?

-- query:kFindPortsOnHost
select DVPORT_KEY
from VPX_DVPORT
where DVS_ID = ?
  and PROXY_HOST = ?

-- query:kFindReservedPortsAndPortsOnHost
select DVPORT_KEY
from VPX_DVPORT
where DVS_ID = ?
  and (PROXY_HOST = ?
       or (RESERVED_TIME is not NULL))

-- query:kGetPortgroupPorts
select DVPORT_KEY
from VPX_DVPORT_MEMBERSHIP
where DVPORTGROUP_ID = ?

-- query:kGetDvsPortNumber
select count(distinct DVPORT_KEY)
from VPX_DVPORT_MEMBERSHIP
where DVS_ID = ?

-- query:kGetDvportgroupPortNumber
select count(distinct DVPORT_KEY)
from VPX_DVPORT_MEMBERSHIP
where DVPORTGROUP_ID = ?

-- query:kGetPvlanIds
select PVLAN_ID, DVPORTGROUP_ID, DVPORT_KEY
from VPX_DVPORT_SETTING
where DVS_ID = ?
  and PVLAN_ID is not NULL

-- find all ports that are connected but non-uplink
-- query:kFindUplinkPortByNameInDvs
select t1.DVPORT_KEY, t1.NAME
from VPX_DVPORT t1
left outer join VPX_DVPORT_MEMBERSHIP t2 on t1.DVS_ID = t2.DVS_ID
and t1.DVPORT_KEY = t2.DVPORT_KEY
where t1.DVS_ID = ?
  and t2.HOST_ID <> 0

-- query:kFindEntitiesAssociatedWithNrp
select DVS_ID, DVPORTGROUP_ID, DVPORT_KEY
from VPX_DVPORT_SETTING
where DVS_ID = ?
  and NRP_KEY = ?

-- query:kFindDvpgsAssociatedWithVmVnicRP
select DVPORTGROUP_NAME
from VPX_DVPORTGROUP
where DVS_ID = ?
  and VMVNIC_RESOURCEPOOL_KEY = ?

-- query:kGetVlanIdReferredInDvs1
select VLAN_ID
from VPX_DVPORT_SETTING
where DVS_ID = ?
  and VLAN_ID is not null

-- query:kGetVlanIdReferredInDvs2
select SECONDARY_VLANID
from VPX_DVS_PVLAN
where DVS_ID = ?

-- query:kGetDvpgAndPortVlanInDvs
select VLAN_ID
from VPX_DVPORT_SETTING
where DVS_ID = ?
  and VLAN_ID is not null
  and (DVPORTGROUP_ID != 0
       or DVPORT_KEY != ' ')

-- query:kGetDvpgAndPortTrunkVlanInDvs
select VLAN_ID_START, VLAN_ID_END
from VPX_DVPORT_VLAN
where DVS_ID = ?
  and RUNTIMEINFO_FLG = 0
  and (DVPORTGROUP_ID != 0
       or DVPORT_KEY != ' ')

-- query:kGetTrunkVlanInDvs
select VLAN_ID_START, VLAN_ID_END
from VPX_DVPORT_VLAN
where DVS_ID = ?
  and RUNTIMEINFO_FLG = 0

-- query:kGetIPHashPolicyReferredInDvs
select TEAMING_POLICY
from VPX_DVPORT_SETTING
where DVS_ID = ?
  and TEAMING_POLICY = 'loadbalance_ip'

-- find the ports in the portgroup that are connected to a vm
-- query:kFindVmConnectedPortInPortgroup
select t1.DVPORT_KEY
from VPX_DVPORT t1
left outer join VPX_DVPORT_MEMBERSHIP t2 on t1.DVS_ID = t2.DVS_ID
and t1.DVPORT_KEY = t2.DVPORT_KEY
where t2.DVPORTGROUP_ID = ?
  and t1.CONNECTEE = ?
  and t1.CONNECTEE_NIC_KEY = ?
  and CONNECTEE_TYPE = 'vmVnic'

-- find the portgroups that has a ports on the host
-- query:kFindPortgroupThatHasPortsOnHost
select distinct t2.DVPORTGROUP_ID
from VPX_DVPORT t1
left outer join VPX_DVPORT_MEMBERSHIP t2 on t1.DVS_ID = t2.DVS_ID
and t1.DVPORT_KEY = t2.DVPORT_KEY
where t1.DVS_ID = ?
  and t1.PROXY_HOST = ?

-- find the portgroups that has a ports on any host
-- query:kFindPortgroupThatHasPortsOnHosts
select distinct t2.DVPORTGROUP_ID, t1.PROXY_HOST
from VPX_DVPORT t1
left outer join VPX_DVPORT_MEMBERSHIP t2 on t1.DVS_ID = t2.DVS_ID
and t1.DVPORT_KEY = t2.DVPORT_KEY
where t1.DVS_ID = ?
  and (t1.PROXY_HOST is not NULL
       and t1.PROXY_HOST <> '')

-- find the reserved ports
-- query:kFindReservedPorts
select DVS_ID, DVPORT_KEY, RESERVED_TIME, CONNECTEE, CONNECTEE_TYPE, CONNECTEE_NIC_KEY
from VPX_DVPORT
where RESERVED_TIME is not null

-- query:kResetAllPortReservation
update VPX_DVPORT
set CONNECTEE = null, CONNECTEE_TYPE = null, CONNECTEE_NIC_KEY = null, CONNECTEE_ADDRESS = null,
    RESERVED_TIME = null
where RESERVED_TIME is not null

-- fetch the hosts of dvses
-- query:kFetchDvsHosts
select DVS_ID, HOST_ID
from VPX_DVHOST

-- fetch the ports of dvpgs
-- query:kFetchDvpgPorts
select DVPORTGROUP_ID, DVPORT_KEY
from VPX_DVPORT_MEMBERSHIP

-- fetch the ports of dvpgs
-- query:kFetchOutOfSyncData
select DVS_ID, HOST_ID, PROPERTY_NAME
from VPX_DVHOST_OUT_OF_SYNC

-- query:kAddOpaqueData
insert into VPX_DVS_OPAQUE_DATA (DVS_ID, DVPORTGROUP_ID, DVPORT_KEY, HOST_ID, IS_RUNTIME, IS_INHERITED_TYPE, OPAQUE_KEY, OPAQUE_DATA)
values(?, ?, ?, ?, ?, ?, ?, ?)

-- query:kRemoveOpaqueData
DELETE
FROM VPX_DVS_OPAQUE_DATA
WHERE DVS_ID = ?
  AND DVPORTGROUP_ID = ?
  AND DVPORT_KEY = ?
  AND HOST_ID = ?
  AND IS_RUNTIME = ?

-- query:kFetchOpaqueData
select DVS_ID, DVPORTGROUP_ID, DVPORT_KEY, HOST_ID, IS_RUNTIME, IS_INHERITED_TYPE,
       OPAQUE_KEY, OPAQUE_DATA
FROM VPX_DVS_OPAQUE_DATA

-- query:kFetchVlanHealthCheckResult
select TRUNKED_FLG, VLAN_ID_START, VLAN_ID_END
from VPX_DVHOST_HC_VLAN_RESULT
where DVS_ID = ?
  and HOST_ID = ?
  and UPLINK_PORT_KEY = ?

-- query:kFetchMtuHealthCheckResult
select SUPPORTED_FLG, VLAN_ID_START, VLAN_ID_END
from VPX_DVHOST_HC_MTU_RESULT
where DVS_ID = ?
  and HOST_ID = ?
  and UPLINK_PORT_KEY = ?

-- query:kDeleteVlanHealthCheckResult
delete
from VPX_DVHOST_HC_VLAN_RESULT
where DVS_ID = ?
  and HOST_ID = ?
  and UPLINK_PORT_KEY = ?

-- query:kDeleteMtuHealthCheckResult
delete
from VPX_DVHOST_HC_MTU_RESULT
where DVS_ID = ?
  and HOST_ID = ?
  and UPLINK_PORT_KEY = ?

-- query:kFindNrpKeyCount
SELECT COUNT(1)
from VPX_DVS_NRP
WHERE NRP_KEY = ?

-- Delete all associations for a particular pool
-- query:kDeleteIpPoolAssociations
delete
from VPX_IP_POOL_ASSOCIATION
where POOL_ID = ?

-- query:kDeleteIpPool
delete
from VPX_IP_POOL
where ID = ?
  and DATACENTER_ID = ?

-- query:kLookupAssociatedIpPool
select ID, NAME
from VPX_IP_POOL P
join VPX_IP_POOL_ASSOCIATION PA on P.ID = PA.POOL_ID
where PA.NETWORK_ID = ?
  and P.DATACENTER_ID = ?

-- query:kLookupAllIpPoolNames
select ID, NAME, P.DATACENTER_ID, PA.NETWORK_ID
from VPX_IP_POOL P
join VPX_IP_POOL_ASSOCIATION PA on P.ID = PA.POOL_ID

-- query:kLookupIpPoolAllocations
select POOL_ID, ALLOCATION_ID, EXTENSION_KEY, IP_ADDRESS
from VPX_IP_POOL_ALLOCATION

-- query:kDeleteIpPoolAllocation
delete
from VPX_IP_POOL_ALLOCATION
where POOL_ID = ?
  and ALLOCATION_ID = ?
  and EXTENSION_KEY = ?

-- query:kDeleteIpPoolAllocationsByExtension
delete
from VPX_IP_POOL_ALLOCATION
where EXTENSION_KEY = ?

-- query:kUpdateStatLevel
UPDATE vpx_stat_def
SET stat_level = ?
WHERE id = ?

-- query:kQuerySchemaHash
select SCHEMA_HASH
from VPX_SCHEMA_HASH

-- query:kInsertSchemaHash
insert into VPX_SCHEMA_HASH (SCHEMA_HASH, BUILD_NUM)
VALUES (?, ?)

-- query:kQueryFaultToleranceConfigInfo
select ID, TEXT_VALUE
from VPX_VM_TEXT
where TYPE_ID = (?)

-- query:kQueryVmResourceConfig
select CPU_ALLOCATION_RESERVATION, CPU_ALLOCATION_LIMIT, C_ALLOC_EXPANDABLE_RESERV_FLG,
       CPU_ALLOCATION_OVERHEAD_LIMIT, CPU_ALLOCATION_SHARES_SHARES, CPU_ALLOCATION_SHARES_LEVEL,
       MEMORY_ALLOCATION_RESERVATION, MEMORY_ALLOCATION_LIMIT, MEM_ALLOCEXPAN_RESERVATION_FLG,
       MEM_ALLOCATION_OVERHEAD_LIMIT, MEM_ALLOCATION_SHARES_SHARES, MEM_ALLOCATION_SHARES_LEVEL
from VPX_NON_ORM_VM_CONFIG_INFO
where ID = ?

-- query:kLookupAssociatedVmid
select VM_ID
from VPX_NORM_VM_FLE_FILE_INFO
where NAME = ?

-- query:kRemoveManagedEntityAutoKeys

delete
from VPX_LIC_AUTO_KEYS
where ENTITY_ID = ?

-- query:kIsSharedAcrossDCs
SELECT COUNT(*)
FROM VPX_DATASTORE_INFO
WHERE URL = ?

-- query:kQueryDbMonitoringCounterSamples
select TMP2.COUNTER_ID, TMP2.SAMPLE_TIME, TMP1.COUNTER_VALUE
from VPX_DBM_COUNTER_VALUE TMP1,

  (select COUNTER_ID, MAX(SAMPLE_TIME) SAMPLE_TIME
   from VPX_DBM_COUNTER_VALUE
   GROUP BY COUNTER_ID) TMP2
where TMP1.COUNTER_ID=TMP2.COUNTER_ID
  and TMP1.SAMPLE_TIME=TMP2.SAMPLE_TIME

-- query:kQueryPublicOvfEnv
select PUBLIC_OVFENV
from VPX_OVFENV
where ID = ?

-- query:kQueryPrivateOvfEnv
select PRIVATE_OVFENV
from VPX_OVFENV
where ID = ?

-- query:kSelectIOFilterVMsOnComputeRes
select VPX_VIRTUAL_DISK_IOFILTERS.VM_ID, VPX_VIRTUAL_DISK_IOFILTERS.DISK_KEY
from VPX_VIRTUAL_DISK_IOFILTERS, VPX_VM, VPX_ENTITY
where VPX_VIRTUAL_DISK_IOFILTERS.VM_ID = VPX_VM.ID
  and VPX_VM.HOST_ID = VPX_ENTITY.ID
  and VPX_ENTITY.PARENT_ID = ?
  and VPX_VIRTUAL_DISK_IOFILTERS.IOFILTER = ?

-- query:kInsertClusterIOFAgency
INSERT INTO VPX_CLUSTER_IOFILTERS (CLUSTER_ID, AGENCY_REF, OP_TYPE, VIB_URL, VIB_ID, VIB_NAME, VIB_VERSION, VIB_VENDOR, VIB_SUMMARY, VIB_RELEASE_DATE, FILTER_CLASS)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

-- query:kUpdateClusterIOFAgency
UPDATE VPX_CLUSTER_IOFILTERS
SET OP_TYPE = ?, VIB_URL = ?, VIB_ID = ?, VIB_NAME = ?, VIB_VERSION = ?, VIB_VENDOR = ?,
    VIB_SUMMARY = ?, VIB_RELEASE_DATE = ?, FILTER_CLASS = ?
WHERE CLUSTER_ID = ?
  AND AGENCY_REF = ?

-- query:kUpdateClusterIOFAgencyOpType
UPDATE VPX_CLUSTER_IOFILTERS
SET OP_TYPE = ?
WHERE CLUSTER_ID = ?
  AND AGENCY_REF = ?

-- query:kDeleteClusterIOFAgency
DELETE
FROM VPX_CLUSTER_IOFILTERS
WHERE CLUSTER_ID = ?
  AND AGENCY_REF = ?

-- query:kSelectClusterIOFAgencies
SELECT CLUSTER_ID, AGENCY_REF, OP_TYPE, VIB_URL, VIB_ID, VIB_NAME, VIB_VERSION, VIB_VENDOR,
       VIB_SUMMARY, VIB_RELEASE_DATE, FILTER_CLASS
FROM VPX_CLUSTER_IOFILTERS

-- query:kCountUninstallIOFilterOps
SELECT VPX_CLUSTER_IOFILTERS.CLUSTER_ID, COUNT(VPX_CLUSTER_IOFILTERS.CLUSTER_ID)
FROM VPX_CLUSTER_IOFILTERS, VPX_COMPUTE_RESOURCE
WHERE VPX_CLUSTER_IOFILTERS.OP_TYPE = ?
  AND VPX_CLUSTER_IOFILTERS.CLUSTER_ID = VPX_COMPUTE_RESOURCE.ID
  AND VPX_COMPUTE_RESOURCE.LIFECYCLE_MANAGED = 1
  GROUP BY VPX_CLUSTER_IOFILTERS.CLUSTER_ID

-- query:kGetAllIOFilterUninstallOps
SELECT VPX_CLUSTER_IOFILTERS.CLUSTER_ID, VPX_CLUSTER_IOFILTERS.VIB_ID
FROM VPX_CLUSTER_IOFILTERS, VPX_COMPUTE_RESOURCE
WHERE VPX_CLUSTER_IOFILTERS.OP_TYPE = ?
  AND VPX_CLUSTER_IOFILTERS.CLUSTER_ID = VPX_COMPUTE_RESOURCE.ID
  AND VPX_COMPUTE_RESOURCE.LIFECYCLE_MANAGED = 1

-- query:kUpdateConnectedStatus
UPDATE VPX_NIC
SET IS_CONNECTED = ?
where ENTITY_ID = ?

-- query:kDeleteVmDevice
DELETE
FROM VPX_VM_VIRTUAL_DEVICE
WHERE ID = ?
  AND DEVICE_KEY = ?

-- query:kDeleteVmSnDevice
DELETE
FROM VPX_VM_SN_VIRTUAL_DEVICE
WHERE ID = ?
  AND DEVICE_KEY = ?

-- query:kDeleteAllVmDevices
DELETE
FROM VPX_VM_VIRTUAL_DEVICE
WHERE ID = ?

-- query:kDeleteAllVmSnDevices
DELETE
FROM VPX_VM_SN_VIRTUAL_DEVICE
WHERE ID = ?

-- query:kRetrieveOpJournals
SELECT j1.ID, j1.TYPE, je.ENTRY_ID, je.ENTRY_NAME, je.ENTRY_VALUE
FROM VPX_JOURNAL j1
LEFT JOIN VPX_JOURNAL_ENTRY je ON je.JOURNAL_ID = j1.ID
ORDER BY j1.ID, je.ENTRY_ID

-- query:kQueryKmipClusterById
SELECT CLUSTER_ID, CLUSTER_TYPE, USE_AS_DEFAULT, BACKUP_COUNT, TPM_REQUIRED
FROM VPX_CRYPTO_KMIP_CLUSTER
WHERE CLUSTER_ID = ?

-- query:kQueryKmipSeverByClusterId
SELECT CLUSTER_ID, CLUSTER_TYPE, NAME, ADDRESS, PORT, PROXY_ADDRESS, PROXY_PORT, RECONNECT, PROTOCOL, NBIO,
       TIMEOUT, USER_NAME
FROM VPX_CRYPTO_SERVER
WHERE CLUSTER_ID = ?

-- query:kQueryKmipSeverByClusterIdAndName
SELECT CLUSTER_ID, CLUSTER_TYPE, NAME, ADDRESS, PORT, PROXY_ADDRESS, PROXY_PORT, RECONNECT, PROTOCOL, NBIO,
       TIMEOUT, USER_NAME
FROM VPX_CRYPTO_SERVER
WHERE CLUSTER_ID = ?
  AND NAME = ?

-- query:kUnsetDefaultKmipCluster
UPDATE VPX_CRYPTO_KMIP_CLUSTER
SET USE_AS_DEFAULT = 0
WHERE USE_AS_DEFAULT != 0

-- query:kQueryDefaultKmipCluster
SELECT CLUSTER_ID, CLUSTER_TYPE
FROM VPX_CRYPTO_KMIP_CLUSTER
WHERE USE_AS_DEFAULT != 0

-- query:kQueryNativeKeyProviderByClusterId
SELECT kc.CLUSTER_ID, kc.CLUSTER_TYPE, kc.USE_AS_DEFAULT, kc.BACKUP_COUNT, kc.TPM_REQUIRED, kc.AUTO_REMEDIATE,
nkp.KEY_ID, nkp.ENC_KDK, nkp.KEY_ID_LAST_UPDATE, nkp.PERSIST_TOKEN
FROM VPX_CRYPTO_NATIVE_KEY_PROVIDER nkp
LEFT JOIN VPX_CRYPTO_KMIP_CLUSTER kc ON nkp.CLUSTER_ID = kc.CLUSTER_ID
WHERE nkp.CLUSTER_ID = ?

-- query:kQueryNativeKeyProviders
SELECT kc.CLUSTER_ID, kc.CLUSTER_TYPE, kc.USE_AS_DEFAULT, kc.BACKUP_COUNT, kc.TPM_REQUIRED, kc.AUTO_REMEDIATE,
nkp.KEY_ID, nkp.ENC_KDK, nkp.KEY_ID_LAST_UPDATE, nkp.PERSIST_TOKEN
FROM VPX_CRYPTO_NATIVE_KEY_PROVIDER nkp
LEFT JOIN VPX_CRYPTO_KMIP_CLUSTER kc ON nkp.CLUSTER_ID = kc.CLUSTER_ID

-- query:kUpdateNativeKeyProviderBackUp
UPDATE VPX_CRYPTO_KMIP_CLUSTER
SET BACKUP_COUNT = 1
WHERE CLUSTER_ID = ?

-- query:kInfraUpdateHaProviders
SELECT t1.PROVIDER_ID, t1.PROVIDER_UUID, t1.PROVIDER_NAME, t1.USER_NAME,
       COALESCE(t2.HU_INFO_ID, -1), COALESCE(t2.NAME, N''),
       COALESCE(t2.COMPONENT_TYPE, N''), COALESCE(t2.DESCRIPTION, N'')
FROM VPX_INFRA_UPD_HA_PROVIDER t1
LEFT JOIN VPX_INFRA_UPD_HA_HU_INFO t2 ON t1.PROVIDER_ID = t2.PROVIDER_ID
ORDER BY t1.PROVIDER_ID

-- query:kInfraUpdateHaFilters
SELECT t1.FILTER_ID, t1.PROVIDER_ID, t1.UUID, t1.NAME, t2.HU_INFO_ID
FROM VPX_INFRA_UPD_HA_FILTER t1
INNER JOIN VPX_INFRA_UPD_HA_FLTR_INF t2 ON t1.FILTER_ID = t2.FILTER_ID
ORDER BY t1.FILTER_ID

-- query:kUpdateHostScsiLun
UPDATE VPX_HOST_SCSI_LUN
SET CANONICAL_NAME = ?, DISPLAY_NAME = ?, UPDATE_DISPLAY_NAME_FLG = ?
WHERE HOST_ID = ?
  AND UUID = ?

-- Host-Scsi related. DELETE.
-- query:kDeleteSingleScsiLun
DELETE
FROM VPX_HOST_SCSI_LUN
WHERE HOST_ID = ?
  and UUID = ?

-- query:kDeleteHostScsiLuns
DELETE
FROM VPX_HOST_SCSI_LUN
WHERE HOST_ID = ?

-- Host-Vmfs-disk part related. DELETE.
-- query:kDeleteHostScsiDiskParts
DELETE
FROM VPX_HOST_SCSI_DISK_PARTITION
WHERE HOST_ID = ?

-- query:kLoadVmClobProperty
SELECT TEXT_VALUE
FROM VPX_VM_TEXT
WHERE ID = ?
  AND TYPE_ID = ?

-- query:kDeleteSnapshot
DELETE
FROM VPX_NON_ORM_VM_CONFIG_INFO
where ID = ?

-- query:kDeleteSnapshotText
DELETE
FROM VPX_VM_TEXT
where ID = ?

-- Host profile retrieval.
-- query:kHostSpecRetrieval
SELECT P.HOST_SPEC
FROM VPX_PROFILE_HOST_SPEC P
WHERE P.HOST_ID = ?
  FOR
  UPDATE

-- VmSpaceUtil disk queries.
-- query:kSharingDisksOnlyWithVm
SELECT t2.NAME, MAX(t2.VM_ID) AS VM_ID
FROM VPX_NORM_VM_FLE_FILE_INFO t1, VPX_NORM_VM_FLE_FILE_INFO t2
WHERE t1.VM_ID = ?
  AND t1.TYPE='diskDescriptor'
  AND t1.NAME = t2.NAME
  AND t1.VM_ID <> t2.VM_ID
GROUP BY t2.NAME
HAVING COUNT(t2.VM_ID)=1

-- query:kDeleteOrphanedStatsCounters
DELETE FROM VPX_STAT_COUNTER sc
WHERE NOT EXISTS (SELECT 1
                    FROM VPX_STAT_ID si
                  WHERE sc.ENTITY_ID = si.ID);

-- query:kQueryObjectsForTag
SELECT t1.ENTITY_ID
FROM VPX_ENTITY_TAG_XREF t1
INNER JOIN VPX_ENTITY t2 ON t2.ID = t1.ENTITY_ID
WHERE TAG_ID = ?
  AND TYPE_ID = ?

-- query:kQueryTagAssnsFirstPage
SELECT assn.ENTITY_ID, assn.TAG_ID, tag.TAG_UUID
FROM VPX_ENTITY_TAG_XREF assn
INNER JOIN VPX_TAG_DEF tag ON tag.ID = assn.TAG_ID
ORDER BY assn.ENTITY_ID, assn.TAG_ID
LIMIT ?

-- query:kQueryTagAssnsNextPage
SELECT assn.ENTITY_ID, assn.TAG_ID, tag.TAG_UUID
FROM VPX_ENTITY_TAG_XREF assn
INNER JOIN VPX_TAG_DEF tag ON tag.ID = assn.TAG_ID
WHERE assn.ENTITY_ID > ? OR (assn.ENTITY_ID = ? AND assn.TAG_ID > ?)
ORDER BY assn.ENTITY_ID, assn.TAG_ID
LIMIT ?

-- query:kQueryTagAssnsFirstPageForTags
SELECT assn.ENTITY_ID, assn.TAG_ID, tag.TAG_UUID
FROM VPX_ENTITY_TAG_XREF assn
INNER JOIN VPX_TAG_DEF tag ON tag.ID = assn.TAG_ID
WHERE assn.TAG_ID IN (%1)
ORDER BY assn.ENTITY_ID, assn.TAG_ID
LIMIT ?

-- query:kQueryTagAssnsNextPageForTags
SELECT assn.ENTITY_ID, assn.TAG_ID, tag.TAG_UUID
FROM VPX_ENTITY_TAG_XREF assn
INNER JOIN VPX_TAG_DEF tag ON tag.ID = assn.TAG_ID
WHERE assn.TAG_ID IN (%1)
  AND (assn.ENTITY_ID > ? OR (assn.ENTITY_ID = ? AND assn.TAG_ID > ?))
ORDER BY assn.ENTITY_ID, assn.TAG_ID
LIMIT ?

-- query:kQueryTagAssnsFirstPageForObjects
SELECT assn.ENTITY_ID, assn.TAG_ID, tag.TAG_UUID
FROM VPX_ENTITY_TAG_XREF assn
INNER JOIN VPX_TAG_DEF tag ON tag.ID = assn.TAG_ID
WHERE assn.ENTITY_ID IN (%1)
ORDER BY assn.ENTITY_ID, assn.TAG_ID
LIMIT ?

-- query:kQueryTagAssnsNextPageForObjects
SELECT assn.ENTITY_ID, assn.TAG_ID, tag.TAG_UUID
FROM VPX_ENTITY_TAG_XREF assn
INNER JOIN VPX_TAG_DEF tag ON tag.ID = assn.TAG_ID
WHERE assn.ENTITY_ID IN (%1)
  AND (assn.ENTITY_ID > ? OR (assn.ENTITY_ID = ? AND assn.TAG_ID > ?))
ORDER BY assn.ENTITY_ID, assn.TAG_ID
LIMIT ?

-- query:kQueryTagAssnsFirstPageForTagsAndObjects
SELECT assn.ENTITY_ID, assn.TAG_ID, tag.TAG_UUID
FROM VPX_ENTITY_TAG_XREF assn
INNER JOIN VPX_TAG_DEF tag ON tag.ID = assn.TAG_ID
WHERE assn.TAG_ID IN (%1)
  AND assn.ENTITY_ID IN (%2)
ORDER BY assn.ENTITY_ID, assn.TAG_ID
LIMIT ?

-- query:kQueryTagAssnsNextPageForTagsAndObjects
SELECT assn.ENTITY_ID, assn.TAG_ID, tag.TAG_UUID
FROM VPX_ENTITY_TAG_XREF assn
INNER JOIN VPX_TAG_DEF tag ON tag.ID = assn.TAG_ID
WHERE assn.TAG_ID IN (%1)
  AND assn.ENTITY_ID IN (%2)
  AND (assn.ENTITY_ID > ? OR (assn.ENTITY_ID = ? AND assn.TAG_ID > ?))
ORDER BY assn.ENTITY_ID, assn.TAG_ID
LIMIT ?

-- query:kQueryServiceProvicerEntities
SELECT a.ENTITY_ID, TYPE_ID
FROM VPX_ENTITY en join VPX_AUTH_TENANT_MGMT_RS a on a.ENTITY_ID = en.ID;

-- query:kQuerySgxEpcRangeForVMs
SELECT VM_ID, SGX_EPC_MEMORY_MB
FROM VPX_VM_SGXINFO
WHERE VM_ID > ? AND VM_ID < ?
  AND ID = VM_ID

-- query:kQueryOneSgxInfo
SELECT *
FROM VPX_VM_SGXINFO
WHERE ID = ?

-- query:kUnsetEntityDefaultKmsCluster
DELETE
FROM VPX_CRYPTO_DEFAULT_KMS_CLUSTER
WHERE ENTITY_ID = ?

-- query:kQueryEntityDefaultKmsCluster
SELECT CLUSTER_ID, CLUSTER_TYPE
FROM VPX_CRYPTO_DEFAULT_KMS_CLUSTER
WHERE ENTITY_ID = ?

-- query:kQueryDefaultKmsClusterEntities
SELECT c.ENTITY_ID, e.TYPE_ID, c.CLUSTER_ID, c.CLUSTER_TYPE
FROM VPX_CRYPTO_DEFAULT_KMS_CLUSTER c join VPX_ENTITY e on c.ENTITY_ID = e.ID
WHERE c.CLUSTER_ID = ?

-- query:kQueryUpdateTrustedClusterState
UPDATE VPX_COMPUTE_RESOURCE
SET TRUSTED_CLUSTER_STATE = ?
WHERE ID = ?

-- query:kGetTrustedClustersStateAll
SELECT ID, TRUSTED_CLUSTER_STATE
FROM VPX_COMPUTE_RESOURCE
WHERE RESOURCE_TYPE = 2
ORDER BY ID

-- query:kGetTrustedClustersState
SELECT ID, TRUSTED_CLUSTER_STATE
FROM VPX_COMPUTE_RESOURCE
WHERE ID = ?
  AND TRUSTED_CLUSTER_STATE = ?
  AND RESOURCE_TYPE = 2
ORDER BY ID

-- query:kGetTrustedClustersStateByIdOnly
SELECT ID, TRUSTED_CLUSTER_STATE
FROM VPX_COMPUTE_RESOURCE
WHERE ID = ?
  AND RESOURCE_TYPE = 2
ORDER BY ID

-- query:kGetTrustedClustersStateByStateOnly
SELECT ID, TRUSTED_CLUSTER_STATE
FROM VPX_COMPUTE_RESOURCE
WHERE TRUSTED_CLUSTER_STATE = ?
  AND RESOURCE_TYPE = 2
ORDER BY ID

-- query:kQueryKmipClusterByIdAndType
SELECT CLUSTER_ID, CLUSTER_TYPE, USE_AS_DEFAULT
FROM VPX_CRYPTO_KMIP_CLUSTER
WHERE CLUSTER_ID = ? and CLUSTER_TYPE = ?

-- query:kQueryTagsByKeyIdAndProviderId
SELECT DISTINCT t2.CRYPTO_TAG
FROM VPX_HOST_CRYPTO_TAG_KEYS t1
JOIN VPX_CRYPTO_TAG t2 on t2.CRYPTO_TAG_ID = t1.CRYPTO_TAG_ID
JOIN VPX_HOST_CRYPTO_KEYS t3 on t3.KEY_ID = t1.KEY_ID
WHERE t3.CRYPTO_KEY_ID = ?
  AND t3.CRYPTO_KEY_PROVIDER_ID = ?

-- query:kQueryTagIdKeyId
SELECT t1.CRYPTO_TAG_ID, t1.KEY_ID
FROM VPX_HOST_CRYPTO_TAG_KEYS t1
JOIN VPX_CRYPTO_TAG t2 on t2.CRYPTO_TAG_ID = t1.CRYPTO_TAG_ID
JOIN VPX_HOST_CRYPTO_KEYS t3 on t3.KEY_ID = t1.KEY_ID
WHERE t2.CRYPTO_TAG = ?
  AND t3.HOST_ID = ?
  AND t3.CRYPTO_KEY_ID = ?
  AND t3.CRYPTO_KEY_PROVIDER_ID = ?

-- query:kDeleteTagKey
DELETE
FROM VPX_HOST_CRYPTO_TAG_KEYS t1
USING VPX_CRYPTO_TAG t2, VPX_HOST_CRYPTO_KEYS t3
WHERE t2.CRYPTO_TAG_ID = t1.CRYPTO_TAG_ID
  AND t3.KEY_ID = t1.KEY_ID
  AND t2.CRYPTO_TAG = ?
  AND t3.HOST_ID = ?
  AND t3.CRYPTO_KEY_ID = ?
  AND t3.CRYPTO_KEY_PROVIDER_ID = ?

-- query:kQueryKeyIdByHostAndKey
SELECT KEY_ID
FROM VPX_HOST_CRYPTO_KEYS
WHERE HOST_ID = ? and CRYPTO_KEY_ID = ? and CRYPTO_KEY_PROVIDER_ID = ?

-- query:kQueryKeyByHostId
SELECT KEY_ID, CRYPTO_KEY_ID, CRYPTO_KEY_PROVIDER_ID, USAGE_COUNT
FROM VPX_HOST_CRYPTO_KEYS
WHERE HOST_ID = ?

-- query:kQueryTagIdByTag
SELECT CRYPTO_TAG_ID
FROM VPX_CRYPTO_TAG
WHERE CRYPTO_TAG = ?

-- query:kQueryKmipClusterByType
SELECT CLUSTER_ID, CLUSTER_TYPE, USE_AS_DEFAULT, BACKUP_COUNT, TPM_REQUIRED
FROM VPX_CRYPTO_KMIP_CLUSTER
WHERE CLUSTER_TYPE = ?

-- query:kQueryDisabledAlarmsOnEntities
SELECT ALARM_ID, ENTITY_ID
FROM VPX_ALARM_STATE
WHERE DISABLED = 1

-- query:kFindReservedPortsAndPortsOnHostByBackingType
select DVPORT_KEY
from VPX_DVPORT
where DVS_ID = ?
  and (PROXY_HOST = ?
       or (RESERVED_TIME is not NULL))
  and BACKING_TYPE = ?

-- query:kFindPortsOnHostByBackingType
select DVPORT_KEY
from VPX_DVPORT
where DVS_ID = ?
  and PROXY_HOST = ?
  and BACKING_TYPE = ?

-- query:kQueryClusterDatastoreConfig
SELECT VERSION, DATASTORE_SPECS, CAN_CHANGE_CONFIG
FROM VPX_CLUSTER_DATASTORE_CONF
WHERE CLUSTER_ID = ?

-- query:kTruncateLicenseDisconnectedHosts
TRUNCATE
VPX_LICENSE_DISCONNECTED_HOSTS

-- query:kQueryLicenseDisconnectedHosts
SELECT HOST_ENTITY_ID
FROM VPX_LICENSE_DISCONNECTED_HOSTS

-- query:kInsertLicenseDisconnectedHosts
INSERT INTO VPX_LICENSE_DISCONNECTED_HOSTS (HOST_ENTITY_ID)
VALUES (?)

-- query:kUpdateCanChangeConfig
UPDATE VPX_CLUSTER_DATASTORE_CONF
SET CAN_CHANGE_CONFIG = ?
WHERE CLUSTER_ID = ?

-- query:kGreenVCLevelBlueClustersCount
SELECT COUNT(TRUSTED_CLS_ID)
FROM
(
SELECT TRUSTED_CLS_ID FROM VPX_KMXD_SERVICES
UNION
SELECT TRUSTED_CLS_ID FROM VPX_ATTESTD_SERVICES
) AS UNION_VPX_SERVICES

-- query:kGetHaClusters
SELECT ID
FROM VPX_COMPUTE_RESOURCE
WHERE RESOURCE_TYPE = 2
      and DAS_ENABLED != 0
      and (DAS_ENABLED is not NULL)

-- query:kGetAssociatedVmids
SELECT DISTINCT VM_ID
FROM VPX_NORM_VM_FLE_FILE_INFO
WHERE NAME LIKE ?

-- query:kGetExtensionManagerFlipFlag
SELECT STATUS FROM VPX_EXT_UPGRADE LIMIT 1;

-- query:kQueryFlipBinarySpUserValues
UPDATE VPX_EXT
SET IS_ONLY_MANAGED_BY_SP_USER =
    CASE
        WHEN IS_ONLY_MANAGED_BY_SP_USER = 0 THEN 1
        WHEN IS_ONLY_MANAGED_BY_SP_USER = 1 THEN 0
        ELSE IS_ONLY_MANAGED_BY_SP_USER
    END
WHERE IS_ONLY_MANAGED_BY_SP_USER IN (1, 0);

-- query:kQueryFlipBinaryExtMgrUpgradeValues
UPDATE VPX_EXT_UPGRADE
SET STATUS =
    CASE
        WHEN STATUS = 0 THEN 1
        ELSE STATUS
    END
WHERE STATUS IN (1, 0);

-- query:kQueryExtMgrSpUserValues
SELECT EXT_ID, IS_ONLY_MANAGED_BY_SP_USER from VPX_EXT;

-- This query is used to limit the number of events we preserve per cluster to
-- a fixed number. For each cluster, we order the events (rows) by event id
-- and delete the ones that are older than the max count (argument passed to
-- the sql).
-- query:kLimitVclsDrsWFCountersPerCluster
DELETE FROM VPX_VCLS_DRS_WF_COUNTERS T1
WHERE EXISTS (SELECT 1
              FROM (SELECT EVENT_ID,
              ROW_NUMBER()
                  OVER (
                     PARTITION BY CLUSTER_ID
                     ORDER BY EVENT_ID DESC) SEQ
FROM VPX_VCLS_DRS_WF_COUNTERS
) T2 WHERE T1.EVENT_ID = T2.EVENT_ID AND SEQ > ?);

-- query:kIsAuthzCacheAvailable
SELECT count(S.VAL) = 2 as result
FROM
(SELECT 1 AS VAL WHERE EXISTS
(SELECT 1 FROM VPX_AUTHZ_GLOBAL_PERMISSIONS)
UNION ALL
SELECT 1 AS VAL WHERE EXISTS (SELECT 1 FROM VPX_AUTHZ_PRIV_ROLE)) S
;

-- query:kLoadAuthzRolesWithPrivileges
SELECT R.ROLE_ID, R.ROLE_NAME, R.ROLE_VERSION, PR.PRIV_NAME
FROM VPX_AUTHZ_ROLES R
LEFT JOIN VPX_AUTHZ_PRIV_ROLE TMP ON TMP.ROLE_ID = R.ROLE_ID
LEFT JOIN VPX_AUTHZ_PRIVILEGES PR ON TMP.PRIV_ID = PR.PRIV_ID
;

-- query:kTruncateAuthzAll
TRUNCATE
VPX_AUTHZ_PRIV_ROLE, VPX_AUTHZ_GLOBAL_PERMISSIONS, VPX_AUTHZ_ROLES, VPX_AUTHZ_PRIVILEGES
;
