Development Documentation

Architecture Overview

Key Concepts

Data

Objects

Relationships

Attributes

Folders

Keys

Model

Objects Types

Relationship Types

Attribute Types

Attribute Definitions

Packages

Administration

Users and Teams

ACLs

Profiles

Repository

Files and Vaults

Business Objects

BO Implementation

BO Inheritance

Transactions

Queries

Query Architecture

Query Specifications

Find Queries

Expand Queries

Join Queries

Services

Service Configuration

Service Builder

Logging

Unit Testing

XML

ice.NET Studio

ice.NET SDK

ice.NET Web SDK

Tutorials

Reference Documentation

Knowledge Base

 

ice.NET > Developers > Development Documentation > Queries > Query Specifications

ice.NET Query Specifications

Built-In Queries

This section deals with the common aspects of the three built-in types of queries, the Find, Expand and Join Queries:

• Fields
• Constraints
• SQL Constraints
• Subqueries

Fields

An important aspect of the query specifications is the collection of fields. Fields define the structure of the query result. Every field specifies a column of the result table. The field specifications have several properties:

Property	Values	Description
Name	[string value]	The name of the field. Names of fields and constraints must be unique within a query specification.
ItemType	Object, Relationship	Specifies if the field refers to the object or the relationship of the current context. The context is defined by the paths and steps in Expand and Join Queries and by the additional steps definition of fields. In some contexts (e.g. Find Query field without additional steps) only Object is a valid value.
FieldType	Id, Name, Description, TypeId, TypeName, TypeDisplayName, CreatedBy, CreatedOn, ChangedBy, ChangedOn, LockedBy, LockedOn, FolderId, FolderName, Key, Attribute, Object	Defines which property of the specified item should be taken as the field value. If Attribute is selected, the AttrDefDeclTypeName and AttrDefName properties must be specified. If Object is selected, an complete IObject instance is provided as the field value.
AttrDefDeclTypeName	[ObjType name], [RelType name]	Only valid if the FieldType is set to Attribute. Defines the declaring type name of the attribute definition.
AttrDefName	[AttrDef name]	Only valid if the FieldType is set to Attribute. Defines the (local) name of the attribute definition.
SortPriority	[integer value]	Optional. If set to a non-negative value, the result set is ordered by this field. If multiple fields have a SortPriority set, the field with the lowest value has the highest priority.
SortOrder	Ascending, Descending	Defines if the sort order is ascending or descending. Only valid if a SortPriority is set for this field.
Aggregation	Count, Sum, Average, Min, Max	Optional. Defines if a aggregation function should be applied to the field. If any grouping or aggregation is set, all fields must have an aggreation or GroupPriority property set.
GroupPriority	[integer value]	Optional. A non-negative value defines that the result should be grouped by this field. If multiple fields have a GroupPriority set, the field with the lowest value has the highest grouping priority. If any grouping or aggregation is set, all fields must have an aggreation or GroupPriority property set.
AddStepRelTypeName	[RelType name]	Optional XML property. Use this property to indicate that the field value should not be taken from the current context object but from an object or relationship that is connected by a single relationship step. If multiple steps are necessary to reach the field value, use the <AddSteps> container tag. This property is only valid together with AddStepRelDirection.
AddStepRelDirection	Forward, Reverse	Optional XML property. Use this property to indicate that the field value should not be taken from the current context object but from an object or relationship that is connected by a single relationship step. If multiple steps are necessary to reach the field value, use the <AddSteps> container tag. This property is only valid together with AddStepRelTypeName.
AssignmentIndicator	true, false	Optional. Use this property to inform the BO Builder that an additional flag should be provided in the generated result structure that indicates if the value has been assigned from a database field or if it is a default value. E.g. a DateTime attribute fields gets the result value of DateTime.MinDate if the context object's attribute value has not been assigned. This property is only valid for XML-based query specifications.

Constraints

Constraints define selection criteria and therefore restrict the result set. The constraint structure is similar to the structure of fields with an important addition: Constraints can have a comparison rule definition, the ConstraintType, that specifies how to match the constraint field value against another value or set of values. Only if this match is valid in the sense of the ConstraintType, the result candidate row qualifies as an actual result. The constraint specifications has several properties:

