Synopse Open Source

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.

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

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

For both our SynOleDB and SynBigTable units, we allow late-binding of data row values, using a variant and direct named access of properties. Thanks to this unique feature (as far as I know in the Delphi database world),

This allows clear and valid code as such:

var Customer: Variant;
begin
  with Props.Execute(
    'select * from Sales.Customer where AccountNumber like ?',
    ['AW000001%'],@Customer) do
    while Step do
      assert(Copy(Customer.AccountNumber,1,8)='AW000001');
end;

In practice, this code is slower than using a standard property based access, like this:

    while Step do
      assert(Copy(Column['AccountNumber'],1,8)='AW000001');

But the first version, using late-binding of column name, just sounds more natural.

Of course, since it's late-binding, we are not able to let the compiler check at compile time for the column name. If the column name in the source code is wrong, an error will be triggered at runtime only. But it would not be an issue, since it would be the same for the SQL code inserted: it's only executed at runtime (this is one of the benefits of using an ORM, by the way: the ORM will generate correct SQL code for you...).

The default VCL implementation of this late-binding was a bit slow for our purpose.
Since it has to deal with Ole Automation, and because it's fun, we hacked the VCL to provide a lighter and faster version for our custom variant types.

Our ORM framework has been released as version 1.14.

It's mainly a bug-fix release:

• Integrated SQLite3 engine updated to latest version 3.7.7.1;
• Fix several issues about JSON generation layout;
• Enhanced automated User Interface generation for object on-screen edition;
• 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 SynOleDB unit.

The SQLite3 engine defines some standard SQL functions, like abs() min() max() or upper().
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 Delphi classes.

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

A dedicated ORM class, named TSQLRecordRTree, is available to create such tables. It inherits from TSQLRecordVirtual, like the other virtual tables types (e.g. TSQLRecordFTS3 or our custom virtual tables).

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 CREATE INDEX statements.)
- One cannot run ALTER TABLE ... ADD COLUMN commands against a virtual table.
- 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 INSERT or DELETE but not UPDATE. Or some virtual table implementations might limit the kinds of UPDATEs that can be made.

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 (e.g. 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.

Synopse Big Table is an open source Delphi unit for very fast data storage and access, using key/values pairs, or records organized with fields.

With this 1.12a version, the unit has evolved into a true field-oriented database, with two new classes:
- TSynBigTableRecord to store an unlimited number of records with fields;
- TSynBigTableMetaData to store any data (pictures, HTML, text) associated with metadata fields.

Both classes handle variable-length storage of integers, floats, currency, text (Unicode or not) with a field name. On-the-fly field adding, integrated indexing and search capabilities.
Data access can be either fast direct access, or via late-binding (i.e. use Record.Field in your Delphi code).

Classic Key/Value storage is always possible via TSynBigTable or TSynBigTableString, but is now faster and safer. A few issues were corrected.

Update: version 1.12b has been published (same download link).
Some issues have been fixed about packing and the two new classes types.

Up to now, most SQL statements were parsed, then prepared before execution.
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.

Extracted from wikipedia, here is a definition of 'many to many' relationships in regular database management:

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