Problem with Oracle schemas

[svytas]

PHP Version: 5.5.12, Phalcon: 2.0.8, Oracle: 10

When there is only one schema in Oracle database - everything is OK. Problems comes when there are multiple schemas with the same tables: when I try to insert data I get error: SQLSTATE[HY000]: General error: 957 OCIStmtExecute: ORA-00957: duplicate column name
In the db log file I see that phalcon tries to get table columns from ALL_TABLES, ALL_TAB_COLUMNS, ALL_CONS_COLUMNS without filtering owner, just by table name.

Setting up schema name in configuration made no difference, so I set schema directly in Model:

public function initialize()
{
        $this->setConnectionService('db');
        $config =  \Phalcon\DI\FactoryDefault::getDefault()->getShared('config');
        $this->setSchema($config->database->schema);
}

Now I see that phalcon tries to filter by owner, but not for all tables:

SELECT TC.COLUMN_NAME, TC.DATA_TYPE, TC.DATA_LENGTH, TC.DATA_PRECISION, TC.DATA_SCALE, TC.NULLABLE, C.CONSTRAINT_TYPE, TC.DATA_DEFAULT, CC.POSITION 
FROM ALL_TAB_COLUMNS TC 
LEFT JOIN (ALL_CONS_COLUMNS CC JOIN ALL_CONSTRAINTS C ON (CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND CC.TABLE_NAME = C.TABLE_NAME AND CC.OWNER = C.OWNER AND C.CONSTRAINT_TYPE = 'P')) ON TC.TABLE_NAME = CC.TABLE_NAME AND TC.COLUMN_NAME = CC.COLUMN_NAME 
WHERE TC.TABLE_NAME = 'TESTTABLE' 
AND TC.OWNER = 'TESTUSER'
ORDER BY TC.COLUMN_ID

JOINS is made without owner what leads to duplicate primary key column and the same duplicate column name error:

COLUMN_NAME	DATA_TYPE
ID	NUMBER
ID	NUMBER
FIELD1	NVARCHAR2
FIELD2	NVARCHAR2

I think SQL should look like this (I added AND TC.OWNER = CC.OWNER):

SELECT TC.COLUMN_NAME, TC.DATA_TYPE, TC.DATA_LENGTH, TC.DATA_PRECISION, TC.DATA_SCALE, TC.NULLABLE, C.CONSTRAINT_TYPE, TC.DATA_DEFAULT, CC.POSITION 
FROM ALL_TAB_COLUMNS TC 
LEFT JOIN (ALL_CONS_COLUMNS CC JOIN ALL_CONSTRAINTS C ON (CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND CC.TABLE_NAME = C.TABLE_NAME AND CC.OWNER = C.OWNER AND C.CONSTRAINT_TYPE = 'P')) ON TC.TABLE_NAME = CC.TABLE_NAME AND TC.COLUMN_NAME = CC.COLUMN_NAME 
AND TC.OWNER = CC.OWNER
WHERE TC.TABLE_NAME = 'TESTTABLE' 
AND TC.OWNER = 'TESTUSER'
ORDER BY TC.COLUMN_ID

Also would be great if schemas where supported from configuration.

[sergeyklay]

Phalcon does not support Oracle. Refer to #12008