hibernate-devel

[Hibernate] Postgres NUMERIC and other problems

[Andrea A. <aa...@li...>]

Hi everybody,
please have a look at the following files, a mapping file and the=20
sql generated by Hibernate tools.=20

=2D------------------------------------------------------------------------=
=2D---------------------------

<?xml version=3D"1.0"?>
<!DOCTYPE hibernate-mapping SYSTEM "hibernate-mapping.dtd">
<hibernate-mapping>

  <class name=3D"Article" table=3D"article">
    <id name=3D"id" column=3D"id" type=3D"long">
      <generator class=3D"vm.long"/>
    </id>
    <property name=3D"description" type=3D"string" length=3D"75" not-null=
=3D"true"/>
    <property name=3D"cost" type=3D"big_decimal" length=3D"2"/>
  </class>
 =20
  <class name=3D"PriceList" table=3D"pricelist">
    <id name=3D"id" column=3D"id" type=3D"long">
      <generator class=3D"vm.long"/>
    </id>
    <set role=3D"righeArticolo">
        <key type=3D"long" column=3D"plid"/>
        <one-to-many class=3D"ArticlePL"/>
    </set>
  </class>
 =20
  <class name=3D"ArticlePL" table=3D"artpl">
    <id name=3D"id" column=3D"id" type=3D"long">
      <generator class=3D"vm.long"/>
    </id>
    <property name=3D"discount" type=3D"float" column=3D"discount" not-null=
=3D"true"/>
    <many-to-one name=3D"article" column=3D"articleid"=20
                 class=3D"Article"/>
    <many-to-one name=3D"pl" column=3D"plid"=20
                 class=3D"PriceList"/>
  </class>
     =20
</hibernate-mapping>

=2D------------------------------------------------------------------------=
=2D-----------------------------

drop table artpl
drop table pricelist
drop table article
create table artpl (articleid INT8, discount FLOAT4 not null, id INT8 not=20
null, plid INT8, primary key (id))
create table pricelist (id INT8 not null, primary key (id))
create table article (cost NUMERIC, description VARCHAR(75) not null, id IN=
T8=20
not null, primary key (id))
alter table artpl add constraint artplFK1 foreign key (plid) references=20
pricelist
alter table artpl add constraint artplFK0 foreign key (articleid) reference=
s=20
article
alter table artpl add constraint artplFK2 foreign key (plid) references=20
pricelist
create index artplIDX3 on artpl (plid)

=2D------------------------------------------------------------------------=
=2D----------------------------

Basically, there are two problems:
=2D the same foreign key is generated two times (artplFK1, artplFK2)
=2D big_decimal is mapped to a NUMERIC... according to the Postgres =20
  documentation "NUMERIC without any precision or scale creates a column in
   which numeric values of any precision and scale can be stored, up to
   the implementation limit on precision." That seems to be a little overki=
ll=20
   to me, moreover I've specified a length=3D2, so I expect something like
   NUMERIC(x,2) (SAPDB dialect outputs FIXED(19,2))

Comments are welcome... :-)
Best regards
Andrea Aime

Re: [Hibernate] Postgres NUMERIC and other problems

[Gavin K. <ga...@ap...>]

> Basically, there are two problems:
> - the same foreign key is generated two times (artplFK1, artplFK2)

That won't happen if you declare one end of the bidirectional association
with readonly="true" which is the preferred style.


> - big_decimal is mapped to a NUMERIC... according to the Postgres
>   documentation "NUMERIC without any precision or scale creates a column
in
>    which numeric values of any precision and scale can be stored, up to
>    the implementation limit on precision." That seems to be a little
overkill
>    to me, moreover I've specified a length=2, so I expect something like
>    NUMERIC(x,2) (SAPDB dialect outputs FIXED(19,2))

Yeah, I know.....I didn't quite know what to do about NUMERIC types....we
really should allow precision and scale attributes as well as length, but
that is starting to complicate the DTD too much. What we will probably have
to do eventually is allow them on the <column> element rather than the
<property> elements, etc. However, you can already do this:

<property name="cost" type="big_decimal">
    <column sql-type="NUMERIC(19,2)"/>
</property>

Which is actually less typing than:

<property name="cost" type="big_decimal">
    <column precision="19" scale="2">
</property>

So I couldn't be bothered with that just yet.

Still, I know its suboptimal. The reason for the inconsistency between
dialects is I would have preferred to handle it like Postgres for all
dialects, but most of the databases default scale to 0, which is useless.

If you are voluteering to fix this stuff, your help is very welcome ;)

Gavin

Re: [Hibernate] Postgres NUMERIC and other problems

[Andrea A. <aa...@li...>]

Gavin King wrote:

>>Basically, there are two problems:
>>- the same foreign key is generated two times (artplFK1, artplFK2)
>>    
>>
>
>That won't happen if you declare one end of the bidirectional association
>with readonly="true" which is the preferred style.
>  
>

Ah, I see. But if I allow editing on both sides, what can go wrong? I 
mean, either the programmer
should not be allowed to mark both sides as editable or the fk should 
not be generated two times...
I will have a look at the code and see if we can avoid this...

>Yeah, I know.....I didn't quite know what to do about NUMERIC types....we
>really should allow precision and scale attributes as well as length, but
>that is starting to complicate the DTD too much. What we will probably have
>to do eventually is allow them on the <column> element rather than the
><property> elements, etc. However, you can already do this:
>
><property name="cost" type="big_decimal">
>    <column sql-type="NUMERIC(19,2)"/>
></property>
>
>  
>
Yes, but this would be database specific, would work with Postgres but 
not with SAPDB, Oracle...
One of the greatest features of the mapping file is that it is database 
agnonistc, using the sql-type
attribute you lose this feature...

>Which is actually less typing than:
>
><property name="cost" type="big_decimal">
>    <column precision="19" scale="2">
></property>
>
>So I couldn't be bothered with that just yet.
>
>If you are voluteering to fix this stuff, your help is very welcome ;)
>
>Gavin
>
>  
>
My proposal is to add precision and scale as column attributes and 
default to (19,2) which
seems to me the most reasonable default, since that would treat all 
databases in an uniform
way. The numeric default type is allowed to grow to the implementation 
limits, that is, a very
big size I guess, which means a very big space overhead on the disk, or 
to a non uniform treatment
of the data contained in the column (like a BLOB type), which may be 
very slow. If one needs
the NUMERIC without precision and scale specification he is asking for a 
PostgreSQL 
specific feature, so it would be reasonable to use the sql-type directly.
If you agree I can have a look at the implementation...
Best regards
Andrea Aime