CODEWITHSUNDEEP
mysqlcodeigniter
Amitav Roy
Amitav Roy

Reputation: 761

Adding comments to columns in codeigniter migrations

I am writing a lot of migration scripts for the database for my application. I would like to add comments for the columns so that others can easily recognize the column's content. One option is to write normal SQL query and add the comment. But is there a way I can add these comments inside the Migration scipt?

$this->dbforge->add_field(array(
  'post_id' => array(
    'type' => 'INT',
    'constraint' => 11,
    'unsigned' => true,
    'auto_increment' => true,
    'comment' => 'Unique post id'
  ),
  'user_id' => array(
    'type' => 'INT',
    'constraint' => 11,
    'unsigned' => true,
  ),
  'group_id' => array(
    'type' => 'INT',
    'constraint' => 11,
    'unsigned' => true,
  ),
  'source' => array(
    'type' => 'VARCHAR',
    'constraint' => 20
  ),
  'data' => array(
    'type' => 'TEXT',
  ),
  'created' => array(
    'type' => 'INT',
    'constraint' => 11,
    'unsigned' => true,
  ),
  'updated' => array(
    'type' => 'INT',
    'constraint' => 11,
    'unsigned' => true,
  ),
  'status' => array(
    'type' => 'INT',
    'constraint' => 1,
    'unsigned' => true,
  )
));

This is the basic code that I have written. May have some syntax error. But I just copy pasted it.

Can anyone please help.

Upvotes: 5

Views: 3376

Answers (3)

Akshay Shrama
Akshay Shrama

Reputation: 83

CodeIgniter added this ability with version 3.0. You can add comments using the 'comment' key:

'first_name' => [
    'type'       => 'VARCHAR',
    'constraint' => 45,
    'null'       => false,
    'comment' => 'Put the field comment here',
]

Upvotes: 8

Waqleh
Waqleh

Reputation: 10161

What you can do is the following in the up method just before the closing tag of it:

$this->db->query("ALTER TABLE `" . $this->db->dbprefix . $this->table_name . "` CHANGE `post_id` `post_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Unique post id'");

Is there any other way to do it without including the column definition in MySQL? No

Note:

Altering a comment will cause a full resconstruction of the table. So you may choose to live without it on very big table.

The Best solution would be to create, extend or copy the mysql or mysqli and reimplementing the _process_fields to handle the comment of the fields. This link can help get you started and this is the advanced.

Upvotes: 0

zachflower
zachflower

Reputation: 56

Looking at the CodeIgniter core, specifically system/database/drivers/mysql/mysql_forge.php, it looks like the COMMENT field isn't supported.

For reference, here is the function that parses the fields array out:

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))
                {
                    switch ($attributes['TYPE'])
                    {
                        case 'decimal':
                        case 'float':
                        case 'numeric':
                            $sql .= '('.implode(',', $attributes['CONSTRAINT']).')';
                        break;

                        case 'enum':
                        case 'set':
                            $sql .= '("'.implode('","', $attributes['CONSTRAINT']).'")';
                        break;

                        default:
                            $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) && $attributes['NULL'] === TRUE)
            {
                $sql .= ' NULL';
            }
            else
            {
                $sql .= ' 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;
}

Upvotes: 1

Related Questions