3.3.5.2.4 Shape Command Language Details

  • Article

When the client issues commands to the server, those commands can contain the Shape command language. The Shape command language is a specialized command language with the primary design goal of providing a mechanism to build RecordSets that have child RecordSets. Section 3.1.1.2.1 specifies how to map the parent-child relationship to entities specified in an adtgTablegram.

There are two distinct ways to specify the parent-child relationship between RecordSets using Shape. The first is to use a primary key–foreign key relationship, as specified in [FIPS127], in which the parent RecordSet contains the primary key, and the child RecordSet contains the foreign key. The Shape APPEND command is used to specify this type of relationship. Consider the following example.

 SHAPE { select * from customers } AS rsCustomer
   APPEND ( { select * from orders } AS rsOrders 
     RELATE customerid TO customerid
   )

This command will create a parent RecordSet called rsCustomers that will be related to the child RecordSet rsOrders. The customerid column is the key field used to form the relationship.

The second type of relationship is a computed RecordSet relationship. In this case, the parent RecordSet is computed from data in the child RecordSet. The Shape COMPUTE command is used to specify this type of relationship. Consider the following example.

 SHAPE { select * from orders } AS rsOrders
   COMPUTE rsOrders, SUM(rsOrders.TotalPrice) BY customerid

This command will create a child RecordSet called rsOrders, and a parent RecordSet that contains two columns: the first will be a chapter column that relates the two RecordSets, and the second will contain the sum of the TotalPrice columns for customerid in rsOrders.

While both COMPUTE and APPEND define parent-child relationships between RecordSets, the semantics of a relationship's direction are opposite for the two command types.

 SHAPE <parent RecordSet> APPEND <child Recordset(s)>
 SHAPE <child RecordSet(s)> COMPUTE <parent Recordset>

The Shape APPEND command can be used to create RecordSets with child RecordSets that have child RecordSets of their own. The depth to which such structures can be nested is not limited by the Shape command language or by the RDS Transport Protocol. This nesting is accomplished by substituting a nested Shape command for a Shape expression normally producing a single RecordSet. The first example in this section can be extended as follows.

 SHAPE { select * from customers } AS rsCustomer
   APPEND ( 
     ( 
       SHAPE { select * from orders } AS rsOrders
         APPEND (
           { select * from orderdetails } AS rsOrderDetails
           RELATE orderid to orderid
         )
     ) AS rsOrders 
     RELATE customerid TO customerid
   )

This would produce the same parent and child RecordSets as the first example, but there would be an additional child RecordSet of rsOrders called rsOrderDetails.

Additional examples of the usage of the Shape command language can be found in section 4.1.

The formal definition of the shape command language is as follows.

 rdsShapeCommandString = "SHAPE" sp
     appendCommand / computeCommand
  
 appendCommand = parentRecordSet [[ sp "AS"] sp ParentAlias]
     appendClause *([sp] "," [sp] appendClause)
  
 ParentAlias = fieldname
  
 appendClause =  
     "APPEND" sp childRecordSet [[sp "AS"] sp ChildAlias] sp
     "RELATE" sp relationConditionList 
     [[sp "AS"] sp chapterColumnAlias ]
  
 relationConditionList = relationCondition 
     *([sp] "," [sp] relationCondition)
  
 relationCondition = parentFieldName sp "TO" sp childFieldName
  
 parentFieldName = fieldName
  
 childFieldName = fieldName
  
 parentRecordSet = "{" nativeSqlStatement "}"
  
 childRecordSet = ( "{" nativeSqlStatement "}" ) /
     ( "(" [sp] rdsShapeCommandString [sp] ")" ) /
     recordSetName
  
 nativeSqlStatement = UNICODESTRING
  
 recordSetName = quotedName
  
 computeCommand = childRecordSet [sp "AS"] childAlias sp
     "COMPUTE" childAlias [[sp "AS"] sp chapterColumnAlias]
     [sp] "," [sp] addedColumnList
     [computeByClause]
  
 addedColumnList = columnList
  
 computeByClause = sp "BY" sp columnList
                      
 columnList = aliasedField *([sp] "," [sp] aliasedField)
  
 aliasedField = fieldExpression [[ sp "AS"] sp alias]
  
 fieldExpression = fieldName / compoundFieldName / calculatedExpression
  
 compoundFieldName = fieldName 1*("." fieldName)
  
 childAlias = fieldName
  
 chapterColumnAlias = fieldName
  
 calculatedExpression = sumExpression / avgExpression
     / minExpression / maxExpression / countExpression
     / stdevExpression / anyExpression / calcExpression
  
 sumExpression = "SUM(" [sp] qualifiedFieldName [sp] ")"
  
 avgExpression = "AVG(" [sp] qualifiedFieldName [sp] ")"
  
 minExpression = "MIN(" [sp] qualifiedFieldName [sp] ")"
  
 maxExpression = "MAX(" [sp] qualifiedFieldName [sp] ")"
  
 countExpression = "COUNT(" [sp] qualifiedFieldName [sp] ")"
  
 stdevExpression = "STDEV(" [sp] qualifiedFieldName [sp] ")"
  
 anyExpression = "ANY(" [sp] qualifiedFieldName [sp] ")"
  
 calcExpression = "CALC(" [sp] expression [sp] ")"
  
 qualifiedFieldName = (alias "." fieldname) / fieldName 
 alias = quotedName 
 fieldName = quotedName 
 quotedName = (dQuote name dQuote) / (sQuote name sQuote) / name
 name = ALPHA *(ALPHA / DIGIT / "#")
  
 sQuote = %x27.00
 dQuote = %x22.00
  
 sp = 1*LWSP

