public function testCommandBuilder()
{
$schema = $this->db->schema;
$builder = $schema->commandBuilder;
$this->assertTrue($builder instanceof CDbCommandBuilder);
$table = $schema->getTable('posts');
$c = $builder->createInsertCommand($table, array('title' => 'test post', 'create_time' => '2000-01-01', 'author_id' => 1, 'content' => 'test content'));
$this->assertEquals('INSERT INTO [dbo].[posts] ([title], [create_time], [author_id], [content]) VALUES (:yp0, :yp1, :yp2, :yp3)', $c->text);
$c->execute();
$this->assertEquals(6, $builder->getLastInsertId($table));
$this->assertEquals(6, $this->db->getLastInsertID());
$c = $builder->createCountCommand($table, new CDbCriteria());
$this->assertEquals('SELECT COUNT(*) FROM [dbo].[posts] [t]', $c->text);
$this->assertEquals(6, $c->queryScalar());
$c = $builder->createDeleteCommand($table, new CDbCriteria(array('condition' => 'id=:id', 'params' => array('id' => 6))));
$this->assertEquals('DELETE FROM [dbo].[posts] WHERE id=:id', $c->text);
$c->execute();
$c = $builder->createCountCommand($table, new CDbCriteria());
$this->assertEquals(5, $c->queryScalar());
$c = $builder->createFindCommand($table, new CDbCriteria(array('select' => 'id, title', 'condition' => 'id=:id', 'params' => array('id' => 5), 'order' => 'title', 'limit' => 2, 'offset' => 0)));
$this->assertEquals('SELECT TOP 2 id, title FROM [dbo].[posts] [t] WHERE id=:id ORDER BY title', $c->text);
$rows = $c->query()->readAll();
$this->assertEquals(1, count($rows));
$this->assertEquals('post 5', $rows[0]['title']);
$c = $builder->createFindCommand($table, new CDbCriteria(array('select' => 'id, title', 'order' => 'title', 'limit' => 2, 'offset' => 3)));
$this->assertEquals('SELECT * FROM (SELECT TOP 2 * FROM (SELECT TOP 5 id, title FROM [dbo].[posts] [t] ORDER BY title) as [__inner__] ORDER BY title DESC) as [__outer__] ORDER BY title ASC', $c->text);
$rows = $c->query()->readAll();
$this->assertEquals(2, count($rows));
$this->assertEquals('post 4', $rows[0]['title']);
$c = $builder->createUpdateCommand($table, array('title' => 'new post 5'), new CDbCriteria(array('condition' => 'id=:id', 'params' => array('id' => 5))));
$c->execute();
$c = $builder->createFindCommand($table, new CDbCriteria(array('select' => 'title', 'condition' => 'id=:id', 'params' => array('id' => 5))));
$this->assertEquals('new post 5', $c->queryScalar());
$c = $builder->createSqlCommand('SELECT title FROM posts WHERE id=:id', array(':id' => 3));
$this->assertEquals('post 3', $c->queryScalar());
$c = $builder->createUpdateCounterCommand($table, array('author_id' => -2), new CDbCriteria(array('condition' => 'id=5')));
$this->assertEquals('UPDATE [dbo].[posts] SET [author_id]=[author_id]-2 WHERE id=5', $c->text);
$c->execute();
$c = $builder->createSqlCommand('SELECT author_id FROM posts WHERE id=5');
$this->assertEquals(1, $c->queryScalar());
// test bind by position
$c = $builder->createFindCommand($table, new CDbCriteria(array('select' => 'title', 'condition' => 'id=?', 'params' => array(4))));
$this->assertEquals('SELECT title FROM [dbo].[posts] [t] WHERE id=?', $c->text);
$this->assertEquals('post 4', $c->queryScalar());
// another bind by position
$c = $builder->createUpdateCommand($table, array('title' => 'new post 4'), new CDbCriteria(array('condition' => 'id=?', 'params' => array(4))));
$c->execute();
$c = $builder->createSqlCommand('SELECT title FROM posts WHERE id=4');
$this->assertEquals('new post 4', $c->queryScalar());
// testCreateCriteria
$c = $builder->createCriteria('column=:value', array(':value' => 'value'));
$this->assertEquals('column=:value', $c->condition);
$this->assertEquals(array(':value' => 'value'), $c->params);
$c = $builder->createCriteria(array('condition' => 'column=:value', 'params' => array(':value' => 'value')));
$this->assertEquals('column=:value', $c->condition);
$this->assertEquals(array(':value' => 'value'), $c->params);
$c2 = $builder->createCriteria($c);
$this->assertTrue($c2 !== $c);
$this->assertEquals('column=:value', $c2->condition);
$this->assertEquals(array(':value' => 'value'), $c2->params);
// testCreatePkCriteria
$c = $builder->createPkCriteria($table, 1, 'author_id>1');
$this->assertEquals('[dbo].[posts].[id]=1 AND (author_id>1)', $c->condition);
$c = $builder->createPkCriteria($table, array(1, 2));
$this->assertEquals('[dbo].[posts].[id] IN (1, 2)', $c->condition);
$c = $builder->createPkCriteria($table, array());
$this->assertEquals('0=1', $c->condition);
$table2 = $schema->getTable('orders');
$c = $builder->createPkCriteria($table2, array('key1' => 1, 'key2' => 2), 'name=\'\'');
$this->assertEquals('[dbo].[orders].[key1]=1 AND [dbo].[orders].[key2]=2 AND (name=\'\')', $c->condition);
$c = $builder->createPkCriteria($table2, array(array('key1' => 1, 'key2' => 2), array('key1' => 3, 'key2' => 4)));
$this->assertEquals('(([dbo].[orders].[key1]=1 AND [dbo].[orders].[key2]=2) OR ([dbo].[orders].[key1]=3 AND [dbo].[orders].[key2]=4))', $c->condition);
// createColumnCriteria
$c = $builder->createColumnCriteria($table, array('id' => 1, 'author_id' => 2), 'title=\'\'');
$this->assertEquals('[dbo].[posts].[id]=:yp0 AND [dbo].[posts].[author_id]=:yp1 AND (title=\'\')', $c->condition);
$c = $builder->createPkCriteria($table2, array());
$this->assertEquals('0=1', $c->condition);
}
