FormAsFile.php 35.2 KB
Newer Older
Marc Egger's avatar
Marc Egger committed
1
<?php
2
declare(strict_types=1);
Marc Egger's avatar
Marc Egger committed
3
4
5
6

namespace IMATHUZH\Qfq\Core\Form;

use IMATHUZH\Qfq\Core\Database\Database;
7
use IMATHUZH\Qfq\Core\Exception\Thrower;
8
use IMATHUZH\Qfq\Core\Helper\HelperFile;
9
use IMATHUZH\Qfq\Core\Helper\Logger;
10
use IMATHUZH\Qfq\Core\Helper\OnArray;
11
use IMATHUZH\Qfq\Core\Helper\OnString;
12
use IMATHUZH\Qfq\Core\Helper\Path;
13
use IMATHUZH\Qfq\Core\Helper\SqlQuery;
14
use IMATHUZH\Qfq\Core\Store\Store;
Marc Egger's avatar
Marc Egger committed
15
16
17

class FormAsFile
{
18
    /**
Marc Egger's avatar
Marc Egger committed
19
20
     * 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.
21
     * If the form exists only in the DB and was never exported, then it is exported.
22
     *
23
24
25
26
     * - 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.
27
     *
28
29
     * @param string $formName
     * @param Database $database
30
     * @param bool $keepIfNeverExported true: If the form was never exported and file not exists, then export the form from DB
31
     * @return bool True if form has been updated.
32
33
34
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
35
     */
36
    public static function importForm(string $formName, Database $database, bool $keepIfNeverExported = true): bool
37
    {
38
        // Get file stats from database form
39
        $formFromDb = $database->selectFormByName($formName, [F_ID, F_FILE_STATS]);
40

41
        // Get file stats from file system
42
43
        $absoluteFormFilePath = self::formPathFileName($formName, $database);
        $fileReadException = function () use ($absoluteFormFilePath, $database, $formName) {
44
            Thrower::userFormException(
45
                "Form file not found or missing permission: '"  . baseName($absoluteFormFilePath) . "'. Form names are case sensitive. Similar forms: "
46
                  . implode(', ', array_filter(self::formFileNames($database), function ($f) use ($formName) {return strtolower($f) === strtolower($formName);})),
47
                "Form definition file not found or no permission to read file: '$absoluteFormFilePath'"
48
49
            );
        };
50
        if(!file_exists($absoluteFormFilePath)) {
51
            if ($keepIfNeverExported && isset($formFromDb[F_ID]) && (!isset($formFromDb[F_FILE_STATS]) || !self::isValidFileStats($formFromDb[F_FILE_STATS]))) {
52
53
54
55
                // 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.");
56
                $fileReadException();
57
            }
58
        }
59
        $fileStatsNew = self::formFileStatsJson($absoluteFormFilePath);
60
        if ($fileStatsNew === false) {
61
            self::deleteFormDB($formName, $database, "Failed to read form file stats.");
62
            $fileReadException();
63
        }
64
65

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

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

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

83
84
85
        // make sure container names are unique and non-empty
        $containerNames = [];
        foreach ($formFromFile[F_FILE_FORM_ELEMENT] as $formElementFromFile) {
86
87
            $keysNotSet = OnArray::keysNotSet([FE_CLASS, FE_NAME], $formElementFromFile);
            if (!empty($keysNotSet)) {
88
                Thrower::userFormException('Failed to import form file.', "One or more required keys are missing in FormElement definition in file: '$absoluteFormFilePath'. Missing keys: " . implode(', ', $keysNotSet));
89
            }
90
91
            if ($formElementFromFile[FE_CLASS] === FE_CLASS_CONTAINER) {
                if (in_array($formElementFromFile[FE_NAME], $containerNames)) {
92
                    Thrower::userFormException('Failed to import form file.', "Multiple formElements of class container with the same name '" . $formElementFromFile[FE_NAME] . "' in form file '$absoluteFormFilePath'");
93
94
                }
                if ($formElementFromFile[FE_NAME] == '') {
95
                    Thrower::userFormException('Failed to import form file.', "Found formElement of class container with empty name in form file '$absoluteFormFilePath'");
96
97
98
99
100
                }
                $containerNames[] = $formElementFromFile[FE_NAME];
            }
        }

101
102
103
        // 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
104

105
        // Insert new Form to DB (after filtering allowed columns and adding column 'name')
106
107
108
109
110
        $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)
111
112
        $insertValues[F_NAME] = $newFormTempName;
        $insertValues[F_FILE_STATS] = $fileStatsNew;
113
        list($sqlFormInsert, $parameterArrayFormInsert) = SqlQuery::insertRecord(TABLE_NAME_FORM, $insertValues);
114
        $formIdNew = $database->sql($sqlFormInsert, ROW_REGULAR, $parameterArrayFormInsert);
115

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

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

129
        // Update container IDs for each form element which has a container name
130
131
132
        foreach ($formFromFile[F_FILE_FORM_ELEMENT] as &$formElementFromFile) {
            if (array_key_exists(FE_FILE_CONTAINER_NAME, $formElementFromFile)) {
                $containerName = $formElementFromFile[FE_FILE_CONTAINER_NAME];
133
                if (!isset($containerIds[$containerName])) {
134
                    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'");
135
                }
136
137
                $containerId = $containerIds[$containerName];
                $feId = $formElementFromFile[FE_ID];
138
139
                list($sql, $parameterArray) = SqlQuery::updateRecord(TABLE_NAME_FORM_ELEMENT, [FE_ID_CONTAINER => $containerId], $feId);
                $database->sql($sql, ROW_REGULAR, $parameterArray);
140
            }
141
        }
142

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

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

152
        return true;
153
154
    }

