Pyrrho DBMS

Linux support restored

2012-01-09T13:24:00.000-08:00

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

Data Visualisation

2011-09-24T07:23:00.000-07:00

I’ve made a start on adding some data visualisation facilities to Pyrrho. These will come built in to REST, and take advantage of the HTML5 canvas facilites. The REST service has been improving steadily, and now accepts posted text/csv data. But the Data Visualisation ideas are not quite such an obvious step.

There are lots of charting and data visualisation libraries, but adding a package such as Visiblox to Pyrrho would more than double its size (the Visiblox DLL is 797KB, while Pyrrho is 700KB). So for now I plan to code the extensions myself.

I have added a set of metadata flags for Tables and Columns that activate the charting code. As usual these are role specific so it is easy to imagine a data visualisation role, with multiple charts for tables. By design column metadata can also be added to views. The flags can be added using ALTER syntax, e.g.
alter table monthlysales series ‘Sales analysis by month’
alter table monthlysales alter mth X ‘Month’
alter table monthlysales alter total line ‘Total Sales $M’
alter table monthlysales alter recurring line ‘Repeat Orders $M’

The following flags have been added so far:

Output flag	Context	Effect
Pie	Table/View	Pie chart
Series	Table/View	Data Series
Points	Table/View	Scatter chart
Caption	Column	Column contains strings to annotate chart points
X	Column	Common column for series or X for X-Y plot. The plan here is that X could be a string, an int, or a real
Y	Column	Y column for points chart. Data should be int or real
Histogram	Column	For bar series chart: description string is for legend
Line	Column	For line series chart: description string is for legend

Unfortunately at present the Windows control used by the RESTClient does not support the HTML5 Canvas, so the results need to be displayed in an ordinary browser.I have made a start on implementation, as the graphic here shows. The axis ranges are selected automatically. So far the new facilities have required less than 20KB in the server, and less than 100 lines of script need to be downloaded to the client. A few more lines will be needed for the titles, multiple series and pie charts, and I hope to add these soon.

Using the WPF DataGrid

2011-09-12T11:06:00.000-07:00

I have no great solutions to this one. Placing data in a DataGrid is not hard. If you want to avoid writing your own Binding and Column definitions, we need to add getters and setters to the relevant class (this is okay).

Unfortunately using object[] as an ItemsSource is not good enough: we need to copy the objects into a List.

This is simple enough, but there is no easy way to support updates to the cells (though there are lots of tricky ways). On balance, with the facilities available in .NET 4, I recommend not using DataGrid. It is really at least as easy to add your own textboxes to an ordinary Grid, and while you are doing that it is easy to add LostFocus events to catch updates, and to keep a copy of the data so that you can see if it has changed. There is some code in PyrrhoSQL that will get you started.

I'd be interested in comments: is the above sample useful enough to get Pyrrho to generate the getters and setters? or to provide a verbose internal AUTHOR[] Get(db,rurl) {...} implementation in the generated class?

Application Programming with POCO

2011-09-11T08:27:00.000-07:00

At long last, Pyrrho has its own POCO technology, which (I modestly claim) is much neater and easier to use than ADO.NET, LINQ, JPA etc. It all links well to the role-based conceptual models and REST service described in previous postings.Recall that a REST url for Pyrrho starts with (http or https)http://host:port/Database/Role/Normally this is followed by table names and selectors. But a GET to this URL, returns a set of POCO definitions:

You can paste these class definitions into your application program. If this has been done for a MOVIE class in a database called Movies, the following simple code works:


using Pyrrho;
class Test
{
   public static void Main(string[] args)
   {
       var db = new PyrrhoConnect("Files=Movies");
       db.Open();
       var obs = db.Get("/MOVIE");
        foreach(MOVIE m in obs)
                Console.WriteLine(m.TITLE);
        db.Close();
   }
}

POCO stands for Plain Old CLR Object. The above example is using the following API in the PyrrhoConnect class:

Property or Method signature	Explanation
object[] Get(string rurl)	Return the set of objects selected by the given relative URL
void Get(object ob,string rurl)	The actual type of the object should match the first object returned by the URL. Fills the object with the data returned.
void Post(object ob)	Installs the given object as a new row in the appropriate base table.
void Put(object old,object ob)	The old object should have been obtained from the database using one of the Get methods above. ob should be an updated version of this: the database will be updated.
void Delete(object ob)	The object should have been obtained from the database using one of the Get methods above. The object will be deleted from the database.

Needless to say, all the REST-style machinery can be used in the GET rurls. When you paste class definitions obtained from Pyrrho, you can add other things to these classes provided what you add is not public (e.g. use internal instead).This feature will be available in tomorrow's update to Pyrrho (along with some bugfixes to v4.5).

REST and transactions

2011-09-08T02:44:00.000-07:00

Much has been said recently about transactions and databases in the cloud. We really need transactions, at almost any price, and Pyrrho supports long-running transactions for as long as a connection to the server stays open.
But Http is supposed to be a stateless protocol, and connections are closed on each request.

In recent years there has been a trend away from using session state as it creates server affinity and so is not scalable. One option is to use stored procedures to wrap several steps into an action that can be called using REST. Another is to offer transaction scripts, and these are available in Pyrrho v4.5 as described in this posting.

Recall that a REST URL in Pyrrho has the form
proto://host:port/database/role{/Selector|/Processing}

