From f31e617f5d7a0908e82e8f62bf415635a02c57b1 Mon Sep 17 00:00:00 2001 From: Michael Date: Wed, 30 Nov 2022 05:59:27 +0000 Subject: [PATCH] Contact suggestions are now cached --- database.sql | 16 ++++++- doc/database.md | 1 + doc/database/db_account-suggestion.md | 32 +++++++++++++ src/Factory/Api/Mastodon/Status.php | 22 +++++++-- src/Model/Contact/Relation.php | 62 +++++++++++++++++++++---- src/Module/Api/Mastodon/Suggestions.php | 2 +- src/Module/Contact/Suggestions.php | 2 +- src/Security/Authentication.php | 4 ++ src/Worker/Cron.php | 2 + src/Worker/UpdateAllSuggestions.php | 41 ++++++++++++++++ src/Worker/UpdateSuggestions.php | 35 ++++++++++++++ static/dbstructure.config.php | 14 +++++- view/theme/vier/theme.php | 2 +- 13 files changed, 218 insertions(+), 17 deletions(-) create mode 100644 doc/database/db_account-suggestion.md create mode 100644 src/Worker/UpdateAllSuggestions.php create mode 100644 src/Worker/UpdateSuggestions.php diff --git a/database.sql b/database.sql index 4e940b3197..cf07937725 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2022.12-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1495 +-- DB_UPDATE_VERSION 1496 -- ------------------------------------------ @@ -309,6 +309,20 @@ CREATE TABLE IF NOT EXISTS `2fa_trusted_browser` ( FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Two-factor authentication trusted browsers'; +-- +-- TABLE account-suggestion +-- +CREATE TABLE IF NOT EXISTS `account-suggestion` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the account url', + `uid` mediumint unsigned NOT NULL COMMENT 'User ID', + `level` smallint unsigned COMMENT 'level of closeness', + `ignore` boolean NOT NULL DEFAULT '0' COMMENT 'If set, this account will not be suggested again', + PRIMARY KEY(`uid`,`uri-id`), + INDEX `uri-id_uid` (`uri-id`,`uid`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Account suggestion'; + -- -- TABLE account-user -- diff --git a/doc/database.md b/doc/database.md index 4259749d2a..ec8d16b2cd 100644 --- a/doc/database.md +++ b/doc/database.md @@ -8,6 +8,7 @@ Database Tables | [2fa_app_specific_password](help/database/db_2fa_app_specific_password) | Two-factor app-specific _password | | [2fa_recovery_codes](help/database/db_2fa_recovery_codes) | Two-factor authentication recovery codes | | [2fa_trusted_browser](help/database/db_2fa_trusted_browser) | Two-factor authentication trusted browsers | +| [account-suggestion](help/database/db_account-suggestion) | Account suggestion | | [account-user](help/database/db_account-user) | Remote and local accounts | | [addon](help/database/db_addon) | registered addons | | [apcontact](help/database/db_apcontact) | ActivityPub compatible contacts - used in the ActivityPub implementation | diff --git a/doc/database/db_account-suggestion.md b/doc/database/db_account-suggestion.md new file mode 100644 index 0000000000..c86ae2f218 --- /dev/null +++ b/doc/database/db_account-suggestion.md @@ -0,0 +1,32 @@ +Table account-suggestion +=========== + +Account suggestion + +Fields +------ + +| Field | Description | Type | Null | Key | Default | Extra | +| ------ | ------------------------------------------------------------ | ------------------ | ---- | --- | ------- | ----- | +| uri-id | Id of the item-uri table entry that contains the account url | int unsigned | NO | PRI | NULL | | +| uid | User ID | mediumint unsigned | NO | PRI | NULL | | +| level | level of closeness | smallint unsigned | YES | | NULL | | +| ignore | If set, this account will not be suggested again | boolean | NO | | 0 | | + +Indexes +------------ + +| Name | Fields | +| ---------- | ----------- | +| PRIMARY | uid, uri-id | +| uri-id_uid | uri-id, uid | + +Foreign Keys +------------ + +| Field | Target Table | Target Field | +|-------|--------------|--------------| +| uri-id | [item-uri](help/database/db_item-uri) | id | +| uid | [user](help/database/db_user) | uid | + +Return to [database documentation](help/database) diff --git a/src/Factory/Api/Mastodon/Status.php b/src/Factory/Api/Mastodon/Status.php index 9d78e5892a..44f3fa02fa 100644 --- a/src/Factory/Api/Mastodon/Status.php +++ b/src/Factory/Api/Mastodon/Status.php @@ -167,9 +167,23 @@ class Status extends BaseFactory if (!empty($shared)) { $shared_uri_id = $shared['post']['uri-id']; - $mentions = array_merge($mentions, $this->mstdnMentionFactory->createFromUriId($shared_uri_id)->getArrayCopy()); - $tags = array_merge($tags, $this->mstdnTagFactory->createFromUriId($shared_uri_id)); - $attachments = array_merge($attachments, $this->mstdnAttachementFactory->createFromUriId($shared_uri_id)); + foreach ($this->mstdnMentionFactory->createFromUriId($shared_uri_id)->getArrayCopy() as $mention) { + if (!in_array($mention, $mentions)) { + $mentions[] = $mention; + } + } + + foreach ($this->mstdnTagFactory->createFromUriId($shared_uri_id) as $tag) { + if (!in_array($tag, $tags)) { + $tags[] = $tag; + } + } + + foreach ($this->mstdnAttachementFactory->createFromUriId($shared_uri_id) as $attachment) { + if (!in_array($attachment, $attachments)) { + $attachments[] = $attachment; + } + } if (empty($card->toArray())) { $card = $this->mstdnCardFactory->createFromUriId($shared_uri_id); @@ -190,7 +204,7 @@ class Status extends BaseFactory if ($is_reshare) { $reshare = $this->createFromUriId($uriId, $uid, false)->toArray(); } - +// $mentions = array_unique($mentions); return new \Friendica\Object\Api\Mastodon\Status($item, $account, $counts, $userAttributes, $sensitive, $application, $mentions, $tags, $card, $attachments, $reshare, $poll); } diff --git a/src/Model/Contact/Relation.php b/src/Model/Contact/Relation.php index ae6859cdd9..5f2f07d397 100644 --- a/src/Model/Contact/Relation.php +++ b/src/Model/Contact/Relation.php @@ -260,6 +260,48 @@ class Relation return true; } + /** + * Update contact suggestions for a given user + * + * @param integer $uid + * @return void + */ + static public function updateSuggestions(int $uid) + { + if (DI::pConfig()->get($uid, 'suggestion', 'last_update') + 3600 > time()) { + return; + } + + DBA::delete('account-suggestion', ['uid' => $uid, 'ignore' => false]); + + foreach (self::getSuggestions($uid) as $contact) { + DBA::insert('account-suggestion', ['uri-id' => $contact['uri-id'], 'uid' => $uid, 'level' => 1], Database::INSERT_IGNORE); + } + + DI::pConfig()->set($uid, 'suggestion', 'last_update', time()); + } + + /** + * Returns a cached array of suggested contacts for given user id + * + * @param int $uid User id + * @param int $start optional, default 0 + * @param int $limit optional, default 80 + * @return array + */ + static public function getCachedSuggestions(int $uid, int $start = 0, int $limit = 80): array + { + $condition = ["`uid` = ? AND `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE NOT `ignore` AND `uid` = ?)", 0, $uid]; + $params = ['limit' => [$start, $limit]]; + $cached = DBA::selectToArray('contact', [], $condition, $params); + + if (!empty($cached)) { + return $cached; + } else { + return self::getSuggestions($uid, $start, $limit); + } + } + /** * Returns an array of suggested contacts for given user id * @@ -285,11 +327,12 @@ class Relation (SELECT `cid` FROM `contact-relation` WHERE `relation-cid` = ?) AND NOT `cid` IN (SELECT `id` FROM `contact` WHERE `uid` = ? AND `nurl` IN (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?))) AND `id` = `cid`) - AND NOT `hidden` AND `network` IN (?, ?, ?, ?)", + AND NOT `hidden` AND `network` IN (?, ?, ?, ?) + AND NOT `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE `uri-id` = `contact`.`uri-id` AND `uid` = ?)", $cid, 0, $uid, Contact::FRIEND, Contact::SHARING, - Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, + Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, $uid ], [ 'order' => ['last-item' => true], 'limit' => $totallimit, @@ -315,9 +358,10 @@ class Relation (SELECT `relation-cid` FROM `contact-relation` WHERE `cid` = ?) AND NOT `cid` IN (SELECT `id` FROM `contact` WHERE `uid` = ? AND `nurl` IN (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?))) AND `id` = `cid`) - AND NOT `hidden` AND `network` IN (?, ?, ?, ?)", + AND NOT `hidden` AND `network` IN (?, ?, ?, ?) + AND NOT `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE `uri-id` = `contact`.`uri-id` AND `uid` = ?)", $cid, 0, $uid, Contact::FRIEND, Contact::SHARING, - Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus], + Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, $uid], ['order' => ['last-item' => true], 'limit' => $totallimit] ); @@ -335,9 +379,10 @@ class Relation // The query returns contacts that follow the given user but aren't followed by that user. $results = DBA::select('contact', [], ["`nurl` IN (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` = ?) - AND NOT `hidden` AND `uid` = ? AND `network` IN (?, ?, ?, ?)", + AND NOT `hidden` AND `uid` = ? AND `network` IN (?, ?, ?, ?) + AND NOT `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE `uri-id` = `contact`.`uri-id` AND `uid` = ?)", $uid, Contact::FOLLOWER, 0, - Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus], + Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, $uid], ['order' => ['last-item' => true], 'limit' => $totallimit] ); @@ -355,9 +400,10 @@ class Relation // The query returns any contact that isn't followed by that user. $results = DBA::select('contact', [], ["NOT `nurl` IN (SELECT `nurl` FROM `contact` WHERE `uid` = ? AND `rel` IN (?, ?) AND `nurl` = `nurl`) - AND NOT `hidden` AND `uid` = ? AND `network` IN (?, ?, ?, ?)", + AND NOT `hidden` AND `uid` = ? AND `network` IN (?, ?, ?, ?) + AND NOT `uri-id` IN (SELECT `uri-id` FROM `account-suggestion` WHERE `uri-id` = `contact`.`uri-id` AND `uid` = ?)", $uid, Contact::FRIEND, Contact::SHARING, 0, - Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus], + Protocol::ACTIVITYPUB, Protocol::DFRN, $diaspora, $ostatus, $uid], ['order' => ['last-item' => true], 'limit' => $totallimit] ); diff --git a/src/Module/Api/Mastodon/Suggestions.php b/src/Module/Api/Mastodon/Suggestions.php index 6eb2509269..f25b4cacfd 100644 --- a/src/Module/Api/Mastodon/Suggestions.php +++ b/src/Module/Api/Mastodon/Suggestions.php @@ -43,7 +43,7 @@ class Suggestions extends BaseApi 'limit' => 40, // Maximum number of results to return. Defaults to 40. ], $request); - $suggestions = Contact\Relation::getSuggestions($uid, 0, $request['limit']); + $suggestions = Contact\Relation::getCachedSuggestions($uid, 0, $request['limit']); $accounts = []; diff --git a/src/Module/Contact/Suggestions.php b/src/Module/Contact/Suggestions.php index 86e3f6cc8a..7a4816f498 100644 --- a/src/Module/Contact/Suggestions.php +++ b/src/Module/Contact/Suggestions.php @@ -57,7 +57,7 @@ class Suggestions extends \Friendica\BaseModule $this->page['aside'] .= Widget::findPeople(); $this->page['aside'] .= Widget::follow(); - $contacts = Contact\Relation::getSuggestions($this->session->getLocalUserId()); + $contacts = Contact\Relation::getCachedSuggestions($this->session->getLocalUserId()); if (!$contacts) { return $this->t('No suggestions available. If this is a new site, please try again in 24 hours.'); } diff --git a/src/Security/Authentication.php b/src/Security/Authentication.php index c38a2eb6dd..61ac4fae00 100644 --- a/src/Security/Authentication.php +++ b/src/Security/Authentication.php @@ -38,6 +38,7 @@ use Friendica\Util\DateTimeFormat; use Friendica\Util\Network; use LightOpenID; use Friendica\Core\L10n; +use Friendica\Core\Worker; use Psr\Log\LoggerInterface; /** @@ -356,6 +357,9 @@ class Authentication // Set the login date for all identities of the user $this->dba->update('user', ['login_date' => DateTimeFormat::utcNow()], ['parent-uid' => $user_record['uid'], 'account_removed' => false]); + + // Update suggestions upon login + Worker::add(Worker::PRIORITY_MEDIUM, 'UpdateSuggestions', $user_record['uid']); } if ($login_initial) { diff --git a/src/Worker/Cron.php b/src/Worker/Cron.php index e0ef134a23..d450f4cd5b 100644 --- a/src/Worker/Cron.php +++ b/src/Worker/Cron.php @@ -130,6 +130,8 @@ class Cron Worker::add(Worker::PRIORITY_LOW, 'CheckDeletedContacts'); + Worker::add(Worker::PRIORITY_LOW, 'UpdateAllSuggestions'); + if (DI::config()->get('system', 'optimize_tables')) { Worker::add(Worker::PRIORITY_LOW, 'OptimizeTables'); } diff --git a/src/Worker/UpdateAllSuggestions.php b/src/Worker/UpdateAllSuggestions.php new file mode 100644 index 0000000000..b1c84b639e --- /dev/null +++ b/src/Worker/UpdateAllSuggestions.php @@ -0,0 +1,41 @@ +. + * + */ + +namespace Friendica\Worker; + +use Friendica\Database\DBA; +use Friendica\Model\Contact; +use Friendica\Util\DateTimeFormat; + +/** + * Update contact suggestions for all aktive users + */ +class UpdateAllSuggestions +{ + public static function execute() + { + $users = DBA::select('user', ['uid'], ["`login_date` > ?", DateTimeFormat::utc('now - 7 days')]); + while ($user = DBA::fetch($users)) { + Contact\Relation::updateSuggestions($user['uid']); + } + DBA::close($users); + } +} diff --git a/src/Worker/UpdateSuggestions.php b/src/Worker/UpdateSuggestions.php new file mode 100644 index 0000000000..c756ed10f5 --- /dev/null +++ b/src/Worker/UpdateSuggestions.php @@ -0,0 +1,35 @@ +. + * + */ + +namespace Friendica\Worker; + +use Friendica\Model\Contact; + +/** + * Update contact suggestions + */ +class UpdateSuggestions +{ + public static function execute(int $uid) + { + Contact\Relation::updateSuggestions($uid); + } +} diff --git a/static/dbstructure.config.php b/static/dbstructure.config.php index 9ec2ae52ed..55c900916b 100644 --- a/static/dbstructure.config.php +++ b/static/dbstructure.config.php @@ -55,7 +55,7 @@ use Friendica\Database\DBA; if (!defined('DB_UPDATE_VERSION')) { - define('DB_UPDATE_VERSION', 1495); + define('DB_UPDATE_VERSION', 1496); } return [ @@ -371,6 +371,18 @@ return [ "uid" => ["uid"], ] ], + "account-suggestion" => [ + "comment" => "Account suggestion", + "fields" => [ + "uri-id" => ["type" => "int unsigned", "not null" => "1", "primary" => "1", "foreign" => ["item-uri" => "id"], "comment" => "Id of the item-uri table entry that contains the account url"], + "uid" => ["type" => "mediumint unsigned", "not null" => "1", "primary" => "1", "foreign" => ["user" => "uid"], "comment" => "User ID"], + "level" => ["type" => "smallint unsigned", "comment" => "level of closeness"], + "ignore" => ["type" => "boolean", "not null" => "1", "default" => "0", "comment" => "If set, this account will not be suggested again"], ], + "indexes" => [ + "PRIMARY" => ["uid", "uri-id"], + "uri-id_uid" => ["uri-id", "uid"], + ] + ], "account-user" => [ "comment" => "Remote and local accounts", "fields" => [ diff --git a/view/theme/vier/theme.php b/view/theme/vier/theme.php index 006450edfc..062e4a105c 100644 --- a/view/theme/vier/theme.php +++ b/view/theme/vier/theme.php @@ -144,7 +144,7 @@ function vier_community_info() // comunity_profiles if ($show_profiles) { - $contacts = Contact\Relation::getSuggestions(DI::userSession()->getLocalUserId(), 0, 9); + $contacts = Contact\Relation::getCachedSuggestions(DI::userSession()->getLocalUserId(), 0, 9); $tpl = Renderer::getMarkupTemplate('ch_directory_item.tpl'); if (DBA::isResult($contacts)) { -- 2.39.5