Coding SQL queries using OMDSReader

In addition to managing the OMDS connection, OMDSReader also has functions for issuing SQL queries and delivering the results: l1t::OMDSReader::basicQuery(...) and l1t::OMDSReader::singleAttribute(...). At the moment, OMDSReader only accomodates basic queries of the form

SELECT <columns> FROM <schema>.<table> WHERE <conditionLHS> = <conditionRHS>

where

<columns> can be one or many column names, packaged in a std::vector<std::string>
<schema> is a single string
<table> is a single string
<conditionLHS> is a single string
<conditionRHS> can be a single string or the result of another query; if the RHS is not a string, then different, templated, function can be used (see below)

If < schema > is an empty string, the nominal schema for the relevant account will be used. However, since O2O will be run from the read-only CMS_TRG_R account, < schema > should always be given.

The query results are returned in an object of type l1t::OMDSReader::QueryResults, whose usage is shown below.

Example SQL queries

If the key in conditionRHS is a std::string

The query SELECT A FROM SCHEMA_A.TABLE_A WHERE TABLE_A.ID = "MyKey" would be written as

l1t::OMDSReader::QueryResults results = m_omdsReader.basicQuery(
   "A",
   "SCHEMA_A",
   "TABLE_A",
   "TABLE_A.ID",
   m_omdsReader.singleAttribute("MyKey")
) ;

For multiple columns, e.g. SELECT A, B, C FROM SCHEMA_A.TABLE_A WHERE TABLE_A.ID = "MyKey":

std::vector< std::string > columns ;
columns.push_back( "A" ) ;
columns.push_back( "B" ) ;
columns.push_back( "C" ) ;
l1t::OMDSReader::QueryResults results = m_omdsReader.basicQuery(
   columns,
   "SCHEMA_A",
   "TABLE_A",
   "TABLE_A.ID",
   m_omdsReader.singleAttribute("MyKey")
) ;

For nested conditions, e.g. SELECT A FROM SCHEMA_A.TABLE_A WHERE TABLE_A.ID = (SELECT B FROM SCHEMA_B.TABLE_B WHERE TABLE_B.ID = ( SELECT C FROM SCHEMA_C.TABLE_C WHERE TABLE_C.ID = "MyKey" ) ):

l1t::OMDSReader::QueryResults results = m_omdsReader.basicQuery(
   "A",
   "SCHEMA_A",
   "TABLE_A",
   "TABLE_A.ID",
   m_omdsReader.basicQuery(
      "B",
      "SCHEMA_B",
      "TABLE_B",
      "TABLE_B.ID",
      m_omdsReader.basicQuery(
         "C",
         "SCHEMA_C",
         "TABLE_C",
         "TABLE_C.ID",
         m_omdsReader.singleAttribute("MyKey")
) ) ) ;

Finally, if conditionRHS is a multi-column QueryResults object, then the column to use in the condition is specified by the fifth (optional) argument:

// SELECT A, B, C FROM SCHEMA_A.TABLE_A WHERE TABLE_A.ID = "MyKey"
std::vector< std::string > columns ;
columns.push_back( "A" ) ;
columns.push_back( "B" ) ;
columns.push_back( "C" ) ;
l1t::OMDSReader::QueryResults results1 = m_omdsReader.basicQuery(
   columns,
   "SCHEMA_A",
   "TABLE_A",
   "TABLE_A.ID",
   m_omdsReader.singleAttribute("MyKey")
) ;

// SELECT X FROM SCHEMA_X.TABLE_X where TABLE_X.ID = ( SELECT A FROM TABLE_A WHERE TABLE_A.ID = "MyKey" )
l1t::OMDSReader::QueryResults results2 = m_omdsReader.basicQuery(
   "X",
   "SCHEMA_X",
   "TABLE_X",
   "TABLE_X.ID",
   results1,
   "A"
) ;

// SELECT Y FROM SCHEMA_Y.TABLE_Y where TABLE_Y.ID = ( SELECT B FROM TABLE_A WHERE TABLE_A.ID = "MyKey" )
l1t::OMDSReader::QueryResults results2 = m_omdsReader.basicQuery(
   "Y",
   "SCHEMA_Y",
   "TABLE_Y",
   "TABLE_Y.ID",
   results1,
   "B"
) ;

If the key in conditionRHS is not a std::string

If the key to be used in conditionRHS is not a string, then there is a templated function OMDSReader::basicQueryGenericKey(), with the same arguments as OMDSReader::basicQuery() above. The template argument conveys the type of MyKey below to the compiler.

For example, if MyKey is an integer, then the query SELECT A FROM SCHEMA_A.TABLE_A WHERE TABLE_A.ID = "MyKey" would be written as

l1t::OMDSReader::QueryResults results = m_omdsReader.basicQueryGenericKey< int >(
   "A",
   "SCHEMA_A",
   "TABLE_A",
   "TABLE_A.ID",
   m_omdsReader.singleAttribute(MyKey)
) ;

All the other function calls in the previous section are modified analogously.

Using SQL query results

l1t::OMDSReader::QueryResults contains the following data members:

  • std::vector<std::string> containing the column names of the results. This is a copy of the vector input to the query.
  • std::vector<coral::AttributeList> containing the data. Each AttributeList is one row in the result.
To simplify access to the data, there are templated functions fillVariable(...) [for results in the first row] and fillVariableFromRow(...) [for results in a given row].

The results of the query are accessed as follows:

double valueA ;
results.fillVariable( "A", valueA ) ;  // column name, variable to be filled
A warning is printed if results contains more than one row. In this case, only the first row will be used to fill valueA. To access multiple rows, for instance, in a loop over rows:
std::vector valuesA ;
for( int i = 0 ; i < results.numberRows() ; ++i )
{
   double valueA ;
   results.fillVariableFromRow( "A", i, valueA ) ;  // column name, row number, variable to be filled
   valuesA.push_back( valueA ) ;
}

In the above examples, if results has only one column, then there is no need to specify the column name, and it can be omitted:

results.fillVariable( valueA ) ;
results.fillVariableFromRow( i, valueA ) ;
A warning will be printed if results contains more than one column. In this case, only the first column will be used to fill valueA.

-- WernerSun - 26 Feb 2009

Edit | Attach | Watch | Print version | History: r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r1 - 2009-02-26 - WernerSun
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    CMSPublic All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback