OQL基本语法

在OQL中当前只支持一种语句: SELECT

SELECT语句的语法如下:

SELECT
    [output_specification FROM]
    class_reference
    [class_joined]
    [WHERE expression]
    [UNION oql_query]
  • output_specification 表示想要检索对象的类,省略时默认为class_reference。它由一组类名组成时,第一个别名将确定所期望的主类是什么。

  • class_reference 表示想要去查询的对象的类。

  • class_joined 表示您想要连接的一系列类,以限制所选对象的集合(请记住,不用对具体列进行操作)。

  • where_condition 是一个表达式,也就是SQL SELECT语句中的查询条件。

  • 在iTop 2.2.0之后 oql_query 是另一个完整的OQL查询语句,它的查询结果将被添加到SELECT的结果中。

output_specification

class_alias [, output_specification]

  • class_alias 指的是在class_reference 或者 class_joined字句中声明的别名(class AS Alias_name)

class_reference

class_name [AS class_alias]

  • class_name 指的是需要查询的类名。

  • class_alias 指的是引用类的别名,可以在WHERE字句中使用。

class_name or class_alias

name | `name`

下列情况中必须使用倒引号:

  • 类的名字和保留字冲突 (比如: JOIN…),

  • 类的名字包含不希望出现的字符。

class_joined

JOIN class_reference
  ON class_left.external_key join_operator class_right.id
  • class_reference 指在JOIN左边或者右边的类,取决于数据模型和给定的外键。

  • class_right.id 虽然不可能是其他值,但是必须指定:它引用了另一个对象指向的对象。如果给定了的话,Class_right是一个别名。

  • class_left.external_key 指的是指向 class_right.id的类的对应属性。很多情况下,外键属性可以猜到,但是无论如何引用必须被清楚地指定。在2.2.0中最新:这个特殊的属性可以是 'AttributeObjectKey'类型。

  • join_operator  连接操作符可以是 =, BELOW, BELOW STRICT, ABOVE 或者 ABOVE STRICT。BELOW,ABOVE 只能用于连接 AttributeHierarchicalKey类型的属性。

表达式

literal
 | function
 | attribute
 | expression operator expression
 | (expression)
  • literal 是一个字符串(用单引号或者双引号括起来),或者一个数字(只支持整数). 版本2.0.1新引入: 十六进制符号支持无符号整数最大 2^64 (如: 0x2F6C585B5FEACF7A 不带引号)。

  • function  是上述动词之一,参数使用逗号分隔的表达式列表。

  • attribute 是一个定义在数据模型的对象属性的引用, class_ref.attribute_code – 使用倒引号解决保留字符或空白字符冲突是很必要的。

  • operator 下列任何的二元运算符。

  • expression 使用圆括号可以处理运算符之间的优先级别。

二元运算符

二元运算符接受两个操作数:左右各一。

运算符描述
AND逻辑与
OR逻辑或
/除法运
=等于
>=大于等于
>大于
<=小于等于
<小于
-减法
<>不等于
LIKE简单模式匹配
NOT LIKE简单模式非匹配
IN列表
NOT IN不在列表中
&

在2.0.1中新增:位操作符”and“。这个操作符不同于“逻辑”操作符“AND“,因为它对每个数字的位进行操作。

|

在2.0.1中新增:位操作符”or“。这个操作符不同于“逻辑”操作符“OR“,因为它对每个数字的位进行操作。

^

在2.0.1中新增:位操作符”xor“。异或。

<<

在2.0.1中新增:位操作符左移。

>>在2.0.1中新增:位操作符右移。
REGEXP正则表达式
MATCHES

在2.0.1中新增:全文匹配字符串。此操作符仅用于TagSet类型的属性。支持的语法是属性匹配'code1 code2'

通配符

在编写OQL时,可以根据当前用户及其关联的联系人指定占位符。

占位符内容
:current_contact→id

