FormAsFile.php

<?php
declare(strict_types=1);

namespace IMATHUZH\Qfq\Core\Form;

use IMATHUZH\Qfq\Core\Database\Database;
use IMATHUZH\Qfq\Core\Exception\Thrower;
use IMATHUZH\Qfq\Core\Helper\HelperFile;
use IMATHUZH\Qfq\Core\Helper\Logger;
use IMATHUZH\Qfq\Core\Helper\OnArray;
use IMATHUZH\Qfq\Core\Helper\OnString;
use IMATHUZH\Qfq\Core\Helper\Path;
use IMATHUZH\Qfq\Core\Helper\SqlQuery;
use IMATHUZH\Qfq\Core\Store\Store;

class FormAsFile
{
    /**
     * Remove the form from the DB and insert it using the form file. (only if the form file was changed)
     * If the form file can't be read, then the form is deleted from the DB and an exception is thrown.
     * If the form exists only in the DB and was never exported, then it is exported.
     *
     * - Form file location: SYSTEM_FORM_FILE_PATH
     * - Recognize form file change: Compare the current file stats with the ones saved in the Form table.
     * - Container References: The form file uses names instead if ids to reference container formElements. These references are translated when the formElements are inserted.
     * - Ignored keys: The keys 'id', 'formId', 'feIdContainer' are ignored when reading the form file.
     *
     * @param string $formName
     * @param Database $database
     * @param bool $keepIfNeverExported true: If the form was never exported and file not exists, then export the form from DB
     * @return bool True if form has been updated.
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function importForm(string $formName, Database $database, bool $keepIfNeverExported = true): bool
    {
        // Get file stats from database form
        $formFromDb = $database->selectFormByName($formName, [F_ID, F_FILE_STATS]);

        // Get file stats from file system
        $absoluteFormFilePath = self::formPathFileName($formName, $database);
        $fileReadException = function () use ($absoluteFormFilePath, $database, $formName) {
            Thrower::userFormException(
                "Form file not found or missing permission: '"  . baseName($absoluteFormFilePath) . "'. Form names are case sensitive. Similar forms: "
                  . implode(', ', array_filter(self::formFileNames($database), function ($f) use ($formName) {return strtolower($f) === strtolower($formName);})),
                "Form definition file not found or no permission to read file: '$absoluteFormFilePath'"
            );
        };
        if(!file_exists($absoluteFormFilePath)) {
            if ($keepIfNeverExported && isset($formFromDb[F_ID]) && (!isset($formFromDb[F_FILE_STATS]) || !self::isValidFileStats($formFromDb[F_FILE_STATS]))) {
                // if file not exists and form was never exported, then export
                self::exportForm($formName, $database);
            } else {
                self::deleteFormDB($formName, $database, "No corresponding form file found.");
                $fileReadException();
            }
        }
        $fileStatsNew = self::formFileStatsJson($absoluteFormFilePath);
        if ($fileStatsNew === false) {
            self::deleteFormDB($formName, $database, "Failed to read form file stats.");
            $fileReadException();
        }

        // if fileStats from DB and file are equal: do nothing
        if (array_key_exists(F_FILE_STATS, $formFromDb) && $fileStatsNew === $formFromDb[F_FILE_STATS]) {
            return false;
        }

        // Read form file
        $fileContents = file_get_contents($absoluteFormFilePath);
        if ($fileContents === false) {
            self::deleteFormDB($formName, $database, "Failed to read form file.");
            $fileReadException();
        }
        $formFromFile = json_decode($fileContents, true);

        // form elements exist?
        if (!isset($formFromFile[F_FILE_FORM_ELEMENT])) {
            Thrower::userFormException('Failed to import form file.', "Json key '" . F_FILE_FORM_ELEMENT . "' in file '$absoluteFormFilePath' is missing.");
        }

        // make sure container names are unique and non-empty
        $containerNames = [];
        foreach ($formFromFile[F_FILE_FORM_ELEMENT] as $formElementFromFile) {
            $keysNotSet = OnArray::keysNotSet([FE_CLASS, FE_NAME], $formElementFromFile);
            if (!empty($keysNotSet)) {
                Thrower::userFormException('Failed to import form file.', "One or more required keys are missing in FormElement definition in file: '$absoluteFormFilePath'. Missing keys: " . implode(', ', $keysNotSet));
            }
            if ($formElementFromFile[FE_CLASS] === FE_CLASS_CONTAINER) {
                if (in_array($formElementFromFile[FE_NAME], $containerNames)) {
                    Thrower::userFormException('Failed to import form file.', "Multiple formElements of class container with the same name '" . $formElementFromFile[FE_NAME] . "' in form file '$absoluteFormFilePath'");
                }
                if ($formElementFromFile[FE_NAME] == '') {
                    Thrower::userFormException('Failed to import form file.', "Found formElement of class container with empty name in form file '$absoluteFormFilePath'");
                }
                $containerNames[] = $formElementFromFile[FE_NAME];
            }
        }

        // keep old form ID for deletion later & define temporary name for new form
        $formIdOld = $formFromDb[F_ID] ?? null;
        $newFormTempName = $formName . '_FAILED_IMPORT'; // will be renamed at the end

        // Insert new Form to DB (after filtering allowed columns and adding column 'name')
        $formSchema = $database->getTableDefinition(TABLE_NAME_FORM);
        $formColumns = array_column($formSchema, 'Field');
        $insertValues = array_filter($formFromFile, function ($columnName) use ($formColumns) {
            return $columnName !== F_ID && in_array($columnName, $formColumns);
        }, ARRAY_FILTER_USE_KEY); // array(column => value)
        $insertValues[F_NAME] = $newFormTempName;
        $insertValues[F_FILE_STATS] = $fileStatsNew;
        list($sqlFormInsert, $parameterArrayFormInsert) = SqlQuery::insertRecord(TABLE_NAME_FORM, $insertValues);
        $formIdNew = $database->sql($sqlFormInsert, ROW_REGULAR, $parameterArrayFormInsert);

        // Delete stale formElements with the new form id (these should not exist, but better make sure)
        self::deleteFormElementsDBWithFormId($formIdNew, $database, "Inserted new form with id $formIdNew.");

        // Insert FormElements to DB and collect container ids
        $containerIds = []; // array(container_name => id)
        foreach ($formFromFile[F_FILE_FORM_ELEMENT] as &$formElementFromFile) {
            $feId = self::insertFormElement($formElementFromFile, $formIdNew, $database);
            $formElementFromFile[FE_ID] = $feId;
            if ($formElementFromFile[FE_CLASS] === FE_CLASS_CONTAINER) {
                $containerIds[$formElementFromFile[FE_NAME]] =  $feId;
            }
        }

        // Update container IDs for each form element which has a container name
        foreach ($formFromFile[F_FILE_FORM_ELEMENT] as &$formElementFromFile) {
            if (array_key_exists(FE_FILE_CONTAINER_NAME, $formElementFromFile)) {
                $containerName = $formElementFromFile[FE_FILE_CONTAINER_NAME];
                if (!isset($containerIds[$containerName])) {
                    Thrower::userFormException('Failed to import form file.', "Key '" . FE_FILE_CONTAINER_NAME . "' points to non-existing container with name '$containerName' in definition of formElement with name '" . $formElementFromFile[FE_NAME] . "' in file '$absoluteFormFilePath'");
                }
                $containerId = $containerIds[$containerName];
                $feId = $formElementFromFile[FE_ID];
                list($sql, $parameterArray) = SqlQuery::updateRecord(TABLE_NAME_FORM_ELEMENT, [FE_ID_CONTAINER => $containerId], $feId);
                $database->sql($sql, ROW_REGULAR, $parameterArray);
            }
        }

        // Delete old form if everything went well
        if ($formIdOld !== null) {
            self::deleteFormDBWithId($formIdOld, $database, "New version of form $formName was imported successfully. New form id: $formIdNew");
        }

        // Replace temporary name of new form
        list($sql, $parameterArray) = SqlQuery::updateRecord(TABLE_NAME_FORM, [F_NAME => $formName], $formIdNew);
        $database->sql($sql, ROW_REGULAR, $parameterArray);

        return true;
    }

    /**
     * Reads the form from the database and saves it in the form folder as a form file.
     * If $formId is given, then $formName is ignored.
     * If the form file path does not exist, it is created and all forms are exported.
     *
     * ! Warning: Overwrites form file without any checks or warning.
     *
     * - FileStats: After the export the column "fileStats" is updated with new stats from new file.
     * - Container FormElements: The form file uses names instead if ids to reference containers. These references are translated before saving the file.
     * - Ignored columns: The columns 'id', 'name' (only Form table), 'fileStats', 'formId', 'feIdContainer' are not saved in the file.
     * - FormElement order: The formElements are ordered by the 'ord' column before writing them to the file.
     *
     * @param string $formName
     * @param Database $database
     * @param int|null $formId if given, then $formName is ignored
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function exportForm(string $formName, Database $database, ?int $formId = null) // : void
    {
        list($formName, $formId, $formJson, $adjustContainerName, $form) = self::formToJson($formName, $database, $formId);

        // backup and write file
        $pathFileName = self::formPathFileName($formName, $database);
        self::backupFormFile($pathFileName);
        HelperFile::file_put_contents($pathFileName, $formJson);
        Logger::logMessage(date('Y.m.d H:i:s ') . ": Overwrote form file '$pathFileName'. Reason: Export new version of form from database.", Path::absoluteQfqLogFile());

        // some column names where adjusted => import form
        if ($adjustContainerName) {
            Logger::logMessage(date('Y.m.d H:i:s ') . ": Importing form file '$pathFileName'. Reason: Empty or non-unique names of container formElements where adjusted during form export.", Path::absoluteQfqLogFile());
            self::importForm($formName, $database);

        // otherwise => Update column fileStats
        } else {
            $fileStats = self::formFileStatsJson($pathFileName);
            list($sql, $parameterArray) = SqlQuery::updateRecord(TABLE_NAME_FORM, [F_FILE_STATS => $fileStats, F_MODIFIED => $form[F_MODIFIED]], $formId);
            $database->sql($sql, ROW_REGULAR, $parameterArray);
        }
    }

    /**
     * Create copy of given form file in form/_backup. If given file does not exist, do nothing.
     * New file name: <formName>.YYYMMDDhhmmss.file.json
     *
     * @param string $absoluteFormFilePath
     * @throws \UserFormException
     */
    private static function backupFormFile(string $absoluteFormFilePath)
    {
        if (file_exists($absoluteFormFilePath))
        {
            if (!is_readable($absoluteFormFilePath)) {
                Thrower::userFormException('Error while trying to backup form file.', "Form file is not readable: $absoluteFormFilePath");
            }

            // copy file
            $absoluteBackupFilePath = self::newBackupPathFileName(basename($absoluteFormFilePath, '.json'), 'file');
            $success = copy($absoluteFormFilePath, $absoluteBackupFilePath);
            if ($success === false) {
                Thrower::userFormException('Error while trying to backup form file.', "Can't copy file $absoluteFormFilePath to $absoluteBackupFilePath");
            }
        }
    }

