Database Trigger Plugin

This plugin periodically executes a SQL query on a database and a trigger when new rows are found.

Saved Parameters Description

• Name: The name used to identify a saved Database Trigger configured instance.
• Comments: Some comments about this saved Database Trigger configured instance.
• Database Type: The type of the external Database to connect to for the execution of the query. Supported RDBMS types are MySQL, Oracle and all ODBC compatible RDBMS.
• Database host: For MySQL only, if a Node is not provided, a host address can be specified.
• Database port: The port to connect to the database. As for the host, the port to be used is specified in the datasource configuration for ODBC and Oracle, hence this parameter only makes sense for MySQL
• Database name (or source name): For MySQL, the database name needs to be specified, for ODBC and Oracle, the data source name is used instead.
• Database user: The user name for the external database being queried
• Database password: The password associate to the database user
• Database query: The SQL query to execute. Stored procedure can be used. Dynamic attributes are supported. For example 'select name,login from users where login = "<%=user_login%>"' would expand an input variable name 'user_login' and return the matching database rows.
• Polling Frequency: Frequency (in seconds) with which the AsperaCentral Web service is called to check for transfer session statuses. By default, a value of 0 is used (i.e. poll as often as possible)
• Trigger persistence scope: Sets the scope/availability of the trigger output. The default value is 'By workorder group'. The definitions are: 'By workorder group', 'By action template', 'absolute', 'By step', 'By workflow', 'None'

Details:

• 'By workorder group' - Trigger folders are tracked while workorder is executing and will only be trapped once. If the workorder is restarted, all past triggers will be detected again if they are still present.

• 'By workflow' - Same as 'By workorder group' except no triggering will occur upon a 'restart' action as the workflow is still the same.

• 'By action template' - Allows the tracking and thus non-triggering in the second workflow for previously processed trigger files shared across workflows that contain the same template,

• 'absolute' - One trigger list is tracked regardless of the where it occurs,

• 'By step' - Similar to 'By workflow', but allows for multiple independent trigger steps within the workflow.

• 'None' – No persistence at all. If a past trigger file is still present, it will re-trigger if ‘keep on-going’ is selected. This option is recommended when ‘keep on-going’ is not selected.

• Maximum number of rows returned: The Maximum number of rows to be returned.
• Row formatting code: This code will be executed for each row of the result set. Each row is represented in a variable name 'row' as a hash indexed by the database column name (as a symbol preceded by :), for example {:name=>'Maurice',:login=>'momo'}. The content of the row variable can be modified to alter formatting. For example, row = "#{row[:login]} (#{row[:name]})", would change the content of row to be a string, here 'momo (Maurice)'.
• Results formatting code: This code will be executed after each row has been post-processed with the code specified in the 'row formatting code' box. A variable 'results' containing the array of the formatted rows can be manipulated to modify the action result. For example, results = results.join(", ") combined with the previous row formatting code, would mean results (and hence the output of the action) would be a string. Here 'momo (Maurice), baba (Bill)'
• Result Type: The type of the action output. By default, the action returns an array of hash. With each matching row found represented by a hash, indexed by the database column name (as a symbol preceded by :), for example [{:name=>'Maurice',:login=>'momo'},{:name=>'Bill',:login=>'baba'}]. The type chosen should match the formatting code specified. No automatic type translation will be done, so all transformation needs to be explicit in the row and results formatting code boxes. In the example above, the output type should be set to 'string' instead of the default 'array'.

Inputs description

The list of inputs depends on the configuration of the Database Query action template as the query and formatting fields support dynamic inputs.

Outputs description

• Trigger_results: The formatted results of the query. If no formatting is provided, the results is an array of hash, otherwise the type of this outputs will depends on the specified output type.

Supported Actions

None

Dependencies

None

Operating Instructions

When using Oracle or ODBC, some additional installation steps are required.

For Oracle: The Oracle client needs to be installed on the Orchestrator server.

The datasource to be accessed must be set up in the tnsnames.ora file. Before attempting a connection through Orchestrator, ensure that connectivity can be achieved and queries can be executed from the command line.

The datasource to be accessed must be set up in the tnsnames.ora file.

Before attempting a connection through Orchestrator, ensure that connectivity can be achieved and queries can be executed from the command line.

See the "Oracle DBI install notes" document for more detailed information.

For ODBC: The ODBC driver needs to be installed on the Orchestrator server (FreeTDS can be used on Linux).

The ODBC source to be accessed must be set up. Before attempting a connection through Orchestrator, ensure that connectivity can be achieved and queries can be executed from the command line.

Once the ODBC drivers are installed, the ruby-odbc and dbd-odbc gems need to be installed.

        cd /opt/aspera/orchestrator
        gem install vendor/gems_linux-gnu/ruby-odbc-0.99994.gem
        gem install vendor/gems_linux-gnu/dbd-odbc-0.2.5.gem