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}

2.7.5 Plotting numerical data

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

2.8 Accessing data in files

2.8.1 Reading tuples from a file

"This is the first line" another word
1 2 3 4.45 2e9
"This line is parsed into two fields" 3.14

{"This is the first line","another","word"}
{1,2,3,4.45,2000000000.0}
{"This line is parsed into two fields",3.14}

2.9 Cursors

Syntax:

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";

The fetch-cursor-stmt fetches the next result tuple from the scan; i.e. the tuple is removed from the scan. 

If present in a fetch-cursor-stmt, the into clause will bind elements of the first result tuple to AmosQL interface variables. There must be one interface variable for each element in the next cursor tuple.

If no into clause is present in a fetch-cursor-stmt, a single result tuple is fetched and displayed.

The close-cursor-stmt deallocates the scan.

Cursors allow iteration over very large bags created by queries or function calls. For example,

Cursors are implemented using a special datatype called Scan that allows iterating over very large bags of tuples. The following functions are available for accessing the tuples in a scan as vectors:

next(Scan s)->Vector
moves the cursor to the next tuple in a scan and returns the cursor tuple. The fetch-cursor-statement is based on this function.

this(Scan s)->Vector
returns the current tuple in a scan without moving the cursor forward.

peek(Scan s)->Vector
returns the next tuple in a scan without moving the cursor forward.

3 Procedural functions

Syntax: 

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 makestudent(Object o,Integer sc) -> Boolean 
                as add type Student(score) to o (sc); 

 makestudent(:p,30); 

 create function flatten_incomes(Integer threshold) -> Boolean 
                as for each Person p where income(p) > threshold 
                     set income(p) = income(p) - 
                                     (income(p) - threshold) / 2;

 flatten_incomes(1000);

 create function sumb2(Bag of Number b1, Bag of Number b2)->Number
   as begin declare Number r, Number x1, Number x2, Scan c1, Scan c2;
            open c1 for b1;
            open c2 for b2;
            set r = 0;
            while more(c1) and more(c2)
            do fetch c1 into x1;
               fetch c2 into x2;
               set r = r + x1*x2;
               return r;
            end while;
      end;
 
sumb2(iota(1,10),iota(10,20));

A procedural function may return a bag of result values iteratively. The return-stmt statement is used for this, where an element of the result bag is returned every time it is executed.  The return-stmt does not not abort the control flow (different from, e.g., return in C), but it only specifies that a value is to be emitted to the return bag of the function and then the procedure evaluation is continued as usual. If return-stmt is never called the result of the procedural function is empty. Usually the result type of procedural functions not returning any value is Boolean.

3.1 Iterating over results

The for-each statement iterates over the result of the query specified by the variable declarations (variable-declaration-commalist) and the where clause executing the for-each-body for each result variable binding of the query. For example the following function adds inc to the incomes of all persons with salaries higher than limit and returns their old incomes: 

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

Amos II databases can be manipulated using SQL as an alternative to AmosQL. The SQL preprocessor translates SQL commands to corresponding AmosQL statements. The SQL preprocessor is called using a special foreign function:

To make an Amos II function be queried using SQL its name must be prefixed with 'sql:'. Thus an Amos II function whose name is sql:<table> can be regarded as a table named <table> which is queried and updated using SQL statements passed as argument to the foreign function sql.

For example, assume we define the stored functions:

Then we can populate the tables by the following calls to the sql function:

Examples of SQL queries are:

The parser is based on the SQL-92 version of SQL. Thus, the SQL processor allows an Amos II database be both updated and queried using SQL-92. The parser passes most of the SQL-92 validation test.  However, SQL views are not supported. For further details see http://www.it.uu.se/research/group/udbl/Theses/MarkusJagerskoghMSc.pdf.

The command line option

will make Amos II accept SQL as query language in the top loop rather than AmosQL.

5 Peer management

5.1 Peer communication

   nameserver(Charstring name)->Charstring
