/**
* @covers Phossa\Query\Clause\UnionTrait::union
* @covers Phossa\Query\Clause\UnionTrait::unionAll
*/
public function testUnion()
{
$ousers = $this->builder->table('oldusers');
$this->assertEquals('SELECT * FROM "users" UNION SELECT * FROM "oldusers"', $this->builder->select()->union()->select()->table('oldusers')->getStatement());
// multiple union
$this->assertEquals('SELECT * FROM "users" UNION SELECT * FROM "oldusers" UNION ALL SELECT "user_id" FROM "oldusers"', $this->builder->select()->union()->select()->from('oldusers')->unionAll()->select('user_id')->from('oldusers')->getStatement());
}
/**
* @covers Phossa\Query\Clause\LimitTrait::page
*/
public function testPage()
{
// page starts with 1
$this->assertEquals('SELECT * FROM "users" LIMIT 30 OFFSET 30', $this->builder->select()->page(2)->getStatement());
// change page length
$this->assertEquals('SELECT * FROM "users" LIMIT 10 OFFSET 20', $this->builder->select()->page(3, 10)->getStatement());
}
/**
* INSERT ... SELECT
*
* @covers Phossa\Query\Dialect\Common::insert()
*/
public function testInsert03()
{
// 2 seperate rows
$str1 = 'INSERT INTO "users" ("uid", "uname") SELECT "user_id", "user_name" FROM "oldusers"';
$ins1 = $this->builder->insert()->into("users")->set(['uid', 'uname'])->select(['user_id', 'user_name'])->from('oldusers');
$this->assertEquals(preg_replace("/\r\n/", "\n", $str1), $ins1->getStatement());
}
/**
* pass parameters
*
* @covers Phossa\Query\Dialect\Common::select()
*/
public function testSelect20()
{
$str = 'SELECT * FROM "students" WHERE "age" IN RANGE(?, ?)';
$sel = $this->builder->select()->from("students")->where("age", "IN", $this->builder->raw('RANGE(?, ?)', 1, 1.2));
$this->assertEquals(preg_replace("/\r\n/", "\n", $str), $sel->getStatement(['positionedParam' => true]));
$this->assertEquals([1, 1.2], $sel->getBindings());
}
/**
* simple update
*
* @covers Phossa\Query\Dialect\Common::update()
*/
public function testUpdate01()
{
// update
$str1 = <<<EOT
UPDATE
"users"
SET
"uid" = 2,
"uname" = 'phossa'
WHERE
"uid" = 10
EOT;
$ins = $this->builder->update()->table("users")->set('uid', 2)->set('uname', 'phossa')->where('uid', 10);
$this->assertEquals(preg_replace("/\r\n/", "\n", $str1), $ins->getStatement($this->settings));
// positioned params
$str2 = 'UPDATE "users" SET "uid" = ?, "uname" = ? WHERE "uid" = ?';
$this->assertEquals(preg_replace("/\r\n/", "\n", $str2), $ins->getStatement(['positionedParam' => true]));
}
/**
* create table
*
* @covers Phossa\Query\Dialect\Common::create()
*/
public function testCreateTable01()
{
$str = <<<EOT
CREATE TEMPORARY TABLE IF NOT EXISTS "new_table"
(
"id" INT NOT NULL AUTO_INCREMENT,
"name" VARCHAR(20) NOT NULL DEFAULT 'NONAME' UNIQUE,
"alias" VARCHAR(10) CHECK (),
PRIMARY KEY ("id"),
UNIQUE ("name"(4) ASC, "alias") ON CONFLICT REPLACE,
UNIQUE ("id", "alias") ON CONFLICT ROLLBACK,
FOREIGN KEY (...)
)
DELAY_KEY_WRITE=1,
MAX_ROWS=100
EOT;
$crt = $this->builder->create()->table('new_table')->temp()->ifNotExists()->addCol('id', 'INT')->notNull()->autoIncrement()->addCol('name', 'VARCHAR(20)')->notNull()->unique()->defaultValue('NONAME')->addCol('alias', 'VARCHAR(10)')->colConstraint('CHECK ()')->primaryKey(['id'])->uniqueKey(['name(4) ASC', 'alias'], 'ON CONFLICT REPLACE')->uniqueKey(['id', 'alias'], 'ON CONFLICT ROLLBACK')->constraint('FOREIGN KEY (...)')->tblOption('DELAY_KEY_WRITE=1')->tblOption('MAX_ROWS=100');
$this->assertEquals(preg_replace("/\r\n/", "\n", $str), $crt->getStatement($this->settings));
}
/**
* @covers Phossa\Query\Clause\WhereTrait::whereExists
* @covers Phossa\Query\Clause\WhereTrait::orWhereExists
* @covers Phossa\Query\Clause\WhereTrait::whereNotExists
* @covers Phossa\Query\Clause\WhereTrait::orWhereNotExists
*/
public function testWhereExists()
{
$users = $this->builder->select('user_id')->where('age', '>', 60);
$sales = $this->builder->table('sales');
// whereExists
$this->assertEquals('SELECT * FROM "sales" WHERE EXISTS (SELECT "user_id" FROM "users" WHERE "age" > 60)', $sales->select()->whereExists($users)->getStatement());
// whereNotExists
$this->assertEquals('SELECT * FROM "sales" WHERE NOT EXISTS (SELECT "user_id" FROM "users" WHERE "age" > 60)', $sales->select()->whereNotExists($users)->getStatement());
// orWhereExists
$this->assertEquals('SELECT * FROM "sales" WHERE "order_id" > 10 OR EXISTS (SELECT "user_id" FROM "users" WHERE "age" > 60)', $sales->select()->where('order_id', '>', 10)->orWhereExists($users)->getStatement());
// orWhereNotExists
$this->assertEquals('SELECT * FROM "sales" WHERE "order_id" > 10 OR NOT EXISTS (SELECT "user_id" FROM "users" WHERE "age" > 60)', $sales->select()->where('order_id', '>', 10)->orWhereNotExists($users)->getStatement());
}
/**
* insert
*
* @covers Phossa\Query\Dialect\Mysql::insert()
*/
public function testInsert01()
{
$str = <<<EOT
INSERT
DELAYED
PARTITION (part1, part2)
(`uid`, `uname`)
VALUES
(2, 'phossa')
ON DUPLICATE KEY UPDATE
uid = uid + 1,
uname = CONCAT(uname, 'xx')
EOT;
$this->assertEquals(preg_replace("/\r\n/", "\n", $str), $this->builder->insert()->addHint('DELAYED')->set(['uid' => 2, 'uname' => 'phossa'])->partition(['part1', 'part2'])->onDup('uid', 'uid + 1')->onDup('uname', 'CONCAT(uname, \'xx\')')->getStatement());
}
/**
* @covers Phossa\Query\Clause\BeforeAfterTrait::after
*/
public function testAfter()
{
$this->assertEquals('SELECT * FROM "users" PARTITION p1', $this->builder->select()->after('from', 'PARTITION p1')->getStatement());
}
/**
* @covers Phossa\Query\Clause\ColTrait::distinct
*/
public function testDistinct()
{
$this->assertEquals('SELECT DISTINCT "user_name"', $this->builder->select()->distinct()->col('user_name')->getStatement());
$this->assertEquals('SELECT DISTINCT "user_name" AS "n"', $this->builder->select()->distinct()->col('user_name', 'n')->getStatement());
}
/**
* example18: parameters
*
* @covers Phossa\Query\Dialect\Common::select()
*/
public function testReadme18()
{
// builder object
$users = new Builder(new Common(), 'users');
// 01: positioned parameters
$this->assertEquals('SELECT * FROM "users" WHERE "user_id" = ?', $users->select()->where("user_id", 10)->getPositionedStatement());
// 02: named parameters
$this->assertEquals('SELECT * FROM "users" WHERE "user_name" = :name AND "user_id" > 100', $users->select()->where("user_name", ':name')->andWhere('user_id', '>', 100)->getNamedStatement());
}
/**
* table() is same as from()
*
* @covers Phossa\Query\Clause\FromTrait::table
*/
public function testTable()
{
$this->assertEquals('SELECT * FROM "users" AS "u", "items" AS "i"', $this->builder->select()->table(['users' => 'u', 'items' => 'i'])->getStatement());
}
/**
* @covers Phossa\Query\Clause\HavingTrait::havingRaw
* @covers Phossa\Query\Clause\HavingTrait::orHavingRaw
*/
public function testHavingRaw()
{
$this->assertEquals('SELECT * FROM "users" HAVING age = 10 OR level > 10', $this->builder->select()->havingRaw('age = 10')->orHavingRaw('level > 10')->getStatement());
}
/**
* @covers Phossa\Query\Clause\OrderByTrait::orderByRaw
*/
public function testOrderByRaw()
{
$this->assertEquals('SELECT * FROM "users" ORDER BY age ASC, level DESC', $this->builder->select()->orderByRaw('age ASC, level DESC')->getStatement());
}
/**
* Test function combinations
*
* @covers Phossa\Query\Clause\FunctionTrait::func
*/
public function testFunc2()
{
$this->assertEquals('SELECT COUNT("user_id") AS "cnt", MIN("age"), MAX("score")', $this->builder->select()->count('user_id', 'cnt')->min('age')->max('score')->getStatement());
$this->assertEquals('SELECT SUM(DISTINCT "age") AS "a", "score"', $this->builder->select()->sumDistinct('age', 'a')->col('score')->getStatement());
}
/**
* raw mode
*
* @covers Phossa\Query\Clause\JoinTrait::crossJoin
*/
public function testJoinRaw()
{
$this->assertEquals('SELECT * FROM "users" CROSS JOIN sales s ON users.uid = s.uid', $this->builder->select()->crossJoin('sales s ON users.uid = s.uid')->getStatement());
}