I am trying to do something with dynamic databases. 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:

1. I do not know the query executed by the user.
2. 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"
3. 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:

1. I created a transaction for every query (or bunch of queries that I get).
2. 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
3. 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
4. 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:

1. 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 stackoverflow itself, but asking the same question none the less)
2. Why is transaction rollback not working?
3. 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.

```
<?php
class DatabaseManager
{
public $db_name;
public $username;
public $password;
const HOST = "localhost"; 
const ROOT = "dummy_root";
const ROOT_PASSWORD = "dummy_root_password";
const DB_PASSWORD_SALT = 'dummy_password_salt';

public function getDbComponent($db_name) {
    //if the component already exists then create it
    if(Yii::app()->hasComponent($db_name)) {
        $component = Yii::app()->getComponent($db_name);
        return $component;
    }

    //if the db component doesn't exist, then create it and return the
    //new component
    try {
        $this->db_name = $db_name;
        $this->username = "dummy_user";
        $this->password = "dummy_password";

        $component = $this->createDbComponent();
        Yii::app()->setComponent($db_name, $component);
    } catch (PDOException $e) {
        die("DB ERROR: ". $e->getMessage());
    }
    return $component;
}

private function createDbComponent() {
    $pdo = new PDO("mysql:host=".self::HOST, self::ROOT, self::ROOT_PASSWORD, array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,));

    $pdo->exec("CREATE DATABASE IF NOT EXISTS `$this->db_name`;
                GRANT ALL PRIVILEGES ON ".$this->db_name.".* TO '".$this->username."'@'localhost' IDENTIFIED BY '".$this->password."';
                FLUSH PRIVILEGES;")
        or die(print_r($pdo->errorInfo(), true));

    $component = Yii::createComponent(array(
                'class'=>'CDbConnection',
                'connectionString'=>'mysql:host=localhost;dbname='.$this->db_name,
                'emulatePrepare' => true,
                'password'=> $this->password, 
                'username'=> $this->username,
                'charset' => 'utf8',
                'enableParamLogging' => true,
                )
            ); 
    return $component;
}

private function formatErrorMessage($msg) {
    $check_for_prefix = "CDbCommand failed to execute the SQL statement:";
    $result = str_replace($check_for_prefix, "", $msg);
    return $result;
}

private function executeSelectQuery($query, $db_name) {
    //Get the db component on which the query should be executed
    $db = $this->getDbComponent($db_name);
    $command = $db->createCommand($query); 

    $transaction = $db->beginTransaction();
    try {
        $command = $db->createCommand($query); 
        $output = $command->queryAll();
        $output = $command->queryAll();
        $transaction->commit();
    } catch (exception $e) {
        $transaction->rollback();
        //throw back this exception
        //as it should be handled by the higher level code
        throw new Exception($e->getMessage());
    }
    return $output;
}

private function executeNonSelectQuery($query, $db_name) {
    //Get the db component on which the query should be executed
    $db = $this->getDbComponent($db_name);
    $transaction = $db->beginTransaction();
    try {
        $command = $db->createCommand($query); 
        $command->prepare();
        $output = $command->execute();
        $transaction->commit();
    } catch (exception $e) {
        $transaction->rollback();
        //throw back this exception
        //as it should be handled by the higher level code
        throw new Exception($e->getMessage());
    }
    return $output;
}

public function runQuery($code, $db_name) {
    try {
        //we can have two kind of queries -- select or non-select
        //since we don't know the type of query, we should by default try to make a select query
        //if this gives an exception, then there is a possibility that this is a non-select query
        //so now we try doing an "execute" on this
        //if that goes fine, it means that the query was a non-sql query
        //else we show the exception to user
        $db = $this->getDbComponent($db_name);
        try {
            $output = $this->executeSelectQuery($code, $db_name);
            $result['status'] = "success";
            $result['output'] = $output;
        } catch (exception $e) {
            try {
                $output = $this->executeNonSelectQuery($code, $db_name);
                $result['status'] = "success";
                $result['output'] = "Query OK, ".$output." row(s) affected";
            } catch (exception $e) {
                $error = $this->formatErrorMessage($e->getMessage());
                $result['status'] = "failure";
                $result['error'] = $error;
            }
        }
    } catch (exception $e) {
        $result = $e->getMessage();
    }

    return $result;
}

//resets the db
public function resetDbComponent($db_name) {
    //in case of reset simply delete the database
    $result = $this->deleteDbComponent($db_name);
    if(!$this->getDbComponent($db_name)) {
        throw new Exception("Could not create database -- ".$db_name);
    } else {
        $result['status'] = "success";
        $result['output'] = "Database reset to inital state";
    }
}

public function deleteDbComponent($db_name) {
    $pdo = new PDO("mysql:host=".self::HOST, self::ROOT, self::ROOT_PASSWORD);
    try {
        $pdo->exec("DROP DATABASE IF EXISTS `$db_name`;");
        Yii::app()->setComponent($db_name, null);
        $result['status'] = 'failure';
        $result['output'] = "Database ".$db_name." deleted";
    } catch (exception $e) {
        $result['status'] = 'failure';
        $result['output'] = "Database ".$db_name." could not be deleted";
    }
}
}

```

Regards,
Kapil