SQLite with MATLAB

SQLite-with-MATLAB.rst

I found that that MATLAB has JDBC drivers just for SQLite. These drivers do not expect a client/server database. They work fine with the flat database file SQLite produces.

There are also MEX clients for SQLite. These client would be more difficult to include in a standalone compiled MATLAB program than a JAR file. So I will concentrate on how to use the JDBC drivers.

First things to do is to get that special JDBC driver for SQLite for your platform. There are many different drivers for this purpose. All the drivers are sourced from third parties. This is not ideal from a trust standpoint as JAR files are executable. But Mathworks recommends the driver from the Xerial project which is well maintained by the University of Tokyo. When I examined the Xerial project, I found several good reasons to use it over alternatives. Get the latest version here: https://bitbucket.org/xerial/sqlite-jdbc/downloads

In my case I downloaded sqlite-jdbc-3.8.11.2.jar. You need a good place to put it. In compiled MATLAB programs, that place will be part of the project. But for your MATLAB command line in general, a good place to put it would be in your MATLAB prefdir. So once downloaded, move the JAR file to a place like ~/.matlab. I made a ./matlab/java directory to be shared among all the versions of MATLAB that might get installed.

The driver can be loaded into either the static ofr dynamic Java classpath. Becuase our eventual target may be a standalone compiled MATLAB program, the dynamic Java classpath makes more sense. So at the MATLAB command prompt, tell MATLAB where you put the JAR file:

>> javaaddpath('/Users/cbc/.matlab/java/sqlite-jdbc-3.8.11.2.jar');

Now you are ready to make a database connection object. This requires several piece of information which we will store first in variables to reuse. We need a path to our database file, a user, a password, the driver name, and the database URL. The database URL is made up of a protocol, a subprotocol, and a resouce. SQLite databases are flat files without security, so there will be no user or password:

>> dbpath = '/Users/cbc/Notebooks/tdsharvest/SSV-Ncml.db';
>> user = '';
>> password = '';
>> driver = 'org.sqlite.JDBC';
>> protocol = 'jdbc';
>> subprotocol = 'sqlite';
>> resource = dbpath;
>> url = strjoin({protocol, subprotocol, resource}, ':');
>> conn = database(dbpath, user, password, driver, url);

The database is now open for queries using normal SQLite semantics. MATLAB database cursors are lazy and must br prompted to fetch data:

>> query = 'SELECT * FROM global';
>> cursor = exec(conn, query);
>> cursor = fetch(cursor);
>> result = cursor.Data

Result now contains a two dimentional array of the requested table contents. Restrict the query with WHERE clauses to perfom searches:

>> query = 'SELECT * FROM global WHERE cdm_data_type=''cgrid''';
>> cursor = exec(conn, query);
>> cursor = fetch(cursor);
>> result = cursor.Data

Limit the returned result using column names:

>> query = 'SELECT location,model FROM global WHERE cdm_data_type=''cgrid''';
>> cursor = exec(conn, query);
>> cursor = fetch(cursor);
>> result = cursor.Data

Column names can be found using the MATLAB database explorer or the slqite3 command shell. However, here is the schema as it was created for SSV-Ncmdl.db:

CREATE TABLE global
         (location TEXT PRIMARY KEY UNIQUE NOT NULL ON CONFLICT FAIL,
          cdm_data_type TEXT,
          conventions TEXT,
          forecaststarttime TEXT,
          forecastendtime TEXT,
          institution TEXT,
          model TEXT,
          wind_source TEXT,
          advisory_or_cycle TEXT,
          grid TEXT,
          stormname TEXT,
          stormtype TEXT,
          stormyear INT,
          id TEXT,
          title TEXT);