155
    /**
156
157
     * 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.
158
     * If the form file path does not exist, it is created and all forms are exported.
159
     *
160
161
162
163
164
165
     * ! 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.
166
     *
167
168
     * @param string $formName
     * @param Database $database
169
     * @param int|null $formId if given, then $formName is ignored
170
171
172
173
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
174
    public static function exportForm(string $formName, Database $database, ?int $formId = null) // : void
175
    {
176
        list($formName, $formId, $formJson, $adjustContainerName, $form) = self::formToJson($formName, $database, $formId);
177

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

184
185
        // some column names where adjusted => import form
        if ($adjustContainerName) {
186
            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());
187
188
189
190
191
            self::importForm($formName, $database);

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

197
    /**
198
     * Create copy of given form file in form/_backup. If given file does not exist, do nothing.
199
200
     * New file name: <formName>.YYYMMDDhhmmss.file.json
     *
201
     * @param string $absoluteFormFilePath
202
203
     * @throws \UserFormException
     */
204
    private static function backupFormFile(string $absoluteFormFilePath)
205
    {
206
        if (file_exists($absoluteFormFilePath))
207
        {
208
209
            if (!is_readable($absoluteFormFilePath)) {
                Thrower::userFormException('Error while trying to backup form file.', "Form file is not readable: $absoluteFormFilePath");
210
211
212
            }

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

221
222
223
224
225
226
227
228
229
230
231
232
233
    /**
     * 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
     */
234
    public static function importFormRecordId(int $recordId, string $tableName, Database $database) // : ?string
235
236
237
238
239
240
241
242
243
    {
        $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);
            }
244
            self::enforceFormFileWritable($recordFormName, $database);
245
246
247
248
        }
        return $recordFormName;
    }

249
    /**
250
     * Deletes the form file for the given form.
251
     *
252
     * @param $formName
253
     * @param Database $database
254
     * @param string $logMessageReason
255
256
     * @throws \CodeException
     * @throws \DbException
257
258
     * @throws \UserFormException
     */
259
    public static function deleteFormFile($formName, Database $database, string $logMessageReason = '') // : void
260
    {
261
262
        self::enforceFormFileWritable($formName, $database);
        $pathFileName = self::formPathFileName($formName, $database);
263
        if(file_exists($pathFileName)) {
264
            self::backupFormFile($pathFileName);
265
266
267
268
269
270
271
            $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);
            }
