Eve Database/SQL

Goals:

• Read and update tables in a database;
• Provide easy way to map data in ETL;
• Provide data reporting in file formating;
• Backup and archive data using compression;
• Transfer data & upload data in the cloud;

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 modules.

Importing

The model library must be imported using these statements:

# using Oracle library
import
   db $evelib/db/core;
   orcl: $evelib/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 or, spread data from one database and push data into multiple other databases.

pattern:

** create a database session
class OracleSession = {user,password,name: String} <: orcl.Database:
  new _credentials: String;
release
  orcl.disconnect();
return

constructor OracleSession(location,user,password:String) => (@self db.Session): 
  ** prepare credentials
  let self.location = location;
  let self.user = user;
  let self.password = password;
  ** prepare credentials
  let self.connect(self._credentials);
  orcl.connect();
return;

process:
  ** create database connection
  new demoDB =  set db := OracleSession(user:$user,password:$password,location:$location);
  ...
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 or set using configuration files that are secret, safly stored on the server. 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 an internal memory model. Then we can perform operations on this model: {search, append, update, delete}. The opperations are mirrored automaticly in the database.

• 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.

Data cashing. The memory table do not store all the database data. Instead, the memory table store a small buffer. On rare ocasions, user can store all data in memory tables for small crytical tables to improve performance when a table is used very often. Usually these tables are metadata tables.

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. Developer/user has full control over what data is stored in memory buffer, can refresh the buffer or can clear the buffer and load other data set with a different filter.

How to define a table:

A table is an smart class that knows it's bound to a database. Therefore we "inherit" a database table like it is a Eve class. EVE engine will bind the table to database. If the table in the database do not exist, EVE compiler will complain and will generate an error.

class TableName = {name: String} <: DB_Name.Table;

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. Records are "bound" to database data. A record has information about it's origin.

class RecordName = {id: Integer} <: Record;

Structure

Table structure is represented by Records structure. This can be flat or hierarchic. A Table is a collection of records. Tables can be related to each other. A record can contain a table of records. This is called nested table. Some databases have support for nested tables, some do not. In this case the nested table is just a relational table that has a foreign key to main table.

Note:

• For record names is singular starting with uppercase like: "Person"
• For table name is good practice to use plural names like: "Persons"

Sessions

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

Operations

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

• Append
• Update
• Delete

Append

Eve can add new data records into one table using append() method of a table. This create new records.

routine update_tables(value1,value2 ...):
  ** create empty records
  new record1 := table_name1.append();
  new record2 := table_name2.append();
  ** populate records with values 
  
  update record1:
    let field_name := value1;
    ...
  update record2:
    let field_name := value2;
    ... 
  commit; 
return;

Update

Eve can do single or multiple row updates in one transaction. The Update need to find the record to update before is able to modify these records.

Single record:

** 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;
return;

Note A process or routine will start a new isolated transaction. In case of error, you can issue a rollback. The issue can be in the database, if you issue a rollback the database changes are not commited but the record remain modified in memory. You must refresh the record if you want the see the old values.

Delete

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

Syntax

** 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;
finalize
  ** check status
  expect deleted1 >  0;
  expect deleted2 >= 0;
  ** print status
  print (deleded1, "records deleted")
  print (deleded2, "records deleted")
return;

Bulk Operations

For bulk operations there are 2 methods. Using Eve cycle or using direct SQL.

** using a loop to create a single session  
routine delete_records(value):
  ** delete current record using: loop
  for record in table_name loop
    record.delete() if record.field_name == value;
  repeat;  
  commit;
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)

Working with Direct SQL is not our purpose. Eve has enaugh power to enable developers handle processes. Using direct SQL is a backup, offered for special opperations Eve is not capable of. Examples will be provided for testing.

Procedures

Some databases have support for stored procedures. It will be a challenge to engage these procedures from EVE. Since not all databases have stored procedures, these are extra features that we can implement in Eve. Most of the time, Eve scripts are self contained and depend only on database row data and do not use extra layers like views, snapshots or stored procedures to read data. However just in case data is not available in any other way we can call stored procedures to retrive data.

driver main:

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

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

Introspection

For debugging the SQL, Eve will enable introspection.

• Before execution, statements are converted into SQL;
• We can visualize generated SQL by using $trace;
• We can use $query system variable to print last SQL;

Read next: Eve Tutorial