DBForge MySQL FULLTEXT Key Support
Category:Modifications::DBForge Category:Contributions::Modifications::DBForge
Simple modification to allow FULLTEXT Key support in MySQL using DBForge for table creation. Forces MySQL engine to MyISAM if FULLTEXT keys are used. Two files to be modified, both included. Based on CI v1.7.0
Also, There is a bug in the primary key adding using DBForge detailed here: http://codeigniter.com/forums/viewthread/100121/ The fix for this issue is included in the quoted files.
Usage:
$this->dbforge->add_key('key_name', FALSE, TRUE);File: ./system/database/DB_forge.php
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/*
- add_key method was altered to allow fulltext support via boolean
- added the $fulltext_key class variable (array) which will hold fulltext keys
- altered the create_table function so it passes in the fulltext keys to
the _create_table() function in database/drivers/mysql/mysql_forge.php
*/
/**
* Code Igniter
*
* An open source application development framework for PHP 4.3.2 or newer
*
* @package CodeIgniter
* @author ExpressionEngine Dev Team
* @copyright Copyright (c) 2008, EllisLab, Inc.
* @license http://codeigniter.com/user_guide/license.html
* @link http://codeigniter.com
* @since Version 1.0
* @filesource
*/
// ------------------------------------------------------------------------
/**
* Database Utility Class
*
* @category Database
* @author ExpressionEngine Dev Team
* @link http://codeigniter.com/user_guide/database/
*/
class CI_DB_forge {
var $fields = array();
var $keys = array();
var $primary_keys = array();
var $db_char_set = '';
// ADDED BY RYAN VENNELL
var $fulltext_keys = array();
/**
* Constructor
*
* Grabs the CI super object instance so we can access it.
*
*/
function CI_DB_forge()
{
// Assign the main database object to $this->db
$CI =& get_instance();
$this->db =& $CI->db;
log_message('debug', "Database Forge Class Initialized");
}
// --------------------------------------------------------------------
/**
* Create database
*
* @access public
* @param string the database name
* @return bool
*/
function create_database($db_name)
{
$sql = $this->_create_database($db_name);
if (is_bool($sql))
{
return $sql;
}
return $this->db->query($sql);
}
// --------------------------------------------------------------------
/**
* Drop database
*
* @access public
* @param string the database name
* @return bool
*/
function drop_database($db_name)
{
$sql = $this->_drop_database($db_name);
if (is_bool($sql))
{
return $sql;
}
return $this->db->query($sql);
}
// --------------------------------------------------------------------
/**
* Add Key
*
* @access public
* @param string key
* @param bool primary or not
* @param bool fulltext or not
* @return void
*/
// RYAN VENNELL - ADDED A FULLTEXT BOOLEAN OPTIONAL PARAMETER
function add_key($key = '', $primary = FALSE, $fulltext = FALSE)
{
if (is_array($key))
{
foreach($key as $one)
{
$this->add_key($one, $primary);
}
return;
}
if ($key == '')
{
show_error('Key information is required for that operation.');
}
if ($primary === TRUE)
{
$this->primary_keys[] = $key;
}
// RYAN VENNELL - ADDED ELSEIF TO ADD KEY AS FULLTEXT IF SPECIFIED
elseif ($fulltext === TRUE) {
$this->fulltext_keys[] = $key;
}
else
{
$this->keys[] = $key;
}
}
// --------------------------------------------------------------------
/**
* Add Field
*
* @access public
* @param string collation
* @return void
*/
function add_field($field = '')
{
if ($field == '')
{
show_error('Field information is required.');
}
if (is_string($field))
{
if ($field == 'id')
{
$this->add_field(array(
'id' => array(
'type' => 'INT',
'constraint' => 9,
'auto_increment' => TRUE
)
));
$this->add_key('id', TRUE);
}
else
{
if (strpos($field, ' ') === FALSE)
{
show_error('Field information is required for that operation.');
}
$this->fields[] = $field;
}
}
if (is_array($field))
{
$this->fields = array_merge($this->fields, $field);
}
}
// --------------------------------------------------------------------
/**
* Create Table
*
* @access public
* @param string the table name
* @return bool
*/
function create_table($table = '', $if_not_exists = FALSE)
{
if ($table == '')
{
show_error('A table name is required for that operation.');
}
if (count($this->fields) == 0)
{
show_error('Field information is required.');
}
// RYAN VENNELL - ADDED $this->fulltext_keys TO PASS OUR NEW FULLTEXT KEYS INTO THE _create_table() method
$sql = $this->_create_table($this->db->dbprefix.$table, $this->fields, $this->primary_keys, $this->keys, $if_not_exists, $this->fulltext_keys);
$this->_reset();
return $this->db->query($sql);
}
// --------------------------------------------------------------------
/**
* Drop Table
*
* @access public
* @param string the table name
* @return bool
*/
function drop_table($table_name)
{
$sql = $this->_drop_table($this->db->dbprefix.$table_name);
if (is_bool($sql))
{
return $sql;
}
return $this->db->query($sql);
}
// --------------------------------------------------------------------
/**
* Rename Table
*
* @access public
* @param string the old table name
* @param string the new table name
* @return bool
*/
function rename_table($table_name, $new_table_name)
{
if ($table_name == '' OR $new_table_name == '')
{
show_error('A table name is required for that operation.');
}
$sql = $this->_rename_table($table_name, $new_table_name);
return $this->db->query($sql);
}
// --------------------------------------------------------------------
/**
* Column Add
*
* @access public
* @param string the table name
* @param string the column name
* @param string the column definition
* @return bool
*/
function add_column($table = '', $field = array(), $after_field = '')
{
if ($table == '')
{
show_error('A table name is required for that operation.');
}
// add field info into field array, but we can only do one at a time
// so only grab the first field in the event there are more then one
$this->add_field(array_slice($field, 0, 1));
if (count($this->fields) == 0)
{
show_error('Field information is required.');
}
$sql = $this->_alter_table('ADD', $this->db->dbprefix.$table, $this->fields, $after_field);
$this->_reset();
return $this->db->query($sql);
}
// --------------------------------------------------------------------
/**
* Column Drop
*
* @access public
* @param string the table name
* @param string the column name
* @return bool
*/
function drop_column($table = '', $column_name = '')
{
if ($table == '')
{
show_error('A table name is required for that operation.');
}
if ($column_name == '')
{
show_error('A column name is required for that operation.');
}
$sql = $this->_alter_table('DROP', $this->db->dbprefix.$table, $column_name);
return $this->db->query($sql);
}
// --------------------------------------------------------------------
/**
* Column Modify
*
* @access public
* @param string the table name
* @param string the column name
* @param string the column definition
* @return bool
*/
function modify_column($table = '', $field = array())
{
if ($table == '')
{
show_error('A table name is required for that operation.');
}
// add field info into field array, but we can only do one at a time
// so only grab the first field in the event there are more then one
$this->add_field(array_slice($field, 0, 1));
if (count($this->fields) == 0)
{
show_error('Field information is required.');
}
$sql = $this->_alter_table('CHANGE', $this->db->dbprefix.$table, $this->fields);
$this->_reset();
return $this->db->query($sql);
}
// --------------------------------------------------------------------
/**
* Reset
*
* Resets table creation vars
*
* @access private
* @return void
*/
function _reset()
{
$this->fields = array();
$this->keys = array();
$this->primary_keys = array();
$this->fulltext_keys = array();
}
}
/* End of file DB_forge.php */
/* Location: ./system/database/DB_forge.php */File: ./system/database/drivers/mysql/mysql_forge.php
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/*
- Bug fixed with DBForge handling primary keys in the _create_table() method
- Added Fulltext key as well as forcing MyISAM if fulltext keys are used
to the _create_table() function. Changes detailed in the function.
*/
/**
* CodeIgniter
*
* An open source application development framework for PHP 4.3.2 or newer
*
* @package CodeIgniter
* @author ExpressionEngine Dev Team
* @copyright Copyright (c) 2008, EllisLab, Inc.
* @license http://codeigniter.com/user_guide/license.html
* @link http://codeigniter.com
* @since Version 1.0
* @filesource
*/
// ------------------------------------------------------------------------
/**
* MySQL Forge Class
*
* @category Database
* @author ExpressionEngine Dev Team
* @link http://codeigniter.com/user_guide/database/
*/
class CI_DB_mysql_forge extends CI_DB_forge {
/**
* Create database
*
* @access private
* @param string the database name
* @return bool
*/
function _create_database($name)
{
return "CREATE DATABASE ".$name;
}
// --------------------------------------------------------------------
/**
* Drop database
*
* @access private
* @param string the database name
* @return bool
*/
function _drop_database($name)
{
return "DROP DATABASE ".$name;
}
// --------------------------------------------------------------------
/**
* Process Fields
*
* @access private
* @param mixed the fields
* @return string
*/
function _process_fields($fields)
{
$current_field_count = 0;
$sql = '';
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);
if (array_key_exists('NAME', $attributes))
{
$sql .= ' '.$this->db->_protect_identifiers($attributes['NAME']).' ';
}
if (array_key_exists('TYPE', $attributes))
{
$sql .= ' '.$attributes['TYPE'];
}
if (array_key_exists('CONSTRAINT', $attributes))
{
$sql .= '('.$attributes['CONSTRAINT'].')';
}
if (array_key_exists('UNSIGNED', $attributes) && $attributes['UNSIGNED'] === TRUE)
{
$sql .= ' UNSIGNED';
}
if (array_key_exists('DEFAULT', $attributes))
{
$sql .= ' DEFAULT \''.$attributes['DEFAULT'].'\'';
}
if (array_key_exists('NULL', $attributes))
{
$sql .= ($attributes['NULL'] === TRUE) ? ' NULL' : ' NOT NULL';
}
if (array_key_exists('AUTO_INCREMENT', $attributes) && $attributes['AUTO_INCREMENT'] === TRUE)
{
$sql .= ' AUTO_INCREMENT';
}
}
// don't add a comma on the end of the last field
if (++$current_field_count < count($fields))
{
$sql .= ',';
}
}
return $sql;
}
// --------------------------------------------------------------------
/**
* Create Table
*
* @access private
* @param string the table name
* @param mixed the fields
* @param mixed primary key(s)
* @param mixed key(s)
* @param boolean should 'IF NOT EXISTS' be added to the SQL
* @param mixed fulltext key(s)
* @return bool
*/
// RYAN VENNELL - ADDED FULLTEXT KEY SUPPORT AND A METHOD PARAMETER FOR IT
function _create_table($table, $fields, $primary_keys, $keys, $if_not_exists, $fulltext_keys)
{
$sql = 'CREATE TABLE ';
if ($if_not_exists === TRUE)
{
$sql .= 'IF NOT EXISTS ';
}
$sql .= $this->db->_escape_identifiers($table)." (";
$sql .= $this->_process_fields($fields);
if (count($primary_keys) > 0)
{
$key_name = $this->db->_protect_identifiers(implode('_', $primary_keys));
//$primary_keys = $this->db->_protect_identifiers($primary_keys);
//$sql .= ",\n\tPRIMARY KEY ".$key_name." (" . implode(', ', $primary_keys) . ")";
$primary_keys = $this->db->_protect_identifiers(implode(", ", $primary_keys));
$sql .= ",\n\tPRIMARY KEY ".$key_name." (" . $primary_keys . ")";
}
// RYAN VENNELL - ADDED FULLTEXT KEY SUPPORT
if (count($fulltext_keys) > 0 )
{
foreach ( $fulltext_keys as $key ) {
$key_name = $this->db->_protect_identifiers($key);
$key = array($key_name);
$sql .= ",\n\tFULLTEXT KEY {$key_name} (" . implode(', ', $key) . ")";
}
}
if (is_array($keys) && count($keys) > 0)
{
foreach ($keys as $key)
{
if (is_array($key))
{
$key_name = $this->db->_protect_identifiers(implode('_', $key));
$key = $this->db->_protect_identifiers($key);
}
else
{
$key_name = $this->db->_protect_identifiers($key);
$key = array($key_name);
}
$sql .= ",\n\tKEY {$key_name} (" . implode(', ', $key) . ")";
}
}
$sql .= "\n)";
// RYAN VENNELL - FORCING MyISAM IF FULLTEXT KEYS ARE ENABLED
if (count($fulltext_keys) > 0) $sql .= " ENGINE=MyISAM";
$sql .= " DEFAULT CHARACTER SET {$this->db->char_set} COLLATE {$this->db->dbcollat};";
return $sql;
}
// --------------------------------------------------------------------
/**
* Drop Table
*
* @access private
* @return string
*/
function _drop_table($table)
{
return "DROP TABLE IF EXISTS ".$this->db->_escape_identifiers($table);
}
// --------------------------------------------------------------------
/**
* Alter table query
*
* Generates a platform-specific query so that a table can be altered
* Called by add_column(), drop_column(), and column_alter(),
*
* @access private
* @param string the ALTER type (ADD, DROP, CHANGE)
* @param string the column name
* @param array fields
* @param string the field after which we should add the new field
* @return object
*/
function _alter_table($alter_type, $table, $fields, $after_field = '')
{
$sql = 'ALTER TABLE '.$this->db->_protect_identifiers($table)." $alter_type ";
// DROP has everything it needs now.
if ($alter_type == 'DROP')
{
return $sql.$this->db->_protect_identifiers($fields);
}
$sql .= $this->_process_fields($fields);
if ($after_field != '')
{
$sql .= ' AFTER ' . $this->db->_protect_identifiers($after_field);
}
return $sql;
}
// --------------------------------------------------------------------
/**
* Rename a table
*
* Generates a platform-specific query so that a table can be renamed
*
* @access private
* @param string the old table name
* @param string the new table name
* @return string
*/
function _rename_table($table_name, $new_table_name)
{
$sql = 'ALTER TABLE '.$this->db->_protect_identifiers($table_name)." RENAME TO ".$this->db->_protect_identifiers($new_table_name);
return $sql;
}
}
/* End of file mysql_forge.php */
/* Location: ./system/database/drivers/mysql/mysql_forge.php */