You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-user@db.apache.org by Hans Muñoz <Ha...@digibis.com> on 2006/02/22 14:36:49 UTC
Some bugs with Oracle
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.
Re: Some bugs with Oracle
Posted by Thomas Dudziak <to...@gmail.com>.
On 2/23/06, Hans Muñoz <Ha...@digibis.com> wrote:
> 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:
<snip>
I'll look into it.
> 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?.
Oracle-only is not the problem (that's why there are the platform
classes). The point is that DdlUtils should determine whether the
trigger and sequence are for auto-increment. Esp. whether the
sequence/procedure sets the values of columns. And that is the hard
thing.
Tom
Re: Some bugs with Oracle
Posted by Thomas Dudziak <to...@gmail.com>.
On 2/23/06, Hans Muñoz <Ha...@digibis.com> wrote:
> >> ----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);
This is not an internal index - "internal index" refers to indices
that the database creates automatically (i.e. you don't specify them)
for primary keys or foreign keys, and which are returned in the JDBC
metadata. Oracle does not define such indices (or at least they are
not present in JDBC) and hence the methods simply contain a "return
false". Your index however is created manually via SQL and thus is not
internal but rather a normal unique index (which plays the role of the
primary key as the table in question does not seem to contain one
which would be unique automatically).
> >Mhmm, this works on Oracle 10. What driver do you use ?
> Oracle 9i.
In general you should use the newest JDBC driver available because
they are usually downward compatible (Oracle drivers are) and contain
important bugfixes. In your case, you should use the driver that can
be downloaded from the Oracle 10 site.
As for the other things, I'll try to get an Oracle 9 installation
running in the next days and then check against it.
Tom
RE: Some bugs with Oracle
Posted by Hans Muñoz <Ha...@digibis.com>.
>> 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.
>Most of the issues you have, are related to the alteration of a
>database, which is not completely finished yet.
Ok, then i'll say last things i found and won't speak more about issues in
that part of the proyect.
In oracle 9i:
-Can't change from required to not required (doesn't drop the constraint)
-Can't add or remove one column from an index or unique entry
RE: Some bugs with Oracle
Posted by Hans Muñoz <Ha...@digibis.com>.
>> ----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.
Re: Some bugs with Oracle
Posted by Thomas Dudziak <to...@gmail.com>.
On 2/22/06, Hans Muñoz <Ha...@digibis.com> wrote:
> 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.
Most of the issues you have, are related to the alteration of a
database, which is not completely finished yet.
> ----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 ?
> ----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 ?
> 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.
> Foreing Key:
> - Doesn't care about onDelete or onUpdate on the xml
Yep, as I wrote in another thread (and there is probably an
outstanding issue): DdlUtils currently ignores these values.
> -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.
Don't know. I'll have to check that.
Tom