From 39b622db9bda38282a32bb45623da63efe685729 Mon Sep 17 00:00:00 2001 From: Derek Allard Date: Wed, 16 Jan 2008 21:10:09 +0000 Subject: Many new Active Record functions, and another whack of stuff --- user_guide/database/active_record.html | 109 ++++++++++++---- user_guide/database/caching.html | 2 +- user_guide/database/call_function.html | 2 +- user_guide/database/configuration.html | 2 +- user_guide/database/connecting.html | 2 +- user_guide/database/examples.html | 2 +- user_guide/database/fields.html | 2 +- user_guide/database/forge.html | 221 +++++++++++++++++++++++++++++++++ user_guide/database/helpers.html | 2 +- user_guide/database/index.html | 5 +- user_guide/database/queries.html | 6 +- user_guide/database/results.html | 2 +- user_guide/database/table_data.html | 114 ++++++++++++++++- user_guide/database/transactions.html | 2 +- user_guide/database/utilities.html | 62 ++------- 15 files changed, 450 insertions(+), 85 deletions(-) create mode 100644 user_guide/database/forge.html (limited to 'user_guide/database') diff --git a/user_guide/database/active_record.html b/user_guide/database/active_record.html index d43101642..45cabdbaa 100644 --- a/user_guide/database/active_record.html +++ b/user_guide/database/active_record.html @@ -27,7 +27,7 @@
- +

CodeIgniter User Guide Version 1.5.4

CodeIgniter User Guide Version 1.6.0
@@ -128,19 +128,53 @@ instead of using the db->where() function:

Please read the about the where function below for more information.

Note: get_where() was formerly known as getwhere(), which has been deprecated

-

$this->db->select();

+

$this->db->select();

Permits you to write the SELECT portion of your query:

- - +

$this->db->select('title, content, date');

$query = $this->db->get('mytable');

-// Produces: SELECT title, content, date FROM mytable +// Produces: SELECT title, content, date FROM mytable

+

Note: If you are selecting all (*) from a table you do not need to use this function. When omitted, CodeIgniter assumes you wish to SELECT *

-

Note: If you are selecting all (*) from a table you do not need to use this function. When omitted, CodeIgniter assumes you wish to SELECT *

+

$this->Db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.

+

$this->db->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4') AS amount_paid', FALSE);
+ $query = $this->db->get('mytable');
+

+

$this->db->select_max();

+

Writes a "SELECT MAX(field)" portion for your query. You can optionally include a second parameter to rename the resulting field.

+

+$this->db->select_max('age');
+$query = $this->db->get('members');
+// Produces: SELECT MAX(age) as age FROM members
+
+$this->db->select_max('age', 'member_age');
+$query = $this->db->get('members');
+// Produces: SELECT MAX(age) as member_age FROM members

+ +

$this->db->select_min();

+

Writes a "SELECT MIN(field)" portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

+

+$this->db->select_min('age');
+$query = $this->db->get('members');
+// Produces: SELECT MIN(age) as age FROM members

+ +

$this->db->select_avg();

+

Writes a "SELECT AVG(field)" portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

+

+$this->db->select_avg('age');
+$query = $this->db->get('members');
+// Produces: SELECT AVG(age) as age FROM members

+ +

$this->db->select_sum();

+

Writes a "SELECT SUM(field)" portion for your query. As with select_max(), You can optionally include a second parameter to rename the resulting field.

+

+$this->db->select_sum('age');
+$query = $this->db->get('members');
+// Produces: SELECT SUM(age) as age FROM members

$this->db->from();

@@ -154,8 +188,8 @@ $query = $this->db->get();

// Produces: SELECT title, content, date FROM mytable -

Note: As shown earlier, the FROM portion of your query can be specified in the $this->db->get() function, so use whichever method -you prefer.

+

Note: As shown earlier, the FROM portion of your query can be specified in the $this->db->get() function, so use whichever method +you prefer.

$this->db->join();

@@ -294,6 +328,19 @@ $this->db->or_where('id >', $id); $this->db->where_in('username', $names);
// Produces: OR WHERE username NOT IN ('Frank', 'Todd', 'James')

+

$this->db->raw_where();

+

Generates an unfiltered WHERE portion of the query exactly as the developer passes it. Separates multiple calls with AND
+ $this->db->raw_where('(grade > 50 AND grade < 75)');
+ // Produces: AND WHERE (grade > 50 AND grade < 75)

+ +

$this->db->raw_or_where();

+

Generates an unfiltered WHERE portion of the query exactly as the developer passes it. Separates multiple calls with OR
+ $this->db->raw_where('(grade > 50 AND grade < 75)');
+ // Produces: OR WHERE (grade > 50 AND grade < 75)

+ +

Note: All values passed through raw_where() and raw_or_where() are not escaped automatically, or otherwise touched. It is the responsibility of the developer to ensure all queries are safe. Consider using protect_identifiers() and escaping your queries as appropriate.

+ +

$this->db->like();

This function enables you to generate LIKE clauses, useful for doing searches.

@@ -390,11 +437,12 @@ $this->db->or_not_like('body', 'match');
$this->db->having(array('title =' => 'My Title', 'id <' => $id)); -

// Produces: HAVING title = 'My Title', 'id < 45' - +

// Produces: HAVING title = 'My Title', 'id < 45' +

$this->db->or_having();

+

Identical to having(), only separates multiple clauses with "OR".

$this->db->order_by();

Lets you set an ORDER BY clause. The first parameter contains the name of the column you would like to order by. The second parameter lets you set the direction of the result. Options are asc or desc, or random.

@@ -419,7 +467,7 @@ The second parameter lets you set the direction of the result. Options are

Note: order_by() was formerly known as orderby(), which has been deprecated.

-

Note: random ordering is not currently supported in Orcacle or MSSQL drivers.

+

Note: random ordering is not currently supported in Orcacle or MSSQL drivers. These will default to 'ASC'.

$this->db->limit();

Lets you limit the number of rows you would like returned by the query:

@@ -516,14 +564,19 @@ $this->db->insert('mytable');

If you use multiple function called they will be assembled properly based on whether you are doing an insert or an update:

-$this->db->set('name', $name);
-$this->db->set('title', $title);
-$this->db->set('status', $status);
-$this->db->insert('mytable'); - - +$this->db->set('name', $name);
+$this->db->set('title', $title);
+$this->db->set('status', $status);
+$this->db->insert('mytable'); +

set() will also accept an optional third parameter ($escape), that will prevent data from being escaped if set to FALSE. To illustrate the difference, here is set() used both with and without the escape parameter.

+

$this->db->set('field', 'field+1', FALSE);
+ $this->db->insert('mytable');
+ // gives INSERT INTO mytable (field) VALUES (field+1)
+
+ $this->db->set('field', 'field+1');
+ $this->db->insert('mytable');
+ // gives INSERT INTO mytable (field) VALUES ('field+1')

You can also pass an associative array to this function:

- $array = array('name' => $name, 'title' => $title, 'status' => $status);

@@ -640,9 +693,23 @@ the data to the second parameter of the function:

$tables = array('table1', 'table2', 'table3');
$this->db->where('id', '5');
$this->db->delete($tables);

-

Note: All values are escaped automatically producing safer queries.

- - +

If you want to delete all data from a table, you can use the truncate() function, or empty_table().

+

$this->db->empty_table();

+

Generates a delete SQL string and runs the query. $this->db->empty_table('mytable');
+
+// Produces
+// DELETE FROM mytable

+

$this->db->truncate();

+

Generates a truncate SQL string and runs the query.

+ $this->db->from('mytable');
+$this->db->truncate();
+// or
+$this->db->truncate('mytable');
+
+// Produce:
+// TRUNCATE mytable
+ +

Note: If the TRUNCATE command isn't available, truncate() will execute as "DELETE FROM table".

 

Method Chaining

diff --git a/user_guide/database/caching.html b/user_guide/database/caching.html index d2cbd9c8e..11cc3f5ce 100644 --- a/user_guide/database/caching.html +++ b/user_guide/database/caching.html @@ -28,7 +28,7 @@
- +

CodeIgniter User Guide Version 1.5.4

CodeIgniter User Guide Version 1.6.0
diff --git a/user_guide/database/call_function.html b/user_guide/database/call_function.html index f61b6f7e8..6157acfa7 100644 --- a/user_guide/database/call_function.html +++ b/user_guide/database/call_function.html @@ -28,7 +28,7 @@
- +

CodeIgniter User Guide Version 1.5.4

CodeIgniter User Guide Version 1.6.0
diff --git a/user_guide/database/configuration.html b/user_guide/database/configuration.html index b9b61d32c..f6097f2b7 100644 --- a/user_guide/database/configuration.html +++ b/user_guide/database/configuration.html @@ -28,7 +28,7 @@
- +

CodeIgniter User Guide Version 1.5.4

CodeIgniter User Guide Version 1.6.0
diff --git a/user_guide/database/connecting.html b/user_guide/database/connecting.html index 04c0c2c4d..96dd1f06a 100644 --- a/user_guide/database/connecting.html +++ b/user_guide/database/connecting.html @@ -28,7 +28,7 @@
- +

CodeIgniter User Guide Version 1.5.4

CodeIgniter User Guide Version 1.6.0
diff --git a/user_guide/database/examples.html b/user_guide/database/examples.html index 8989b961c..bcda80f81 100644 --- a/user_guide/database/examples.html +++ b/user_guide/database/examples.html @@ -28,7 +28,7 @@
- +

CodeIgniter User Guide Version 1.5.4

CodeIgniter User Guide Version 1.6.0
diff --git a/user_guide/database/fields.html b/user_guide/database/fields.html index 6dc4eeb8a..ab0920754 100644 --- a/user_guide/database/fields.html +++ b/user_guide/database/fields.html @@ -28,7 +28,7 @@
- +

CodeIgniter User Guide Version 1.5.4

CodeIgniter User Guide Version 1.6.0
diff --git a/user_guide/database/forge.html b/user_guide/database/forge.html new file mode 100644 index 000000000..90991a9c5 --- /dev/null +++ b/user_guide/database/forge.html @@ -0,0 +1,221 @@ + + + + +CodeIgniter User Guide : Database Utility Class + + + + + + + + + + + + + + + + + + + + + + +
+ + + + + +

CodeIgniter User Guide Version 1.6.0

+
+ + + + + + + + + +
+ + + +
+ + + +
+ +

Database Forge Class

+ +

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

+ +

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->dbutil->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->dbutil->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
    • +
+

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

+

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

+

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')
+// 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

+

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 sites ADD preferences TEXT

+

$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_coumn(), except it alters an existing column rather than adding a new one. In order to use it you must add a "name" key into the field defining array.

+

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

+

 

+
+ + + + + + + \ No newline at end of file diff --git a/user_guide/database/helpers.html b/user_guide/database/helpers.html index 6db89e0cc..93b9a6ddc 100644 --- a/user_guide/database/helpers.html +++ b/user_guide/database/helpers.html @@ -28,7 +28,7 @@
- +

CodeIgniter User Guide Version 1.5.4

CodeIgniter User Guide Version 1.6.0
diff --git a/user_guide/database/index.html b/user_guide/database/index.html index 7ad5ace42..d4319b918 100644 --- a/user_guide/database/index.html +++ b/user_guide/database/index.html @@ -28,7 +28,7 @@
- +

CodeIgniter User Guide Version 1.5.4

