Making WordPress.org

Changeset 2022


Ignore:
Timestamp:
11/01/2015 10:27:39 AM (11 years ago)
Author:
nacin
Message:

Trac Notifications: Abstract all queries into a separate class.

Location:
sites/trunk/wordpress.org/public_html/wp-content/plugins/trac-notifications
Files:
1 added
2 edited

Legend:

Unmodified
Added
Removed
  • sites/trunk/wordpress.org/public_html/wp-content/plugins/trac-notifications/trac-components.php

    r1965 r2022  
    44    const last_x_days = 7;
    55
    6     function __construct() {
     6    function __construct( $api ) {
     7        $this->api = $api;
    78        add_action( 'init', array( $this, 'init' ) );
    89        add_action( 'pre_get_posts', array( $this, 'pre_get_posts' ) );
     
    1516        add_filter( 'manage_component_posts_columns', array( $this, 'manage_posts_columns' ) );
    1617        add_action( 'manage_component_posts_custom_column', array( $this, 'manage_posts_custom_column' ), 10, 2 );
    17         $this->trac = $GLOBALS['wpdb'];
    1818    }
    1919
     
    319319        echo "<strong>Want to help? Start following this component!</strong> <a href='/core/notifications/'>Adjust your notifications here</a>. Feel free to dig into any ticket." . "\n\n";
    320320
    321         $followers = $this->trac->get_col( $this->trac->prepare( "SELECT username FROM _notifications WHERE type = 'component' AND value = %s", $post->post_title ) );
     321        $followers = $this->api->get_component_followers( $post->post_title );
    322322        $followers = "'" . implode( "', '", esc_sql( $followers ) ) . "'";
    323323        $followers = $wpdb->get_results( "SELECT user_login, user_nicename, user_email FROM $wpdb->users WHERE user_login IN ($followers)" );
     
    345345        $rows = wp_cache_get( 'trac_tickets_by_component_type_milestone' );
    346346        if ( ! $rows ) {
    347             $rows = $this->trac->get_results( "SELECT component, type, milestone, count(*) as count FROM ticket
    348                 WHERE status <> 'closed' GROUP BY component, type, milestone ORDER BY component, type, milestone" );
     347            $rows = $this->api->get_tickets_by_component_type_milestone();
    349348            wp_cache_add( 'trac_tickets_by_component_type_milestone', $rows, '', 300 );
    350349        }
     
    364363        $component_unreplied = wp_cache_get( 'trac_tickets_by_component_unreplied' );
    365364        if ( ! $component_unreplied ) {
    366             $rows = $this->trac->get_results( "SELECT id, component FROM ticket t
    367                 WHERE id NOT IN (SELECT ticket FROM ticket_change WHERE ticket = t.id AND t.reporter <> author AND field = 'comment' AND newvalue <> '')
    368                 AND status <> 'closed'" );
    369             $component_unreplied = array();
    370             foreach ( $rows as $row ) {
    371                 $component_unreplied[ $row->component ][] = $row->id;
    372             }
     365            $component_unreplied = $this->api->get_unreplied_ticket_counts_by_component();
    373366            wp_cache_add( 'trac_tickets_by_component_unreplied', $component_unreplied, '', 300 );
    374367        }
     
    399392        }
    400393
    401         $history = $this->get_component_history( $component );
     394        $history = $this->api->get_component_history( $component );
    402395        $direction = '';
    403396        if ( $history['change'] > 0 ) {
     
    451444
    452445    function trac_content( $component ) {
    453         $unreplied_tickets = $this->trac->get_results( $this->trac->prepare(
    454             "SELECT id, summary, status, resolution, milestone, value as focuses
    455             FROM ticket t LEFT JOIN ticket_custom c ON c.ticket = t.id AND c.name = 'focuses'
    456             WHERE id NOT IN (
    457                 SELECT ticket FROM ticket_change
    458                 WHERE ticket = t.id AND t.reporter <> author
    459                 AND field = 'comment' AND newvalue <> ''
    460             ) AND status <> 'closed' AND component = %s", $component ) );
     446        $unreplied_tickets = $this->api->get_unreplied_tickets_by_component( $component );
    461447
    462448        if ( $unreplied_tickets ) {
     
    467453        }
    468454
    469         $next_milestone = $this->trac->get_results( $this->trac->prepare( "SELECT id, summary, status, resolution, milestone, value as focuses FROM ticket t
    470             LEFT JOIN ticket_custom c ON c.ticket = t.id AND c.name = 'focuses' WHERE component = %s AND status <> 'closed' AND milestone LIKE '_._'", $component ) );
     455        $next_milestone = $this->api->get_tickets_in_next_milestone( $component );
     456
    471457        if ( $next_milestone ) {
    472458            $count = count( $next_milestone );
     
    476462        }
    477463
    478         return; // Ditch the rest for now.
    479 
    480         $tickets_by_type = $this->trac->get_results( $this->trac->prepare( "SELECT type, COUNT(*) as count FROM ticket WHERE component = %s AND status <> 'closed' GROUP BY type", $component ), OBJECT_K );
    481         foreach ( $tickets_by_type as &$object ) {
    482             $object = $object->count;
    483         }
    484         unset( $object );
     464        $tickets_by_type = $this->api->get_ticket_counts_for_component( $component );
    485465
    486466        $count = array_sum( $tickets_by_type );
     
    490470        echo "\n\n";
    491471
    492         if ( $enhancements = $this->trac->get_results( $this->trac->prepare( "SELECT id, summary, status, resolution, milestone FROM ticket WHERE component = %s AND status <> 'closed' AND type = %s", $component, 'enhancement' ) ) ) {
    493             printf( '<h3>Open enhancements (%d)</h3>', count( $enhancements ) );
    494             echo $this->trac_query_link( 'View list on Trac', array( 'component' => $component, 'type' => 'enhancement' ) );
    495             $this->render_tickets( $enhancements );
    496         }
    497 
    498         if ( $tasks = $this->trac->get_results( $this->trac->prepare( "SELECT id, summary, status, resolution, milestone FROM ticket WHERE component = %s AND status <> 'closed' AND type = %s", $component, 'task (blessed)' ) ) ) {
    499             printf( '<h3>Open tasks (%d)</h3>', count( $tasks ) );
    500             echo $this->trac_query_link( 'View list on Trac', array( 'component' => $component, 'type' => 'task (blessed)' ) );
    501             $this->render_tickets( $tasks );
    502         }
    503 
    504         if ( $feature_requests = $this->trac->get_results( $this->trac->prepare( "SELECT id, summary, status, resolution, milestone FROM ticket WHERE component = %s AND status <> 'closed' AND type = %s", $component, 'feature request' ) ) ) {
    505             printf( '<h3>Open feature requests (%d)</h3>', count( $feature_requests ) );
    506             echo $this->trac_query_link( 'View list on Trac', array( 'component' => $component, 'type' => 'feature request' ) );
    507             $this->render_tickets( $feature_requests );
     472        return;
     473
     474        $types = array(
     475            'enhancement'     => 'Open enhancements',
     476            'task (blessed)'  => 'Open tasks',
     477            'feature request' => 'Open feature requests',
     478        );
     479
     480        foreach ( $types as $type => $title ) {
     481            $args = compact( 'component', 'type' );
     482            if ( $tickets = $this->api->get_tickets_by( $args ) ) {
     483                printf( '<h3>%s (%d)</h3>', $title, count( $tickets ) );
     484                echo $this->trac_query_link( 'View list on Trac', $args );
     485                $this->render_tickets( $tickets ); 
     486            }
    508487        }
    509488    }
     
    531510        }
    532511        echo '</ul>';
    533     }
    534 
    535     function get_component_history( $component ) {
    536         $days_ago = ( time() - ( DAY_IN_SECONDS * self::last_x_days ) ) * 1000000;
    537         $closed_reopened = $this->trac->get_results( $this->trac->prepare( "SELECT newvalue, COUNT(DISTINCT ticket) as count
    538             FROM ticket_change tc INNER JOIN ticket t ON tc.ticket = t.id
    539             WHERE field = 'status' AND (newvalue = 'closed' OR newvalue = 'reopened')
    540             AND tc.time >= %s AND t.component = %s GROUP BY newvalue", $days_ago, $component ), OBJECT_K );
    541         $reopened = isset( $closed_reopened['reopened'] ) ? $closed_reopened['reopened']->count : 0;
    542         $closed = isset( $closed_reopened['closed'] ) ? $closed_reopened['closed']->count : 0;
    543         $opened = $this->trac->get_var( $this->trac->prepare( "SELECT COUNT(DISTINCT id) FROM ticket WHERE time >= %s AND component = %s", $days_ago, $component ) );
    544         $assigned_unassigned = $this->trac->get_results( $this->trac->prepare( "SELECT IF(newvalue = %s, 'assigned', 'unassigned') as direction,
    545             COUNT(*) as count FROM ticket_change WHERE field = 'component' AND ( oldvalue = %s OR newvalue = %s ) AND time >= %s GROUP BY direction",
    546             $component, $component, $component, $days_ago ), OBJECT_K );
    547         $assigned = isset( $assigned_unassigned['assigned'] ) ? $assigned_unassigned['assigned']->count : 0;
    548         $unassigned = isset( $assigned_unassigned['unassigned'] ) ? $assigned_unassigned['unassigned']->count : 0;
    549 
    550         $change = $opened + $reopened + $assigned - $closed - $unassigned;
    551         return compact( 'change', 'opened', 'reopened', 'closed', 'assigned', 'unassigned' );
    552512    }
    553513
     
    584544        }
    585545        if ( in_array( 'component', $topics ) ) {
    586             $components = $this->trac->get_col( "SELECT name FROM component" );
     546            $components = $this->api->get_components();
    587547            foreach ( $components as $component ) {
    588548                echo '<option value="component/' . esc_attr( str_replace( ' ', '+', $component ) ) . '">' . esc_html( $component ) . "</option>";
     
    602562        $component = $post->post_title;
    603563        $this->generate_component_breakdowns();
    604         $history = $this->get_component_history( $component );
     564        $history = $this->api->get_component_history( $component );
    605565
    606566        $arrow = '';
     
    633593        }
    634594
    635         $maintainers = $this->get_maintainers_by_post( $post->ID );
     595        $maintainers = $this->get_component_maintainers_by_post( $post->ID );
    636596        echo '<td class="no-grav maintainers">';
    637597        foreach ( $maintainers as $maintainer ) {
     
    642602    }
    643603
    644     function get_maintainers_by_post( $post_id ) {
     604    function get_component_maintainers_by_post( $post_id ) {
    645605        return array_filter( array_map( 'trim', explode( ',', get_post_meta( $post_id, '_active_maintainers', true ) ) ) );
    646606    }
    647607
    648     function get_maintainers_by_component( $component ) {
    649         return $this->get_maintainers_by_post( get_page_by_title( $component, OBJECT, 'component' )->ID );
     608    function get_component_maintainers( $component ) {
     609        return $this->get_component_maintainers_by_post( get_page_by_title( $component, OBJECT, 'component' )->ID );
    650610    }
    651611}
  • sites/trunk/wordpress.org/public_html/wp-content/plugins/trac-notifications/trac-notifications.php

    r1979 r2022  
    88class wporg_trac_notifications {
    99
    10     protected $trac_subdomain;
     10    protected $trac;
    1111    protected $components;
    1212
     
    2323            $trac = $_GET['trac'];
    2424        }
    25         $this->set_trac( $trac );
    26         add_filter( 'allowed_http_origins', array( $this, 'filter_allowed_http_origins' ) );
    27         add_action( 'template_redirect', array( $this, 'action_template_redirect' ) );
    28         add_shortcode( 'trac-notifications', array( $this, 'notification_settings_page' ) );
    29         if ( 'core' === $trac ) {
    30             require __DIR__ . '/trac-components.php';
    31             $this->components = new Make_Core_Trac_Components;
    32         }
    33     }
    34 
    35     function set_trac( $trac ) {
    36         $this->trac_subdomain = $trac;
     25
     26        require __DIR__ . '/trac-notifications-db.php';
     27
    3728        if ( function_exists( 'add_db_table' ) ) {
    3829            $tables = array( 'ticket', '_ticket_subs', '_notifications', 'ticket_change', 'component', 'milestone', 'ticket_custom' );
     
    4031                add_db_table( 'trac_' . $trac, $table );
    4132            }
    42             $this->trac = $GLOBALS['wpdb'];
    43         }
     33        }
     34        $this->api = new Trac_Notifications_DB( $GLOBALS['wpdb'] );
     35
     36        if ( 'core' === $trac ) {
     37            require __DIR__ . '/trac-components.php';
     38            $this->components = new Make_Core_Trac_Components( $this->api );
     39        }
     40
     41        $this->trac = $trac;
     42
     43        add_filter( 'allowed_http_origins', array( $this, 'filter_allowed_http_origins' ) );
     44        add_action( 'template_redirect', array( $this, 'action_template_redirect' ) );
     45        add_shortcode( 'trac-notifications', array( $this, 'notification_settings_page' ) );
    4446    }
    4547
    4648    function trac_url() {
    47         return 'https://' . $this->trac_subdomain . '.trac.wordpress.org';
     49        return 'https://' . $this->trac . '.trac.wordpress.org';
    4850    }
    4951
     
    7678            exit;
    7779        }
    78     }
    79 
    80     function get_trac_ticket( $ticket_id ) {
    81         return $this->trac->get_row( $this->trac->prepare( "SELECT * FROM ticket WHERE id = %d", $ticket_id ) );
    82     }
    83 
    84     function get_trac_ticket_focuses( $ticket_id ) {
    85         return $this->trac->get_var( $this->trac->prepare( "SELECT value FROM ticket_custom WHERE ticket = %d AND name = 'focuses'", $ticket_id ) );
    86     }
    87 
    88     function get_trac_ticket_participants( $ticket_id ) {
    89         // Make sure we suppress CC-only comments that still exist in the database.
    90         // Do this by suppressing any 'cc' changes and also any empty comments (used by Trac for comment numbering).
    91         // Empty comments are also used for other property changes made without comment, but those changes will still be returned by this query.
    92         $ignore_cc = "field <> 'cc' AND NOT (field = 'comment' AND newvalue = '') AND";
    93         return $this->trac->get_col( $this->trac->prepare( "SELECT DISTINCT author FROM ticket_change WHERE $ignore_cc ticket = %d", $ticket_id ) );
    94     }
    95 
    96     function get_trac_ticket_subscriptions( $ticket_id ) {
    97         $by_status = array( 'blocked' => array(), 'starred' => array() );
    98         $subscriptions = $this->trac->get_results( $this->trac->prepare( "SELECT username, status FROM _ticket_subs WHERE ticket = %s", $ticket_id ) );
    99         foreach ( $subscriptions as $subscription ) {
    100             $by_status[ $subscription->status ? 'starred' : 'blocked' ][] = $subscription->username;
    101         }
    102         return $by_status;
    10380    }
    10481
     
    135112    }
    136113
    137     function get_trac_components() {
    138         return $this->trac->get_col( "SELECT name FROM component WHERE name <> 'WordPress.org site' ORDER BY name ASC" );
    139     }
    140 
    141     function get_trac_milestones() {
    142         // Only show 3.8+, when this feature was launched.
    143         return $this->trac->get_results( "SELECT name, completed FROM milestone
    144             WHERE name NOT IN ('WordPress.org', '3.5.3', '3.6.2', '3.7.2') AND (completed = 0 OR completed >= 1386864000000000)
    145             ORDER BY (completed = 0) DESC, name DESC", OBJECT_K );
    146     }
    147 
    148     function get_trac_notifications_for_user( $username ) {
    149         $rows = $this->trac->get_results( $this->trac->prepare( "SELECT type, value FROM _notifications WHERE username = %s ORDER BY type ASC, value ASC", $username ) );
    150         $notifications = array( 'component' => array(), 'milestone' => array(), 'focus' => array(), 'newticket' => array() );
    151 
    152         foreach ( $rows as $row ) {
    153             $notifications[ $row->type ][ $row->value ] = true;
    154         }
    155         $notifications['newticket'] = ! empty( $notifications['newticket']['1'] );
    156 
    157         return $notifications;
    158     }
    159 
    160     function get_trac_ticket_subscription_status_for_user( $ticket_id, $username ) {
    161         $status = $this->trac->get_var( $this->trac->prepare( "SELECT status FROM _ticket_subs WHERE username = %s AND ticket = %s", $username, $ticket_id ) );
    162         if ( null !== $status ) {
    163             $status = (int) $status;
    164         }
    165         return $status;
    166     }
    167 
    168     function get_trac_ticket_subscriptions_for_user( $username ) {
    169         return $this->trac->get_col( $this->trac->prepare( "SELECT ticket FROM _ticket_subs WHERE username = %s AND status = 1", $username ) );
    170     }
    171 
    172114    function trac_notifications_box_actions() {
    173115        send_origin_headers();
     
    179121        $username = wp_get_current_user()->user_login;
    180122
    181         $ticket_id = absint( $_POST['trac-ticket-sub'] );
    182         if ( ! $ticket_id ) {
     123        $ticket = absint( $_POST['trac-ticket-sub'] );
     124        if ( ! $ticket ) {
    183125            wp_send_json_error();
    184126        }
    185127
    186         $ticket = $this->get_trac_ticket( $ticket_id );
    187         if ( ! $ticket ) {
     128        if ( ! $this->api->get_trac_ticket( $ticket ) ) {
    188129            wp_send_json_error();
    189130        }
     
    198139            case 'block' :
    199140                $status = $action === 'subscribe' ? 1 : 0;
    200                 $this->trac->delete( '_ticket_subs', array( 'username' => $username, 'ticket' => $ticket_id ) );
    201                 $result = $this->trac->insert( '_ticket_subs', array( 'username' => $username, 'ticket' => $ticket_id, 'status' => $status ) );
     141                $result = $this->api->update_subscription( $username, $ticket, $status );
    202142                break;
    203143
     
    205145            case 'unblock' :
    206146                $status = $action === 'unsubscribe' ? 1 : 0;
    207                 $result = $this->trac->delete( '_ticket_subs', array( 'username' => $username, 'ticket' => $ticket_id, 'status' => $status ) );
     147                $result = $this->api->delete_subscription( $username, $ticket, $status );
    208148                break;
    209149        }
     
    229169        }
    230170
    231         $subscribed_tickets = $this->get_trac_ticket_subscriptions_for_user( $username );
     171        $subscribed_tickets = $this->api->get_trac_ticket_subscriptions_for_user( $username );
    232172        if ( ! is_array( $subscribed_tickets ) ) {
    233173            wp_send_json_error();
     
    250190            exit;
    251191        }
    252         $ticket = $this->get_trac_ticket( $ticket_id );
     192        $ticket = $this->api->get_trac_ticket( $ticket_id );
    253193        if ( ! $ticket ) {
    254194            exit;
    255195        }
    256196
    257         $focuses = explode( ', ', $this->get_trac_ticket_focuses( $ticket_id ) );
    258 
    259         $notifications = $this->get_trac_notifications_for_user( $username );
    260 
    261         $ticket_sub = $this->get_trac_ticket_subscription_status_for_user( $ticket_id, $username );
    262 
    263         $ticket_subscriptions = $this->get_trac_ticket_subscriptions( $ticket_id );
     197        $focuses = explode( ', ', $this->api->get_trac_ticket_focuses( $ticket_id ) );
     198
     199        $notifications = $this->api->get_trac_notifications_for_user( $username );
     200
     201        $ticket_sub = $this->api->get_trac_ticket_subscription_status_for_user( $ticket_id, $username );
     202
     203        $ticket_subscriptions = $this->api->get_trac_ticket_subscriptions( $ticket_id );
    264204        $stars = $ticket_subscriptions['starred'];
    265205        $star_count = count( $stars );
    266206
    267         $participants = $this->get_trac_ticket_participants( $ticket_id );
     207        $participants = $this->api->get_trac_ticket_participants( $ticket_id );
    268208
    269209        $unblocked_participants = array_diff( $participants, $ticket_subscriptions['blocked'] );
     
    368308        $send = array( 'notifications-box' => ob_get_clean() );
    369309        if ( isset( $this->components ) ) {
    370             $send['maintainers'] = $this->components->get_maintainers_by_component( $ticket->component );
     310            $send['maintainers'] = $this->components->get_component_maintainers( $ticket->component );
    371311        }
    372312        wp_send_json_success( $send );
     
    379319        }
    380320
    381         $previous_tickets = $this->trac->get_results( $this->trac->prepare( "SELECT id, summary, type, status, resolution
    382             FROM ticket WHERE reporter = %s AND id <= %d LIMIT 5", $ticket->reporter, $ticket->id ) );
    383 
    384         if ( count( $previous_tickets ) >= 5 ) {
     321        $activity = $this->api->get_reporter_past_activity( $ticket->reporter, $ticket->id );
     322
     323        if ( count( $activity['tickets'] ) >= 5 ) {
    385324            return;
    386325        }
    387326
    388         if ( 1 == count( $previous_tickets ) ) {
    389             $previous_comments = $this->trac->get_var( $this->trac->prepare( "SELECT ticket FROM ticket_change
    390                 WHERE field = 'comment' AND author = %s AND ticket <> %d LIMIT 1", $ticket->reporter, $ticket->id ) );
    391 
     327        if ( 1 == count( $activity['tickets'] ) ) {
    392328            $output = '<strong>Make sure ' . $ticket->reporter . ' receives a warm welcome.</strong><br/>';
    393329
    394             if ( $previous_comments ) {
     330            if ( ! empty( $activity['comments'] ) ) {
    395331                $output .= 'They&#8217;ve commented before, but it&#8127;s their first ticket!';
    396332            } else {
     
    400336            $mapping = array( 2 => 'second', 3 => 'third', 4 => 'fourth' );
    401337
    402             $output = '<strong>This is only ' . $ticket->reporter . '&#8217;s ' . $mapping[ count( $previous_tickets ) ] . ' ticket!</strong><br/>Previously:';
    403 
    404                 foreach ( $previous_tickets as $t ) {
     338            $output = '<strong>This is only ' . $ticket->reporter . '&#8217;s ' . $mapping[ count( $activity['tickets'] ) ] . ' ticket!</strong><br/>Previously:';
     339
     340                foreach ( $activity['tickets'] as $t ) {
    405341                    if ( $t->id != $ticket->id ) {
    406342                        $output .= ' ' . $this->ticket_link( $t );
     
    424360
    425361        ob_start();
    426         $components = $this->get_trac_components();
    427         $milestones = $this->get_trac_milestones();
     362        $components = $this->api->get_components();
     363        $milestones = $this->api->get_milestones();
    428364        $focuses = $this->get_trac_focuses();
    429365
    430366        $username = wp_get_current_user()->user_login;
    431         $notifications = $this->get_trac_notifications_for_user( $username );
     367        $notifications = $this->api->get_trac_notifications_for_user( $username );
    432368
    433369        if ( $_POST && isset( $_POST['trac-nonce'] ) ) {
    434370            check_admin_referer( 'save-trac-notifications', 'trac-nonce' );
     371
     372            $changes = array();
    435373
    436374            foreach ( array( 'milestone', 'component', 'focus' ) as $type ) {
     
    438376                    foreach ( $_POST['notifications'][ $type ] as $value => $on ) {
    439377                        if ( empty( $notifications[ $type ][ $value ] ) ) {
    440                             $this->trac->insert( '_notifications', compact( 'username', 'type', 'value' ) );
     378                            $changes['insert'][] = compact( 'username', 'type', 'value' );
    441379                            $notifications[ $type ][ $value ] = true;
    442380                        }
     
    446384                foreach ( $notifications[ $type ] as $value => $on ) {
    447385                    if ( empty( $_POST['notifications'][ $type ][ $value ] ) ) {
    448                         $this->trac->delete( '_notifications', compact( 'username', 'type', 'value' ) );
     386                        $changes['delete'][] = compact( 'username', 'type', 'value' );
    449387                        unset( $notifications[ $type ][ $value ] );
    450388                    }
     
    452390            }
    453391            if ( empty( $_POST['notifications']['newticket'] ) && ! empty( $notifications['newticket'] ) ) {
    454                 $this->trac->delete( '_notifications', array( 'username' => $username, 'type' => 'newticket' ) );
     392                $changes['delete'][] = array( 'username' => $username, 'type' => 'newticket' );
    455393                $notifications[ 'newticket' ] = false;
    456394            } elseif ( ! empty( $_POST['notifications']['newticket'] ) && empty( $notifications['newticket'] ) ) {
    457                 $this->trac->insert( '_notifications', array( 'username' => $username, 'type' => 'newticket', 'value' => '1' ) );
     395                $changes['insert'][] = array( 'username' => $username, 'type' => 'newticket', 'value' => '1' );
    458396                $notifications[ 'newticket' ] = true;
    459397            }
    460         }
     398            $this->api->update_notifications( $changes );
     399        }
     400
    461401        ?>
    462402
Note: See TracChangeset for help on using the changeset viewer.