monoteos commented 5 years ago

Bug report

Model getSchema returns MySqlBuilder and MySqlGrammar for Postgres connection which causing error in getting columns list

select column_name as `column_name` from information_schema.columns where table_schema = botpress_prod and table_name = web_conversations

What I did:

I got latest release of Laravel, and installed latest release of Backpack

composer require backpack/crud
php artisan backpack:base:install
php artisan backpack:crud:install

this is my DB config in .env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=root
DB_PASSWORD=secret

DB_HOST_BOTPRESS=database.rds.amazonaws.com
DB_PORT_BOTPRESS=5432
DB_DATABASE_BOTPRESS=user
DB_USERNAME_BOTPRESS=database
DB_PASSWORD_BOTPRESS=secret

this is my database config in configs:

'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],
        'pgsql' => [
            'driver' => 'pgsql',
            'host' => env('DB_HOST_BOTPRESS', '127.0.0.1'),
            'port' => env('DB_PORT_BOTPRESS', '5432'),
            'database' => env('DB_DATABASE_BOTPRESS', 'forge'),
            'username' => env('DB_USERNAME_BOTPRESS', 'forge'),
            'password' => env('DB_PASSWORD_BOTPRESS', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            'schema' => 'public',
            'sslmode' => 'prefer',
        ],

Then I generated a model, controller and request

php artisan backpack:crud-controller BotpressConversation
php artisan backpack:crud-model BotpressConversation
php artisan backpack:crud-request BotpressConversation

Added CRUD to routes:

Route::group([
    'prefix'     => config('backpack.base.route_prefix', 'admin'),
    'middleware' => ['web', config('backpack.base.middleware_key', 'admin')],
    'namespace'  => 'App\Http\Controllers\Admin',
], function () { // custom admin routes

    CRUD::resource('botpressconversation', 'BotpressConversationCrudController');

}); // this should be the absolute last line of this file

This is my generated model, I tweaked to use my pgsql table and connection:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Backpack\CRUD\CrudTrait;

class BotpressConversation extends Model
{
    use CrudTrait;

    protected $connection = 'pgsql';
    protected $table = 'web_conversations';
    protected $primaryKey = 'id';
    public $timestamps = false;
    protected $fillable = [];
}

What I expected to happen:

I expected to open admin route and see list of my model

What happened:

When I'm trying to open my model route in admin panel I got error:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "`" LINE 1: select column_name as `column_name` from information_schema.... ^ (SQL: select column_name as `column_name` from information_schema.columns where table_schema = botpress_prod and table_name = web_conversations)

What I've already tried to fix it:

I found a place where it fails and thinking about extend Column trait to avoid this

Columns trait, line 419:

$columns = $cache[$table] = $this->getSchema()->getColumnListing($table);

Dump of $this:

CrudPanel {#243 ▼
  +model: BotpressConversation {#307 ▶}
  +route: "admin/botpressconversation"
  +entity_name: "botpressconversation"
  +entity_name_plural: "botpress_conversations"
  +request: Request {#43 ▶}
  +settings: array:8 [▶]
  +access: array:4 [▶]
  +reorder: false
  +reorder_label: false
  +reorder_max_level: 3
  +details_row: false
  +export_buttons: false
  +bulk_actions: false
  +columns: []
  +create_fields: []
  +update_fields: []
  +query: Builder {#314 ▼
    #query: Builder {#313 ▼
      +connection: PostgresConnection {#299 ▶}
      +grammar: PostgresGrammar {#311 ▶}
      +processor: PostgresProcessor {#312}
      +bindings: array:7 [▶]
      +aggregate: null
      +columns: array:1 [▶]
      +distinct: false
      +from: "web_conversations"
      +joins: null
      +wheres: []
      +groups: null
      +havings: null
      +orders: null
      +limit: null
      +offset: null
      +unions: null
      +unionLimit: null
      +unionOffset: null
      +unionOrders: null
      +lock: null
      +operators: array:29 [▶]
      +useWritePdo: false
    }
    #model: BotpressConversation {#307 ▶}
    #eagerLoad: []
    #localMacros: []
    #onDelete: null
    #passthru: array:15 [▶]
    #scopes: []
    #removedScopes: []
  }
  +entry: null
  +buttons: Collection {#253 ▶}
  +db_column_types: []
  +default_page_length: false
  +page_length_menu: false
  +sort: []
  +ajax_table: true
  +responsive_table: null
  +persistent_table: null
  #groupedErrors: true
  #inlineErrors: true
  +actions_column_priority: 1
  +labeller: false
  +autoFocusOnFirstField: true
  +filters: []
  +tabsEnabled: false
  +tabsType: "horizontal"
  #createView: "crud::create"
  #editView: "crud::edit"
  #showView: "crud::show"
  #detailsRowView: "crud::details_row"
  #revisionsView: "crud::revisions"
  #revisionsTimelineView: "crud::inc.revision_timeline"
  #reorderView: "crud::reorder"
  #listView: "crud::list"
  #createContentClass: null
  #editContentClass: null
  #listContentClass: null
  #showContentClass: null
  #reorderContentClass: null
  #revisionsTimelineContentClass: null
  +requiredFields: []
  +titles: []
  +headings: []
  +subheadings: []
  +operation: null
}

Dump of $this->getSchema():

MySqlBuilder {#316 ▼
  #connection: PostgresConnection {#299 ▶}
  #grammar: MySqlGrammar {#315 ▶}
  #resolver: null
}

so builder in getSchema must be PostgresBuilder instead of MySqlBuilder and also grammar must be PostgresGrammar, but somewhere it get overwritten

Backpack, Laravel, PHP, DB version:

Backpack: 3.6.22

Laravel: v5.8.29

PHP 7.3.6 (cli) (built: Jun 17 2019 08:40:34) ( NTS ) Copyright (c) 1997-2018 The PHP Group Zend Engine v3.3.6, Copyright (c) 1998-2018 Zend Technologies with Zend OPcache v7.3.6, Copyright (c) 1999-2018, by Zend Technologies

DB: Mysql: 8.0.13 (MySQL Community Server - GPL) Postgres: PostgreSQL 10.6

welcome[bot] commented 5 years ago

Hello there! Thanks for opening your first issue on this repo!

Just a heads-up: Here at Backpack we use Github Issues only for tracking bugs. Talk about new features is also acceptable. This helps a lot in keeping our focus on improving Backpack. If you issue is not a bug/feature, please help us out by closing the issue yourself and posting in the appropriate medium (see below). If you're not sure where it fits, it's ok, a community member will probably reply to help you with that.

Backpack communication mediums:

Bug Reports, Feature Requests - Github Issues (here);
Quick help (How do I do X) - Gitter Chatroom;
Long questions (I have done X and Y and it won't do Z wtf) - Stackoverflow, using the backpack-for-laravel tag;

Please keep in mind Backpack offers no official / paid support. Whatever help you receive here, on Gitter, Slack or Stackoverflow is thanks to our awesome awesome community members, who give up some of their time to help their peers. If you want to join our community, just start pitching in. We take pride in being a welcoming bunch.

Thank you!

-- Justin Case The Backpack Robot

tabacitu commented 5 years ago

Thank you for writing the detailed report @monoteos .

Unfortunately, Backpack does not provide official support for PosgreSQL. We use Eloquent which should abstract away the DBMS, but in practice there are quirks and bugs, features of Eloquent that only MySQL supports (like json column type), things like that.

Our focus is on providing an excellent user experience on MySQL, but we do have reports of people using Backpack successfully with Postgres, and have a number of conditionals in code to provide support for those few Posgres users.

We're happy to merge PRs that bring PosgreSQL support, but don't bloat our code. However, it's not a priority for us right now to push for full PosgreSQL support - so we won't be actively working on it. Maybe in the future.

Sorry to disappoint. Thanks again for taking the time to write this issue. Cheers!

Laravel-Backpack / CRUD

[3.6][Bug] Postgres - getSchema returns MySqlBuilder and MySqlGrammar #1940