Database Adapter not setting schema in PostgreSQL

Question

Database Adapter not setting schema in PostgreSQL

Aboozar Ghaffari Oct '14

Created Oct '14	Last Reply Oct '14	Replies 1	Views 2362	Votes 1

Aboozar Ghaffari 10.5k

Oct '14

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.

1

Phalcon
98.9k

Accepted
answer

Oct '14

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

Aboozar Ghaffari · Answer 1 · 2014-10-03T07:48:33-07:00

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!

Aboozar Ghaffari · Answer 2 · 2014-10-10T00:01:04-07:00

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');
}