MATLAB Examples

Contents

function d=demo(d,varargin)
% If an existing database object d is provided use it; otherwise, open a connection to a demo.sqlite database under the glob('datadir/db') directory.
% Provide varargin to override the parameters used to open database
% connections. e.g, use: db.demo('temp.sqlite','dbg',false)
% Copyright (C) 2012 by Ahmet Sacan

if ~exist('varargin','var'); varargin={}; end
if ~exist('d','var')||isempty(d); d=glob('datadir/demo.sqlite'); io_mkfiledirif(d); end

Opening a Database Connection

if hot=true, the database structure is modified as needed. e.g, when you get a table with d.table('tablex'), tablex is automatically created. Similarly, when you insert entries into database tables, the fields are automatically created. Use dbg=true to have all the executed sql statements be printed out.

if isa(d,'db')&&~isempty(varargin); d.hot=true;
else d=db(d,'hot',true,'dbg',true,varargin{:}); end
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
SELECT tbl_name,sql FROM sqlite_master WHERE type='table' AND sql LIKE "CREATE TABLE %" AND tbl_name IN ('car','user','user_car')
PRAGMA TABLE_INFO("car")
PRAGMA TABLE_INFO("user")
PRAGMA TABLE_INFO("user_car")
SELECT tbl_name,sql FROM sqlite_master WHERE type='index' AND sql LIKE "CREATE %INDEX %" AND tbl_name IN ('car','user','user_car')

Connecting to a Table

the following creates the table if not already exists, the user table will be created with a single "id" field.

t=d.table('user');

% you can drop previously created tables if you need to create them afresh.
d.droptable('user');
t=d.table('user');
t.insert(struct('name','ahmet'));
DROP TABLE IF EXISTS "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"
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup90"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(5) COLLATE nocase)
DROP TABLE "user_backup90"
COMMIT
--- NOTICE: added field "user.name"
INSERT INTO "user"("name") VALUES ('ahmet')

Inserting data

%insert one/more structs
t.insert(struct('name','ahmet'));
t.insert(struct('name','tom'));
t.insert(struct('name','jerry'));
INSERT INTO "user"("name") VALUES ('ahmet')
INSERT INTO "user"("name") VALUES ('tom')
INSERT INTO "user"("name") VALUES ('jerry')
%insert cell arrays. each row of cell arrays must have all fields of the
%table.
t.insert({100,'apple'});
INSERT INTO "user"("id","name") VALUES (100,'apple')
%the following would produce an integrity constraint error, because id=1
%already exists.
%t.insert(struct{1,'jerry'});
try; t.insert(struct('id',1,'name','jerry'));
catch me; dbg_errormsg(me); end

%if you need to insert if a row does not exist, but update if it does, use
%updateoninserterror, so an update() will be performed if insertion fails.
d.updateoninserterror=true;
t.insert(struct('id',1,'name','orange'));

%set it back to false so we do get errors.
d.updateoninserterror=false;
INSERT INTO "user"("id","name") VALUES (1,'jerry')
Ignoring error: UNIQUE constraint failed: user.id
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup79"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(6) COLLATE nocase)
INSERT INTO "user" SELECT "id","name" FROM "user_backup79"
DROP TABLE "user_backup79"
COMMIT
--- NOTICE: changed fieldtype "user.name"->string
INSERT INTO "user"("id","name") VALUES (1,'orange')
UPDATE "user" SET "name"='orange' WHERE id=1

Getting data from tables

%Use dbtable.getby() function to select rows that meet given criteria.
%get the entry with name='ahmet'.
t.getby(struct('name','ahmet'))
SELECT * FROM "user" WHERE name='ahmet' 

ans = 

      id: 2
    name: 'ahmet'

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

ans = 

2x1 struct array with fields:

    id
    name

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

ans = 

2x1 struct array with fields:

    id
    name

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

ans = 

      id: 2
    name: 'ahmet'

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

ans = 

5x1 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   ahmet 
3   tom   
4   jerry 
100 apple 
%getcolby() gets a single column. here, we are getting the name column.
t.getcolby('(id<3)','name')
SELECT "name" FROM "user" WHERE (id<3) 

ans = 

    'orange'
    'ahmet'

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

ans = 

      id: 2
    name: 'ahmet'

