Adapter functions, updatable views and joins

With the introduction of REST Views it has become more interesting to support updatable views and joins, and more important to support adapter functions. The Beta version 5.7 of Pyrrho makes some important contributions in these aspects.
In the following sample, we model a situation where databases have columns that nearly match but do not quite do so. For example suppose Table A(B,C) has an integer primary key B, while table D(E,G) uses corresponding string values all prefixed by the letter H as its primary key E. It is easy to define a suitable adapter function F

create function f(x int) returns char return 'H'|| cast(x as char)

and create a view of A that makes sense in D's database:

create view v as select f(b) as fb,c from a

D, however, wishes to be able to update table A, so would like this view to be updatable. Pyrrho allows the declaration of an inverse of F:

create function f1(a char) returns int inverts f return cast(substring(a from 1) as int)

where the phrase "inverts f" is parsed as metadata for the new function f1 (otherwise everything so far is ordinary standard SQL). This makes f and f1 into mutually inverse functions, that is, declaring f1 as an inverse for f also declares f as an inverse of f1. The machinery works for multiple parameters using row types for the return values.

This makes v updatable and we can write

insert into v values('H91','This is new')

We can also use such adapter functions in referential constraints, e.g. a slight extension to SQL in Pyrrho allows us to declare the above relationship between tables A and D as follows:

create table d (e char references a using f1,g int)

The following SQL sample code demonstrating the above ideas is supported by Pyrrho 5.7 as of today.

create function f(x int) returns char return 'H'|| cast(x as char)
[create function f1(a char) returns int inverts f return cast(substring(a from 1) as int)]
select f(45) from static
select f1('B67') from static
create table a(b int primary key,c char)
insert into a values(23,'Twenty3'),(42,'Forty2')
create view v as select f(b) as fb,c from a
select * from v
insert into v values('H91','This is new')
table a
create table d (e char references a using f1,g int)
insert into d values('H23',234),('H91',567)
create view w as select c,e from a left join d on b=f1(e)
delete from w where c='Twenty3'
table a

Composite Database example with details

This post gives more details of the example contained in the posting Composite Databases in this blog.

The following transcripts use the beta version 5.7 of Pyrrho dated 16 March 2017, and using localhost instead of servA, with servers A and B using folders \A and \B respectively. I have set a debugging –D flag on server A so that that we can see the use of RVVs and ETags.

We begin by setting up the databases on servers A and B:

Database A:

create table D (e int primary key, f char, g char)


insert into D values (1,'Joe','Soap'), (2,'Betty','Boop')

Database B:

create table H (e int primary key, k char, m int)

insert into H values (1,'Cleaner',12500), (2,'Manager',31400)

[create view W of (e int, f char, g char) as get
'http://localhost:8180/A/A/D']

create view V as select * from W natural join H

The square brackets here are added because of the embedded newline added in the formatting of the page.
After setting up the databases on A and B with B’s views defined, we see the transaction log contents for A and B. Some of the numbers shown will be used in RVV and ETags in what follows.

We see at position 381 that the URL http://localhost:8180/A/A/D has been provided in metadata for the view W. W was defined in position 366 in terms of the anonymous structure with columns E, F, G declared at position 290.

The use of position numbers instead of identifiers in the definition of view V at position 439 is a standard feature of Pyrrho to allow renaming of objects.

In the blog post we now have the following operations on database B:

select e,f,m,check from V where e=1

Note that in normal use there is no need to request the check pseudocolumn: it is here so can show what is happening within the two databases. The database API uses it to implement the Versioned feature in client side “database model” classes.

Here, the check value was requested explicitly in the SELECT statement, and shows that this row of the join uses a row from A with defining position 209 placed there in transaction 193 (see the log for A) and a row from B with defining position 208 arising from transaction 192 (see the log for B).

The debug information for server A shows the REST request from B to A, and the ETag it constructed.

The ETag consists of an RVV for the first row of the result (mentioned above), and a readCheck for the read operation carried out by A. This was a specific row in table D (position 69) with key (1) .

The next operation is

update v set f='Elizabeth' where e=2

We see there is now an updated ETag supplied by A showing the new transaction that has updated the record defined at 241.

Also check B’s view using the join:

The next operation is an Insert into the View/RestView/Join combination:

[insert into v(e,f,g,k,m)

values(3,'Fred','Smith','Janitor',22160)]

This time the readCheck information indicates it will conflict with any read operation on table 69.

And again verifying the view from B:

Finally, we try a deletion from the View/RestView/Join combination: