Database Utility Class
+ +The Database Utility Class contains functions that help you manage your database.
+ +Table of Contents
+ +-
+
- Initializing the Utility Class +
- Listing your Databases +
- Checking for a specific Database +
- Optimizing your Tables +
- Repairing your Databases +
- Optimizing your Database +
- CSV Files from a Database Result +
- XML Files from a Database Result +
- Backing up your Database +
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.
+ +Load the Utility Class as follows:
+ +$this->load->dbutil()
+
+Once initialized you will access the functions using the $this->dbutil object:
+ +$this->dbutil->some_function()
+
+$this->dbutil->list_databases()
+Returns an array of database names:
+ +
+$dbs = $this->dbutil->list_databases();
+
+foreach ($dbs as $db)
+{
+ echo $db;
+}
+
+
+$this->dbutil->database_exists();
+ +Sometimes it's helpful to know whether a particular database exists. +Returns a boolean TRUE/FALSE. Usage example:
+ +
+if ($this->dbutil->database_exists('database_name'))
+{
+ // some code...
+}
+
+
+Note: Replace database_name with the name of the table you are looking for. This function is case sensitive.
+ + + +$this->dbutil->optimize_table('table_name');
+ +Note: This features is only available for MySQL/MySQLi databases.
+ + +Permits you to optimize a table using the table name specified in the first parameter. Returns TRUE/FALSE based on success or failure:
+ +
+if ($this->dbutil->optimize_table('table_name'))
+{
+ echo 'Success!';
+}
+
+
+Note: Not all database platforms support table optimization.
+ + +$this->dbutil->repair_table('table_name');
+ +Note: This features is only available for MySQL/MySQLi databases.
+ + +Permits you to repair a table using the table name specified in the first parameter. Returns TRUE/FALSE based on success or failure:
+ +
+if ($this->dbutil->repair_table('table_name'))
+{
+ echo 'Success!';
+}
+
+
+Note: Not all database platforms support table repairs.
+ + +$this->dbutil->optimize_database();
+ +Note: This features is only available for MySQL/MySQLi databases.
+ +Permits you to optimize the database your DB class is currently connected to. Returns an array containing the DB status messages or FALSE on failure.
+ +
+$result = $this->dbutil->optimize_database();
+
+if ($result !== FALSE)
+{
+ print_r($result);
+}
+
+
+Note: Not all database platforms support table optimization.
+ + +$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:
+ +
+$this->load->dbutil();
+
+$query = $this->db->query("SELECT * FROM mytable");
+
+echo $this->dbutil->csv_from_result($query);
+
+
+The second and third parameters allows you to +set the delimiter and newline character. By default tabs are used as the delimiter and "\n" is used as a new line. Example:
+ +
+$delimiter = ",";
+$newline = "\r\n";
+
+echo $this->dbutil->csv_from_result($query, $delimiter, $newline);
+
+
+Important: This function will NOT write the CSV file for you. It simply creates the CSV layout. +If you need to write the file use the File Helper.
+ + +$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:
+ +
+$this->load->dbutil();
+
+$query = $this->db->query("SELECT * FROM mytable");
+
+$config = array (
+ 'root' => 'root',
+ 'element' => 'element',
+ 'newline' => "\n",
+ 'tab' => "\t"
+ );
+
+echo $this->dbutil->xml_from_result($query, $config);
+
+
+Important: This function will NOT write the XML file for you. It simply creates the XML layout. +If you need to write the file use the File Helper.
+ + +$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.
+ +Note: This features is only available for MySQL databases.
+ +Note: Due to the limited execution time and memory available to PHP, backing up very large +databases may not be possible. If your database is very large you might need to backup directly from your SQL server +via the command line, or have your server admin do it for you if you do not have root privileges.
+ +Usage Example
+ +
+// Load the DB utility class
+$this->load->dbutil();
+
+// Backup your entire database and assign it to a variable
+$backup =& $this->dbutil->backup();
+
+
+// Load the file helper and write the file to your server
+$this->load->helper('file');
+write_file('/path/to/mybackup.gz', $backup);
+
+
+// Load the download helper and send the file to your desktop
+$this->load->helper('download');
+force_download('mybackup.gz', $backup);
+
+
+Setting Backup Preferences
+ +Backup preferences are set by submitting an array of values to the first parameter of the backup function. Example:
+ +$prefs = array(
+ 'tables' => array('table1', 'table2'), // Array of tables to backup.
+ 'ignore' => array(), // List of tables to omit from the backup
+ 'format' => 'txt', // gzip, zip, txt
+ 'filename' => 'mybackup.sql', // File name - NEEDED ONLY WITH ZIP FILES
+ 'add_drop' => TRUE, // Whether to add DROP TABLE statements to backup file
+ 'add_insert' => TRUE, // Whether to add INSERT data to backup file
+ 'newline' => "\n" // Newline character used in backup file
+ );
+
+$this->dbutil->backup($prefs);
+
+
+
+Description of Backup Preferences
+ +Preference | +Default Value | +Options | +Description | +
---|---|---|---|
tables | empty array | None | An array of tables you want backed up. If left blank all tables will be exported. | +
ignore | empty array | None | An array of tables you want the backup routine to ignore. | +
format | gzip | gzip, zip, txt | The file format of the export file. | +
filename | the current date/time | None | The name of the backed-up file. The name is needed only if you are using zip compression. | +
add_drop | TRUE | TRUE/FALSE | Whether to include DROP TABLE statements in your SQL export file. | +
add_insert | TRUE | TRUE/FALSE | Whether to include INSERT statements in your SQL export file. | +
newline | "\n" | "\n", "\r", "\r\n" | Type of newline to use in your SQL export file. | + +