Controlling storage format of query results

Contents

• Create new on-disc database
• Create datasets in one transaction
• Read all records as array of structs (default)
• Read all records as struct of arrays
• Read all records as cell array/matrix
• Close database

function sqlite_test_result_types

    clear all
    close all
    clc
    dummy = mksqlite('version mex');
    fprintf( '\n\n' );

mksqlite Version 2.5 build: 133, ein MATLAB Interface zu SQLite
(c) 2008-2017 by Martin Kortmann <mail@kortmann.de>
                 Andreas Martin  <andimartin@users.sourceforge.net>
basierend auf SQLite Version 3.16.2 - http://www.sqlite.org
mksqlite verwendet:
 - DEELX perl kompatible regex engine Version 1.3 (Sswater@gmail.com)
 - BLOSC/LZ4 1.3.0-rc3.dev zur Datenkompression (Francesc Alted / Yann Collett) 
 - MD5 Message-Digest Algorithm (RFC 1321) Implementierung von Alexander Peslyak
   
Platform: PCWIN64, little endian

Create new on-disc database

    database  = 'my_testdb';    % name of the database file
    table     = 'test_table';   % name of the table

    NumOfSamples = 10;  % amount of records to create

    ruler    = [repmat( '-', 1, 60 ), '\n'];  % ruler


    % delete existing database file if any
    try
        if exist( database, 'file' )
            delete (database);
        end
    catch
        error( 'Unable to delete database' );
    end

    % Open (create) database
    mksqlite('open', database);
    mksqlite('PRAGMA synchronous = OFF');

    % result types may be:
    % 0: array of structs
    % 1: struct of arrays
    % 2: (cell) matrix
    mksqlite('result_type', 0);  % needless, since is default

    % create table
    % take a look at the boolean field (you would surely never
    % name a column like that) and see later, how mksqlite handles
    % invalid MATLAB field name characters.
    mksqlite( ['CREATE TABLE ' table        , ...
               '  ( Entry         CHAR(32), ' , ...
               '    BigFloat      DOUBLE, '   , ...
               '    SmallFloat    FLOAT, '    , ...
               '    Value         INT, '      , ...
               '    Chars         TINYINT, '  , ...
               '    "0/1-Boolean" BIT, '      , ...
               '    ManyChars     CHAR(255) ) '] );

    fprintf( ruler )
    fprintf( 'Creating %d records in one single transaction\n', NumOfSamples );

    ManyChars = repmat( '1234567890', 1, 20 );

------------------------------------------------------------
Creating 10 records in one single transaction

Create datasets in one transaction

    tic;
    mksqlite('begin');

    for idx = 1:NumOfSamples
        mksqlite( ['INSERT INTO ' table, ...
                   ' (Entry, BigFloat, ManyChars) ', ...
                   '  VALUES(?,?,?)'], ...
                   sprintf('Entry_%d', idx), idx, ManyChars );
    end

    mksqlite('commit');
    toc
    fprintf ('done.\n');

    fprintf ('Query amount of records\n')
    res = mksqlite(['select count(*) as count from ' table]);
    fprintf ('select count(*) returned %d\n', res.count);


    fprintf( ruler )

Elapsed time is 0.001637 seconds.
done.
Query amount of records
select count(*) returned 10
------------------------------------------------------------

Read all records as array of structs (default)

    tic;
    mksqlite( 'result_type', 0 );  % array of structs

    % Introducing two further return values:
    % 1.: res_count is the numer of records (rows) returned
    % 2.: col_names is a cell array containing the (original) column
    %     names. Keep in mind, that MATLAB struct fields have naming
    %     restrictions and may thus differ from original column names.
    %
    % note: each of following queries fetch the column "value" twice
    %       to show how mksqlite handles duplicate fields
    [res, res_count, col_names] = mksqlite( ['SELECT *,value FROM ' table] )
    a = toc;
    fprintf( 'ready, %f seconds = %d records per second\n\n', a, int32(NumOfSamples/a) );

res = 

10x1 struct array with fields:
    Entry
    BigFloat
    SmallFloat
    Value
    Chars
    X0_1_Boolean
    ManyChars
    Value_1


res_count =

    10


col_names = 

    'Entry'          'Entry'       
    'BigFloat'       'BigFloat'    
    'SmallFloat'     'SmallFloat'  
    'Value'          'Value'       
    'Chars'          'Chars'       
    '0/1-Boolean'    'X0_1_Boolean'
    'ManyChars'      'ManyChars'   
    'Value'          'Value_1'     

ready, 0.000382 seconds = 26209 records per second

Read all records as struct of arrays

    tic;
    mksqlite( 'result_type', 1 );  % struct of arrays
    [res, res_count, col_names] = mksqlite(['SELECT *,value FROM ' table])
    a = toc;
    fprintf ('ready, %f seconds = %d records per second\n\n', a, int32(NumOfSamples/a));

res = 

           Entry: {10x1 cell}
        BigFloat: [10x1 double]
      SmallFloat: {10x1 cell}
           Value: {10x1 cell}
           Chars: {10x1 cell}
    X0_1_Boolean: {10x1 cell}
       ManyChars: {10x1 cell}
         Value_1: {10x1 cell}


res_count =

    10


col_names = 

    'Entry'          'Entry'       
    'BigFloat'       'BigFloat'    
    'SmallFloat'     'SmallFloat'  
    'Value'          'Value'       
    'Chars'          'Chars'       
    '0/1-Boolean'    'X0_1_Boolean'
    'ManyChars'      'ManyChars'   
    'Value'          'Value_1'     

ready, 0.000368 seconds = 27191 records per second

Read all records as cell array/matrix

    tic;
    mksqlite( 'result_type', 2 );  % (cell) matrix
    [res, res_count, col_names] = mksqlite(['SELECT *,value FROM ' table])
    a = toc;
    fprintf( 'ready, %f seconds = %d records per second\n', a, int32(NumOfSamples/a) );

    fprintf('done.\n');

res = 

    'Entry_1'     [ 1]    []    []    []    []    [1x200 char]    []
    'Entry_2'     [ 2]    []    []    []    []    [1x200 char]    []
    'Entry_3'     [ 3]    []    []    []    []    [1x200 char]    []
    'Entry_4'     [ 4]    []    []    []    []    [1x200 char]    []
    'Entry_5'     [ 5]    []    []    []    []    [1x200 char]    []
    'Entry_6'     [ 6]    []    []    []    []    [1x200 char]    []
    'Entry_7'     [ 7]    []    []    []    []    [1x200 char]    []
    'Entry_8'     [ 8]    []    []    []    []    [1x200 char]    []
    'Entry_9'     [ 9]    []    []    []    []    [1x200 char]    []
    'Entry_10'    [10]    []    []    []    []    [1x200 char]    []


res_count =

    10


col_names = 

    'Entry'          'Entry'       
    'BigFloat'       'BigFloat'    
    'SmallFloat'     'SmallFloat'  
    'Value'          'Value'       
    'Chars'          'Chars'       
    '0/1-Boolean'    'X0_1_Boolean'
    'ManyChars'      'ManyChars'   
    'Value'          'Value_1'     

ready, 0.000446 seconds = 22433 records per second
done.

Close database

    mksqlite('close');

Published with MATLAB® 7.13