▼Racquel:   An Object/  Relational Mapper for Racket
►4 RQL:   The Racquel Query Language
On this page:
4.1 Syntax forms
4.2 The join clause
4.3 The where clause
4.4 RQL examples
  top  ← prev  up  next → 

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

  top  ← prev  up  next →