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 and Microsoft's SQLServer driver.
An instance of type Relational represents a relational database and functions of type Relational implements the interface to relational databases. The general wrapper Relational is an abstract wrapper in the sense that it does not implement an interface a a specific relational DBMS therefore has no instances. Several of the database interface functions of type Relational are defined as abstract functions. In the type hierarchy there is a specific implemented wrapper for JDBC represented by type Jdbc. The type Jdbc has one instance for each relational database JDBC connection.
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 also being subtype to Relational. The use of abstract functions type checking to find equivalent implementations for different relational database interfaces.
Connecting
The instances of relational data sources are created using a
datasource constructor function that loads necessary drivers. Later
the connect()
function associates a connection object to a wrapped
database using the driver.
Creating connections
For creating a relational database connection using JDBC use the constructor:
jdbc(Charstring dsname, Charstring driver);
where dsname is the chosen data source name and driver is the JDBC
driver to use for the access. The created connection is an instance of
the wrapper type Jdbc
. For example, to create a connection called
db1
to access a relational database using JDBC with MySQL call:
jdbc("db1"," com.mysql.jdbc.Driver");
Connecting the data source to a database
Once the connection object has been created you can open the connection to a specific relational database:
connect(Relational r, Charstring database, Charstring username, Charstring password) -> Relational
where r
is the data source object, db
is the identifier of the database to access, along with user name and password to use when accessing the database. For example, if the relational database called 'Personnel' resides on the local computer and MySQL is used for the managing it, the following opens a connection to the database for user U1
with password PW
:
connect(:db, "jdbc:mysql://localhost:3306/Personnel", "U1", "PW");
Disconnecting from the database
Once the connection is open you can use the data source object for various manipulations of the connected database. The connection is closed with:
disconnect(Relational r) -> Boolean
for example:
disconnect(:a):
Finding a named data source
To get a relational data source object given its name use:
relational_named(Charstring nm)-> Relational
for example:
relational_named("db1");
Accessing meta-data
Relational meta-data are general information about the tables stored in a relational database.
Tables in database
To find out what tables there are in a relational database, use:
tables(Relational r)
-> Bag of (Charstring table,
Charstring catalog,
Charstring schema,
Charstring owner)
for example
tables(relational_named("db1"));
The function tables()
returns a bag of tuples describing the tables stored in the relational database. To test whether a table is present in a database use:
has_table(Relational r, Charstring table_name) -> Boolean
for example
has_table(relational_named("db1"),"SALES");
Columns in table
To get a description of the columns in a table use:
columns(Relational r, Charstring table_name)
-> Bag of (Charstring column_name,
Charstring column_type)
for example:
columns(relational_named("db1"),"CUSTOMER");
Size of table
To find out how many rows there are in a table use:
cardinality(Relational r, Charstring table_name) -> Integer
for example
cardinality(relational_named("db1"),"SALES");
Primary keys of table
primary_keys(Relational r, Charstring table_name)
-> Bag of (Charstring column_name,
Charstring constraint_name)
for example:
primary_keys(relational_named("db1"),"CUSTOMER");
Foreign keys of table
To get information about the foreign keys referenced from a table use:
imported_keys(Jdbc j, Charstring fktable) -> Bag of (Charstring pktable, Charstring pkcolumn, Charstring fkcolumn)
for example
imported_keys(relational_named("db1"),"PERSON_TELEPHONES");
The elements of the result tuples denote the following:
pktable
- The table referenced by the foreign key.pkcolumn
- The column referenced by the foreign key.fkcolumn
- The foreign key column in the table.
NOTICE that composite foreign keys are not supported.
To find what keys in a table are exported as foreign keys to some other table use:
exported_keys(Jdbc j, Charstring pktable) -> Bag of (Charstring pkcolumn, Charstring fktable, Charstring fkcolumn)
for example:
exported_keys(relational_named("db1"),"PERSON");
The elements of the result tuples denote the following:
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.
Deleting tables
The function drop_table()
deletes a table from a wrapped relational database:
drop_table(Relational r, Charstring name) -> Integer
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 result
The parameters params are substituted into the corresponding occurrences in the SQL statement, for example:
sql(relational_named("db1"), "select NAME from PERSON where INCOME > ? and AGE>?", {1000,50));
Loading SQL scripts
SQL statements in a file separated with ';' can be loaded with:
read_sql(Relational r, Charstring filename) -> Bag of Vector
The result from read_sql()
is a bag containing the result tuples from executing the read SQL statements.
Hint: If something is wrong in the script you may trace the calls inside read_sql()
to sql()
by calling trace("sql");
Object-oriented views of tables
The relational wrapper allows to to define object-oriented views of data residing in a relational database. Once the view has been defined the contents of the database can be used in AmosQL queries without any explicit calls to SQL.
To regard a relational table as an sa.amos type use:
import_table(Relational r, Charstring table_name) -> Mapped_type
for example
import_table(relational_named("db1"),"SALES");
The view is represented by a mapped type which is a type whose
extent is defined by the rows of the table. Each instance of the
mapped type corresponds to a row in the table. The name of the mapped
type is constructed by concatenating the table name, the character _
and the data source name, for example Person_db1. Mapped type names
are internally capitalized, as for other sa.amos types.
For each columns in the mapped relational database 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 returning the social security number of a person from the imported relational table.
The system also allows wrapped relational tables to be transparently updated using an update statement by importing the table with:
import_table(Relational r,
Charstring table_name,
Boolean updatable)
-> Mappedtype mt
for example
import_table(relational_named("db1"),"COUNTRY",true);
If the flag updatable
is set to true the functions in the view are transparently updatable so the relational database is updated when instances of the mapped type are created or the extent of some wrapper function updated. For example:
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. The parameter typename
is the desired name of the mapped type created, as alternative to the system generated concatenation of table and data source name. The parameter updatable
gives an updatable mapped type. The parameter supertypes
is a vector of either type names or type objects, or a mixture of both. The effect is that sa.amos will perceive the mapped type as an immediate subtype of the supertypes.
There are also two other variants of import_table()
to be used for relational databases where schema and catalog names need not be specified:
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