Creating and Opening a New SQLite Database

When connecting to a database, you can specify additional parameters to control the behavior of the database object. Enabling the hot option will turn on automated database schema changes. Enabling the dbg option will print all the SQL statements that ZozaniDB executes in the background. When you are working with a new database you are building, we recommend you enable both hot and dbg options. When an SQLite database is hot, it will be created if it does not already exist. 
>> % Connect to an SQLite database, turn on automated database schema building,
>> % and turn on debugging to print all executed SQL statements.
>> d=zdb('temp.sqlite', 'hot',true, 'dbg',true);
SELECT name FROM sqlite_master WHERE type="table" AND name!="sqlite_sequence" UNION ALL SELECT name FROM sqlite_temp_master WHERE type="table" AND name!="sqlite_sequence" ORDER BY 1
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
PRAGMA temp_store = 2
PRAGMA cache_size
PRAGMA cache_size=20000
SELECT tbl_name,sql FROM sqlite_master WHERE type='table' AND sql LIKE "CREATE TABLE %" AND tbl_name='user'
PRAGMA TABLE_INFO("user")
SELECT tbl_name,sql FROM sqlite_master WHERE type='index' AND sql LIKE "CREATE %INDEX %" AND tbl_name='user'
If you already have a pre-built database and you do not want ZozaniDB to not automatically change its schema, disable the hot option (or omit it; the default is off). Similarly, the default for the dbg option is off. 








>> % Connect to an existing SQLite database,
>> % turn off automated database schema building and turn off debugging.
>> d=zdb('temp.sqlite');

Connecting to a MySQL Database

Connecting to a MySQL is just a matter of specifying the database parameters. We assume you already have a MySQL database installed and know the username and password for the connection. To connect to a MySQL database, run the following in Matlab. 








>> d=zdb('mysql:host=localhost;dbname=somedatabase;user=myusername;pass=mypassword');
ZozaniDB will automatically download the MySQL driver when it needs to. If this download fails or you want to manually load the driver, download the MySQL Java Connector. If you use the Windows installer, the Jar file will for example located at C:\Program Files (x86)\MySQL\MySQL Connector J\mysql-connector-java-5.1.34-bin.jar. If you use the platform independent file, unpack it and use its location below instead. In Matlab, run the following: 








>> % Replace the path below if you saved the jar file to a different location.
>> javaaddpath('C:\Program Files (x86)\MySQL\MySQL Connector J\mysql-connector-java-5.1.34-bin.jar');

Connecting to a Table

If you have connected to the database with hot turned on, the following will create the user table if does not already exist; the user table will be created with a single user field. 
>> % connect to the user table.
>> % if you have d.hot=true, the table will be automatically created if it does not exist.
>> t=d.table('user');

>> %You can drop previously created tables if you need to create them afresh.
>> d.droptable('user');
DROP TABLE IF EXISTS "user"
>> t=d.table('user');
CREATE TABLE IF NOT EXISTS "user"(id integer  not null, PRIMARY KEY(id))
SELECT tbl_name,sql FROM sqlite_master WHERE type='table' AND sql LIKE "CREATE TABLE %" AND tbl_name='user'
PRAGMA TABLE_INFO("user")
SELECT tbl_name,sql FROM sqlite_master WHERE type='index' AND sql LIKE "CREATE %INDEX %" AND tbl_name='user'
--- NOTICE: created table "user"

>> % Or delete them all:
>> d.dropalltables('force');
DROP TABLE IF EXISTS "user"

>> %If the user already exists, you can use the shorthand dot operator to get the table.
>> t=d.user;
CREATE TABLE IF NOT EXISTS "user"(id integer  not null, PRIMARY KEY(id))
SELECT tbl_name,sql FROM sqlite_master WHERE type='table' AND sql LIKE "CREATE TABLE %" AND tbl_name='user'
PRAGMA TABLE_INFO("user")
SELECT tbl_name,sql FROM sqlite_master WHERE type='index' AND sql LIKE "CREATE %INDEX %" AND tbl_name='user'
--- NOTICE: created table "user"

Inserting new elements into the table

