Database Path Contexts (Part 2)
The previous part Data Paths and Expressions (Part 1) described the data path concept and its usage in various areas cross the CRM and its peripheral applications.
Database path contexts include paths with focus object as context, as well as absolute paths (Database contexts).
BO context object can serve as focus object as well and the path can be a BO context path.
In addition to the basic syntax, path relations can have modifiers, specified using round brackets ( and ) after the relation.
A condition modifier must be specified when there is a need to refer to a single target instance when traversing OTM or MTM relations
The modifiers serve two purposes:
- Restricting a path or a portion of the path to a unique result.
- Resolving the starting point for an absolute path.
1. Restricting a path or a portion of the path to a unique result.
For example, consider the following path that traverses from a case, to the sites for its contacts:
Since the contact2contact_role relation is one to many, this path might return several site_ids. The path can be modified to restrict the result as follows:
Note role_name is unique for each contact / contact_role / site combination. Therefore this path will select a single item.
The SQL for this path would be:
2. Resolving the starting point for an absolute path.
Alias must be specified when it is necessary to differentiate between two different paths to the same target object. For example, a case query for Owner = ‘jdoe’ and Originator=‘jsmith’ uses the case query properties:
The following SQL query is generated by the query:
Property Name Path
Property names are defined in the prop_name database object.
Assume case property “Originator” is path to case originator login name i.e. prop_name is “Originator” and path_name field is “case_originator2user:login_name”
With case focus specifies to login name of case originator
A Flexible Attribute may be used to terminate a path definition:
Here bandwidth is a previously-defined Flexible Attribute for site_part
Run Time Path Types
The class CoreDataPath (amdocs.core.datapath.CoreDataPath) provides methods for reading and writing data using data paths.
A data path defines the location of a data item within a specified context object.
A path starts by identifying the context object, and consists of one or more path items, separated by colons (:), that lead to the required data item.
This defines a data path using a context object named Var, with path items Customer and Address that lead to the data item State.
The context object name is prefixed with a dollar sign ($).
Context objects are passed to the API using a java.util.Map with the context names as the keys. Paths that do not specify a context name use the default context passed to the API methods. The type of the context determines the mechanism by which the path items are evaluated.
The following context types are supported for all application frameworks:
With the exception of JDBC paths, the appropriate evaluation mechanism is used to evaluate each item.
Thus, in the example above, $Var might refer to a java.util.Map, that holds a simple value object keyed by “Customer”. The path might be evaluated by
JDBC paths use the reference path mechanism for switching between evaluation mechanisms – this is explained in more detail below.
An indirect path is a path that defines a (String) data item that contains a path. Indirect paths start with a dollar, and must also specify a context name (the default cannot be inferred). Thus indirect paths start with “$$”.
For example, suppose a JDOM element contained the following structure:
The path $Elem:State will evaluate to the string “$Var:Customer:Address:State“.
The path $$Elem:State will evaluate to the actual state.
JDBC Paths Contexts
The jdbcDatapath use JDBC mechanism to access external databases.
This includes paths with focus object as context as well as absolute paths (JDBC contexts). Context name is used to resolve a Context object at runtime.
The context type JdbcFocusObject ( amdocs.core.JdbcFocusObject) can identify JDBC data.
JDBC Path Data Sources
JDBC database is configured as a SAM (Server Action Manager) resource and is identified by a resource name defined in ASC.
The exact ASC node is /AmdocsCommon/Aif/Connections/SamConnection.
Two types of JDBC data sources are used.
- LocalJdbc data sources use inproc JDBC API and the database driver is located on the same machine.
- ContainerManaged JDBC data sources have the drivers located on different server and have the benefits of connection pooling, J2EE transactions etc. Amdocs CRM support both Local and Container Managed JDBC data sources.
The jdbcDatapath is using JDBC mechanism to access external databases. This includes paths with focus object as context as well as absolute paths (Jdbc contexts).
Context name is used to resolve a Context object at runtime.
To identify a record in a database, JdbcFocusObject should be provided with the following parameters:
- Server Action Manager (SAM)
- Resource name,
- Table name
- If the table name is empty or null then the path is considered to be absolute data path.
- If table name and table-filter are given, then the path is a relative path starting with the row identified by table-filter.
- The method JdbcFocusObject.isAbsPath() indicates that the path is absolute (a TRUE value).
If it returns FALSE it indicates that the paths is relative.
Amdocs / Clarify CRM databases have rich metadata for relations and prop-names. This simplifies datapath string specification.
For JDBC databases, metadata for relations and prop-names are defined in the Amdocs System Configurtion files (Prior to release 7.5 it used to be provided in an external JdbcSchema.xml file).
The metadata is optional. The syntax allows relation information to be specified in-place. If metadata is provided then a logical relation name can be given in the path.
At runtime, datapath evaluats the relation name to physical relation information. For pre 7.5 releases JdbcSchema.xml must exist in same directory as ClarifyEnv.xml.
JDBC Path Modifiers
In addition to the basic syntax, path relations can have modifiers, specified using round brackets (and) after the relation. The modifiers serve 2 purposes:
A. Restricting a path or a portion of the path to a unique result.
For example, consider the following path that traverses from an employee, to the publisher for its address:
Since it is possible that two different employees named John Smith will be in more than one publisher, this path might return several addresses. The path can be modified as follows:
The publisher name is unique for each publisher, this path selects a single item. The SQL for this would be:
B. Resolving the starting point for an absolute path.
RelationInfo is a path syntax element that defines how multiple tables are traversed. Each token specifies either the physical relation info used for joins or a logical relation name. If a logical relation name is given then the Context object provides an API to resolve this to physical relation info.
Inner joins are used to traverse tables. The inner join is evaluation as follows:
- First token is field list from previous table.
- Second token is current table name
- Third token is field list belonging to current table
These 3 tokens are separated by two forward slash characters
Fields within a field list are separated by plus character. Typically the join includes one field from each table. But it is possible to have multi part join keys.
An optional filter can be added. The filter applies to current table.
Refers to id_number field on the Case object (Database path)
Path with relation:
Refers to the handset field on the attrition object, related by the age and city of the customer (assume that the focus table object holds the table customer with the filter name = ‘John Smith’).
Refers to the field price plan on the customer object, where the customer id is 91460.
Absolute path with relation
This path selects a single item, even though it is possible to have more than one person named John Smith (since publisher name is unique for each employee).
JDBC Reference Paths
A reference path combines two paths into a single item.
- The first half of path can defines a JDBC focus object context
- The second half defines a path from that JDBC focus object.
The reference operator ‘->’ separates the two components of the path.
The reference operator (->) conforms to the following rules:
- Left hand side path of context operator must be non-database path and right hand side path must be Jdbc focus object based database path with no context name.
- Left hand side path of context operator must resolve to JdbcFocusObject to be used as context object for JDBC database path on right hand side of context operator.
Reference path example:
Specifies equip_id field of user object. Jdbc object is specified by Hash map path
The Context name Var must resolve to valid context object instance at run time.
Using Path in Configuration Settings
Schema information for relations and prop-names are now defined in ASC (Amdocs System Configuration) under the Path
The following two resource types are supported by Server Action Manager (SAM) for JDBC:
The configurations for these resource types are located in the ASC (Amdocs System Configuration) under the path
More on JDBC Data Pathes
JDBC paths enable data items to be accessed from a JDBC database. There are two types of path;
relative and absolute.
To explain these paths, consider this simple database schema:
The database defines books, their authors and organizations that deal with them.
The role of the organization is one of “publisher”, “retailer” and so on.
A relative path is used when the context object is a JdbcFocusObject.
The context object identifies a row in a database table – the path specifies how to get data starting from that row.
The simplest relative path consists of the context name, followed by a column name.
A JDBC focus object might define a row in the book table. The book’s title is defined by:
Joins are specified using a relation specification. The syntax of a relation is:
For example, the author of the book is defined by:
Note the relation from table Books to table Author: auth_id/author/id
If a join is through a one-to-many relation, a filter may be added to a relation to specify a unique row.
The syntax of a filter is:
For example, the publisher of the book is defined by:
If a join condition requires concatenated keys, the concatenation is made using the plus sign (+).
The syntax is
The join condition is constructed like this:
Multi-part keys are specified using multiple plus signs.
An absolute path is not used with a JdbcFocusObject.
Instead, the context name must be one of the JDBC data source names defined in ASC (Amdocs System Configurator) repository by the Core/JDBC/DataSources node.
The starting point of the path is a database. The first path item specifies a table name.
Suppose that the above database was defined as BooksDb. The path
returns an array of JdbcFocusObject with an entry for each book in the table.
Absolute paths are typically used with a filter. Thus, the author of “Lord of the Rings” is defined by:
JDBC paths can be simplified by defining a logical schema in the ASC (Amdocs System Configuration) repository by the Core/JDBC/Schema node. The schema defines relation names and property names.
A relation name associates a name to a join condition, for example book2author can be defined for the book table as auth_id/author/id. This enables the author of a book to be defined by:
A property name defines an entire path.
For example the property publisher can be defined for the book table as :
This enables the publisher of a book to be defined by:
Note that an exclamation mark is used to introduce a property.
If a property name contains invalid characters (spaces or colons for example) it must be enclosed in double quotes.
A reference path consists of two parts, separated by ->.
- The first part of the path may use any context type, and must resolve to a JdbcFocusObject.
- The second part of the path is a JDBC path.
If $Var refers to a Map, then theMap.get(“BookRow”) contains a JdbcFocusObject defining a row in the book table. The above path will return its author.
HTTP Servlet Request Attributes
An attribute can be extracted from the Servlet request using an @ in front of the path item.
Named Collection Data Paths
A named element in a collection can be extracted from a java object by using two path items,
- The first identifies the collection,
- The second identifies the name of the item.
The java object’s class must implement either amdocs.core.datapath.INamedCollection or
amdocs.core.datapath.INamedCollectionExt or extend amdocs.core.datapath.NamedCollectionExt.
An example data path is as follows:
The $Dataset context type, represented by a java object which is an INamedCollection, contains a collection that can be accessed by getDataModelByName(“myDataModel”).