Tag - Database

Entries feed - Comments feed

2014-03-29

Enhanced and fixed late-binding of variants for Delphi XE2 and up

For several units of our framework, we allow late-binding of data values, using a variant and direct named access to properties:
- In SynCommons, we defined our TDocVariant custom variant type, able to store any JSON/BSON document-based content;
- In SynBigTable, we use the TSynTableVariantType custom variant type, as defined in SynCommons;
- In SynDB, we defined a TSQLDBRowVariantType, ready to access any column of a RDBMS data result set row;
- In mORMot, we allow access to TSQLTableRowVariantType column values.

It's a very convenient way of accessing result rows values. Code is still very readable, and safe at the same time.

For instance, we can write:

var V: variant;
 ...
  TDocVariant.New(V); // or slightly slower V := TDocVariant.New;
  V.name := 'John';
  V.year := 1972;
  // now V contains {"name":"john","year":1982}

This is just another implementation of KISS design in our framework.

Since Delphi XE2, some modifications were introduced to the official DispInvoke() RTL implementation:

  1. A new varUStrArg kind of parameter has been defined, which will allow to transmit UnicodeString property values;
  2. All text property values would be transmitted as BSTR / WideString / varOleStr variants to the invoked variant type;
  3. All textual property names were normalized to be in UPPERCASE.

Those modifications are worth considering...
And we may have discovered two regressions: one about speed, and the other about an unexpected logic bug...

Continue reading

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

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

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

2014-02-28

Are NoSQL databases ACID?

One of the main features you may miss when discovering NoSQL ("Not-Only SQL"?) databases, coming from a RDBMS background, is ACID.

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction. For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction. (Wikipedia)

But are there any ACID NoSQL database?

Please ensure you read the Martin Fowler introduction about NoSQL databases.
And the corresponding video.

First of all, we can distinguish two types of NoSQL databases:

  1. Aggregate-oriented databases;
  2. Graph-oriented databases (e.g. Neo4J).

By design, most Graph-oriented databases are ACID!
This is a first good point.

Then, what about the other type?
In Aggregate-oriented databases, we can identify three sub-types:

  • Document-based NoSQL databases (e.g. MongoDB, CouchDB);
  • Key/Value NoSQL databases (e.g. Redis);
  • Column family NoSQL databases (e.g. Cassandra).
Whatever document/key/column oriented they are, they all use some kind of document storage.
It may be schema-less, blob-stored, column-driven, but it is always some set of values bound together to be persisted.
This set of values define a particular state of one entity, in a given model.
Which we may call Aggregate.

Continue reading

2014-01-04

Domain-Driven Design: part 4

Clean Uncoupled Domain-Oriented Architecture

One year ago, we already made a quick presentation of Domain-Driven Design, in the context of our mORMot framework.
After one year of real-world application of those patterns, it is now time to give more light to DDD.

Let's continue with part 4, which will define Domain-Driven Design as could be implemented with our Synopse mORMot framework

Continue reading

Domain-Driven Design: part 3

One year ago, we already made a quick presentation of Domain-Driven Design, in the context of our mORMot framework.
After one year of real-world application of those patterns, it is now time to give more light to DDD.

Let's continue with part 3, which will define Domain-Driven Design patterns and principles - this will be the main article of the whole serie!

Continue reading

Domain-Driven Design: part 2

One year ago, we already made a quick presentation of Domain-Driven Design, in the context of our mORMot framework.
After one year of real-world application of those patterns, it is now time to give more light to DDD.

Let's continue with part 2, which will define Domain-Driven Design high-level model principles.

Continue reading

Domain-Driven Design: part 1

One year ago, we already made a quick presentation of Domain-Driven Design, in the context of our mORMot framework.
After one year of real-world application of those patterns, and a training made by a great French software designer named Jérémie Grodziski, it is now time to give more light to DDD.

Let's start with part 1, which will be a general introduction to Domain-Driven Design, trying to state how it may be interesting (or not) for your projects.

Continue reading

2013-11-04

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

Thread-safety of mORMot

We tried to make mORMot at the same time fast and safe, and able to scale with the best possible performance on the hardware it runs on.
Multi-threading is the key to better usage of modern multi-core CPUs, and also client responsiveness.

As a result, on the Server side, our framework was designed to be thread-safe.

On typical production use, the mORMot HTTP server will run on its own optimized thread pool, then call the TSQLRestServer.URI method. This method is therefore expected to be thread-safe, e.g. from the TSQLHttpServer. Request method. Thanks to the RESTful approach of our framework, this method is the only one which is expected to be thread-safe, since it is the single entry point of the whole server. This KISS design ensure better test coverage.

Let us see now how this works, and publish some benchmarks to test how efficient it has been implemented.

Continue reading

2013-06-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-06-14

SQLite3 performance in Exclusive file locking mode

