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 Some bugs with Oracle
Date Wed, 22 Feb 2006 13:36:49 GMT
Hi, i'm a little new to this proyect, but i've been trying it for some days
with Oracle 9i and i found some problems. I'm sure most of them are already
documented so sorry if i repeat them. If its a problem about the use or
configuration let me now.

----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.

----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

CREATE OR REPLACE TRIGGER "XXX" BEFORE INSERT ON "YYY" FOR EACH ROW
BEGIN
SELECT "seq_...".nextval INTO :new."..." FROM dual;
END; <-- need this

A posible solution is to finish sentences with ";\n" and then write the
commands like this:
CREATE OR REPLACE TRIGGER "XXX" BEFORE INSERT ON "YYY" FOR EACH ROW BEGIN
SELECT "seq_...".nextval INTO :new."..." FROM dual; END;

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

Foreing Key:
- Doesn't care about onDelete or onUpdate on the xml

While altering:
-Dropping indexes
The command for dropping indexes use "DROP (index) ON (table);" and should
be "DROP (index);"

-Change types:
If the column was not null and still now not null (ie.-while trying to
change from Number to Varchar ) it makes a Oracle Error because the
constraint (NOT NULL) already exist.

SQL Command ALTER TABLE "BIBCAMPOS"
    MODIFY "SECUENCIA" VARCHAR2(10) NOT NULL failed with ORA-01442 <- not
null shouldn't be here

-Trying to change columns that are the same
I create the database, but not the tables, I use the code below to create 2
tables and then use the same xml but using "alterTables" and it tries to
change columns that are already the same (the check on SQLBuilder line
1366?). I think its because it checks the native type from the database to
the JDBC type.

22-feb-2006 12:32:11 org.apache.ddlutils.platform.SqlBuilder alterTable
INFO:   desired = Column [name=VALOREXT; javaName=null; type=OTHER;
typeCode=1111; size=4000; required=false; primaryKey=false;
autoIncrement=false; defaultValue=null; precisionRadix=4000; scale=0]
22-feb-2006 12:32:11 org.apache.ddlutils.platform.SqlBuilder alterTable
INFO:   current = Column [name=VALOREXT; javaName=null; type=OTHER;
typeCode=1111; size=4000; required=false; primaryKey=false;
autoIncrement=false; defaultValue=null; precisionRadix=4000; scale=0]
(al params are the same)

xml
  <database name="test">
    <table name="BIBSUBCAMPOS">
      <column name="IDREGISTRO" primaryKey="true" required="true"
type="DECIMAL" size="10" autoIncrement="false"/>
      <column name="SECUENCIACAMPO" primaryKey="true" required="true"
type="DECIMAL" size="10" autoIncrement="false"/>
      <column name="SECUENCIASUBCAMPO" primaryKey="true" required="true"
type="DECIMAL" size="10" autoIncrement="false"/>
      <column name="LETRASUBCAMPO" primaryKey="false" required="false"
type="VARCHAR" size="1" autoIncrement="false"/>
      <column name="VALOR" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="VALOREXT" primaryKey="false" required="false"
type="OTHER" size="4000" autoIncrement="false"/>
      <column name="CODENLACE" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <foreign-key foreignTable="BIBCAMPOS"
name="BIBSUBCAMPOS_FK21063286181246" onUpdate="none" onDelete="cascade">
        <reference local="IDREGISTRO" foreign="IDREGISTRO"/>
        <reference local="SECUENCIACAMPO" foreign="SECUENCIA"/>
      </foreign-key>
    </table>
    <table name="BIBCAMPOS">
      <column name="IDREGISTRO" primaryKey="true" required="true"
type="DECIMAL" size="10" autoIncrement="false"/>
      <column name="CODCAMPO" primaryKey="false" required="false"
type="VARCHAR" size="3" autoIncrement="false"/>
      <column name="SECUENCIA" primaryKey="true" required="true"
type="DECIMAL" size="10" autoIncrement="false"/>
      <column name="INDICADOR1" primaryKey="false" required="false"
type="VARCHAR" size="1" autoIncrement="false"/>
      <column name="INDICADOR2" primaryKey="false" required="false"
type="VARCHAR" size="1" autoIncrement="false"/>
      <column name="VALOR" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="VALOREXT" primaryKey="false" required="false"
type="OTHER" size="4000" autoIncrement="false"/>
      <column name="FUM" primaryKey="false" required="false"
type="TIMESTAMP" size="7" autoIncrement="false"/>
      <column name="UUM" primaryKey="false" required="false" type="VARCHAR"
size="32" autoIncrement="false"/>
      <column name="IDCAMPO" primaryKey="false" required="false"
type="DECIMAL" size="10" autoIncrement="true"/>
      <unique name="BIBCAM_IDCAMPO">
        <unique-column name="IDCAMPO"/>
      </unique>
    </table>
  </database>


Regards,
Hans M.



Mime
View raw message