From SQL to ORM

Arnaud Bouchez
June 2015
From SQL to ORM
 SQL
 NoSQL
 ORM
 ODM
 practice

From SQL to ORM

June 2015
SQL
 De-Facto standard for data manipulation
 Schema-based

 Relational-based

 ACID by transactions
 Time proven and efficient

 Almost standard

From SQL to ORM

June 2015
SQL
 De-Facto standard for data manipulation
 Schema-based

 Relational-based

From SQL to ORM

June 2015
Not Only SQL
 Two main families of NoSQL databases:
 Aggregate-oriented databases
 Graph-oriented databases

Martin Fowler

From SQL to ORM

June 2015
Not Only SQL
 Graph Database
 Store data by their relations / associations

From SQL to ORM

June 2015
Not Only SQL
 Aggregate
 is a collection of data
that we interact with
as a unit
 forms the boundaries
for ACID operations
in a given model

(Domain-Driven Design modeling)

From SQL to ORM

June 2015
Not Only SQL
 Aggregate Database families

 Document-based
e.g. MongoDB, CouchDB

 Key/Value
e.g. Redis

 Column family
e.g. Cassandra

From SQL to ORM

June 2015
Not Only SQL
 Are designed to scale for the web
 Examples: Google farms, Amazon
 Should not be used as scaling RDBMS

 Can be schema-less
 For document-based and key/value
or column-driven

 Can be BLOB storage

From SQL to ORM

June 2015
Not Only SQL
 RBMS stores data per table
 JOIN the references to get the aggregate

From SQL to ORM

June 2015
Not Only SQL
 NoSQL stores aggregate as documents
 Whole data is embedded

From SQL to ORM

June 2015
Not Only SQL
 NoSQL stores aggregate as documents
 Whole data is embedded

(boundaries for ACID behavior)

From SQL to ORM

June 2015
Not Only SQL
 Data modeling

From SQL to ORM

June 2015
Not Only SQL
 Data modeling

SQL NoSQL
 Normalization  Denormalization
 Consistency  Duplicated data
 Transactions  Document ACID
 Vertical scaling  Horizontal scaling

From SQL to ORM

June 2015
Not Only SQL
 SQL > NoSQL
 Ubiquitous SQL
 Easy vertical scaling

 Data size (avoid duplicates and with no schema)

 Data is stored once, therefore consistent

 Complex ACID statements

 Aggregation functions (depends)

From SQL to ORM

June 2015
Not Only SQL

From SQL to ORM

June 2015
Not Only SQL
 NoSQL > SQL
 Uncoupled data: horizontal scaling
 Schema-less: cleaner evolution

 Straight-To-ODM

 Version management (e.g. CouchDB)

 Graph storage (e.g. Redis)

From SQL to ORM

June 2015
Object Relational Mapping (ORM)

 ORM
 gives a set of methods (CRUD)
 to ease high-level objects persistence

 into an RDBMS

 via mapping

From SQL to ORM

June 2015
Object Relational Mapping (ORM)

 Since decades
 Classes
are the root of our OOP model
 RDBMS is (the) proven way of storage

 Some kind of "glue" is needed to let class

properties be saved into one or several tables

From SQL to ORM

June 2015
Object Relational Mapping (ORM)

 In fact
 Classes
are accessed via Delphi/Java/C# code
 RDBMS is accessed via SQL

 SQL by itself is a full programming language

 With diverse flavors (e.g. data types)
 Difficult to switch the logic

 Error prone, and difficult to maintain

From SQL to ORM

June 2015
Object Relational Mapping (ORM)

 Sometimes, object object

 therewill be nothing better instance instance

than a tuned SQL statement CRUD

operations

 But most of the time, SQL ORM

 You
need just to perform mapping
some basic CRUD operations
(Create Retrieve Update Delete) RDBMS SQL

RDBMS

From SQL to ORM

June 2015
Object Relational Mapping (ORM)

myObject.Value := 10;

myContext.Update(myObject);

UPDATE OBJTABLE SET …

01010101001110011111

From SQL to ORM

