DatabaseUpdate.php

<?php
/**
 * Created by PhpStorm.
 * User: crose
 * Date: 5/9/17
 * Time: 8:56 AM
 */

namespace IMATHUZH\Qfq\Core\Database;

use IMATHUZH\Qfq\Core\Form\FormAsFile;
use IMATHUZH\Qfq\Core\Helper\Logger;
use IMATHUZH\Qfq\Core\Helper\Path;
use IMATHUZH\Qfq\Core\Store\Store;
use IMATHUZH\Qfq\Core\Typo3\T3Handler;


/*
 * Read the extension version number.
 * Read the QFQ database version number: stored in the comment field of table 'Form'. Format:  Version=x.y.z
 * If versions different:
 *   * Read the update array 'DatabaseUpdateData.php'.
 *   * Play all changes from the update array after 'old' upto 'new'.
 *   * Save new QFQ database version in the comment field of table 'Form'
 *
 * In a new QFQ installation, the comment field of table 'Form' is empty. On the first call of QFQ, the version string
 * will be set. Also the 'formEditor.sql' will be played initially.
 *
 */

/**
 * Class DatabaseUpdate
 * @package qfq
 */
class DatabaseUpdate {

    /**
     * @var Database
     */
    protected $db = null;

    /**
     * @var Store
     */
    protected $store = null;

    /**
     * @param Database $db
     * @param Store $store
     */
    public function __construct(Database $db, Store $store) {
        $this->db = $db;
        $this->store = $store;
    }

    /**
     * @return mixed
     * @throws \CodeException
     */
    private function getExtensionVersion() {
        $path = __DIR__ . '/../../../ext_emconf.php';
        $_EXTKEY = EXT_KEY;
        $EM_CONF = null;
        if (@file_exists($path)) {
            include $path;

            if (isset($EM_CONF[$_EXTKEY]['version'])) {
                return $EM_CONF[$_EXTKEY]['version'];
            }
        }

        throw new \CodeException('Failed to read extension version', ERROR_QFQ_VERSION);
    }

