public function testException()
{
$cases = array(array('params' => array(), 'message' => 'Empty'), array('params' => "blabla", 'message' => 'Not array - string'), array('params' => 3223, 'message' => 'Not array - int'), array('params' => array(1, 2, 3), 'message' => 'Not valid format - list of integer'), array('params' => array('1', '2', '3'), 'message' => 'Not valid format - list of string'), array('params' => array(array(1, 2, 3)), 'message' => 'Not valid format - array of integer list'));
foreach ($cases as $case) {
$where = new Where($case['params']);
$this->assertEmpty($where->getSQL(), $case['message']);
$this->assertEmpty($where->getParams(), $case['message']);
}
//TODO test partial format with correct and wrong data
}
/**
* Gera um comando SQL de delete no banco de dados
* Se não houver uma condição WHERE por segurança não será gerado o comando
* @param string $table Nome da tabela
* @param mixed $where Array associativo no formato wherearray ou um objeto Where
* @example
* var_dump($query->delete("tabela1", ['id' => 33]));
* //Saida: array(
* 'status' => true,
* 'sql' => 'DELETE FROM tabela1 WHERE id = ?',
* 'values' => array(33)
* )
*
* @return array No formato queryresult
*/
public function delete($table, $where)
{
if (!$this->validateTable($table)) {
return $this->returnQueryResultError('O nome da tabela é inválido');
}
$where = Where::getInstance($where);
if ($where->isEmpty()) {
return $this->returnQueryResultError('Condição inválida para um comando DELETE');
}
$sql = 'DELETE FROM ' . $table . ' ' . $where->getSQL();
return $this->getQueryResult($sql, $where->getParams());
}
/**
* @covers DataBase::update
* @covers Database::select
* @covers Database::selectOne
*/
public function testUpdate()
{
$r0 = $this->db->selectOne('name', 'test', array('id' => 1));
$this->assertEquals('value 1', $r0['name']);
$this->assertEquals(1, $this->db->update('test', array('name' => 'Other test'), array('id' => 1)));
$r1 = $this->db->selectOne('name', 'test', array('id' => 1));
$this->assertEquals('Other test', $r1['name']);
$this->assertEquals(3, $this->db->update('test', array('name' => 'Other test'), Where::one('id', '>', 0)));
$result = $this->db->select('*', 'test');
foreach ($result as $id => $row) {
$this->assertEquals('Other test', $row['name']);
}
$this->assertEquals(0, $this->db->update('test', array('name' => 'Other test'), array('id' => 0)));
$this->assertFalse(LoggerApp::hasError(), LoggerApp::getLastError());
$this->assertEquals(0, $this->db->update('testinvalid', array('xyz' => 1), array('id' => 1)));
$this->assertTrue(LoggerApp::hasError(), 'Não gerou o erro esperado');
//Update without where
$this->assertEquals(0, $this->db->update('test', array('name' => 'a'), array()));
}
/**
* @covers Expression::setHaving
* @covers Expression::getHaving
* @covers Expression::hasHaving
* @covers Expression::clearHaving
*/
public function testHaving()
{
$this->assertFalse($this->object->hasHaving(), 'Having deveria ser vazio');
$this->assertEquals($this->object, $this->object->setHaving(array('id' => 1)), 'Não retornou o objeto');
$this->assertTrue($this->object->hasHaving(), 'Having não deveria ser vazio');
$this->assertEquals(array(1), $this->object->getHaving()->getParams(), 'Parametro inválido para o having');
$this->object->setHaving(array());
$this->assertFalse($this->object->hasHaving(), 'Having deveria ser vazio novamente');
$where = Where::getInstance(array('id' => 2));
$this->object->setHaving($where);
$this->assertEquals($where, $this->object->getHaving());
$this->assertEquals($this->object, $this->object->clearHaving(), 'Não retornou o objeto');
$this->assertFalse($this->object->hasHaving(), 'Having deveria ser vazio novamente');
}
/**
* Retorna se foi definido uma condição para agrupamento
* @return boolean
*/
public function hasHaving()
{
return !$this->having->isEmpty();
}
public function testSelect()
{
$r1 = $this->object->select('*', 'teste', Where::one('a', '>', 0));
$this->assertTrue($r1['status'], $r1['message']);
$this->assertEquals('SELECT * FROM teste WHERE a > ?', trim($r1['sql']));
$this->assertEquals(array(0), $r1['values']);
$r2 = $this->object->select(array('a', 'b'), 'teste', array('id' => 2), array('limit' => 10, 'orderBy' => 'id'));
$this->assertTrue($r2['status'], $r2['message']);
$this->assertEquals('SELECT a,b FROM teste WHERE id = ? ORDER BY id ASC LIMIT ?', trim($r2['sql']));
$this->assertEquals(array(2, 10), $r2['values']);
$r3 = $this->object->select('*', 'teste', array('id' => 2), array('groupBy' => array('id', 'teste'), 'having' => array('teste' => 1)));
$this->assertTrue($r3['status'], $r3['message']);
$this->assertEquals('SELECT * FROM teste WHERE id = ? GROUP BY id,teste HAVING teste = ?', trim($r3['sql']));
$this->assertEquals(array(2, 1), $r3['values']);
$r4 = $this->object->select(array('a', 'b'), 'teste', null, array('limit' => 10, 'offset' => 11));
$this->assertTrue($r4['status'], $r4['message']);
$this->assertEquals('SELECT a,b FROM teste LIMIT ? OFFSET ?', trim($r4['sql']));
$this->assertEquals(array(10, 11), $r4['values']);
$r5 = $this->object->select('a, b, c', 'xyz', 'WHERE a > ? AND b = ?', array(3, 5));
$this->assertTrue($r5['status'], $r5['message']);
$this->assertEquals('SELECT a, b, c FROM xyz WHERE a > ? AND b = ?', trim($r5['sql']));
$this->assertEquals(array(3, 5), $r5['values']);
$extra = array('orderBy' => array('id' => 'desc'), 'limit' => 10, 'offset' => 0);
$r6 = $this->object->select('*', 'table1', array('ativo' => true), $extra);
$this->assertTrue($r6['status'], $r6['message']);
$this->assertEquals('SELECT * FROM table1 WHERE ativo = ? ORDER BY id DESC LIMIT ?', trim($r6['sql']));
$this->assertEquals(array(true, 10), $r6['values']);
}