The function makes the current stand-alone database into a name server and registers there itself as a peer with the given name. If name is empty ("") the name server will become anonymous and not registered as a peer. It can be accessed under the peer name "NAMESERVER" through.

   listen()
The function starts the peer listening loop. It informs the name server that this peer is ready to receive incoming messages. The listening loop can be interrupted with CTRL-C and resumed again by calling listen(). The name server must be listening before any other peer can register.

   register(Charstring name)->Charstring
The function registers in the name server the current stand-alone database as a peer with the given name.  The system will complain if the name is already registered in the name server.  The peer needs to be activated with listen() to be able to receive incoming requests. The name server must be running on the local host.

   register(Charstring name, Charstring host)->Charstring
Registers the current database as a peer in the federation name server running on the given host. Signals error if peer with same name already registered in federation.

   reregister(Charstring name)->Charstring
   reregister(Charstring name, Charstring host)->Charstring
as register but first unregisters another registered peer having same name rather than signaling error. Good when restarting peer registered in name server after crash so the crashed peer will be unregistered.  

   this_amosid()->Charstring name
Returns the name of the peer where the call is issued. Returns the string "NIL" if issued in a not registered standalone Amos II system.

   other_peers()->Bag of Charstring name
Returns the names of the other peers in the federation managed by the name server.

   ship(Charstring peer, Charstring cmd)-> Bag of Vector
Ships the AmosQL command cmd for execution in the named peer. The result is shipped back to the caller as a set of tuples represented as vectors.  If an error happens at the other peer the error is also shipped back.

   call_function(Charstring peer, Charstring fn, Vector args, Integer stopafter)-> Bag of Vector
Calls the Amos II function named fn with argument list args in peer. The result is shipped back to the caller as a set of tuples represented as vectors. The maximum number of tuples shipped back is limited by stopafter. If an error happens at the other peer the error is also shipped back.

   send(Charstring peer, Charstring cmd)-> Charstring peer
Sends the AmosQL command cmd for asynchronous execution in the named peer without waiting for the result to be returned. Errors are handled at the other peer and not shipped back.

   send_call(Charstring peer, Charstring fn, Vector args)-> Boolean
Calls the Amos II function named fn with argument list args asynchronously in the named peer without waiting for the result to be returned. Errors are handled at the other peer and not shipped back.

   broadcast(Charstring cmd)-> Bag of Charstring
Sends the AmosQL command cmd for asynchronous execution in all other peers. Returns the names of the receiving peers.

   hostname()->Charstring name
Returns the name of the host where the current peer is running.

   kill_peer(Charstring name)->Charstring
Kills the named peer. If the peer is the name server it will not be killed, though. Returns the name of the killed peer.

   kill_all_peers()->Bag of Charstring
Kills all peers. The name server and the current peer will still be alive afterwards. Returns the names of the killed peers.

   kill_the_federation()->Boolean
Kills all the peers in the federation, including the name server and the peer calling kill_the_federation.

   is_running(Charstring  peer)->Boolean
Returns true if peer is listening.

   wait_for_peer(Charstring peer)->Charstring
Waits until the peer is running and then returns the peer name.

   amos_servers()->Bag of Amos
Returns all peers managed by the name server on this computer. You need not be member of federation to run the function.

5.2 Peer queries and views

Once you have established connections to Amos II peers you can define views of data from your peers. You first have to import the meta-data about selected types and functions from the peers. This is done by defining proxy types and proxy functions [RJK03] using the system function import_types:

   import_types(Vector of Charstring typenames,  Charstring p)-> Bag of Type pt
defines proxy types pt for types named typenames in peer p. Proxy functions are defined for the functions in p having the imported types as only argument. Inheritance among defined proxy types  is imported according to the corresponding  inheritance relationships between imported types in the peer p.

Once the proxy types and functions are defined they can transparently be queried. Proxy types can be references using @ notation to reference types in other peers.
For example,
   select name(p) from Person@p1;