    /**
     * Import Form from file if loaded record is Form/FormElement.
     * If form file was changed, import it and throw exception.
     * If form file is not writeable, throw exception.
     *
     * @param int $recordId
     * @param string $tableName
     * @param Database $database
     * @return string|null formName|null
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function importFormRecordId(int $recordId, string $tableName, Database $database) // : ?string
    {
        $recordFormName = self::formNameFromFormRelatedRecord($recordId, $tableName, $database);
        if ($recordFormName !== null) {
            if(self::importForm($recordFormName, $database)) {
                throw new \UserFormException(json_encode([
                    ERROR_MESSAGE_TO_USER => 'Form file was changed.',
                    ERROR_MESSAGE_TO_DEVELOPER => "Form definition file has been changed. Please close tab and reload the form list and Form-Editor from scratch."]),
                    ERROR_FORM_NOT_FOUND);
            }
            self::enforceFormFileWritable($recordFormName, $database);
        }
        return $recordFormName;
    }

    /**
     * Deletes the form file for the given form.
     *
     * @param $formName
     * @param Database $database
     * @param string $logMessageReason
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function deleteFormFile($formName, Database $database, string $logMessageReason = '') // : void
    {
        self::enforceFormFileWritable($formName, $database);
        $pathFileName = self::formPathFileName($formName, $database);
        if(file_exists($pathFileName)) {
            self::backupFormFile($pathFileName);
            $success = unlink($pathFileName);
            if ($success === false) {
                throw new \UserFormException(json_encode([
                    ERROR_MESSAGE_TO_USER => "Deleting form file failed: " . baseName($pathFileName),
                    ERROR_MESSAGE_TO_DEVELOPER => "Can't delete form file '$pathFileName'"]),
                    ERROR_IO_WRITE_FILE);
            }
            Logger::logMessage(date('Y.m.d H:i:s ') . ": Removed form file '$pathFileName'. Reason: $logMessageReason", Path::absoluteQfqLogFile());
        }
    }

    /**
     * Multiple errors might occur after a form file import. This hint can be added to such exceptions to help users.
     * Only returns non-empty string if the table is either Form of FormElement.
     *
     * @param string $tableName
     * @return string
     */
    public static function errorHintFormImport (string $tableName = TABLE_NAME_FORM): string
    {
        $message = '';
        if (in_array($tableName, [TABLE_NAME_FORM, TABLE_NAME_FORM_ELEMENT])) {
            $message .= "Hint: Form definition file might have changed. Please reopen the form list and Form-Editor from scratch.";
        }
        return $message;
    }

