ZF-7668: Support DB Link identifier as part of table name

Description

Oracle supports DB Links in order to query remote schemas via the following syntax


SELECT TABLE_NAME.* FROM SCHEMA.TABLE_NAME@LINK

Using Zend_Db_Table_Abstract, if I use something like


protected $_schema = 'SCHEMA';
protected $_name = 'TABLE_NAME@LINK';

the generated identifiers in SELECT statements appears as


SELECT "TABLE_NAME@LINK".* FROM "SCHEMA"."TABLE_NAME@LINK" ...

when it should be


SELECT "TABLE_NAME".* FROM "SCHEMA"."TABLE_NAME"@"LINK" ...

The issue is that the @ should not be quoted in the FROM clause nor should the link be included in the column prefix.

P.S. We're stuck at ZF 1.7.4 due to RHEL PHP version however a quick glance at 1.9.1 does not seem to indicate that this issue is resolved.

Comments

does this not work?


protected $_name = 'TABLE_NAME"@"LINK'

Sorry Ralph, it doesn't. That property generates queries like


SELECT "TABLE_NAME""@""LINK".* FROM "SCHEMA"."TABLE_NAME""@""LINK" ...

notice the extra quotes?

The problem isn't just quoting however. The link name should only appear in the FROM clause. See the last code example in my bug report above for the correct syntax.

Something tells me this will be difficult to implement into Zend_Db_Table_Abstract without affecting other, non-Oracle use cases. A solution is to create a synonym for the linked entity however this requires specific access to the schema in question and reduces portability.

The problem is that you can create a table with @ inside the name (at least with Oracle), this code functions:


CREATE TABLE "ZFTEST"."FOO@BAR" ("ID" NUMBER(10) NOT NULL, PRIMARY KEY("ID"));

If you access to this table from a link BAZ:


SELECT "ID" FROM "ZFTEST"."FOO@BAR"@BAZ

The link must be differentiate from the table name.

Perhaps you can use:


class My_Table extends Zend_Db_Table_Abstract
{
    public function init()
    {
        $this->_name = new Zend_Db_Expr('TABLE_NAME@LINK');
    }
}

or


class My_Table extends Zend_Db_Table_Abstract
{
    public function init()
    {
        $this->_name = new Zend_Db_Expr('"TABLE_NAME"@"LINK"');
    }
}

I didn't try but it should work.

No, that still won't work as the link identifier must only appear in the FROM clause.

As Mickael pointed out above, the link would have to be separate from the table name (ie, new property).

After a test this function for me:


class My_Table extends Zend_Db_Table_Abstract
{
    public function init()
    {
        $this->_name = new Zend_Db_Expr('"TABLE_NAME"@"LINK"');
        $this->_cols = array('ID', 'MY_VAL', 'MY_COMMENTS');
        $this->_primary = array('ID');
    }
}
$myTable = new My_Table();
var_dump($myTable->fetchAll()->toArray());

The generated SQL is:


SELECT t.* FROM "TABLE_NAME"@"LINK" t

I must define the column and the primary key by hand.

Except using the proposed solution, you can't do it.

Even if we add the support of the link in Zend_Db_Table_Abstract, you don't have access to the remote server to retrieve the description of the table.