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