ZF-5082: Syntaxe compatibility issue between Pdo_Oci and oracle 8i and less in describeTable()

Description

in Zend_Db_Adapter_Pdo_Oci::describeTable()

the sql syntaxe used to get column description use ansi join.

ansi join is available since Oracle 9i :

Oracle 8 only support old oracle style join

I've modify the $sql ="..." line.

To be clean a test on the oracle version should be done and different syntaxe provided.

here is the Oracle 8 compatible syntaxe :


$sql = "SELECT TC.TABLE_NAME, TB.OWNER, TC.COLUMN_NAME, TC.DATA_TYPE,
                TC.DATA_DEFAULT, TC.NULLABLE, TC.COLUMN_ID, TC.DATA_LENGTH,
                TC.DATA_SCALE, TC.DATA_PRECISION, C.CONSTRAINT_TYPE, CC.POSITION
            FROM ALL_TAB_COLUMNS TC, ALL_TABLES TB, ALL_CONS_COLUMNS CC, ALL_CONSTRAINTS C
            WHERE TC.TABLE_NAME = :TBNAME
              AND TB.TABLE_NAME = TC.TABLE_NAME AND TB.OWNER = TC.OWNER
              AND TC.TABLE_NAME = CC.TABLE_NAME(+) AND TC.COLUMN_NAME = CC.COLUMN_NAME(+)
              AND CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME(+) AND CC.TABLE_NAME = C.TABLE_NAME(+) AND C.CONSTRAINT_TYPE(+) = 'P' ";

Comments

I haven't Oracle8 for the moment but if I run this SQL on 9i or 10g, I obtain the primary key field in multiple lines. As I remember 9i also supports this syntax. Have you any problems with table with primary key? Please run this command in SQLPlus and give us informations about result?

sorry,

I don't exactly understand what u want me to do ....

you want me to test the old syntax on 8i and give you results ??

anyway

I ve "fixed" Zend_Db_Adapter_Pdo_Oci::describeTable() with this old syntax and test it against a table with a primary key : it works fine.

...

ok,

well

it's a bit more complicated to avoid duplicate row for some columns....

the sql must be :


SELECT TC.TABLE_NAME, TC.OWNER, TC.COLUMN_NAME, TC.DATA_TYPE,
                TC.DATA_DEFAULT, TC.NULLABLE, TC.COLUMN_ID, TC.DATA_LENGTH,
                TC.DATA_SCALE, TC.DATA_PRECISION, CC.CONSTRAINT_TYPE, CC.POSITION
            FROM ALL_TAB_COLUMNS TC, (
            SELECT ALL_CONSTRAINTS.OWNER, ALL_CONSTRAINTS.TABLE_NAME, ALL_CONS_COLUMNS.COLUMN_NAME, ALL_CONSTRAINTS.CONSTRAINT_TYPE, ALL_CONS_COLUMNS.POSITION
            from ALL_CONSTRAINTS, ALL_CONS_COLUMNS
              WHERE ALL_CONS_COLUMNS.CONSTRAINT_NAME = ALL_CONSTRAINTS.CONSTRAINT_NAME 
                AND ALL_CONS_COLUMNS.TABLE_NAME = ALL_CONSTRAINTS.TABLE_NAME
                AND ALL_CONS_COLUMNS.OWNER = ALL_CONSTRAINTS.OWNER
                AND ALL_CONSTRAINTS.CONSTRAINT_TYPE = 'P'
                AND ALL_CONSTRAINTS.TABLE_NAME = 'FOO'
                ) CC
            WHERE TC.TABLE_NAME = 'FOO'
              AND TC.OWNER = CC.OWNER(+) AND TC.TABLE_NAME = CC.TABLE_NAME(+) AND TC.COLUMN_NAME = CC.COLUMN_NAME(+);

and php code



...
        $consql="SELECT ALL_CONSTRAINTS.OWNER, ALL_CONSTRAINTS.TABLE_NAME, ALL_CONS_COLUMNS.COLUMN_NAME, ALL_CONSTRAINTS.CONSTRAINT_TYPE, ALL_CONS_COLUMNS.POSITION
            from ALL_CONSTRAINTS, ALL_CONS_COLUMNS
              WHERE ALL_CONS_COLUMNS.CONSTRAINT_NAME = ALL_CONSTRAINTS.CONSTRAINT_NAME 
                AND ALL_CONS_COLUMNS.TABLE_NAME = ALL_CONSTRAINTS.TABLE_NAME
                AND ALL_CONS_COLUMNS.OWNER = ALL_CONSTRAINTS.OWNER
                AND ALL_CONSTRAINTS.CONSTRAINT_TYPE = 'P'
                AND ALL_CONSTRAINTS.TABLE_NAME = :TBNAME";
        $bind[':TBNAME'] = $tableName;
        if ($schemaName) {
            $sql .= ' AND ALL_CONS_COLUMNS.OWNER = :SCNAME';
            $bind[':SCNAME'] = $schemaName;
        }
 
        $sql="SELECT TC.TABLE_NAME, TC.OWNER, TC.COLUMN_NAME, TC.DATA_TYPE,
                TC.DATA_DEFAULT, TC.NULLABLE, TC.COLUMN_ID, TC.DATA_LENGTH,
                TC.DATA_SCALE, TC.DATA_PRECISION, CC.CONSTRAINT_TYPE, CC.POSITION
            FROM ALL_TAB_COLUMNS TC, ($consql) CC
            WHERE TC.TABLE_NAME = :TBNAME
              AND TC.OWNER = CC.OWNER(+) AND TC.TABLE_NAME = CC.TABLE_NAME(+) AND TC.COLUMN_NAME = CC.COLUMN_NAME(+)";      
        if ($schemaName) {
            $sql .= ' AND TB.OWNER = :SCNAME';
        }
        
        $sql .= ' ORDER BY TC.COLUMN_ID';

...

That's exactly what I want :) . I link this to ZF-5099 to be able to read Oracle server version.

Resolved in SVN12982 but NOT merged to 1.7-branch for the moment

Merged to 1.7-branch