0

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.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360

2 Answers2

1

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.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
0

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';
}
drmonkeyninja
  • 8,490
  • 4
  • 31
  • 59
  • I don't mean having an entirely different configuration in `DATABASE_CONFIG` but using the same host, user and password to access both databases at once (e.g. making joins on associated models that belong to different databases). – Álvaro González May 10 '17 at 11:50
  • @ÁlvaroGonzález as far as I am aware you will need a new configuration in `DATABASE_CONFIG` to achieve this, but by setting it in the constructor of `DATABASE_CONFIG` you can use the existing host, user and password, and just modify the database name. – drmonkeyninja May 10 '17 at 11:54
  • I'm starting to suspect that the datasource driver does not support it at all. At `\Sqlserver::listSources`it runs `SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES` and discards everything that doesn't show up here :( – Álvaro González May 10 '17 at 11:58