public function dataInsert()
{
$rows = array();
$args = array();
// ROW 1
// --------------------
$args[0] = new Query\Insert(self::getNewDb());
$args[0]->type('IGNORE')->into('table1')->set(array('name' => 10, 'email' => '*****@*****.**'));
$args[1] = <<<SQL
INSERT IGNORE INTO `table1` SET `name` = ?, `email` = ?
SQL;
$args[2] = array(10, '*****@*****.**');
$rows[] = $args;
// ROW 2
// --------------------
$select = new Query\Select(self::getNewDb());
$select->from('table2')->where('name', '10');
$args[0] = new Query\Insert(self::getNewDb());
$args[0]->into('table1')->columns(array('id', 'name'))->select($select);
$args[1] = <<<SQL
INSERT INTO `table1` (`id`, `name`) SELECT * FROM `table2` WHERE (`name` = ?)
SQL;
$args[2] = array(10);
$rows[] = $args;
// ROW 3
// --------------------
$args[0] = new Query\Insert(self::getNewDb());
$args[0]->into('table1')->columns(array('id', 'name'))->values(array(1, 'name1'))->values(array(2, 'name2'));
$args[1] = <<<SQL
INSERT INTO `table1` (`id`, `name`) VALUES (?, ?), (?, ?)
SQL;
$args[2] = array(1, 'name1', 2, 'name2');
$rows[] = $args;
return $rows;
}
public function dataRenderValue()
{
$query = new Query\Select(self::getNewDb());
$query->from('table1')->where('name', 'Peter');
$sql = new SQL\SQL('IF(name = "test", "big", "samll")');
return array(array(new SQL\Set('col', 'param1'), '?'), array(new SQL\Set('col', $query), '(SELECT * FROM `table1` WHERE (`name` = ?))'), array(new SQL\Set('col', $sql), 'IF(name = "test", "big", "samll")'), array(new SQL\SetMultiple('col', array(1 => 'test', 2 => 'param')), 'CASE id WHEN ? THEN ? WHEN ? THEN ? ELSE col END'));
}
public function dataConstruct()
{
$sql = new SQL\SQL('IF (column, 10, ?)', array(20));
$query = new Query\Select(self::getNewDb());
$query->from('table1')->where('name', 10)->limit(1);
return array(array('column', 20, 'column', 20, array(20)), array('column', $sql, 'column', $sql, array(20)), array('column', $query, 'column', $query, array(10)));
}
/**
* @covers ::getParameters
*/
public function testGetParameters()
{
$query = new Query\Union(self::getDb());
$select1 = new Query\Select(self::getDb());
$select2 = new Query\Select(self::getDb());
$select1->from('table1')->where('name', 10);
$select2->from('table2')->whereIn('name', array(1, 2));
$query->select($select1)->select($select2);
$this->assertEquals(array(10, 1, 2), $query->getParameters());
}
/**
* @covers ::render
* @covers ::parameters
*/
public function testCompile()
{
$select2 = new Query\Select(self::getDb());
$select2->from('one')->type('DISTINCT')->join('table1', new SQL('USING (col1, col2)'))->where('table1.name', 'small');
$select = new Query\Select(self::getDb());
$select->from('bigtable')->from('smalltable', 'alias')->from($select2, 'select_alias')->column('col1')->column(new SQL('IF(name = ?, "big", "small")', array(10)), 'type')->column('col3', 'alias_col')->where('bigtable.test', 'value')->whereNot('alias.test', 'some bad val')->whereRaw('test_statement = IF ("test", ?, ?)', array('val1', 'val2'))->join('table2', array('col1' => 'col2'))->whereRaw('type > ? AND type < ? AND base IN ?', array(10, 20, array('1', '2', '3')))->having('test', 'value2')->havingRaw('type > ? AND base IN ?', array(20, array('5', '6', '7')))->limit(10)->offset(8)->order('type', 'ASC')->order('base')->group('base', 'ASC')->group('type');
$expectedSql = <<<SQL
SELECT `col1`, IF(name = ?, "big", "small") AS `type`, `col3` AS `alias_col` FROM `bigtable`, `smalltable` AS `alias`, (SELECT DISTINCT * FROM `one` JOIN `table1` USING (col1, col2) WHERE (`table1`.`name` = ?)) AS `select_alias` JOIN `table2` ON `col1` = `col2` WHERE (`bigtable`.`test` = ?) AND (`alias`.`test` != ?) AND (test_statement = IF ("test", ?, ?)) AND (type > ? AND type < ? AND base IN (?, ?, ?)) GROUP BY `base` ASC, `type` HAVING (`test` = ?) AND (type > ? AND base IN (?, ?, ?)) ORDER BY `type` ASC, `base` LIMIT 10 OFFSET 8
SQL;
$this->assertEquals($expectedSql, Compiler\Select::render($select));
$expectedParameters = array(10, 'small', 'value', 'some bad val', 'val1', 'val2', '10', '20', '1', '2', '3', 'value2', '20', '5', '6', '7');
$this->assertEquals($expectedParameters, Compiler\Select::parameters($select));
}
/**
* @covers ::render
* @covers ::parameters
*/
public function testCompile()
{
$select1 = new Query\Select(self::getDb());
$select1->from('one')->column('col1')->type('DISTINCT')->join('table1', new SQL('USING (col1, col2)'))->where('name', 'small');
$select2 = new Query\Select(self::getDb());
$select2->from('bigtable')->column('bigtable.col1')->column('base')->column('type')->where('test', 'value')->whereRaw('test_statement = IF ("test", ?, ?)', array('val1', 'val2'))->join('table2', array('col1' => 'col2'))->limit(10)->offset(8)->order('base')->group('type');
$union = new Query\Union(self::getDb());
$union->select($select1)->select($select2)->order('col1')->limit(20);
$expectedSql = <<<SQL
(SELECT DISTINCT `col1` FROM `one` JOIN `table1` USING (col1, col2) WHERE (`name` = ?)) UNION (SELECT `bigtable`.`col1`, `base`, `type` FROM `bigtable` JOIN `table2` ON `col1` = `col2` WHERE (`test` = ?) AND (test_statement = IF ("test", ?, ?)) GROUP BY `type` ORDER BY `base` LIMIT 10 OFFSET 8) ORDER BY `col1` LIMIT 20
SQL;
$this->assertEquals($expectedSql, Compiler\Union::render($union));
$expectedParameters = array('small', 'value', 'val1', 'val2');
$this->assertEquals($expectedParameters, Compiler\Union::parameters($union));
}
public function __construct(Repo $repo)
{
$this->repo = $repo;
$table = $this->getDb()->escapeName($repo->getTable());
$this->from($repo->getTable())->column(new SQL("{$table}.*"));
parent::__construct($repo->getDbInstance());
}
/**
* @covers ::getParameters
*/
public function testGetParameters()
{
$query = new Query\Select(self::getDb());
$query->from('table1')->where('name', 10)->whereIn('value', array(2, 3));
$this->assertEquals(array(10, 2, 3), $query->getParameters());
}
/**
* @param Query\Select $query
* @return array
*/
public static function parameters(Query\Select $query)
{
return Compiler::parameters(array($query->getColumns(), $query->getFrom(), $query->getJoin(), $query->getWhere(), $query->getGroup(), $query->getHaving(), $query->getOrder(), $query->getLimit(), $query->getOffset()));
}
public function dataRender()
{
$query = new Query\Select(self::getNewDb());
$query->from('table2')->limit(1);
return array(array('table1', 'alias1', 'table1 AS alias1'), array('table1', null, 'table1'), array($query, 'alias1', '(SELECT * FROM `table2` LIMIT 1) AS alias1'), array($query, null, '(SELECT * FROM `table2` LIMIT 1)'));
}