    /**
     * Throw exception if form file or form directory is not writeable.
     *
     * @param string $formName
     * @param Database $database
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function enforceFormFileWritable(string $formName, Database $database) // : void
    {
        $pathFileName = self::formPathFileName($formName, $database);
        HelperFile::enforce_writable_or_creatable($pathFileName);

    }

    /**
     * Return form name if given record is an existing Form or FormElement.
     * Return null otherwise.
     *
     * @param int $recordId
     * @param string $recordTable
     * @param Database $database
     * @return string formName|null
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function formNameFromFormRelatedRecord(int $recordId, string $recordTable, Database $database) // : ?string
    {
        if ($recordId === 0) {
            return null;
        }
        switch ($recordTable) {
            case TABLE_NAME_FORM:
                list($sql, $parameterArray) = SqlQuery::selectFormById($recordId);
                $formFromDb = $database->sql($sql, ROW_EXPECT_1,
                    $parameterArray, "Form with id '$recordId' not found. " . self::errorHintFormImport());
                return $formFromDb[F_NAME];
            case TABLE_NAME_FORM_ELEMENT:
                $F_NAME = F_NAME;
                $TABLE_NAME_FORM_ELEMENT = TABLE_NAME_FORM_ELEMENT;
                $TABLE_NAME_FORM = TABLE_NAME_FORM;
                $F_ID = F_ID;
                $FE_FORM_ID = FE_FORM_ID;
                $FE_ID = FE_ID;

                // Select form name by formElement id
                $formFromDb = $database->sql("SELECT `f`.`$F_NAME` FROM `$TABLE_NAME_FORM` AS f INNER JOIN `$TABLE_NAME_FORM_ELEMENT` AS fe ON f.`$F_ID`=fe.`$FE_FORM_ID` WHERE `fe`.`$FE_ID`=?", ROW_EXPECT_1,
                    [$recordId], "Form element with id '$recordId' not found. " . self::errorHintFormImport());
                return $formFromDb[F_NAME];
            default:
                return null;
        }
    }

    /**
     * Returns true if the sql query selects Form or FormElement table
     *
     * @param string $sql
     * @return bool
     */
    public static function isFormQuery(string $sql): bool
    {
        // find substrings which start with FROM and are followed by Form or FormElement
        preg_match_all('/(?i)FROM(?-i)(.*?)\b(' . TABLE_NAME_FORM . '|' . TABLE_NAME_FORM_ELEMENT . ')\b/s', $sql, $matches);

        // Check if no other SQL keywords are in between FROM and the table name
        $keywordsAfterFrom = ['WHERE', 'GROUP BY', 'HAVING', 'WINDOW', 'ORDER BY', 'LIMIT', 'FOR', 'INTO'];
        foreach($matches[0] as $match)
        {
            if(!OnString::containsOneOfWords($keywordsAfterFrom, $match)) {
                return true;
            }
        }
        return false;
    }