    /**
     * Try to read the QFQ version number from 'comment' in table 'Form'.
     * In a very special situation , there might be table 'form' and 'Form' in the same Database. This should be handled
     *  in the way that the latest version number is the active one.
     *
     * @return bool|string  false if there is no table 'Form' or if there is no comment set in table 'Form'.
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function getDatabaseVersion() {

        $arr = $this->db->sql("SHOW TABLE STATUS WHERE Name='Form'", ROW_REGULAR);

        $found = '';
        //
        foreach ($arr as $row) {
            if (isset($row['Comment'])) {
                parse_str($row['Comment'], $arr);
                if (($arr[QFQ_VERSION_KEY] ?? '') !== '' AND (version_compare($arr[QFQ_VERSION_KEY], $found) == 1)) {
                    $found = $arr;
                }
            } else {
                continue;
            }
        }

        return ($found === '') ? false : $found;
    }

    /**
     * @param $version
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function setDatabaseVersion($version) {

        if (is_array($version)) {
            $versionInfo = $version;
        } else {
            $versionInfo = $this->getDatabaseVersion();
            $versionInfo[QFQ_VERSION_KEY] = $version;
        }

        $this->db->sql("ALTER TABLE `Form` COMMENT = '" . http_build_query($versionInfo) . "'");

    }

    /**
     *
     * @param string $dbUpdate SYSTEM_DB_UPDATE_ON | SYSTEM_DB_UPDATE_OFF | SYSTEM_DB_UPDATE_AUTO
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function checkNupdate($dbUpdate) {

        $new = $this->getExtensionVersion();
        $versionInfo = $this->getDatabaseVersion();
        $old = $versionInfo[QFQ_VERSION_KEY] ?? false;

        $this->checkT3QfqConfig($old, $new);


        if ($dbUpdate === SYSTEM_DB_UPDATE_NEVER) {
            return;
        }

        if ($dbUpdate === SYSTEM_DB_UPDATE_ALWAYS || ($dbUpdate === SYSTEM_DB_UPDATE_AUTO && $new != $old)) {

            if (version_compare($old, '21.2.0') < 1 && !defined('PHPUNIT_QFQ')) {
                $this->enforceExistenceOfFormEditorReport();
            }

            $newFunctionHash = $this->updateSqlFunctions($versionInfo[QFQ_VERSION_KEY_FUNCTION_HASH] ?? '');
            if (null !== $newFunctionHash) {
                $versionInfo[QFQ_VERSION_KEY_FUNCTION_HASH] = $newFunctionHash;
                $versionInfo[QFQ_VERSION_KEY_FUNCTION_VERSION] = $new;
            }

            if (FEATURE_FORM_FILE_SYNC) {
                if ($this->db->existTable('Form')) {
                    // If Form table exists, import all form files so everything is up to date.
                    FormAsFile::importAllForms($this->db, true); // Note: Creates path and exports all forms first if the form directory does not exist.

                    // create Form table and export all system forms
                    $this->db->playSqlFile(__DIR__ . '/../../Sql/formEditor.sql');
                    FormAsFile::exportAllForms($this->db);
                } else {
                    // If not, then create Form table and export all system forms
                    $this->db->playSqlFile(__DIR__ . '/../../Sql/formEditor.sql');
                    FormAsFile::exportAllForms($this->db);

                    // import form files which existed before the new installation
                    FormAsFile::importAllForms($this->db, true);
                }
            } else {
                $this->db->playSqlFile(__DIR__ . '/../../Sql/formEditor.sql');
            }

            // Perform dbUpdate only after formEditor.sql has been played:
            // in case a new system table has been added between old and new and there is an dbUpdate on the new system table now.
            $this->dbUpdateStatements($old, $new);

            FormAsFile::importSystemForms($this->db);

            Logger::logMessage(date('Y.m.d H:i:s ') . ": Updated from QFQ version '$old' to '$new'", Path::absoluteQfqLogFile());

            // Finally write the latest version number.
            $versionInfo[QFQ_VERSION_KEY] = $new;
            $this->setDatabaseVersion($versionInfo);
        }

        if ($old === false) {
            // A complete new installation get's some extra tables
            $this->db->playSqlFile(__DIR__ . '/../../Sql/customTable.sql');
        }

        if (version_compare($old, '19.9.0') === -1) {
            $this->updateSpecialColumns();
            if (FEATURE_FORM_FILE_SYNC) {
                FormAsFile::exportAllForms($this->db);
            }
        }

    }

    /**
     * Check Typo3 config if values needs to be updated.
     * This is typically necessary if default config values change, to guarantee existing installations behave in legacy mode.
     *
     * @param $old
     * @param $new
     */
    private function checkT3QfqConfig($old, $new) {

        if ($new == $old || $old===false ) {
            // Set baseUrl default
            if($GLOBALS["_SERVER"]["SCRIPT_URI"] !== ''){
                if($GLOBALS["TYPO3_CONF_VARS"]["EXTENSIONS"]["qfq"]["baseUrl"] === ''){
                    $fullUrl = parse_url($GLOBALS["_SERVER"]["SCRIPT_URI"]);
                    $fullUrl['sections'] = explode('/', $fullUrl['path']);
                    $baseUrl = $fullUrl['scheme'].'://'.$fullUrl['host'];
                    for($i = 1; $i < sizeof($fullUrl['sections']) -1; $i++){
                        $baseUrl .= '/'.$fullUrl['sections'][$i];
                    }
                    T3Handler::updateT3QfqConfig(SYSTEM_BASE_URL, $baseUrl); //Legacy behaviour.
                }
            }
            return;

        }

        if (version_compare($old, '20.2.0') == -1) {
            T3Handler::updateT3QfqConfig(SYSTEM_RENDER, SYSTEM_RENDER_BOTH); //Legacy behaviour.
        }
    }

