Introduce `wp db size`

[danielbachhuber]

Indicate the total size of the database and sizes of each table, a la https://github.com/petenelson/wp-cli-size#usage

[danielbachhuber]

@petenelson Is this something you want to submit a PR for?

[petenelson]

Sure, I can give it a shot.

[petenelson]

Apologies, I'm a bit lost in how this is set up now with different repos. I have the main WP-CLI repo cloned and can run tests, but I'm not 100% positive if I'm using this DB repo correctly.

1. After composer install, I went into vendor/wp-cli and removed the db-command directory.
2. I then git cloned this repo into that same directory, but did not run composer install in it.
3. I can then code and run tests against updated code:

./bin/wp --path=/srv/www/wordpress-default/ db size
./vendor/bin/behat vendor/wp-cli/db-command/features/db-size.feature

Is this the right way to work on this?

[schlessera]

@petenelson We're currently working on improving this process. Unfortunately, through a standard composer install you will still pull in the last stable release of WP-CLI, not the current work-in-progress version.

So, if you want to stay with the latest stable version, then yes, this is possible way of running the tests.

If you want to experience the new way of doing this, you can do the following:

1. git clone the wp-cli/db-command somewhere
2. run composer require wp-cli/wp-cli:dev-master in that folder, to force using the latest development version of WP-CLI
3. you can now run the tests directly in that folder through vendor/bin/behat features/db-size.feature

Note: You've changed the composer.json and composer.lock file for this to work. Don't commit them into your pull-request.

Once we've published v1.2.0 of WP-CLI, step 2. will not be necessary anymore.

[petenelson]

Excellent, thanks!

[petenelson]

What's the trick for getting access to wbdb and the other WP functions? I see that the tables() function has access to these, but can't figure out how to get access inside my new function.

[danielbachhuber]

What's the trick for getting access to wbdb and the other WP functions? I see that the tables() function has access to these, but can't figure out how to get access inside my new function.

This is because WP-CLI runs most wp db * commands right after wp-config.php is located, but before the rest of WordPress is loaded: https://github.com/wp-cli/wp-cli/blob/d9d405a4ca01ba23e80277b8cb9000857e84e0cd/php/WP_CLI/Runner.php#L875

To get yourself unblocked, you can run @WP_CLI::get_runner()-> load_wordpress() at the beginning of your method to load WordPress.

We should switch to using named execution points though, to avoid this special case behavior #15

[petenelson]

I've added the size command https://github.com/petenelson/db-command/blob/feature/db-size/src/DB_Command.php#L459 and started working on the tests https://github.com/petenelson/db-command/blob/feature/db-size/features/db-size.feature

Works great when running db size, but the database size varies during the automated tests, as I feared it would. Locally in VVV, sometimes it's 640 KB, sometimes it's 624 KB, and it may even be different when running through another system such as Travis CI.

I could write the tests using regex, though it doesn't look like any of the Then steps in then.php currently support regex, only be/contain/not contain via checkString()

[danielbachhuber]

Works great when running db size, but the database size varies during the automated tests, as I feared it would. Locally in VVV, sometimes it's 640 KB, sometimes it's 624 KB, and it may even be different when running through another system such as Travis CI.

I'd be fine with fetching the database size out of the database with a MySQL query, assigning to a Behat variable, and then comparing wp db size output to the variable. I can't think of a way to do better than this.

[petenelson]

Thanks! This look like the right solution?

  Scenario: Display database and table sizes for a WordPress install
    Given a WP install

    When I run `wp db query "SELECT SUM(data_length + index_length) FROM information_schema.TABLES where table_schema = 'wp_cli_test' GROUP BY table_schema;"`
    Then save STDOUT '(\d+)' as {DBSIZE}

    When I run `wp db size`
    Then STDOUT should contain:
      """
      wp_cli_test
      """

    And STDOUT should contain:
      """
      KB	{DBSIZE}
      """

    And STDOUT should contain:
      """
      wp_terms	48 KB
      """

[danielbachhuber]

Yes, except for the last instance of:

And STDOUT should contain:
      """
      wp_terms	48 KB
      """

[petenelson]

Cool, that was there to verify that it was including tables with the standard db size command, but I can remove that, or perhaps switch the logic to not include tables by default. Do you want to look over the new command before I submit a PR?

Some sample output as it's currently written:

vagrant@vvv:/srv/www/wp-cli$ ./bin/wp --path=/srv/www/wordpress-default db size
+-----------------------+--------+--------+
| Name                  | Size   | Bytes  |
+-----------------------+--------+--------+
| wp_cli_test           | 624 KB | 638976 |
| wp_users              | 64 KB  | 65536  |
| wp_usermeta           | 48 KB  | 49152  |
| wp_posts              | 80 KB  | 81920  |
| wp_comments           | 96 KB  | 98304  |
| wp_links              | 32 KB  | 32768  |
| wp_options            | 32 KB  | 32768  |
| wp_postmeta           | 48 KB  | 49152  |
| wp_terms              | 48 KB  | 49152  |
| wp_term_taxonomy      | 48 KB  | 49152  |
| wp_term_relationships | 32 KB  | 32768  |
| wp_termmeta           | 48 KB  | 49152  |
| wp_commentmeta        | 48 KB  | 49152  |
+-----------------------+--------+--------+

vagrant@vvv:/srv/www/wp-cli$ ./bin/wp --path=/srv/www/wordpress-default db size --db-only
+-------------+--------+--------+
| Name        | Size   | Bytes  |
+-------------+--------+--------+
| wp_cli_test | 624 KB | 638976 |
+-------------+--------+--------+

vagrant@vvv:/srv/www/wp-cli$ ./bin/wp --path=/srv/www/wordpress-default db size --db-only --format=bytes
638976