-- =========================================================================
-- Copyright 2019 VMware, Inc.   All rights reserved. -- VMware Confidential
-- =========================================================================

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

-- query:kQuerySchemaName
select schema_name()

-- query:kGetDBEdition
SELECT serverproperty('Edition')

-- query:kRollupJob1Status
SELECT CASE
           WHEN DATEDIFF(HH, MIN(SAMPLE_TIME),GETUTCDATE()) > 3
                AND EXISTS
                  (SELECT 1
                   FROM VPX_STAT_INTERVAL_DEF
                   WHERE ROLLUP_ENABLED_FLG = 1
                     AND INTERVAL_SEQ_NUM = 2) THEN 1
           ELSE 0
       END
FROM VPX_SAMPLE_TIME1
WHERE ROLLUP_COUNTER IS NULL;

-- query:kRollupJob2Status
SELECT CASE
           WHEN DATEDIFF(HH, MIN(SAMPLE_TIME),GETUTCDATE()) > 6
                AND EXISTS
                  (SELECT 1
                   FROM VPX_STAT_INTERVAL_DEF
                   WHERE ROLLUP_ENABLED_FLG = 1
                     AND INTERVAL_SEQ_NUM = 3) THEN 1
           ELSE 0
       END
FROM VPX_SAMPLE_TIME2
WHERE ROLLUP_COUNTER IS NULL;

-- query:kRollupJob3Status
SELECT CASE
           WHEN DATEDIFF(DAY, MIN(SAMPLE_TIME),GETUTCDATE()) > 3
                AND EXISTS
                  (SELECT 1
                   FROM VPX_STAT_INTERVAL_DEF
                   WHERE ROLLUP_ENABLED_FLG = 1
                     AND INTERVAL_SEQ_NUM = 4) THEN 1
           ELSE 0
       END
FROM VPX_SAMPLE_TIME3
WHERE ROLLUP_COUNTER IS NULL;

-- Query to check free space in database machine disks.
-- Version of query used for SQL server 2008R2 and above.

-- query:kSpaceStatus
SELECT File_Type, Drive, VCDB_Space_Mb, Physical_Name, Sql_Server_Edition, Sql_Server_Version,
       CASE
           WHEN CHARINDEX('Express Edition',CAST (Sql_Server_Edition as varchar)) >0 AND File_Type = 'DATA' THEN
                  (SELECT MIN(n)
                   FROM (
                         VALUES (10240 - VCDB_Space_Mb), (FreeSpaceInMB), (DB_limit - VCDB_Space_Mb)) AS i(n))
           ELSE
                  (SELECT MIN(n)
                   FROM (
                         VALUES (FreeSpaceInMB), (DB_limit - VCDB_Space_Mb)) AS i(n))
       END AS FreeSpaceInMB,
       CASE
           WHEN CHARINDEX('Express Edition',CAST (Sql_Server_Edition as varchar)) >0 AND File_Type = 'DATA' THEN
                  (SELECT MIN(n)
                   FROM (
                         VALUES (10240), (TotalSpaceInMB), (DB_limit)) AS i(n))
           ELSE
                  (SELECT MIN(n)
                   FROM (
                         VALUES (TotalSpaceInMB), (DB_limit)) AS i(n))
       END AS TotalSpaceInMB
FROM
  (SELECT DB_NAME(os.database_id) DBName, CASE mf.FILE_ID
                                              WHEN 1 THEN 'DATA'
                                              WHEN 2 THEN 'LOG'
                                          END File_Type,
          os.volume_mount_point AS Drive, CONVERT(BIGINT, (mf.SIZE * 8) / 1024.0) AS VCDB_Space_Mb,
          mf.PHYSICAL_NAME Physical_Name, SERVERPROPERTY('edition') Sql_Server_Edition,
          SERVERPROPERTY('productversion') Sql_Server_Version, CONVERT(BIGINT,((os.available_bytes)/1024.0)/1024.0) AS FreeSpaceInMB,
          CONVERT(BIGINT,((os.total_bytes)/1024.0)/1024.0) AS TotalSpaceInMB,
          CASE
              WHEN mf.growth = 0 THEN CONVERT(BIGINT, (mf.SIZE * 8) / 1024.0)
              ELSE CASE
                       WHEN mf.max_SIZE not in (0,-1, 268435456) THEN CONVERT(BIGINT, (mf.max_SIZE * 8) / 1024.0)
                       ELSE CONVERT(BIGINT,((os.total_bytes)/1024.0)/1024.0)
                   END
          END AS DB_limit
   FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) os
   where os.DATABASE_ID = DB_ID() ) as realdata