Use the insert method of the table object to insert a struct array (single or multiple elements). If the table does not already contain the fields, ZozaniDB will automatically adjust the database schema to accommodate the data you are inserting. 
>> t.insert(struct('name','ahmet'));
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup142"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(5) COLLATE nocase)
DROP TABLE "user_backup142"
COMMIT
--- NOTICE: added field "user.name"
INSERT INTO "user"("name") VALUES('ahmet')
>> t.insert(struct('name',{'tom','jerry'}));
INSERT INTO  "user"("name") SELECT  'tom' AS "name" UNION ALL SELECT 'jerry'

>> %insert cell arrays. each row of cell arrays must have all fields of the
>> %table.
>> t.insert({100,'apple'});
INSERT INTO "user" VALUES(100,'apple')

InsertOrUpdate entries

ZozaniDB by default will set the "id" field as a primary key. Each row in the table would then need to have a unique value for the "id" field. If you try to insert a new entry with a duplicate id, you would normally get an integrity violation error from the database. The prudent way to avoid this error would be, for every entry you are inserting, to first check if already exists in the table, and only insert if it doesn't. ZozaniDB provides an automated way of handling insertion errors. You can control what ZozaniDB does upon an insertion error by setting the oninserterror property of the zdb object.

By default, zdb will first try an insertion and if a uniqueness constraint violation happens, it will update that entry instead.

>> t.insert(struct('id',1,'name','jerry'));
INSERT INTO "user"("id","name") VALUES(1,'jerry')
--- ERROR:SQLITE:CONSTRAINT_PRIMARYKEY UNIQUE constraint failed: user.id
--- stack: query.m:33, insert.m:84, func_runhtmlpublish.m:31
--- CAUSE: The sql statement was:
     INSERT INTO "user"("id","name") VALUES(1,'jerry')
UPDATE "user" SET "name"='jerry' WHERE id=1

>> % To ignore all uniqueness constraint violation errors but just let us know when they happen:
>> d.oninserterror='ignoreduplicates,echoduplicates';
>> t.insert({1,'orange'});
INSERT INTO "user" VALUES(1,'orange')
--- ERROR:SQLITE:CONSTRAINT_PRIMARYKEY UNIQUE constraint failed: user.id
--- stack: query.m:33, insert.m:84, func_runhtmlpublish.m:31
--- CAUSE: The sql statement was:
     INSERT INTO "user" VALUES(1,'orange')

>> % To ignore all insert errors (uniqueness constraints or any other errors), use:
>> d.oninserterror='ignore,echo';
>> t.insert({1,'orange'});
INSERT INTO "user" VALUES(1,'orange')
--- ERROR:SQLITE:CONSTRAINT_PRIMARYKEY UNIQUE constraint failed: user.id
--- stack: query.m:33, insert.m:84, func_runhtmlpublish.m:31
--- CAUSE: The sql statement was:
     INSERT INTO "user" VALUES(1,'orange')

>> % To not ignore insert errors, use:
>> d.oninserterror='';
>> t.insert({1,'orange'});
Error using sqlitemex
UNIQUE constraint failed: user.id

Error in zdb/query (line 33)
        ret{i}=sqlitemex(d.connect,sq);

Error in dbtable/insert (line 86)
	else t.db.query(sql); end

Error in func_runhtmlpublish (line 31)
					cmdout=evalc(cmd);

Caused by:
    The sql statement was:
    INSERT INTO "user" VALUES(1,'orange')
Instead of relying on the zdb.oninserterror property for all insertions, you can also call the insertorupdate() method of the dbtable object for individual insertion operations. 
>> t.insertorupdate(struct('id',1,'name','orange'));
INSERT INTO "user"("id","name") VALUES(1,'orange')
UPDATE "user" SET "name"='orange' WHERE id=1

Getting data from tables

ZozaniDB tables provide a set of get* methods to find entries that match a certain criteria and returns them as struct arrays. Each get* method also has a corresponding find* method (described below) that returns the same entries and dbentity objects. The signature of the get*by() functions follow getby(where,fields,limit) signature. The first argument is a criteria that gets converted to an SQL WHERE clause. You can provide the criteria as a struct or as an SQL where clause string. The second argument specifies the fields you would like to retrieve (as a single fieldname string or as a cell array of fieldnames). The third argument can be used to limit the number of entries returned. See the examples below. Some of the get*by() methods have a corresponding method with the same name without the "-by" suffix, e.g., getcol(). These methods work the same way as the get*by() methods, but don't enforce selection of items by any criteria. 
>> % get the entry with name='ahmet'.
>> t.getby(struct('name','ahmet'))
SELECT * FROM "user" WHERE name='ahmet' 

