On this page:
4.1 Syntax forms
4.2 The join clause
4.3 The where clause
4.4 RQL examples

4 RQL: The Racquel Query Language

The RQL query language defines SQL-like S-expressions. The expressions are used to define selection criteria when loading data objects (using select-data-object or select-data-objects) or joining to other data objects (using joins). RQL-expressions are translated into database system-specific SQL.

Rather than naming a table in a query, as in an SQL, a class which maps to a table is named instead. For instance if the table "INVOICE" was mapped to the class invoice%, the name invoice% would be using the an RQL query. In addition, columns in RQL as identified using a pair with class name and field name. This the SQL table-column reference "INVOICE.CREATED" would be (invoice% created). A column name must be defined in RQL with it’s corresponding class, in order to be correctly mapped to the corresponding SQL.

RQL query parameters are represented using a question mark (?). Since the representation of parameter values can be database system specific, it is recommended that parameters be used rather than actual hard-coded values, as to ensure correct mapping of the value into the specific database system’s format.

4.1 Syntax forms

Below is the BNF for RQL expressions.

 

                

 ::= 

 

expression

 ::= 

join clause*

 

  |  

where clause*

 

join clause

 ::= 

( join type table name search condition )

 

join type

 ::= 

join

 

  |  

left-join

 

  |  

right-join

 

where clause

 ::= 

( where search condition )

 

search condition

 ::= 

boolean term

 

  |  

( or search condition boolean term )

 

boolean term

 ::= 

boolean factor

 

  |  

( and boolean term boolean factor )

 

boolean factor

 ::= 

( not boolean test )

 

boolean test

 ::= 

boolean primary

 

boolean primary

 ::= 

predicate

 

  |  

search condition

 

predicate

 ::= 

comparison predicate

 

  |  

between predicate

 

  |  

in predicate

 

  |  

like predicate

 

  |  

null predicate

 

between predicate

 ::= 

( between row value constructor row value constructor row value constructor )

 

in predicate

 ::= 

( in row value constructor in value list )

 

in value list

 ::= 

value expression+

 

like predicate

 ::= 

( like pattern )

4.2 The join clause

Join clauses can also included in select-data-object and select-data-objects functions. The join clauses must be defined before the where clause. There may be any number of join clauses, each expressing a join relationship, similar to an SQL join clause.

There are three types of joins: join, left-join, and right-join. The first is an inner join, the second is a left outer join, and the last is a right outer join.

A join clause may also be defined in a data-class declaration, but is expressed in a slightly different form (see data-class* above. For instance, the join clause, expresses the equivalent of the SQL-expression "JOIN PERSON ON PERSON.ID = ADDRESS.PERSON_ID".

(join person% (= (person% id) (address% person-id)))

4.3 The where clause

A where clause is used in select-data-object and select-data-objects. It follows the behavior of SQL-expressions, which can include AND, OR, =, IN, LIKE, etc., but are expressed as S-expressions. Thus the SQL-expression "ID = 1" would be coded as the S-expression "(= ID 1)".

Currently only a subset of SQL is supported. Subqueries and existence functions are not supported.

4.4 RQL examples

Here is an example of a simple join which returns all address for a person with the last name "O’Brien". A parameter is used so that the quote in the name is properly escaped in the generated SQL statement.
(select-data-object con address%
                    (join person% (= (person% person-id)
                                     (address% person-id)))
                    (where (= (person% last-name) ?)) "O'Brien")

Below is an example of an in clause which loads three albums by title.
(select-data-objects con album% (where (in title (make-list 3 '?)))
                     "Inflammable Material" "London Calling" "Ramones")

This in clause uses a list of ids to retrieve the account data objects:
(define account-ids '(5 23 17))
(select-data-objects con account% (where (in id account-ids)))

Because the #:print? keyword is true, the query will return the SQL that would be used to select the objects from the database. The ? is the RQL parameter placeholder and will be translated into the appropriate placeholder for the SQL dialect used by the connection, e.g. "$1".

(select-data-objects con address% #:print? #t (where (>= id ?)) min-id)

Below is an example of a manually defined data class map. This class implements interface my-interface ,%>. It also has a column x which is required to be specified when a new instance of the class is created. There is also a one-to-one join to an object of class object% where the id column of the object equals 1.
(define my-class% (data-class* object% (my-interface<%>)
                                   (table-name "test")
                                   (column [id #f ("id" "Id")]
                                           [name #f ("name" "Name")]
                                           [title #f ("title" "Title")])
                                   (init-column [x ("x" "X")])
                                   (join [object object%
                                          #:cardinality 'one-to-one
                                          (where (= id ?)) 1])
                                   (primary-key id)
                                   (define/public (test) (x + 1))
                                   (super-new)))

This creates an instance of the class above. Note that x must be specified.

(define obj (new test-class% [x 2]))

Generate a class book% from the table Book in the Library database, with joins and reverse joins.
(define book% (gen-data-class con "Book"
               #:schema-name "Library"
               #:generate-joins? #t #:generate-reverse-joins? #t))