Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

size command doesn't work with SQLite #243

Open
mrsdizzie opened this issue Oct 18, 2023 · 1 comment
Open

size command doesn't work with SQLite #243

mrsdizzie opened this issue Oct 18, 2023 · 1 comment

Comments

@mrsdizzie
Copy link
Member

The following only works with MySQL

db-command/src/DB_Command.php

Lines 1019 to 1024 in 0908bf5

$db_bytes = $wpdb->get_var(
$wpdb->prepare(
'SELECT SUM(data_length + index_length) FROM information_schema.TABLES where table_schema = %s GROUP BY table_schema;',
DB_NAME
)
);

I don't believe there is a similar way to get the size of a SQLite database, and the preferred method is to just get the size of the .sqlite file itself.

I wonder what the general solution should be to detect SQLite use in wp-cli commands so any new code is consistent across various projects? It could be good to have a helper function to give the current database type and maybe also the location of the .sqlite file if it is SQLite.

Or is this just better left until WordPress adopts SQLite officially and adds a built in method for wbdp?

@danielbachhuber
Copy link
Member

danielbachhuber commented Oct 18, 2023

Or is this just better left until WordPress adopts SQLite officially and adds a built in method for wbdp?

I don't think it's likely that WordPress core will add exactly what we need. wp db size is something we invented for our own use.

I wonder what the general solution should be to detect SQLite use in wp-cli commands so any new code is consistent across various projects? It could be good to have a helper function to give the current database type and maybe also the location of the .sqlite file if it is SQLite.

I'm open to it! I'd suggest we fix SQLite support for a couple/few commands first, and then figure out what abstraction they need.

I don't believe there is a similar way to get the size of a SQLite database, and the preferred method is to just get the size of the .sqlite file itself.

Worth noting: wp db size also supports a --tables argument:

$ wp db size --tables
+-----------------------+-----------+
| Name                  | Size      |
+-----------------------+-----------+
| wp_commentmeta        | 49152 B   |
| wp_comments           | 98304 B   |
| wp_links              | 32768 B   |
| wp_options            | 1163264 B |
| wp_postmeta           | 49152 B   |
| wp_posts              | 81920 B   |
| wp_term_relationships | 32768 B   |
| wp_term_taxonomy      | 49152 B   |
| wp_termmeta           | 49152 B   |
| wp_terms              | 49152 B   |
| wp_usermeta           | 49152 B   |
| wp_users              | 65536 B   |
+-----------------------+-----------+

Here's what I see with SQLite:

$ wp db size
+--------+------------------+
| Name   | Size             |
+--------+------------------+
| wplite | WP_COMMENTMETA B |
+--------+------------------+
$ wp db size --tables
+-------------------------+------------------+
| Name                    | Size             |
+-------------------------+------------------+
| _mysql_data_types_cache | WP_COMMENTMETA B |
| sqlite_sequence         | WP_COMMENTMETA B |
| wp_commentmeta          | WP_COMMENTMETA B |
| wp_comments             | WP_COMMENTMETA B |
| wp_links                | WP_COMMENTMETA B |
| wp_options              | WP_COMMENTMETA B |
| wp_postmeta             | WP_COMMENTMETA B |
| wp_posts                | WP_COMMENTMETA B |
| wp_term_relationships   | WP_COMMENTMETA B |
| wp_term_taxonomy        | WP_COMMENTMETA B |
| wp_termmeta             | WP_COMMENTMETA B |
| wp_terms                | WP_COMMENTMETA B |
| wp_usermeta             | WP_COMMENTMETA B |
| wp_users                | WP_COMMENTMETA B |
+-------------------------+------------------+

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants