Skip to content

Commit bd815e3

Browse files
swissspidyclaude
swissspidy
and
claude
committed
db size: Collect the SQLite sizes in a single pass over dbstat
Every dbstat query walks all pages of the database file, so asking for the table size and the index size separately doubled that work for each table. One grouped query over dbstat joined to sqlite_master returns both, which also means the database-wide breakdown costs one query instead of two. The dbstat virtual table is not part of every SQLite build. Instead of letting a failed query pass a silent 0 B on to the output, fail with a message that names the missing extension. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01WA7GLFtiJMyt4ZwaDq194v
1 parent 5362d5a commit bd815e3

2 files changed

Lines changed: 63 additions & 25 deletions

File tree

‎src/DB_Command.php‎

Lines changed: 11 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1301,19 +1301,10 @@ public function size( $args, $assoc_args ) {
13011301

13021302
// Get the table size.
13031303
if ( $is_sqlite ) {
1304-
$data_bytes = (int) $wpdb->get_var(
1305-
$wpdb->prepare(
1306-
'SELECT SUM(pgsize) as size_in_bytes FROM dbstat where name = %s LIMIT 1',
1307-
$table_name
1308-
)
1309-
);
1304+
$sqlite_sizes = $this->sqlite_size_breakdown( $table_name );
13101305

1311-
$index_bytes = (int) $wpdb->get_var(
1312-
$wpdb->prepare(
1313-
"SELECT SUM(pgsize) as size_in_bytes FROM dbstat where name IN ( SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = %s )",
1314-
$table_name
1315-
)
1316-
);
1306+
$data_bytes = $sqlite_sizes['data'];
1307+
$index_bytes = $sqlite_sizes['index'];
13171308
} else {
13181309
$table_sizes = $wpdb->get_row(
13191310
$wpdb->prepare(
@@ -1345,9 +1336,14 @@ public function size( $args, $assoc_args ) {
13451336
$db_path = $this->get_sqlite_db_path();
13461337
$db_name = $db_path ? basename( $db_path ) : '';
13471338

1348-
// Unlike the file size, this does not cover overhead such as free pages.
1349-
$data_bytes = $show_details ? $this->sqlite_type_size( 'table' ) : 0;
1350-
$index_bytes = $show_details ? $this->sqlite_type_size( 'index' ) : 0;
1339+
// Unlike the file size, these do not cover overhead such as free pages.
1340+
$sqlite_sizes = $show_details ? $this->sqlite_size_breakdown() : [
1341+
'data' => 0,
1342+
'index' => 0,
1343+
];
1344+
1345+
$data_bytes = $sqlite_sizes['data'];
1346+
$index_bytes = $sqlite_sizes['index'];
13511347
} else {
13521348
$db_sizes = $wpdb->get_row(
13531349
$wpdb->prepare(

‎src/DB_Command_SQLite.php‎

Lines changed: 52 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -553,20 +553,62 @@ protected function sqlite_size() {
553553
}
554554

555555
/**
556-
* Get the size of all SQLite objects of a given type.
556+
* Get the data and the index size of the SQLite database, or of a single table.
557557
*
558-
* @param string $type Object type, either `table` or `index`.
559-
* @return int Size in bytes.
558+
* Both sizes are collected in a single pass over `dbstat`, which walks every
559+
* page of the database file.
560+
*
561+
* @param string $table_name Optional. Table to limit the sizes to. Defaults to the whole database.
562+
* @return array{data: int, index: int} Sizes in bytes.
560563
*/
561-
protected function sqlite_type_size( $type ) {
564+
protected function sqlite_size_breakdown( $table_name = '' ) {
562565
global $wpdb;
563566

564-
return (int) $wpdb->get_var(
565-
$wpdb->prepare(
566-
'SELECT SUM(pgsize) as size_in_bytes FROM dbstat where name IN ( SELECT name FROM sqlite_master WHERE type = %s )',
567-
$type
568-
)
569-
);
567+
// The dbstat virtual table is not part of every SQLite build.
568+
$suppress_errors = $wpdb->suppress_errors( true );
569+
570+
if ( '' !== $table_name ) {
571+
$rows = $wpdb->get_results(
572+
$wpdb->prepare(
573+
"SELECT sqlite_master.type as type, SUM(dbstat.pgsize) as size_in_bytes FROM dbstat JOIN sqlite_master ON sqlite_master.name = dbstat.name WHERE sqlite_master.name = %s OR ( sqlite_master.type = 'index' AND sqlite_master.tbl_name = %s ) GROUP BY sqlite_master.type",
574+
$table_name,
575+
$table_name
576+
),
577+
ARRAY_A
578+
);
579+
} else {
580+
$rows = $wpdb->get_results(
581+
'SELECT sqlite_master.type as type, SUM(dbstat.pgsize) as size_in_bytes FROM dbstat JOIN sqlite_master ON sqlite_master.name = dbstat.name GROUP BY sqlite_master.type',
582+
ARRAY_A
583+
);
584+
}
585+
586+
$last_error = $wpdb->last_error;
587+
588+
$wpdb->suppress_errors( $suppress_errors );
589+
590+
if ( '' !== $last_error ) {
591+
WP_CLI::error( 'Could not determine the data and index size of the database. This requires the dbstat extension, which is not available for this SQLite installation.' );
592+
}
593+
594+
$sizes = [
595+
'data' => 0,
596+
'index' => 0,
597+
];
598+
599+
foreach ( (array) $rows as $row ) {
600+
if ( ! is_array( $row ) || ! isset( $row['type'], $row['size_in_bytes'] ) || ! is_numeric( $row['size_in_bytes'] ) ) {
601+
continue;
602+
}
603+
604+
if ( 'table' === $row['type'] ) {
605+
$sizes['data'] = (int) $row['size_in_bytes'];
606+
} elseif ( 'index' === $row['type'] ) {
607+
$sizes['index'] = (int) $row['size_in_bytes'];
608+
}
609+
}
610+
611+
return $sizes;
570612
}
571613

572614
/**

0 commit comments

Comments
 (0)