Untitled

mail@pastecode.io avatar
unknown
plain_text
a month ago
6.1 kB
2
Indexable
Never
Current code:
---- workflow_detail_view
create or replace view workflow_detail_view as
select 
	wv.workflow_id,
	wv.internal_workflow_id,
	wv.engine, 
    CASE
            WHEN ((wv.benchmark_config_jsonb -> 'config'::text) -> 'workflowType'::text) IS NOT NULL THEN (wv.benchmark_config_jsonb -> 'config'::text) ->> 'workflowType'::text
            ELSE (wv.benchmark_config_jsonb -> 'config'::text) ->> 'pas_workflow_type'::text
    END AS workflow_type,
    (wv.benchmark_config_jsonb->'config'->'dbInstanceClasses')::json as dbinstanceclasses_json,
    rtrim(ltrim(wv.benchmark_config_jsonb->'config'->>'dbInstanceClasses', '["'), '"]') as dbinstanceclasses,
    wv.benchmark_config_jsonb->'config'->>'domain'  as domain,
    (wv.benchmark_config_jsonb->'config'->'dbParameterGroup')::json as dbparametergroup,
    (wv.benchmark_config_jsonb->'config'->'engineVersions')::json as engineversions_json,
	COALESCE(
		rtrim(ltrim((wv.benchmark_config_jsonb -> 'config'::text) ->> 'engineVersions'::text, '["'::text), '"]'::text),
		(
			SELECT i.engine_internal_version
		 	FROM instance_v2 i
		 	INNER JOIN child_workflow_v2 cw ON (i.child_workflow_id = cw.child_workflow_id)
		 	WHERE wv.workflow_id = cw.workflow_id 
		 	LIMIT 1
		)
	) as engineversions,
    (wv.benchmark_config_jsonb->'config'->'threads')::json as threads,
    (wv.benchmark_config_jsonb->'config'->'emails')::json as emails,
    wv.benchmark_config_jsonb->'config'->>'concurrency' as concurrency,
    wv.benchmark_config_jsonb->'config'->>'workloadGroup' as workloadgroup,
    wv.benchmark_config_jsonb->'config'->>'monitoringInterval' as monitoringInterval,
    wv.status,
    wv.submitted_time,
    wv.start_time,
    wv.end_time,
	regexp_replace(wv.user_config::json ->> 'feature'::text, '[\[\]"]'::text, ''::text, 'g'::text) AS features,
	wv.username,
	CASE
	  WHEN wv.benchmark_config_jsonb->'perf_service_configs'->>'test_type' = 'regression' THEN
		'regression'
	  ELSE
		'adhoc'
	END 
	as test_type,
	CASE
	  WHEN rtrim(ltrim(wv.benchmark_config_jsonb->'config'->>'dbInstanceClasses', '["'), '"]') LIKE '%.serverless' THEN
		 split_part(wv.benchmark_config_jsonb->'config'->'extraParams'->'dbClusterExtraParams'->'serverlessV2ScalingConfiguration'->>'maxCapacity', '.', 1)
	  ELSE
		split_part(rtrim(ltrim(wv.benchmark_config_jsonb->'config'->>'dbInstanceClasses', '["'), '"]'), '.', 3)
	END
	as instance_size,	
	(wv.benchmark_config_jsonb -> 'sidekick'::text) IS NOT NULL AS sidekick_enabled,
	"position"(wv.BENCHMARK_CONFIG::text, '"--report-interval":'::text) > 0 AS sysbench_interval_enabled,
	"position"(wv.BENCHMARK_CONFIG::text, '"--histogram": "on"'::text) > 0 AS histogram_enabled,
	((wv.benchmark_config_jsonb -> 'testconfig'::text) -> 'copy_flamegraph_to_s3'::text) IS NOT NULL AS flamegraph_enabled,
	split_part(rtrim(ltrim((wv.benchmark_config_jsonb -> 'config'::text) ->> 'dbInstanceClasses'::text, '["'::text), '"]'::text), '.', 2) AS instance_family,
	COALESCE(
--	Check cluster config for io_sku
		(
		SELECT 
			CASE 
				WHEN describe_cluster_info -> 'DBClusters' -> 0 ->> 'StorageType' = 'aurora-iopt1' THEN 'enabled'
				WHEN describe_cluster_info -> 'DBClusters' -> 0 ->> 'StorageType' <> 'aurora-iopt1' THEN 'disabled'
			--  If storage type is aurora standard (aurora-st1) then storageType won't returned by describe_cluster_info()
				WHEN (describe_cluster_info IS NOT NULL) 
					AND (describe_cluster_info -> 'DBClusters' -> 0 ->> 'StorageType' IS NULL) THEN 'disabled'
				ELSE NULL
			END
		FROM cluster_v2 cls 
		INNER JOIN child_workflow_v2 cw ON cls.child_workflow_id = cw.child_workflow_id AND cw.workflow_id = wv.workflow_id
		LIMIT 1
		),
--	Fall back to csd config.
		(
			SELECT 
				CASE
					WHEN config_json->>'volume_storage_sku' = 'iopt1' THEN 'enabled'
					WHEN config_json->>'volume_storage_sku' <> 'iopt1' THEN 'disabled'
					ELSE NULL
				END AS "io_sku"
			FROM csd_config_v2 cc
			INNER JOIN child_workflow_v2 cw ON cc.child_workflow_id = cw.child_workflow_id AND cw.workflow_id = wv.workflow_id
			LIMIT 1
		),
--  Fall back to json config		
        CASE
			WHEN ((wv.benchmark_config_jsonb -> 'config') ->> 'storageType') = 'aurora-iopt1' THEN 'enabled'
			WHEN ((wv.benchmark_config_jsonb -> 'config') ->> 'storageType') <> 'aurora-iopt1' THEN 'disabled'
--			Backward compatibility with workflows that set io_sku using bms custom scripts but did not have csd or cluster configs.
			WHEN ((wv.benchmark_config_jsonb -> 'testconfig'::text) -> 'execute_enable_io_sku'::text) IS NOT NULL THEN 'enabled'::text
			ELSE 'disabled'::text
        END
	) as io_sku,
	(wv.benchmark_config_jsonb -> 'logicalreplication'::text) IS NOT NULL AS logicalreplication_enabled,
    COALESCE(substring(wv.internal_workflow_id  FROM '(.*)-' || wv.engine ||  '-.*'), 'none') AS "prefix",
	CASE
		WHEN wv.benchmark_config_jsonb -> 'config' -> 'endpoints' ->> 0 like '%rds-internal-%-pz-%-cluster%'
			THEN substring(wv.benchmark_config_jsonb -> 'config' -> 'endpoints' ->> 0 from 'rds-internal-(.*?)-pz-')
		WHEN wv.benchmark_config_jsonb -> 'config' ->> 'pz' is not null 
			THEN wv.benchmark_config_jsonb -> 'config' ->> 'pz'
		WHEN wv.benchmark_config_jsonb -> 'config' ->> 'dbClusterNameSuffix' like 'rds-internal-%-pz-%-cluster'
			THEN substring(wv.benchmark_config_jsonb -> 'config' ->> 'dbClusterNameSuffix' from 'rds-internal-(.*?)-pz-')
		ELSE NULL
	END::text as  pz,
	wv.benchmark_config_jsonb -> 'config' ->> 'dbClusterNameSuffix' as db_cluster_name_suffix,
	wv.benchmark_config_jsonb -> 'config' ->> 'pasWorkflowType' as pas_workflow_type,
	COALESCE((wv.benchmark_config_jsonb -> 'config' ->> 'numberOfReaders')::int, 0) as "number_of_readers"
from workflow_v2 wv;


I want to add this functionality in the above view:
(CASE WHEN wdv.instance_family = 'serverless'::text THEN GREATEST(wdv.instance_size::integer / 16, 1)
      WHEN wdv.instance_size ~ '\d'::text THEN regexp_replace(wdv.instance_size, '[^\d]'::text, ''::text, 'g'::text)::integer
      ELSE 0 END) * 4 AS instance_cores
	FROM public.workflow_detail_view wdv;

and revise this code, syntax according to current code and add this to it
Leave a Comment