Property	Values	Description
Name	[string value]	The name of the constraint. Names of fields and constraints must be unique within a query specification.
ItemType	Object, Relationship	Specifies if the constraint refers to the object or the relationship of the current context. The context is defined by the paths and steps in Expand and Join Queries and by the additional steps definition of fields. In some contexts (e.g. Find Query constraint without additional steps) only Object is a valid value.
FieldType	Id, Name, Description, TypeId, TypeName, TypeDisplayName, CreatedBy, CreatedOn, ChangedBy, ChangedOn, LockedBy, LockedOn, FolderId, FolderName, Key, Attribute, Object	Defines which property of the specified item should be taken as the constraint field value. If Attribute is selected, the AttrDefDeclTypeName and AttrDefName properties must be specified. The value Object is only valid for fields, not for constraints.
AttrDefDeclTypeName	[ObjType name], [RelType name]	Only valid if the FieldType is set to Attribute. Defines the declaring type name of the attribute definition.
AttrDefName	[AttrDef name]	Only valid if the FieldType is set to Attribute. Defines the (local) name of the attribute definition.
AddStepRelTypeName	[RelType name]	Optional XML property. Use this property to indicate that the field value should not be taken from the current context object but from an object or relationship that is connected by a single relationship step. If multiple steps are necessary to reach the field value, use the <AddSteps> container tag. This property is only valid together with AddStepRelDirection.
AddStepRelDirection	Forward, Reverse	Optional XML property. Use this property to indicate that the field value should not be taken from the current context object but from an object or relationship that is connected by a single relationship step. If multiple steps are necessary to reach the field value, use the <AddSteps> container tag. This property is only valid together with AddStepRelTypeName.
ConstraintType	Symbol, Equal, NotEqual, Like, Between, Less, LessEqual, Greater, GreaterEqual, In, NotIn, InSet, TypeOf	Specifies how to match the constraint field value against another value or set of values. Symbol means that there is no comparison at all, it is sufficient that the field value is assigned at all. In and NotIn compare against the result of a subquery. InSet compares against an array of parameter values. TypeOf requires an ObjType ID as a parameter and checks for type compatibility (including inheritance). Between requires two parameter values. All other constraint types compare against a single parameter value.
Parameter0Name, Parameter1Name	[string value]	Specifies the name of the parameter(s) to provide the actual constraint values. Depending on the ConstraintType one, two or no parameter name definitions are required.

Additional Steps

Field and constraint definitions can use additional steps to indicate that the field value should not be taken from the current context object but from an object or relationship that is connected by one or multiple relationship steps. Each step is specified by a relationship type name and a direction.

The following example shows how to apply additional steps to field defintions. The additional steps syntax for constraints is identical.

<!-- Field definition without additional steps -->
<Field Name="ToDate" ItemType="Object" FieldType="Attribute" AttrDefName="ToDate" AttrDefDeclTypeName="PDTec.ICR.Booking" />

<!-- Field definition with single additional step -->
<Field Name="VehicleRegisterNumber" ItemType="Object" FieldType="Attribute" AttrDefName="RegisterNumber" AttrDefDeclTypeName="PDTec.ICR.Vehicle"
       AddStepRelTypeName="PDTec.ICR.BookedVehicle" AddStepRelDirection="Forward" />

<!-- Field definition with multiple additional steps -->
<Field Name="VehicleModelName" ItemType="Object" FieldType="Name">
    <AddSteps>
        <AddStep RelTypeName="PDTec.ICR.BookedVehicle" RelDirection="Forward" />
        <AddStep RelTypeName="PDTec.ICR.VehicleModel" RelDirection="Forward" />
    </AddSteps>
</Field>

Field Types

Field types specify which property or attribute of an item should be used as a field value or for comparison in a constraint. The following table describes all possible field types:

FieldType	Description
Id	The object or relationship ID
Name	The object name, only valid for ItemType = Object
Description	The object or relationship description
TypeId	The object type ID or relationship type ID
TypeName	The object type name or relationship type name
TypeDisplayName	The object type display name or relationship type display name
CreatedBy, ChangedBy	The name of the user that created or changed the object or relationship
CreatedOn, ChangedOn	The timestamp when the object or relationship was created or changed
LockedBy	The user that has locked the object, only valid for ItemType = Object
LockedOn	The timestamp when the object was locked, only valid for ItemType = Object
FolderId	The folder ID of the object, only valid for ItemType = Object
FolderName	The folder name of the object, only valid for ItemType = Object
Key	A key assigned to the object, only valid for ItemType = Object CAUTION: Objects can have more than one key. Therefore, defining a Key field or constraint can increase the number of result rows.
Attribute	An attribute value of the object or relationship. The AttrDefDeclTypeName and AttrDefName properties must be specified.
Object	A reference to an IObject instance, only valid for ItemType = Object, not valid for constraint definitions

Constraint Types

Constraint types specify how to compare the field values with the query parameters. They also define how many parameter values are required for the constraint (none, one, two or an array of values):

