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
.