ans = 

  0x1 empty struct array with fields:

    id
    name

>> % get name='ahmet' OR name='tom'.
>> t.getby(struct('name',{{'ahmet','tom'}}))
SELECT * FROM "user" WHERE name IN ('ahmet','tom') 

ans = 

  struct with fields:

      id: 2
    name: 'tom'

>> % you can also provide full sql statements, but we recommend using
>> % zdb.query() for this instead.
>> t.getby('SELECT * FROM user WHERE name like "%met"')
SELECT * FROM user WHERE name like "%met"

ans = 

  0x1 empty struct array with fields:

    id
    name

>> %use getall() to fetch the entire table.
>> t.getall()
SELECT * FROM "user" WHERE 1 

ans = 

  4x1 struct array with fields:

    id
    name

>> %use dumpall() to view the entire table in a fixed-width format.
>> t.dumpall()
SELECT * FROM "user" WHERE 1 
id  name  
=== ======
1   orange
2   tom   
3   jerry 
100 apple

>> % getcolby() returns a single column. The result will be a numerical column
>> % vector if the entries are numerical; otherwise it will be a cell array.
>> % Here, we are getting the name column as a cell array.
>> t.getcolby('(id<3)','name')
SELECT "name" FROM "user" WHERE (id<3) 

ans =

  2x1 cell array

    {'orange'}
    {'tom'   }

>> % getrowby() gets a single row.
>> t.getrowby('(id=2)')
SELECT * FROM "user" WHERE (id=2) LIMIT 0,1

ans = 

  struct with fields:

      id: 2
    name: 'tom'

>> % getrowbyid(x) is a short hand for getrowby(struct('id',x))
>> t.getrowbyid(2)
SELECT * FROM "user" WHERE id=2 LIMIT 0,1

ans = 

  struct with fields:

      id: 2
    name: 'tom'

>> % getcol() gets a column from the entire table. Below, we get the names of all
>> % entries in the user table.
>> t.getcol('name')
SELECT "name" FROM "user" WHERE 1 

ans =

  4x1 cell array

    {'orange'}
    {'tom'   }
    {'jerry' }
    {'apple' }

>> % getfirstrow() gets a the first row of the table. getfirstrow() is a quick
>> % way of examining the available fields and data types of a table.
>> t.getfirstrow()
SELECT * FROM "user" WHERE 1 LIMIT 0,1

ans = 

  struct with fields:

      id: 1
    name: 'orange'

>> % getnrows() gets the first n rows.
>> t.getnrows(2)
SELECT * FROM "user" WHERE 1 LIMIT 0,2

ans = 

  2x1 struct array with fields:

    id
    name

>> % You can specify a starting position for getting n rows. The example below
>> % will get 1 row (first argument) starting at position 2 (second argument).
>> % Note that the first entry of the database is located at position 0.
>> t.getnrows(1,2)
SELECT * FROM "user" WHERE 1 LIMIT 2,1

ans = 

  struct with fields:

      id: 3
    name: 'jerry'

Using entries as dbentity objects

The find*() methods query the database similar to the get*() methods, but the find*() methods create dbentity objects from the result of the query. These objects work similar to matlab structures, but have additional functionalities such as delayed data fetching and automatically issuing updates to the database when you change the value of their fields. 
>> e=t.findrowbyid(2,'id')
SELECT "id" FROM "user" WHERE id=2 LIMIT 0,1

e = 

   dbentity: user
1 entries.
      id: 2

>> % Notice above that e.name is not in the entity, but it will be fetched when you
>> % first access it.
>> e.name
SELECT "name" FROM "user" WHERE id=2 LIMIT 0,1

ans =

    'tom'

>> % You can change the dbentity objects just like you change a struct's values.
>> e.name='adam';


>> % Note that your changes are not saved into the database right away. The changes
>> % will be saved when the object is removed from memory, allowing you to make
>> % more than one change to the object before the data is saved to the database.
>> clear e
UPDATE "user" SET "name"='adam' WHERE id=2


