cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christian Haul <h...@dvs1.informatik.tu-darmstadt.de>
Subject Re: [Modular DB Actions] - Problem updating rows with a multiple key...
Date Wed, 14 May 2003 07:39:42 GMT
On 13.May.2003 -- 06:07 PM, Antonio Gallardo wrote:
> Hi:
> 
> We discovered sometime ago a problem in Modular Database Actions (MoDBA)
> when the updated tables has more than ONE field registered as PRIMARY KEY.
> 
> Description:
> Lets define a table called My table into some database in PostgreSQL
> 7.3.2. The SQL scripts will be:
> 
> CREATE TABLE Departamentos
> (
>   dep_id     int4 not null default nextval('departamentos_dep_id_seq'),
>   coe_id     int4 not null,
>   coc_id     int4 not null,
>   dep_name   varchar(50) not null,
>   dep_enable int4 not null default 1,
> 
>   primary key(dep_id,coe_id,coc_id),
>   unique(coe_id,coc_id,dep_name),
> 
>   foreign key(coe_id,coc_id) references coCliente(coe_id,coc_id) on update
> CASCADE,
> 
>   CHECK(dep_enable BETWEEN 0 AND 1)
> );
> 
> Warning - Not Exist a Boolean Database Type:
> I needed to describe the "dep_enable" field as an INT4 and then check that
> ep_enable can have only to values. This is because the Boolean Data ype is
> not curently supported by Modular Database Actions. :(

Sorry to disappoint you, Antonio, but they do support "boolean" and
use getBoolean / setBoolean to access it :-)

> Please see that the PRIMARY KEY is composed of 3 fields.
> 
> Problem:
> Using any SQL dialect I can change some or all the values of the fields
> into the table, but using MoDBA you cannot do that if the field in
> question is part of the PRIMARY KEY.
> 
> However I tried I cannot send a normal UPDATE command to set for example
> the field "coe_id". No matter if the values are valid (see constrains
> above) or not.
> 
> Is this a known limitation? Why that happens?

Yes, it is a known limitation to the extend that no <key/> may be
updated. If you had two descriptions of your table, one using the PK
attributes as <key/> and one using the unique attributes as <key/>
would solve the problem.

However, there is no technical limitation that imposes this
restriction. At that time I decided that MoDBA is not intended to
solve everything but is more a convenient method to store tuples in a
database. 

So, if you care enough for a patch.... it's just
DatabaseUpdateAction.getQuery() and DatabaseUpdateAction.processRow()
that need to be adjusted for the extra columns.

It might be nice to have this configurable because updating key (or
unique) columns entail modifying the index which may or may not be
costly depending on how smart the DBMS is if their values don't
change.

In addition it is a (very small) safeguard against unwanted changes. I
believe that in 95% of all cases one would like the key to stay the same.

	Chris.
-- 
C h r i s t i a n       H a u l
haul@informatik.tu-darmstadt.de
    fingerprint: 99B0 1D9D 7919 644A 4837  7D73 FEF9 6856 335A 9E08

---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
For additional commands, e-mail: cocoon-users-help@xml.apache.org


Mime
View raw message