From Declarative to Functional Database Operations

SQL CRUD RESTful Transformations in Mathematica and OrientDB

2 minute read

Overview

This is a new release of OrientDB RESTful API in Mathematica, enhanced with new commands such as addIndex, addInstance and addEdge used especially in Graph Database modeling. Most important the functional set of API commands is built in a concise and succinct way. Database operations are now grouped in four main categories, GET, UPDATE, DELETE, and ADD, a.k.a. (GUDA), that resemble Create, Read, Update, and Delete, a.k.a. (CRUD), major functions that are implemented in relational database applications. The number of functional arguments have been also significantly revised and classified as optional and mandatory depending on the command that they are applied.

The implementation of the package is based on the HTTP RESTful SQL commands of OrientDB. The DBAPI, Mathematica application project, can be extended to cover also the programming operations of other DBMS, in an analogy of the functional patterns and operations of this package.

Examples

These are a few CRUD examples that are transformed to GUDA in order to compare the functional with the declarative form of SQL operations.

# Create bidirectional linking, i.e. association
CREATE EDGE isOwnerOf FROM 12:0 TO 16:0
ODBapi[com->"addEdge", class->"isOwnerOf", from->"12:0", to->"16:0"]

# Read records with projection and filtering
SELECT @rid, DOB FROM Person WHERE lastName="Smith"
ODBapi[com->"getRecords", prjkt->"@rid, DOB", class->"Person", propnam->"lastName", propval->"Smith"]

# Update with restrictions
UPDATE Person set age=57 WHERE age<60
ODBapi[com->"updValues", class->"Person", propnam->"age", propval->"57 where age<60"]

# Delete all the records of a class (table, vertex, document, ....)
DELETE VERTEX Person
ODBapi[com->"delRecords", class->"Person", all->"True", construct->"VERTEX"]

Installation

The DBAPI application project is open source and free. You are most welcome to contribute in the Github repository of the project that can be found here

Testing

The OrientDB API Package can be tested with Demo 1-Test API Commands and Demo 3-Schemaless vs Schemafull Operations Notebooks.

Conclusion

This functional RESTful API can be served as a prototype for a uniform, universal treatment of data language (UDL) that will be independent of the DBMS implementation. Commands and parameters can become more efficient and execution can be simplified if we take on account the hierarchical relationship of Server, Database, Class, Property and Record containers. We foresee that it will be far more intuitive, user-friendly, and easier to program with a functional style than the declarative commands of its SQL ancestor.

Documentation Pages

If you have already installed the DBAPI project, then you can search Wolfram Mathematica documentation for DBAPI. This is your starting point for browsing the documentation. For demonstration purposes, we have included here the following documentation pages in pdf format for the ODBapi

2016

From Declarative to Functional Database Operations
SQL CRUD RESTful Transformations in Mathematica and OrientDB
A new alternative, functional way to program databases

2015

OrientDB Mathematica Package
OrientDB RESTful HTTP-JSON API in Mathematica
Extends Wolfram Language (Mathematica) by porting OrientDB RESTful API

AtomicDB Mathematica AddOn (ADBM)
Demo and Professional Versions in Wolfram Programming Language
The first add-on for AtomicDB that makes it fully compatible to work with Wolfram Language: