Changeset 7860

Timestamp:

02/12/2014 09:27:20 PM (13 years ago)

Author:

boonebgorges

Message:

Migrate user 'last_activity' data from usermeta to the activity table

Storing last_activity in usermeta caused severe bottlenecks on sites with
large user bases. The usermeta table has a tendency to get bloated. Its
option_value column is not indexed, and even if it were, it would not be
indexed properly for the kind of chronological sorting that BuddyPress was
using it for.

This changeset refactors all core last_activity user functionality, so that
the data is stored in the wp_bp_activity table (even when the activity
component is disabled).

For backward compatibility with plugins that reference last_activity metadata
entries, all last_activity data is retained in wp_usermeta, and new data will
be mirrored there until further notice.

See #5128

Location:

trunk

Files:

• bp-activity/bp-activity-classes.php (modified) (4 diffs)
• bp-core/admin/bp-core-schema.php (modified) (1 diff)
• bp-core/bp-core-classes.php (modified) (8 diffs)
• bp-core/bp-core-functions.php (modified) (1 diff)
• bp-core/bp-core-update.php (modified) (2 diffs)
• bp-friends/bp-friends-classes.php (modified) (1 diff)
• bp-loader.php (modified) (1 diff)
• bp-members/bp-members-functions.php (modified) (5 diffs)
• bp-members/bp-members-loader.php (modified) (1 diff)
• tests/includes/testcase.php (modified) (1 diff)
• tests/testcases/core/class-bp-core-user.php (modified) (2 diffs)

trunk/bp-activity/bp-activity-classes.php

@@ -362 +362 @@
                 }
 
+                // Exclude 'last_activity' items unless the 'action' filter has
+                // been explicitly set
+                if ( empty( $filter['object'] ) ) {
+                        $where_conditions[] = "a.type != 'last_activity'";
+                }
+
                 // Filter the where conditions
                 $where_conditions = apply_filters( 'bp_activity_get_where_conditions', $where_conditions, $r, $select_sql, $from_sql, $join_sql );
@@ -1063 +1069 @@
 
                 // split items at the comma
-                $items_dirty = explode( ',', $items );
+                if ( ! is_array( $items ) ) {
+                        $items = explode( ',', $items );
+                }
 
                 // array of prepared integers or quoted strings
@@ -1069 +1077 @@
 
                 // clean up and format each item
-                foreach ( $items_dirty as $item ) {
+                foreach ( $items as $item ) {
                         // clean up the string
                         $item = trim( $item );
@@ -1121 +1129 @@
                 if ( !empty( $filter_array['action'] ) ) {
                         $action_sql = BP_Activity_Activity::get_in_operator_sql( 'a.type', $filter_array['action'] );
-                        if ( !empty( $action_sql ) )
+                        if ( ! empty( $action_sql ) )
                                 $filter_sql[] = $action_sql;
                 }

trunk/bp-core/admin/bp-core-schema.php

@@ -24 +24 @@
                 $active_components = apply_filters( 'bp_active_components', bp_get_option( 'bp-active-components' ) );
 
+        // Activity Streams
+        // Install tables even when inactive, to store last_activity data
+        bp_core_install_activity_streams();
+
         // Notifications
         if ( !empty( $active_components['notifications'] ) )
                 bp_core_install_notifications();
-
-        // Activity Streams
-        if ( !empty( $active_components['activity'] ) )
-                bp_core_install_activity_streams();
 
         // Friend Connections

trunk/bp-core/bp-core-classes.php

@@ -246 +246 @@
                         case 'online' :
                                 $this->uid_name = 'user_id';
-                                $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$wpdb->usermeta} u";
-                                $sql['where'][] = $wpdb->prepare( "u.meta_key = %s", bp_get_user_meta_key( 'last_activity' ) );
-                                $sql['where'][] = $wpdb->prepare( "u.meta_value >= DATE_SUB( UTC_TIMESTAMP(), INTERVAL %d MINUTE )", apply_filters( 'bp_user_query_online_interval', 15 ) );
-                                $sql['orderby'] = "ORDER BY u.meta_value";
+                                $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$bp->members->table_name_last_activity} u";
+                                $sql['where'][] = $wpdb->prepare( "u.component = %s AND u.type = 'last_activity'", buddypress()->members->id );
+                                $sql['where'][] = $wpdb->prepare( "u.date_recorded >= DATE_SUB( UTC_TIMESTAMP(), INTERVAL %d MINUTE )", apply_filters( 'bp_user_query_online_interval', 15 ) );
+                                $sql['orderby'] = "ORDER BY u.date_recorded";
                                 $sql['order']   = "DESC";
 
@@ -260 +260 @@
                         case 'random' :
                                 $this->uid_name = 'user_id';
-                                $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$wpdb->usermeta} u";
-                                $sql['where'][] = $wpdb->prepare( "u.meta_key = %s", bp_get_user_meta_key( 'last_activity' ) );
+                                $sql['select']  = "SELECT u.{$this->uid_name} as id FROM {$bp->members->table_name_last_activity} u";
+                                $sql['where'][] = $wpdb->prepare( "u.component = %s AND u.type = 'last_activity'", buddypress()->members->id );
 
                                 if ( 'newest' == $type ) {
@@ -269 +269 @@
                                         $sql['orderby'] = "ORDER BY rand()";
                                 } else {
-                                        $sql['orderby'] = "ORDER BY u.meta_value";
+                                        $sql['orderby'] = "ORDER BY u.date_recorded";
                                         $sql['order'] = "DESC";
                                 }
@@ -538 +538 @@
                 // Turn user ID's into a query-usable, comma separated value
                 $user_ids_sql = implode( ',', wp_parse_id_list( $this->user_ids ) );
+
+                $bp = buddypress();
 
                 /**
@@ -554 +556 @@
                 do_action_ref_array( 'bp_user_query_populate_extras', array( $this, $user_ids_sql ) );
 
+                // Fetch last_active data from the activity table
+                $last_activities = BP_Core_User::get_last_activity( $this->user_ids );
+
+                if ( ! empty( $last_activities ) ) {
+                        foreach ( $last_activities as $la_user => $la_value ) {
+                                if ( isset( $this->results[ $la_user ] ) ) {
+                                        $this->results[ $la_user ]->last_activity = $la_value['date_recorded'];
+                                }
+                        }
+                }
+
                 // Fetch usermeta data
                 // We want the three following pieces of info from usermeta:
                 // - friend count
-                // - last activity
                 // - latest update
                 $total_friend_count_key = bp_get_user_meta_key( 'total_friend_count' );
-                $last_activity_key      = bp_get_user_meta_key( 'last_activity'      );
                 $bp_latest_update_key   = bp_get_user_meta_key( 'bp_latest_update'   );
 
@@ -570 +581 @@
 
                 // Create, prepare, and run the seperate usermeta query
-                $user_metas = $wpdb->get_results( $wpdb->prepare( "SELECT user_id, meta_key, meta_value FROM {$wpdb->usermeta} WHERE meta_key IN (%s,%s,%s) AND user_id IN ({$user_ids_sql})", $total_friend_count_key, $last_activity_key, $bp_latest_update_key ) );
+                $user_metas = $wpdb->get_results( $wpdb->prepare( "SELECT user_id, meta_key, meta_value FROM {$wpdb->usermeta} WHERE meta_key IN (%s,%s) AND user_id IN ({$user_ids_sql})", $total_friend_count_key, $bp_latest_update_key ) );
 
                 // The $members_template global expects the index key to be different
@@ -578 +589 @@
                                 case $total_friend_count_key :
                                         $key = 'total_friend_count';
-                                        break;
-
-                                case $last_activity_key :
-                                        $key = 'last_activity';
                                         break;
 
@@ -1297 +1304 @@
 
                 return $user;
+        }
+
+        /**
+         * Get last activity data for a user or set of users.
+         *
+         * @param int|array User IDs or multiple user IDs.
+         * @return array
+         */
+        public static function get_last_activity( $user_id ) {
+                global $wpdb;
+
+                if ( is_array( $user_id ) ) {
+                        $user_ids = wp_parse_id_list( $user_id );
+                } else {
+                        $user_ids = array( absint( $user_id ) );
+                }
+
+                if ( empty( $user_ids ) ) {
+                        return false;
+                }
+
+                $bp = buddypress();
+
+                $user_ids_sql = implode( ',', $user_ids );
+                $user_count   = count( $user_ids );
+
+                $last_activities = $wpdb->get_results( $wpdb->prepare( "SELECT id, user_id, date_recorded FROM {$bp->members->table_name_last_activity} WHERE component = %s AND type = 'last_activity' AND user_id IN ({$user_ids_sql}) LIMIT {$user_count}", $bp->members->id ) );
+
+                // Re-key
+                $retval = array();
+                foreach ( $last_activities as $last_activity ) {
+                        $retval[ $last_activity->user_id ] = array(
+                                'user_id'       => $last_activity->user_id,
+                                'date_recorded' => $last_activity->date_recorded,
+                                'activity_id'   => $last_activity->id,
+                        );
+                }
+
+                return $retval;
+        }
+
+        /**
+         * Set a user's last_activity value.
+         *
+         * Will create a new entry if it does not exist. Otherwise updates the
+         * existing entry.
+         *
+         * @since 2.0
+         *
+         * @param int $user_id ID of the user whose last_activity you are updating.
+         * @param string $time MySQL-formatted time string.
+         * @return bool True on success, false on failure.
+         */
+        public static function update_last_activity( $user_id, $time ) {
+                global $wpdb;
+
+                $table_name = buddypress()->members->table_name_last_activity;
+
+                $existing = self::get_last_activity( $user_id );
+
+                if ( ! empty( $existing ) ) {
+                        $data = array(
+                                'date_recorded' => $time,
+                        );
+
+                        $data_format = array(
+                                '%s',
+                        );
+
+                        $where = array(
+                        );
+
+                        $where_format = array(
+                                '%d',
+                        );
+
+                        $updated = $wpdb->update(
+                                $table_name,
+
+                                // Data to update
+                                array(
+                                        'date_recorded' => $time,
+                                ),
+
+                                // WHERE
+                                array(
+                                        'id' => $existing[ $user_id ]['activity_id'],
+                                ),
+
+                                // Data sanitization format
+                                array(
+                                        '%s',
+                                ),
+
+                                // WHERE sanitization format
+                                array(
+                                        '%d',
+                                )
+                        );
+                } else {
+                        $updated = $wpdb->insert(
+                                $table_name,
+
+                                // Data
+                                array(
+                                        'user_id'       => $user_id,
+                                        'component'     => buddypress()->members->id,
+                                        'type'          => 'last_activity',
+                                        'date_recorded' => $time,
+                                ),
+
+                                // Data sanitization format
+                                array(
+                                        '%d',
+                                        '%s',
+                                        '%s',
+                                        '%s',
+                                )
+                        );
+                }
+
+                return $updated;
+        }
+
+        /**
+         * Delete a user's last_activity value.
+         *
+         * @since 2.0
+         *
+         * @param int $user_id
+         * @return bool True on success, false on failure or if no last_activity
+         *         is found for the user.
+         */
+        public static function delete_last_activity( $user_id ) {
+                global $wpdb;
+
+                $existing = self::get_last_activity( $user_id );
+
+                if ( empty( $existing ) ) {
+                        return false;
+                }
+
+                $deleted = $wpdb->delete(
+                        buddypress()->members->table_name_last_activity,
+
+                        // WHERE
+                        array(
+                                'id' => $existing[ $user_id ]['activity_id'],
+                        ),
+
+                        // WHERE sanitization format
+                        array(
+                                '%s',
+                        )
+                );
+
+                return $deleted;
         }
 }

trunk/bp-core/bp-core-functions.php

@@ -1054 +1054 @@
  * Plugin authors should use BP's _user_meta() functions, which bakes in
  * bp_get_user_meta_key():
- *    $last_active = bp_get_user_meta( $user_id, 'last_activity', true );
+ *    $friend_count = bp_get_user_meta( $user_id, 'total_friend_count', true );
  * If you must use WP's _user_meta() functions directly for some reason, you
  * should use this function to determine the $key parameter, eg
- *    $last_active = get_user_meta( $user_id, bp_get_user_meta_key( 'last_activity' ), true );
+ *    $friend_count = get_user_meta( $user_id, bp_get_user_meta_key( 'total_friend_count' ), true );
  * If using the WP functions, do not not hardcode your meta keys.
  *

trunk/bp-core/bp-core-update.php

@@ -230 +230 @@
                 if ( $raw_db_version < 7731 ) {
                         bp_update_to_1_9_2();
+                }
+
+                // 2.0
+                if ( $raw_db_version < 7859 ) {
+                        bp_update_to_2_0();
                 }
         }
@@ -329 +334 @@
 
 /**
+ * 2.0 update routine.
+ *
+ * - Ensure that the activity tables are installed, for last_activity storage.
+ * - Migrate last_activity data from usermeta to activity table
+ */
+function bp_update_to_2_0() {
+        global $wpdb;
+
+        // Install activity tables
+        bp_core_install_activity_streams();
+
+        $bp = buddypress();
+
+        // Migrate data
+        // The "NOT IN" clause prevents duplicates
+        $sql = "INSERT INTO {$bp->members->table_name_last_activity} (`user_id`, `component`, `type`, `date_recorded` ) (
+                  SELECT user_id, '{$bp->members->id}' as component, 'last_activity' as type, meta_value AS date_recorded
+                  FROM {$wpdb->usermeta}
+                  WHERE
+                    meta_key = 'last_activity'
+                    AND
+                    user_id NOT IN (
+                      SELECT user_id
+                      FROM {$bp->members->table_name_last_activity}
+                      WHERE component = '{$bp->members->id}' AND type = 'last_activity'
+                    )
+        );";
+
+        $wpdb->query( $sql );
+}
+
+/**
  * Redirect user to BP's What's New page on first page load after activation.
  *

trunk/bp-friends/bp-friends-classes.php

@@ -365 +365 @@
                 global $wpdb;
 
-                $user_ids = implode( ',', wp_parse_id_list( $user_ids ) );
-
-                return $wpdb->get_results( $wpdb->prepare( "SELECT meta_value as last_activity, user_id FROM {$wpdb->usermeta} WHERE meta_key = %s AND user_id IN ( {$user_ids} ) ORDER BY meta_value DESC", bp_get_user_meta_key( 'last_activity' ) ) );
+                $last_activities = BP_Core_User::get_last_activity( $user_ids );
+
+                // Sort and structure as expected in legacy function
+                usort( $last_activities, create_function( '$a, $b', '
+                        if ( $a["date_recorded"] == $b["date_recorded"] ) {
+                                return 0;
+                        }
+
+                        return ( strtotime( $a["date_recorded"] ) < strtotime( $b["date_recorded"] ) ) ? 1 : -1;
+                ' ) );
+
+                $retval = array();
+                foreach ( $last_activities as $last_activity ) {
+                        $u = new stdClass;
+                        $u->last_activity = $last_activity['date_recorded'];
+                        $u->user_id       = $last_activity['user_id'];
+
+                        $retval[] = $u;
+                }
+
+                return $retval;
         }
 

trunk/bp-loader.php

@@ -305 +305 @@
 
                 $this->version    = '2.0-alpha-7752';
-                $this->db_version = 7731;
+                $this->db_version = 7859;
 
                 /** Loading ***************************************************/

