Yii2: Postgresql schema ignored

[thomaslindgaard]

What steps will reproduce the problem?

1. Install Yii2 advanced app using composer and run ./init.
2. Create a Postgresql database.
3. Create a schema in the database (different from the user default schema so it is not in the search path).
4. Edit the database config in common/config/main-local.php as follows:

return [
    'components' => [
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'pgsql:host=myserver;dbname=mydb',
            'username' => 'user',
            'password' => 'password',
            'charset' => 'utf8',
            'schemaMap' => [
                'pgsql'=> [
                    'class'=>'yii\db\pgsql\Schema',
                    'defaultSchema' => 'myschema',
                ],
            ],
        ],
        ...
];

1. Run migration: ./yii migrate.
   

   What is the expected result?

Migration and user tables should exist under myschema in the database.

What do you get instead?

Tables are created under a different schema (the one that I assume is the default for the user - I don't have admin rights to the database).

Additional info

Using a controllerMap directive I can get the migration table created under the correct schema (haven't found out how to do that with the user table yet):

    'controllerMap' => [
        'migrate' => [
            'class' => 'yii\console\controllers\MigrateController',
            'migrationTable' => "myschema.migration",
        ],
    ],

But without specifying a controllerMap for user then that table is still created under the wrong schema.

| Q | A |
| --- | --- |
| Yii version | 2.0.9 |
| PHP version | 5.5.38 / 7.0.10 |
| Operating system | MacOs X (El Capitan) |

[thomaslindgaard]

This looks like a related issue: https://github.com/yiisoft/yii2/issues/11832

[MaksimKiselev]

@thomaslindgaard for temporary solution you may configure db component like this:

'db' => [
    'class' => 'yii\db\Connection',
    'dsn' => 'pgsql:dbname=xxx;host=127.0.0.1;',
    'username' => 'xxx',
    'password' => 'xxx',
    'charset' => 'utf8',
    'schemaMap' => [
        'pgsql' => [
            'class' => 'yii\db\pgsql\Schema',
            'defaultSchema' => 'myschema',
        ],
    ],
    'on afterOpen' => function ($event) {
        $event->sender->createCommand("SET search_path TO myschema;")->execute();
    },
],

[lourdas]

Yes, this is a solution, but it requires an extra command after opening each database connection. Since the default schema in PostgreSQL is $user,public, it's just easier (and quicker) to use the same name for the database schema and the user that is used for the database connection.

[thomaslindgaard]

@MKiselev thanks - works beautifully.

[samdark]

@lourdas that would change current behavior people may rely on.

[lourdas]

@samdark Why? What do you mean?

[samdark]

Oh. Ignore that comment. Was thinking about something else.

[MaksimKiselev]

@samdark считаешь ли ты это ошибкой?

Что если как решение внутри yiidbpgsqlSchema определить конструктор так, чтобы в Configurable передавалось что-то типа фикса выше?

[samdark]

Can't it be solved w/o extra query?

[MaksimKiselev]

@samdark yep, if use table names as myschema.table_name

[thomaslindgaard]

I tried editing the migration SQL file, so that it used myschema.user for the table name. That change gave me a user table in the correct schema, but then I could not register a new user because the user could no longer be found.

[turalus]

Same here. Why this issue still not resolved, guys? What do you suggest?
@samdark

[samdark]

@turalus not enough time to solve everything at once. If you know how to fix it and have extra time it would be great to get a pull request solving the issue and covering it with unit tests so it won't ever break again.

[cebe]

see also https://github.com/yiisoft/yii2/issues/11832#issuecomment-228842334

[brandonkelly]

Any thoughts on what the right approach is to fix this?

Considering defaultSchema is defined in yii\db\Schema (base class, not yii\db\pgsql\Schema), it seems to me that if those are defined, yii\db\QueryBuilder should be respecting their values, rather than leaving it up to yii\db\pgsql\QueryBuilder to override a bunch of methods.

[samdark]

@brandonkelly it makes sense, yes.

[brandonkelly]

So, there should probably be a normalizeTableName() method on yii\db\Schema, which applies the default schema if one isn't already defined:

public function normalizeTableName($name)
{
    if ($this->defaultSchema === null || strpos($name, '.') !== false) {
        return $name;
    }
    return "{$this->defaultSchema}.{$name}";
}

Then wherever a QueryBuilder method is SQLizing a table name, it should start normalizing it first:

$table = $schema->normalizeTableName($table);

return 'INSERT INTO ' . $schema->quoteTableName($table) // ...

[samdark]

@klimov-paul, @cebe, @SilverFire, @dynasource ?

[brandonkelly]

(Worth noting that putting the schema normalization code in quoteTableName() isn’t an option, since there are places that gets called for non-table names, i.e. createIndex(), which passes the index $name through it. You’ll get a SQL error if you try to prepend the schema to an index name.)

[dynasource]

I think it is valuable to have a defaultSchema in QueryBuilder or ActiveQuery. It could fix issue https://github.com/yiisoft/yii2/issues/5470#issuecomment-317630362

[mtangoo]

@samdark @dynasource et al What was the final resolution to this one? at leas recommended way?

[mtangoo]

am referring to setting multiple schema once as in

'schemaMap' => [
        'pgsql' => [
            'class' => 'yii\db\pgsql\Schema',
            'defaultSchema' => 'public,tenant',
        ],
    ],

Our current way is to override tableName() for all tenant related AR Models and it isn't that beautiful. It would be nice to set something at config level or even at boostrapping stage.

[samdark]

@mtangoo what @brandonkelly suggested makes sense. We currently don't have enough time to code it ourselves though.

[mtangoo]

can you explain what need to be done and specific files may be? I would like to weigh if I can do it and make a PR. This is very important to us and wouldn't mind set aside some time to fix it

[samdark]

https://github.com/yiisoft/yii2/issues/12763#issuecomment-317446615

[mtangoo]

@samdark thanks, let me create PR and then we can discuss it there

[mtangoo]

@samdark can you review the PR at https://github.com/yiisoft/yii2/pull/17283
I have tested with Postgres here and its working fine. Should work with other databases just fine

[brandonkelly]

Just looked into this again. I realized that my proposed solution wouldn’t account for hardcoded SQL queries such as:

Yii::$db->createCommand("update {{%tablename}} set [[foo]] = 'bar'")->execute();

Craft CMS has a handful of places where we’ve needed to do that, in places where the query couldn’t be expressed using the query builder; I’m sure other apps do as well.

I think expected behavior would be that the % in {{%tablename}} would start adding the default schema in addition to the table prefix – but if a {{schema.%tablename}} syntax is used, just add the table prefix.

If @samdark agrees, then maybe a better place to be focusing on is getRawTableName().

[brandonkelly]

By the way, everything works as expected if you set your search_path setting in your Postgres config to your custom schema name. The default value is "$user", public, meaning Postgres will initially try to work with a schema based on the current username, or fallback to public if that doesn’t exist.

I just verified that if I create a schema called homestead (my DB username) and set defaultSchema to homestead, I’m able to install and operate Craft CMS successfully using that homestead schema.

In fact, looking at places where defaultSchema is already being checked, it may already be working as expected – a way to remove the schema from DB queries when the database would already be configured to use it.

https://github.com/yiisoft/yii2/blob/2927a7af1c40ba3d1294b3c9bfcbc7a70bd4c9b1/framework/db/pgsql/Schema.php#L148

If that’s really all it’s for, then this issue can be closed as everything is working as expected already.

[samdark]

@brandonkelly seems documentation is lacking in this area, right?

[mtangoo]

@brandonkelly @samdark what Brandon explains is already working. What was not working (and the main issue here) was changing the schema after connection is made, that is changing connections when loadig different models.

I will write small example later explaining what is working and what is not!

[lourdas]

Are you sure this is fixed? I stumbled on this again today, with yii2-2.0.27. I applied the migration for the queue db table with ./yii migrate and the queue and migration table were created in the public schema, instead of the one in my user. My db.php contains:

    <?php
    return [
        'class' => 'yii\db\Connection',
        'dsn' => 'pgsql:host=localhost;dbname=db',
        'username' => 'sensor',
        'password' => 'xxxx',
        'charset' => 'utf8',
        'emulatePrepare' => false,
        'enableSchemaCache' => !YII_DEBUG,
        'schemaMap' => [
            'pgsql' => [
                'class' => 'yii\db\pgsql\Schema',
                'defaultSchema' => 'sensor',
            ]
        ],
    ];

So, the tables were created in the public schema instead of sensor.

[mtangoo]

If you try this does it work?

<?php
return [
    'class' => 'yii\db\Connection',
    'dsn' => 'pgsql:host=localhost;dbname=db',
    'username' => 'sensor',
    'password' => 'xxxx',
    'charset' => 'utf8',
    'emulatePrepare' => false,
    'enableSchemaCache' => !YII_DEBUG,
    'on afterOpen' => function($event) {
        $event->sender->createCommand('SET search_path TO sensor,public;')->execute();
    }
];

[lourdas]

Even if it works, I wouldn't use it. You duplicate every query this way (although this specific might not be that intensive). I'd rather hard code the schema in every query of mine. Although it works inside the application (you don't have to hard code the schema if its name is the same as the username connection), it fails for migrations.

[mtangoo]

Even if it works, I wouldn't use it.

That was a question for probing if that works or not and not whether you should use it or not

[lourdas]

Even if it works, I wouldn't use it.

That was a question for probing if that works or not and not whether you should use it or not

From that point of view, yes, although I haven't tested it, it will 99.9% work.

[mtangoo]

From that point of view, yes, although I haven't tested it, it will 99.9% work.

Care to just test?