Data Visualisation

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

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

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

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

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.