reverse engineering of Postgresql in EntityModeler gives problems in creating another database

From: Johan Henselmans (joha..etsense.nl)
Date: Thu May 31 2007 - 18:44:23 EDT

  • Next message: Mike Schrag: "Re: reverse engineering of Postgresql in EntityModeler gives problems in creating another database"

    I am looking at Entity Modeler, and apart from the WOLips quirks, it
    looks a fine replacement of EOModeler. I am also using the
    ERPrototypes/EOJDBCPostgresqlPrototypes.

    There seem to be a few quirks, however, when using it against
    postgresql.

    I am running the latest postgresql (8.2.4) with the latest postgresql
    JDBC3 driver: (postgresql-8.2-505.jdbc3.jar)

    When I try to reverse engineer a database, the tables are all part of
    the public catalog. That is fine, however, when generating indexes it
    also uses the public. as a pre-text, which causes the creation of
    indexes to fail.

    Another problem is the way text fields are interpreted: in the
    relations table I have a field relation_remark, with fieldtype text.
    The reverse-engineering makes that text 2147483647 long, which fails
    very nicely.

    An example of reverse engineering:

    ================================
    CREATE TABLE public.relations (relation_city varchar(30) ,
    relation_country_id int4 , relation_email varchar(50) ,
    relation_emailold varchar(30) , relation_id int4 NOT NULL,
    relation_name varchar(50) , relation_phone1 varchar(20) ,
    relation_phone2 varchar(20) , relation_remarks text(2147483647) ,
    relation_straat varchar(50) , relation_straat2 varchar(50) ,
    relation_telex varchar(20) , relation_zipcode bpchar(4) ,
    relation_zipcode2 bpchar(2) , relation_zipcodeext varchar(15) ,
    relation_zipcodeint varchar(8) );

    CREATE SEQUENCE public.relations_seq;

    CREATE TEMP TABLE EOF_TMP_TABLE AS SELECT SETVAL
    ('public.relations_seq', (SELECT MAX(relation_id) FROM
    public.relations));

    DROP TABLE EOF_TMP_TABLE;

    ALTER TABLE public.relations ALTER COLUMN relation_id SET DEFAULT
    nextval( 'public.relations_seq' );

    ALTER TABLE public.relations ADD CONSTRAINT public.relations_pk
    PRIMARY KEY (relation_id);
    =======================================

    The last ALTER TABLE will fail, it should be:

    ALTER TABLE public.relations ADD CONSTRAINT relations_pk PRIMARY KEY
    (relation_id);

    Here the original creation and alter code from pg_dump:

    =======================================
    CREATE TABLE relations (
         relation_id integer NOT NULL,
         relation_name character varying(50),
         relation_straat character varying(50),
         relation_zipcode character(4),
         relation_zipcode2 character(2),
         relation_zipcodeint character varying(8),
         relation_city character varying(30),
         relation_country_id integer,
         relation_phone1 character varying(20),
         relation_phone2 character varying(20),
         relation_emailold character varying(30),
         relation_telex character varying(20),
         relation_remarks text,
         relation_straat2 character varying(50),
         relation_zipcodeext character varying(15),
         relation_email character varying(50)
    ); 

    --
-- Name: relations_pkey; Type: CONSTRAINT; Schema: public; Owner:  
johan; Tablespace:
--

    ALTER TABLE ONLY relations
     ADD CONSTRAINT relations_pkey PRIMARY KEY (relation_id);
=======================================

    Regards,

    Johan Henselmans
http://www.netsense.nl
Tel: +31-20-6267538
Fax: +31-20-6273852

    This archive was generated by hypermail 2.0.0 : Thu May 31 2007 - 18:49:27 EDT