Thread: meta-data problem

Hi everyone,

I use cayenne as on object relational mapping system and have a question about using it with pgsql. It seems that when I try to reverse engineer a pgsql db (server v.8.1.2), the table structure is OK, but no table relations are discovered, so all the foreign keys are simply interpreted as additional integer columns, instead of foreign keys. I've tried using the driver supplied with pgsql 8.1.2 (8.0-310?) and 8.2dev-501, but it makes no obvious difference.
My question is: could this be a jdbc-driver bug? I can't tell, but I've used another RDBMS with cayenne and database structure discovery worked flawlessly there. Could be a cayenne issue, but the developers suggest it's probably not, but could be worked arround if needed.

I don't think there's anything wrong with the table and references definitions. To illustrate, here's one:
CREATE TABLE opca.dokument
(
  sif_dokument int4 NOT NULL,
  sif_vrsta_dokumenta int4,
  sif_spis int4,
  zaprema timestamp,
  opis varchar(255),
  CONSTRAINT dokument_pkey PRIMARY KEY (sif_dokument),
  CONSTRAINT fk_dokument_1 FOREIGN KEY (sif_spis)
      REFERENCES opca.spis (sif_spis) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_dokument_2 FOREIGN KEY (sif_vrsta_dokumenta)
      REFERENCES opca.vrsta_dokumenta (sif_vrsta_dokumenta) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;

Btw, I don't use any composite keys in this database whatsoever, so that's not part of the problem (I've seen it mentioned in o post some time ago).
I'll reconstruct the relationships by hand this time, but it'd be nice if we find out why the relationships aren't found.

TIA,
Tomislav

Tomi NA wrote:

> My question is: could this be a jdbc-driver bug? I can't tell, but I've
> used another RDBMS with cayenne and database structure discovery worked
> flawlessly there. Could be a cayenne issue, but the developers suggest
> it's probably not, but could be worked arround if needed.

If you can get some info about exactly what the postgresql driver is
returning that is incorrect or not what cayenne expects, then you might
make some progress.. Otherwise this is a bit vague.

-O

On 3/11/06, Oliver Jowett <oliver@opencloud.com> wrote:

On 3/11/06, Oliver Jowett <oliver@opencloud.com> wrote:
> Tomi NA wrote:
>
> > My question is: could this be a jdbc-driver bug? I can't tell, but I've
> > used another RDBMS with cayenne and database structure discovery worked
> > flawlessly there. Could be a cayenne issue, but the developers suggest
> > it's probably not, but could be worked arround if needed.
>
> If you can get some info about exactly what the postgresql driver is
> returning that is incorrect or not what cayenne expects, then you might
> make some progress.. Otherwise this is a bit vague.

Does not seem to be a bug in the JDBC driver. Running DdlUtils'
DumpMetadataTask on these tables (the one from the original post with
the two other required ones):

CREATE TABLE spis
(
  sif_spis int4 NOT NULL,
  CONSTRAINT spis_pkey PRIMARY KEY (sif_spis)
);
CREATE TABLE vrsta_dokumenta
(
  sif_vrsta_dokumenta int4 NOT NULL,
  CONSTRAINT vrsta_dokumenta_pkey PRIMARY KEY (sif_vrsta_dokumenta)
);
CREATE TABLE dokument
(
  sif_dokument int4 NOT NULL,
  sif_vrsta_dokumenta int4,
  sif_spis int4,
  zaprema timestamp,
  opis varchar(255),
  CONSTRAINT dokument_pkey PRIMARY KEY (sif_dokument),
  CONSTRAINT fk_dokument_1 FOREIGN KEY (sif_spis)
      REFERENCES spis (sif_spis) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_dokument_2 FOREIGN KEY (sif_vrsta_dokumenta)
      REFERENCES vrsta_dokumenta (sif_vrsta_dokumenta) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;

produces this XML when using the 8.0-311 JDBC driver against a 8.0
database running on Windows XP:

  <tables>
    <table name="dokument" schema="public" type="TABLE">
      <column name="sif_dokument" typeCode="4" type="int4" size="4"
digits="0" precision="10" nullable="false" maxByteLength="4" index="1"
isNullable="false"/>
      <column name="sif_vrsta_dokumenta" typeCode="4" type="int4"
size="4" digits="0" precision="10" nullable="true" maxByteLength="4"
index="2" isNullable="true"/>
      <column name="sif_spis" typeCode="4" type="int4" size="4"
digits="0" precision="10" nullable="true" maxByteLength="4" index="3"
isNullable="true"/>
      <column name="zaprema" typeCode="93" type="timestamp" size="8"
digits="0" precision="10" nullable="true" maxByteLength="8" index="4"
isNullable="true"/>
      <column name="opis" typeCode="12" type="varchar" size="255"
digits="0" precision="0" nullable="true" maxByteLength="255" index="5"
isNullable="true"/>
      <primaryKey column="sif_dokument" name="dokument_pkey"
sequenceNumberInPK="1"/>
      <versionedColumn column="ctid" typeCode="1111" type="tid"
size="0" precision="0" scale="0" columnType="pseudo column"/>
      <foreignKey name="fk_dokument_1" primaryKeyName="spis_pkey"
column="sif_spis" foreignSchema="public" foreignTable="dokument"
foreignColumn="sif_spis" sequenceNumberInFK="1" updateRule="no action"
deleteRule="no action" deferrability="not deferred"/>
      <foreignKey name="fk_dokument_2"
primaryKeyName="vrsta_dokumenta_pkey" column="sif_vrsta_dokumenta"
foreignSchema="public" foreignTable="dokument"
foreignColumn="sif_vrsta_dokumenta" sequenceNumberInFK="1"
updateRule="no action" deleteRule="no action" deferrability="not
deferred"/>
      <index name="dokument_pkey" nonUnique="false" type="other"
column="sif_dokument" sequenceNumberInIndex="1" sortOrder="unknown"
cardinality="0" pages="1"/>
    </table>
    <table name="spis" schema="public" type="TABLE">
      <column name="sif_spis" typeCode="4" type="int4" size="4"
digits="0" precision="10" nullable="false" maxByteLength="4" index="1"
isNullable="false"/>
      <primaryKey column="sif_spis" name="spis_pkey" sequenceNumberInPK="1"/>
      <versionedColumn column="ctid" typeCode="1111" type="tid"
size="0" precision="0" scale="0" columnType="pseudo column"/>
      <index name="spis_pkey" nonUnique="false" type="other"
column="sif_spis" sequenceNumberInIndex="1" sortOrder="unknown"
cardinality="0" pages="1"/>
    </table>
    <table name="vrsta_dokumenta" schema="public" type="TABLE">
      <column name="sif_vrsta_dokumenta" typeCode="4" type="int4"
size="4" digits="0" precision="10" nullable="false" maxByteLength="4"
index="1" isNullable="false"/>
      <primaryKey column="sif_vrsta_dokumenta"
name="vrsta_dokumenta_pkey" sequenceNumberInPK="1"/>
      <versionedColumn column="ctid" typeCode="1111" type="tid"
size="0" precision="0" scale="0" columnType="pseudo column"/>
      <index name="vrsta_dokumenta_pkey" nonUnique="false"
type="other" column="sif_vrsta_dokumenta" sequenceNumberInIndex="1"
sortOrder="unknown" cardinality="0" pages="1"/>
    </table>
  </tables>

so the foreign keys are there alright. And AFAIK there is no special
JDBC metadata handling necessary for PostgreSQL (as opposed to other
databases).

cheers,
Tom

Just to let everyone know, it seems at the moment it's a cayenne issue. It's not confirmed yet, but I'd say it's probable. Stay tuned for further developments. :)

Tomislav