If no Selector or Processing components are provided, the target is the Role itself, to which a REST transaction script can be POSTed. This is envisaged for application use (not for an interactive user). Here is an example (POST to https://mybank:8133/MyBank/Teller):

(
(POST /CurrentAccount (AC:567123,AMT:655.43,REASON:’From Malcolm’))
(POST /CurrentAccount (AC:423991,AMT:-655.43,REASON:’To Fred’))
(COMMIT)
)

In this syntax embedded spaces in the url must be encoded as %20. In XML this could look like:

<?xml version=”1.0”?>
<root>
<POST Path=”/CurrentAccount” AC=”567123” AMT=”655.43”>
<REASON>From Malcolm</REASON>
</POST>
<POST Path=”/CurrentAccount” AC=”423991” AMT=”-655.43”>
<REASON>To Fred</REASON>
</POST>
<COMMIT/>
</root>

As usual with transactions, from within the transaction each step is seen to complete, but no external or persistent changes are made unless the COMMIT executes without conflict. The SQL syntax for a transaction script is thus:

Script = ‘(‘ ‘(‘ Step ‘)’ { [‘,’] ‘(‘ Step ‘)’ } ‘)’ .
Step = POST url Row_Value
| PUT url ‘(‘ Row_Value {[‘,’] Row_Value } ‘)’
| GET url
| DELETE url
| COMMIT .

REST and role-based CRUD operations

2011-09-06T12:45:00.000-07:00

This posting will demonstrate the use of the REST interface to perform updates, insertions and deletions in a database, where the URL structure, access permissions and navigation are determined by the roles in the database, rather than by application defined routing tables or configuration files. The default Pyrrho REST service from version 4.5 uses Windows authentication, and the server administrator has the option to use a secure channel for the http transport. The features apply in both the standard and Open Source version of Pyrrho (OSP).
We will use the following simple REST client (which is not Pyrrho-specific):

This screenshot shows the database as Library, the role as Library, and the table as BOOK (the REST interface is case-sensitive, and the default case in SQL is upper case).
Let us use PUT to provide better author names. With the permissions set up in previous postings, any user granted the Library or LIBRARIAN roles can do this. The roles are different as we will see below. We can use XML or SQL format for the data we supply. First let’s use SQL. The SQL output format for http://localhost:8180/Library/Library/AUTHOR/NAME gives (('Dickens')(‘Conrad’)), so we use the same format, naturally, to update the names:

If we use XML, things look a little different. Let’s look at BOOK with XML

Here the librarian has changed the names of some columns for the LIBRARIAN role, and marked book as an entity and id and aid as attributes. We can post a new book:

Notice that we don’t need to supply all of the fields (in this case it would be incorrect to supply values for the generated Author column). Pyrrho is forgiving about whether columns are supplied as attributes or children. But only one record can be POSTed at a time.
POST does not return data.
Checking, we see the new book has gone in with a generated key value of 1 (we could have given our own value).

Recall that this table looks very different in other roles:

Finally DELETE works. Provided the current role has the right permissions, the records that would be returned by GET are instead deleted.
Role names do not need to be meaningful, and using a hard-t-guess name for a role with extra privileges can be a useful extra layer of security.

REST and role models

2011-08-31T08:20:00.000-07:00

In this posting I want to discuss the new role-based REST service in Pyrrho v4.5. For simplicity we will continue to use the example database from the last two postings, with the following additional steps. These create a new role "Web" with read-only access to the tables, and some metadata for XML formatting of the output. The details are given at the end of this article.
For now let’s just consider GET actions. (As we will see in the next posting, one simple way of handling moderate security for PUT, DELETE and POST is to grant Insert, Update and Delete to a role with a hard-to-guess name.)
We can use REST to explore the Web role of the database:

For the Author table, AUTHOR has been declared as an entity, and ID has been declared as an attribute, so the output has a different form:

We can select by a primary key value

We can limit the output to particular columns:

We can select rows satisfying a particular set of conditions:

We can navigate foreign keys:

We can reverse-navigate foreign keys using the OF keyword (see below)

The syntax here is
http://host:port/database/role{/Selector|/Processing}
Selector matches
[table ]Table_id
[procedure ]Procedure_id
[where ]Column_id=string
[select ]Column_id{,Column_id}
[key ]string
[of ]Table_id[(Column_id{, Column_id})]
Appending another selector is used to restrict a list of data to match a given primary key value or named column values, or to navigate to another list by following a foreign key, or supply the current result as the parameters of a named procedure. The of option is used for reverse navigation of foreign keys, or to traverse many-many relationships.
Processing matches:
distinct [Column_id{, Column_id}]
ascending Column_id{, Column_id}
descending Column_id{, Column_id}
skip Int_string
count Int_string
The relationship of this Library database to the one in the last posting is (approximately) as follows:
In role Library:
Create role “Web”
Grant select on author to “Web”
Grant select on book to “Web”
Insert into book(title,aid) values(‘Great Expectations’,1)
In role “Web”:
Alter table author entity
Alter table author alter id attribute

Conceptual models in the physical database – II

2011-08-28T08:39:00.000-07:00

In the first part of this article, I set up a simple database to show how Pyrrho allows roles to change object names and define new ones. In this article we will take a deep dive into Pyrrho’s system tables to explore how the mechanisms work. (Later articles will explore more of the features of the new server version, including REST and entity models. The general reader can skip this article and wait for REST.)
The database creator has set up the following objects in the Library database: tables AUTHOR(ID,NAME) and BOOK(ID,TITLE,AID,ANAME). In the “Role$Object” system table we can see these objects as owned by the default database role, and the PUBLIC standard types that have been used: INTEGER and CHAR as required for these tables, and the BOOLEAN standard type that the librarian used for his new Availability column.

In this table we can also see that database objects can have other role-based metadata such as an output flag (this can be Entity or Attribute as we will see later), a human-readable Description, and an Iri for Web metadata.
In the corresponding tables for the other roles, we see different metadata for different sets of objects. The LIBRARIAN role renamed three of these objects, and defined the Availability column, and the READER role contains just a few entries. As at the end of the last blog posting, the database owner cannot use this role: it was created by the LIBRARIAN and had not yet been made public. Fred can get us the entries, and also make the role PUBLIC so anyone can use it.
Instead of looking at the Role$Object table for each role, let’s instead look at the Role$Column table: the first is for “Library”, the second for “LIBRARIAN”, the third for “READER”:

As expected, we see the librarian’s column names in the second two tables. But the biggest difference is the way that the Default value for the ANAME or Author column is shown. None of these exactly matches the actual definition used (select name from author a where a.id=aid). The first screenshot shows the identifiers capitalised, the second uses the LIBRARIAN’s name Authorid for the AID column, and in the third the code cannot be displayed, since the READER role does not know about the AUTHOR table. In fact, Pyrrho uses numeric identifiers internally (select “158” from “68” a where a.”119”=”277”), and, if possible, displays the code appropriately for the viewing role.
There are four blank columns in these tables Update can specify a set of actual assignments to be carried out if a generated column is assigned to. The next three columns are used for reverse relationship navigation and are specified using Pyrrho’s new REFLECTS syntax. These new features of Pyrrho were announced in July and will be described in future postings.
Let’s examine the list of users allowed to login to roles (this Sys$RoleUser table looks the same from any role allowed to see it):

“guest” appears in this list because Fred has ordered “grant reader to public”.
Finally in this posting I’d like to show some entries from what the database file actually contains. These are from the Log$ table.
The first shows where I create the LIBRARIAN role and grant all privileges on table AUTHOR with grant option. I’d have liked to crop this image to show only the lines from 570 to 643:

We can see that both these transactions are by Role=4, which is the default role for the database (always the first database object to be created). User=35 is TORE\Malcolm, the database creator. So role Library is defining the librarian role. Granting all privileges on the AUTHOR table, unless it is restricted to a particular set of columns, implies granting all privileges on all the columns too. And “all privileges” means all the privileges held by the grantor, in this case also all of the grant options. So the single SQL statement has been expanded into three Grant records in the database. 586, as we can see, refers to the Librarian. Generally, log entries refer to database objects by number rather than by name. Pyrrho has always done this because objects can be renamed. The mechanism now works really well for role-based naming, so that the new version is backwards compatible with existing Pyrrho databases.
The second extract is the last few entries in the log, from 878 on, where the user is Fred:

Here the user is Fred, and the role is LIBRARIAN. The transactions correspond to the four SQL statements:
alter table book add “Available” boolean default true
create role reader
grant select(“Title”,”Author”,”Available”) on book to reader
grant reader to public
The Grant NoPrivilege entry is probably not required, but at present it ensures that table BOOK (197) is entered in the namespace for READER (970). One other oddity in this list is where the “Avaialble” column is defined. The figures 197(4)[895] are for the table BOOK, the table’s owner, and the new column’s domain, which is a slightly odd collection of data to display in the Log entry (the table’s owner is not actually mentioned in the log entry).
There are some extra columns in the Log$ table: the record type, a related previous log entry if any, and the start of the transaction.

Conceptual Models in the Physical Database - I

2011-08-27T12:27:00.000-07:00

Having promised a progress report on the new role-based conceptual modelling features in Pyrrho, it is now time to post some news on this project. In this post I will walk through a simple database example. There is also a new Pyrrho client to show off.

Let’s start to build a simple database of books and authors.

This looks okay to a database specialist but the Librarian is not impressed. He wants the author’s name in the book table: after feebly trying to explain about joins, I provide a special generated column in this table using the standard SQL2008 syntax:

This pleases him a bit but he wants more reader-friendly names and to hide these numeric columns. So I add a new role for the Librarian, and allow Fred the admin option so he can define his preferred column headings:

(A generation rule in SQL2008 is not allowed to contain a query expression. Otherwise there are no Pyrrho extensions here.)
Fred can now log in to the system with his Librarian role. He decides to rename some columns (this is a Pyrrho extension), define a new column called Availability, and to create a role for his readers with a simpler table structure:

The only columns the Reader can see are the ones granted, so Reader can say simply “table book” to see these:

Note that the Author data comes from a table that is otherwise inaccessible to the Reader, because the generation rule uses “definer’s rights”.
Now this is how things stand. The database objects as viewed from the default “Library” role have not changed:

From the Librarian role we have:

and as we have seen the Reader does not see the numeric fields.
It is important (I think) that the renaming of columns is the only non-standard aspect here.
In the next posting we will look at how Pyrrho’s system tables reflect this structure. After that I plan postings on the resulting role-based REST service. The other promised Pyrrho features are on the way and I hope to offer the open source version of Pyrrho 4.5 within a week or two.

REST and role-based data models

2011-07-25T05:27:00.000-07:00

Despite the popularity of REST approaches, there is still no standard mechanism for automatically generating url patterns appropriate for a database. The early implementations from Microsoft had a very complex url syntax, and the later MVC3 approaches simply expect programmers to create routing URLs.
There is not even any agreed way of deciding whether a data value should be given as an attribute or a child element of a given entity. Many data modelling systems allow a distinction between data values and attributes, and this can help. Let us suppose that the data model system is extended by adding a small set of metadata flags to indicate which tables are entities and which columns are attributes or properties.
Then the following modest proposal is offered as a natural url model for a REST service:
All data segments are URLencoded so that SQL identifiers never need to be enclosed in double quotes. Matching rules are applied in the order given, but can be disambiguated using the optional $ prefixes. White space is significant (e.g. the spaces in the rules below must be a single space, and the commas should not have adjacent spaces).

http://host:port/database/role{/Selector}{/Processing}

Selector matches
[$table ]id
[$procedure ]id[(string{,string}]
[$where ]id=string
[$select ]id{,id}
[$key ]string
Appending another selector is used to restrict a list of data to match a given primary key value or named column values, or to navigate to another list by following a foreign key, or supply the current result as the parameters of a named procedure, function, or method.

Processing matches:
distinct
ascending id{,id}
descending id{,id}
skip Int_string
count Int_string

For example with an obvious data model, GET http://Sales/Sales/Orders/1234 returns a single row from the Orders table, while http://Sales/Sales/Orders/1234/OrderItem returns a list of rows from the OrderItem table, and http://Sales/Sales/Orders/1234/CUST/Customer returns the row from the Customer table.
A URL can be used to GET a single item, a list of rows or single items, PUT an update to a single items, POST a new item to a list, or DELETE a single row.
For example, PUT http://Sales/Sales/Orders/1234/DeliveryDate with posted data of 2011-07-20 will update the DeliveryDate cell in a row of the Orders table.
POST http://Sales/Sales/Orders will create a new row in the Orders table: the posted data should contain the XML version of the row. In all cases the new primary key value will be contained in the Http response.

Generated columns and updatable views

2011-07-25T05:11:00.000-07:00

When developing the conceptual level of a database, new structure can be added by using views and generated columns. The programming model for this in the SQL standard is theoretically complete, but in practical examples the standard machinery using triggers can seem over-complex.
Recent programming frameworks (e.g. .NET 4) have focussed instead on providing up to 4 pieces of SQL for the GET, PUT, POST and DELETE operations for any named structure. This suggests an extension to the CREATE VIEW syntax as follows:
CREATE VIEW id AS QueryExpression [UPDATE SqlStatement] [INSERT SqlStatement] [DELETE SqlStatement]
Similarly the syntax for a generated column can be extended:
GenerationRule = GENERATED ALWAYS AS '('Value')' [UPDATE '(' Assignments ')' ]
The implementation of these ideas is extremely straightfoward so they are good candidates for inclusion in the next version of Pyrrho.
More importantly, together with the following syntax extensions:
SqlStatement =| HTTP (POST|UPDATE|DELETE) url ['('Value')']
QueryExpression =| HTTP GET url .
we can see how a local database include or refer to data selected from an external database.
It is quite interesting to consider the effect of such extensions on the transaction model. One reasonable approach would be to recommend that for long transactions database designers should use Web-based transaction control as in JAX-RS. Another reasonable suggestion would be that such web requests should be tunnelled in duplex streams established for each web host referenced in the transaction, and this would be quite suitable for short transactions. For example, instead of the sequence
PUT http://myhost/mydb/url1 (+20)
PUT http://myhost/mydb/url2 (-20)
send
POST http://myhost/mydb/strtrans (PUT /url1 (+20) PUT /url2 (-20) COMMIT)

Reflection and Relationships

2011-07-09T02:35:00.000-07:00

Relational databases need to provide support two-way relationships especially now that such relationships are so easily navigated using REST-style URLs. This article proposes a simple approach to defining path navigation in the relational database at physical or conceptual level. The resulting generated columns (or properties) are of type ROW ARRAY and are quite difficult to use in SQL, but very easy from REST.
Interestingly the Java Persistence Query Language provides query language support for such two-way relationships, but required a lot of annotation infrastructure.
For example (inspired by the Java Tutorial), suppose we have tables
player: (pid int primary key, pname char)
team: (tid int primary key, tname char, capt int references player)
playerteam: (pid references player, tid references team)
This club has a number of teams for different purposes, and players can belong to (or even captain) more than one team. Then the syntax proposed here would allow us to write definitions as simple as
alter table player add captains reflects team
alter table player add playsin reflects playerteam
Then in the REST interface we can write
http://myclub.org/db/db/player(pname=’Fred Bloggs’)/captains/tname
to obtain the names of all the teams Fred captains.
The syntax is one of the alternatives for ColumnDefinition, and defines a generated-always column:
| id REFLECTS [Cols IN] Table_id [Cols]
Recall that ColumnDefinition is also used to define a property in a role-based data model, and of course in that context entity and property identifiers can be used in place of table and column identifiers.
The REFLECTS definition sets up an array-valued generated column as a reverse reference: e.g. if t.f references g(i) then “alter g add h reflects t” is a shorthand for “alter g add h row t array generated always as (select * from t where f=i)”. That is, each cell in the generated column contains a list of rows from the referenced table.
The optional [Cols] following the referenced Table_id allows selection of a particular foreign key if there is more than one.
For many-many relationships the referenced table will be a secondary table, so that the type of the generated column is defined only implicitly. Still the basic idea is simple enough. Suppose we have a many-many relationship R between tables A and B. In a relational database this will be implemented by means of a secondary table AB consisting of pairs of primary keys for A and B. In both A and B we can easily define additional generated columns for our convenience (e.g. for REST navigation):
alter table A add X reflects AB
alter table B add Y reflects AB
Then in each row of A, X will be a list of rows from table B; and in each row of B, Y will be a list of rows from table A. In practice we would probably name these properties B and A rather than X and Y to make the resulting REST-based navigation more intuitive.
Again, the optional column specification [Cols IN] allows selection of a particular foreign key in the secondary table. In the above player and team example we could define
alter table player add captains reflects team(capt)
alter table team add players reflects (pid) in playerteam(tid)
The full details of the [Cols IN] option are as follows. [Cols IN] is only permitted in relationships that navigate a secondary table. In a definition such as “alter S add C reflects (TF) in MM (SF)”, MM will be a secondary table implementing a many-many relationship between table S and some other table T, containing (at least) foreign keys SF and F where TF references the target table T and SF references the source table S. Then the values of C will be lists of rows of T.
The final [Cols] syntax allows selection of a foreign key that references the source table, and can be used in any REFLECTS construct.

On concurrency tokens

2011-07-06T12:43:00.000-07:00

Microsoft states for .NET Framework 4: "WCF Data Services supports the optimistic concurrency model by enabling you to define a concurrency token for an entity. This concurrency token, which includes one or more properties of the entity, is used by the data service to determine whether a change has occurred in the data that is being requested, updated, or deleted."
Now this is interesting as it suggests a growing realisation that more needs to be done about optimistic concurrency. But alas, all that is done here is to declare a property as useful for applications to test: it comes with no guarantees.
Pyrrho already has (mandatory) optimistic concurrency control, but still, it is an attractive notion to have a test to perform during a transaction ("is it already too late to commit?").
So from the next version (4.5) of Pyrrho, there will be a boolean property of transactions. Requesting the value of Transaction.Conflict will cause a server round trip to discover if other transaction commits have already made committing this transaction impossible.
Whatever the result of this test, the Transaction will remain open as it probably still contains useful information, until Commit or Rollback is called.

Role-based Data Models

2011-07-05T06:06:00.000-07:00

It does appear that Entity Frameworks loom ever larger in the data access world, whether they are used in a code-first or model-first way. But any database can support several data models.
It has been a common occurrence that Java programmers will use different data models (as defined by annotations in their program) to access the same database. This is because annotations can provide different entity and property names and map their relationships in different ways. Pyrrho (up to version 4.4) therefore regarded the data model in use as a thread-specific structure. This now seems quite the wrong thing to do.
Instead, Pyrrho now takes its cue from the business analytics and business modelling field, and considers that it will be best practice for each role to have a different view of the data in the database, and it is at such a conceptual model level that entity modelling should take place.
So, from version 4.5 of Pyrrho, a data model will be regarded as a property of a Role. The physical database defines a default data model, and an associated default Role. This default Role treats each base table with a primary key as an entity, and each column of such a table as a property, and allows navigation using foreign keys. Multi-column keys are handled as lists whose property name is the reserved word KEY.
[Updated 2 August: Users with admin access to a role can create new database objects for their role. Granting access to database objects for a Role copies the necessary entitiy and property definitions into the Role's data model. The data model that is applied is determined by the current Role: this is initially set in the connection but can be changed during the session to any other role that the user has been granted.]
The data model can be modified for a Role by hiding entity types or properties, or defining entity types and properties by renaming inherited entities and properties tables or views and their columns. Descriptive text can be associated with the entity and property definitions. An additional syntax for ALTER ROLE has been created for providing this facility.
This allows data models to have more user-friendly names and allows Roles to be localised for different languages. Pyrrho 4.5 enhances generated columns by introducing updatable generated columns in addition to updatable views so additional derived columns can be added to the database schema where required for making data models more natural. It is a feature of Pyrrho that such derived columns are only computed when required, so there is little overhead in using them.
[Added 2 August: The SQL contained within e.g. stored procedures executes under definer's rights. Although there is only one copy of the SQL in the database, a developer viewing the SQL will see a version adapted to their current data model. The SQL will execute with definer's rights, although the code will reflect the user's data model. The resulting dependencies between roles and their data models are tracked by Pyrrho, and the usual restrict/cascade protocols apply to changes to the data model schema.]
The data model can be retrieved from the database is the usual way in EDMX format, and will determine the URL mapping used in Pyrrho's new secure REST service. SQL will of course continue to be applied at the level of base tables. Pyrrho's implementation of LINQ and Java Persistence will also use the new role-based data models: for these APIs the change will only be noticed if a Role is specified in the connection string.
In Pyrrho, strong types, including types with embedded metadata, will continue to be supported in the physical database. The new derived column machinery can present the same physical data as different types as appropriate for role-based data models.
Over the summer I hope to publish more articles on the version 4.5 goodies, including some better support for role base security.

A Database for the Windows Phone

2011-03-05T11:16:00.000-08:00

The purpose of this demonstration is to introduce the embedded Pyrrho engine that is now available for the Windows Phone 7. This uses Web local storage on the client device to hold any databases used.
The demo application is a simple persistent shopping list, and for simplicity only addition and deletion of items is supported. The application has a reference to PhoneOSP.dll. This document contains step-by-step instructions to build this complete application. You will need Windows Phone developer tools, which you can get from Microsoft
and the PhoneOSP.dll from the Pyrrho distribution.
The new project
1. Open Visual Studio 2010, File>New>Project.. In the New Project dialogue box, find the Silverlight for Windows Phone entry under Visual C#, ensure that .NET Framework 4 is selected, and select Windows Phone Application. Set the name of the application to ShoppingList, and the Location to somewhere you control, such as C:\Temp. Select OK.
The Xaml Markup

2. Position the mouse cursor just after “LayoutRoot”, and type a space and H. From the Intellisense dropdown, double-click HorizontalAlignment and then Left. Similarly add VerticalAlignment=”Top”.
3. Position the mouse cursor at the start of the blank line between <Grid ..> and </Grid> . Type an opening pointy bracket < and from the IntelliSense dropdown, double-click StackPanel. Type a closing pointy bracket >.
4. The cursor is now between <StackPanel> and <StackPanel> . View>ToolBox, and double-click ListBox. Change the Height attribute of the ListBox to 350 and the Width to 400.
5. Place the cursor just before </StackPanel> again. Type <S and from the IntelliSense dropdown, double-click StackPanel and type > .
6. Place the cursor just before the closing pointy bracket of the new <StackPanel> element. Type a space followed by O. Double-click Orientation, and then Horizontal.
7. Move the cursor to just before the first </StackPanel>, and double-click TextBox from the Toolbox. Set the width property to 240.
8. Now double-click Button in the Toolbox . In the Properties window, change the name of the Button to bAdd, set the Content property to Add, and press the Enter key.
9. Move the mouse cursor to just before the second </StackPanel> and select Button from the ToolBox. Change the name of the button to bDEl and the Content to Delete Selected. Change the Width to 100. Now select Debug (the green arrow in the toolbar). The application should display as shown in a browser. Close the browser window.
The Shopping class
10. If you want to know how Pyrrho works, you can add the source code for Pyrrho by right-clicking the Solution, and Add PhoneOSP as an Existing Project…., and browse in to PhoneOSP\PhoneOSP to open PhoneOSP.csproj.
11. Right-click on References in the ShoppingList Project, select AddReference.. If you have just done step 10, use the Projects tab to add PhoneOSP. If not, Browse to add PhoneOSP.dll.
12. In the Solution Explorer, right-click on the ShoppingList project and select Add> Class. In the Add New Item – ShoppingList , given the name as Shopping.cs, and select Add. The Shopping.cs file opens in the designer.
13. Change the Shopping.cs file to contain the following:

using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Collections.ObjectModel;
using Pyrrho;

namespace WindowsPhoneShopping
{
    public class Shopping
    {
        public PyrrhoConnect conn = null;
        MainPage main = null;

        public Shopping(MainPage page)
        {
            main = page;
            FillData();
        }
        void FillData()
        {
            try
            {
                conn = new PyrrhoConnect("Files=Shopping");
                conn.Open();
                PyrrhoCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from Items";
                PyrrhoReader rdr = null;
                try
                {
                    rdr = cmd.ExecuteReader();
                }
                catch (Exception)
                {
                    rdr = null;
                }
                if (rdr == null)
                {
                    conn.Act("create table Items(Name char primary key)");
                }
                else
                {
                    while (rdr.Read())
                    {
                        main.listBox1.Items.Add(rdr.GetString(0));
                    }
                    rdr.Close();
                }
            }
            catch (Exception) { }
        }
        public void Refresh()
        {
            main.listBox1.Items.Clear();
            FillData();
        }
    }

}

Check this still runs as before. Close the browser window.
Adding code to add and delete items
14. In the MainPage.xaml markup, place the mouse cursor in the Grid element. In the Properties window, select the Events tab, and double-click the Loaded event. The MainPage.xaml.cs file opens and you should add the following lines to the LayoutRoot_Loaded event handler:

shopping = new Shopping(this);
            textBox1.Text = "";

15. Still in the MainPage.xaml.cs file, position the mouse cursor just inside the first curly bracket, and enter

Shopping shopping = null;

16. In the MainPage.xaml designer, double-click the Add button. The MainPage.xaml.cs file opens in the designer window. Insert the following code in the the bAdd_Click method (between the curly brackets):

try
    {
        shopping.conn.Act("insert into Items values('" + textBox1.Text + "')");
        shopping.Refresh();
        textBox1.Text = "";
     }
     catch (Exception) { }

17. In the MainPage.xaml designer, double-click the Delete Selected button. When the MainPage.xaml.cs file opens again, insert the following code in the bDel_Click method:

try
     {
         var it = listBox1.SelectedItem as string;
         if (it != null)
            shopping.conn.Act("delete from Items where name='" + it + "'");
         shopping.Refresh();
      }
      catch (Exception) { }

18. The application should now run.

A Silverlight OSP Tutorial

2011-03-04T11:26:00.000-08:00

The purpose of this demonstration is to introduce the embedded Pyrrho engine that is now available for Silverlight 4. This uses Web local storage on the client machine to hold any databases used.
The demo application is a simple persistent shopping list, and for simplicity only addition and deletion of items is supported. The application has a reference to SilverlightOSP. This document contains step-by-step instructions to build this complete application. You will need the Silverlight 4 SDK and Tools for Visual Studio 2010, which you can get from www.silverlight.net, and the SilverlightOSP.dll from the Pyrrho distribution.

The new project

1. Open Visual Studio 2010, File>New>Project.. In the New Project dialogue box, find the Silverlight entry under Visual C#, ensure that .NET Framework 4 is selected, and select Silverlight Application. Set the name of the application to ShoppingList, and the Location to somewhere you control, such as C:\Temp. Select OK.

2. In the New Silverlight Application dialogue box, accept the defaults and click OK. The designer for MainPage.xaml opens.
The Xaml Markup
3. Position the mouse cursor just after "LayoutRoot", and type a space and H. From the Intellisense dropdown, double-click HorizontalAlignment and then Left. Similarly add VerticalAlignment="Top".
4. Position the mouse cursor at the start of the blank line between <Grid ..> and </Grid> .
Type an opening pointy bracket < and from the IntelliSense dropdown,
double-click StackPanel. Type a closing pointy
bracket >.
5. The cursor is now between <StackPanel> and <StackPanel> . View>ToolBox, and double-click DataGrid. Change the Height attribute of the DataGrid to 150 and the Width to 200.
6. Place the cursor just before </StackPanel> again. Type <S and from the IntelliSense dropdown, double-click StackPanel and type > .
7. Place the cursor just before the closing pointy bracket of the new <StackPanel> element. Type a space followed by O. Double-click Orientation, and then Horizontal.
8. Move the cursor to just before the first </StackPanel>, and double-click TextBox
from the Toolbox.
9. Now double-click Button in the Toolbox . In the Properties window, change the name of the Button to bAdd, set the Content property to Add, and press the Enter key.
10. Move the mouse cursor to just before the second </StackPanel> and select Button from the ToolBox. Change the name of the button to bDEl and the Content to Delete Selected. Change the Width to 100. Now select Debug (the green arrow in the toolbar). The application should display as shown in a browser. Close the browser window.

You can see the MainPage.xaml markup: here

The Shopping class

11. If you want to know how Pyrrho works, you can add the source code for Pyrrho by right-clicking the Solution, and Add SilverlightOSP as an Existing Project…., and browse in to SilverlistOSP\SilverlightOSP to open SilverlightOSP.csproj.
12. Right-click on References in the ShoppingList Project, select AddReference.. If you have just done step 11, use the Projects tab to add SilverlightOSP. If not, Browse to add SilverlightOSP.dll.
13. In the Solution Explorer, right-click on the ShoppingList project and select Add> Class. In the Add New Item – ShoppingList , given the name as Shopping.cs, and select Add. The Shopping.cs file opens in the designer.
14. Change the Shopping.cs file to contain the following:

using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Collections.ObjectModel;
using Pyrrho;

namespace ShoppingList
{
  public class Shopping
  {
     public ObservableCollection ItemsRequired { get; set; }
     public static PyrrhoConnect conn = null;
     public static Shopping theList = null;

     public Shopping()
     {
        ItemsRequired = new ObservableCollection();
        theList = this;
        FillData();
      }
      void FillData()
      {
        try
        {
           conn = new PyrrhoConnect("Files=Shopping");
           conn.Open();
           PyrrhoCommand cmd = conn.CreateCommand();
           cmd.CommandText = "select * from Items";
           PyrrhoReader rdr = null;
           try
           {
              rdr = cmd.ExecuteReader();
           }
           catch (Exception)
           {
              rdr = null;
           }
           if (rdr == null)
           {
              conn.Act("create table Items(Name char primary key)");
           }
           else
           {
              while (rdr.Read())
              {
                 ItemsRequired.Add(new ItemToBuy()
                     {
                       Name = rdr.GetString(0)
                     });
              }
              rdr.Close();
           }
        }
        catch (Exception) { }
      }
      public void Refresh()
      {
        ItemsRequired.Clear();
        FillData();
      }
   }
   public class ItemToBuy
   {
      string name;
      public string Name { get; set; }
   }
}

15. Check this still runs as before. Close the browser window.
16. Return to the MainPage.xaml designer by double-clicking its tab. Place the mouse cursor just after mc:ignorable=”d” and enter xmlns:my="clr-namespace:ShoppingList"
17. Place the mouse cursor just before <Grid and paste the following code:
<UserControl.Resources>
<my:ShoppingManager x:Key="shoppingListViewSource" />
</UserControl.Resources>
18. Place the mouse cursor after “LayoutRoot” and paste the following:
DataContext="{Binding Path=ItemsRequired, Source={StaticResource shoppingListViewSource}}"
19. In the DataGrid markup, change the value of AutoGenerateColumns to True.
20. Place the mouse cursor just after "True" and paste the following:
HeadersVisibility="Column" ItemsSource ="{Binding}"
21. Notice that the DataGrid already shows the Name column header. Run the application. Close the browser window.
Adding code to add and delete items
22. In the MainPage.xaml designer, double-click the Add button. The MainPage.xaml.cs file opens in the designer window. Insert the following code in the the bAdd_Click method (between the curly brackets):
try
{
Shopping.conn.Act("insert into Items values('" + textBox1.Text + "')");
Shopping.theList.Refresh();
textBox1.Text = "";
}
catch (Exception) { }
23. In the MainPage.xaml designer, double-click the Delete Selected button. When the MainPage.xaml.cs file opens again, insert the following code in the bDel_Click method:
try
{
var it = dataGrid1.SelectedItem as ItemToBuy;
if (it != null)
Shopping.conn.Act("delete from Items where name='" + it.Name + "'");
Shopping.theList.Refresh();
}
catch (Exception) { }
24. Now test out the application. You should be able to add and remove items using the buttons. (To add an item, type its name in the textbox and click Add.)

Silverlight and OWL2

2011-03-01T07:45:00.000-08:00

Version 4.4 of PyrrhoDBMS is released today, with support for OWL 2 subtypes and Silverlight applications.
I am particularly excited by the Silverlight version. This is a dll that is included in your Silverlight project, and via inclusion in the .xap file, runs on the client machine, using secure local storage in the client machine for the database(s) used by your application. I really feel this brings a whole new perspective on persistence, since the application can organise the data flexibly using SQL. Full SQL is available (apart from the xmlquery primitive) in a compact engine (600KB !).
Okay, I've removed a few previous technologies in this edition. Many people thought I was mad to include SPARQL in a DBMS, and this has now been removed, as have the built-in HTTP services, and the datamodel machinery.
I will publish some samples for both of the above innovations on the PyrrhoDB web site in the next few days. So much has changed that there are bound to be some bugs. For the moment the download pages also provide previous versions of the PyrrhoDBMS. Please let me know if anything strange happens for you.
UPDATE 2 March: The 1 March versions had a serious bug affecting Alter table add, with database corruption. The 2 March versions fix this problem.

Library update

2010-10-11T00:28:00.000-07:00

Today's updates are to the PyrrhoLink and OSPLink libraries, to fix a bug in code from 17 Sept.

Adding a Mono version

2010-09-24T03:28:00.000-07:00

Today's update includes some version compiled for Linux.

Transaction Profiling

2010-09-10T06:44:00.000-07:00

Today's update fixes some mathematical functions that the parser didn't recognise, and makes some improvements to threading.
There is also some new support for transaction profiling, to be discussed in a paper I am writing.

Fix to ordering

2010-06-02T01:24:00.001-07:00

Today's update fixes a problem with rowset ordering.
Keep me posted on any performance issues.

SQL2008 Compliance

2010-05-24T06:00:00.000-07:00

Belatedly perhaps, the version released today has been updated to address SQL2008 compliance. The main change is the new FETCH FIRST n ROWS ONLY clause that can be added in any top-level query expression (usually in combination with an ORDER BY clause).
Please advise me of any performance issues.

Groups and Window Functions

2010-04-26T15:41:00.001-07:00

Today's fix is for the advanced window functions.
Email me if you find anything that does not work.

Java Persistence Library

2010-04-01T05:35:00.000-07:00

The Open Source Edition has been updated to fix an error in the Java Persistence implementation org.pyrrhodb.* (PyrrhoJC.jar).
Please report any performance issues to malcolm@pyrrhodb.com .

Fixing Date Types

2010-03-29T09:04:00.001-07:00

Today's fixes relate to the date type, which contained some serious errors.
Keep me posted on any performance issues.

Version 4.0 is released

2010-02-26T01:02:00.001-08:00

Pyrrho DBMS Version 4.0 is now available. There are three editions, all free to use: Standard, Embedded and OpenSourcePyrrho (OSP). As before, OSP contains an implementation of Java Persistence Architecture v1.0.
The Standard and OSP editions include deployment options for taking advantage of cloud computing technologies, following the design of my December 2009 blog post. There is also an advanced implementation of subtypes as I believe they can be very useful in identifying special features of individual data values (similar to annotations in Excel, or footnotes in documents). I expect this sort of feature in databases to become more important with the increasing interest in provenance and digital curation.
A lot of the code in Version 4.0 is new, so I will be continuing to test this version and updates should get posted regularly. I look forward to receiving feedback on any performance issues and on the concepts introduced in this version.

Pyrrho and the Cloud

2009-12-10T13:11:00.000-08:00

A number of writers, considering the poor support for transactions and locking support, have concluded that cloud storage might not be suitable as a data store for the purposes of most line-of-business applications (Chantry 2009), (Abadi, 2009), (Wei et al, 2009). Brantner et al (2009) reviewing the support for databases in the cloud, note that although some services are quite powerful, they might not directly satisfy the requirements for a given task. In particular, storage providers usually give only relaxed consistency guarantees, and storage services are “orders of magnitude” slower than locally attached disks. Abadi (2009) discusses the limitations of the “simple databases” offered by cloud services such as Amazon and Yahoo, noting that they simply relax the ACID requirements for transactions and do not guarantee consistency.
The different cloud services providers differ most of all in their choice of implementation language. Microsoft’s Windows Azure platform, based on .NET and its language set, notably C#, provides three sorts of storage: Blobs, Tables, and Queues, which are accessible either using Web Roles (from IIS) or from Worker Roles (from Azure). Azure hosted services can also be accessed using WCF and REST, but so far I haven’t been able to access Azure Storage by this route. Distressingly, the web pages generated to access Azure storage using the November 2009 samples seem to have a lot of hidden authorisation stuff, and I haven’t been able to generate my own client applications to consume Azure Storage data. It is all still clearly under development, and is only due for release in 2010.
Still, I am sure that before too long we will be able to use WCF Streaming from Azure storage, so I am assuming this in the following design ideas.
Many authors draw attention to the CAP theorem whereby a shared-data system can choose at most two out of the three properties: consistency, availability, and tolerance to partitions. DBMS designers usually insist on consistency at all costs. For this reason, we will end up with a solution that retains a transaction master server for each database, while taking advantage of the cloud for data replication and transaction routing. The paper by Wei et al (2009) presents essentially the same solution as the one proposed here.

Pyrrho’s Binary Storage Architecture
Pyrrho operates a layered architecture described in the documentation using the diagram shown. In this diagram the bottom two layers represent durable storage (e.g. file storage on the hard disk). It is natural to focus on using Cloud storage as an alternative form of durable storage, and the concept of a logical file consisting of binary segments, all but the last of which will never require updating, makes Pyrrho particularly attractive for such a design.

In fact, the original design of Pyrrho envisaged completely immutable file data, where each committed transaction would simply append new durable data to the file.
The current physical design of these two layers is not ideally suited to use with the cloud however. In the first place, there is a tamper-proofing end-of-file marker which is overwritten by the next transaction commit. Secondly, the segment size used on the hard disk is measured in gigabytes, which is too large for current internet implementations. Finally, there is exactly one disk write for each transaction commit.
Taking this last point first, all of Pyrrho’s competitors, when committing a transaction finally to disk storage, write in many places in the disk file, so that multiple disk writes are required. Measurements of disk traffic show that under heavy transaction loading, Pyrrho uses fewer disk writes by a factor of around 70 compared to commercial DBMSs. Nevertheless, the default transaction mode used is that each SQL statement will autocommit, so that there is approximately one disk update for each INSERT/UPDATE/DELETE statement. If cloud storage is used, there will be one server round-trip corresponding to each storage change: this strongly motivates the use of explicit BEGIN TRANSACTION and COMMIT if many changes are to be made, as this would tend to group associated modifications in a single batch. It is an obvious first step towards implementation to modify Pyrrho’s autocommit policy so that by default, transactions are committed when a connection is closed rather than at completion of each statement. This will have the effect of adding to cloud storage in larger chunks, and this seems to point to a natural solution for the segment size issue mentioned in the second point.
For the segment size of cloud storage, there is obviously a balance to be struck between the practical size of a binary transmission and the number of connection requests managed by the network. Assuming an efficient streaming implementation, which we will come to in a moment, the natural thing to do would be to store the data from each transaction in a new segment to be committed to cloud storage.
This leaves the question of the end-of-file marker, which we no longer need. Its purpose was to ensure that the contents of the entire data file had not been changed since they were committed. Instead, we simply store the same marker data in the cloud storage along with the associated segment: the marker can be used to check the entire data file up to that point.

Following this step, the Pyrrho DBMS server can operate using local (disk) storage and remote (cloud) storage. So far, this model could be conveniently hosted by a simple REST/bytestream service such as Amazon S3. Older parts of the database can be replicated without loss of consistency. We consider next how we can ensure that the data is up-to-date.
Transaction Serialisation
Pyrrho’s transaction design is actually very well suited to sharing cloud storage between database engines, because its transaction serialisation handling is performed at the level of the binary records (“Physical”s) that are written to the durable storage when a transaction commits. Pyrrho uses optimistic locking, which is also very well suited to use with cloud storage, since it would be a bad idea to have an exclusive lock on the Cloud service.
For a proper understanding of the mechanism, it is necessary first to mention that the tree and queue data structures managed in the Logical Database layer have the property that all their elements are immutable: any change results in a new head structure, and following links through new components of the data structure usually leads to components of the original structure.
So, at the start of a transaction, new headers for the database(s) involved are created, which initially lead to the current committed state: if a few transactions are in progress, there will be several versions of the database(s), mostly coinciding (on unchanged portions). The changes associated with the transaction each have a Physical which will be written to durable storage along with a descriptor of the transaction itself.
Whether such ongoing transactions conflict can be determined by examining just these Physicals. For example, two updates to the same row of a table will conflict, an insert in a table will conflict with dropping the table, etc. This means that transaction serialisation can be determined at the Physical Records level of the database.
There are two subtleties to the resulting design, which apply equally to local and remote storage. The first relates to relocation of Physicals, the second to transaction conflicts where reads conflict with changes.
The relocation issue arises because Pyrrho uses the (immutable) defining addresses of data in Physical records: so that for example a Column will refer to its parent Table by giving its defining address rather than its (mutable) name. For this reason a group of Physicals in a transaction are very likely to refer to each other using their positions in the data file. While transactions are in progress, nobody can tell which transaction will commit first, so the positions of Physicals in the data file are generally not known until the Commit succeeds. The physical layer of the DBMS therefore relocates the transaction to its final place in the data file.
The read-conflict issue is rather pedantic. Pyrrho uses a very strict concept of transaction isolation, and notes all data that a transaction reads. If any of this data changes before the transaction commits, Pyrrho will roll back the transaction. For example, if a transaction computes a value to insert in the database, it should be invalidated by changes to any values used in the computation. Pyrrho calls these ReadConstraints.
Similarly, referential constraints (called IndexConstraints in the code) must also be checked again at Commit time, in case another transaction has inserted a record with a conflicting unique key, or deleted a record that our transaction intends to refer to.
For this reason, the data used in the Commit process consists of the list of proposed Physicals together with sets of ReadConstraints and IndexConstraints. For a remote database. It is this collection of data, and not the final binary data, that must be sent to the remote master server for processing.

Unfortunately, we are left with the situation that serialisation must ultimately be decided by a single DBMS server. The most we can use the cloud for in transaction management is as an enterprise system bus, routing transaction streams to the transaction master. To ensure that we have read the most up-to-date version, we simply call commit: if it is not up to date, our transaction will fail, and re-running the query will get its new value.
The Data Stream
The upper layers of the DBMS still need to construct and maintain the logical database for doing query processing, joins etc, and for this purpose, as with local databases, the complete data stream of Physicals must be read at some stage. For a large database, this is a time-consuming process (roughly 1 minute per 200MB) so should only be carried out when a cold-start of the DBMS occurs. Thus the DBMS itself is too stateful to be placed in the cloud.
On the other hand, once the DBMS is up to date with all of the Physicals that have committed to the local or remote data file, it is relatively easy matter to check for transactions committed by other servers, at the start of a transaction and again at transaction commit. The protocol for obtaining this Physical data must be able to deal with both small and large volumes of data, and take into account that Physicals can vary greatly in size. A binary large object (blob) for example, is committed in a single Physical.
Pyrrho already has excellent mechanisms for serialising such diverse binary data, with the AsyncStream class. This streaming mechanism can be used directly to deal with the Physical data from cloud storage. It is important to be able to use streamed responses from the cloud service, though, as it would be unacceptably slow to recreate repeated connections for downloading such data piecemeal. The AsyncStream class is also a useful mechanism for uploading to the cloud service the data described in the last section. It also supports a mechanism for reporting any exceptions thrown by the cloud service, which would most likely be transaction conflicts.
Nevertheless, a local initialisation device (such as an SD card) containing a copy of a database up to a certain date would seem an obvious way to save a lot of time, particularly if the remote database is often used.
Distributed Databases
Pyrrho maintains a single logical file for each database, which as we have seen might comprise a number of segments. This has the advantage that ordinary operating system utilities can be used for backing up, moving or renaming databases.
Pyrrho supports multi-database connections, where one DBMS can combine data from several databases in response to a query. A two-phase commit protocol must be used to ensure that all of the participating databases succeed in committing the data, or all roll it back. Pyrrho does not support multi-database relationships: it is not possible to have a foreign key relationship to a table in another database, since references to another database would be vulnerable to the sorts of file operations just mentioned. Whenever a multi-database connection is used, Pyrrho checks that all distributed transaction commits recorded in the databases are reflected in all of the databases in the connection.
In the simplest cases the DBMS has at its disposal the logical database structure for all of the databases involved in the connection, either because the data comes from local durable files under its control, or has been synchronised from another DBMS or the cloud service. In the latter case, there may be a local file or device that contains a copy of the data for initialisation purposes. Another DBMS is involved in cluster computing scenarios, where only one member of the cluster is given write access to the data file for each database, but all members maintain all the logical databases. This mechanism, can be used effectively to partition data among servers so that transaction masters for each partition of the data collaborate to ensure the ACID properties of the transaction.
Remote Databases
With the introduction of cloud storage, and with the increasing use of smaller devices as servers, it becomes important to extend the storage modes available for remote data, to include the case where, although the local server needs to know the schema for all databases in a connection, it does not have row or index information for remote databases. The logical databases all need to exist somewhere, of course: in this case the remote database needs to be managed by a remote DBMS, not just by the cloud service. During query processing, the local server constructs a query for the remote DBMS which includes join, projection and selection aspects to minimise the amount of traffic required, and it is only when there is local data to be combined with the resulting rowset(s) that the local DBMS actually needs to do much work.

It will be seen that this is a rather different matter than simply using cloud storage. Which approach is preferable will depend on whether the memory of the local device is capable of holding the whole of the remote logical database, and how frequently consultation of the remote database is really required.
As a result of these developments, in the next edition of the Pyrrho DBMS, any server can have a configuration file which specifies which databases are available from specific servers or cloud storage (and how to access them): and the cloud service needs to refer all transactions to their transaction masters. In addition, the configuration file should explain for remote databases whether the local database maintains the full logical database, and if so, whether there is a local initialisation file.
“Eventual Consistency”
It seems reasonable for read-only transactions to appeal to the eventual consistency of cloud computing services. A client can always COMMIT a read-only transaction if they wish to be warned about a recent update that might have invalidated their work; but this is of limited value since a further update can always occur before they commit their next transaction. It is best for the reasons given above to transact reads with their resulting updates. For simplicity we have stayed with a single transaction master per data item, although a majority voting (Byzantine) system would be theoretically possible.
Conclusion
This paper has outlined a manifesto for a future version of Pyrrho DBMS supporting cloud storage and the use of small devices as local DBMS servers. We agree with other published studies that cloud storage on its own would violate several key database principles and so remains unsuitable for transactional data management. It is pleasing that this approach is corroborated by the work reported in Wei et al (2009).
References
Abadi, Daniel (2009): Data Management in the Cloud: Limitations and Opportunities, Bulletin of the IEEE Computer Society, 32 (2009), p.3-12.
Brantner, Matthias; Florescu, Daniela; Graf, David; Kossmann, Donald; Kraska, Tim (2009) Building a Database in the Cloud, Technical Report, ETH Zurich, www.dbiz.ethz.ch
Chantry, Darryl (2009): Mapping Applications to the Cloud, The Architecture Journal, 19 (2009), p.2-9 (Microsoft, www.architecturejournal.net)
Crowe, Malcolm (2007): The Pyrrho Database Management System, Computing and Information Systems Technical Reports No. 38 (University of the West of Scotland) www.pyrrhodb.com
Wei, Zhou; Pierre, Guillaume; Chi, Chi-Hung (2009) Scalable transactions for web applications in the cloud, Proc 15th International Euro-Par Conference on Parallel Processing, LNCS 5704, p.442-453 (Springer)

Embedded Pyrrho

2009-11-21T06:14:00.000-08:00

The main effect of today's updates is to make Embedded Pyrrho available again.

Bugfixes

2009-11-07T00:03:00.000-08:00

The 6 November updates dealt with three quite severe bugs. Two were in the fundamental data structures in the Pyrrho data engine - indexing and multi-level indexes, and seem to have crept in at different stages, fairly recently. They weren't detected in routine testing. An unrelated query processing problem was just days old, and should have been spotted.
The Embedded edition is temporarily unavailable because of a different problem.
Please send in reports any time you find anything incorrect.

Updatable RowSets

2009-11-02T12:40:00.000-08:00

Today's update fixes a number of issues with the updatability of RowSets. The basic idea is unchanged: a RowSet is updatable if is has been selected (via a view or directly) from a single base table and its columns include the primary key. Unfortunately this simple idea got overlaid with PyrrhoMgr trying to be too clever.
(Updated: 5 Nov 09)

Sparql fix

2009-10-20T13:00:00.000-07:00

Today's fix is to the Sparql engine which has obviously been broken since the last posting. I have also reformatted the Sparql tutorial as I noticed some unfortunate page and line breaks in the code samples that caused difficulties if people pasted the text into the Rdf tool without watching carefully.

Transaction Conflicts

2009-10-04T13:56:00.000-07:00

Today's fix is to the mechanism for detecting and handling Transaction conflicts. (Updated: 5 Oct)

Out and Inout Parameters

2009-09-25T08:34:00.000-07:00

Yesterday's fix was about output parameters from procedures and methods.

Procedure body syntax

2009-09-22T10:47:00.000-07:00

Today's fix relates to procedure bodies.
According to SQL2003 (in section 02) procedure call statements must have the CALL keyword and (in section 04) assignment statements must have the SET keyword. For ages Pyrrho has not been enforcing this rule properly. It would be (strictly speaking) a breaking change to start enforcing it now.
So, both keywords had better be optional in Pyrrho procedure bodies, at least for call statements that have a possibly empty parameter list enclosed in () and for simple assignment statements, and this is yet another departure from SQL2003. I will confess to this in the next version of the user manual.

Embedded Pyrrho etc

2009-09-21T14:06:00.000-07:00

Today there is a fix for the Embedded edition, and for yet another type-checking bug in the server...

Triggers and DataGridView

2009-09-19T12:18:00.000-07:00

Today's update is mainly about fixing some errors in the implementation of Triggers and DataGridViews. A serious issue about type checking has also been resolved.

For the improved functionality of DataGridView, please use the new version of PyrrhoLink.dll or OSPLink.dll. (Anything that worked previously should still work.)

DataTypeName

2009-09-07T07:45:00.000-07:00

The ADO.NET API includes a method called GetDataTypeName on the IDataReader: this is supposed to return a string, given a field index. The documentation says

The data type information can differ from the type information returned by GetFieldType, especially where the underlying data types do not map one for one to the runtime types supported by the language. (for example, DataTypeName may be "integer", while Type.Name may be "Int32".)

Up to now, the DataTypeName was just the name of the closest native type, such as System.Decimal.

From today, and version 3.3 of Pyrrho, it will return the name of the SQL type. For example, NUMERIC(8,2) or a user defined type name.

The version number is changed since the new version of PyrrhoLink for this behaviour will then only work with server versions 3.3 or later. (In all other respects backwards compatibility is preserved.)

The SPARQL Tutorial

2009-08-30T07:59:00.000-07:00

Today's update (30 August 2009) includes a version of the ARQ SPARQL Tutorial and some associated fixes to the SPARQL subsystem. Specifically, default data sets should work better for the RDF application program.

Better Numeric Types

2009-08-22T09:13:00.000-07:00

Today's update (22 August) fixes a number of bugs mostly related to handling of numeric types. Two corrections to the syntax checking fix the BETWEEN predicate and named columns joins.
I really do like finding and fixing bugs!

Subtype improvements

2009-08-14T08:32:00.000-07:00

Today's update to the DBMS corrects the handling of the new URI-based subtypes of standard types. As mentioned in previous posts, these are a novelty in Pyrrho, allowing declarations such as
create type uk_numberplate as char with 'http://dvla.gov.uk'
As of today's version the representation clause ("as char" in this example) for such types is constrained to be a standard type. Subtypes of such types must be declared as follows:
create type sc_nunberplate under uk_numberplate as char with 'http://dvla.scotland.gov.uk'
The semantic of IS OF for these subtypes has been corrected.

Provenance again

2009-07-11T06:32:00.000-07:00

Today's version of the software aims to fix a number of issues.
(a) a bug in transaction handling that resulted in premature closure of transactions
(b) a number of corrections to provenance handling and subtypes.
(c) a new system table called Sys$Object which makes it easier to see the provenance of tables and types.

To try out the new ideas, here are some examples.

1. To have database objects or inserted rows with a particular provenance, say something like
begin transaction with provenance 'http://example.com'
...
commit
Pyrrho refers to this internally as an import provenance, but of course it is more flexible than that.

2. To have rows in a table associated with a provenance, you can also say
insert with provenance '/data' into ...
and then the rows have a provenance consisting of eith or both the import and row provenance (if both are specified the provenance is the result of concatenating them).

There is no way of modifying provenance (this would be heresy).

3. Subtypes can be associated with a uri: there is a WITH syntax defined in Pyrrho for this purpose, e.g.
create type abcint as integer with 'http://abc.net'
and then if desired the subtype can be used for values you want to mark with the URI, by using (say) treat(11) as abcint instead of just 11.
Such a subtype behaves like an integer of course, but you can select on such subtype values using the OF predicate, e.g. select * from a where b is of(only abcint).

Write to me with any problems.

Bug fixed: SelectedColumnsRowSet

2009-06-11T15:21:00.000-07:00

In previous releases of v3.2, the rowType was incorrectly reported as the original rowType, not the rowType with just the selected columns. This would have caused an error 22202 when the wrong rowType was used.
Similar fixes to other RowSet classes. Fixed as of 12 June.

On importing data

2009-06-08T14:21:00.000-07:00

I've started to provide better support for data import into Pyrrho. Version 3.x has been introducing ideas of provenance. Now I am enhancing the PyrrhoMgr application a bit. The changes will get published in the next few days. So far they amount to:
1. Supporting direct import from Access 2007 in addition to Access 2003 and SQL Server.
2. Supporting the "Percent" numeric format in Access. (Intriguingly in Access 2007 the default Percent format is for a long integer, and so validation changes every value to either 0.0% or 100.0% !)
3. Allowing the importer to specify the "From Culture" so that culture specific formats for numbers and dates can get converted into the culture used by the importing thread (the culture of the machine that PyrrhoMgr is running on).
As mentioned in the last posting, the server always uses an invariant culture.
As a result of some ongoing research, more changes in this area can be expected soon.

Why the version hasn’t changed

2009-06-06T00:28:00.001-07:00

Changes like this are possible in Pyrrho because of the distinctive way that Pyrrho is designed. Nearly all DBMSs store data structures like indexes in permanent storage within the files making up the database. Pyrrho by contrast places in permanent storage only the data required for durability of each committed transaction. This approach minimises disk activity during normal operations, to about one-seventieth of the disk activity of rival systems.
The server’s data structures are private to the server, and are initialised when the server starts up, and the server brings its state up to date by re-reading the database file, which is just the transaction record. This is a time-consuming process for a large database, equivalent to a cold-start with resynchronisation step for other products.
The format of this data is as far as possible version and platform-independent – there is not even any assumption for what “double precision” means, or how many bits make up a long integer. This means that every aspect of the organisation of data structures can evolve without breaking backward compatibility. Database files created by version 0.1 can still be used in every version of Pyrrho, so all Pyrrho data bases can always be managed by the latest version of the server. New features, such as the recently introduced concept of provenance, or URI-based data types, introduce new or modified data formats which would not be understood by earlier versions, but they really should not be in use. Upgrading to the latest version is always recommended, and is free of charge.

Speeding up joins

2009-06-03T09:43:00.000-07:00

The next version (still numbered 3.2) will have a new implementation of the matching code for joins. There will be situations where a significant performance improvement can be expected, for example where the join condition refers to a component of a primary or foreign key. In the source code this is called a TrivialJoin, but it is not necessarily quite so trivial where the join condition does not constrain all the components of the key...
I should be able to release this implementation this weekend following testing.
Update 4 June: so FDJoin is a better term than TrivialJoin, with consequential renaming of lots of things. I've introduced a new internal datatype called Wild for dealing with partial matches. And better handling of recursive traversal of multilevel indexes. It's all loking rather nice.

Version 3.2 is done

2009-05-27T14:30:00.000-07:00

I will release the next version 3.2 tomorrow 28th May.
It contains the promised improvements to the infrastructure.

Towards stronger types internally

2009-05-13T13:54:00.000-07:00

The next version of Pyrrho will use much stronger generics. The internal Tree structures will be Tree<Key,Value> instead of just Tree<Key> . But this is taking an age to sort out...
With any luck version 3.2 should be out next month (June 2009) though.
The change has flushed out a few obscure bugs, but unfortunately the changes to the code are extensive so no doubt there will be the occasional null refernce that slips through. So if you get a bad message from the server, please let me know about it...

The REST service

2009-05-03T06:34:00.000-07:00

Pyrrho's new REST service is similar to ADO.NET data services, from which it differs by providing a default data model. For a Pyrrho database file myfile, the default data service is myfile.svc. Custom data models can be supplied, and then the data service is given the same name as the data model, e.g. mymodel.pdm.

The service is hosted by default on port 8080 on the host running the Pyrrho DBMS server, e.g.

http://example.com:8080/mydatabase.svc/

and the rest of the URI follows the rules for ADO.NET. As with ADO.NET, the detailed datamodel is obtained by adding $metadata to the end of the service URI.

The methods GET, PUT, POST, and DELETE are supported. If no resource path is specified, a GET on the service gives a synopsis of the service, and otherwise returns XML for the specified data. POST is used for inserting new entities, PUT for updating specified data in existing entities, and DELETE for deleting specified entities.

However, to make any changes to the database, you need to supply UserID and Role identifiers in the HTTP headers. This means that most browser access will support only GET,