File Exchange

image thumbnail

Table SQL

version 1.2.6 (3.74 KB) by Tommaso Belluzzo
A script for performing SQL CRUD operations on Matlab tables.

10 Downloads

Updated 25 Aug 2019

View License

# 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 = query_table('SELECT A.ID, A.LastName, B.Age FROM t1 A INNER JOIN t2 B ON B.ID = A.ID');
query_table('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 = query_table('DELETE FROM t1 WHERE Gender = "Male"'); t1
ar = query_table('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.

Cite As

Tommaso Belluzzo (2019). Table SQL (https://www.mathworks.com/matlabcentral/fileexchange/68771-table-sql), MATLAB Central File Exchange. Retrieved .

Comments and Ratings (0)

Updates

1.2.6

Changed image.

1.2.5

Changed image.

1.2.4

Changed image.

1.2.3

Changed image.

1.2.2

Changed image.

1.2.1

Changed image.

1.2.0

Improved description.

1.1.9

Minor fixes and improvements.

1.1.8

Minor fixes and improvements.

1.1.7

Minor fixes and improvements.

1.1.6

Minor fixes and improvements.

1.1.5

Minor fixes and improvements.

1.1.4

Updated details concerning compatibility & requirements.

1.1.3

Updated details concerning compatibility & requirements.

1.1.2

Minor fixes and improvements.

1.1.1

Project website.

1.1.0

Target release.

1.0.9

Improved tags.

1.0.8

Improved tags.

1.0.7

Improved tags.

1.0.6

Improved description.

1.0.5

Improved description.

1.0.4

Improved description.

1.0.3

Screenshot added.

1.0.2

Minor fixes and improvements.

1.0.1

Added details concerning compatibility & requirements.

MATLAB Release Compatibility
Created with R2018a
Compatible with R2015b to R2018b
Platform Compatibility
Windows macOS Linux