Skip to content

Commit 34710cf

Browse files
MauricioFauthMauricioFauth
committed
Merge PR #19686 Use native MySQL paging
Closes #19686 Signed-off-by: Maurício Meneghini Fauth <mauricio@mfauth.net>
2 parents b9726b7 + 8c62b1a commit 34710cf

6 files changed

Lines changed: 79 additions & 81 deletions

File tree

‎phpstan-baseline.neon‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5205,7 +5205,7 @@ parameters:
52055205
Use dependency injection instead\.$#
52065206
'''
52075207
identifier: staticMethod.deprecated
5208-
count: 3
5208+
count: 2
52095209
path: src/Database/Routines.php
52105210

52115211
-
@@ -5271,7 +5271,7 @@ parameters:
52715271
-
52725272
message: '#^Loose comparison via "\=\=" is not allowed\.$#'
52735273
identifier: equal.notAllowed
5274-
count: 3
5274+
count: 1
52755275
path: src/Database/Routines.php
52765276

52775277
-

‎psalm-baseline.xml‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3467,7 +3467,6 @@
34673467
<DeprecatedMethod>
34683468
<code><![CDATA[Config::getInstance()]]></code>
34693469
<code><![CDATA[Config::getInstance()]]></code>
3470-
<code><![CDATA[Config::getInstance()]]></code>
34713470
</DeprecatedMethod>
34723471
<InvalidArgument>
34733472
<code><![CDATA[$itemParamDir]]></code>

‎src/Controllers/Database/RoutinesController.php‎

Lines changed: 2 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -25,8 +25,6 @@
2525
use PhpMyAdmin\Util;
2626

2727
use function __;
28-
use function array_slice;
29-
use function count;
3028
use function htmlentities;
3129
use function htmlspecialchars;
3230
use function in_array;
@@ -457,8 +455,7 @@ public function __invoke(ServerRequest $request): Response
457455
$type = null;
458456
}
459457

460-
$items = Routines::getDetails($this->dbi, Current::$database, $type);
461-
$totalNumRoutines = count($items);
458+
$totalNumRoutines = Routines::getRoutineCount($this->dbi, Current::$database, $type);
462459
$pageSize = $this->config->settings['MaxRoutineList'];
463460
$pos = (int) $request->getParam('pos');
464461

@@ -481,7 +478,7 @@ public function __invoke(ServerRequest $request): Response
481478
return $this->response->response();
482479
}
483480

484-
$items = array_slice($items, $pos, $pageSize);
481+
$items = Routines::getDetails($this->dbi, Current::$database, $type, limit: $pageSize, offset: $pos);
485482

486483
$isAjax = $request->isAjax() && empty($_REQUEST['ajax_page_request']);
487484

‎src/Database/Routines.php‎

Lines changed: 20 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -20,9 +20,7 @@
2020

2121
use function __;
2222
use function _ngettext;
23-
use function array_column;
2423
use function array_merge;
25-
use function array_multisort;
2624
use function count;
2725
use function explode;
2826
use function htmlentities;
@@ -42,7 +40,6 @@
4240
use function stripos;
4341

4442
use const ENT_QUOTES;
45-
use const SORT_ASC;
4643

4744
/**
4845
* Functions for routine management.
@@ -1203,35 +1200,17 @@ public static function getDetails(
12031200
string $db,
12041201
string|null $which = null,
12051202
string $name = '',
1203+
int $limit = 0,
1204+
int $offset = 0,
12061205
): array {
1207-
if (! Config::getInstance()->selectedServer['DisableIS']) {
1208-
$query = QueryGenerator::getInformationSchemaRoutinesRequest(
1209-
$dbi->quoteString($db),
1210-
in_array($which, ['FUNCTION', 'PROCEDURE'], true) ? $which : null,
1211-
$name === '' ? null : $dbi->quoteString($name),
1212-
);
1213-
$routines = $dbi->fetchResultSimple($query);
1214-
} else {
1215-
$routines = [];
1216-
1217-
if ($which === 'FUNCTION' || $which == null) {
1218-
$query = 'SHOW FUNCTION STATUS WHERE `Db` = ' . $dbi->quoteString($db);
1219-
if ($name !== '') {
1220-
$query .= ' AND `Name` = ' . $dbi->quoteString($name);
1221-
}
1222-
1223-
$routines = $dbi->fetchResultSimple($query);
1224-
}
1225-
1226-
if ($which === 'PROCEDURE' || $which == null) {
1227-
$query = 'SHOW PROCEDURE STATUS WHERE `Db` = ' . $dbi->quoteString($db);
1228-
if ($name !== '') {
1229-
$query .= ' AND `Name` = ' . $dbi->quoteString($name);
1230-
}
1231-
1232-
$routines = array_merge($routines, $dbi->fetchResultSimple($query));
1233-
}
1234-
}
1206+
$query = QueryGenerator::getInformationSchemaRoutinesRequest(
1207+
$dbi->quoteString($db),
1208+
in_array($which, ['FUNCTION', 'PROCEDURE'], true) ? $which : null,
1209+
$name === '' ? null : $dbi->quoteString($name),
1210+
$limit,
1211+
$offset,
1212+
);
1213+
$routines = $dbi->fetchResultSimple($query);
12351214

12361215
$ret = [];
12371216
/** @var array{Name:string, Type:string, Definer:string, DTD_IDENTIFIER:string|null} $routine */
@@ -1244,13 +1223,19 @@ public static function getDetails(
12441223
);
12451224
}
12461225

1247-
// Sort results by name
1248-
$name = array_column($ret, 'name');
1249-
array_multisort($name, SORT_ASC, $ret);
1250-
12511226
return $ret;
12521227
}
12531228

1229+
public static function getRoutineCount(DatabaseInterface $dbi, string $db, string|null $which = null): int
1230+
{
1231+
$query = QueryGenerator::getInformationSchemaRoutinesCountRequest(
1232+
$dbi->quoteString($db),
1233+
in_array($which, ['FUNCTION', 'PROCEDURE'], true) ? $which : null,
1234+
);
1235+
1236+
return (int) $dbi->fetchValue($query);
1237+
}
1238+
12541239
public static function getFunctionDefinition(DatabaseInterface $dbi, string $db, string $name): string|null
12551240
{
12561241
$result = $dbi->fetchValue(

‎src/Query/Generator.php‎

Lines changed: 32 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -177,19 +177,13 @@ public static function getInformationSchemaRoutinesRequest(
177177
string $quotedDbName,
178178
string|null $routineType,
179179
string|null $quotedRoutineName,
180+
int $limit = 0,
181+
int $offset = 0,
180182
): string {
181183
$query = 'SELECT'
182-
. ' `ROUTINE_SCHEMA` AS `Db`,'
183184
. ' `SPECIFIC_NAME` AS `Name`,'
184185
. ' `ROUTINE_TYPE` AS `Type`,'
185186
. ' `DEFINER` AS `Definer`,'
186-
. ' `LAST_ALTERED` AS `Modified`,'
187-
. ' `CREATED` AS `Created`,'
188-
. ' `SECURITY_TYPE` AS `Security_type`,'
189-
. ' `ROUTINE_COMMENT` AS `Comment`,'
190-
. ' `CHARACTER_SET_CLIENT` AS `character_set_client`,'
191-
. ' `COLLATION_CONNECTION` AS `collation_connection`,'
192-
. ' `DATABASE_COLLATION` AS `Database Collation`,'
193187
. ' `DTD_IDENTIFIER`'
194188
. ' FROM `information_schema`.`ROUTINES`'
195189
. ' WHERE `ROUTINE_SCHEMA` ' . Util::getCollateForIS()
@@ -202,6 +196,36 @@ public static function getInformationSchemaRoutinesRequest(
202196
$query .= ' AND `SPECIFIC_NAME` = ' . $quotedRoutineName;
203197
}
204198

199+
$query .= ' ORDER BY `SPECIFIC_NAME`';
200+
201+
if ($limit > 0) {
202+
$query .= ' LIMIT ' . $limit;
203+
}
204+
205+
if ($offset > 0) {
206+
$query .= ' OFFSET ' . $offset;
207+
}
208+
209+
return $query;
210+
}
211+
212+
public static function getInformationSchemaRoutinesCountRequest(
213+
string $quotedDbName,
214+
string|null $routineType,
215+
string|null $quotedRoutineName = null,
216+
): string {
217+
$query = 'SELECT COUNT(*) AS `count`'
218+
. ' FROM `information_schema`.`ROUTINES`'
219+
. ' WHERE `ROUTINE_SCHEMA` ' . Util::getCollateForIS()
220+
. ' = ' . $quotedDbName;
221+
if ($routineType !== null) {
222+
$query .= " AND `ROUTINE_TYPE` = '" . $routineType . "'";
223+
}
224+
225+
if ($quotedRoutineName !== null) {
226+
$query .= ' AND `SPECIFIC_NAME` = ' . $quotedRoutineName;
227+
}
228+
205229
return $query;
206230
}
207231

‎tests/unit/Controllers/Database/RoutinesControllerTest.php‎

Lines changed: 23 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -39,21 +39,11 @@ public function testWithRoutines(): void
3939
['Grants for definer@localhost'],
4040
);
4141
$dummyDbi->addResult(
42-
'SHOW FUNCTION STATUS WHERE `Db` = \'test_db\'',
43-
[['test_db', 'test_func', 'FUNCTION', 'definer@localhost']],
44-
['Db', 'Name', 'Type', 'Definer'],
45-
);
46-
$dummyDbi->addResult(
47-
'SHOW PROCEDURE STATUS WHERE `Db` = \'test_db\'',
48-
[['test_db', 'test_proc', 'PROCEDURE', 'definer@localhost']],
49-
['Db', 'Name', 'Type', 'Definer'],
42+
"SELECT `SPECIFIC_NAME` AS `Name`, `ROUTINE_TYPE` AS `Type`, `DEFINER` AS `Definer`, `DTD_IDENTIFIER` FROM `information_schema`.`ROUTINES` WHERE `ROUTINE_SCHEMA` COLLATE utf8_bin = 'test_db' ORDER BY `SPECIFIC_NAME` LIMIT 250",
43+
[['test_db', 'test_func', 'FUNCTION', 'definer@localhost', null], ['test_db', 'test_proc', 'PROCEDURE', 'definer@localhost', null]],
44+
['Db', 'Name', 'Type', 'Definer', 'DTD_IDENTIFIER'],
5045
);
5146
$dummyDbi->addResult('SELECT @@lower_case_table_names', []);
52-
$dummyDbi->addResult(
53-
"SELECT `DEFINER` FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA COLLATE utf8_bin='test_db' AND SPECIFIC_NAME='test_func' AND ROUTINE_TYPE='FUNCTION';",
54-
[['definer@localhost']],
55-
['DEFINER'],
56-
);
5747
$dummyDbi->addResult(
5848
"SELECT `PRIVILEGE_TYPE` FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` WHERE GRANTEE='''definer''@''localhost''' AND PRIVILEGE_TYPE='CREATE ROUTINE'",
5949
[['CREATE ROUTINE']],
@@ -64,16 +54,6 @@ public function testWithRoutines(): void
6454
[['EXECUTE']],
6555
['PRIVILEGE_TYPE'],
6656
);
67-
$dummyDbi->addResult(
68-
'SHOW CREATE FUNCTION `test_db`.`test_func`',
69-
[['test_func', 'CREATE FUNCTION `test_func` (p INT) RETURNS int(11) BEGIN END']],
70-
['Function', 'Create Function'],
71-
);
72-
$dummyDbi->addResult(
73-
"SELECT `DEFINER` FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA COLLATE utf8_bin='test_db' AND SPECIFIC_NAME='test_proc' AND ROUTINE_TYPE='PROCEDURE';",
74-
[['definer@localhost']],
75-
['DEFINER'],
76-
);
7757
$dummyDbi->addResult(
7858
"SELECT `PRIVILEGE_TYPE` FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` WHERE GRANTEE='''definer''@''localhost''' AND PRIVILEGE_TYPE='CREATE ROUTINE'",
7959
[['CREATE ROUTINE']],
@@ -84,16 +64,16 @@ public function testWithRoutines(): void
8464
[['EXECUTE']],
8565
['PRIVILEGE_TYPE'],
8666
);
87-
$dummyDbi->addResult(
88-
'SHOW CREATE PROCEDURE `test_db`.`test_proc`',
89-
[['test_proc2', 'CREATE PROCEDURE `test_proc2` (p INT) BEGIN END']],
90-
['Procedure', 'Create Procedure'],
91-
);
9267
$dummyDbi->addResult(
9368
"SELECT `PRIVILEGE_TYPE` FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` WHERE GRANTEE='''definer''@''localhost''' AND PRIVILEGE_TYPE='CREATE ROUTINE'",
9469
[['CREATE ROUTINE']],
9570
['PRIVILEGE_TYPE'],
9671
);
72+
$dummyDbi->addResult(
73+
"SELECT COUNT(*) AS `count` FROM `information_schema`.`ROUTINES` WHERE `ROUTINE_SCHEMA` COLLATE utf8_bin = 'test_db'",
74+
[[2]],
75+
['count'],
76+
);
9777
// phpcs:enable
9878

9979
$dbi = $this->createDatabaseInterface($dummyDbi);
@@ -293,6 +273,8 @@ public function testWithRoutines(): void
293273
// phpcs:enable
294274

295275
self::assertSame($expected, $actual);
276+
$dummyDbi->assertAllQueriesConsumed();
277+
$dummyDbi->assertAllSelectsConsumed();
296278
}
297279

298280
public function testWithoutRoutines(): void
@@ -312,13 +294,22 @@ public function testWithoutRoutines(): void
312294
[['GRANT ALL PRIVILEGES ON *.* TO `definer`@`localhost`']],
313295
['Grants for definer@localhost'],
314296
);
315-
$dummyDbi->addResult('SHOW FUNCTION STATUS WHERE `Db` = \'test_db\'', [], ['Db', 'Name', 'Type', 'Definer']);
316-
$dummyDbi->addResult('SHOW PROCEDURE STATUS WHERE `Db` = \'test_db\'', [], ['Db', 'Name', 'Type', 'Definer']);
317297
$dummyDbi->addResult(
318298
"SELECT `PRIVILEGE_TYPE` FROM `INFORMATION_SCHEMA`.`USER_PRIVILEGES` WHERE GRANTEE='''definer''@''localhost''' AND PRIVILEGE_TYPE='CREATE ROUTINE'",
319299
[['CREATE ROUTINE']],
320300
['PRIVILEGE_TYPE'],
321301
);
302+
$dummyDbi->addResult('SELECT @@lower_case_table_names', []);
303+
$dummyDbi->addResult(
304+
"SELECT COUNT(*) AS `count` FROM `information_schema`.`ROUTINES` WHERE `ROUTINE_SCHEMA` COLLATE utf8_bin = 'test_db'",
305+
[[1]],
306+
['count'],
307+
);
308+
$dummyDbi->addResult(
309+
"SELECT `SPECIFIC_NAME` AS `Name`, `ROUTINE_TYPE` AS `Type`, `DEFINER` AS `Definer`, `DTD_IDENTIFIER` FROM `information_schema`.`ROUTINES` WHERE `ROUTINE_SCHEMA` COLLATE utf8_bin = 'test_db' ORDER BY `SPECIFIC_NAME` LIMIT 250",
310+
[],
311+
['Db', 'Name', 'Type', 'Definer', 'DTD_IDENTIFIER'],
312+
);
322313
// phpcs:enable
323314

324315
$dbi = $this->createDatabaseInterface($dummyDbi);
@@ -438,5 +429,7 @@ public function testWithoutRoutines(): void
438429
// phpcs:enable
439430

440431
self::assertSame($expected, $actual);
432+
$dummyDbi->assertAllQueriesConsumed();
433+
$dummyDbi->assertAllSelectsConsumed();
441434
}
442435
}

0 commit comments

Comments
 (0)