/*-*-sql-*-*******************************************************************
 * AMOS II
 *
 * Description: Example of definition of Amos II schema and database
 *
 ****************************************************************************

/* Create user defined type Person: */
create type Person;

/* The type Person has stored properties name and (final) age: */
create function name(Person p) -> Charstring nm as stored;
create function age(Person p) -> Integer as stored;

/* Create two first persons: */
create Person (name, age)
  instances :Adam ('Adam', 930);

create Person (name, age)
  instances     :Eve ('Eve', 900);

/* Make derived function to get object p representing a named person n: */
create function personNamed (Charstring n) -> Person p
  as select p 
     from Person p 
     where Name(p)=n;

/* Try out personNamed: */
personNamed('Adam');
personNamed('Eve');

/* Store binary one-to-one relationship between persons: */
create function isMarried (Person husband, Person wife) -> Boolean as stored;

/* Define separate names of directions of relationship: */
create function husband (Person w) -> Person h 
  as select h 
     from Person h 
     where isMarried (h, w);

create function wife (Person h) -> Person w
  as select w 
     from Person w 
     where isMarried(h, w);

/* Marry two first persons: */
set isMarried(:Adam, :Eve) = true;

/* Check if it worked: */
name(wife(personNamed("Adam")));
name(husband(personNamed("Eve")));
age(husband(personNamed("Eve")));

/* Store relationship from children to their parents: */
create function parents(Person c) -> Bag of Person p as stored;

/* Define function children as inverse of parents: */
create function children(Person p) -> Bag of Person c
  as select c 
     from Person c 
     where p in parents(c);

/* Create first children: */
create Person (name, age, parents)
  instances :Cain ('Cain', 913, bag(:Adam,:Eve));

create Person (name, age, parents)
  instances :Abel ('Abel', 914, bag(:Adam,:Eve));

/* Try out relationship: */
name(parents(personNamed('Abel')));

/* Make new subtype Seaman under type Person: */
create type Seaman under Person;

/* Type Seaman has extra property rank: */
create function rank(Seaman s) -> Charstring as stored;

/* Create another subtype Criminal under Person: */
create type Criminal under Person;

/* Criminals have additional properties crimes and victims: */
create function crimes (Criminal) -> Bag of Charstring as stored;
create function victims (Criminal) -> Bag of Person as stored;

/* Make first criminal and his victim: */
add type Criminal to :Cain;

set crimes(:Cain) = 'Murder';
set victims(:Cain) = :Abel;

/* Check out the criminal: */
select name(c), crimes(c), name(victims(c))
from Criminal c;
 
/*** Populate the rest of the database ***/

create Person (name, age, parents)
  instances :Seth ('Seth', 912, bag(:Adam,:Eve));

create Person (name, age, parents)
     instances  :Lilith ('Lilith', 840, nil);

set isMarried (:Seth, :Lilith) = True;

create Person (name, age, parents)
  instances :Noah ('Noah', 950, bag(:Seth, :Lilith));

add type Seaman (rank)
  to :Noah ('Captain');

create Person (name, age, parents)
  instances :Ruth ('Ruth', 869, :Cain);

set isMarried(:Noah, :Ruth) = True;

create Person (name, age, parents)
  instances :Shem ('Shem', 600, bag(:Noah, :Ruth));

create Person (name, age, parents)
  instances :Ham ('Ham', 590, bag(:Noah, :Ruth));

create Person (name, age, parents)
  instances :Japheth ('Japheth', 580, bag(:Noah, :Ruth));

create Person (name, age, parents)
  instances :Cush ('Cush', nil,:Ham);

create Person (name, age, parents)
  instances :Mizraim ('Mizraim', nil, :Ham);

create Person (name, age, parents)
  instances :Canaan ('Canaan', nil, :Ham);

create Person (name, age, parents)
  instances :Gomer ('Gomer', nil, :Japheth);

create Person (name, age, parents)
  instances :Ashkenaz ('Ashkenaz', nil, :Gomer);

create Person (name, age, parents)
  instances :Riphath ('Riphath', nil, :Gomer);

/* Define derived function finding the siblings of a person p: */
create function siblings(Person p) -> Bag of Person gc
  as select gc 
     from Person gc 
     where gc in children(parents(p)) and 
           gc != p;

/* Define derived function finding the grandparents of a person p: */
create function grandparents (Person p) -> Bag of Person gc
  as select gc 
     from Person gc 
     where gc in parents(parents(p));

/* Define inverse of grandparents: */
create function grandchildren(Person p) -> Bag of Person gc
  as select gc 
     from Person gc 
     where p in parents(parents(gc));

/* Define the cousins c of a person p: */
create function cousins (Person p) -> Bag of Person c nonkey
  as select c 
     from Person c 
     where c in children(siblings(parents(p)));

		/*  Queries  */

/*** Make some queries ***/

/* Find names and ages of all persons: */
select name(p), age(p) from Person p;

/* Find names of Shem's all grandparents: */
select c from Charstring c
         where c in name(grandparents(personNamed('Shem')));

/* Find names of Adam's all grandchildren: */
select c from Charstring c
         where c in name(grandchildren(personNamed('Adam')));

/* Find names and ranks of all Eve's grandchildren being seamen: */
select name(s), rank(s) from  Seaman s, Person p
  	 where p in grandchildren(personNamed('Eve')) and
               s = p;

/* Find the names of Noah's cousins: */
select c from Charstring c
         where c in name(cousins(personnamed('Noah')));

/* Find names of Noah's cousins with duplicates removed: */
select distinct c
       from Charstring c
       where c in name(cousins(personnamed('Noah')));

save "family.dmp"; /* Save the database on disk */

/*** Query on-line documentation ***/

/* What function names contain substring 'times'? */
apropos("times");

/* What are the signatures of the functions whose names contain 
   substring 'times'? */
signature(apropos("times"));

/* Get source code of function 'elemtimes' */
sourcecode("elemtimes");

/* Get the documentation of all functions whose names contain 'times' */
doc(apropos("times")); 

/* Get the function documentations containing the string 'multiply' */
doc("multiply");

/* Get the function documentations containing the string 'plus' followed 
   by 'element' */
doc("plus*element");