| Reference Manual |  |
Copyright © 2009 Corrado Santoro, Vincenzo Nicosia
Version: 1.4.0
Authors: Corrado Santoro (santoro [at] dmi [dot] unict [dot] it), Vincenzo Nicosia (vnicosia [at] diit [dot] unict [dot] it).
AMNESIA is structured by means of a layered architecture.
The amnesia module is at the topmost layer and provides the main high-level functions for database access. Then, on the basis of the DBMS chosen for the specific database implemented, the amnesia module contacts the proper amnesia_driver; the latter acts as a bridge between the amnesia layer and the bottom layer (dbms_layer), which provides the real interface to the specific DBMS selected.
The following document describes how to install and use AMNESIA.
Installing AMNESIA is quite simple: unpack the tarball, reach the amnesia-X.Y.Z directory and type:
$ ./configure $ make $ sudo make install
Let us describe how to use AMNESIA by means of an example and let us suppose that we want to implement a database for our sales office; this database has to manage customers, products and orders.
On this basis, we will include in our database the tables customer, product, orders and order_line structured as follows:
|
|
|
|
||||||||||||||||||||||||||||||||||
To this aim, let us write a module sales.erl where we will define everything we need, that is the DMBS type, the connection information, the tables with their fields, field types and references. This module will contain the following preamble:
-module (sales).
-include_lib ("amnesia/include/amnesia_db_def.hrl").
Then the following functions must be present:
These functions are exported in the amnesia.hrl include file, so the presence of the proper -export(...) compiler directive is not necessary.
Since we are using MySQL, the driver_info/0 function will be like the following:
driver_info () ->
[{driver, mysql_drv},
{host, "localhost"},
{user, "sales_office"},
{password, "sales"}].
Here we have specified that we are using the mysql driver to connect to the MySQL DBMS present in our "localhost", using the user name "sales_office", with password "sales". Note the user has not to be present in the DBMS: it's up to AMNESIA to perform all operations to create the user name, assign the password and grant access to DB tables.
The next function to be defined, tables/0, has to return a list of atoms, each one expressing the name of one of the tables of our database:
tables () -> [customer, product, orders, order_line].
Then we are ready to define the structure of each table; this can be performed by means of function table/1, which takes as argument an atom specifying the table name and returns the table structure, as the listing below shows:
table (customer) ->
[ {customer_code, integer, [unique, not_null]},
{name, varchar, not_null},
{address, varchar, not_null},
{email, varchar, [not_null, {default, ""}]} ];
table (product) ->
[ {product_code, varchar, [unique, not_null]},
{description, varchar, not_null},
{price, {decimal, 10, 2}, not_null} ];
table (orders) ->
[ {order_number, integer, [unique, not_null]},
{order_date, date, not_null},
refers_to (customer) ];
table (order_line) ->
[ refers_to (orders),
refers_to (product),
{quantity, integer, not_null} ].
As the reader can note, a different function clause is used for each table of our database; the table structure is expressed by means of a list of 3-elements tuples, which represent the fields; each element of the tuple has the following meaning:
In the special case in which a table has a reference (a foreign key) to another table, the expression refers_to(TableName) has to be placed in the field list. This instructs AMNESIA to automatically create a foreign key and thus properly link the tables.
After having written the Erlang module specifying the database as the previous Section reports, the next step is to physically create the database in the DBMS and generate a .hrl file which will contain the proper Erlang record definitions for the data to be handled.
To this aim, after compiling the Erlang module (sales.erl in our case), we perform database and include-file generation as follows:
Erlang (BEAM) emulator version 5.5.5 [source] [async-threads:0] [hipe] [kernel-poll:false]
Eshell V5.5.5 (abort with ^G)
1> amnesia:db_tool(sales, [{make_hdr, "."}, make_db, {dba_user, "root"}, {dba_password, "yourpassword"}]).
The db_tool/2 function of the amnesia module takes two arguments: an atom specifying the module defining the database (which will also become the name of the database), and a list of options each one having the following meaning:
If nothing went wrong, AMNESIA should have created the sales database in your MySQL with tables customer, product, orders and order_line, as well as the user sales_office, with password sales. This can be verified by means of the MySQL command line client, as it is shown below:
corrado@csanto:~$ mysql -u sales_office -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.0.38-Ubuntu_0ubuntu1.4-log Ubuntu 7.04 distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use sales; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> describe customer; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | customer_code | int(11) | NO | UNI | | | | name | varchar(255) | NO | | | | | address | varchar(255) | NO | | | | | email | varchar(255) | NO | | | | +---------------+--------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> describe order_line; +------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | orders_id | int(11) | YES | | NULL | | | product_id | int(11) | YES | | NULL | | | quantity | int(11) | NO | | | | +------------+---------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
The work performed by AMNESIA can be derived by comparing the structure of the tables created with the definition provided in the sales.erl file. For each table, AMNESIA automatically adds an id field which acts as primary key and is also used to make connections between tables (for example, the orders_id field of table order_line is a foreign key and is linked to the id field of the orders table). This id field is a treated as a "sequence", with respect to the DBMS, or "auto_increment" in the specific case of MySQL.
Together with the creation of the tables, AMNESIA generated the sales.hrl file, containing an Erlang record definition for each table; the fields of each record are exactly the same as the ones of the database tables, as the listing below reports:
-record (customer, {
id = null,
customer_code = null,
name = null,
address = null,
email = []}).
-record (product, {
id = null,
product_code = null,
description = null,
price = null}).
-record (orders, {
id = null,
order_number = null,
order_date = null,
customer = null}).
-record (order_line, {
id = null,
orders = null,
product = null,
quantity = null}).
Once the database and the include files are generated we are ready to perform data management. To work with the created database, the first step is to open it through the amnesia:open/1 function; then we can use functions add_new, update, delete and fetch to respectively perform data adding, updating, deleting and querying.
To test these functionalities, let's write a simple Erlang module like the following:
-module (sales_test).
-include ("sales.hrl").
-export ([populate/0]).
populate() ->
{ok, Pid} = amnesia:open(sales),
%% adding 3 customers
{ok, Cust1} = amnesia:add_new (Pid, #customer {customer_code = 102341, name = "John", address = "XXXXX", email = "john@xxx"}),
{ok, Cust2} = amnesia:add_new (Pid, #customer {customer_code = 394021, name = "Corrado", address = "YYYYYY", email = "corrado@yyy"}),
{ok, Cust3} = amnesia:add_new (Pid, #customer {customer_code = 102391, name = "Dave", address = "Dave's home", email = "dave@zzz"}),
%% adding 3 products
{ok, P1} = amnesia:add_new (Pid, #product { product_code = "001", description = "CPU Intel", price = 231.10 }),
{ok, P2} = amnesia:add_new (Pid, #product { product_code = "002", description = "Compact Flash 4G", price = 57.90 }),
{ok, P3} = amnesia:add_new (Pid, #product { product_code = "003", description = "Hard Disk 500G", price = 190.77 }),
%% now let's add an order for customer "Cust2"
{ok, Order} = amnesia:add_new (Pid, #orders { order_number = 30, order_date = {2008, 7, 17}, customer = Cust2 }),
%% ... and these are the order lines
amnesia:add_new (Pid, #order_line { orders = Order, product = P2, quantity = 3 }),
amnesia:add_new (Pid, #order_line { orders = Order, product = P1, quantity = 10 }),
%% finally, let's add a new product and another order using a single function call
amnesia:add_new (Pid, [ #product { product_code = "004", description = "Data Server", price = 5200.00 },
#orders { order_number = 31, order_date = {2008, 7, 20}, customer = Cust1},
#order_line { orders = '$2', product = P3, quantity = 2},
#order_line { orders = '$2', product = '$1', quantity = 11 } ]),
ok.
Function populate of the listing below shows the various way in which we can perform data adding by means of function amnesia:add_new/2.
First of all, we open the sales database by using the function call amnesia:open(sales). Following this call, AMNESIA contacts the sales.erl module to know the structure of the database and opens the connection with the DBMS.
Then, to perform data adding, we have to simply invoke the function amnesia:add_new/2 providing two parameters: the pid returned by amnesia:open and representing the process handling the database, and the record defining our data, with its field properly assigned. If the insert operation succeeds, the function returns the tuple {ok, Data}; here Data is the same record provided as second parameter, but with the id field properly set. On the other hand, if the insert operation fails, the tuple {error, Reason} will be returned.
The data returned can be directly used when adding records to other tables which have fields referring to it. In our sales database, for example, the "orders" table has a reference to the "customer" table; as a result, the record #orders has the field customer which should be set equal to the id field (primary key) of the referred #customer record. However, instead of manually assigning the id field (and thus having a specific knowledge of "ids"), the programmer can assign the foreign key directly to the complete record: it's up to AMNESIA to extract the "id" and perform data adding correctly. Such a feature is provided to hide the details of "record-linking-by-id" and let the programmer to handle only high-level data.
This is shown in the example when adding an order to the customer with code "394021" (assigned to variable Cust2); the order will contain two lines, the first specifying 3 items of product with code "002" and 10 items of product with code "001". When we pass the #orders record, we set the customer field directly to variable Cust2, and a similar thing is done in adding order lines as for the belonging order and the product(s) specified.
The last add_new call of the example above shows another feature provided by AMNESIA to add information to the database, that is, passing by means of a list of records, the bunch of data that has to be inserted. This feature can be used, for example, to add an order with its lines using a single function call. In this case, to refer to a record of the list, the programmer can use the special atoms '$1', '$2', '$3', ..., to respectively refer to the first, the second, the third, ..., record of the provided list. In the example, we are adding a new product and a new order which contains two products: order lines (third and fourth elements of the list) are linked to the order (second element) by using the '$2' atom, while the second order line refers to the product (first element of the list) by means of the '$1' atom. It is the task of AMNESIA to resolve such internal references and replace '$n' markers with proper data.
Querying table's data can be easily performed by means of function amnesia:fetch, which, in its basic form, requires two parameters: the pid of the AMNESIA process handling the database and the name of the table to be queried. For example, executing the code:
amnesia:fetch(Pid, customer)
the following data will be returned:
{ok,[{customer,1,102341,"John","XXXXX","john@xxx"},
{customer,2,394021,"Corrado","YYYYYY","corrado@yyy"},
{customer,3,102391,"Dave","Dave's home","dave@zzz"}]}
The reply of the fetch function (when this succeeds) is therefore {ok, Data}, where Data is a list of records of the same type of the table specified.
The second form of the fetch function gets an additional parameter specifying a filtering predicate, as in the "WHERE" clause of an SQL "SELECT" statement. This is provided in a tuple {PredicateSpec,ValueList} where PredicateSpec is a string which expresses the predicate in SQL syntax but containing the placemarks $1, $2, etc., for actual data which are passed in the list ValueList. For example, to get the orders of customer with "id = 2", we can use the following example:
8> {ok, [_, Cust2 | _]} = amnesia:fetch (Pid, customer).
{ok, ...,
#customer{id = 2,
customer_code = 394021,
name = "Corrado",
address = "YYYYYY",
email = "corrado@yyy"},
...}
9> amnesia:fetch(Pid, orders, {"customer_id = $1", [Cust2#customer.id]}).
{ok,[#orders{id = 1,order_number = 30,order_date = {2008,7,17},customer = 2}]}
The PredicateSpec can contain any valid SQL predicate, which can include any "and", "or", "not", "like" connective. Surely the presence of a "piece of SQL code" is, at first sight, a violation of the basic AMNESIA design principle, which aims at hiding any SQL detail to the programmer; however this violation is a price we have to pay to let the DBMS engine to perform the query: allowing a programmer to specify a predicate in the Erlang form (e.g. a "fun") would have implied to transfer the filtering (and query processing) complexity from the SQL/DBMS level to the Erlang/AMNESIA one; this is not the aim of the authors, since they want only to provide a interface to a DBMS, letting the latter to make the proper work of data filtering, query optimization and so on.
The third form of the fetch function takes four parameters: in addition to the filtering predicate, a fourth parameter is a list of options which can be used to specify the ordering criteria (corresponding to the "ORDER BY" SQL clause) and the maximum number of data records to be returned (corresponding to the "LIMIT" SQL clause). The option for the ordering criteria can be specified using one of the following expressions:
In the following two examples, we fetch all customers' data ordering by customer_code in the first example, and by name (descending) and customer_code in the second example. Please note the use of the empty tuple "{}" in the third parameter of fetch to indicate that, in our example, we don't want any filtering.
10> amnesia:fetch(Pid, customer, {}, [{order_by, customer_code}]).
{ok,[#customer{id = 1,
customer_code = 102341,
name = "John",
address = "XXXXX",
email = "john@xxx"},
#customer{id = 3,
customer_code = 102391,
name = "Dave",
address = "Dave's home",
email = "dave@zzz"},
#customer{id = 2,
customer_code = 394021,
name = "Corrado",
address = "YYYYYY",
email = "corrado@yyy"}]}
11> amnesia:fetch(Pid, customer, {}, [{order_by, [{name, desc}, customer_code]}]).
{ok,[#customer{id = 1,
customer_code = 102341,
name = "John",
address = "XXXXX",
email = "john@xxx"},
#customer{id = 3,
customer_code = 102391,
name = "Dave",
address = "Dave's home",
email = "dave@zzz"},
#customer{id = 2,
customer_code = 394021,
name = "Corrado",
address = "YYYYYY",
email = "corrado@yyy"}]}
Instead, the option {limit, N} or {limit, Start, N} can be used to limit the number of records returned. For example, to get the first two (with respect to the customer_code) customers, we can use the following statement:
12> amnesia:fetch(Pid, customer, {}, [{order_by, customer_code}, {limit, 2}]).
{ok,[#customer{id = 1,
customer_code = 102341,
name = "John",
address = "XXXXX",
email = "john@xxx"},
#customer{id = 3,
customer_code = 102391,
name = "Dave",
address = "Dave's home",
email = "dave@zzz"}]}
It's well known that connections between tables are made through primary keys, which, in the case of AMNESIA, are the "ids" of the records. As a result, for example, the #orders record has a customer field which contains the "id" of the referred record of the customer table. This implies that, when retrieving an order, we don't know the complete informations about the customer, unless we perform an "inner join" or query the customer table.
Even if AMNESIA supports joins (inner and outer), an additional way to retrieve connected records and resolve external references is provided through the load_referenced function: by giving a record containing a foreign key, this function is able to resolve the reference and replace the numeric "id", in the field's record, with the complete information related to the connected record. The following example clarifies the working scheme of this functionality: in line 13, we fetch the order with number = 30, storing it into the variable Order, its customer field contains the number 2; then we pass this variable to function load_referenced/2 (line 14) and we obtain the same #orders record but with the customer field properly filled with customer's information.
13> {ok, [Order]} = amnesia:fetch(Pid, orders, {"order_number = 30", []}).
{ok,[#orders{id = 1,order_number = 30,order_date = {2008,7,17},customer = 2}]}
14> amnesia:load_referenced(Pid, Order).
{ok,#orders{id = 1,
order_number = 30,
order_date = {2008,7,17},
customer = #customer{id = 2,
customer_code = 394021,
name = "Corrado",
address = "YYYYYY",
email = "corrado@yyy"}}}
The example has shown how to load data from referring table to referred table, but also navigation in the opposite way is possible through function load_referenced, that is, from a referred record to all records of a given table that refer to the first.
As an example, if we want to retrieve all the lines of the order above, we can use the following statement:
15> {ok, OrderLines} = amnesia:load_referenced(Pid, Order, order_line).
{ok,[#order_line{id = 1,orders = 1,product = 2,quantity = 3},
#order_line{id = 2,orders = 1,product = 1,quantity = 10}]}
And finally, we can retrieve all the details about the products of the order by iterating over OrderLines and call once again load_referenced/2:
16> lists:map(fun (X) -> {ok, Data} = amnesia:load_referenced(Pid, X), Data end, OrderLines).
[#order_line{id = 1,
orders = #orders{id = 1,
order_number = 30,
order_date = {2008,7,17},
customer = 2},
product = #product{id = 2,
product_code = "002",
description = "Compact Flash 4G",
price = 57.9000},
quantity = 3},
#order_line{id = 2,
orders = #orders{id = 1,
order_number = 30,
order_date = {2008,7,17},
customer = 2},
product = #product{id = 1,
product_code = "001",
description = "CPU Intel",
price = 231.100},
quantity = 10}]
To retrieve connected records, together with the mechanism illustrated above, AMNESIA allows programmers to use the classical join operator of the relational algebra. Theta-, equi- and all outer-joins are fully supported, provided that they are also supported by the interfaced DBMS (by means of the proper SQL operators). The following macros, defined in amnesia.hrl, can be used to include joins in a fetch function call:
A join is executed by calling the fetch function and specifying, as the second parameter, a list such as [table_1, join_operator, table_2]. The result won't be a list of records, as in 4.2, but a list in which each element is, in turn, another list of records such as [#record_of_table_1,#record_of_table_2].
As an example, to perform a join between order_line and product tables, the following expression can be used:
-include("amnesia.hrl").
...
{ok, Data} = amnesia:fetch(Pid, [order_line, ?JOIN, product]),
...
As a result, the Data variable will contain the following value(s):
[[#order_line{id = 2,orders = 1,product = 1,quantity = 10},
#product{id = 1,
product_code = "001",
description = "CPU Intel",
price = 231.100}],
[#order_line{id = 1,orders = 1,product = 2,quantity = 3},
#product{id = 2,
product_code = "002",
description = "Compact Flash 4G",
price = 57.9000}],
[#order_line{id = 3,orders = 2,product = 3,quantity = 2},
#product{id = 3,
product_code = "003",
description = "Hard Disk 500G",
price = 190.770}],
[#order_line{id = 4,orders = 2,product = 4,quantity = 11},
#product{id = 4,
product_code = "004",
description = "Data Server",
price = 5200.00}]]
AMNESIA join operators are associative, meaning that we can write the following expression to perform (for example) a join among all tables:
-include("amnesia.hrl").
...
{ok, Data} = amnesia:fetch(Pid, [customer, ?JOIN, orders, ?JOIN, order_line, ?JOIN, product]),
...
In this case, the Data variable will contain the following value(s):
[[#customer{id = 2,
customer_code = 394021,
name = "Corrado",
address = "YYYYYY",
email = "corrado@yyy"},
#orders{id = 1,order_number = 30,order_date = {2008,7,17},customer = 2},
#order_line{id = 1,orders = 1,product = 2,quantity = 3},
#product{id = 2,
product_code = "002",
description = "Compact Flash 4G",
price = 57.9000}],
[#customer{id = 2,
customer_code = 394021,
name = "Corrado",
address = "YYYYYY",
email = "corrado@yyy"},
#orders{id = 1,order_number = 30,order_date = {2008,7,17},customer = 2},
#order_line{id = 2,orders = 1,product = 1,quantity = 10},
#product{id = 1,
product_code = "001",
description = "CPU Intel",
price = 231.100}],
[#customer{id = 1,
customer_code = 102341,
name = "John",
address = "XXXXX",
email = "john@xxx"},
#orders{id = 2,order_number = 31,order_date = {2008,7,20},customer = 1},
#order_line{id = 3,orders = 2,product = 3,quantity = 2},
#product{id = 3,
product_code = "003",
description = "Hard Disk 500G",
price = 190.770}],
[#customer{id = 1,
customer_code = 102341,
name = "John",
address = "XXXXX",
email = "john@xxx"},
#orders{id = 2,order_number = 31,order_date = {2008,7,20},customer = 1},
#order_line{id = 4,orders = 2,product = 4,quantity = 11},
#product{id = 4,
product_code = "004",
description = "Data Server",
price = 5200.00}]]
To update or delete a data item, AMNESIA offers the functions update/2 and delete/2, both receiving the name of the database and a record representing the data item to update or delete. Their usage is thus quite simple: supposing that you have to update the address of customer with code "102341" and that you want to delete the customer with code "394021", you can use the following code.
...
{ok, [C1]} = amnesia:fetch (Pid, customer, {"customer_code = $1", [102341]}),
{ok, [C2]} = amnesia:fetch (Pid, customer, {"customer_code = $1", [394021]}),
NewC1 = C1#customer { address = "new address" },
amnesia:update (Pid, NewC1), %% update customer 1
amnesia:delete (Pid, C2), %% delete customer 2
...
Selections and joins are not the sole operations usually performed while working with databases, queries that use aggregated functions, such as count(*), max(...), sum(...), avg(...), etc., are also very often present in database applications. AMNESIA also supports aggregated queries by means of special parameters given in the fourth argument (options) of the fetch function.
Basically, we can distinguish three types of aggregated queries in SQL:
To perform such queries with AMNESIA the aggregate option must be given to a fetch function call with the following meaning:
| Option | Meaning | Examples |
| {aggregate, Function, Type} | Performs a query of type 1:
|
{aggregate, "count(*)", integer} {aggregate, "sum(price)", decimal} |
| {aggregate, Function, Type, Grouping} | Performs a query of type 2:
|
{aggregate, "count(*)", integer, customer_code} {aggregate, "sum(price)", decimal, product_code} |
| {aggregate, Function, Type, Grouping, {PredicateSpec, ValueList}} | Performs a query of type 3:
|
{aggregate, "count(*)", integer, customer_code,
{"__aggregated_data__ > 2", []}} {aggregate, "sum(price)", decimal, product_code, {"__aggregated_data__ < $1", [1000.0]}} |
In the following, some examples of aggregated queries are provided, in order to let the reader to understand the correct usage of this feature.
As a first example, let us suppose we want to obtain the number of products present in the database, a query that, in SQL, we write as: "SELECT COUNT(*) FROM PRODUCT". In AMNESIA, instead, we have to call the fetch function as follows:
10> amnesia:fetch(Pid, product, {}, [{aggregate, "count(*)", integer}]).
{ok,[4]}
11>
As the example shows, since the result of such a query is a single scalar value, the return data is a list with a single element containing the data.
The second example illustrates how to execute a query of type 2; to this aim, we want to obtain the total amount of each order stored in the database; this is performed by summing the values of quantity * price present in the order_line records of the same order; obviously, we have to join tables product, orders and order_lines. The resulting query is:
{ok, Data} = amnesia:fetch (Pid, [product, ?JOIN, order_line, ?JOIN, orders], {},
[{aggregate, "sum(quantity * price)", decimal, order_number}]),
The value of Data is a list as in a classical multi-join query. Each element is a list containing, as the first element, the scalar value resulting from the aggregated function, and then the data records relevant to each table specified in the multi-join. The result of the query above is thus the following data:
[[2484.7,
#product{id = 2,product_code = "002",
description = "Compact Flash 4G",price = 57.9},
#order_line{id = 1,orders = 1,product = 2,quantity = 3},
#orders{id = 1,order_number = 30,
order_date = {2008,7,17},
customer = 2}],
[57581.54,
#product{id = 3,product_code = "003",
description = "Hard Disk 500G",price = 190.77},
#order_line{id = 3,orders = 2,product = 3,quantity = 2},
#orders{id = 2,order_number = 31,
order_date = {2007,7,12},
customer = 1}]]
Obviously, to obtain data according the desired query specification (the total amount for each order) we have to extract only values relevant to the aggregated function and the #orders record, i.e.:
[ [Total, Order] || [Total, _Product, _OrderLine, Order] <- Data]
The fetch function returns the bunch of data relevant to the specified query altogether. Cursors are instead a way to store query results and pick one record (or some records) at time; this is the case, for example, of applications which display all data not in a single page, but in several pages which can be browsed by the user. Cursors, once created from the results of a query, are stored in the AMNESIA engine and a reference ID is provided which can be then used to navigate the cursor and access the data records. Cursors are deleted explicitly by means of the delete_cursor function call, or automatically garbage collected when they are no more used for 30 seconds.
Cursors are created using the create_cursor/2 function provided by AMNESIA, which takes two parameters: the Pid of the AMNESIA process representing your database, and the result of a amnesia:fetch call; here is an example of creating a cursor for the list of ordered customers:
{ok, CursorID} = amnesia:create_cursor (Pid, amnesia:fetch(Pid, customer, {}, [{order_by, customer_code}])).
On success, the create_cursor function returns the tuple {ok, CursorID}, while, in the case of error, the value returned is {error, Reason. The CursorID must be then used in order to retrieve data from the created cursor, by using the following functions:
| Function | Meaning | Return value |
| next(Pid, CursorID) | Returns the next record in the specified cursor | {ok, Record} | {end_of_data} | {error, no_cursor} |
| prev(Pid, CursorID) | Returns the previous record in the specified cursor | {ok, Record} | {end_of_data} | {error, no_cursor} |
| nth(Pid, CursorID, RecordNumber) | Returns the specified record number in the cursor. First record is referred as "1" (not "0") | {ok, Record} | {end_of_data} | {error, no_cursor} |
| first(Pid, CursorID) | Returns the first record in the cursor. | {ok, Record} | {end_of_data} | {error, no_cursor} |
| last(Pid, CursorID) | Returns the last record in the cursor. | {ok, Record} | {end_of_data} | {error, no_cursor} |
| index(Pid, CursorID) | Returns the number of the current record in the cursor. | {ok, RecordNumber} | {end_of_data} | {error, no_cursor} |
| iterate(Pid, CursorID, Fun) | Applies the functor Fun to each record of the cursor. | ok | {error, no_cursor} |
| delete_cursor(Pid, CursorID) | Deletes the specified cursor | ok | {error, no_cursor} |
The screenshot below of the erlang shell show how to use some cursor related functions. Here we are creating a cursor with customer data; then we scan it until the end; then we seek a specific record, given its number; and then we scan the cursor backwards.
26> {ok, Cursor} = amnesia:create_cursor(Pid, amnesia:fetch(Pid, customer)).
{ok,1}
27> amnesia:next(Pid, Cursor).
{ok,#customer{id = 1,customer_code = 102341,name = "John",
address = "XXXXX",email = []}}
28> amnesia:next(Pid, Cursor).
{ok,#customer{id = 2,customer_code = 394021,
name = "Corrado",address = "YYYYYY",email = "corrado@yyy"}}
29> amnesia:next(Pid, Cursor).
{ok,#customer{id = 3,customer_code = 102391,name = "Dave",
address = "Dave's home",email = "dave@zzz"}}
30> amnesia:next(Pid, Cursor).
{end_of_data}
31> amnesia:nth(Pid, Cursor, 2).
{ok,#customer{id = 2,customer_code = 394021,
name = "Corrado",address = "YYYYYY",email = "corrado@yyy"}}
32> amnesia:prev(Pid, Cursor).
{ok,#customer{id = 1,customer_code = 102341,name = "John",
address = "XXXXX",email = []}}
33> amnesia:prev(Pid, Cursor).
{end_of_data}
As reported in the example in Sect. 3.1, in table specification field types and additional constraints (uniqueness, etc.) are expressed using special atoms that are interpreted by AMNESIA and translated into proper SQL types/constraints. Moreover, as for structured types such as SQL DATE, DATETIME or TIME, a proper Erlang representation is provided, which, in the majority of cases, conforms to the one provided by Erlang libraries handling such kind of data.
The following table summarizes the correspondence between AMNESIA types, SQL data types and their relevant Erlang representation:
| AMNESIA Type | SQL Type | Erlang Type |
| char | VARCHAR(1) | string() |
| varchar | VARCHAR(255) | string() |
| {varchar, N} | VARCHAR(N) | string() |
| text | TEXT | string() |
| int | INTEGER | integer() |
| integer | INTEGER | integer() |
| boolean bool |
BIT(1) | boolean atom, with 'true' or 'false' values |
| decimal | DECIMAL | float() |
| {decimal, N, M} | DECIMAL (N, M) | float() |
| date | DATE | {Year, Month, Day} |
| Not yet supported | TIME | |
| datetime | DATETIME | {{Year, Month, Day}, {HH, MM, SS}} |
The following table instead provides the correspondence between AMNESIA constraints and SQL ones:
| AMNESIA | SQL |
| not_null | NOT NULL |
| unique | UNIQUE |
| {default, Value} | DEFAULT Value |