[PEAR-BUG] Bug #18669 [Opn]: Oracle error when PHPUnit read the metadatas of a table

Edit report at http://pear.php.net/bugs/bug.php?id=18669&edit=1

 ID:               18669
 Updated by:       [email protected]
 Reported By:      dlanoire at gmail dot com
 Summary:          Oracle error when PHPUnit read the metadatas of a
                    table
 Status:           Open
 Type:             Bug
 Package:          PHPUnit
 Operating System: Windows XP SP3
 Package Version:  Unknown
 PHP Version:      5.3.6
 Roadmap Versions: 
 New Comment:

I found the bug : it's the class
PHPUnit_Extensions_Database_DB_MetaData_Oci which is using the table
USER_TAB_COLUMNS the get the columns of a table BUT there is no column
OWNER in this table because it shows only the table columns the current
user owns.
Instead of this table, you should use the table ALL_TAB_COLUMNS which
shows the tables the current user is granted.

So in the method loadColumnInfo(), you must replace the code :
        $query = "SELECT DISTINCT COLUMN_NAME
                    FROM USER_TAB_COLUMNS
                   WHERE TABLE_NAME='".$tableParts['table']."'
                    $ownerQuery
                   ORDER BY COLUMN_NAME";

by :
        $query = "SELECT DISTINCT COLUMN_NAME
                    FROM ALL_TAB_COLUMNS
                   WHERE TABLE_NAME='".$tableParts['table']."'
                    $ownerQuery
                   ORDER BY COLUMN_NAME";

And same bug to get the primary key of a table, you must replace the
code :
        $keyQuery = "SELECT b.column_name
                       FROM user_constraints a, user_cons_columns b
                      WHERE a.constraint_type='P'
                        AND a.constraint_name=b.constraint_name
                        $conOwnerQuery
                        AND a.table_name = '".$tableParts['table']."'
";

by :
        $keyQuery = "SELECT b.column_name
                       FROM all_constraints a, all_cons_columns b
                      WHERE a.constraint_type='P'
                        AND a.constraint_name=b.constraint_name
                        $conOwnerQuery
                        AND a.table_name = '".$tableParts['table']."'
";


And it works fine every time, even if the current user is not the owner
of the tables he uses with the tables prefixed by the schema


Previous Comments:
------------------------------------------------------------------------

[2011-07-15 22:19:22] dlanoire

And here in the SQL to create the table :

CREATE TABLE  "TEST_PDO" 
   (	"ID" NUMBER, 
	"VALUE" VARCHAR2(250), 
	"QTY" NUMBER, 
	 CONSTRAINT "TEST_PDO_PK" PRIMARY KEY ("ID") ENABLE
   )

------------------------------------------------------------------------

[2011-07-15 22:13:33] dlanoire

The content of the xml dataset file seed.xml used by my demo script is
:

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
    <DENIS.TEST_PDO ID="1" VALUE="eviter les caracteres accentues"
QTY="421.35" />
    <DENIS.TEST_PDO ID="2" VALUE="libelle sans accents" QTY="-17.85" />
</dataset>

------------------------------------------------------------------------

[2011-07-15 22:08:21] dlanoire

Description:
------------
My PHP version is 5.3.5 and PHPUnit version is 3.5.14 (unknown versions
in the listboxes for whatever reason ?).
I use Oracle Express 10g server with PHPUnit (and DBUnit). When I try to
use a xml dataset with the schema of the table, I get an error :
ORA-00904: "OWNER": invalid identifier: 
SELECT DISTINCT COLUMN_NAME
                    FROM USER_TAB_COLUMNS
                   WHERE TABLE_NAME='TEST_PDO'
                     AND OWNER = 'DENIS'
                   ORDER BY COLUMN_NAME

Test script:
---------------
class MyTest extends PHPUnit_Extensions_Database_TestCase {
    static private $pdo = null;
    private $conn = null;
       
    protected function getConnection()
    {
    	if ($this->conn === null) {
            if (self::$pdo == null) {
            	self::$pdo = new PDO('oci://localhost/XE', 'denis',
'mypassw');
            }
            $this->conn = $this->createDefaultDBConnection(self::$pdo,
'DENIS');
        }
		self::$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE);
        return $this->conn;
    }

    protected function getDataSet()
    {
    	return
$this->createFlatXMLDataSet(dirname(__FILE__).'/files/seed.xml');
    }

    public function testDummy () {}
}


Expected result:
----------------
ORA-00904: "OWNER": invalid identifier: SELECT DISTINCT COLUMN_NAME
                    FROM USER_TAB_COLUMNS
                   WHERE TABLE_NAME='TEST_PDO'
                     AND OWNER = 'DENIS'
                   ORDER BY COLUMN_NAME

------------------------------------------------------------------------


-- 
Edit this bug report at http://pear.php.net/bugs/bug.php?id=18669&edit=1