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.