>> % You can also explicitly have the object update itself to the database by
>> % calling its save() method.
>> e=t.findrowbyid(2,'id');
SELECT "id" FROM "user" WHERE id=2 LIMIT 0,1
>> e.name='ahmet';
>> e.save;
UPDATE "user" SET "name"='ahmet' WHERE id=2
Below are additional examples (similar to the get*() functions above) that construct dbentity objects. 
>> t.findby(struct('name',{{'ahmet','tom'}}))
SELECT * FROM "user" WHERE name IN ('ahmet','tom') 

ans = 

   dbentity: user
1 entries.
        id: 2
      name: 'ahmet'

>> t.findby('SELECT * FROM user WHERE name like "%met"')
SELECT * FROM user WHERE name like "%met"

ans = 

   dbentity: user
1 entries.
        id: 2
      name: 'ahmet'

>> t.findall()
SELECT * FROM "user" WHERE 1 

ans = 

   dbentity: user
Fields: id,name
4 entries.

>> t.findrowby('(id=2)')
SELECT * FROM "user" WHERE (id=2) LIMIT 0,1

ans = 

   dbentity: user
1 entries.
        id: 2
      name: 'ahmet'

>> t.findfirstrow()
SELECT * FROM "user" WHERE 1 LIMIT 0,1

ans = 

   dbentity: user
1 entries.
        id: 1
      name: 'orange'

>> t.findnrows(2)
SELECT * FROM "user" WHERE 1 LIMIT 0,2

ans = 

   dbentity: user
Fields: id,name
2 entries.

>> t.findnrows(1,2)
SELECT * FROM "user" WHERE 1 LIMIT 2,1

ans = 

   dbentity: user
1 entries.
        id: 3
      name: 'jerry'

Storing complex datatypes

Available database systems support basic datatypes, so that in each field you are limited to storing numbers and strings. If you need to store more complex data (such as matlab structs or cell arrays), change the type of a field to one of yaml, json, or serialize datatypes. The choice of complex datatype will depend on whether you need to access these data from outside matlab. "yaml" is a human-readable text format and works best for individual struct values. "json" is not as human-readable but is frequently used in web applications. You can find encoders and decoders for yaml and json formats in most other programming languages. The "serialize" datatype will use an internal format that you probably won't be able to decode in other programming languages. If you plan to view and edit the database contents using a database browser If you plan to use the data outside Matlab (such as in SQLite Manager), stick with the "yaml" format. If you don't need to view/edit data manually but plan to access the database from other programming languages, use the "json" format. If you plan to use ZozaniDB exclusively for accessing the complex data, then you can use the "serialize" datatype. 
>> % change the datatype of the info field to yaml datatype.
>> t.changefieldtypeifnot('info','yaml');
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup951"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(5) COLLATE nocase, "info" text COLLATE nocase)
INSERT INTO "user" SELECT "id","name",null FROM "user_backup951"
DROP TABLE "user_backup951"
COMMIT
>> e=t.findrowbyid(2,'id')
SELECT "id" FROM "user" WHERE id=2 LIMIT 0,1

e = 

   dbentity: user
1 entries.
      id: 2

>> % Now you can set the value of the info field to complex data.
>> e.info=struct('age',34,'height',5.10,'weight',175);

>> % when the entity is saved, ZozaniDB will automatically convert the data into
>> % a yaml string before storing it into the database.
>> e.save
UPDATE "user" SET "info"='age: 34
height: 5.1
weight: 175' WHERE id=2

>> % Conversely, ZozaniDB will restore the complex data when it creates the
>> % dbentity from a query.
>> t.findrowbyid(2,'id').info
SELECT "id" FROM "user" WHERE id=2 LIMIT 0,1
SELECT "info" FROM "user" WHERE id=2 LIMIT 0,1

ans = 

  struct with fields:

       age: 34
    height: 5.1000
    weight: 175

>> % Note that the decoding is only done when you use the find*() methods to created
>> % object. The get*() methods will return the raw strings, without decoding them.
>> info=t.getonebyid(2,'info')
SELECT "info" FROM "user" WHERE id=2 LIMIT 0,1

info =

    'age: 34
     height: 5.1
     weight: 175'

>> whos('info')
  Name      Size            Bytes  Class    Attributes

  info      1x31               62  char

Changing the table structure

