function test_addHaving()
{
// which companies has more than one worker one the same place and the company_id must be greater than 1
$userIds = array();
$user = new tests_Common(TABLE_USER);
$newData = array('login' => 'hans', 'password' => '0', 'name' => 'Hans Dampf', 'address_id' => 1, 'company_id' => 1);
$userIds[] = $user->add($newData);
$newData = array('login' => 'rudi', 'password' => '0', 'name' => 'Rudi Ratlos', 'address_id' => 1, 'company_id' => 1);
$userIds[] = $user->add($newData);
$newData = array('login' => 'susi', 'password' => '0', 'name' => 'Susi Sorglos', 'address_id' => 2, 'company_id' => 3);
$userIds[] = $user->add($newData);
$newData = array('login' => 'lieschen', 'password' => '0', 'name' => 'Lieschen Mueller', 'address_id' => 3, 'company_id' => 5);
$userIds[] = $user->add($newData);
$newData = array('login' => 'werner', 'password' => '0', 'name' => 'Werner Lehmann', 'address_id' => 3, 'company_id' => 5);
$userIds[] = $user->add($newData);
$user->setGroup('company_id,address_id');
$user->setHaving('COUNT(address_id) > 1');
$user->addHaving('company_id > 1');
$this->assertEquals(array(5), $user->getCol('company_id'));
// first test
$user->reset();
$user->setGroup('company_id,address_id');
$user->addHaving('COUNT(address_id) > 1');
// this is not correct but must also work.
$user->addHaving('company_id > 1');
$this->assertEquals(array(5), $user->getCol('company_id'));
// second test
}
function test_getCol()
{
$user = new tests_Common(TABLE_USER);
$user->setLimit(0, 10);
$user->add(array('login' => 1));
$user->add(array('login' => 2));
$user->add(array('login' => 3));
$user->add(array('login' => 4));
$this->assertEquals(1, sizeof($user->getCol('id', 0, 1)));
$user->setLimit(0, 3);
$this->assertEquals(2, sizeof($user->getCol('id', 0, 2)));
$this->assertEquals(3, sizeof($user->getCol('id')));
}
function test_addWhereSearch()
{
$user = new tests_Common(TABLE_USER);
$user->removeAll();
$user->add(array('name' => 'Wolfram Kriesing'));
$user->add(array('name' => 'WOLFRAM Daniel KrIESIng'));
$user->add(array('name' => ' kriesing wolfram '));
$user->setWhere();
$user->addWhereSearch('name', 'Wolfram Kriesing');
$this->assertEquals(2, $user->getCount(), 'getCount(): Did not find the inserted number of user names.');
$user->add(array('name' => 'Wolfram and here goes some string Kriesing but it should be found'));
$user->add(array('name' => '%Wolfram man in the middle :-) Kriesing and smthg behind%'));
$this->assertEquals(4, $user->getCount(), 'getCount(): Did not find the inserted number of user names.');
$user->removeAll();
}
/**
* This method actually checks if the functionality that needs to be changed
* for the above test to work will still work after the change ...
*
* check if stuff like MAX(id), LOWER(question), etc. will be converted to
* MAX(TABLE_QUESTION.id), LOWER(TABLE_QUESTION.question)
* this is done for preventing ambigious column names, that's why it only applies
* in joined queries ...
*/
function test_testSqlFunction()
{
$theQuestion = 'Why does this not work?';
$theAnswer = 'I dont know!';
$question = new tests_Common(TABLE_QUESTION);
$newQuest = array(TABLE_QUESTION => $theQuestion);
$qid = $question->add($newQuest);
$answer = new tests_Common(TABLE_ANSWER);
$newAnswer = array(TABLE_QUESTION . '_id' => $qid, TABLE_ANSWER => $theAnswer);
$aid = $answer->add($newAnswer);
$question->autoJoin(TABLE_ANSWER);
// $question->setSelect('id,'.TABLE_QUESTION.' as question,'.TABLE_ANSWER.' as answer');
$question->setSelect('MAX(id),' . TABLE_ANSWER . '.id');
$this->assertTrue(strpos($question->_buildSelectQuery(), 'MAX(' . TABLE_QUESTION . '.id)'));
// check '(question)'
$question->setSelect('LOWER(question),' . TABLE_ANSWER . '.*');
$this->assertTrue(strpos($question->_buildSelectQuery(), 'LOWER(' . TABLE_QUESTION . '.question)'));
// check 'id,'
$question->setSelect('id,' . TABLE_ANSWER . '.*');
$this->assertTrue(strpos($question->_buildSelectQuery(), TABLE_QUESTION . '.id'));
// check 'id as qid'
$question->setSelect('id as qid,' . TABLE_ANSWER . '.*');
$this->assertTrue(strpos($question->_buildSelectQuery(), TABLE_QUESTION . '.id as qid'));
// check 'id as qid'
$question->setSelect('LOWER( question ),' . TABLE_ANSWER . '.*');
$this->assertTrue(strpos($question->_buildSelectQuery(), 'LOWER( ' . TABLE_QUESTION . '.question )'));
}
