Fix #93 Updating postgres dbforge create table method. Thanks to James Gifford for the patch. http://codeigniter.com/forums/viewthread/73392/
diff --git a/system/database/drivers/postgre/postgre_forge.php b/system/database/drivers/postgre/postgre_forge.php
index 41858f3..16127ff 100644
--- a/system/database/drivers/postgre/postgre_forge.php
+++ b/system/database/drivers/postgre/postgre_forge.php
@@ -69,7 +69,10 @@
if ($if_not_exists === TRUE)
{
- $sql .= 'IF NOT EXISTS ';
+ if ($this->db->table_exists($table))
+ {
+ return "SELECT * FROM $table"; // Needs to return innocous but valid SQL statement
+ }
}
$sql .= $this->db->_escape_identifiers($table)." (";
@@ -90,16 +93,55 @@
$sql .= "\n\t".$this->db->_protect_identifiers($field);
- $sql .= ' '.$attributes['TYPE'];
+ $is_unsigned = (array_key_exists('UNSIGNED', $attributes) && $attributes['UNSIGNED'] === TRUE);
- if (array_key_exists('CONSTRAINT', $attributes))
+ // Convert datatypes to be PostgreSQL-compatible
+ switch (strtoupper($attributes['TYPE']))
{
- $sql .= '('.$attributes['CONSTRAINT'].')';
+ 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 (array_key_exists('UNSIGNED', $attributes) && $attributes['UNSIGNED'] === TRUE)
+ // 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 .= ' UNSIGNED';
+ $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))
@@ -116,9 +158,10 @@
$sql .= ' NOT NULL';
}
- if (array_key_exists('AUTO_INCREMENT', $attributes) && $attributes['AUTO_INCREMENT'] === TRUE)
+ // Added new attribute to create unqite fields. Also works with MySQL
+ if (array_key_exists('UNIQUE', $attributes) && $attributes['UNIQUE'] === TRUE)
{
- $sql .= ' AUTO_INCREMENT';
+ $sql .= ' UNIQUE';
}
}
@@ -131,10 +174,17 @@
if (count($primary_keys) > 0)
{
- $primary_keys = $this->db->_protect_identifiers($primary_keys);
+ // 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)
@@ -148,12 +198,13 @@
$key = array($this->db->_protect_identifiers($key));
}
- $sql .= ",\n\tFOREIGN KEY (" . implode(', ', $key) . ")";
+ foreach ($key as $field)
+ {
+ $sql .= "CREATE INDEX " . $table . "_" . str_replace(array('"', "'"), '', $field) . "_index ON $table ($field); ";
+ }
}
}
- $sql .= "\n);";
-
return $sql;
}
@@ -162,12 +213,12 @@
/**
* Drop Table
*
- * @access private
- * @return bool
+ * @access private
+ * @return bool
*/
function _drop_table($table)
{
- return "DROP TABLE ".$this->db->_escape_identifiers($table)." CASCADE";
+ return "DROP TABLE IF EXISTS ".$this->db->_escape_identifiers($table)." CASCADE";
}
// --------------------------------------------------------------------
diff --git a/user_guide/changelog.html b/user_guide/changelog.html
index 251f6fb..786b902 100644
--- a/user_guide/changelog.html
+++ b/user_guide/changelog.html
@@ -124,6 +124,7 @@
<li>Modified the database errors to show the filename and line number of the problematic query.</li>
<li>Removed the following deprecated functions: orwhere, orlike, groupby, orhaving, orderby, getwhere.</li>
<li>Removed deprecated _drop_database() and _create_database() functions from the db utility drivers.</li>
+ <li>Improved dbforge create_table() function for the Postgres driver.</li>
</ul>
</li>
<li>Helpers