The automated schema building (performed when you leave the zdb.hot option on) will be sufficient for most purposes. If you need to manually modify the database schema, ZozaniDB provides several utility functions that make it easier to add/change/remove table fields and indices. 
>> % drop a field
>> t.dropfield('info');
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup755"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(5) COLLATE nocase)
INSERT INTO "user" SELECT "id","name" FROM "user_backup755"
DROP TABLE "user_backup755"
COMMIT

>> % add a new field
>> t.addfield('email','type','integer')
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup499"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(5) COLLATE nocase, "email" integer)
INSERT INTO "user" SELECT "id","name",null FROM "user_backup499"
DROP TABLE "user_backup499"
COMMIT

ans = 

   dbtable: user
  dbfields: id, name, email
>> t.addfield('age');
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup256"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(5) COLLATE nocase, "email" integer, "age" integer)
INSERT INTO "user" SELECT "id","name","email",null FROM "user_backup256"
DROP TABLE "user_backup256"
COMMIT

>> % change the type or attributes of an existing field
>> t.changefield('age','type','decimal');
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup139"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(5) COLLATE nocase, "email" integer, "age" real)
INSERT INTO "user" SELECT "id","name","email","age" FROM "user_backup139"
DROP TABLE "user_backup139"
COMMIT

>> % changefieldtype(...) is a short hand for changefield('type',...)
>> t.changefieldtype('email','string','length',32);
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup244"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(5) COLLATE nocase, "email" text(32) COLLATE nocase, "age" real)
INSERT INTO "user" SELECT "id","name","email","age" FROM "user_backup244"
DROP TABLE "user_backup244"
COMMIT

>> % you would usually need to set a uniqueness constraint, to prevent duplicate rows.
>> t.adduniqueindexifnotexists('name,email');
CREATE UNIQUE INDEX "user_name_email_idx" ON "user"("name","email")

>> % create an index on a field, to speed up queries that check values in this column.
>> t.addindex('email')
CREATE  INDEX "user_email_idx" ON "user"("email")

ans = 

   dbtable: user
  dbfields: id, name, email, age

>> % You can add/remove a field in the list of primary keys, to generate composite 
>> % primary keys.
>> % An autoincrement key implies non-composite primary key, so you need to remove
>> % autoincrement before you can generate a composite primary key.
>> t.changefield('id','autoincrement',false);
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
DROP INDEX IF EXISTS "user_name_email_idx"
DROP INDEX IF EXISTS "user_email_idx"
ALTER TABLE "user" RENAME TO "user_backup586"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(5) COLLATE nocase, "email" text(32) COLLATE nocase, "age" real)\nCREATE  INDEX IF NOT EXISTS "user_email_idx" ON "user"("email")\nCREATE UNIQUE INDEX IF NOT EXISTS "user_name_email_idx" ON "user"("name","email")
INSERT INTO "user" SELECT "id","name","email","age" FROM "user_backup586"
DROP TABLE "user_backup586"
COMMIT
>> t.addprimarykey('name'); %this will make (id,name) pair the new composite primary key.
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
DROP INDEX IF EXISTS "user_name_email_idx"
DROP INDEX IF EXISTS "user_email_idx"
ALTER TABLE "user" RENAME TO "user_backup758"
CREATE TABLE "user"( "id" integer, "name" text(5) COLLATE nocase, "email" text(32) COLLATE nocase, "age" real, PRIMARY KEY("id","name"))\nCREATE  INDEX IF NOT EXISTS "user_email_idx" ON "user"("email")\nCREATE UNIQUE INDEX IF NOT EXISTS "user_name_email_idx" ON "user"("name","email")
INSERT INTO "user" SELECT "id","name","email","age" FROM "user_backup758"
DROP TABLE "user_backup758"
COMMIT

>> % Change the primary key (existing values in this column should be unique).
>> t.setprimarykey('id');
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
DROP INDEX IF EXISTS "user_name_email_idx"
DROP INDEX IF EXISTS "user_email_idx"
ALTER TABLE "user" RENAME TO "user_backup531"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(5) COLLATE nocase, "email" text(32) COLLATE nocase, "age" real)\nCREATE  INDEX IF NOT EXISTS "user_email_idx" ON "user"("email")\nCREATE UNIQUE INDEX IF NOT EXISTS "user_name_email_idx" ON "user"("name","email")
INSERT INTO "user" SELECT "id","name","email","age" FROM "user_backup531"
DROP TABLE "user_backup531"
COMMIT

Constructing and executing your own SQL queries