June 2015
Object Relational Mapping (ORM)

 Benefits  No magic bullet

 Stay at OOP level  Hard task

 Manage SQL flavors  Hidden process
 Persistence Ignorance  Legacy (tuned) SQL
 Optimize SQL  Performance cost
 Cache

From SQL to ORM

June 2015
Object Relational Mapping (ORM)

 Benefits  No magic bullet

 Stay at OOP level  Hard task

 Manage SQL flavors  Hidden process
 Persistence Ignorance  Legacy (tuned) SQL
 Optimize SQL  Performance cost
 Cache (or not)

From SQL to ORM

June 2015
Object Relational Mapping (ORM)
ORM

 Mapping may be created:

class type data model
 Code-first
 Objects are defined,
object instance RDBMS
then persisted (dehydrated)
 Usually the best for a new project

 Model-first
 Froman (existing) database model
 Sometimes difficult to work with legacy structures

From SQL to ORM

June 2015
Object Relational Mapping (ORM)

 Mapping may be defined:

 by configuration
 via code (attributes or fluent interface)
 via external files (XML/JSON)

 by convention
 from object layout
 from database layout

From SQL to ORM

June 2015
Object Relational Mapping (ORM)

 Objects may be:

 Any business class (PODO)

 Eases integration with existing code
 But may pollute the class (attributes)

 Inherit from a common class

 Geta shared behavior
 Tend to enforce DDD’s persistence agnosticity

From SQL to ORM

June 2015
Object Relational Mapping (ORM)

 ORM advantages
 Compile time naming and types check
(strong types and names)
 One language to rule all your logic
(no mix with SQL nor LINQ syntax)
 Database abstraction
(the ORM knows all dialects, and can switch)
 Able to cache the statements and the values
 You still can write hand-tuned SQL if needed

From SQL to ORM

June 2015
Object Relational Mapping (ORM)

 Don't think about…

 tables with data types (varchar/number...),
but objects with high level types
 Master/Detail,
but logical units (your aggregates)
 writing SQL,
but writing your business code
 "How will I store it?",
but "Which data do I need?".

From SQL to ORM

June 2015
Object Document Mapping (ODM)
 Aggregate = all data in a given context

Such documents do map our objects!

From SQL to ORM

June 2015
Object Document Mapping (ODM)
 CRUD operation on Aggregates documents

mORMot’s ODM is able to share code with ORM

From SQL to ORM

June 2015
mORMot’s RESTful ORM/ODM

From SQL to ORM

June 2015
mORMot’s RESTful ORM/ODM

 mORMot’s client-server RESTful ORM/ODM

 code-first
or model-first ORM/ODM
 ORM for RDBMS: generates SQL

 ODM for NoSQL: handles documents and queries

 convention (TSQLRecord) over configuration

 can be consumed via REST

 over HTTP or WebSockets, via JSON transmission
 or locally, on the server side, e.g. in SOA services

From SQL to ORM

June 2015
mORMot’s RESTful ORM/ODM
UI Components UI UI UI 1 UI 2 (web)

MVC MVC/MVVM MVC

RAD code mapping
binding binding binding

DataBase Delphi classes ORM Client 1 Client 2

Secure
Handwritten Generated
protocol
SQL SQL
(REST)

DataBase Database Server

Persistence
layer

ORM

Generated
SQL

Database

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Defining the object
/// some enumeration
// - will be written as 'Female' or 'Male' in our UI Grid
// - will be stored as its ordinal value, i.e. 0 for sFemale, 1 for sMale
// - as you can see, ladies come first, here
TSex = (sFemale, sMale);

/// table used for the Babies queries

TSQLBaby = class(TSQLRecord)
private
fName: RawUTF8;
fAddress: RawUTF8;
fBirthDate: TDateTime;
fSex: TSex;
published
property Name: RawUTF8 read fName write fName;
property Address: RawUTF8 read fAddress write fAddress;
property BirthDate: TDateTime read fBirthDate write fBirthDate;
property Sex: TSex read fSex write fSex;
end;

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Defining the Model and the Server:
Model := TSQLModel.Create([TSQLBaby],'rootURI');
ServerDB := TSQLRestServerDB.Create(Model,'data.db'),true);
ServerDB.CreateMissingTables;
Server := TSQLHttpServer.Create('8080',ServerDB);

 Defining the Model and the Client:

