Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "COSMOS CMDBf Query to SQL Mapping"

(Find computers running Windows)
(Same query as #5, but only include columns in Computer table in the result)
Line 124: Line 124:
 
</pre>
 
</pre>
  
==Same query as #5, but only include columns in Computer table in the result==
+
==Find Computers Running Windows==
 
SQL:  
 
SQL:  
  

Revision as of 17:57, 26 May 2008

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 cover the basic queries 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

Schema1.jpg

Schema 2

Schema2.jpg

Both schemas are for modeling relationships between operating system (OS) instances 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

Queries

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>

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>

Find computers with 2G memory or more

SQL: select * from Computer where memory > 2000

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>

Find computers with serial number XYZ

SQL: select * from Computers 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>

Use InstanceIdConstratin?!

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>

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>

Same query as #5, but only include serial number in the result

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>

Find computers running Windows, installed in 2008

SQL (Schema 1):

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

SQL (Schema 2):

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

Back to the top