    /**
     * Throws exception if no tt-content record exists which contains "file=_formEditor"
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function enforceExistenceOfFormEditorReport() {
        $dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);
        $sql = "SELECT `uid` FROM " . $dbT3 . ".`tt_content` WHERE `CType`='qfq_qfq' AND `deleted`=0 AND `bodytext` LIKE '%file=_formEditor%'";
        $res = $this->db->sql($sql);
        if (empty($res)) {
            $message = '<h2>FormEditor Report not found</h2>'
                . 'Please create a Typo3 QFQ content element with the following content:'
                . '<pre>file=_formEditor</pre>'
                . 'More information: See Section "FormEditor" of the QFQ Documentation.';
            $errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq.';
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $message;
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER_SANITIZE] = false;
            throw new \DbException(json_encode($errorMsg), E_ERROR);
        }
    }

    /**
     * Check if there are special columns without prepended underscore in the QFQ application. If yes, then throw an error.
     * A link is provided to automatically prepend all found special columns. And another link to skip the auto-replacement.
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function updateSpecialColumns() {

        // Prepare regex patterns to find "AS <special column name>"
        $special_columns = ['link', 'exec', 'Page', 'Pagec', 'Paged', 'Pagee', 'Pageh', 'Pagei', 'Pagen', 'Pages'
            , 'page', 'pagec', 'paged', 'pagee', 'pageh', 'pagei', 'pagen', 'pages', 'yank', 'Pdf', 'File', 'Zip'
            , 'pdf', 'file', 'zip', 'excel', 'savePdf', 'thumbnail', 'monitor', 'mimeType', 'fileSize', 'nl2br'
            , 'htmlentities', 'striptags', 'XLS', 'XLSs', 'XLSb', 'XLSn', 'bullet', 'check', 'img', 'mailto'
            , 'sendmail', 'vertical'];

        $make_pattern = function ($column) {
            return '/([aA][sS]\s+)(' . $column . ')/s';
        };

        $patterns = array_map($make_pattern, $special_columns);

        // Prepare search and replace
        $placeholder = '%%%UNDERLINE%%%';  // used temporarily to mark where '_' should go
        $actionSpecialColumn = $_GET[ACTION_SPECIAL_COLUMN_UPDATE] ?? ''; // get parameter to decide whether to execute the replacement
        $dbT3 = $this->store->getVar(SYSTEM_DB_NAME_T3, STORE_SYSTEM);
        $message = ''; // error message in case an old special column is found

        // TT_CONTENT tt_content.bodytext
        $message_fe = '';
        if (defined('PHPUNIT_QFQ')) {
            $res = array();
        } else {
            $res = $this->db->sql("SELECT `uid`, `header`, `bodytext` FROM `" . $dbT3 . "`.`tt_content` WHERE `CType`='qfq_qfq' AND `deleted`=0;");
        }
        foreach ($res as $i => $tt_content) {
            $replaced_placeholder = preg_replace($patterns, '${1}' . $placeholder . '${2}', $tt_content['bodytext']);
            if (strpos($replaced_placeholder, $placeholder) !== false) {
                if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {
                    $replace = str_replace($placeholder, '_', $replaced_placeholder);
                    $query = "UPDATE `" . $dbT3 . "`.`tt_content` SET `bodytext`='" . addslashes($replace) . "' WHERE `uid`='" . $tt_content['uid'] . "'";
                    $this->db->sql($query);
                }
                $message_fe .= '<hr><b>' . $tt_content['header'] . ' [uid:' . $tt_content['uid'] . ']</b><br><br>';
                $message_fe .= str_replace($placeholder,
                    '<span style="font-weight: bold; color: red;">>>>_</span>',
                    htmlentities($replaced_placeholder));
            }
        }
        if ($message_fe != '') {
            $message .= '<hr><h3>Typo3 Table: tt_content (column: bodytext)</h3>' . $message_fe;
        }

        // FORM ELEMENTS FormElement.value, FormElement.note
        $message_ttc = '';
        if (defined('PHPUNIT_QFQ')) {
            $res = array();
        } else {
            $res = $this->db->sql("SELECT `fe`.`id`, `fe`.`name`, `fe`.`value`, `fe`.`note` FROM `FormElement` AS fe WHERE `fe`.`type`='note' AND `fe`.`value` LIKE '#!report%' OR `fe`.`note` LIKE '%#!report%';");
        }
        foreach ($res as $i => $tt_content) {

            foreach (['value', 'note'] as $j => $columnName) {
                $replaced_placeholder = preg_replace($patterns, '${1}' . $placeholder . '${2}', $tt_content[$columnName]);
                if (strpos($replaced_placeholder, $placeholder) !== false) {
                    if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {
                        $replace = str_replace($placeholder, '_', $replaced_placeholder);
                        $query = "UPDATE `FormElement` SET `" . $columnName . "`='" . addslashes($replace) . "' WHERE `id`='" . $tt_content['id'] . "'";
                        $this->db->sql($query);
                    }
                    $message_ttc .= '<hr><b>' . $tt_content['name'] . ' [id:' . $tt_content['id'] . '] (FormElement.' . $columnName . ')</b><br><br>';
                    $message_ttc .= str_replace($placeholder,
                        '<span style="font-weight: bold; color: red;">>>>_</span>',
                        htmlentities($replaced_placeholder));
                }
            }
        }
        if ($message_ttc != '') {
            $message .= '<hr><h3>QFQ Table: FormElement (columns: value and note)</h3>' . $message_ttc;
        }

        // show error message or save log
        if ($message != '') {
            if ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_REPLACE) {
                // save log file
                $message = '<h1>Special column names replaced</h1>The following special column names were replaced.<hr>' . $message;
                Logger::logMessage($message, Path::absoluteLog() . '/' . date("YmdHi") . '_special_columns_auto_update.html');
            } elseif ($actionSpecialColumn === ACTION_SPECIAL_COLUMN_DO_SKIP_REPLACE) {
                // do nothing
            } else {
                // show error
                $message = $actionSpecialColumn
                    . '<h2>Special Column names without prepended underscore found.</h2>'
                    . ' Those are not supported any longer.'
                    . '<h2>SOLUTION</h2>'
                    . 'Click <a href="?' . http_build_query(array_merge($_GET, array(ACTION_SPECIAL_COLUMN_UPDATE => ACTION_SPECIAL_COLUMN_DO_REPLACE))) . '">Auto-Replace</a>'
                    . ' to automatically prepend the found column names with an underscore.'
                    . ' In the report below the missing underscores are marked by "<span style="font-weight: bold; color: red;">>>>_</span>".'
                    . ' This report will be saved in ' . Path::absoluteLog() . ' after the automatic replacement.'
                    . ' <br><br>To update qfq without changing the special columns (your app will probably be broken): '
                    . '<a href="?' . http_build_query(array_merge($_GET, array(ACTION_SPECIAL_COLUMN_UPDATE => ACTION_SPECIAL_COLUMN_DO_SKIP_REPLACE))) . '">Skip Auto-Replace</a>'
                    . '<h2>Report</h2>'
                    . $message;
                $errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq. ';
                $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = $message;
                $errorMsg[ERROR_MESSAGE_TO_DEVELOPER_SANITIZE] = false;
                throw new \DbException(json_encode($errorMsg), ERROR_PLAY_SQL_FILE);
            }
        }
    }

    /**
     * @param $oldFunctionsHash
     *
     * @return string
     *
     * @throws \DbException
     * @throws \UserFormException
     */
    private function updateSqlFunctions($oldFunctionsHash) {

        if (ACTION_FUNCTION_UPDATE_NEVER === $oldFunctionsHash) {
            return null;
        }

        $actionFunction = $_GET[ACTION_FUNCTION_UPDATE] ?? '';

        if ($actionFunction === ACTION_FUNCTION_UPDATE_NEXT_UPDATE) {
            return ACTION_FUNCTION_UPDATE_NOT_PERFORMED;
        } elseif ($actionFunction === ACTION_FUNCTION_UPDATE_NEVER) {
            return ACTION_FUNCTION_UPDATE_NEVER;
        }

        $functionSql = file_get_contents(__DIR__ . '/../../Sql/function.sql');
        $functionHash = hash('md5', $functionSql);

        if ($functionHash === $oldFunctionsHash) {
            return null;
        }

        $query = str_replace('%%FUNCTIONSHASH%%', $functionHash, $functionSql);
        if (stripos($query, 'delimiter')) {
            $errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq.';
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] = "Error in file " . QFQ_FUNCTION_SQL . ": The keyword DELIMITER is present " .
                "in " . QFQ_FUNCTION_SQL . ", this usually leads to errors when trying to execute it on the database.";
            throw new \DbException(json_encode($errorMsg), ERROR_PLAY_SQL_FILE);
        }

