From e46354a522ae8c8cddb5882d0570d8d0cfa5e1fd Mon Sep 17 00:00:00 2001 From: Michael Date: Wed, 1 Jun 2022 22:11:03 +0000 Subject: [PATCH] New command to merge duplicated contacts --- src/Console/MergeContacts.php | 165 ++++++++++++++++++++++++++++++++++ src/Core/Console.php | 2 + src/Database/DBA.php | 5 +- src/Database/Database.php | 11 ++- view/lang/C/messages.po | 57 +++++++++++- 5 files changed, 235 insertions(+), 5 deletions(-) create mode 100644 src/Console/MergeContacts.php diff --git a/src/Console/MergeContacts.php b/src/Console/MergeContacts.php new file mode 100644 index 0000000000..c3588545f4 --- /dev/null +++ b/src/Console/MergeContacts.php @@ -0,0 +1,165 @@ +. + * + */ + +namespace Friendica\Console; + +use Friendica\Core\L10n; +use Friendica\Database\Database; + +/** + * tool to find and merge duplicated contact entries. + */ +class MergeContacts extends \Asika\SimpleConsole\Console +{ + protected $helpOptions = ['h', 'help', '?']; + + /** + * @var $dba Database + */ + private $dba; + + /** + * @var L10n + */ + private $l10n; + + protected function getHelp() + { + $help = <<dba = $dba; + $this->l10n = $l10n; + } + + protected function doExecute() + { + $duplicates = $this->dba->p("SELECT COUNT(*) AS `total`, `uri-id`, MAX(`url`) AS `url` FROM `contact` WHERE `uid` = 0 GROUP BY `uri-id` HAVING total > 1"); + while ($duplicate = $this->dba->fetch($duplicates)) { + $this->out($this->l10n->t('%d %s, %d duplicates.', $duplicate['uri-id'], $duplicate['url'], $duplicate['total'])); + if ($this->getOption(['e', 'execute'], false)) { + $this->mergeContacts($duplicate['uri-id']); + } + } + return 0; + } + + private function mergeContacts(int $uriid) + { + $first = $this->dba->selectFirst('contact', ['id', 'nurl', 'url'], ["`uri-id` = ? AND `nurl` != ? AND `url` != ?", $uriid, '', ''], ['order' => ['id']]); + if (empty($first)) { + $this->err($this->l10n->t('No valid first countact found for uri-id %d.', $uriid)); + return; + } + $this->out($first['url']); + + $duplicates = $this->dba->select('contact', ['id', 'nurl', 'url'], ['uri-id' => $uriid]); + while ($duplicate = $this->dba->fetch($duplicates)) { + if ($first['id'] == $duplicate['id']) { + continue; + } + if ($first['url'] != $duplicate['url']) { + $this->err($this->l10n->t('Wrong duplicate found for uri-id %d in %d (url: %s != %s).', $uriid, $duplicate['id'], $first['url'], $duplicate['url'])); + continue; + } + if ($first['nurl'] != $duplicate['nurl']) { + $this->err($this->l10n->t('Wrong duplicate found for uri-id %d in %d (nurl: %s != %s).', $uriid, $duplicate['id'], $first['nurl'], $duplicate['nurl'])); + continue; + } + $this->out($duplicate['id'] . "\t" . $duplicate['url']); + $this->mergeContactInTables($duplicate['id'], $first['id']); + } + } + + private function mergeContactInTables(int $from, int $to) + { + $this->out($from . "\t=> " . $to); + + foreach (['post', 'post-thread', 'post-thread-user', 'post-user'] as $table) { + foreach (['author-id', 'causer-id', 'owner-id'] as $field) { + $this->updateTable($table, $field, $from, $to, false); + } + } + + $this->updateTable('contact-relation', 'cid', $from, $to, true); + $this->updateTable('contact-relation', 'relation-cid', $from, $to, true); + $this->updateTable('event', 'cid', $from, $to, false); + $this->updateTable('fsuggest', 'cid', $from, $to, false); + $this->updateTable('group', 'cid', $from, $to, false); + $this->updateTable('group_member', 'contact-id', $from, $to, true); + $this->updateTable('intro', 'contact-id', $from, $to, false); + $this->updateTable('intro', 'suggest-cid', $from, $to, false); + $this->updateTable('mail', 'author-id', $from, $to, false); + $this->updateTable('mail', 'contact-id', $from, $to, false); + $this->updateTable('notification', 'actor-id', $from, $to, false); + $this->updateTable('photo', 'contact-id', $from, $to, false); + $this->updateTable('post-tag', 'cid', $from, $to, true); + $this->updateTable('post-user', 'contact-id', $from, $to, false); + $this->updateTable('post-thread-user', 'contact-id', $from, $to, false); + $this->updateTable('user-contact', 'cid', $from, $to, true); + + if (!$this->dba->delete('contact', ['id' => $from])) { + $this->err($this->l10n->t('Deletion of id %d failed', $from)); + } else { + $this->out($this->l10n->t('Deletion of id %d was successful', $from)); + } + } + + private function updateTable(string $table, string $field, int $from, int $to, bool $in_unique_key) + { + $this->out($this->l10n->t('Updating "%s" in "%s" from %d to %d', $field, $table, $from, $to), false); + if ($this->dba->exists($table, [$field => $from])) { + $this->out($this->l10n->t(' - found'), false); + if ($in_unique_key) { + $params = ['ignore' => true]; + } else { + $params = []; + } + if (!$this->dba->update($table, [$field => $to], [$field => $from], [], $params)) { + $this->out($this->l10n->t(' - failed'), false); + } else { + $this->out($this->l10n->t(' - success'), false); + } + if ($in_unique_key && $this->dba->exists($table, [$field => $from])) { + $this->dba->delete($table, [$field => $from]); + $this->out($this->l10n->t(' - deleted'), false); + } + } + $this->out($this->l10n->t(' - done')); + } +} diff --git a/src/Core/Console.php b/src/Core/Console.php index be5f315987..ebd4c51a9d 100644 --- a/src/Core/Console.php +++ b/src/Core/Console.php @@ -60,6 +60,7 @@ Commands: lock Edit site locks maintenance Set maintenance mode for this node movetoavatarcache Move cached avatars to the file based avatar cache + mergecontacts Merge duplicated contact entries user User management php2po Generate a messages.po file from a strings.php file po2php Generate a strings.php file from a messages.po file @@ -93,6 +94,7 @@ HELP; 'globalcommunitysilence' => Friendica\Console\GlobalCommunitySilence::class, 'lock' => Friendica\Console\Lock::class, 'maintenance' => Friendica\Console\Maintenance::class, + 'mergecontacts' => Friendica\Console\MergeContacts::class, 'movetoavatarcache' => Friendica\Console\MoveToAvatarCache::class, 'php2po' => Friendica\Console\PhpToPo::class, 'postupdate' => Friendica\Console\PostUpdate::class, diff --git a/src/Database/DBA.php b/src/Database/DBA.php index b72cc632a4..a0eb1c3ece 100644 --- a/src/Database/DBA.php +++ b/src/Database/DBA.php @@ -422,13 +422,14 @@ class DBA * @param array $fields contains the fields that are updated * @param array $condition condition array with the key values * @param array|boolean $old_fields array with the old field values that are about to be replaced (true = update on duplicate, false = don't update identical fields) + * @param array $params Parameters: "ignore" If set to "true" then the update is done with the ignore parameter * * @return boolean was the update successfull? * @throws \Exception */ - public static function update($table, $fields, $condition, $old_fields = []) + public static function update($table, $fields, $condition, $old_fields = [], $params = []) { - return DI::dba()->update($table, $fields, $condition, $old_fields); + return DI::dba()->update($table, $fields, $condition, $old_fields, $params); } /** diff --git a/src/Database/Database.php b/src/Database/Database.php index 41733f5f95..671425f9d1 100644 --- a/src/Database/Database.php +++ b/src/Database/Database.php @@ -1284,11 +1284,12 @@ class Database * @param array $fields contains the fields that are updated * @param array $condition condition array with the key values * @param array|boolean $old_fields array with the old field values that are about to be replaced (true = update on duplicate, false = don't update identical fields) + * @param array $params Parameters: "ignore" If set to "true" then the update is done with the ignore parameter * * @return boolean was the update successfull? * @throws \Exception */ - public function update($table, $fields, $condition, $old_fields = []) + public function update($table, $fields, $condition, $old_fields = [], $params = []) { if (empty($table) || empty($fields) || empty($condition)) { $this->logger->info('Table, fields and condition have to be set'); @@ -1325,7 +1326,13 @@ class Database $condition_string = DBA::buildCondition($condition); - $sql = "UPDATE " . $table_string . " SET " + if (!empty($params['ignore'])) { + $ignore = 'IGNORE '; + } else { + $ignore = ''; + } + + $sql = "UPDATE " . $ignore . $table_string . " SET " . implode(" = ?, ", array_map([DBA::class, 'quoteIdentifier'], array_keys($fields))) . " = ?" . $condition_string; diff --git a/view/lang/C/messages.po b/view/lang/C/messages.po index 777b011609..5fbaeeacc4 100644 --- a/view/lang/C/messages.po +++ b/view/lang/C/messages.po @@ -8,7 +8,7 @@ msgid "" msgstr "" "Project-Id-Version: 2022.05-rc\n" "Report-Msgid-Bugs-To: \n" -"POT-Creation-Date: 2022-05-31 13:27+0000\n" +"POT-Creation-Date: 2022-06-01 22:09+0000\n" "PO-Revision-Date: YEAR-MO-DA HO:MI+ZONE\n" "Last-Translator: FULL NAME \n" "Language-Team: LANGUAGE \n" @@ -1641,6 +1641,61 @@ msgstr "" msgid "The contact has been blocked from the node" msgstr "" +#: src/Console/MergeContacts.php:74 +#, php-format +msgid "%d %s, %d duplicates." +msgstr "" + +#: src/Console/MergeContacts.php:86 +#, php-format +msgid "No valid first countact found for uri-id %d." +msgstr "" + +#: src/Console/MergeContacts.php:97 +#, php-format +msgid "Wrong duplicate found for uri-id %d in %d (url: %s != %s)." +msgstr "" + +#: src/Console/MergeContacts.php:101 +#, php-format +msgid "Wrong duplicate found for uri-id %d in %d (nurl: %s != %s)." +msgstr "" + +#: src/Console/MergeContacts.php:137 +#, php-format +msgid "Deletion of id %d failed" +msgstr "" + +#: src/Console/MergeContacts.php:139 +#, php-format +msgid "Deletion of id %d was successful" +msgstr "" + +#: src/Console/MergeContacts.php:145 +#, php-format +msgid "Updating \"%s\" in \"%s\" from %d to %d" +msgstr "" + +#: src/Console/MergeContacts.php:147 +msgid " - found" +msgstr "" + +#: src/Console/MergeContacts.php:154 +msgid " - failed" +msgstr "" + +#: src/Console/MergeContacts.php:156 +msgid " - success" +msgstr "" + +#: src/Console/MergeContacts.php:160 +msgid " - deleted" +msgstr "" + +#: src/Console/MergeContacts.php:163 +msgid " - done" +msgstr "" + #: src/Console/MoveToAvatarCache.php:91 msgid "The avatar cache needs to be enabled to use this command." msgstr "" -- 2.39.5