COSMOS CMDBf Query to SQL Mapping

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.
  • recordConstraint/contentSelector/selectedProperty/@localName: columns to include.
• 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.

Example 1: Find all computers

SQL: select * from Computer

CMDBf Query:

<s:query xmlns:s="http://cmdbf.org/schema/1-0-0/datamodel">
   <s:itemTemplate id="computers">
      <s:recordConstraint >
         <s:recordType namespace="http://www.eclipse.org/cosmos/examples" localName="Computer" >
         </s:recordType>
      </s:recordConstraint>
   </s:itemTemplate>
</s:query>

Example 2: Find computers with model equals "A123"

SQL: select * from Computer where model=A123

CMDBf Query:

<s:query xmlns:s="http://cmdbf.org/schema/1-0-0/datamodel">
   <s:itemTemplate id="computers">
      <s:recordConstraint>
         <s:recordType namespace="http://www.eclipse.org/cosmos/examples" localName="Computer" >
         </s:recordType>
         <s:propertyValue namespace="http://www.eclipse.org/cosmos/examples" localName="model" >
         <s:equal>A123</s:equal>
         </s:propertyValue>
      </s:recordConstraint>
   </s:itemTemplate>
</s:query>

Example 3: Find computers with 2GB memory or more

SQL: select * from Computer where memory > 2GB

CMDBf Query:

<s:query xmlns:s="http://cmdbf.org/schema/1-0-0/datamodel">
   <s:itemTemplate id="computers">
      <s:recordConstraint>
         <s:recordType namespace="http://www.eclipse.org/cosmos/examples" localName="Computer" >
         </s:recordType>
         <s:propertyValue namespace="http://www.eclipse.org/cosmos/examples" localName="memory" >
            <s:greater>2GB</s:greater>
         </s:propertyValue>
      </s:recordConstraint>
   </s:itemTemplate>
</s:query>

Example 4: Find computers with serial number XYZ

SQL: select * from Computer where serialnum=XYZ

CMDBf Query:

<s:query xmlns:s="http://cmdbf.org/schema/1-0-0/datamodel">
   <s:itemTemplate id="computers">
      <s:recordConstraint>
         <s:recordType namespace="http://www.eclipse.org/cosmos/examples" localName="Computer" >
         </s:recordType>
         <s:propertyValue namespace="http://www.eclipse.org/cosmos/examples" localName="serialnum" >
            <s:equal>XYZ</s:equal>
         </s:propertyValue>
      </s:recordConstraint>
   </s:itemTemplate>
</s:query>

Example 5: Find computers running Windows and their OS instances

SQL: select * from Computer, OS where OS.computer_serialnum=Computer.serialnum and OS.type=WINDOWS

CMDBf Query:

<s:query xmlns:s="http://cmdbf.org/schema/1-0-0/datamodel">
   <s:itemTemplate id="computers">
      <s:recordConstraint>
         <s:recordType namespace="http://www.eclipse.org/cosmos/examples" localName="Computer" >
         </s:recordType>
      </s:recordConstraint>
   </s:itemTemplate>
   <s:itemTemplate id="operatingSystems">
      <s:recordConstraint>
         <s:recordType namespace="http://www.eclipse.org/cosmos/examples" localName="OS" >
         </s:recordType>
         <s:propertyValue namespace="http://www.eclipse.org/cosmos/examples" localName="type" >
         <s:equal>Windows</s:equal>
         </s:propertyValue>
      </s:recordConstraint>
   </s:itemTemplate>
   <s:relationshipTemplate id="installedOn">
      <s:sourceTemplate ref="computers" minimum="0" maximum="0" >
      </s:sourceTemplate>
      <s:targetTemplate ref="operatingSystems" minimum="0" maximum="0" >
      </s:targetTemplate>
   </s:relationshipTemplate>
</s:query>

Example 6: Find computers running Windows

SQL:

select Computer.serialnum, Computer.model, Computer.memory
from Computer, OS
where OS.computer_serialnum=Computer.serialnum and OS.type=WINDOWS

CMDBf Query:

<s:query xmlns:s="http://cmdbf.org/schema/1-0-0/datamodel">
   <s:itemTemplate id="computers">
      <s:recordConstraint>
         <s:recordType namespace="http://www.eclipse.org/cosmos/examples" localName="Computer" >
         </s:recordType>
      </s:recordConstraint>
   </s:itemTemplate>
   <s:itemTemplate id="operatingSystems" suppressFromResult="true">
      <s:recordConstraint>
         <s:recordType namespace="http://www.eclipse.org/cosmos/examples" localName="OS" >
         </s:recordType>
         <s:propertyValue namespace="http://www.eclipse.org/cosmos/examples" localName="type" >
         <s:equal>Windows</s:equal>
         </s:propertyValue>
      </s:recordConstraint>
   </s:itemTemplate>
   <s:relationshipTemplate id="installedOn">
      <s:sourceTemplate ref="computers" minimum="0" maximum="0" >
      </s:sourceTemplate>
      <s:targetTemplate ref="operatingSystems" minimum="0" maximum="0" >
      </s:targetTemplate>
   </s:relationshipTemplate>