    /**
     * Import all form files into the database.
     * If the file folder does not exist, it is created and all forms from the DB are exported.
     *
     * @param Database $database
     * @param bool $enforceWritable Throw exception if one of the form files is not writable by current user.
     * @param bool $deleteFromDB Delete forms from DB if there are no corresponding form files. Except if the form was never exported.
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function importAllForms(Database $database, bool $enforceWritable = false, bool $deleteFromDB = false) // : void
    {
        // Import all form files
        $formFileNames = self::formFileNames($database);
        foreach ($formFileNames as $formFileName) {
            self::importForm($formFileName, $database);
            if ($enforceWritable) {
                self::enforceFormFileWritable($formFileName, $database);
            }
        }

        // Delete all forms which are in DB but not in files. Except if they were never exported.
        if ($deleteFromDB) {
            $formNamesDB = self::queryAllFormNames($database);
            $formsToDelete = array_diff($formNamesDB, $formFileNames);
            foreach ($formsToDelete as $formToDelete) {
                self::deleteFormDB($formToDelete, $database, "No corresponding form file found.", true);
            }
        }
    }

    /**
     * Export all forms in database to form files.
     * Warning: This overwrites form files without any checks or warning. Use cautiously.
     *
     * @param Database $database
     * @param bool $deleteFiles Delete form files without a corresponding form record in the DB.
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public static function exportAllForms(Database $database, bool $deleteFiles = false) // : void
    {
        $formNamesDB = self::queryAllFormNames($database);
        foreach ($formNamesDB as $formNameDB) {
            self::exportForm($formNameDB, $database);
        }
        if ($deleteFiles) {
           $formFileNames = self::formFileNames($database);
           $filesToDelete = array_diff($formFileNames, $formNamesDB);
           foreach ($filesToDelete as $fileToDelete) {
               self::deleteFormFile($fileToDelete, $database, "Export all forms from database. No form with name '$fileToDelete' in database.");
           }
        }
    }

    /**
     * Insert formElement to the given form.
     * Keys removed before insert: id, formId, feIdContainer
     *
     * @param array $values
     * @param int $formId
     * @param Database $database
     * @return int
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function insertFormElement(array $values, int $formId, Database $database): int
    {
        // filter allowed formElement columns (remove id, formId, feIdContainer)
        $formElementSchema = $database->getTableDefinition(TABLE_NAME_FORM_ELEMENT);
        $formElementColumns = array_column($formElementSchema, 'Field');
        $insertValues = array_filter($values, function ($columnName) use ($formElementColumns) {
            return !in_array($columnName, [FE_ID, FE_FORM_ID, FE_ID_CONTAINER]) && in_array($columnName, $formElementColumns);
        }, ARRAY_FILTER_USE_KEY); // array(column => value)

        // execute insert
        $insertValues[FE_FORM_ID] = $formId;
        list($sql, $parameterArray) = SqlQuery::insertRecord(TABLE_NAME_FORM_ELEMENT, $insertValues);
        $id = $database->sql($sql, ROW_REGULAR, $parameterArray);

        return $id;
    }

    /**
     * Delete form with given name and its form elements from DB.
     * If $keepIfNeverExported is set to true then the form is kept (i.e. exported) instead of deleted if the form was never exported yet.
     *
     * @param string $formName
     * @param Database $database
     * @param string $logMessageReason
     * @param bool $keepIfNeverExported
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function deleteFormDB(string $formName, Database $database, string $logMessageReason = '', bool $keepIfNeverExported = false) // : void
    {
        $formFromDb = $database->selectFormByName($formName, [F_ID, F_FILE_STATS]);

        if ($keepIfNeverExported && (isset($formFromDb[F_ID]) && !isset($formFromDb[F_FILE_STATS])) ||  (isset($formFromDb[F_FILE_STATS]) && !self::isValidFileStats($formFromDb[F_FILE_STATS]))) {
            // export form instead of deleting since it was never exported before
            self::exportForm($formName, $database);
        } else if (array_key_exists(F_ID, $formFromDb)) {
            self::deleteFormDBWithId($formFromDb[F_ID], $database, $logMessageReason . " Form name '$formName'.");
        }
    }

    /**
     * Return true if the given string is a valid JSON encoded file stats string.
     * Currently only checks if first char is '{'
     *
     * @param string $fileStats
     * @return bool
     */
    private static function isValidFileStats(string $fileStats) {
        return substr($fileStats, 0, 1 ) === '{';
    }

