Getting started AmosQL 2.1 Basic constructs 2.1.1 Syntactic conventions 2.1.2 Statements 2.1.3 Indentifiers 2.1.4 Variables 2.1.5 Constants 2.1.6 Expressions 2.1.7 Collections 2.1.8 Comments 2.2 Defining types 2.2.1 Deleting types 2.3 Creating Objects 2.3.1 Deleting objects 2.4 Queries 2.4.1 Calling functions 2.4.2 The select statement 2.4.3 Predicates 2.4.4 Grouped selections 2.4.5 Ordered selections 2.4.6 Top-k queries 2.4.7 Aggregation and bags 2.4.8 Quantifiers 2.5 Vector queries 2.5.1 Vector construction 2.5.2 The vselect statement 2.5.3 Accessing vector elements 2.5.4 Vector functions 2.6 Defining Functions 2.6.1 Stored functions 2.6.2 Derived functions 2.6.3 Overloaded functions 2.6.4 Casting 2.6.5 Second order functions 2.6.6 Transitive closures 2.6.7 Iteration 2.6.8 Abstract functions 2.6.9 Deleting functions 2.7 Updates 2.7.1 Cardinality constraints 2.7.2 Changing object types 2.7.3 Dynamic updates 2.8 Data mining primitives 2.8.1 Numerical vector functions 2.8.2 Vector aggregate functions 2.8.3 Plotting numerical data 2.9 Accessing data in files 2.9.1 Reading vectors from a file 2.10 Scans Procedural functions 3.1 Iterating over results 3.2 User update procedures The SQL processor Peer management 5.1 Peer communication 5.2 Peer queries and views Accessing external systems 6.1 Foreign and multi-directional functions 6.1.1 Cost estimates 6.2 The relational database wrapper 6.2.1 Connecting 6.2.2 Accessing meta-data 6.2.3 Executing SQL 6.2.4 Object-oriented views of tables 6.3 Defining new wrappers 6.3.1 Data sources 6.3.2 Mapped types 6.3.3 Type translation System functions and commands 7.1 Comparison operators 7.2 Arithmetic functions 7.3 String functions 7.4 Aggregate functions 7.4.1 Generalized aggregate functions 7.5 Temporal functions 7.6 Sorting functions 7.7 Accessing system meta-data 7.7.1 Type meta-data 7.7.2 Function meta-data 7.8 Searching source code 7.9 Extents 7.10 Query optimizer tuning 7.11 Indexing 7.12 Clustering 7.13 Unloading 7.14 Miscellaneous References

Amos II Release 17 User's Manual Staffan Flodin, Martin Hansson, Vanja Josifovski, Timour Katchaounov, Tore Risch, Martin Sköld, and Erik Zeitler Uppsala DataBase Laboratory Department of Information Technology Uppsala University Sweden January 9,  2015 Summary Amos II is an extensible NoSQL database system which allows different kinds of data sources to be integrated and queried. The system is centered around an object-oriented and functional query language, AmosQL, documented here. The system can store data in its internal main-memory object store. In AmosQL queries variables are bound to instances of objects of any kind, in contrast to SQL where variables in queries always have to be bound to rows in tables. Queries in terms function composition over sets of objects make AmosQL queries versatile. Amos II includes primitives for data mining through queries and functions to group, aggregate, transform, and visualize data. Ordered collections are supported through the datatype Vector. Queries can produce ordered results as vectors. Several distributed Amos II peers can collaborate in a federation. The documentation includes descriptions of basic peer communication primitives. Amos II enables wrappers to be defined for different kinds of data sources and external storage managers accessed to make them queryable. A predefined wrapper for relational databases allows queries combining relational data with other kinds of data accessible through Amos II. This manual describes how to use the Amos II system and the AmosQL query language. The principles of the Amos II system and AmosQL are described in the document [RJK03]. Acknowledgments The following persons have contributed to the development of the Amos II Release 17 system: Andrej Andrejev, Sobhan Badiozamany, Kristofer Cassel, Daniel Elin, Gustav Fahl, Staffan Flodin, Ruslan Fomkin, Jörn Gebhardt, Gyozo Gidofalvi, Martin Hansson, Milena Ivanova, Vanja Josifovski, Markus Jägerskogh, Jonas Karlsson, Timour Katchaounov,Khalid Mahmood, Salah-Eddine Machani, Lars Melander, Joakim Näs, Kjell Orsborn, Johan Petrini, Tore Risch, Manivasakan Sabesan, Martin Sköld, Silvia Stefanova, Thanh Truong, Christian Werner, Magnus Werner, Cheng Xu, Erik Zeitler, and Minpeng Zhu.

Organization of document

• Chapter 1 gives some basic information on how to download and get started running the system. It also mentions different subsystems available.
• Chapter 2 describes the core of the AmosQL query language, including how to define database schemas, populate, query, and update the database.
• Chapter 3 describes procedural extensions to AmosQL making it possible to implement algorithms that have side effects changing the database.
• In addition to AmosQL the system can process also SQL (SQL-92) queries, as described in Chapter 4.
  
• The mechanisms for setting up federations of distributed Amos II peers are described in Chapter 5.
• One core functionality of Amos II is the ability to access external systems and to integrate and query external databases managed by different kinds of data managers. The extensibility mechanisms are described in Chapter 6.
• Chapter 7 documents built-in system functions. 
• Finally Chapter 8 gives some references to related documents.
  

1 Getting started

 
amos2.exe
amos2.dll
amos2.dmp

amos2 [<db>]

You need not connect to any particular database, but instead, if <db> is omitted, the system enters an empty database (named amos2.dmp), where only the system objects are defined. The system looks for amos2.dmp in the same directory as where the executable amos2.exe is located.

The executable has a number of command line parameters to specify, e.g., the database or AmosQL script to load. To get a list of the command line parameters do:
  amos2 -h

The Amos toploop

When started, the system enters the Amos top loop where it reads AmosQL statements, executes them, and prints their results.  The prompter in the Amos II top loop is:

Amos n>

Typically you start by defining  meta-data (a schema) as types and properties of types represented as functions. For example, the following statement create a type named Person having two property functions name() and income():

Amos 1> create type Person properties
  (name Charstring,
   income Number);

For example:
Amos 2> create Person(name, income) instances
("Bill",100),
("John",250),
("Ulla",380),
("Eva", 280);

When the database is populated you can query it, e.g.:
Amos 3> select income(p) from Person p where name(p)="Ulla";

Usually you load AmosQL definitions from a script file rather than entering them on the command line, e.g.
Amos 1> < 'mycode.amosql';

Transactions

Database changes can be undone by using the rollback statement with a generation number as argument. For example, the statement:

Amos 4> rollback 2;

The statement commit makes changes non-undoable, i.e. all updates so far cannot be rolled back any more and the generation numbering starts over from 1. 

For example: 

Amos 2> commit;
Amos 1> ...

Saving and quitting

When your Amos II database is defined and populated, it can be saved on disk with the AmosQL statement:

save "filename";

In a later session you can connect to the saved database by starting Amos II with:

amos2 filename

To shut down Amos II orderly first save the database and then type:

Amos 1> quit;

This is all you need to know to get started with Amos II.

Java interface

