index<--

Eve Database/SQL

Goals:

• Resolve impedance mismatch using ORM;
• Read and update tables in a database;
• Provide easy way to map data in ETL;
• Provide data reporting in file format;
• Backup and archive data using compression;

Page bookmarks:

definition

• Models
• Databases
• Sessions
• Direct SQL
• Procedures

Models

Eve must read and update a database structure using an internal data model. A model is a map between Eve a relational data model and an object oriented model. This is called  ORM = Object Relational Model. An ORM model is connecting to one or multiple databases using vendor specific database library.

Importing

The model library must be imported using these statements:

# using Oracle library
import
   $EVE_lib.db.core:(*);
   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 database session
class Session(String user,password,name) <: Oracle.Database:
  new _credentials: String;
create
  let self = Oracle.Database(location);
  ** prepare credentials
  let self._credentials := user + '/' + password + '@'+ dbname;
  ** connect to database
  let self.connect(self._credentials);
return;

** create database connection
global
  set db := Session(user:'test',password:'password',name:'demo');
process
  ...
return;

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

Database API

For each database kind we will create a library module. These modules will provide similar functionality for different databases. This basic functionality is created to make available a "Database Connector" in Eve scripts. We need this functions:

Functionality

Targeted Databases

Eve should provide drivers for main-stream databases:

• PostgreSQL,
• Oracle,
• MySQL

Concepts

One database provide a complex framework to organize data in tables and documents. An application can read database structure and map it to internal memory model. Then we can perform operations on this model: {search, append, update, delete}.

• Mapping
• Tables
• Records
• Structure

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.

class Table(String name) <: Object;

Records

Records are object instances representing in memory table structure. 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. A record id identified by a numeric id.

class Record(Integer id) <: Object;

Structure

Table structure is represented by Records structure. This can be flat or hierarchic. A Tableis a collection of records. Tables can be related to each other. A record can contain a table of records. This is called aggregate object.

Note:

• For Record_Name is singular starting with uppercase like: "Person"
• For Table_Name is good to use plural names like: "Persons"

Sessions

Data model can work with sessions. A session can be anonymous or can have a name. A session can involve one or more databases. After a set of modifications is done you can commit changes. If transaction fail all the modifications are reset. You can customize the actions in case of error. After session you can create a report.

Operations

Any of the following operations can be included in a session:

• Append
• Update
• Delete
• Scrub

Append

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

routine update_tables():
  ** create empty records
  new record1 := table_name.append();
  new record2 := table_name.append();
  update record1:
    let field_name := value;
    ...
  update record2:
    let field_name := value;
    ... 
  commit; 
recover
  case $error.code == error1 then
    ...
  case $error.code == error2 then
    ... 
  else
    rollback;
return;

Update

** use search fields and values
process
  new record1 := table_name.find(field:value1, ...);
  new record2 := table_name.find(field:value2, ...);
  update record1:
    let field_name := value;
    ...
  update record2:
    let field_name := value;
    ...
  commit;
recover
  case error1 then
  ...
  case error2 then
  ...
  else
    rollback;
return;

Delete

** Find one or more records and delete them
process
  ** local variables
  new deleted1 := table_name1.delete(field:value,...);
  new deleted2 := table_name2.delete(field:value,...);
  commit;
recover
  case $error == code then:
    ** handle exceptions
    rollback;
    print ("failed")
  else:
    rollback;
finalize
  ** check status
  expect deleted1 >  0;
  expect deleted2 >= 0;
  ** print status
  print (deleded1, "records deleted")
  print (deleded2, "records deleted")
return;

** using a loop to create a single session  
routine name:
  ** delete current record using: loop
  cycle: for record in table_name loop
    record.delete() if field_name == value;
  repeat;  
  commit;
recover
  rollback;
return;

Scrub

Remove all records from a table in bulk is very fast but there are technical problems. Triggers are deactivated for these kind of operations.

process
  scrub db1.table1;
  scrub db2.table1;
  commit;
recover
  ** revert session
  rollback;
return;

Direct SQL

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

# execute query string and return a list of records
  new query_result := db.query(query_template ? source)

Procedures

driver main:

** prepare an object (not updatable)
class Result = {
      field_name1:Type ,
      field_name2:Type ,
      ...
      } <: Record;

global
  new buffer: ()Result; -- collection
process
  ** execute stored procedure and collect
  let buffer <+ db.procedure_name(arguments);
  ...
return;

Introspection

Read next: Eve Tutorial