db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hans Muñoz <Hans.mu...@digibis.com>
Subject RE: Some bugs with Oracle
Date Thu, 23 Feb 2006 09:35:12 GMT
>> ----Getting DDL and generation xml from the database----
>> - Fails to remove internal primary keys (JDBCModelReader.java in
>> removeInternalPrimaryKeyIndex:629 - always false) So it fails while
triyng
>> to Create tables from xml generated, because is duplicated.

>? Could you post the table definition SQL, and the generated XML ?
I think the problem is in the method removeInternalPrimaryKeyIndex as long
as it only have one line... return false; so it can never remove the
duplicate index.
The original Database hasn't been created with ddlUtils, the SQL definition
of one table is:

ALTER TABLE ACCESOS DROP PRIMARY KEY CASCADE;
DROP TABLE ACCESOS CASCADE CONSTRAINTS;

CREATE TABLE ACCESOS
(
  IDACCESO    NUMBER(10)                        NOT NULL,
  USUARIO     VARCHAR2(255 BYTE)                NOT NULL,
  OPERACION   VARCHAR2(255 BYTE)                NOT NULL,
  FECHA       DATE                              NOT NULL,
  ADICIONAL1  NUMBER(10),
  ADICIONAL2  VARCHAR2(255 BYTE),
  NUMERO      NUMBER(10)                        DEFAULT 1
NOT NULL
)
TABLESPACE BIBDESA_DAT
LOGGING
NOCACHE
NOPARALLEL;


CREATE UNIQUE INDEX PK_ACCESOS ON ACCESOS
(IDACCESO)
LOGGING
TABLESPACE BIBDESA_IND
NOPARALLEL;


CREATE OR REPLACE TRIGGER TRG_ACCESOS
BEFORE INSERT
ON ACCESOS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
   IF :new.IDACCESO IS NULL THEN
   	  SELECT SEQ_ACCESOS.NEXTVAL INTO :new.IDACCESO FROM dual;
   END IF;
END ;
/
SHOW ERRORS;



ALTER TABLE ACCESOS ADD (
  CONSTRAINT PK_ACCESOS PRIMARY KEY (IDACCESO)
    USING INDEX
    TABLESPACE BIBDESA_IND);

**and XML generated**

  <database name="test">
    <table name="ACCESOS">
      <column name="IDACCESO" primaryKey="true" required="true"
type="DECIMAL" size="10" autoIncrement="false"/>
      <column name="USUARIO" primaryKey="false" required="true"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="OPERACION" primaryKey="false" required="true"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="FECHA" primaryKey="false" required="true"
type="TIMESTAMP" size="7" autoIncrement="false"/>
      <column name="ADICIONAL1" primaryKey="false" required="false"
type="DECIMAL" size="10" autoIncrement="false"/>
      <column name="ADICIONAL2" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="NUMERO" primaryKey="false" required="true"
type="DECIMAL" size="10" default="1" autoIncrement="false"/>
      <unique name="PK_ACCESOS">  <- this cause the problem should not
appear
        <unique-column name="IDACCESO"/>
      </unique>
    </table>
  </database>

>> ----Generate database from xml----
>>
>> About autonumeric columns:
>> The trigger to control the sequence doesn't compile (have BEGIN but
doesn't
>> have END), about this i found that the proyect use tokenizer(...,';') to
>> decide wich commands to execute so althought you put the correct sentence
it
>> would fail

>Mhmm, this works on Oracle 10. What driver do you use ?
Oracle 9i.

>> Also it doesn't check if the column has change the property "autonumeric"
>> while altering a database (should go on SQLBuilder line 1366?)

>Yep, this is expected, because there is currently no way to determine
>this via JDBC (at least none that I know of). If you know some clever
>SQL that is able to determine the trigger  and sequence structure,
>then please let me know.

Well sure not a smart and standard way :).
In oracle you can get info about triggers and sequences from user_sequences
and user_triggers tables, this can be usefull but will only work with
Oracle. From here, the rest still not easy, the easy and bad solution is to
make it only work with databases created with ddlutils checking (while
looking for changes) the names of triggers/sequences?.

Hans M.



Mime
View raw message