I am trying to do something with dynamic databases in Yii which I could not find in the Yii discuss posts. My requirement is as follows:
- Need to generate and dynamic databases on the fly.
=> These databases are for users to do whatever they want. However, every database is created with restricted permissions and users cannot go beyond their own database.
- Need to execute any random user query (or a set of user queries) on that database
=> Given that the users can write any query, I do not know whether the query is going to be a "select" query or a "non-select" query. affected". This is equivalent to "running a mysql script file"
Now, I have written a DatabaseManager class, which does "1" for me, but I am still facing issues in implementing "2". The key problem lies in the following things:
I do not know the query executed by the user.
I need to handle the query and revert back with appropriate result to the user (this is very important), i.e.
– for a select query, it should return the data requested by the user
– for a non-select query, it should return "no. of rows affected"
– for a composite query (which contains both select and non-select queries), it can return the "no. of rows affected"
- The user can change the delimiters and write complex stuff such as procedures etc., so I cannot do command level split using semicolon ";" and execute them one by one.
What I tried with Yii is following:
I created a transaction for every query (or bunch of queries that I get).
Not knowing what the query is, I always execute the "queryAll" on that. If it is a "select based query", then I hope to get the correct result from it. If it succeeds, then I commit the transaction
If it is a "non-select" query, I will get an exception here, and I will roll back the transaction. In this case, I will next create a new transaction and then call the "execute" command. Given that this is a non-sql or composite query, I expect the command to run. It should only throw an exception in case of a syntax error or in case of some constraint violation
In case "execute" also throws an exception, I roll back this transaction as well, and show the result to the user.
I was expecting my approach 2 to work in this case. However, I see that the transactions are not working properly in case of "create table command". Here, the "queryAll" command gives and exception, but still goes ahead and creates the user table. Now, when I try to run the same command again with "execute", it again gives the exception, because the table already exists. I am surprised that the transaction rollback did not make any difference.
Now, my above approach may be grossly incorrect, and I’ll be happy to hear some feedback on what is the right way to approach this problem. For now, here are some key questions that I have:
Is it possible to execute a bunch of random sql queries in Yii? More importantly, is it possible to break the queries into individual queries? (I know this is tough problem and a big topic of discussion in general, but asking the same question none the less)
Why is transaction rollback not working?
What are the holes in my approach and how can anyone screw up this system?
My apologies for such a long post. However, it was a little difficult to explain the problem in brief. I am attaching the source code for my DatabaseManager class with this post for a detailed reference.