앱 간 통신¶

ALTER APPLICATION
  SET CONFIGURATION DEFINITION my_server_app_name_configuration
    TYPE = APPLICATION_NAME
    LABEL = 'Server App'
    DESCRIPTION = 'Request for an app that will provide access to server procedures and functions. The server app version must be greater than or equal to 3.2.'
    APPLICATION_ROLES = (my_server_app_role);

SHOW CONFIGURATIONS IN APPLICATION my_server_app_name;

ALTER APPLICATION my_client_app_name
  SET CONFIGURATION my_server_app_name_configuration
  VALUE = MY_SERVER_APP_NAME;

ALTER APPLICATION SET SPECIFICATION my_server_app_name_connection_specification
  TYPE = CONNECTION
  LABEL = 'Server App'
  DESCRIPTION = 'Request for an app that will provide access to server procedures and functions. The server app version must be greater than or equal to 3.2.'
  SERVER_APPLICATION = MY_SERVER_APP_NAME -- server name obtained from Step 1
  SERVER_APPLICATION_ROLES = (my_server_app_role);

SHOW APPROVED SPECIFICATIONS ->>
  SELECT PARSE_JSON("definition"):"SERVER_APPLICATION"::STRING
  FROM $1
  WHERE "name" = 'MY_SERVER_APP_NAME_CONNECTION_SPECIFICATION';

CREATE OR ALTER VERSIONED SCHEMA app_schema;

-- create a callback that creates the connection request before the config value of the server name is saved
CREATE OR REPLACE PROCEDURE app_schema.before_config_change_callback(config_name STRING, config_value STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    spec_name VARCHAR;
    existing_target VARCHAR;
BEGIN
    IF (config_value IS NOT NULL AND config_name = 'MY_SERVER_APP_NAME_CONFIGURATION') THEN
        SHOW SPECIFICATIONS;
        SELECT PARSE_JSON("definition"):SERVER_APPLICATION::STRING
            INTO existing_target
            FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

        IF(existing_target IS NOT NULL AND UPPER(existing_target) != UPPER(config_value)) THEN
            EXECUTE IMMEDIATE 'ALTER APPLICATION DROP SPECIFICATION CONNECTION_' || UPPER(existing_target);
        END IF;

        spec_name := 'CONNECTION_' || UPPER(config_value);
        EXECUTE IMMEDIATE
        'ALTER APPLICATION SET SPECIFICATION ' || spec_name || '
            TYPE = CONNECTION
            LABEL = ''Server App''
            DESCRIPTION = ''Request for an app that will provide access to server procedures and functions. The server app version must be greater than or equal to 3.2.''
            SERVER_APPLICATION = ' || config_value || '
            SERVER_APPLICATION_ROLES = (my_server_app_role)';
    END IF;
RETURN 'success';
END;
$$;

CREATE APPLICATION ROLE IF NOT EXISTS client_app_user;
GRANT USAGE ON SCHEMA app_schema TO APPLICATION ROLE client_app_user;
ALTER APPLICATION SET CONFIGURATION DEFINITION my_server_app_name_configuration
    TYPE = APPLICATION_NAME
    LABEL = 'Server App'
    DESCRIPTION = 'Request for an application that will provide access to server procedures and functions. The server app version must be greater than or equal to 3.2'
    APPLICATION_ROLES = (client_app_user);

CREATE TABLE IF NOT EXISTS app_schema.processing_queue (
  request_id NUMBER AUTOINCREMENT,
  operation STRING,
  input STRING,
  status STRING DEFAULT 'PENDING',
  response STRING DEFAULT ''
);

CREATE OR REPLACE PROCEDURE app_schema.submit_request(operation STRING, input STRING)
RETURNS STRING
LANGUAGE SQL
EXECUTE AS OWNER
AS
$$
BEGIN
    INSERT INTO app_schema.processing_queue (operation, input) VALUES (:operation, :input);
    RETURN 'Request submitted successfully';
END;
$$;

CREATE OR REPLACE PROCEDURE app_schema.process_requests()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS OWNER
AS
$$
DECLARE
    -- Cursor to find all PENDING requests
    c1 CURSOR FOR SELECT * FROM app_schema.processing_queue WHERE status = 'PENDING';
    result STRING;
BEGIN
    FOR request IN c1 DO

        IF (request.operation = 'OPERATION_X') THEN
            -- assuming there is a UDF func_x(input) to perform operation_x
            result := (SELECT func_x(:request.input));
        END IF;

        -- update the processing queue with the result
        LET stmt STRING :=
            'UPDATE app_schema.processing_queue SET status = 'DONE', response = ' ||
            result ||
            ' WHERE request_id = ' ||
            request.request_id;
        EXECUTE IMMEDIATE (:stmt);

    END FOR;

    RETURN 'Processed pending requests.';
END;
$$;

CREATE OR REPLACE PROCEDURE app_schema.fetch_response(operation STRING, input STRING)
RETURNS STRING
LANGUAGE SQL
EXECUTE AS OWNER
AS
$$
BEGIN
    LET res STRING := (SELECT response FROM app_schema.processing_queue WHERE operation = :operation AND input = :input);
    RETURN res;
END;
$$;

CREATE APPLICATION ROLE IF NOT EXISTS my_server_app_role;
GRANT USAGE ON SCHEMA app_schema TO APPLICATION ROLE my_server_app_role;
GRANT USAGE ON PROCEDURE app_schema.submit_request(string, string) TO APPLICATION ROLE my_server_app_role;
GRANT USAGE ON PROCEDURE app_schema.process_requests() TO APPLICATION ROLE my_server_app_role;
GRANT USAGE ON PROCEDURE app_schema.fetch_response(string, string) TO APPLICATION ROLE my_server_app_role;