static function getDbConnection()
 {
     if (!CommandCache::$db) {
         if (Yii::app()->params['sqlitecache_commands']) {
             $file = realpath(dirname(__FILE__) . '/../data/') . '/cmdcache.db';
             $init = false;
             if (@(!filesize($file))) {
                 $init = true;
             }
             $db = new CDbConnection('sqlite:' . $file);
             $db->emulatePrepare = true;
             $db->charset = 'utf8';
             $db->schemaCachingDuration = '3600';
             $db->active = true;
             if ($init) {
                 $cmd = $db->createCommand('create table if not exists `command_cache` (' . '`server_id` integer not null, `command` integer not null, `ts` integer not null,' . ' `data` text not null, primary key (`server_id`, `command`))');
                 $cmd->execute();
             }
             CommandCache::$db = $db;
         } else {
             CommandCache::$db = Yii::app()->db;
         }
     }
     return CommandCache::$db;
 }

 /**
  * Generates a string which is suitable for output into an sql query from
  * any data that may be outputable. This method protects strings against
  *
  * @param CDbConnection $connection
  * @param mixed $columnInsertValue
  * @return string
  */
 public function secureOutput(CDbConnection $connection, $columnInsertValue)
 {
     if ($columnInsertValue === null) {
         return 'NULL';
     }
     return $connection->quoteValue($columnInsertValue);
 }

 public function up()
 {
     try {
         $db = \Yii::app()->db;
         $dsn = 'mysql:host=127.0.0.1;port=3306;dbname=tiu_last';
         $connection = new CDbConnection($dsn, $db->username, $db->password);
         $connection->connectionStatus;
         echo "ALTER TABLE `specification` \n";
         $sql = "ALTER TABLE `specification`\n\t\t\t\t\tADD INDEX `id_product` (`id_product`);";
         $command = $connection->createCommand($sql)->execute();
         echo "ALTER TABLE `productToCat` \n";
         $sql = "ALTER TABLE `productToCat`\n\t\t\t\t\tADD INDEX `id_product` (`id_product`),\n\t\t\t\t\tADD INDEX `id_cat` (`id_cat`),\n\t\t\t\t\tADD INDEX `id_product_id_cat` (`id_product`, `id_cat`);";
         $command = $connection->createCommand($sql)->execute();
         echo "ALTER TABLE `image` \n";
         $sql = "ALTER TABLE `image`\n\t\t\t\t\tADD INDEX `id_item` (`id_item`);";
         $command = $connection->createCommand($sql)->execute();
         echo "ALTER TABLE `category_all` \n";
         $sql = "ALTER TABLE `category_all`\n\t\t\t\t\tADD INDEX `id_item` (`id_item`);";
         $command = $connection->createCommand($sql)->execute();
         $connection = null;
     } catch (CDbException $e) {
         echo $e->getMessage();
     }
     $this->execute("\n\t\t\tALTER TABLE `site_store_product`\n\t\t\t\tADD INDEX `user_id` (`user_id`);\n\t\t");
 }

 public function makeDb(CDbConnection $db)
 {
     $bb_account_sql = $db->createCommand('INSERT INTO bb_account
                                     ( account_name, vendor_id )
                                     VALUES
                                     ( :account_name, :vendor_id )');
     $cps_order_sql = $db->createCommand('INSERT INTO cps_order
                                     ( vendor_id, order_id, place_order_time, sign_contract_time)
                                     VALUES
                                     ( :vendor_id, :order_id, :place_order_time, :sign_contract_time)
                                     ');
     $cps_purchase_order_sql = $db->createCommand('INSERT INTO cps_purchase_order
                                     ( order_id, expense, vendor_id, expense_ratio, purchase_order_id)
                                     VALUES
                                     ( :order_id, :expense, :vendor_id, :expense_ratio, :purchase_order_id)');
     $order_id = 10000;
     for ($i = 500; $i < 600; $i++) {
         $bb_account_sql->execute(array(':account_name' => 'vendor' . $i, ':vendor_id' => $i));
         for ($j = 0; $j < 10; $j++) {
             $order_id++;
             $cps_order_sql->execute(array(':vendor_id' => $i, ':order_id' => $order_id, ':place_order_time' => date("Y-m-D H:i:s"), ':sign_contract_time' => date('Y-m-d')));
             //'0000-00-00' COMMENT '签约时间',
             for ($k = 0; $k < 20; $k++) {
                 $cps_purchase_order_sql->execute(array(':order_id' => $order_id, ':expense' => $k * 88, ':vendor_id' => $i, ':purchase_order_id' => $order_id * 20 + $k, ':expense_ratio' => 0.03 + $k / 2000));
             }
         }
     }
 }

 public function createUser()
 {
     $connection = new CDbConnection("mysql:host={$this->_DBHOST};dbname={$this->_DBNAME}", "{$this->_DBUSER}", "{$this->_DBPASS}");
     $sql = "INSERT INTO users (name,surname,login,password,email,telephone) VALUES('{$this->name}','{$this->surname}','{$this->login}','{$this->password}','{$this->email}','{$this->telephone}')";
     $command = $connection->createCommand($sql);
     $command->execute();
 }

 /**
  * Constructor.
  * @param CDbConnection $conn database connection.
  */
 public function __construct($conn)
 {
     $conn->setActive(true);
     $this->_connection = $conn;
     foreach ($conn->schemaCachingExclude as $name) {
         $this->_cacheExclude[$name] = true;
     }
 }

 public function testInitialized()
 {
     $db = new CDbConnection();
     $db->autoConnect = false;
     $this->assertFalse($db->isInitialized);
     $db->init();
     $this->assertTrue($db->isInitialized);
 }

 public function createDbConnection($db_must_exists = true)
 {
     $conn_string = 'mysql:host=' . $this->db_host . ';' . ($db_must_exists ? 'dbname=' . $this->db_name : '');
     $con = new CDbConnection($conn_string, $this->db_login, $this->db_pass);
     $con->initSQLs = array("SET NAMES 'utf8' COLLATE 'utf8_general_ci';");
     $con->init();
     return $con;
 }

 public function install()
 {
     # Does the application need installing? Check if database exists, and can connect
     try {
         # Required fields
         $required = array("db-host", "db-name", "db-username", "db-password", "table-prefix");
         # Did all the required fields get passed in?
         if (count(array_intersect($required, array_keys($_REQUEST))) != count($required)) {
             throw new Exception("Not all required fields were submitted.");
         }
         # Verify the required unempty fields
         foreach ($required as $field) {
             # Skip the fields that can be empty
             if ($field == "table-prefix" or $field == "db-password") {
                 continue;
             }
             # Check if empty, throw error if they are.
             if (empty($_REQUEST[$field])) {
                 throw new Exception("Field <i>" . lookupfieldname($field) . "</i> cannot be empty.");
             }
         }
         # Try connecting to the database with the passed in credentials
         try {
             # Setup connection details
             $dsn = 'mysql:host=' . $_REQUEST["db-host"] . ';dbname=' . $_REQUEST["db-name"];
             $username = $_REQUEST["db-username"];
             $password = $_REQUEST["db-password"];
             $prefix = $_REQUEST["table-prefix"];
             # Make the connection
             $conn = new CDbConnection($dsn, $username, $password);
             $conn->active = true;
             $conn->setActive(true);
         } catch (Exception $e) {
             throw new Exception("Could not connect to database. Make sure you have created the database first. Details: " . $e->getMessage());
         }
         # Setup the database params for saving in the extended configuration
         $db_params = array('components' => array('db' => array('connectionString' => $dsn, 'emulatePrepare' => true, 'username' => $username, 'password' => $password, 'charset' => 'utf8', 'tablePrefix' => $prefix)), 'params' => array('LOCALAPP_SERVER' => $_SERVER["HTTP_HOST"]));
         # Make sure to only overwrite if explicitly asked to
         $config_ext = Yii::app()->basePath . "\\config\\main-ext.php";
         if (is_file($config_ext)) {
             throw new Exception("Database configuration already exists. Delete this configuration in order to install this application.");
         }
         # Open up the file and write the new configuration.
         $handle = fopen($config_ext, "w");
         fwrite($handle, "<?php return ");
         fwrite($handle, var_export($db_params, true));
         fwrite($handle, "; ?>");
         fclose($handle);
         # Make read-only
         chmod($config_ext, 060);
     } catch (Exception $e) {
         $this->set_error($e->getMessage());
         return false;
     }
     # If we made it to here, installation is a success!
     return true;
 }

 /**
  * gets the list of left and right boundaries for any given owner objects.
  *
  * @param CDbConnection $db
  * @param string        $tree_table
  * @param string        $obj_id
  *
  * @returns array of array(left, right)
  */
 protected function _treeLimits($db, $tree_table, $obj_id)
 {
     $query = 'SELECT ' . $this->leftAttribute . ',' . $this->rightAttribute . ' FROM ' . $tree_table . ' WHERE ' . $this->idAttribute . ' = ' . $db->quoteValue($obj_id);
     $res = $db->createCommand($query)->query();
     $result = array();
     foreach ($res as $r) {
         $result[] = array($r[$this->leftAttribute], $r[$this->rightAttribute]);
     }
     return $result;
 }

 public static function saveAdToRt($adID)
 {
     $ad = Ad::model()->findByPk($adID);
     $connection = new CDbConnection(Yii::app()->params['sphinx']['dsn'], Yii::app()->params['sphinx']['user'], Yii::app()->params['sphinx']['pass']);
     $connection->active = true;
     $sphinxIndexes = Yii::app()->params['sphinx']['indexes'];
     $rt = $sphinxIndexes['rt'][0];
     $sql = "INSERT INTO {$rt} (id, title, description, added)\n                VALUES (:id, :title, :description, :added)";
     $command = $connection->createCommand($sql);
     $command->execute(array(':id' => $ad->id, ':title' => $ad->title, ':description' => $ad->description, ':added' => time()));
 }

 /**
  * 
  * @param CDbConnection $db
  * @param string $key
  * @param string $query
  */
 public static function fetchWithKey($db, $key = '', $query)
 {
     $data = array();
     $list = $db->createCommand($query)->queryAll();
     if (!empty($list)) {
         foreach ($list as $item) {
             $data[$item[$key]] = $item;
         }
     }
     return $data;
 }

 /**
  * Creates the session DB table.
  * @param CDbConnection $db the database connection
  * @param string $tableName the name of the table to be created
  */
 protected function createSessionTable($db, $tableName)
 {
     $driver = $db->getDriverName();
     if ($driver === 'mysql') {
         $blob = 'LONGBLOB';
     } elseif ($driver === 'pgsql') {
         $blob = 'BYTEA';
     } else {
         $blob = 'BLOB';
     }
     $db->createCommand()->createTable($tableName, array('id' => 'CHAR(255) PRIMARY KEY', 'expire' => 'integer', 'user_id' => 'integer', 'data' => $blob));
 }

 /**
  * Search via sphinx (mysql client)
  */
 protected function sphinxSearch($phrase)
 {
     $connection = new CDbConnection(Yii::app()->params['sphinx']['dsn'], Yii::app()->params['sphinx']['user'], Yii::app()->params['sphinx']['pass']);
     $connection->active = true;
     $words = mb_split('[^\\w]+', $phrase);
     $words = array_filter($words);
     // unset empty elements
     $search = implode('|', $words);
     $sphinxIndexes = SphinxService::implodeIndexes();
     $sql = "SELECT * FROM {$sphinxIndexes} WHERE MATCH('{$search}') LIMIT 10000";
     $command = $connection->createCommand($sql);
     return $command->queryColumn();
 }

 public function testCreateDb()
 {
     $step1 = new Step1();
     $step1->setAttributes($this->data, false);
     $this->assertTrue($step1->createDb());
     $conn_string = 'mysql:host=' . $step1->db_host . ';dbname=' . $step1->db_name;
     $con = new CDbConnection($conn_string, $step1->db_login, $step1->db_pass);
     try {
         $con->init();
     } catch (Exception $e) {
         $this->assert('Db was not created');
     }
     $con->createCommand('DROP DATABASE IF EXISTS ' . $con->quoteTableName($this->data['db_name']))->execute();
 }

 public function actionIndex()
 {
     $fisrt_day_of_month = date("Y-m-01");
     $last_day_of_month = date("Y-m-30");
     $connection = new CDbConnection("mysql:host=localhost;dbname=shop", "mysql", "mysql");
     $sql = "SELECT COUNT(*),`productid` FROM `order`   WHERE `time` BETWEEN STR_TO_DATE('{$fisrt_day_of_month}', '%Y-%m-%d')  AND STR_TO_DATE('{$last_day_of_month}', '%Y-%m-%d') GROUP BY `productid`";
     $command = $connection->createCommand($sql);
     $order = $command->queryAll();
     $sql2 = "SELECT * FROM `product`";
     $command2 = $connection->createCommand($sql2);
     $products = $command2->queryAll();
     // BETWEEN "2008.11.01"  AND  "2009.10.12"
     $this->render('index', ['orders' => $order, 'products' => $products]);
 }

 /**
  * Creates the DB table for storing log messages.
  * @param CDbConnection $db the database connection
  * @param string $tableName the name of the table to be created
  */
 protected function createLogTable($db, $tableName)
 {
     $driver = $db->getDriverName();
     if ($driver === 'mysql') {
         $logID = 'id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY';
     } else {
         if ($driver === 'pgsql') {
             $logID = 'id SERIAL PRIMARY KEY';
         } else {
             $logID = 'id INTEGER NOT NULL PRIMARY KEY';
         }
     }
     $sql = "\r\nCREATE TABLE {$tableName}\r\n(\r\n\t{$logID},\r\n\tlevel VARCHAR(128),\r\n\tcategory VARCHAR(128),\r\n\tlogtime INTEGER,\r\n\tmessage TEXT\r\n)";
     $db->createCommand($sql)->execute();
 }

 public function checkUser()
 {
     if ($this->hasErrors('db_exp_sql_host') || $this->hasErrors('db_exp_sql_dbname') || $this->hasErrors('db_exp_sql_login') || $this->hasErrors('db_exp_sql_password') || $this->hasErrors('db_exp_sql_port')) {
         return false;
     }
     try {
         $connection = new CDbConnection('mysql:host=' . $this->db_exp_sql_host, $this->db_exp_sql_login, $this->db_exp_sql_password);
         $res = $connection->setActive(true);
         $sql = "CREATE DATABASE IF NOT EXISTS `{$this->db_exp_sql_dbname}` CHARACTER SET UTF8";
         $res = $connection->createCommand($sql)->query();
     } catch (CDbException $e) {
         $this->addError('db_exp_sql_login', $e->getMessage());
         return false;
     }
     return true;
 }

 public function actionIndex()
 {
     $data = array();
     if (!empty($_GET['dest'])) {
         $redirect = base64_decode($_GET['dest']);
     }
     if ($_SERVER['REQUEST_METHOD'] == 'POST') {
         if (!isset($_GET['dest'])) {
             $redirect = $this->createUrl('index/index');
         }
         $email = formPostParams('email', VARIABLE_STRING);
         $password = formPostParams('password', VARIABLE_STRING);
         $condition = array(':email' => $email, ':password' => md5($password));
         $query = "SELECT * FROM {{" . $this->_table . "}} WHERE email = :email AND password = :password";
         $user = $this->db->createCommand($query)->bindValues($condition)->queryRow();
         if (!empty($user)) {
             Yii::app()->session['user'] = $user;
             $this->redirect($redirect);
         } else {
             createMessage('Email hoặc mật khẩu không đúng', 'danger');
         }
     } else {
         $user = Yii::app()->session['user'];
         if (!empty($user)) {
             $this->redirect($redirect);
         }
     }
     $this->renderPartial('index', array('data' => $data));
 }

 /**
  * https://github.com/yiisoft/yii/issues/2449
  */
 public function testFetchKeysWithDbCommandSpecifiedAndPdoFetchObjEnabled()
 {
     $command1 = $this->db->createCommand()->select('*')->from('posts')->setFetchMode(PDO::FETCH_ASSOC);
     $dataProvider1 = new CSqlDataProvider($command1);
     $this->assertSame(array('1', '2', '3', '4', '5'), $dataProvider1->keys);
     $command2 = $this->db->createCommand()->select('*')->from('posts')->setFetchMode(PDO::FETCH_OBJ);
     $dataProvider2 = new CSqlDataProvider($command2);
     $this->assertSame(array('1', '2', '3', '4', '5'), $dataProvider2->keys);
 }