Synopse

To content | To menu | To search

Tag - SynDB

Entries feed

2014, Friday April 18

Introducing mORMot's architecture and design principles

We have just released a set of slides introducing 

  • ORM, SOA, REST, JSON, MVC, MVVM, SOLID, Mocks/Stubs, Domain-Driven Design concepts with Delphi, 
  • and showing some sample code using our Open Source mORMot framework.

You can follow the public link on Google Drive!

This is a great opportunity to discovers some patterns you may not be familiar with, and find out how mORMot try to implement them.
This set of slides may be less intimidating than our huge documentation - do not be terrified by our 1400 pages Software Architecture Design pdf!

Feedback is welcome on our forum, as usual.

2014, Thursday March 13

ORM mapping class fields to external table columns

When working with an ORM, you have mainly two possibilites:

  1. Start from scratch, i.e. write your classes and let the ORM creates all the database structure - it is also named "code-first";
  2. From an existing database, you define in your model how your classes map the existing database structure - this is "database-first".

We have just finalized ORM external table field mapping in mORMot, using e.g.
aModel.Props[aExternalClass].ExternalDB.MapField(..)
See this last commit.

So you can write e.g.

fProperties := TSQLDBSQLite3ConnectionProperties.Create(
  SQLITE_MEMORY_DATABASE_NAME,'','','');
VirtualTableExternalRegister(fExternalModel,
  TSQLRecordPeopleExt,fProperties,'PeopleExternal');
fExternalModel.Props[TSQLRecordPeopleExt].ExternalDB.
  MapField('ID','Key').
  MapField('YearOfDeath','YOD');

Then you use your TSQLRecordPeopleExt table as usual from Delphi code, with ID and YearOfDeath fields:

  • The "internal" TSQLRecord class will be stored within the PeopleExternal external table;
  • The "internal" TSQLRecord.ID field will be an external "Key: INTEGER" column;
  • The "internal" TSQLRecord.YearOfDeath field will be an external "YOD: BIGINT" column;
  • Other internal published properties will be mapped by default with the same name to external column.

Continue reading...

2014, Friday March 7

Support of MySQL, DB2 and PostgreSQL

We just tested, benchmarked and validated Oracle MySQL, IBM DB2 and PostgreSQL support for our SynDB database classes and the mORMot's ORM core.
This article will also show all updated results, including our newly introduced multi-value INSERT statement generations, which speed up a lot BATCH insertion.

Stay tuned!

Purpose here is not to say that one library or database is better or faster than another, but publish a snapshot of mORMot persistence layer abilities, depending on each access library.

In this timing, we do not benchmark only the "pure" SQL/DB layer access (SynDB units), but the whole Client-Server ORM of our framework.

Process below includes all aspects of our ORM:

  • Access via high level CRUD methods (Add/Update/Delete/Retrieve, either per-object or in BATCH mode);
  • Read and write access of TSQLRecord instances, via optimized RTTI;
  • JSON marshaling of all values (ready to be transmitted over a network);
  • REST routing, with security, logging and statistic;
  • Virtual cross-database layer using its SQLite3 kernel;
  • SQL on-the-fly generation and translation (in virtual mode);
  • Access to the database engines via several libraries or providers.

In those tests, we just bypassed the communication layer, since TSQLRestClient and TSQLRestServer are run in-process, in the same thread - as a TSQLRestServerDB instance. So you have here some raw performance testimony of our framework's ORM and RESTful core, and may expect good scaling abilities when running on high-end hardware, over a network.

On a recent notebook computer (Core i7 and SSD drive), depending on the back-end database interfaced, mORMot excels in speed, as will show the following benchmark:

  • You can persist up to 570,000 objects per second, or retrieve 870,000 objects per second (for our pure Delphi in-memory engine);
  • When data is retrieved from server or client 38, you can read more than 900,000 objects per second, whatever the database back-end is;
  • With a high-performance database like Oracle, and our direct access classes, you can write 70,000 (via array binding) and read 160,000 objects per second, over a 100 MB network;
  • When using alternate database access libraries (e.g. Zeos, or DB.pas based classes), speed is lower (even if comparable for DB2, MS SQL, PostgreSQL, MySQL) but still enough for most work, due to some optimizations in the mORMot code (e.g. caching of prepared statements, SQL multi-values insertion, direct export to/from JSON, SQlite3 virtual mode design, avoid most temporary memory allocation...).

