# Basic Constructs

The basic building blocks of the OSQL query language are described here.

## Statements

*Statements* instruct sa.engine to perform various kinds of operations on the database, for example:

`1+2`

sin(2)+3

quit

When using the sa.engine Console REPL, OSQL statements are always terminated by a semicolon (;). The `;`

is not needed when using sa.studio.

## Identifiers

*Identifiers* represent names of OSQL variables, functions, and types. Examples:

` MySalary`

sin

Integer

x

x1234

x1234_b

Notice that OSQL identifiers are NOT case sensitive; i.e. they are always internally capitalized. By contrast OSQL reserved keywords are always written with *lower case* letters, for example.

`create function ...`

## Variables

*Variables* are of two kinds: *local variables* or *session variables*.

*Local variables* are identifiers for data values inside OSQL queries and functions. *Local variables* must be declared in function signatures (see Defining Functions), in from clauses (see Queries). Notice that variables are **not** case sensitive.

Examples:

` my_variable`

MyVariable2

*Session variables* prefixed with `:`

hold only **temporary** results during interactive sessions. Session variables **cannot** be referenced in function bodies and they are **not** stored in the database. Their lifespan is the current transaction only. Their purpose is to hold temporary values in scripts and database interactions.

Examples:

` :my_session_variable`

:MySessionVariable2

The user can declare a session variable to be of a particular type by the *session variable declare statemen*, for example:

` declare Integer :i, Real :x3`

Session variables can be assigned either by the `into`

clause of the select queries or by the session variable assignment statement *set*.

Examples

` set :x3 = 2.3`

set :i = 2 + sqrt(:x3)

*Constants* can be integers, reals, strings, time stamps, booleans, or null.

Examples:

` 123`

-123

1.2

-1.0

2.3E2

-2.4e-21

true

false

null

*Boolean constants* represent logical values. The constant `false`

is equivalent to `null`

casted to type *Boolean*. The only legal boolean value that can be stored in the database is `true`

and a boolean value is regarded as false if it is not in the database (so called close world assumption).

Examples of *strings*:

` "A string"`

'A string'

'A string with "'

"A string with \" and '"

The enclosing string separators (`'`

or `"`

) for a string constant must be the same. If the string separator is `"`

then `\`

is the escape character inside the string, replacing the succeeding character. For example the string `'ab"\'`

can also be written as `"ab\"\\"`

, and the string `a'"b`

must be written as `"a'\"b"`

.

A *simple value* is either a constant or a variable reference.

Examples:

` :MySessionVariable`

MyLocalVariable

123

"Hello World"

## Comments

A *comment* enclosed with `/*`

and `*/`

can be placed anywhere in an OSQL statement outside identifiers, constants, strings, or variables.

## Expressions

*Expressions* are formulas expressed with the OSQL syntax that can be evaluated by the system to produce a *value*. Complex expressions can be built up in terms of other expression. Expressions are basic building blocks in all kinds of OSQL statements.

Examples:

` 1.23`

1+2

1<2 and 1>3

sqrt(:a) + 3 * :b

[1,2,3]

cast(:p as Student)

a[3]

sum(select income(p) from Person p) +10

The value of an expression is computed if the expression is entered to an sa.engine REPL. Example:

` 1+5*sqrt(6)`

Entering simple expressions followed by a semicolon is the simplest form of OSQL queries. Example:

` 1+sqrt(25)`

Notice that Boolean expressions, *predicates*, either return *true*, or nothing if the expression is not true. Example:

` 1<2 or 3<2`

=> true

1<2 and 3<2

=> nothing

## Collections

*Collections* represent sets of objects. OSQL supports three kinds of collections: bags, vectors, and key-value associations (records):

- A
`bag`

is a set where duplicates are allowed. - A
`vector`

is an ordered sequence of objects. - A
`record`

is an associative array of key-value pairs.

Collections are constructed by collection constructor expressions. Examples:

` bag(1,2,3)`

bag(1,:x+2)

[1,2,3]

[[1,2],[3,4]]

[1,name(:p),1+sqrt(:a)]

{"id":1,"name":"Kalle","age":32}

A common collection is *bags*, which are unordered sets of objects with duplicates allowed. The value of a query is usually a bag. When a query to an sa.engine REPL returns a bag as result the elements of the bag are printed on separate lines.

For example:

` select name(p) from Person p`

returns the bag:

` "Bill"`

"John"

"Ulla"

"Eva"

Bags can be explicitly created using the *bag-constr* syntax, for example:

` bag(1,2,2,3)`

*Vectors* are sequences of objects of any kind. Square brackets `[]`

enclose vector elements, For example, `set :v=[1,2,3]`

then `:v`

returns `[1,2,3]`

.

Vector element `vi`

can be access with the notation `v[i]`

, where the indexing `i`

is from 1 and up. For example `set :v=[1,2,3]`

then `:v[3]`

returns `3`

.

*Records* represent dynamic associations between keys and values. A record is a dynamic and associative array. Other commonly used terms for associative arrays are property lists, key-value pairs, dictionaries, or hash links. OSQL uses generalized JSON notation to construct records. For example the following expression assigns `:r`

to a record where the key (property) 'Greeting' field has the value 'Hello, I am Tore' and the key 'Email' has the value 'Tore.Andersson@it.uu.se':

` set :r= {'Greeting':'Hello, I am Tore','Email':'Tore.Andersson@it.uu.se'}`

A field `f`

of a record bound to a variable `r`

can be access with the notation `r[f]`

, for example:
`:r['Greeting' ]`

returns `Hello, I am Tore`

.