public function testSqlInsert() { $model = new \Simple\Model(['table' => 'superA']); $query = (new \Simple\Query($model))->field($model->field('username'), 'John')->field($model->field('password'), 'hello_world'); $sql = 'INSERT superA (superA.username, superA.password) VALUES ((?), (?))'; $this->assertEquals($sql, $query->sqlInsert()); $this->assertEquals(array('John', 'hello_world'), $query->bindParameters); }
public function testSqlUpdateToString() { $model = new \Simple\Model(['table' => 'superA']); $query = (new \Simple\Query($model, "update"))->field($model->field('username'), 'John')->field($model->field('password'), 'hello_world')->where($model->pk(), 1); $sql = 'UPDATE superA SET (superA.username = (?), superA.password = (?)) WHERE idSuperA = (?)'; $this->assertEquals($sql, "{$query}"); $this->assertEquals(array('John', 'hello_world', 1), $query->bindParameters); }
public function testFk() { $model = new \Simple\Model(['table' => 'superA', 'pk' => 'primaryA']); $modelB = new \Simple\Model(['table' => 'superB', 'pk' => 'primaryB']); $this->assertEquals($model->pk(), 'primaryA'); $this->assertEquals($model->field('work'), 'superA.work'); $this->assertEquals($model->fk($modelB), 'superA.primaryBSuperB'); }
public function testSqlSelectWithAlias() { $modelA = new \Simple\Model(['table' => 'superA', 'alias' => 'A']); $modelB = new \Simple\Model(['table' => 'hiperB', 'alias' => 'B']); $modelC = new \Simple\Model(['table' => 'megaC', 'alias' => 'C']); $incrementalQuery = 'SELECT A.* FROM superA AS A'; $query = new \Simple\Query($modelA); $query->select($modelA->field('*')); $this->assertEquals($incrementalQuery, $query->sqlSelect()); $queryPage = clone $query; $queryPage->page(1, 15); $this->assertEquals('SELECT A.* FROM superA AS A LIMIT 0, 15', $queryPage->sqlSelect()); $queryNull = clone $query; $queryNull->where($modelA->field('user'), 'NULL'); $this->assertEquals('SELECT A.* FROM superA AS A WHERE A.user IS NULL', $queryNull->sqlSelect()); $queryNull->where($modelA->field('gender'), 'NULL', 'IS NOT'); $this->assertEquals('SELECT A.* FROM superA AS A WHERE A.user IS NULL AND A.gender IS NOT NULL', $queryNull->sqlSelect()); $queryPage->limit(0, 150); $this->assertEquals('SELECT A.* FROM superA AS A LIMIT 0, 150', $queryPage->sqlSelect()); $incrementalQuery .= ' WHERE A.name = (?)'; $query->where($modelA->field('name'), 'John'); $this->assertEquals($incrementalQuery, $query->sqlSelect()); $this->assertContains('John', $query->bindParameters); $incrementalQuery .= ' AND A.age = (?)'; $query->where($modelA->field('age'), 1); $this->assertEquals($incrementalQuery, $query->sqlSelect()); $this->assertContains('John', $query->bindParameters); $this->assertContains(1, $query->bindParameters); $query->group(); $this->assertEquals('SELECT A.* FROM superA AS A WHERE A.name = (?) AND A.age = (?) GROUP BY A.id', $query->sqlSelect()); $queryJoin = clone $query; $queryJoin->join('left', $modelB); $this->assertEquals('SELECT A.* FROM superA AS A LEFT JOIN hiperB AS B ON A.id = B.idSuperA WHERE A.name = (?) AND A.age = (?) GROUP BY A.id', $queryJoin->sqlSelect()); $queryOrder = clone $queryJoin; $queryOrder->order($modelB->field($modelB->pk())); $this->assertEquals('SELECT A.* FROM superA AS A LEFT JOIN hiperB AS B ON A.id = B.idSuperA WHERE A.name = (?) AND A.age = (?) GROUP BY A.id ORDER BY B.id ASC', $queryOrder->sqlSelect()); $queryJoin->join('left', (new \Simple\Query($modelC))->equal($modelA->field($modelA->pk()), $modelC->field('fk_id_table_A'))->where($modelC->field('category'), 2)); $this->assertEquals('SELECT A.* FROM superA AS A LEFT JOIN hiperB AS B ON A.id = B.idSuperA LEFT JOIN megaC AS C ON A.id = C.fk_id_table_A AND C.category = (?) WHERE A.name = (?) AND A.age = (?) GROUP BY A.id', $queryJoin->sqlSelect()); $this->assertContains('John', $queryJoin->bindParameters); $this->assertContains(1, $queryJoin->bindParameters); $this->assertContains(2, $queryJoin->bindParameters); $this->assertEquals(array(2, 'John', 1), $queryJoin->bindParameters); //$this->assertEquals('SELECT A.* FROM superA AS A LEFT JOIN hiperB AS B ON A.id = B.idSuperA LEFT JOIN megaC AS C ON A.id = C.fk_id_table_A WHERE A.name = (?) AND A.age = (?) GROUP BY A.id',$queryJoin->sqlSelect()); //$this->assertEquals('SELECT A.* FROM superA AS A LEFT JOIN hiperB AS B ON A.id=B.idSuperA',$query->sqlSelect()); }
public function testSqlDeleteToString() { $model = new \Simple\Model(['table' => 'superA']); $query = (new \Simple\Query($model, 'delete'))->where($model->pk(), 1); $sql = 'DELETE FROM superA WHERE idSuperA = (?)'; $this->assertEquals($sql, $query->sqlDelete()); $query->where($model->field('user'), 2); $sql = 'DELETE FROM superA WHERE idSuperA = (?) AND superA.user = (?)'; $this->assertEquals($sql, "{$query}"); $this->assertEquals(array(1, 2), $query->bindParameters); }
public function testLimit() { $modelA = new \Simple\Model(['table' => 'superA', 'alias' => 'A']); $incrementalQuery = 'SELECT A.* FROM superA AS A LIMIT 123'; $query = new \Simple\Query($modelA); $query->select($modelA->field('*'))->limit(123); $this->assertEquals($incrementalQuery, $query->sqlSelect()); $this->assertEquals($incrementalQuery, $query->sqlSelect()); }