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 @? {}
- returnsT?
, zero or one, fails if more than one is found.T @ {}
- returnsT
, exactly one, fails if zero or more than one is found.T @* {}
- returnslist<T>
, zero or more.T @+ {}
- returnslist<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 bylast_name
first, then byfirst_name
user @* {} ( @sort_desc .year_of_birth, @sort .last_name )
- sort byyear_of_birth
descending, then bylast_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
orlist<T>
.
Examples
user @ { ... }
- returnsuser
user @? { ... }
- returnsuser?
user @* { ... }
- returnslist<user>
user @+ { ... }
- returnslist<user>
(user, company) @ { ... }
- returns a tuple(user,company)
(user, company) @* { ... }
- returnslist<(user,company)>
user @ { ... } ( .name )
- returnstext
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
: returnslist<T>
.@set T
: returnsset<T>
.@map (K, V)
: accepts a two-element tuple, returnsmap<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 )
- returnslist<(integer,list<text>)>
data @* {} ( @group .k, @set .v )
- returnslist<(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
@*
returnslist<(integer,list<text>)>
. - Second at-expression
@
convertslist<(integer,list<text>)>
tomap<integer,list<text>>
.
Notes
- Expression data
@ {} ( @list .v )
returnslist<text>
, but data@* {} ( @list .v )
returnslist<list<text>>
, which always has one element of typelist<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
.