.dev/tests/unit/db/class_db_offline_query_builder_test.Test.php
<?php
require_once __DIR__ . '/db_offline_abstract.php';
/**
* @requires extension mysqli
*/
class class_db_offline_query_builder_test extends db_offline_abstract
{
public function test_select_star()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'*',
self::qb()->select()->_sql['select'][0]
);
$this->assertEquals(
'*',
self::qb()->select('*')->_sql['select'][0]
);
$this->assertEquals(
'*',
self::qb()->select(' *')->_sql['select'][0]
);
$this->assertEquals(
'*',
self::qb()->select('* ')->_sql['select'][0]
);
$this->assertEquals(
'*',
self::qb()->select(' * ')->_sql['select'][0]
);
$this->assertEmpty(
self::qb()->select()->sql()
);
$this->assertEmpty(
self::qb()->select()->__toString()
);
}
public function test_select_aggregates()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'`id`',
self::qb()->select('id')->_sql['select'][0]
);
$this->assertFalse(
self::qb()->select('id')->sql()
);
$this->assertEquals(
'COUNT(id)',
self::qb()->select('COUNT(id)')->_sql['select'][0]
);
$this->assertEquals(
'SUM(id)',
self::qb()->select('SUM(id)')->_sql['select'][0]
);
$this->assertEquals(
'MIN(id)',
self::qb()->select('MIN(id)')->_sql['select'][0]
);
$this->assertEquals(
'MAX(id)',
self::qb()->select('MAX(id)')->_sql['select'][0]
);
$this->assertEquals(
'AVG(id)',
self::qb()->select('AVG(id)')->_sql['select'][0]
);
$this->assertEquals(
'COUNT(id) AS `num`',
self::qb()->select('COUNT(id) as num')->_sql['select'][0]
);
}
public function test_select_basic()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'`id` , `name`',
self::qb()->select('id', 'name')->_sql['select'][0]
);
$this->assertFalse(
self::qb()->select('id', 'name')->sql()
);
}
public function test_select_check_wrong_input()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertNull(
self::qb()->select('')->_sql['select'][0]
);
$this->assertNull(
self::qb()->select(false)->_sql['select'][0]
);
$this->assertNull(
self::qb()->select(0)->_sql['select'][0]
);
$this->assertNull(
self::qb()->select('0')->_sql['select'][0]
);
$this->assertFalse(
self::qb()->select('')->sql()
);
$this->assertFalse(
self::qb()->select([])->sql()
);
$this->assertFalse(
self::qb()->select(false)->sql()
);
$this->assertFalse(
self::qb()->select(0)->sql()
);
$this->assertFalse(
self::qb()->select('0')->sql()
);
}
public function test_select_complex()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'`u`.`id` , `s`.`id` , `t`.`pid`',
self::qb()->select('u.id', 's.id', 't.pid')->_sql['select'][0]
);
$this->assertEquals(
'`u`.`id` AS `user_id`',
self::qb()->select(['u.id' => 'user_id'])->_sql['select'][0]
);
$this->assertEquals(
'`u`.`id` AS `user_id` , `a`.`id` AS `article_id` , `b`.`id` AS `blog_id`',
self::qb()->select(['u.id' => 'user_id', 'a.id' => 'article_id', 'b.id' => 'blog_id'])->_sql['select'][0]
);
$this->assertEquals(
'`u`.`id` AS `user_id` , `a`.`id` AS `article_id` , `b`.`id` AS `blog_id`',
self::qb()->select(['u.id' => 'user_id'], ['a.id' => 'article_id'], ['b.id' => 'blog_id'])->_sql['select'][0]
);
$this->assertEquals(
'COUNT(*) AS `num`',
self::qb()->select(['COUNT(*)' => 'num'])->_sql['select'][0]
);
$this->assertEquals(
'COUNT(id) AS `num`',
self::qb()->select(['COUNT(id)' => 'num'])->_sql['select'][0]
);
$this->assertEquals(
'COUNT(u.id) AS `num`',
self::qb()->select(['COUNT(u.id)' => 'num'])->_sql['select'][0]
);
$this->assertEquals(
'DISTINCT u.id',
self::qb()->select('DISTINCT u.id')->_sql['select'][0]
);
$this->assertEquals(
'DISTINCT u.id AS `num`',
self::qb()->select(['DISTINCT u.id' => 'num'])->_sql['select'][0]
);
$this->assertEquals(
'DISTINCT `u`.`id` AS `num` , `a`.`id` AS `article_id`',
self::qb()->select(function () {
return 'DISTINCT `u`.`id` AS `num`';
}, function () {
return '`a`.`id` AS `article_id`';
})->_sql['select'][0]
);
}
public function test_select_string_as()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'`s`.`id` AS `sid`',
self::qb()->select('s.id as sid')->_sql['select'][0]
);
$this->assertEquals(
'`s`.`id` AS `sid` , `u`.`id` AS `uid`',
self::qb()->select('s.id as sid', 'u.id as uid')->_sql['select'][0]
);
$this->assertEquals(
'`u`.`id` AS `uid`',
self::qb()->select(['u.id as uid'])->_sql['select'][0]
);
}
public function test_select_multiple_calls()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT `s`.`id` , `u`.`id` FROM `' . DB_PREFIX . 'user`',
self::qb()->select('s.id')->select('u.id')->from('user')->sql()
);
$this->assertEquals(
'SELECT `s`.`id` AS `sid` , `u`.`id` AS `uid` FROM `' . DB_PREFIX . 'user`',
self::qb()->select('s.id as sid')->select('u.id as uid')->from('user')->sql()
);
$this->assertEquals(
'SELECT `s`.`id` AS `sid` , `u`.`id` AS `uid` FROM `' . DB_PREFIX . 'user`',
self::qb()->select('s.id as sid, u.id as uid')->from('user')->sql()
);
$this->assertEquals(
'SELECT `s`.`id` AS `sid` , `u`.`id` AS `uid` , `u`.`name` AS `uname` , `u`.`group` AS `group_id` , `u`.`verified` FROM `' . DB_PREFIX . 'user`',
self::qb()->select('s.id as sid, u.id as uid', ['u.name as uname'], ['u.group' => 'group_id'], 'u.verified')->from('user')->sql()
);
}
public function test_from()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->from()->sql()
);
$this->assertFalse(
self::qb()->select()->from()->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user`',
self::qb()->from('user')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user`',
self::qb()->select()->from('user')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` , `' . DB_PREFIX . 'articles`',
self::qb()->select()->from('user', 'articles')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u`',
self::qb()->select()->from(['user' => 'u'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` , `' . DB_PREFIX . 'articles` AS `a`',
self::qb()->select()->from(['user' => 'u', 'articles' => 'a'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` , `' . DB_PREFIX . 'articles` AS `a`',
self::qb()->select()->from(['user' => 'u'], ['articles' => 'a'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` , `' . DB_PREFIX . 'articles` AS `a`',
self::qb()->select()->from(['user' => 'u'])->from(['articles' => 'a'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` , `' . DB_PREFIX . 'articles` AS `a`',
self::qb()->select()->from('user as u, articles as a')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` , `' . DB_PREFIX . 'articles` AS `a` , `' . DB_PREFIX . 'products` AS `p` , `' . DB_PREFIX . 'orders` AS `o` , `' . DB_PREFIX . 'rating` AS `r`',
self::qb()->select()->from('user as u, articles as a', ['products' => 'p', 'orders' => 'o'], 'rating as r')->sql()
);
}
public function test_table()
{
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` , `' . DB_PREFIX . 'articles` AS `a`',
self::qb()->select()->table(['user' => 'u'])->table(['articles' => 'a'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` , `' . DB_PREFIX . 'articles` AS `a`',
self::qb()->select()->table('user as u, articles as a')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` , `' . DB_PREFIX . 'articles` AS `a` , `' . DB_PREFIX . 'products` AS `p` , `' . DB_PREFIX . 'orders` AS `o` , `' . DB_PREFIX . 'rating` AS `r`',
self::qb()->select()->table('user as u, articles as a', ['products' => 'p', 'orders' => 'o'], 'rating as r')->sql()
);
}
public function test_from_string_as()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u`',
self::qb()->select()->from('user as u')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` , `' . DB_PREFIX . 'articles` AS `a`',
self::qb()->select()->from('user as u', 'articles as a')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` , `' . DB_PREFIX . 'articles` AS `a`',
self::qb()->select()->from('user as u')->from('articles as a')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` , `' . DB_PREFIX . 'articles` AS `a`',
self::qb()->select()->from('user as u, articles as a')->sql()
);
}
public function test_where_basic()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->where()->sql()
);
$this->assertFalse(
self::qb()->from()->where()->sql()
);
$this->assertFalse(
self::qb()->select()->from()->where()->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\'',
self::qb()->from('user')->where('id', 1)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\'',
self::qb()->from('user')->where('id', '1')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\'',
self::qb()->from('user')->where('id', '=', '1')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\'',
self::qb()->from('user')->where(['id', '=', 1])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\'',
self::qb()->from('user')->where(['id', 1])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` , `' . DB_PREFIX . 'articles` WHERE `u`.`id` = \'1\'',
self::qb()->from('user', 'articles')->where(['u.id', '=', 1])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\'',
self::qb()->from(['user' => 'u'])->where(['u.id', '=', 1])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' AND `u`.`gid` = \'4\'',
self::qb()->from(['user' => 'u'])->where(['u.id', '=', '1'], 'and', ['u.gid', '=', '4'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' OR `u`.`gid` = \'4\'',
self::qb()->from(['user' => 'u'])->where(['u.id', '=', '1'], 'or', ['u.gid', '=', '4'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' XOR `u`.`gid` = \'4\'',
self::qb()->from(['user' => 'u'])->where(['u.id', '=', '1'], 'xor', ['u.gid', '=', '4'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `id` = \'1\' AND `gid` = \'4\'',
self::qb()->from(['user' => 'u'])->where(['id' => '1', 'gid' => '4'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' AND `u`.`gid` = \'4\'',
self::qb()->from(['user' => 'u'])->where(['u.id' => '1', 'u.gid' => '4'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`gid` = \'4\'',
self::qb()->from(['user' => 'u'])->where(['u.id' => '', 'u.gid' => '4'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\'',
self::qb()->from(['user' => 'u'])->where(['u.id' => '1', 'u.gid' => ''])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` IS NULL',
self::qb()->from('user')->where('id', 'IS NULL')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` IS NOT NULL',
self::qb()->from('user')->where(['id', 'IS NOT NULL'])->sql()
);
}
public function test_where_like()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `name` LIKE \'test\'',
self::qb()->from('user')->where('name', 'like', 'test')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `name` LIKE \'test\'',
self::qb()->from('user')->where('name', 'LIKE', 'test')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `name` LIKE \'test%\'',
self::qb()->from('user')->where('name', 'like', 'test%')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `name` LIKE \'test%\'',
self::qb()->from('user')->where('name', 'like', 'test*')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `name` NOT LIKE \'test%\'',
self::qb()->from('user')->where('name', 'not like', 'test*')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `name` RLIKE \'(test|other)\'',
self::qb()->from('user')->where('name', 'rlike', '(test|other)')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `name` NOT RLIKE \'(test|other)\'',
self::qb()->from('user')->where('name', 'not rlike', '(test|other)')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` LIKE \'1%\' AND `u`.`gid` LIKE \'%4\'',
self::qb()->from(['user' => 'u'])->where(['u.id' => '1*', 'u.gid' => '*4'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` LIKE \'1%\' AND `u`.`gid` LIKE \'%4\'',
self::qb()->from(['user' => 'u'])->where(['u.id', '1*'], ['u.gid', '*4'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` LIKE \'1%\' AND `u`.`gid` LIKE \'%4\'',
self::qb()->from(['user' => 'u'])->where(['u.id', '1*'], ['u.gid' => '*4'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` LIKE \'%1%\' XOR `u`.`gid` NOT LIKE \'%4%\'',
self::qb()->from(['user' => 'u'])->where(['u.id', 'like', '%1%'], 'xor', ['u.gid', 'not like', '%4%'])->sql()
);
}
public function test_where_simplified_syntax()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\'',
self::qb()->from('user as u')->where('u.id = 1')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` > \'1\'',
self::qb()->from('user as u')->where('u.id > 1')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` > \'1\' AND `u`.`visits` < \'3\'',
self::qb()->from('user as u')->where('u.id > 1', 'u.visits < 3')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` > \'1\' AND `u`.`visits` < \'3\'',
self::qb()->from('user as u')->where('u.id > 1')->where('u.visits < 3')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` > \'1\' AND `u`.`visits` < \'3\'',
self::qb()->from('user as u')->where('u.id > 1, u.visits < 3')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` > \'1\' AND `u`.`visits` < \'3\'',
self::qb()->from('user as u')->where(['u.id > 1', 'u.visits < 3'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` IS NULL',
self::qb()->from('user as u')->where('u.id is null')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` IS NULL',
self::qb()->from('user as u')->where('u.id IS NULL')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` IS NOT NULL',
self::qb()->from('user as u')->where('u.id is not null')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` IS NOT NULL',
self::qb()->from('user as u')->where('u.id IS NOT NULL')->sql()
);
}
public function test_where_null()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` IS NULL',
self::qb()->from('user as u')->where_null('u.id')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` IS NOT NULL',
self::qb()->from('user as u')->where_not_null('u.id')->sql()
);
}
public function test_where_or()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' OR `u`.`gid` = \'4\'',
self::qb()->from('user as u')->where('u.id = 1')->where_or('u.gid = 4')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' OR `u`.`gid` = \'4\' OR `u`.`visits` < \'4\'',
self::qb()->from('user as u')->where('u.id = 1')->where_or('u.gid = 4')->where_or('u.visits < 4')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` > \'1\' OR `u`.`visits` < \'3\'',
self::qb()->from('user as u')->where_or('u.id > 1, u.visits < 3')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` > \'1\' OR `u`.`visits` < \'3\'',
self::qb()->from('user as u')->where_or(['u.id > 1, u.visits < 3'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` > \'1\' OR `u`.`visits` < \'3\'',
self::qb()->from('user as u')->where_or('u.id > 1', 'u.visits < 3')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` > \'1\' OR `u`.`visits` < \'3\'',
self::qb()->from('user as u')->where_or('u.id > 1')->where_or('u.visits < 3')->sql()
);
}
public function test_whereid()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->whereid()->sql()
);
$this->assertFalse(
self::qb()->from()->whereid()->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\'',
self::qb()->from('user')->whereid(1)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\'',
self::qb()->from('user')->whereid(1, '')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `uid` = \'1\'',
self::qb()->from('user')->whereid(1, 'uid')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `u`.`id` = \'1\'',
self::qb()->from('user')->whereid(1, 'u.id')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` IN(1,2,3)',
self::qb()->from('user')->whereid([1, 2, 3])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `uid` IN(1,2,3)',
self::qb()->from('user')->whereid([1, 2, 3], 'uid')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `u`.`id` IN(1,2,3)',
self::qb()->from('user')->whereid([1, 2, 3], 'u.id')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` IN(1,2,3)',
self::qb()->from('user')->whereid(1, 2, 3)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `uid` IN(1,2,3)',
self::qb()->from('user')->whereid(1, 2, 3, 'uid')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `u`.`id` IN(1,2,3)',
self::qb()->from('user')->whereid(1, 2, 3, 'u.id')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\'',
self::qb()->from('user')->where(1)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\'',
self::qb()->from('user')->where(1, '')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` IN(1,2,3)',
self::qb()->from('user')->where([1, 2, 3])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `uid` IN(1,2,3)',
self::qb()->from('user')->where('uid', [1, 2, 3])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `uid` IN(1,2,3)',
self::qb()->from('user')->where(['uid', [1, 2, 3]])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `u`.`id` IN(1,2,3)',
self::qb()->from('user')->where('u.id', [1, 2, 3])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `u`.`id` IN(1,2,3)',
self::qb()->from('user')->where(['u.id', [1, 2, 3]])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `uid` IN(1,2,3)',
self::qb()->from('user')->where(['uid' => [1, 2, 3]])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `u`.`id` IN(1,2,3)',
self::qb()->from('user')->where(['u.id' => [1, 2, 3]])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `uid` IN(1,2,3) AND `pid` IN(4,5,6)',
self::qb()->from('user')->where(['uid' => [1, 2, 3]])->where(['pid' => [4, 5, 6]])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `uid` IN(1,2,3) AND `uid` IN(4,5,6)',
self::qb()->from('user')->where(['uid' => [1, 2, 3]])->where(['uid' => [4, 5, 6]])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `u`.`id` IN(1,2,3)',
self::qb()->from('user')->where(['u.id' => [1, 2, 3]])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `u`.`id` IN(1,2,3) AND `u`.`pid` IN(4,5,6)',
self::qb()->from('user')->where(['u.id' => [1, 2, 3]])->where(['u.pid' => [4, 5, 6]])->sql()
);
}
public function test_where_in()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user`',
self::qb()->from('user')->where('product_id', 'in', '')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user`',
self::qb()->from('user')->where('product_id', 'in', ['', '', ''])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `product_id` IN(\'0\')',
self::qb()->from('user')->where('product_id', 'IN', '0')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `product_id` IN(\'`\')',
self::qb()->from('user')->where('product_id', 'IN', '`')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `product_id` IN(0)',
self::qb()->from('user')->where('product_id', 'IN', 0)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `product_id` IN(1)',
self::qb()->from('user')->where('product_id', 'IN', 1)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `product_id` IN(1)',
self::qb()->from('user')->where('product_id', 'IN', [1])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `product_id` IN(1,2,3)',
self::qb()->from('user')->where('product_id', 'in', [1, 2, 3])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `product_id` IN(1,2,3)',
self::qb()->from('user')->where('product_id', 'IN', [1, 2, 3])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `product_id` NOT IN(1,2,3)',
self::qb()->from('user')->where('product_id', 'NOT IN', [1, 2, 3])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\'',
self::qb()->from('user')->where(1)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` IN(1,2,3)',
self::qb()->from('user')->where(1, 2, 3)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` IN(1,2,3)',
self::qb()->from('user')->where([1, 2, 3])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` IN(1,2,3)',
self::qb()->from('user')->where('id', [1, 2, 3])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` IN(1,2,3)',
self::qb()->from('user')->where(['id' => [1, 2, 3]])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `product_id` IN(1,2,3)',
self::qb()->from('user')->where('product_id', [1, 2, 3])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `product_id` IN(1,2,3)',
self::qb()->from('user')->where(['product_id' => [1, 2, 3]])->sql()
);
}
public function test_where_complex()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'2\' AND `gid` = \'1\' AND `sid` = \'3\' AND `pid` = \'4\' AND `hid` = \'5\' AND `mid` = \'6\' AND `rank` IS NULL',
self::qb()->from('user')->where('id = 2', ['gid', 1], ['sid', '=', '3'], ['pid' => 4, 'hid' => 5, 'mid' => 6], ['rank', 'IS NULL'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'2\' OR `gid` = \'1\' OR `sid` = \'3\' OR `pid` = \'4\' OR `hid` = \'5\' OR `mid` = \'6\' OR `rank` IS NULL',
self::qb()->from('user')->where_or('id = 2', ['gid', 1], ['sid', '=', '3'], ['pid' => 4, 'hid' => 5, 'mid' => 6], ['rank', 'IS NULL'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' AND `u`.`gid` > \'2\' AND `u`.`name` LIKE \'%hello%\' AND `u`.`pid` = \'4\' AND `u`.`hid` = \'5\' AND `u`.`mid` = \'6\' AND `u`.`id` IS NULL',
self::qb()->from('user as u')->where(['u.id', 1], 'u.gid > 2', ['u.name', 'like', '*hello*'], ['u.pid' => 4, 'u.hid' => 5, 'u.mid' => 6], ['u.id', 'IS NULL'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' OR `u`.`gid` > \'2\' OR `u`.`name` LIKE \'%hello%\' OR `u`.`pid` = \'4\' OR `u`.`hid` = \'5\' OR `u`.`mid` = \'6\' OR `u`.`id` IS NULL',
self::qb()->from('user as u')->where_or(['u.id', 1], 'u.gid > 2', ['u.name', 'like', '*hello*'], ['u.pid' => 4, 'u.hid' => 5, 'u.mid' => 6], ['u.id', 'IS NULL'])->sql()
);
}
public function test_where_raw()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE id BETWEEN 1 AND 5',
self::qb()->from('user')->where_raw('id BETWEEN 1 AND 5')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `name` = ""',
self::qb()->from('user')->where_raw('`name` = ""')->sql()
);
}
public function test_where_between()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` BETWEEN \'1\' AND \'5\'',
self::qb()->from('user')->where_between('id', 1, 5)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` BETWEEN \'1\' AND \'5\'',
self::qb()->from('user as u')->where_between('u.id', 1, 5)->sql()
);
}
public function test_join()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`id`',
self::qb()->select()->from('user as u')->join(['articles' => 'a'], ['u.id' => 'a.id'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`id`',
self::qb()->select()->from('user as u')->join('articles as a', 'u.id = a.id')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`id`',
self::qb()->select()->from('user as u')->join('articles as a', 'u.id = a.id ')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`id`',
self::qb()->select()->from('user as u')->join('articles as a', ' u.id = a.id ')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` LEFT JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`id`',
self::qb()->select()->from('user as u')->left_join('articles as a', 'u.id = a.id')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` RIGHT JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`id`',
self::qb()->select()->from('user as u')->right_join('articles as a', 'u.id = a.id')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` INNER JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`id`',
self::qb()->select()->from('user as u')->inner_join('articles as a', 'u.id = a.id')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` INNER JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`id`',
self::qb()->select()->from('user as u')->join('articles as a', 'u.id = a.id', 'inner')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` INNER JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`user_id` AND `u`.`order_id` = `a`.`order_id`',
self::qb()->select()->from('user as u')->join('articles as a', ['u.id = a.user_id', 'u.order_id = a.order_id'], 'inner')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` INNER JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`user_id` AND `u`.`order_id` = `a`.`order_id`',
self::qb()->select()->from('user as u')->join('articles as a', 'u.id = a.user_id and u.order_id = a.order_id', 'inner')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` INNER JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`id` INNER JOIN `' . DB_PREFIX . 'blogs` AS `b` ON `u`.`id` = `b`.`id`',
self::qb()->select()->from('user as u')->inner_join('articles as a', 'u.id = a.id')->inner_join('blogs as b', 'u.id = b.id')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` INNER JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`id` INNER JOIN `' . DB_PREFIX . 'blogs` AS `b` ON `u`.`id` = `b`.`id` AND `u`.`id2` = `b`.`id2` AND `u`.`id3` = `b`.`id3`',
self::qb()->select()->from('user as u')->inner_join('articles as a', 'u.id = a.id')->inner_join('blogs as b', ['u.id = b.id', 'u.id2 = b.id2', 'u.id3 = b.id3'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` INNER JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`id` INNER JOIN `' . DB_PREFIX . 'blogs` AS `b` ON `u`.`id` = `b`.`id` AND `u`.`id2` = `b`.`id2` AND `u`.`id3` = `b`.`id3`',
self::qb()->select()->from('user as u')->inner_join('articles as a', 'u.id = a.id')->inner_join('blogs as b', ['u.id' => 'b.id', 'u.id2' => 'b.id2', 'u.id3' => 'b.id3'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` LEFT JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`id`',
self::qb()->select()->from('user as u')->left_join_raw('`' . db('articles') . '` AS `a` ON `u`.`id` = `a`.`id`')->sql()
);
}
public function test_group_by()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->group_by()->sql()
);
$this->assertFalse(
self::qb()->from()->where()->group_by()->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\' GROUP BY `gid`',
self::qb()->from('user')->where(['id', '=', 1])->group_by('gid')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `id` = \'1\' GROUP BY `u`.`id`',
self::qb()->from('user as u')->whereid(1)->group_by('u.id')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `id` = \'1\' GROUP BY `u`.`id` , `u`.`gid`',
self::qb()->from('user as u')->whereid(1)->group_by('u.id', 'u.gid')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `id` = \'1\' GROUP BY `u`.`id` , `u`.`gid`',
self::qb()->from('user as u')->whereid(1)->group_by('u.id')->group_by('u.gid')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `id` = \'1\' GROUP BY `u`.`id` , `u`.`gid`',
self::qb()->from('user as u')->whereid(1)->group_by('u.id, u.gid')->sql()
);
}
public function test_having()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->having()->sql()
);
$this->assertFalse(
self::qb()->from()->where()->group_by()->having()->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\' GROUP BY `gid` HAVING `gid` = \'4\'',
self::qb()->from('user')->where(['id', '=', 1])->group_by('gid')->having(['gid', '=', 4])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' GROUP BY `u`.`gid` HAVING `u`.`gid` = \'4\'',
self::qb()->from(['user' => 'u'])->where(['u.id', '=', 1])->group_by('u.gid')->having(['u.gid', '=', 4])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' GROUP BY `u`.`gid` HAVING `u`.`gid` = \'4\' AND `u`.`visits` < \'4\'',
self::qb()->from(['user' => 'u'])->where(['u.id', '=', 1])->group_by('u.gid')->having(['u.gid', '=', 4], ['u.visits', '<', 4])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' GROUP BY `u`.`gid` HAVING `u`.`gid` = \'4\' AND `u`.`visits` < \'4\'',
self::qb()->from('user as u')->where('u.id = 1')->group_by('u.gid')->having('u.gid = 4')->having('u.visits < 4')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' GROUP BY `u`.`gid` HAVING `u`.`gid` = \'4\' AND `u`.`visits` < \'4\'',
self::qb()->from('user as u')->where('u.id = 1')->group_by('u.gid')->having('u.gid = 4, u.visits < 4')->sql()
);
}
public function test_order_by()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->order_by()->sql()
);
$this->assertFalse(
self::qb()->from()->where()->having()->group_by()->order_by()->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` ORDER BY `id` DESC',
self::qb()->from('user')->order_by(['id' => 'desc'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` ORDER BY `u`.`id` ASC',
self::qb()->from(['user' => 'u'])->order_by('u.id')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` ORDER BY `u`.`id` ASC , `u`.`gid` DESC',
self::qb()->from('user as u')->order_by('u.id', 'u.gid desc')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` ORDER BY `u`.`id` ASC',
self::qb()->from('user as u')->order_by('u.id', 'asc')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` ORDER BY `u`.`id` ASC',
self::qb()->from('user as u')->order_by(['u.id', 'asc'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` ORDER BY `u`.`id` ASC , `u`.`gid` DESC',
self::qb()->from('user as u')->order_by(['u.id', 'asc'], ['u.gid', 'desc'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` ORDER BY `u`.`id` ASC , `u`.`gid` DESC',
self::qb()->from('user as u')->order_by(['u.id' => 'asc'], ['u.gid', 'desc'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` ORDER BY `u`.`id` ASC , `u`.`gid` DESC',
self::qb()->from('user as u')->order_by(['u.id', 'asc'], ['u.gid desc'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` ORDER BY `u`.`id` ASC , `u`.`gid` DESC',
self::qb()->from('user as u')->order_by('u.id', 'asc')->order_by('u.gid desc')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` ORDER BY `u`.`id` ASC , `u`.`gid` DESC',
self::qb()->from('user as u')->order_by(['u.id', 'asc'])->order_by(['u.gid desc'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` ORDER BY `u`.`id` ASC , `u`.`gid` DESC',
self::qb()->from('user as u')->order_by('u.id asc, u.gid desc')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` ORDER BY `u`.`id` ASC , `u`.`gid` DESC',
self::qb()->from('user as u')->order_by(['u.id asc, u.gid desc'])->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' GROUP BY `u`.`gid` HAVING `u`.`gid` = \'4\' ORDER BY `u`.`id` ASC , `u`.`gid` DESC',
self::qb()->from('user as u')->where('u.id = 1')->group_by('u.gid')->having('u.gid = 4')->order_by('u.id', 'u.gid desc')->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' GROUP BY `u`.`gid` HAVING `u`.`gid` = \'4\' ORDER BY `u`.`id` ASC , `u`.`gid` DESC',
self::qb()->from('user as u')->where('u.id = 1')->group_by('u.gid')->having('u.gid = 4')->order_by('u.id')->order_by('u.gid desc')->sql()
);
}
public function test_limit()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->limit()->sql()
);
$this->assertFalse(
self::qb()->from()->limit()->sql()
);
$this->assertFalse(
self::qb()->from()->where()->having()->group_by()->order_by()->limit()->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` LIMIT 10',
self::qb()->from('user')->limit(10)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` LIMIT 20, 5',
self::qb()->from('user')->limit(5, 20)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\' GROUP BY `gid` HAVING `gid` = \'4\' ORDER BY `id` DESC LIMIT 10',
self::qb()->from('user')->where(['id', '=', 1])->group_by('gid')->having(['gid', '=', 4])->order_by(['id' => 'desc'])->limit(10)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' GROUP BY `u`.`gid` HAVING `u`.`gid` = \'4\' ORDER BY `u`.`id` ASC LIMIT 20, 5',
self::qb()->from(['user' => 'u'])->where(['u.id', '=', 1])->group_by('u.gid')->having(['u.gid', '=', 4])->order_by('u.id')->limit(5, 20)->sql()
);
}
// Testing that changing order of method calls not changing result SQL
public function test_calls_ordering()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' GROUP BY `u`.`gid` HAVING `u`.`gid` = \'4\' ORDER BY `u`.`id` ASC LIMIT 20, 5',
self::qb()->from(['user' => 'u'])->where(['u.id', '=', 1])->group_by('u.gid')->having(['u.gid', '=', 4])->order_by('u.id')->limit(5, 20)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' GROUP BY `u`.`gid` HAVING `u`.`gid` = \'4\' ORDER BY `u`.`id` ASC LIMIT 20, 5',
self::qb()->from(['user' => 'u'])->where(['u.id', '=', 1])->group_by('u.gid')->having(['u.gid', '=', 4])->order_by('u.id')->limit(5, 20)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`id` = \'1\' GROUP BY `u`.`gid` HAVING `u`.`gid` = \'4\' ORDER BY `u`.`id` ASC LIMIT 20, 5',
self::qb()->group_by('u.gid')->where(['u.id', '=', 1])->order_by('u.id')->limit(5, 20)->from(['user' => 'u'])->having(['u.gid', '=', 4])->sql()
);
}
public function test_delete()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->delete()
);
$this->assertEquals(
'DELETE FROM `' . DB_PREFIX . 'user`',
self::qb()->from('user')->delete(null, $sql = true)
);
$this->assertEquals(
'DELETE FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\'',
self::qb()->from('user')->whereid(1)->delete(null, $sql = true)
);
$this->assertEquals(
'DELETE FROM `' . DB_PREFIX . 'user` WHERE `id` = \'1\'',
self::qb()->from('user')->delete(1, $sql = true)
);
$this->assertEquals(
'DELETE FROM `' . DB_PREFIX . 'user` WHERE `id` IN(1,2,3)',
self::qb()->from('user')->delete([1, 2, 3], $sql = true)
);
$this->assertEquals(
'DELETE FROM `' . DB_PREFIX . 'user` WHERE `id` IN(1,2,3)',
self::qb()->from('user')->whereid([1, 2, 3])->delete(null, $sql = true)
);
$this->assertEquals(
'DELETE FROM `' . DB_PREFIX . 'user` WHERE `uid` IN(1,2,3)',
self::qb()->from('user')->whereid([1, 2, 3], 'uid')->delete(null, $sql = true)
);
$this->assertEquals(
'DELETE FROM `' . DB_PREFIX . 'user` WHERE `u`.`id` IN(1,2,3)',
self::qb()->from('user as u')->whereid([1, 2, 3], 'u.id')->delete(null, $sql = true)
);
}
public function test_increment()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->increment(null, null, $sql = true)
);
$this->assertFalse(
self::qb()->increment('visits', null, $sql = true)
);
$this->assertFalse(
self::qb()->increment('visits', 1, $sql = true)
);
$this->assertFalse(
self::qb()->increment('visits', 5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` + 1',
self::qb()->table('user')->increment('visits', null, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` + 1',
self::qb()->table('user')->increment('visits', 1, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` + 5',
self::qb()->table('user')->increment('visits', 5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` + 500',
self::qb()->table('user')->increment('visits', 500, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` - 5',
self::qb()->table('user')->increment('visits', -5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` + 1 WHERE `id` = \'1\'',
self::qb()->table('user')->whereid(1)->increment('visits', null, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` + 1 WHERE `id` = \'1\'',
self::qb()->table('user')->whereid(1)->increment('visits', 1, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` + 5 WHERE `id` = \'1\'',
self::qb()->table('user')->whereid(1)->increment('visits', 5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` - 5 WHERE `id` = \'1\'',
self::qb()->table('user')->whereid(1)->increment('visits', -5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` + 5 LIMIT 1',
self::qb()->table('user')->limit(1)->increment('visits', 5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` + 5 WHERE `id` = \'1\' LIMIT 1',
self::qb()->table('user')->whereid(1)->limit(1)->increment('visits', 5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `u`.`visits` = `u`.`visits` + 5 WHERE `u`.`id` = \'1\' LIMIT 1',
self::qb()->table('user as u')->where('u.id', 1)->limit(1)->increment('u.visits', 5, $sql = true)
);
}
public function test_decrement()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->decrement(null, null, $sql = true)
);
$this->assertFalse(
self::qb()->decrement('visits', null, $sql = true)
);
$this->assertFalse(
self::qb()->decrement('visits', 1, $sql = true)
);
$this->assertFalse(
self::qb()->decrement('visits', 5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` - 1',
self::qb()->table('user')->decrement('visits', null, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` - 1',
self::qb()->table('user')->decrement('visits', 1, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` - 5',
self::qb()->table('user')->decrement('visits', 5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` - 500',
self::qb()->table('user')->decrement('visits', 500, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` + 5',
self::qb()->table('user')->decrement('visits', -5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` - 1 WHERE `id` = \'1\'',
self::qb()->table('user')->whereid(1)->decrement('visits', null, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` - 1 WHERE `id` = \'1\'',
self::qb()->table('user')->whereid(1)->decrement('visits', 1, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` - 5 WHERE `id` = \'1\'',
self::qb()->table('user')->whereid(1)->decrement('visits', 5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` + 5 WHERE `id` = \'1\'',
self::qb()->table('user')->whereid(1)->decrement('visits', -5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` - 5 LIMIT 1',
self::qb()->table('user')->limit(1)->decrement('visits', 5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `visits` = `visits` - 5 WHERE `id` = \'1\' LIMIT 1',
self::qb()->table('user')->whereid(1)->limit(1)->decrement('visits', 5, $sql = true)
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'user` SET `u`.`visits` = `u`.`visits` - 5 WHERE `u`.`id` = \'1\' LIMIT 1',
self::qb()->table('user as u')->where('u.id', 1)->limit(1)->decrement('u.visits', 5, $sql = true)
);
}
public function test_avg()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->avg(null, $sql = true)
);
$this->assertFalse(
self::qb()->avg('', $sql = true)
);
$this->assertFalse(
self::qb()->avg(false, $sql = true)
);
$this->assertEquals(
'SELECT AVG(`id`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->avg(null, $sql = true)
);
$this->assertEquals(
'SELECT AVG(`id`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->avg('id', $sql = true)
);
$this->assertEquals(
'SELECT AVG(`visits`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->avg('visits', $sql = true)
);
$this->assertEquals(
'SELECT AVG(`u`.`visits`) FROM `' . DB_PREFIX . 'user` AS `u`',
self::qb()->table('user as u')->avg('u.visits', $sql = true)
);
$this->assertEquals(
'SELECT AVG(`u`.`visits`) FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`group` = \'3\'',
self::qb()->table('user as u')->where('u.group', 3)->avg('u.visits', $sql = true)
);
}
public function test_min()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->min(null, $sql = true)
);
$this->assertFalse(
self::qb()->min('', $sql = true)
);
$this->assertFalse(
self::qb()->min(false, $sql = true)
);
$this->assertEquals(
'SELECT MIN(`id`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->min(null, $sql = true)
);
$this->assertEquals(
'SELECT MIN(`id`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->min('id', $sql = true)
);
$this->assertEquals(
'SELECT MIN(`visits`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->min('visits', $sql = true)
);
$this->assertEquals(
'SELECT MIN(`u`.`visits`) FROM `' . DB_PREFIX . 'user` AS `u`',
self::qb()->table('user as u')->min('u.visits', $sql = true)
);
$this->assertEquals(
'SELECT MIN(`u`.`visits`) FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`group` = \'3\'',
self::qb()->table('user as u')->where('u.group', 3)->min('u.visits', $sql = true)
);
}
public function test_max()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->max(null, $sql = true)
);
$this->assertFalse(
self::qb()->max('', $sql = true)
);
$this->assertFalse(
self::qb()->max(false, $sql = true)
);
$this->assertEquals(
'SELECT MAX(`id`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->max(null, $sql = true)
);
$this->assertEquals(
'SELECT MAX(`id`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->max('id', $sql = true)
);
$this->assertEquals(
'SELECT MAX(`visits`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->max('visits', $sql = true)
);
$this->assertEquals(
'SELECT MAX(`u`.`visits`) FROM `' . DB_PREFIX . 'user` AS `u`',
self::qb()->table('user as u')->max('u.visits', $sql = true)
);
$this->assertEquals(
'SELECT MAX(`u`.`visits`) FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`group` = \'3\'',
self::qb()->table('user as u')->where('u.group', 3)->max('u.visits', $sql = true)
);
}
public function test_sum()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertFalse(
self::qb()->sum(null, $sql = true)
);
$this->assertFalse(
self::qb()->sum('', $sql = true)
);
$this->assertFalse(
self::qb()->sum(false, $sql = true)
);
$this->assertEquals(
'SELECT SUM(`id`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->sum(null, $sql = true)
);
$this->assertEquals(
'SELECT SUM(`id`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->sum('id', $sql = true)
);
$this->assertEquals(
'SELECT SUM(`visits`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->sum('visits', $sql = true)
);
$this->assertEquals(
'SELECT SUM(`u`.`visits`) FROM `' . DB_PREFIX . 'user` AS `u`',
self::qb()->table('user as u')->sum('u.visits', $sql = true)
);
$this->assertEquals(
'SELECT SUM(`u`.`visits`) FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`group` = \'3\'',
self::qb()->table('user as u')->where('u.group', 3)->sum('u.visits', $sql = true)
);
}
public function test_count()
{
$this->assertFalse(
self::qb()->count(null, $sql = true)
);
$this->assertFalse(
self::qb()->count('', $sql = true)
);
$this->assertFalse(
self::qb()->count(false, $sql = true)
);
$this->assertEquals(
'SELECT COUNT(*) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->count(null, $sql = true)
);
$this->assertEquals(
'SELECT COUNT(`id`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->count('id', $sql = true)
);
$this->assertEquals(
'SELECT COUNT(`visits`) FROM `' . DB_PREFIX . 'user`',
self::qb()->table('user')->count('visits', $sql = true)
);
$this->assertEquals(
'SELECT COUNT(`u`.`visits`) FROM `' . DB_PREFIX . 'user` AS `u`',
self::qb()->table('user as u')->count('u.visits', $sql = true)
);
$this->assertEquals(
'SELECT COUNT(`u`.`visits`) FROM `' . DB_PREFIX . 'user` AS `u` WHERE `u`.`group` = \'3\'',
self::qb()->table('user as u')->where('u.group', 3)->count('u.visits', $sql = true)
);
}
public function test_compile_insert()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$data = ['user_id' => 1, 'date' => '1234567890', 'total_sum' => '19,12', 'name' => 'name'];
$this->assertEquals(
'INSERT INTO `' . DB_PREFIX . 'shop_orders` (`user_id`, `date`, `total_sum`, `name`) VALUES (\'1\', \'1234567890\', \'19,12\', \'name\')',
str_replace(PHP_EOL, '', self::qb()->compile_insert('shop_orders', $data))
);
$this->assertEquals(
'REPLACE INTO `' . DB_PREFIX . 'shop_orders` (`user_id`, `date`, `total_sum`, `name`) VALUES (\'1\', \'1234567890\', \'19,12\', \'name\')',
str_replace(PHP_EOL, '', self::qb()->compile_insert('shop_orders', $data, ['replace' => true]))
);
$this->assertEquals(
'INSERT IGNORE INTO `' . DB_PREFIX . 'shop_orders` (`user_id`, `date`, `total_sum`, `name`) VALUES (\'1\', \'1234567890\', \'19,12\', \'name\')',
str_replace(PHP_EOL, '', self::qb()->compile_insert('shop_orders', $data, ['ignore' => true]))
);
$this->assertEquals(
'INSERT INTO `' . DB_PREFIX . 'shop_orders` (`user_id`, `date`, `total_sum`, `name`) VALUES (\'1\', \'1234567890\', \'19,12\', \'name\') ON DUPLICATE KEY UPDATE `user_id` = VALUES(`user_id`), `date` = VALUES(`date`), `total_sum` = VALUES(`total_sum`), `name` = VALUES(`name`)',
str_replace(PHP_EOL, '', self::qb()->compile_insert('shop_orders', $data, ['on_duplicate_key_update' => true]))
);
}
public function test_compile_update()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$data = ['user_id' => 1, 'date' => '1234567890', 'total_sum' => '19,12', 'name' => 'name'];
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'shop_orders` SET `user_id` = \'1\', `date` = \'1234567890\', `total_sum` = \'19,12\', `name` = \'name\' WHERE id=1',
str_replace(PHP_EOL, '', self::qb()->compile_update('shop_orders', $data, 'id=1'))
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'shop_orders` SET `user_id` = \'1\', `date` = \'1234567890\', `total_sum` = \'19,12\', `name` = \'name\' WHERE id=1',
str_replace(PHP_EOL, '', self::qb()->compile_update('shop_orders', $data, '1'))
);
}
public function test_insert()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$data = ['user_id' => 1, 'date' => '1234567890', 'total_sum' => '19,12', 'name' => 'name'];
$this->assertEquals(
'INSERT INTO `' . DB_PREFIX . 'shop_orders` (`user_id`, `date`, `total_sum`, `name`) VALUES (\'1\', \'1234567890\', \'19,12\', \'name\')',
str_replace(PHP_EOL, '', self::qb()->table('shop_orders')->insert($data, ['sql' => true]))
);
}
public function test_insert_into()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'INSERT INTO `' . DB_PREFIX . 'stats_archive` (`user_id`) SELECT `user_id` FROM `' . DB_PREFIX . 'stats`',
str_replace(PHP_EOL, '', self::qb()->select('user_id')->from('stats')->insert_into('stats_archive', ['sql' => true]))
);
$this->assertEquals(
'INSERT INTO `' . DB_PREFIX . 'stats_archive` (`user_id` , `visits`) SELECT `user_id` , `visits` FROM `' . DB_PREFIX . 'stats` WHERE `id` > \'1234\'',
str_replace(PHP_EOL, '', self::qb()->select('user_id', 'visits')->from('stats')->where('id > 1234')->insert_into('stats_archive', ['sql' => true]))
);
}
public function test_update()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$data = ['user_id' => 1, 'date' => '1234567890', 'total_sum' => '19,12', 'name' => 'name'];
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'shop_orders` SET `user_id` = \'1\', `date` = \'1234567890\', `total_sum` = \'19,12\', `name` = \'name\' WHERE `id` = \'1\'',
str_replace(PHP_EOL, '', self::qb()->table('shop_orders')->whereid(1)->update($data, ['sql' => true]))
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'shop_orders` SET `user_id` = \'1\', `date` = \'1234567890\', `total_sum` = \'19,12\', `name` = \'name\' WHERE `id` >= \'1\'',
str_replace(PHP_EOL, '', self::qb()->table('shop_orders')->where('id >= 1')->update($data, ['sql' => true]))
);
$data = [
1 => ['id' => 1, 'name' => 'name1'],
2 => ['id' => 2, 'name' => 'name2'],
];
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'users` SET `name` = CASE WHEN `id` = \'1\' THEN \'name1\' WHEN `id` = \'2\' THEN \'name2\' ELSE `name` END WHERE `id` IN(\'1\',\'2\');',
trim(str_replace(PHP_EOL, ' ', self::qb()->table('users')->update($data, ['sql' => true])))
);
$data = [
1 => ['uid' => 1, 'name' => 'name1'],
2 => ['uid' => 2, 'name' => 'name2'],
];
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'users` SET `name` = CASE WHEN `uid` = \'1\' THEN \'name1\' WHEN `uid` = \'2\' THEN \'name2\' ELSE `name` END WHERE `uid` IN(\'1\',\'2\');',
trim(str_replace(PHP_EOL, ' ', self::qb()->table('users')->update($data, ['id' => 'uid', 'sql' => true])))
);
}
public function test_update_batch()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$data = [
1 => ['id' => 1, 'name' => 'name1'],
2 => ['id' => 2, 'name' => 'name2'],
];
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'users` SET `name` = CASE WHEN `id` = \'1\' THEN \'name1\' WHEN `id` = \'2\' THEN \'name2\' ELSE `name` END WHERE `id` IN(\'1\',\'2\');',
trim(str_replace(PHP_EOL, ' ', self::qb()->table('users')->update_batch('users', $data, null, $sql = true)))
);
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'users` SET `name` = CASE WHEN `id` = \'1\' THEN \'name1\' WHEN `id` = \'2\' THEN \'name2\' ELSE `name` END WHERE `id` IN(\'1\',\'2\');',
trim(str_replace(PHP_EOL, ' ', self::qb()->table('users')->update_batch('users', $data, 'id', $sql = true)))
);
$data = [
1 => ['uid' => 1, 'name' => 'name1'],
2 => ['uid' => 2, 'name' => 'name2'],
];
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'users` SET `name` = CASE WHEN `uid` = \'1\' THEN \'name1\' WHEN `uid` = \'2\' THEN \'name2\' ELSE `name` END WHERE `uid` IN(\'1\',\'2\');',
trim(str_replace(PHP_EOL, ' ', self::qb()->table('users')->update_batch('users', $data, 'uid', $sql = true)))
);
$data = [
1 => ['id' => 1, 'cat_id' => 11, 'name' => 'name1'],
2 => ['id' => 2, 'cat_id' => 22, 'name' => 'name2'],
];
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'users` SET `name` = CASE WHEN `id` = \'1\' AND `cat_id` = \'11\' THEN \'name1\' WHEN `id` = \'2\' AND `cat_id` = \'22\' THEN \'name2\' ELSE `name` END WHERE `id` IN(\'1\',\'2\') AND `cat_id` IN(\'11\',\'22\');',
trim(str_replace(PHP_EOL, ' ', self::qb()->table('users')->update_batch('users', $data, ['id', 'cat_id'], $sql = true)))
);
$data = [
1 => ['id' => 1, 'cat_id' => 11, 'name' => 'name1', 'desc' => 'desc1'],
2 => ['id' => 2, 'cat_id' => 22, 'name' => 'name2', 'desc' => 'desc2'],
];
$this->assertEquals(
'UPDATE `' . DB_PREFIX . 'users` SET `name` = CASE WHEN `id` = \'1\' AND `cat_id` = \'11\' THEN \'name1\' WHEN `id` = \'2\' AND `cat_id` = \'22\' THEN \'name2\' ELSE `name` END, `desc` = CASE WHEN `id` = \'1\' AND `cat_id` = \'11\' THEN \'desc1\' WHEN `id` = \'2\' AND `cat_id` = \'22\' THEN \'desc2\' ELSE `desc` END WHERE `id` IN(\'1\',\'2\') AND `cat_id` IN(\'11\',\'22\');',
trim(str_replace(PHP_EOL, ' ', self::qb()->table('users')->update_batch('users', $data, ['id', 'cat_id'], $sql = true)))
);
}
public function test_render_select()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT `id` , `name`',
self::qb()->select('id, name')->_render_select()
);
$this->assertEquals(
'SELECT `id` AS `uid` , `name` AS `uname`',
self::qb()->select('id as uid, name as uname')->_render_select()
);
}
public function test_render_from()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'FROM `' . DB_PREFIX . 'user`',
self::qb()->from('user')->_render_from()
);
$this->assertEquals(
'FROM `' . DB_PREFIX . 'user` AS `u`',
self::qb()->from('user as u')->_render_from()
);
$this->assertEquals(
'FROM `' . DB_PREFIX . 'user` AS `u` , `' . DB_PREFIX . 'stats` AS `s`',
self::qb()->from('user as u, stats as s')->_render_from()
);
}
public function test_render_where()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'WHERE `u`.`id` > \'5\'',
self::qb()->where('u.id > 5')->_render_where()
);
$this->assertEquals(
'WHERE `u`.`id` > \'5\' AND `u`.`id` < \'3\'',
self::qb()->where('u.id > 5', 'u.id < 3')->_render_where()
);
$this->assertEquals(
'WHERE `u`.`id` > \'5\' OR `u`.`id` < \'3\'',
self::qb()->where('u.id > 5')->where_or('u.id < 3')->_render_where()
);
}
public function test_render_joins()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'JOIN `' . DB_PREFIX . 'articles` AS `a` ON `u`.`id` = `a`.`id`',
self::qb()->join(['articles' => 'a'], ['u.id' => 'a.id'])->_render_joins()
);
}
public function test_render_order_by()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'ORDER BY `add_date` ASC',
self::qb()->order_by('add_date')->_render_order_by()
);
$this->assertEquals(
'ORDER BY `add_date` DESC',
self::qb()->order_by('add_date', 'desc')->_render_order_by()
);
$this->assertEquals(
'ORDER BY `u`.`add_date` ASC',
self::qb()->order_by('u.add_date')->_render_order_by()
);
$this->assertEquals(
'ORDER BY `u`.`add_date` ASC , `u`.`visits` DESC',
self::qb()->order_by('u.add_date asc', 'u.visits desc')->_render_order_by()
);
}
public function test_render_limit()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'LIMIT 10, 1',
self::qb()->limit(1, 10)->_render_limit()
);
}
public function test_subquery()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'(' . PHP_EOL . 'SELECT 1' . PHP_EOL . ')',
self::qb()->subquery('SELECT 1')
);
$this->assertEquals(
'(' . PHP_EOL . 'SELECT * FROM `' . DB_PREFIX . 'users`' . PHP_EOL . ')',
self::qb()->subquery('SELECT * FROM `' . DB_PREFIX . 'users`')
);
$this->assertEquals(
'(' . PHP_EOL . 'SELECT * FROM `' . DB_PREFIX . 'users`' . PHP_EOL . ')',
self::qb()->subquery(
self::qb()->from('users')
)
);
}
public function test_union()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$first = self::qb()->from('users')->where_null('first_name');
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'users` WHERE `last_name` IS NULL UNION (' . PHP_EOL . 'SELECT * FROM `' . DB_PREFIX . 'users` WHERE `first_name` IS NULL' . PHP_EOL . ')',
self::qb()->from('users')->where_null('last_name')->union($first)->sql()
);
}
public function test_union_all()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$first = self::qb()->from('users')->where_null('first_name');
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'users` WHERE `last_name` IS NULL UNION ALL (' . PHP_EOL . 'SELECT * FROM `' . DB_PREFIX . 'users` WHERE `first_name` IS NULL' . PHP_EOL . ')',
self::qb()->from('users')->where_null('last_name')->union_all($first)->sql()
);
}
public function test_where_any()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$first = self::qb()->select('gid')->from('groups')->where_not_null('active');
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'users` WHERE `group_id` = ANY (' . PHP_EOL . 'SELECT `gid` FROM `' . DB_PREFIX . 'groups` WHERE `active` IS NOT NULL' . PHP_EOL . ')',
self::qb()->from('users')->where_any('group_id', '=', $first)->sql()
);
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'users` WHERE `group_id` > ANY (' . PHP_EOL . 'SELECT `gid` FROM `' . DB_PREFIX . 'groups` WHERE `active` IS NOT NULL' . PHP_EOL . ')',
self::qb()->from('users')->where_any('group_id', '>', $first)->sql()
);
}
public function test_where_all()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$first = self::qb()->select('gid')->from('groups')->where_not_null('active');
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'users` WHERE `group_id` > ALL (' . PHP_EOL . 'SELECT `gid` FROM `' . DB_PREFIX . 'groups` WHERE `active` IS NOT NULL' . PHP_EOL . ')',
self::qb()->from('users')->where_all('group_id', '>', $first)->sql()
);
}
public function test_where_exists()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$first = self::qb()->select('gid')->from('groups')->where_not_null('active');
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'users` WHERE EXISTS (' . PHP_EOL . 'SELECT `gid` FROM `' . DB_PREFIX . 'groups` WHERE `active` IS NOT NULL' . PHP_EOL . ')',
self::qb()->from('users')->where_exists($first)->sql()
);
}
public function test_where_not_exists()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$first = self::qb()->select('gid')->from('groups')->where_not_null('active');
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'users` WHERE NOT EXISTS (' . PHP_EOL . 'SELECT `gid` FROM `' . DB_PREFIX . 'groups` WHERE `active` IS NOT NULL' . PHP_EOL . ')',
self::qb()->from('users')->where_not_exists($first)->sql()
);
}
public function test_shared_lock()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'users` WHERE `id` > \'10\' LOCK IN SHARE MODE',
self::qb()->from('users')->where('id > 10')->shared_lock()->sql()
);
}
public function test_lock_for_update()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertEquals(
'SELECT * FROM `' . DB_PREFIX . 'users` WHERE `id` > \'10\' FOR UPDATE',
self::qb()->from('users')->where('id > 10')->lock_for_update()->sql()
);
}
public function test_split_by_comma()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertSame(
[['1', '2', '3']],
self::qb()->_split_by_comma(['1,2,3'])
);
}
public function test_ids_sql_from_array()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$this->assertSame(
[1 => 1, 2 => 2, 3 => 3],
self::qb()->_ids_sql_from_array([1, 2, 3])
);
}
public function test_is_where_all_numeric()
{
if ($this->_need_skip_test(__FUNCTION__)) {
return;
}
$data = ['id' => 1, 2, 3];
$this->assertFalse(
self::qb()->_is_where_all_numeric($data)
);
$data = [1, 2, 'id' => 3];
$this->assertFalse(
self::qb()->_is_where_all_numeric($data)
);
$data = [1, 2, 'id'];
$this->assertFalse(
self::qb()->_is_where_all_numeric($data)
);
$data = [1, 2, 3];
$this->assertTrue(
self::qb()->_is_where_all_numeric($data)
);
$data = [1, 2, ''];
$this->assertTrue(
self::qb()->_is_where_all_numeric($data)
);
$this->assertSame([1, 2], $data);
}
}