Synopse

To content | To menu | To search

Tag - SQL

Entries feed

2013, Friday June 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, Friday June 7

Authentication and Authorization

Our mORMot framework tries to implement security via:
- Process safety;
- Authentication;
- Authorization.

Process safety is implemented at every n-Tier level:
- Atomicity of the SQLite3 database core;
- RESTful architecture to avoid most synchronization issues;
- ORM associated to the Object pascal strong type syntax;
- Extended test coverage of the framework core.

Authentication allows user identification:
- Build-in optional authentication mechanism, implementing both per-user sessions and individual REST Query Authentication;
- Authentication groups are used for proper authorization;
- Several authentication schemes, from very secure SHA-256 based challenging to weak but simple authentication;
- Class-based architecture, allowing custom extension.

Authorization of a given process is based on the group policy, after proper authentication:
- Per-table access right functionalities built-in at lowest level of the framework;
- Per-method execution policy for interface-based services;
- General high-level security attributes, for SQL or Service remote execution.

We will now give general information about both authentication and authorization in the framework.

In particular, authentication is now implemented via a set of classes.

Continue reading...

2013, Friday May 24

REGEXP operator for SQLite3

Our SQLite3 engine can now use regular expression within its SQL queries, by enabling the REGEXP operator in addition to standard SQL operators (= == != <> IS IN LIKE GLOB MATCH). It will use the Open Source PCRE library (bounded since Delphi XE, or available as a separate download) to perform the queries.

It will enable advanced searches within text columns and our objects, when used as a WHERE clause of our mORMot's ORM.

Continue reading...

2013, Wednesday April 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 download the corresponding DB file to run the sample.
Enjoy!

2013, Tuesday April 2

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 mORMot authentication model seems much more secure than DataSnap's), this is a very nice demo!
Thanks a lot, warleyalex for the feedback and information!

2013, Wednesday March 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, Thursday March 7

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, Monday February 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, Sunday February 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, Tuesday February 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, Sunday February 3

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

2013, Monday January 28

External database speed improvements

Some major speed improvements have been made to our SynDB* units, and how they are used within the mORMot persistence layer.
It results in an amazing speed increase, in some cases.

Here are some of the optimizations how took place in the source code trunk:

Overall, I observed from x2 to x10 performance boost with simple Add() operations, using ODBC, OleDB and direct Oracle access, when compare to previous benchmarks (which were already impressive).
BATCH mode performance is less impacted, since it by-passed some of those limitations, but even in this operation mode, there is some benefits (especially with ODBC and OleDB).

Here are some results, directly generated by the supplied "15 - External DB performance" sample.

Continue reading...

2012, Monday December 31

Enhance existing projects with mORMot

Even if mORMot will be more easily used in a project designed from scratch, it fits very well the purpose of evolving any existing Delphi project, or even creating the server side part of an AJAX application. 

One benefit of such a framework is to facilitate the transition from a Client-Server architecture to a N-Tier layered pattern.

Continue reading...

2012, Sunday October 28

SynDBOracle: Open Source native Oracle access

(this is an update of the article published in 2011/07)

For our mORMot framework, and in completion to our SynOleDB unit, we added a new Open Source unit, named SynDBOracle. It allows direct access to any remote Oracle server, using the Oracle Call Interface.

Oracle Call Interface (OCI) is the most comprehensive, high performance, native unmanaged interface to the Oracle Database that exposes the full power of the Oracle Database. We wrote a direct call of the oci.dll library, using our DB abstraction classes introduced for SynOleDB.

We tried to implement all best-practice patterns detailed in the official Building High Performance Drivers for Oracle document

Resulting speed is quite impressive: for all requests, SynDBOracle is 3 to 5 times faster than a SynOleDB connection using the native OleDB Provider supplied by Oracle. We noted also that our implementation is 10 times faster than the one provided with ZEOS/ZDBC, which is far from optimized.

You can use the latest version of the Oracle Instant Client provided by Oracle - see this link - which allows you to run your applications without installing the standard (huge) Oracle client or having an ORACLE_HOME. Just deliver the dll files in the same directory than your application, and it will work.

Continue reading...

2012, Wednesday October 3

Today's sugar: "stored AS_UNIQUE" syntax

The limited RTTI available in earlier versions of Delphi we want to support (starting with Delphi 6/7) lacks of attributes.
Even the attribute feature of newer Delphi version is not compatible with the one exposed by the FreePascalCompiler, we also want to support.

Therefore, our ORM expects unique columns in a TSQLRecord published property to be defined as stored false.
It could be misleading at first, as reported by several users.
In order to avoid any confusion, we just added a new constant named AS_UNIQUE.

Continue reading...

2012, Friday September 14

Updated mORMot benchmarks on another HW configuration

With a refreshed hardware, and the latest code modifications of the framework code, I run again the benchmark sample.

The new PC has an Intel Core i7-2600 CPU, running on Windows Seven 64-bit, with anti-virus (Norton) fully enabled.
Oracle 11g database is remotely accessed over a corporate network, so latency and bandwidth is not optimal.
Still no SSD, but a standard 7200 rpm hard drive of 500 GB.

The results are impressive, when compared to the previous run using a Core 2 Duo CPU - mORMot's optimized code achieves amazing speed on this platform.
And I guess the 8MB of L3 cache of the Core i7 does wonders with our code.

Continue reading...

2012, Sunday September 9

Synopse mORMot framework 1.17

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

The main new features are the following:

We have some very exciting features on the road-map for the next 1.18 release, like direct Event/CallBacks handling.
Stay tuned!

Continue reading...

2012, Monday September 3

Client-Server allowed back to XE3 pro

The attempt to restrict the XE3 professional license did evolve into an amazing discussion in Embarcadero forums, and Delphi-related blogs.

David I announced the (reverted) EULA for Delphi Pro. Remote database access is again possible, with terms similar to Delphi Xe2.
You can check the Software License and Support Terms (EULA) for RAD Studio XE3 products.

This is good news, but also the opportunity to check the definitive terms.

In short, with the XE3 pro license, you have a deployment restrictive clause:

  • You can use DBExpress components and units only locally;
  • You can use DataSnap features only locally (it means that you can prototype using DataSnap, but are not allowed to deploy or redistribute DataSnap).

If you want to use those two features on Client-Server, you would need to buy a Client/Server Pack license:

If licensee has purchased a Client/Server Pack, the Licensee of RAD Studio, Delphi, or C++Builder XE3 Professional Edition (“Product”) may deploy that portion of the Product identified as "dbExpress" and dbExpress enterprise database drivers, in executable form only, to enable client server database access. Embarcadero may deliver the Product identified as “Enterprise,” however Licensee is licensed to use only the “Professional” edition features plus "dbExpress" and the Enterprise dbExpress database drivers in a client/server configuration. Licensee may evaluate the n-Tier DataSnap functionality included in the Enterprise Product delivered, but may not deploy or redistribute DataSnap.

This is now a real opportunity for our Open Source mORMot framework.
With a XE3 pro license, and even with a XE3 starter license, you are able, via our free units and classes:

That is, everything you need to build from a small Client-Server or stand-alone application up to the most scalable Domain-Driven design.

Nice, isn't it?

2012, Friday August 31

Breaking change of *FillPrepare() method parameters

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.

Continue reading...

2012, Friday August 10

Microsoft states: OleDB out - enjoy ODBC!

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.

Continue reading...

- page 2 of 4 -