Making WordPress.org

Changeset 1881


Ignore:
Timestamp:
09/17/2015 03:05:18 AM (9 years ago)
Author:
dd32
Message:

Translate: Update the Project Stats plugin to store data in an easier to parse format.

  • Stores a Locale + Locale slug (ie. en-au/default) rather than a translation set id
  • Projects now include their sub-projects in their counts. A project no longer has to have sets directly on it for it's status to be queried.
  • Updates the Stats overview to take advantage of these optimizations.
Location:
sites/trunk/translate.wordpress.org/includes/gp-plugins
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • sites/trunk/translate.wordpress.org/includes/gp-plugins/wporg-project-stats.php

    r1827 r1881  
    88 * The datbase update is delayed until shutdown to bulk-update the database during imports.
    99 *
     10 * NOTE: The counts includes all sub-projects in the count, as that's more useful for querying (top-level projects excluded)
     11 * for example, wp-plugins won't exist, but wp-plugins/akismet will include wp-plugins/akismet/stable wp-plugins/akismet/stable-readme
     12 *
    1013 * @author dd32
    1114 */
     
    1316    var $id = 'wporg-project-stats';
    1417
    15     private $projects_to_update = array();
    16     private $translation_sets_to_update = array();
     18    public $projects_to_update = array();
    1719
    1820    function __construct() {
     
    3032
    3133    function translation_created( $translation ) {
    32         $this->translation_sets_to_update[ $translation->translation_set_id ] = true;
     34        $set = GP::$translation_set->get( $translation->translation_set_id );
     35        $this->projects_to_update[ $set->project_id ][ $set->locale . '/' . $set->slug ] = true;
    3336    }
    3437
    3538    function translation_saved( $translation ) {
    36         $this->translation_sets_to_update[ $translation->translation_set_id ] = true;
     39        $set = GP::$translation_set->get( $translation->translation_set_id );
     40        $this->projects_to_update[ $set->project_id ][ $set->locale . '/' . $set->slug ] = true;
    3741    }
    3842
     
    4145    }
    4246
     47    // Counts up all the
     48    function get_project_translation_counts( $project_id, $locale, $locale_slug, &$counts = array() ) {
     49        if ( ! $counts ) {
     50            $counts = array( 'all' => 0, 'current' => 0, 'waiting' => 0, 'fuzzy' => 0, 'warning' => 0, 'untranslated' => 0 );
     51        }
     52
     53        // Not all projects have translation sets
     54        $set = GP::$translation_set->by_project_id_slug_and_locale( $project_id, $locale_slug, $locale );
     55        if ( $set ) {
     56            // Force a refresh of the translation set counts
     57            wp_cache_delete( $set->id, 'translation_set_status_breakdown' );
     58
     59            $counts['all'] += $set->all_count();
     60            $counts['current'] += $set->current_count();
     61            $counts['waiting'] += $set->waiting_count();
     62            $counts['fuzzy'] += $set->fuzzy_count();
     63            $counts['warnings'] += $set->warnings_count();
     64            $counts['untranslated'] += $set->untranslated_count();
     65        }
     66
     67        // Fetch the strings from the sub projects too
     68        foreach ( GP::$project->get( $project_id )->sub_projects() as $project ) {
     69            if ( ! $project->active ) {
     70                continue;
     71            }
     72            $this->get_project_translation_counts( $project->id, $locale, $locale_slug, $counts );
     73        }
     74
     75        return $counts;
     76    }
     77
    4378    function shutdown() {
    4479        global $gpdb;
    4580        $values = array();
    4681
    47         // Convert projects to a list of sets
    48         foreach ( $this->projects_to_update as $project_id => $dummy ) {
    49             foreach ( GP::$translation_set->by_project_id( $project_id ) as $set ) {
    50                 $this->translation_sets_to_update[ $set->id ] = true;
     82        // If a project is `true` then we need to fetch all translation sets for it.
     83        foreach ( $this->projects_to_update as $project_id => $set_data ) {
     84            if ( true === $set_data ) {
     85                $this->projects_to_update[ $project_id ] = array();
     86                foreach ( GP::$translation_set->by_project_id( $project_id ) as $set ) {
     87                    $this->projects_to_update[ $project_id ][ $set->locale . '/' . $set->slug ] = true;
     88                }
    5189            }
    52             unset( $this->projects_to_update[ $project_id ] );
     90   
    5391        }
    5492
    55         foreach ( $this->translation_sets_to_update as $set_id => $dummy ) {
    56             $set = GP::$translation_set->get( $set_id );
     93        // Update all parent projects as well.
     94        // This does NOT update a root parent (ie. ! $parent_project_id) as we use those as grouping categories.
     95        $projects = $this->projects_to_update;
     96        foreach ( $projects as $project_id => $data ) {
     97            // Do all parents
     98            $project = GP::$project->get( $project_id );
     99            while ( $project ) {
     100                $project = GP::$project->get( $project->parent_project_id );
     101                if ( $project->parent_project_id ) {
     102                    $projects[ $project->id ] = $data;
     103                } else {
     104                    break;
     105                }
     106            }
     107        }
     108        $this->projects_to_update += $projects;
    57109
    58             unset( $this->translation_sets_to_update[ $set_id ] );
     110        foreach ( $this->projects_to_update as $project_id => $locale_sets ) {
     111            $locale_sets = array_keys( $locale_sets );
     112            $locale_sets = array_map( function( $set ) { return explode( '/', $set ); }, $locale_sets );
    59113
    60             if ( ! $set ) {
    61                 continue;
     114            foreach ( $locale_sets as $locale_set ) {
     115                list( $locale, $locale_slug ) = $locale_set;
     116                $counts = $this->get_project_translation_counts( $project_id, $locale, $locale_slug );
     117   
     118                $values[] = $gpdb->prepare( '(%d, %s, %s, %d, %d, %d, %d, %d, %d)',
     119                    $project_id,
     120                    $locale,
     121                    $locale_slug,
     122                    $counts['all'],
     123                    $counts['current'],
     124                    $counts['waiting'],
     125                    $counts['fuzzy'],
     126                    $counts['warning'],
     127                    $counts['untranslated']
     128                );
    62129            }
    63130
    64             $values[] = $gpdb->prepare( '(%d, %d, %d, %d, %d, %d, %d, %d)',
    65                 $set->project_id,
    66                 $set->id,
    67                 $set->all_count(),
    68                 $set->current_count(),
    69                 $set->waiting_count(),
    70                 $set->fuzzy_count(),
    71                 $set->warnings_count(),
    72                 // Untranslated is ( all - current ), we really want ( all - current - waiting - fuzzy ) which is (untranslated - waiting - fuzzy )
    73                 $set->untranslated_count() - $set->waiting_count() - $set->fuzzy_count()
    74             );
    75 
     131            // If we're processing a large batch, add them as we go to avoid query lengths & memory limits
    76132            if ( count( $values ) > 50 ) {
    77                 // If we're processing a large batch, add them as we go to avoid query lengths & memoryl imits
    78                 $gpdb->query( "INSERT INTO {$gpdb->project_translation_status} (`project_id`, `translation_set_id`, `all`, `current`, `waiting`, `fuzzy`, `warnings`, `untranslated` ) VALUES " . implode( ', ', $values ) . " ON DUPLICATE KEY UPDATE `all`=VALUES(`all`), `current`=VALUES(`current`), `waiting`=VAlUES(`waiting`), `fuzzy`=VALUES(`fuzzy`), `warnings`=VALUES(`warnings`), `untranslated`=VALUES(`untranslated`)" );
     133                $gpdb->query( "INSERT INTO {$gpdb->project_translation_status} (`project_id`, `locale`, `locale_slug`, `all`, `current`, `waiting`, `fuzzy`, `warnings`, `untranslated` ) VALUES " . implode( ', ', $values ) . " ON DUPLICATE KEY UPDATE `all`=VALUES(`all`), `current`=VALUES(`current`), `waiting`=VAlUES(`waiting`), `fuzzy`=VALUES(`fuzzy`), `warnings`=VALUES(`warnings`), `untranslated`=VALUES(`untranslated`)" );
    79134                $values = array();
    80135            }
     
    82137
    83138        if ( $values ) {
    84             $gpdb->query( "INSERT INTO {$gpdb->project_translation_status} (`project_id`, `translation_set_id`, `all`, `current`, `waiting`, `fuzzy`, `warnings`, `untranslated` ) VALUES " . implode( ', ', $values ) . " ON DUPLICATE KEY UPDATE `all`=VALUES(`all`), `current`=VALUES(`current`), `waiting`=VALUES(`waiting`), `fuzzy`=VALUES(`fuzzy`), `warnings`=VALUES(`warnings`), `untranslated`=VALUES(`untranslated`)" );
     139            $gpdb->query( "INSERT INTO {$gpdb->project_translation_status} (`project_id`, `locale`, `locale_slug`, `all`, `current`, `waiting`, `fuzzy`, `warnings`, `untranslated` ) VALUES " . implode( ', ', $values ) . " ON DUPLICATE KEY UPDATE `all`=VALUES(`all`), `current`=VALUES(`current`), `waiting`=VALUES(`waiting`), `fuzzy`=VALUES(`fuzzy`), `warnings`=VALUES(`warnings`), `untranslated`=VALUES(`untranslated`)" );
    85140        }
    86141    }
     
    95150  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    96151  `project_id` int(10) unsigned NOT NULL,
    97   `translation_set_id` int(10) unsigned NOT NULL,
     152  `locale` varchar(10) NOT NULL,
     153  `locale_slug` varchar(255) NOT NULL,
    98154  `all` int(10) unsigned NOT NULL DEFAULT '0',
    99155  `current` int(10) unsigned NOT NULL DEFAULT '0',
     
    103159  `untranslated` int(10) unsigned NOT NULL DEFAULT '0',
    104160  PRIMARY KEY (`id`),
    105   UNIQUE KEY `project_translation_set` (`project_id`,`translation_set_id`),
     161  UNIQUE KEY `project_locale` (`project_id`,`locale`,`locale_slug`),
    106162  KEY `all` (`all`),
    107163  KEY `current` (`current`),
     
    113169
    114170*/
     171
  • sites/trunk/translate.wordpress.org/includes/gp-plugins/wporg-stats-overview.php

    r1861 r1881  
    4444        }
    4545
    46         $all_project_ids = $sql_cases = array();
    47         foreach ( $projects as $slug => &$project ) {
    48             $project_ids = array();
     46        // Load the projects for each display item
     47        array_walk( $projects, function( &$project, $project_slug ) {
    4948            if ( ! $project ) {
    50                 $project = GP::$project->by_path( $slug );
     49                $project = GP::$project->by_path( $project_slug );
    5150            }
    52             $project_ids[] = $project->id;
     51        } );
    5352
    54             foreach ( $project->inclusive_sub_projects() as $sub ) {
    55                 if ( $sub->active ) {
    56                     $project_ids[] = $sub->id;
    57                 }
    58             }
    59             unset( $sub );
    60 
    61             $project_id_list = implode( ', ', array_map( 'intval', $project_ids ) );
    62             $sql_cases[] = $gpdb->prepare( "WHEN ts.project_id IN( $project_id_list ) THEN %s", $slug );
    63 
    64             $all_project_ids = array_merge( $all_project_ids, $project_ids );
    65         }
    66         unset( $slug, $project_ids, $subs, $project );
    67 
    68         $sql_cases = implode( "\n", $sql_cases );
    69         $all_project_ids = implode( ', ', array_map( 'intval', $all_project_ids ) );
    70 
     53        $all_project_paths_sql = '"' . implode( '", "', array_keys( $projects ) ) . '"';
    7154        $sql = "SELECT
    72                 CASE
    73                     $sql_cases
    74                 END as project_slug,
    75                 s.locale as locale,
    76                 s.slug as locale_slug,
    77                 (100 * SUM(ts.current)/SUM(ts.all)) as percent_complete
    78             FROM translate_project_translation_status ts
    79                 LEFT JOIN translate_translation_sets s ON ts.project_id = s.project_id AND ts.translation_set_id = s.id
     55                path, locale, locale_slug,
     56                (100 * stats.current/stats.all) as percent_complete
     57            FROM {$gpdb->prefix}project_translation_status stats
     58                LEFT JOIN {$gpdb->prefix}projects p ON stats.project_id = p.id
    8059            WHERE
    81                 ts.project_id IN ( $all_project_ids )
    82             GROUP BY project_slug, s.locale, s.slug";
     60                p.path IN ( $all_project_paths_sql )";
    8361
    8462        $rows = $gpdb->get_results( $sql );
     
    9169                $locale_key = $set->locale . '/' . $set->locale_slug;
    9270            }
    93             $translation_locale_statuses[ $locale_key ][ $set->project_slug ] = floor( (float) $set->percent_complete );
     71            $translation_locale_statuses[ $locale_key ][ $set->path ] = floor( (float) $set->percent_complete );
    9472        }
    9573        unset( $rows, $locale_key, $set );
Note: See TracChangeset for help on using the changeset viewer.