272
            Logger::logMessage(date('Y.m.d H:i:s ') . ": Removed form file '$pathFileName'. Reason: $logMessageReason", Path::absoluteQfqLogFile());
273
        }
Marc Egger's avatar
Marc Egger committed
274
    }
275

276
    /**
277
278
279
280
281
282
     * 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
     */
283
    public static function errorHintFormImport (string $tableName = TABLE_NAME_FORM): string
284
285
286
287
288
289
290
291
292
293
    {
        $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.
Marc Egger's avatar
Marc Egger committed
294
     *
295
     * @param string $formName
296
     * @param Database $database
297
     * @return void
298
299
     * @throws \CodeException
     * @throws \DbException
300
301
     * @throws \UserFormException
     */
302
    public static function enforceFormFileWritable(string $formName, Database $database) // : void
303
    {
304
        $pathFileName = self::formPathFileName($formName, $database);
305
        HelperFile::enforce_writable_or_creatable($pathFileName);
306
307
308
309
310

    }

    /**
     * Return form name if given record is an existing Form or FormElement.
311
     * Return null otherwise.
312
313
314
315
     *
     * @param int $recordId
     * @param string $recordTable
     * @param Database $database
316
     * @return string formName|null
317
318
319
320
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
321
    public static function formNameFromFormRelatedRecord(int $recordId, string $recordTable, Database $database) // : ?string
322
323
    {
        if ($recordId === 0) {
324
            return null;
325
326
327
        }
        switch ($recordTable) {
            case TABLE_NAME_FORM:
328
329
330
                list($sql, $parameterArray) = SqlQuery::selectFormById($recordId);
                $formFromDb = $database->sql($sql, ROW_EXPECT_1,
                    $parameterArray, "Form with id '$recordId' not found. " . self::errorHintFormImport());
331
332
333
334
335
336
337
338
                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;
339
340

                // Select form name by formElement id
341
342
343
344
                $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:
345
                return null;
346
347
        }
    }
348

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

360
        // Check if no other SQL keywords are in between FROM and the table name
361
362
363
364
365
366
367
368
369
370
371
        $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;
    }

    /**
372
373
     * 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.
374
     *
375
     * @param Database $database
376
     * @param bool $enforceWritable Throw exception if one of the form files is not writable by current user.
377
     * @param bool $deleteFromDB Delete forms from DB if there are no corresponding form files. Except if the form was never exported.
378
379
     * @throws \CodeException
     * @throws \DbException
380
381
     * @throws \UserFormException
     */
382
    public static function importAllForms(Database $database, bool $enforceWritable = false, bool $deleteFromDB = false) // : void
383
    {
384
        // Import all form files
385
386
387
388
389
        $formFileNames = self::formFileNames($database);
        foreach ($formFileNames as $formFileName) {
            self::importForm($formFileName, $database);
            if ($enforceWritable) {
                self::enforceFormFileWritable($formFileName, $database);
390
            }
391
392
        }

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

403
404
405
406
407
    /**
     * Export all forms in database to form files.
     * Warning: This overwrites form files without any checks or warning. Use cautiously.
     *
     * @param Database $database
408
     * @param bool $deleteFiles Delete form files without a corresponding form record in the DB.
409
410
411
412
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
413
    public static function exportAllForms(Database $database, bool $deleteFiles = false) // : void
414
415
416
417
418
    {
        $formNamesDB = self::queryAllFormNames($database);
        foreach ($formNamesDB as $formNameDB) {
            self::exportForm($formNameDB, $database);
        }
419
420
421
422
        if ($deleteFiles) {
           $formFileNames = self::formFileNames($database);
           $filesToDelete = array_diff($formFileNames, $formNamesDB);
           foreach ($filesToDelete as $fileToDelete) {
423
               self::deleteFormFile($fileToDelete, $database, "Export all forms from database. No form with name '$fileToDelete' in database.");
424
425
           }
        }
426
427
    }

Marc Egger's avatar
Marc Egger committed
428
    /**
429
430
431
     * Insert formElement to the given form.
     * Keys removed before insert: id, formId, feIdContainer
     *
Marc Egger's avatar
Marc Egger committed
432
433
434
435
436
437
438
439
440
441
     * @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
    {
442
        // filter allowed formElement columns (remove id, formId, feIdContainer)
Marc Egger's avatar
Marc Egger committed
443
444
445
        $formElementSchema = $database->getTableDefinition(TABLE_NAME_FORM_ELEMENT);
        $formElementColumns = array_column($formElementSchema, 'Field');
        $insertValues = array_filter($values, function ($columnName) use ($formElementColumns) {
446
            return !in_array($columnName, [FE_ID, FE_FORM_ID, FE_ID_CONTAINER]) && in_array($columnName, $formElementColumns);
Marc Egger's avatar
Marc Egger committed
447
448
449
450
451
452
453
454
455
456
        }, 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;
    }

457
    /**
458
459
     * 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.
460
461
462
     *
     * @param string $formName
     * @param Database $database
463
     * @param string $logMessageReason
464
     * @param bool $keepIfNeverExported
465
466
467
468
469
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
470
    private static function deleteFormDB(string $formName, Database $database, string $logMessageReason = '', bool $keepIfNeverExported = false) // : void
471
    {
472
        $formFromDb = $database->selectFormByName($formName, [F_ID, F_FILE_STATS]);
473
474
475
476
477

        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)) {
478
            self::deleteFormDBWithId($formFromDb[F_ID], $database, $logMessageReason . " Form name '$formName'.");
479
480
481
        }
    }

482
483
484
485
486
487
488
489
490
491
492
    /**
     * 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 ) === '{';
    }

493
494
495
    /**
     * Delete form with given id and its form elements from DB
     *
496
     * @param int $formId
497
     * @param Database $database
498
     * @param string $logMessageReason
499
500
501
502
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
503
    private static function deleteFormDBWithId(int $formId, Database $database, string $logMessageReason = '') // : void
504
    {
505
        self::backupFormDb($formId, $database);
506
507
        $F_ID = F_ID; // can't use constants in strings directly
        $TABLE_NAME_FORM = TABLE_NAME_FORM; // can't use constants in strings directly
508
509
510
511
512
        $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);
513
514
    }

515
516
517
518
519
520
521
522
523
524
525
526
527
    /**
     * 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);
528
529
        $absoluteBackupFilePath = self::newBackupPathFileName($formName, 'db');
        HelperFile::file_put_contents($absoluteBackupFilePath, $formJson);
530
531
532
    }


533
534
535
    /**
     * Delete form elements with given formId from DB
     *
536
     * @param int $formId
537
     * @param Database $database
538
     * @param string $logMessageReason
539
540
541
542
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
543
    private static function deleteFormElementsDBWithFormId(int $formId, Database $database, string $logMessageReason = '') // : void
544
    {
545
546
        $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
547
548
549
550
        $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());
        }
551
    }
552
553

    /**
Marc Egger's avatar
Marc Egger committed
554
555
556
     * 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.
     *
557
558
559
560
561
     * @param string $pathFileName
     * @return false|string
     */
    private static function formFileStatsJson(string $pathFileName)
    {
562
        clearstatcache (true, $pathFileName);
563
564
565
566
567
568
569
570
571
572
573
574
575
        $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.
576
     * Create path if it doesn't exist and export all forms from DB.
577
578
     *
     * @param string $formName
579
     * @param Database $database
580
     * @return string
581
582
     * @throws \CodeException
     * @throws \DbException
583
584
     * @throws \UserFormException
     */
585
    private static function formPathFileName(string $formName, Database $database): string
586
    {
587
588
589
        // validate form name
        if (!HelperFile::isValidFileName($formName)) {
            throw new \UserFormException(json_encode([
590
591
                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 _ . -"]),
592
593
                ERROR_FORM_INVALID_NAME);
        }
594
        return self::formPath($database) . '/' . $formName . ".json";
595
596
597
    }

    /**
598
     * Return the path of the form directory relative to CWD.
599
     * Create path if it doesn't exist and export all forms from DB.
600
     *
601
     * @param Database $database
602
     * @return string
603
604
     * @throws \CodeException
     * @throws \DbException
605
     * @throws \UserFormException
606
     */
607
    private static function formPath(Database $database): string
608
    {
609
        $absoluteFormPath = Path::absoluteProject(Path::projectToForm());
610
        if (!is_dir($absoluteFormPath)) {
611
612

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

            // export all forms
622
            self::exportAllForms($database);
623
        }
624
        return $absoluteFormPath;
625
    }
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641

    /**
     * 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);
    }
642
643

    /**
644
     * Return array of form file names contained in the form path (without suffix).
645
646
     *
     * @param Database $database
647
     * @return array|false [formName]
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
     * @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);
664
            if (isset($fileInfo['extension']) && isset($fileInfo['filename']) && $fileInfo['extension'] === 'json') {
665
666
667
668
669
                $result[] = $fileInfo['filename'];
            }
            return $result;
        }, []);
    }
670
671
672
673
674

    /**
     * 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.
675
     * - 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.
676
677
678
679
680
681
     * - 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
682
     * @return array [string $formName, int $formId, string $formJson, bool ($adjustedContainerNames > 0), array $form]
683
684
685
686
687
688
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function formToJson(string $formName, Database $database, ?int $formId = null): array
    {
689
        // Get form from DB (either by id or by name)
690
691
692
693
694
        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 {
695
696
697
698
            $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.");
            }
699
700
701
702
        }

        // Remove columns: id, name, fileStats
        $formId = $form[F_ID];
Marc Egger's avatar
Marc Egger committed
703
        $formName = $form[F_NAME];
704
705
706
707
708
709
710
711
        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))

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

                // container name not unique => adjust make unique
Marc Egger's avatar
Marc Egger committed
719
                if (in_array($containerName, $result)) {
720
721
                    $containerName = $containerName . '_auto_adjust_not_unique_' . count($result);
                    $adjustedContainerNames++;
Marc Egger's avatar
Marc Egger committed
722
                }
723
724

                // container name empty => adjust make non-empty unique
Marc Egger's avatar
Marc Egger committed
725
                if ($containerName === '') {
726
727
                    $containerName = 'auto_adjust_empty_' . count($result);
                    $adjustedContainerNames++;
728
729
730
731
732
                }
                $result[$formElement[FE_ID]] = $containerName;
            }
            return $result;
        }, []); // array(id => name)
733
734

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

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

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

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

753
754
755
756
757
758
            return $formElement;
        }, $formElements);

        // add form elements and create json
        $form[F_FILE_FORM_ELEMENT] = $formElements;
        $formJson = json_encode($form, JSON_PRETTY_PRINT);
759
        return array($formName, $formId, $formJson, $adjustedContainerNames > 0, $form);
760
761
762
763
764
765
766
767
768
769
770
771
772
773
    }

    /**
     * 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
774
        $absoluteBackupPath = Path::absoluteProject(Path::projectToForm(), Path::FORM_TO_FORM_BACKUP);
775
776
        if (!is_dir($absoluteBackupPath)) {
            $success = mkdir($absoluteBackupPath, 0777, true);
777
            if ($success === false) {
778
                Thrower::userFormException('Error while trying to backup form file.', "Can't create backup path: $absoluteBackupPath");
779
780
781
            }
        }

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

        // add index to filename if backup file with current timestamp already exists
        $index = 1;
786
787
        while (file_exists($absoluteBackupFilePath)) {
            $absoluteBackupFilePath = Path::join($absoluteBackupPath, $formName . '.json.' . date('Y-m-d_H-i-s') . ".$index.$tag");
788
            $index ++;
789
            if ($index > 20) {
790
791
                Thrower::userFormException('Error while trying to backup form file.', 'Infinite loop.');
            }
792
        }
793
        return $absoluteBackupFilePath;
794
    }
Marc Egger's avatar
Marc Egger committed
795
}