Phinx: Multiple schemas in same database pgsql

[qpautrat]

Hello,

I'm wondering if Phinx are able to handle multiple schemas for given database with PostgreSQL ?

I want to do something like this:

$this->createTable('foo.bar')

where foo is one of my schema and bar the table name.

Thanks !

[BallisticPain]

@qpautrat
Have you tried this? I just peeked down in the code, and I don't really see any reason why it wouldn't work.

[qpautrat]

Hi @BallisticPain ,

To be fair, it's a long time ago, i don't remember exactly, but i guess i tried and it didn't work.

[BallisticPain]

I assumed you had, but thought I would ask. I didn't see anything that would lead me to believe it's not possible, but it may have more to do with configuration. If it's not already a feature, then you could always bring a PR for it. ;)

Sorry I wasn't of any assistance.

[renegat59]

I have figured out that you can define the schema parameter in the phinx configuration, however, there is an issue there. I made a new schema called "blog" and set it in phinx config. The first migration (creating all the tables) went ok, and phinx created it's own table with the migration history or sth, but once I wanted to run another migration (alter table) it was complaining that the phinx table was not there, since he was searching for it in schema 'public'. I will recreate the issue tonight and paste here the exact errors and steps to recreate.

[paulop]

Hello,
I have this code in class extended of AbstractMigration

public function change()
    {        
        //select schemaname
        $schemas = $this->fetchAll("SELECT id, schemaname FROM empresas WHERE TRIM(schemaname) <> '' ;");        

        foreach($schemas as $schema){
            $schemaname = $schema['schemaname'];

            $this->table("recorrencias", ['schema'=>$schemaname])
            ->addColumn('ativo', 'boolean', [
                'default' => true,
                'null' => false,
            ])
            ->addColumn('convenio', 'string', [
                'default' => null,
                'limit' => 20,
                'null' => true,
            ])
            ->update();

doesn't work

and this

$this->table("$schemaname.recorrencias")
            ->addColumn('ativo', 'boolean', [
                'default' => true,
                'null' => false,
            ])
            ->addColumn('convenio', 'string', [
                'default' => null,
                'limit' => 20,
                'null' => true,
            ])
            ->update();

doesn't work too

$this->table("$schemaname.recorrencias")->exists();
//return false

Any help for update multiple schemas?

[paulop]

I solved using this
$this->getAdapter()->setOptions(array_replace($this->getAdapter()->getOptions(), ['schema' => $schemaname]));

[leovant]

I had to use @paulop workaround too. Using 0.8.1.

[dereuromark]

Are you able to provide a patch with your suggested changes as PR?
Otherwise please open a PR for documentation improvement.

[paulop]

@dereuromark In the documentation, you say to write an example here?

https://book.cakephp.org/3.0/en/phinx/migrations.html#creating-a-table

[dereuromark]

Yes :)

[corpsee]

This feature is implemented in #1193.

[othercorey]

Added in https://github.com/cakephp/phinx/pull/1193