Synopse Open Source

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.

2012-08-30

WinRT support for XE3

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

Apart the sad and concerning license change issue (which has been confirmed by David I. himself), XE3 has some features, in order to support Windows 8 new 'tile-based' interface (formerly known as "Metro").

Windows Runtime, or WinRT (not to be confused with Windows RT, which is a tablet manufacturer only version of Windows 8) is a cross-platform application architecture on the Windows 8 operating system.
WinRT supports development in C++/CX (Component Extensions, a language based on C++) and the managed languages C# and VB.NET, as well as JavaScript.
WinRT applications natively support both the x86 and ARM architectures, and also run inside a sandboxed environment to allow for greater security and stability.
WinRT will also be part of the upcoming Windows Phone 8 operating systems.
(source: Wikipedia)

It has been clearly stated that only Microsoft compilers and runtime libraries (RTL) will be able to have full access to the low-level API needed to create a decent RTL.
This has been done for security reasons, but it won't allow third-party JIT or compilers to work as expected. Only Microsoft's C++ and C# compilers / virtual machines have access to the needed API. Even if you do not have a JIT in your language (Delphi is compiled and do not have any virtual machine), you would need to access to some low-level API calls e.g. to mark some memory block as executable (e.g. for virtual methods stubbing).

So Delphi is not able to have native support of WinRT, due to this limitation.

This is a known fact, but let us tell about "Windows 8 sideloading" feature, available with XE3.
In short, even if you do not have 100% WinRT application, XE3 "Metropolis" (sic) styled Desktop applications have some potential to behave like native UI applications, even if not being native.

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).

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.

2012-08-01

Managing unique properties

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

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:

External tables are now able to create properly UNIQUE fields at database level, as expected;
A hashed-based index feature has been added to the fast TSQLRestServerStaticInMemory internal engine, and insertion will have no speed penalty any more.

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

2012-07-26

ACID and speed

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

As stated during our recent benchmarks, 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.

You can overwrite this default behavior by setting the TSQLDataBase.Synchronous property to smOff instead of the default smFull setting. When Synchronous is set to smOff, SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, some operations are as much as 50 or more times faster with this setting.

When the same tests are performed with Synchronous := smOff, "Write one" speed is enhanced from 8-9 rows per second into about 400 rows per second, on a physical hard drive (SSD or NAS drives may not suffer from this delay). We'll show below the detailed benchmark results.

So depending on your application requirements, you may switch Synchronous setting to off, to enhance server-side responsiveness.

2012-07-25

Synopse mORMot benchmark

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

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. :)

2012-07-24

SQLite3-powered, not SQLite3-limited

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

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, ZDBC, TDataSet, (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)