373 lines
14 KiB
PHP
373 lines
14 KiB
PHP
<?php
|
|
|
|
namespace Ozdemir\Datatables\Test;
|
|
|
|
use Ozdemir\Datatables\DB\SQLite;
|
|
use Ozdemir\Datatables\Datatables;
|
|
use PHPUnit\Framework\TestCase;
|
|
use Ozdemir\Datatables\Http\Request;
|
|
|
|
class DatatablesTest extends TestCase
|
|
{
|
|
|
|
protected $db;
|
|
protected $request;
|
|
|
|
private function customfunction($data)
|
|
{
|
|
return substr($data, 0, 3).'...';
|
|
}
|
|
|
|
public function setUp(): void
|
|
{
|
|
$sqlconfig = __DIR__.'/../fixtures/test.db';
|
|
$this->request = Request::create(array(), ['draw' => 1]);
|
|
|
|
$this->db = new Datatables(new SQLite($sqlconfig), $this->request);
|
|
}
|
|
|
|
public function tearDown(): void
|
|
{
|
|
unset($this->db);
|
|
}
|
|
|
|
public function testConstructor()
|
|
{
|
|
$this->assertInstanceOf(Datatables::class, $this->db);
|
|
}
|
|
|
|
public function testReturnsRecordCounts()
|
|
{
|
|
$this->db->query('select id as fid, name, surname, age from mytable where id > 3');
|
|
$datatables = $this->db->generate()->toArray();
|
|
|
|
$this->assertSame(8, $datatables['recordsTotal']);
|
|
$this->assertSame(8, $datatables['recordsFiltered']);
|
|
}
|
|
|
|
public function testReturnsDataFromABasicSql()
|
|
{
|
|
$this->db->query('select id as fid, name, surname, age from mytable');
|
|
|
|
$data = $this->db->generate()->toArray()['data'][0];
|
|
|
|
$this->assertSame("1", $data[0]);
|
|
$this->assertSame("John", $data[1]);
|
|
$this->assertStringContainsString('Doe', $data[2]);
|
|
}
|
|
|
|
public function testSetsColumnNamesFromAliases()
|
|
{
|
|
$this->db->query("select
|
|
film_id as fid,
|
|
title,
|
|
'description' as info,
|
|
release_year 'r_year',
|
|
film.rental_rate,
|
|
film.length as mins
|
|
from film");
|
|
|
|
$this->assertSame(['fid', 'title', 'info', 'r_year', 'rental_rate', 'mins'], $this->db->getColumns());
|
|
}
|
|
|
|
public function testHidesUnnecessaryColumnsFromOutput()
|
|
{
|
|
$this->db->query('select id as fid, name, surname, age from mytable');
|
|
$this->db->hide('fid');
|
|
$data = $this->db->generate()->toArray()['data']['2'];
|
|
|
|
$this->assertCount(3, $data);
|
|
$this->assertSame(['name', 'surname', 'age'], $this->db->getColumns());
|
|
}
|
|
|
|
public function testReturnsModifiedDataViaClosureFunction()
|
|
{
|
|
$this->db->query('select id as fid, name, surname, age from mytable');
|
|
|
|
$this->db->edit('name', function ($data) {
|
|
return strtolower($data['name']);
|
|
});
|
|
|
|
$this->db->edit('surname', function ($data) {
|
|
return $this->customfunction($data['surname']);
|
|
});
|
|
|
|
$data = $this->db->generate()->toArray()['data']['2'];
|
|
|
|
$this->assertSame('george', $data[1]);
|
|
$this->assertSame('Mar...', $data[2]);
|
|
}
|
|
|
|
public function testReturnsUsingSpecialMysqlWordsIfItIsEscaped()
|
|
{
|
|
$this->db->query('select name, surname as `default`, age from mytable');
|
|
|
|
$this->db->edit('default', function ($data) {
|
|
return $this->customfunction($data['default']);
|
|
});
|
|
|
|
$data = $this->db->generate()->toArray()['data']['2'];
|
|
|
|
$this->assertSame('Mar...', $data[1]);
|
|
}
|
|
|
|
public function testReturnsColumnNamesFromQueryThatIncludesASubqueryInSelectStatement()
|
|
{
|
|
$dt = $this->db->query("SELECT column_name,
|
|
(SELECT group_concat(cp.GRANTEE)
|
|
FROM COLUMN_PRIVILEGES cp
|
|
WHERE cp.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA
|
|
AND cp.TABLE_NAME = COLUMNS.TABLE_NAME
|
|
AND cp.COLUMN_NAME = COLUMNS.COLUMN_NAME)
|
|
privs
|
|
FROM COLUMNS
|
|
WHERE table_schema = 'mysql' AND table_name = 'user';");
|
|
|
|
$this->assertSame(['column_name', 'privs'], $dt->getColumns());
|
|
}
|
|
|
|
public function testReturnsColumnNamesFromQueryThatIncludesASubqueryInWhereStatement()
|
|
{
|
|
$dt = $this->db->query("SELECT column_name
|
|
FROM COLUMNS
|
|
WHERE table_schema = 'mysql' AND table_name = 'user'
|
|
and (SELECT group_concat(cp.GRANTEE)
|
|
FROM COLUMN_PRIVILEGES cp
|
|
WHERE cp.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA
|
|
AND cp.TABLE_NAME = COLUMNS.TABLE_NAME
|
|
AND cp.COLUMN_NAME = COLUMNS.COLUMN_NAME) is not null;");
|
|
$columns = $dt->getColumns();
|
|
|
|
$this->assertSame($columns[0], 'column_name');
|
|
}
|
|
|
|
public function testFiltersDataViaGlobalSearch()
|
|
{
|
|
$this->request->query->set('search', ['value' => 'doe']);
|
|
|
|
$this->request->query->set('columns', [
|
|
['data' => '0', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
['data' => '1', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
]);
|
|
|
|
$this->db->query('Select name, surname from mytable');
|
|
$datatables = $this->db->generate()->toArray();
|
|
|
|
$this->assertSame(11, $datatables['recordsTotal']);
|
|
$this->assertSame(2, $datatables['recordsFiltered']);
|
|
|
|
}
|
|
|
|
public function testSortsDataViaSorting()
|
|
{
|
|
$this->request->query->set('search', ['value' => '']);
|
|
$this->request->query->set('order', [['column' => '1', 'dir' => 'desc']]); //surname-desc
|
|
|
|
$this->request->query->set('columns', [
|
|
['data' => '0', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
['data' => '1', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
['data' => '2', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
]);
|
|
|
|
$this->db->query('Select name, surname, age from mytable');
|
|
$datatables = $this->db->generate()->toArray();
|
|
|
|
$this->assertSame(['Todd', 'Wycoff', '36'], $datatables['data'][0]);
|
|
}
|
|
|
|
public function testSortsExcludingHiddenColumns()
|
|
{
|
|
$this->request->query->set('search', ['value' => '']);
|
|
$this->request->query->set('order', [['column' => '1', 'dir' => 'asc']]); // age - asc
|
|
|
|
$this->request->query->set('columns', [
|
|
['data' => '0', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
['data' => '1', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
]);
|
|
|
|
$this->db->query('Select id as fid, name, surname, age from mytable');
|
|
$this->db->hide('fid');
|
|
$this->db->hide('surname');
|
|
$datatables = $this->db->generate()->toArray(); // only name and age visible
|
|
|
|
$this->assertSame(['Colin', '19'], $datatables['data'][0]);
|
|
}
|
|
|
|
public function testSortsExcludingHiddenColumnsObjectData()
|
|
{
|
|
$this->request->query->set('search', ['value' => '']);
|
|
$this->request->query->set('order', [['column' => '1', 'dir' => 'asc']]);
|
|
|
|
$this->request->query->set('columns', [
|
|
[
|
|
'data' => 'name',
|
|
'name' => '',
|
|
'searchable' => 'true',
|
|
'orderable' => 'true',
|
|
'search' => ['value' => ''],
|
|
],
|
|
['data' => 'age', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
]);
|
|
|
|
$this->db->query('Select id as fid, name, surname, age from mytable');
|
|
$this->db->hide('fid');
|
|
$this->db->hide('surname');
|
|
$datatables = $this->db->generate()->toArray(); // only name and age visible
|
|
|
|
$this->assertSame(['name' => 'Colin', 'age' => '19'], $datatables['data'][0]);
|
|
}
|
|
|
|
|
|
public function testReorderingColumnsDoesNotAffectOrdering()
|
|
{
|
|
$this->request->query->set('search', ['value' => '']);
|
|
$this->request->query->set('order', [['column' => '0', 'dir' => 'asc']]);
|
|
|
|
$this->request->query->set('columns', [
|
|
['data' => 'age', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
[
|
|
'data' => 'surname',
|
|
'name' => '',
|
|
'searchable' => 'true',
|
|
'orderable' => 'true',
|
|
'search' => ['value' => ''],
|
|
],
|
|
[
|
|
'data' => 'name',
|
|
'name' => '',
|
|
'searchable' => 'true',
|
|
'orderable' => 'true',
|
|
'search' => ['value' => ''],
|
|
],
|
|
]);
|
|
|
|
$this->db->query('Select id as fid, name, surname, age from mytable');
|
|
$this->db->hide('fid');
|
|
$datatables = $this->db->generate()->toArray();
|
|
|
|
$this->assertSame(['name' => 'Colin', 'surname' => 'McCoy', 'age' => '19'], $datatables['data'][0]);
|
|
}
|
|
|
|
public function testReorderingColumnsDoesNotAffectGlobalSearching()
|
|
{
|
|
$this->request->query->set('search', ['value' => 'Stephanie']);
|
|
$this->request->query->set('order', [['column' => '0', 'dir' => 'asc']]);
|
|
|
|
$this->request->query->set('columns', [
|
|
['data' => '2', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
['data' => '0', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
['data' => '1', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
]);
|
|
|
|
$this->db->query('Select id as fid, name, surname, age from mytable');
|
|
$this->db->hide('fid');
|
|
$datatables = $this->db->generate()->toArray();
|
|
|
|
$this->assertSame(['Stephanie', 'Skinner', '45'], $datatables['data'][0]);
|
|
}
|
|
|
|
public function testReorderingColumnsDoesNotAffectIndividualSearching()
|
|
{
|
|
$this->request->query->set('search', ['value' => '']);
|
|
$this->request->query->set('order', [['column' => '0', 'dir' => 'asc']]);
|
|
|
|
$this->request->query->set('columns', [
|
|
[
|
|
'data' => 'surname',
|
|
'name' => '',
|
|
'searchable' => 'true',
|
|
'orderable' => 'true',
|
|
'search' => ['value' => 'McCoy'],
|
|
],
|
|
[
|
|
'data' => 'age',
|
|
'name' => '',
|
|
'searchable' => 'true',
|
|
'orderable' => 'true',
|
|
'search' => ['value' => '19'],
|
|
],
|
|
[
|
|
'data' => 'name',
|
|
'name' => '',
|
|
'searchable' => 'true',
|
|
'orderable' => 'true',
|
|
'search' => ['value' => 'Colin'],
|
|
],
|
|
]);
|
|
|
|
$this->db->query('Select id as fid, name, surname, age from mytable');
|
|
$this->db->hide('fid');
|
|
$datatables = $this->db->generate()->toArray();
|
|
|
|
$this->assertSame(['name' => 'Colin', 'surname' => 'McCoy', 'age' => '19'], $datatables['data'][0]);
|
|
}
|
|
|
|
public function testCustomFilteringBetween()
|
|
{
|
|
$this->request->query->set('search', ['value' => '']);
|
|
$this->request->query->set('order', [['column' => '0', 'dir' => 'asc']]);
|
|
|
|
$this->request->query->set('columns', [
|
|
['data' => '0', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
['data' => '1', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
['data' => '2', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
]);
|
|
|
|
$this->db->query('Select id as fid, name, surname, age from mytable');
|
|
$this->db->filter('fid', function () {
|
|
return $this->between(4, 6);
|
|
});
|
|
|
|
$datatables = $this->db->generate()->toArray();
|
|
|
|
$this->assertSame(11, $datatables['recordsTotal']);
|
|
$this->assertSame(3, $datatables['recordsFiltered']);
|
|
}
|
|
|
|
public function testCustomFilteringWhereIn()
|
|
{
|
|
$this->request->query->set('search', ['value' => '']);
|
|
$this->request->query->set('order', [['column' => '0', 'dir' => 'asc']]);
|
|
|
|
$this->request->query->set('columns', [
|
|
['data' => '0', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
['data' => '1', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
['data' => '2', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
]);
|
|
|
|
$this->db->query('Select id as fid, name, surname, age from mytable');
|
|
$this->db->filter('fid', function () {
|
|
return $this->whereIn([5]);
|
|
});
|
|
|
|
$datatables = $this->db->generate()->toArray();
|
|
|
|
$this->assertSame(11, $datatables['recordsTotal']);
|
|
$this->assertSame(1, $datatables['recordsFiltered']);
|
|
$this->assertSame(['5', 'Ruby', 'Pickett', '28'], $datatables['data'][0]);
|
|
}
|
|
|
|
public function testReturnDefaultSearchWhenNull()
|
|
{
|
|
$this->request->query->set('search', ['value' => '']);
|
|
$this->request->query->set('order', [['column' => '0', 'dir' => 'asc']]);
|
|
|
|
$this->request->query->set('columns', [
|
|
['data' => '0', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '5']],
|
|
['data' => '1', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
['data' => '2', 'name' => '', 'searchable' => 'true', 'orderable' => 'true', 'search' => ['value' => '']],
|
|
]);
|
|
|
|
$this->db->query('Select id as fid, name, surname, age from mytable');
|
|
$this->db->filter('fid', function () {
|
|
//return $this->defaultFilter(); // when it is not defined, returns defaultFilter
|
|
});
|
|
|
|
$datatables = $this->db->generate()->toArray();
|
|
|
|
$this->assertSame(11, $datatables['recordsTotal']);
|
|
$this->assertSame(1, $datatables['recordsFiltered']);
|
|
$this->assertSame(['5', 'Ruby', 'Pickett', '28'], $datatables['data'][0]);
|
|
}
|
|
}
|