ConstraintType	Description
Equal, NotEqual	Compare for equality (=) or inequality (!=)
Like	Match for patterns with wildcards (*, ?), only valid for string-valued fields
Between	Requires two parameter values, checks if field value is between
Less, LessEqual, Greater, GreaterEqual	Compares values with <, <=, >, >= operators
In, NotIn	Evaluates if the field value is (not) in the result set of a subquery. This constraint type requires a subquery that has exactly one result field of a compatible field type. Requires no parameter values.
InSet	Evaluates if the field value is in the set of values provided as a parameter. The parameter value must be an array of a compatible type.
TypeOf	Only valid if ItemType = Object and FieldType = TypeId. Evaluates if the object type specified by the field value is subtype (or exactly of the type) of the object type specified by the parameter value. The parameter value must be an object type ID.
Symbol	No parameter value is required. The existence of the field value (e.g. attribute assignment) is sufficent. Typically this constraint type is choosen to use the constraint name as a symbol in an SQL constraint expression.

SQL Constraints

The constraints of a query provide a convenient way to express conditions for results in a structured, object-oriented, database implementation-independent way. However, sometimes it is useful to be able to evaluate an SQL expression directly in the context of the query.

In the following example we define a query that finds all inconsistent booking objects (where the begin of the renting period is later than the end of the period). To do this, we define two symbol constraints for the FromDate/ToDate attributes and compare both resulting values using an SQL expression ({FromDate} > {ToDate}):

<FindQuery Name="InvalidBookings" ObjTypeName="PDTec.ICR.Booking" IsExactType="false" Range="Global">
    <Field Name="BookingId" ItemType="Object" FieldType="Id"  />
    <Constraint Name="FromDate" ItemType="Object" FieldType="Attribute" AttrDefName="FromDate" AttrDefDeclTypeName="PDTec.ICR.Booking" ConstrainType="Symbol" />
    <Constraint Name="ToDate" ItemType="Object" FieldType="Attribute" AttrDefName="ToDate" AttrDefDeclTypeName="PDTec.ICR.Booking" ConstrainType="Symbol" />
    <SqlConstraint SqlExpression="{FromDate} > {ToDate}" />
</FindQuery>        

Subqueries

Subqueries provide the possibility for constraints not to compare field values against parameter values but against results from other queries. This can be expressed by the ConstraintType values In and NotIn. The result field (only a single field is allowed!) must be compatible to the constraint field type to be properly compared.

In the following example we check for available vehicles i.e. vehicles that are not in the set of vehicles that have been booked for a specific period of time:

<FindQuery Name="FindAvailableVehicles" ObjTypeName="PDTec.ICR.Vehicle" IsExactType="true" Range="Folder">
    <Field Name="VehicleId" ItemType="Object" FieldType="Id" /> 
    <Field Name="RegisterNumber" ItemType="Object" FieldType="Attribute" AttrDefName="RegisterNumber" AttrDefDeclTypeName="PDTec.ICR.Vehicle" /> 
    <Constraint Name="VehicleIdNotInBooked" ItemType="Object" FieldType="Id" ConstraintType="NotIn">
        <FindQuery Name="FindBookedVehicles" ObjTypeName="PDTec.ICR.Booking" IsExactType="true" Range="Folder" CheckAuthorization="false">
            <Field Name="BookedVehicleId" ItemType="Object" FieldType="Id" AddStepRelTypeName="PDTec.ICR.BookedVehicle" AddStepRelDirection="Forward" /> 
            <Constraint Parameter0Name="fromDate" ItemType="Object" FieldType="Attribute" AttrDefName="ToDate" AttrDefDeclTypeName="PDTec.ICR.Booking" ConstraintType="Greater" /> 
            <Constraint Parameter0Name="toDate" ItemType="Object" FieldType="Attribute" AttrDefName="FromDate" AttrDefDeclTypeName="PDTec.ICR.Booking" ConstraintType="Less" /> 
        </FindQuery>
    </Constraint>
</FindQuery>    

All three types of built-in queries (Find, Expand, Join) can be used as subqueries of all In and NotIn constraints.

Permissions

It is possible to explicitly restrict the query results to objects that a specific party is allowed to read/write/administrate. To specify this authorization constraint, add to attributes to the query root element:

Attribute	Values	Description
CheckAuthorization	true, false	Specifies if the results should be restricted by a permission check
AuthorizationTarget	Folder, Object	Specifies if the permission should be checked against the target objects ACL or against its parent folder.

If the permission check is enabled, two parameter values must be provided at runtime:

Parameter Value	Description
UserName	The name of the user who must have permissions on the target object/folder
MinAuthLevel	The permission level that must be reached to qualify as a result

The permission check takes the memberships of the user into account when calculating the permission level. The key feature of the query permission check is that the check is included in the actual compiled SQL query and does not require to iterate on the result candidates and check for permissions individually.