selects the name property of objects of type Person in peer p1.

import_types imports only those functions having one of typenames as its single arguments. You can import other functions using system function import_func:

   import_func(Charstring fn, Charstring p)->Function pf
imports a function named fn from peer p returning proxy function pf.

On top of the imported types object-oriented multi-peer views can be defined, as described in [RJK03] consisting of derived types [JR99a] whose extents are derived through queries intersecting extents of other types, and IUTs [JR99b] whose extents reconciles unions of other type extents. Notice that the implementation of IUTs is limited. (In particular the system flag latebinding('OR'); must be set before IUTs can be used and this may cause other problems).

6 Accessing external systems

A general wrappers for relational databases using JDBC is predefined in Amos II.

6.1 Foreign and multi-directional functions

1. A filter which is a predicate, indicated by a foreign function whose result is of type Boolean, e.g. '<', that succeeds when certain conditions over its results are satisfied.
2. A computation that produces a result given that the arguments are known, e.g. + or sqrt. Such a function has no argument nor result of type Bag.
   
3. A generator that has a result of type Bag. It produces the result as a bag by generating a stream of several  result tuples given the argument tuple, e.g. iota or the function sending SQL statements to a relational database for evaluation where the result is a bag of tuples.
4. An aggregate function has some argument of type Bag but not result of type Bag. It iterates over one or several bag argument to compute some aggregate value over the bag, e.g. average.
5. A combiner has has both one or several arguments of type Bag and some result of type Bag. It combines one or several bags to form a new bag. For example, basic join operators can be defined as combiners.
   

    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 */ 

1. Implement each foreign function capability using the interface of the implementation language. For Java this is explained in [ER00]  and for C and Lisp in  [Ris00a].
2. In case the foreign code is statically linked C/C++ the implementation must be linked to the kernel by making a driver program that is referencing it as an external function [Ris00a]. This is needed for foreign functions in C, but not for Java or Lisp foreign functions which are dynamically loaded into the system.
3. Statically linked C/C++ code must also be assigned a symbolic name to be referenced in the foreign function capability definition (sqrts and square in the example) [Ris00a].
4. Finally the multidirectional foreign function needs to be defined through a foreign function definition in AmosQL as above. Here the implementor may associate a binding pattern and an optional cost estimate with each capability. Normally the foreign function definition is done separate from the actual code implementing its capabilities, in an AmosQL script.

6.1.1 Cost estimates

• The cost is an estimate of how expensive it is to completely execute (emit all tuples of) a foreign function for given arguments.
• The fanout estimates the expected number of elements in the result stream (emitted tuples), given the arguments.

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}); 

6.2 The relational database wrapper

There is a predefined wrapper for relational databases using the JDBC standard Java-based relational database interface. The JDBC wrapper is tested with MySQL Connector,  FireBird's InterClient JDBC driver, and Microsoft's SQLServer driver. 

An instance of type Relational represents a relational database and the functions represents the interface functions to relational databases. The Relational wrapper is an abstract wrapper in the sense that it does not implement a specific data source and thus has no instances. Therefore some of the relational database interface functions of type Relational are defined as abstract functions. In the type hierarchy below for the abstract Relational wrapper there is a specific implemented wrapper for JDBC represented by type Jdbc. The type Jdbc has one instance for each JDBC data source connected to by the system. The type hierarchy is currently:

	Datasource
	    |
	Relational
            |
	  Jdbc

If some other interface than JDBC (e.g. ODBC) is used for a relational database it would require the implementation of a new wrapper and subtype to Relational.

The use of abstract functions makes sure that Amos II will use the type resolution to look for implementations of these functions in any subtype to Relational.

6.2.1 Connecting

   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

6.2.2 Accessing meta-data

   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");

• pktable - The table referenced by the foreign key.
• pkcolumn - The column referenced by the foreign key.
• fkcolumn - The foreign key column in the table.

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

