Version 4.7 published

This version improves transaction handling by allowing an explicit ROLLBACK to terminate an SQL routine such as a store procedure. (COMMIT is not allowed inside a routine since it would cause premature termination of the routine.) New error messages for this behaviour means that the client libraries OSPLink and PyrrhoLink have been updated. Transaction behaviour is more transparent in the command prompt tool PyrrhoCmd, for example by the use of a special prompt SQL-T> inside transactions. Other fixes help with tables constraints, parsing of error handlers and default values for strings.

CQRS: Command Query Responsibility Segregation

There is currently a lot of interest in the above concept, e.g. at CQRS info, which basically looks at separating database reads (queries) from writes (commands) when considering scaability and replication. A June 2012 Channel 9 video in MSDn by Grigori Melnik and Julian Dominguez included the first picture here:

In 2010, in a presentation on "A cloud database with transactions" at DB Tech, Helsinki, I included the second picture here on how Pyrrho's cloud version could do the same thing:

The really important aspect here is the convergence between the ideas involved in long-running transactions and eventual consistency with the optimistic transaction model. It offers some hope in the current nightmare world of no-transactions/no-SQL nonsense. For Pyrrho's approach see my 2010 blog post.

Bitemporal data and SQL2011

Pyrrho 4.6 is uploaded today (and updated 12 May), which contains an implementation of system and application versioning as now enshrined in the December 2011 version of the SQL Standard. In fact, the SQL2011 standard does not quite get as far as supporting bitemporal data, but Pyrrho 4.6 does, and it is worth spending a little time to explain what is happening.

In a bitemporal table each row has 4 timestamps associated with it: a logical starting time for the data, a logical finishing time, and the system transaction times for creation and deletion of the row. An update operation involves creation of a row with the new values and deletion of the old one. Most of the time, we are only concrened with current data (i.e. the deletion time is in the future), but there should be mechanisms for accessing the historical data.

First, SQL2011 supports the idea of Period types. A period consists of a pair of base table columns of the same date type, with the first column implicitly constrained to be less than the second. There is a set of period predicates (succeeds, immediately precedes etc). A base table can have two periods defined: one for system time, and one for application time. However, the system-time columns are required to be defined, using new syntax, as "generated always as row start" and "generated always as row end". A base table with the system_time period defined can be declared as "with system versioning".

In ordinary use a system-versioned table appears to behave just like an ordinary table. All visible rows have the deletion time set to the maximum date (e.g. 31 December 9999). But if the table is referenced in a query, a system period can be specified, using new syntax: FOR SYSTEM_TIME AS OF .. , FOR SYSTEM_TIME BETWEEN ..., and FOR SYSTEM TIME FROM ... This allows table data to be viewed as it was at a previous time (the only difference being that the deletion time will no longer be the maximum date). The system also works even if the table has no primary key. However, im all cases the SQL2011 syntax limits the visibility of historical rows to a single time period.

Pyrrho has combined these new features with its own temporal tables concept to support a full implementation of bitempotal tables, temporal joins etc. As a start, in Pyrrho if T is a system versioned table that does have a primary key, T FOR SYSTEM_TIME (without the AS OF/etc specifications) is a temporal table whose row start column is the temporal key component, and the other components of T FOR SYSTEM_TIME's key are the primary key of T.

Secondly, if T has a primary key, and an application time period P (S,E) and the E column is generated always as row next, then Pyrrho allows T to be declared as WITH APPLICATION VERSIONING, and application time periods can be specified similarly to system time periods, by syntax such as FOR P AS OF/BETWEEN/FROM. This allows historical rows to be retrieved according to the application-defined time concept. In addition, T FOR P (without the AS OF/BETWEEN/FROM specification) is a temporal table whse temporal key is the first column of P.

