iTop中的OQL的语法格式
iTop中OQL是什么?今天小编给大家分享的是iTop中的OQL的详细介绍,相信大部分人都不太了解,为了让大家更加了解,小编给大家总结了以下内容,话不多说,一起往下看吧。
成都创新互联基于成都重庆香港及美国等地区分布式IDC机房数据中心构建的电信大带宽,联通大带宽,移动大带宽,多线BGP大带宽租用,是为众多客户提供专业服务器托管报价,主机托管价格性价比高,为金融证券行业多线服务器托管,ai人工智能服务器托管提供bgp线路100M独享,G口带宽及机柜租用的专业成都idc公司。
iTop的OQL(object query language)只支持一种类型,即查询语句,也就是SQL查询中的Select语句。OQL用于过滤或查询,可在iTop中用于邮件通知、审计规则等。OQL的返回值限于一个对象或者一组iTop对象。iTop将OQL最终翻译成对应的SQL Select语句,所以可以将OQL看做是SQL Select的一个子集,功能上不如SQL语句那么灵活,但足以应付iTop中各种操作。注意,OQL中的SQL保留字都必须大写,语法大小写敏感,所以每个对象的大小写必须准确。
OQL语法格式非常简单,形式如下,
SELECT [output_specification FROM] class_reference [class_joined] [WHERE expression] [UNION oql_query]
其中各项参数解释如下,
1. [output_specification FROM] , 此为可选项,当使用了多个对象或者别名时才需要用到。
如:
SELECT p1 FROM Person as p1, 此处p1为Person的别名。
或者类似于:
SELECT p1,t1 FROM Person JOIN lnkPersonToTeam AS t1 ON t1.person_id=p1.id
2. class_reference, 查询的对象名,如上例中的Person。当不使用别名时,可以直接使用如下形式获取数据
>SELECT Person
则iTop自动从Person对象表中获取数据
如果iTop对象名或要使用的别名与SQL保留字冲突,则对象名或别名必须用``包装起来。如,
SELECT `Union`。
3. [class_joined],如上面的例子,描述需要关联的其他对象。这个等于SQL语句的JOIN。具体格式为JOIN object2 ON object1.attribute1 [操作符] object2.attirbute1,其中操作符如下,
=, BELOW, BELOW STRICT, ABOVE or ABOVE STRICT.
其中BELOW和ABOVE相关的几个操作符用于当对象1和对象2有从属关系时。比如iTop中的服务和子服务。
4. [WHERE expression],与SQL的WHERE语句一样的功能,expression支持的语法和函数下面再进行描述。
5. [UNION oql_query], 与SQL的UNION的语句一样的功能。其中oql_query只要符合OQL语法即可。但此处的OQL返回的对象应该与主语句一致。
WHERE表达式语法,
WHERE表达式可以由几个部分组成,即字符串、操作符、函数,最终形成一个布尔结果值,实现数据筛选。这个跟SQL的WHERE子句功能一致。
操作符表如下,
Operator | Description |
---|---|
AND | Logical AND |
OR | Logical 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 |
LIKE | Simple pattern matching |
NOT LIKE | Negation of simple pattern matching |
IN | List operator |
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 |
REGEXP | Regular expression |
MATCHES | New 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' |
函数列表如下,
Function name | Description | Examples |
---|---|---|
COALESCE | Return the first non-NULL argument | COALESCE(field1, field2, 'Undefined') |
CONCAT | Return concatenated string | CONCAT(firstname, ' ', lastname) |
CURRENT_DATE | Return the current date | CURRENT_DATE() |
DATE | Extract the date part of a date or datetime expression | DATE() |
DATE_ADD | Add time values (intervals) to a date value. See allowed interval units below | DATE_ADD(NOW(), INTERVAL 1 HOUR) |
DATE_FORMAT | Format date as specified | DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
DATE_SUB | Substract time values (intervals) from a date value. See allowed interval units below | DATE_SUB(NOW(), INTERVAL 5 MINUTE) |
DAY | Return the day of the month (0-31) | DAY(DATE()) |
ELT | Return string at index number | ELT(index, 'string1', 'string2', 'string3') |
FLOOR | Return the largest integer value not greater than the argument | FLOOR(12.356) |
FROM_DAYS | Convert a day number to a date | FROM_DAYS(12345) |
IF | If/else construct | IF(a=b, 'equals', 'differs') |
INET_ATON | Return the numeric value of an IP address | INET_ATON('15.15.121.12') |
INET_NTOA | Return the IP address from a numeric value | INET_NTOA(1231654) |
ISNULL | ISNULL(field1) | |
MONTH | Return the month from the date passed | MONTH(DATE()) |
NOW | Return the current date and time | NOW() |
ROUND | Round the argument | ROUND(12.356, 2) |
SUBSTR | Return the substring as specified | SUBSTR('abcdef', 2, 3) |
TIME | Extract the time portion of the expression passed | TIME() |
TO_DAYS | Return the date argument converted to days | TO_DAYS('2009-05-01') |
TRIM | Remove leading and trailing spaces | TRIM(' blah ') |
YEAR | Return the year from the date passed | YEAR(DATE()) |
其他说明,
current_contact和current_user可以作为两个与当前登录用户相关的通用变量,从而可以在表达式中进行使用,使用格式如下,
:current_contact→attribute where 'attribute' is any code attribute of the Contact class
:current_user→attribute where 'attribute' is any code attribute of the User class
如:
SELECT UserRequest WHERE agent_id = :current_contact->id AND status
NOT IN ('closed', 'resolved')
看完上述内容,你们对iTop中的OQL大概了解了吗?如果想了解更多相关文章内容,欢迎关注创新互联行业资讯频道,感谢各位的阅读!
当前题目:iTop中的OQL的语法格式
文章链接:http://myzitong.com/article/jdjpcd.html