4 RQL: The Racquel Query Language

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

1	Data Class Mapping
2	Automated Data Class Generation
3	Data Object Persistence and Restoration
4	RQL: The Racquel Query Language
5	Data Object Serialization

4.1	Syntax forms
4.2	The join clause
4.3	The where clause
4.4	RQL examples