EllisLab text mark
Advanced Search
     
DBForge isn’t working properly with PostgreSQL
Posted: 05 March 2008 06:54 PM
Avatar
Joined: 2006-04-25
83 posts

I have been using the new DBForge class to create databases automatically and for the most part things have been working well with a MySQL database.  However, I recently tried a script with a PostgreSQL database (ver. 8.2.5) and found that there were several errors.

First of all, when trying to drop a table that does not exist, a critical error is thrown and the script exits.  I believe this is because the “IF EXISTS” term does not work in PostgreSQL like it does in MySQL.  I know I can do a simple check to see if the table exists before deleting it, but it would be nice if DBForge could simply ignore this error.

Also, The terminology for creating primary keys in a PostgreSQL database is off.  DBForge tries to use the common “AUTO_INCREMENT” term for a primary key, but this also does not work in PostgreSQL.  In PostgreSQL, there is a special datatype for an auto-incrementing value called “serial” that should be used.

I haven’t looked thoroughly through the PostgreSQL drivers in CI to see if there are any other issues, but these two are enough prevent me from using DBForge with PostgreSQL for now. 

I’d like to use DBForge to create platform-independent installer scripts so I hope these issues are resolved in the near future.

 Signature 

My website: http://jamesgifford.com

 
Posted: 05 March 2008 11:39 PM   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2006-03-23
3194 posts

James, I’m sincerely welcome you input in making it more postgre compatible.  I know you have the programming know-how to make suggestions, but even if you just wanted to suggest some proper syntaxes, I’d be happy to fix those up.  I want db forge to be as compatible as possible.

 Signature 