• pkcolumn - The primary key column in the table.
• fktable - The table whose foreign key references the table.
• fkcolumn - The foreign key column in the table that references the table.

6.2.3 Executing SQL

SQL statements

The function sql executes an arbitrary SQL statement as a string:

    sql(Relational r, Charstring query) -> Bag of Vector results

The result is a bag of results tuples represented as vectors. If the SQL statement is an update a single tuple containing one number is returned, being the number of rows affected by the update. Example:

    sql(relational_named("db1"), "select NAME from PERSON where INCOME > 1000 and AGE>50");

Parameterized SQL statements

To execute the same SQL statement with different parameters use:

    sql(Relational r, Charstring query, Vector params) -> Bag of Vector results

The parameters in params are substituted into the corresponding occurrences in the SQL statement, for example:

6.2.4 Object-oriented view of tables

    import_table(Relational r, Charstring table_name) -> Mapped_type

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

For each columns in the table import_table will generate a corresponding derived wrapper function returning the column's value given an instance of the mapped type. For example, a table named person having the column ssn will have a function

 ssn(Person_db1)->Integer 

      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";

The most general resolvent of import_table is:

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

The table resides in the given catalog and schema. If catalog is "", the table is assumed not to be in a catalog. If schema is "", the table is assumed not to be in a schema. typename is the desired name of the mapped type created, as alternative to the system generated concatenation of table and data source name. updatable gives an updatable mapped type. supertypes is a vector of either type names or type objects, or a mixture of both. The effect is that Amos II will perceive the mapped type as an immediate subtype of the types.

      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

6.2.4.1 Importing many-to-many relationships

All tables in relational databases do not correspond to 'entities' in an ER diagram and therefore cannot be directly mapped to types. The most common case is tables representing many-to-many relationships between entities. Typically such tables have two columns, each of which is a foreign key imported from two other tables, expressing the many-to-many relationship between the two. Only entities are imported as types and special types are not generated for such relationship tables. A many-to-many relationship in a relational database corresponds to a function returning a bag in AmosQL, and can be imported using import_relation rather than import_table:

• import_relation(Relational r,
                  Charstring table_name, Charstring argument_column_name, 
                  Charstring result_column_name, Charstring function_name, 
                  Boolean updatable)
              -> Function
  

• table_name - the name of the table containing the relation.
• argument_column_name - the name of the column which is argument of the function.
• result_column_name - the name of the column which is result of the function.
• function_name - the desired name of the function.
• updatable - whether the function should be transparently updatable via set, add, and remove.

For example, assume we have two entities, person and telephone. Most of the time telephones are not regarded as entities in their own respect since nobody would care to know more about a telephone than its number. However, assume that also the physical location of the telephone is kept in the database, so telephones are an entity type of their own.

A person can be reached through several telephones, and every telephone may be answered by several person. The schema looks as follows:

1. import_table(my_relational, 'person');
2. import_table(my_relational, 'telephone');
3. import_relation(my_relational, 'telephone', 'ssn','ext_no','phones', false);
   
4. create function phones(person@my_relational p) -> telephone@my_relational t as select t where phones(ssn(p)) = ext_no(t);
   

Notice that only relationship functions with a single argument and result can be generated, i.e. composite foreign keys are not supported.

6.3 Defining new wrappers

6.3.1 Data sources

6.3.2 Mapped types

6.3.3 Type translation

• amos_type(Datasource ds, Charstring native_type_name) -> Type;
  

for example

amos_type(relational_named("IBDS"),"VARCHAR");

amos_type returns the Amos II type corresponding to the a specific data source.

• wrapped_type(Datasource ds, Type t) -> Charstring typename;
  

for example

wrapped_type(relational_named("IBDS"),typenamed("CHARSTRING"));

returns the data source type corresponding to an Amos II type. Since one external type may correspond to more than one Amos II type wrapped_type is not the inverse of amos_type. 

7 Physical database design

7.1 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 function, Charstring argname, Charstring index_type,
                   Charstring uniqueness)

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

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

