0

I am writing a PHP application which needs to create new databases as data is added. I want a nice and simple way of specifying how the created databases should look like (i.e. what tables they contain, what indices these tables should contain, ...) and this is currently an EER Diagram in MySql Workbench which I then forward engineer onto the server. I do not consider lots of CREATE-statements in PHP code "nice and simple". Triggered by some event in the PHP application I need to do something which creates a new database with a specified name which is a copy of the template database.

This is sorta a duplicate of this question:
MySQL copy/duplicate database
But the answers to that question are not suitable for me.
I do not want to use mysqldump because I cannot take the security risk of executing shell code on the server, which may be vulnerable to code injection as the name of the created database has to be inserted. Also this.
I also do not want to break the database apart with SHOW-statements and manually execute CREATE-statements to stitch together a new database because that becomes cumbersome fast and it may not capture all the aspects of the template database and therefore introduce hard to find bugs. I am also not sure how well this method handles foreign keys when a referencing table is created before the referenced one.

Ideally I'd like to use a simple SQL statement like CREATE DATABASE ? LIKE TemplateDatabase (where ? represents the database name in some form of prepared statement). Unfortunately though it does not seem like that functionality exists in MySQL.
Apparently PostgreSQL can do something like that just fine but I don't want to migrate just because of a single feature.

How can this be accomplished while fulfilling the aforementioned constraints?

Niko O
  • 406
  • 3
  • 15

1 Answers1

0

If your project are new, by correctly well manage migrations, you can separated which part is for table schema, which is for table's data. Copy database is just re-run migration.

If your project are existed. You can create sql files, and write php scripts to run it manually.

ThangTD
  • 1,586
  • 17
  • 16
  • Do I understand you right that I should use the migration feature of MySQL? How do I trigger a migration from PHP and how exactly should the migration be configured to achieve the desired effect? I would like to avoid using SQL files because of reasons stated in my question. – Niko O Mar 15 '16 at 22:46
  • Fulling this problem should be a long story. But you can do with patient. Create a custom console controller, triggering the migrations might happen by calling ` shell_exec('your command to run') `, configuring migration as your desired effect should be control within that console controller. – ThangTD Mar 16 '16 at 04:35