MongoDB + ORM = ODM
The mORMotMongoDB.pas unit is able to let any
TSQLRecord class be persisted on a remote MongoDB
server.
As a result, our ORM is able to be used as a NoSQL and Object-Document
Mapping (ODM) framework, with almost no code change.
Any MongoDB database can be accessed via RESTful commands, using JSON
over HTTP.
This integration benefits from the other parts of the framework (e.g. our UTF-8 dedicated process, which is also the native encoding for BSON), so you can easily mix SQL and NoSQL databases with the exact same code, and are still able to tune any SQL or MongoDB request in your code, if necessary.
Register the TSQLRecord class
In the database model, we define a TSQLRecord class, as
usual:
TSQLORM = class(TSQLRecord)
private
fAge: integer;
fName: RawUTF8;
fDate: TDateTime;
fValue: variant;
fInts: TIntegerDynArray;
fCreateTime: TCreateTime;
fData: TSQLRawBlob;
published
property Name: RawUTF8 read fName write fName stored AS_UNIQUE;
property Age: integer read fAge write fAge;
property Date: TDateTime read fDate write fDate;
property Value: variant read fValue write fValue;
property Ints: TIntegerDynArray index 1 read fInts write fInts;
property Data: TSQLRawBlob read fData write fData;
property CreateTime: TCreateTime read fCreateTime write fCreateTime;
end;
Note that we did not define any index
... values for the RawUTF8 property, as we need for
external SQL databases, since MongoDB does not expect any restriction
about text fields length.
The property values will be stored in the native MongoDB layout, i.e. with a better coverage than the SQL types:
| Delphi | MongoDB | Remarks |
byte |
int32 | |
word |
int32 | |
integer |
int32 | |
cardinal |
N/A | You should use Int64 instead |
Int64 |
int64 | |
boolean |
boolean | 0 is false, anything else is true |
| enumeration | int32 | store the ordinal value of the enumerated item(i.e. starting at 0 for the first element) |
| set | int32 | each bit corresponding to an enumerated item (therefore a set of up to 64 elements can be stored in such a field) |
single |
double | |
double |
double | |
extended |
double | stored as double (precision lost) |
currency |
double | safely converted to/from currency type with fixed decimals,
without rounding error |
RawUTF8 |
UTF-8 | this is the preferred field type for storing some textual content in the ORM |
WinAnsiString |
UTF-8 | WinAnsi char-set (code page 1252) in Delphi |
RawUnicode |
UTF-8 | UCS2 char-set in Delphi, as AnsiString |
WideString |
UTF-8 | UCS2 char-set, as COM BSTR type (Unicode in all version of Delphi) |
SynUnicode |
UTF-8 | Will be either WideString before Delphi 2009, or
UnicodeString later |
string |
UTF-8 | Not to be used before Delphi 2009 (unless you may loose some data during
conversion) - RawUTF8 is preferred in all cases |
TDateTime |
datetime | ISO 8601 encoded date time |
TTimeLog |
int64 | as proprietary fast Int64 date time |
TModTime |
int64 | the server date time will be stored when a record is modified (as
proprietary fast Int64) |
TCreateTime |
int64 | the server date time will be stored when a record is created (as
proprietary fast Int64) |
TSQLRecord |
int32 | RowID pointing to another record (warning: the field value
contains pointer(RowID), not a valid object instance - the record
content must be retrieved with late-binding via its ID using a
PtrInt(Field) typecast or the Field.ID method), or by
using e.g. CreateJoined() |
TSQLRecordMany |
nothing | data is stored in a separate pivot table; for MongoDB, you should better use data sharding, and an embedded sub-document |
TRecordReference |
int32 | store both ID and TSQLRecord type in a
RecordRef-like value (use e.g. TSQLRest.
Retrieve(Reference) to get a record content) |
TPersistent |
object | BSON object (from ObjectToJSON) |
TCollection |
array | BSON array of objects (from ObjectToJSON) |
TObjectList |
array | BSON array of objects (from ObjectToJSON) - see
TJSONSerializer. RegisterClassForJSON TObjectList
serialization |
TStrings |
array | BSON array of strings (from ObjectToJSON) |
TRawUTF8List |
array | BSON array of string (from ObjectToJSON) |
any TObject |
object | See TJSONSerializer. RegisterCustomSerializer TObject
serialization |
TSQLRawBlob |
binary | BSON binary for blob storage - note that you need to retrieve explicitly
such fields, which are not part of the default Retrieve() method;
this type is an alias to RawByteString |
| dynamic arrays | array binary |
if the dynamic array can be saved as true JSON, will be stored as BSON
array - otherwise, will be stored in the TDynArray.SaveTo binary
format - note that TByteDynArray will always be stored as BSON
binary, so that TSQLRawBlob may be reserved to store content on
GridFS in the future |
variant |
array object |
BSON number, text, object or array, depending on TDocVariant custom
variant type or TBSONVariant stored value |
record |
binary object |
BSON as defined in code by overriding
TSQLRecord.InternalRegisterCustomProperties |
On the server side (there won't be any difference for the client), you
define a TMongoDBClient, and assign it to a given
TSQLRecord class:
MongoClient := TMongoClient.Create('localhost',27017);
DB := MongoClient.Database['dbname'];
Model := TSQLModel.Create([TSQLORM]);
Client := TSQLRestClientDB.Create(Model,nil,':memory:',TSQLRestServerDB);
if StaticMongoDBRegister(TSQLORM,fClient.Server,fDB,'collectionname')=nil then
raise Exception.Create('Error');
And... that's all!
You can then use any ORM command, as usual:
writeln(Client.TableRowCount(TSQLORM)=0);
As with external databases, you can specify the field names mapping between
the objects and the MongoDB collection.
By default, the TSQLRecord.ID property is mapped to the
MongoDB's _id field, and the ORM will populate this
_id field with a sequence of integer values, just like any
TSQLRecord table.
You can specify your own mapping, using for instance:
aModel.Props[aClass].ExternalDB.MapField(..)
Since the field names are stored within the document itself, it may be a good idea to use shorter naming for the MongoDB collection. It may save some storage space, when working with a huge number of documents.
Once the TSQLRecord is mapped to a MongoDB collection,
you can always have direct access to the TMongoCollection instance
later on, by calling:
(aServer.StaticDataServer[aClass] as TSQLRestServerStaticMongoDB).Collection
This may allow any specific task, including any tuned query or process.
ORM/ODM CRUD methods
You can add documents with the standard CRUD methods of the ORM, as usual:
R := TSQLORM.Create;
try
for i := 1 to COLL_COUNT do begin
R.Name := 'Name '+Int32ToUTF8(i);
R.Age := i;
R.Date := 1.0*(30000+i);
R.Value := _ObjFast(['num',i]);
R.Ints := nil;
R.DynArray(1).Add(i);
assert(Client.Add(R,True)=i);
end;
finally
R.Free;
end;
As we already saw, the framework is able to handle any kind of properties,
including complex types like dynamic arrays or
variant.
In the above code, a TDocVariant document has been stored in
R.Value, and a dynamic array of integer values is
accessed via its index 1 shortcut and the
TSQLRecord.DynArray() method.
The usual Retrieve / Delete / Update
methods are available:
R := TSQLORM.Create;
try
for i := 1 to COLL_COUNT do begin
Check(Client.Retrieve(i,R));
// here R instance contains all values of one document, excluding BLOBs
end;
finally
R.Free;
end;
You can define a WHERE clause, as if the back-end where a regular SQL database:
R := TSQLORM.CreateAndFillPrepare(Client,'ID=?',[i]);
try
...
Current implementation understand one condition over one single field, with
= > >= < <= IN clauses. More advanced queries are
possible, but won't be handled as SQL, but via direct access to the
TMongoDBCollection.
To perform a query and retrieve the content of several documents, you can
use regular CreateAndFillPrepare or FillPrepare
methods:
R := TSQLORM.CreateAndFillPrepare(Client,''); try n := 0; while R.FillOne do begin // here R instance contains all values of one document, excluding BLOBs inc(n); end; assert(n=COLL_COUNT); finally R.Free; end;
A WHERE clause can also be defined for CreateAndFillPrepare or
FillPrepare methods.
BATCH mode
In addition to individual CRUD operations, our MongoDB is able to use BATCH mode for adding or deleting documents.
You can write the exact same code as with any SQL back-end:
Client.BatchStart(TSQLORM);
R := TSQLORM.Create;
try
for i := 1 to COLL_COUNT do begin
R.Name := 'Name '+Int32ToUTF8(i);
R.Age := i;
R.Date := 1.0*(30000+i);
R.Value := _ObjFast(['num',i]);
R.Ints := nil;
R.DynArray(1).Add(i);
assert(Client.BatchAdd(R,True)>=0);
end;
finally
R.Free;
end;
assert(Client.BatchSend(IDs)=HTML_SUCCESS);
Or for deletion:
Client.BatchStart(TSQLORM);
for i := 5 to COLL_COUNT do
if i mod 5=0 then
assert(fClient.BatchDelete(i)>=0);
assert(Client.BatchSend(IDs)=HTML_SUCCESS);
Speed benefit may be huge in regard to individual Add/Delete operations, even on a local MongoDB server. We will see some benchmark numbers now.
ORM/ODM performance
You can take a look at Data access benchmark to compare MongoDB as back-end for our ORM classes.
In respect to external SQL engines, it features very high speed, low CPU
use, and almost no difference in use. We interfaced the BatchAdd()
and BatchDelete() methods to benefit of MongoDB BULK
process, and avoided most memory allocation during the process.
Here are some numbers, extracted from the MongoDBTests.dpr
sample, which reflects the performance of our ORM/ODM, depending on the
Write Concern mode used:
2. ORM
2.1. ORM with acknowledge: - Connect to local server: 6 assertions passed 18.65ms - Insert: 5,002 assertions passed 521.25ms 5000 rows inserted in 520.65ms i.e. 9603/s, aver. 104us, 2.9 MB/s - Insert in batch mode: 5,004 assertions passed 65.37ms 5000 rows inserted in 65.07ms i.e. 76836/s, aver. 13us, 8.4 MB/s - Retrieve: 45,001 assertions passed 640.95ms 5000 rows retrieved in 640.75ms i.e. 7803/s, aver. 128us, 2.1 MB/s - Retrieve all: 40,001 assertions passed 20.79ms 5000 rows retrieved in 20.33ms i.e. 245941/s, aver. 4us, 27.1 MB/s - Retrieve one with where clause: 45,410 assertions passed 673.01ms 5000 rows retrieved in 667.17ms i.e. 7494/s, aver. 133us, 2.0 MB/s - Update: 40,002 assertions passed 681.31ms 5000 rows updated in 660.85ms i.e. 7565/s, aver. 132us, 2.4 MB/s - Blobs: 125,003 assertions passed 2.16s 5000 rows updated in 525.97ms i.e. 9506/s, aver. 105us, 2.4 MB/s - Delete: 38,003 assertions passed 175.86ms 1000 rows deleted in 91.37ms i.e. 10944/s, aver. 91us, 2.3 MB/s - Delete in batch mode: 33,003 assertions passed 34.71ms 1000 rows deleted in 14.90ms i.e. 67078/s, aver. 14us, 597 KB/s Total failed: 0 / 376,435 - ORM with acknowledge PASSED 5.00s
2.2. ORM without acknowledge: - Connect to local server: 6 assertions passed 16.83ms - Insert: 5,002 assertions passed 179.79ms 5000 rows inserted in 179.15ms i.e. 27908/s, aver. 35us, 3.9 MB/s - Insert in batch mode: 5,004 assertions passed 66.30ms 5000 rows inserted in 31.46ms i.e. 158891/s, aver. 6us, 17.5 MB/s - Retrieve: 45,001 assertions passed 642.05ms 5000 rows retrieved in 641.85ms i.e. 7789/s, aver. 128us, 2.1 MB/s - Retrieve all: 40,001 assertions passed 20.68ms 5000 rows retrieved in 20.26ms i.e. 246718/s, aver. 4us, 27.2 MB/s - Retrieve one with where clause: 45,410 assertions passed 680.99ms 5000 rows retrieved in 675.24ms i.e. 7404/s, aver. 135us, 2.0 MB/s - Update: 40,002 assertions passed 231.75ms 5000 rows updated in 193.74ms i.e. 25807/s, aver. 38us, 3.6 MB/s - Blobs: 125,003 assertions passed 1.44s 5000 rows updated in 150.58ms i.e. 33202/s, aver. 30us, 2.6 MB/s - Delete: 38,003 assertions passed 103.57ms 1000 rows deleted in 19.73ms i.e. 50668/s, aver. 19us, 2.4 MB/s - Delete in batch mode: 33,003 assertions passed 47.50ms 1000 rows deleted in 364us i.e. 2747252/s, aver. 0us, 23.4 MB/s Total failed: 0 / 376,435 - ORM without acknowledge PASSED 3.44s
As for direct MongoDB access, the wcUnacknowledged is
not to be used on production, but may be very useful in some particular
scenarios.
As expected, the reading process is not impacted by the Write Concern
mode set.
You can take a look at the previous blog article, about low-level MongoDB direct
access.
Or check out more complete
benchmark results, to compare MongoDB with other SQL back-end
handled by our ORM.
Feedback is welcome
on our forum, as usual!
