src/Database/PostUpdate.php

   1 <?php
   2 /**
   3  * @copyright Copyright (C) 2010-2023, the Friendica project
   4  *
   5  * @license GNU AGPL version 3 or any later version
   6  *
   7  * This program is free software: you can redistribute it and/or modify
   8  * it under the terms of the GNU Affero General Public License as
   9  * published by the Free Software Foundation, either version 3 of the
  10  * License, or (at your option) any later version.
  11  *
  12  * This program is distributed in the hope that it will be useful,
  13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15  * GNU Affero General Public License for more details.
  16  *
  17  * You should have received a copy of the GNU Affero General Public License
  18  * along with this program.  If not, see <https://www.gnu.org/licenses/>.
  19  *
  20  */
  21
  22 namespace Friendica\Database;
  23
  24 use Friendica\Core\Logger;
  25 use Friendica\Core\Protocol;
  26 use Friendica\DI;
  27 use Friendica\Model\Contact;
  28 use Friendica\Model\Conversation;
  29 use Friendica\Model\GServer;
  30 use Friendica\Model\Item;
  31 use Friendica\Model\ItemURI;
  32 use Friendica\Model\Photo;
  33 use Friendica\Model\Post;
  34 use Friendica\Model\Post\Category;
  35 use Friendica\Model\Tag;
  36 use Friendica\Model\Verb;
  37 use Friendica\Protocol\ActivityPub\Processor;
  38 use Friendica\Protocol\ActivityPub\Receiver;
  39 use Friendica\Util\JsonLD;
  40 use Friendica\Util\Strings;
  41 use GuzzleHttp\Psr7\Uri;
  42
  43 /**
  44  * These database-intensive post update routines are meant to be executed in the background by the cronjob.
  45  *
  46  * If there is a need for a intensive migration after a database structure change, update this file
  47  * by adding a new method at the end with the number of the new DB_UPDATE_VERSION.
  48  */
  49 class PostUpdate
  50 {
  51         // Needed for the helper function to read from the legacy term table
  52         const OBJECT_TYPE_POST  = 1;
  53
  54         const VERSION = 1507;
  55
  56         /**
  57          * Calls the post update functions
  58          */
  59         public static function update()
  60         {
  61                 if (!self::update1297()) {
  62                         return false;
  63                 }
  64                 if (!self::update1322()) {
  65                         return false;
  66                 }
  67                 if (!self::update1329()) {
  68                         return false;
  69                 }
  70                 if (!self::update1341()) {
  71                         return false;
  72                 }
  73                 if (!self::update1342()) {
  74                         return false;
  75                 }
  76                 if (!self::update1345()) {
  77                         return false;
  78                 }
  79                 if (!self::update1346()) {
  80                         return false;
  81                 }
  82                 if (!self::update1347()) {
  83                         return false;
  84                 }
  85                 if (!self::update1348()) {
  86                         return false;
  87                 }
  88                 if (!self::update1349()) {
  89                         return false;
  90                 }
  91                 if (!self::update1383()) {
  92                         return false;
  93                 }
  94                 if (!self::update1384()) {
  95                         return false;
  96                 }
  97                 if (!self::update1400()) {
  98                         return false;
  99                 }
 100                 if (!self::update1424()) {
 101                         return false;
 102                 }
 103                 if (!self::update1425()) {
 104                         return false;
 105                 }
 106                 if (!self::update1426()) {
 107                         return false;
 108                 }
 109                 if (!self::update1427()) {
 110                         return false;
 111                 }
 112                 if (!self::update1452()) {
 113                         return false;
 114                 }
 115                 if (!self::update1483()) {
 116                         return false;
 117                 }
 118                 if (!self::update1484()) {
 119                         return false;
 120                 }
 121                 if (!self::update1506()) {
 122                         return false;
 123                 }
 124                 if (!self::update1507()) {
 125                         return false;
 126                 }
 127                 return true;
 128         }
 129
 130         /**
 131          * Set the delivery queue count to a negative value for all items preceding the feature.
 132          *
 133          * @return bool "true" when the job is done
 134          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 135          */
 136         private static function update1297()
 137         {
 138                 // Was the script completed?
 139                 if (DI::keyValue()->get('post_update_version') >= 1297) {
 140                         return true;
 141                 }
 142
 143                 if (!DBStructure::existsTable('item-delivery-data')) {
 144                         DI::keyValue()->set('post_update_version', 1297);
 145                         return true;
 146                 }
 147
 148                 $max_item_delivery_data = DBA::selectFirst('item-delivery-data', ['iid'], ['queue_count > 0 OR queue_done > 0'], ['order' => ['iid']]);
 149                 $max_iid = $max_item_delivery_data['iid'] ?? 0;
 150
 151                 Logger::info('Start update1297 with max iid: ' . $max_iid);
 152
 153                 $condition = ['`queue_count` = 0 AND `iid` < ?', $max_iid];
 154
 155                 DBA::update('item-delivery-data', ['queue_count' => -1], $condition);
 156
 157                 if (DBA::errorNo() != 0) {
 158                         Logger::error('Database error ' . DBA::errorNo() . ':' . DBA::errorMessage());
 159                         return false;
 160                 }
 161
 162                 Logger::info('Processed rows: ' . DBA::affectedRows());
 163
 164                 DI::keyValue()->set('post_update_version', 1297);
 165
 166                 Logger::info('Done');
 167
 168                 return true;
 169         }
 170         /**
 171          * Remove contact duplicates
 172          *
 173          * @return bool "true" when the job is done
 174          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 175          */
 176         private static function update1322()
 177         {
 178                 // Was the script completed?
 179                 if (DI::keyValue()->get('post_update_version') >= 1322) {
 180                         return true;
 181                 }
 182
 183                 Logger::info('Start');
 184
 185                 $contacts = DBA::p("SELECT `nurl`, `uid` FROM `contact`
 186                         WHERE EXISTS (SELECT `nurl` FROM `contact` AS `c2`
 187                                 WHERE `c2`.`nurl` = `contact`.`nurl` AND `c2`.`id` != `contact`.`id` AND `c2`.`uid` = `contact`.`uid` AND `c2`.`network` IN (?, ?, ?) AND NOT `deleted`)
 188                         AND (`network` IN (?, ?, ?) OR (`uid` = ?)) AND NOT `deleted` GROUP BY `nurl`, `uid`",
 189                         Protocol::DIASPORA, Protocol::OSTATUS, Protocol::ACTIVITYPUB,
 190                         Protocol::DIASPORA, Protocol::OSTATUS, Protocol::ACTIVITYPUB, 0);
 191
 192                 while ($contact = DBA::fetch($contacts)) {
 193                         Logger::info('Remove duplicates', ['nurl' => $contact['nurl'], 'uid' => $contact['uid']]);
 194                         Contact::removeDuplicates($contact['nurl'], $contact['uid']);
 195                 }
 196
 197                 DBA::close($contact);
 198                 DI::keyValue()->set('post_update_version', 1322);
 199
 200                 Logger::info('Done');
 201
 202                 return true;
 203         }
 204
 205         /**
 206          * update user notification data
 207          *
 208          * @return bool "true" when the job is done
 209          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 210          */
 211         private static function update1329()
 212         {
 213                 // Was the script completed?
 214                 if (DI::keyValue()->get('post_update_version') >= 1329) {
 215                         return true;
 216                 }
 217
 218                 if (!DBStructure::existsTable('item')) {
 219                         DI::keyValue()->set('post_update_version', 1329);
 220                         return true;
 221                 }
 222
 223                 $id = DI::keyValue()->get('post_update_version_1329_id') ?? 0;
 224
 225                 Logger::info('Start', ['item' => $id]);
 226
 227                 $start_id = $id;
 228                 $rows = 0;
 229                 $condition = ["`id` > ?", $id];
 230                 $params = ['order' => ['id'], 'limit' => 10000];
 231                 $items = DBA::select('item', ['id', 'uri-id', 'uid'], $condition, $params);
 232
 233                 if (DBA::errorNo() != 0) {
 234                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
 235                         return false;
 236                 }
 237
 238                 while ($item = DBA::fetch($items)) {
 239                         $id = $item['id'];
 240
 241                         Post\UserNotification::setNotification($item['uri-id'], $item['uid']);
 242
 243                         ++$rows;
 244                 }
 245                 DBA::close($items);
 246
 247                 DI::keyValue()->set('post_update_version_1329_id', $id);
 248
 249                 Logger::info('Processed', ['rows' => $rows, 'last' => $id]);
 250
 251                 if ($start_id == $id) {
 252                         DI::keyValue()->set('post_update_version', 1329);
 253                         Logger::info('Done');
 254                         return true;
 255                 }
 256
 257                 return false;
 258         }
 259
 260         /**
 261          * Fill the "tag" table with tags and mentions from the body
 262          *
 263          * @return bool "true" when the job is done
 264          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 265          */
 266         private static function update1341()
 267         {
 268                 // Was the script completed?
 269                 if (DI::keyValue()->get('post_update_version') >= 1341) {
 270                         return true;
 271                 }
 272
 273                 if (!DBStructure::existsTable('item-content')) {
 274                         DI::keyValue()->set('post_update_version', 1342);
 275                         return true;
 276                 }
 277
 278                 $id = DI::keyValue()->get('post_update_version_1341_id') ?? 0;
 279
 280                 Logger::info('Start', ['item' => $id]);
 281
 282                 $rows = 0;
 283
 284                 $items = DBA::p("SELECT `uri-id`,`body` FROM `item-content` WHERE
 285                         (`body` LIKE ? OR `body` LIKE ? OR `body` LIKE ?) AND `uri-id` >= ?
 286                         ORDER BY `uri-id` LIMIT 100000", '%#%', '%@%', '%!%', $id);
 287
 288                 if (DBA::errorNo() != 0) {
 289                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
 290                         return false;
 291                 }
 292
 293                 while ($item = DBA::fetch($items)) {
 294                         Tag::storeFromBody($item['uri-id'], $item['body'], '#!@', false);
 295                         $id = $item['uri-id'];
 296                         ++$rows;
 297                         if ($rows % 1000 == 0) {
 298                                 DI::keyValue()->set('post_update_version_1341_id', $id);
 299                         }
 300                 }
 301                 DBA::close($items);
 302
 303                 DI::keyValue()->set('post_update_version_1341_id', $id);
 304
 305                 Logger::info('Processed', ['rows' => $rows, 'last' => $id]);
 306
 307                 // When there are less than 1,000 items processed this means that we reached the end
 308                 // The other entries will then be processed with the regular functionality
 309                 if ($rows < 1000) {
 310                         DI::keyValue()->set('post_update_version', 1341);
 311                         Logger::info('Done');
 312                         return true;
 313                 }
 314
 315                 return false;
 316         }
 317
 318         /**
 319          * Fill the "tag" table with tags and mentions from the "term" table
 320          *
 321          * @return bool "true" when the job is done
 322          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 323          */
 324         private static function update1342()
 325         {
 326                 // Was the script completed?
 327                 if (DI::keyValue()->get('post_update_version') >= 1342) {
 328                         return true;
 329                 }
 330
 331                 if (!DBStructure::existsTable('term') || !DBStructure::existsTable('item-content')) {
 332                         DI::keyValue()->set('post_update_version', 1342);
 333                         return true;
 334                 }
 335
 336                 $id = DI::keyValue()->get('post_update_version_1342_id') ?? 0;
 337
 338                 Logger::info('Start', ['item' => $id]);
 339
 340                 $rows = 0;
 341
 342                 $terms = DBA::p("SELECT `term`.`tid`, `item`.`uri-id`, `term`.`type`, `term`.`term`, `term`.`url`, `item-content`.`body`
 343                         FROM `term`
 344                         INNER JOIN `item` ON `item`.`id` = `term`.`oid`
 345                         INNER JOIN `item-content` ON `item-content`.`uri-id` = `item`.`uri-id`
 346                         WHERE term.type IN (?, ?, ?, ?) AND `tid` >= ? ORDER BY `tid` LIMIT 100000",
 347                         Tag::HASHTAG, Tag::MENTION, Tag::EXCLUSIVE_MENTION, Tag::IMPLICIT_MENTION, $id);
 348
 349                 if (DBA::errorNo() != 0) {
 350                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
 351                         return false;
 352                 }
 353
 354                 while ($term = DBA::fetch($terms)) {
 355                         if (($term['type'] == Tag::MENTION) && !empty($term['url']) && !strstr($term['body'], $term['url'])) {
 356                 $condition = ['nurl' => Strings::normaliseLink($term['url']), 'uid' => 0, 'deleted' => false];
 357                 $contact = DBA::selectFirst('contact', ['url', 'alias'], $condition, ['order' => ['id']]);
 358                 if (!DBA::isResult($contact)) {
 359                         $ssl_url = str_replace('http://', 'https://', $term['url']);
 360                         $condition = ['`alias` IN (?, ?, ?) AND `uid` = ? AND NOT `deleted`', $term['url'], Strings::normaliseLink($term['url']), $ssl_url, 0];
 361                         $contact = DBA::selectFirst('contact', ['url', 'alias'], $condition, ['order' => ['id']]);
 362                 }
 363
 364                 if (DBA::isResult($contact) && (!strstr($term['body'], $contact['url']) && (empty($contact['alias']) || !strstr($term['body'], $contact['alias'])))) {
 365                         $term['type'] = Tag::IMPLICIT_MENTION;
 366                 }
 367                         }
 368
 369                         Tag::store($term['uri-id'], $term['type'], $term['term'], $term['url']);
 370
 371                         $id = $term['tid'];
 372                         ++$rows;
 373                         if ($rows % 1000 == 0) {
 374                                 DI::keyValue()->set('post_update_version_1342_id', $id);
 375                         }
 376                 }
 377                 DBA::close($terms);
 378
 379                 DI::keyValue()->set('post_update_version_1342_id', $id);
 380
 381                 Logger::info('Processed', ['rows' => $rows, 'last' => $id]);
 382
 383                 // When there are less than 1,000 items processed this means that we reached the end
 384                 // The other entries will then be processed with the regular functionality
 385                 if ($rows < 1000) {
 386                         DI::keyValue()->set('post_update_version', 1342);
 387                         Logger::info('Done');
 388                         return true;
 389                 }
 390
 391                 return false;
 392         }
 393
 394         /**
 395          * Fill the "post-delivery-data" table with data from the "item-delivery-data" table
 396          *
 397          * @return bool "true" when the job is done
 398          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 399          */
 400         private static function update1345()
 401         {
 402                 // Was the script completed?
 403                 if (DI::keyValue()->get('post_update_version') >= 1345) {
 404                         return true;
 405                 }
 406
 407                 if (!DBStructure::existsTable('item-delivery-data')) {
 408                         DI::keyValue()->set('post_update_version', 1345);
 409                         return true;
 410                 }
 411
 412                 $id = DI::keyValue()->get('post_update_version_1345_id') ?? 0;
 413
 414                 Logger::info('Start', ['item' => $id]);
 415
 416                 $rows = 0;
 417
 418                 $deliveries = DBA::p("SELECT `uri-id`, `iid`, `item-delivery-data`.`postopts`, `item-delivery-data`.`inform`,
 419                         `queue_count`, `queue_done`, `activitypub`, `dfrn`, `diaspora`, `ostatus`, `legacy_dfrn`, `queue_failed`
 420                         FROM `item-delivery-data`
 421                         INNER JOIN `item` ON `item`.`id` = `item-delivery-data`.`iid`
 422                         WHERE `iid` >= ? ORDER BY `iid` LIMIT 10000", $id);
 423
 424                 if (DBA::errorNo() != 0) {
 425                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
 426                         return false;
 427                 }
 428
 429                 while ($delivery = DBA::fetch($deliveries)) {
 430                         $id = $delivery['iid'];
 431                         unset($delivery['iid']);
 432                         DBA::insert('post-delivery-data', $delivery, Database::INSERT_UPDATE);
 433                         ++$rows;
 434                 }
 435                 DBA::close($deliveries);
 436
 437                 DI::keyValue()->set('post_update_version_1345_id', $id);
 438
 439                 Logger::info('Processed', ['rows' => $rows, 'last' => $id]);
 440
 441                 // When there are less than 100 items processed this means that we reached the end
 442                 // The other entries will then be processed with the regular functionality
 443                 if ($rows < 100) {
 444                         DI::keyValue()->set('post_update_version', 1345);
 445                         Logger::info('Done');
 446                         return true;
 447                 }
 448
 449                 return false;
 450         }
 451
 452         /**
 453          * Generates the legacy item.file field string from an item ID.
 454          * Includes only file and category terms.
 455          *
 456          * @param int $item_id
 457          * @return string
 458          * @throws \Exception
 459          */
 460         private static function fileTextFromItemId($item_id)
 461         {
 462                 $file_text = '';
 463
 464                 $condition = ['otype' => self::OBJECT_TYPE_POST, 'oid' => $item_id, 'type' => [Category::FILE, Category::CATEGORY]];
 465                 $tags = DBA::selectToArray('term', ['type', 'term', 'url'], $condition);
 466                 foreach ($tags as $tag) {
 467                         if ($tag['type'] == Category::CATEGORY) {
 468                                 $file_text .= '<' . $tag['term'] . '>';
 469                         } else {
 470                                 $file_text .= '[' . $tag['term'] . ']';
 471                         }
 472                 }
 473
 474                 return $file_text;
 475         }
 476
 477         /**
 478          * Fill the "tag" table with tags and mentions from the "term" table
 479          *
 480          * @return bool "true" when the job is done
 481          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 482          */
 483         private static function update1346()
 484         {
 485                 // Was the script completed?
 486                 if (DI::keyValue()->get('post_update_version') >= 1346) {
 487                         return true;
 488                 }
 489
 490                 if (!DBStructure::existsTable('term')) {
 491                         DI::keyValue()->set('post_update_version', 1346);
 492                         return true;
 493                 }
 494
 495                 $id = DI::keyValue()->get('post_update_version_1346_id') ?? 0;
 496
 497                 Logger::info('Start', ['item' => $id]);
 498
 499                 $rows = 0;
 500
 501                 $terms = DBA::select('term', ['oid'],
 502                         ["`type` IN (?, ?) AND `oid` >= ?", Category::CATEGORY, Category::FILE, $id],
 503                         ['order' => ['oid'], 'limit' => 1000, 'group_by' => ['oid']]);
 504
 505                 if (DBA::errorNo() != 0) {
 506                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
 507                         return false;
 508                 }
 509
 510                 while ($term = DBA::fetch($terms)) {
 511                         $item = Post::selectFirst(['uri-id', 'uid'], ['id' => $term['oid']]);
 512                         if (!DBA::isResult($item)) {
 513                                 continue;
 514                         }
 515
 516                         $file = self::fileTextFromItemId($term['oid']);
 517                         if (!empty($file)) {
 518                                 Category::storeTextByURIId($item['uri-id'], $item['uid'], $file);
 519                         }
 520
 521                         $id = $term['oid'];
 522                         ++$rows;
 523                         if ($rows % 100 == 0) {
 524                                 DI::keyValue()->set('post_update_version_1346_id', $id);
 525                         }
 526                 }
 527                 DBA::close($terms);
 528
 529                 DI::keyValue()->set('post_update_version_1346_id', $id);
 530
 531                 Logger::info('Processed', ['rows' => $rows, 'last' => $id]);
 532
 533                 // When there are less than 10 items processed this means that we reached the end
 534                 // The other entries will then be processed with the regular functionality
 535                 if ($rows < 10) {
 536                         DI::keyValue()->set('post_update_version', 1346);
 537                         Logger::info('Done');
 538                         return true;
 539                 }
 540
 541                 return false;
 542         }
 543
 544         /**
 545          * update the "vid" (verb) field in the item table
 546          *
 547          * @return bool "true" when the job is done
 548          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 549          * @throws \ImagickException
 550          */
 551         private static function update1347()
 552         {
 553                 // Was the script completed?
 554                 if (DI::keyValue()->get('post_update_version') >= 1347) {
 555                         return true;
 556                 }
 557
 558                 if (!DBStructure::existsTable('item-activity') || !DBStructure::existsTable('item')) {
 559                         DI::keyValue()->set('post_update_version', 1347);
 560                         return true;
 561                 }
 562
 563                 $id = DI::keyValue()->get('post_update_version_1347_id') ?? 0;
 564
 565                 Logger::info('Start', ['item' => $id]);
 566
 567                 $start_id = $id;
 568                 $rows = 0;
 569
 570                 $items = DBA::p("SELECT `item`.`id`, `item`.`verb` AS `item-verb`, `item-content`.`verb`, `item-activity`.`activity`
 571                         FROM `item` LEFT JOIN `item-content` ON `item-content`.`uri-id` = `item`.`uri-id`
 572                         LEFT JOIN `item-activity` ON `item-activity`.`uri-id` = `item`.`uri-id` AND `item`.`gravity` = ?
 573                         WHERE `item`.`id` >= ? AND `item`.`vid` IS NULL ORDER BY `item`.`id` LIMIT 10000", Item::GRAVITY_ACTIVITY, $id);
 574
 575                 if (DBA::errorNo() != 0) {
 576                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
 577                         return false;
 578                 }
 579
 580                 while ($item = DBA::fetch($items)) {
 581                         $id = $item['id'];
 582                         $verb = $item['item-verb'];
 583                         if (empty($verb)) {
 584                                 $verb = $item['verb'];
 585                         }
 586                         if (empty($verb) && is_int($item['activity'])) {
 587                                 $verb = Item::ACTIVITIES[$item['activity']];
 588                         }
 589                         if (empty($verb)) {
 590                                 continue;
 591                         }
 592
 593                         DBA::update('item', ['vid' => Verb::getID($verb)], ['id' => $item['id']]);
 594                         ++$rows;
 595                 }
 596                 DBA::close($items);
 597
 598                 DI::keyValue()->set('post_update_version_1347_id', $id);
 599
 600                 Logger::info('Processed', ['rows' => $rows, 'last' => $id]);
 601
 602                 if ($start_id == $id) {
 603                         DI::keyValue()->set('post_update_version', 1347);
 604                         Logger::info('Done');
 605                         return true;
 606                 }
 607
 608                 return false;
 609         }
 610
 611         /**
 612          * update the "gsid" (global server id) field in the contact table
 613          *
 614          * @return bool "true" when the job is done
 615          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 616          * @throws \ImagickException
 617          */
 618         private static function update1348()
 619         {
 620                 // Was the script completed?
 621                 if (DI::keyValue()->get('post_update_version') >= 1348) {
 622                         return true;
 623                 }
 624
 625                 $id = DI::keyValue()->get('post_update_version_1348_id') ?? 0;
 626
 627                 Logger::info('Start', ['contact' => $id]);
 628
 629                 $start_id = $id;
 630                 $rows = 0;
 631                 $condition = ["`id` > ? AND `gsid` IS NULL AND `baseurl` != '' AND NOT `baseurl` IS NULL", $id];
 632                 $params = ['order' => ['id'], 'limit' => 10000];
 633                 $contacts = DBA::select('contact', ['id', 'baseurl'], $condition, $params);
 634
 635                 if (DBA::errorNo() != 0) {
 636                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
 637                         return false;
 638                 }
 639
 640                 while ($contact = DBA::fetch($contacts)) {
 641                         $id = $contact['id'];
 642
 643                         DBA::update('contact',
 644                                 ['gsid' => GServer::getID($contact['baseurl'], true), 'baseurl' => GServer::cleanURL($contact['baseurl'])],
 645                                 ['id' => $contact['id']]);
 646
 647                         ++$rows;
 648                 }
 649                 DBA::close($contacts);
 650
 651                 DI::keyValue()->set('post_update_version_1348_id', $id);
 652
 653                 Logger::info('Processed', ['rows' => $rows, 'last' => $id]);
 654
 655                 if ($start_id == $id) {
 656                         DI::keyValue()->set('post_update_version', 1348);
 657                         Logger::info('Done');
 658                         return true;
 659                 }
 660
 661                 return false;
 662         }
 663
 664         /**
 665          * update the "gsid" (global server id) field in the apcontact table
 666          *
 667          * @return bool "true" when the job is done
 668          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 669          * @throws \ImagickException
 670          */
 671         private static function update1349()
 672         {
 673                 // Was the script completed?
 674                 if (DI::keyValue()->get('post_update_version') >= 1349) {
 675                         return true;
 676                 }
 677
 678                 $id = DI::keyValue()->get('post_update_version_1349_id') ?? '';
 679
 680                 Logger::info('Start', ['apcontact' => $id]);
 681
 682                 $start_id = $id;
 683                 $rows = 0;
 684                 $condition = ["`url` > ? AND `gsid` IS NULL AND `baseurl` != '' AND NOT `baseurl` IS NULL", $id];
 685                 $params = ['order' => ['url'], 'limit' => 10000];
 686                 $apcontacts = DBA::select('apcontact', ['url', 'baseurl'], $condition, $params);
 687
 688                 if (DBA::errorNo() != 0) {
 689                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
 690                         return false;
 691                 }
 692
 693                 while ($apcontact = DBA::fetch($apcontacts)) {
 694                         $id = $apcontact['url'];
 695
 696                         DBA::update('apcontact',
 697                                 ['gsid' => GServer::getID($apcontact['baseurl'], true), 'baseurl' => GServer::cleanURL($apcontact['baseurl'])],
 698                                 ['url' => $apcontact['url']]);
 699
 700                         ++$rows;
 701                 }
 702                 DBA::close($apcontacts);
 703
 704                 DI::keyValue()->set('post_update_version_1349_id', $id);
 705
 706                 Logger::info('Processed', ['rows' => $rows, 'last' => $id]);
 707
 708                 if ($start_id == $id) {
 709                         DI::keyValue()->set('post_update_version', 1349);
 710                         Logger::info('Done');
 711                         return true;
 712                 }
 713
 714                 return false;
 715         }
 716
 717         /**
 718          * Remove orphaned photo entries
 719          *
 720          * @return bool "true" when the job is done
 721          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 722          * @throws \ImagickException
 723          */
 724         private static function update1383()
 725         {
 726                 // Was the script completed?
 727                 if (DI::keyValue()->get('post_update_version') >= 1383) {
 728                         return true;
 729                 }
 730
 731                 Logger::info('Start');
 732
 733                 $deleted = 0;
 734                 $avatar = [4 => 'photo', 5 => 'thumb', 6 => 'micro'];
 735
 736                 $photos = DBA::select('photo', ['id', 'contact-id', 'resource-id', 'scale'], ["`contact-id` != ? AND `album` = ?", 0, Photo::CONTACT_PHOTOS]);
 737                 while ($photo = DBA::fetch($photos)) {
 738                         $delete = !in_array($photo['scale'], [4, 5, 6]);
 739
 740                         if (!$delete) {
 741                                 // Check if there is a contact entry with that photo
 742                                 $delete = !DBA::exists('contact', ["`id` = ? AND `" . $avatar[$photo['scale']] . "` LIKE ?",
 743                                         $photo['contact-id'], '%' . $photo['resource-id'] . '%']);
 744                         }
 745
 746                         if ($delete) {
 747                                 Photo::delete(['id' => $photo['id']]);
 748                                 $deleted++;
 749                         }
 750                 }
 751                 DBA::close($photos);
 752
 753                 DI::keyValue()->set('post_update_version', 1383);
 754                 Logger::info('Done', ['deleted' => $deleted]);
 755                 return true;
 756         }
 757
 758         /**
 759          * update the "hash" field in the photo table
 760          *
 761          * @return bool "true" when the job is done
 762          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 763          * @throws \ImagickException
 764          */
 765         private static function update1384()
 766         {
 767                 // Was the script completed?
 768                 if (DI::keyValue()->get('post_update_version') >= 1384) {
 769                         return true;
 770                 }
 771
 772                 $condition = ["`hash` IS NULL"];
 773                 Logger::info('Start', ['rest' => DBA::count('photo', $condition)]);
 774
 775                 $rows = 0;
 776                 $photos = DBA::select('photo', [], $condition, ['limit' => 100]);
 777
 778                 if (DBA::errorNo() != 0) {
 779                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
 780                         return false;
 781                 }
 782
 783                 while ($photo = DBA::fetch($photos)) {
 784                         $img = Photo::getImageForPhoto($photo);
 785                         if (!empty($img)) {
 786                                 $md5 = md5($img->asString());
 787                         } else {
 788                                 $md5 = '';
 789                         }
 790                         DBA::update('photo', ['hash' => $md5], ['id' => $photo['id']]);
 791                         ++$rows;
 792                 }
 793                 DBA::close($photos);
 794
 795                 Logger::info('Processed', ['rows' => $rows]);
 796
 797                 if ($rows <= 100) {
 798                         DI::keyValue()->set('post_update_version', 1384);
 799                         Logger::info('Done');
 800                         return true;
 801                 }
 802
 803                 return false;
 804         }
 805
 806         /**
 807          * update the "external-id" field in the post table
 808          *
 809          * @return bool "true" when the job is done
 810          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 811          * @throws \ImagickException
 812          */
 813         private static function update1400()
 814         {
 815                 // Was the script completed?
 816                 if (DI::keyValue()->get('post_update_version') >= 1400) {
 817                         return true;
 818                 }
 819
 820                 if (!DBStructure::existsTable('item')) {
 821                         DI::keyValue()->set('post_update_version', 1400);
 822                         return true;
 823                 }
 824
 825                 $condition = ["`extid` != ? AND EXISTS(SELECT `id` FROM `post-user` WHERE `uri-id` = `item`.`uri-id` AND `uid` = `item`.`uid` AND `external-id` IS NULL)", ''];
 826                 Logger::info('Start', ['rest' => DBA::count('item', $condition)]);
 827
 828                 $rows = 0;
 829                 $items = DBA::select('item', ['uri-id', 'uid', 'extid'], $condition, ['order' => ['id'], 'limit' => 10000]);
 830
 831                 if (DBA::errorNo() != 0) {
 832                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
 833                         return false;
 834                 }
 835
 836                 while ($item = DBA::fetch($items)) {
 837                         Post::update(['external-id' => ItemURI::getIdByURI($item['extid'])], ['uri-id' => $item['uri-id'], 'uid' => $item['uid']]);
 838                         ++$rows;
 839                 }
 840                 DBA::close($items);
 841
 842                 Logger::info('Processed', ['rows' => $rows]);
 843
 844                 if ($rows <= 100) {
 845                         DI::keyValue()->set('post_update_version', 1400);
 846                         Logger::info('Done');
 847                         return true;
 848                 }
 849
 850                 return false;
 851         }
 852
 853         /**
 854          * update the "uri-id" field in the contact table
 855          *
 856          * @return bool "true" when the job is done
 857          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 858          * @throws \ImagickException
 859          */
 860         private static function update1424()
 861         {
 862                 // Was the script completed?
 863                 if (DI::keyValue()->get('post_update_version') >= 1424) {
 864                         return true;
 865                 }
 866
 867                 $condition = ["`uri-id` IS NULL"];
 868                 Logger::info('Start', ['rest' => DBA::count('contact', $condition)]);
 869
 870                 $rows = 0;
 871                 $contacts = DBA::select('contact', ['id', 'url'], $condition, ['limit' => 1000]);
 872
 873                 if (DBA::errorNo() != 0) {
 874                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
 875                         return false;
 876                 }
 877
 878                 while ($contact = DBA::fetch($contacts)) {
 879                         DBA::update('contact', ['uri-id' => ItemURI::getIdByURI($contact['url'])], ['id' => $contact['id']]);
 880                         ++$rows;
 881                 }
 882                 DBA::close($contacts);
 883
 884                 Logger::info('Processed', ['rows' => $rows]);
 885
 886                 if ($rows <= 100) {
 887                         DI::keyValue()->set('post_update_version', 1424);
 888                         Logger::info('Done');
 889                         return true;
 890                 }
 891
 892                 return false;
 893         }
 894
 895         /**
 896          * update the "uri-id" field in the fcontact table
 897          *
 898          * @return bool "true" when the job is done
 899          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 900          * @throws \ImagickException
 901          */
 902         private static function update1425()
 903         {
 904                 // Was the script completed?
 905                 if (DI::keyValue()->get('post_update_version') >= 1425) {
 906                         return true;
 907                 }
 908
 909                 if (!DBStructure::existsTable('fcontact')) {
 910                         DI::keyValue()->set('post_update_version', 1425);
 911                         return true;
 912                 }
 913
 914                 $condition = ["`uri-id` IS NULL"];
 915                 Logger::info('Start', ['rest' => DBA::count('fcontact', $condition)]);
 916
 917                 $rows = 0;
 918                 $fcontacts = DBA::select('fcontact', ['id', 'url', 'guid'], $condition, ['limit' => 1000]);
 919
 920                 if (DBA::errorNo() != 0) {
 921                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
 922                         return false;
 923                 }
 924
 925                 while ($fcontact = DBA::fetch($fcontacts)) {
 926                         if (!empty($fcontact['guid'])) {
 927                                 $uriid = ItemURI::insert(['uri' => $fcontact['url'], 'guid' => $fcontact['guid']]);
 928                         } else {
 929                                 $uriid = ItemURI::getIdByURI($fcontact['url']);
 930                         }
 931                         DBA::update('fcontact', ['uri-id' => $uriid], ['id' => $fcontact['id']]);
 932                         ++$rows;
 933                 }
 934                 DBA::close($fcontacts);
 935
 936                 Logger::info('Processed', ['rows' => $rows]);
 937
 938                 if ($rows <= 100) {
 939                         DI::keyValue()->set('post_update_version', 1425);
 940                         Logger::info('Done');
 941                         return true;
 942                 }
 943
 944                 return false;
 945         }
 946
 947         /**
 948          * update the "uri-id" field in the apcontact table
 949          *
 950          * @return bool "true" when the job is done
 951          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 952          * @throws \ImagickException
 953          */
 954         private static function update1426()
 955         {
 956                 // Was the script completed?
 957                 if (DI::keyValue()->get('post_update_version') >= 1426) {
 958                         return true;
 959                 }
 960
 961                 $condition = ["`uri-id` IS NULL"];
 962                 Logger::info('Start', ['rest' => DBA::count('apcontact', $condition)]);
 963
 964                 $rows = 0;
 965                 $apcontacts = DBA::select('apcontact', ['url', 'uuid'], $condition, ['limit' => 1000]);
 966
 967                 if (DBA::errorNo() != 0) {
 968                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
 969                         return false;
 970                 }
 971
 972                 while ($apcontact = DBA::fetch($apcontacts)) {
 973                         if (!empty($apcontact['uuid'])) {
 974                                 $uriid = ItemURI::insert(['uri' => $apcontact['url'], 'guid' => $apcontact['uuid']]);
 975                         } else {
 976                                 $uriid = ItemURI::getIdByURI($apcontact['url']);
 977                         }
 978                         DBA::update('apcontact', ['uri-id' => $uriid], ['url' => $apcontact['url']]);
 979                         ++$rows;
 980                 }
 981                 DBA::close($apcontacts);
 982
 983                 Logger::info('Processed', ['rows' => $rows]);
 984
 985                 if ($rows <= 100) {
 986                         DI::keyValue()->set('post_update_version', 1426);
 987                         Logger::info('Done');
 988                         return true;
 989                 }
 990
 991                 return false;
 992         }
 993
 994         /**
 995          * update the "uri-id" field in the event table
 996          *
 997          * @return bool "true" when the job is done
 998          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
 999          * @throws \ImagickException
1000          */
1001         private static function update1427()
1002         {
1003                 // Was the script completed?
1004                 if (DI::keyValue()->get('post_update_version') >= 1427) {
1005                         return true;
1006                 }
1007
1008                 $condition = ["`uri-id` IS NULL"];
1009                 Logger::info('Start', ['rest' => DBA::count('event', $condition)]);
1010
1011                 $rows = 0;
1012                 $events = DBA::select('event', ['id', 'uri', 'guid'], $condition, ['limit' => 1000]);
1013
1014                 if (DBA::errorNo() != 0) {
1015                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
1016                         return false;
1017                 }
1018
1019                 while ($event = DBA::fetch($events)) {
1020                         if (!empty($event['guid'])) {
1021                                 $uriid = ItemURI::insert(['uri' => $event['uri'], 'guid' => $event['guid']]);
1022                         } else {
1023                                 $uriid = ItemURI::getIdByURI($event['uri']);
1024                         }
1025                         DBA::update('event', ['uri-id' => $uriid], ['id' => $event['id']]);
1026                         ++$rows;
1027                 }
1028                 DBA::close($events);
1029
1030                 Logger::info('Processed', ['rows' => $rows]);
1031
1032                 if ($rows <= 100) {
1033                         DI::keyValue()->set('post_update_version', 1427);
1034                         Logger::info('Done');
1035                         return true;
1036                 }
1037
1038                 return false;
1039         }
1040
1041         /**
1042          * Fill the receivers of the post via the raw source
1043          *
1044          * @return bool "true" when the job is done
1045          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
1046          * @throws \ImagickException
1047          */
1048         private static function update1452()
1049         {
1050                 // Was the script completed?
1051                 if (DI::keyValue()->get('post_update_version') >= 1452) {
1052                         return true;
1053                 }
1054
1055                 if (!DBStructure::existsTable('conversation')) {
1056                         DI::keyValue()->set('post_update_version', 1452);
1057                         return true;
1058                 }
1059
1060                 $id = DI::keyValue()->get('post_update_version_1452_id') ?? 0;
1061
1062                 Logger::info('Start', ['uri-id' => $id]);
1063
1064                 $rows     = 0;
1065                 $received = '';
1066
1067                 $conversations = DBA::p("SELECT `post-view`.`uri-id`, `conversation`.`source`, `conversation`.`received` FROM `conversation`
1068                         INNER JOIN `post-view` ON `post-view`.`uri` = `conversation`.`item-uri`
1069                         WHERE NOT `source` IS NULL AND `conversation`.`protocol` = ? AND `uri-id` > ? LIMIT ?",
1070                         Conversation::PARCEL_ACTIVITYPUB, $id, 1000);
1071
1072                 if (DBA::errorNo() != 0) {
1073                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
1074                         return false;
1075                 }
1076
1077                 while ($conversation = DBA::fetch($conversations)) {
1078                         $id       = $conversation['uri-id'];
1079                         $received = $conversation['received'];
1080
1081                         $raw = json_decode($conversation['source'], true);
1082                         if (empty($raw)) {
1083                                 continue;
1084                         }
1085                         $activity = JsonLD::compact($raw);
1086
1087                         $urls = Receiver::getReceiverURL($activity);
1088                         Processor::storeReceivers($conversation['uri-id'], $urls);
1089
1090                         if (!empty($activity['as:object'])) {
1091                                 $urls = array_merge($urls, Receiver::getReceiverURL($activity['as:object']));
1092                                 Processor::storeReceivers($conversation['uri-id'], $urls);
1093                         }
1094                         ++$rows;
1095                 }
1096
1097                 DBA::close($conversations);
1098
1099                 DI::keyValue()->set('post_update_version_1452_id', $id);
1100
1101                 Logger::info('Processed', ['rows' => $rows, 'last' => $id, 'last-received' => $received]);
1102
1103                 if ($rows <= 100) {
1104                         DI::keyValue()->set('post_update_version', 1452);
1105                         Logger::info('Done');
1106                         return true;
1107                 }
1108
1109                 return false;
1110         }
1111
1112         /**
1113          * Correct the parent.
1114          * This fixes a bug that was introduced in the development of version 2022.09
1115          *
1116          * @return bool "true" when the job is done
1117          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
1118          * @throws \ImagickException
1119          */
1120         private static function update1483()
1121         {
1122                 // Was the script completed?
1123                 if (DI::keyValue()->get('post_update_version') >= 1483) {
1124                         return true;
1125                 }
1126
1127                 Logger::info('Start');
1128
1129                 $posts = DBA::select('post-view', ['uri-id'], ['conversation' => './']);
1130                 while ($post = DBA::fetch($posts)) {
1131                         $parent = Item::getParent($post['uri-id']);
1132                         if ($parent != 0) {
1133                                 DBA::update('post', ['parent-uri-id' => $parent], ['uri-id' => $post['uri-id']]);
1134                                 DBA::update('post-user', ['parent-uri-id' => $parent], ['uri-id' => $post['uri-id']]);
1135                         }
1136                 }
1137                 DBA::close($posts);
1138
1139                 DI::keyValue()->set('post_update_version', 1483);
1140                 Logger::info('Done');
1141                 return true;
1142         }
1143
1144         /**
1145          * Handle duplicate contact entries
1146          *
1147          * @return bool "true" when the job is done
1148          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
1149          * @throws \ImagickException
1150          */
1151         private static function update1484()
1152         {
1153                 // Was the script completed?
1154                 if (DI::keyValue()->get('post_update_version') >= 1484) {
1155                         return true;
1156                 }
1157
1158                 $id = DI::keyValue()->get('post_update_version_1484_id') ?? 0;
1159
1160                 Logger::info('Start', ['id' => $id]);
1161
1162                 $rows = 0;
1163
1164                 $contacts = DBA::select('contact', ['id', 'uid', 'uri-id', 'url'], ["`id` > ?", $id], ['order' => ['id'], 'limit' => 1000]);
1165
1166                 if (DBA::errorNo() != 0) {
1167                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
1168                         return false;
1169                 }
1170
1171                 while ($contact = DBA::fetch($contacts)) {
1172                         $id = $contact['id'];
1173                         if (is_null($contact['uri-id'])) {
1174                                 $contact['uri-id'] = ItemURI::getIdByURI($contact['url']);
1175                                 DBA::update('contact', ['uri-id' => $contact['uri-id']], ['id' => $contact['id']]);
1176                         }
1177                         Contact::setAccountUser($contact['id'], $contact['uid'], $contact['uri-id'], $contact['url']);
1178                         ++$rows;
1179                 }
1180                 DBA::close($contacts);
1181
1182                 DI::keyValue()->set('post_update_version_1484_id', $id);
1183
1184                 Logger::info('Processed', ['rows' => $rows, 'last' => $id]);
1185
1186                 if ($rows <= 100) {
1187                         DI::keyValue()->set('post_update_version', 1484);
1188                         Logger::info('Done');
1189                         return true;
1190                 }
1191
1192                 return false;
1193         }
1194
1195         /**
1196          * update the "gsid" (global server id) field in the contact table
1197          *
1198          * @return bool "true" when the job is done
1199          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
1200          * @throws \ImagickException
1201          */
1202         private static function update1506()
1203         {
1204                 // Was the script completed?
1205                 if (DI::keyValue()->get('post_update_version') >= 1506) {
1206                         return true;
1207                 }
1208
1209                 $id = DI::keyValue()->get('post_update_version_1506_id') ?? 0;
1210
1211                 Logger::info('Start', ['contact' => $id]);
1212
1213                 $start_id = $id;
1214                 $rows = 0;
1215                 $condition = ["`id` > ? AND `gsid` IS NULL AND `network` = ?", $id, Protocol::DIASPORA];
1216                 $params = ['order' => ['id'], 'limit' => 10000];
1217                 $contacts = DBA::select('contact', ['id', 'url'], $condition, $params);
1218
1219                 if (DBA::errorNo() != 0) {
1220                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
1221                         return false;
1222                 }
1223
1224                 while ($contact = DBA::fetch($contacts)) {
1225                         $id = $contact['id'];
1226
1227                         $parts = parse_url($contact['url']);
1228                         unset($parts['path']);
1229                         $server = (string)Uri::fromParts($parts);
1230
1231                         DBA::update('contact',
1232                                 ['gsid' => GServer::getID($server, true), 'baseurl' => GServer::cleanURL($server)],
1233                                 ['id' => $contact['id']]);
1234
1235                         ++$rows;
1236                 }
1237                 DBA::close($contacts);
1238
1239                 DI::keyValue()->set('post_update_version_1506_id', $id);
1240
1241                 Logger::info('Processed', ['rows' => $rows, 'last' => $id]);
1242
1243                 if ($start_id == $id) {
1244                         DI::keyValue()->set('post_update_version', 1506);
1245                         Logger::info('Done');
1246                         return true;
1247                 }
1248
1249                 return false;
1250         }
1251
1252         /**
1253          * update the "gsid" (global server id) field in the inbox-status table
1254          *
1255          * @return bool "true" when the job is done
1256          * @throws \Friendica\Network\HTTPException\InternalServerErrorException
1257          * @throws \ImagickException
1258          */
1259         private static function update1507()
1260         {
1261                 // Was the script completed?
1262                 if (DI::keyValue()->get('post_update_version') >= 1507) {
1263                         return true;
1264                 }
1265
1266                 $id = DI::keyValue()->get('post_update_version_1507_id') ?? '';
1267
1268                 Logger::info('Start', ['apcontact' => $id]);
1269
1270                 $start_id = $id;
1271                 $rows = 0;
1272                 $condition = ["`url` > ? AND NOT `gsid` IS NULL", $id];
1273                 $params = ['order' => ['url'], 'limit' => 10000];
1274                 $apcontacts = DBA::select('apcontact', ['url', 'gsid', 'sharedinbox', 'inbox'], $condition, $params);
1275
1276                 if (DBA::errorNo() != 0) {
1277                         Logger::error('Database error', ['no' => DBA::errorNo(), 'message' => DBA::errorMessage()]);
1278                         return false;
1279                 }
1280
1281                 while ($apcontact = DBA::fetch($apcontacts)) {
1282                         $id = $apcontact['url'];
1283
1284                         $inbox = [$apcontact['inbox']];
1285                         if (!empty($apcontact['sharedinbox'])) {
1286                                 $inbox[] = $apcontact['sharedinbox'];
1287                         }
1288                         $condition = DBA::mergeConditions(['url' => $inbox], ["`gsid` IS NULL"]);
1289                         DBA::update('inbox-status', ['gsid' => $apcontact['gsid'], 'archive' => GServer::isDefunctById($apcontact['gsid'])], $condition);
1290                         ++$rows;
1291                 }
1292                 DBA::close($apcontacts);
1293
1294                 DI::keyValue()->set('post_update_version_1507_id', $id);
1295
1296                 Logger::info('Processed', ['rows' => $rows, 'last' => $id]);
1297
1298                 if ($start_id == $id) {
1299                         DI::keyValue()->set('post_update_version', 1507);
1300                         Logger::info('Done');
1301                         return true;
1302                 }
1303
1304                 return false;
1305         }
1306 }