-- query:kDeleteOrphanedDevices
DELETE D
FROM VPX_DEVICE D
WHERE NOT EXISTS
    (SELECT 0
     FROM VPX_STAT_COUNTER SC
     WHERE SC.DEVICE_ID = D.DEVICE_ID)
  AND DEVICE_NAME <> '';

-- query:kDeleteOrphanedStatsID
DELETE
FROM VPX_STAT_ID
WHERE CAST(substring(MOID, PatIndex('%[0-9]%', MOID), len(MOID)) as BIGINT) NOT IN
    (SELECT ID FROM VPX_ENTITY);

-- query:kTruncateStatsPartition
truncate table VPX_HIST_STAT1_%d

-- query:kUpdateOptimizerForStatsPartition
update statistics VPX_HIST_STAT1_%d with fullscan

-- query:kDeleteStatsId
DELETE
FROM VPX_STAT_ID WITH (ROWLOCK)
WHERE ID=?

-- query:kFindSchemaName
select schema_name()

-- query:kDeleteStatsCounters
DELETE
FROM VPX_STAT_COUNTER WITH (ROWLOCK)
WHERE ENTITY_ID=?

-- query:kDeleteFieldVals
DELETE
FROM VPX_FIELD_VAL WITH (ROWLOCK)
WHERE MO_ID=?

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

-- query:kRollupStats1
l_stats_rollup1_proc

-- query:kRollupStats2
l_stats_rollup2_proc

-- query:kRollupStats3
l_stats_rollup3_proc

-- query:kPurgeStats2
l_purge_stat2_proc

-- query:kPurgeStats3
l_purge_stat3_proc

-- query:kRule_topn1_proc
rule_topn1_proc

-- query:kRule_topn2_proc
rule_topn2_proc

-- query:kRule_topn3_proc
rule_topn3_proc

-- query:kRule_topn4_proc
rule_topn4_proc

-- query:kVcResourcePurgeStats
purge_usage_stat_proc

-- query:kQueryProviderId
SELECT ID
FROM VPX_STAT_ID WITH (NOLOCK)
WHERE MOID = ?

-- query:kUpdateRollupSetting
UPDATE VPX_STAT_INTERVAL_DEF WITH (ROWLOCK)
SET INTERVAL_DEF_NAME = ?, INTERVAL_VAL = ?, INTERVAL_LENGTH = ?, STATS_LEVEL = ?, ROLLUP_ENABLED_FLG = ?
WHERE INTERVAL_SEQ_NUM = ?

-- query:kQueryStatsStatIdDeviceId1
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT1 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND D.DEVICE_NAME = ?
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
  AND ST.COUNTER_ID = SC.COUNTER_ID
  AND ST.TIME_ID = SM.TIME_ID
ORDER BY SM.SAMPLE_TIME

-- query:kQueryStatsStatIdDeviceId1Part
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), (%1) ST
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND D.DEVICE_NAME = ?
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
  AND ST.COUNTER_ID = SC.COUNTER_ID
  AND ST.TIME_ID = SM.TIME_ID
ORDER BY SM.SAMPLE_TIME

-- query:kQueryStatsStatIdDeviceId2
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME2 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT2 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND D.DEVICE_NAME = ?
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
  AND ST.COUNTER_ID = SC.COUNTER_ID
  AND ST.TIME_ID = SM.TIME_ID
ORDER BY SM.SAMPLE_TIME

-- query:kQueryStatsStatIdDeviceId3
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME3 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT3 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND D.DEVICE_NAME = ?
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
  AND ST.COUNTER_ID = SC.COUNTER_ID
  AND ST.TIME_ID = SM.TIME_ID
ORDER BY SM.SAMPLE_TIME

-- query:kQueryStatsStatIdDeviceId4
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME4 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT4 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND D.DEVICE_NAME = ?
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
  AND ST.COUNTER_ID = SC.COUNTER_ID
  AND ST.TIME_ID = SM.TIME_ID