Difficult to find a faster ORM, I suspect.

Continue reading...

2014, Monday March 3

ORM enhanced for BATCH insert

We just committed some nice features to the ORM kernel, and SynDB* classes of our mORMot framework.

During BATCH insertion, the ORM is able to generate some optimized SQL statements, depending on the target database, to send several rows of data at once.
It induces a noticeable speed increase when saving several objects into an external database.

This feature is available for SQlite3 (3.7.11 and later), MySQL, PostgreSQL, MS SQL Server (2008 and up), Oracle, Firebird and NexusDB.
Since it is working at SQL level, it is available for all supported access libraries, e.g. ODBC, OleDB, Zeos/ZDBC, UniDAC, FireDAC.
It means that even properties not implementing array binding (like OleDB, Zeos or UniDAC) are able to have a huge boost at data insertion, ready to compete with the (until now) more optimized libraries.

Continue reading...

2013, Monday November 4

Updated mORMot database benchmark - including MS SQL and PostgreSQL

On an recent notebook computer (Core i7 and SSD drive), depending on the back-end database interfaced, mORMot excels in speed:

  • You can persist up to 570,000 objects per second, or retrieve more than 900,000 objects per second (for our pure Delphi in-memory engine);
  • When data is retrieved from server or client cache, you can read more than 900,000 objects per second, whatever the database back-end is;
  • With a high-performance database like Oracle and our direct access classes, you can write 65,000 (via array binding) and read 160,000 objects per second, over a 100 MB network;
  • When using alternate database access libraries (e.g. Zeos, or DB.pas based classes), speed is lower, but still enough for most work.

Difficult to find a faster ORM, I suspect.

The following tables try to sum up all available possibilities, and give some benchmark (average objects/second for writing or read).

In these tables:

  • 'SQLite3 (file full/off/exc)' indicates use of the internal SQLite3 engine, with or without Synchronous := smOff and/or DB.LockingMode := lmExclusive;
  • 'SQLite3 (mem)' stands for the internal SQLite3 engine running in memory;
  • 'SQLite3 (ext ...)' is about access to a SQLite3 engine as external database - either as file or memory;
  • 'TObjectList' indicates a TSQLRestServerStaticInMemory instance, either static (with no SQL support) or virtual (i.e. SQL featured via SQLite3 virtual table mechanism) which may persist the data on disk as JSON or compressed binary;
  • 'Oracle' shows the results of our direct OCI access layer (SynDBOracle.pas);
  • 'NexusDB' is the free embedded edition, available from official site;
  • 'Zeos *' indicates that the database was accessed directly via the ZDBC layer;
  • 'FireDAC *' stands for FireDAC library;
  • 'UniDAC *' stands for UniDAC library;
  • 'BDE *' when using a BDE connection;
  • 'ODBC *' for a direct access to ODBC;
  • 'Jet' stands for a MSAccess database engine, accessed via OleDB;
  • 'MSSQL local' for a local connection to a MS SQL Express 2008 R2 running instance (this was the version installed with Visual Studio 2010), accessed via OleDB.

This list of database providers is to be extended in the future. Any feedback is welcome!

Numbers are expressed in rows/second (or objects/second). This benchmark was compiled with Delphi 7, so newer compilers may give even better results, with in-lining and advanced optimizations.

Note that these tests are not about the relative speed of each database engine, but reflect the current status of the integration of several DB libraries within the mORMot database access.

Purpose here is not to say that one library or database is better or faster than another, but publish a snapshot of current mORMot persistence layer abilities.

In this timing, we do not benchmark only the "pure" SQL/DB layer access (SynDB units), but the whole Client-Server ORM of our framework: process below includes read and write RTTI access of a TSQLRecord, JSON marshaling, CRUD/REST routing, virtual cross-database layer, SQL on-the-fly translation. We just bypass the communication layer, since TSQLRestClient and TSQLRestServer are run in-process, in the same thread - as a TSQLRestServerDB instance. So you have here some raw performance testimony of our framework's ORM and RESTful core.