The semantic meanings of these fields are as follows:

  • rdsShapeCommandString

    An rdsShapeCommandString is a string expression that appears as a parameter in the RDS Transport Protocol, and is executed by the server to produce a RecordSet that contains one or more child RecordSets. Note that an rdsShapeCommandString can be legally nested within another rdsShapeCommandString—the resulting behavior is a child RecordSet that has child RecordSets of its own.

  • appendCommand

    Specifies one or more primary key–foreign key relationships between a parent RecordSet and one or more child RecordSets.

  • appendClause

    Specifies a child RecordSet and its relationship to the parent using one or more foreign keys.

  • relationConditionList

    Specifies the set of primary key–foreign key relationships that relates the child RecordSet to its parent RecordSet.

  • relationCondition

    Specifies a column in the parent RecordSet (the parentFieldName field) that serves as the primary key, and a column in the child RecordSet (the childFieldName field) that serves as the foreign key in the relationship.

  • childRecordSet

    Specifies a child RecordSet in one of three ways:

    • A query that can be executed by the data store, specified as a nativeSqlStatement.

    • A nested Shape language Command, specified as an rdsShapeCommandString.

    • The name of a RecordSet specified elsewhere within the top-level rdsShapeCommandString.

  • nativeSqlStatement

    Specifies a query that can be executed by the data store.

  • recordSetName

    Specifies a RecordSet already defined elsewhere within the top-level rdsShapeCommandString.

  • computeCommand

    Specifies a parent-child RecordSet relationship in which the parent RecordSet is computed from data in the child RecordSet.

  • addedColumnList

    Specifies columns in the parent RecordSet in addition to the chapter column. These are typically aggregate functions specified using the calculatedExpression syntax. See below for details.

  • computeByClause

    Specifies the grouping of columns in the child RecordSet for the purpose of computing the values in the parent RecordSet. The rows of the child RecordSet are organized into groups according to the following algorithm:

    1. If the computeByClause is present, one group is created for each unique combination of values in the column or columns specified in the computeByClause. Otherwise, all rows of the child RecordSet are treated as a single group.

    2. Each row of the child RecordSet is assigned to a group that matches the combination of values contained in the row.

    3. One row per group is created in the parent RecordSet.

    4. The chapter column in the parent RecordSet is used to link each row in the parent RecordSet to its respective group of rows in the child RecordSet. For more information, see RelationConditions in section 2.2.3.14.3.7.

    5. The values of each row in the parent RecordSet are populated by computing aggregate functions specified in the addedColumnList list field on its group of rows in the child RecordSet.

  • compoundFieldName

    Specifies a field in another named RecordSet. The syntax used is "<RecordSet name>.<Field name>". The field can also be specified by tracing the parent-child relationships using the syntax "<RecordSet>.<Child RecordSet>.[<Additional Descendant RecordSets].<Field Name>".

  • calculatedExpression

    Specifies how a value for a column in a row of the parent RecordSet is calculated from the corresponding rows in the child RecordSet using an aggregate function. In each case, the value of the qualifiedFieldName field specifies which column or columns in the child RecordSet will supply values to the aggregate function. The allowed type of values for this field and their semantic meanings are as follows:

    • sumExpression - The aggregate function returns the arithmetic sum of its arguments.

    • avgExpression - The aggregate function is an arithmetic average of its arguments.

    • minExpression - The aggregate function returns the smallest of its arguments.

    • maxExpression - The aggregate function returns the largest of its arguments.

    • stdevExpression - The aggregate function returns the standard deviation of its arguments.

    • countExpression - The aggregate function returns the number of arguments supplied to it.

    • anyExpression - The aggregate function returns true if any of its arguments evaluate to true.

    • calcExpression - The aggregate function, which can be any of the above, operates on an expression involving the values in each row of the child RecordSet, instead of any one of the values directly.

    The syntax and semantics of the range of values for the expression field is as follows.

     expression = aggregateFunction "(" [sp]
     (stringExpression / numericExpression / 
      dateExpression / booleanExpression) 
     [sp] ")"
  
 aggregateFunction = "SUM" / "AVG" / "COUNT" / "STDEV" / "ANY"
  
 stringOperand = string / qualifiedFieldName / stringExpression
 ; the qualifiedFieldName MUST evaluate to a string field
  
 numericOperand = number / qualifiedFieldName / numericExpression
 ; the qualifiedFieldName MUST evaluate to a numeric field
  
 dateOperand = date / dateNow / qualifiedFieldName
 ; the qualifiedFieldName MUST evaluate to a date and time field
 date = "#" month "/" day "/" year "#"
 month = [unicodeNum] unicodeNum ; January = 1
 day = [unicodeNum] unicodeNum
 year = [2*unicodeNum] 2*unicodeNum ; two- or four-digit year
 dateNow = "NOW(" [sp] ")" ; returns the current date and time
  
 booleanOperand = boolean / qualifiedFieldName / booleanExpression
 ; the qualifiedFieldName MUST evaluate to a Boolean field
  
 stringExpression = stringOperand / 
     (stringOperand binaryStringOperation stringOperand) /
     ( "(" [sp] stringExpression [sp] ")" )
  
 numericExpression = numericOperand / 
     (unaryNumericOperation [sp] numericOperand) /
     (numericOperand [sp] binaryNumericOperation [sp] numericOperand) /
     ( "(" [sp] numericExpression [sp] ")" )
  
 booleanExpression = reservedBoolean /
     (unaryBooleanOperation [sp] booleanOperand) /
     (booleanOperand [sp] binaryBooleanOperation [sp] booleanOperand) /
     (stringOperand [sp] strComparisonOperation [sp] stringOperand) /
     (numericOperand [sp] numComparisonOperation [sp] numericOperand) /
     (dateOperand [sp] dateComparisonOperation [sp] dateOperand) /
     ( "(" [sp] booleanExpression [sp] ")" )
  
 reservedBoolean = "True" / "False"
  
 dateExpression = dateOperand / ( "(" [sp] dateExpression [sp] ")" )
  
 binaryStringOperation = opConcat
 opConcat = "&" ; operator concatenates two string together
  
 unaryNumericOperation = ( opPositive / opNegative/ (sp opBnot sp) )
 opNegative = "-" ; operator negates a numeric value
 opPositive = "+" ; operator makes a numeric value positive
 opBnot = "bnot" ; bitwise negation
  
 binaryNumericOperation = binaryMathOperation / 
     binaryBitwiseOperation
  
 binaryMathOperation = opPlus / opMinus / opMult / opDiv / opModulus 
 opPlus = "+" ; operator adds operands together
 opMinus = "-" ; operator subtracts following operand from preceding
     ; operand
 opMult = "*" ; operator multiplies operands together
 opDiv = "/" ; operator divides preceding operand by following operand
 opModulus =  "%" ; operator returns remainder of dividing preceding 
     ; operand by following operand
  
 binaryBitwiseOperation = sp (opBAnd / opBOr / opBXor) sp
 opBAnd = "band" ; bitwise and
 opBOr = "bor" ; bitwise or
 opBXor = "bxor" ; bitwise exclusive-or
  
 unaryBooleanOperation = opNot
 opNot = "!" ; operator negates a Boolean value
  
 binaryBooleanOperation = sp (opEqv / opXor / opImp / opAnd / opOr) sp
 opEqv = "Eqv" ; operation returns true iff operands are equal
 opXor = "Xor" ; operation returns true iff exactly one operand is true
 opImp = "Imp" ; operation returns true if preceding operand is false
     ; else it returns the following operand
 opAnd = "And" ; operation returns true iff both operands are true
 opOr = "Or" ; operation return true iff one or both operands are true
  
 strComparisonOperation= (sp opContains sp) / commonComparisonOperation
     ; In the context of a strComparisonOperation, less than is 
     ;   interpreted to mean "comes alphabetically before", and greater
     ;   than is interpreted to mean "comes alphabetically after"
 opContains = "contains" ; operation returns true if following operand
     ; is a substring of preceding operand
  
 numComparisonOperation = commonComparisonOperation
 dateComparisonOperation = commonComparisonOperation
  
 commonComparisonOperation = opLT / opLTE / opGT / opGTE / opEq
 opLT = "<" ; operation returns true if preceding operand is less than
     ; following operand, and false otherwise
 opLTE = "=<" ; operation returns true if preceding operand is
     ; less than or equal to following operand, and false otherwise
 opGT = ">" ; operation returns true if preceding operand is greater
     ; than following operand, and false otherwise
 opGTE = ">=" ; operation returns true if preceding operand is greater
     ; than or equal to following operand, and false otherwise
 opEq = "=" ; operation returns true if preceding operand is equal to
     ; following operand, and false otherwise
  
 boolean = rTrue / rFalse / rYes / rNo / rOn / rOff
  
 rTrue = "true"
 rFalse = "false"
 rYes = "Yes" ; equivalent to rTrue
 rNo = "No" ; equivalent to rFalse
 rOn = "On" ; equivalent to rTrue
 rOff = "Off" ; equivalent to rFalse
  
 identifier = *(unicodeAlpha / %x5F.00 / "$") 
 string = sQuote *(unicodeAlpha / %x5F.00 / "$")  sQuote
 hexConstant = "0" ("x" /  "X") hexDigit hexDigit
 number = *unicodeNum [ ("e" / "E") ["+" "-"] *unicodeNum ]
  
 hexDigit = %x30-39 / %x41-46 / %x61-66
 unicodeAlpha = ( %x41-5A %x00 ) / ( %x61-7A %x00 ) 
 unicodeNum = %x30-39 %x00