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 found.
• T @ {} - returns T, exactly one, fails if zero or more than one 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 } ( ... )

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.

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.