As stated in previous blog articles, the default SQlite3 write speed is quite slow, when running on a normal hard drive. By default, the engine will pause after issuing a OS-level write command. This guarantees that the data is written to the disk, and features the ACID properties of the database engine.

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 SQLite3, ACID is implemented by two means at file level:
- Synchronous writing: it means that the engine will wait for any written content to be flushed to disk before processing the next request;
- File locking: it means that the database file is locked for exclusive use during writing, allowing several processes to access the same database file concurrently.

Changing these default settings can ensure much better writing performance.

We just added direct File locking tuning.
It appears that defining exclusive access mode is able to increase the performance a lot, in both reading and writing speed.

Here are some new benchmarks and data, extracted from the updated SAD documentation.

Continue reading

2013-05-11

Delphi XE4 NextGen compiler: using byte instead of ansichar?

When I first read the technical white paper covering all of the language changes in XE4 for mobile development (tied to the new ARM LLVM-based Delphi compiler), I have to confess I was pretty much confused.

Two great mORMot users just asked for XE4/iOS support of mORMot.

Win32/Win64 support for XE4 will be done as soon as we got a copy of it.
I suspect the code already works, since it was working as expected with XE3, and we rely on our own set of low-level functions for most internal work.

But iOS-targetting is more complex, due to the NextGen compiler, mainly.

Continue reading

2013-04-24

mORMots know how to swim like fishes

Another great video by warleyalex. This time, a full FishFacts demo in AJAX, using mORMot and its SQLite3 ORM as server. See it on YouTube! Feedback is welcome on our forum. Update: I've just uploaded the corresponding source code to our repository. See sample 19 - AJAX ExtJS FishFacts. You need to  […]

Continue reading

2013-04-22

TDataSet... now I'm confused

You perhaps know that I'm not a big fan of the TDataSet / RAD DB approach for end-user applications.
They are easy to define, almost no code to write, and you are able to publish a working solution very fast.

But it is a nightmare to debug and maintain. I prefer the new DataBinding feature, or... of course... ORM!
In mORMot, we have some auto-generated screens, and in our roadmap, we forcast to use some auto-binding features, using a KISS by-convention MVC pattern.

For some users, we made a ORM / TDataSet conversion unit.
And we discovered that TDataSet has a weird, and very misleading definition of its AsString property, for Unicode versions of Delphi.

Continue reading

2013-04-02

Two videos about EXTjs client of mORMot server

Two nice videos, posted by a framework user. The first one presents a remote RESTful access of a SQLite3 database, hosted by a mORMot server: After one post in the forum, warleyalex was able to easily add remote filtering of the request: In addition to the previous video about security (by which the  […]

Continue reading

2013-03-27

Introducing TSQLTable.Step() method

We have just added TSQLTable.Step(), FieldBuffer() and Field() methods, handling a cursor at TSQLTable level, with optional late-binding column access.

It allows to retrieve results from a TSQLTable / TSQLTableJSON result sets within a "cursor-like" orientation.
That is, no need to specify the row number, but write a simple while aList.Step do ... loop.

Of course, you should better use TSQLRecord.FillPrepare most of the time, and access the data from a TSQLRecord instance.
But it can be very useful, e.g. when working on a custom JOINed SQL statement.

Continue reading

2013-03-07

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

Using external MinGW/VisualC++ sqlite3.dll - including benchmark

With upcoming revision 1.18 of the framework, our SynSQlite3.pas unit is able to access the SQLite3 engine in two ways:

  • Either statically linked within the project executable;
  • Or from an external sqlite3.dll library file.

The SQLite3 APIs and constants are defined in SynSQlite3.pas, and accessible via a TSQLite3Library class definition. It defines a global sqlite3 variable as such:

var
  sqlite3: TSQLite3Library;

To use the SQLite3 engine, an instance of TSQLite3Library class shall be assigned to this global variable. Then all mORMot's calls will be made through it, calling e.g. sqlite3.open() instead of sqlite3_open().

There are two implementation classes:

Class Unit Purpose
TSQLite3LibraryStatic SynSQLite3Static.pas Statically linked engine (.obj within the .exe)
TSQLite3LibraryDynamic SynSQLite3.pas Instantiate an external sqlite3.dll instance

Referring to SynSQLite3Static.pas in the uses clause of your project is enough to link the .obj engine into your executable.

Warning - breaking change: before version 1.18 of the framework, link of static .obj was forced - so you must add a reference to SynSQLite3Static in your project uses clause to work as expected.

In order to use an external sqlite3.dll library, you have to set the global sqlite3 variable as such:

 FreeAndNil(sqlite3); // release any previous instance (e.g. static)
 sqlite3 := TSQLite3LibraryDynamic.Create;

Of course, FreeAndNil(sqlite3) is not mandatory, and should be necessary only to avoid any memory leak if another SQLite3 engine instance was allocated (may be the case if SynSQLite3Static is referred somewhere in your project's units).

Here are some benchmarks, compiled with Delphi XE3, run in a 32 bit project, using either the static bcc-compiled engine, or an external sqlite3.dll, compiled via MinGW or Microsoft Visual C++.

Continue reading

- page 2 of 6 -