perClass Documentation
version 5.1 (31-May-2017)

Chapter 10: Interfacing databases

Table of contents

10.1. Introduction ↩

perClass 4 includes a local SQLite database engine. It can connect to a database file on a disk and perform SQL queries importing data directly into Matlab workspace. Database interface is available though "DB" licensing option. You can check if the DB option is present using sdversion.

perClass supports multiple simmultaneous database connections and multiple active queries per connection. Database connections are handled by sddb objects and SQL queries by sdsql objects.

10.2. Creating an empty database ↩

To create a new SQLite databse, we provide sddb constructor with a database filename that does not exist:

>> d=sddb('test.db')
Warning: Database file 'test.db' does not exist. Creating an empty database.
perClass DB: 1 connections

The variable d contains a reference to the created database. We will use it in each command working with this database. We will create a table called data in the database. The table contains a numerical id and a string name:

>> sdsql(d,'create table data (id integer, name text);')

We may now insert values into the table data:

>> insert(d,'data',{1,'Joe'; 5,'Bill'; 10,'John'; 12,'Betty'})

When we're done working with the database, we can manually close it using

>> close(d)

10.3. Opening an existing database from Matlab ↩

To open an existing database from Matlab, we use a ~@sddb/sddb~ constructor:

>> d=sddb('test.db')
perClass DB: 1 connections

To perform a query on a database d, we use the sdsql command:

>> s=sdsql(d,'select * from data;')
4 by 2 set in '/tmp/test.db':
SQL='select * from data;'

It returns sdsql object representing the set of records. In our example, we got 4 records, each with 2 fields.

So far, the content of the query was not loaded in Matlab. We may do so using the cell function that will create a cell array with query results:

>> C=cell(s)

C = 

[ 1]    'Joe'  
[ 5]    'Bill' 
[10]    'John' 
[12]    'Betty'

Handy shortcut for the cell conversion is the unary plus operator:

>> +s

ans = 

[ 1]    'Joe'  
[ 5]    'Bill' 
[10]    'John' 
[12]    'Betty'

10.3.1. Converting a query subset ↩

Instead of converting the entire query result, we may provide a set of record indices to be fetched. This is useful to quickly see first few records of a large set:

>> +s(1:2)

ans = 

[1]    'Joe' 
[5]    'Bill'

10.3.2. Converting a query into numerical matrix ↩

The double function convers sdsql query into a matrix.

>> s=sdsql(d,'select id from data;')
4 by 1 set in '/tmp/test.db':
SQL='select id from data;'
>> +s

ans = 

[ 1]
[ 5]
[10]
[12]

>> val=double(s)

val =

 1
 5
10
12

10.4. Closing database connections ↩

Unused database connections need to be manually closed. This can be accomplished by:

>> close(d)
>> d
perClass DB: 0 connections

Database interface is provided by the perclass_mex library. Removing this library from Matlab memory closes all open database connections.

>> d=sddb('test.db')
perClass DB: 1 connections
>> clear mex
>> d
perClass Pro DB (11-Jan-2012) build 0854, Copyright (C) 2007-2011, PR Sys Design, All rights reserved
perClass DB: 0 connections

10.5. Sorting entries ↩

Database entries may be sorted using the 'order by' SQL clause. For example, we may order the names alphabetically:

>> s=sdsql(d,'select * from data order by name;')
4 by 2 set in '/tmp/test.db':
SQL='select * from data order by name;'
>> +s

ans = 

[12]    'Betty'
[ 5]    'Bill' 
[ 1]    'Joe'  
[10]    'John' 

10.6. Inserting new records ↩

New records may be inserted from Matlab using the insert method of ~@sddb/sddb~ object.

>> s=sdsql(d,'select * from data order by name;')
4 by 2 set in '/tmp/test.db':
SQL='select * from data order by name;'
>> +s

ans = 

[12]    'Betty'
[ 5]    'Bill' 
[ 1]    'Joe'  
[10]    'John' 

Insert operates on the open database (not on a query!). In addition to the database object, it takes the table name and a cell array with the new record. The cell array should have an entry for each table column in a proper format:

>> insert(d,'data',{7 'Fredy'})

ans =

 0

Note, that the data is available imedeately in all opened queries:

>> +s

ans = 

[12]    'Betty'
[ 5]    'Bill' 
[ 7]    'Fredy'
[ 1]    'Joe'  
[10]    'John' 

Also the opened SQLite sessions on the command-line will directly show new record:

sqlite> select * from data;
1|Joe
5|Bill
10|John
12|Betty
7|Fredy

Multiple records may be inserted in one step providing 2D cell array:

>> insert(d,'data',{6 'Tom'; 20 'Ted'})

ans =

 0

>> +s

ans = 

[12]    'Betty'
[ 5]    'Bill' 
[ 7]    'Fredy'
[ 1]    'Joe'  
[10]    'John' 
[20]    'Ted'  
[ 6]    'Tom'  

10.7. Updating existing records ↩

To update existing record, we can use update SQL statement. Here, we change the name of a record, specified by the id number:

>> s=sdsql(d,'update data set name="Richard" where id=10;')

s2 =

 []

>> s=sdsql(d,'select * from data;')
7 by 2 set in 'test.db':
SQL='select * from data;'

>> +s

ans = 

[12]    'Betty'  
[ 5]    'Bill'   
[ 7]    'Fredy'  
[ 1]    'Joe'    
[10]    'Richard'
[20]    'Ted'    
[ 6]    'Tom'    

10.8. Table information ↩

Names of tables, present in a database, are returned in a cell array using:

>> tables(d)

ans = 

'data'

The names of fields in a table are accesible using:

>> fields(d,'data')

ans = 

'id'    'name'

The table schema, including the field type information, using:

>> schema(d,'data')

ans =

CREATE TABLE data (id integer, name text)