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