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.

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