We've built a single-tenant webapp in PHP and as it stands each hosted 'site' has its own database.
However we need to be able to run a script to create their database, assign a mysql user, and insert the default sql.
Now, we've got the database and user stuff working fine, its just the importing of the SQL. From what I can tell, PHP quite simply sucks when it comes to handling sql files, so storing a 'template' sql file seems to be out of the question.
My next thought was maybe storing a live database which is then cloned, giving the site its pre-populated database, however I cant see how this would work.
At the same time, I'd rather not 'hard code' the table creations into PHP as if we perform any updates we want to be able to update the SQL direct from our backend control panel.
Surely there has to be a logical way of handling creations and updates on a multi-tenant SaaS?