Compatibility issues with PostGre and possible fixes

Grocery CRUD Enterprise
1

Hello,

I use CodeIgniter 3 and GroceryCrud Enterprise, I’ve upgraded from the community Edition and I’ve had some compatibility troubles with Postgre. I’ve managed to fix most of my issues and i’m currently in the process of migrating multiple pages to the GCE. I’m not sure if the way i fixed my problems was the right way though, I hope that you can answer to this question and if it’s the right way fix it in the next version.

Major Problems
Major Problem 1

My first issue was that GCE asked Postgre columns for the table specified but I have multiple tables with the same name but in different schema as it is possible with Postgre, let’s take an example:

In schema A:
TableX(id, city, country)

In Schema B:
TableX(id, roadName, roadAlias)

so even if I specified the schema to GCE, it would get all the columns of ANY table named TableX, in the example it would get the columns: id, id, city, country, roadName, roadAlias.

To fix this i changed the Model, around Line 810:

From:

if ($driverName === 'Postgresql') {
            $statement = $this->adapter->createStatement(
                'SELECT column_name, data_type, is_nullable, column_default
                FROM information_schema.columns
                where table_name = \'' . $tableName . '\'');
     [...]
}

To:

if ($driverName === 'Postgresql') {
            $statement = $this->adapter->createStatement(
                'SELECT column_name, data_type, is_nullable, column_default
                FROM information_schema.columns
                where table_name = \'' . $tableName . '\' AND table_schema = \'' . ($this->getDatabaseSchema() != null ?  $this->getDatabaseSchema() : 'public') . '\'');

     [...]

So it specifies the schema but goes to the public schema by default.

Major Problem 2

Another Problem i had was with the function GetTableNameWithSchema that wasn’t doing the job properly for me so i changed it to what getTableNameWithSchemaForOperations was doing, i realize now it might not be the best way.

Major Problem 3

One big problem that i encountered was that some of the tables I was using weren’t tables but views and your system to detect the primary key to the table obviously couldn’t work as a view doesn’t have a primary key, So i used setPrimaryKey but it wasn’t taken into account, after some digging, I realized no mapping was done between tables and primary keys, i had to force states to use SetInitalData from the stateAbstract because it was doing said mapping:

        $primaryKeys = $this->gCrud->getPrimaryKeys();
        if (!empty($primaryKeys)) {
               foreach ($primaryKeys as $tableName => $primaryKey) {
                   $model->setPrimaryKey($primaryKey, $tableName);
               }
        }
Minor Problems
Minor Problem 1

I also had a problem in the filters (had this problem in the community edition but fixed it) where i would need a cast.

so in the Model around line 430 from:

        if ($whereComparison['value'] === '') {
            $whereArray[] = ["( $filterName {$whereComparison['comparison']} ? OR $filterName IS NULL)", $whereComparison['value']];
        } else {
            $whereArray[] = [$filterName . ' ' . $whereComparison['comparison'] . ' ?', $whereComparison['value']];
        }

To:

        if ($whereComparison['value'] === '') {
            $whereArray[] = ["( CAST ($filterName AS VARCHAR) {$whereComparison['comparison']} ? OR $filterName IS NULL)", $whereComparison['value']];
        } else {
            $whereArray[] = ["CAST ($filterName AS VARCHAR) " . $whereComparison['comparison'] . ' ?', $whereComparison['value']];
        }
Minor Problem 2

The Pagination wasn’t right when i had set relations as they weren’t taken into account. So i fixed it in Model around line 620:
From:

        if (!empty($this->_filters_or)) {
            $select = $this->filtering($select, PredicateSet::OP_OR);
        }

        if (!empty($this->_filters) || !empty($this->_filters_or)) {
            $select = $this->joinStatements($select);
        }
        $select = $this->extraJoinStatements($select);

To:

        if (!empty($this->_filters_or)) {
            $select = $this->filtering($select, PredicateSet::OP_OR);
        }
         
        if (!empty($this->_relation_1_n) || !empty($this->_relation_n_n)) {
            $select = $this->joinStatements($select);
        }
        
        $select = $this->extraJoinStatements($select);

I hope this post wasn’t too long and too hard to read.

Thanks

1 Like
2

Hello,
For minor problem 1, that is to say the filtering problems, the filtering function should also be modified to quote table name :

if ($this->isFieldWithRelationNtoN($filterName)) {
       // If the filter is relationNtoN we will filter than within a second query later
       continue;
} else {
       $filterName =  '"' .$this->tableName. '"'. '.' . $filterName;
}
3

I’m not sure if the way i fixed my problems was the right way though, I hope that you can answer to this question and if it’s the right way fix it in the next version.

4

Hello again, i ran into another issue.

When filtering either search or extended search if I have a column of type boolean in my PostGre Database, casting it as a varchar gets me ‘true’ or ‘false’ when the crud requests for 0 or 1.

I don’t want to change my database so i was forced to check and transform the search value in $_GET or $_POST depending on the action