Amos II User`s Manual Abstract Acknowledgements 1 Running
Contents
1. Page 1 of 30 AMOS II Documentation Download News UDBL Amos II User s Manual 1 Running AMOS II 2 AMOSQL 2 1 Types Amos II Beta Release 5 2 2 Objects 2 3 Functions Staffan Flodin Vanja Josifovski Timour Katchaounov Tore Risch 2 4 Query statements Martin Sk ld and Magnus Werner 2 5 Cursors June 23 2000 3 Multidatabase system Latest revision April 25 2003 functions 3 1 Data integration primitives Abstract 3 2 ODBC Wrapper 4 Database procedures This working document describes details of how to use the AMOS II 5 Sagas tor long running system In particular it describes the syntax and semantics of the transactions AMOSQL language and the various system functions available You are 6 Physical database design recommended to read the document AMOS I Concepts first which 6 1 Indexing describes the principles of the AMOS II system and gives an overview of 6 2 Clustering the AMOSQL concepts This manual is continously updated as AMOS II 7 System functions and evolves the subtitle refers to the current system release commands 7 1 Comparison operators 7 2 Arithmetic functions Acknowledgements 7 3 String functions TA Bagtagaian iue au The following persons have contributed to the development of the 7 Sorina a AMOS TONIC ae PES 77 Accesdinaihe 6 08 Silvio Brandani Kristoffer Cassel Daniel Elin Marcus Eriksson Gilles svetem Fabre Gustav Fahl Staffan Flodin J rn Gebhardt Bj rn Hellande2. Timeval gt Integer Extract minute from Timeval second Timeval gt Integer Extract second from Timeval year Date gt Integer Extract year from Date month Date gt Integer Extract month from Date day Date gt Integer Extract day from Date hour Time gt Integer Extract hour from Time minute Time gt Integer Extract minute from Time second Time gt Integer Extract second from Time timespan Timeval Timeval gt lt Time Integer usec gt Compute difference in Time and microseconds between two time values 7 6 Sorting functions Currently AMOSQL has no special syntax for sorting Instead there are several that can be used to sort bags or vectors Sort bags or vectors with a custom comparator function This group of sort functions are useful to sort bags or vectors of either objects or tuples with a custom function supplied by the user Either the function object or function named can be supplied The comparator function must take two arguments with types compatible with the elements of the bag or the vector and return a boolean http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 27 of 30 value sort vector vl function compfno gt vector sort vector v charstring compfn gt vector sort bag b function compfno gt vector sort bag b charstring compfn gt vector Example create function younger person pl person p2 gt boolean
3. result stmt quit stmt set interface variable stmt query stmt select stmt function call set interface variable stmt set interface variable name function call 2 1 Creating regular types The create type statement creates a new user type Syntax create type stmt create type type spec under type name commalist properties attr function commalist type spec identifier bag of identifier attr function function name type spec key Type names must be unique across all types The new type will be an subtype of all the supertypes in the subtype of clause If no supertypes are specified the new type becomes a subtype of the system type userobject The attr function commalist clause is optional and provides a way to define attributes for the new type The attributes are functions having a single argument and a single result and are initially stored functions but can be later redefined as other kinds of functions The argument type of an attribute function is the type being created and the result type is specified by the type spec The result type must be previously defined If key is specified for a property it indicates that each value of the attribute is unique 2 1 1 Deleting types The delete type statement deletes a type and all its subtypes Syntax delete type stmt delete type type nam http user it uu se udbl amos doc amos5 16 users guide html 11
4. set s create sub saga only to be used within another saga The syntax for executing something in a saga is as follows saga stmt saga saga procedure body http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 21 of 30 compensation procedure body Sagas are committed and deleted by commit saga s and are aborted and deleted by abort _saga s During abortion of a saga all the compensations are executed until the beginning or until stopped by a call to stop compensation Sagas can be passed to procedures to be executed in the body of the procedure Note that any local variables defined outside the saga statement will have the values in the compensation that they had at the end of execution of the forward transaction of the associated saga statement If such variables are changed after the saga statement is executed this will not be seen in the compensation To support such behavior it is possible to associate data with a saga through functions that are indexed with the current saga can be accessed by current_saga 6 Physical database design This section describes some AMOSQL commands for database space and performance tuning 6 1 Indexing The system supports indexing on any single argument or result of a stored function Indexes can be unique or non unique A unique index disallows storing different values for the indexed argument or result The cardinality const
5. 4 2004 Page 6 of 30 Functions using the deleted type will be deleted as well 2 2 Creating objects The create object statement creates one or more objects and makes the new object s instance s of a given user type and all its supertypes Syntax create object stmt create type nam identifier commalist instances initializer commalist initializer gen variable name gen variable name simple init value commalist Simple init value single value collection value single value gen variable name constant collection value bag value vector value bag value bag single value commalist vector value vector single value commalist single value commalist Example create person name age instances sadam Adam 26 eve Eve 32 create person instances olof create person parents instances tore bag adam eve The new objects are assigned initial values for the specified attributes The attributes can be any updatable AMOSQL functions of a single argument and value One object will be created for each initializer Each initializer can have an optional variable name which will be bound to the new object The variable name can subsequently be used as a reference to the object The initializer also contains a comma separated list of initial values for the specified functions Initial values are specified as constants or varia
6. A limitation is that the internal operating system representation is used for representing Timeval values which means that one cannot specify value too far in the past or future Constants of type Timeval are written as vear month day hour minute second e g 1995 11 15 12 51 32 Constants of type Time are written as hour minute second e g 12 51 32 Constants of type Date are written as year month day e g 1995 11 15 The following functions exist for types Timeval Time and Date now gt Timeval The current absolute time time gt Time The current time of day date gt Date The current year and date timeval Integer year Integer month Integer day Integer hour Integer minute Integer second gt Timeval Construct Timeval time Integer hour Integer minute Integer second gt Time Construct Time date Integer year Integer month Integer day gt Date Construct Date time Timeval gt Time Extract Time from Timeval date Timeval gt Date Extract Date from Timeval date time to timeval Date Time gt Timeval Combine Date and Time to Timeval http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 26 of 30 year Timeval gt Integer Extract year from Timeval month Timeval gt Integer Extract month from Timeval day Timeval gt Integer Extract day from Timeval hour Timeval gt Integer Extract hour from Timeval minute
7. Function g returns the generic functions having a single argument of type t and a single result methods Type t gt Bag of Function r returns the resolvents having a single argument of type t and a single result resolventtype Function fn gt Bag of Type t returns the types of the first arguments of the resolvents of function resolvent fn argrestypes Function fn gt Bag of lt Integer pos Type tp Integer kind gt argrestypes Charstring fname gt Bag of lt Integer pos Type tp Integer kind gt returns for each argument or result of a function resolvent http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 29 of 30 pos The position number 1st is 1 etc type The type kind A number indicating if it is an argument kind O or a result kind 1 cardinality Type t gt Integer c returns the number of object of type t and all its subtypes arity Function f gt Integer a returns the number of arguments of function f width Function f gt Integer w returns the width of the result tuple of function f 7 8 Query optimizer tuning costhint Charstring r Charstring bpat Object q gt Boolean costhint Function r Charstring bpat Object q gt Boolean Declare cost hint q for the AMOSQL resolvent function r and the binding pattern bpat This cost hint feature is explained in AMOS II External Interfaces and in LR92 The cost hint can be a vector of two elements cost fanout in
8. for all the specified property functions in the same manner as in the create object statement 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 name commalist Referential integrity is maintained so that all references to the objects as instances of the specified type cease to exist An object will always be an instance of some type If all user defined types have been removed the object will still be member of userobject 2 3 Creating functions The create function statement creates a new user function http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 8 of 30 Syntax create function stmt create function function name argl spec gt resl spec fn implementation function name specific function name type name list specific function name gt type name list type name list type name type name type name list specific function name identifier argl spec arg spec commalist arg spec simple arg spec bag of simple arg spec simple arg spec type name variable name key nonkey resl spec arg spec multiple result spec multiple result spec lt simple arg spec commalist gt fn implementation as derived body procedure body foreign body stored derived body simple select stmt foreign body forei
9. gt odbc _ds ods Connect to an ODBC datasource using a default user and password connect odbc_ds dsl charstring dsn gt odbc _ ds ds2 Connect to an ODBC datasource using a DSN username password connect http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 17 of 30 odbc _ds dsl charstring dsn charstring usr charstring pass gt odbc _ds ds2 Connect to an ODBC datasource using a DBMS dependent connect string connect native odbc _ds dsl charstring conn string gt odbc_ds ds2 Disconnect from a datasource This is also done automatically at system exit disconnect odbc_ds ds gt boolean Map a relational table to a type The mapping is done as follows a table corresponds to an AMOS type each column of the table corresponds to an AMOS function attribute of this type import _table relational ds charstring table name gt type mapped typ Execute an SQL statement given e the name of an ODBC data source e an SQL statement as a string e a vector of parameters to the query successive in the statement are substituted with the corresponding vector elements e the maximum number of rows to return 1 means unlimited The result is a bag of result rows represented as vectors sql odbc_ds ds charstring query vector params integer maxrows gt bag of vector res ODBC data meta data functions Return information about the tables in a datasourse tables odb
10. specified argument or result of a stored function unique by specifying it as a key If you regard the extent of a function as a relation of tuples the key annotation for an argument or result specifies that the argument result is a key to the extent relation For example create function name person key gt charstring key as stored In this case the system guarantees that there is a one to one relationship between OIDs of type person and their names http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 9 of 30 If the key cardinality constraint is violated by a database update the following error message is printed Update would violate upper object participation updating function The keyword nonkey specifies that the parameter has no cardinality constraint The default cardinality constraint for a stored function is key for the first argument of a stored function and nonkey for all other arguments and the result s This implies that stored functions are by default single valued Since the first argument of a stored function by default is declared as key the function name could thus also have been written create function name person gt charstring key as stored For foreign functions it is up to the implementor to guarantee that specified cardinality constraints hold Cardinality constraint declarations are ignored for derived functions Another example create function married person husb
11. the most flexible way to specify queries http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 13 of 30 Syntax select stmt select distinct into clause from clause where clause expr commalist into clause into gen variable name commalist from clause from variable declaration commalist variable declaration type name variable name bag of type name variable name where clause where predicate expression The expr commalist defines the object s to be retrieved See Predicate expressions for definition of function call The from clause declares types of local variables used in the query The where clause gives a selection criteria for the search The details of the where clause is described below in Predicate expressions The result of a select statement is a bag of single result tuples Duplicates are removed by using the keyword distinct in which case a set is returned An into clause is available for specifying variables to be bound to the result In case more than one result tuple is returned the variables will be bound only to the elements of the first encountered tuple For example Ei lt select p into eve2 from person p where name p name eve2 This query retrieves into the environment variable eve2 the person whose name is Eve 4 3 Predicate expressions The general syntax o
12. 4 Page 12 of 30 e set sets the value of an updatable function given the arguments A boolean function can be set to either true or false Setting the value of a boolean function to false means that the truth value is removed from the function e add adds the specified tuple s to the result of an updatable bag result function analogous to set e remove removes the specified tuple s from the result of an updatable bag result function analogous to set The update statements are not allowed to violate the cardinality constraints key See Query Statements specified by the create type stmt or the create function stmt 2 4 Query statements Queries retrieve objects having specified properties They are specified using the query statement denoting either function calls or select statements Syntax query stmt select stmt function call 2 4 1 Function calls Syntax function call function name parameter value commalist parameter value function call single value simple select stmt simple select stmt select expr commalist for each clause where clause expr eS function call single value The built in functions have equivalent infix syntax with the usual priorities For example income eve income ulla 0 5 is equivalent to times plus income eve income ulla 0 5 2 4 2 The select statement The select statement provides
13. and person wife key gt boolean as stored Polygamous marriages are refused to be stored by the function married since the first argument has the default cardinality constraint key The bag of declaration on the result of a stored function actually just overrides the default key declaration of its Ist argument with nonkey Thus the function parents above could also have been written create function parents person nonkey gt person as stored 2 3 3 Deleting functions Functions are deleted with the delete function statement Syntax delete function stmt delete function function name For example delete function married Deleting a type also deletes all subtypes and all functions using the deleted types 2 3 4 Overloaded functions and late binding Function names may be overloaded i e functions having the same name may be defined differently on different http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 10 of 30 argument types This allows generic functions to apply to several different object types Each specific implementation of an overloaded function is called a resolvent For example assume the two following AMOS II function definitions create function less number i number j gt boolean as select true where i lt j create function less charstring s charstring t gt boolean as select true where s lt t Its resolvents will have the signatures less number number
14. as select TRUE where age p1 lt age p2 Sort all persons sorted by their age sort select p from person p younger Sort bags with a default comparator function Whenever bags of literals tuples of literals are sorted for simplicity one may use the natural order of the literal types Here the tuple positions are 1 based and order is one of inc or dec Sort the bag b by the element at position pos in order specified by order sort bag b integer pos charstring order gt vector Sort the bag of tuples b in default ascending order by the element at position pos sort bag b integer pos gt vector Sort all elements in a bag b by comparing the whole elements in alphabetic order sort _ tuples bag b charstring order gt vector Same as above defaults to ascending order sort bag b gt vector Note Surrogate object will be sorted too but by their OID which usually has no meaning Example sort select i from integer i where i iota 0 10 1 dec 7 7 Accessing the type system allfunctions gt Bag of Function f allfunctions Type t gt Bag of Function f allobjects gt Bag of Object o alltypes gt Bag of Type t returns all functions objects and types respectively in the database http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 28 of 30 allfunctions Type t gt Bag of Function returns all functions where some argument or result is o
15. bles The types of the initial values must match the declared result types of the corresponding functions Bag valued functions are initialized using the keywords bag of syntax bag value Vector result functions are normally initialized with a comma separated list of values enclosed in curly brackets syntax vector value It is possible to specify NIL for a value when no initialization is desired for the corresponding function http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 7 of 30 2 2 1 Deleting objects Objects are deleted from the database with the delete statement Syntax delete object stmt delete gen variable name Referential integrity is maintained by the system which will automatically delete references to the deleted object It is thus also removed from all stored functions where it is referenced Deleted objects are printed as 0ID nnn DELETED The objects may be undeleted by rollback The garbage collector physically removes the OIDs from the database only when their creation has been rolled back or their deletion committed and they are not references from some variable or external system 2 2 3 Updating type memberships The add type stmt changes the type of one or more objects to the specified type Syntax add type stmt add type type name function name commalist to new instances The updated objects may be assigned initial values
16. c_ds ds gt lt charstring table charstring catalog charstring schema charstring owner gt Return information about the columns in a table in a datasourse columns odbc _ds ds charstring table gt lt charstring column name charstring amos_type gt Return information about the primary keys in a table in a datasourse primary keys odbc_ds ds charstring table gt lt charstring column_name charstring constraint name gt Retrive all ODBC data sources odbc _datasources gt lt charstring dsname charstring description gt There are several more functions which take an integer instead of a datasource object this is to be fixed later http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 18 of 30 odbc _catalog integer hconn gt charstring x odbc driver info integer hconn gt lt charstring dname charstring version integer odbc major integer odbc_minorv gt odbc_dbms_ info integer hconn gt lt charstring prodname charstring prodversion gt relational _ds_named charstring nm gt relational rds Example usage session make a new datasource object named ds and connect it to an ODBC data source named ODBCTEST set a odbc ds ds connect a ODBCTEST import _table ds IDAEMP retrieve meta data from the ODBC data source tables ds columns ds IDAEMP primary keys ds IDAEMP direct SQL execution sql ds sel
17. case the cost to execute r is constant It can also be an AMOSQL function returning the cost and the fanout Strings denoting function names can be used instead of function objects optmethod Charstring new gt 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 optmethod returns the old setting of the optimization method Random optimization can be tuned by using the function optlevel Integer i Integer j where i and j are integers specifying number of iterations in iterative improvement and sequence heuristics respectively Default settings is i 5 and j 5 7 9 Miscellaneous http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 30 of 30 eval Charstring stmt gt Object r evaluates the AMOSQL statement stmt print Object x gt Object r prints x on the console quit quits AMOS II exit returns to the program that called AMOS II if the system is embedded in some other system Same as quit for stand alone AMOS IL goovi starts the multi database browser GOOVI CRO1 This works only under JavaAMOS The redirec
18. ect minagg bag of object x gt object some bag of object x gt boolean notany bag of object x gt boolean Notice that sum must be applied only to uniform bags of numbers Subqueries always return bags as their results thus the result of a subquery must be passed to only aggregation operators Local variables in queries may be declared as bags For example totalincomes could also have been written create function totalincomes gt integer as select sum b from bag of Integer b where b select i from Person p Integer i where income p i 2 5 Cursors For queries and function calls returning bag valued results the open cursor stmt and the fetch cursor stmt statements are available to iterate over the result Syntax http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 open cursor stmt open cursor name cursor name gen variable name fetch cursor stmt for fetch cursor name into clause next clause next integer constant close cursor stmt close cursor name For example create person name age instances open cl for select p from person p wher fetch cl into Violal close cl name Violal gt Ao ka Viola Page 15 of 30 select stmt next clause Viola 38 name p Viola A cursor is created by the open cursor stmt and is represented by a bag of result tuples containing objects with unknown types The result of the query
19. ect name dept from idaemp 1 sql ds select name from idaemp where dept IDA 1 sql ds select name from idaemp where ssn 5 10 Some example queries to the ODBC Wrapper select name e salary e ssn e hobby e age e dept e from idaemp ds e select salary e dept e from idaemp ds e select salary e from idaemp ds e where salary e 4 select d from idaemp ds e integer s charstring d where s salary e and s 5 and d dept e finally disconnect from the data source disconnect a 4 Database procedures A database procedure is an AMOS II function defined as a sequence of AMOSQL statements that may have side effects i e database update statements or variable assignments Procedures may return results by using a special result statement Procedures should not be used in queries but this restriction is currently not enforced Most but not all AMOSQL statements are allowed in procedure bodies as can be seen by the syntax below Syntax http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 19 of 30 procedure body block create object stmt delete object stmt for each stmt update stmt add type stmt remove type stmt set variable stmt gquery stmt if stmt result stmt quit stmt block begin procedure body semicolonlist end begin declare variable declaration commalist procedure body se
20. er is increased every time an AMOSQL database update statement is executed For example AMOS 1 gt create type person AMOS 2 gt create type student under person Database changes can be undone by using the rollback statement with a generation number as argument For example the statement AMOS 3 gt rollback 2 will restore the database to the state it had at generation number 2 It thus undoes the effect of the statement create type student under person After the rollback above the type student is removed from the database but not type person 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 gt commit AMOS 1 gt To shut down AMOS II orderly first save the database and then type AMOS 1 gt quit JavaAMOS is a version of the AMOS II kernel connected to the Java virtual machine With JavaAMOS AMOS http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 3 of 30 II foreign functions can be written in Java the callout interface and Java functions can call AMOS II functions and send AMOSQL statements to AMOS II for evaluation the callin interface To start JavaAMOS use the script JavaAMOS instead of amos2 It will enter a top loop reading and evaluating AMOSQL statements as amos2 The multi database browser GOOVI CRO1 is a graphical browser for AMOS II wri
21. es gt integer i as select income p from employee p income p late bound Since income is overloaded with resolvents employee income gt integer and manager income gt integer and both qualify to apply to employees the resolution of income p will be done at run time If only incomes of employees are sought the desired resolvent has to be explicitly specified as employee income gt integer Since the detection of the necessity of dynamic resolution is done at compile time overloading a function name may lead to a cascading recompilation of functions defined in terms of that function name This may take some time For a more detailed presentation of the management of late bound functions see FR95 2 3 5 Function updates Information in AMOSQL can be thought of as mappings from function arguments to results These mappings are either defined at object creation time Creating Objects or altered by one of the function update statements set add or remove Syntax update stmt update op update item for each clause where clause update op set add remove update item function name single value commalist res values Not every function is updatable AMOS II defines a function f to be updatable if it is a stored function or if it is derived from a single updatable function without a join Semantics http user it uu se udbl amos doc amos5 16 users guide html 11 4 200
22. f predicate expressions is predicate expression predicate expression and predicate expression predicate expression or predicate expression predicate expression simple predicate simple predicate function call simple relterm relop simple relterm relterm relterm Ssimple relterm function call single value relterm http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 14 of 30 simple relterm multiple value select statement multiple value lt single value commalist gt relop lt 4 S gt lt LoS 1s In a function call the types of the actual parameters and results must be the same as or subtypes of the types of the corresponding formal parameters or results Resolution of overloaded functions is described in Overloaded Functions and Late Binding Query variables can be bound to bags on which aggregation operators can be applied The comparison operators lt lt and gt are treated gt as binary boolean functions They are all defined for any object gt type 1 4 4 Subqueries and aggregation operators Aggregation operators are defined as functions where one or several arguments are declared as bags bag of type x The following system aggregation operators are defined sum bag of number x gt number count bag of object x gt integer maxagg bag of object x gt obj
23. f type t subtypes Type t gt Bag of Type s supertypes Type t gt Bag of Type s returns the types immediately below above type t in the type hierarchy allsupertypes Type t gt Bag of Type s returns all types above t in the type hierarchy typesof Object o gt Bag of Object t returns the type set of an object typeof Object o gt Type t returns the most specific type of an object functionnamed Charstring nm gt Function fn returns the function named nm Notice that function names are in upper case kindoffunction Function f gt Charstring knd returns the kind of the function f as a string The result can be one of stored derived foreign or overloaded name Function fn gt Charstring nm returns the name of the function fn typenamed Charstring nm gt Type t returns the type named nm Notice that type names are in upper case name Type t gt Charstring nm returns the name of the type t objectname Object o Charstring nm gt Boolean returns TRUE if the object o has the name nm usedwhere Function f gt Function c returns the functions calling the function f useswhich Function f gt Function c returns the functions called from the function f generic Function f gt Function g returns the generic function of a resolvent resolvents Function g gt Bag of Function r returns the resolvents of an overloaded function g attributes Type t gt Bag of
24. g mbtree mutiple The parameters of create index are 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 unambigous 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 mbt ree 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 system procedure drop _index charstring functioname charstring argname The meaning of the parameters are as for function create index There must always be at least one index left on each stored function and therefore the system will never delete the last remaining index on a stored function 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 gt integer id as stored partid will have two indexes one on p and one on id To drop the index on p do the following drop _index partid p 6 2 Clustering Functions can be clustered by creating multiple result stored functi
25. gn string constant The argl spec and the resl spec specify the signature of the function Semantics e The types used in the declarations must be previously defined The name of an argument or result parameter can be left unspecified if it is never referenced in the function implementation The names of the argument and result parameters for a given function definition must be unique e bag of specifications on a single result parameter declares it to be a bag Derived functions always have implicit bag of results e Derived functions can also have arguments declared bag of making them aggregation operators NOTICE Stored functions cannot be aggregation operators bag of declarations for results of derived functions are ignored e AMOSQL functions may also have multiple results indicating that a logical tuple of values is returned This is indicated by bracketing the result declarations see syntax for multiple result spec and example below Derived AMOSQL functions are defined by a single query select statement 2 3 2 Specifying cardinality constraints A cardinality constraint is a system maintained restriction on the number of allowed occurrences in the database of an argument or result of a function For example a cardinality constraint could be that there can be at most one salary and one name per person while a person may have any number of parents The only cardinality constraint which is currently supported in AMOSQL is to make a
26. gt boolean less charstring charstring gt boolean Internally the system stores the resolvents under different function names The name of a resolvent is obtained by concatenating the type of its arguments with the name of the overloaded function followed by the symbol gt and the type of the result syntax in Query Statements The two resolvents above will be given the names number number less gt boolean and charstring charstring less gt boolean Overloaded function resolvents are allowed to differ on their argument types and the result types 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 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 try to use local variable declarations to choose the correct resolvent early binding For example create function younger person p person q gt boolean as select less age p age q will choose the resolvent number number less gt boolean since age returns integers and the resolvent number number 1less gt boolean is applicable to integers by inheritance The other function resolvent charstring charstring less gt boolean does not qualify since it is not legal to apply to arguments of type integer On the other hand th
27. ifier e Interface variables hold temporary query results during a session Interface variables cannot be referenced in function bodies and are not consirered as being parts of the database Syntax interface variable name identifier gen variable name variable name interface variable name Constants Constants can be integers reals strings or booleans Syntax constant scs integer constant real constant boolean constant string constant nil integer constant digit list real constant digit list digit list boolean constant true false string constant string separator character list string separator string separator tire rue The surrounding string separators in string constants must be the same Comments The comment statement can be placed anywhere outside identifiers and constants Syntax comment character list Statements The following statements can be entered to the AMOS II top loop Their details are described hereafter create type stmt delete type stmt create object stmt delete object stmt add type stmt remove type stmt create function stmt http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 5 of 30 delete function stmt update stmt query stmt open cursor stmt fetch cursor stmt close cursor stmt block for each stmt set variable stmt if stmt
28. is always materialized and stored in the cursor bag The fetch cursor stmt fetches the first resul t tuple from the cursor i e the tuple is removed from the front of the cursor bag NIL is returned if there are no more result tuples left in the cursor 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 result tuple If present ina fetch cursor stmt the next clause will display the specified number of result tuples and remove them from the cursor bag If neither a next nor an into clause is present ina fetch cursor stmt a single result tuple is fetched and displayed The close cursor stmt empties the cursor It is sometimes useful to count the number of re create function ageofpersonnamed chars as select age p from person p open cl for select ageofpersonnamed count cl al sult tuples in a cursor bag tring nm gt integer a where name p nm Eve 3 Multidatabase system functions The following AMOSQL system functions and http user it uu se udbl amos doc amos5 16 users guide html procedures are available for inter mediator communication 11 4 2004 Page 16 of 30 amos servers gt bag of charstring Returns the names of the mediator servers registered in the nameserver listen Starts the mediator server listening loop The loop can be interr
29. is function create function nameordered person p person q gt boolean as select less name p name q will choose the resolvent charstring charstring less gt boolean In both cases the resolution will be done at compile time Dynamic type resolution is also done for top level function call to choose the correct resolvent For example less 1 2 will choose number number less gt boolean http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 11 of 30 To avoid the overhead of dynamic type resolution one may use the dot notation number number less gt boolean 1 2 AMOS II also supports late binding of overloaded functions where the overload resolution is done at run time instead of at compile time For example suppose that managers are employees whose incomes are the sum of the income as a regular employee plus some manager bonus create typ mploy under person create type manager under employee create function mgrbonus manager gt integer as stored create function income employee gt integer as stored create function income manager m gt integer i as select employee income gt integer m mgrbonus m Now suppose that we need a function that returns the gross incomes of all persons in the database i e we use manager income gt integer for managers and employee income gt integer for non manager In AMOS II such a function is defined as create function grossincom
30. micolonlist end result stmt result expr for each stmt for each distinct variable declaration commalist where clause procedure body if stmt if predicate expression then procedure body else procedure body set variable stmt set gen variable name function call Examples create function creperson charstring nm integer inc gt person p as begin create person instances p set name p nm set income p inc result p end set p creperson Karl 3500 create function makestudent object o integer sc gt boolean as add type student score to o sc makestudent p 30 create function flatten incomes integer threshold gt boolean as for each person p where income p gt threshold set income p income p income p threshold 2 flatten incomes 1000 Procedures are compiled at creation time Procedures may return bags of results The result stmt is used for this where expr is returned as the result from the procedure http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 20 of 30 The for each stmt construct can be used to iterate over the result of a query For example the following function adds inc to the incomes of all persons with salaries higher than 1imit and returns their old incomes create function increase incomes integer inc integer limit gt integer oldinc as for each person p integer i where i g
31. ons and then each individual function can be defined as a derived function For example to cluster the properties name and address of persons one can define http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 delete function person name gt charstring create function personprops person p gt create function name person p gt charstring nm as select nm from charstring a where personprops p lt nm a gt create function address person p gt charstring a as select a from charstring nm where personprops p lt nm a gt lt charstring name charstring address gt as stored Page 23 of 30 Clustering does not improve the execution time performance significantly in a main memory DBMS such as AMOS II However clustering can decrease the database size considerably 7 System functions and commands 7 1 Comparison operators The built in infix comparison operators are object x object y gt boolean object x object y gt boolean infix operator object x object y gt boolean infix operator infix operator infix operator infix operator infix operator object x object y gt boolean object x object y gt boolean object x object y gt gt lt lt gt boolean NAV N Wh lie sl nen lt sit All objects can be compared Strings are compared by characters lists by elements OIDs by identifier numbers Eq
32. r a 5 Guer optimizer utihg Vanja Josifovski J onas Karlsson Timour Katchaounov Salah Eddine 7 9 Miscellaneous Machani Joakim N s Kjell Orsborn Thomas Padron McCarthy Tore E Risch Andreas Sj stedt Martin Sk ld Rickard Svensson and Magnus Werner 1 Running AMOS H It is recommended that each user creates a private directory for AMOS II lt privdir gt You should then do cd lt privdir gt and copy the following files to lt privdir gt amos2 exe amos2 dmp amos dll AMOS II is then ready to run in lt privdir gt by the command amos2 lt db gt http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 2 of 30 where lt db gt is an optional name of an AMOS II database image default is amos2 dmp The system enters an AMOS II top loop where it reads AMOSQL statements executes them and prints their results You need not connect to any particular database but instead if lt db gt is omitted the system enters an empty database where only the system objects are defined When the AMOS II database is defined and populated it can be saved on disk with the AMOSQL statement save filename NOTICE You cannot save using the name amos2 dmp since that would overwrite the system database In a later session you can connect to the saved database by starting AMOS II with amos2 filename The prompter in the AMOS II top loop is Amos n gt where n is a generation number The generation numb
33. raint key of stored functions See Cardinality Constraints is implemented as unique indexes Thus by default the system puts a unique index on the first argument of stored functions That index can be made non unique by suffixing the first argument declaration with the keyword nonkey or to specify bag of for the result in which case a non unique index is used instead For example in the following function there can be only one name per person create function name person gt charstring as stored By contrast names allow more than one name per person create function names person p nonkey gt charstring nm as stored Alternative definition of names create function names person p gt bag of charstring nm as stored Any other argument or result declaration can also be suffixed with the keyword key to indicate the position of a unique index For example the following definition puts a unique index on nm to prohibit two persons to have the same name http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 22 of 30 create function name person p gt charstring nm key as stored Indexes can also be explicitly created on any argument or result with a system procedure create_index create index charstring function charstring argname charstring index type charstring uniqueness For example create index person name gt charstring nm hash unique create index names charstrin
34. returns TRUE like ac a c returns TRUE like ac a2c fails like i Charstring string Charstring pattern gt Boolean Case insensitive like substring Charstring string Integer start Integer end gt Charstring Extract substring from given character positions First character has position 0 upper Charstring gt Charstring Uppercase string 7 4 Aggregation functions Some of these system functions are described in Subqueries and Aggregation Operators Number of objects in bag o Subqueries and Aggregation Operators count bag of object o gt integer c Extract elements of collections in bag of object b gt bag of object o in vector v gt bag of object o Largest object in bag maxagg bag of object x gt object y Smallest number in bag minagg bag of object x gt object y Test if bag empty Logical NOT EXISTS http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 25 of 30 notany bag of object o gt boolean b Test if there are any elements in bag Logical EXISTS some bag of object x gt boolean b Sum uniform bags of numbers sum bag of number x gt number s 7 5 Temporal functions Amos II supports three data types for referencing Time Timeval and Date Type Timeval is for specifying absolute time points including year month and time of day The type Date specifies just year and date and type Time specifies time of day
35. t limit and i income p begin result i set income p i inc end NOTICE result stmt does not not change the control flow different from e g return in C but it only specifies that a value is to be added to the result bag of the function and then the procedure evaluation is continued as usual The for each stmt does not return any value at all unless result stmt is used within its body NOTICE Queries and updates embedded in procedure bodies are optimized at compile time The compiler saves the optimized query plans in the database so that dynamic query optimization is not needed when procedures are executed 5 Sagas for long running transactions The AMOS II transaction system has been extended with sagas Sagas are first class objects and can be used to chain a sequence of committed transactions with compensating transactions The sequence of sagas can be nested by defining sub sagas Abortion of a saga causes all the compensations to be executed and the sagas and sub sagas to be deleted Committing a saga just causes deletion since the transactions are already committed Compensation of one saga is done in one complete sequence unless stopped If an application needs to schedule sagas forward and backward in smaller steps it is possible to orchestrate many sagas through a saga layer as part of the application outside AMOS IL Sagas are created by the following function calls set s create saga or
36. t statement reads AMOSQL statements from a file redirect stmt lt string constant For example lt person amosql References FR95 S Flodin T Risch Processing Object Oriented Queries with Invertible Late Bound Functions Proc VLDB Conf Z rich Switzerland 1995 CRO1 K Cassel 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 LR92 W Litwin T Risch Main Memory Oriented Optimization of OO Queries Using Typed Datalog with Foreign Predicates JEEE Transactions on Knowledge and Data Engineering Vol 4 No 6 December 1992 http www dis 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 http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004
37. tten as a Java application You can start the GOOVI browser from the JavaAMOS top loop by calling the AMOS II foreign function goovi It will start the browser in a separate thread 2 AMOSQL This section describes the syntax of AMOSQL and explains some semantic details not described elsewhere For the syntax we use BNF notation with the following special constructs A B C A consists of B followed by C B C alternatively B C A consists of B or C B A consists of B or nothing B list A consists of one or more Bs B commalist A consists of one or more Bs separated by commas xxx The string keyword xxx A A A A AMOSQL statements are always terminated by a semicolon Identifiers Identifiers have the syntax identifier _ letter identifier character list identifier character alphanumeric AMOS II keywords are case sensitive they are always written with lower case letters AMOS II identifiers are NOT case sensitive i e they are always internally capitalized Variables Variables are of two kinds e Local variables are identifiers for data values in AMOSQL queries and functions Local variables must be declared in function signatures by from clauses Query Statements or by the declare construct Database procedures http user it uu se udbl amos doc amos5 16 users guide html 11 4 2004 Page 4 of 30 Syntax variable name ident
38. uality between a bag and another object denotes set membership of that object The comparison functions can of course be overloaded for user defined types 7 2 Arithmetic functions abs number x gt number y div number x number y gt number z infix opera max object x object y gt object z min object x object y gt object z minus number x number y gt number z infix opera mod integer x integer y gt integer z plus number x number y gt number z infix opera times number x number y gt number z infix opera iota integer 1 integer u gt bag of integer z sqrt number x gt number z iota constructs a bag of integers between and u For example to execute n times AMOSQL statement stmt do for each integer i where i iota 1 n stmt 7 3 String functions http user it uu se udbl amos doc amos5 16 users guide html Cor COL Cor Cor 11 4 2004 Page 24 of 30 String concatenation is made using the operator e g ab cd ef returns abcdef char length Charstring gt Integer Count number of characters in string itoa Integer gt Charstring Convert integer to string lower Charstring gt Charstring Lowercase string like Charstring string Charstring pattern gt Boolean Test if string matches regular expression pattern where matches sequence of characters and matches single character For example like abc c
39. upted with CTRL C and resumed again by calling listen nameserver charstring name gt charstring Makes the current stand alone database into a nameserver and registers there itself as a mediator server with the given name register charstring name gt charstring Registers the current stand alone database as a client mediator with the given name in the nameserver running on the local host The system will complain if the name is already registered in the nameserver register charstring name charstring host gt charstring Registers the current database as a client mediator in the nameserver running on the given host ship charstring name charstring cmd gt object Ships the AMOSQL command cmd for execution in the mediator server name The result is shipped back to the caller this name gt charstring name Returns the name of the mediator where the call is issued Returns NIL if issued in a stand alone database 3 1 Data integration primitives More to come here about derived and IUTs 3 2 ODBC Wrapper The basic AMOS II system contains a wrapper for ODBC relational data sources It allows transparent access to data in relational databases through OO views ODBC data sources in AMOS II are represented by the type odbc_ds subtype of relational ODBC data access functions The following functions are used to connect to and access data in an ODBC data source Constructor for odbc_ds objects odbc _ds charstring logname
Download Pdf Manuals
Related Search