From 79494dde1c53b671f279e5c5fab2ae03a9ff0353 Mon Sep 17 00:00:00 2001 From: Andrew Podner Date: Wed, 19 Dec 2012 14:15:41 -0500 Subject: Resolves issue #2081 : provides an option to include statements to disable and re-enable foreign key checks in a MySQL database backup output statement. --- system/database/DB_utility.php | 3 ++- system/database/drivers/mysql/mysql_utility.php | 13 +++++++++++ system/database/drivers/mysqli/mysqli_utility.php | 13 +++++++++++ user_guide_src/source/database/utilities.rst | 27 ++++++++++++----------- 4 files changed, 42 insertions(+), 14 deletions(-) diff --git a/system/database/DB_utility.php b/system/database/DB_utility.php index c4140aef3..3ed75967e 100644 --- a/system/database/DB_utility.php +++ b/system/database/DB_utility.php @@ -326,7 +326,8 @@ abstract class CI_DB_utility { 'format' => 'gzip', // gzip, zip, txt 'add_drop' => TRUE, 'add_insert' => TRUE, - 'newline' => "\n" + 'newline' => "\n", + 'fk_checks' => TRUE ); // Did the user submit any preferences? If so set them.... diff --git a/system/database/drivers/mysql/mysql_utility.php b/system/database/drivers/mysql/mysql_utility.php index 8aa051755..e2de82d0b 100644 --- a/system/database/drivers/mysql/mysql_utility.php +++ b/system/database/drivers/mysql/mysql_utility.php @@ -76,6 +76,13 @@ class CI_DB_mysql_utility extends CI_DB_utility { // Build the output $output = ''; + + // Do we need to include a statement to disable FK checks? + if ($fk_checks === FALSE) + { + $output .= "SET foreign_key_checks = 0;".$newline; + } + foreach ( (array) $tables as $table) { // Is the table in the "ignore" list? @@ -181,6 +188,12 @@ class CI_DB_mysql_utility extends CI_DB_utility { $output .= $newline.$newline; } + // Do we need to include a statement to re-enable FK checks? + if ($fk_checks === FALSE) + { + $output .= "SET foreign_key_checks = 1;".$newline; + } + return $output; } diff --git a/system/database/drivers/mysqli/mysqli_utility.php b/system/database/drivers/mysqli/mysqli_utility.php index 345691e84..ff4949046 100644 --- a/system/database/drivers/mysqli/mysqli_utility.php +++ b/system/database/drivers/mysqli/mysqli_utility.php @@ -76,6 +76,13 @@ class CI_DB_mysqli_utility extends CI_DB_utility { // Build the output $output = ''; + + // Do we need to include a statement to disable FK checks? + if ($fk_checks === FALSE) + { + $output .= "SET foreign_key_checks = 0;".$newline; + } + foreach ( (array) $tables as $table) { // Is the table in the "ignore" list? @@ -181,6 +188,12 @@ class CI_DB_mysqli_utility extends CI_DB_utility { $output .= $newline.$newline; } + // Do we need to include a statement to re-enable FK checks? + if ($fk_checks === FALSE) + { + $output .= "SET foreign_key_checks = 1;".$newline; + } + return $output; } diff --git a/user_guide_src/source/database/utilities.rst b/user_guide_src/source/database/utilities.rst index 06ecb2da1..1631066fe 100644 --- a/user_guide_src/source/database/utilities.rst +++ b/user_guide_src/source/database/utilities.rst @@ -45,7 +45,7 @@ $this->dbutil->list_databases(); Returns an array of database names:: $dbs = $this->dbutil->list_databases(); - + foreach ($dbs as $db) { echo $db; @@ -102,7 +102,7 @@ FALSE on failure. :: $result = $this->dbutil->optimize_database(); - + if ($result !== FALSE) { print_r($result); @@ -119,9 +119,9 @@ parameter of the method 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, third, and fourth parameters allow you to set the delimiter @@ -147,16 +147,16 @@ 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" + 'tab' => "\t" ); - + echo $this->dbutil->xml_from_result($query, $config); .. important:: This method will NOT write the XML file for you. It @@ -172,7 +172,7 @@ backup data can be compressed in either Zip or Gzip format. .. note:: This feature is only available for MySQL and Interbase/Firebird databases. .. note:: For Interbase/Firebird databases, the backup file name is the only parameter. - + Eg. $this->dbutil->backup('db_backup_filename'); .. note:: Due to the limited execution time and memory available to PHP, @@ -188,14 +188,14 @@ 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); @@ -215,7 +215,7 @@ parameter of the ``backup()`` method. Example:: '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 @@ -233,4 +233,5 @@ Preference Default Value Options Description **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. +**fk_checks** TRUE TRUE/FALSE Whether output should keep foreign key checks enabled. =============== ======================= ======================= ======================================================================== \ No newline at end of file -- cgit v1.2.3-24-g4f1b From 4851217a1330580ebf73f0a6fb79ad451b73759a Mon Sep 17 00:00:00 2001 From: Andrew Podner Date: Thu, 20 Dec 2012 07:56:19 -0500 Subject: fixes #2081 : change parameter/variable name to 'foreign_key_checks', update change log Signed-off-by:Andrew Podner --- system/database/DB_utility.php | 16 ++++++------- system/database/drivers/mysql/mysql_utility.php | 8 +++---- system/database/drivers/mysqli/mysqli_utility.php | 8 +++---- user_guide_src/source/changelog.rst | 3 ++- user_guide_src/source/database/utilities.rst | 28 +++++++++++------------ 5 files changed, 32 insertions(+), 31 deletions(-) diff --git a/system/database/DB_utility.php b/system/database/DB_utility.php index 3ed75967e..aff3cf8c2 100644 --- a/system/database/DB_utility.php +++ b/system/database/DB_utility.php @@ -320,14 +320,14 @@ abstract class CI_DB_utility { // Set up our default preferences $prefs = array( - 'tables' => array(), - 'ignore' => array(), - 'filename' => '', - 'format' => 'gzip', // gzip, zip, txt - 'add_drop' => TRUE, - 'add_insert' => TRUE, - 'newline' => "\n", - 'fk_checks' => TRUE + 'tables' => array(), + 'ignore' => array(), + 'filename' => '', + 'format' => 'gzip', // gzip, zip, txt + 'add_drop' => TRUE, + 'add_insert' => TRUE, + 'newline' => "\n", + 'foreign_key_checks' => TRUE ); // Did the user submit any preferences? If so set them.... diff --git a/system/database/drivers/mysql/mysql_utility.php b/system/database/drivers/mysql/mysql_utility.php index e2de82d0b..f118033dc 100644 --- a/system/database/drivers/mysql/mysql_utility.php +++ b/system/database/drivers/mysql/mysql_utility.php @@ -77,8 +77,8 @@ class CI_DB_mysql_utility extends CI_DB_utility { // Build the output $output = ''; - // Do we need to include a statement to disable FK checks? - if ($fk_checks === FALSE) + // Do we need to include a statement to disable foreign key checks? + if ($foreign_key_checks === FALSE) { $output .= "SET foreign_key_checks = 0;".$newline; } @@ -188,8 +188,8 @@ class CI_DB_mysql_utility extends CI_DB_utility { $output .= $newline.$newline; } - // Do we need to include a statement to re-enable FK checks? - if ($fk_checks === FALSE) + // Do we need to include a statement to re-enable foreign key checks? + if ($foreign_key_checks === FALSE) { $output .= "SET foreign_key_checks = 1;".$newline; } diff --git a/system/database/drivers/mysqli/mysqli_utility.php b/system/database/drivers/mysqli/mysqli_utility.php index ff4949046..2edf38cf5 100644 --- a/system/database/drivers/mysqli/mysqli_utility.php +++ b/system/database/drivers/mysqli/mysqli_utility.php @@ -77,8 +77,8 @@ class CI_DB_mysqli_utility extends CI_DB_utility { // Build the output $output = ''; - // Do we need to include a statement to disable FK checks? - if ($fk_checks === FALSE) + // Do we need to include a statement to disable foreign key checks? + if ($foreign_key_checks === FALSE) { $output .= "SET foreign_key_checks = 0;".$newline; } @@ -188,8 +188,8 @@ class CI_DB_mysqli_utility extends CI_DB_utility { $output .= $newline.$newline; } - // Do we need to include a statement to re-enable FK checks? - if ($fk_checks === FALSE) + // Do we need to include a statement to re-enable foreign key checks? + if ($foreign_key_checks === FALSE) { $output .= "SET foreign_key_checks = 1;".$newline; } diff --git a/user_guide_src/source/changelog.rst b/user_guide_src/source/changelog.rst index a3d29056e..f3c3bfa50 100644 --- a/user_guide_src/source/changelog.rst +++ b/user_guide_src/source/changelog.rst @@ -121,6 +121,7 @@ Release Date: Not Released - Added support for SQLite3 database driver. - Added Interbase/Firebird database support via the *ibase* driver. - Added ODBC support for ``create_database()``, ``drop_database()`` and ``drop_table()`` in :doc:`Database Forge `. + - Added 'foreign_key_checks' parameter to MySQL/MySQLi backup, allowing statement to disable/re-enable foreign key checks to be inserted into the backup output - :doc:`Query Builder ` changes include: - Renamed the Active Record class to Query Builder to remove confusion with the Active Record design pattern. - Added the ability to insert objects with ``insert_batch()``. @@ -180,7 +181,7 @@ Release Date: Not Released - Deprecated ``add_column()``'s third method. *AFTER* clause should now be added to the field definition array instead. - Added support for usage of the *FIRST* clause in ``add_column()`` for MySQL and CUBRID. - Overall improved support for all of the drivers. - - :doc:`Database Utility ` chages include: + - :doc:`Database Utility ` changes include: - Added support for passing a custom database object to the loader. - Modified the class to no longer extend :doc:`Database Forge `, which has been a deprecated behavior for awhile. - Overall improved support for all of the drivers. diff --git a/user_guide_src/source/database/utilities.rst b/user_guide_src/source/database/utilities.rst index 1631066fe..bd40cdadd 100644 --- a/user_guide_src/source/database/utilities.rst +++ b/user_guide_src/source/database/utilities.rst @@ -221,17 +221,17 @@ parameter of the ``backup()`` method. Example:: 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. -**fk_checks** TRUE TRUE/FALSE Whether output should keep foreign key checks enabled. -=============== ======================= ======================= ======================================================================== \ No newline at end of file +======================= ======================= ======================= ======================================================================== +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. +**foreign_key_checks** TRUE TRUE/FALSE Whether output should keep foreign key checks enabled. +======================= ======================= ======================= ======================================================================== \ No newline at end of file -- cgit v1.2.3-24-g4f1b From bee50e7eebdf0f0aa3072958bb30e3235b3fc4a8 Mon Sep 17 00:00:00 2001 From: Andrew Podner Date: Thu, 20 Dec 2012 09:20:29 -0500 Subject: fixes #2081 : fix change log entry Signed-off-by:Andrew Podner --- user_guide_src/source/changelog.rst | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/user_guide_src/source/changelog.rst b/user_guide_src/source/changelog.rst index f3c3bfa50..cc9afc45c 100644 --- a/user_guide_src/source/changelog.rst +++ b/user_guide_src/source/changelog.rst @@ -121,7 +121,6 @@ Release Date: Not Released - Added support for SQLite3 database driver. - Added Interbase/Firebird database support via the *ibase* driver. - Added ODBC support for ``create_database()``, ``drop_database()`` and ``drop_table()`` in :doc:`Database Forge `. - - Added 'foreign_key_checks' parameter to MySQL/MySQLi backup, allowing statement to disable/re-enable foreign key checks to be inserted into the backup output - :doc:`Query Builder ` changes include: - Renamed the Active Record class to Query Builder to remove confusion with the Active Record design pattern. - Added the ability to insert objects with ``insert_batch()``. @@ -185,6 +184,7 @@ Release Date: Not Released - Added support for passing a custom database object to the loader. - Modified the class to no longer extend :doc:`Database Forge `, which has been a deprecated behavior for awhile. - Overall improved support for all of the drivers. + - Added 'foreign_key_checks' option to MySQL/MySQLi backup, allowing statement to disable/re-enable foreign key checks to be inserted into the backup output. - Libraries -- cgit v1.2.3-24-g4f1b