rework/improve majiq-to-sql query language capability

Idea created by Michael Mueller Employee on Nov 8, 2016
    Not planned

    Based on another idea discussion Option to disable 'cartesian product' check

    this one tries to take a more generic look at the query capability of SDM


    SDM supports its own proprietary database query language which provides a translation of an object oriented query language to sql select statements.

    Even though this proprietary query language (majic whereclauses) already supports certain kind of queries, there are query areas where I would like to see further / additional / more generic support of query terms to better align query capabilities to customer business needs.


    Areas of interest:

    • SREL chain support together with OR conditions
      • current: OR conditions are more or less only supported with direct attributes of the current context object, like
        • bop_odump domsrvrv cr "customer = U'zzz' or requested_by = U'yyy'" id
        • purpose: Give me all tickets, where the affected users internal key is zzz or the internal key of the requester of the ticket is yyy 
      • prospect: generic support of OR conditions, like
        • bop_odump domsrvr cr ""New York' or'New York'
        • purpose: Give me all tickets where affected users site name or the requesters site name is equal to "New York"
    • attribute support of right hand side of condition
      • current: only static/literal value support of right hand side of conditions.For example
        • bop_odump domsrvr cr " = 'New York'" id
        • purpose: give me all tickets wher the name of the location of the site of the affected user of a ticket  is equal to "New York"
      • prospect: Attribute value support of right hand side of conditions
        • bop_odump domsrvr cr " =" id
        • purpose: give me all tickets where the affected users site name is equal to the assignees site name"
    • generic QREL support
      • current: QRELS are supported by using a specific square bracket syntax, and not their underlying object/majic definition
      • purpose: by supporting the underlying query definition of the QREL definition. the square bracket syntax could be ommitted and QREL attributes could be used the same way as BREL's can be used already today.
    • SREL chain support for @root/@cnt references
    • support of exists and not exists
      • current: exists is somehow supported, not exists is not supported att all.
      • prospect: supporting the capability for checking if data exists or not, for example:
        • bop_odump domsrvr cr "active=1 and not exists(act_log.type='ESC')" id
        • purpose: show me active ticktets where a non internal activity log entries of type Escalate does not exists.
    • support of hierarchical queries
      • current: no support except in odbc driver
      • prospect: supporting the capability of getting hierachical tree information such as business service trees or Ticket-children trees, including  "where to start"-condiition, flexible connect by definition and "maximal level" condition