Model := TSQLModel.Create([TSQLBaby],'rootURI');
Client := TSQLHttpClient.Create(Server,'8080', Model);

 Both will now communicate e.g. over

http://server:8080/rootURI/Baby

From SQL to ORM

June 2015
mORMot’s RESTful ORM
var Baby: TSQLBaby;
ID: integer;
begin
// create a new record, since Smith, Jr was just born
Baby := TSQLBaby.Create;
try
Baby.Name := 'Smith';
Baby.Address := 'New York City';
Baby.BirthDate := Now;
Baby.Sex := sMale;
ID := Client.Add(Baby);
finally
Baby.Free;
end;
// update record data
Baby := TSQLBaby.Create(Client,ID);
try
assert(Baby.Name='Smith');
Baby.Name := 'Smeeth';
Client.Update(Baby);
finally
Baby.Free;
end;
// retrieve record data
Baby := TSQLBaby.Create;
try
Client.Retrieve(ID,Baby);
// we may have written: Baby := TSQLBaby.Create(Client,ID);
assert(Baby.Name='Smeeth');
finally
Baby.Free;
end;
// delete the created record
Client.Delete(TSQLBaby,ID);
end;

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Returning several objects
aMale := TSQLBaby.CreateAndFillPrepare(Client,
'Name LIKE ? AND Sex = ?',['A%',ord(sMale)]);
try
while aMale.FillOne do
DoSomethingWith(aMale);
finally
aMale.Free;
end;

 Results are transmitted as a JSON array

 Results can be cached at client or server side

 Only one object instance is allocated and filled

 Full WHERE clause of the SELECT is at hand

 See also function TSQLRest.RetrieveList(): TObjectList

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 JSON transmission layout
 Expanded / standard (AJAX)
JSON array of JSON objects
[{"ID":1},{"ID":2},{"ID":3},{"ID":4},{"ID":5},{"ID":6},{"ID":7}]

 Non expanded / faster (Delphi)

JSON object of JSON array of values, first row being idents
{"fieldCount":1,"values":["ID",1,2,3,4,5,6,7]}

In mORMot, all JSON content is parsed and processed in-place,

then directly mapped to UTF-8 structures.

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 « One to one » / « One to many » cardinality
TSQLMyFileInfo = class(TSQLRecord)
private
FMyFileDate: TDateTime;
FMyFileSize: Int64;
published
property MyFileDate: TDateTime read FMyFileDate write FMyFileDate;
property MyFileSize: Int64 read FMyFileSize write FMyFileSize;
end;

TSQLMyFile = class(TSQLRecord)
private
FSecondOne: TSQLMyFileInfo;
FFirstOne: TSQLMyFileInfo;
FMyFileName: RawUTF8;
published
property MyFileName: RawUTF8 read FMyFileName write FMyFileName;
property FirstOne: TSQLMyFileInfo read FFirstOne write FFirstOne;
property SecondOne: TSQLMyFileInfo read FSecondOne write FSecondOne;
end;

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Automatic JOINed query
 Atconstructor level
 With nested instances memory management

var MyFile: TSQLMyFile;

begin
MyFile := TSQLMyFile.CreateJoined(Client,aMyFileID);
try
// here MyFile.FirstOne and MyFile.SecondOne are true instances
// and have already retrieved from the database by the constructor
// so you can safely access MyFile.FirstOne.MyFileDate or MyFile.SecondOne.MyFileSize here!
finally
MyFile.Free; // will release also MyFile.FirstOne and MyFile.SecondOne
end;
end;

 See also CreateAndFillPrepareJoined()

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Server or Client Cache
 Disabledby default
 May be activated
 For a given table
 For a given set of IDs
 On a given TSQLRest

 In
