Package configuration

Configuration guide

• The package is implemented in Perl and can be used with Perl-based as well as VBScript-based schemas.
• The package does not require Perl modules deployment on clients; all code elements are stored in ClearQuest schema.
• The packages use ClearQuest Security Context feature and require metaschema version 22

(ClearQuest repository that created with CQ 2001a and higher or upgraded to 2001A from previous releases).

• The package works with ClearQuest clients 2001A, 2002, 2003, and v7 (as of 12/07/2008, 7.1 has not been tested yet). The following platforms are supported: Windows, old CQWeb (ASP-based version), CQWeb (Java Web), ClearQuest UNIX.

Installation

Please refer to installation instructions page. You will need to install the latest version of EmailNotification package only. UDBProperties will be installed automatically for you.

Note: due to a bug in CQ 2002 (if somebody still use it), all users must have 'SQL Editor' rights on that platform.

Configuration

Email Notification properties

Email notification package properties are ClearQuest records of udb_property record type. Configure property usually means submitting record of that record type.

The following properties can be configured for Email Notifications package:

email_host

It is your SMTP mail relay. If you need to specify non-default port, it can be done in host:port combination.

Examples: mail.provider.net, mail.provider.net:625.

email_login (optional)

login name that is used for password authentication with SMTP relay. If you SMTP relay does not require password authentication, you do not need to use this property, and it shoul not exist.

Note: If you would like, it can be a 'Secure' property.

email_password (optional)

password that is used for password authentication. If you SMTP relay does not require password authentication, you do not need this property, and it should not exist.

Note: It is a good idea to use 'secure' property feature when storing it in a ClearQuest database.

email_address (optional)

Static "From:" email address for notification. Email address of current ClearQuest user is used if property does not exist.

Note: This feature might be useful to bypass mail relaying restrictions: some SMTP relays may restrict mail relaying based on FROM email address. In case of password authentication, it might be also critical: FROM email address could be restricted to email address of the account used for authentication. Ask your SMTP relay admin for additional information.

email_reply_to (optional)

Static Reply-To address, if different from email_address can be specified. For example, you can configure mail replies to be sent to some distribution list.

email_enabled

It is the second mandatory property (besides email_host). It has to be set to '1' to enable email notification for user database. By setting it to 0 (or by deleting property), you can disable email notification on entire ClearQuest database

EN_DeliveryMode (optional)

The following delivery modes are available. Please check Email Notification delivery mode page for details.

Light	deliver notifications "on the fly" and log only error messages for re-delivery,
Deferred	(default) save all messages before attempting delivery, and deliver them in the same notification hook
Queue	save all messages in the message queue, and do not attempt to deliver them. Delivery will be done by a separate script running by scheduler on the server. You can find simple delivery script on Download page.

EN_DeliveryTimeout (optional)

Delivery timeout interval in seconds. If message in the message queue was not delivered it will be re-delivered (by other user, for example) after that.

Default value: 300 (5 minutes)

EN_DeliveryAttempt (optional)

Maximum number of delivery attempts performed for a message in the queue.

Default value: 5

EN_DeliveryScript (script only)

Should never be set in a ClearQuest database. To be used in Delivery script only.

EN_AdminGroup

Email administrators group. It is used in en_email_message action access control hooks If it is not set, only user with superuser can modify or delete email messages in the queue.

Debug (optional)

Debug level 1-9. Setting this property forces script to trace execution. Output could be captured by using dbwin32.exe utility

Default value: NONE

Notification_Default_Security_Group (optional)

Default visibility value. Existing udb_visibility record name that is used as a security context default value in Email_Notification_Rule submit action.

EN_Disable_Variable_Name_Validation (optional)

By default syntax of all parameterized fields and variable names are validated. You can turn off variable names check by setting this property to non-zero value.

Notification Rules

To create notification rule, Submit new Email_Notification_Rule record. Here are some important fields of this record type