argname: 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: Type kind of index to put on the argument/result. The supported index types are currently hash indexes (type hash) and ordered B-tree indexes (type mbtree). 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.2 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);

8 System functions and commands

8.1 Comparison operators

=(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 <=)

8.2 Arithmetic functions

abs(Number x) -> Number y
div(Number x, Number y)   -> Number z         Infix operator /
max(Object x, Object y)   -> Object z 
min(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);

8.3 String functions

8.4 Aggregate functions

count(Bag of Object o) -> Integer c

sum(Bag of Number x) -> Number s 

average(Bag of Number x) -> Real s

maxagg(Bag of TP x) -> TP y

minagg(Bag of TP x) -> TP y

notany(Bag of Object o) -> Boolean b

some(Bag of Object x) -> Boolean b

section(Bag of TP x, Integer start, Integer stop) -> Bag of TP r

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

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

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

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

concatagg(Bag of Object b)-> Charstring s

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

8.5 Temporal functions

8.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}}

Sorting bags by ordering directives

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}}

Sorting bags or vectors with a custom comparison function

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 sorted by their age */
sortbag((select p from Person p), 'YOUNGER');

8.7 Accessing system meta-data

• The types and functions used in a database are accessible through system functions. It is possible to search the database for types and functions and how they relate.
• The goovi browser available from javaamos by calling the system function goovi(); presents the database graphically. It is written completely as an application Java program using AmosQL queries as the only interface to the Amos II kernel.

8.7.1 Type meta-data

subtypes(Type t) -> Bag of Type s
supertypes(Type t) -> Bag of Type s
returns the types immediately below/above type t in the type hierarchy.

allsupertypes(Type t) -> Bag of Type s
returns all types above t in the type hierarchy.

typesof(Object o) -> Bag of Object t
returns the type set of an object.

typeof(Object o) -> Type t
returns the most specific type of an object.

typenamed(Charstring nm) -> Type t
returns the type named nm. Notice that type names are in upper case.

name(Type t) -> Charstring nm
returns the name of the type t.

attributes(Type t) -> Bag of Function g
returns the generic functions having a single argument of type t and a single result.

methods(Type t) -> Bag of Function r
returns the resolvents having a single argument of type t and a single result.

cardinality(Type t) -> Integer c
returns the number of object of type t and all its subtypes.

objectname(Object o, Charstring nm) -> Boolean
returns TRUE if the object o has the name nm.

8.7.2 Function meta-data

allfunctions() -> Bag of Function
returns all functions in the database.

functionnamed(Charstring nm) -> Function
returns the object representing the function named nm. Useful for second order functions.

theresolvent(Charstring nm) -> Function
returns the single resolvent of a generic function named nm. If there is more than one resolvent for nm an error is raised. If fn is the name of a resolvent its functional is returned.  The notation #'...' is syntactic sugar for theresolvent('..');

name(Function fn) -> Charstring
returns the name of the function fn.

signature(Function f)  -> Charstring
returns the signature of f.

• Type extents represent surrogate objects belonging to a particular type.
  • The deep extent of a type is defined as the set of all surrogate objects belonging to that type and to all its descendants in the type hierarchy. The deep extent of a type is retrieved with:

    extent(Type t)->Bag of Object

    For example, to count how many functions are defined in the database call:
    

       count(extent(typenamed("function")));

    To get all surrogate objects in the database call:
       extent(typenamed("object"))
    
    The function allobjects(); does the same.
    
  • The shallow extent of a type is defined as all surrogate objects belonging only to that type but not to any of its descendants. The shallow extent is retrieved with:

    shallow_extent(Type t)->Bag of Object

    For example:

       shallow_extent(typenamed("object"));

    returns nothing since type Object has no own instances.

