Cheat-sheet- 10.1. Introduction
- 10.2. Creating an empty database
- 10.3. Opening an existing database from Matlab
- 10.3.1. Converting a query subset
- 10.3.2. Converting a query into numerical matrix
- 10.4. Closing database connections
- 10.5. Sorting entries
- 10.6. Inserting new records
- 10.7. Updating existing records
- 10.8. Table information
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)