Sage-Code Laboratory
index<--

EVE Database Layer

EVE has basic interaction with relational databases.

Goals:

Bookmarks:

definition

Models

A model is a complex data structure mapping EVE data types to database. EVE can read and update a database using an internal data model. This kind of applications are called data-centric. A model is connecting to one or multiple databases using API library.

loading

A model must load database library using this statement:

load $EVE_lib.db.core:(*);
load Oracle := $EVE_lib.db.oracle;

Connection

One application can connect to multiple databases simultaneously. A specific kind of application called pipeline can pull data from multiple sources to update one target database.

pattern:

** create a wrapper for database connection
rule connect(name, password, database ∈ S) => (db ∈ Oracle.Database):
    ** prepare credentials
    credential := user + '/' + password + '@'+ dbname;
    ** connect to database
    db.connect(credential);
return;
** create database connection
db := connect(user:'test',password:'password',database:'EVE_demo');

Note: Usually a database has a security protocol based on user-name and password. These credentials can not be encoded in the EVE script they need to be received as input parameters by a driver. Once a connection is established it can be used to manipulate data, using EVE statements.

Databases

Database module must provide a basic functionality:

operation Description
connect() Connect the database
disconnect() Disconnect from database
query() Direct SQL execution
execute() Execute a stored procedure
commit() Save all pending modifications
rollback() Rollback all pending modifications

target database

EVE should provide drivers for main-stream databases:

Structure One database provide a structure for tables. An application can read table structure and map it to internal memory model. Then you can perform operations on data model: {search, append, update, delete}.

Mapping A database table has records. EVE strategy is to define one memory table as a collection of objects for each database table. The memory table is mapped to database table. Mapping is one to one. Field names are case sensitive.

Tables

Internal memory tables are mixed collections of objects having same name as database tables. We can read a database table record by record on demand. A memory table can load one or multiple records in memory.

Records

Records are object instances created from table structure in memory. A record has a status property that can be used in conditionals. One record is the current record. Several records are cached in memory for a table.

**record type

** This type is defined in core database library
type Record: {rowid ∈ S(32), status ∈ Status} <: Object;

record status

** This type is defined in core database library
type Status: {.unknown:0 .verified, .updated, .deleted } <: Ordinal;

table structure For table structure we must define a record type then use generic Table to declare table structure:

Note:

type Record_Name: {field_name ∈ Type, ...}  <: Record;              ** entity record
table_name := db.open('table_name','w') ∈  Table{Record_Name}; //table mapping

table methods

** first record
bookmark ∈ Table_Record;
table_name.first;
bookmark := table_name.record;  //bookmark current record 
** fetch next record
apply table_name.fetch;
** last record
apply table_name.last;
** previous record
apply table_name.back;
** synchronize current record
apply table_name.seek(bookmark);
** find a specific record
apply table_name.find(field_name:value,...);
** get filed values
print table_name.field_name; //data from current record
pass if bookmark.field_name = table_name.field_name; //same data 

Table traversal You can read one table record by record:

pattern:

....
** table must be open to be scanned
with record ∈ table_name do
    ** use current_record fields
    print record.status; //expect: 1 = .verified
    ... 
loop;

Mutating data You can modify table data using current record. First you modify record attributes, then you call commit or rollback. EVE is cashing the updated rows and perform a bulk update using a buffer to improve performance when you commit.

with record ∈ table_name do
    ** update current record
    table_name.field_name := new_value;
    ...
loop;
apply db.commit;

Transactions

Data model can work with transactions. A transaction start automatically when you make first modification. Modifications must be consistent. When all modifications are done you can commit changes. If transaction fail all the modifications are reset. You can not commit a second time.

data manipulation

Any of the following operations will start automatically a transaction:

Append

EVE can add new data records into one table using append() rule.

** create empty record
apply table_name.append;
** modify record attributes
table_name.field_name := value;
...
apply db.commit;

Update

EVE can do single or multiple row updates in one transaction.

Syntax:
** use search fields and values
apply table_name.find(search_field:value, search_field:value ...);
** prepare record
alter table_name.field_name := value;
  ...
apply db.commit;

Delete

This statement will remove one or more records from a table.

Syntax

** Find one single record and delete
table_name.find(search_field:value,...);
apply table_name.delete;
** check status
pass if table_name.status = deleted;
apply db.commit;
** Using search fields to delete multiple records
apply table_name.delete(search_field:value,...);
apply db.commit;
** Remove all records from a table in bulk
apply table_name.scrub;
apply db.commit;
** delete current record using _for_
with record ∈ table_name do
  record.delete if condition;
return;
apply db.commit;

Note:

Direct SQL

Sometimes we need to bypass the ORM and execute native SQL:

** apply a modification query to database
apply db.query(query_template ? source)
** apply a query that return; a buffer
type  TRecord: {
      field_name ∈ Type,      
      ...
      };
** execute query string and return a list of records
buffer ∈ (TRecord); 
buffer := db.query(query_template ? source); 

Stored procedure

Some databases have support for stored procedures:

** prepare an object (not updatable)
type  Result_Record: {
      field_name ∈ Type,      
      ...
      } <: Object;
** prepare a list of records      
make  buffer ∈ (Result_Record); 
** execute stored procedure
alter buffer := db.execute procedure_name(arguments); 

Introspection

For debugging the SQL, EVE enable introspection.


Read next: Modules