        try {
            $this->db->playMultiQuery($query);
            $functionsHashTest = $this->db->sql('SELECT GETFUNCTIONSHASH() AS res;', ROW_EXPECT_1)['res'];
        } catch (\DbException $e) {
            $functionsHashTest = null;
        } catch (\CodeException $e) {
            $functionsHashTest = null;
        }

        $qfqFunctionSqlRelToApp = Path::appToExt('Classes/Sql/' . QFQ_FUNCTION_SQL);

        if ($functionHash !== null AND $functionsHashTest === $functionHash) {
            return $functionHash;
        } else {
            $errorMsg[ERROR_MESSAGE_TO_USER] = 'Error while updating qfq.';
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER] =
                "Failed to play " . QFQ_FUNCTION_SQL . ", probably not enough <a href='https://mariadb.com/kb/en/library/stored-routine-privileges/'>permissions</a> for the qfq mysql user. " .
                "Possible solutions: <ul>" .
                '<li>Grant SUPER, CREATE ROUTINE, ALTER ROUTINE privileges to qfq mysql user temporarily.</li>' .
                '<li>Play the following file manually on the database: ' .
                '<a href="' . $qfqFunctionSqlRelToApp . '">' . $qfqFunctionSqlRelToApp . '</a><br>and grant the qfq mysql user execution privileges on the sql functions.</li>' .
                '<li><a href="?' . http_build_query(array_merge($_GET, array(ACTION_FUNCTION_UPDATE => ACTION_FUNCTION_UPDATE_NEXT_UPDATE))) . '">Click here</a> to skip the sql functions update until next qfq release update</li>' .
                '<li><a href="?' . http_build_query(array_merge($_GET, array(ACTION_FUNCTION_UPDATE => ACTION_FUNCTION_UPDATE_NEVER))) . '">Click here</a> to skip the sql functions update forever</li>' .
                '</ul>' .
                "To enable the sql functions update again you can delete the parameter 'functionsHash' in the table comments of the table 'Form'.";
            $errorMsg[ERROR_MESSAGE_TO_DEVELOPER_SANITIZE] = false;
            throw new \DbException(json_encode($errorMsg), ERROR_PLAY_SQL_FILE);
        }
    }

    /**
     * @param $path
     *
     * @return array
     * @throws \CodeException
     */
    private function readUpdateData($path) {

        if (!@file_exists($path)) {
            throw new \CodeException("File '$path'' not found", ERROR_IO_OPEN);
        }

        $UPDATE_ARRAY = null;

        include $path;

        return $UPDATE_ARRAY;

    }

    /**
     * Play all update statement with version number are '>' than $old and '<=' to $new.
     *
     * @param $old
     * @param $new
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private function dbUpdateStatements($old, $new) {

        $dummy = array();

        if ($new == '' || $old === false || $old === null) {
            return;
        }
        $updateArray = $this->readUpdateData(__DIR__ . '/DatabaseUpdateData.php');

        $apply = false;
        foreach ($updateArray as $key => $sqlStatements) {

            // Search starting point to apply updates. Do not apply updates for $key>$new
            $rc1 = version_compare($key, $old);
            $rc2 = version_compare($key, $new);
            if ($rc1 == 1 && $rc2 != 1) {
                $apply = true;
            }

            if ($apply) {
                // Play Statements
                foreach ($sqlStatements as $sql) {
                    $this->db->sql($sql, ROW_REGULAR, array(),
                        "Apply updates to QFQ database. Installed version: $old. New QFQ version: $new",
                        $dummy, $dummy, [1060] /* duplicate column name */);
                }
                // Remember already applied updates - in case something breaks and the update has to be repeated.
                $this->setDatabaseVersion($new);
            }
        }
    }
}