A table can have both system and application versioning. In this way, although a bitemporal table has four timestamps associated with a row, only one of these needs to be set by the application, and the others can be managed automatically. (If you want to retian full control of the values of timestamps, then don’t specify automatic versioning: you can still use the SQL2011’s period predicates and Pyrrho’s temporal machinery.)

For example, consider the following bitemporal table:

Create table employee(
    name char primary key, 
    post char, 
    salary int, 
    effective date, 
    till date generated always as row next, 
    entered timestamp generated always as row start, 
    superseded timestamp generated always as row end, 
    period for system_time(entered, superseded), 
    period for details(effective, till)) 
    with application versioning 
    with system versioning

Insert into employee(name,post,salary,effective) 
values ('Joe Bloggs','Cleaner',9600,date'2006-01-07'), 
       ('Mary Smith','HR Director',58005,date'2006-02-01')

Update employee set post='Janitor',salary=10416,effective=date'2007-07-11' where name='Joe Bloggs'

Update employee set salary=61050,effective=date'2009-11-27' where name='Mary Smith'

Insert into employee(name,post,salary,effective) values ('Chris Black','Secretary',12345,date'2009-12-03')

Delete from employee where name='Joe Bloggs'

Then "table employee" contains just the current employees and their current posts and salaries:

We can get similar data as the table stood at a past point in time (this is using transaction times, for this sample data looks a bit strange):

It is perhaps more useful to filter for a past transaction time:

Finally we can work with the complete historical record if we wish:

A subtle point is that UDATE statements that change nothing are silently ignored. This is not specifid in SQL2011 but seems harmless enough. These extensions in Pyrrho only work on base tables. In principle they should work also on subqueries and this will be tackled soon in Pyrrho.

Accessing remote databases

Pyrrho now has quite a range of open-source embedded versions: for Windows, Windows Phone 7.1 Linux/Mono, Android, and Silverlight 5. There is also an embedded version of the standard edition. The whole purpose of an embedded DBMS is that the database is basically private to the application.

However, the application may also wish to contact other database servers. Pyrrho provided extended SQL syntax so that any REST service can be contacted from within SQL. An HTTP GET can be used anywhere a value is expected, and HTTP ADD, HTTP UPDATE and HTTP DELETE can be used anywhere a statement is expected. There is of course a url parameter, which is an SQL value, so expressions are allowed, and for ADD and UPDATE there is also a value to be used as data. Mime types are also supported. The full syntax for this feature is

Statement = .. | HTTP HttpRest .
HttpRest = (ADD|UPDATE) url_Value data_Value [AS mime_string]
| DELETE url_Value .
Value = .. | HTTP GET url_Value [ AS mime_string ] .

If HTTP GET is used in a context where the type is constrained (e.g. within an INSERT), Pyrrho will provide type conversion of the returned value to the expected data type. Otherwise the value is treated as a string.

This feature is available in all editions of Pyrrho, although currently Phone and Silverlight are not allowed to use the TCP API.

If the remote database is provided by a Pyrrho server, then access to it can be managed entirely within SQL, by arranging for the connection to access all of the databases concerned. For security, access from one Pyrrho server to remote servers must be set up as part of server configuration. But with embedded Pyrrho, a connection string such as “mylocaldb,corpdb@data.corp.com” could be used to contact a database corpdb on a company server in addition to the embedded database mylocaldb. Several local and remote databases can be accessed.

When the connection string gives a list of Pyrrho databases in this way, references to tables and other database objects are attempted on each database in the connection in order. Usually, any changes are only being made to the first named database. There are several semantic restrictions: schema changes require a single database connection, and local and remote databases cannot be mixed in the one query (but subqueries are okay).

As usual, let me know of any problems or unexpected behaviour.

Today's update

... has a server date of 12 March 2012 and fixes some bugs.

Another update

A rather bad bug fixed this time... Seems much better now. Keep those bug reports coming!

Linux support restored

New upload on 6 Jan 2012 fixes some important bugs and works for Linux (under Mono). If you find any bugs please email me.