    /**
     * Delete form with given id and its form elements from DB
     *
     * @param int $formId
     * @param Database $database
     * @param string $logMessageReason
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function deleteFormDBWithId(int $formId, Database $database, string $logMessageReason = '') // : void
    {
        self::backupFormDb($formId, $database);
        $F_ID = F_ID; // can't use constants in strings directly
        $TABLE_NAME_FORM = TABLE_NAME_FORM; // can't use constants in strings directly
        $rowsAffected = $database->sql("DELETE FROM `$TABLE_NAME_FORM` WHERE `$F_ID`=? LIMIT 1", ROW_REGULAR, [$formId]);
        if ($rowsAffected > 0) {
            Logger::logMessage(date('Y.m.d H:i:s ') . ": Remove form with id $formId from database. Reason: $logMessageReason", Path::absoluteQfqLogFile());
        }
        self::deleteFormElementsDBWithFormId($formId, $database, 'Remove form. ' . $logMessageReason);
    }

    /**
     * Export the given form from database into a backup file.
     * Backup file name: <formName>.YYYMMDDhhmmss.db.json
     *
     * @param int $formId
     * @param Database $database
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function backupFormDb(int $formId, Database $database)
    {
        list($formName, $formId, $formJson) = self::formToJson('', $database, $formId);
        $absoluteBackupFilePath = self::newBackupPathFileName($formName, 'db');
        HelperFile::file_put_contents($absoluteBackupFilePath, $formJson);
    }


    /**
     * Delete form elements with given formId from DB
     *
     * @param int $formId
     * @param Database $database
     * @param string $logMessageReason
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function deleteFormElementsDBWithFormId(int $formId, Database $database, string $logMessageReason = '') // : void
    {
        $TABLE_NAME_FORM_ELEMENT = TABLE_NAME_FORM_ELEMENT; // can't use constants in strings directly
        $FE_FORM_ID = FE_FORM_ID; // can't use constants in strings directly
        $affectedRows = $database->sql("DELETE FROM `$TABLE_NAME_FORM_ELEMENT` WHERE `$FE_FORM_ID`=?", ROW_REGULAR, [$formId]);
        if ($affectedRows > 0) {
            Logger::logMessage(date('Y.m.d H:i:s ') . ": Removed $affectedRows formElements with formId $formId from database. Reason: $logMessageReason", Path::absoluteQfqLogFile());
        }
    }

    /**
     * Return a selection of file stats as a JSON string which serves as a fingerprint of the file.
     * If any one of the stats changed the file content was probably changed as well.
     *
     * @param string $pathFileName
     * @return false|string
     */
    private static function formFileStatsJson(string $pathFileName)
    {
        clearstatcache (true, $pathFileName);
        $stats = stat($pathFileName);
        if ($stats === false) {
            return false;
        }
        return json_encode([
            'modified' => $stats['mtime'],
            'size' => $stats['size'],
            'inode' => $stats['ino']
        ]);
    }