You can compile the "15 - External DB performance" supplied sample code, and run the very same benchmark on your own configuration.

Continue reading...

2013, Sunday June 16

FireDAC / AnyDAC support for mORMot

Our SynDB classes feature now FireDAC / AnyDAC access, with full speed!

Up to now, only UniDAC, BDE or ZEOS components were available as source, but we just added FireDAC / AnyDAC.

FireDAC is an unique set of Universal Data Access Components for developing cross platform database applications on Delphi. This was in fact a third-party component set, bought by Embarcadero to DA-SOFT Technologies (formerly known as AnyDAC), and included with several editions of Delphi XE3 and up. This is the new official platform for high-speed database development in Delphi, in favor to the now deprecated DBExpress.

Our integration within SynDB.pas units and the mORMot persistence layer has been tuned. For instance, you can have direct access to high-speed FireDAC Array DML feature, via the ORM batch process, via so-called array binding.

Continue reading...

2013, Thursday March 7

64 bit compatibility of mORMot units

I'm happy to announce that mORMot units are now compiling and working great in 64 bit mode, under Windows.
Need a Delphi XE2/XE3 compiler, of course!

ORM and services are now available in Win64, on both client and server sides.
Low-level x64 assembler stubs have been created, tested and optimized.
UI part is also available... that is grid display, reporting (with pdf export and display anti-aliasing), ribbon auto-generation, SynTaskDialog, i18n... the main SynFile demo just works great!

Overall impression is very positive, and speed is comparable to 32 bit version (only 10-15% slower).

Speed decrease seems to be mostly due to doubled pointer size, and some less optimized part of the official Delphi RTL.
But since mORMot core uses its own set of functions (e.g. for JSON serialization, RTTI support or interface calls or stubbing), we were able to release the whole 64 bit power of your hardware.

Delphi 64 bit compiler sounds stable and efficient. Even when working at low level, with assembler stubs.
Generated code sounds more optimized than the one emitted by FreePascalCompiler - and RTL is very close to 32 bit mode.
Overall, VCL conversion worked as easily than a simple re-build.
Embarcadero's people did a great job for VCL Win64 support, here!

Continue reading...

2013, Sunday February 17

Interface-based service sample: remote SQL access

You will find in the SQLite3\Sample\16 - Execute SQL via services folder of mORMot source code a Client-Server sample able to access any external database via JSON and HTTP.
It is a good demonstration of how to use an interface-based service between a client and a server.
It will also show how our SynDB classes have a quite abstract design, and are easy to work with, whatever database provider you need to use.

The corresponding service contract has been defined:

  TRemoteSQLEngine = (rseOleDB, rseODBC, rseOracle, rseSQlite3, rseJet, rseMSSQL);

IRemoteSQL = interface(IInvokable) ['{9A60C8ED-CEB2-4E09-87D4-4A16F496E5FE}'] procedure Connect(aEngine: TRemoteSQLEngine; const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); function GetTableNames: TRawUTF8DynArray; function Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON; end;

Purpose of this service is:
- To Connect() to external databases, given the parameters of a standard TSQLDBConnectionProperties. Create() constructor;
- Retrieve all table names of this external database as a list;
- Execute any SQL statement, returning the content as JSON array, ready to be consumed by AJAX applications (if aExpanded is true), or a Delphi client (e.g. via a TSQLTableJSON and the mORMotUI unit).

Of course, this service will be define as sicClientDriven mode, that is, the framework will be able to manage a client-driven TSQLDBProperties instance life time.

Benefit of this service is that no database connection is required on the client side: a regular HTTP connection is enough.
No need to install nor configure any database provider, and full SQL access to the remote databases.

Due to our optimized JSON serialization, it will probably be faster to work with such plain HTTP / JSON services, instead of a database connection through a VPN. In fact, database connections are made to work on a local network, and do not like high-latency connections, which are typical on the Internet.

Continue reading...

2013, Tuesday February 12

Introducing ZEOS, UniDAC, NexusDB, BDE, any TDataset to SynDB and mORMot's ORM

Up to now, our SynDB database classes were handling ODBC, OleDB providers and direct Oracle or SQLite3 connection.