定义一个OQL“查询菜单”或者“dashlet”,返回用户依赖数据

:current_contact→attribute

联系人类的属性

:current_user→attribute

用户类的属性

例如,标准“帮助台”菜单“分配给我的请求(作为代理)”

SELECT UserRequest WHERE agent_id = :current_contact->id AND `status` NOT IN ('closed', 'resolved')

函数

verb(expression[,expression [,expression...]...] ...])
  • verb 是下面功能列表中的一个函数

在SQL中,所有函数实际上都映射到SQL对应的函数。换句话说,最终执行的SQL查询将使用相同的函数。

因此,这些函数的规范(参数的个数和类型、返回值)类似于底层数据库服务器的规范。任何限制或副作用都与数据库引擎的版本有关。

下面提供的超链接将指向mySQL 5.0的参考文档,是推荐的标准数据库引擎(用于OQL处理)文档。

  • 函数名称区分大小写。在当前OQL实现中,必须大写,虽然MySQL不限制。

  • 到目前为止,同义词还没有实现(对于给定的函数只有一个名称;例如:OQL实现了DAY,而mySQL实现DAY,并且把DAYOFMONTH作为函数的别名)

函数名称描述示例
IFIf/else 结构IF(a=b, 'equals', 'differs')
ELT返回索引的字符串ELT(index, 'string1', 'string2', 'string3')
COALESCE

返回第一个非空参数

COALESCE(field1, field2, 'Undefined')
ISNULL ISNULL(field1)
CONCAT

返回连接后的字符串

CONCAT(firstname, ' ', lastname)
SUBSTR返回指定的子字符串SUBSTR('abcdef', 2, 3)
TRIM删除开头和结尾的空格TRIM(' blah ')
DATE

提取日期或日期时间表达式的日期部分

DATE()
DATE_FORMAT

指定格式日期

DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y')
CURRENT_DATE返回当前日期CURRENT_DATE()
NOW返回当前日期和时间NOW()
TIME

提取表达式的时间部分

TIME()
TO_DAYS

返回日期参数转换后的天数

TO_DAYS('2009-05-01')
FROM_DAYS

转换一个日期数为日期

FROM_DAYS(12345)
YEAR

返回日期的年

YEAR(DATE())
MONTH

返回日期的月

MONTH(DATE())
DAY

返回日期的日 (0-31)

DAY(DATE())
DATE_ADD

添加时间值(间隔)到日期。参见下面允许的间隔单位

DATE_ADD(NOW(), INTERVAL 1 HOUR)
DATE_SUB

缩短时间值(间隔)到日期。参见下面允许的间隔单位

DATE_SUB(NOW(), INTERVAL 5 MINUTE)
ROUND返回参数X的四舍五入的数值。ROUND(12.356, 2)
FLOOR

返回不大于参数的最大整数值

FLOOR(12.356)
INET_ATON

返回IP地址的数值

INET_ATON('15.15.121.12')
INET_NTOA

返回数值的IP地址

INET_NTOA(1231654)

DATE_ADD和DATE_SUB函数当前支持的时间间隔单位列表,MySQL中允许的值的子集。

OQL接受:

时间间隔单位
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND

原文:https://www.itophub.io/wiki/page?id=2_6_0%3Aoql%3Aoql_syntax


OQL Statements Syntax

There is currently only one type of statement in OQL: SELECT

The syntax of the SELECT statement is the following:

SELECT
    [output_specification FROM]
    class_reference
    [class_joined]
    [WHERE expression]
    [UNION oql_query]
  • output_specification indicates 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_reference indicates the class of objects that you want to query.

  • class_joined indicates 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_condition is an expression, very close to what could be found in an SQL SELECT statement.

  • Only since iTop 2.2.0 oql_query is another complete OQL query which results will be added to the results of the SELECT

output_specification

class_alias [, output_specification]

  • class_alias indicates an alias that is declared in the class_reference or class_joined clauses.