DerekAllard.com - CodeIgniter, ExpressionEngine, and the World of Web Design

 
Posted: 06 March 2008 05:03 PM   [ # 2 ]   [ Rating: 0 ]
Avatar
Joined: 2006-04-25
83 posts

I’ve made some changes to the database/drivers/postgre/postgre_forge.php file which address the problems I’ve encountered so far.  These are just quick hacks to get the code working, I’m sure they could be made more efficient. I’m not sure where I should post my changes to postgre_forge.php, let me know if/how you would like them.

I’ll summarize the issues I’ve found so far:

1. Postgres doesn’t support the “IF NOT EXISTS” term when creating a table. The only workaround for this I could think of was to use the $this->db->table_exists() function to check for the table first.

2. When dropping a table, Postgres will throw an error if that table doesn’t already exist.  Adding “IF EXISTS” to the drop table query fixes this.

3. Postgres doesn’t support unsigned integer data types. These types must be converted.

4. Postgres doesn’t support all the same data types as MySQL such as tinyint.  These types must be converted.

5. Postgres doesn’t allow constraints with integer data types (eg: int(11)).

6. Perhaps the biggest difference is the treatment of auto-incrementing variables.  Postgres has a special datatype called serial that handles these.  The “AUTO INCREMENT” term is not supported.

7. Instead of “FOREIGN KEY” for non-primary keys Postgres uses “UNIQUE”.

That’s all I’ve found so far.  I hope the changes I’ve made to the forge are useful.  I haven’t tested them fully, but they are working for me so far.  I’ll continue testing and searching for other errors.

 Signature 

My website: http://jamesgifford.com

 
Posted: 06 March 2008 09:23 PM   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2006-03-23
3194 posts

Awesome James!  Thanks.  What’s easiest for you?  Want to keep everything in this thread?  Feel free to keep posting here.

 Signature 

DerekAllard.com - CodeIgniter, ExpressionEngine, and the World of Web Design

 
Posted: 06 March 2008 11:27 PM   [ # 4 ]   [ Rating: 0 ]
Avatar
Joined: 2006-04-25
83 posts

Okay.

Here are the two methods I modified in the postgre_forge.php file:

/**
     * Create Table
     *
     * @access    private
     * @param    string    the table name
     * @param    array    the fields
     * @param    mixed    primary key(s)
     * @param    mixed    key(s)
     * @param    boolean    should 'IF NOT EXISTS' be added to the SQL
     * @return    bool
     */
    
function _create_table($table$fields$primary_keys$keys$if_not_exists)
    
{
        $sql 
'CREATE TABLE ';
        
        if (
$if_not_exists === TRUE)
        
{
            
// Modified for PostgreSQL compatibility
            
if ($this->db->table_exists($table))
                return 
false;
        
}
        
        $sql 
.= $this->db->_escape_table($table)." (";
        
$current_field_count 0;

        foreach (
$fields as $field=>$attributes)
        
{
            
// Numeric field names aren't allowed in databases, so if the key is
            // numeric, we know it was assigned by PHP and the developer manually
            // entered the field information, so we'll simply add it to the list
            
if (is_numeric($field))
            
{
                $sql 
.= "\n\t$attributes";
            
}
            
else
            
{
                $attributes 
array_change_key_case($attributesCASE_UPPER);
                
                
$sql .= "\n\t".$this->db->_protect_identifiers($field);
                
                
$is_unsigned = (array_key_exists('UNSIGNED'$attributes) && $attributes['UNSIGNED'=== TRUE);
                
                
// Convert datatypes to be PostgreSQL-compatible
                
switch (strtoupper($attributes['TYPE']))
                
{
                    
case 'TINYINT':
                        
$attributes['TYPE''SMALLINT'; break;
                    
                    case 
'SMALLINT':
                        
$attributes['TYPE'= ($is_unsigned) ? 'INTEGER' 'SMALLINT'; break;
                    
                    case 
'MEDIUMINT':
                        
$attributes['TYPE''INTEGER'; break;
                    
                    case 
'INT':
                        
$attributes['TYPE'= ($is_unsigned) ? 'BIGINT' 'INTEGER'; break;
                    
                    case 
'BIGINT':
                        
$attributes['TYPE'= ($is_unsigned) ? 'NUMERIC' 'BIGINT'; break;
                    
                    case 
'DOUBLE':
                        
$attributes['TYPE''DOUBLE PRECISION';
                    
                    case 
'DATETIME':
                        
$attributes['TYPE''TIMESTAMP';
                    
                    case 
'LONGTEXT':
                        
$attributes['TYPE''TEXT';
                    
                    case 
'BLOB':
                        
$attributes['TYPE''BYTEA';
                
}
                
                
// If this is an auto-incrementing primary key, use the serial data type instead
                
if (in_array($field$primary_keys) && array_key_exists('AUTO_INCREMENT'$attributes) && $attributes['AUTO_INCREMENT'=== TRUE)
                
{
                    $sql 
.= ' SERIAL';
                
}
                
else
                
{
                    $sql 
.=  ' '.$attributes['TYPE'];
                
}
                
                
// Modified to prevent constraints with integer data types
                
if (array_key_exists('CONSTRAINT'$attributes) && strpos($attributes['TYPE']'INT') === false)
                
{
                    $sql 
.= '('.$attributes['CONSTRAINT'].')';
                
}
    
                
if (array_key_exists('DEFAULT'$attributes))
                
{
                    $sql 
.= ' DEFAULT \''.$attributes['DEFAULT'].'\'';
                
}
    
                
if (array_key_exists('NULL'$attributes) && $attributes['NULL'=== TRUE)
                
{
                    $sql 
.= ' NULL';
                
}
                
else
                
{
                    $sql 
.= ' NOT NULL';            
                
}
            }
            
            
// don't add a comma on the end of the last field
            
if (++$current_field_count count($fields))
            
{
                $sql 
.= ',';
            
}
        }

        
if (count($primary_keys) > 0)
        
{
            $primary_keys 
$this->db->_protect_identifiers($primary_keys);
            
$sql .= ",\n\tPRIMARY KEY (" implode(', '$primary_keys) . ")";
        
}

        
if (is_array($keys) && count($keys) > 0)
        
{
            $keys 
$this->db->_protect_identifiers($keys);
            foreach (
$keys as $key)
            
{
                
// Modified for PostgreSQL compatibility
                
$sql .= ",\n\tUNIQUE ($key)";
            
}
        }

        $sql 
.= "\n);";

        return 
$sql;
    
}

    
/**
     * Drop Table
     *
     * @access    private
     * @return    bool
     */
    
function _drop_table($table)
    
{
        
// Modified to prevent critical errors with PostgreSQL
        
return "DROP TABLE IF EXISTS ".$this->db->_escape_table($table)." CASCADE";
    

I’ll try to improve on this and test for other errors, but so far this code is working for me.

 Signature 

My website: http://jamesgifford.com

 
Posted: 05 November 2008 07:32 PM   [ # 5 ]   [ Rating: 0 ]
Avatar
Joined: 2006-04-25
83 posts

I thought I’d revive this thread to point out that dbforge sill isn’t working properly with PostgreSQL databases in CI 1.7. I’m not sure that any changes have been made to postgre_forge.php since the last release. I believe the updates I posted earlier will still work, though I still haven’t done an in-depth examination of the differences.

 Signature 

My website: http://jamesgifford.com

 
Posted: 06 November 2008 12:06 AM   [ # 6 ]   [ Rating: 0 ]
Avatar
Joined: 2006-04-25
83 posts

For what its worth, here are the changes I made to postgre_forge.php.

I added the option for a ‘unique’ attribute as well as a fix for the _protect_identifiers() error others are having.

function _create_table($table$fields$primary_keys$keys$if_not_exists)
    
{
        $sql 
'CREATE TABLE ';
        
        if (
$if_not_exists === TRUE)
        
{
            
if ($this->db->table_exists($table))
                return 
"SELECT * FROM $table"// Needs to return innocous but valid SQL statement
        
}
        
        $sql 
.= $this->db->_escape_identifiers($table)." (";
        
$current_field_count 0;

        foreach (
$fields as $field=>$attributes)
        
{
            
// Numeric field names aren't allowed in databases, so if the key is
            // numeric, we know it was assigned by PHP and the developer manually
            // entered the field information, so we'll simply add it to the list
            
if (is_numeric($field))
            
{
                $sql 
.= "\n\t$attributes";
            
}
            
else
            
{
                $attributes 
array_change_key_case($attributesCASE_UPPER);
                
                
$sql .= "\n\t".$this->db->_protect_identifiers($field);
                
                
$is_unsigned = (array_key_exists('UNSIGNED'$attributes) && $attributes['UNSIGNED'=== TRUE);
                
                
// Convert datatypes to be PostgreSQL-compatible
                
switch (strtoupper($attributes['TYPE']))
                
{
                    
case 'TINYINT':
                        
$attributes['TYPE''SMALLINT'; break;
                    
                    case 
'SMALLINT':
                        
$attributes['TYPE'= ($is_unsigned) ? 'INTEGER' 'SMALLINT'; break;
                    
                    case 
'MEDIUMINT':
                        
$attributes['TYPE''INTEGER'; break;
                    
                    case 
'INT':
                        
$attributes['TYPE'= ($is_unsigned) ? 'BIGINT' 'INTEGER'; break;
                    
                    case 
'BIGINT':
                        
$attributes['TYPE'= ($is_unsigned) ? 'NUMERIC' 'BIGINT'; break;
                    
                    case 
'DOUBLE':
                        
$attributes['TYPE''DOUBLE PRECISION'; break;
                    
                    case 
'DATETIME':
                        
$attributes['TYPE''TIMESTAMP'; break;
                    
                    case 
'LONGTEXT':
                        
$attributes['TYPE''TEXT'; break;
                    
                    case 
'BLOB':
                        
$attributes['TYPE''BYTEA'; break;
                
}
                
                
// If this is an auto-incrementing primary key, use the serial data type instead
                
if (in_array($field$primary_keys) && array_key_exists('AUTO_INCREMENT'$attributes) && $attributes['AUTO_INCREMENT'=== TRUE)
                
{
                    $sql 
.= ' SERIAL';
                
}
                
else
                
{
                    $sql 
.=  ' '.$attributes['TYPE'];
                
}
    
                
// Modified to prevent constraints with integer data types
                
if (array_key_exists('CONSTRAINT'$attributes) && strpos($attributes['TYPE']'INT') === false)
                
{
                    $sql 
.= '('.$attributes['CONSTRAINT'].')';
                
}
    
                
if (array_key_exists('DEFAULT'$attributes))
                
{
                    $sql 
.= ' DEFAULT \''.$attributes['DEFAULT'].'\'';
                
}
    
                
if (array_key_exists('NULL'$attributes) && $attributes['NULL'=== TRUE)
                
{
                    $sql 
.= ' NULL';
                
}
                
else
                
{
                    $sql 
.= ' NOT NULL';            
                
}
                
                
// Added new attribute to create unqite fields. Also works with MySQL
                
if (array_key_exists('UNIQUE'$attributes) && $attributes['UNIQUE'=== TRUE)
                
{
                    $sql 
.= ' UNIQUE';
                
}
            }
            
            
// don't add a comma on the end of the last field
            
if (++$current_field_count count($fields))
            
{
                $sql 
.= ',';
            
}
        }

        
if (count($primary_keys) > 0)
        
{
            
// Something seems to break when passing an array to _protect_identifiers()
            
foreach ($primary_keys as $index => $key)
                
$primary_keys[$index] $this->db->_protect_identifiers($key);
            
            
$sql .= ",\n\tPRIMARY KEY (" implode(', '$primary_keys) . ")";
        
}
        
        $sql 
.= "\n);";
        
        if (
is_array($keys) && count($keys) > 0)
        
{
            
foreach ($keys as $key)
            
{
                
if (is_array($key))
                
{
                    $key 
$this->db->_protect_identifiers($key);    
                
}
                
else
                
{
                    $key 
= array($this->db->_protect_identifiers($key));
                
}
                
                
foreach ($key as $field)
                    
$sql .= "CREATE INDEX " $table "_" str_replace(array('"'"'"), ''$field) . "_index ON $table ($field); ";
            
}
        }

        
return $sql;
    
}

    
// --------------------------------------------------------------------

    /**
     * Drop Table
     *
     * @access    private
     * @return    bool
     */
    
function _drop_table($table)
    
{
        
return "DROP TABLE IF EXISTS ".$this->db->_escape_identifiers($table)." CASCADE";
    
 Signature 

My website: http://jamesgifford.com

 
Posted: 05 August 2010 07:02 PM   [ # 7 ]   [ Rating: 0 ]
Avatar
Joined: 2006-12-20
88 posts

What’s it take to get this added into bitbucket?

Thanks James for taking the time on this one. No offense to anyone but the desire to support other DBMS besides MySQL is a fluke with proof based on the timeline below. A fix is in this thread!

http://bitbucket.org/ellislab/codeigniter/history/system/database/drivers/postgre/postgre_forge.php

 
Posted: 21 December 2010 02:06 PM   [ # 8 ]   [ Rating: 0 ]
Avatar
Joined: 2007-05-07
6067 posts

Better late than never?  smile  http://bitbucket.org/ellislab/codeigniter/changeset/743dbbf3e3d4  Thanks James.

-greg

 Signature 

gregaker.net | Follow me on Twitter