Database tables of a selected plugin are not listed correctly

[uematsusoft]

In a OctoberCMS v4 with Builder Plugin v3.0.1 site, using PostgreSQL driver, the tables of the selected plugin are not listed. I can see the controllers, models, etc..
In a OctoberCMS v2 with the latest version of the Builder Plugin fro the OC version it works perfectly.

Database configuration:

• OctoberCMS v4 is in its own schema - "october" (DB_SCHEMA points to it in .env);
• There is a specific user with permissions to the "october" schema - "ocuser";

Problem

• The result when calling the method \RainLab\Builder\Models\DatabaseTableModel::listPluginTables('acme.example") is an empty array because the "search_path/schema" is prepended to the table name.

Some investigation

• In method "listPluginTables" the call: self::getSchemaManager()->listTableNames() return the list of tables but prefixed with the "search_path/schema", for example: " october. backend_users"
  • My first solution was to add the "search_path" to the $foundTables startsWith filter. (Yes, the tables were now listed in the UI but the migrations table were also prefixed with the "search_path" which failed)

Digging deeper

• The "getSchemaSearchPaths" of the Class "Doctrine\DBAL\Schema\PostgreSqlSchemaManager" lists returns ['ocuser', 'public'] instead of ['october', 'public'].
  • Don't know why it returns the DB_USER instead of the DB_SCHEMA.
  • Since the result array is filtered by getCurrentSchema() ('october') and it does not exists, the resulting table name is the concatenation of the schema and the table name.

Hope that I didn't over explainded the situation :)

[daftspunk]

Hey @uematsusoft

I have a proposed fix for this in the following commit: 4e79a51

Could you test that it works?

[uematsusoft]

Hello @daftspunk,

Thank you for your quick response.
Unfortunately your solution didn't worked.

But, I replaced your code with the following and it worked.

$doctrineConnection = DriverManager::getConnection($params, new Configuration);

if ($doctrineDriver === 'pdo_pgsql' && isset($configArray['search_path'])) {
    $doctrineConnection->executeStatement("SET search_path TO {$configArray['search_path']}");
}

return $doctrineConnection;

Even when saving changes in the table using the plugin, the migration uses the correct table name.

I'm not submitting my code as a PR because I don't like it. I think that could be a better way without hardcoding a statement.

This solution follows the great explanation on this submission to the laravel idea - laravel/ideas#916

Hope it helps.

Best regards,
Domingos