Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
2  
If each tenant has its own database, it's not really a "multi-tenant database" as your title suggests, right? –  FrustratedWithFormsDesigner Feb 7 '13 at 20:09
    
ah crap sorry. Working on too many things at once and getting muddled up. Correct, its single tenancy. –  Rick Feb 7 '13 at 20:20
    
I'm curious how 'php sucks at handling sql files'... are they magically different from other text files? –  GrandmasterB Mar 12 '13 at 15:37
    
From the point of being able to simply give php an sql file to run - it cant. Its nothing to do with the file per-se, it's just PHP's lack of ability to split queries. So you cant get PHP to run an import without splitting the sql file into individual queries. –  Rick Mar 12 '13 at 18:41
    
Any reason you can't just use a single SQL file, have PHP split the file on the newline character using explode("\n", file_get_contents($sqlFilePath)), and then run each of the query statements one at a time by looping through the array of queries to execute? That would get around the limitation of only being able to execute a single statement per request in PHP. –  Kyle Lowry Mar 13 '13 at 0:24
show 1 more comment

3 Answers

You can execute an SQL file from the command line using MySQL's client. So you always have the option of doing:

mysql -u <etc> -p <etc> <database> < master_definition.sql

right from a command line or shell script. Or exec() from within PHP. Once the structure is created, then insert users from within a PHP script.

share|improve this answer
    
Much better solution than attempting to split a file of commands yourself. –  Jules Mar 14 '13 at 1:24
add comment

I think I would connect to the database you want to take this from, and query the information_schema database in you MySQL server that holds info on all databases. Then loop it, then use what ever is in it to make a new database by running querys. If you change some fields or table names you don't need to worry about anything beeing hardcoded, because all names are taken from the information_schema and not an existing database that you might change:

select * from SCHEMATA  where SCHEMA_NAME = 'dateabase_to_clone'

You can pass in the name with a variable, so when you start this cloning script nothing at all is hard coded. This gets you the specifics on the database, caracterset, colarion, path. You can now create an identical database dynamically.

select * from TABLES where TABLE_SCHEMA = 'database_to_clone' 

gives you a list off all the tables in this database. You loop it and get the rest and so on.

share|improve this answer
add comment
up vote 0 down vote accepted

We ended up going with a rather dirty method.

The sql file has a comment below each query like so:

-- split --

When the sql file is loaded up by the php script, it simply explodes the sql data, using the split. It then just loops through the exploded array data and runs each query.

I dont like it, but its the best option out of a bad bunch really.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.