Santhosh has uploaded a new change for review.
https://gerrit.wikimedia.org/r/238114
Change subject: Stats: Optimize the query behind contenttranslationlangtrend API
......................................................................
Stats: Optimize the query behind contenttranslationlangtrend API
Query simplified by avoiding subquery, single method of both
published and draft stats.
Bug: T111943
Change-Id: I1503d7bf0f677955c7e740cd0290e02f398a4edc
---
M api/ApiQueryContentTranslationLanguageTrend.php
M includes/Translation.php
2 files changed, 36 insertions(+), 114 deletions(-)
git pull
ssh://gerrit.wikimedia.org:29418/mediawiki/extensions/ContentTranslation
refs/changes/14/238114/1
diff --git a/api/ApiQueryContentTranslationLanguageTrend.php
b/api/ApiQueryContentTranslationLanguageTrend.php
index a790c47..2ade9f4 100644
--- a/api/ApiQueryContentTranslationLanguageTrend.php
+++ b/api/ApiQueryContentTranslationLanguageTrend.php
@@ -33,8 +33,8 @@
$interval = $params['interval'];
$data = array(
- 'translations' => Translation::getPublishTrend(
$source, $target, $interval ),
- 'drafts' => Translation::getDraftTrend( $source,
$target, $interval ),
+ 'translations' => Translation::getTrendByStatus(
$source, $target, 'published', $interval ),
+ 'drafts' => Translation::getTrendByStatus( $source,
$target, 'draft', $interval ),
);
if ( $target !== null ) {
diff --git a/includes/Translation.php b/includes/Translation.php
index 3a7ba17..95f6699 100644
--- a/includes/Translation.php
+++ b/includes/Translation.php
@@ -211,86 +211,6 @@
}
/**
- * Get time-wise cumulative number of drafts for given
- * language pairs, with given interval.
- * @param string $source Source language code
- * @param string $target Target language code
- * @param string $interval 'weekly' or 'monthly' trend
- * @return array
- */
- public static function getDraftTrend( $source, $target, $interval ) {
- $dbr = Database::getConnection( DB_SLAVE );
-
- $draftCondition = $dbr->makeList(
- array(
- 'translation_status' => 'draft',
- 'translation_target_url IS NULL'
- ),
- LIST_AND
- );
-
- $conditions = array();
- $conditions[] = $draftCondition;
- if ( $source !== null ) {
- $conditions['translation_source_language'] = $source;
- }
- if ( $target !== null ) {
- $conditions['translation_target_language'] = $target;
- }
-
- $options = null;
- if ( $interval === 'week' ) {
- $options = array(
- 'GROUP BY' => array(
-
'YEARWEEK(translation_last_updated_timestamp)',
- ),
- );
- } elseif ( $interval === 'month' ) {
- $options = array(
- 'GROUP BY' => array(
-
'YEAR(translation_last_updated_timestamp),
MONTH(translation_last_updated_timestamp)',
- ),
- );
- }
-
- $subQuery = $dbr->selectSQLText(
- 'cx_translations',
- 'count(*)',
- $dbr->makeList( array(
- 'translation_last_updated_timestamp <=
MAX(translations.translation_last_updated_timestamp)',
- $dbr->makeList( $conditions, LIST_AND ),
- ),
- LIST_AND )
- );
-
- $rows = $dbr->select(
- array( 'translations' => 'cx_translations' ),
- array(
-
"translations.translation_last_updated_timestamp AS date",
- '(' . $subQuery . ') translatons_count',
- ),
- $dbr->makeList( $conditions, LIST_AND ),
- __METHOD__,
- $options
- );
-
- $prev = 0;
- $result = array();
- foreach ( $rows as $row ) {
- $count = (int)$row->translatons_count;
- $time = self::getResultTime( $row->date, $interval );
- $result[$time] = array(
- 'count' => $count,
- 'delta' => $count - $prev,
- );
-
- $prev = $count;
- }
-
- return $result;
- }
-
- /**
* Get time-wise cumulative number of deletions for given
* language pairs, with given interval.
*/
@@ -355,71 +275,73 @@
/**
* Get time-wise cumulative number of translations for given
* language pairs, with given interval.
+ * @param string $source Source language code
+ * @param string $target Target language code
+ * @param string $status Status of translation. Either 'published' or
'draft'
+ * @param string $interval 'weekly' or 'monthly' trend
+ * @return array
*/
- public static function getPublishTrend( $source, $target, $interval ) {
+ public static function getTrendByStatus( $source, $target, $status,
$interval ) {
$dbr = Database::getConnection( DB_SLAVE );
- $publishedCondition = $dbr->makeList(
- array(
- 'translation_status' => 'published',
- 'translation_target_url IS NOT NULL',
- ),
- LIST_OR
- );
$conditions = array();
- $conditions[] = $publishedCondition;
+ if ( $status === 'published' ) {
+ $conditions[] = $dbr->makeList(
+ array(
+ 'translation_status' => 'published',
+ 'translation_target_url IS NOT NULL',
+ ),
+ LIST_OR
+ );
+ } else {
+ $conditions[] = $dbr->makeList(
+ array(
+ 'translation_status' => 'draft',
+ 'translation_target_url IS NULL'
+ ),
+ LIST_AND
+ );
+ }
+
if ( $source !== null ) {
$conditions['translation_source_language'] = $source;
}
if ( $target !== null ) {
$conditions['translation_target_language'] = $target;
}
-
- $options = null;
+ $groupBy = null;
if ( $interval === 'week' ) {
- $options = array(
+ $groupBy = array(
'GROUP BY' => array(
'YEARWEEK(translation_last_updated_timestamp)',
),
);
} elseif ( $interval === 'month' ) {
- $options = array(
+ $groupBy = array(
'GROUP BY' => array(
-
'YEAR(translation_last_updated_timestamp),
MONTH(translation_last_updated_timestamp)',
+
'YEAR(translation_last_updated_timestamp), MONTH(ar_timestamp)',
),
);
}
- $subQuery = $dbr->selectSQLText(
- 'cx_translations',
- 'count(*)',
- $dbr->makeList( array(
- 'translation_last_updated_timestamp <=
MAX(translations.translation_last_updated_timestamp)',
- $dbr->makeList( $conditions, LIST_AND ),
- ),
- LIST_AND )
- );
$rows = $dbr->select(
- array( 'translations' => 'cx_translations' ),
- array(
-
"translations.translation_last_updated_timestamp AS date",
- '(' . $subQuery . ') translatons_count',
- ),
+ array( 'cx_translations' ),
+ array( 'translation_last_updated_timestamp',
'count(translation_id) as count' ),
$dbr->makeList( $conditions, LIST_AND ),
__METHOD__,
- $options
+ $groupBy
);
$prev = 0;
+ $count = 0;
$result = array();
foreach ( $rows as $row ) {
- $count = (int)$row->translatons_count;
- $time = self::getResultTime( $row->date, $interval );
+ $count += (int)$row->count;
+ $time = self::getResultTime(
$row->translation_last_updated_timestamp, $interval );
$result[$time] = array(
'count' => $count,
'delta' => $count - $prev,
);
-
$prev = $count;
}
--
To view, visit https://gerrit.wikimedia.org/r/238114
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I1503d7bf0f677955c7e740cd0290e02f398a4edc
Gerrit-PatchSet: 1
Gerrit-Project: mediawiki/extensions/ContentTranslation
Gerrit-Branch: master
Gerrit-Owner: Santhosh <santhosh.thottin...@gmail.com>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
