OQL Statements Syntax
There is currently only one type of statement in OQL:
The syntax of the SELECT statement is the following:
SELECT [output_specification FROM] class_reference [class_joined] [WHERE expression] [UNION oql_query]
output_specificationindicates the class of objects that you want to retrieve, when omitted, it will default to class_reference. When it is made of a list of class aliases, then the first one determines what is the primary class expected.
class_referenceindicates the class of objects that you want to query.
class_joinedindicates a series of classes that you want to join, in order to restrict the set of selected objects (remember, it makes no sense to mention columns).
where_conditionis an expression, very close to what could be found in an SQL SELECT statement.
Only since iTop 2.2.0
oql_queryis another complete OQL query which results will be added to the results of the SELECT
class_alias [, output_specification]
class_aliasindicates an alias that is declared in the
class_name [AS class_alias]
class_nameindicates the class of objects that you want to select.
class_aliasindicates an alias that will be used to refer to the given class, in the expressions found into the WHERE clause.
class_name or class_alias
name | `name`
Backticks must be used in the following circumstances:
the name of the class is in conflict with a reserved word (example: JOIN…),
the name of the class contains undesirable characters.
JOIN class_reference ON class_left.external_key join_operator class_right.id
class_referencerefers either to the class on the left of the join or the right… depending on the data model and the given external key.
class_right.idhas to be specified though it cannot be something else: it refers to the object that is pointed to by the other one. Class_right is an alias if any has been given.
class_left.external_keyindicates which attribute from which class should be pointing to
class_right.id. In most cases, the external key attributes could be guessed, but the reference has to be specified explicitely anyway. New in 2.2.0: the specified attribute can be of the type 'AttributeObjectKey'.
join_operatorcan be either
ABOVE STRICT. BELOW and ABOVE can be used only in conjunction with attributes of type
literal | function | attribute | expression operator expression | (expression)
literalis either a string (enclosed with single or double quotes) or a number (only integers are supported). New in 2.0.1: hexadecimal notation is supported for unsigned integers up to 2^64 (example: 0x2F6C585B5FEACF7A without quotes).
functionis one of the verbs listed above, the arguments are a coma separated list of expressions
attributeis a reference to an object property as defined in the data model, in the form class_ref.attribute_code – use of backticks is necessary to solve conflict with reseverd words or white characters.
operatoris any of the binary operators listed below.
expressionmay be enclosed in parenthesis to cope with operators precedence.
Binary operators accepts two operands: one at the left and one at the right.
|>=||Greater than or equal operator|
|>||Greater than operator|
|<=||Less than or equal operator|
|<||Less than operator|
|!=, <>||Non-equality operator|
|LIKE||Simple pattern matching|
|NOT LIKE||Negation of simple pattern matching|
|NOT IN||Negation of list operator|
|&||New in 2.0.1 Bitwise operator “and”. This operator is different from the “logical” operator “AND” since it operates on every bit of each number.|
||||New in 2.0.1 Bitwise operator “or”. This operator is different from the “logical” operator “OR” since it operates on every bit of each number.|
|^||New in 2.0.1 Bitwise operator “xor”.|
|<<||New in 2.0.1 Bitwise left shift|
|>>||New in 2.0.1 Bitwise right shift|
|MATCHES||New in 2.6.0 Fulltext match
against a string. This operator only works with attributes of type
TagSet. The supported syntax is
There are possibilities while writing your OQL to specify placeholders based on the current user and its contact associated. The following placeholders can be to define an OQL 'search menu' or a 'dashlet' which will return user dependent data.
|:current_contact->id :current_contact_id||This placeholder returns the
||where 'attribute' is any code attribute of the Contact class|
||where 'attribute' is any code attribute of the User class|
Example from the standard “Helpdesk” menu “Requests assigned to me (as Agent)”
SELECT UserRequest WHERE agent_id = :current_contact->id AND `status` NOT IN ('closed', 'resolved')
verb(expression[,expression [,expression...]...] ...])
verbis one the functions listed below
All functions are actually mapped to their equivalent in SQL. In other words, the same functions will be used in the resulting SQL queries that will be finally executed.
Therefore, the specification of those functions (number and type of arguments, returned values) is similar to the specification of the underlying database server. Any limitation or side-effect, will be related to the version of the database engine.
The hyperlinks provided hereafter will direct you to the reference documentation of mySQL 5.0, which is the standard recommended database engine (used for qualification of the OQL processor).
Function names are case-sensitive. They have to be uppercase in the current OQL implementation, though MySQL is less restrictive.
So far, no synomym has been implemented (There is just one single name for a given function ; example: OQL implements DAY whereas mySQL implements DAY and DAYOFMONTH as an alias to the same function)
IF(a=b, 'equals', 'differs')
|ELT||Return string at index number||
ELT(index, 'string1', 'string2', 'string3')
|COALESCE||Return the first non-NULL argument||
COALESCE(field1, field2, 'Undefined')
|CONCAT||Return concatenated string||
CONCAT(firstname, ' ', lastname)
|SUBSTR||Return the substring as specified||
SUBSTR('abcdef', 2, 3)
|TRIM||Remove leading and trailing spaces||
TRIM(' blah ')
|DATE||Extract the date part of a date or datetime expression||
|DATE_FORMAT||Format date as specified||
DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y')
|CURRENT_DATE||Return the current date||
|NOW||Return the current date and time||
|TIME||Extract the time portion of the expression passed||
|TO_DAYS||Return the date argument converted to days elapsed since the first day of year 0||
|FROM_DAYS||Convert a day number to a date||
|YEAR||Return the year from the date passed||
|MONTH||Return the month from the date passed||
|DAY||Return the day of the month (0-31)||
|DATE_ADD||Add time values (intervals) to a date value. See allowed interval units below||
DATE_ADD(NOW(), INTERVAL 1 HOUR)
|DATE_SUB||Substract time values (intervals) from a date value. See allowed interval units below||
DATE_SUB(NOW(), INTERVAL 5 MINUTE)
|ROUND||Round the argument||
|FLOOR||Return the largest integer value not greater than the argument||
|INET_ATON||Return the numeric value of an IP address||
|INET_NTOA||Return the IP address from a numeric value||
The list of time interval units currently supported by the functions DATE_ADD and DATE_SUB, is a subset of the values allowed in MySQL.
OQL does accept:
|Time interval units|