• Function extents represent the state of stored functions. The extent of a function is the bag of tuples mapping its argument(s) to the corresponding result(s). The function extent returns the extent of the function fn. The extent tuples are returned as a bag of vectors. The function can be any kind of function.
  
  

  extent(Function fn) -> Bag of Vector

  For example, 

     extent(#'coercers'); 
  

  For stored functions the extent is directly stored in the local database. The example query thus returns the state of all stored functions. The state of the local database is this state plus the deep extent of type Object.

  The extent is always defined for stored functions and can also be computed for derived functions through their function definitions. The extent of a derived function may not be computable, unsafe, in which case the extent function returns nothing.

  The extent of a foreign function is always empty.

  The extent of a generic function is the union of the extents of its resolvents.

8.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 f) -> Boolean
reoptimizes function named fn. 

8.11 Unloading

unload(Charstring filename)->Boolean
generates an AmosQL script that restores the current local database state if loaded into an Amos II peer with the same schema as the current one.

excluded_fns()->Bag of Function
set of functions that should not be unloaded. Can be updated by user.

8.12 Miscellaneous

error(Charstring msg) -> Boolean
prints an error message on the terminal and raises an exception. Transaction aborted.

output_lines(Number n) -> Number
controls how many lines to print on standard output before prompting for more lines. By default the system prints the entire result of a query on standard output. The user can interrupt the printing by CTRL-C. However, when running under Emacs and Windows the CTRL-C method to terminate an output may not be effective. As an alternative to CTRL-C output_lines(n) causes the system to prompt for more output lines after n lines have been printed on standard output. output_lines(0) turns off output line control.

quit;
quits Amos II.

goovi();
starts the multi-database browser GOOVI[CR01]. 
This works only under JavaAmos.            

The redirect statement reads AmosQL statements from a file: 

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

< 'person.amosql';

9 References

[CR01]K.Cassel and T.Risch: An Object-Oriented Multi-Mediator Browser. Presented at 2nd International Workshop on User Interfaces to Data Intensive Systems, Zürich, Switzerland, May 31 - June 1, 2001

[ER00] D.Elin and T. Risch: Amos II Java Interfaces,  Uppsala University, 2000.

[JR99a] V.Josifovski, T.Risch: Functional Query Optimization over Object-Oriented Views for Data Integration Journal of Intelligent Information Systems (JIIS), Vol. 12, No. 2-3, 1999.

[JR99b] V.Josifovski, T.Risch: Integrating Heterogeneous Overlapping Databases through Object-Oriented Transformations. In Proc. 25th Intl. Conf. On Very Large Databases, Edinburgh, Scotland, September 1999.

[JR02] V.Josifovski, T.Risch: Query Decomposition for a Distributed Object-Oriented Mediator System . Distributed and Parallel Databases J., Kluwer, May 2002.

[KJR03] T.Katchaounov, V.Josifovski, and T.Risch: Scalable View Expansion in a Peer Mediator System, Proc. 8th International Conference on Database Systems for Advanced Applications (DASFAA 2003), Kyoto, Japan, March 2003.

[LR92] W.Litwin and T.Risch: Main Memory Oriented Optimization of OO Queries Using Typed Datalog with Foreign Predicates, IEEE Transactions on Knowledge and Data Engineering, Vol. 4, No. 6, December 1992 ( http://user.it.uu.se/~udbl/publ/tkde92.pdf). 

[Nas93] J.Näs: Randomized optimization of object oriented queries in a main memory database management system, MSc thesis, LiTH-IDA-Ex 9325 Linköping University 1993.

[Ris00a] T.Risch: Amos II External Interfaces, Uppsala University, 2000.

[Ris00b]T.Risch: ALisp v2 User's Guide, Uppsala University, 2006.

[RJK03] T.Risch, V.Josifovski, and T.Katchaounov: Functional Data Integration in a Distributed Mediator System, in P.Gray, L.Kerschberg, P.King, and A.Poulovassilis (eds.): Functional Approach to Data Management - Modeling, Analyzing and Integrating Heterogeneous Data, Springer, ISBN 3-540-00375-4, 2003.