Tag - SynDB

Entries feed - Comments feed

2018-03-12

New AES-based SQLite3 encryption

By AB4327-GANDI, 2018-03-12. Open Source › mORMot Framework

We just committed a deep refactoring of the SynSQlite3Static.pas unit - and all units using static linking for FPC. It also includes a new encryption format for SQlite3, using AES, so much more secure than the previous one. This is a breaking change, so worth a blog article! Now all static .o .a  […]

Continue reading

2014-11-18

HTTP remote access for SynDB SQL execution

By AB4327-GANDI, 2014-11-18. Open Source › Open Source libraries

For mORMot, we developed a fully feature direct access layer to any RDBMS, implemented in the SynDB.pas unit.

You can use those SynDB classes to execute any SQL statement, without any link to the framework ORM.
At reading, the resulting performance is much higher than using the standard TDataSet component, which is in fact a true performance bottleneck.
It has genuine features, like column access via late-binding, an innovative ISQLDBRows interface, and ability to directly access the low-level binary buffers of the database clients.

We just added a nice feature to those classes: the ability to access remotely, via plain HTTP, to any SynDB supported database!

Continue reading

2014-04-18

Introducing mORMot's architecture and design principles

By AB4327-GANDI, 2014-04-18. Open Source › mORMot Framework

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  […]

Continue reading

2014-03-13

ORM mapping class fields to external table columns

By AB4327-GANDI, 2014-03-13. Open Source › mORMot Framework

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-03-07

Support of MySQL, DB2 and PostgreSQL

By AB4327-GANDI, 2014-03-07. Open Source › mORMot Framework

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-03-03

ORM enhanced for BATCH insert

By AB4327-GANDI, 2014-03-03. Open Source › mORMot Framework

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-11-04

Updated mORMot database benchmark - including MS SQL and PostgreSQL

By AB4327-GANDI, 2013-11-04. Open Source › mORMot Framework

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-06-16

FireDAC / AnyDAC support for mORMot

By AB4327-GANDI, 2013-06-16. Open Source › mORMot Framework

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-03-07

64 bit compatibility of mORMot units

By AB4327-GANDI, 2013-03-07. Open Source › mORMot Framework

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-02-17

Interface-based service sample: remote SQL access

By AB4327-GANDI, 2013-02-17. Open Source › mORMot Framework

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-02-12

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

By AB4327-GANDI, 2013-02-12. Open Source › mORMot Framework

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-01-28

External database speed improvements

By AB4327-GANDI, 2013-01-28. Open Source › mORMot Framework

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-12-31

Enhance existing projects with mORMot

By AB4327-GANDI, 2012-12-31. Open Source › mORMot Framework

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-10-28

SynDBOracle: Open Source native Oracle access

By AB4327-GANDI, 2012-10-28. Open Source › mORMot Framework

(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-09-14

Updated mORMot benchmarks on another HW configuration

By AB4327-GANDI, 2012-09-14. Open Source › mORMot Framework

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-09-09

Synopse mORMot framework 1.17

By AB4327-GANDI, 2012-09-09. Open Source › mORMot Framework

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-09-03

Client-Server allowed back to XE3 pro

By AB4327-GANDI, 2012-09-03. Open Source › mORMot Framework

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  […]

Continue reading

2012-08-31

Breaking change of *FillPrepare() method parameters

By AB4327-GANDI, 2012-08-31. Open Source › mORMot Framework

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-08-28

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

By AB4327-GANDI, 2012-08-28. Pascal Programming

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-08-10

Microsoft states: OleDB out - enjoy ODBC!

By AB4327-GANDI, 2012-08-10. Open Source › mORMot Framework

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

- page 1 of 2