Extensions By User

First, we will explain how to include a user's SQL script extension as well as how to include additional user-specific global variables.

How to add a user-specific variables and SQL scripts?

For and during the installation of the metadata and thus also for the deployment to different environments, such as development,…

For and during the installation of the metadata and thus also for the deployment to different environments, such as development, test and production, it is possible for the data modeler or DevOps to include their own user-specific SQL scripts and variables in the installation without much effort.

Purpose

The idea behind this is that it should be possible for data modelers or DevOps to include additional, non-framework specific variables and SQL scripts or artifacts of the ExMeX tools in the installation or deployment of the metadata.

Motivation

Environments that do not allow direct access by the ExMeX Companion App, respectively for the DevOps or data modelers, it is necessary to include SQL scripts or variables in the deployment and installation.
Or to reduce the manual steps in a continuous deployment process.

Applicability

The SQL scripts or variables created, and tested by DevOps or data modelers are automatically executed at the end of the metadata installation.

Content

The examples below show on the one hand how to include the artifacts of the ExMeX tool Set Batch Setting.
Another example shows how to create your own user-specific variables and how to install them in the ExMeX Framework Core.

Considerations and consequences

Since an execution of a user-specific SQL scripts and variables may have far-reaching consequences for all parts of the ExMeX Framework Core, they should be chosen carefully.

For example, a user-specific script for setting the batch settings can cause the values set with the ExMeX Companion App to be overwritten after each installation. This is also one of the main reasons why the ExMeX Tools are not a standard part of the metadata installation.

Example

The following, sample step-by-step instructions show how to install an artifact of the ExMeX tool Set Batch Setting (aka user-specific SQL script) as well as user-specific variables.

Add artifact of ExMeX tool Set Load Batch

Please follow all the steps below to add an artifact of the ExMeX tool Set Batch Setting to the Metadata Installation. For more information on the ExMeX tool Set Batch Setting see section Toolbox -> Set Batch Setting -> How to configure Batch settings without the Companion App?

  • Copy “./library/tool/Set Batch Setting/SetBatchSetting.sql” file to “./usr/query/SetBatchSetting.sql”.
  • Add the execution command for the script "./usr/query/SetBatchSetting.sql" at the end (or the desired order) of the file "./usr/sqlcmdquery.sql":
    :r $(InitPath)"\usr\query\SetBatchSetting.sql”
  • Done


Add user-specific global variables

Please follow all the steps below to add user-specific global variables to the Metadata Installation. For more information on global variables see section Getting Started -> Configuration -> How do you set global variables and what is their purpose?

  • Create “./usr/query/LoadConfigUserSpecificVariables.sql” file.
  • Add the following code-snippet to “./usr/query/LoadConfigUserSpecificVariables.sql” file and adapt it to your needs:
    /*----------------------------------------------------------------------------*/
    /*-- Declare variable                                                         */
    /*----------------------------------------------------------------------------*/
    DECLARE @UserSpecificReturnNumber      INT;
    
    /*----------------------------------------------------------------------------*/
    /*-- Set variable initial                                                     */
    /*----------------------------------------------------------------------------*/
    SET @UserSpecificReturnNumber = 0;
    
    /*----------------------------------------------------------------------------*/
    /*-- Set global variable                                                      */
    /*----------------------------------------------------------------------------*/
    EXEC @UserSpecificReturnNumber = MetadataZoneCore.uspConfigExMeXFramework
        @VariableName         = 'UserSpecificName' --@VariableName   
        -- Varaibale value can be set in  ./etc/env/SetEnv*.sql   
    	 -- if it needs to be environment specific 
       ,@VariableValue        = @YourSpecificSetEnvValue --@VariableValue      
       ,@VariableDatatype     = 'NVARCHAR(256)' --@VariableDatatype   
       ,@VariableDescription  = 'Special user specific variable' --@VariableDescription
    
    SELECT @UserSpecificReturnNumber;
  • Add the execution command for the script "./usr/query/LoadConfigUserSpecificVariables.sql" at the end (or the desired order) of the file "./usr/sqlcmdquery.sql":
    :r $(InitPath)"\usr\query\LoadConfigUserSpecificVariables.sql”
  • Done

 

Posted 10 months agoby Dirk.Lerner