PHP CDbConnection::createCommand方法代码示例

 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));
 }

 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 setUp()
 {
     if (!extension_loaded('pdo') || !extension_loaded('pdo_mysql')) {
         $this->markTestSkipped('PDO and MySQL extensions are required.');
     }
     $this->db = new CDbConnection('mysql:host=127.0.0.1;dbname=yii', 'test', 'test');
     $this->db->charset = 'UTF8';
     $this->db->enableParamLogging = true;
     try {
         $this->db->active = true;
     } catch (Exception $e) {
         $schemaFile = realpath(dirname(__FILE__) . '/data/mysql.sql');
         $this->markTestSkipped("Please read {$schemaFile} for details on setting up the test environment for MySQL test case.");
     }
     $tables = array('comments', 'post_category', 'posts', 'categories', 'profiles', 'users', 'items', 'orders', 'types');
     foreach ($tables as $table) {
         $this->db->createCommand("DROP TABLE IF EXISTS {$table} CASCADE")->execute();
     }
     $sqls = file_get_contents(dirname(__FILE__) . '/data/mysql.sql');
     foreach (explode(';', $sqls) as $sql) {
         if (trim($sql) !== '') {
             $this->db->createCommand($sql)->execute();
         }
     }
 }

 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");
 }

 /**
  * 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);
 }

 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]);
 }

    public function setUp()
    {
        if (self::DB_DSN_PREFIX == 'sqlsrv' && (!extension_loaded('pdo') || !extension_loaded('sqlsrv') || !extension_loaded('pdo_sqlsrv'))) {
            $this->markTestSkipped('PDO and SQLSRV extensions are required.');
        } else {
            if (self::DB_DSN_PREFIX != 'sqlsrv' && (!extension_loaded('pdo') || !extension_loaded('pdo_dblib'))) {
                $this->markTestSkipped('PDO and MSSQL extensions are required.');
            }
        }
        if (self::DB_DSN_PREFIX == 'sqlsrv') {
            $dsn = self::DB_DSN_PREFIX . ':Server=' . self::DB_HOST . ';Database=' . self::DB_NAME;
        } else {
            $dsn = self::DB_DSN_PREFIX . ':host=' . self::DB_HOST . ';dbname=' . self::DB_NAME;
        }
        $this->db = new CDbConnection($dsn, self::DB_USER, self::DB_PASS);
        if (self::DB_DSN_PREFIX == 'sqlsrv') {
            $this->db->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_SYSTEM);
        }
        try {
            $this->db->active = true;
        } catch (Exception $e) {
            $schemaFile = realpath(dirname(__FILE__) . '/../data/mssql.sql');
            $this->markTestSkipped("Please read {$schemaFile} for details on setting up the test environment for MSSQL test case.");
        }
        $tables = array('comments', 'post_category', 'posts', 'categories', 'profiles', 'users', 'items', 'orders', 'types');
        foreach ($tables as $table) {
            $sql = <<<EOD
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[{$table}]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[{$table}]
EOD;
            $this->db->createCommand($sql)->execute();
        }
        $rawSqls = file_get_contents(dirname(__FILE__) . '/../data/mssql.sql');
        // remove comments from SQL
        $sqls = '';
        foreach (array_filter(explode("\n", $rawSqls)) as $line) {
            if (substr($line, 0, 2) == '--') {
                continue;
            }
            $sqls .= $line . "\n";
        }
        // run SQL
        foreach (explode('GO', $sqls) as $sql) {
            if (trim($sql) !== '') {
                $this->db->createCommand($sql)->execute();
            }
        }
    }

 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;
 }

 /**
  * Контроль предметов
  *
  * @params array $itemsIds
  *
  * @return array
  */
 public function getItemsControl(array $itemsIds)
 {
     if (!$itemsIds) {
         return array();
     }
     $res = AllItems::model()->findAllByAttributes(array('item_id' => $itemsIds));
     $itemNames = array();
     foreach ($res as $row) {
         $itemNames[$row->getPrimaryKey()] = $row;
     }
     unset($res);
     $res = $this->_db->createCommand()->select("Max(items.count) AS maxCountItems,Count(items.count) AS countItems,items.object_id,items.owner_id,items.item_id,items.count,items.enchant_level,items.loc,items.loc_data,characters.account_name,characters.char_name,characters.x,characters.y,characters.z,characters.obj_Id AS char_id,characters.sex,characters.karma,\r\n                characters.pvpkills,characters.pkkills,characters.clanid AS clan_id,characters.title,characters.accesslevel AS access_level,characters.online,characters.onlinetime,character_subclasses.class_id AS base_class,character_subclasses.level,character_subclasses.exp,\r\n                character_subclasses.sp,character_subclasses.curHp,character_subclasses.curMp,character_subclasses.curCp,character_subclasses.maxHp,character_subclasses.maxMp,character_subclasses.maxCp,clan_data.clan_level,clan_data.hasCastle,clan_data.hasFortress AS hasFort,\r\n                clan_data.crest AS clan_crest,clan_data.reputation_score,clan_subpledges.name AS clan_name")->leftJoin('characters', 'items.owner_id = characters.obj_Id')->leftJoin('character_subclasses', 'characters.obj_Id = character_subclasses.char_obj_id AND character_subclasses.isBase = 1')->leftJoin('clan_data', 'characters.clanid = clan_data.clan_id')->leftJoin('clan_subpledges', 'clan_data.clan_id = clan_subpledges.clan_id AND clan_subpledges.type = 0')->where(array('in', 'item_id', $itemsIds))->group('items.owner_id, items.item_id')->from('items')->queryAll();
     $characters = array();
     foreach ($res as $item) {
         if (!isset($characters[$item['item_id']]['maxTotalItems'])) {
             $characters[$item['item_id']]['maxTotalItems'] = 0;
         }
         $characters[$item['item_id']]['itemInfo'] = $itemNames[$item['item_id']];
         $characters[$item['item_id']]['characters'][] = $item;
         $characters[$item['item_id']]['maxTotalItems'] += $item['maxCountItems'];
         $characters[$item['item_id']]['totalItems'] = count($characters[$item['item_id']]['characters']);
     }
     foreach (array_diff_key($itemNames, $characters) as $item) {
         $characters[$item->item_id]['itemInfo'] = $item;
         $characters[$item->item_id]['characters'] = array();
         $characters[$item->item_id]['maxTotalItems'] = 0;
         $characters[$item->item_id]['totalItems'] = 0;
     }
     return $characters;
 }

 /**
  * Function to install the LimeSurvey database from the command line
  * Call it like:
  *    php index.php installer cmd_install_db
  * from your command line
  * The function assumes that /config/database.php is already configured and the database controller
  * is added in autoload.php in the libraries array
  *
  */
 function cmd_install_db()
 {
     if (php_sapi_name() != 'cli') {
         die('This function can only be run from the command line');
     }
     $sDbType = $this->db->dbdriver;
     if (!in_array($sDbType, InstallerConfigForm::supported_db_types)) {
         throw new Exception(sprintf('Unkown database type "%s".', $sDbType));
     }
     $aDbConfig = array('sDatabaseName' => $this->db->database, 'sDatabaseLocation' => $this->db->hostname, 'sDatabaseUser' => $this->db->username, 'sDatabasePwd' => $this->db->password, 'sDatabaseType' => $sDbType, 'sDatabasePort' => $this->db->port);
     $sDbPrefix = $this->db->dbprefix;
     self::_dbConnect($aDbConfig);
     $aErrors = self::_setup_tables(Yii::app()->getConfig('rootdir') . '/installer/sql/create-' . $sFileName . '.sql', $aDbConfig, $sDbPrefix);
     foreach ($aErrors as $sError) {
         echo $sError . PHP_EOL;
     }
     $sPasswordHash = hash('sha256', Yii::app()->getConfig('defaultpass'));
     try {
         $this->connection->createCommand()->insert("{{settings_global}}", array('stg_name' => 'SessionName', 'stg_value' => 'ls' . self::_getRandomID() . self::_getRandomID() . self::_getRandomID()));
         $this->connection->createCommand()->insert('{{users}}', array('users_name' => Yii::app()->getConfig('defaultuser'), 'password' => $sPasswordHash, 'full_name' => Yii::app()->getConfig('siteadminname'), 'parent_id' => 0, 'lang' => 'auto', 'email' => Yii::app()->getConfig('siteadminemail'), 'create_survey' => 1, 'participant_panel' => 1, 'create_user' => 1, 'delete_user' => 1, 'superadmin' => 1, 'configurator' => 1, 'manage_template' => 1, 'manage_label' => 1));
     } catch (Exception $e) {
         $aErrors[] = $e;
     }
     if (count($aErrors) > 0) {
         echo "There were errors during the installation.\n";
         foreach ($aErrors as $sError) {
             echo "Error: " . $sError . "\n";
         }
     } else {
         echo "Installation was successful.\n";
     }
 }

 public function actionResetPassword()
 {
     $id = urlGETParams('id');
     $base64Data = base64_decode($id);
     $arr = explode('<>', $base64Data);
     if (count($arr) < 3) {
         $this->redirect($this->createUrl('index'));
     }
     if ($arr[2] <= time()) {
         createMessage('URL hết hạn', 'danger');
         $this->redirect($this->createUrl('index'));
     }
     $query = "SELECT * FROM {{" . $this->_table . "}} WHERE id = :id AND email = :email AND is_advertiser = 1 LIMIT 1";
     $values = array(':email' => $arr[1], ':id' => $arr[0]);
     $row = $this->db->createCommand($query)->bindValues($values)->queryRow();
     if (empty($row)) {
         $this->redirect($this->createUrl('index'));
     }
     $data = array('user' => $row);
     $resetForm = new AdvertiseResetPasswordForm();
     if ($_SERVER['REQUEST_METHOD'] == 'POST') {
         $resetForm->setAttributes($_POST['AdvertiseResetPasswordForm']);
         if ($resetForm->validate()) {
             $query = "UPDATE {{" . $this->_table . "}} SET password = :password WHERE id = " . $arr[0];
             $values = array(':password' => md5($resetForm->password));
             $this->db->createCommand($query)->bindValues($values)->execute();
             Yii::app()->session['reset_passsword'] = $row['email'];
             $this->redirect($this->createUrl('success'));
         }
     }
     $data['form'] = $resetForm;
     $this->render('resetpassword', array('data' => $data));
 }

 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();
 }

 public function testResetSequence()
 {
     // we're assuming in this test that COciSchema::resetSequence() is not implemented
     // empty CDbSchema::resetSequence() being used
     $max = $this->db->createCommand('SELECT MAX("id") FROM "users"')->queryScalar();
     $this->db->createCommand('DELETE FROM "users"')->execute();
     $this->db->createCommand('INSERT INTO "users" ("username", "password", "email") VALUES (\'user4\', \'pass4\', \'email4\')')->execute();
     $max2 = $this->db->createCommand('SELECT MAX("id") FROM "users"')->queryScalar();
     $this->assertEquals($max + 1, $max2);
     $userTable = $this->db->schema->getTable('users');
     $this->db->createCommand('DELETE FROM "users"')->execute();
     $this->db->schema->resetSequence($userTable);
     $this->db->createCommand('INSERT INTO "users" ("username", "password", "email") VALUES (\'user4\', \'pass4\', \'email4\')')->execute();
     $max = $this->db->createCommand('SELECT MAX("id") FROM "users"')->queryScalar();
     $this->assertEquals(6, $max);
     $this->db->createCommand('INSERT INTO "users" ("username", "password", "email") VALUES (\'user4\', \'pass4\', \'email4\')')->execute();
     $max = $this->db->createCommand('SELECT MAX("id") FROM "users"')->queryScalar();
     $this->assertEquals(7, $max);
     $this->db->createCommand('DELETE FROM "users"')->execute();
     $this->db->schema->resetSequence($userTable, 10);
     $this->db->createCommand('INSERT INTO "users" ("username", "password", "email") VALUES (\'user4\', \'pass4\', \'email4\')')->execute();
     $max = $this->db->createCommand('SELECT MAX("id") FROM "users"')->queryScalar();
     $this->assertEquals(8, $max);
     $this->db->createCommand('INSERT INTO "users" ("username", "password", "email") VALUES (\'user4\', \'pass4\', \'email4\')')->execute();
     $max = $this->db->createCommand('SELECT MAX("id") FROM "users"')->queryScalar();
     $this->assertEquals(9, $max);
 }

 /**
  * 创建一个 command.
  *
  * @param string $sql
  * @return CDbCommand
  */
 public function createCommand($sql = null)
 {
     if (!$this->_forceUseMaster && $this->slaves && is_string($sql) && !$this->getCurrentTransaction() && $this->isReadOperation($sql) && ($slave = $this->getSlave())) {
         return $slave->createCommand($sql);
     }
     return parent::createCommand($sql);
 }

 /**
  * Executes an SQL file
  *
  * @param string $sFileName
  * @param string $sDatabasePrefix
  */
 function _executeSQLFile($sFileName, $sDatabasePrefix)
 {
     $aMessages = array();
     $sCommand = '';
     if (!is_readable($sFileName)) {
         return false;
     } else {
         $aLines = file($sFileName);
     }
     foreach ($aLines as $sLine) {
         $sLine = rtrim($sLine);
         $iLineLength = strlen($sLine);
         if ($iLineLength && $sLine[0] != '#' && substr($sLine, 0, 2) != '--') {
             if (substr($sLine, $iLineLength - 1, 1) == ';') {
                 $line = substr($sLine, 0, $iLineLength - 1);
                 $sCommand .= $sLine;
                 $sCommand = str_replace('prefix_', $sDatabasePrefix, $sCommand);
                 // Table prefixes
                 try {
                     $this->connection->createCommand($sCommand)->execute();
                 } catch (Exception $e) {
                     $aMessages[] = "Executing: " . $sCommand . " failed! Reason: " . $e;
                 }
                 $sCommand = '';
             } else {
                 $sCommand .= $sLine;
             }
         }
     }
     return $aMessages;
 }