February 2013 (4)

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

2013-02-17

Interface-based service sample: remote SQL access

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

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

Log to the console

Our framework features an integrated logging class, ready to be enabled for support and statistics.

For debugging purposes, it could be very handy to output the logging content to a console window.
It enables interactive debugging of a Client-Server process, for instance: you can interact with the Client, then look in real time at the server console window, and inspect which requests are processed, without the need to open the log file.

Depending on the events, colors will be used to write the corresponding information. Errors will be displayed as light red, for instance.

Continue reading