Changeset 60614

Timestamp:

08/07/2025 01:32:17 AM (3 months ago)

Author:

peterwilsoncc

Message:

Upgrade/Install: Reduce number of DB queries populating roles.

Reduces the number of database queries made when populating roles during install/multisite site creation by 344 (347 queries down to 3).

populate_roles() has been modified to prevent an individual database query each time a role or capability is added to the WP_Roles object. Instead the roles option, {$wpdb->prefix}user_roles is updated once at the end of the function call.

Introduces a test to ensure that updating the roles option via WP_Roles and updating the option in the manner now used by populate_roles() results in the same capabilities been applied to a role.

Props fliespl, johnjamesjacoby, ocean90, realloc, rishabhwp, sainathpoojary, sirlouen, spacedmonkey, swissspidy.
Fixes #37687.

Location:

trunk

Files:

• src/wp-admin/includes/schema.php (modified) (2 diffs)
• tests/phpunit/tests/user/capabilities.php (modified) (1 diff)

trunk/src/wp-admin/includes/schema.php

@@ -714 +714 @@
  */
 function populate_roles() {
+    $wp_roles = wp_roles();
+
+    // Disable role updates to the database while populating roles.
+    $original_use_db  = $wp_roles->use_db;
+    $wp_roles->use_db = false;
+
+    // Populate roles
     populate_roles_160();
     populate_roles_210();
@@ -722 +729 @@
     populate_roles_280();
     populate_roles_300();
+
+    // Save the updated roles to the database.
+    if ( $original_use_db ) {
+        update_option( $wp_roles->role_key, $wp_roles->roles, true );
+    }
+
+    // Restore original value for writing to database.
+    $wp_roles->use_db = $original_use_db;
 }
 

trunk/tests/phpunit/tests/user/capabilities.php

@@ -2543 +2543 @@
         }
     }
+
+    /**
+     * Ensure that caps are updated correctly when using `update_option()` to save roles.
+     *
+     * Compares the efficiency and accuracy of updating role capabilities when `WP_Roles`
+     * uses the database vs when the updates are done via `update_option()`.
+     *
+     * This method of updating roles is used in `populate_roles()` to reduce the number of
+     * queries by approximately 300.
+     *
+     * @ticket 37687
+     */
+    public function test_role_capabilities_updated_correctly_via_update_option() {
+        global $wp_roles;
+        $emcee_role = 'emcee';
+        $wp_roles->add_role( $emcee_role, 'Emcee', array( 'level_1' => true ) );
+        $this->flush_roles();
+
+        $expected_caps = array(
+            'level_1'             => true,
+            'attend_kit_kat_klub' => true,
+            'win_tony_award'      => true,
+        );
+
+        $start_queries = get_num_queries();
+        $wp_roles->add_cap( $emcee_role, 'attend_kit_kat_klub' );
+        $wp_roles->add_cap( $emcee_role, 'win_tony_award' );
+        $emcee_queries = get_num_queries() - $start_queries;
+        $this->flush_roles();
+        $emcee_caps = $wp_roles->get_role( $emcee_role )->capabilities;
+
+        $wp_roles->use_db = false;
+        $sally_role       = 'sally';
+        $wp_roles->add_role( $sally_role, 'Sally Bowles', array( 'level_1' => true ) );
+        $start_queries = get_num_queries();
+        $wp_roles->add_cap( $sally_role, 'attend_kit_kat_klub' );
+        $wp_roles->add_cap( $sally_role, 'win_tony_award' );
+
+        update_option( $wp_roles->role_key, $wp_roles->roles, true );
+        $sally_queries    = get_num_queries() - $start_queries;
+        $wp_roles->use_db = true;
+
+        // Restore the default value.
+        $this->flush_roles();
+        $sally_caps = $wp_roles->get_role( $sally_role )->capabilities;
+
+        $this->assertSameSetsWithIndex( $expected_caps, $emcee_caps, 'Emcee role should include the three expected capabilities.' );
+        $this->assertSameSetsWithIndex( $expected_caps, $sally_caps, 'Sally role should include the three expected capabilities.' );
+        $this->assertSameSetsWithIndex( $emcee_caps, $sally_caps, 'Emcee and Sally roles should have the same capabilities after update.' );
+        $this->assertLessThan( $emcee_queries, $sally_queries, 'Updating roles via update_option should be more efficient than WP_Roles using the database.' );
+    }
 }