Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Database Adapter not setting schema in PostgreSQL

Hi.

I use PostgreSQL as my application's database server, but the database adapter I create don't set the schema for the models and I need to do it manually using the models manager, how can I avoid setting the schema manually for every model?

This is how I'm creating the Adapter

my database config:

    'database' => [
        'host'     => 'localhost',
        'username' => 'username',
        'password' => 'password',
        'name'     => 'mydbname',
        'schema'   => 'app'
    ],

db connection in my app bootstrap:

        // Database
        $di->set('db', function () use ($config) {
            return new Phalcon\Db\Adapter\Pdo\Postgresql(array(
                'host'     => $config->database->host,
                'username' => $config->database->username,
                'password' => $config->database->password,
                'dbname'   => $config->database->name,
                'schema'   => $config->database->schema,
            ));
        });

This doesn't work!!!

$operators = new Operator;
$operator = $operators->findFirstByEmail($credentials['email']);

This works!!!

$operators = new Operator;
$app->modelsManager->setModelSchema($operators, 'myschema');
$operator = $operators->findFirstByEmail($credentials['email']);

If I don't set the schema manually then the model object can't find the tables:

Table "operators" doesn't exist on database when dumping meta-data for Operators.

I think the script is always reading the public schema instead of the one I set up.

Thanks.

edited Oct '14

I've found this solution too:

adding following method to my models abstract class

  public function initialize() {
        $this->getModelsManager()->setModelSchema($this, 'the_schema_name');
    }

but i'm looking for a better final solution!!!

MAYBE IT'S A BUG!



85.3k
Accepted
answer

Set your connection as:

$di->set('db', function () use ($config) {
    $connection = new Phalcon\Db\Adapter\Pdo\Postgresql(array(
        'host'     => $config->database->host,
        'username' => $config->database->username,
        'password' => $config->database->password,
        'dbname'   => $config->database->name,
        'schema'   => $config->database->schema,
    ));

    $connection->execute('set search_path=$user,public,' . $config->database->schema . ';');
    return $connection;
});

JustPostgreSQLThings

Dear @Phalcon I've accepted your solution but in action I saw that the following solution (that i said before) is more reliable and your solution didn't works 100% and Table "....." doesn't exist on database when dumping meta-data for ...... appears in some cases again.

public function initialize() {
    $this->getModelsManager()->setModelSchema($this, 'the_schema_name');
}