ORDER BY SM.SAMPLE_TIME

-- query:kQueryStatsStatId1
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT1 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SM.TIME_ID = ST.TIME_ID
ORDER BY D.DEVICE_NAME, SM.SAMPLE_TIME

-- query:kQueryStatsStatId1Part
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), (%1) ST
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SM.TIME_ID = ST.TIME_ID
ORDER BY D.DEVICE_NAME, SM.SAMPLE_TIME

-- query:kQueryStatsStatId2
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME2 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT2 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SM.TIME_ID = ST.TIME_ID
ORDER BY D.DEVICE_NAME, SM.SAMPLE_TIME

-- query:kQueryStatsStatId3
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME3 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT3 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SM.TIME_ID = ST.TIME_ID
ORDER BY D.DEVICE_NAME, SM.SAMPLE_TIME

-- query:kQueryStatsStatId4
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME4 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT4 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SM.TIME_ID = ST.TIME_ID
ORDER BY D.DEVICE_NAME, SM.SAMPLE_TIME

-- query:kQueryStats1
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT1 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
ORDER BY SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME

-- query:kQueryStats1Part
ELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), (%1) ST
WHERE SC.ENTITY_ID = ?
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
ORDER BY SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME

-- query:kQueryStats2
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME2 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT2 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
ORDER BY SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME

-- query:kQueryStats3
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME3 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT3 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
ORDER BY SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME

-- query:kQueryStats4
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME4 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT4 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
ORDER BY SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME

-- query:kQueryStatsStatIdNullDevice
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:kQueryStatsStatIdNullDevice1
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT1 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND D.DEVICE_NAME = ''
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
ORDER BY SM.SAMPLE_TIME

-- query:kQueryStatsStatIdNullDevice1Part
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), (%1) ST
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND D.DEVICE_NAME = ''
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
ORDER BY SM.SAMPLE_TIME

-- query:kQueryStatsStatIdNullDevice1Condition
SELECT sc.stat_id, d.device_name, sm.sample_time, sm.sample_interval, st.stat_val
FROM vpx_stat_counter sc, vpx_sample_time1 sm, vpx_device d, (%1) st
WHERE sc.counter_id = st.counter_id
  AND sc.device_id = d.device_id
  AND st.time_id = sm.time_id
ORDER BY sm.sample_time

-- query:kQueryStatsStatIdNullDevice2
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME2 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT2 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND D.DEVICE_NAME = ''
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
ORDER BY SM.SAMPLE_TIME

-- query:kQueryStatsStatIdNullDevice3
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME3 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT3 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND D.DEVICE_NAME = ''
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
ORDER BY SM.SAMPLE_TIME

-- query:kQueryStatsStatIdNullDevice4
SELECT SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME4 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT4 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.STAT_ID = ?
  AND D.DEVICE_NAME = ''
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
ORDER BY SM.SAMPLE_TIME

-- query:kQuerySampleList1
SELECT SM.SAMPLE_TIME, SM.SAMPLE_INTERVAL, SM.PARTITION_INDEX
FROM VPX_SAMPLE_TIME1 SM WITH (NOLOCK)
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 WITH (NOLOCK)
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 WITH (NOLOCK)
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 WITH (NOLOCK)
WHERE SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
ORDER BY SM.SAMPLE_TIME

-- query:kSelectHistStat1Part
SELECT *
FROM VPX_HIST_STAT1_%1 WITH (NOLOCK)

-- query:kSelectHistStat1All
SELECT *
FROM VPX_HIST_STAT1 WITH (NOLOCK)

