Thanks for all the help…
I was able to find a work around for getting Gii to generate the code. The bug (which I believe is an oracle issue) was in the query itself.
In the ‘framework/db/schema/oci/COciSchema.php’ file there is a function to find table contraints. I listed the actual SQL above.
When running that SQL by hand I get an oracle error and here is why and the fix:
SELECT
D.constraint_type as CONSTRAINT_TYPE,
C.COLUMN_NAME,
C.position,
D.r_constraint_name,
E.table_name as table_ref,
f.column_name as column_ref, /* <--- added comma so I can add the following line */
C.table_name /* <--- If we do not also select this line we cause oracle error */
FROM
ALL_CONS_COLUMNS C
inner join
ALL_constraints D on D.OWNER = C.OWNER
and
D.constraint_name = C.constraint_name
left join
ALL_constraints E on E.OWNER = D.r_OWNER
and
E.constraint_name = D.r_constraint_name
left join
ALL_cons_columns F on F.OWNER = E.OWNER
and
F.constraint_name = E.constraint_name
and
F.position = c.position WHERE C.OWNER = 'NETOPS_SCHEMA'
and
C.table_name = 'NETOPS_HOSTS'
and
D.constraint_type <> 'P'
order by
d.constraint_name,
c.position
For some reason we have to add ‘C.table_name’ to the columns to select or Oracle will generate an error and core dump!
This should not be necessary, using a join on columns not viewed is acceptable in SQL as far as I know. I do know that adding the columns acts as a work around though.
The actual code in the file now works for model/crud generation and looks like this:
$sql=<<<EOD
SELECT D.constraint_type as CONSTRAINT_TYPE, C.COLUMN_NAME, C.position, D.r_constraint_name,
E.table_name as table_ref, f.column_name as column_ref, C.TABLE_NAME
FROM ALL_CONS_COLUMNS C
inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
left join ALL_constraints E on E.OWNER = D.r_OWNER and E.constraint_name = D.r_constraint_name
left join ALL_cons_columns F on F.OWNER = E.OWNER and F.constraint_name = E.constraint_name and F.position = c.position
WHERE C.OWNER = '{$table->schemaName}'
and C.table_name = '{$table->name}'
and D.constraint_type <> 'P'
order by d.constraint_name, c.position
EOD;
*NOTE the added ‘, C.TABLE_NAME’ at the end of the line before the ‘FROM’ part starts.
The next error I am now having is that the generated code fails with the following error:
Doing more back tracking, I placed a vardump in the code in the COciSchema.php file at the end of the function ‘findColumns’.
Here is where it gets odd again… I added a var_dump and an exit right above the return line and var_dumped the resulting $table object. This var_dump showed ALL columns and the properties for them. So it clearly does find the table despite the fact that the error says ‘table or view does not exist’.
I am still backtracking to see where that error is generated and why and will update on my findings as I find them.
<EDIT>
I am not seeing the problem. I have dug deep into the CActiveRecord, CDbCommandBuilder, CDbSchema, and others.
From inside the protected|views|nETOPSHOSTS|index.php, if I comment out the CListView section and var_dump the $dataProvider variable I get a dumped view of all columns and column descriptions along with other data.
This tells me that the table is seen and can be queried (at least for details on structure).
However CListView (using the same $dataProvider variable returns the error ‘table or view does not exist’
I am stumped… any help here would be appreciated.
</EDIT>
