- -

Database Forge Class

- -

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

- -

Initializing the Forge Class
Creating a Database
Dropping a Database
Adding Fields
Adding Keys
Creating a Table
Dropping a Table
Renaming a Table
Modifying a Table

- - -

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

CodeIgniter User Guide Version 2.0.3