Missing relations elements in generated code
Manfred Schäfer
i'm generating source code with jooq 1.5.9. The debug output tells me,
that JOOQ has correctly identified the primary keys:
DEBUG [main] (JooqLogger.java:142) - Adding primary key :
SYS_C0013879 (LOCATIONS.MTRLFRMTRLRDRTMPLT.PK__MATERIALORDERTEMPLATE_ID)
DEBUG [main] (JooqLogger.java:142) - Adding primary key :
SYS_C0013879 (LOCATIONS.MTRLFRMTRLRDRTMPLT.PK_ID)
But in the generated code all primary and foreign keys are missing.
The generation properties file conatins the line
generator.generate.relations=true
If i'm using the same libs with my toy XE Database, the generation
works as expected!?
regards,
Manfred
Lukas Eder
This works fine for me both in my Oracle XE development database, and
in a "full-fledged" 11g database, so I'm guessing it's not a version
issue here (unless you have some other version than 10g or 11g?
Can you do these things for me:
- Provide me with the generated Keys.java class (if it exists)
- Provide me with any errors in your generation log, that you might have gotten
- Provide me with the generated Mtrlfrmtrlrdrtmplt.java class
- Provide me with the definition of the MTRLFRMTRLRDRTMPLT table
(CREATE TABLE ... statement, including all key specifications)
Cheers
Lukas
2011/5/30 Manfred Schäfer <mscha...@googlemail.com>:
Manfred Schäfer
My Oracle Version is 11g 11.1.0.7.0
the Keys class is empty:
import javax.annotation.Generated;
import org.jooq.impl.AbstractKeys;
/**
* This class is generated by jOOQ.
*
* A class modelling foreign key relationships between tables of the
LOCATIONS2 schema
*/
@Generated(value = "http://jooq.sourceforge.net",
comments = "This class is generated by jOOQ")
@SuppressWarnings({"unchecked"})
public class Keys extends AbstractKeys {
// IDENTITY definitions
// UNIQUE and PRIMARY KEY definitions
// FOREIGN KEY definitions
/**
* No instances
*/
private Keys() {}
}
I've got one error in the generation log which looks unrelated:
DEBUG [main] (JooqLogger.java:142) - Adding unique key :
UNIQUE_PLAN_LOCATION (LOCATIONS2.LOCATIONPLAN.FK__LOCATION_KEY)
DEBUG [main] (JooqLogger.java:142) - Executing query : select
"cc1"."CONSTRAINT_NAME" "fk_name", "cc2"."CONSTRAINT_NAME" "uk_name",
"cc1"."TABLE_NAME" "fk_table", "cc2"."TABLE_NAME" "uk_table",
"cc1"."COLUMN_NAME" "fk_column" from "SYS"."ALL_CONSTRAINTS" join
"SYS"."ALL_CONS_COLUMNS" "cc1" on "cc1"."CONSTRAINT_NAME" =
"SYS"."ALL_CONSTRAINTS"."CONSTRAINT_NAME" join
"SYS"."ALL_CONS_COLUMNS" "cc2" on ("cc2"."CONSTRAINT_NAME" =
"SYS"."ALL_CONSTRAINTS"."R_CONSTRAINT_NAME" and "cc2"."POSITION" =
"cc1"."POSITION") where ("SYS"."ALL_CONSTRAINTS"."OWNER" =
'LOCATIONS2' and "cc1"."OWNER" = 'LOCATIONS2' and "cc2"."OWNER" =
'LOCATIONS2' and "cc1"."CONSTRAINT_NAME" = (select
"SYS"."ALL_CONSTRAINTS"."CONSTRAINT_NAME" from
"SYS"."ALL_CONS_COLUMNS" join "SYS"."ALL_CONSTRAINTS" on
"SYS"."ALL_CONS_COLUMNS"."CONSTRAINT_NAME" =
"SYS"."ALL_CONSTRAINTS"."CONSTRAINT_NAME" where
("SYS"."ALL_CONSTRAINTS"."CONSTRAINT_TYPE" = 'R' and
"SYS"."ALL_CONS_COLUMNS"."OWNER" = 'LOCATIONS2' and
"SYS"."ALL_CONS_COLUMNS"."TABLE_NAME" = "cc1"."TABLE_NAME" and
"SYS"."ALL_CONS_COLUMNS"."COLUMN_NAME" = "cc1"."COLUMN_NAME"))) order
by "fk_name" asc, "cc2"."POSITION" asc
DEBUG [main] (JooqLogger.java:142) - Statement executed : Total: 14.371ms
ERROR [main] (JooqLogger.java:268) - Error while fetching relations
java.sql.SQLException: ORA-01427: Unterabfrage für eine Zeile liefert
mehr als eine Zeile
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
at org.jooq.impl.AbstractResultProviderQuery.executeLazy(AbstractResultProviderQuery.java:125)
at org.jooq.impl.AbstractResultProviderQuery.execute(AbstractResultProviderQuery.java:68)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:90)
at org.jooq.util.oracle.OracleDatabase.loadForeignKeys(OracleDatabase.java:182)
at org.jooq.util.AbstractDatabase.getRelations0(AbstractDatabase.java:424)
at org.jooq.util.AbstractDatabase.getRelations(AbstractDatabase.java:308)
at org.jooq.util.DefaultColumnDefinition.getPrimaryKey(DefaultColumnDefinition.java:141)
at org.jooq.util.AbstractTableDefinition.getMainUniqueKey(AbstractTableDefinition.java:71)
at org.jooq.util.DefaultGenerator.generate(DefaultGenerator.java:460)
at org.jooq.util.GenerationTool.main(GenerationTool.java:142)
at org.jooq.util.GenerationTool.main(GenerationTool.java:101)
the DDL statement (reengineered from SQL Developer)
CREATE TABLE "LOCATIONS"."MTRLFRMTRLRDRTMPLT"
(
"PK__MATERIALORDERTEMPLATE_ID" NUMBER(20,0) NOT NULL ENABLE,
"FK__MATERIAL_NO" VARCHAR2(256 BYTE),
"ADDTOAREA" VARCHAR2(1 BYTE),
"PK_ID" NUMBER(20,0) NOT NULL ENABLE,
"IGNOREEXISTINGMATERIAL" VARCHAR2(1 BYTE),
"QUANTITY" NUMBER(10,0),
PRIMARY KEY ("PK__MATERIALORDERTEMPLATE_ID", "PK_ID") USING INDEX
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL
65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"LOCATIONS_DATA" ENABLE,
CONSTRAINT "MTRLFRMTRLRDRTMPLT_MTRL_MTRL" FOREIGN KEY
("FK__MATERIAL_NO") REFERENCES "LOCATIONS"."MATERIAL" ("PK_NO") ON
DELETE
SET NULL ENABLE,
CONSTRAINT "MTRLFRMTRLRDRTMPLT_MTRLRDRTMPL" FOREIGN KEY
("PK__MATERIALORDERTEMPLATE_ID") REFERENCES
"LOCATIONS"."MATERIALORDERTEMPLATE" ("PK_ID") ON
DELETE CASCADE ENABLE
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "LOCATIONS_DATA" ;
CREATE INDEX "LOCATIONS"."IDX_MTRLFRMTRLRDRTMPLT_MTRLRDR" ON
"LOCATIONS"."MTRLFRMTRLRDRTMPLT"
(
"PK__MATERIALORDERTEMPLATE_ID"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "LOCATIONS_INDEX" ;
CREATE INDEX "LOCATIONS"."IDX_MTRLFRMTRLRDRTMPLT_MTRL_MT" ON
"LOCATIONS"."MTRLFRMTRLRDRTMPLT"
(
"FK__MATERIAL_NO"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "LOCATIONS_INDEX" ;
CREATE UNIQUE INDEX "LOCATIONS"."SYS_C005603" ON
"LOCATIONS"."MTRLFRMTRLRDRTMPLT"
(
"PK__MATERIALORDERTEMPLATE_ID", "PK_ID"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE
(
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "LOCATIONS_DATA" ;
that was a little bit messy but hopefully it will help you.
cheers,
Manfred
2011/5/30 Lukas Eder <lukas...@gmail.com>:
Lukas Eder
Looks like I have a SQL-Bug in my Oracle integration for source code generation:
java.sql.SQLException: ORA-01427: Unterabfrage für eine Zeile liefert
mehr als eine Zeile
I'll check into that more in-depth and potentially release a 1.5.9.1
patch for Oracle users
Thanks for reporting this
Lukas
2011/6/1 Manfred Schäfer <mscha...@googlemail.com>:
Lukas Eder
The reported ORA-01427 is caused by a bad query against the Oracle
dictionary views. The subquery CONSTRAINT_NAME = (select...) seems to
be invalid in some special foreign key configurations. Unfortunately,
I could not reproduce this with the partial schema you provided, but
the overall query is much too complex for the data it needs to fetch.
I could rephrase the query to produce an equivalent result, and the
ORA-01427 shouldn't be possible anymore.
This is fixed on trunk with
https://sourceforge.net/apps/trac/jooq/ticket/569
And I released a 1.6.0-SNAPSHOT version:
https://oss.sonatype.org/content/repositories/snapshots/org/jooq/
Or you wait until next week for the 1.6.0 release.
Cheers
Lukas
Manfred Schäfer
i've repeated the generation and now it works. Thank you. But there is
another issue: One record class does have two methods with same
signature. I'll report more details when i find the time.
cheers,
Manfred
2011/6/2 Lukas Eder <lukas...@gmail.com>:
Lukas Eder
What are those methods? Getters or navigation methods? What is the
underlying table specification that causes this to happen?
Cheers
Lukas
2011/6/10 Manfred Schäfer <mscha...@googlemail.com>: