Overview

Rell provides several built-in functions for database operations. Rell allows both the comma notation and the and notation for defining records. However, they are not completely equivalent.

The comma notation allows for shortcut expressions with just a variable name. The keys of the record are automatically set to the variable names used in the expression, and the corresponding values are the values of the variables.

On the other hand, the and notation requires complete expressions for each key-value pair in the record. This notation allows for more complex expressions that involve logical operators and comparisons.

For example, in the following code:

val name = 'Bill';
val company = 'Microsoft';
return user @ { name, company };

The record being returned has two keys: name and company. The values of these keys are set to the values of the name and company variables, respectively. This shorthand is a convenient way to create a record with simple key-value pairs, but it's important to note that it may not be suitable for more complex expressions.

The and notation expression should be complete as follows:

return user @ { .name == name and .company == company };

@ operator

You can use the at operator, to retrieve database records. The general syntax consists of five parts, some of which you can omit: <from> <cardinality> { <where> } [<what>] [limit N]

Simplest form

user @ { .name == 'Bob' }

Cardinality

Specifies whether the expression must return one or many objects:

• T @? {} - returns T?, zero or one, fails if more than one is found.
• T @ {} - returns T, exactly one, fails if zero or more than one is found.
• T @* {} - returns list<T>, zero or more.
• T @+ {} - returns list<T>, one or more, fails if none found.

From part

You declare the from-part before @. It specifies the entity type that's retrieved.

Simple (one entity)

user @* { .name == 'Bob' }

Complex (one or more entities)

(user, company) @* { user.name == 'Bob' and company.name == 'Microsoft' and user.xyz == company.xyz }

Specifying entity aliases

(u: user) @* { u.name == 'Bob' }

(u: user, c: company) @* { u.name == 'Bob' and c.name == 'Microsoft' and u.xyz == c.xyz }

Where part

You declare the where-part after @. It specifies the entity type that's retrieved based on attributes. You can filter the records by zero or more comma-separated expressions using entity attributes, local variables, or system functions.

• user @* {} - returns all users.
• user @ { .name == 'Bill', .company == 'Microsoft' } - returns a specific user (all conditions must match).
• Can access attributes of an entity with a dot, e. g. .name or with an entity name or alias, user.name.

Entity attributes are also matched implicitly by name or type.

val ms = company @ { .name == 'Microsoft' };
val name = 'Bill';
return user @ { name, ms };

Explanation: The first where-expression is the local variable name, there is an attribute called name in the entity user. The second expression is ms, there is no such attribute, but the type of the local variable ms is company, and there is an attribute of type company in user.

What part

You declare the what-part after the where-part to specify the record attributes for retrieval.

