[eluser]James Gifford[/eluser]
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.
[eluser]Derek Allard[/eluser]
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.
[eluser]James Gifford[/eluser]
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.
[eluser]Derek Allard[/eluser]
Awesome James! Thanks. What's easiest for you? Want to keep everything in this thread? Feel free to keep posting here.
[eluser]James Gifford[/eluser]
Okay.
Here are the two methods I modified in the postgre_forge.php file:
Code: /**
* 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($attributes, CASE_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.
[eluser]James Gifford[/eluser]
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.
[eluser]James Gifford[/eluser]
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.
Code: 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($attributes, CASE_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";
}
[eluser]eedfwChris[/eluser]
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/codeignite...forge.php
|
http://bitbucket.org/ellislab/codeignite...3dbbf3e3d4 Thanks James.
