Tag - Database

Entries feed - Comments feed

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

2012-07-24

SQLite3-powered, not SQLite3-limited

Our downloadable documentation has been enhanced, and contains now a description about the main feature of 1.15 version, i.e. "database agnosticism".

The core database of our mORMot framework uses the SQLite3 library, which is a Free, Secure, Zero-Configuration, Server-less, Single Stable Cross-Platform Database File database engine.

As stated below, you can use any other database access layer, if you wish.
A fast in-memory engine (TObjectList-based) is included, and can be used instead or together with the SQLite3 engine.
Or you may be able to access any remote database, and use one or more OleDB, ODBC, ZDBCTDataSet, (or direct Oracle) connections to store your precious ORM objects.
The SQlite3 will be used as the main SQL engine, able to JOIN all those tables, thanks to its Virtual Table unique feature.

(article updated after removal of the TSQLRecordExternal class type for revision 1.17 - note also that BATCH process is now directly supported by the framework and converted to bound array parameters if available)

Continue reading

2012-07-12

One ORM to rule them all

If you discovered the mORMot framework, you may have found out that its implementation may sound restricted, in comparison to other ORMs, due to its design. It would be easy to answer that "it is not a bug, it is a feature", but I suspect it is worth a dedicated article.

Some common (and founded) criticisms are the following (quoting from our forum - see e.g. this question):
- "One of the things I don't like so much about your approach to the ORM is the mis-use of existing Delphi constructs like "index n" attribute for the maximum length of a string-property. Other ORMs solve this i.e. with official Class-attributes";
- "You have to inherit from TSQLRecord, and can't persist any plain class";
- "There is no way to easily map an existing complex database".

I understand very well those concerns.
Our mORMot framework is not meant to fit any purpose, but it is worth understanding why it has been implemented as such, and why it may be quite unique within the family of ORMs - which almost all are following the Hibernate way of doing.

Continue reading

2012-06-24

Use TDataSet in mORMot or SynDB

In our documentation, and in all our code source, we avoid using the VCL DB.pas related units, and all the associated RAD components.

This is by design, since our experiment encouraged us to "think ORM, forget anything about RAD (and even SQL in most cases)" in mORMot.
And it introduced some nice border-side effect to Delphi users, e.g. that even a "Delphi Starter Edition" is able to use mORMot, have access to SQLite3, MS SQL or Oracle or any other DB, add interface-based RESTful JSON services over it, just for free...

But in the real world, you may need to upgrade some existing application, get rid of the BDE, or add a SOA layer over an existing business intelligence.
And mORMot is able to serve you well in those scenarios.
That's why we just added a first attempt to expose SynDB results and mORMOt TSQLTableJSON content into a TDataSet.

Continue reading

2012-06-23

SynDBExplorer enhancements

Our SynDBExplorer free tool has been enhanced. A SQL request history has been added to the software. It is now able to handle directly Jet / MSAccess .mdb files. It has also several fixes included (including Oracle direct link), and the internal SQLite3 engine has been updated to its latest  […]

Continue reading

2012-05-28

Synopse mORMot Framework 1.16

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

The main new features are the following:

Thanks to its features, mORMot is now able to provide a stand-alone Domain-Driven Design framework for Delphi.

Quite a long and nice road for a little mORMot, and more to come!

Continue reading

2012-05-25

Domain-Driven design

With a previous article, we introduced the concept of "Domain-Driven design" into our framework presentation.

It's now time to detail a bit more this very nice software architecture design, and how mORMot is able to achieve such an implementation pattern.

Continue reading

2012-04-25

The mORMot attitude

In a discussion with Henrick Hellström, in Embarcadero forums, I wrote some high-level information about mORMot.

It was clear to me that our little mORMot is now far away from a simple Client-Server solution.

The Henrick point was that with Real Thin Client (RTC), you are able to write any Client-Server solution, even a RESTful / JSON based one.

