(r6) XeoPrimerXeoQL < WebXEO

WebXEO Web>XeoPrimer>XeoPrimerXeoQL (revision 6) (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 (tipically a relational database).

Using XEO's Java API to issue a XEOQL expression against the datasource will produce a [[XeoPrimerJavaAPIBoObjectList][boObjectList]] instance which can be used to iterate through all elements that are the result of the query. The methods available in [[XeoPrimerJavaAPIObjectListManager][ObjectListManager]] allow you to create a boObjectList 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 XEO Application's Administration Viewer, which will be discussed later) 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 instance (though 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 to Object Model through a collection attribute (named 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 LIB_Book Object Model (created for the XEO Library) defines a categories attribute (collection attribute) and a publisher attribute (object attribute). To select all publishers that are associated with a book the following query would be needed:
<verbatim>select LIB_Book.publisher</verbatim>

The preivous 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 [[XeoPrimerLibraryIntroduction#objModels1][XEO Library introduction]] that the LIB_Book Model had a _title_ attribute (a text 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 "HarperCollins".
<verbatim>select BIBLIO_book WHERE BIBLIO_book.publisher.name = 'HarperCollins'</verbatim>

or
<verbatim>select BIBLIO_book WHERE publisher.name = 'HarperCollins'</verbatim>

aa

aa
---++++++ Where Clause - Literals

aaa
---++++++ Where Clause - Functions

aaa
---++++++ Where Clause - Arithmetic Operators

aaa
---++++++ Where Clause - Aggregate Functions

aaa
---++++++ Where Clause - Relational Operators

zzzz

zzz

---+++ Order by Clause

aaa

---+++ The MY / USING expressions

The MY expression is used to declared *that only instances which were created by the user executing the query should be returned*. In reallity, what's being done is make a where clause with SYSUSER = USER_ID????

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 |
| | |
| | |

Table XEOQL.1 - Special constants in XEOQL

a

a

a

a

a
---+++ Sample queries

aaa
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: r6 - 2011-01-05 - PedroRio
No permission to view TWiki.WebTopBar
No permission to view TWiki.WebBottomBar