CodeIgniter User Guide Version 1.6.0
@@ -75,7 +75,8 @@ structures and Active Record patterns. The database functions offer clear, simpl
  • Field MetaData
  • Custom Function Calls
  • Query Caching
    • -
  • Database Utilities Class
    • +
  • Database manipulation with Database Forge
    • +
  • Database Utilities Class
    • diff --git a/user_guide/database/queries.html b/user_guide/database/queries.html index e1d346c38..3ff8cc70f 100644 --- a/user_guide/database/queries.html +++ b/user_guide/database/queries.html @@ -28,7 +28,7 @@
    - +

    CodeIgniter User Guide Version 1.5.4

    CodeIgniter User Guide Version 1.6.0
    @@ -80,8 +80,10 @@ It DOES NOT return a database result set, nor does it set the query timer, or co It simply lets you submit a query. Most users will rarely use this function.

    +

    Protecting identifiers

    +

    In many databases it is advisable to protect table and field names - for example with backticks in MySQL. Active Record queries are automatically protected, however if you need to manually protect an identifier you can use:

    +

    $this->db->protect_identifier('table_name');

    Escaping Queries

    -

    It's a very good security practice to escape your data before submitting it into your database. CodeIgniter has two functions that help you do this:

    diff --git a/user_guide/database/results.html b/user_guide/database/results.html index 9ce6a310c..31b07567d 100644 --- a/user_guide/database/results.html +++ b/user_guide/database/results.html @@ -28,7 +28,7 @@
    - +

    CodeIgniter User Guide Version 1.5.4

    CodeIgniter User Guide Version 1.6.0
    diff --git a/user_guide/database/table_data.html b/user_guide/database/table_data.html index 22e05c108..16c3bba91 100644 --- a/user_guide/database/table_data.html +++ b/user_guide/database/table_data.html @@ -1 +1,113 @@ - CodeIgniter User Guide : Table Data

    CodeIgniter User Guide Version 1.5.4

    Table Data

    These functions let you fetch table information.

    $this->db->list_tables();

    Returns an array containing the names of all the tables in the database you are currently connected to. Example:

    $tables = $this->db->list_tables();

    foreach ($tables as $table)
    {
       echo $table;
    }

    $this->db->table_exists();

    Sometimes it's helpful to know whether a particular table exists before running an operation on it. Returns a boolean TRUE/FALSE. Usage example:

    if ($this->db->table_exists('table_name'))
    {
       // some code...
    }

    Note: Replace table_name with the name of the table you are looking for.

    \ No newline at end of file + + + + +CodeIgniter User Guide : Table Data + + + + + + + + + + + + + + + + + + + + + + +
    + + + + + +

    CodeIgniter User Guide Version 1.6.0

    +
    + + + + + + + + + +
    + + + +
    + + + +
    + + + +

    Table Data

    + +

    These functions let you fetch table information.

    + +

    $this->db->list_tables();

    + +

    Returns an array containing the names of all the tables in the database you are currently connected to. Example:

    + +$tables = $this->db->list_tables();
    +
    +foreach ($tables as $table)
    +{
    +   echo $table;
    +} +     + + +

    $this->db->table_exists();

    + +

    Sometimes it's helpful to know whether a particular table exists before running an operation on it. +Returns a boolean TRUE/FALSE. Usage example:

    + + +if ($this->db->table_exists('table_name'))
    +{
    +   // some code...
    +} +     + +

    Note: Replace table_name with the name of the table you are looking for.

    + + + + + +
    + + + + + + + \ No newline at end of file diff --git a/user_guide/database/transactions.html b/user_guide/database/transactions.html index 8a9ac1b05..3adf0192c 100644 --- a/user_guide/database/transactions.html +++ b/user_guide/database/transactions.html @@ -28,7 +28,7 @@
    - +

    CodeIgniter User Guide Version 1.5.4

    CodeIgniter User Guide Version 1.6.0
    diff --git a/user_guide/database/utilities.html b/user_guide/database/utilities.html index 6b6956c88..cdf3f68f5 100644 --- a/user_guide/database/utilities.html +++ b/user_guide/database/utilities.html @@ -28,7 +28,7 @@
    - +

    CodeIgniter User Guide Version 1.5.4

    CodeIgniter User Guide Version 1.6.0
    @@ -65,8 +65,6 @@ Database Utility Class - -

    Initializing the Utility Class

    + +

    Initializing the Utility Class

    Important:  In order to initialize the Utility class, your database driver must already be running, since the utilities class relies on it.

    @@ -91,36 +89,9 @@ already be running, since the utilities class relies on it.

    $this->dbutil->some_function() - - - -

    $this->dbutil->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->dbutil->create_database('my_db'))
    -{
    -    echo 'Database created!';
    -}     - - - - -

    $this->dbutil->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->dbutil->drop_database('my_db'))
    -{
    -    echo 'Database deleted!';
    -}     - - - -

    $this->dbutil->list_databases()

    +

    $this->dbutil->list_databases()

    Returns an array of database names:

    - $dbs = $this->dbutil->list_databases();

    @@ -128,10 +99,7 @@ foreach($dbs as $db)
    {
        echo $db;
    }     - - - -

    $this->dbutil->optimize_table('table_name');

    +

    $this->dbutil->optimize_table('table_name');

    Note:  This features is only available for MySQL/MySQLi databases.

    @@ -148,8 +116,7 @@ if ($this->dbutil->optimize_table('table_name'))

    Note: Not all database platforms support table optimization.

    - -

    $this->dbutil->repair_table('table_name');

    +

    $this->dbutil->repair_table('table_name');

    Note:  This features is only available for MySQL/MySQLi databases.

    @@ -166,8 +133,7 @@ if ($this->dbutil->repair_table('table_name'))

    Note: Not all database platforms support table repairs.

    - -

    $this->dbutil->optimize_database();

    +

    $this->dbutil->optimize_database();

    Note:  This features is only available for MySQL/MySQLi databases.

    @@ -185,8 +151,7 @@ if ($result !== FALSE)

    Note: Not all database platforms support table optimization.

    - -

    $this->dbutil->csv_from_result($db_result)

    +

    $this->dbutil->csv_from_result($db_result)

    Permits you to generate a CSV file from a query result. The first parameter of the function must contain the result object from your query. Example:

    @@ -213,8 +178,7 @@ echo $this->dbutil->csv_from_result($query, $delimiter, $newline); If you need to write the file use the File Helper.

    - -

    $this->dbutil->xml_from_result($db_result)

    +

    $this->dbutil->xml_from_result($db_result)

    Permits you to generate an XML file from a query result. The first parameter expects a query result object, the second may contain an optional array of config parameters. Example:

    @@ -238,8 +202,7 @@ echo $this->dbutil->xml_from_result($query, $config); If you need to write the file use the File Helper.

    - -

    $this->dbutil->backup()

    +

    $this->dbutil->backup()

    Permits you to backup your full database or individual tables. The backup data can be compressed in either Zip or Gzip format.

    @@ -320,12 +283,11 @@ $this->dbutil->backup($prefs); -- cgit v1.2.3-24-g4f1b