class_reference

class_name [AS class_alias]

  • class_name indicates the class of objects that you want to select.

  • class_alias indicates 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.

class_joined

JOIN class_reference
  ON class_left.external_key join_operator class_right.id
  • class_reference refers 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.id has 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_key indicates 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_operator can be either =, BELOW, BELOW STRICT, ABOVE or ABOVE STRICT. BELOW and ABOVE can be used only in conjunction with attributes of typeAttributeHierarchicalKey.

expression

literal
 | function
 | attribute
 | expression operator expression
 | (expression)
  • literal is 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).

  • function is one of the verbs listed above, the arguments are a coma separated list of expressions

  • attribute is 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.

  • operator is any of the binary operators listed below.

  • expression may be enclosed in parenthesis to cope with operators precedence.

Binary operators

Binary operators accepts two operands: one at the left and one at the right.

OperatorDescription
ANDLogical AND
ORLogical OR
/Division operator
 Equality operator
>=Greater than or equal operator
>Greater than operator
<=Less than or equal operator
<Less than operator
-Substraction operator
!=, <>Non-equality operator
LIKESimple pattern matching
NOT LIKENegation of simple pattern matching
INList operator
NOT INNegation 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
REGEXPRegular expression
MATCHESNew in 2.6.0 Fulltext match against a string. This operator only works with attributes of type TagSet. The supported syntax is attribute MATCHES 'code1 code2'

Wildcard

There are possibilities while writing your OQL to specify placeholders based on the current user and its contact associated.

PlaceholderComment
:current_contact→idto define an OQL 'search menu' or a 'dashlet' which will return user dependent data
:current_contact→attributewhere 'attribute' is any code attribute of the Contact class
:current_user→attributewhere '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')

function

verb(expression[,expression [,expression...]...] ...])
  • verb is 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)

Function nameDescriptionExamples
IFIf/else construct 
IF(a=b, 'equals', 'differs')
ELTReturn string at index number 
ELT(index, 'string1', 'string2', 'string3')
COALESCEReturn the first non-NULL argument 
COALESCE(field1, field2, 'Undefined')
ISNULL  
ISNULL(field1)
CONCATReturn concatenated string 
CONCAT(firstname, ' ', lastname)
SUBSTRReturn the substring as specified 
SUBSTR('abcdef', 2, 3)
TRIMRemove leading and trailing spaces 
TRIM('  blah  ')
DATEExtract the date part of a date or datetime expression 
DATE()
DATE_FORMATFormat date as specified 
DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y')
CURRENT_DATEReturn the current date 
CURRENT_DATE()
NOWReturn the current date and time 
NOW()
TIMEExtract the time portion of the expression passed 
TIME()
TO_DAYSReturn the date argument converted to days 
TO_DAYS('2009-05-01')
FROM_DAYSConvert a day number to a date 
FROM_DAYS(12345)
YEARReturn the year from the date passed 
YEAR(DATE())
MONTHReturn the month from the date passed 
MONTH(DATE())
DAYReturn the day of the month (0-31) 
DAY(DATE())
DATE_ADDAdd time values (intervals) to a date value. See allowed interval units below 
DATE_ADD(NOW(), INTERVAL 1 HOUR)
DATE_SUBSubstract time values (intervals) from a date value. See allowed interval units below 
DATE_SUB(NOW(), INTERVAL 5 MINUTE)
ROUNDRound the argument 
ROUND(12.356, 2)
FLOORReturn the largest integer value not greater than the argument 
FLOOR(12.356)
INET_ATONReturn the numeric value of an IP address 
INET_ATON('15.15.121.12')
INET_NTOAReturn the IP address from a numeric value 
INET_NTOA(1231654)

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
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
标签:
由 superadmin 在 2020/08/25, 16:22 创建
    

需要帮助?

如果您需要有关XWiki的帮助,可以联系:

深圳市艾拓先锋企业管理咨询有限公司