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.

REST and role models

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

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

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.

create table author(id int primary key,name char not null)

create table book(id int primary key, title char not null, aid int references author)

insert into author values(1,'Dickens'),(2,'Conrad')

insert into book values(10,'Lord Jim',2),(11,'Nicholas Nickleby',1)

table book

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:

alter table book add aname char generated always as (select name from author a where a.id=aid)

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:

create role librarian

grant all privileges on author to librarian

grant all privileges on book to librarian

grant librarian to "computer\Fred" with admin option

(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:

alter table book alter aid to "AuthorId"

alter table book alter aname to "Author"

alter table book alter title to "Title"

alter table book add "Availability" boolean default true

select "Title","Author","Availability" from book

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

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

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

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

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

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

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

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

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.