Re: meta-data problem - Mailing list pgsql-jdbc

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