JavaAmos is a version of the Amos II kernel connected to the Java virtual machine (32 bits JVM). With it Java programs can call Amos II functions and send AmosQL statements to Amos II for evaluation (the callin interface) [ER00]. You can also define Amos II foreign functions in Java (the callout interface). To start JavaAmos use the script

 javaamos

Back-end relational databases

Amos II includes a interface to relational databases using JDBC on top of JavaAmos. Any relational database can be accessed and queried in terms of AmosQL using this interface. The interface is described in the section Relational database wrapper.

Graphical database browser

The multi-database browser GOOVI [CR01] is a graphical browser for Amos II written as a Java application. You can start the GOOVI browser from the JavaAmos top loop by calling the Amos II function

goovi();

PHP interface

Amos II includes an interface allowing programs in PHP to call Amos II servers. The interface is tested for Apache servers. To use Amos II with PHP or SQL under Windows you are recommended to download and install WAMP http://www.wamp.org/. WAMP packages together a version of the Apache web server, the  PHP script language, and the MySQL database. Amos II  is tested with WAMP 2.0 (32 bits). See further the file readme.txt in subdirectory embeddings/PHP of the Amos II download.

C interface

The system is interfaced with the programming language C (and C++). As with Java, Amos II can be called from C (callin interface) and foreign Amos II functions can be implemented in C. See [Ris12].

Lisp interface

There is a built-in interpreter for a subset of the programming language CommonLisp in Amos II, aLisp [Ris06]. The system can be accessed and extended using aLisp.

2 AmosQL

In general the user may enter different kinds of AmosQL statements to the Amos top loop in order to instruct the system to do operations on the database:

1. First the database schema is created by defining types with associated properties.
2. Once the schema is defined the database can be populated by creating objects and their properties in terms of the database schema.
   
3. Once the database is populated queries may be expressed to retrieve and analyze data from the database. Queries return collections of objects, which can be both unordered sets of objects or ordered sequences of objects.
4. A populated database may be updated to change its contents.
5. Procedural functions (stored procedures) may be defined, which are AmosQL programs having side effects that may modify the database.

This section is organized as follows:

• Before going into the details of the different kinds of AmosQL statements, in Section 2.1 syntactic notation is introduced along with descriptions of syntax and semantics of the basic building blocks of the query language.
• Section 2.2 describes how to create a simple database schema by defining types and properties.
  
• Section 2.3 introduces how to populate the database by creating objects.
• The concept of queries over a populated database is presented in Section 2.4.
• Regular queries return unordered sets of data. In addition Amos II provides the ability to specify vector queries, which return ordered sequences of data, as described in Section 2.5.
  
• A central concept in Amos II is the extensive use of functions in database schema definitions. There are several kinds of user-defined functions supported by the system as described in Section 2.6.
• Section 2.7 describes how to update a populated database.
• Section 2.8 describes primitives in AmosQL useful for data mining, in particular different ways of grouping data and of making operations of sequences and numerical vectors.
• Section 2.9 describes functions available to read data from files, such as CSV files.
• Section 2.10 describes how to define scans making it possible to iterate over very large query results.

2.1 Basic constructs

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

2.1.1 Syntactic conventions

A ::= B C: A consists of B followed by C. 
A ::= B | C, alternatively (B | C): A consists of B or C.
A ::= [B]: A consists of B or nothing.
A ::= B-list: A consists of one or more Bs.
A ::= B-commalist: A consists of one or more Bs separated by commas.
'xyz': The string (keyword) xyz.

2.1.2 Statements

        create-type-stmt |
        delete-type-stmt | 
        create-object-stmt | 
        delete-object-stmt | 
        create-function-stmt | 
        delete-function-stmt |
        query | 
        update-stmt |
        add-type-stmt |
        remove-type-stmt |
        for-each-stmt |
        set-interface-variable-stmt | 
        declare-interface-variable-stmt |
        commit-stmt |
        rollback-stmt |
        open-cursor-stmt | 
        fetch-cursor-stmt | 
        close-cursor-stmt |
        quit-stmt |
        exit-stmt 

2.1.3 Identifiers

identifier ::=

        ('_' | letter) [identifier-character-list] 

identifier-character ::=
        alphanumeric | '_'

E.g.: MySalary
      x
      x1234
      x1234_b

2.1.4 Variables

• Local variables are identifiers for data values inside AmosQL queries and functions. Local variables must be declared in function signatures (see Function definitions), in from clauses (see Queries), or by the declare statement (see procedural functions). Notice that variables are not case sensitive.

Syntax:
local-variable ::= identifier


   E.g. my_variable
        MyVariable2


• Interface variables hold only temporary results during interactive sessions. Interface 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.

Syntax:
interface-variable ::= ':' identifier

   E.g. :my_interface_variable
        :MyInterfaceVariable2


The user can declare an interface variable to be of a particular type by the interface variable declare statement:


interface-variable-declare-stmt ::= 'declare' interface-variable-declaration-commalist


interface-variable-declaration ::= type-spec interface-variable

   E.g. declare Integer :i, Real :x3;


Interface variables can be assigned either by the into-clause of the select statement or by the interface variable assignment statement set:

set-interface-variable-stmt ::= 'set' interface-variable '=' expr


   E.g. set :x3 = 2.3;
        set :i = 2 + sqrt(:x3);

2.1.5 Constants

constant ::=
        integer-constant | real-constant | boolean-constant | 
        string-constant | time-stamp | functional-constant | 'nil'

integer-constant ::= 
        ['-'] digit-list 
 
   E.g. 123
        -123
 
real-constant ::=
        decimal-constant | scientific-constant

decimal-constant ::=
        ['-'] digit-list '.' [digit-list]

scientific-constant ::=
        decimal-constant ['e' | 'E'] integer-constant
 
   E.g. 1.2
        -1.0
        2.3E2
        -2.4e-21
 
boolean-constant ::=
        'true' | 'false' 

2.1.6 Expressions

expr ::=  simple-value | function-call | collection-constr | casting | vector-indexing | '(' query ')'
    E.g. 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 the Amos top loop, e.g.:

   1+5*sqrt(6);
    => 13.2474487139159

Notice that Boolean expressions, predicates, either return true, or nothing if the expression is not true. For example:
   1<2 or 3<2;
    => TRUE
   1<2 and 3<2;
    => nothing

Entering simple expressions followed by a semicolon is the simplest form of AmosQL queries, e.g.:
  1+sqrt(25);

2.1.7 Collections

• 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.
  

Bags

returns the bag:

  set :h = hobbies(:sam);

Vectors

Vector element v_i can be access with the notation v[i], where the indexing i is from 0 and up. For example:
   set :v={1,2,3};
then
   :v[2];
returns
   3

Records

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. Amos II uses 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'}

2.1.8 Comments

comment ::= '/*' character-list '*/'

2.2 Defining types

Syntax: 

create-type-stmt ::=
        'create type' type-name ['under' type-name-commalist]
                ['properties' '(' attr-function-commalist ')']

type-spec ::= type-name | 'Bag of' type-spec | 'Vector of' type-spec

type-name ::= identifier

attr-function ::= generic-function-name type-spec ['key']

create type Person properties
   (name Charstring,
    income Number,
    age Number,
    parents Bag of Person);

