(r17) XeoPrimerXeoQL < WebXEO

WebXEO Web>XeoPrimer>XeoPrimerXeoQL (revision 17) (raw view)~~EditAttach~~
-- Main.PedroRio - 27 Dec 2010
---+ XeoQL - XEO Query Language ( also known as BOQL )

XEO Query Language (XEOQL a.k.a BOQL - Business Object Query Language) is a language designed to query instances of XEO Object Models. It's very similar to SQL and has many of the same constructors. SQL is designed to return records from a relational database, while XEOQL is designed to return Object Model instances from its datasource (typically a relational database).

Using XEO's Java API to issue a XEOQL expression against the datasource will produce a [[WebXEO.XeoPrimerJavaAPIBoObjectList][boObjectList]] instance which can be used to iterate through all elements that are the result of the query. The methods available in [[WebXEO.XeoPrimerJavaAPIObjectListManager][ObjectListManager]] allow you to create a <em>boObjectList </em>instance from a XEOQL query. The syntax for a XEOQL expression is the following:

*SELECT [MY] [USING user/pwd] [attlist FROM] objname [WHERE whereStatement (AND | OR whereStament)* ][ORDER BY attName[ASC|DESC] (,attName [ASC|DESC])*]*

Although the syntax may seem complicated usually you'll not use every option in the syntax. Let's start with the simplest of queries, selecting all instances of a given Object Model, for example the system Object Model <em>Ebo_Perf </em>(which represents a user).
<verbatim>select Ebo_Perf</verbatim>

