CMDBf query only supports a subset of SQL query capabilities, and there are CMDBf queries that are difficult to be done with SQL. The goal is to clearly define the subset of SQL queries capabilities that allows a two-way mapping between CMDBf query and SQL.

CMDBf query only supports a subset of SQL query capabilities, and there are CMDBf queries that are difficult to be done with SQL. The goal is to clearly define the subset of SQL queries capabilities that allows a two-way mapping between CMDBf query and SQL.

−

−

= Schema =

−

The examples below will use the following two schema definitions. These two schemas are equivalent but the CMDBf queries may be different.

−

−

'''Schema 1'''

−

−

[[Image:Schema1.jpg]]

−

−

'''Schema 2'''

−

−

[[Image:Schema2.jpg]]

−

−

Both schemas are for modeling relationships between operating system instances (OS) with computers. An operating system instance can be installed on one computer. In the first schema, the relationship is represented by the foreign key from the OS table to the Computer table. In the second schema, the relationship is represented by a separate table.

= Mapping =

= Mapping =

Line 41:

Line 28:

=Examples=

=Examples=

+

== Schema ==

+

The examples below will use the following two schema definitions. These two schemas are equivalent but the CMDBf queries may be different.

+

+

'''Schema 1'''

+

+

[[Image:Schema1.jpg]]

+

+

'''Schema 2'''

+

+

[[Image:Schema2.jpg]]

+

+

Both schemas are for modeling relationships between operating system instances (OS) with computers. An operating system instance can be installed on one computer. In the first schema, the relationship is represented by the foreign key from the OS table to the Computer table. In the second schema, the relationship is represented by a separate table.

Overview

MDRs and federating CMDBs support CMDBf query services, which accepts queries in CMDBf query format. The query service interprets the CMDBf query and queries the underlying data store for data. We can safely assume that no database or application would understand the CMDBf query today, as it is a new standard. So the MDR or ferderating CMDB would need to translate the CMDBf query to another query language, or invoke an appropriate method call of the application that serves the data for the MDR.

It is common to have data stored in relational databases. It is useful to come up with a set of guidelines for mapping CMDBf queries to SQL, and vice versa.

CMDBf query only supports a subset of SQL query capabilities, and there are CMDBf queries that are difficult to be done with SQL. The goal is to clearly define the subset of SQL queries capabilities that allows a two-way mapping between CMDBf query and SQL.

Mapping

namespace: database or schema

recordConstraint/recordType/@localName: table name

recordConstraint/propertyValue/@localname: column name

recordConstraint/propertyValue/*: predicates in the where clause

a predicate is in the form <column_name> <operator> <value>

operators supported: =, <, >, <=, >=, contains, like, isNull

the negate operation can be applied on a predicate

When there are more than one predicates, they can be logically AND-ed or OR-ed, using the matchAny attribute. (A combination of AND and OR is not allowed.)

The value on the right side of the operator cannot be a variable or a column name. i.e. the operator can only operate on a constant.

SQL lets you select a subset of the table columns to be included in the query result. The response filtering can be done by:

suppressFromResult attribute of itemTemplate or relationshipTemplate: columns from tables representing the items or relationships will not be part of the query result.

relationshipTemplate can be used to specify a table JOIN. Specifically, it is a "natural join", where you have equality predicates on attributes that are common to the tables involved. The sourceTemplate and targetTemplate specify the two tables to be JOIN'ed. The relationship of two object types can be modeled with a relationship table, or simply with foreign keys. If it is the former case, relationshipTemplate/recordConstraint/recordType/@localname can identify the table, and the join will involve three tables.

Open issues:

InstanceIdConstraints: This constraint is useful when querying a federating CMDB where identifiers are reconciled, and items/relationships can be identified by mdrId + localName. When mapping with SQL, one may map that to a query that select rows by unique identifiers (e.g. primary keys). There are two observations:

Select by unique keys can be realized with recordConstraints.

The InstanceIdConstraint lacks a way to specify the table name, or record type. It is difficult to locate an item simply by its ID, without knowing the type of the ID. This may be a more general problem of the specification and I'm seeking an answer from the CMDBf team.

dangling items: the interpretation of item templates that are not referenced relationship templates can produce different results.

Examples

Schema

The examples below will use the following two schema definitions. These two schemas are equivalent but the CMDBf queries may be different.

Schema 1

Schema 2

Both schemas are for modeling relationships between operating system instances (OS) with computers. An operating system instance can be installed on one computer. In the first schema, the relationship is represented by the foreign key from the OS table to the Computer table. In the second schema, the relationship is represented by a separate table.

The Aperi MDR Example

The aperi data manager example is intended to be an example to show how to write an MDR that has a relationship database backend. The implementation handles the query differently from the interpretation presented above.

Here are some of differences:

suppressFromResult: template not processed, as opposed to use as a filter of the response

use of instanceIdConstraint: (see my comment of the use of instanceIdConstraints)

The Aperi MDR requires each relationship template must have a single record type constraint. But this is not a requirement in the interpretation presented above.

Query handling framework

This exercise can help extract the design pattern for interpreting CMDBf queries and translating CMDBf queries into other query languages. The goal is to provide a framework for facilitating the implementation of an MDR. COSMOS already has a framework designed for this purpose, but it may not be in the most optimized form, and its limiatations may prevent adopters from using it. (The Aperi implementation didn't use this framework.) I want to learn from our experience to improve on the query interpretation framework.