conjunction with
Server DB cache

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Object Time Machine
Database.TrackChanges([TSQLInvoice]);

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Object Time Machine
Database.TrackChanges([TSQLInvoice]);
aInvoice := TSQLInvoice.Create;
aHist := TSQLRecordHistory.CreateHistory(aClient,TSQLInvoice,400);
try
writeln('History Count: ',aHist.HistoryCount);
for i := 0 to aHist.HistoryCount-1 do begin
aHist.HistoryGet(i,aEvent,aTimeStamp,aInvoice);
writeln('Event: ',ord(aEvent))^);
writeln('TimeStamp: ',TTimeLogBits(aTimeStamp).ToText);
writeln('Identifier: ',aInvoice.Number);
end;
...

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Master/Slave Replication
TSQLRecordPeopleVersioned = class(TSQLRecordPeople)
protected
fFirstName: RawUTF8;
fLastName: RawUTF8;
fVersion: TRecordVersion;
published
property FirstName: RawUTF8 read fFirstName write fFirstName;
property LastName: RawUTF8 read fLastName write fLastName;
property Version: TRecordVersion read fVersion write fVersion;
end;
 Hidden monotonic version number will be maintained
to monitor Add/Update/Delete actions
 Deletions stored in a separated table

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Master/Slave Replication
MasterServer := TSQLRestServerDB.Create(MasterModel,'master.db3');
HttpMasterServer := TSQLHttpServer.Create('8888',[MasterServer]);

MasterClient := TSQLHttpClientHTTP.Create(
'127.0.0.1',HTTP_DEFAULTPORT,MasterModel);

SlaveServer := TSQLRestServerDB.Create(SlaveModel,'slave.db3');

SlaveServer.RecordVersionSynchronizeSlave(
TSQLRecordPeopleVersioned,MasterClient);

From SQL to ORM

June 2015
mORMot’s RESTful ORM
Master

 Master/Slave Replication MasterServer

(MasterModel)

MasterServer := TSQLRestServerDB.Create( HTTP

MasterModel,'master.db3'); Slave
HttpMasterServer := TSQLHttpServer.Create(
'8888',[MasterServer]); MasterClient
master.db3
(MasterModel)

MasterClient := TSQLHttpClientHTTP.Create( On Demand

'127.0.0.1',HTTP_DEFAULTPORT,MasterModel); Replication

SlaveServer := TSQLRestServerDB.Create( SlaveServer

(SlaveModel)
SlaveModel,'slave.db3');

SlaveServer.RecordVersionSynchronizeSlave(
slave.db3
TSQLRecordPeopleVersioned,MasterClient);

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Master/Slave Real-Time Replication
 Push ORM modifications over WebSockets

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Master/Slave Real-Time Replication
MasterServer := TSQLRestServerDB.Create(MasterModel,'master.db3');
HttpMasterServer :=
TSQLHttpServer.Create('8888',[MasterServer],'+',useBidirSocket);
HttpMasterServer.WebSocketsEnable(Server,'PrivateAESEncryptionKey');
MasterServer.RecordVersionSynchronizeMasterStart;

MasterClient := TSQLHttpClientWebSockets.Create(
'127.0.0.1',HTTP_DEFAULTPORT,MasterModel);
MasterClient.WebSocketsUpgrade('PrivateAESEncryptionKey');

SlaveServer := TSQLRestServerDB.Create(SlaveModel,'slave.db3');
SlaveServer.RecordVersionSynchronizeSlaveStart(
TSQLRecordPeopleVersioned,MasterClient);

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Master/Slave Real-Time Replication
Master

MasterServer
SlaveServer := TSQLRestServerDB.Create( (MasterModel)
SlaveModel,'slave.db3');
WebSockets
TCP/IP

SlaveServer.RecordVersionSynchronizeSlaveStart( Slave

TSQLRecordPeopleVersioned,MasterClient); master.db3
MasterClient
(MasterModel)

Replication

SlaveServer
(SlaveModel)

slave.db3

From SQL to ORM

June 2015
 mORMot’s RESTful ORM
 Persistence over External RDMS
 SynDB optimized data access layer

SynDB

DB.pas
ZDBC ODBC OleDB Oracle SQLite3
TDataset