We have added a DB.pas based layer, ready to be used with UniDAC, NexusDB, or the BDE.
Any other TDataset based component is ready to be interfaced, including UIB, AnyDAC or DBExpress.

The ZEOS library (in its latest 7.0.3 stable version, which works from Delphi 7 up to XE3) has also been interfaced, but without the TDataset/DB.pas layer: our SynDBZEOS.pas unit calls the ZDBC layer, which is not tied to DB.pas nor its RAD components, and is therefore faster. By the way, it will work also with the Starter edition of Delphi (which does not include the DB components) - just like the other "regular" SynDB classes.

This is a work in progress, any testing and feedback is welcome!
We had to circumvent some particularities of the libraries, but I guess we have something interesting.

A dedicated "SynDBDataset" sub-folder has been created in the repository, to contain all SynDBDataset.pas-based database providers.
SynDBNexusDB.pas unit has been moved within this sub-folder, as SynDBUniDAC.pas + SynDBBDE.pas units have been added.
SynDBZeos.pas has a direct access to the ZDBC layer, so is not part of the "SynDBDataset" sub-folder.

Here is some benchmark, mainly about Oracle and SQlite3 database access.
Of course, our direct SynDBOracle / SynDBSQLite3 layers are the fastest around, and we can see that ZDBC layer is sometimes more efficient than the TDataset components.

Continue reading...

2013, Monday January 28

External database speed improvements

Some major speed improvements have been made to our SynDB* units, and how they are used within the mORMot persistence layer.
It results in an amazing speed increase, in some cases.

Here are some of the optimizations how took place in the source code trunk:

Overall, I observed from x2 to x10 performance boost with simple Add() operations, using ODBC, OleDB and direct Oracle access, when compare to previous benchmarks (which were already impressive).
BATCH mode performance is less impacted, since it by-passed some of those limitations, but even in this operation mode, there is some benefits (especially with ODBC and OleDB).

Here are some results, directly generated by the supplied "15 - External DB performance" sample.

Continue reading...

2012, Monday December 31

Enhance existing projects with mORMot

Even if mORMot will be more easily used in a project designed from scratch, it fits very well the purpose of evolving any existing Delphi project, or even creating the server side part of an AJAX application. 

One benefit of such a framework is to facilitate the transition from a Client-Server architecture to a N-Tier layered pattern.

Continue reading...

2012, Sunday October 28

SynDBOracle: Open Source native Oracle access

(this is an update of the article published in 2011/07)

For our mORMot framework, and in completion to our SynOleDB unit, we added a new Open Source unit, named SynDBOracle. It allows direct access to any remote Oracle server, using the Oracle Call Interface.

Oracle Call Interface (OCI) is the most comprehensive, high performance, native unmanaged interface to the Oracle Database that exposes the full power of the Oracle Database. We wrote a direct call of the oci.dll library, using our DB abstraction classes introduced for SynOleDB.

We tried to implement all best-practice patterns detailed in the official Building High Performance Drivers for Oracle document

Resulting speed is quite impressive: for all requests, SynDBOracle is 3 to 5 times faster than a SynOleDB connection using the native OleDB Provider supplied by Oracle. We noted also that our implementation is 10 times faster than the one provided with ZEOS/ZDBC, which is far from optimized.

You can use the latest version of the Oracle Instant Client provided by Oracle - see this link - which allows you to run your applications without installing the standard (huge) Oracle client or having an ORACLE_HOME. Just deliver the dll files in the same directory than your application, and it will work.

Continue reading...

2012, Friday September 14

Updated mORMot benchmarks on another HW configuration

With a refreshed hardware, and the latest code modifications of the framework code, I run again the benchmark sample.

The new PC has an Intel Core i7-2600 CPU, running on Windows Seven 64-bit, with anti-virus (Norton) fully enabled.
Oracle 11g database is remotely accessed over a corporate network, so latency and bandwidth is not optimal.
Still no SSD, but a standard 7200 rpm hard drive of 500 GB.

The results are impressive, when compared to the previous run using a Core 2 Duo CPU - mORMot's optimized code achieves amazing speed on this platform.
And I guess the 8MB of L3 cache of the Core i7 does wonders with our code.

