On Mon, 2002-05-27 at 05:30, John Foster
> The Problem;
> I have merged the tables from 3 different databases into one by building
> them by hand.
> I have 3 different applications all accessing this database and all are
> working properly. There are tables within the database that are similar
> and in fact have fields that contain basically the same info. They all
> have different names. The names can not be altered and the properties of
> the tables & fields can not be diminished. I want to be able to do these
> things:
> 1. Update the various applications software without having to rebuild
> the basic structure of the database. This seems to be doable as long as
> I leave the necessary tables and fields alone. So no problem there.
> 2. Update the database info from any of the applications in a fashion
> that update all of the fields in all of the tables where the info is the
> same.
>
> ex:
> olly@lfix.co.uk
Jaffeollo
Have one table and two updatable views of it (use rules to make the
views updatable). Something like this:
junk=# create table table1 ("name" text primary key, addr1 text not
null, addr2 text not null, addr3 text not null, addr4 char(2) not
null);
"name" is a reserved word so it has to be quoted.
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'table1_pkey' for table 'table1'
junk=# create view table2 as select "name" as "user", addr1 as
"st.", addr2 as "apt#", addr3 as city, addr4 as state from table1;
"user" is a reserved word; "." and "#" are not valid characters for an
SQL identifier, so all these have to be quoted. In your example you had
the field "st." occurring twice, so I have changed the second occurrence
to "state".
CREATE
Now you need rules on each of insert, update and delete for each view:
junk=# create view table3 as select "name" as member, addr1 as
address, addr2 as "box#", addr3 as city, addr4 as state from table1;
CREATE
junk=# create rule table2_insert as on insert to table2 do instead
insert into table1 values (NEW."user", NEW."st.", NEW."apt#",
NEW.city, NEW.state);
CREATE
junk=# create rule table2_delete as on delete to table2 do instead
delete from table1 where "name" = OLD."user";
CREATE
junk=# create rule table2_update as on update to table2 do instead
update table1 set "name" = NEW."user", addr1 = NEW."st.", addr2 =
NEW."apt#", addr3 = NEW.city, addr4 = NEW.state where "name" =
OLD."user";
CREATE
junk=# create rule table3_insert as on insert to table3 do instead
insert into table1 values (NEW.member, NEW.address, NEW."box#",
NEW.city, NEW.state);
CREATE
junk=# create rule table3_update as on update to table3 do instead
update table1 set "name" = NEW.member, addr1 = NEW.address, addr2 =
NEW."box#", addr3 = NEW.city, addr4 = NEW.state where "name" =
OLD.member;
CREATE
junk=# create rule table3_delete as on delete to table3 do instead
delete from table1 where "name" = OLD.member;
CREATE
And now it works:
junk=# insert into table2 values ('Fred','12 Green St.', '',
'Colchester', 'MA');
INSERT 0 0
junk=# insert into table3 values ('George','1 Park Lane', 'Box
#566', 'Frinksworth', 'CO');
INSERT 0 0
junk=# select * from table1;
name | addr1 | addr2 | addr3 | addr4
--------+--------------+----------+-------------+-------
Fred | 12 Green St. | | Colchester | MA
George | 1 Park Lane | Box #566 | Frinksworth | CO
(2 rows)
junk=# select * from table2;
user | st. | apt# | city | state
--------+--------------+----------+-------------+-------
Fred | 12 Green St. | | Colchester | MA
George | 1 Park Lane | Box #566 | Frinksworth | CO
(2 rows)
junk=# select * from table3;
member | address | box# | city | state
--------+--------------+----------+-------------+-------
Fred | 12 Green St. | | Colchester | MA
George | 1 Park Lane | Box #566 | Frinksworth | CO
(2 rows)
Rules are fully described in chapter 16 of the Programmer's Manual.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"All that the Father giveth me shall come to me; and
him that cometh to me I will in no wise cast out."
John 6:37
Attachment:
signature.asc
Description: This is a digitally signed message part