He is of course right, but it made clear to me all the work done in mORMot since its beginning.
From a Client-Server ORM, it is now a complete SOA framework, ready to serve Domain-Driven-Design solutions.

Continue reading

2012-04-20

WCF, mORMot and Event Sourcing

Our latest mORMot feature is interface-based service implementation.

How does it compare with the reference of SOA implementation (at least in the Windows world) - aka WCF?

"Comparaison n'est pas raison", as we use to say in France.
But we will also speak about Event Sourcing, and why it is now on our official road map.
Comparing our implementation with WCF is the opportunity to make our framework always better.

Continue reading

2012-02-14

ORM cache

Here is the definition of "cache", as stated by Wikipedia:

In computer engineering, a cache is a component that transparently stores data so that future requests for that data can be served faster. The data that is stored within a cache might be values that have been computed earlier or duplicates of original values that are stored elsewhere. If requested data is contained in the cache (cache hit), this request can be served by simply reading the cache, which is comparatively faster. Otherwise (cache miss), the data has to be recomputed or fetched from its original storage location, which is comparatively slower. Hence, the greater the number of requests that can be served from the cache, the faster the overall system performance becomes.

To be cost efficient and to enable an efficient use of data, caches are relatively small. Nevertheless, caches have proven themselves in many areas of computing because access patterns in typical computer applications have locality of reference. References exhibit temporal locality if data is requested again that has been recently requested already. References exhibit spatial locality if data is requested that is physically stored close to data that has been requested already.


In our ORM framework, since performance was one goal since the beginning, cache has been implemented at four levels:

  • Statement cache for reuse of SQL prepared statements, and bound parameters on the fly - note that this cache is available not only for the SQlite3 database engine, but also for any external engine; 
  • Global JSON result cache at the database level, which is flushed globally on any INSERT / UPDATE
  • Tuned record cache at the CRUD/RESTful level for specified tables or records on the server side; 
  • Tuned record cache at the CRUD/RESTful level for specified tables or records on the client side.

Continue reading

2012-01-17

SynDBExplorer fast direct export

The Open Source SynDBExplorer tool has been enhanced these days.

Main new features are:

  • Execution of the current selected text (if any) instead of the whole memo content;
  • "Exec & Export" new button, for direct export to file.
I really like the selection execution feature - this speed up SQL process a lot, and allow to switch from one statement to another.
And the new exporting features are opening new possibilities.

Continue reading

2011-12-06

Automatic JOIN query

In mORMot, all the methods available to handle many-to-many relationship (ManySelect, DestGetJoined...) are used to retrieve the relations between tables from the pivot table point of view. This saves bandwidth, and can be used in most simple cases, but it is not the only way to perform requests on many-to-many relationships. And you may have several TSQLRecordMany instances in the same main record - in this case, those methods won't help you.

It is very common, in the SQL world, to create a JOINed request at the main "Source" table level, and combine records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two or more tables by using values common to each. Writing such JOINed statements is not so easy by hand, especially because you'll have to work with several tables, and have to specify the exact fields to be retrieved; if you have several pivot tables, it may start to be a nightmare.

Let's see how our ORM will handle it.

Continue reading

2011-11-30

AJAX authentication

A nice framework user, named esmondb, did write and publish some JavaScript code to handle our RESTful authentication mechanism.

It seems to work well, and implements all secure hashing and challenging.
Our authentication mechanism is much more advanced than the one used by DataSnap - which is a basic HTTP authentication with the password transmitted in clear (this is the reason why it shall better be used over HTTPS, whereas mORMot can be used over plain HTTP).
Resulting JavaScript code seems not difficult to follow, even for a no JS expert like me.

Continue reading

2011-11-08

Currency is your friend

The currency type is the standard Delphi type to be used when storing and handling monetary values. It will avoid any rounding problems, with 4 decimals precision. It is able to safely store numbers in the range -922337203685477.5808 .. 922337203685477.5807. Should be enough for your pocket change.

As stated by the official Delphi documentation:

Currency is a fixed-point data type that minimizes rounding errors in monetary calculations. On the Win32 platform, it is stored as a scaled 64-bit integer with the four least significant digits implicitly representing decimal places. When mixed with other real types in assignments and expressions, Currency values are automatically divided or multiplied by 10000.

In fact, this type matches the corresponding OLE and .Net implementation of currency, and the one used by most database providers (when it comes to money, a dedicated type is worth the cost in a "rich man's world"). It is still implemented the same in the Win64 platform (since XE 2). The Int64 binary representation of the currency type (i.e. value*10000 as accessible via PInt64(aCurrencyValue)^) is a safe and fast implementation pattern.

In our framework, we tried to avoid any unnecessary conversion to float values when dealing with currency values. Some dedicated functions have been implemented for fast and secure access to currency published properties via RTTI, especially when converting values to or from JSON text. Using the Int64 binary representation can be not only faster, but also safer: you will avoid any rounding problem which may be introduced by the conversion to a float type. Rounding issues are a nightmare to track - it sounds safe to have a framework handling natively a currency type from the ground up.

Continue reading

2011-09-25

Synopse SQLite3/mORMot framework 1.15

Our Client-Server ORM framework is now available in revision 1.15.

This is a major upgrade of the framework:

  • It is now called mORMot - so please update your T-Shirts or coffee cups ;)
  • It is able to use any Database engine back-end - in fact, it is SQLite3 powered, not SQLite3 limited;
  • In particular, direct OleDB and native Oracle have been implemented;
  • It makes use of the genuine SQlite3 Virtual Table mechanism everywhere to allow mixed access to any database engine;
  • New TModTime / TCreateTime kind of fields;
  • Enhanced stability, speed and multi-thread implementation;
  • Methods and functions have been enhanced, according to user feedback (thanks you all for your interest and forum posts!);
  • Extended documentation (more than 700 pdf pages), with new diagrams and a lot of new content;
  • New associated tools, like LogViewer or SynDBExplorer;
  • The SQLite3 core can now be used without our ORM - it has been updated to the latest 3.7.8 version;
  • Open Source (under GPL/LGPL/MPL license), running from Delphi 6 up to XE2.

Continue reading

Synopse SQLite3 framework is now mORMot

In case you were redirected from the previous "Synopse SQLite3 framework" category link, here is the new thread to be used instead: http://blog.synopse.info/category/Open-Source-Projects/mORMot-Framework Since revision 1.15 of the framework, it is able to connect to any database engine  […]

Continue reading

2011-08-20

Enhanced Log viewer

We already shipped a sophisticated set of logging classes some month ago.

Since then, its features have been enhanced, and the system has been deeply interfaced with our main ORM framework. Now almost all low-level or high-level operations can be logged on request.

But since the log files tend to be huge (for instance, if you set the logging for our unitary tests, the 6,000,000 unitary tests creates a 280 MB log file), a log viewer was definitively in need.

Continue reading

2011-08-10

Framework documentation updated for revision 1.15

The framework documentation was just updated.

The general organization of the SAD document (which is the one to be read in all cases) has been refreshed, and is now separated in smaller chapters.

The new official name has been changed into "Synopse SQLite3/mORMot framework"...

Continue reading

2011-08-08

Our mORMot won't hibernate this winter, thanks to FireMonkey

Everybody is buzzing about FireMonkey...

Our little mORMot will like FireMonkey!
Here is why...

Continue reading

2011-07-25

Close future of the framework: database agnosticism

Our ORM RESTful Framework is about to access any available database engine.

It will probably change its name (since it won't use only SQlite3 as database), to become mORMot - could be an acronym for "Manage Object Relational Mapping Over Tables", or whatever you may think of...

We'll still rely on SQLite3 on the server, but a dedicated mechanism will allow to access via OleDB any remote database, and mix those tables content with the native ORM tables of the framework. A flexible Virtual Tables and column mapping will allow any possible architecture: either a new project in pure ORM, either a project relying on an existing database with its own table layout.

Continue reading

- page 4 of 6 -