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