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.