<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head><meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>[12984] sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli: Translate: Add a command to detect duplicated translations</title>
</head>
<body>
<style type="text/css"><!--
#msg dl.meta { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; }
#msg dl.meta dt { float: left; width: 6em; font-weight: bold; }
#msg dt:after { content:':';}
#msg dl, #msg dt, #msg ul, #msg li, #header, #footer, #logmsg { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; }
#msg dl a { font-weight: bold}
#msg dl a:link { color:#fc3; }
#msg dl a:active { color:#ff0; }
#msg dl a:visited { color:#cc6; }
h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; }
#msg pre { white-space: pre-line; overflow: auto; background: #ffc; border: 1px #fa0 solid; padding: 6px; }
#logmsg { background: #ffc; border: 1px #fa0 solid; padding: 1em 1em 0 1em; }
#logmsg p, #logmsg pre, #logmsg blockquote { margin: 0 0 1em 0; }
#logmsg p, #logmsg li, #logmsg dt, #logmsg dd { line-height: 14pt; }
#logmsg h1, #logmsg h2, #logmsg h3, #logmsg h4, #logmsg h5, #logmsg h6 { margin: .5em 0; }
#logmsg h1:first-child, #logmsg h2:first-child, #logmsg h3:first-child, #logmsg h4:first-child, #logmsg h5:first-child, #logmsg h6:first-child { margin-top: 0; }
#logmsg ul, #logmsg ol { padding: 0; list-style-position: inside; margin: 0 0 0 1em; }
#logmsg ul { text-indent: -1em; padding-left: 1em; }#logmsg ol { text-indent: -1.5em; padding-left: 1.5em; }
#logmsg > ul, #logmsg > ol { margin: 0 0 1em 0; }
#logmsg pre { background: #eee; padding: 1em; }
#logmsg blockquote { border: 1px solid #fa0; border-left-width: 10px; padding: 1em 1em 0 1em; background: white;}
#logmsg dl { margin: 0; }
#logmsg dt { font-weight: bold; }
#logmsg dd { margin: 0; padding: 0 0 0.5em 0; }
#logmsg dd:before { content:'\00bb';}
#logmsg table { border-spacing: 0px; border-collapse: collapse; border-top: 4px solid #fa0; border-bottom: 1px solid #fa0; background: #fff; }
#logmsg table th { text-align: left; font-weight: normal; padding: 0.2em 0.5em; border-top: 1px dotted #fa0; }
#logmsg table td { text-align: right; border-top: 1px dotted #fa0; padding: 0.2em 0.5em; }
#logmsg table thead th { text-align: center; border-bottom: 1px solid #fa0; }
#logmsg table th.Corner { text-align: left; }
#logmsg hr { border: none 0; border-top: 2px dashed #fa0; height: 1px; }
#header, #footer { color: #fff; background: #636; border: 1px #300 solid; padding: 6px; }
#patch { width: 100%; }
#patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;}
#patch .propset h4, #patch .binary h4 {margin:0;}
#patch pre {padding:0;line-height:1.2em;margin:0;}
#patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;}
#patch .propset .diff, #patch .binary .diff {padding:10px 0;}
#patch span {display:block;padding:0 10px;}
#patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;}
#patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;}
#patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;}
#patch .lines, .info {color:#888;background:#fff;}
--></style>
<div id="msg">
<dl class="meta" style="font-size: 105%">
<dt style="float: left; width: 6em; font-weight: bold">Revision</dt> <dd><a style="font-weight: bold" href="http://meta.trac.wordpress.org/changeset/12984">12984</a><script type="application/ld+json">{"@context":"http://schema.org","@type":"EmailMessage","description":"Review this Commit","action":{"@type":"ViewAction","url":"http://meta.trac.wordpress.org/changeset/12984","name":"Review Commit"}}</script></dd>
<dt style="float: left; width: 6em; font-weight: bold">Author</dt> <dd>amieiro</dd>
<dt style="float: left; width: 6em; font-weight: bold">Date</dt> <dd>2023-11-30 12:21:25 +0000 (Thu, 30 Nov 2023)</dd>
</dl>
<pre style='padding-left: 1em; margin: 2em 0; border-left: 2px solid #ccc; line-height: 1.25; font-size: 105%; font-family: sans-serif'>Translate: Add a command to detect duplicated translations</pre>
<h3>Added Paths</h3>
<ul>
<li><a href="#sitestrunkwordpressorgpublic_htmlwpcontentpluginswporggpcustomizationsinccliclassduplicatetranslationscliphp">sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-duplicate-translations-cli.php</a></li>
<li><a href="#sitestrunkwordpressorgpublic_htmlwpcontentpluginswporggpcustomizationsinccliclassduplicatetranslationsphp">sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-duplicate-translations.php</a></li>
</ul>
</div>
<div id="patch">
<h3>Diff</h3>
<a id="sitestrunkwordpressorgpublic_htmlwpcontentpluginswporggpcustomizationsinccliclassduplicatetranslationscliphp"></a>
<div class="addfile"><h4 style="background-color: #eee; color: inherit; margin: 1em 0; padding: 1.3em; font-size: 115%">Added: sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-duplicate-translations-cli.php</h4>
<pre class="diff"><span>
<span class="info" style="display: block; padding: 0 10px; color: #888">--- sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-duplicate-translations-cli.php (rev 0)
+++ sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-duplicate-translations-cli.php 2023-11-30 12:21:25 UTC (rev 12984)
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -0,0 +1,67 @@
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+<?php
+/**
+ * This WP-CLI command detects duplicate translations in the database in "current" status.
+ *
+ * To execute this command, you need to use this text in the CLI:
+ *
+ * wp wporg-translate duplicate-translations --url=translate.wordpress.org
+ *
+ * @package WordPressdotorg\GlotPress\Customizations\CLI
+ */
+
+namespace WordPressdotorg\GlotPress\Customizations\CLI;
+
+use WP_CLI_Command;
+
+/**
+ * Class Duplicate_Translations_CLI
+ */
+class Duplicate_Translations_CLI extends WP_CLI_Command {
+ /**
+ * Detect duplicate translations in the database in "current" status and update them.
+ *
+ * ## OPTIONS
+ * [--<fix>]
+ * Update the duplicates, setting all translations to old except the last one.
+ *
+ * [--<notify>]
+ * Notify the duplicates in Slack.
+ *
+ * [--<verbose>]
+ * Show the results in the CLI.
+ *
+ * [--<print-sql>]
+ * Show the SQL queries to show and to update the duplicate entries.
+ *
+ * ## EXAMPLES
+ *
+ * wp wporg-translate duplicate-translations --url=translate.wordpress.org
+ * wp wporg-translate duplicate-translations --url=translate.wordpress.org --fix
+ * wp wporg-translate duplicate-translations --url=translate.wordpress.org --notify
+ * wp wporg-translate duplicate-translations --url=translate.wordpress.org --fix --verbose --print-sql
+ *
+ * @param array $args The arguments.
+ * @param array $assoc_args The associative arguments.
+ */
+ public function __invoke( $args, $assoc_args ) {
+ $update_values = false;
+ $notify = false;
+ $verbose = false;
+ $print_sql = false;
+ if ( array_key_exists( 'fix', $assoc_args ) ) {
+ $update_values = true;
+ }
+ if ( array_key_exists( 'notify', $assoc_args ) ) {
+ $notify = true;
+ }
+ if ( array_key_exists( 'verbose', $assoc_args ) ) {
+ $verbose = true;
+ }
+ if ( array_key_exists( 'print-sql', $assoc_args ) ) {
+ $print_sql = true;
+ }
+
+ $duplicates = new Duplicate_Translations();
+ $duplicates( $update_values, $notify, $verbose, $print_sql );
+ }
+}
</ins><span class="cx" style="display: block; padding: 0 10px">Property changes on: sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-duplicate-translations-cli.php
</span><span class="cx" style="display: block; padding: 0 10px">___________________________________________________________________
</span></span></pre></div>
<a id="svneolstyle"></a>
<div class="addfile"><h4 style="background-color: #eee; color: inherit; margin: 1em 0; padding: 1.3em; font-size: 115%">Added: svn:eol-style</h4></div>
<ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+native
</ins><span class="cx" style="display: block; padding: 0 10px">\ No newline at end of property
</span><a id="sitestrunkwordpressorgpublic_htmlwpcontentpluginswporggpcustomizationsinccliclassduplicatetranslationsphp"></a>
<div class="addfile"><h4 style="background-color: #eee; color: inherit; margin: 1em 0; padding: 1.3em; font-size: 115%">Added: sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-duplicate-translations.php</h4>
<pre class="diff"><span>
<span class="info" style="display: block; padding: 0 10px; color: #888">--- sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-duplicate-translations.php (rev 0)
+++ sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-duplicate-translations.php 2023-11-30 12:21:25 UTC (rev 12984)
</span><span class="lines" style="display: block; padding: 0 10px; color: #888">@@ -0,0 +1,277 @@
</span><ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+<?php
+/**
+ * This class detects duplicate translations in the database in "current" status and updates them.
+ *
+ * @package WordPressdotorg\GlotPress\Customizations\CLI
+ */
+
+namespace WordPressdotorg\GlotPress\Customizations\CLI;
+
+/**
+ * Class Duplicate_Translations
+ */
+class Duplicate_Translations {
+
+ /**
+ * The lower limit for translation_set_id.
+ *
+ * @var int
+ */
+ private int $low_limit = 0;
+
+ /**
+ * The upper limit for translation_set_id.
+ *
+ * Note: It must be greater than the highest translation set id available in the database.
+ *
+ * @var int
+ */
+ private int $high_limit;
+
+ /**
+ * The step size for iteration.
+ *
+ * With steps of 10,000 we sometimes have timeouts in the database.
+ *
+ * @var int
+ */
+ private int $step = 1000;
+
+ /**
+ * The duplicated entries.
+ *
+ * @var array
+ */
+ private array $duplicates = array();
+
+ /**
+ * If true, show the results in the CLI.
+ *
+ * @var bool
+ */
+ private bool $verbose = false;
+
+ /**
+ * If true, show the SQL queries to show and to update the duplicate entries.
+ *
+ * @var bool
+ */
+ private bool $print_sql = false;
+
+ /**
+ * Here we store the last MySQL error.
+ *
+ * @var string
+ */
+ private string $last_sql_error = '';
+
+ /**
+ * Duplicate_Translations constructor.
+ *
+ * @param bool $update_values If true, update the duplicate entries.
+ * @param bool $notify If true, notify the duplicate entries in Slack.
+ * @param bool $verbose If true, show the results in the CLI.
+ * @param bool $print_sql If true, show the SQL queries to show and to update the duplicate entries.
+ *
+ * @return void
+ */
+ public function __invoke( bool $update_values = false, bool $notify = false, bool $verbose = false, bool $print_sql = false ) {
+ global $wpdb;
+ $this->verbose = $verbose;
+ $this->print_sql = $print_sql;
+ $this->high_limit = $this->get_high_limit();
+ $this->duplicates = $this->get_duplicates();
+ if ( $update_values ) {
+ $this->update_duplicates();
+ }
+ if ( $notify ) {
+ $this->notify_duplicates();
+ }
+ }
+
+ /**
+ * Get the highest translation_set_id in the translations table.
+ *
+ * Round up the highest translation_set_id to the next multiple of $this->step.
+ *
+ * @return int
+ */
+ private function get_high_limit() {
+ global $wpdb;
+ $max_translation_set_id = $wpdb->get_var( "SELECT MAX(translation_set_id) FROM {$wpdb->gp_translations}" );
+ if ( $this->verbose ) {
+ // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
+ echo 'Highest translation_set_id: ' . number_format_i18n( $max_translation_set_id ) . "\n";
+ }
+
+ if ( $wpdb->last_error ) {
+ // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
+ echo "Error in the SQL query. Last query:\n" . $wpdb->last_query . "\nLast error: " . $wpdb->last_error . "\n";
+ $this->last_sql_error = $wpdb->last_error;
+ }
+
+ return ceil( $max_translation_set_id / $this->step ) * $this->step;
+ }
+
+ /**
+ * Get the duplicate entries from the translations table.
+ *
+ * @return array The duplicated entries.
+ */
+ private function get_duplicates(): array {
+ global $wpdb;
+ $duplicate_entries = array();
+
+ for ( $limit = $this->low_limit; $limit <= $this->high_limit; $limit += $this->step ) {
+ $upper_limit = $limit + $this->step - 1;
+ $prepared_query = $wpdb->prepare(
+ "SELECT original_id, translation_set_id
+ FROM {$wpdb->gp_translations}
+ WHERE translation_set_id BETWEEN %d AND %d
+ AND status = 'current'
+ GROUP BY original_id, translation_set_id
+ HAVING COUNT(*) > 1;",
+ $limit,
+ $upper_limit
+ );
+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ $results = $wpdb->get_results( $prepared_query, ARRAY_A );
+
+ if ( $wpdb->last_error ) {
+ // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
+ echo "Error in the SQL query. Last query:\n" . $wpdb->last_query . "\nLast error: " . $wpdb->last_error . "\n";
+ $this->last_sql_error = $wpdb->last_error;
+ break;
+ }
+
+ $duplicate_entries = array_merge( $duplicate_entries, $results );
+
+ if ( $this->print_sql ) {
+ foreach ( $results as $result ) {
+ // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
+ echo "- Translation_set_id: {$result['translation_set_id']}, Original_id: {$result['original_id']} -> ";
+ $prepared_query = $wpdb->prepare("SELECT * FROM `{$wpdb->gp_translations}` WHERE `original_id` = %d AND `translation_set_id` = %d AND `status`='current';",
+ $result['original_id'],
+ $result['translation_set_id'],
+ );
+ echo $prepared_query . "\n";
+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ $duplicated = $wpdb->get_results( $prepared_query, ARRAY_A );
+ foreach ( $duplicated as $entry ) {
+ // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
+ echo " - translation_id: {$entry['id']}, date_added: {$entry['date_added']}, date_modified: {$entry['date_modified']}, user_id: {$entry['user_id']}, user_id_last_modified: {$entry['user_id_last_modified']}, translation: {$entry['translation_0']}\n";
+ }
+ }
+ }
+ }
+ if ( $this->verbose ) {
+ // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
+ echo 'Number of duplicate entries: ' . number_format_i18n( count( $duplicate_entries ) ) . "\n";
+ }
+ return $duplicate_entries;
+ }
+
+ /**
+ * Update the duplicated entries.
+ *
+ * Set all translations to old except the last one, updating the date_modified and user_id_last_modified fields.
+ *
+ * @return void
+ */
+ private function update_duplicates() {
+ global $wpdb;
+
+ foreach ( $this->duplicates as $duplicate ) {
+ $id_to_not_update = $wpdb->get_var(
+ $wpdb->prepare("
+ SELECT id
+ FROM {$wpdb->gp_translations}
+ WHERE original_id = %d
+ AND translation_set_id = %d
+ AND status = 'current'
+ ORDER BY date_modified DESC, id DESC
+ LIMIT 1
+ ",
+ $duplicate['original_id'],
+ $duplicate['translation_set_id']
+ )
+ );
+
+ $update_query = $wpdb->prepare("
+ UPDATE {$wpdb->gp_translations}
+ SET status = 'old',
+ user_id_last_modified = NULL,
+ date_modified = NOW()
+ WHERE original_id = %d
+ AND translation_set_id = %d
+ AND id != %d
+ AND status = 'current';
+ ",
+ $duplicate['original_id'],
+ $duplicate['translation_set_id'],
+ $id_to_not_update
+ );
+
+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
+ $wpdb->query( $update_query );
+
+ if ( $wpdb->last_error ) {
+ // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
+ echo "Error in the SQL query. Last query:\n" . $wpdb->last_query . "\nLast error: " . $wpdb->last_error . "\n";
+ $this->last_sql_error = $wpdb->last_error;
+ break;
+ }
+
+ if ( $this->print_sql ) {
+ // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
+ echo "- Translation_set_id: {$duplicate['translation_set_id']}, Original_id: {$duplicate['original_id']} -> ";
+ // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
+ echo $update_query . "\n";
+ }
+ }
+
+ if ( $this->verbose ) {
+ // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
+ echo 'Number of translations updated: ' . number_format_i18n( count( $this->duplicates ) ) . "\n";
+ }
+ }
+
+ /**
+ * Notify the duplicates in Slack.
+ *
+ * @return void
+ */
+ private function notify_duplicates() {
+ $mysql_error = '';
+ $message = '';
+ $matrix_room = 'polyglots-duplicated-translations';
+ $send_result = false;
+
+ if ( $this->last_sql_error ) {
+ $mysql_error = "The last MySQL error was: " . $this->last_sql_error . "\n";
+ }
+
+ if ( 0 == count( $this->duplicates ) ) {
+ $message = "There are no duplicate translations in the database with *current* status.\n\n";
+ } else {
+ $message = "There are " . count( $this->duplicates ) . " duplicate translations in the database with *current* status.\n\n";
+ $message .= "Execute `wp wporg-translate duplicate-translations --url=translate.wordpress.org --verbose` to show the duplicate entries.\n\n";
+ $message .= "Execute `wp wporg-translate duplicate-translations --url=translate.wordpress.org --fix` to update the duplicate entries, solving the problem.\n\n";
+ }
+ $message .= $mysql_error;
+
+ require_once '/home/api/public_html/includes/matrix/poster.php';
+ $send_result = \DotOrg\Matrix\Poster::force_send( $matrix_room, $message );
+
+ if ( $this->verbose ) {
+ if ( $send_result ) {
+ // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
+ echo "Message sent to the " . $matrix_room . ' room: ' . $message . "\n";
+ } else {
+ // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped
+ echo "Message not sent to the " . $matrix_room . ' room: ' . $message . "\n";
+ }
+ }
+
+ }
+}
</ins><span class="cx" style="display: block; padding: 0 10px">Property changes on: sites/trunk/wordpress.org/public_html/wp-content/plugins/wporg-gp-customizations/inc/cli/class-duplicate-translations.php
</span><span class="cx" style="display: block; padding: 0 10px">___________________________________________________________________
</span></span></pre></div>
<a id="svneolstyle"></a>
<div class="addfile"><h4 style="background-color: #eee; color: inherit; margin: 1em 0; padding: 1.3em; font-size: 115%">Added: svn:eol-style</h4></div>
<ins style="background-color: #dfd; text-decoration:none; display:block; padding: 0 10px">+native
</ins><span class="cx" style="display: block; padding: 0 10px">\ No newline at end of property
</span></div>
</body>
</html>