Executing this query in the XEOQL Console (available in every [[WebXEO.XeoMainAdmin][XEO Application's Administration Viewer]]) provides the following results:

<img width="800" alt="Simple XEOQL expression" src="http://wiki.itds.pt/pub/WebITDS/XeoPrimerXeoQL/XEOQL-Console.png" title="Simple XEOQL expression" height="263" />

__Figure XEOQL.1 - Executing a simple query in the XEOQL console.__

In the bottom of figure XEOQL.1 you can see the two instances of Ebo_Perf (a default XEO application has the SYSUSER and ROBOT user) that are the result of this query. The results reflect a default XEO application. See in the following section how to select instances which are related to a current instance (through collection or object attributes). It's possible to select instances which are related with other instances (through a object or collection attribute), see the "dot syntax" section bellow for more information.
---+++ The dot syntax

An Object Model A can define an 1:N relation with another Object Model through a collection attribute (named, for example, attCollection) or a 1:1 relation through an object attribute. It's possible (using XEOQL) to retrieve instances associated with other instances by using a dot syntax. For example: The [[WebXEO.XeoPrimerLibraryIntroduction][LIB_Book Object Model]] (created for the XEO Library) defines a <em>categories </em>attribute (collection attribute) and a <em>publisher </em>attribute (object attribute). To select the publishers that is associated with a book the following query would be needed:
<verbatim>select LIB_Book.publisher</verbatim>

The previous query will return a list of all LIB_Publisher instances that are related to a LIB_Book instance (if one wanted only the publisher of a given book, you would need a where clause, which will be discussed in the next section). To select all LIB_BookCategory instances associated to any given book, the following query could be used
<verbatim>select LIB_Book.categories</verbatim>

You can go further down the hierarchy, like selecting all publisher nucleus from publishers that are related to book instance with the following query:
<verbatim>select LIB_Book.publisher.nucleus</verbatim>

Dot syntax *can be used continually as long as the attributes* being selected are of type *object* or *collection*.
---+++ Where Clause

In order to restrict the results of a XEOQL expression, and just like the SQL counterpart, XEOQL provides a WHERE clause. The where clause allows to restrict selection by using logical operators, attribute references, functions, arithmetic and literals. The syntax for the where clause is the following:
<verbatim>WHERE whereStatement (AND | OR whereStament)*</verbatim>

The _whereStatament_ expression encapsulates all the possible combinations for valid expressions which can be created using the following elements:
   * Attributes - Reference to an attribute to be compared with some value (ex. Literals)
   * Literals - Literal values to be used for comparison
   * Functions
   * Arithmetic Operator - Allow to make calculations using +,-,*,/ (i.e sum, subtraction, multiplication and divison)
   * Aggregate Functions - Sum(), Avg(), etc...
   * Relational operators - ( =, &gt;, &lt;, &gt;=, &lt;=, like, is null, etc...)
---++++++ Where Clause - Attributes (and the Dot Syntax)

In a XEOQL expression is possible to retrict the selected instances by making to only instances whose attributes match a certain condition are returned. Recall from the [[WebXEO.XeoPrimerLibraryIntroduction#objModels1][XEO Library introduction]] that the LIB_Book Model had a _title_ attribute (a textual attribute). In order to select all book instances which have a particular title ("Da Vinci Code", for example), we would use the following XEOQL expression.
<verbatim>select LIB_Book where title = 'Da Vinci Code'</verbatim>

One could also explicitly refer the Object Model being selected in the expression, like the following:
<verbatim>select LIB_Book where LIB_Book.title = 'Da Vinci Code'</verbatim>

One can use attributes from instances with which the first is related, for example to select all books which were published by "Wrox".
<verbatim>select LIB_Book WHERE LIB_Book.publisher.name = 'Wrox'</verbatim>

or
<verbatim>select LIB_Book WHERE publisher.name = 'Wrox'</verbatim>

Beware that when selecting object instances through an attribute (i.e. selecting LIB_Publisher instances by using LIB_Book.publisher) the context for the where clause is that of the of the first Object Model. So if you make the following (wrong) query to select all publishers with a name similar to "Wrox" that have a book published it will fail with an error:
<verbatim>select LIB_Book.publisher where name Like '%Wrox%'</verbatim>

The error generated from the query is the following:
<verbatim>QLParser:XEO.QL:null Base Error:Not a valid attribute
select LIB_book.publisher where<strong>[name]</strong> Like '%Wrox%'</verbatim>

The error occurs because, although the publisher (of the LIB_Publisher Object Model) has an attribute _name_, in BOQL the context for the where clause is always that of the first Object Model selected, in this case, LIB_Book. In order to make this query correct you would have to refer to the publisher name by prefixing it with "publisher." so that the XEOQL Parser knows that the "name" attribute is from the publisher and not from the book. The correct query would be the following:
<verbatim>select LIB_Book.publisher where publisher.name like '%Wrox%'</verbatim>

Another important issue is when comparing to an attribute that is relation. The following two queries are equivalent:
<verbatim>select LIB_Book.publisher where publisher.boui = 111111
select LIB_Book.publisher where publisher = 111111</verbatim>

When comparing to a relation attribute you're comparing with its BOUI attribute.

___
---++++++ Where Clause - Literals

In a where clause, often you want to compare certain attribute values with literal values. Such as knowing if a given attributes is bigger than five (5), or a certain attribute represents the date of today. The following table summarizes how to use the various types of literals available.
| *Literal Type* | *Usage* | *Notes* |
| String | 'String' | |
| Number | 12345 | |
| Duration | 12345 | |
| Date | to_date('5/5/2010','DD-MM-YYYY') | When comparing dates, it's required to use the to_date function, which will converted the date to the internal representation of the database |
---++++++ Where Clause - Functions

It's possible to user SQL functions directly from whithin a XEOQL expression. The XEOQL parser, however, does not make any type of validation when using these, it passes them directly to the database as SQL.
---++++++ Where Clause - Arithmetic Operators

When refering to a number attribute in a where clause, you can use arithmetic operators build XEOQL expressions, for instance the following query (use of parenthesis to arrange priorities is permited):
<verbatim>select LIB_Movement where (fine * 10) / 100 > 200</verbatim>

The four arithmetic operators are available (sum, subtraction, mutiplication and division)
---++++++ Where Clause - Aggregate Functions

It's possible to use aggregate functions in a where clause. Aggregate functions are only usabled with attributes of type number (as they perform calculations on the values of those attributes). There are five aggregate functions that can be used.
   * AVG - Average of values
   * SUM - The sum of values
   * COUNT - Count the number of values
   * MIN - Minimum value of the group
   * MAX - Maximum value of the group

__BUGGY: DO NOT USE: Generates wrong SQL__
---++++++ Where Clause - Relational Operators

To compare attributes in a where clause with other values, you'll need the relational operators in XEOQL. There are the following operators:
| *Operator* | *Description* | *Example* |
| = | Equality operator | 1 = 1 |
| &gt; | Bigger than operator | salary &gt; 1000 |
| &lt; | Less than operator | value &lt; 100 |
| &lt;= | Bigger or equal than operator | salary &gt;= 200 |
| &gt;= | Less or equal than operator | value &lt;= 100 |
| &lt;&gt; | Different operator (not equal) | salary &lt;&gt; 1000 (salary not equal to 1000) |
| ! | Different operator (alias to &lt;&gt;) | salary ! 1000 |
| Like | Allows to compare strings with wildcards | <p>name like "XEO%"</p> <p>name not like "XEO %"</p> |
| Startswith | Restricts attributes that start with a given character | <p>name startswith "A"</p> <p>name startswith "#"</p> |
| In | Retricts arguments to the ones in the following subqueries | <p>boui in (select Object where value &gt; 3)</p> <p>boui not (select Object where value &gt; 3)</p> |
| Is Null | Evaluates if the argument does have a value | <p>Name is null</p> <p>Name is not null</p> |
| Not | Negates a condition | Name not null |

<strong><em>___</em></strong>
---+++ <strong>Contains Clause<em><br /></em></strong>

The contains clause allows you to search for instances of XEO Models which have a certain keyword indexed by Ebo_TextIndex (Full text search indexer). If you know you have instances of Object Model "Foo" that have a certain attribute with the word "bar" you can search for those instances by using the following query:
<verbatim>select Foo where Contains('bar')</verbatim>

You could also use wildcard matching*, such as:
<verbatim>select Foo where Contains('bar%')</verbatim>

which would match Foo instances which have words started with "bar"*.

_*Note: Wildcard matching is database dependent. The "contains" clause will be converted to the underlying database's function to make full text searches. In Oracle databases the '%' wildcard can be used in XEOQL expressions (with the contains clause), while Mysql uses the '*' wildcard in its "MATCH" function. When using the contains clause, be sure to read the documentation of your database about wildcard text searching._
---+++ Order by Clause

It's possible to order the results of a XEOQL expression by a given attribute or multiple attributes (in ascending or descending order). For example to retrieve all books instances and ordering by title you could use the following expression.
<pre><verbatim>select LIB_Book order by title ASC</verbatim>
</pre>

To select all books instances ordered by the publisher's name and then by title, the following query would do.
<verbatim>select LIB_Book order by publisher.name,title ASC</verbatim>

__
---+++ Parameters

When creating a XEOQL expression you can use parameters to bind values. Just like SQL bindings, you can create a XEOQL expression like the following:
<pre><verbatim>select LIB_Book where title = ?
</verbatim>
</pre>

And later, bind the parameter to a value. To use parameters, the are methods in the [[WebXEO.XeoPrimerJavaAPIObjectListManager][ObjectListManager ]]which allow you to pass a XEOQL expression and an array of Objects (the values for the parameters) and will execute your expression with the defined bindings. For example.
<verbatim>EboContext ctx = //get an EboContext
boObjectList results = ObjectListManager.list(ctx, "select LIB_Book where title = ? and ISBN = ?", new Object[] {"Da Vinci Code", 11344444});</verbatim>

__
---+++ The MY / USING expressions

The MY expression is used to declare *that only instances which were created by the user executing the query should be returned*.

The USING USER/PASS expression is used to perform queries as another user (using the given username and password, hence the name). If one issues the query:
<verbatim>SELECT USING SYSUSER/ABC Ebo_Perf</verbatim>

The results will be all Ebo_Perf instances that the SYSUSER user would see when executing the _select Ebo_Perf_ query.
---+++ Special Constants

There are a number of special constants which can be used when creating a XEOQL expression.The following table summarizes them.
| *Constant* | *Description* |
| CTX_PERFORMER_BOUI | The boui of the user which is executing the query, when you use this in a XEOQL expression it will be automatically replaced by the user's BOUI. |

Table XEOQL.1 - Special constants in XEOQL

_
---+++ Sample queries

<em>__</em>

=%ICON{todo}%=
<div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow: hidden;" id="_mcePaste"> =%ICON{todo}%= </div>
Attachments
Topic attachments
I	Attachment	Action	Size	Date	Who	Comment
png	XEOQL-Console.png	manage	13.4 K	2011-01-04 - 17:52	PedroRio	XEOQL Console - Simple Query
Topic revision: r17 - 2011-04-04 - NicolauGrosskopf
No permission to view TWiki.WebTopBar
No permission to view TWiki.WebBottomBar