    /**
     * Return correct pathFileName of form file relative to current working directory.
     * Create path if it doesn't exist and export all forms from DB.
     *
     * @param string $formName
     * @param Database $database
     * @return string
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function formPathFileName(string $formName, Database $database): string
    {
        // validate form name
        if (!HelperFile::isValidFileName($formName)) {
            throw new \UserFormException(json_encode([
                ERROR_MESSAGE_TO_USER => 'Reading/Writing Form file failed.',
                ERROR_MESSAGE_TO_DEVELOPER => "Form name '$formName' not valid. Name may only consist of alphanumeric characters and _ . -"]),
                ERROR_FORM_INVALID_NAME);
        }
        return self::formPath($database) . '/' . $formName . ".json";
    }

    /**
     * Return the path of the form directory relative to CWD.
     * Create path if it doesn't exist and export all forms from DB.
     *
     * @param Database $database
     * @return string
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function formPath(Database $database): string
    {
        $absoluteFormPath = Path::absoluteProject(Path::projectToForm());
        if (!is_dir($absoluteFormPath)) {

            // create path
            $success = mkdir($absoluteFormPath, 0777, true);
            if ($success === false) {
                throw new \UserFormException(json_encode([
                    ERROR_MESSAGE_TO_USER => "Can't create form file path.",
                    ERROR_MESSAGE_TO_DEVELOPER => "Can't create path: " . $absoluteFormPath]),
                    ERROR_IO_WRITE_FILE);
            }

            // export all forms
            self::exportAllForms($database);
        }
        return $absoluteFormPath;
    }

    /**
     * Return array of all form names in the Form table.
     *
     * @param Database $database
     * @return array [formName]
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function queryAllFormNames(Database $database): array
    {
        $NAME = F_NAME;
        $FORM = TABLE_NAME_FORM;
        return array_column($database->sql("SELECT `$NAME` FROM `$FORM`", ROW_REGULAR), $NAME);
    }

    /**
     * Return array of form file names contained in the form path (without suffix).
     *
     * @param Database $database
     * @return array|false [formName]
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function formFileNames(Database $database): array
    {
        $formPath = self::formPath($database);
        $files = scandir($formPath);
        if ($files === false) {
            throw new \UserFormException(json_encode([
                ERROR_MESSAGE_TO_USER => "Reading directory failed.",
                ERROR_MESSAGE_TO_DEVELOPER => "Can't read directory: " . $formPath]),
                ERROR_IO_READ_FILE);
        }
        return $jsonFileNames = array_reduce($files, function ($result, $file) {
            $fileInfo = pathinfo($file);
            if (isset($fileInfo['extension']) && isset($fileInfo['filename']) && $fileInfo['extension'] === 'json') {
                $result[] = $fileInfo['filename'];
            }
            return $result;
        }, []);
    }

    /**
     * Return json string of the given form with form-elements. If $formId is given, $formName is ignored.
     *
     * - Container FormElements: The form file uses names instead if ids to reference containers. These references are translated before saving the file.
     * - Adjust container names: If a container name is empty or not unique it is adjusted. In that case the fourth return parameter is set to true.
     * - Ignored columns: The columns 'id', 'name' (only Form table), 'fileStats', 'formId', 'feIdContainer' are not added to the json output.
     * - FormElement order: The formElements are ordered by the 'ord' column before writing them to the file.
     *
     * @param string $formName
     * @param Database $database
     * @param int|null $formId If given, $formName is ignored
     * @return array [string $formName, int $formId, string $formJson, bool ($adjustedContainerNames > 0), array $form]
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function formToJson(string $formName, Database $database, ?int $formId = null): array
    {
        // Get form from DB (either by id or by name)
        if ($formId !== null) {
            list($sql, $parameterArray) = SqlQuery::selectFormById($formId);
            $form = $database->sql($sql, ROW_EXPECT_1,
                $parameterArray, "Form with id $formId not found."); // array(column name => value)
        } else {
            $form = $database->selectFormByName($formName); // array(column name => value)
            if (!isset($form[F_ID])) {
                Thrower::userFormException("Error during form export.", "Form $formName not found in database. Note: Form names are case sensitive.");
            }
        }

        // Remove columns: id, name, fileStats
        $formId = $form[F_ID];
        $formName = $form[F_NAME];
        unset($form[F_ID]);
        unset($form[F_NAME]);
        unset($form[F_FILE_STATS]);

        // Get formElements from DB
        list($sql, $parameterArray) = SqlQuery::selectFormElementById($formId);
        $formElements = $database->sql($sql, ROW_REGULAR, $parameterArray); // array(array(column name => value))

        // Create id => name dictionary for column names
        $adjustedContainerNames = 0;
        $containerNames = array_reduce($formElements, function ($result, $formElement) use ($formName, $formId, &$adjustedContainerNames) {
            if ($formElement[FE_CLASS] === FE_CLASS_CONTAINER) {
                $containerName = $formElement[FE_NAME];

                // container name not unique => adjust make unique
                if (in_array($containerName, $result)) {
                    $containerName = $containerName . '_auto_adjust_not_unique_' . count($result);
                    $adjustedContainerNames++;
                }

                // container name empty => adjust make non-empty unique
                if ($containerName === '') {
                    $containerName = 'auto_adjust_empty_' . count($result);
                    $adjustedContainerNames++;
                }
                $result[$formElement[FE_ID]] = $containerName;
            }
            return $result;
        }, []); // array(id => name)

        // ajdust formElements for export
        $formElements = array_map(function ($formElement) use ($containerNames) {

            // in case container name was auto adjusted above we set new name
            if (isset($containerNames[$formElement[FE_ID]])) {
                $formElement[FE_NAME] = $containerNames[$formElement[FE_ID]];
            }

            // Replace container id references with name references
            $containerId = $formElement[FE_ID_CONTAINER];
            if ($containerId !== 0 && isset($containerNames[$containerId])) {
                $formElement[FE_FILE_CONTAINER_NAME] = $containerNames[$containerId];
            }

            // remove columns id, formId, feIdContainer
            unset($formElement[FE_ID_CONTAINER]);
            unset($formElement[FE_ID]);
            unset($formElement[FE_FORM_ID]);

            return $formElement;
        }, $formElements);

        // add form elements and create json
        $form[F_FILE_FORM_ELEMENT] = $formElements;
        $formJson = json_encode($form, JSON_PRETTY_PRINT);
        return array($formName, $formId, $formJson, $adjustedContainerNames > 0, $form);
    }

    /**
     * Return the path to a (non-existing) form backup file with name:
     * <formName>.YYYMMDDhhmmss.<tag>.json
     *
     * @param string $formName
     * @param string $tag
     * @return string
     * @throws \UserFormException
     */
    private static function newBackupPathFileName(string $formName, string $tag): string
    {
        // create backup path if not exists
        $absoluteBackupPath = Path::absoluteProject(Path::projectToForm(), Path::FORM_TO_FORM_BACKUP);
        if (!is_dir($absoluteBackupPath)) {
            $success = mkdir($absoluteBackupPath, 0777, true);
            if ($success === false) {
                Thrower::userFormException('Error while trying to backup form file.', "Can't create backup path: $absoluteBackupPath");
            }
        }

        $absoluteBackupFilePath = Path::join($absoluteBackupPath, $formName . '.json.' . date('Y-m-d_H-i-s') . ".$tag");

        // add index to filename if backup file with current timestamp already exists
        $index = 1;
        while (file_exists($absoluteBackupFilePath)) {
            $absoluteBackupFilePath = Path::join($absoluteBackupPath, $formName . '.json.' . date('Y-m-d_H-i-s') . ".$index.$tag");
            $index ++;
            if ($index > 20) {
                Thrower::userFormException('Error while trying to backup form file.', 'Infinite loop.');
            }
        }
        return $absoluteBackupFilePath;
    }
}