/** * @test */ public function itShouldBeAbleToWriteCommentInQuery() { $valueArray = array('user_id' => 1, 'name' => 'Nil', 'contact' => '*****@*****.**'); $this->query->setTable('user')->setComment('This is a comment')->setValues($valueArray); $expected = "-- This is a comment\n" . 'REPLACE INTO user (user.user_id, user.name, user.contact) VALUES (:v1, :v2, :v3)'; $this->assertSame($expected, $this->writer->write($this->query)); $this->assertEquals(\array_values($valueArray), \array_values($this->query->getValues())); $expected = array(':v1' => 1, ':v2' => 'Nil', ':v3' => '*****@*****.**'); $this->assertEquals($expected, $this->writer->getValues()); }
/** * @test */ public function itShouldWriteDeleteRowWithWhereConditionAndLimit1() { $this->query->setTable('user'); $conditions = $this->query->where(); $conditions->equals('user_id', 10)->equals('user_id', 20)->equals('user_id', 30); $this->query->limit(1); $expected = <<<SQL DELETE FROM user WHERE (user.user_id = :v1) AND (user.user_id = :v2) AND (user.user_id = :v3) LIMIT :v4 SQL; $this->assertSame($expected, $this->writer->write($this->query)); $expected = array(':v1' => 10, ':v2' => 20, ':v3' => 30, ':v4' => 1); $this->assertEquals($expected, $this->writer->getValues()); }
/** * @test */ public function itShouldAllowWhereConditionAsLiteral() { $this->query->setTable('user')->where()->asLiteral("(username is not null and status=:status)")->notEquals('name', '%N%'); $expected = "SELECT user.* FROM user WHERE (username is not null and status=:status) AND (user.name <> :v1)"; $this->assertSame($expected, $this->writer->write($this->query)); $expected = array(':v1' => '%N%'); $this->assertEquals($expected, $this->writer->getValues()); }
/** * @test */ public function itShouldBeAbleToWriteColumnAsAFuncWithoutBracketsStatement() { $this->query->setTable('user')->setColumns(array('user_id', 'username'))->setFunctionAsColumn('CURRENT_TIMESTAMP', array(), 'server_time')->where()->equals('user_id', 1); $expected = 'SELECT user.user_id, user.username, CURRENT_TIMESTAMP AS \'server_time\' FROM user WHERE (user.user_id = :v1)'; $this->assertSame($expected, $this->writer->write($this->query)); $expected = array(':v1' => 1); $this->assertEquals($expected, $this->writer->getValues()); }
/** * @test */ public function itShouldWriteJoin() { $this->query->isJoin(true)->setTable('user')->on()->equals('user_id', 1); $expected = 'JOIN user ON (user.user_id = :v1)'; $this->assertSame($expected, $this->writer->write($this->query)); $expected = array(':v1' => 1); $this->assertEquals($expected, $this->writer->getValues()); }
/** * @test */ public function itShouldWriteUpdateQueryWithWhereConstrainAndLimit1() { $this->query->setTable('user')->setValues($this->valueArray)->where()->equals('user_id', 1); $this->query->limit(1); $expected = 'UPDATE user SET user.user_id = :v1, user.name = :v2, user.contact = :v3 WHERE (user.user_id = :v4) LIMIT :v5'; $this->assertSame($expected, $this->writer->write($this->query)); $expected = array(':v1' => 1, ':v2' => 'Nil', ':v3' => '*****@*****.**', ':v4' => 1, ':v5' => 1); $this->assertEquals($expected, $this->writer->getValues()); }
/** * @test */ public function itShouldBeAbleToDoWhereNotExists() { $select = new Select('banned_user'); $select->where()->equals('user_id', 1); $this->query->setTable('user')->setColumns(array('user_id', 'role_id'))->where()->notExists($select)->equals('user', 'Nil'); $expected = 'SELECT user.user_id, user.role_id FROM user WHERE (user.user = :v1) AND ' . 'NOT EXISTS (SELECT banned_user.* FROM banned_user WHERE (banned_user.user_id = :v2))'; $this->assertSame($expected, $this->writer->write($this->query)); $expected = array(':v1' => 'Nil', ':v2' => 1); $this->assertEquals($expected, $this->writer->getValues()); }