-- query:kQueryStatsEx1
SELECT SC.ENTITY_ID, SC.STAT_ID, D.DEVICE_NAME, SM.SAMPLE_TIME, ST.STAT_VAL
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), (%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 WITH (NOLOCK), VPX_SAMPLE_TIME2 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT2 ST WITH (NOLOCK)
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 WITH (NOLOCK), VPX_SAMPLE_TIME3 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT3 ST WITH (NOLOCK)
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 WITH (NOLOCK), VPX_SAMPLE_TIME4 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT4 ST WITH (NOLOCK)
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 WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), (%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 WITH (NOLOCK), VPX_SAMPLE_TIME2 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT2 ST WITH (NOLOCK)
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 WITH (NOLOCK), VPX_SAMPLE_TIME3 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT3 ST WITH (NOLOCK)
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 WITH (NOLOCK), VPX_SAMPLE_TIME4 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT4 ST WITH (NOLOCK)
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 WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), (%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 WITH (NOLOCK), VPX_SAMPLE_TIME2 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT2 ST WITH (NOLOCK)
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 WITH (NOLOCK), VPX_SAMPLE_TIME3 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT3 ST WITH (NOLOCK)
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 WITH (NOLOCK), VPX_SAMPLE_TIME4 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT4 ST WITH (NOLOCK)
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 WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), (%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 WITH (NOLOCK), VPX_SAMPLE_TIME2 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT2 ST WITH (NOLOCK)
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 WITH (NOLOCK), VPX_SAMPLE_TIME3 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT3 ST WITH (NOLOCK)
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 WITH (NOLOCK), VPX_SAMPLE_TIME4 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT4 ST WITH (NOLOCK)
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:kQueryAvailableMetric1
SELECT SC.STAT_ID, D.DEVICE_NAME
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME1 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT1 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
GROUP BY SC.STAT_ID, D.DEVICE_NAME
ORDER BY SC.STAT_ID, D.DEVICE_NAME

-- query:kQueryAvailableMetric2
SELECT SC.STAT_ID, D.DEVICE_NAME
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME2 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT2 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
GROUP BY SC.STAT_ID, D.DEVICE_NAME
ORDER BY SC.STAT_ID, D.DEVICE_NAME

-- query:kQueryAvailableMetric3
SELECT SC.STAT_ID, D.DEVICE_NAME
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME3 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT3 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
GROUP BY SC.STAT_ID, D.DEVICE_NAME
ORDER BY SC.STAT_ID, D.DEVICE_NAME

-- query:kQueryAvailableMetric4
SELECT SC.STAT_ID, D.DEVICE_NAME
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_SAMPLE_TIME4 SM WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK), VPX_HIST_STAT4 ST WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
  AND SC.COUNTER_ID = ST.COUNTER_ID
  AND ST.TIME_ID = SM.TIME_ID
  AND SM.SAMPLE_TIME > ?
  AND SM.SAMPLE_TIME <= ?
GROUP BY SC.STAT_ID, D.DEVICE_NAME
ORDER BY SC.STAT_ID, D.DEVICE_NAME

-- query:kQueryAllAvailableMetric
SELECT SC.STAT_ID, D.DEVICE_NAME
FROM VPX_STAT_COUNTER SC WITH (NOLOCK), VPX_DEVICE D WITH (NOLOCK)
WHERE SC.ENTITY_ID = ?
  AND SC.DEVICE_ID = D.DEVICE_ID
GROUP BY SC.STAT_ID, D.DEVICE_NAME
ORDER BY SC.STAT_ID, D.DEVICE_NAME

-- query:kDeleteNetworkAssignment
DELETE
FROM VPX_NW_ASSIGNMENT WITH (ROWLOCK)
WHERE NETWORK_ID = ?
  and ENTITY_ID = ?

-- query:kDeleteDatastoreAssignment
DELETE
FROM VPX_DS_ASSIGNMENT WITH (ROWLOCK)
WHERE DS_ID = ?
  AND ENTITY_ID = ?

-- query:kDeleteComputeResourceDasVm
DELETE
FROM VPX_COMPUTE_RESOURCE_DAS_VM WITH (ROWLOCK)
WHERE COMP_RES_ID = ?

-- query:kDeleteComputeResourceOrcVm
DELETE
FROM VPX_COMPUTE_RESOURCE_ORC_VM WITH (ROWLOCK)
WHERE COMP_RES_ID = ?

-- query:kDeleteComputeResourceDasHbDsSys
DELETE
FROM VPX_DAS_HB_DATASTORE_SYS WITH (ROWLOCK)
WHERE COMP_RES_ID = ?

-- query:kDeleteComputeResourceDasUserHbDs
DELETE
FROM VPX_COMPUTE_RES_USER_HB_DS WITH (ROWLOCK)
WHERE COMP_RES_ID = ?

-- query:kDeleteDasHbDsSysForDs
DELETE
FROM VPX_DAS_HB_DATASTORE_SYS WITH (ROWLOCK)
WHERE DATASTORE_ID = ?