Continue reading...

2012, Sunday September 9

Synopse mORMot framework 1.17

Our Open Source mORMot framework is now available in revision 1.17.

The main new features are the following:

We have some very exciting features on the road-map for the next 1.18 release, like direct Event/CallBacks handling.
Stay tuned!

Continue reading...

2012, Monday September 3

Client-Server allowed back to XE3 pro

The attempt to restrict the XE3 professional license did evolve into an amazing discussion in Embarcadero forums, and Delphi-related blogs.

David I announced the (reverted) EULA for Delphi Pro. Remote database access is again possible, with terms similar to Delphi Xe2.
You can check the Software License and Support Terms (EULA) for RAD Studio XE3 products.

This is good news, but also the opportunity to check the definitive terms.

In short, with the XE3 pro license, you have a deployment restrictive clause:

  • You can use DBExpress components and units only locally;
  • You can use DataSnap features only locally (it means that you can prototype using DataSnap, but are not allowed to deploy or redistribute DataSnap).

If you want to use those two features on Client-Server, you would need to buy a Client/Server Pack license:

If licensee has purchased a Client/Server Pack, the Licensee of RAD Studio, Delphi, or C++Builder XE3 Professional Edition (“Product”) may deploy that portion of the Product identified as "dbExpress" and dbExpress enterprise database drivers, in executable form only, to enable client server database access. Embarcadero may deliver the Product identified as “Enterprise,” however Licensee is licensed to use only the “Professional” edition features plus "dbExpress" and the Enterprise dbExpress database drivers in a client/server configuration. Licensee may evaluate the n-Tier DataSnap functionality included in the Enterprise Product delivered, but may not deploy or redistribute DataSnap.

This is now a real opportunity for our Open Source mORMot framework.
With a XE3 pro license, and even with a XE3 starter license, you are able, via our free units and classes:

That is, everything you need to build from a small Client-Server or stand-alone application up to the most scalable Domain-Driven design.

Nice, isn't it?

2012, Friday August 31

Breaking change of *FillPrepare() method parameters

I like very much user participation (SCRUM / Agile is my moto) - I never believe to be always right nor write perfect code, and I'm convinced Open Source projects are also about sharing ideas among people of good will.

So when an active member of the forum reported his confusion / concern about some of the ORM methods of our framework, it appeared that some re-factoring was necessary.

There was a breaking change about the TSQLRecord.Create / FillPrepare / CreateAndFillPrepare and TSQLRest.OneFieldValue / MultiFieldValues methods: for historical reasons, they expected parameters to be marked as % in the SQL WHERE clause, and inlined via :(...):.
Since revision 1.17 of the framework, those methods expect parameters marked as ? and with no :(...):.

For instance, instead of writing:

 aRec.CreateAndFillPrepare(Client,'Datum=?',[],[DateToSQL(EncodeDate(2012,5,4))]);
you should write now:
 aRec.CreateAndFillPrepare(Client,'Datum=?',[DateToSQL(EncodeDate(2012,5,4))]);

The void [], array (used for replacing % characters) is not to be written any more, since the default is to use bound parameters via ? and not textual replacement via %.

Due to this breaking change, user code review is necessary if you want to upgrade the engine from 1.16 or previous.

In all cases, using ? is less confusing for new users, and more close to the usual way of preparing database queries - e.g. as used in SynDB.pas units.

Both TSQLRestClient.EngineExecuteFmt / ListFmt methods are not affected by this change, since they are just wrappers to the FormatUTF8() function.

Continue reading...

2012, Tuesday August 28

"Trop c'est trop" - No Client-Server for XE3 PRO users

Here is some unbelievable news retrieved from "Te Waka o Delphi" blog:

From XE3 onwards, your Delphi Professional EULA will prohibit you from using Delphi Professional for anything other than local data access.
If you want to build client/server database applications using Delphi Professional, you will be required to purchase a “Client/Server Add-On” pack.

This goes beyond the fact that you do not get (or can otherwise use or install) client/server drivers for the DBExpress or other “built in” data access frameworks, but extends even to 3rd party data access technologies.
That is, whatever you may be able to do or achieve – technically – using some 3rd party component or library with you Delphi Professional compiler, you cannot legally create a client/server application.
Never mind any 3rd party components or libraries, this same prohibition will apply even if you are using naked, unadorned Microsoft ADO.

