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;
}
/**
* 创建一个 command.
*
* @param string $sql
* @return CDbCommand
*/
public function createCommand($sql = null)
{
if (is_string($sql) && $this->isNeedReadCheck && !$this->isReadOperation($sql)) {
throw new CDbException('Slave database is readonly! SQL:' . $sql);
}
return parent::createCommand($sql);
}
protected function getListVideoTags($list_tags_id)
{
$data = array();
if (empty($list_tags_id)) {
return $data;
}
$list_tags_id = array_unique(array_filter($list_tags_id));
if (empty($list_tags_id)) {
return $data;
}
$query = "SELECT id, name, alias FROM tbl_tags_youtube WHERE id IN (" . implode(',', $list_tags_id) . ")";
$result = $this->db->createCommand($query)->queryAll();
foreach ($result as $item) {
$data[$item['id']] = $item;
}
return $data;
}
/**
* Creates a CDbCommand object for excuting sql statement.
* It will detect the sql statement's behavior.
* While the sql is a simple read operation.
* It will use a slave database connection to contruct a CDbCommand object.
* Default it use current connection(master database).
*
* @override
* @param string $sql
* @return CDbCommand
*/
public function createCommand($query = null)
{
if ($this->enableSlave && !$this->getCurrentTransaction() && self::isReadOperation($query)) {
return $this->getSlave()->createCommand($query);
} else {
return parent::createCommand($query);
}
}
public function testFetchModeClass()
{
$sql = 'SELECT * FROM posts';
$command = $this->_connection->createCommand($sql);
$command->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, 'TestClass');
$result = $command->queryRow();
$this->assertTrue($result instanceof TestClass);
}
private function runDbScript()
{
try {
$connection = new CDbConnection("{$this->dbDriver}:host={$this->dbHost};dbname={$this->dbName}", $this->dbUser, $this->dbPassword);
$connection->active = true;
$sqlFile = Yii::getPathOfAlias('webroot.protected.data') . '/schema.mysql.sql';
$sql = file_get_contents($sqlFile);
$connection->beginTransaction();
$connection->createCommand("SET CHARACTER SET utf8")->execute();
$command = $connection->createCommand($sql);
$command->execute(array(':username' => $this->username, ':salt' => $salt = substr(md5(uniqid(rand(), true)), 0, 9), ':password' => sha1($salt . sha1($salt . sha1($this->password))), ':email' => $this->email));
$connection->getCurrentTransaction()->commit();
$connection->active = false;
return true;
} catch (Exception $ex) {
return false;
}
}
/**
* 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;
}
/**
* https://github.com/yiisoft/yii/issues/2336
*/
public function testEmptyModel()
{
$post = new NullablePost2();
$post->insert();
$post = new NullablePost2();
$post->title = 'dummy';
$post->insert();
$this->assertEquals(2, $this->db->createCommand('SELECT COUNT(*) FROM "test"."nullable_posts"')->queryScalar());
$this->assertEquals(1, $this->db->createCommand('SELECT COUNT(*) FROM "test"."nullable_posts" WHERE LENGTH("title") > 0')->queryScalar());
}
/**
*
* @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;
}
/**
* Запросы к форуму vBulletin
*
* @return array
*/
private function vBulletin()
{
$limit = (int) config('forum_threads.limit');
$command = $this->db->createCommand()->select('forumid as id_topic, dateline AS start_date, postusername AS starter_name, lastposterid AS starter_id, threadid AS id_forum, title')->from('{{thread}}')->order('start_date DESC')->limit($limit);
if (config('forum_threads.id_deny') != '') {
$ids = explode(',', config('forum_threads.id_deny'));
$ids = $this->filterIds($ids);
$command->where(array('not in', 'forumid', $ids));
}
return $command->queryAll();
}
/**
* Creates a CDbCommand object for excuting sql statement.
* It will detect the sql statement's behavior.
* While the sql is a simple read operation.
* It will use a slave database connection to contruct a CDbCommand object.
* Default it use current connection(master database).
*
* @override
* @param string $sql
* @return CDbCommand
* */
public function createCommand($sql = null)
{
if ($this->enableSlave && !empty($this->slaves) && is_string($sql) && !$this->getCurrentTransaction() && self::isReadOperation($sql) && ($slave = $this->getSlave())) {
return $slave->createCommand($sql);
} else {
if ($this->_disableWrite && !self::isReadOperation($sql)) {
throw new CDbException("Master db server is not available now!Disallow write operation on slave server!");
}
return parent::createCommand($sql);
}
}
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()));
}
public function createCommand($query = null)
{
if (!$this->logdir) {
$this->logdir = sys_get_temp_dir();
}
if (!$this->monitorFilter) {
$this->monitorFilter = ["type" => "index_subquery", "or" => ["select_type" => "PRIMARY, UNCACHEABLE SUBQUERY", "rows" => 2000, "Extra" => "Using filesort,Using temporary", "duration" => 0.5]];
}
parent::createCommand($query);
return new MonitorDbCommand($this, $query, $this->monitorFilter, $this->logdir);
}
private function createMeta(CDbCommand $command)
{
$matches = $command->queryAll();
$metaInfo = $this->db->createCommand("SHOW META")->queryAll();
$meta = array();
foreach ($metaInfo as $item) {
list($name, $value) = array_values($item);
$meta[$name] = $value;
}
$meta['matches'] = $matches;
return $meta;
}
/**
* 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();
}
