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.