Table SQL

A script for performing SQL CRUD operations on Matlab tables.

# INTRODUCTION #

Basically, this script is a SQL wrapper around Matlab tables. It is especially useful for those who are new to Matlab and have the habit of "thinking in SQL", or for those who need to accomplish complex data manipulation tasks and struggle to achieve them with the built-in table manipulation functions.

# METHODOLOGY & USAGE #

The "table_sql" function accepts a single input argument, which must be a valid SQL CRUD statement (DELETE, INSERT, SELECT or UPDATE) where the table names refer to ordinary Matlab tables defined within the main workspace.

Once the query is parsed, the script sets up an in-memory instance of the SQLite database, imports the tables, performs the specified SQL statement, synchronizes the workspace tables with the database and returns the result. The content of the latter depends upon the performed operation:
> for SELECT statements, an m-by-n table representing the result set returned by the database;
> for DELETE, INSERT and UPDATE statements, an integer representing the number of affected rows.

# EXAMPLES #

1) Setup:
load('patients');
ids = (1:numel(LastName)).';
t1 = table(ids,LastName,Gender,'VariableNames',{'ID' 'LastName' 'Gender'});
t2 = table(ids,Age,Height,Weight,'VariableNames',{'ID' 'Age' 'Height' 'Weight'});

2) Selections (with and without assignment):
t3 = table_sql('SELECT A.ID, A.LastName, B.Age FROM t1 A INNER JOIN t2 B ON B.ID = A.ID');
table_sql('SELECT Age, AVG(Height) FROM t1 A INNER JOIN t2 B ON B.ID = A.ID GROUP BY Age ORDER BY Age');

3) Modifications:
ar = table_sql('DELETE FROM t1 WHERE Gender = "Male"'); t1
ar = table_sql('UPDATE t2 SET Age = Age + 1 WHERE Age >= 40'); t2

# NOTES #

> The script requires a SQLite JDBC driver, which can be downloaded from the following link: https://bitbucket.org/xerial/sqlite-jdbc/downloads/. The ".jar" file must be placed in the script folder.
> The column types of the tables being queried are normalized as follows: categoricals and strings become character arrays, logicals become 8-bit integers (bytes), integers become doubles.