The new type will be a subtype of all the supertypes in the under clause. For example, in the following definition Student subtype of Person and Person is supertype of Student:
   create type Student under Person;

If no supertypes are specified the new type becomes a subtype of the system type named Userobject.

If key is specified for a property, it indicates that each value of the attribute is unique and the system will raise an error if this uniqueness is violated. In the following example, two objects of type Employee cannot have the same value of property emp_no:
   create type Employee under Person properties
   (emp_no Number key);

   create type TA under Student, Employee;

2.2.1 Deleting types

Syntax:

delete-type-stmt ::= 'delete type' type-name
 
   E.g. delete type Employee;

2.3 Creating objects

Syntax: 

create-object-stmt ::=
        'create' type-name
        ['(' generic-function-name-commalist ')'] 'instances' initializer-commalist       

initializer ::= variable |
                [variable] '(' expr-commalist ')'

    create Person(name, income, age) instances
       ("Adam",3500,35),
       ("Eve",3900,40);

   create Person (name,income) instances
          ("Kalle "+"Persson" , 3345*1.5);

    create Person(name, income) instances
    :pelle ("Per",3836);

Then the query

    income(:pelle);

    3386

Bag valued functions are initialized using the syntax 'bag(e1,...)' (syntax bag-expr),
for example:

create Person (name,parents,income,age) instances
     :adam   ("Adam",nil,2300,64),
     :eve    ("Eve",nil,3200,63),
     :cain   ("Cain",bag(:adam,:eve),1500,44),
             ("Abel",bag(:adam,:eve),900,43),
     :seth   ("Seth",bag(:adam,:eve),1700,42),
     :lilith ("Lilith",bag(:adam,:eve),4500,40),
     :noah   ("Noah",bag(:seth,:lilith),5300,25),
     :ruth   ("Ruth",:cain,500,24),
             ("Shem",bag(:noah,:ruth),800,15),
     :ham    ("Ham",bag(:noah,:ruth),3800,16),
             ("Cush",:ham,10,2);

It is possible to specify nil for a value when no initialization is desired for the corresponding function.

2.3.1 Deleting objects

Syntax:

delete-object-stmt ::= 'delete' variable

delete :pelle;

Deleted objects are printed as

#[OID nnn *DELETED*]

2.4 Queries

1. It can be calls to built-in or user defined function.
2. It can be a select statement to search the database for a set of objects having properties fulfilling a query condition specified as a logical predicate.
3. If can be a vector selection statements (vselect-statement) to construct an ordered sequence (vector) of objects fulfilling the query condition. 
4. It can be an expression.
   

2.4.1 Calling functions

function-call ::=
        function-name '(' [parameter-value-commalist] ')' |
        expr infix-operator expr |
        tuple-expr

infix-operator ::= '+' | '-' | '*' | '/' | '<' | '>' | '<=' | '>=' | '=' | '!=' | 'in'

parameter-value ::= expr | 
                    '(' select-stmt ')' |
                    tuple-expr

tuple-expr ::= '(' expr-commalist ')'


   E.g. sqrt(2.1);
        1+2;
        1+2 < 3+4;
        "a" + 1;   

 (income(:eve) + income(:ham)) * 0.5;

 times(plus(income(:eve),income(:ham)),0.5);

The result of a function call can be saved temporarily in an interface variable, for example:

    set :inca = income(:adam);

Also bags valued function calls can be saved in variables, for example:

    set :pb = parents(:cain);

    in(:pb);

Tuple expressions can be used to assign the result of functions returning tuples, for example:

    set (:m,:f)=parents2(:cain);

2.4.2 The select statement

Syntax:

select-stmt ::=
        'select' ['distinct']
                 [select-clause]
                 [into-clause] 
                 [from-clause]
                 [where-clause]
                 [group-by-clause]
                 [order-by-clause]
                 [limit-clause]
select-clause ::= expr-commalist
into-clause ::= 
        'into' variable-commalist 

from-clause ::= 
        'from' variable-declaration-commalist 

variable-declaration ::= 
        type-spec local-variable

where-clause ::=
       'where' predicate-expression

group-by-clause ::=
        'group by' expression-commalist

order-by-clause ::=
        'order by' expression ['asc' | 'desc']

limit-clause ::=
        'limit' expression

      select name(p), income(p) 
        from Person p 
       where income(p)>2500;

The from-clause declares data types of local variables used in the query. For example:

      select name(p), income(p)
             from Person p
            where age(p)>20;

If a function is applied on the result of a function returning a bag of values, the outer function is applied on each element of that bag, the bag is flattened. This is called Daplex semantics. For example: if there are more than one parents per parent generation of Cush there will be several names (e.g. Noah and Ruth) returned when querying:

    select name(parents(parents(q)))
      from Person q
     where name(q)= "Cush";

    "Noah"
    "Ruth"

      select name(p), income(p)
        from Person p
       where age(p)>20;

    select name(m), name(f)
      from Person m, Person p
     where (m,f) = parents2(p);

Duplicates are removed from the result only when the keyword 'distinct' is specified, in which case a set (rather than a bag) is returned from the selection.

For example, this query returns the set of different names in the database:

    select distinct name(p)
      from Person p
     where age(p)>20;

The optional group-by-clause groups and summarizes (aggregates) the result. A select statement with a group-by-class is called a grouped selection. For example:

      select name(p), sum(income(p))
        from Person p
       where age(p) > 20
       group by name(p); 

The optional order-by-clause sorts the result ascending ('asc', default) or descending ('desc'). A select statement with an order-by-clause is called an ordered selection. For example:

      select name(p), income(p)
        from Person p
       where age(p) >  20
       order by income(p) desc;

The optional limit-clause limits the number of returned values from the select statement. It is often used together with ordered selections to specify top-k queries. For example:

      select name(p), income(p)
        from Person p
       where age(p) > 20
       order by income(p) desc
       limit 10;

The optional into-clause specifies variables to be bound to the result. 

For example:

    select p into :e
      from Person p
     where name(p) = 'Eve';

    set :r = (select p from Person p where name(p) = 'Eve');

    in(:r);
    select p from Person p where p in :r;

2.4.3 Predicates

predicate-expression ::=
        predicate-expression 'and' predicate-expression | 
        predicate-expression 'or' predicate-expression | 
        '(' predicate-expression ')' | 
        expr

Examples of predicates:   
	x < 5
        child(x)
        "a" != s
        home(p) = "Uppsala" and name(p) = "Kalle"
        name(x) = "Carl" and child(x)
        x < 5 or x > 6 and 5 < y 
        1+y <= sqrt(5.2)       
        parents2(p) = (m,f) 
        count(select friends(x) from Person x where child(x)) < 5

The boolean operator and has precedence over or. Negation is handled by the quantifier function notany().
For example:
	a<2 and a>3 or b<3 and b>2
is equivalent to
	(a<2 and a>3) or (b<3 and b>2)

The comparison operators (=, !=, <, <=, and >=) are treated as binary predicates. You can compare objects of any type.

Predicates are also allowed in the result of a select expression. For example, the query:
       select age(:p1) < 20 and home(:p1)="Uppsala";
returns true if person :p1 is younger than 20 and lives in Uppsala.

2.4.4 Grouped selections

A grouped selection is a select statement with a group-by-clause specified. The execution semantics of a grouped selection is different than for regular queries.

When analyzing data it is often necessary to group data, for example to get the sum of the salaries of employees per department. Such regroupings are specified though the group-by-clause. It specifies on which expressions in the select clause the data should be grouped and summarized.  

For example:
    select name(d), sum(salary(p))
      from Department d, Person p
     where dept(p)=d
     group by name(d);

Here the group-by-clause specifies that the result should be grouped on the name of the departments in the database. After the grouping, for each department d the salaries of the persons p working at that department should be summed using the aggregate function sum().

An element of a select-clause of a grouped selection must be one of:

1. An element in the group key specified by the group-by-clause, which is name(d) in the example. The result is grouped on each group key. In the example the grouping is made over each department name so the group key is specified as name(d).
   
2. A call to an aggregate function, which is sum() in the example. The aggregate function is applied for the set of bindings specified by the group key. In the example the aggregate function sum() is applied on the set of values of salary(p) for the persons p working in department d, i.e. where dept(p)=d.
   

In general aggregate functions such as sum(), avg(), stdev(), min(), max(), count() are applied on collections (bags) of values, rather than single objects.

    select name(d), sum(salary(p))
      from Department d, Person p
     where dept(p)=d;

Without the grouping the aggregate function sum() is applied on the salary of each person p, rather than the collection of salaries corresponding to the group key name(p) in the grouped selection.

The group key need not be part of the result, for example the following query returns the sum of the salaries for all departments without revealing the department names:
    select sum(salary(p))
      from Department d, Person p
     where dept(p)=d
     group by name(d);

2.4.5 Ordered selections

The order-by-clause specifies that the result should be (partially) sorted by the specified sort key. The sort order is descending when desc is specified and ascending otherwise.

For example, the following query sorts the result descending based on the sort key salary(p):

  select name(p), salary(p)
    from Person p
   order by name(p) desc;
 
The sort key does not need to be part of the result.

For example, the following query list the salaries of persons in descending order without associating any names with the salaries:

 select salary(p)
   from Person p
  order by name(p) desc;

2.4.6 Top-k queries

A top-k query is a query returning the first few tuples in a larger set of tuples based on some ranking. The order-by-clause and limit-clause can be combined to specify top-k queries. For example, the following query returns the names and salaries of the 10 highest income earners:

  select name(p), salary(p)
    from Person p
   order by name(p) desc
   limit 10;

The limit can be any numerical expression.For example, the following query retrieves the :k+3 lowest income earners, where :k is a variable bound to a numeric value:

  select name(p), salary(p)
    from Person p
   order by name(p)
   limit :k+3;

2.4.7 Aggregation over subqueries

1. They can be used in grouped selections.
2. They can be applied on subqueries.
   

In the second case, the subqueries are specified as nested select expressions returning bags, for example:

  select name(friends(p)) 
    from Person p 
   where name(p)= "Bill";

The function friends() returns several (a bag of) persons on which the function name() is applied. The normal semantics in Amos II is that when a function (e.g. name()) is applied on a bag valued function (e.g. friends()) it will be applied on each element of the returned bag. In the example a bag of the names of the persons named Bill is returned. This is called Daplex semantics.

Aggregate functions work differently. They are applied on the entire bag. For example:

  count(friends(:p));

In this case count() is applied on the subquery of all friends of :p. The system uses a rule that arguments are converted (coerced) into a subquery when an argument of the calling function (e.g. count) is declared Bag of.

  select sum(b), avg(b), stdev(b) 
    from Bag of Integer b
   where b = (select income(p) 
                from Person p);

Elements in subqueries can be accessed with the in operator. For example:

  select name(p), count(b) 
    from Bag of Integer b, Person p
   where b = (select p from Person p)
     and p in b;

The query returns the names of all persons paired with the number of persons in the database.

Variables may be assigned to bags by assigning values of functions returning bags, for example:
   set :f = friends(:p);
   count(:f);

  bequal(Bag x, Bag y) -> Boolean

See 7.4 Aggregate functions for a details on aggregate functions.

2.4.8 Quantifiers

The function some() implements logical exist over a subquery:

some(Bag sq) -> Boolean

   select name(p) 
     from Person p
    where some(parents(p));

notany(Bag sq) -> Boolean

   select name(p) 
     from Person p 
    where notany(select parents(p) where age(p)>65);

2.5 Vector queries

1. It can be a vector construction expression that creates a new vector from other objects.
2. It can be a vector indexing expression that accesses vector elements by their indexes.
   
3. It can be a regular select statement returning a set of constructed vectors.
4. It can be a vselect statement, which returns an ordered vector rather than an unordered bag as the regular select statement.
5. It can be a call to some vector function returning vectors as result.
   

2.5.1 Vector construction

 2.5.2 The vselect statement

vselect-stmt ::=
        'vselect' ['distinct']
                  [select-clause]
                  [into-clause] 
                  [from-clause]
                  [where-clause]
                  [group-by-clause]
                  [order-by-clause]
                  [limit-clause]

2.5.3 Accessing vector elements

2.5.4 Vector functions

2.6 Defining functions

• Stored functions are stored in the Amos II database as a table.
  
• Derived functions are defined by a single query that returns the result of the a function call for given parameters. 
  
• Foreign functions are defined in an external programming language. Foreign functions can be defined in the programming languages C/C++ [Ris12], Java [ER00], and Lisp [Ris06].
• Procedural functions are defined using procedural AmosQL statements that can have side effects changing the state of the database. Procedural functions makes AmosQL computationally complete.
  
• Overloaded functions have different implementations depending on the argument types in a function call.

Syntax:

2.6.1 Stored functions

AmosQL functions may also have tuple valued results by using the tuple-result-spec notation. For example:

  set (:mother,:father) = parents2(:eve);

2.6.2 Derived functions

  create function child(Person p) -> Boolean
            as select true where age(p)<18;

         create function child(Person p) -> Boolean
            as age(p) < 18;

Since the select statement returns a bag of values, derived functions also often return a  Bag of results. If you know that a function returns a bag of values you should indicate that in the signature. For example:

   create function youngFriends(Person p)-> Bag of Person
     as select f
          from Person f
         where age(f) < 18
           and f in friends(p);

   create function youngFriends(Person p)-> Person
     as select f
          from Person f
         where age(f) < 18
           and f in friends(p);

Variables declared in the result of a derived function need not be declared again in the from clause, their types are inferred from the function signature. For example, youngFriends() can also be defined as:

create function youngFriends(Person p) -> Bag of Person f
  as select f
      where age(f) < 18
        and f in friends(p);

Derived functions whose arguments are declared Bag of are user defined aggregate functions. For example:

create function myavg(Bag of Number x) -> Number
     as sum(x)/count(x);

Aggregate functions do not flatten the argument bag. For example, the following query computes the average age of Carl's grandparents:

   select myavg(age(grandparents(q)))
     from Person p
    where name(q)="Carl";  

       select age(m), age(f)
         from Person m, Person f, Person p
        where (m,f) = parents2(p)
          and name(p) = "Oscar"; 

2.6.3 Overloaded functions

For example, assume the following two Amos II function definitions having the same generic function name less():

create function less(Number i, Number j)->Boolean
        as i < j; 
create function less(Charstring s,Charstring t)->Boolean 
        as s < t;

  less(Number,Number) -> Boolean
  less(Charstring,Charstring) -> Boolean