FireDAC
NexusDB BDE DBExpress UniDAC
AnyDAC

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 SynDB classes
 By-pass the DB.pas unit
avoid TDataSet bottleneck, work with Starter Edition
… but can still use it (e.g. FireDAC)
 Simple KISS designed API
less data types, interface-based
 Statement cache
 Array binding (bulk insert / batch mode)

 Direct JSON generation

 Optional remote access via HTTP

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 SynDB integration with ORM
TSQLRestServerDB.Add

external
internal
table
table
REST

TSQLRestServerDB.EngineAdd TSQLRestServerStaticExternal.EngineAdd

INSERT INTO... INSERT INTO...

TSQLRequest ISQLDBStatement

internal engine ODBC/ZDBC/OleDB...

SQlite3 engine External DB client

SQLite3 file External DB server

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 SQLite3 Virtual Tables

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 SQLite3 Virtual Tables
 Mapping of external field names
 JOIN several external databases

 Very slight performance penalty

 Access TObjectList instances

 Access NoSQL databases

 By-passed if possible

 Full SQL-92 engine at hand

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 SQLite3 Virtual Tables
mORMot
ORM

direct

direct SQLite3 direct

virtual

TObjectList External DB

DB.pas
direct
TDataSet

Oracle SQLite3 ODBC OleDB ZDBC FireDAC AnyDAC UniDAC BDE DBExpress NexusDB

From SQL to ORM

June 2015
mORMot’s RESTful ORM

From SQL to ORM

June 2015
mORMot’s RESTful ORM

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Defining the object
type
TSQLRecordPeopleExt = class(TSQLRecord) ID : integer ID : INTEGER
private
fData: TSQLRawBlob; Data : TSQLRawBlob Data : BLOB
fFirstName: RawUTF8; FirstName : RawUTF8 FirstName : NVARCHAR(40)
fLastName: RawUTF8; LastName : RawUTF8 LastName : NVARCHAR(40)
fYearOfBirth: integer;
YearOfBirth : integer YearOfBirth : INTEGER
fYearOfDeath: word;
fLastChange: TModTime; YearOfDeath : word YearOfDeath : INTEGER
fCreatedAt: TCreateTime;
published
property FirstName: RawUTF8 index 40 read fFirstName write fFirstName;
property LastName: RawUTF8 index 40 read fLastName write fLastName;
property Data: TSQLRawBlob read fData write fData;
property YearOfBirth: integer read fYearOfBirth write fYearOfBirth;
property YearOfDeath: word read fYearOfDeath write fYearOfDeath;
property LastChange: TModTime read fLastChange write fLastChange;
property CreatedAt: TCreateTime read fCreatedAt write fCreatedAt;
end;

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Defining the Model and the Server:
Props := TOleDBMSSQLConnectionProperties.Create(
'.\SQLEXPRESS','AdventureWorks2008R2','','');
Model := TSQLModel.Create([TSQLRecordPeopleExt],'root');
VirtualTableExternalRegister(Model,TSQLRecordPeopleExt,Props,'Test.People');
ServerDB := TSQLRestServerDB.Create(Model,'application.db'),true);
ServerDB.CreateMissingTables;
Server := TSQLHttpServer.Create('8080',ServerDB);

 Defining the Model and the Client:

Model := TSQLModel.Create([TSQLRecordPeopleExt],'root');
Client := TSQLHttpClient.Create(Server,'8080', Model);

 Both will now communicate e.g. over

http://server:8080/root/PeopleExt

From SQL to ORM

June 2015
mORMot’s RESTful ORM
 Refining the mapping Model on the Server:
Props := TOleDBMSSQLConnectionProperties.Create(
'.\SQLEXPRESS','AdventureWorks2008R2','','');
Model := TSQLModel.Create([TSQLRecordPeopleExt],'root');
VirtualTableExternalRegister(Model,TSQLRecordPeopleExt,Props,'Test.People');
ID : integer Key : INTEGER
Model.Props[TSQLRecordPeopleExt].ExternalDB. Data : TSQLRawBlob Data : BLOB
MapField('ID','Key'). FirstName : RawUTF8 FirstName : NVARCHAR(40)
MapField('YearOfDeath','YOD'); LastName : RawUTF8 LastName : NVARCHAR(40)
YearOfBirth : integer YearOfBirth : INTEGER
YearOfDeath : word YOD : INTEGER