-- query:kDeleteComputeResourceDrsVm
DELETE
FROM VPX_COMPUTE_RESOURCE_DRS_VM WITH (ROWLOCK)
WHERE COMP_RES_ID = ?

-- query:kDeleteComputeResourceDpmHost
DELETE
FROM VPX_COMPUTE_RESOURCE_DPM_HOST WITH (ROWLOCK)
WHERE COMP_RES_ID = ?

-- Force file_name lookup to be case-sensitive on MSSQL server.
-- XXX: Remove when we configure MSSQL server to use case-insensitive collation
-- query:kLookupVmByUrl
SELECT ID
FROM VPX_VM
WHERE FILE_NAME COLLATE Latin1_General_CS_AS = ?

-- Force file_name lookup to be case-sensitive on MSSQL server.
-- XXX: Remove when we configure MSSQL server to use case-insensitive collation
-- query:kLookupVmByUrlAndDatacenter
SELECT ID
FROM VPX_VM
WHERE FILE_NAME COLLATE Latin1_General_CS_AS = ?
  AND DATACENTER_ID = ?

-- query:kDeleteVmDsSpaceValuesForDatastore
DELETE
FROM VPX_VM_DS_SPACE WITH (ROWLOCK)
WHERE DS_ID = ?

-- query:kUpdateScsiLunDisplayName
update VPX_HOST_SCSI_LUN WITH (ROWLOCK)
SET DISPLAY_NAME = ?
WHERE HOST_ID = ?
  AND UUID = ?

-- Extension type id and privilege related sql
-- query:kDeleteExtTypeIds
DELETE
FROM VPX_EXT_TYPE_IDS WITH (ROWLOCK)
WHERE EXT_ID = ?

-- query:kDeleteExtPrivs
DELETE
FROM VPX_EXT_PRIVS WITH (ROWLOCK)
WHERE EXT_ID = ?

-- query:kDeleteExtSolutionTabs
DELETE
FROM VPX_EXT_TABS WITH (ROWLOCK)
WHERE EXT_ID = ?

-- query:kDeleteExtManagedEntities
DELETE
FROM VPX_EXT_MANAGEDBY_INFO WITH (ROWLOCK)
WHERE EXT_ID = ?

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

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

-- query:kGetDatastoresInVmChain
WITH FILEBACKING(BACKING_ID, DATASTORE_ID, FILE_BACKING_PARENT_ID) AS
  (SELECT BACKING_ID, DATASTORE_ID, FILE_BACKING_PARENT_ID
   FROM VPX_VDEVICE_FILE_BACKING
   WHERE BACKING_ID = ?
     UNION ALL
     SELECT I.BACKING_ID, I.DATASTORE_ID, I.FILE_BACKING_PARENT_ID
     FROM VPX_VDEVICE_FILE_BACKING AS I, FILEBACKING AS O WHERE O.FILE_BACKING_PARENT_ID = I.BACKING_ID )
SELECT DISTINCT DATASTORE_ID
FROM FILEBACKING

-- query:kGetDatastoresInSnChain
WITH SNFILEBACKING(BACKING_ID, DATASTORE_ID, FILE_BACKING_PARENT_ID) AS
  (SELECT BACKING_ID, DATASTORE_ID, FILE_BACKING_PARENT_ID
   FROM VPX_SN_VDEVICE_FILE_BACKING
   WHERE BACKING_ID = ?
     UNION ALL
     SELECT I.BACKING_ID, I.DATASTORE_ID, I.FILE_BACKING_PARENT_ID
     FROM VPX_SN_VDEVICE_FILE_BACKING AS I, SNFILEBACKING AS O WHERE O.FILE_BACKING_PARENT_ID = I.BACKING_ID )
SELECT DISTINCT DATASTORE_ID
FROM SNFILEBACKING