Although ZozaniDB enables you to use a database without dealing with SQL queries, you can still run your own SQL queries using the zdb.query() method. ZozaniDB provides utility functions that make it easy to escape values and construct insert/update/select clauses. 
>> % Results of the query are returned as structs.
>> d.query('select * FROM user')
select * FROM user

ans = 

  4x1 struct array with fields:

    id
    name
    email
    age

>> % You can use question marks to fill values into the sql statement;
>> % ZozaniDB will escape the string and wrap them with proper quoting.
>> % You can think about this functionality similar to Matlab's fprintf()
>> % function, but instead of the % symbol, the entries are specified
>> % with one of the ?, ??, ??? markers:
>> % * ? is replaced with quoted value.
>> % * ?? is replaced with quoted field. use this as placeholder for table and field names.
>> % * ??? is replaced with the provided string, without any change.

>> d.query('select * FROM ?? WHERE name=? AND ???', 'user', 'ahmet', 'id<3')
select * FROM "user" WHERE name='ahmet' AND id<3

ans = 

  struct with fields:

       id: 2
     name: 'ahmet'
    email: []
      age: []

>> % If you want to construct queries without executing them, use zdb.fillquery().
>> d.fillquery('select * FROM ?? WHERE name=? AND ???','user','ahmet','id<3')
ans =
    'select * FROM "user" WHERE name='ahmet' AND id<3'
>> d.fillquery('select * FROM ?? WHERE name=? AND ???','user',5,'id<3')
ans =
    'select * FROM "user" WHERE name=5 AND id<3'

>> % d.whereclause() is useful for constructing the criteria for the selection 
>> % statements. The input to this method can be strings, structs, and cells.
>> % Multiple values (given as cell array) for the same field are OR'ed, whereas
>> % different fields are AND'ed.
>> d.whereclause(struct('id',3))
WHERE id=3
>> d.whereclause(struct('id',{{3,5}}))
WHERE id IN (3,5)
>> d.whereclause(struct('id',{{3,5}},'name',{{'tom','jerry'}}))
WHERE id IN (3,5) AND name IN ('tom','jerry')
>> d.whereclause(struct('id',3,'name',{{'tom','jerry'}},'lastname','sacan'))
WHERE id=3 AND name IN ('tom','jerry') AND lastname='sacan'
>> d.whereclause({'id=3','age=32',struct('name','ahmet')})
WHERE id=3 AND age=32 AND name='ahmet'
>> d.whereclause({'id=3','age=32',struct('name','ahmet','lastname','sacan')})
WHERE id=3 AND age=32 AND (name='ahmet' AND lastname='sacan')

>> % If you want to provide your own criteria in a struct, mixed with other fields
>> % and values, use 'X___1','X___2',etc. as fieldnames for your criteria.
>> d.whereclause(struct('name','ahmet','X___1','lastname LIKE "saca%"'))
WHERE name='ahmet' AND lastname LIKE "saca%"

>> % The second argument to whereclause() specifies whether we prepend the
>> % string with 'WHERE'.
>> d.whereclause(struct('id',3),false)
id=3
>> d.whereclause(struct('id',{{3,5}},'name',{{'tom','jerry'}}) ,false)
id IN (3,5) AND name IN ('tom','jerry')

>> % insert and update clauses can be constructed similarly. 
>> d.insertclause(struct('id',3,'name','ahmet','lastname','sacan'))
VALUES (3,'ahmet','sacan')
>> d.insertclause({3,'ahmet','sacan'})
VALUES (3,'ahmet','sacan')

>> d.updateclause(struct('id',3,'name','ahmet','lastname','sacan'))
SET "id"=3,"name"='ahmet',"lastname"='sacan'

Creating and using associations

Associations become useful for navigating across different tables using the dbentity object properties. If you want to use entity relationships, you need to turn on withrel property when connecting to the database. For existing databases, ZozaniDB automatically determines entity-relationships across multiple tables by analyzing the field names, datatypes, primary keys, and uniqueness constraints. withrel=1 turns on only direct relationships; if you want to also have ZozaniDB detect transitive relationships, use withrel=2. ZozaniDB uses shortest-path in the relationship graph to set transitive relationships.

