From 8ede1a2ecbb62577afd32996956c5feaf7ddf9b6 Mon Sep 17 00:00:00 2001 From: Derek Jones Date: Wed, 5 Oct 2011 13:34:52 -0500 Subject: replacing the old HTML user guide with a Sphinx-managed user guide --- user_guide_src/source/database/forge.rst | 212 +++++++++++++++++++++++++++++++ 1 file changed, 212 insertions(+) create mode 100644 user_guide_src/source/database/forge.rst (limited to 'user_guide_src/source/database/forge.rst') diff --git a/user_guide_src/source/database/forge.rst b/user_guide_src/source/database/forge.rst new file mode 100644 index 000000000..ee033248c --- /dev/null +++ b/user_guide_src/source/database/forge.rst @@ -0,0 +1,212 @@ +#################### +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 + + + -- cgit v1.2.3-24-g4f1b From f24f404a34081241c1398f568b506e2c9d9bec5b Mon Sep 17 00:00:00 2001 From: Joseph Wensley Date: Thu, 6 Oct 2011 22:53:29 -0400 Subject: cleaning up and formatting database pages --- user_guide_src/source/database/forge.rst | 88 ++++++++++++++++++++++++++------ 1 file changed, 72 insertions(+), 16 deletions(-) (limited to 'user_guide_src/source/database/forge.rst') diff --git a/user_guide_src/source/database/forge.rst b/user_guide_src/source/database/forge.rst index ee033248c..bf17e2918 100644 --- a/user_guide_src/source/database/forge.rst +++ b/user_guide_src/source/database/forge.rst @@ -29,7 +29,10 @@ $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!'; } + if ($this->dbforge->create_database('my_db')) + { + echo 'Database created!'; + } $this->dbforge->drop_database('db_name') ========================================== @@ -37,7 +40,10 @@ $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!'; } + if ($this->dbforge->drop_database('my_db')) + { + echo 'Database deleted!'; + } **************************** Creating and Dropping Tables @@ -57,7 +63,13 @@ also require a 'constraint' key. :: - $fields = array(                         'users' => array(                                                  'type' => 'VARCHAR',                                                  'constraint' => '100',                                           ),                 ); // will translate to "users VARCHAR(100)" when the field is added. + $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: @@ -72,7 +84,27 @@ Additionally, the following key/values can be used: :: - $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,                                           ),                 ); + $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 @@ -95,7 +127,7 @@ 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. +.. note:: Multiple calls to add_field() are cumulative. Creating an id field -------------------- @@ -106,7 +138,8 @@ Primary Key. :: - $this->dbforge->add_field('id'); // gives id INT(9) NOT NULL AUTO_INCREMENT + $this->dbforge->add_field('id'); + // gives id INT(9) NOT NULL AUTO_INCREMENT Adding Keys @@ -122,7 +155,18 @@ 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`) + $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 @@ -133,7 +177,8 @@ with :: - $this->dbforge->create_table('table_name'); // gives CREATE TABLE table_name + $this->dbforge->create_table('table_name'); + // gives CREATE TABLE table_name An optional second parameter set to TRUE adds an "IF NOT EXISTS" clause @@ -141,7 +186,8 @@ into the definition :: - $this->dbforge->create_table('table_name', TRUE); // gives CREATE TABLE IF NOT EXISTS table_name + $this->dbforge->create_table('table_name', TRUE); + // gives CREATE TABLE IF NOT EXISTS table_name Dropping a table @@ -151,7 +197,8 @@ Executes a DROP TABLE sql :: - $this->dbforge->drop_table('table_name'); // gives DROP TABLE IF EXISTS table_name + $this->dbforge->drop_table('table_name'); + // gives DROP TABLE IF EXISTS table_name Renaming a table @@ -161,7 +208,8 @@ 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 + $this->dbforge->rename_table('old_table_name', 'new_table_name'); + // gives ALTER TABLE old_table_name RENAME TO new_table_name **************** @@ -177,7 +225,11 @@ 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 + $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. @@ -206,7 +258,11 @@ 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 - - - + $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 \ No newline at end of file -- cgit v1.2.3-24-g4f1b