user_guide_src/source/database/forge.rst

####################
Database Forge Class
####################

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

.. contents:: Table of Contents

****************************
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()

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

	$this->dbforge->some_function()

$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.

::

	$fields = array(                             'blog_id' => array(                                                      'type' => 'INT',                                                      'constraint' => 5,                                                       'unsigned' => TRUE,                                                      'auto_increment' => TRUE                                               ),                             'blog_title' => array(                                                  'type' => 'VARCHAR',                                                  'constraint' => '100',                                           ),                         '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() function.

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

The add fields function 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: 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 assinged 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


Dropping a table
================

Executes a DROP TABLE sql

::

	$this->dbforge->drop_table('table_name');     // gives DROP TABLE IF EXISTS  table_name


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
****************

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

The add_column() function 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);  // gives ALTER TABLE table_name ADD     preferences TEXT

An optional third parameter can be used to specify which existing column
to add the new column after.

::

	$this->dbforge->add_column('table_name', $fields, 'after_field');


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

Used to remove a column from a table.

::

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


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

The usage of this function 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