Skip to content

DBForge MySQL FULLTEXT Key Support

Jump to bottom Edit New page
Derek Jones edited this page Jul 5, 2012 · 10 revisions

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 */
Add a custom footer
Add a custom sidebar
Clone this wiki locally