trunk/bp-members/bp-members-functions.php

@@ -511 +511 @@
 
         if ( !$count = get_transient( 'bp_active_member_count' ) ) {
+                $bp = buddypress();
+
                 // Avoid a costly join by splitting the lookup
                 if ( is_multisite() ) {
@@ -520 +522 @@
                 $exclude_users     = $wpdb->get_col( $sql );
                 $exclude_users_sql = !empty( $exclude_users ) ? "AND user_id NOT IN (" . implode( ',', wp_parse_id_list( $exclude_users ) ) . ")" : '';
-                $count             = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(user_id) FROM {$wpdb->usermeta} WHERE meta_key = %s {$exclude_users_sql}", bp_get_user_meta_key( 'last_activity' ) ) );
+                $count             = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(user_id) FROM {$bp->members->table_name_last_activity} WHERE component = %s AND type = 'last_activity' {$exclude_users_sql}", $bp->members->id ) );
 
                 set_transient( 'bp_active_member_count', $count );
@@ -818 +820 @@
 
 /**
- * Update a user's last activity
- *
- * @since BuddyPress (1.9)
- * @param int $user_id ID of the user being updated
- * @param string $time Time of last activity, in 'Y-m-d H:i:s' format
- * @return bool True on success
+ * Update a user's last activity.
+ *
+ * @since BuddyPress (1.9.0)
+ *
+ * @param int $user_id ID of the user being updated.
+ * @param string $time Time of last activity, in 'Y-m-d H:i:s' format.
+ * @return bool True on success, false on failure.
  */
 function bp_update_user_last_activity( $user_id = 0, $time = '' ) {
@@ -841 +844 @@
         }
 
-        return bp_update_user_meta( $user_id, 'last_activity', $time );
-}
-
-/**
- * Get the last activity for a given user
- *
- * @param int $user_id The ID of the user
+        // As of BuddyPress 2.0, last_activity is no longer stored in usermeta.
+        // However, we mirror it there for backward compatibility. Do not use!
+        // Remove our warning and re-add.
+        remove_filter( 'update_user_metadata', '_bp_update_user_meta_last_activity_warning', 10, 4 );
+        update_user_meta( $user_id, 'last_activity', $time );
+        add_filter( 'update_user_metadata', '_bp_update_user_meta_last_activity_warning', 10, 4 );
+
+        return BP_Core_User::update_last_activity( $user_id, $time );
+}
+
+/**
+ * Backward compatibility for 'last_activity' usermeta fetching.
+ *
+ * In BuddyPress 2.0, user last_activity data was moved out of usermeta. For
+ * backward compatibility, we continue to mirror the data there. This function
+ * serves two purposes: it warns plugin authors of the change, and it returns
+ * the data from the proper location.
+ *
+ * @since BuddyPress (2.0.0)
+ *
+ * @access private For internal use only.
+ *
+ * @param null $retval
+ * @param int $object_id ID of the user.
+ * @param string $meta_key Meta key being fetched.
+ */
+function _bp_get_user_meta_last_activity_warning( $retval, $object_id, $meta_key ) {
+        static $warned;
+
+        if ( 'last_activity' === $meta_key ) {
+                // Don't send the warning more than once per pageload
+                if ( empty( $warned ) ) {
+                        _doing_it_wrong( 'get_user_meta( $user_id, \'last_activity\' )', __( 'User last_activity data is no longer stored in usermeta. Use bp_get_user_last_activity() instead.', 'buddypress' ), '2.0.0' );
+                        $warned = 1;
+                }
+
+                return bp_get_user_last_activity( $object_id );
+        }
+
+        return $retval;
+}
+add_filter( 'get_user_metadata', '_bp_get_user_meta_last_activity_warning', 10, 3 );
+
+/**
+ * Backward compatibility for 'last_activity' usermeta setting.
+ *
+ * In BuddyPress 2.0, user last_activity data was moved out of usermeta. For
+ * backward compatibility, we continue to mirror the data there. This function
+ * serves two purposes: it warns plugin authors of the change, and it updates
+ * the data in the proper location.
+ *
+ * @since BuddyPress (2.0.0)
+ *
+ * @access private For internal use only.
+ *
+ * @param int $meta_id ID of the just-set usermeta row.
+ * @param int $object_id ID of the user.
+ * @param string $meta_key Meta key being fetched.
+ * @param string $meta_value Active time.
+ */
+function _bp_update_user_meta_last_activity_warning( $meta_id, $object_id, $meta_key, $meta_value ) {
+        if ( 'last_activity' === $meta_key ) {
+                _doing_it_wrong( 'update_user_meta( $user_id, \'last_activity\' )', __( 'User last_activity data is no longer stored in usermeta. Use bp_update_user_last_activity() instead.', 'buddypress' ), '2.0.0' );
+                bp_update_user_last_activity( $object_id, $meta_value );
+        }
+}
+add_filter( 'update_user_metadata', '_bp_update_user_meta_last_activity_warning', 10, 4 );
+
+/**
+ * Get the last activity for a given user.
+ *
+ * @param int $user_id The ID of the user.
  * @return string Time of last activity, in 'Y-m-d H:i:s' format, or an empty
- *   string if none is found
+ *         string if none is found.
  */
 function bp_get_user_last_activity( $user_id = 0 ) {
-        // Fall back on current user
-        if ( empty( $user_id ) ) {
-                $user_id = bp_loggedin_user_id();
-        }
-
-        $activity = bp_get_user_meta( $user_id, 'last_activity', true );
+        $activity = '';
+
+        $last_activity = BP_Core_User::get_last_activity( $user_id );
+        if ( ! empty( $last_activity[ $user_id ] ) ) {
+                $activity = $last_activity[ $user_id ]['date_recorded'];
+        }
 
         return apply_filters( 'bp_get_user_last_activity', $activity, $user_id );
@@ -997 +1065 @@
 function bp_core_remove_data( $user_id ) {
 
-        // Remove usermeta
-        bp_delete_user_meta( $user_id, 'last_activity' );
+        // Remove last_activity data
+        BP_Core_User::delete_last_activity( $user_id );
 
         // Flush the cache to remove the user from all cached objects

trunk/bp-members/bp-members-loader.php

@@ -73 +73 @@
                         'root_slug'     => isset( $bp->pages->members->slug ) ? $bp->pages->members->slug : BP_MEMBERS_SLUG,
                         'has_directory' => true,
+                        'global_tables' => array(
+                                'table_name_last_activity' => bp_core_get_table_prefix() . 'bp_activity',
+                        ),
                         'search_string' => __( 'Search Members...', 'buddypress' ),
                 ) );

trunk/tests/includes/testcase.php

@@ -219 +219 @@
                 $r = wp_parse_args( $args, array(
                         'role' => 'subscriber',
-                        'last_activity' => bp_core_current_time() - 60*60*24*365,
+                        'last_activity' => date( 'Y-m-d h:i:s', strtotime( bp_core_current_time() ) - 60*60*24*365 ),
                 ) );
 

trunk/tests/testcases/core/class-bp-core-user.php

@@ -3 +3 @@
 /**
  * @group core
+ * @group BP_Core_User
  */
 class BP_Tests_BP_Core_User_TestCases extends BP_UnitTestCase {
@@ -120 +121 @@
         }
 
+        /**
+         * @group last_activity
+         */
+        public function test_get_last_activity() {
+                $u = $this->create_user();
+                $time = bp_core_current_time();
 
+                BP_Core_User::update_last_activity( $u, $time );
 
+                $a = BP_Core_User::get_last_activity( $u );
+                $found = isset( $a[ $u ]['date_recorded'] ) ? $a[ $u ]['date_recorded'] : '';
+
+                $this->assertEquals( $time, $found );
+        }
+
+        /**
+         * @group last_activity
+         */
+        public function test_update_last_activity() {
+                $u = $this->create_user();
+                $time = bp_core_current_time();
+                $time2 = '1968-12-25 01:23:45';
+
+                BP_Core_User::update_last_activity( $u, $time );
+                $a = BP_Core_User::get_last_activity( $u );
+                $found = isset( $a[ $u ]['date_recorded'] ) ? $a[ $u ]['date_recorded'] : '';
+                $this->assertEquals( $time, $found );
+
+                BP_Core_User::update_last_activity( $u, $time2 );
+                $a = BP_Core_User::get_last_activity( $u );
+                $found = isset( $a[ $u ]['date_recorded'] ) ? $a[ $u ]['date_recorded'] : '';
+                $this->assertEquals( $time2, $found );
+        }
+
+        /**
+         * @group last_activity
+         */
+        public function test_delete_last_activity() {
+                $u = $this->create_user();
+                $time = bp_core_current_time();
+
+                BP_Core_User::update_last_activity( $u, $time );
+                $a = BP_Core_User::get_last_activity( $u );
+                $found = isset( $a[ $u ]['date_recorded'] ) ? $a[ $u ]['date_recorded'] : '';
+                $this->assertEquals( $time, $found );
+
+                BP_Core_User::delete_last_activity( $u );
+                $a = BP_Core_User::get_last_activity( $u );
+                $found = isset( $a[ $u ]['date_recorded'] ) ? $a[ $u ]['date_recorded'] : '';
+                $this->assertEquals( '', $found );
+        }
 }