Name	any unique name
Record_Type	desirable record type for which notification package is applied
Actions	notification can be restricted for set of actions. Notification is Triggered for any action if empty
Field Change	notification can be triggered by filed change. Triggered regardless any of fields change if empty. Fields changed in Action or Field initialization hooks are not counted.
Condition	Notification condition. Notification is triggered if notification condition evaluation result is TRUE. See section expression field syntax for reference. Assumed "TRUE" when the field is empty.
Priority	Message priority patameterized field, expression can be used. See expression field syntax section for reference. if evaluation result < 0, message priority is Low. == 0, message priority is Normal > 0, message priority is High Condition result is assumed 0 when the field is empty ('Normal' priority).
To	"TO:" addresses (parameterized). see parameter fields section for syntax reference. One email address per line is expected as evaluation result.
CC	"CC:" addresses (parameterized). One email address per line is expected as evaluation result.
BCC	"BCC:" addresses (parameterized). One email address per line is expected as evaluation result.
Header Add-in	Additional lines to be included in message header (envelope) - parameterized field.
Subject	Subject - parameterized field. Can be multiline, converted to single line and included into the message header after evaluation.
Body	message body (parameterized).

Notes

• Notification is triggered only if Action AND Field restrictions are satisfied AND Condition is true AND any of message recipients ("To:", "CC:", or "BCC") fields are not empty after evaluation.
• You can create site specific properties in multisited environment as <SiteName>PropertyName. If you would like to disable a global property on specific site, you could do it by setting <SiteName>PropertyName value to "EN_NONE".

Parameterized Text and Condition fields Syntax

Parameterized Text fields

Text field can contain plain text, variables that are expanded during rule evaluation, SQL statements, user-defined, pre-defined and Perl build-in functions.

Any character in the field can be escaped with \;

Variables

Variable Syntax

${var_name}	or
$var_name

Variables are evaluated (expanded) in the following order:

• Check if property with the same name defined in CQ database and return value
• Check predefined variable names and return value.
• Check record field with the same name.
  (you can use dot, ., in the name for reference field data type - $name1.name2.name3, but be careful when placing dot at the end of sentence after a variable - it can trigger an error: "${variable}." is safe, "$variable ." is safe, "$variable." is not)
  

Predefined variable names

$Action	current action name
$EntityDefName	current entity definition.
$SiteName	Site name in multisited environment
$EmailNotificationRuleName	Email Rule name that is currently evaluated

Examples:

$State	state field value
$owner.email	owner's email (REFERENCE field data type)
${Property_Name}	value of property "Property_Name"

Notes:

• Property names and Build-in Environment variables are case sensitive, while Field names are case insensitive!
• Date_Time variables: format that is used for Date_Time variable type inside ClearQuest hooks is yyyy-mm-dd hh:mm:ss.
  

SQL statements

SQL statement that will be passed to the backend database when variable is expanded.

SQL(<statement>)

SQL execution function

• Statement could be Double-Quoted string with variables, functions and SQL statements, single-quoted non-expandable string, or result of function that returns scalar value. *Statement string (SQL function parameter) is not limited to single line.
• Statement is executed in run-time using BuildSQLQuery API call and inherited all restrictions related to the API call. Please check ClearQuest API documentation.

Examples:

• Get owner's email (the same result as $owner.email)
  

