SQL import

[eluser]Unknown[/eluser]
I use dbutil to save out portions of my MySQL database into plain text SQL files, which works great.
However, I can't seem to figure out how to read the SQL files back in. Right now, I use a system call along the lines of "mysql < myfile.sql", which isn't exactly elegant.

I'm thinking I might have to do something like read the SQL file in, then pipe it line by line to $this->db, but I don't know how. What's the right way to do this?

[eluser]flaky[/eluser]
you could do it like this

Code:

//read the file
$file = $this->load->file('filepath/filename.txt', true);

//explode it in an array
$file_array = explode(';', $file);

//execute the exploded text content
foreach($file_array as $query)
    $this->db->query($query);

[eluser]Unknown[/eluser]
Just a followup: this works amazingly. It also happens to break if foreign key constraints are around, because the import goes in whatever order it pleases. The solution to that is to disable foreign key checks before your code, then re-enable after:

Code:

$this->db->query("SET FOREIGN_KEY_CHECKS = 0");
/* your import code here */
$this->db->query("SET FOREIGN_KEY_CHECKS = 1");

Hope this helps someone else =)

[eluser]SeeleyBoothe[/eluser]
Ok I am giving this a try. I keep getting a query was empty error.

I use dbutil to back up the table. and it looks like this:

Code:

#
# TABLE STRUCTURE FOR: actions
#

DROP TABLE IF EXISTS actions;

CREATE TABLE `actions` (
  `aid` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Primary Key: Unique actions ID.',
  `type` varchar(32) NOT NULL DEFAULT '' COMMENT 'The object that that action acts on (node, user, comment, system or custom types.)',
  `callback` varchar(255) NOT NULL DEFAULT '' COMMENT 'The callback function that executes when the action runs.',
  `parameters` longblob NOT NULL COMMENT 'Parameters to be passed to the callback function.',
  `label` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Label of the action.',
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores action information.';

INSERT INTO actions (`aid`, `type`, `callback`, `parameters`, `label`) VALUES ('comment_publish_action', 'comment', 'comment_publish_action', '', 'Publish comment');
INSERT INTO actions (`aid`, `type`, `callback`, `parameters`, `label`) VALUES ('comment_save_action', 'comment', 'comment_save_action', '', 'Save comment');
INSERT INTO actions (`aid`, `type`, `callback`, `parameters`, `label`) VALUES ('comment_unpublish_action', 'comment', 'comment_unpublish_action', '', 'Unpublish comment');
INSERT INTO actions (`aid`, `type`, `callback`, `parameters`, `label`) VALUES ('node_make_sticky_action', 'node', 'node_make_sticky_action', '', 'Make content sticky');
INSERT INTO actions (`aid`, `type`, `callback`, `parameters`, `label`) VALUES ('node_make_unsticky_action', 'node', 'node_make_unsticky_action', '', 'Make content unsticky');
INSERT INTO actions (`aid`, `type`, `callback`, `parameters`, `label`) VALUES ('node_promote_action', 'node', 'node_promote_action', '', 'Promote content to front page');
INSERT INTO actions (`aid`, `type`, `callback`, `parameters`, `label`) VALUES ('node_publish_action', 'node', 'node_publish_action', '', 'Publish content');
INSERT INTO actions (`aid`, `type`, `callback`, `parameters`, `label`) VALUES ('node_save_action', 'node', 'node_save_action', '', 'Save content');
INSERT INTO actions (`aid`, `type`, `callback`, `parameters`, `label`) VALUES ('node_unpromote_action', 'node', 'node_unpromote_action', '', 'Remove content from front page');
INSERT INTO actions (`aid`, `type`, `callback`, `parameters`, `label`) VALUES ('node_unpublish_action', 'node', 'node_unpublish_action', '', 'Unpublish content');
INSERT INTO actions (`aid`, `type`, `callback`, `parameters`, `label`) VALUES ('system_block_ip_action', 'user', 'system_block_ip_action', '', 'Ban IP address of current user');
INSERT INTO actions (`aid`, `type`, `callback`, `parameters`, `label`) VALUES ('user_block_user_action', 'user', 'user_block_user_action', '', 'Block current user');

It must have something to do with the file format.

here is my code to import it:

Code:

function restoreAllTables($folder_name = null) {
  $folder_name = '2012-04-18-09-47am';
  $path = 'assets/backup_db/';
  
  $files = get_filenames($path .  $folder_name . '/');
  
  foreach ($files as $file)
  {
   $file_restore = $this->load->file($path . $folder_name . '/' . $file, true);
   $file_array = explode(';', $file_restore);
   foreach ($file_array as $query)
   {
    $this->db->query("SET FOREIGN_KEY_CHECKS = 0");
     $this->db->query($query);
     $this->db->query("SET FOREIGN_KEY_CHECKS = 1");
   }
  }
}

[eluser]InsiteFX[/eluser]

Code:

$path = base_url('assets/backup_db/');