PROCEDURE "hdi::SP_DELETE-SERVICE"(IN "ORGANIZATION" VARCHAR(1024),IN "SPACE" VARCHAR(1024), IN "SERVICE" VARCHAR(1024))
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN
DECLARE "OUTPUT" TABLE (RESULT NVARCHAR(1073741823));
CALL "Z1A00_Security.hdi_api::runXSCommand"(:ORGANIZATION, :SPACE, 'xs delete-service ''' || :SERVICE || ''' -f', OUTPUT);
END
PROCEDURE "hdi::SP_GET_SCHEMA"(IN "ORGANIZATION" VARCHAR(1024),IN "SPACE" VARCHAR(1024), IN "SERVICE" VARCHAR(1024), OUT SCHEMA_NAME NVARCHAR(256))
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN
DECLARE "OUTPUT" TABLE (RESULT NVARCHAR(1073741823));
CALL "Z1A00_Security.hdi_api::runXSCommand"(:ORGANIZATION, :SPACE, 'xs sk ''' || :SERVICE || ''' SharedDevKey | sed ''1,3d'' | head -n -3', OUTPUT);
SELECT JSON_VALUE("RESULT", '$.schema') AS "SCHEMA_NAME" INTO SCHEMA_NAME FROM :OUTPUT;
END
PROCEDURE "hdi::SP_LIST_DEPLOYED"(
IN HDI_SCHEMA_NAME NVARCHAR(256),
IN PATHS "hdi::TT_FILESFOLDERS",
IN PARAMETERS "hdi::TT_PARAMETERS",
OUT RETURN_CODE INT,
OUT REQUEST_ID BIGINT,
OUT MESSAGES "hdi::TT_MESSAGES",
OUT RESULT "hdi::TT_FILESFOLDERS_METADATA"
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS BEGIN
EXEC 'CALL ' || :HDI_SCHEMA_NAME || '#DI.LIST_DEPLOYED( :PATHS, :PARAMETERS, :RETURN_CODE, :REQUEST_ID, :MESSAGES, :RESULT)' INTO RETURN_CODE, REQUEST_ID, MESSAGES, RESULT USING :PATHS, :PARAMETERS;
END;
"PARAMETERS" LIKE '%"makeUniqueName":true%'
PROCEDURE "hdi::SP_DEV_WORKSPACES_CLEAN" (IN "ORGANIZATION" VARCHAR(1024),
IN "SPACE" VARCHAR(1024),
IN RETENTION_DAYS INT,
IN TESTMODE BOOLEAN,
IN PERSIST_RESULT BOOLEAN,
OUT DEV_WORKSPACES TABLE(
"NAME" NVARCHAR(255),
"SCHEMA_NAME" NVARCHAR(256),
"CREATE_TIMESTAMP_UTC" LONGDATE,
"MODIFICATION_TIMESTAMP_UTC" LONGDATE,
"TO_BE_DELETED" BOOLEAN
))
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
AS
BEGIN
-- step 1
DECLARE CURSOR C_STOREDSERVICEINSTANCES FOR
SELECT A."GUID", A."NAME", ADD_SECONDS('1970-01-01',A."CREATEDAT"/1000) AS "CREATE_TIMESTAMP_UTC"
FROM "SYS_XS_RUNTIME.STOREDSERVICEINSTANCE"() A
INNER JOIN "SYS_XS_RUNTIME.STOREDSPACE"() B
ON A.SPACEGUID = B.GUID
INNER JOIN "SYS_XS_RUNTIME.STOREDORGANIZATION"() C
ON B.ORGANIZATIONGUID = C.GUID
WHERE DAYS_BETWEEN(ADD_SECONDS('1970-01-01',A."CREATEDAT"/1000),CURRENT_DATE) > :RETENTION_DAYS
AND A."PARAMETERS" LIKE '%"makeUniqueName":true%'
AND C.NAME=:ORGANIZATION
AND B.NAME=:SPACE;
DECLARE SCHEMA_NAME NVARCHAR(256);
DECLARE MESSAGES "hdi::TT_MESSAGES";
DECLARE RETURN_CODE INT;
DECLARE REQUEST_ID BIGINT;
DECLARE RESULT "hdi::TT_FILESFOLDERS_METADATA";
DECLARE MODIFICATION_TIMESTAMP_UTC LONGDATE;
CREATE LOCAL TEMPORARY COLUMN TABLE #PARAMETERS LIKE "hdi::TT_PARAMETERS";
INSERT INTO #PARAMETERS ( KEY, VALUE ) VALUES ('recursive', 'true');
VAR_T_NO_FILESFOLDERS = select * from "hdi::T_NO_FILESFOLDERS";
VAR_PARAMETERS = select * from #PARAMETERS;
-- step 2
FOR SERVICEINSTANCE AS C_STOREDSERVICEINSTANCES
DO
CALL "hdi::SP_GET_SCHEMA"(
ORGANIZATION => :ORGANIZATION,
SPACE => :SPACE,
SERVICE => :SERVICEINSTANCE."NAME",
SCHEMA_NAME => SCHEMA_NAME );
IF :SCHEMA_NAME IS NOT NULL THEN
CALL "hdi::SP_LIST_DEPLOYED"(
HDI_SCHEMA_NAME => :SCHEMA_NAME,
PATHS => :VAR_T_NO_FILESFOLDERS,
PARAMETERS => :VAR_PARAMETERS,
RETURN_CODE => RETURN_CODE,
REQUEST_ID => REQUEST_ID,
MESSAGES => MESSAGES,
RESULT => RESULT );
SELECT MAX("MODIFICATION_TIMESTAMP_UTC") INTO MODIFICATION_TIMESTAMP_UTC FROM :RESULT;
ELSE
MODIFICATION_TIMESTAMP_UTC = NULL;
END IF;
DEV_WORKSPACES = SELECT * FROM :DEV_WORKSPACES UNION ALL
SELECT :SERVICEINSTANCE.NAME,
:SCHEMA_NAME,
:SERVICEINSTANCE.CREATE_TIMESTAMP_UTC,
:MODIFICATION_TIMESTAMP_UTC,
CASE
WHEN DAYS_BETWEEN(IFNULL(:MODIFICATION_TIMESTAMP_UTC,:SERVICEINSTANCE.CREATE_TIMESTAMP_UTC),CURRENT_DATE) > :RETENTION_DAYS
THEN True
ELSE False
END
FROM "SYS.DUMMY";
END FOR;
IF :PERSIST_RESULT = true THEN
DELETE FROM "hdi::T_DEV_WORKSPACES";
INSERT INTO "hdi::T_DEV_WORKSPACES" SELECT * FROM :DEV_WORKSPACES;
END IF;
-- step 3
IF :TESTMODE = false THEN
BEGIN
DECLARE CURSOR DEV_WORKSPACES_TO_BE_DELETED FOR
SELECT *
FROM :DEV_WORKSPACES
WHERE TO_BE_DELETED = true;
FOR DEV_WORKSPACE AS DEV_WORKSPACES_TO_BE_DELETED
DO
CALL "hdi::SP_DELETE-SERVICE"(
ORGANIZATION => :ORGANIZATION,
SPACE => :SPACE,
SERVICE => :DEV_WORKSPACE."NAME" );
END FOR;
END;
END IF;
DROP TABLE #PARAMETERS;
END;
COLUMN TABLE "hdi::T_DEV_WORKSPACES" ("NAME" NVARCHAR(255),
"SCHEMA_NAME" NVARCHAR(256),
"CREATE_TIMESTAMP_UTC" LONGDATE CS_LONGDATE,
"MODIFICATION_TIMESTAMP_UTC" LONGDATE CS_LONGDATE,
"TO_BE_DELETED" BOOLEAN
) UNLOAD PRIORITY 0 AUTO MERGE
SELECT NB_J AS RETENTION_DAYS, MAX(ROWNUM) FROM
(SELECT NB_J, ROW_NUMBER() OVER(ORDER BY NB_J DESC) AS ROWNUM FROM
(SELECT
DAYS_BETWEEN(IFNULL("MODIFICATION_TIMESTAMP_UTC","CREATE_TIMESTAMP_UTC"),CURRENT_DATE) AS NB_J
FROM "Z1A00_PLATFORM_UTILITIES"."hdi::T_DEV_WORKSPACES"))
GROUP BY NB_J
ORDER BY NB_J DESC
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
10 | |
9 | |
7 | |
6 | |
6 | |
6 | |
5 | |
4 | |
4 | |
3 |