Database Forge Class
********************

The Database Forge Class contains methods that help you manage your
database.


Table of Contents
^^^^^^^^^^^^^^^^^

* Database Forge Class

  * Initializing the Forge Class

  * Creating and Dropping Databases

  * Creating and Dropping Tables

    * Adding fields

    * Adding Keys

    * Creating a table

    * Dropping a table

    * Renaming a table

  * Modifying Tables

    * Adding a Column to a Table

    * Dropping a Column From a Table

    * Modifying a Column in a Table

  * Class Reference


Initializing the Forge Class
============================

Important:

  In order to initialize the Forge class, your database driver must
  already be running, since the forge class relies on it.

Load the Forge Class as follows:

   $this->load->dbforge()

You can also pass another database object to the DB Forge loader, in
case the database you want to manage isn’t the default one:

   $this->myforge = $this->load->dbforge($this->other_db, TRUE);

In the above example, we’re passing a custom database object as the
first parameter and then tell it to return the dbforge object, instead
of assigning it directly to "$this->dbforge".

Note:

  Both of the parameters can be used individually, just pass an empty
  value as the first one if you wish to skip it.

Once initialized you will access the methods using the
"$this->dbforge" object:

   $this->dbforge->some_method();


Creating and Dropping Databases
===============================

**$this->dbforge->create_database(‘db_name’)**

Permits you to create the database specified in the first parameter.
Returns TRUE/FALSE based on success or failure:

   if ($this->dbforge->create_database('my_db'))
   {
           echo 'Database created!';
   }

**$this->dbforge->drop_database(‘db_name’)**

Permits you to drop the database specified in the first parameter.
Returns TRUE/FALSE based on success or failure:

   if ($this->dbforge->drop_database('my_db'))
   {
           echo 'Database deleted!';
   }


Creating and Dropping Tables
============================

There are several things you may wish to do when creating tables. Add
fields, add keys to the table, alter columns. CodeIgniter provides a
mechanism for this.


Adding fields
-------------

Fields are created via an associative array. Within the array you must
include a ‘type’ key that relates to the datatype of the field. For
example, INT, VARCHAR, TEXT, etc. Many datatypes (for example VARCHAR)
also require a ‘constraint’ key.

   $fields = array(
           'users' => array(
                   'type' => 'VARCHAR',
                   'constraint' => '100',
           ),
   );
   // will translate to "users VARCHAR(100)" when the field is added.

Additionally, the following key/values can be used:

* unsigned/true : to generate “UNSIGNED” in the field definition.

* default/value : to generate a default value in the field definition.

* null/true : to generate “NULL” in the field definition. Without
  this, the field will default to “NOT NULL”.

* auto_increment/true : generates an auto_increment flag on the field.
  Note that the field type must be a type that supports this, such as
  integer.

* unique/true : to generate a unique key for the field definition.

   $fields = array(
           'blog_id' => array(
                   'type' => 'INT',
                   'constraint' => 5,
                   'unsigned' => TRUE,
                   'auto_increment' => TRUE
           ),
           'blog_title' => array(
                   'type' => 'VARCHAR',
                   'constraint' => '100',
                   'unique' => TRUE,
           ),
           'blog_author' => array(
                   'type' =>'VARCHAR',
                   'constraint' => '100',
                   'default' => 'King of Town',
           ),
           'blog_description' => array(
                   'type' => 'TEXT',
                   'null' => TRUE,
           ),
   );

After the fields have been defined, they can be added using
"$this->dbforge->add_field($fields);" followed by a call to the
"create_table()" method.

**$this->dbforge->add_field()**

The add fields method will accept the above array.


Passing strings as fields
~~~~~~~~~~~~~~~~~~~~~~~~~

If you know exactly how you want a field to be created, you can pass
the string into the field definitions with add_field()

   $this->dbforge->add_field("label varchar(100) NOT NULL DEFAULT 'default label'");

Note:

  Passing raw strings as fields cannot be followed by "add_key()"
  calls on those fields.

Note:

  Multiple calls to add_field() are cumulative.


Creating an id field
~~~~~~~~~~~~~~~~~~~~

There is a special exception for creating id fields. A field with type
id will automatically be assigned as an INT(9) auto_incrementing
Primary Key.

   $this->dbforge->add_field('id');
   // gives id INT(9) NOT NULL AUTO_INCREMENT


Adding Keys
-----------

Generally speaking, you’ll want your table to have Keys. This is
accomplished with $this->dbforge->add_key(‘field’). An optional second
parameter set to TRUE will make it a primary key. Note that add_key()
must be followed by a call to create_table().

Multiple column non-primary keys must be sent as an array. Sample
output below is for MySQL.

   $this->dbforge->add_key('blog_id', TRUE);
   // gives PRIMARY KEY `blog_id` (`blog_id`)

   $this->dbforge->add_key('blog_id', TRUE);
   $this->dbforge->add_key('site_id', TRUE);
   // gives PRIMARY KEY `blog_id_site_id` (`blog_id`, `site_id`)

   $this->dbforge->add_key('blog_name');
   // gives KEY `blog_name` (`blog_name`)

   $this->dbforge->add_key(array('blog_name', 'blog_label'));
   // gives KEY `blog_name_blog_label` (`blog_name`, `blog_label`)


Creating a table
----------------

After fields and keys have been declared, you can create a new table
with

   $this->dbforge->create_table('table_name');
   // gives CREATE TABLE table_name

An optional second parameter set to TRUE adds an “IF NOT EXISTS”
clause into the definition

   $this->dbforge->create_table('table_name', TRUE);
   // gives CREATE TABLE IF NOT EXISTS table_name

