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