2011, Sunday September 25
By A.Bouchez on 2011, Sunday September 25, 13:59
In case you were redirected from the previous "Synopse SQLite3
framework" category link, here is the new thread to be used instead:
Since revision 1.15 of the framework, it is able to connect to any database
engine (therefore is not limited to
SQLite3), and is now called mORMot.
2011, Thursday September 1
By A.Bouchez on 2011, Thursday September 1, 20:02
The server side call back signature changed since this article was first
published in 2010.
Please refer to the documentation or this forum article and
The article was totally rewritten to reflect the enhancements.
And do not forget to see mORMot's interface-based
Note that the main difference with previous implementation is the
signature of the service implementation event, which should be now
(note that there is one unique
class parameter, with no
Please update your code if you are using method-based
You certainly knows about the new DataSnap Client-Server features, based on
JSON, introduced in Delphi 2010.
We added such communication in our mORmot Framework, in a KISS
(i.e. simple) way: no expert, no new unit or new class. Just add a published
method Server-side, then use easy functions about JSON or URL-parameters to get
the request encoded and decoded as expected, on Client-side.
2011, Monday July 4
By A.Bouchez on 2011, Monday July 4, 06:09
If you want to implement an HTTP client access in your application, you may
consider several choices:
- Use the provided
- Use third-party components like Synapse, ICS or
your own WinSock-based wrapper;
- Use WinINet;
- Use WinHTTP.
For our ORM, we tried to avoid external dependencies, and did not have the
need of all Indy's features and overhead.
We fist wrote our own WinSock wrapper, then tried out
When used on our testing benchmark, we found out that WinINet was
Then we tried WinHTTP, the new API provided by Microsoft, and we found
out this was blazing fast. As fast as direct WinSock access,
without the need of writing all the wrapper code.
2011, Sunday July 3
By A.Bouchez on 2011, Sunday July 3, 20:12
Here is what wikipedia states at http://en.wikipedia.org/wiki/Shared_nothing_architecture:
A shared nothing architecture (SN) is a distributed computing
architecture in which each node is independent and self-sufficient, and there
is no single point of contention across the system. People typically contrast
SN with systems that keep a large amount of centrally-stored state information,
whether in a database, an application server, or any other similar single point
This is just one approach of "sharding". Sharding is indeed related to a
shared nothing architecture - once sharded, each shard can live in a totally
separate logical schema instance.
"I sharded, therefore it scales"...
You can do this in Delphi... and opens a new world of scaling
opportunities... Just as Google, Facebook, or
2011, Wednesday June 29
By A.Bouchez on 2011, Wednesday June 29, 06:10
Our ORM framework has been released as version 1.14.
It's mainly a bug-fix release:
- Integrated SQLite3 engine updated to latest version 188.8.131.52;
- Fix several issues about JSON generation layout;
- Enhanced automated User Interface generation for object on-screen
- SynPdf unit now handles Bézier curves from TCanvas, and
some CMYK functions; also enhanced PDF/A-1compatibility;
- Some speed enhancements, and new functions for the
2011, Sunday June 5
By A.Bouchez on 2011, Sunday June 5, 17:45
This is a major step for the framework.
Among a lot of new features and bug fixes:
Open Source project, for Delphi 6 up to XE, licensed under a MPL/LGPL/GPL
2011, Thursday June 2
By A.Bouchez on 2011, Thursday June 2, 16:42
The SQLite3 engine defines some standard SQL functions, like
abs() min() max() or
A complete list is available at http://www.sqlite.org/lang_corefunc.html
One of the greatest SQLite3 feature is the ability to define custom
SQL functions in high-level language. In fact, its C API allows to implement
new functions which may be called within a SQL query. In other database engine,
such functions are usually named UDF (for User Defined Functions).
Our framework allows you to add easily such custom functions, directly from
By A.Bouchez on 2011, Thursday June 2, 16:16
When it deals with parsing some (textual) content, two directions are
usually envisaged. In the XML world, you have usually to make a choice
- A DOM parser, which creates an in-memory tree structure of objects mapping
the XML nodes;
- A SAX parser, which reads the XML content, then call pre-defined
events for each XML content element.
In fact, DOM parsers use internally a SAX parser to read the XML content.
Therefore, with the overhead of object creation and their property
initialization, DOM parsers are typically three to five times slower than SAX.
But, DOM parsers are much more powerful for handling the data: as soon as it's
mapped in native objects, code can access with no time to any given node,
whereas a SAX-based access will have to read again the whole XML content.
Most JSON parser available in Delphi use a DOM-like approach. For instance,
the DBXJSON unit included
since Delphi 2010 or the SuperObject or DWS libraries create a
class instance mapping each JSON node.
In a JSON-based Client-Server ORM like ours, profiling shows that a lot of
time is spent in JSON parsing, on both Client and Server side. Therefore, we
tried to optimize this part of the library.
By A.Bouchez on 2011, Thursday June 2, 15:18
When use the so-called BATCH sequences?
In a standard Client-Server architecture, especially with the common
understanding (and most implementations) of a RESTful service, any
Update / Delete method call requires a back and forth flow to then from
the remote server.
In case of a remote connection via the Internet (or a slow network), you
could have some 100 ms of latency: it's just the "ping" timing, i.e. the time
spent for your IP packet to go to the server, then back to you.
If you are making a number of such calls (e.g. add 1000 records), you'll
have 100*1000 ms = 100 s = 1:40 min just because of this network latency!
The BATCH sequence allows you to regroup those statements into just ONE
remote call. Internally, it builds a JSON stream, then post this stream at once
to the server. Then the server answers at once, after having performed all the
By A.Bouchez on 2011, Thursday June 2, 15:00
An R-Tree is a special index that is designed for doing range queries.
R-Trees are most commonly used in geospatial systems where each entry is a
rectangle with minimum and maximum X and Y coordinates. Given a query
rectangle, an R-Tree is able to quickly find all entries that are contained
within the query rectangle or which overlap the query rectangle.
This idea is easily extended to three dimensions for use in CAD
systems. R-Trees also find use in time-domain range look-ups. For example,
suppose a database records the starting and ending times for a large number of
events. A R-Tree is able to quickly find all events, for example, that were
active at any time during a given time interval, or all events that started
during a particular time interval, or all events that both started and ended
within a given time interval. And so forth. See http://www.sqlite.org/rtree.html
Since the 2010-06-25 source code repository update, the RTREE extension is
compiled by default within all supplied
.obj files of the
A dedicated ORM class, named
TSQLRecordRTree, is available to
create such tables. It inherits from
TSQLRecordVirtual, like the
other virtual tables types (e.g.
our custom virtual
2011, Wednesday June 1
By A.Bouchez on 2011, Wednesday June 1, 08:19
According to the n-Tier architecture,
data filtering and validation should be implemented in the business logic, not
in the User Interface.
If you were used to develop RAD database application using Delphi, you may
have to change a bit your habits here. Data filtering and validation should be
implemented not in the User Interface, but in pure Delphi code.
Data filtering is a process run on the User entry: for instance, it will
trim left or right spaces, make the text uppercase...
Data validating is performed before saving the data to the database: for
instance, an email address is checked for consistency, a field value to be
Some try to implement this using an external
scripting engine, in a procedure/event mode. Back to the 80th...
In our ORM framework, filtering and validation can be performed by creating
some Delphi classes, which may be used on both Server and Client side.
2011, Tuesday May 24
By A.Bouchez on 2011, Tuesday May 24, 22:58
How to handle authentication in a RESTful Client-Server architecture is a
matter of debate.
Commonly, it can be achieved, in the SOA over HTTP world via:
- HTTP basic auth over HTTPS;
- Cookies and session management;
- Query Authentication with additional signature parameters.
We'll have to adapt, or even better mix those techniques, to match our
framework architecture at best.
Each authentication scheme has its own PROs and CONs, depending on the
purpose of your security policy and software architecture.
2011, Saturday May 14
By A.Bouchez on 2011, Saturday May 14, 11:35
The SQlite3 engine has ability to create Virtual Tables from code.
From the perspective of an SQL statement, the virtual table object looks like
any other table or view. But behind the scenes, queries from and updates to a
virtual table invoke callback methods on the virtual table object instead of
reading and writing to the database file.
The virtual table mechanism allows an application to publish interfaces that
are accessible from SQL statements as if they were tables. SQL statements can
in general do anything to a virtual table that they can do to a real table,
with the following exceptions:
- One cannot create a trigger on a virtual table.
- One cannot create additional indices on a virtual table. (Virtual tables can
have indices but that must be built into the virtual table implementation.
Indices cannot be added separately using
- One cannot run
ALTER TABLE ... ADD COLUMN commands against a
- Particular virtual table implementations might impose additional constraints.
For example, some virtual implementations might provide read-only tables. Or
some virtual table implementations might allow
DELETE but not
UPDATE. Or some virtual table
implementations might limit the kinds of
UPDATEs that can be
Example of virtual tables, already included in the SQLite3 engine,
are FTS or
RTREE tables. A
custom virtual table might represent in-memory data structures (like
TSQLVirtualTableJSON, TSQLVirtualTableBinary). Or it might
represent a view of data on disk that is not in the SQLite3 format
TSQLVirtualTableLog). Or the application might compute the
content of the virtual table on demand.
Thanks to the generic implementation of Virtual Table in SQLite3,
you can use such tables in your SQL statement, and even safely execute a
SELECT statement with
JOIN or custom functions,
mixing normal SQLite3 tables and any other Virtual Table.
A dedicated mechanism has been added to the framework, beginning with
revision 1.13, in order to easily add such virtual tables with pure Delphi
code, just by inheriting some classes.
2011, Friday March 11
By A.Bouchez on 2011, Friday March 11, 22:06
Since Windows XP SP2 and Windows Server 2003, the Operating System provides
a kernel stack to handle HTTP requests. This
http.sys driver is in
fact a full featured HTTP server, running in kernel mode. It is part of the
networking subsystem of the Windows operating system, as a core component.
SynCrtSock unit can now implement a HTTP server based on
this component. Of course, our SQLite3 framework will use it. If it's not
available, it will launch our pure Delphi optimized HTTP server, using I/O
completion ports and a Thread Pool.
2011, Wednesday February 23
By A.Bouchez on 2011, Wednesday February 23, 21:43
The core of our SQLite3 framework is using JSON for all its Client/Server
communication, and also for the internal cache of previous SQL requests.
There is indeed a damn fast JSON generator and parser in the
I've written some functions able to serialize and unserialize not only
TSQLRecord (which is the main parent class of our ORM), but any
TPersistent class instance.
2011, Monday February 7
By A.Bouchez on 2011, Monday February 7, 20:56
The Synopse SQLite3 Database Framework was just released under version
- internal SQLite3 database engine was updated to version 3.7.5,
and SQL functions have been enhanced;
- now handle
prepared SQL statements with a neutral automated syntax;
- with Delphi 2009/2010/XE, you can define directly
Unicode string properties in TSQLRecord (will be stored
- include some overloaded methods using Variants for direct property
access (e.g. in any grid);
- SynPdf has now direct bookmarks and links methods, can reuse any
existing bitmap, and generate PDF/A-1
- internal reporting now supports bookmarks and
links (with on screen navigation), and better integrates with
- a lot of bug fixes, new methods and enhancements.
2010, Saturday December 18
By A.Bouchez on 2010, Saturday December 18, 14:57
Up to now, most SQL statements were parsed, then prepared before
Only individual TSQLRecord content retrieval was using prepared statements.
For the upcoming version 1.12 of the framework, we added an internal SQL
statement cache in the database access.
That is, if a previous SQL statement is run with some parameters, a prepared
version, available in cache, is used, and new parameters are bounded to it
before the execution by SQLite3.
In some cases, it can speed the SQLite3 process a lot.
2010, Monday December 13
By A.Bouchez on 2010, Monday December 13, 20:48
The Synopse SQLite3 Database Framework was just released under version
- internal SQLite3 database engine is updated to version 3.7.4;
- new TSQLRecordMany to handle "has many" and "has many through"
- new TSQLRecordFTS4 class, to handle the new FTS4 extension module;
- new RANK() function available in SQL statements for ranking FTS3/FTS4;
- introduces new TSQLite3DB, TSQLite3Statement, TSQLite3Blob, TSQLite3Value and
TSQLite3FunctionContext types to clarify SQLite3 internal handle usage;
- new sqlite3_busy_timeout and sqlite3_busy_handler low-level functions, with
new TSQLDataBase.BusyTimeout property;
- now handles User Defined Functions, via sqlite3_create_function_v2 and
corresponding sqlite3_result_* functions;
- new MOD() and SOUNDEX() functions available in SQL statements;
- a lot of code refactoring, speed improvements, numerous fixes, new
2010, Thursday October 28
By A.Bouchez on 2010, Thursday October 28, 20:37
Extracted from wikipedia, here is
a definition of 'many to many' relationships in regular database
In systems analysis, a many-to-many relationship is a type of cardinality
that refers to the relationship between two entities (see also
Entity-Relationship Model) A and B in which A may contain a parent row for
which there are many children in B and vice versa. For instance, think of A as
Authors, and B as Books. An Author can write several Books, and a Book can be
written by several Authors. Because most database management systems only
support one-to-many relationships, it is necessary to implement such
relationships physically via a third and fourth junction table, say, AB with
two one-to-many relationships A -> AB and B -> AB. In this case the
logical primary key for AB is formed from the two foreign keys (i.e. copies of
the primary keys of A and B).
In our SQLite3 framework, we just created a dedicated class for handling such
"many to many" relationships.
2010, Saturday October 23
By A.Bouchez on 2010, Saturday October 23, 09:52
The Synopse SQLite3 Database Framework was just released under version
- internal SQLite3 database engine is updated to version 3.7.3;
- code modifications to compile with Delphi 6 compiler;
- enhancements in TSQLRestServerStatic, for easier stand-alone work of this
in-memory database engine;
- new SQLite3Edit unit, for automated creation of a UI window, ready to edit
any TSQLRecord, with no RAD necessary (all components are created from RTTI):
think this is an ORM for User Interface.
This version compiles from Delphi 6 up to Delphi XE.