PHP DboSource::fetchRow方法代碼示例

 /**
  * testRealQueries method
  *
  * @return void
  */
 public function testRealQueries()
 {
     $this->loadFixtures('Apple', 'Article', 'User', 'Comment', 'Tag', 'Sample', 'ArticlesTag');
     $Apple = ClassRegistry::init('Apple');
     $Article = ClassRegistry::init('Article');
     $result = $this->Dbo->rawQuery('SELECT color, name FROM ' . $this->Dbo->fullTableName('apples'));
     $this->assertTrue(!empty($result));
     $result = $this->Dbo->fetchRow($result);
     $expected = array($this->Dbo->fullTableName('apples', false, false) => array('color' => 'Red 1', 'name' => 'Red Apple 1'));
     $this->assertEquals($expected, $result);
     $result = $this->Dbo->fetchAll('SELECT name FROM ' . $this->Dbo->fullTableName('apples') . ' ORDER BY id');
     $expected = array(array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'Red Apple 1')), array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'Bright Red Apple')), array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'green blue')), array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'Test Name')), array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'Blue Green')), array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'My new apple')), array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'Some odd color')));
     $this->assertEquals($expected, $result);
     $result = $this->Dbo->field($this->Dbo->fullTableName('apples', false, false), 'SELECT color, name FROM ' . $this->Dbo->fullTableName('apples') . ' ORDER BY id');
     $expected = array('color' => 'Red 1', 'name' => 'Red Apple 1');
     $this->assertEquals($expected, $result);
     $Apple->unbindModel(array(), false);
     $result = $this->Dbo->read($Apple, array('fields' => array($Apple->escapeField('name')), 'conditions' => null, 'recursive' => -1));
     $expected = array(array('Apple' => array('name' => 'Red Apple 1')), array('Apple' => array('name' => 'Bright Red Apple')), array('Apple' => array('name' => 'green blue')), array('Apple' => array('name' => 'Test Name')), array('Apple' => array('name' => 'Blue Green')), array('Apple' => array('name' => 'My new apple')), array('Apple' => array('name' => 'Some odd color')));
     $this->assertEquals($expected, $result);
     $result = $this->Dbo->read($Article, array('fields' => array('id', 'user_id', 'title'), 'conditions' => null, 'recursive' => 1));
     $this->assertTrue(Set::matches('/Article[id=1]', $result));
     $this->assertTrue(Set::matches('/Comment[id=1]', $result));
     $this->assertTrue(Set::matches('/Comment[id=2]', $result));
     $this->assertFalse(Set::matches('/Comment[id=10]', $result));
 }

 /**
  * sql insert statement
  *
  * @param $datasource
  * @param $tablename
  * @param $exclude_missing_tables
  * @param $return if want return sql string, set true.
  * @return string
  */
 function getInsertSql($datasource, $tablename, $exclude_missing_tables = false, $return = false)
 {
     if (!$this->_checkCurrentDatasource($datasource)) {
         $this->_setupDataSource();
     }
     if (!$return && (empty($this->File) || !$this->File->writable())) {
         return false;
     }
     $tables = $this->_getProcessTables($tablename, $exclude_missing_tables);
     $insert_sql = '';
     foreach ($tables as $table => $fields) {
         /* @var $model AppModel */
         $model = ClassRegistry::init(array('class' => Inflector::classify($table), 'table' => $table));
         $field_names = array_keys($this->DataSource->describe($model));
         $full_tablename = $this->DataSource->fullTableName($model);
         $all_fields = implode(', ', array_map(array($this->DataSource, 'name'), $field_names));
         $count_query = array('table' => $full_tablename, 'fields' => 'count(*) ' . $this->DataSource->alias . 'count', 'alias' => $this->DataSource->alias . $this->DataSource->name($model->alias), 'joins' => '', 'conditions' => 'WHERE 1=1', 'group' => '', 'order' => '', 'limit' => '');
         $count_sql = $this->DataSource->renderStatement('select', $count_query);
         $total = $this->DataSource->fetchRow($count_sql);
         if (is_array($total)) {
             $total = $total[0]['count'];
         }
         $query = array('table' => $full_tablename, 'fields' => implode(', ', $this->DataSource->fields($model)), 'alias' => $this->DataSource->alias . $this->DataSource->name($model->alias), 'joins' => '', 'conditions' => '', 'group' => '', 'order' => '', 'limit' => '');
         $limit = 100;
         $record = array();
         for ($offset = 0; $offset < $total; $offset += $limit) {
             $query['limit'] = $this->DataSource->limit($limit, $offset);
             $select_sql = $this->DataSource->renderStatement('select', $query);
             $datas = $this->DataSource->fetchAll($select_sql, false);
             foreach ($datas as $record) {
                 $insert_query = array('table' => $full_tablename, 'fields' => $all_fields, 'values' => implode(', ', array_map(array($this->DataSource, 'value'), array_values($record[$model->alias]))));
                 $_sql = $this->out($this->DataSource->renderStatement('create', $insert_query) . ';');
                 if ($return) {
                     $insert_sql .= $_sql;
                 }
             }
         }
         // -- sequence update section for postgres
         // NOTE: only primary key sequence..
         if (method_exists($this->DataSource, 'getSequence')) {
             foreach ($fields as $field => $column) {
                 if ($field == 'indexes' || empty($record)) {
                     continue;
                 }
                 if ($column['type'] == 'integer' && isset($column['key']) && $column['key'] == 'primary') {
                     // only primary key
                     $sequence_name = $this->DataSource->getSequence($this->DataSource->fullTableName($model, false), $field);
                     $_sql = $this->out(sprintf('SELECT setval(%s, %s);', $this->DataSource->value($sequence_name), $record[$model->alias][$field]));
                     if ($return) {
                         $insert_sql .= $_sql;
                     }
                 }
             }
         }
     }
     return $insert_sql;
 }

 /**
  * Test that fields are parsed out in a reasonable fashion.
  *
  * @return void
  */
 public function testFetchRowColumnParsing()
 {
     $this->loadFixtures('User');
     $sql = 'SELECT "User"."id", "User"."user", "User"."password", "User"."created", (1 + 1) AS "two" ' . 'FROM "users" AS "User" WHERE ' . '"User"."id" IN (SELECT MAX("id") FROM "users") ' . 'OR "User.id" IN (5, 6, 7, 8)';
     $result = $this->Dbo->fetchRow($sql);
     $expected = array('User' => array('id' => 4, 'user' => 'garrett', 'password' => '5f4dcc3b5aa765d61d8327deb882cf99', 'created' => '2007-03-17 01:22:23'), 0 => array('two' => 2));
     $this->assertEquals($expected, $result);
     $sql = 'SELECT "User"."id", "User"."user" ' . 'FROM "users" AS "User" WHERE "User"."id" = 4 ' . 'UNION ' . 'SELECT "User"."id", "User"."user" ' . 'FROM "users" AS "User" WHERE "User"."id" = 3';
     $result = $this->Dbo->fetchRow($sql);
     $expected = array('User' => array('id' => 3, 'user' => 'larry'));
     $this->assertEquals($expected, $result);
 }

 /**
  * Test parsing more complex field names.
  *
  * @return void
  */
 public function testFetchColumnRowParsingMoreComplex()
 {
     $this->loadFixtures('User');
     $sql = 'SELECT
   COUNT(*) AS User__count,
   COUNT(CASE id WHEN 2 THEN 1 ELSE NULL END) as User__case,
   AVG(CAST("User"."id" AS BIGINT)) AS User__bigint
   FROM "users" AS "User"
   WHERE "User"."id" > 0';
     $result = $this->Dbo->fetchRow($sql);
     $expected = array('0' => array('User__count' => '4', 'User__case' => '1', 'User__bigint' => '2.5'));
     $this->assertEquals($expected, $result);
 }

 /**
  * Use an old user as an admin.
  *
  * @return string
  */
 protected function _oldUser()
 {
     $user_id = trim($this->in('<question>User ID:</question>'));
     $userMap = Configure::read('Forum.userMap');
     if (!$user_id || !is_numeric($user_id)) {
         $user_id = $this->_oldUser();
     } else {
         $result = $this->db->fetchRow(sprintf("SELECT * FROM `%s` AS `User` WHERE `id` = %d LIMIT 1", $this->install['table'], $user_id));
         if (!$result) {
             $this->out('<error>User ID does not exist, please try again</error>');
             $user_id = $this->_oldUser();
         } else {
             $this->install['username'] = $result['User'][$userMap['username']];
             $this->install['password'] = $result['User'][$userMap['password']];
             $this->install['email'] = $result['User'][$userMap['email']];
         }
     }
     return $user_id;
 }