ServerDB := TSQLRestServerDB.Create(Model,'application.db'),true);
ServerDB.CreateMissingTables;
Server := TSQLHttpServer.Create('8080',ServerDB);

From SQL to ORM

June 2015
mORMot’s RESTful ORM/ODM
 Data Sharding / Denormalization pattern
 Storethe whole aggregate as once
 Without JOIN

 From ORM to ODM

 Object Relational Mapping (ORM)
 Object Document Mapping (ODM)

From SQL to ORM

June 2015
mORMot’s RESTful ODM
 Object Document Mapping
 ODM over regular RDBMS
 NoSQL storage via TDocVariant property
 Stored as JSON, handled as a variant

 ODM over dedicated NoSQL engine

 TObjectList
 MongoDB direct access

From SQL to ORM

June 2015
mORMot’s RESTful ODM
 NoSQL storage via TDocVariant property
TSQLRecordData = class(TSQLRecord)
private
fName: RawUTF8;
fData: variant;
public
published
property Name: RawUTF8 read fTest write fTest stored AS_UNIQUE;
property Data: variant read fData write fData;
end;

 Document is indexed:
 by TSQLRecord.ID: integer
 by Name: RawUTF8

From SQL to ORM

June 2015
mORMot’s RESTful ODM
 NoSQL storage via TDocVariant custom type
TSQLRecordData = class(TSQLRecord)
private
fName: RawUTF8;
fData: variant;
public
published
property Name: RawUTF8 read fTest write fTest stored AS_UNIQUE;
property Data: variant read fData write fData;
end;

 Data: variant will store any document

 As JSON in the RDBMS
 Accessed via late-binding in Delphi code

From SQL to ORM

June 2015
mORMot’s RESTful ODM
 Data: variant will store a TDocVariant

 Schema-less data
{ name : "Joe", x : 3.3, y : [1,2,3] }
{ name : "Kate", x : "abc" }
{ q : 456 }

 Real-world evolving data

{ name : "Joe", age : 30, interests : "football" }
{ name : "Kate", age : 25 }

From SQL to ORM

June 2015
mORMot’s RESTful ODM
var aRec: TSQLRecordData;
aID: integer;
begin
// initialization of one record
aRec := TSQLRecordData.Create;
aRec.Name := 'Joe'; // one unique key
aRec.data := _JSONFast('{name:"Joe",age:30}'); // create a TDocVariant
// or we can use this overloaded constructor for simple fields
aRec := TSQLRecordData.Create(['Joe',_ObjFast(['name','Joe','age',30])]);
// now we can play with the data, e.g. via late-binding:
writeln(aRec.Name); // will write 'Joe'
writeln(aRec.Data); // will write '{"name":"Joe","age":30}' (auto-converted to JSON string)
aRec.Data.age := aRec.Data.age+1; // one year older
aRec.Data.interests := 'football'; // add a property to the schema
aID := aClient.Add(aRec); // will store {"name":"Joe","age":31,"interests":"footbal"}
aRec.Free;
// now we can retrieve the data either via the aID created integer, or via Name='Joe'
end;

From SQL to ORM

June 2015
mORMot’s RESTful ODM
 NoSQL Engines
 TObjectList - TSQLRestStorageInMemory
 Fast in-memory storage
 Supports hash indexes on (unique) properties
 Binary or JSON persistence as file
 Could embed some unpersisted live process

 MongoDB
 SynMongoDB.pasfor direct access
 mORMotMongoDB.pas for ODM integration

From SQL to ORM

June 2015
mORMot’s RESTful ODM

 SynMongoDB.pas

 BSON types, including BLOB, ObjectID, date/time…

 TBSONVariant with late-binding
 (Extended) JSON as input or output
 Document-level access
 Bulk document insertion
 Could be used standalone, without the ODM

