CODEWITHSUNDEEP
sql-servercakephpcakephp-2.9
Álvaro González
Álvaro González

Reputation: 146460

Read table from another database

Is it possible to read tables from another database using the builting CakePHP model features? I don't mean having an entirely different configuration in DATABASE_CONFIG but using the same host, user and password. The obvious thing:

class Provincia extends AppModel {
    public $useTable = 'shared_data.dbo.provincia';
}

class DebugController extends AppController {
    public function index() {
        /* @var $modeloProvincia Provincia */
        $modeloProvincia = ClassRegistry::init('Provincia');
        $provincias = $modeloProvincia->find('all');
    }
}

... triggers:

Error: Table shared_data.dbo.provincia for model Provincia was not found in datasource default.

Upvotes: 0

Views: 114

Answers (2)

Álvaro González
Álvaro González

Reputation: 146460

I'll share my findings so far...

Short answer: you cannot.

CakePHP magic depends heavily on information about tables and columns fetched from the INFORMATION_SCHEMA views. That information is gathered in \Sqlserver::listSources (list of tables) and \Sqlserver::describe (list of columns).

While it's possible to extend the datasource driver and reimplement these methods:

// Model/Datasource/CustomSqlserver.php
class CustomSqlserver extends Sqlserver {
}

class DATABASE_CONFIG {
    public $default = array(
        'datasource' => 'CustomSqlserver',
        // ...
    );
}

... that's just the tip of the iceberg. The data structures account for two levels:

  1. Schema (e.g. dbo)
  2. Table (e.g. users)

They aren't designed for an extra database level on top. As a result, you end up needing to patch so much code that it isn't worth the effort.

I've also been playing with Synonyms in SQL Server. It's a more promising path because, while you still need to write \CustomSqlserver::listSources and \CustomSqlserver::describe yourself, to most (not all) effects they behave like regular tables. The main restriction though is that there can't be duplicate table names.

Upvotes: 1

drmonkeyninja
drmonkeyninja

Reputation: 8540

You would need to add a new connection to DATABASE_CONFIG, but you could do this in the constructor so that you can inherit your default database credentials and just modify the database name:-

public function __construct() {
    $this->alt = $this->default;
    $this->alt['database'] = 'provincia';
}

Then in your Provincia model you can swap to the alt database connection:-

public function __construct($id = false, $table = null, $ds = null) {
    parent::__construct($id, $table, $ds);
    $this->useDbConfig = 'alt';
}

Upvotes: 0

Related Questions