SQL("

 SELECT  T1.email 
   FROM  users 
   WHERE T1.login_name = '$owner'

")

• Notify 'CHCD_Approvers' ClearQuest group members
  

SQL("

 SELECT  T2.email 
   FROM  parent_child_links T1, groups T3, users T2
   WHERE T1.parent_dbid = T2.dbid
     AND T1.child_dbid = T3.dbid 
     AND T3.name = 'CHCD_Approvers'
     AND T2.is_active = 1

")

• Here is a little bit more complicated scenario: assume that we use AssignmentGroups stateless record type at our site. It has the following fields:

Name	SHORT_STRING	Unique group name
Managers	REFERENCE to users	group managers
Users	REFERENCE to users	group members
Active	INT	obsolete groups marked as ‘0’, valid groups as ‘1’

"Request" record type has AssignedToGroup field (reference to "AssignmentGroups").
Request assigned to a Group first, then the Group Manager will assign it to somebody in the team.
If we would like to notify the Group Managers about new request that has been assigned to the Team,
we could set Email Notification Rule's "To:" field to the following value:
SQL("

 SELECT  T3.email
   FROM  parent_child_links T1, fielddef T2, users T3, assignmentgroups T4
   WHERE T1.parent_dbid = T4.dbid
     AND T1.child_dbid  = T3.dbid
     AND T1.parent_fielddef_id = T2.id
     AND T2.name = 'Managers'
     AND T4.name = '${AssignedToGroup}'

")

Functions

function_name(parameter [, paramater1 …])
Function name is a name of build-in, pre-defined or user-defined function. Spaces between function name and ( symbol are not allowed.

Parameter list is a comma-separated list of:

• variables,
• functions or SQL queries,
• single-quoted strings,
• double quoted strings with variables, functions, etc.
• expression in round brackets ( )
• numeric constants

Scalar value expected as function's return value.

Function name lookup order

• check a database property with the same name.
  • If found, property value assumed to be a valid Perl-syntax code, that will be executed as function.
  • Function parameters are passed as "@_" array to the function.
  • $entity global variable available in function.
    Return value becomes function result.
• check predefined functions
• function name is assumed to be a Perl function.

Examples:

• Limit Subject line to 100 characters (Perl buld-in function "substr" is used):

  substr( "some subject line with $parameters", 0, 100)

• Condition function:
  

  IF(($attempt > 0), 1, -1 )

• Check whether "Description" field was updated during action and trigger a notification (an alternative way)

Notification Rule Condition filed:

  IsFieldUpdatedThisAction('Description') != 0

Property "IsFieldUpdatedThisAction" contains the following code:

  my ($name) = @_;
  my $result = 0 ;
  eval {
    my $fields = $entity->GetFieldsUpdatedThisAction(); 
    $result = 1 if defined( $fields->ItemByName($name) );
  };
  $@ = '';
  return $result;

• Use original value of "Description" field in the mail body

Email Notification Rule "Msg_Body" field:

 Original Description: 
 "RT_GetFieldOriginalValue('Description')" 
 was changed to: 
 "$Description" 

Property "RT_GetFieldOriginalValue" contains the following code:

  my ($name) = @_;
  my $value = '';

  eval {
    $value = $entity->GetFieldOriginalValue($name)->GetValue();
  };
  $@ = '';
  return $value;

Note: variables of CQPerlExt module in user defined functions scope can cause 'false' execution errors, because CQPerlExt objects usually do not clear $@ variable on destruction. It would be safer to destroy private objects references before leaving the function and clean $@ variable to avoid the problem, for example:

  my $session = $entity->GetSession();
  # ... <some code> 

  $session = undef;
  $@       = '';
  return $return_value;

Double-quoted string

Variables, functions, SQL statements are expanded within double-quoted strings.
Escape symbols are: "\n", "\r","\t", "\f", "\b", "\a"
"\" symbol by the end of new lines will escape new line symbol, i.e.
"\" in front of any other symbol escapes that symbol, i.e. \$, \", etc

 "aaa\ 
  bbb" 
  is equal to 
 "aaa bbb"

Examples:

"some string here with $variable and function f1($par1, $SQL{'Statement'}, 'string') and others"

Single-quoted strings

Single quoted strings are not expanded. Escape symbols are \\ and \'.

Conditions

Conditions are implemented in perl-like style (please check Perl manual for detailed syntax documentation).
They can contain variable, single- and double-quoted strings, function and other elements described in previous section.
You can group expressions with brackets ( ).

Operators (in priority order)

Operator	Comments
!, ~, +, -	Unary operators
=~, !~	regular expression at the right side can include modifiers (/m/i/…/)
*, /, %, x	mult, div, ...
+, -, .	plus, minus, string concatenation
gt, ge, lt, le, >, >=, <, <=	String and numeric comparison
eq, ne, cmp, != , ==, <=>	String and numeric comparison
&&	Logical AND
||	Logical OR

Note: it has to be a space between '/' operator and operand.

Examples:

($field =~ /something/i) && ($another_field > function($param1, $param2) || $another_value)

Build-in (Predefined) Functions

IF( <expression>, <val1>, <val2) )

Parameters:

<expression>	logical expression
<val1>	parameter returned if expression is true
<val2>	parameter returned if expression is false

Returns:
<val1> or <val2> depending on expression result.

Examples:

IF( $owner.email ne '' , $owner.email, 'administrator@email-address.net' )

GetFieldOriginalValue(<fieldname>)

Parameters:

<fieldname>

ClearQuest field name

Returns:
ClearQuest field original value.

Examples:

IF( GetFieldOriginalValue('owner') ne $owner, GetFieldOriginalValue('owner.email'), $owner.email )

GetDateTime( [ <offset> ] )

Parameters:

<offset>

offset from current date/time in the following format:

General offset format: [+-]?[\d+][smhd]?

none or ‘now’	offset = 0
+1	+ 1 second
'+5s'	+ 5 seconds
'-2m'	- 2 minutes
'+10h'	+ 10 hours
'3d'	+ 3 days

Returns:
Current date/time plus offset as date_time string in 'yyyy-mm-dd hh:mm:ss' format

Examples:

GetDateTime()	now
GetDateTime('-1d')	yesterday
GetDateTime( -300 )	five minutes before

DateTimePlusOffset( <date_time> [, <offset> ] )

Parameters:

<date_time>	string in yyyy-mm-dd [hh:mm:ss] format
<offset>	offset from <date_time> in [+-]?[\d+][smhd]? format

Returns:
Given date plus offset as a date_time string in 'yyyy-mm-dd hh:mm:ss' format

Examples:

DateTimePlusOffset('2000-10-01 00:01:30', '+30m')

Returns: 2000-10-02 00:31:30

DateTimePlusOffset( $Eneter_Date, '3d' )

Returns: value of 'Enter_Date' field + 3 days.

GetDateTimeFromTimestamp( <timestamp>)

Parameters:

<timestamp>

long number of seconds since 01/01/1970 (result of time() function)

Converts timestamp to local date/time.

Returns:
string in yyyy-mm-dd hh:mm:ss format

Example:

GateDateTimeFromTimestamp(time())

Returns current date

DateTimeToDate( <date_time> )

Parameters:

<date_time>

string in ‘yyyy-mm-dd [hh:mm:ss]’ format

Converts date/time string to date only string.

Returns:
Date String in yyyy-mm-dd format.

GetDate( [ <offset> ] )

Parameters:

<offset>

offset from current date/time in the following format:

General offset format: [+-]?[\d+][smhd]?

none or now	offset = 0
+1	+ 1 second
'+5s'	+ 5 seconds
'-2m'	- 2 minutes
'+10h'	+ 10 hours
'3d'	+ 3 days

Returns:
Current date/time plus <offset> as a date strings in yyyy-mm-dd format

Examples:

GetDate()	today
GetDate('-1d')	yesterday
GetDate('+1d' )	tomorrow

DateTimeDiff( <date_time1>, <date_time2> [ , <modifier>])

Parameters:

<date_time1>	string in ‘yyyy-mm-dd [hh:mm:ss]’ format
<date_time2>	string in ‘yyyy-mm-dd [hh:mm:ss]’ format
<modifier>	none/’s’/’m’/’h’/’d’

Modifier changes returned result from difference in seconds, when it is s or absent, to minutes (m), hours (h) or days (d).

Modifier	result in
undef	seconds
's'	seconds
'm'	minutes
'h'	hours
'd'	days

Returns:
(<date_time1> - <date_time2>) in seconds or other metrics, according to modifier:

Example:

DateTimeDiff( $action_date, GetDateTime(), 'd')

Returns a difference between $action_date field and current date/time in days.

Examples

Additional examples can be found on Examples page.