Changeset 1881

Timestamp:

09/17/2015 03:05:18 AM (10 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:

• wporg-project-stats.php (modified) (8 diffs)
• wporg-stats-overview.php (modified) (2 diffs)

sites/trunk/translate.wordpress.org/includes/gp-plugins/wporg-project-stats.php

@@ -8 +8 @@
  * The datbase update is delayed until shutdown to bulk-update the database during imports.
  *
+ * NOTE: The counts includes all sub-projects in the count, as that's more useful for querying (top-level projects excluded)
+ * for example, wp-plugins won't exist, but wp-plugins/akismet will include wp-plugins/akismet/stable wp-plugins/akismet/stable-readme
+ *
  * @author dd32
  */
@@ -13 +16 @@
     var $id = 'wporg-project-stats';
 
-    private $projects_to_update = array();
-    private $translation_sets_to_update = array();
+    public $projects_to_update = array();
 
     function __construct() {
@@ -30 +32 @@
 
     function translation_created( $translation ) {
-        $this->translation_sets_to_update[ $translation->translation_set_id ] = true;
+        $set = GP::$translation_set->get( $translation->translation_set_id );
+        $this->projects_to_update[ $set->project_id ][ $set->locale . '/' . $set->slug ] = true;
     }
 
     function translation_saved( $translation ) {
-        $this->translation_sets_to_update[ $translation->translation_set_id ] = true;
+        $set = GP::$translation_set->get( $translation->translation_set_id );
+        $this->projects_to_update[ $set->project_id ][ $set->locale . '/' . $set->slug ] = true;
     }
 
@@ -41 +45 @@
     }
 
+    // Counts up all the 
+    function get_project_translation_counts( $project_id, $locale, $locale_slug, &$counts = array() ) {
+        if ( ! $counts ) {
+            $counts = array( 'all' => 0, 'current' => 0, 'waiting' => 0, 'fuzzy' => 0, 'warning' => 0, 'untranslated' => 0 );
+        }
+
+        // Not all projects have translation sets
+        $set = GP::$translation_set->by_project_id_slug_and_locale( $project_id, $locale_slug, $locale );
+        if ( $set ) {
+            // Force a refresh of the translation set counts
+            wp_cache_delete( $set->id, 'translation_set_status_breakdown' );
+
+            $counts['all'] += $set->all_count();
+            $counts['current'] += $set->current_count();
+            $counts['waiting'] += $set->waiting_count();
+            $counts['fuzzy'] += $set->fuzzy_count();
+            $counts['warnings'] += $set->warnings_count();
+            $counts['untranslated'] += $set->untranslated_count();
+        }
+
+        // Fetch the strings from the sub projects too
+        foreach ( GP::$project->get( $project_id )->sub_projects() as $project ) {
+            if ( ! $project->active ) {
+                continue;
+            }
+            $this->get_project_translation_counts( $project->id, $locale, $locale_slug, $counts );
+        }
+
+        return $counts;
+    }
+
     function shutdown() {
         global $gpdb;
         $values = array();
 
-        // Convert projects to a list of sets
-        foreach ( $this->projects_to_update as $project_id => $dummy ) {
-            foreach ( GP::$translation_set->by_project_id( $project_id ) as $set ) {
-                $this->translation_sets_to_update[ $set->id ] = true;
+        // If a project is `true` then we need to fetch all translation sets for it.
+        foreach ( $this->projects_to_update as $project_id => $set_data ) {
+            if ( true === $set_data ) {
+                $this->projects_to_update[ $project_id ] = array();
+                foreach ( GP::$translation_set->by_project_id( $project_id ) as $set ) {
+                    $this->projects_to_update[ $project_id ][ $set->locale . '/' . $set->slug ] = true;
+                }
             }
-            unset( $this->projects_to_update[ $project_id ] );
+    
         }
 
-        foreach ( $this->translation_sets_to_update as $set_id => $dummy ) {
-            $set = GP::$translation_set->get( $set_id );
+        // Update all parent projects as well.
+        // This does NOT update a root parent (ie. ! $parent_project_id) as we use those as grouping categories.
+        $projects = $this->projects_to_update;
+        foreach ( $projects as $project_id => $data ) {
+            // Do all parents
+            $project = GP::$project->get( $project_id );
+            while ( $project ) {
+                $project = GP::$project->get( $project->parent_project_id );
+                if ( $project->parent_project_id ) {
+                    $projects[ $project->id ] = $data;
+                } else {
+                    break;
+                }
+            }
+        }
+        $this->projects_to_update += $projects;
 
-            unset( $this->translation_sets_to_update[ $set_id ] );
+        foreach ( $this->projects_to_update as $project_id => $locale_sets ) {
+            $locale_sets = array_keys( $locale_sets );
+            $locale_sets = array_map( function( $set ) { return explode( '/', $set ); }, $locale_sets );
 
-            if ( ! $set ) {
-                continue;
+            foreach ( $locale_sets as $locale_set ) {
+                list( $locale, $locale_slug ) = $locale_set;
+                $counts = $this->get_project_translation_counts( $project_id, $locale, $locale_slug );
+    
+                $values[] = $gpdb->prepare( '(%d, %s, %s, %d, %d, %d, %d, %d, %d)',
+                    $project_id,
+                    $locale,
+                    $locale_slug,
+                    $counts['all'],
+                    $counts['current'],
+                    $counts['waiting'],
+                    $counts['fuzzy'],
+                    $counts['warning'],
+                    $counts['untranslated']
+                );
             }
 
-            $values[] = $gpdb->prepare( '(%d, %d, %d, %d, %d, %d, %d, %d)',
-                $set->project_id,
-                $set->id,
-                $set->all_count(),
-                $set->current_count(),
-                $set->waiting_count(),
-                $set->fuzzy_count(),
-                $set->warnings_count(),
-                // Untranslated is ( all - current ), we really want ( all - current - waiting - fuzzy ) which is (untranslated - waiting - fuzzy )
-                $set->untranslated_count() - $set->waiting_count() - $set->fuzzy_count()
-            );
-
+            // If we're processing a large batch, add them as we go to avoid query lengths & memory limits
             if ( count( $values ) > 50 ) {
-                // If we're processing a large batch, add them as we go to avoid query lengths & memoryl imits
-                $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`)" );
+                $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`)" );
                 $values = array();
             }
@@ -82 +137 @@
 
         if ( $values ) {
-            $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`)" );
+            $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`)" );
         }
     }
@@ -95 +150 @@
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `project_id` int(10) unsigned NOT NULL,
-  `translation_set_id` int(10) unsigned NOT NULL,
+  `locale` varchar(10) NOT NULL,
+  `locale_slug` varchar(255) NOT NULL,
   `all` int(10) unsigned NOT NULL DEFAULT '0',
   `current` int(10) unsigned NOT NULL DEFAULT '0',
@@ -103 +159 @@
   `untranslated` int(10) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
-  UNIQUE KEY `project_translation_set` (`project_id`,`translation_set_id`),
+  UNIQUE KEY `project_locale` (`project_id`,`locale`,`locale_slug`),
   KEY `all` (`all`),
   KEY `current` (`current`),
@@ -113 +169 @@
 
 */
+

sites/trunk/translate.wordpress.org/includes/gp-plugins/wporg-stats-overview.php

@@ -44 +44 @@
         }
 
-        $all_project_ids = $sql_cases = array();
-        foreach ( $projects as $slug => &$project ) {
-            $project_ids = array();
+        // Load the projects for each display item
+        array_walk( $projects, function( &$project, $project_slug ) {
             if ( ! $project ) {
-                $project = GP::$project->by_path( $slug );
+                $project = GP::$project->by_path( $project_slug );
             }
-            $project_ids[] = $project->id;
+        } );
 
-            foreach ( $project->inclusive_sub_projects() as $sub ) {
-                if ( $sub->active ) {
-                    $project_ids[] = $sub->id;
-                }
-            }
-            unset( $sub );
-
-            $project_id_list = implode( ', ', array_map( 'intval', $project_ids ) );
-            $sql_cases[] = $gpdb->prepare( "WHEN ts.project_id IN( $project_id_list ) THEN %s", $slug );
-
-            $all_project_ids = array_merge( $all_project_ids, $project_ids );
-        }
-        unset( $slug, $project_ids, $subs, $project );
-
-        $sql_cases = implode( "\n", $sql_cases );
-        $all_project_ids = implode( ', ', array_map( 'intval', $all_project_ids ) );
-
+        $all_project_paths_sql = '"' . implode( '", "', array_keys( $projects ) ) . '"';
         $sql = "SELECT
-                CASE
-                    $sql_cases
-                END as project_slug,
-                s.locale as locale,
-                s.slug as locale_slug,
-                (100 * SUM(ts.current)/SUM(ts.all)) as percent_complete
-            FROM translate_project_translation_status ts
-                LEFT JOIN translate_translation_sets s ON ts.project_id = s.project_id AND ts.translation_set_id = s.id
+                path, locale, locale_slug,
+                (100 * stats.current/stats.all) as percent_complete
+            FROM {$gpdb->prefix}project_translation_status stats
+                LEFT JOIN {$gpdb->prefix}projects p ON stats.project_id = p.id
             WHERE
-                ts.project_id IN ( $all_project_ids )
-            GROUP BY project_slug, s.locale, s.slug";
+                p.path IN ( $all_project_paths_sql )";
 
         $rows = $gpdb->get_results( $sql );
@@ -91 +69 @@
                 $locale_key = $set->locale . '/' . $set->locale_slug;
             }
-            $translation_locale_statuses[ $locale_key ][ $set->project_slug ] = floor( (float) $set->percent_complete );
+            $translation_locale_statuses[ $locale_key ][ $set->path ] = floor( (float) $set->percent_complete );
         }
         unset( $rows, $locale_key, $set );