CodeIgniter - Create multiple databases from master SQL file

Question

What would be the best way to take an .sql file that includes schema and table creation statements and use it to create new databases from within CodeIgniter? What I want to be able to do is use this .sql file as a blueprint for several databases with the same schema, but which may be created at any time.

I imagine I just need to be able to take this file, extract the contents and echo it out into a database query. Is there a better way to do it?

I also need to be able to inject a custom database name into the statements before submitting the query. How would I go about this? Just have a placeholder keyword and do a preg replace with the database name?

Just to ensure all databases are maintained synchronously, I thought perhaps this blueprint schema should be added to CodeIgniter as a module. That way if I need to alter the schema, I can upload a new version of the module with the updated .sql file and perform the necessary migrations across all the databases. Sound reasonable? If so, how would I go about this?

Craig · Accepted Answer · 2014-06-26 13:32:53Z

1

I have done this (run a .sql file) before, and I used this;

$sql = read_file('path/to/file.sql');

$final = '';
foreach(explode("\n", $sql) as $line)
{
      if ( isset($line[0]) && $line[0] != '#' )
      {
                $final .= $line . "\n";
      }
}

foreach (explode(";\n", final) as $sql)
{
      if ($sql)
      {
                $this->db->query($sql);
      }
}

answered Jun 26, 2014 at 13:32

Craig

1,8211 gold badge11 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bobe Over a year ago

Looks good as far as reading the file goes. I guess if I need to inject the database name I can just do a str_replace on each line, right? What about database alterations? I imagine I'd have to just run the statements that alter the tables and then upload a new master .sql file for subsequent databases?

Craig Over a year ago

Maybe the CI Migration Class could help you? ellislab.com/codeigniter/user-guide/libraries/migration.html

Bobe Over a year ago

Yeah migrations would work for that I suppose, I'm just always trying to reduce risk by not having to maintain two things (migrations and the updated master .sql file). I try to use the CI classes as much as possible, but for the database creation the DB Forge class just seemed a bit limited (from what I could see) and it's much more convenient to just use the .sql exported from Workbench.

Collectives™ on Stack Overflow

CodeIgniter - Create multiple databases from master SQL file

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related