%getcol() gets a column from the entire table.
t.getcol('name')
SELECT "name" FROM "user" WHERE 1 

ans = 

    'orange'
    'ahmet'
    'tom'
    'jerry'
    'apple'

%getfirstrow() gets a the first row of the table.
t.getfirstrow()
SELECT * FROM "user" WHERE 1 LIMIT 0,1

ans = 

      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

%or from the specified row onward.
t.getnrows(1,2)
SELECT * FROM "user" WHERE 1 LIMIT 2,1

ans = 

      id: 3
    name: 'tom'

Getting data as dbentity objects

t.findby(struct('name','ahmet'))
SELECT * FROM "user" WHERE name='ahmet' 

ans = 

  id: 2
  name: ahmet
t.findby(struct('name',{{'ahmet','tom'}}))
SELECT * FROM "user" WHERE name IN ('ahmet','tom') 

ans = 

Fields: id,name
2 entries.
t.findby(struct('name',{'ahmet','tom'}))
SELECT * FROM "user" WHERE name IN ('ahmet','tom') 

ans = 

Fields: id,name
2 entries.
t.findby('SELECT * FROM user WHERE name like "%met"')
SELECT * FROM user WHERE name like "%met"

ans = 

  id: 2
  name: ahmet
t.findall()
SELECT * FROM "user" WHERE 1 

ans = 

Fields: id,name
5 entries.
t.findrowby('(id=2)')
SELECT * FROM "user" WHERE (id=2) LIMIT 0,1

ans = 

  id: 2
  name: ahmet
t.findfirstrow()
SELECT * FROM "user" WHERE 1 LIMIT 0,1

ans = 

  id: 1
  name: orange
t.findnrows(2)
SELECT * FROM "user" WHERE 1 LIMIT 0,2

ans = 

Fields: id,name
2 entries.
t.findnrows(1,2)
SELECT * FROM "user" WHERE 1 LIMIT 2,1

ans = 

  id: 3
  name: tom

Using dbentity to get/set data.

e=t.findrowbyid(2,'id');
%notice that e.name was not in the entity, but it will be fetched when you
%access it.
e.name
e.name='adam';
SELECT "id" FROM "user" WHERE id=2 LIMIT 0,1
SELECT "name" FROM "user" WHERE id=2 LIMIT 0,1

ans =

ahmet

%data is saved into database when you explicitly call save(), or automatically,
%when the object handle is deleted.
clear e
UPDATE "user" SET "name"='adam' WHERE id=2
e=t.findrowbyid(2,'id');
e.name='ahmet'
e.save;
SELECT "id" FROM "user" WHERE id=2 LIMIT 0,1

e = 

  id: 2
  name: ahmet
UPDATE "user" SET "name"='ahmet' WHERE id=2

Using yaml/json as field types, to store complex data.

t.changefieldtypeifnot('info','yaml');
e=t.findrowbyid(2,'id');
e.info=struct('age',34,'height',5.10,'weight',175);
e.info
e.save;
t.findrowbyid(2,'id').info
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup996"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(6) COLLATE nocase, "info" text COLLATE nocase)
INSERT INTO "user" SELECT "id","name",null FROM "user_backup996"
DROP TABLE "user_backup996"
COMMIT
SELECT "id" FROM "user" WHERE id=2 LIMIT 0,1

ans = 

       age: 34
    height: 5.1000
    weight: 175

UPDATE "user" SET "info"='age: 34
height: 5.1
weight: 175' WHERE id=2
SELECT "id" FROM "user" WHERE id=2 LIMIT 0,1
SELECT "info" FROM "user" WHERE id=2 LIMIT 0,1

ans = 

       age: 34
    height: 5.1000
    weight: 175

Changing table structure

If db.hot is on, most changes to the table structure can be automatically performed during insert() operations. If you need more control, you can use the utility functions described here.

drop a field

t.dropfield('info');
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup593"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(6) COLLATE nocase)
INSERT INTO "user" SELECT "id","name" FROM "user_backup593"
DROP TABLE "user_backup593"
COMMIT
%add a new field
t.addfield('email','type','integer')
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup1000"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(6) COLLATE nocase, "email" integer)
INSERT INTO "user" SELECT "id","name",null FROM "user_backup1000"
DROP TABLE "user_backup1000"
COMMIT

