413 lines
11 KiB
ReStructuredText
413 lines
11 KiB
ReStructuredText
####################
|
|
Database Forge Class
|
|
####################
|
|
|
|
The Database Forge Class contains methods that help you manage your
|
|
database.
|
|
|
|
.. contents:: Table of Contents
|
|
:depth: 3
|
|
|
|
****************************
|
|
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
|
|
***************
|
|
|
|
.. php:class:: CI_DB_forge
|
|
|
|
.. php:method:: add_column($table[, $field = array()[, $_after = NULL]])
|
|
|
|
:param string $table: Table name to add the column to
|
|
:param array $field: Column definition(s)
|
|
:param string $_after: Column for AFTER clause (deprecated)
|
|
:returns: TRUE on success, FALSE on failure
|
|
:rtype: bool
|
|
|
|
Adds a column to a table. Usage: See `Adding a Column to a Table`_.
|
|
|
|
.. php:method:: add_field($field)
|
|
|
|
:param array $field: Field definition to add
|
|
:returns: CI_DB_forge instance (method chaining)
|
|
:rtype: CI_DB_forge
|
|
|
|
Adds a field to the set that will be used to create a table. Usage: See `Adding fields`_.
|
|
|
|
.. php:method:: add_key($key[, $primary = FALSE])
|
|
|
|
:param array $key: Name of a key field
|
|
:param bool $primary: Set to TRUE if it should be a primary key or a regular one
|
|
:returns: CI_DB_forge instance (method chaining)
|
|
:rtype: CI_DB_forge
|
|
|
|
Adds a key to the set that will be used to create a table. Usage: See `Adding Keys`_.
|
|
|
|
.. php:method:: create_database($db_name)
|
|
|
|
:param string $db_name: Name of the database to create
|
|
:returns: TRUE on success, FALSE on failure
|
|
:rtype: bool
|
|
|
|
Creates a new database. Usage: See `Creating and Dropping Databases`_.
|
|
|
|
.. php:method:: create_table($table[, $if_not_exists = FALSE[, array $attributes = array()]])
|
|
|
|
:param string $table: Name of the table to create
|
|
:param string $if_not_exists: Set to TRUE to add an 'IF NOT EXISTS' clause
|
|
:param string $attributes: An associative array of table attributes
|
|
:returns: TRUE on success, FALSE on failure
|
|
:rtype: bool
|
|
|
|
Creates a new table. Usage: See `Creating a table`_.
|
|
|
|
.. php:method:: drop_column($table, $column_name)
|
|
|
|
:param string $table: Table name
|
|
:param array $column_name: The column name to drop
|
|
:returns: TRUE on success, FALSE on failure
|
|
:rtype: bool
|
|
|
|
Drops a column from a table. Usage: See `Dropping a Column From a Table`_.
|
|
|
|
.. php:method:: drop_database($db_name)
|
|
|
|
:param string $db_name: Name of the database to drop
|
|
:returns: TRUE on success, FALSE on failure
|
|
:rtype: bool
|
|
|
|
Drops a database. Usage: See `Creating and Dropping Databases`_.
|
|
|
|
.. php:method:: drop_table($table_name[, $if_exists = FALSE])
|
|
|
|
:param string $table: Name of the table to drop
|
|
:param string $if_exists: Set to TRUE to add an 'IF EXISTS' clause
|
|
:returns: TRUE on success, FALSE on failure
|
|
:rtype: bool
|
|
|
|
Drops a table. Usage: See `Dropping a table`_.
|
|
|
|
.. php:method:: modify_column($table, $field)
|
|
|
|
:param string $table: Table name
|
|
:param array $field: Column definition(s)
|
|
:returns: TRUE on success, FALSE on failure
|
|
:rtype: bool
|
|
|
|
Modifies a table column. Usage: See `Modifying a Column in a Table`_.
|
|
|
|
.. php:method:: rename_table($table_name, $new_table_name)
|
|
|
|
:param string $table: Current of the table
|
|
:param string $new_table_name: New name of the table
|
|
:returns: TRUE on success, FALSE on failure
|
|
:rtype: bool
|
|
|
|
Renames a table. Usage: See `Renaming a table`_.
|