The query compiler resolves the correct resolvent to apply based on the types of the arguments; the type of the result is not considered. If there is an ambiguity, i.e. several resolvents qualify in a call, or if no resolvent qualify, an error will be generated by the query compiler.

When overloaded function names are encountered in AmosQL function bodies, the system will use local variable declarations to choose the correct resolvent (early binding).  For example:

 create function younger(Person p,Person q)->Boolean
        as less(age(p),age(q));

On the other hand, this function:

create function nameordered(Person p,Person q)->Boolean
        as less(name(p),name(q));

Late binding

Dynamic type resolution at run time, late binding, is sometimes required to choose the correct resolvent. For example, the query

less(1,2);

 create type Employee under Person;
 create type Manager under Employee;
 create function mgrbonus(Manager)->Integer as stored;
 create function income(Employee)->Integer as stored; 
 create function income(Manager m)->Integer i 
        as income(cast(m as Employee)) + mgrbonus(m);

 create function grossincomes()->Integer i 
        as select income(p)
             from Employee p; 
        /* income(p) late bound */

To avoid the overhead of late binding one may use casting.

Since the detection of the necessity of dynamic resolution is often at compile time, overloading a function name may lead to a cascading recompilation of functions defined in terms of that function name. For a more detailed presentation of the management of late bound functions see [FR95].

2.6.4 Casting

The type of an expression can be explicitly defined using the casting statement:

 casting ::= 'cast'(expr 'as' type-spec)

for example

 create function income(Manager m)->Integer i 
        as income(cast(m as Employee)) + mgrbonus(m);

By using casting statements one can avoid late binding.

2.6.5 Second order functions

Amos II functions are internally represented as any other objects and stored in the database. Object representing functions can be used in functions and queries too. An object representing a function is called a functional. Second order functions take functionals as arguments or results. The system function functionnamed() retrieves the functional fno having a given name fn:

functionnamed(Charstring fn) -> Function fno

functionnamed("plus");
  => #[OID 155 "PLUS"]

functionnamed("number.number.plus->number");
  => #[OID 156 "NUMBER.NUMBER.PLUS->NUMBER"]

returns the object representing the resolvent named NUMBER.NUMBER.PLUS->NUMBER.

Another example of a second order function is the system function

apply(Function fno, Vector argl) -> Bag of Vector

It calls the functional fno with the vector argl as argument list. The result tuples are returned as a bag of vectors, for example:

apply(functionnamed("number.number.plus->number"),{1,3.4});
  => {4.4}

Notice how apply() represents argument lists and result tuples as vectors.

When using second order functions one often needs to retrieve a functional fno given its name and the function functionnamed() provides one way to achieve this. A simpler way is often to use functional constants with syntax:

functional-constant ::= '#' string-constant

for example

#'mod';

A functional constant is translated into the functional with the name uniquely specified by the string constant. For example, the following expression