ans = 

  dbtable with properties:

                           db: [1x1 db]
                         name: 'user'
                detachedmodel: []
                      dynamic: 1
                      rellist: [1x0 struct]
                     relpaths: {}
                    pathcache: {}
                     behavior: [1x1 struct]
                thawfieldfunc: []
    thawfieldfunconlynonempty: 0
              freezefieldfunc: []
                        model: [1x1 struct]
                       fields: {3x1 cell}
                     idfields: {'id'}
                 autogenfield: 'id'
                  entityclass: @dbentity
                      indexes: {0x1 cell}

%change the type of an existing field
t.changefieldtype('email','string','length',32)

%change an existing field
t.addfield('age');
t.changefield('age','type','decimal')
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup297"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(6) COLLATE nocase, "email" text(32) COLLATE nocase)
INSERT INTO "user" SELECT "id","name","email" FROM "user_backup297"
DROP TABLE "user_backup297"
COMMIT

ans = 

  dbtable with properties:

                           db: [1x1 db]
                         name: 'user'
                detachedmodel: []
                      dynamic: 1
                      rellist: [1x0 struct]
                     relpaths: {}
                    pathcache: {}
                     behavior: [1x1 struct]
                thawfieldfunc: []
    thawfieldfunconlynonempty: 0
              freezefieldfunc: []
                        model: [1x1 struct]
                       fields: {3x1 cell}
                     idfields: {'id'}
                 autogenfield: 'id'
                  entityclass: @dbentity
                      indexes: {0x1 cell}

PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup320"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(6) COLLATE nocase, "email" text(32) COLLATE nocase, "age" integer)
INSERT INTO "user" SELECT "id","name","email",null FROM "user_backup320"
DROP TABLE "user_backup320"
COMMIT
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
BEGIN TRANSACTION
SELECT count(*) FROM "user" WHERE 1 LIMIT 0,1
ALTER TABLE "user" RENAME TO "user_backup136"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(6) COLLATE nocase, "email" text(32) COLLATE nocase, "age" real)
INSERT INTO "user" SELECT "id","name","email","age" FROM "user_backup136"
DROP TABLE "user_backup136"
COMMIT

ans = 

  dbtable with properties:

                           db: [1x1 db]
                         name: 'user'
                detachedmodel: []
                      dynamic: 1
                      rellist: [1x0 struct]
                     relpaths: {}
                    pathcache: {}
                     behavior: [1x1 struct]
                thawfieldfunc: []
    thawfieldfunconlynonempty: 0
              freezefieldfunc: []
                        model: [1x1 struct]
                       fields: {4x1 cell}
                     idfields: {'id'}
                 autogenfield: 'id'
                  entityclass: @dbentity
                      indexes: {0x1 cell}

(Unique) Indexes and primary keys.

%you usually need to set a uniqueness constraint, to prevent duplicate rows
t.adduniqueindexifnotexists('name,email');

%create an index on a field, to speed up queries that check values in this
%column.
t.addindex('email')

%You can add/remove a field to 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);
t.addprimarykey('name'); %this will make (id,name) pair the new composite primary key.

%set the primary key to one or more fields (not combining with existing primary keys).
%You must ensure that existing values in this column are unique.
t.setprimarykey('id');
CREATE UNIQUE INDEX "user_name_email_idx" ON "user"("name","email")
CREATE  INDEX "user_email_idx" ON "user"("email")

ans = 

  dbtable with properties:

                           db: [1x1 db]
                         name: 'user'
                detachedmodel: []
                      dynamic: 1
                      rellist: [1x0 struct]
                     relpaths: {}
                    pathcache: {}
                     behavior: [1x1 struct]
                thawfieldfunc: []
    thawfieldfunconlynonempty: 0
              freezefieldfunc: []
                        model: [1x1 struct]
                       fields: {4x1 cell}
                     idfields: {'id'}
                 autogenfield: 'id'
                  entityclass: @dbentity
                      indexes: {2x1 cell}

PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
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_backup461"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(6) COLLATE nocase, "email" text(32) COLLATE nocase, "age" real)
CREATE  INDEX IF NOT EXISTS "user_email_idx" ON "user"("email")
CREATE UNIQUE INDEX IF NOT EXISTS "user_name_email_idx" ON "user"("name","email")
INSERT INTO "user" SELECT "id","name","email","age" FROM "user_backup461"
DROP TABLE "user_backup461"
COMMIT
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
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_backup562"
CREATE TABLE "user"( "id" integer, "name" text(6) COLLATE nocase, "email" text(32) COLLATE nocase, "age" real, PRIMARY KEY("id","name"))
CREATE  INDEX IF NOT EXISTS "user_email_idx" ON "user"("email")
CREATE UNIQUE INDEX IF NOT EXISTS "user_name_email_idx" ON "user"("name","email")
INSERT INTO "user" SELECT "id","name","email","age" FROM "user_backup562"
DROP TABLE "user_backup562"
COMMIT
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
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_backup52"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(6) COLLATE nocase, "email" text(32) COLLATE nocase, "age" real)
CREATE  INDEX IF NOT EXISTS "user_email_idx" ON "user"("email")
CREATE UNIQUE INDEX IF NOT EXISTS "user_name_email_idx" ON "user"("name","email")
INSERT INTO "user" SELECT "id","name","email","age" FROM "user_backup52"
DROP TABLE "user_backup52"
COMMIT

Using db.query() to run sql queries

%results of the query, if available are returned as structs.
d.query('select * FROM user')
select * FROM user

ans = 

5x1 struct array with fields:

    id
    name
    email
    age

%you can use question marks to fill values into the sql statement, with
%proper quoting.
% * ? 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 = 

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

%you can use the same usage with db.fillquery() function, to construct
%your own sql queries.
d.fillquery('select * FROM ?? WHERE name=? AND ???','user','ahmet','id<3')
d.fillquery('select * FROM ?? WHERE name=? AND ???','user',5,'id<3')
ans =

select * FROM "user" WHERE name='ahmet' AND id<3


ans =

select * FROM "user" WHERE name=5 AND id<3

Helper Function for Constructing SQL Queries

strings, structs, and cells can be used to construct sql queries. multiple values (given as cell array) for the same field are OR'ed, whereas multiple 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')
%the second argument specifies whether we prepend the phrase with 'WHERE'
%or not.
d.whereclause(struct('id',3),false)
id=3
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')
%use 'X___1','X___2',etc. as field names for the proceeding sql phrases.
d.whereclause(struct('name','ahmet','X___1','lastname LIKE "saca%"'))
WHERE name='ahmet' AND lastname LIKE "saca%"
%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 associations

%You can create associations between tables using
%db.addmanytoone/addmanytomany/addonetoone functions. 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
%db.hot=true so the fields and join tables can be automatically created in
%the database.
d.table('user');
d.table('car');
d.addmanytomany('user','car');

% Let's fill in some example data.
% 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'}));
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}));

e=d.table('user').findby(struct('name','agent50'))
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
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_backup993"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(7) COLLATE nocase, "email" text(32) COLLATE nocase, "age" real)
CREATE  INDEX IF NOT EXISTS "user_email_idx" ON "user"("email")
CREATE UNIQUE INDEX IF NOT EXISTS "user_name_email_idx" ON "user"("name","email")
INSERT INTO "user" SELECT "id","name","email","age" FROM "user_backup993"
DROP TABLE "user_backup993"
COMMIT
--- NOTICE: changed fieldtype "user.name"->string
INSERT INTO "user"("id","name") VALUES (50,'agent50')
INSERT INTO "user"("id","name") VALUES (60,'agent60')
INSERT INTO "user"("id","name") VALUES (70,'agent70')
INSERT INTO "car"("id","name") VALUES (5,'bmw')
Ignoring error: UNIQUE constraint failed: car.id
UPDATE "car" SET "name"='bmw' WHERE id=5
INSERT INTO "car"("id","name") VALUES (10,'mercedes')
UPDATE "car" SET "name"='mercedes' WHERE id=10
INSERT INTO "car"("id","name") VALUES (15,'honda')
UPDATE "car" SET "name"='honda' WHERE id=15
INSERT INTO "car"("id","name") VALUES (20,'toyota')
UPDATE "car" SET "name"='toyota' WHERE id=20
INSERT INTO "user_car"("user_id","car_id") VALUES (50,5)
INSERT INTO "user_car"("user_id","car_id") VALUES (50,10)
INSERT INTO "user_car"("user_id","car_id") VALUES (60,10)
INSERT INTO "user_car"("user_id","car_id") VALUES (60,20)
INSERT INTO "user_car"("user_id","car_id") VALUES (70,5)
INSERT INTO "user_car"("user_id","car_id") VALUES (70,15)
SELECT * FROM "user" WHERE name='agent50' 

e = 

Associations:
  car: manyToMany:car
1 entries.
  id: 50
  name: agent50
  email: 
  age: 
f=e.car
SELECT "car".* FROM "user_car","car" WHERE "user_car"."user_id"=50 AND "user_car"."car_id"="car"."id"

f = 

Associations:
  userdata: manyToMany:userdata
  user_user_id: manyToMany:user_user_id
  user: manyToOne:user
  user_car: oneToMany:user_car
Fields: id,name
2 entries.
f.dump
id name    
== ========
5  bmw     
10 mercedes
e.car.name
SELECT "car".* FROM "user_car","car" WHERE "user_car"."user_id"=50 AND "user_car"."car_id"="car"."id"

ans = 

    'bmw'    'mercedes'

Dynamic Behaviors

Behaviors are managed in each table model's "behavior" field. We look for a class with the name 'db[behaviorname]' Behaviors are setup during db.expandmodels and register themselves into onbeforeinsert, onafterinsert, e.g., table events. db.modelpatchfile is a good place for the behaviors to be specified. You can also add behaviors using dbtable.addbehavior.

t=d.user;
t.addbehavior('timestampable_demo');
t.insert(struct('id',55,'name','apple'));
t.getrowby(struct('id',55))
pause(2) %pause 2 seconds, so we get a different modification time.
t.update(struct('id',55,'name','orange'));
t.getrowby(struct('id',55))
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
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_backup326"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(7) COLLATE nocase, "email" text(32) COLLATE nocase, "age" real, "created" text(17) COLLATE nocase)
CREATE  INDEX IF NOT EXISTS "user_email_idx" ON "user"("email")
CREATE UNIQUE INDEX IF NOT EXISTS "user_name_email_idx" ON "user"("name","email")
INSERT INTO "user" SELECT "id","name","email","age",null FROM "user_backup326"
DROP TABLE "user_backup326"
COMMIT
--- NOTICE: added field "user.created"
PRAGMA journal_mode = MEMORY
PRAGMA synchronous = OFF
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_backup905"
CREATE TABLE "user"( "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" text(7) COLLATE nocase, "email" text(32) COLLATE nocase, "age" real, "created" text(17) COLLATE nocase, "modified" text(17) COLLATE nocase)
CREATE  INDEX IF NOT EXISTS "user_email_idx" ON "user"("email")
CREATE UNIQUE INDEX IF NOT EXISTS "user_name_email_idx" ON "user"("name","email")
INSERT INTO "user" SELECT "id","name","email","age","created",null FROM "user_backup905"
DROP TABLE "user_backup905"
COMMIT
--- NOTICE: added field "user.modified"
INSERT INTO "user"("id","name","created","modified") VALUES (55,'apple','15-03-14 08:34:03','15-03-14 08:34:03')
SELECT * FROM "user" WHERE id=55 LIMIT 0,1

ans = 

          id: 55
        name: 'apple'
       email: []
         age: []
     created: '15-03-14 08:34:03'
    modified: '15-03-14 08:34:03'

UPDATE "user" SET "name"='orange',"modified"='15-03-14 08:34:06' WHERE id=55
SELECT * FROM "user" WHERE id=55 LIMIT 0,1

ans = 

          id: 55
        name: 'orange'
       email: []
         age: []
     created: '15-03-14 08:34:03'
    modified: '15-03-14 08:34:06'

Constructing and populating a large database / Bulk loading.

%If you need to perform many inserts, use the following recipe:
d=db(d.dsn,'hot',true,'ignoreinserterror',true,'fastmode',true,'dbg',false);
%use dbg=false, so sql statements are not printed.
%if you know the database structure is finalized, you can set hot=false.
ans = 

       name: ''
    version: ''
        dsn: 'D:/data/demo.sqlite'
       conn: '...not connected yet...'
        hot: 1
        dbg: 0
    dynamic: 1