• user @ { .name == 'Bob' } ( .company.name ) - returns a single value (name of the user's company).
• user @ { .name == 'Bob' } ( .company.name, .company.address ) - returns a tuple of two values.

Sorting

You can sort tuples by using the @sort (ascending) and @sort_desc (descending) annotations.

• user @* {} ( @sort .last_name, @sort .first_name ) - sort by last_name first, then by first_name
• user @* {} ( @sort_desc .year_of_birth, @sort .last_name ) - sort by year_of_birth descending, then by last_name ascending

Result tuple fields

Returned tuples can have named fields.

• user @* {} ( x = .company.name, y = .company.address, z = .year_of_birth ) - returns a tuple with named fields (x, y, z)
• user @* {} ( @sort x = .last_name, @sort_desc y = .year_of_birth ) - can combine field names with sorting

When field names aren't specified explicitly, you can infer them implicitly from the attribute name:

val u = user @ { ... } ( .first_name, .last_name, age = 2018 - .year_of_birth );
print(u.first_name, u.last_name, u.age);

By default, if a field name isn't specified and the expression is a single name (an attribute of an entity), then you can use that name as a tuple field name:

val u = user @ { ... } ( .first_name, .last_name );
// Result is a tuple (first_name: text, last_name: text).

To have a tuple field without a name, use _ as field name:

val u = user @ { ... } ( _ = .first_name, _ = .last_name );
// Result is a tuple (text, text).

To exclude a field from the result tuple, use @omit annotation:

val us = user @* {} ( .last_name, @omit .first_name ) ;
// Result is list<text>, since first_name is excluded, so there is only one expression to return

The possibility to exclude a field is useful, for example, when one needs to sort by some expression but doesn't want to include that expression into the result tuple:

val sorted_users = user @* {} ( _ = .first_name, _ = .last_name, @omit @sort .date_of_birth );
// Returns list<(text,text)>.

Tail part

You declare the tail part after the what-part and filter the returned tuples. You can do this by limiting and skipping records.

Limiting records

• user @* { .company == 'Microsoft' } limit 10 - Returns at most 10 objects.

The cardinality test happens before the limit applies, so the following code can't fail with the "more than one object" error:

• val u: user = user @ { .company == 'Microsoft' } limit 1; - Returns one record.

Skipping records

• user @* {}(@sort .company) offset 10- Skips the first ten records in the table, and can use it alongside limit to specify a subset within the found records.
• people @* {}(@sort .age) offset 10 limit 20 - Returns the eleventh youngest up to the thirtieth youngest person.

Result type

The Result type depends on the cardinality, from- and what-parts.

• From- and what-parts define the type of a single record, T.
• Cardinality defines the type of the @-operator result: T?, T or list<T>.

Examples

• user @ { ... } - returns user
• user @? { ... } - returns user?
• user @* { ... } - returns list<user>
• user @+ { ... } - returns list<user>
• (user, company) @ { ... } - returns a tuple (user,company)
• (user, company) @* { ... } - returns list<(user,company)>
• user @ { ... } ( .name ) - returns text
• user @ { ... } ( .first_name, .last_name ) - returns (first_name:text,last_name:text)
• (user, company) @ { ... } ( user.first_name, user.last_name, company ) - returns (text,text,company)

Nested at operators

You can use a nested at-operator in any expression inside another at-operator:

user @* { .company == company @ { .name == 'Microsoft' } } ( ... )

This is equivalent to:

val c = company @ { .name == 'Microsoft' };
user @* { .company == c } ( ... )

Inner Join

Each entity in an at-expression can have its own where-expression, which is translated into SQL JOIN ON clause. So instead of

(u: user, c: contract) @* { c.user == u } ( u, c )

one can write:

(
    u: user,
    c: contract @* { c.user == u }
) @* {} ( u, c )

The first expression is translated into SQL like:

SELECT U.rowid, C.rowid FROM user U, contract C WHERE C.user = U.rowid

while the second one is translated into:

SELECT U.rowid, C.rowid FROM user U JOIN contract C ON C.user = U.rowid

The two statements do the same, as inner joins are used by default. But the new syntax makes a difference when using outer joins.

Outer Join

With the new join syntax, one can use @outer annotation to make an Outer Join:

(
    u: user,
    @outer c: contract @* { c.user == u }
) @* {} ( u, c )

This expression is translated into SQL like:

SELECT U.rowid, C.rowid FROM user U LEFT OUTER JOIN contract C ON C.user = U.rowid

If there is no matching "contract" for a "user", a null "contract" record is added to the result set. In contrast, for a default (Inner) Join, the "user" record wouldn't be added to the result set if there is no matching "contract".

When Outer Join is used, the type of "c" is "contract?", not "contract". This means its attributes can't be accessed directly, and one shall use null-handling operators, like "?." or "?:".

Aggregate functions and grouping

You can use @group, @min, @max, and @sum to express equivalents to SQL statements GROUP BY, MIN, MAX, and SUM.

• To calculate an aggregated value (min/max/sum) use @min, @max or @sum.
• annotate with @group to group by an attribute or an expression
• To calculate the count, use @sum 1.

Example entity

entity city {
            name;
            country: text;
            population: integer;
}

Examples

To calculate the number of cities in every country and group it by country, one can write:

city @*{} ( @group .country, @sum 1 )

The result is a list of tuples (text, integer) - (country name, number of cities).

You can calculate the total population of all cities in each country by:

city @*{} ( @group .country, @sum 1, @sum .population )

You can annotate more than one expression with @group to group by multiple values.

Collection annotations

Similarly to the aggregation annotations @sum, @min, and @max, there are now annotations to collect values into collections: @list, @set, and @map.

• @list T: returns list<T>.
• @set T: returns set<T>.
• @map (K, V): accepts a two-element tuple, returns map<K, V>, fails on key conflict.

Usage

entity data { k: integer; v: text; }

data @ {} ( @list .v )           // returns list<text>
data @ {} ( @set .v )            // returns set<text>
data @ {} ( @map (.k, .v) )      // returns map<integer, text>

With grouping

• data @* {} ( @group .k, @list .v ) - returns list<(integer,list<text>)>
• data @* {} ( @group .k, @set .v ) - returns list<(integer,set<text>)>

To group values into a map of lists, combine two at-expressions:

data @* {} ( @group .k, @list .v ) @ {} ( @map $ ) - returns map<integer,list<text>>

• First at-expression @* returns list<(integer,list<text>)>.
• Second at-expression @ converts list<(integer,list<text>)> to map<integer,list<text>>.

Notes

• Expression data @ {} ( @list .v ) returns list<text>, but data @* {} ( @list .v ) returns list<list<text>>, which always has one element of type list<text>.
• When annotation @list, @set or @map is used in a database at-expression, all values (which match the where part) are read from the database without grouping and aggregation, and then grouped and aggregated in memory.

Sorting

You can use @sort with or without the @omit statement to sort.

city @*{} ( @group .country, @omit @sort_desc @sum 1 )

In this example, you sort the countries by the number of cities in descending order. You can see that because the @omit statement is present, hence the number of cities isn't displayed in the result.

Field names

You can specify tuple field names after annotations:

city @*{} ( @group .country, @sum city_count = 1, @sum total_population = .population )

$ operator

The $ operator is used to denote the current item within an at-expression. If the item type includes attributes, you can access them using attribute notation. For instance, if the item has an attribute named name, you can access it as follows: $.name.