[an error occurred while processing this directive]
AQL - Movie DB Tutorial
Simple key selection
- (1)
select all from class movie
select all class movie (the same, just shorter)
- (list the key-values of all movies)
-
List all keys in the class movie.
Note the system is not case sensitive when referring to
database related terms, such as class names or tags.
results
- (2)
select p from p in class person
- (list the key-values of all persons)
-
A locator-variable p is defined to loop over the key-values in class person.
The query works exactly like the (1), but this query can be extended easily,
as p can be referred to for tag-dereferencing, column-wise sorting etc..
results
Tag dereferencing to access object attributes
- (3)
select m->Title, m->Director from m in class Movie
- (list the titles of movies and the key-values of their directors)
-
The from-clause defines a locator variable m that loops over all key-values of class movie.
The select-clause defines two select-fields, that are tags in the movie class.
The Title tag returns a string, whereas the Director tag returns another key from the Person
class. This key can be dereferenced further to gain access to the tags in the Person
class for all the stored values.
results
- (4)
select m->Title, m->Director->Full_name from m in class Movie
- (list the titles of movies and the names of their directors)
-
Basically the same as (3), but the result of m->Director has been dereferenced to
get the Full_name tag value for the particular key from the Person class.
results
- (5)
select a->Full_name from m in class Movie, a in m->Based_on->Author
- (list the names of authors of books that movies are based on)
-
The from-clause defines two locators - m loops over the Movie class
and a returns the Person-key, that is stored with the Book-key
for movies that are based on novels or plays.
The select-clause defines one result column that lists the Full_name
tag values for the key-values in a. Some authors names will be listed more than once, if
they've written a few novels on which movies were based.
Note that is isn't important where the dereferencing is done. The above query is the same as
select m->Based_on->Author->Full_name from m in class Movie
-
which performs the same without the need for an extra variable a.
results
- (6)
select p->Full_name, p->Address[2] from p in class person
- (list all people and all their address info)
-
This query makes use of tag-positioning. You will notice that the fields just right of the
Address tag in the Person class contain no actual data, they just introduce
some more structure. This query will therefor return all lines that are stored 2 positions
to the right of Address, and list all Mail, Email or Phone information that is stored.
results
- (7)
select p->Full_name, p->Phone from p in class person
- (list all people and their phone numbers)
-
In order to access specific items in the Address sub-field,
The required tag can be stated without having to refer to Address.
Such shortcuts come in handy in deeply nested class models, but the design
of the model needs to make sure that tag names aren't duplicated within a model.
results
Conditional expressions in the where-clause
- (8)
select m->Title, d->Full_name from m in class Movie, d in m->Director where exists d
- (for all movies that have directors, list with their title and the director's name)
-
Similar to (4), but omitting the Movie-keys in m for which the Director-tag doesn't exist.
As shown in (5), it is not necessary to define another locator d :-
select m->Title, m->Director->Full_name from m in class movie where exists m->Director
-
Note the difference to the output of (4) - this time AQL only lists movies where a director tag exists.
This demonstrates one of the basic philosophies about data storage in ACeDB - if a tag doesn't exist for an object
it doesn't store that fact (i.e. a NULL value for that tag) it simple doens't store it.
results
- (9)
select m->Cast->Full_name from m in class Movie where m = "truerom"
- (list the names of cast members in "True Romance")
-
By finding the key "truerom" in the movie class, it finds all Person-keys stored
under the Cast-tag. Those keys are dereferenced to be displayed as the full names.
The same result can be gathered by finding all the people that have starred in "True Romance".
Note that keys are compared to by their string value - i.e. in quotes.
select p->Full_name from p in class person where p->Stars_in->Title = "True Romance"
-
This shows an interesting aspect of AQL - It ispossible to get the same result using totally different queries.
The same result can also be expressed using the object construct which will be explained later.
select object("movie", "truerom")->cast->full_name
results
- (10)
select m->Title, year from m in class Movie, year in m->Released where year > `1990
- (list the movies that have been relased after 1990, i.e. in 1991 and from thereon)
-
This condition uses a date literal which is stated using the
` syntax.
This query assumes automatically an exists year expression in the where-clause,
because it couldn't otherwise evaluate the expression year > `1990
for movies that have no Released date value.
Look at (14) for how AQL processes that query internally.
results
- (11)
select m, m->Released from m in class movie where not exists m->Released
- (list the keys of all movies for which no release year information is stored)
-
Use of the
not keyword negates the value of exists m->Released
and thereby shows all movies that failed the where-clause evaluation check of (10).
results
The subject of expression evaluation in the where-clause is properly explored in the
AQL semantics document, which explores relationships between different value types
and their use in expressions, such as comparisons and arithmetic expressions.
Date functions
- (12)
select p->Full_name, yeardiff(p->Born, today) from p in class Person
- (list all people and their age)
-
This query uses the AQL keyword
today, which will be rendered to
2024-07-01
(i.e. the current date) during evalaution. If time-of-day information is important,
the keyword now, will be rendered to
2024-07-01_06:16:00.
The function yeardiff acts on two date values and returns an integer.
results
- (13)
select p->Full_name, yeardiff(p->Born, today) from p in class Person where yeardiff(p->Born, today) > 55
- (for all people over 55, list their name and age)
-
Same as (12), but restricts the list to people with ages greater than 55.
Note that only values for p can be included for which the Born tag exists.
results
- (14)
select m->Title, year from m in class Movie, year in m->Released where yeardiff (year,`1990) > 0
- (list the movies that have been relased after 1990, i.e. in 1991 and from thereon)
-
Variation on (10), but this time using yeardiff explicitly.
results
Possible date-functions are :-
- yeardiff
- monthdiff
- weekdiff
- daydiff
- hourdiff
- mindiff
- secdiff
.
These functions will return a sensible result of the date/time value specifies the appropriate portion that the function requests.
No sensible result will be returned for monthdiff (m->released, `1996), because although some
movies feature release dates with month information,
the date literal '1996 does not and therefore the evaluation will always fail.
All these functions return an integer value, which is positive if left argument is the smaller (i.e. earlier) date.
Table functions
- (15)
select p->Full_name, count(select p->Stars_in) from p in class Person
- (list all people and how many movies they've starred in)
-
The table-function
count acts on the subquery select p->Stars_in
and returns an integer value for all objects where the Stars_in-tag exists. It simply counts
the number of times the Stars_in-tag is contained in each object p.
results
- (16)
select m->Title from m in class Movie where count(select m->Cast) > 3
- (list all movies with more than 3 cast members)
-
The table-function
count acts on the subquery select p->Stars_in
and returns an integer value for all objects where the Stars_in-tag exists.
results
- (17)
select avg(select yeardiff(p->Born, now) from p in class Actor)
- (returns the average age of all the actors)
-
This time a table function acts on the return-value of a table expression.
The inner select-query returns a list of ages, over which the average value
is computed.
results
- (18)
select max(select m->released from m in class movie)
- (returns latest movie release date)
-
Similar to (17),
max acts on a list of dates.
results
Possible table-functions are :-
- count (all tables)
- min (Int, Float, DateType) **should allow Strings
- max (Int, Float, DateType)
- avg (Int, Float)
- sum (Int, Float)
- first (nyi)
- last (nyi)
All table functions will work on either accumulated object attributes (15),(16) or on the table
values of whole select-queries(17),(18). All such tables expression have to produce a table with only one column,
and in most cases they have to be of a specific value-type for the table-function to work.
Scalar variables
- (19)
$x := count(select all class movie)
- (how many movies are stored)
-
Scalar variable have a perl-like '$' syntax and can assume just one value.
This value is usually the return value of a table-function expression.
A variable assignment is treated as a whole query expression in itself.
The result will be a table with one column and one row containing the single
return value that the variable $x will assume.
The query is therefore equivalent to
select count(select all class movie)
-
results
- (20)
$x := avg(select m->rating from m in class movie);
select m->title, r from m in class movie, r in m->rating where r > $x
- (list movies with an higher than average rating)
-
The first query assigns a scalar variable to be the average rating of movie (ignoring
movie for which no such value is stored).
The second query then sues this value to list all movies for which the rating value is higher
that that.
This demonstrates how queries can be concatinated using the semi-colon ';'. Usually
one or more queries will calculate variables, whereas the last query is a table expression
that uses those variables to generate the output.
results
Table variables
[an error occurred while processing this directive]