-- query:kGetUrlAndContentIdForAllVmParentBackings
WITH FILEBACKING(BACKING_ID, BACKING_CONTENT_ID, BACKING_FILENAME, FILE_BACKING_PARENT_ID) AS
  (SELECT BACKING_ID, FILE_BACKING_CONTENT_ID, HARD_DEVICE_BACKING_FILE_NAME,
          FILE_BACKING_PARENT_ID
   FROM VPX_VDEVICE_FILE_BACKING
   WHERE BACKING_ID = ?
     UNION ALL
     SELECT I.BACKING_ID, I.FILE_BACKING_CONTENT_ID, I.HARD_DEVICE_BACKING_FILE_NAME,
            I.FILE_BACKING_PARENT_ID
     FROM VPX_VDEVICE_FILE_BACKING AS I, FILEBACKING AS O WHERE O.FILE_BACKING_PARENT_ID = I.BACKING_ID )
SELECT BACKING_FILENAME, BACKING_CONTENT_ID
FROM FILEBACKING

-- query:kGetUrlAndContentIdForAllSnParentBackings
WITH SNFILEBACKING(BACKING_ID, BACKING_CONTENT_ID, BACKING_FILENAME, FILE_BACKING_PARENT_ID) AS
  (SELECT BACKING_ID, FILE_BACKING_CONTENT_ID, HARD_DEVICE_BACKING_FILE_NAME,
          FILE_BACKING_PARENT_ID
   FROM VPX_SN_VDEVICE_FILE_BACKING
   WHERE BACKING_ID = ?
     UNION ALL
     SELECT I.BACKING_ID, I.FILE_BACKING_CONTENT_ID, I.HARD_DEVICE_BACKING_FILE_NAME,
            I.FILE_BACKING_PARENT_ID
     FROM VPX_SN_VDEVICE_FILE_BACKING AS I, SNFILEBACKING AS O WHERE O.FILE_BACKING_PARENT_ID = I.BACKING_ID )
SELECT BACKING_FILENAME, BACKING_CONTENT_ID
FROM SNFILEBACKING

-- end linked clone query
-- query:kDeleteVmDsSpaceValues
DELETE
FROM VPX_VM_DS_SPACE WITH (ROWLOCK)
WHERE VM_ID = ?

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

-- query:kUpdateVmSpaceSummary
UPDATE VPX_VM WITH (ROWLOCK)
SET AGGR_COMMITED_STORAGE_SPACE = ?, AGGR_UNCOMMITED_STORAGE_SPACE = ?, AGGR_UNSHARED_STORAGE_SPACE = ?,
    STORAGE_SPACE_UPDATED_TIME = ?
WHERE ID = ?

-- query:kEightHostLimitQuery
select HOST_ID
FROM VPX_VM WITH (NOLOCK), VPX_VDEVICE_FILE_BACKING WITH (NOLOCK)
WHERE VPX_VM.ID = VPX_VDEVICE_FILE_BACKING.VM_ID
  AND VPX_VDEVICE_FILE_BACKING.HARD_DEVICE_BACKING_FILE_NAME = ?

-- query:kUpdateDatastoreAssignment
UPDATE VPX_DS_ASSIGNMENT WITH (ROWLOCK)
SET DS_ID = ?
WHERE ENTITY_ID = ?
  AND DS_ID = ?

-- query:kUpdateDatastoreForVmInVmDsSpace
UPDATE VPX_VM_DS_SPACE WITH (ROWLOCK)
SET DS_ID = ?
WHERE VM_ID = ?
  AND DS_ID = ?

-- query:kUpsertLastEvent
{ call upsert_last_event_proc(?, ?, ?) }

-- query:kDoEventTaskCleanup
cleanup_events_tasks_proc

-- query:kLoadEventsQueryLimit
SELECT TOP %%2%% EVENT_ID, CHAIN_ID, EVENT_TYPE, EXTENDED_CLASS, CREATE_TIME, USERNAME, CATEGORY, VM_ID,
       VM_NAME, HOST_ID, HOST_NAME, COMPUTERESOURCE_ID, COMPUTERESOURCE_TYPE,
       COMPUTERESOURCE_NAME, DATACENTER_ID, DATACENTER_NAME, DATASTORE_ID, DATASTORE_NAME, NETWORK_ID,
       NETWORK_NAME, NETWORK_TYPE, DVS_ID, DVS_NAME, STORAGEPOD_ID, STORAGEPOD_NAME, CHANGE_TAG_ID
FROM %1 %%1%%;

-- 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 WITH (NOLOCK)

