Skip to main content

The Local Database

sa.engine includes a built-in main-memory local database. The main use of the local database is for storing models, parameters and other meta-data. It is an object-oriented database where objects of different types can be stored and queried. The object-orientation allows the definition of complex meta-data sometimes called ontologies.

In this section we show how the local database can be used as a regular main-memory database.

In sa.engine, a database is a collection of objects. Each object belongs to one or several types. Functions define mappings between objects of different types. The schema is a part of the database that represents type and function definitions.

Suppose we are going to store data about departments and their properties. The first thing to do is to define a new type named Department by this statement:

create type Department

Then we define the property name of departments by the statement:

create function name (Department) -> Charstring as stored

The function name is an example of a stored function that is stored in the database.

Now we can populate the database by creating objects of type Department:

create Department (name) instances :toys ("Toys"),
:food ("Food"),
:tools ("Tools")

We have here created three objects and assigned them to session variables prefixed with ':': :toys, :food, and :tools. It is practical to use such session variables to temporarily hold database objects. However, notice that the session variables are only temporarily present during the current session with sa.engine and will not be saved in the database.

In addition to creating three objects the above statement assigns corresponding values to the function (or property) name.

Session variables can be used in queries. For example, to get the value of the function name for argument :toys we can make the following query being a function call:

name(:toys)

The following query finds the names of the objects of type Department:

select name(d)
from Department d

We may filter the result by matching the name against a pattern using the function like:

select name(d)
from Department d
where like(name(d),"T*")

Here the two department names "Toys" and "Tools"are matching the pattern "T*" (i.e. a * matches any string).

It is often practical to get the object representing a department, given its name. For that we define the function theDept(Charstring name)->Department as the inverse of name(Department)->Charstring by evaluating:

create function theDept(Charstring name) -> Department
as inverse of name

Session variables can be assigned to values using the set statement:

set :toys  = theDept("Toys");

set :food = theDept("Food");

set :tools = theDept("Tools")

The set statement can also be used for assigning values to functions for given arguments. To add another department named Shoes to the database we can issue the statement:

create Department instances :shoes;

set name(:shoes) = "Shoes"

Here the first statement creates a new object of type Department and assigns it to the session variable :shoes, while the second statement assigns the value "Shoes" to name(:shoes).

Now, let's create another type Person with properties name, dept, and income:

create type Person;
create function name(Person) -> Charstring as stored;
create function dept(Person) -> Department as stored;
create function income(person) -> Number as stored

We can now create eight persons with this statement:

create Person(name, income, dept) instances
:p1 ("Maja", 100, :toys),
:p2 ("Bill", 200, :food),
:p3 ("Bull", 300, :tools),
:p4 ("Pelle", 400, :toys),
:p5 ("Mons", 500, :food),
:p6 ("Olle", 500, :toys),
:p7 ("Birgitta",600, :tools),
:p8 ("Murre", 700, :toys)

Here, the session variables :p1, :p2,... are bound to the new objects of type Person. We use the previously defined session variables :toys, :food, and :tools to link the created objects to their departments.

Let's try some other queries:

Get the names and incomes of all persons in the database:

select name(p), income(p) from Person p

Get the names of persons working in the department named 'Toys':

select name(p)
from Person p
where name(dept(p)) = "Toys"

Get the names and incomes of all persons ordered decreasingly by income:

select name(p), income(p)
from Person p
order by income(p) desc

Get the two highest paid persons:

select name(p), income(p)
from Person p
order by income(p) desc
limit 2

Get the names and incomes of all persons earning more than 400 ordered by their names:

select name(p), income(p)
from Person p
where income(p) > 400
order by name(p)

How many persons are there in the database?

count(select p from Person p)

What is the total sum of the incomes of all persons?

sum(select income(p) from Person p)

What is average income of all persons?

mean(select income(p) from Person p)

What is the median income?

median(select income(p) from Person p)

Let's create another type Account with properties id, owner, and balance:

create type Account;
create function id(Account) -> Number as stored;
create function owner(Account) -> Person as stored;
create function balance(Account) -> Number as stored

Create some accounts and assign them to the persons owning the accounts:

create Account(id, owner, balance) instances
(1,:p1, 150),
(2,:p1, 200),
(3,:p2, 400),
(4,:p2, 85),
(5,:p2, 70),
(6,:p3, 10),
(7,:p5, 500),
(8,:p6, 75),
(9,:p6, 95),
(10,:p7,105),
(11,:p8, 90)

Here the session variables connect accounts to persons.

Let's make some more queries over accounts, persons, and departments.

Get the balances of the accounts of the person named 'Bill':

select balance(a)
from Account a
where name(owner(a)) = "Bill"

Get the names and account balances of all persons in the 'Toys' department:

select name(p), balance(a)
from Person p, Account a
where name(dept(p)) = "Toys"
and owner(a) = p

Count the number of accounts per person in 'Toys' department:

select name(p), count(a)
from Person p, Account a
where name(dept(p)) = "Toys"
and owner(a) = p
group by name(p)

Compute the total balances of accounts per person in 'Toys' department:

select name(p), sum(balance(a))
from Person p, Account a
where name(dept(p)) = "Toys"
and owner(a) = p
group by name(p)

Get the total balances for all persons in the database:

select name(p), sum(balance(a))
from Person p, Account a
where owner(a) = p
group by name(p)

Get decreasingly ordered total balances of each person in the database:

select name(p), sum(balance(a))
from Person p, Account a
where owner(a) = p
group by name(p)
order by sum(balance(a)) desc

What are the lowest incomes in each department?

select name(d), min(income(p))
from Department d, Person p
where dept(p) = d
group by name(d)
order by min(income(p))

Get the two departments with highest average incomes:

select name(d), mean(income(p))
from Department d, Person p
where dept(p) = d
group by name(d)
order by mean(income(p)) desc
limit 2

For each person get the name, department name, income, and total balance, ordered by total balance decreasingly:

select name(p), name(d), income(p), sum(balance(a))
from Department d, Person p, Account a
where owner(a) = p
and dept(p) = d
group by name(p), name(d), income(p)
order by sum(balance(a)) desc

User defined functions defined as a query are called derived functions.

The following derived function finds the incomes higher than a given threshold. It is a derived function returning a bag (set with duplicates) of numbers:

create function higherIncomes (Number thres) -> Bag of Number
as select income(p)
from Person p
where income(p)>thres

Call function highIncomes():

higherIncomes(500)

The function highIncomesPers() returns both names and incomes of persons earning more than a threshold thres:

create function higherIncomePers (Number thres)
-> Bag of (Charstring nm,
Number inc)
as select name(p), inc
from Number inc, Person p
where income(p)=inc
and inc > thres

Get the persons earning more than 100:

higherIncomePers(500)

This function call returns the k highest paid persons:

create function highestIncomePers(Number k)
-> Bag of (Charstring name, Number inccome)
as select name(p), income(p)
from Person p
order by income(p) desc
limit k

Get the two highest earners:

highestIncomePers(2)

In the next tutorial we develop a fusion model that analyzes local shake detections on several edge devices with accelerometers.