apply(#'mod',{4,3});
  => {1}

Notice that an error is raised if the function name specified in the functional constant is not uniquely identifying the functional. This happens if it is the generic name of an overloaded function. For example, the functional constant #'plus' is illegal, since plus() is overloaded. For overloaded functions the name of a resolvent has to be used instead, for example:

apply(#'plus',{2,3.5});

apply(#'number.number.plus->number', {2,3.5});
 => {5.5}

apply(functionnamed("plus"),{2,3.5});
 => {5.5}

2.6.6 Transitive closures

The transitive closure functions tclose() is a second order function to explore graphs where the edges are expressed by a transition function specified by argument fno:

   tclose(Function fno, Object o) -> Bag of Object

tclose() applies the transition function fno(o), then fno(fno(o)), then fno(fno(fno(o))), etc until fno returns  no new result. Because of the Daplex semantics, if the transition function fno returns a bag of values for some argument o, the successive applications of fno will be applied on each element of the result bag. The result types of a transition function must either be the same as the argument types or a bag of the argument types. Such a function that has the same arguments and (bag of) result types is called a closed function.

For example, assume the following definition of a graph defined by the transition function arcsto():

  create function arcsto(Integer node)-> Bag of Integer n as stored;
  set arcsto(1) = bag(2,3);
  set arcsto(2) = bag(4,5);
  set arcsto(5) = bag(1);

The following query traverses the graph starting in node 1:

Amos 5> tclose(#'arcsto', 1);
  1
  3
  2
  5
  4

In general the function tclose() traverses a graph where the edges (arcs) are defined by the transition function. The vertices (nodes) are defined by the arguments and results of calls to the transition function fno, i.e. a call to the transition function fno defines the neighbors of a node in the graph. The graph may contain loops and tclose() will remember what vertices it has visited earlier and stop further traversals for vertices already visited.

You can also query the inverse of tclose(), i.e. from which nodes f can be reached, by the query:

Amos 6> select f from Integer f where 1 in tclose(#'arcsto',f);
  1
  5
  2

If you know that the graph to traverse is a tree or a directed acyclic graph (DAG) you can instead use the faster function

   traverse(Function fno, Object o) -> Bag of Object

The children in the tree to traverse is defined by the transition function fno. The tree is traversed in pre-order depth first. Leaf nodes in the tree are nodes for which fno returns nothing. The function traverse() will not terminate if the graph is circular. Nodes are visited more than once for acyclic graphs having common subtrees.

A transition function may have extra arguments and results, as long as it is closed. This allows to pass extra parameters to a transitive closure computation. For example, to compute not only the transitive closure, but also the distance from the root of each visited graph node, specify the following transition function:

create function arcstod(Integer node, Integer d) -> Bag of (Integer,Integer)
  as select arcsto(node),1+d;

and call

tclose(#'arcstod',1,0);

which will return

(1,0)
(3,1)
(2,1)
(5,2)
(4,2)

2.6.7 Iteration

   iterate(Function fn, Number maxdepth, Object x) -> Object r

   iterate(Function fn, Number maxdepth, Object x0, Object p) -> Object r

2.6.8 Abstract functions

Sometimes there is a need to have a function defined for subtypes of a common supertype, but the function should never be used for the supertype itself. For example, one may have a common supertype Dog with two subtypes Beagle and Poodle. One would like to have the function bark defined for different kinds of dogs, but not for dogs in general. In this case one defines the bark function for type Dog as an abstract function, for example::

create type Dog;
create function name(Dog)->Charstring as stored;
create type Beagle under Dog;
create type Poodle under Dog;
create function bark(Dog d) -> Charstring as foreign 'abstract-function';
create function bark(Beagle d) -> Charstring;
create function bark(Poodle d) -> Charstring;
create Poodle(name,bark) instances ('Fido','yip yip');
create Beagle(name,bark) instances ('Snoopy','arf arf');

Now you can use bark() as a function over dogs in general, but only if the object is a subtype of Dog:

Amos 15> select bark(d) from dog d;
"arf arf"
"yip yip"

An abstract function is defined by:

create function foo(...)->... as foreign 'abstract-function'.

An abstract functions are implemented as a foreign function whose implementation is named 'abstract-function'. If an abstract function is called it gives an informative error message. For example,  if one tries to call bark() for an object of type Dog, the following error message is printed:

Amos 16> create Dog instances :buggy;
NIL
Amos 17> bark(:buggy);
BARK(DOG)->CHARSTRING
  is an abstract function requiring a more specific argument signature than
  (DOG) for arguments
  (#[OID 1009])

2.6.9 Deleting functions

Syntax:

2.7 Updates

Syntax:

update-stmt ::=
        update-op update-item [from-clause] [where-clause] 

update-op ::= 
        'set' | 'add' | 'remove' 

update-item ::= 
        function-name '(' expr-commalist ')' '=' expr

  create function name(Person) -> Charstring as stored;
  create function hobbies(Person) -> Bag of Charstring as stored;

  create Person instances :sam, :eve;

  set name(:sam) = "Sam";
  set name(:eve) = "Eve";

set hobbies(:eve) = bag("Camping","Diving");

  add hobbies(:sam) = "Sailing";
  add hobbies(:sam) = "Fishing";

  remove hobbies(:sam) = "Fishing";

  set hobbies(:eve) = hobbies(:sam);

  set hobbies(:eve) = h
  from Charstring h
  where h in hobbies(:sam) and
        h != "Sailing";

  create function married(Person,Person)->Boolean as stored;
  set married(:sam,:eve) = true;

  set married(:sam,:eve)=false;

  remove married(:sam) = :eve;

  create function marriedto(Person p) -> Person q
  as select q where married(p,q);

2.7.1 Cardinality constraints

• many-one is the default when defining a stored function as in salary().
  
  
• many-many is specified by prefixing the result type specification with 'Bag of' as in children(). In this case there is no cardinality constraint enforced.
  
  
• one-one is specified by suffixing a result variable with 'key'
  For example:
     create function name(Person p) -> Charstring nm key
       as stored;
  will guarantee that a person's name is unique.
  
  
• one-many is normally represented by an inverse function with cardinality constraint many-one. For example, suppose we want to represent a one-many relationship between types Department and Employee, i.e. there can be many employees for a given department but only one department for a given employee. The recommended way is to define the function department() enforcing a many-one constraint between employees as departments:
  
    create function department(Employee e) -> Department d
      as stored;
  
  The inverse function can then be defined as a derived function:
  
    create function employees(Department d) -> Bag of Employee e
      as select e where department(e) = d;
  
  Since inverse functions are updatable the function employees() is also updatable and can be used when populating the database.
  

2.7.2 Changing object types

Syntax:

add-type-stmt ::=
        'add type' type-name ['(' [generic-function-name-commalist] ')'] 
        'to' variable-commalist

The remove-type-stmt makes one or more objects no longer belong to the specified type.

Syntax:

remove-type-stmt ::= 
        'remove type' type-name 'from' variable-commalist

2.7.3 Dynamic updates

createobject(Type t)->Object
createobject(Charstring tpe)->Object
deleteobject(Object o)->Boolean
addfunction(Function f, Vector argl, Vector resl)->Boolean
remfunction(Function f, Vector argl, Vector resl)->Boolean
setfunction(Function f, Vector argl, Vector resl)->Boolean

createobject() creates an object of the type specified by its argument.

deleteobject() deletes an object.

The procedural system functions setfunction(), addfunction(), and remfunction() update a function given an argument list and a result tuple as vectors. They return TRUE if the update succeeded.

To delete all rows in a stored function fn, use

dropfunction(Function fn, Integer permanent)->Function

If the parameter permanent is the number one the deletion cannot be rolled back, which saves space if the extent of the function is large.

2.8 Data mining primitives

2.8.1 Numerical vector functions

2.8.2 Vector aggregate functions

meansub(Bag of Vector of Number b) -> Bag of Vector of Number
zscore(Bag of Vector of Number b) -> Bag of Vector of Number
maxmin(Bag of Vector of Number b) -> Bag of Vector of Number

meansub((select {i, i/2 + 10}
           from integer i
          where i in iota(1, 5)));

{-2.0,-1.0}
{-1.0,-0.5}
{0.0,0.0}
{1.0,0.5}
{2.0,1.0}

scatter2(Bag of Vector v) -> Integer 
scatter2l(Bag of Vector v) -> Integer 
scatter2p(Bag of Vector v) -> Integer 
scatter3(Bag of Vector v) -> Integer 
scatter3l(Bag of Vector v) -> Integer 
scatter3p(Bag of Vector v) -> Integer
scatter2(Vector of Integer projs, Bag of Vector v) -> Integer 
scatter2l(Vector of Integer projs, Bag of Vector v) -> Integer 
scatter2p(Vector of Integer projs, Bag of Vector v) -> Integer 
scatter3(Vector of Integer projs, Bag of Vector v) -> Integer 
scatter3l(Vector of Integer projs, Bag of Vector v) -> Integer 
scatter3p(Vector of Integer projs, Bag of Vector v) -> Integer

open-cursor-stmt ::=
        'open' cursor-name 'for' expr 

cursor-name ::= 
        variable 

fetch-cursor-stmt ::= 
        'fetch' cursor-name [into-clause] 

close-cursor-stmt ::=
        'close' cursor-name

create person (name,age) instances :Viola ('Viola',38);
open :c1  for (select p from Person p where name(p) = 'Viola'); 
fetch :c1 into :Viola1;
close :c1; 
name(:Viola1); 
--> "Viola";

set :b = (select iota(1,1000000)+iota(1,1000000)); 
/* :b contains a bag with 10**12 elements! */

open :c for :b;
fetch :c;
-> 2
fetch :c;
-> 3
etc.
close :c; 

3 Procedural functions

procedural-function-definition ::=
        block | procedure-stmt
    
    procedure-stmt ::=
        create-object-stmt | 
        delete-object-stmt | 
        for-each-stmt |
        update-stmt |
        add-type-stmt |
        remove-type-stmt |
        set-local-variable-stmt | 
        query | 
        if-stmt |
        commit-stmt |
        abort-stmt |
        loop-stmt |
        while-stmt |
        open-cursor-stmt |
        fetch-cursor-stmt |
        close-cursor-stmt

    block ::=  
        'begin' 
               ['declare' variable-declaration-commalist ';'] 
               procedure-stmt-semicolonlist 
        'end'        

    return-stmt ::= 
        'return' expr 

    for-each-stmt ::= 
        'for each' [for-each-option] variable-declaration-commalist 
                   [where-clause] for-each-body 

    for-each-option ::= 'distinct' | 'copy'

    for-each-body ::= procedure-body

    if-stmt ::= 
        'if' expr 
        'then' procedure-body 
        ['else' procedure-body] 

     set-local-variable-stmt ::= 
        'set' local-variable '=' expr
    
    while-stmt ::=
        'while' expr 'do' procedure-stmt-semicolonlist 'end while'

    loop-stmt ::=
        'loop' procedure-stmt-semicolonlist 'end loop'

    leave-stmt ::=
        'leave'

 create function creperson(Charstring nm,Integer inc) -> Person p 
                as 
                begin 
                  create Person instances p; 
                  set name(p)=nm; 
                  set income(p)=inc; 
                  return p; 
                end;

 set :p = creperson('Karl',3500);

create function increase_incomes(Integer inc,Integer thres)
                               -> Integer oldinc 
  as for each Person p, Integer i 
        where i > thres 
          and i = income(p) 
     begin 
       return i; 
       set income(p) = i + inc 
     end;

3.2 User update procedures

    set_addfunction(Function f, Function up)->Boolean
  set_remfunction(Function f, Function up)->Boolean
  set_setfunction(Function f, Function up)->Boolean

The function f is the function for which we wish to declare a user update function and up is the actual update procedure. The arguments of a user update procedures is the concatenation of argument and result tuples of  f. For example, assume we have a function

  create function netincome(Employee e) -> Number
    as income(e)-taxes(e);

Then we can define the following user update procedure:

create function set_netincome(Employee e, Number i) -> Boolean
  as begin
     set taxes(e)= i*taxes(e)/income(e) + taxes(e);
     set income(e) = i*(1-taxes(e))/income(e) +
                     income(e);
     end;

The following declaration makes netincome() updatable with the set statement:

  set_setfunction(#'employee.netincome->number',
                  #'employee.number->boolean');

Now one can update netincome() with, e.g.:

4 The SQL processor

sql(Charstring query)->Bag of vector result

create function sql:employee(Integer ssn) -> (Charstring name, Number Income, Integer dept) as stored;

create function sql:dept(Integer dno) -> Charstring dname as stored;

sql("insert into employee values (12345, ‘Kalle’, 10000, 1)");

sql("insert into employee values (12386, ‘Elsa’, 12000, 2)");

sql("insert into employee values (12493, ‘Olof’, 5000, 1)");

sql("insert into dept values(1,’Toys’)");

sql("insert into dept values(2,’Cloths’)");

sql("select ssn from employee where name = ‘Kalle’");

sql("select dname from dept, employee where dept = dno and name=’Kalle’");

        amos2 ... -q sql...

    parents(Person)-> Bag of Person 

    parents(:p);  /* Result is the bag of parents of :p */
    select c from Person c where :p in parents(c);
                  /* Result is bag of children of :p */ 

    sqroots(4.0);    /* Result is -2.0 and 2.0 */ 
    select x from Number x where sqroots(x)=4.0;
                    /* result is 16.0 *
    sqroots(4.0)=2.0;
                    /* Is the square root of 4 = 2 */ 

create function plus(Number x, Number y) -> Number r
  as multidirectional
     ('bbf' key foreign 'plus--+')     /* addition*/
     ('bfb' key foreign 'plus-+-')     /* subtraction */
     ('fbb' key select x where y+x=r); /* Addition is commutative */

create function <cfn>(Function f, Vector bpat, Vector args)  
                    -> (Integer cost, Integer fanout) as ...;
e.g.

create function typesofcost(Function f, Vector bpat, Vector args) 
                             -> (Integer cost, Integer fanout) as foreign ...;

   costhint(Charstring fn,Charstring bpat,Vector ch)->Boolean  

   costhint("number.sqroots->number","bf",{4,2}); 
   costhint("number.sqroots->number","fb",{2,1}); 

	Datasource
	    |
	Relational
            |
	  Jdbc

   jdbc("db1","com.mysql.jdbc.Driver");

   connect(Relational r, Charstring database, Charstring username, Charstring password) -> Relational

   disconnect(Relational r) -> Boolean

   relational_named(Charstring nm)-> Relational

   tables(Relational r) 
       -> Bag of (Charstring table, Charstring catalog, 
                  Charstring schema, Charstring owner)

    tables(relational_named("db1"));

    has_table(Relational r, Charstring table_name) -> Boolean

    has_table(relational_named("db1"),"SALES");

    columns(Relational r, Charstring table_name) 
        -> Bag  of (Charstring column_name, Charstring column_type)

     cardinality(Relational r, Charstring table_name) -> Integer

     cardinality(relational_named("db1"),"SALES");

    primary_keys(relational r, charstring table_name) 
             -> Bag of (charstring  column_name, charstring constraint_name)

     primary_keys(relational_named("db1"),"CUSTOMER");

     imported_keys(Jdbc j, Charstring fktable) 
               -> Bag of (Charstring pktable, Charstring pkcolumn, Charstring fkcolumn)

     imported_keys(relational_named("db1"),"PERSON_TELEPHONES");

     exported_keys(Jdbc j, Charstring pktable) 
               -> Bag of (Charstring pkcolumn, Charstring fktable, Charstring fkcolumn)

    import_table(Relational r, Charstring table_name) -> Mapped_type

    import_table(relational_named("db1"),"SALES");

      import_table(Relational r, 
                   Charstring table_name, 
                   Boolean updatable)
               -> Mappedtype mt

      import_table(relational_named("db1"),"COUNTRY",true);

      create Country_db1(currency,country) instances ("Yen","Japan");
      set currency(c)= "Yen" from Country_db1 c where country(c)= "Japan";

      import_table(Relational r, Charstring catalog_name,
                   Charstring schema_name, Charstring table_name,
                   Charstring typename, Boolean updatable,
                   Vector supertypes) -> Mappedtype mt

      import_table(Relational r, 
                   Charstring table_name, 
                   Charstring typename, 
                   Boolean updatable,
                   Vector supertypes) -> Mappedtype mt

      import_table(Relational r,
                   Charstring table_name, 
                   Charstring type_name, 
                   Boolean updatable) -> Mappedtype mt

=(Object x, Object y) -> Boolean   (infix operator =)
!=(Object x, Object y) -> Boolean  (infix operator !=) 
>(Object x, Object y) -> Boolean   (infix operator >) 
>=(Object x,Object y) -> Boolean   (infix operator >=)
<(Object x, Object y) -> Boolean   (infix operator <) 
<=(Object x,Object y) -> Boolean   (infix operator <=)

7.2 Arithmetic functions

abs(Number x) -> Number y
div(Number x, Number y)   -> Number z         Infix operator /
max(Object x, Object y)   -> Object z
minus(Number x, Number y) -> Number z         Infix operator - 
mod(Number x, Number y) -> Number z
plus(Number x, Number y)  -> Number z         Infix operator +
times(Number x, Number y) -> Number z         Infix operator * 
power(Number x, Number y) -> Number z         Infix operator ^ 
iota(Integer l, Integer u)-> Bag of Integer z 
sqrt(Number x) -> Number z
integer(Number x) -> Integer i                Round x to nearest integer
real(Number x) -> Real r                      Convert x to real number
roundto(Number x, Integer d) -> Number        Round x to d decimals
log10(Number x) -> Real y

for each Integer i where i in iota(1,n) 
        print(1);

7.3 String functions

   aggfn(Bag of Type1 x) -> Type2

   sum(Bag of Number x) -> Number 
   count(Bag of Object x) -> Integer
   avg(Bag of Number x) -> Real
   stdev(Bag of Number x) -> Real
   max(Bag of Object x) -> Object
   min(Bag of Object x) -> Object

in(Bag of Object b) -> Bag
in(Vector v) -> Bag

in({1,2,2}); =>
1
2
2

count(Bag of Object b) -> Integer

count(iota(1,100000)); =>
100000

sum(Bag of Number b) -> Number 

sum(iota(1,100000)); =>
705082704

avg(Bag of Number b) -> Real

avg(iota(1,100000)); =>
50000.5

stdev(Bag of Number b) -> Real

stdev(iota(1,100000)); =>
28867.6577966877

max(Bag of TP b) -> TP r
maxagg(Bag of Tp b) -> TP r

max(bag(3,4,2))+2; =>
6

min(Bag of TP b) -> TP r
minagg(Bag of Tp b) -> TP r

minagg(bag(3,4,2))+2; =>
4

notany(Bag of Object b) -> Boolean b

notany(bag()); =>
TRUE

notany(select n from number n where n>5 and n in {1,2,3});
TRUE

some(Bag of Object b) -> Boolean b

some(iota(1,1000000)); =>
TRUE

some(select n from number n where n in {1,2,3} and n < 10); =>
TRUE

concatagg(Bag of Object b)-> Charstring s

concatagg(bag("ab",2,"cd")); =>
"ab2cd"

concatagg(inject(bag("ab",2,"cd"),",")); =>
"ab,2,cd"

7.4.1 Generalized aggregate functions

unique(Bag of TP b) -> Bag of TP r

unique(bag(1,2,1,4)); =>
1 
2 
4

exclusive(Bag of TP b) -> Bag of TP r

exclusive(bag(1,2,1,4)); =>
2
4

inject(Bag of Object b, Object x) -> Bag of Object r

inject(bag(1,2,3),0); =>
1
0
2
0
3

  topk(Bag b, Number k) -> Bag of (Object rk, Object value)
  leastk(Bag b, Number k) -> Bag of (Object rk, Object value)

If the tuples in b have only one attribute (the rk attribute) the value will be nil. The limit clause of select statements provide a more general way to do this, do these functions are normally not used.

For example,
  topk(iota(1, 100), 3);
returns

7.5 Temporal functions

7.6 Sorting functions

Sorting by tuple order

sort(Bag b)->Vector
sort(Bag b, Charstring order)->Vector

Amos 1> sort(1-iota(1,3));
=> {-2,-1,0}
Amos 2> sort(1-iota(1,3),'dec');
{0,-1,-2}
Amos 3> sort(select i, 1-i from Number i where i in iota(1,3));
{{1,0},{2,-1},{3,-2}}

sortbagby(Bag b, Integer pos, Charstring order) -> Vector
sortbagby(Bag b, Vector of Integer pos, Vector of Charstring order)->Vector

Amos 1> sortbagby((select i, mod(i,2) from Number i where i in iota(1,3)),1,'dec');
=> {{3,1},{2,0},{1,1}}
Amos 2> sortbagby((select i, mod(i,2) from Number i where i in iota(1,3)),{2,1},{'inc','inc'});
=> {{2,0},{1,1},{3,1}}

sortvector(Vector v1, Function compfno) -> Vector
sortvector(Vector v, Charstring compfn) -> Vector
sortbag(Bag b, Function compfno) -> Vector 
sortbag(Bag b, Charstring compfn) -> Vector      

create function younger(Person p1, Person p2) -> Boolean 
as age(p1) < age(p2); 

/* Sort all persons ordered by their age */
sortbag((select p from Person p), 'YOUNGER');

generic(Function f) -> Function
returns the generic function of a resolvent.

resolvents(Function g) -> Bag of Function
returns the resolvents of an overloaded function g.

resolvents(Charstring fn) -> Bag of Function
retturns the resolvents of an overloaded function named fn.

resolventtype(Function f) -> Bag  of Type
returns the types of only the first argument of the resolvents of function resolvent f.

arguments(Function r) -> Bag of Vector
returns vector describing arguments of signature of resolvent r. Each element in the vector is a triplet (vector) describing one of the arguments with structure {type,name,uniqueness} where type is the type of the argument, name is the name of the argument, and uniqueness is either key or nonkey depending on the declaration of the argument.. For example,
arguments(#'timespan'); 
--> {{#[OID 371 "TIMEVAL"],"TV1","nonkey"},
     {#[OID 371 "TIMEVAL"],"TV2","nonkey"}}

results(Function r) -> Bag of Vector
Analogous to arguments for result (tuple) of function.

arity(Function f)-> Integer
returns the number of arguments of function.

width(Function f) -> Integer
returns the width of the result tuple of function f.

7.8 Searching source code

sourcecode(Function f) -> Bag of Charstring
sourcecode(Charstring fname) -> Bag of Charstring
returns the sourcecode of a function f, if available. For generic functions the sources of all resolvents are returned. For example:
sourcecode("sqrt");

To find all functions whose definitions contain the string 'tclose' use:
     select sc
     from Function f, Charstring sc
     where like_i(sc,"*tclose*") and source_text(f)=sc;

usedwhere(Function f) -> Function c
returns the functions calling the function f.

useswhich(Function f) -> Function c
returns the functions called from the function f.

userfunctions() -> Bag of Function
returns all user defined functions in the database.

usertypes() -> Bag of Type
returns all user defined types in the database.

allfunctions(Type t)-> Bag of Function
returns all functions where one of the arguments are of type t.

7.9 Extents

7.10 Query optimizer tuning

optmethod(Charstring new) -> Charstring old
sets the optimization method used for cost-based optimization in Amos II to the method named new. 
Three optimization modes for AmosQL queries can be chosen:
"ranksort": (default) is fast but not always optimal.  
"exhaustive": is optimal but it may slow down the optimizer considerably.
"randomopt": is a combination of two random optimization heuristics:
Iterative improvement and sequence heuristics [Nas93].        

optlevel(Integer i,Integer j);

reoptimize(Function f) -> Boolean
reoptimize(Charstring fn) -> Boolean
reoptimizes function named fn. 

7.11 Indexing

For example, in the following function there can be only one name per person:

      create function name(Person)->Charstring as stored;

      create function names(Person p)->Bag of Charstring nm as stored;

      create function name(Person p)->Charstring nm key as stored;

      create_index(Charstring fn, Charstring arg, Charstring index_type,
                   Charstring uniqueness)

      create_index("person.name->charstring", "nm", "hash", "unique");
      create_index("names", "charstring", "mbtree", "multiple");

fn: The name of a stored function. Use the resolvent name for overloaded functions.

arg: The name of the argument/result parameter to be indexed. When unambiguous, the names of types of arguments/results can also be used here.

index_type: The kind of index to put on the argument/result. The supported index types are currently hash indexes (type hash), ordered main-memory B-tree indexes (type mbtree), and X-tree spatial indexes (type xtree). The default index for key/nonkey declarations is hash.

uniqueness: Index uniqueness indicated by unique for unique indexes and multiple for non-unique indexes.

Indexes are deleted by the procedural system function:

      drop_index(Charstring functioname, Charstring argname); 

To save space it is possible to delete the default index on the first argument of a stored function. For example, the following stored function maps parts to unique identifiers through a unique hash index on the identifier:

      create type Part; 
      create function partid(Part p)->Integer id as stored;

      drop_index('partid', 'p');

7.12 Clustering

For example, to cluster the properties name and address of persons one can define:  

create function personprops(Person p) -> 
        (Charstring name,Charstring address) as stored; 
create function name(Person p) -> Charstring nm 
  as select nm 
       from Charstring a 
      where personprops(p) = (nm,a); 
create function address(Person p) -> Charstring a 
  as select a 
       from Charstring nm 
      where personprops(p) = (nm,a);

7.13 Unloading

7.14 Miscellaneous

goovi();

redirect-stmt ::= '<' string-constant

< 'person.amosql';