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