Damn show-stopper for me.
Embarcadero is killing Delphi.

Our very own mORMot Open-Source framework is fully Client-Server oriented, and allow creating scalable Client-Server applications even with an Oracle DB system back-end, even with XE2 starter edition (direct access, without any DB.pas / DBExpress layer).

Continue reading...

2012, Friday August 10

Microsoft states: OleDB out - enjoy ODBC!

For our native connection to any DB, we developed a set of classes and several units.

We implemented at first OleDB, then native Oracle direct access and SQlite3 static engine.

Now, Microsoft is officially deprecating OleDB, and urge all developers to switch to the open and cross-platform ODBC API for native connection.

Continue reading...

2012, Wednesday August 1

Managing unique properties

For real applications, retrieving objects per ID is not enough.
Your project may have the need to retrieve objects by a textual field, e.g. a name or identifier.
In this case, you can specify a published property of the TSQLRecord as stored false, and it will be defined as an unique column in the underlying database.

For instance, in the latest version of our performance benchmark sample code, you can define the UNIK conditional to define both LastName and FirstName properties as unique:

type
  TSQLRecordSample = class(TSQLRecord)
  private
    fFirstName: RawUTF8;
    fLastName: RawUTF8;
    fAmount: currency;
    fBirthDate: TDateTime;
    fLastChange: TModTime;
    fCreatedAt: TCreateTime;
  published
    property FirstName: RawUTF8 index 40 read fFirstName write fFirstName
      {$ifdef UNIK}stored false{$endif};
    property LastName: RawUTF8 index 40 read fLastName write fLastName
      {$ifdef UNIK}stored false{$endif};
    property Amount: currency read fAmount write fAmount;
    property BirthDate: TDateTime read fBirthDate write fBirthDate;
    property LastChange: TModTime read fLastChange;
    property CreatedAt: TCreateTime read fCreatedAt write fCreatedAt;
  end;

During insertion or update of records, the database will have to check for uniqueness of those column values. It will have an additional performance cost, since a search of the new value is to be performed among existing values.
In order to speed-up the process, a so-called index is created at the database level.
As a consequence, further lookup using this property will benefit for this index, and will be much faster than a classic loop throughout all data.

In the mORMot core, we just made some modifications related to this feature:

Let's see how it works on the benchmark side.

Continue reading...

2012, Wednesday July 25

Synopse mORMot benchmark

After having tested and enhanced the external database speed (including BATCH mode), we are now able to benchmark all database engines available in mORMot.

In fact, the ORM part of our framework has several potential database backends, in addition to the default SQLite3 file-based engine.
Each engine may have its own purpose, according to the application expectation.

The following tables try to sum up all available possibilities, and give some benchmark (average rows/seconds for writing or read). 

In these tables:

  • 'internal' means use of the internal SQLite3 engine;
  • 'external' stands for an external access via SynDB;
  • 'TObjectList' indicates a TSQLRestServerStaticInMemory instance either static (with no SQL support) or virtual (i.e. SQL featured via SQLite3 virtual table mechanism) which may persist the data on disk as JSON or compressed binary;
  • 'trans' stands for Transaction, i.e. when the write process is nested within BeginTransaction / Commit calls;
  • 'batch' mode will be described in this article;
  • 'read one' states that one object is read per call (ORM generates a SELECT * FROM table WHERE ID=?);
  • 'read all' is when all 5000 objects are read in a single call (i.e. running SELECT * FROM table);
  • ACID is an acronym for "Atomicity Consistency Isolation Durability" properties, which guarantee that database transactions are processed reliably: for instance, in case of a power loss or hardware failure, the data will be saved on disk in a consistent way, with no potential loss of data.
In short: depending on the database you can persist up to 150,000 objects per second, or retrieve  240,000 objects per second.
With a high-performance database like Oracle and our direct access classes, you write 53,000 and read 72,000 objects per second.
Difficult to find a faster ORM, I suspect. :)

Continue reading...

- page 1 of 2