Sage-Code Laboratory
index<--

EVE Database/SQL

EVE has basic interaction with relational databases. We design EVE to connect to different third party databases. This is the purpose of EVE to be versatile. It enables query and data transfers using embeded SQL within EVE scripts.

Goals:

Bookmarks:

definition

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:   
    String location;
    String user;
    String credentials;     
create
    self = Oracle.Database(location);
    ** prepare credentials
    credentials := user + '/' + password + '@'+ dbname;
    ** connect to database
    self.connect(credentials);
return;

** create database connection
globals
    db := Session(user:'test',password:'password',name:'demo');

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

Method 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

Targeted Databases

EVE should provide drivers for main-stream databases:

Concepts

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.

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 agregate object.

Note:

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 records
record := table_name.append();

** modify record attributes
with record apply
   .field_name := value;
   ...
end with;
   
commit;

Update

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

Syntax:

** use search fields and values
record := table_name.find(field:value, field:value ...);

** prepare record
with record apply
  table_name.field_name := value;
  ...
end with;
  
commit;

Delete

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

Syntax

** Find one single record and delete
record := table_name.find(field:value,...);
record.delete();

** check status
pass if record.status = deleted;
commit;

** Using search fields to delete multiple records
deleted := table_name.delete(field:value,...);
commit;

** Remove all records from a table in bulk
db.scrub(table:"table_name");
commit;

** delete current record using: loop
with record in table_name loop
     record.delete() if condition;
end loop;
commit;

Note:

Direct SQL

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

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

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 but not necessary for our purpose.

** prepare an object (not updatable)
type  Result_Record: (
      field_name ∈ Type,      
      ...
      ) <: Object;
      
** prepare a list of records 
     
Table(Result_Record) buffer; 

** execute stored procedure
buffer := db.procedure_name(arguments); 

Introspection

For debugging the SQL, EVE enable introspection.


Read next: Modules