The 4.x release of DODS allows tables like the
following: create table person (
id integer,
name char(10),
primary key(id)
);
create table employee (
id integer,
salary integer,
primary key(id),
foreign key(id) references person(id)
);
create table manager (
id integer,
bonus integer,
primary key(id),
foreign key(id) references employee(id)
);
By having the PK of employee be a FK referencing the PK of person, a one-to-one
relationship is established. A given employee is one and only one person. An employee
"is-a" person.
By having the PK of manager be a FK referencing the PK of employee, a one-to-one
relationship is established. A given manager is one and only one employee. A manager
"is-a" employee.
So for a given manager, the manager.id = employee.id = person.id.
Now you can create an updatable join view:
create view full_manager as
select p.id id,
p.name name,
e.salary salary,
m.bonus bonus
from person p, employee e, manager m
where p.id = e.id
and e.id = m.id;
You can then create a FullManagerDO for that view, and use it to interrogate and modify
the name, salary, and bonus for a given manager through a single object. That is, you can
update values in multiple tables using a single DO.
Data objects that utilize an updatable join view are an elegant replacement for the
"derived Data Object" scheme currently provided by DODS.
This technique only works for databases that support updatable join views.
To verify that the columns returned by a view are updatable, the following query can be
run (in Oracle):
select column_name, updatable, insertable, deletable
from user_updatable_columns
where table_name = 'full_manager'
Notes on "key-preserved" tables:
Columns in a view are updatable if the table they come from is key-preserved. A table
is key-preserved in a join view if every key of the table (whether or not it is included
in the select clause of the view) would still be a valid key following a change to the
columns seen in the view. Only key-preserved tables can be updated through the view.
When tables joined in a view are connected via one-to-one relationships (as in the
person/employee/manager example above) the database knows that columns in various tables
can be updated without affecting integrity constraints.
But if, instead of an "is-a" relationship, the manager table had a
"has-a" relationship with the employee table
create table manager (
id integer,
empinfo integer, /* each manager "has" personal employee info */
bonus integer,
primary key(id),
/*
manager primary key is not foreign key to employee primary key;
the database assumes this is a many-to-one relationship
*/
foreign key(empinfo) references employee(id)
);
then only the bonus column of the full_manager view would be updatable.
The "has-a" relationship was the best that could be achieved using the
oid/version scheme.
Now that DODS supports arbitrary primary keys in tables, correct relationships can be
established, and elegant solutions for object-relational mappings are available.
The old "derived Data Object" scheme supported by DODS is hereby deprecated. |