From SQL to ORM

June 2015
mORMot’s RESTful ODM

 mORMotMongoDB.pas

 Full
integration with mORMot’s RESTful ORM
 Share the same logic code with RDBMS
 Just one line to change on the server side
 Same exact RESTful business code
 On-the-fly query translation
 SQL WHERE clause translated to MongoDB query object
 Including aggregate functions, limits, offset
 BATCH support, low memory use, data marshalling

From SQL to ORM

June 2015
mORMot’s RESTful ODM

uses SynMongoDB, mORMotMongoDB;

Model := TSQLModel.Create([TSQLORM]);
Server := TSQLRestServerDB.Create(Model,nil,':memory:');
MongoClient := TMongoClient.Create('localhost',27017);
MongoDatabase := MongoClient.Database[DB_NAME];
StaticMongoDBRegister(TSQLORM,Server,MongoDatabase);
Server.CreateMissingTables;

… usual code

From SQL to ORM

June 2015
mORMot’s RESTful ORM/ODM
 BATCH process // start the BATCH sequence
Check(ClientDist.BatchStart(TSQLRecordPeople,5000));
ORM CRUD operation // delete some elements
for i := 0 to n-1 do
Check(ClientDist.BatchDelete(IntArray[i])=i);
In-process // update some elements
no latency nupd := 0;
for i := 0 to aStatic.Count-1 do
if i and 7<>0 then
ORM HTTP Client begin // not yet deleted in BATCH mode
Check(ClientDist.Retrieve(aStatic.ID[i],V));
V.YearOfBirth := 1800+nupd;
Internet Check(ClientDist.BatchUpdate(V)=nupd+n);
100 ms latency inc(nupd);
end;
// add some elements
ORM HTTP Server V.LastName := 'New';
for i := 0 to 1000 do
begin
In-process V.FirstName := RandomUTF8(10);
V.YearOfBirth := i+1000;
no latency
Check(ClientDist.BatchAdd(V,true)=n+nupd+i);
end;
// send the BATCH sequences to the server
ORM database core Check(ClientDist.BatchSend(Results)=200);

From SQL to ORM

June 2015
mORMot’s RESTful ORM/ODM
 BATCH process
 Truly ACID, even with several RDBMS backends
 Automatic transaction handling
 Avoids slow Client-Server roundtrips
 Unit-Of-Work pattern, even on Server side
 Optimized SQL
 Parameter Array Binding
 e.g. Oracle, ZDBC, FireDAC
 Multiple INSERT statement
 Depending on the database dialect
 Re-use of prepared statements
 NoSQL (MongoDB) bulk insertion

From SQL to ORM

June 2015
mORMot’s RESTful ORM/ODM
 Security
 Rooted on framework’s authentication(s)
 Secured by SHA-256 challenge to avoid MIM / replay
 Users belong to groups
 Per-table CRUD access right for each group

 Optional JSON compression and encryption

 Over standard HTTP / HTTPS wire

From SQL to ORM

June 2015
mORMot’s RESTful ORM/ODM
 RESTful Client-Server
 In-process
 Stand-alone client, fast server-side access
 Named pipes or GDI messages
 Stand-alone client, fast server-side access
 HTTP/1.1 via kernel-mode http.sys API
 Part of the OS since Windows XP SP2, used by IIS and WCF
 Kernel-mode execution, IOCP driven
 System-wide URI registration: share root and port
 Socket-based server is also available, e.g. under Linux
 Optional query/answer REST emulation over WebSockets

From SQL to ORM

June 2015
mORMot’s RESTful ORM/ODM
 Cross-platform Clients
 Generated client code using Mustache templates
 Delphi:Windows, MacOS, Android, iOS
 FPC: Windows, MacOS, Android, iOS, Linux, …
 (Cross)Kylix: Linux
 SmartMobileStudio: Ajax / HTML5

 Featuring almost all framework abilities

 JSON, security, TSQLRest, TSQLRestBatch

From SQL to ORM

June 2015
From SQL to ORM
June 2015