Database Operations

At-Operator

Simplest form:

user @ { .name == 'Bob' }

General syntax:

<from> <cardinality> { <where> } [<what>] [limit N]

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

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

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)

Attributes of an entity can be accessed with a dot, e. g. .name or with an entity name or alias, user.name.

Entity attributes can also be 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

Simple example:

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

Specifying names of result tuple fields:

user @* {} ( x = .company.name, y = .company.address, z = .year_of_birth ) - returns a tuple with named fields (x, y, z)

Sorting:

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.

Field names can be combined with sorting:

user @* {} ( @sort x = .last_name, @sort_desc y = .year_of_birth )

When field names are not specified explicitly, they can be inferred implicitly from 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 is not specified and the expression is a single name (e. g. an attribute of an entity), that name is used 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 does not 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

Limiting records:

user @* { .company == 'Microsoft' } limit 10

Returns at most 10 objects. The limit is applied before the cardinality check, so the following code can’t fail with “more than one object” error:

val u: user = user @ { .company == 'Microsoft' } limit 1;

Skipping records:

user @* {}(@sort .company) offset 10

Skips the first 10 records in the table, can be used alongside limit to specify a subset within the found records.

people @* {}(@sort .age) offset 10 limit 20

Will for example return the 11th youngest up to the 30th youngest person.

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

A nested at-operator can be used in any expression inside of another at-operator:

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

This is equivalent to:

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

Equivalents to SQL statements GROUP BY, MIN, MAX and SUM can be expressed by using @group, @min, @max and @sum.

To calculate an aggregated value (min/max/sum), annotate an expression with @min, @max or @sum. Grouping by an attribute (or an expression) is done by annotating it with @group. To calculate count, use @sum 1.

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

To calculate the number of cities in every country and grouping 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)

Calculating the total population of all cities in each country can be expressed as:

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

Note that more than one expression can be annotated with @group in order to group by multiple values.

Sorting can be done by using @sort with or without the @omit statement.

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

In the example above, the countries are sorted by the number of cities in a descending order. Note that the @omit statement is included, hence the number of cities is not displayed in the result.

Tuple field names can be specified after annotations:

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

Create Statement

Must specify all attributes that don’t have default values.

create user(name = 'Bob', company = company @ { .name == 'Amazon' });

No need to specify attribute name if it can be matched by name or type:

val name = 'Bob';
create user(name, company @ { company.name == 'Amazon' });

Can use the created object:

val new_company = create company(name = 'Amazon');
val new_user = create user(name = 'Bob', new_company);
print('Created new user:', new_user);

Update Statement

Operators @, @?, @*, @+ are used to specify cardinality, like for the at-operator. If the number of updated records does not match the cardinality, a run-time error occurs.

update user @ { .name == 'Bob' } ( company = 'Microsoft' );             // exactly one
update user @? { .name == 'Bob' } ( deleted = true );                   // zero or one
update user @* { .company.name == 'Bad Company' } ( salary -= 1000 );   // any number

Can change only mutable attributes.

Entity attributes can be matched implicitly by name or type:

val company = 'Microsoft';
update user @ { .name == 'Bob' } ( company );

Using multiple entities with aliases. The first entity is the one being updated. Other entities can be used in the where-part:

update (u: user, c: company) @ { u.xyz == c.xyz, u.name == 'Bob', c.name == 'Google' } ( city = 'Seattle' );

Can specify an arbitrary expression returning a entity, a nullable entity or a collection of entities:

val u = user @? { .name == 'Bob' };
update u ( salary += 5000 );

A single attribute of can be modified using a regular assignment syntax:

val u = user @ { .name == 'Bob' };
u.salary += 5000;

Delete Statement

Operators @, @?, @*, @+ are used to specify cardinality, like for the at-operator. If the number of deleted records does not match the cardinality, a run-time error occurs.

delete user @ { .name == 'Bob' };                    // exactly one
delete user @? { .name == 'Bob' };                   // zero or one
delete user @* { .company.name == 'Bad Company' };   // any number

Using multiple entities. Similar to update, only the object(s) of the first entity will be deleted:

delete (u: user, c: company) @ { u.xyz == c.xyz, u.name == 'Bob', c.name == 'Google' };

Can specify an arbitrary expression returning an entity, a nullable entity or a collection of entities:

val u = user @? { .name == 'Bob' };
delete u;