Tag - SynDB

Entries feed - Comments feed


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:

  TSQLRecordSample = class(TSQLRecord)
    fFirstName: RawUTF8;
    fLastName: RawUTF8;
    fAmount: currency;
    fBirthDate: TDateTime;
    fLastChange: TModTime;
    fCreatedAt: TCreateTime;
    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;

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


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


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


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


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


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


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


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


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


SynDBSQLite3: SQLite3 direct access

For our ORM framework, we implemented an efficient SQLite3 wrapper, joining statically (i.e. without any external dll) the SQLite3 engine to the executable. SQLite3 is in fact used as the DB kernel of the framework. For instance, thanks to its unique virtual table mechanism, even tables in other databases (like Oracle or MSSQL) are available as if they were SQLite3 tables.

We just made this wrapper independent from our ORM, in a new dedicated unit, named SynSQLite3.pas.

It was an easy task to let this unit be called from our SynDB database abstract classes.

Continue reading


SynOleDB: OpenSource Unit for direct access to any database via OleDB

That's it, our SynOleDB unit seems alive and running well.

OLE DB (Object Linking and Embedding, Database, sometimes written as OLEDB or OLE-DB) is an API designed by Microsoft for accessing data from a variety of sources in a uniform manner. It was designed as a higher-level replacement for, and successor to, ODBC, extending its feature set to support a wider variety of non-relational databases, such as object databases and spreadsheets that do not necessarily implement SQL.

SynOleDB unit implementation has been made with several points in mind:

  • Tested with SQL Server 2008 R2 and Oracle 11g providers from Microsoft and Oracle; 
  • Ability to be truly Unicode, even with pre-Unicode version of Delphi (like Delphi 7 or 2007); 
  • Could access any local or remote Database, from any version of Delphi, since it doesn't use the DB.pas unit or any related part of the VCL (even the Delphi 7 personal or the Turbo Explorer editions), just for free; 
  • Handle NULL or BLOB content for parameters and results; 
  • Avoid most memory copy or unnecessary allocation: we tried to access the data directly from the retrieved data buffer, just as given from OleDB; 
  • Was therefore designed to achieve the best performance possible: most time is spent in OleDB: the code layer added to the OleDB customer is very thin; 
  • True OOP architecture, to be used with any OleDB provider (allowing custom parameters or such), and even without OleDB (in the future, direct access to any DB client could be used); 
  • Could be safely used in a multi-threaded application/server (with one TOleDBConnection per thread); 
  • Allow parameter bindings of requests, with fast access to any parameter or column name (thanks to TDynArrayHashed);
  • Late binding of column values in Delphi code;
  • Direct JSON content creation, with no temporary data copy nor allocation; 
  • Designed to be used with our mORMot ORM, but could be used stand-alone (a full Delphi 7 client executable is just about 200 KB), or even in any existing Delphi application, thanks to a TQuery-like wrapper.

Continue reading

page 2 of 2 -