</s:query>

Example 7: Find serial numbers of computers running windows

SQL:

select Computer.serialnum
from Computer, OS
where OS.computer_serialnum=Computer.serialnum and OS.type=WINDOWS

CMDBf Query:

<s:query xmlns:s="http://cmdbf.org/schema/1-0-0/datamodel">
   <s:contentSelector>
      <s:selectedRecordType namespace="http://www.eclipse.org/cosmos/examples" 
          localName="Computer">
         <s:selectedProperty namespace="http://www.eclipse.org/cosmos/examples"
             localName="serialnum"/>
      </s:selectedRecordType>
   </s:contentSelector>
   <s:itemTemplate id="computers">
      <s:recordConstraint>
         <s:recordType namespace="http://www.eclipse.org/cosmos/examples" localName="Computer" >
         </s:recordType>
      </s:recordConstraint>
   </s:itemTemplate>
   <s:itemTemplate id="operatingSystems" suppressFromResult="true">
      <s:recordConstraint>
         <s:recordType namespace="http://www.eclipse.org/cosmos/examples" localName="OS" >
         </s:recordType>
         <s:propertyValue namespace="http://www.eclipse.org/cosmos/examples" localName="type" >
         <s:equal>Windows</s:equal>
         </s:propertyValue>
      </s:recordConstraint>
   </s:itemTemplate>
   <s:relationshipTemplate id="installedOn">
      <s:sourceTemplate ref="computers" minimum="0" maximum="0" >
      </s:sourceTemplate>
      <s:targetTemplate ref="operatingSystems" minimum="0" maximum="0" >
      </s:targetTemplate>
   </s:relationshipTemplate>
</s:query>

Example 8: Find computers running Windows, installed in 2008

SQL (Schema 1):

select Computer.serialnum, Computer.model, Computer.memory
from Computer, OS
where OS.computer_serialnum=Computer.serialnum and OS.type=WINDOWS and OS.installation_date > 2008-01-01

SQL (Schema 2):

select Computer.serialnum, Computer.model, Computer.memory
from Computer, installedOn, OS
where OS.computer_serialnum=Computer.serialnum and OS.type=WINDOWS and installedOn.installation_date > 2008-01-01

CMDBf Query (for Schema 2):

<s:query xmlns:s="http://cmdbf.org/schema/1-0-0/datamodel">
   <s:itemTemplate id="computers">
      <s:recordConstraint>
         <s:recordType namespace="http://www.eclipse.org/cosmos/examples" localName="Computer" >
         </s:recordType>
      </s:recordConstraint>
   </s:itemTemplate>
   <s:itemTemplate id="operatingSystems" suppressFromResult="true">
      <s:recordConstraint>
         <s:recordType namespace="http://www.eclipse.org/cosmos/examples" localName="OS" >
         </s:recordType>
         <s:propertyValue namespace="http://www.eclipse.org/cosmos/examples" localName="type" >
         <s:equal>Windows</s:equal>
         </s:propertyValue>
      </s:recordConstraint>
   </s:itemTemplate>
   <s:relationshipTemplate id="installedOn">
      <s:sourceTemplate ref="computers">
      </s:sourceTemplate>
      <s:targetTemplate ref="operatingSystems">
      </s:targetTemplate>
         <s:recordType namespace="http://www.eclipse.org/cosmos/examples"
            localName="installedOn" >
            <s:propertyValue namespace="http://www.eclipse.org/cosmos/examples" 
               localName="installation_date">
               <greater>2008-01-01</greater>
            </s:propertyValue>
         </s:recordType>
   </s:relationshipTemplate>
</s:query>

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)

<s:instanceIdConstraint >
  <s:instanceId>
    <s:mdrId>AperiMDRID</s:mdrId>         
    <s:localId>Disk=6514</s:localId>
  </s:instanceId>
</s:instanceIdConstraint>

• The use of minimum and maximum attributes in relationship template. (both set to zero)
• Use of namespace to indicate table name. (We can get that from recordType/@localName.)

      <s:recordConstraint >
         <s:recordType namespace="http://schemas.aperimdr.org/schema/AperiMDR" localName="StorageSystem" >
         </s:recordType>
         <s:propertyValue namespace="StorageSystem" localName="Subsystem_ID" >
            <s:equal negate="false" caseSensitive="false" >3310</s:equal >
         </s:propertyValue>
      </s:recordConstraint>

• 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.