[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