You can create associations between tables using zdb.addmanytoone/addmanytomany/addonetoone methods. You need to provide the names of the tables being associated. The fieldnames will be automatically determined with an '_id' postfix, e.g., "tablex_id". Keep zdb.hot=true so that the fields and join tables can be automatically created in the database.

In the example below, we link users and cars. 

>> d=zdb('temp.sqlite','hot',true,'dbg',false, 'withrel',1);

>> % Create the tables and link them with manytomany relationship.
>> d.table('user');
>> d.table('car');
--- NOTICE: created table "car"

>> d.addmanytomany('user','car');

>> % Let's fill in some example data.
>> d.table('user').insertorupdate(struct('id',{50,60,70},'name',{'agent50','agent60','agent70'}));
>> d.table('car').insertorupdate(struct('id',{5,10,15,20},'name',{'bmw','mercedes','honda','toyota'}));
--- NOTICE: added field "car.name"
>> d.table('user_car').insertorupdate(struct('user_id',50,'car_id',{5 10}));
>> d.table('user_car').insertorupdate(struct('user_id',60,'car_id',{10 20}));
>> d.table('user_car').insertorupdate(struct('user_id',70,'car_id',{5 15}));


>> % Now, a user dbentity object will be able to get its respective car(s).
>> % Let's create a dbentity object for agent50.
>> e=d.table('user').findby(struct('name','agent50'))
e =
   dbentity: user
Associations:
  car: manyToMany:car
1 entries.
         id: 50
       name: 'agent50'
      email: []
        age: []

>> % Let's get the car(s) for this user. Notice that ZozaniDB constructs an SQL
>> % join query to link the tables. The result is again a dbentity object, now
>> % representing the cars.
>> f=e.car
f =
   dbentity: car
Associations:
  user: manyToMany:user
Fields: id,name
2 entries.

>> % Use the dump method to view the data.
>> f.dump
id name    
== ========
5  bmw     
10 mercedes

>> % or get an individual field.
>> f.name
ans =
  1x2 cell array

    {'bmw'}    {'mercedes'}


>> % We can repeat the same exercise starting from a car and getting all the users
>> % associated with that car.
>> d.table('car').findby(struct('name','bmw')).user.dump
id name    email age
== ======= ===== ===
50 agent50          
70 agent70

Dynamic Behaviors

You can "hook" onto specific table events to perform additional actions before and after these events. Behaviors are managed in each table model's "behavior" attribute. The recommended way to implement behaviors is via a behavior class. See the dbtimestampable_demo class in the ZozaniDB sources for an example.

Behaviors are setup during zdb.expandmodels(), which is executed when you connect to a database. These behaviors register themselves into onbeforeinsert, onafterinsert, e.g., table events. Note that any behavior you attache to the tables would be lost when the database model is regenerated from the database system. If you want behaviors to persist, zdb.modelpatchfile is a good place to specify them.

In the example below, we attach a timestamping behavior, such that whenever a new entry is inserted or an existing entry is updated, we automatically set the timestamps for that entry. 

>> t=d.user;

>> % Add the timestampeable_demo class. ZozaniDB will look for a class with the
>> % name 'db[behaviorname]'
>> t.addbehavior('timestampable_demo');

>> % Inserting a new table will automaticall call timestampable_demo.onbeforeinsert()
>> % method, which will add the current time to the entry being inserted.
>> t.insert(struct('id',55,'name','apple'));
--- NOTICE: added field "user.created"
--- NOTICE: added field "user.modified"

>> t.getrowby(struct('id',55))
ans =
  struct with fields:

          id: 55
        name: 'apple'
       email: []
         age: []
     created: '18-12-21 12:59:45'
    modified: '18-12-21 12:59:45'

>> t.update(struct('id',55,'name','orange'));

>> t.getrowby(struct('id',55))
ans =
  struct with fields:

          id: 55
        name: 'orange'
       email: []
         age: []
     created: '18-12-21 12:59:45'
    modified: '18-12-21 12:59:45'

Constructing and populating a large database / Bulk loading

If you need to perform many insertions when creating a new large database, you may want to disable certain ZozaniDB features.
  • If you know the database structure is finalized, you can set hot=false. This will prevent ZozaniDB from checking for database schema adjustments, saving you time. One way to finalize your database structure is to insert a smaller number of entries with hot=true, and then setting hot=false for the remaining data. For string fields, you may need to manually set a maximum length.
  • Use dbg=false, so sql statements are not printed. 
