UpdatePidToSiteRoot.php

<?php

namespace SGalinski\SgMail\Updates;

/***************************************************************
 *  Copyright notice
 *
 *  (c) sgalinski Internet Services (https://www.sgalinski.de)
 *
 *  All rights reserved
 *
 *  This script is part of the TYPO3 project. The TYPO3 project is
 *  free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  The GNU General Public License can be found at
 *  http://www.gnu.org/copyleft/gpl.html.
 *
 *  This script is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  This copyright notice MUST APPEAR in all copies of the script!
 ***************************************************************/

use SGalinski\SgMail\Service\BackendService;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Install\Updates\DatabaseUpdatedPrerequisite;
use TYPO3\CMS\Install\Updates\UpgradeWizardInterface;

/**
 * Migrate template db entries to the correct root pages
 */
class UpdatePidToSiteRoot implements UpgradeWizardInterface {
	/**
	 * The wizard identifier
	 */
	const IDENTIFIER = 'tx_sgmail_update_pid_to_site_root';

	/**
	 * @var array
	 */
	protected $tables = [
		'tx_sgmail_domain_model_mail', 'tx_sgmail_domain_model_template'
	];

	/**
	 * check if site_root columns actually exist
	 *
	 * @param string $table
	 * @return bool
	 */
	private function siteRootColumnExists($table) {
		$connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($table);
		$result = $connection->getSchemaManager()->listTableColumns($table);
		return \array_key_exists('site_root_id', $result);
	}

	/**
	 * @return string
	 */
	public function getIdentifier(): string {
		return self::IDENTIFIER;
	}

	/**
	 * @return string
	 */
	public function getTitle(): string {
		return 'Find all templates & queue entries with site root and assign the correct site root id as pid. Also check if the pids are actually site roots and update them accordingly';
	}

	/**
	 * @return string
	 */
	public function getDescription(): string {
		return 'Move site root ids to pid & update pids to their correspondent site root ids';
	}

	/**
	 * @return bool
	 * @throws \Doctrine\DBAL\DBALException
	 */
	public function executeUpdate(): bool {
		foreach ($this->tables as $table) {
			if ($this->siteRootColumnExists($table)) {
				$connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($table);
				$sql = 'UPDATE ' . $table . ' SET pid = site_root_id';
				$connection->executeQuery($sql);
			} else {
				$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
				$queryBuilder->getRestrictions()->removeAll()->add(
					GeneralUtility::makeInstance(DeletedRestriction::class)
				);
				$result = $queryBuilder->select('pid')->from($table)->groupBy('pid')->execute();

				/** @var array $result */
				foreach ($result as $row) {
					$siteRootId = BackendService::getSiteRoot($row['pid']);
					if ($siteRootId === 0) {
						$siteRootId = 1;
					}
					$queryBuilder->update($table)->set('pid', $siteRootId)->where('pid = ' . $row['pid'])->execute();
				}
			}
		}

		return TRUE;
	}

	/**
	 * @return bool
	 */
	public function updateNecessary(): bool {
		// are there site root columns differing from pids?
		foreach ($this->tables as $table) {
			if (!$this->siteRootColumnExists($table)) {
				continue;
			}
			$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
			$queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
			$rowCount = $queryBuilder->select('*')
				->from($table)
				->where($queryBuilder->expr()->neq('site_root_id', 'pid'))
				->execute()->rowCount();
			if ($rowCount > 0) {
				return TRUE;
			}
		}

		// are the pids not belonging to site root pages ?
		foreach ($this->tables as $table) {
			$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
			$queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
			$result = $queryBuilder->select('pid')->from($table)->groupBy('pid')->execute();
			foreach ($result as $row) {
				$siteRootId = BackendService::getSiteRoot($row['pid']);
				if ($siteRootId !== $row['pid']) {
					return TRUE;
				}
			}
		}

		return FALSE;
	}

	/**
	 * @return array|string[]
	 */
	public function getPrerequisites(): array {
		return [
			DatabaseUpdatedPrerequisite::class
		];
	}
}