-- query:kLoadTasksQueryLimit
SELECT TOP %2 TASK_ID, NAME, DESCRIPTIONID, ENTITY_ID, ENTITY_TYPE, ENTITY_NAME, LOCKED_DATA,
       COMPLETE_STATE, CANCELLED, CANCELLABLE, ERROR_DATA, RESULT_DATA, REASON_DATA, QUEUE_TIME,
       START_TIME, COMPLETE_TIME, EVENT_CHAIN_ID, USERNAME, VM_ID, HOST_ID, COMPUTERESOURCE_ID,
       DATACENTER_ID, RESOURCEPOOL_ID, FOLDER_ID, ALARM_ID, SCHEDULEDTASK_ID, CHANGE_TAG_ID,
       PARENT_TASK_ID, ROOT_TASK_ID, DESCRIPTION, ACTIVATION_ID
FROM VPX_TASK WITH (NOLOCK) %1;

-- find any one free port in the portgroup (exclude uplink ports)
-- query:kFindSingleFreePortgroupPort
select TOP 1 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.DVPORTGROUP_ID = ?
  and t1.HOST_ID = 0
  and t2.CONNECTEE is NULL

-- query:kFindDvsConnectionOnHost
SELECT T1.DVS_ID, T1.DVPORT_KEY, T1.CONNECTEE_TYPE, T1.CONNECTEE_NIC_KEY,
       T1.CONNECTION_COOKIE, T1.PROXY_HOST, T2.DVPORTGROUP_ID
FROM VPX_DVPORT T1 WITH (NOLOCK), VPX_DVPORT_MEMBERSHIP T2 WITH (NOLOCK)
WHERE T1.DVS_ID = T2.DVS_ID
  AND T1.DVPORT_KEY = T2.DVPORT_KEY
  AND T1.CONNECTEE = ?
  AND T1.RESERVED_TIME IS NULL

-- remove port reservation at start up
-- query:kResetAllPortReservation
update VPX_DVPORT WITH (ROWLOCK)
set CONNECTEE = null, CONNECTEE_TYPE = null, CONNECTEE_NIC_KEY = null, CONNECTEE_ADDRESS = null,
    RESERVED_TIME = null
where RESERVED_TIME is not null

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

-- Update the stat level in vpx_stat_def
-- query:kUpdateStatLevel
UPDATE VPX_STAT_DEF WITH (ROWLOCK)
SET STAT_LEVEL = ?
WHERE ID = ?

-- Host-Scsi related. UPDATE.
-- query:kUpdateHostScsiLun
UPDATE VPX_HOST_SCSI_LUN WITH (ROWLOCK)
SET CANONICAL_NAME = ?, DISPLAY_NAME = ?, UPDATE_DISPLAY_NAME_FLG = ?
WHERE HOST_ID = ?
  AND UUID = ?

-- VM-CLOB read.
-- query:kLoadVmClobProperty
SELECT TEXT_VALUE
FROM VPX_VM_TEXT WITH (NOLOCK)
WHERE ID = ?
  AND TYPE_ID = ?

-- query:kHostSpecRetrieval
SELECT P.HOST_SPEC
FROM VPX_PROFILE_HOST_SPEC P WITH (UPDLOCK, INDEX(PK_VPX_PROF_HOSTSPEC))
WHERE P.HOST_ID = ?

-- query:kSharingDisksOnlyWithVm
SELECT t2.NAME, MAX(t2.VM_ID) AS VM_ID
FROM VPX_NORM_VM_FLE_FILE_INFO t1 WITH(NOLOCK), VPX_NORM_VM_FLE_FILE_INFO t2 WITH(NOLOCK)
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:kGetDBVersion
SELECT SUBSTRING(@@VERSION, 1, CHARINDEX(' - ', @@VERSION)-1)

-- query:kSelectHistStat1Conditioned
SELECT i_st.counter_id, i_st.time_id, i_st.stat_val
FROM VPX_HIST_STAT1_%1 i_st WITH (NOLOCK) JOIN VPX_STAT_COUNTER i_sc on i_st.counter_id = i_sc.counter_id LEFT 
JOIN (SELECT time_id
    FROM vpx_sample_time1
    WHERE sample_time >= %2
       AND sample_time <= %3) t ON t.time_id = i_st.time_id

-- query:kSelectDeviceID
SELECT device_id
from VPX_DEVICE
WHERE DEVICE_NAME = %1;