>> % Connect to the database as follows when you need to perform many inserts.
>> d=zdb('temp.sqlite','hot',true,'fastmode',true,'dbg',false);

>> % If you already have the table schema setup, you may want to set d.hot=false.
>> % Also set d.dbg=false if you don't want to see the debugging messages.

Loading from text or Excel files

You can load data from a file into database table using zdb.loadtable function. The files can be local files stored on your computer or external files that are available on the web. They can be tab-separated, csv-separated, or Excel files. If the first row of these files do not contain the field names, you need specify the field names manually.

In the example below, we load a tab file into a new table. 

>> % Let's use Table 12 available at: https://elifesciences.org/articles/35471/figures
>> % We use maxlines=10 for demonstration purposes. Don't specify maxlines if you want
>> % to load the entire file.
>> % We use reset=1, so that the table (if already present) is emptied before we load it.
>> d.loadtabfile('goenrichment','https://elifesciences.org/download/aHR0cHM6Ly9jZG4uZWxpZmVzY2llbmNlcy5vcmcvYXJ0aWNsZXMvMzU0NzEvZWxpZmUtMzU0NzEtZGF0YTEyLXYyLnhsc3g=/elife-35471-data12-v2.xlsx?_hash=o1Ew%2BslRkaIhjvGAPrVHbAcYlBlcmJQSgocT6EdOxVU%3D','maxlines',10,'reset',true);
--- NOTICE: added field "goenrichment.annotated"
--- NOTICE: added field "goenrichment.significant"
--- NOTICE: added field "goenrichment.expected"

>> d.goenrichment.dumpall
id goid       term                                                        pvalue  annotated significant expected
== ========== =========================================================== ======= ========= =========== ========
1  GO:0006357 regulation of transcription from RNA polymerase II promoter 3.7e-09 457       19          3.99    
2  GO:0006355 regulation of transcription, DNA-templated                  7.8e-09 719       23          6.27    
3  GO:1903506 regulation of nucleic acid-templated transcription          8e-09   720       23          6.28    
4  GO:2001141 regulation of RNA biosynthetic process                      8e-09   720       23          6.28    
5  GO:0051252 regulation of RNA metabolic process                         1.3e-08 737       23          6.43    
6  GO:0006351 transcription, DNA-templated                                3.6e-08 778       23          6.79    
7  GO:0006366 transcription from RNA polymerase II promoter               3.6e-08 524       19          4.57    
8  GO:0097659 nucleic acid-templated transcription                        3.7e-08 779       23          6.8     
9  GO:0032774 RNA biosynthetic process                                    4e-08   783       23          6.83    


>> % If you don't want to load all the columns, use selectfields to specify a subset.
>> d.loadtabfile('goenrichment2','https://elifesciences.org/download/aHR0cHM6Ly9jZG4uZWxpZmVzY2llbmNlcy5vcmcvYXJ0aWNsZXMvMzU0NzEvZWxpZmUtMzU0NzEtZGF0YTEyLXYyLnhsc3g=/elife-35471-data12-v2.xlsx?_hash=o1Ew%2BslRkaIhjvGAPrVHbAcYlBlcmJQSgocT6EdOxVU%3D','maxlines',10,'reset',true, 'selectfields',{'GO.ID','Term','pValue'});
--- NOTICE: created table "goenrichment2"
--- NOTICE: added field "goenrichment2.goid"
--- NOTICE: added field "goenrichment2.term"
--- NOTICE: added field "goenrichment2.pvalue"

>> d.goenrichment2.dumpall
id goid       term                                                        pvalue 
== ========== =========================================================== =======
1  GO:0006357 regulation of transcription from RNA polymerase II promoter 3.7e-09
2  GO:0006355 regulation of transcription, DNA-templated                  7.8e-09
3  GO:1903506 regulation of nucleic acid-templated transcription          8e-09  
4  GO:2001141 regulation of RNA biosynthetic process                      8e-09  
5  GO:0051252 regulation of RNA metabolic process                         1.3e-08
6  GO:0006351 transcription, DNA-templated                                3.6e-08
7  GO:0006366 transcription from RNA polymerase II promoter               3.6e-08
8  GO:0097659 nucleic acid-templated transcription                        3.7e-08
9  GO:0032774 RNA biosynthetic process                                    4e-08