You could also pass optional table attributes, such as MySQL’s
"ENGINE":

   $attributes = array('ENGINE' => 'InnoDB');
   $this->dbforge->create_table('table_name', FALSE, $attributes);
   // produces: CREATE TABLE `table_name` (...) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Note:

  Unless you specify the "CHARACTER SET" and/or "COLLATE" attributes,
  "create_table()" will always add them with your configured
  *char_set* and *dbcollat* values, as long as they are not empty
  (MySQL only).


Dropping a table
----------------

Execute a DROP TABLE statement and optionally add an IF EXISTS clause.

   // Produces: DROP TABLE table_name
   $this->dbforge->drop_table('table_name');

   // Produces: DROP TABLE IF EXISTS table_name
   $this->dbforge->drop_table('table_name',TRUE);


Renaming a table
----------------

Executes a TABLE rename

   $this->dbforge->rename_table('old_table_name', 'new_table_name');
   // gives ALTER TABLE old_table_name RENAME TO new_table_name


Modifying Tables
================


Adding a Column to a Table
--------------------------

**$this->dbforge->add_column()**

The "add_column()" method is used to modify an existing table. It
accepts the same field array as above, and can be used for an
unlimited number of additional fields.

   $fields = array(
           'preferences' => array('type' => 'TEXT')
   );
   $this->dbforge->add_column('table_name', $fields);
   // Executes: ALTER TABLE table_name ADD preferences TEXT

If you are using MySQL or CUBIRD, then you can take advantage of their
AFTER and FIRST clauses to position the new column.

Examples:

   // Will place the new column after the `another_field` column:
   $fields = array(
           'preferences' => array('type' => 'TEXT', 'after' => 'another_field')
   );

   // Will place the new column at the start of the table definition:
   $fields = array(
           'preferences' => array('type' => 'TEXT', 'first' => TRUE)
   );


Dropping a Column From a Table
------------------------------

**$this->dbforge->drop_column()**

Used to remove a column from a table.

   $this->dbforge->drop_column('table_name', 'column_to_drop');


Modifying a Column in a Table
-----------------------------

**$this->dbforge->modify_column()**

The usage of this method is identical to "add_column()", except it
alters an existing column rather than adding a new one. In order to
change the name you can add a “name” key into the field defining
array.

   $fields = array(
           'old_name' => array(
                   'name' => 'new_name',
                   'type' => 'TEXT',
           ),
   );
   $this->dbforge->modify_column('table_name', $fields);
   // gives ALTER TABLE table_name CHANGE old_name new_name TEXT


Class Reference
===============

class CI_DB_forge

   add_column($table[, $field = array()[, $_after = NULL]])

      Parameters:
         * **$table** ("string") – Table name to add the column to

         * **$field** ("array") – Column definition(s)

         * **$_after** ("string") – Column for AFTER clause
           (deprecated)

      Returns:
         TRUE on success, FALSE on failure

      Return type:
         "bool"

      Adds a column to a table. Usage:  See Adding a Column to a
      Table.

   add_field($field)

      Parameters:
         * **$field** ("array") – Field definition to add

      Returns:
         CI_DB_forge instance (method chaining)

      Return type:
         "CI_DB_forge"

      Adds a field to the set that will be used to create a table.
      Usage:  See Adding fields.

   add_key($key[, $primary = FALSE])

      Parameters:
         * **$key** ("array") – Name of a key field

         * **$primary** ("bool") – Set to TRUE if it should be a
           primary key or a regular one

      Returns:
         CI_DB_forge instance (method chaining)

      Return type:
         "CI_DB_forge"

      Adds a key to the set that will be used to create a table.
      Usage:  See Adding Keys.

   create_database($db_name)

      Parameters:
         * **$db_name** ("string") – Name of the database to create

      Returns:
         TRUE on success, FALSE on failure

      Return type:
         "bool"

      Creates a new database. Usage:  See Creating and Dropping
      Databases.

   create_table($table[, $if_not_exists = FALSE[, array $attributes = array()]])

      Parameters:
         * **$table** ("string") – Name of the table to create

         * **$if_not_exists** ("string") – Set to TRUE to add an ‘IF
           NOT EXISTS’ clause

         * **$attributes** ("string") – An associative array of table
           attributes

      Returns:
         TRUE on success, FALSE on failure

      Return type:
         "bool"

      Creates a new table. Usage:  See Creating a table.

   drop_column($table, $column_name)

      Parameters:
         * **$table** ("string") – Table name

         * **$column_name** ("string") – The column name to drop

      Returns:
         TRUE on success, FALSE on failure

      Return type:
         "bool"

      Drops a column from a table. Usage:  See Dropping a Column From
      a Table.

   drop_database($db_name)

      Parameters:
         * **$db_name** ("string") – Name of the database to drop

      Returns:
         TRUE on success, FALSE on failure

      Return type:
         "bool"

      Drops a database. Usage:  See Creating and Dropping Databases.

   drop_table($table_name[, $if_exists = FALSE])

      Parameters:
         * **$table** ("string") – Name of the table to drop

         * **$if_exists** ("string") – Set to TRUE to add an ‘IF
           EXISTS’ clause

      Returns:
         TRUE on success, FALSE on failure

      Return type:
         "bool"

      Drops a table. Usage:  See Dropping a table.

   modify_column($table, $field)

      Parameters:
         * **$table** ("string") – Table name

         * **$field** ("array") – Column definition(s)

      Returns:
         TRUE on success, FALSE on failure

      Return type:
         "bool"

      Modifies a table column. Usage:  See Modifying a Column in a
      Table.

   rename_table($table_name, $new_table_name)

      Parameters:
         * **$table** ("string") – Current of the table

         * **$new_table_name** ("string") – New name of the table

      Returns:
         TRUE on success, FALSE on failure

      Return type:
         "bool"

      Renames a table. Usage:  See Renaming a table.
