Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "COSMOS CMDBf Query to SQL Mapping"
(→Mapping) |
(→Mapping) |
||
Line 29: | Line 29: | ||
** 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.) | ** 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. | ** 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. | ||
=Queries= | =Queries= |
Revision as of 23:04, 26 May 2008
Contents
- 1 Overview
- 2 Schema
- 3 Mapping
- 4 Queries
- 4.1 Find all computers
- 4.2 Find computers with model equals "A123"
- 4.3 Find computers with 2G memory or more
- 4.4 Find computers with serial number XYZ
- 4.5 Find computers running Windows and their OS instances
- 4.6 Find computers running Windows
- 4.7 Find serial numbers of computers running windows
- 4.8 Find computers running Windows, installed in 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
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.
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.
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>
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>
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" minimum="0" maximum="0" > </s:sourceTemplate> <s:targetTemplate ref="operatingSystems" minimum="0" maximum="0" > </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>