xref: /webtrees/app/Services/GedcomImportService.php (revision db60fbe701448745baf2f397225debcc8f5e760d) 
1<?php
2
3/**
4 * webtrees: online genealogy
5 * Copyright (C) 2022 webtrees development team
6 * This program is free software: you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation, either version 3 of the License, or
9 * (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 * You should have received a copy of the GNU General Public License
15 * along with this program. If not, see <https://www.gnu.org/licenses/>.
16 */
17
18declare(strict_types=1);
19
20namespace Fisharebest\Webtrees\Services;
21
22use Fisharebest\Webtrees\Date;
23use Fisharebest\Webtrees\Elements\UnknownElement;
24use Fisharebest\Webtrees\Exceptions\GedcomErrorException;
25use Fisharebest\Webtrees\Family;
26use Fisharebest\Webtrees\Gedcom;
27use Fisharebest\Webtrees\Header;
28use Fisharebest\Webtrees\Individual;
29use Fisharebest\Webtrees\Location;
30use Fisharebest\Webtrees\Media;
31use Fisharebest\Webtrees\Note;
32use Fisharebest\Webtrees\Place;
33use Fisharebest\Webtrees\PlaceLocation;
34use Fisharebest\Webtrees\Registry;
35use Fisharebest\Webtrees\Repository;
36use Fisharebest\Webtrees\Soundex;
37use Fisharebest\Webtrees\Source;
38use Fisharebest\Webtrees\Submission;
39use Fisharebest\Webtrees\Submitter;
40use Fisharebest\Webtrees\Tree;
41use Illuminate\Database\Capsule\Manager as DB;
42use Illuminate\Database\Query\JoinClause;
43
44use function app;
45use function array_chunk;
46use function array_intersect_key;
47use function array_map;
48use function array_unique;
49use function assert;
50use function date;
51use function explode;
52use function max;
53use function mb_substr;
54use function preg_match;
55use function preg_match_all;
56use function preg_replace;
57use function round;
58use function str_contains;
59use function str_replace;
60use function str_starts_with;
61use function strlen;
62use function strtolower;
63use function strtoupper;
64use function strtr;
65use function substr;
66use function trim;
67
68use const PREG_SET_ORDER;
69
70/**
71 * Class GedcomImportService - import GEDCOM data
72 */
73class GedcomImportService
74{
75    /**
76     * Tidy up a gedcom record on import, so that we can access it consistently/efficiently.
77     *
78     * @param string $rec
79     * @param Tree   $tree
80     *
81     * @return string
82     */
83    private function reformatRecord(string $rec, Tree $tree): string
84    {
85        $gedcom_service = app(GedcomService::class);
86        assert($gedcom_service instanceof GedcomService);
87
88        // Strip out mac/msdos line endings
89        $rec = preg_replace("/[\r\n]+/", "\n", $rec);
90
91        // Extract lines from the record; lines consist of: level + optional xref + tag + optional data
92        $num_matches = preg_match_all('/^[ \t]*(\d+)[ \t]*(@[^@]*@)?[ \t]*(\w+)[ \t]?(.*)$/m', $rec, $matches, PREG_SET_ORDER);
93
94        // Process the record line-by-line
95        $newrec = '';
96        foreach ($matches as $n => $match) {
97            [, $level, $xref, $tag, $data] = $match;
98
99            $tag = $gedcom_service->canonicalTag($tag);
100
101            switch ($tag) {
102                case 'AFN':
103                    // AFN values are upper case
104                    $data = strtoupper($data);
105                    break;
106                case 'DATE':
107                    // Preserve text from INT dates
108                    if (str_contains($data, '(')) {
109                        [$date, $text] = explode('(', $data, 2);
110                        $text = ' (' . $text;
111                    } else {
112                        $date = $data;
113                        $text = '';
114                    }
115                    // Capitals
116                    $date = strtoupper($date);
117                    // Temporarily add leading/trailing spaces, to allow efficient matching below
118                    $date = ' ' . $date . ' ';
119                    // Ensure space digits and letters
120                    $date = preg_replace('/([A-Z])(\d)/', '$1 $2', $date);
121                    $date = preg_replace('/(\d)([A-Z])/', '$1 $2', $date);
122                    // Ensure space before/after calendar escapes
123                    $date = preg_replace('/@#[^@]+@/', ' $0 ', $date);
124                    // "BET." => "BET"
125                    $date = preg_replace('/(\w\w)\./', '$1', $date);
126                    // "CIR" => "ABT"
127                    $date = str_replace(' CIR ', ' ABT ', $date);
128                    $date = str_replace(' APX ', ' ABT ', $date);
129                    // B.C. => BC (temporarily, to allow easier handling of ".")
130                    $date = str_replace(' B.C. ', ' BC ', $date);
131                    // TMG uses "EITHER X OR Y"
132                    $date = preg_replace('/^ EITHER (.+) OR (.+)/', ' BET $1 AND $2', $date);
133                    // "BET X - Y " => "BET X AND Y"
134                    $date = preg_replace('/^(.* BET .+) - (.+)/', '$1 AND $2', $date);
135                    $date = preg_replace('/^(.* FROM .+) - (.+)/', '$1 TO $2', $date);
136                    // "@#ESC@ FROM X TO Y" => "FROM @#ESC@ X TO @#ESC@ Y"
137                    $date = preg_replace('/^ +(@#[^@]+@) +FROM +(.+) +TO +(.+)/', ' FROM $1 $2 TO $1 $3', $date);
138                    $date = preg_replace('/^ +(@#[^@]+@) +BET +(.+) +AND +(.+)/', ' BET $1 $2 AND $1 $3', $date);
139                    // "@#ESC@ AFT X" => "AFT @#ESC@ X"
140                    $date = preg_replace('/^ +(@#[^@]+@) +(FROM|BET|TO|AND|BEF|AFT|CAL|EST|INT|ABT) +(.+)/', ' $2 $1 $3', $date);
141                    // Ignore any remaining punctuation, e.g. "14-MAY, 1900" => "14 MAY 1900"
142                    // (don't change "/" - it is used in NS/OS dates)
143                    $date = preg_replace('/[.,:;-]/', ' ', $date);
144                    // BC => B.C.
145                    $date = str_replace(' BC ', ' B.C. ', $date);
146                    // Append the "INT" text
147                    $data = $date . $text;
148                    break;
149                case '_FILE':
150                    $tag = 'FILE';
151                    break;
152                case 'FORM':
153                    // Consistent commas
154                    $data = preg_replace('/ *, */', ', ', $data);
155                    break;
156                case 'HEAD':
157                    // HEAD records don't have an XREF or DATA
158                    if ($level === '0') {
159                        $xref = '';
160                        $data = '';
161                    }
162                    break;
163                case 'NAME':
164                    // Tidy up non-printing characters
165                    $data = preg_replace('/  +/', ' ', trim($data));
166                    break;
167                case 'PEDI':
168                    // PEDI values are lower case
169                    $data = strtolower($data);
170                    break;
171                case 'PLAC':
172                    // Consistent commas
173                    $data = preg_replace('/ *[,，،] */u', ', ', $data);
174                    // The Master Genealogist stores LAT/LONG data in the PLAC field, e.g. Pennsylvania, USA, 395945N0751013W
175                    if (preg_match('/(.*), (\d\d)(\d\d)(\d\d)([NS])(\d\d\d)(\d\d)(\d\d)([EW])$/', $data, $match)) {
176                        $data =
177                            $match[1] . "\n" .
178                            ($level + 1) . " MAP\n" .
179                            ($level + 2) . ' LATI ' . ($match[5] . round($match[2] + ($match[3] / 60) + ($match[4] / 3600), 4)) . "\n" .
180                            ($level + 2) . ' LONG ' . ($match[9] . round($match[6] + ($match[7] / 60) + ($match[8] / 3600), 4));
181                    }
182                    break;
183                case 'RESN':
184                    // RESN values are lower case (confidential, privacy, locked, none)
185                    $data = strtolower($data);
186                    if ($data === 'invisible') {
187                        $data = 'confidential'; // From old versions of Legacy.
188                    }
189                    break;
190                case 'SEX':
191                    $data = strtoupper($data);
192                    break;
193                case 'STAT':
194                    if ($data === 'CANCELLED') {
195                        // PhpGedView mis-spells this tag - correct it.
196                        $data = 'CANCELED';
197                    }
198                    break;
199                case 'TEMP':
200                    // Temple codes are upper case
201                    $data = strtoupper($data);
202                    break;
203                case 'TRLR':
204                    // TRLR records don't have an XREF or DATA
205                    if ($level === '0') {
206                        $xref = '';
207                        $data = '';
208                    }
209                    break;
210            }
211            // Suppress "Y", for facts/events with a DATE or PLAC
212            if ($data === 'y') {
213                $data = 'Y';
214            }
215            if ($level === '1' && $data === 'Y') {
216                for ($i = $n + 1; $i < $num_matches - 1 && $matches[$i][1] !== '1'; ++$i) {
217                    if ($matches[$i][3] === 'DATE' || $matches[$i][3] === 'PLAC') {
218                        $data = '';
219                        break;
220                    }
221                }
222            }
223            // Reassemble components back into a single line
224            switch ($tag) {
225                default:
226                    // Remove tabs and multiple/leading/trailing spaces
227                    $data = strtr($data, ["\t" => ' ']);
228                    $data = trim($data, ' ');
229                    while (str_contains($data, '  ')) {
230                        $data = strtr($data, ['  ' => ' ']);
231                    }
232                    $newrec .= ($newrec ? "\n" : '') . $level . ' ' . ($level === '0' && $xref ? $xref . ' ' : '') . $tag . ($data === '' && $tag !== 'NOTE' ? '' : ' ' . $data);
233                    break;
234                case 'NOTE':
235                case 'TEXT':
236                case 'DATA':
237                case 'CONT':
238                    $newrec .= ($newrec ? "\n" : '') . $level . ' ' . ($level === '0' && $xref ? $xref . ' ' : '') . $tag . ($data === '' && $tag !== 'NOTE' ? '' : ' ' . $data);
239                    break;
240                case 'FILE':
241                    // Strip off the user-defined path prefix
242                    $GEDCOM_MEDIA_PATH = $tree->getPreference('GEDCOM_MEDIA_PATH');
243                    if ($GEDCOM_MEDIA_PATH !== '' && str_starts_with($data, $GEDCOM_MEDIA_PATH)) {
244                        $data = substr($data, strlen($GEDCOM_MEDIA_PATH));
245                    }
246                    // convert backslashes in filenames to forward slashes
247                    $data = preg_replace("/\\\\/", '/', $data);
248
249                    $newrec .= ($newrec ? "\n" : '') . $level . ' ' . ($level === '0' && $xref ? $xref . ' ' : '') . $tag . ($data === '' && $tag !== 'NOTE' ? '' : ' ' . $data);
250                    break;
251                case 'CONC':
252                    // Merge CONC lines, to simplify access later on.
253                    $newrec .= ($tree->getPreference('WORD_WRAPPED_NOTES') ? ' ' : '') . $data;
254                    break;
255            }
256        }
257
258        return $newrec;
259    }
260
261    /**
262     * import record into database
263     * this function will parse the given gedcom record and add it to the database
264     *
265     * @param string $gedrec the raw gedcom record to parse
266     * @param Tree   $tree   import the record into this tree
267     * @param bool   $update whether this is an updated record that has been accepted
268     *
269     * @return void
270     * @throws GedcomErrorException
271     */
272    public function importRecord(string $gedrec, Tree $tree, bool $update): void
273    {
274        $tree_id = $tree->id();
275
276        // Escaped @ signs (only if importing from file)
277        if (!$update) {
278            $gedrec = str_replace('@@', '@', $gedrec);
279        }
280
281        // Standardise gedcom format
282        $gedrec = $this->reformatRecord($gedrec, $tree);
283
284        // import different types of records
285        if (preg_match('/^0 @(' . Gedcom::REGEX_XREF . ')@ (' . Gedcom::REGEX_TAG . ')/', $gedrec, $match)) {
286            [, $xref, $type] = $match;
287        } elseif (str_starts_with($gedrec, '0 HEAD')) {
288            $type = 'HEAD';
289            $xref = 'HEAD'; // For records without an XREF, use the type as a pseudo XREF.
290        } elseif (str_starts_with($gedrec, '0 TRLR')) {
291            $tree->setPreference('imported', '1');
292            $type = 'TRLR';
293            $xref = 'TRLR'; // For records without an XREF, use the type as a pseudo XREF.
294        } elseif (str_starts_with($gedrec, '0 _PLAC_DEFN')) {
295            $this->importLegacyPlacDefn($gedrec);
296
297            return;
298        } elseif (str_starts_with($gedrec, '0 _PLAC ')) {
299            $this->importTNGPlac($gedrec);
300
301            return;
302        } elseif (str_starts_with($gedrec, '0 _EVDEF')) {
303            // Created by RootsMagic.  We cannot process these records without an XREF.
304            return;
305        } elseif (str_starts_with($gedrec, '0 _EVENT_DEFN')) {
306            // Created by PAF and Legacy.  We cannot process these records without an XREF.
307            return;
308        } elseif (str_starts_with($gedrec, '0 PEDIGREELINK')) {
309            // Created by GenoPro.  We cannot process these records without an XREF.
310            return;
311        } elseif (str_starts_with($gedrec, '0 GLOBAL')) {
312            // Created by GenoPro.  We cannot process these records without an XREF.
313            return;
314        } elseif (str_starts_with($gedrec, '0 GENOMAP')) {
315            // Created by GenoPro.  We cannot process these records without an XREF.
316            return;
317        } elseif (str_starts_with($gedrec, '0 EMOTIONALRELATIONSHIP')) {
318            // Created by GenoPro.  We cannot process these records without an XREF.
319            return;
320        } elseif (str_starts_with($gedrec, '0 SOCIALRELATIONSHIP')) {
321            // Created by GenoPro.  We cannot process these records without an XREF.
322            return;
323        } elseif (str_starts_with($gedrec, '0 LABEL')) {
324            // Created by GenoPro.  We cannot process these records without an XREF.
325            return;
326        } else {
327            throw new GedcomErrorException($gedrec);
328        }
329
330        // Add a _UID
331        if ($tree->getPreference('GENERATE_UIDS') === '1' && !str_contains($gedrec, "\n1 _UID ")) {
332            $element = Registry::elementFactory()->make($type . ':_UID');
333            if (!$element instanceof UnknownElement) {
334                $gedrec .= "\n1 _UID " . $element->default($tree);
335            }
336        }
337
338        // If the user has downloaded their GEDCOM data (containing media objects) and edited it
339        // using an application which does not support (and deletes) media objects, then add them
340        // back in.
341        if ($tree->getPreference('keep_media')) {
342            $old_linked_media = DB::table('link')
343                ->where('l_from', '=', $xref)
344                ->where('l_file', '=', $tree_id)
345                ->where('l_type', '=', 'OBJE')
346                ->pluck('l_to');
347
348            // Delete these links - so that we do not insert them again in updateLinks()
349            DB::table('link')
350                ->where('l_from', '=', $xref)
351                ->where('l_file', '=', $tree_id)
352                ->where('l_type', '=', 'OBJE')
353                ->delete();
354
355            foreach ($old_linked_media as $media_id) {
356                $gedrec .= "\n1 OBJE @" . $media_id . '@';
357            }
358        }
359
360        // Convert inline media into media objects
361        $gedrec = $this->convertInlineMedia($tree, $gedrec);
362
363        switch ($type) {
364            case Individual::RECORD_TYPE:
365                $record = Registry::individualFactory()->new($xref, $gedrec, null, $tree);
366
367                if (preg_match('/\n1 RIN (.+)/', $gedrec, $match)) {
368                    $rin = $match[1];
369                } else {
370                    $rin = $xref;
371                }
372
373                // The database can only store MFU, and many of the stats queries assume this.
374                $sex = $record->sex();
375                $sex = $sex === 'M' || $sex === 'F' ? $sex : 'U';
376
377                DB::table('individuals')->insert([
378                    'i_id'     => $xref,
379                    'i_file'   => $tree_id,
380                    'i_rin'    => $rin,
381                    'i_sex'    => $sex,
382                    'i_gedcom' => $gedrec,
383                ]);
384
385                // Update the cross-reference/index tables.
386                $this->updatePlaces($xref, $tree, $gedrec);
387                $this->updateDates($xref, $tree_id, $gedrec);
388                $this->updateNames($xref, $tree_id, $record);
389                break;
390
391            case Family::RECORD_TYPE:
392                if (preg_match('/\n1 HUSB @(' . Gedcom::REGEX_XREF . ')@/', $gedrec, $match)) {
393                    $husb = $match[1];
394                } else {
395                    $husb = '';
396                }
397                if (preg_match('/\n1 WIFE @(' . Gedcom::REGEX_XREF . ')@/', $gedrec, $match)) {
398                    $wife = $match[1];
399                } else {
400                    $wife = '';
401                }
402                $nchi = preg_match_all('/\n1 CHIL @(' . Gedcom::REGEX_XREF . ')@/', $gedrec, $match);
403                if (preg_match('/\n1 NCHI (\d+)/', $gedrec, $match)) {
404                    $nchi = max($nchi, $match[1]);
405                }
406
407                DB::table('families')->insert([
408                    'f_id'      => $xref,
409                    'f_file'    => $tree_id,
410                    'f_husb'    => $husb,
411                    'f_wife'    => $wife,
412                    'f_gedcom'  => $gedrec,
413                    'f_numchil' => $nchi,
414                ]);
415
416                // Update the cross-reference/index tables.
417                $this->updatePlaces($xref, $tree, $gedrec);
418                $this->updateDates($xref, $tree_id, $gedrec);
419                break;
420
421            case Source::RECORD_TYPE:
422                if (preg_match('/\n1 TITL (.+)/', $gedrec, $match)) {
423                    $name = $match[1];
424                } elseif (preg_match('/\n1 ABBR (.+)/', $gedrec, $match)) {
425                    $name = $match[1];
426                } else {
427                    $name = $xref;
428                }
429
430                DB::table('sources')->insert([
431                    's_id'     => $xref,
432                    's_file'   => $tree_id,
433                    's_name'   => mb_substr($name, 0, 255),
434                    's_gedcom' => $gedrec,
435                ]);
436                break;
437
438            case Repository::RECORD_TYPE:
439            case Note::RECORD_TYPE:
440            case Submission::RECORD_TYPE:
441            case Submitter::RECORD_TYPE:
442            case Location::RECORD_TYPE:
443                DB::table('other')->insert([
444                    'o_id'     => $xref,
445                    'o_file'   => $tree_id,
446                    'o_type'   => $type,
447                    'o_gedcom' => $gedrec,
448                ]);
449                break;
450
451            case Header::RECORD_TYPE:
452                // Force HEAD records to have a creation date.
453                if (!str_contains($gedrec, "\n1 DATE ")) {
454                    $today = strtoupper(date('d M Y'));
455                    $gedrec .= "\n1 DATE " . $today;
456                }
457
458                DB::table('other')->insert([
459                    'o_id'     => $xref,
460                    'o_file'   => $tree_id,
461                    'o_type'   => Header::RECORD_TYPE,
462                    'o_gedcom' => $gedrec,
463                ]);
464                break;
465
466
467            case Media::RECORD_TYPE:
468                $record = Registry::mediaFactory()->new($xref, $gedrec, null, $tree);
469
470                DB::table('media')->insert([
471                    'm_id'     => $xref,
472                    'm_file'   => $tree_id,
473                    'm_gedcom' => $gedrec,
474                ]);
475
476                foreach ($record->mediaFiles() as $media_file) {
477                    DB::table('media_file')->insert([
478                        'm_id'                 => $xref,
479                        'm_file'               => $tree_id,
480                        'multimedia_file_refn' => mb_substr($media_file->filename(), 0, 248),
481                        'multimedia_format'    => mb_substr($media_file->format(), 0, 4),
482                        'source_media_type'    => mb_substr($media_file->type(), 0, 15),
483                        'descriptive_title'    => mb_substr($media_file->title(), 0, 248),
484                    ]);
485                }
486                break;
487
488            default: // Custom record types.
489                DB::table('other')->insert([
490                    'o_id'     => $xref,
491                    'o_file'   => $tree_id,
492                    'o_type'   => mb_substr($type, 0, 15),
493                    'o_gedcom' => $gedrec,
494                ]);
495                break;
496        }
497
498        // Update the cross-reference/index tables.
499        $this->updateLinks($xref, $tree_id, $gedrec);
500    }
501
502    /**
503     * Legacy Family Tree software generates _PLAC_DEFN records containing LAT/LONG values
504     *
505     * @param string $gedcom
506     */
507    private function importLegacyPlacDefn(string $gedcom): void
508    {
509        $gedcom_service = new GedcomService();
510
511        if (preg_match('/\n1 PLAC (.+)/', $gedcom, $match)) {
512            $place_name = $match[1];
513        } else {
514            return;
515        }
516
517        if (preg_match('/\n3 LATI ([NS].+)/', $gedcom, $match)) {
518            $latitude = $gedcom_service->readLatitude($match[1]);
519        } else {
520            return;
521        }
522
523        if (preg_match('/\n3 LONG ([EW].+)/', $gedcom, $match)) {
524            $longitude = $gedcom_service->readLongitude($match[1]);
525        } else {
526            return;
527        }
528
529        $location = new PlaceLocation($place_name);
530
531        if ($location->latitude() === null && $location->longitude() === null) {
532            DB::table('place_location')
533                ->where('id', '=', $location->id())
534                ->update([
535                    'latitude'  => $latitude,
536                    'longitude' => $longitude,
537                ]);
538        }
539    }
540
541    /**
542     * Legacy Family Tree software generates _PLAC records containing LAT/LONG values
543     *
544     * @param string $gedcom
545     */
546    private function importTNGPlac(string $gedcom): void
547    {
548        if (preg_match('/^0 _PLAC (.+)/', $gedcom, $match)) {
549            $place_name = $match[1];
550        } else {
551            return;
552        }
553
554        if (preg_match('/\n2 LATI (.+)/', $gedcom, $match)) {
555            $latitude = (float) $match[1];
556        } else {
557            return;
558        }
559
560        if (preg_match('/\n2 LONG (.+)/', $gedcom, $match)) {
561            $longitude = (float) $match[1];
562        } else {
563            return;
564        }
565
566        $location = new PlaceLocation($place_name);
567
568        if ($location->latitude() === null && $location->longitude() === null) {
569            DB::table('place_location')
570                ->where('id', '=', $location->id())
571                ->update([
572                    'latitude'  => $latitude,
573                    'longitude' => $longitude,
574                ]);
575        }
576    }
577
578    /**
579     * Extract all level 2 places from the given record and insert them into the places table
580     *
581     * @param string $xref
582     * @param Tree   $tree
583     * @param string $gedrec
584     *
585     * @return void
586     */
587    public function updatePlaces(string $xref, Tree $tree, string $gedrec): void
588    {
589        // Insert all new rows together
590        $rows = [];
591
592        preg_match_all('/\n2 PLAC (.+)/', $gedrec, $matches);
593
594        $places = array_unique($matches[1]);
595
596        foreach ($places as $place_name) {
597            $place = new Place($place_name, $tree);
598
599            // Calling Place::id() will create the entry in the database, if it doesn't already exist.
600            while ($place->id() !== 0) {
601                $rows[] = [
602                    'pl_p_id' => $place->id(),
603                    'pl_gid'  => $xref,
604                    'pl_file' => $tree->id(),
605                ];
606
607                $place = $place->parent();
608            }
609        }
610
611        // array_unique doesn't work with arrays of arrays
612        $rows = array_intersect_key($rows, array_unique(array_map('serialize', $rows)));
613
614        // PDO has a limit of 65535 placeholders, and each row requires 3 placeholders.
615        foreach (array_chunk($rows, 20000) as $chunk) {
616            DB::table('placelinks')->insert($chunk);
617        }
618    }
619
620    /**
621     * Extract all the dates from the given record and insert them into the database.
622     *
623     * @param string $xref
624     * @param int    $ged_id
625     * @param string $gedrec
626     *
627     * @return void
628     */
629    private function updateDates(string $xref, int $ged_id, string $gedrec): void
630    {
631        // Insert all new rows together
632        $rows = [];
633
634        preg_match_all("/\n1 (\w+).*(?:\n[2-9].*)*\n2 DATE (.+)(?:\n[2-9].*)*/", $gedrec, $matches, PREG_SET_ORDER);
635
636        foreach ($matches as $match) {
637            $fact = $match[1];
638            $date = new Date($match[2]);
639            $rows[] = [
640                'd_day'        => $date->minimumDate()->day,
641                'd_month'      => $date->minimumDate()->format('%O'),
642                'd_mon'        => $date->minimumDate()->month,
643                'd_year'       => $date->minimumDate()->year,
644                'd_julianday1' => $date->minimumDate()->minimumJulianDay(),
645                'd_julianday2' => $date->minimumDate()->maximumJulianDay(),
646                'd_fact'       => $fact,
647                'd_gid'        => $xref,
648                'd_file'       => $ged_id,
649                'd_type'       => $date->minimumDate()->format('%@'),
650            ];
651
652            $rows[] = [
653                'd_day'        => $date->maximumDate()->day,
654                'd_month'      => $date->maximumDate()->format('%O'),
655                'd_mon'        => $date->maximumDate()->month,
656                'd_year'       => $date->maximumDate()->year,
657                'd_julianday1' => $date->maximumDate()->minimumJulianDay(),
658                'd_julianday2' => $date->maximumDate()->maximumJulianDay(),
659                'd_fact'       => $fact,
660                'd_gid'        => $xref,
661                'd_file'       => $ged_id,
662                'd_type'       => $date->minimumDate()->format('%@'),
663            ];
664        }
665
666        // array_unique doesn't work with arrays of arrays
667        $rows = array_intersect_key($rows, array_unique(array_map('serialize', $rows)));
668
669        DB::table('dates')->insert($rows);
670    }
671
672    /**
673     * Extract all the links from the given record and insert them into the database
674     *
675     * @param string $xref
676     * @param int    $ged_id
677     * @param string $gedrec
678     *
679     * @return void
680     */
681    private function updateLinks(string $xref, int $ged_id, string $gedrec): void
682    {
683        // Insert all new rows together
684        $rows = [];
685
686        preg_match_all('/\n\d+ (' . Gedcom::REGEX_TAG . ') @(' . Gedcom::REGEX_XREF . ')@/', $gedrec, $matches, PREG_SET_ORDER);
687
688        foreach ($matches as $match) {
689            // Some applications (e.g. GenoPro) create links longer than 15 characters.
690            $link = mb_substr($match[1], 0, 15);
691
692            // Take care of "duplicates" that differ on case/collation, e.g. "SOUR @S1@" and "SOUR @s1@"
693            $rows[$link . strtoupper($match[2])] = [
694                'l_from' => $xref,
695                'l_to'   => $match[2],
696                'l_type' => $link,
697                'l_file' => $ged_id,
698            ];
699        }
700
701        DB::table('link')->insert($rows);
702    }
703
704    /**
705     * Extract all the names from the given record and insert them into the database.
706     *
707     * @param string     $xref
708     * @param int        $ged_id
709     * @param Individual $record
710     *
711     * @return void
712     */
713    private function updateNames(string $xref, int $ged_id, Individual $record): void
714    {
715        // Insert all new rows together
716        $rows = [];
717
718        foreach ($record->getAllNames() as $n => $name) {
719            if ($name['givn'] === Individual::PRAENOMEN_NESCIO) {
720                $soundex_givn_std = null;
721                $soundex_givn_dm  = null;
722            } else {
723                $soundex_givn_std = Soundex::russell($name['givn']);
724                $soundex_givn_dm  = Soundex::daitchMokotoff($name['givn']);
725            }
726
727            if ($name['surn'] === Individual::NOMEN_NESCIO) {
728                $soundex_surn_std = null;
729                $soundex_surn_dm  = null;
730            } else {
731                $soundex_surn_std = Soundex::russell($name['surname']);
732                $soundex_surn_dm  = Soundex::daitchMokotoff($name['surname']);
733            }
734
735            $rows[] = [
736                'n_file'             => $ged_id,
737                'n_id'               => $xref,
738                'n_num'              => $n,
739                'n_type'             => $name['type'],
740                'n_sort'             => mb_substr($name['sort'], 0, 255),
741                'n_full'             => mb_substr($name['fullNN'], 0, 255),
742                'n_surname'          => mb_substr($name['surname'], 0, 255),
743                'n_surn'             => mb_substr($name['surn'], 0, 255),
744                'n_givn'             => mb_substr($name['givn'], 0, 255),
745                'n_soundex_givn_std' => $soundex_givn_std,
746                'n_soundex_surn_std' => $soundex_surn_std,
747                'n_soundex_givn_dm'  => $soundex_givn_dm,
748                'n_soundex_surn_dm'  => $soundex_surn_dm,
749            ];
750        }
751
752        DB::table('name')->insert($rows);
753    }
754
755    /**
756     * Extract inline media data, and convert to media objects.
757     *
758     * @param Tree   $tree
759     * @param string $gedcom
760     *
761     * @return string
762     */
763    private function convertInlineMedia(Tree $tree, string $gedcom): string
764    {
765        while (preg_match('/\n1 OBJE(?:\n[2-9].+)+/', $gedcom, $match)) {
766            $xref   = $this->createMediaObject($match[0], $tree);
767            $gedcom = strtr($gedcom, [$match[0] =>  "\n1 OBJE @" . $xref . '@']);
768        }
769        while (preg_match('/\n2 OBJE(?:\n[3-9].+)+/', $gedcom, $match)) {
770            $xref   = $this->createMediaObject($match[0], $tree);
771            $gedcom = strtr($gedcom, [$match[0] =>  "\n2 OBJE @" . $xref . '@']);
772        }
773        while (preg_match('/\n3 OBJE(?:\n[4-9].+)+/', $gedcom, $match)) {
774            $xref   = $this->createMediaObject($match[0], $tree);
775            $gedcom = strtr($gedcom, [$match[0] =>  "\n3 OBJE @" . $xref . '@']);
776        }
777
778        return $gedcom;
779    }
780
781    /**
782     * Create a new media object, from inline media data.
783     *
784     * GEDCOM 5.5.1 specifies: +1 FILE / +2 FORM / +3 MEDI / +1 TITL
785     * GEDCOM 5.5 specifies: +1 FILE / +1 FORM / +1 TITL
786     * GEDCOM 5.5.1 says that GEDCOM 5.5 specifies:  +1 FILE / +1 FORM / +2 MEDI
787     *
788     * Legacy generates: +1 FORM / +1 FILE / +1 TITL / +1 _SCBK / +1 _PRIM / +1 _TYPE / +1 NOTE
789     * RootsMagic generates: +1 FILE / +1 FORM / +1 TITL
790     *
791     * @param string $gedcom
792     * @param Tree   $tree
793     *
794     * @return string
795     */
796    private function createMediaObject(string $gedcom, Tree $tree): string
797    {
798        preg_match('/\n\d FILE (.+)/', $gedcom, $match);
799        $file = $match[1] ?? '';
800
801        preg_match('/\n\d TITL (.+)/', $gedcom, $match);
802        $title = $match[1] ?? '';
803
804        preg_match('/\n\d FORM (.+)/', $gedcom, $match);
805        $format = $match[1] ?? '';
806
807        preg_match('/\n\d MEDI (.+)/', $gedcom, $match);
808        $media = $match[1] ?? '';
809
810        preg_match('/\n\d _SCBK (.+)/', $gedcom, $match);
811        $scrapbook = $match[1] ?? '';
812
813        preg_match('/\n\d _PRIM (.+)/', $gedcom, $match);
814        $primary = $match[1] ?? '';
815
816        preg_match('/\n\d _TYPE (.+)/', $gedcom, $match);
817        if ($media === '') {
818            // Legacy uses _TYPE instead of MEDI
819            $media = $match[1] ?? '';
820            $type  = '';
821        } else {
822            $type = $match[1] ?? '';
823        }
824
825        preg_match_all('/\n\d NOTE (.+(?:\n\d CONT.*)*)/', $gedcom, $matches);
826        $notes = $matches[1] ?? [];
827
828        // Have we already created a media object with the same title/filename?
829        $xref = DB::table('media_file')
830            ->where('m_file', '=', $tree->id())
831            ->where('descriptive_title', '=', mb_substr($title, 0, 248))
832            ->where('multimedia_file_refn', '=', mb_substr($file, 0, 248))
833            ->value('m_id');
834
835        if ($xref === null) {
836            $xref = Registry::xrefFactory()->make(Media::RECORD_TYPE);
837
838            // convert to a media-object
839            $gedcom = '0 @' . $xref . "@ OBJE\n1 FILE " . $file;
840
841            if ($format !== '') {
842                $gedcom .= "\n2 FORM " . $format;
843
844                if ($media !== '') {
845                    $gedcom .= "\n3 TYPE " . $media;
846                }
847            }
848
849            if ($title !== '') {
850                $gedcom .= "\n3 TITL " . $title;
851            }
852
853            if ($scrapbook !== '') {
854                $gedcom .= "\n1 _SCBK " . $scrapbook;
855            }
856
857            if ($primary !== '') {
858                $gedcom .= "\n1 _PRIM " . $primary;
859            }
860
861            if ($type !== '') {
862                $gedcom .= "\n1 _TYPE " . $type;
863            }
864
865            foreach ($notes as $note) {
866                $gedcom .= "\n1 NOTE " . strtr($note, ["\n3" => "\n2", "\n4" => "\n2", "\n5" => "\n2"]);
867            }
868
869            DB::table('media')->insert([
870                'm_id'     => $xref,
871                'm_file'   => $tree->id(),
872                'm_gedcom' => $gedcom,
873            ]);
874
875            DB::table('media_file')->insert([
876                'm_id'                 => $xref,
877                'm_file'               => $tree->id(),
878                'multimedia_file_refn' => mb_substr($file, 0, 248),
879                'multimedia_format'    => mb_substr($format, 0, 4),
880                'source_media_type'    => mb_substr($media, 0, 15),
881                'descriptive_title'    => mb_substr($title, 0, 248),
882            ]);
883        }
884
885        return $xref;
886    }
887
888    /**
889     * update a record in the database
890     *
891     * @param string $gedrec
892     * @param Tree   $tree
893     * @param bool   $delete
894     *
895     * @return void
896     * @throws GedcomErrorException
897     */
898    public function updateRecord(string $gedrec, Tree $tree, bool $delete): void
899    {
900        if (preg_match('/^0 @(' . Gedcom::REGEX_XREF . ')@ (' . Gedcom::REGEX_TAG . ')/', $gedrec, $match)) {
901            [, $gid, $type] = $match;
902        } elseif (preg_match('/^0 (HEAD)(?:\n|$)/', $gedrec, $match)) {
903            // The HEAD record has no XREF.  Any others?
904            $gid  = $match[1];
905            $type = $match[1];
906        } else {
907            throw new GedcomErrorException($gedrec);
908        }
909
910        // Place links
911        DB::table('placelinks')
912            ->where('pl_gid', '=', $gid)
913            ->where('pl_file', '=', $tree->id())
914            ->delete();
915
916        // Orphaned places.  If we're deleting  "Westminster, London, England",
917        // then we may also need to delete "London, England" and "England".
918        do {
919            $affected = DB::table('places')
920                ->leftJoin('placelinks', function (JoinClause $join): void {
921                    $join
922                        ->on('p_id', '=', 'pl_p_id')
923                        ->on('p_file', '=', 'pl_file');
924                })
925                ->whereNull('pl_p_id')
926                ->delete();
927        } while ($affected > 0);
928
929        DB::table('dates')
930            ->where('d_gid', '=', $gid)
931            ->where('d_file', '=', $tree->id())
932            ->delete();
933
934        DB::table('name')
935            ->where('n_id', '=', $gid)
936            ->where('n_file', '=', $tree->id())
937            ->delete();
938
939        DB::table('link')
940            ->where('l_from', '=', $gid)
941            ->where('l_file', '=', $tree->id())
942            ->delete();
943
944        switch ($type) {
945            case Individual::RECORD_TYPE:
946                DB::table('individuals')
947                    ->where('i_id', '=', $gid)
948                    ->where('i_file', '=', $tree->id())
949                    ->delete();
950                break;
951
952            case Family::RECORD_TYPE:
953                DB::table('families')
954                    ->where('f_id', '=', $gid)
955                    ->where('f_file', '=', $tree->id())
956                    ->delete();
957                break;
958
959            case Source::RECORD_TYPE:
960                DB::table('sources')
961                    ->where('s_id', '=', $gid)
962                    ->where('s_file', '=', $tree->id())
963                    ->delete();
964                break;
965
966            case Media::RECORD_TYPE:
967                DB::table('media_file')
968                    ->where('m_id', '=', $gid)
969                    ->where('m_file', '=', $tree->id())
970                    ->delete();
971
972                DB::table('media')
973                    ->where('m_id', '=', $gid)
974                    ->where('m_file', '=', $tree->id())
975                    ->delete();
976                break;
977
978            default:
979                DB::table('other')
980                    ->where('o_id', '=', $gid)
981                    ->where('o_file', '=', $tree->id())
982                    ->delete();
983                break;
984        }
985
986        if (!$delete) {
987            $this->importRecord($gedrec, $tree, true);
988        }
989    }
990}
991