xref: /webtrees/app/Services/GedcomImportService.php (revision 27021e098ccbe20b6939efbf1d038db302b35d0f) 
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                DB::table('individuals')->insert([
374                    'i_id'     => $xref,
375                    'i_file'   => $tree_id,
376                    'i_rin'    => $rin,
377                    'i_sex'    => $record->sex(),
378                    'i_gedcom' => $gedrec,
379                ]);
380
381                // Update the cross-reference/index tables.
382                $this->updatePlaces($xref, $tree, $gedrec);
383                $this->updateDates($xref, $tree_id, $gedrec);
384                $this->updateNames($xref, $tree_id, $record);
385                break;
386
387            case Family::RECORD_TYPE:
388                if (preg_match('/\n1 HUSB @(' . Gedcom::REGEX_XREF . ')@/', $gedrec, $match)) {
389                    $husb = $match[1];
390                } else {
391                    $husb = '';
392                }
393                if (preg_match('/\n1 WIFE @(' . Gedcom::REGEX_XREF . ')@/', $gedrec, $match)) {
394                    $wife = $match[1];
395                } else {
396                    $wife = '';
397                }
398                $nchi = preg_match_all('/\n1 CHIL @(' . Gedcom::REGEX_XREF . ')@/', $gedrec, $match);
399                if (preg_match('/\n1 NCHI (\d+)/', $gedrec, $match)) {
400                    $nchi = max($nchi, $match[1]);
401                }
402
403                DB::table('families')->insert([
404                    'f_id'      => $xref,
405                    'f_file'    => $tree_id,
406                    'f_husb'    => $husb,
407                    'f_wife'    => $wife,
408                    'f_gedcom'  => $gedrec,
409                    'f_numchil' => $nchi,
410                ]);
411
412                // Update the cross-reference/index tables.
413                $this->updatePlaces($xref, $tree, $gedrec);
414                $this->updateDates($xref, $tree_id, $gedrec);
415                break;
416
417            case Source::RECORD_TYPE:
418                if (preg_match('/\n1 TITL (.+)/', $gedrec, $match)) {
419                    $name = $match[1];
420                } elseif (preg_match('/\n1 ABBR (.+)/', $gedrec, $match)) {
421                    $name = $match[1];
422                } else {
423                    $name = $xref;
424                }
425
426                DB::table('sources')->insert([
427                    's_id'     => $xref,
428                    's_file'   => $tree_id,
429                    's_name'   => mb_substr($name, 0, 255),
430                    's_gedcom' => $gedrec,
431                ]);
432                break;
433
434            case Repository::RECORD_TYPE:
435            case Note::RECORD_TYPE:
436            case Submission::RECORD_TYPE:
437            case Submitter::RECORD_TYPE:
438            case Location::RECORD_TYPE:
439                DB::table('other')->insert([
440                    'o_id'     => $xref,
441                    'o_file'   => $tree_id,
442                    'o_type'   => $type,
443                    'o_gedcom' => $gedrec,
444                ]);
445                break;
446
447            case Header::RECORD_TYPE:
448                // Force HEAD records to have a creation date.
449                if (!str_contains($gedrec, "\n1 DATE ")) {
450                    $today = strtoupper(date('d M Y'));
451                    $gedrec .= "\n1 DATE " . $today;
452                }
453
454                DB::table('other')->insert([
455                    'o_id'     => $xref,
456                    'o_file'   => $tree_id,
457                    'o_type'   => Header::RECORD_TYPE,
458                    'o_gedcom' => $gedrec,
459                ]);
460                break;
461
462
463            case Media::RECORD_TYPE:
464                $record = Registry::mediaFactory()->new($xref, $gedrec, null, $tree);
465
466                DB::table('media')->insert([
467                    'm_id'     => $xref,
468                    'm_file'   => $tree_id,
469                    'm_gedcom' => $gedrec,
470                ]);
471
472                foreach ($record->mediaFiles() as $media_file) {
473                    DB::table('media_file')->insert([
474                        'm_id'                 => $xref,
475                        'm_file'               => $tree_id,
476                        'multimedia_file_refn' => mb_substr($media_file->filename(), 0, 248),
477                        'multimedia_format'    => mb_substr($media_file->format(), 0, 4),
478                        'source_media_type'    => mb_substr($media_file->type(), 0, 15),
479                        'descriptive_title'    => mb_substr($media_file->title(), 0, 248),
480                    ]);
481                }
482                break;
483
484            default: // Custom record types.
485                DB::table('other')->insert([
486                    'o_id'     => $xref,
487                    'o_file'   => $tree_id,
488                    'o_type'   => mb_substr($type, 0, 15),
489                    'o_gedcom' => $gedrec,
490                ]);
491                break;
492        }
493
494        // Update the cross-reference/index tables.
495        $this->updateLinks($xref, $tree_id, $gedrec);
496    }
497
498    /**
499     * Legacy Family Tree software generates _PLAC_DEFN records containing LAT/LONG values
500     *
501     * @param string $gedcom
502     */
503    private function importLegacyPlacDefn(string $gedcom): void
504    {
505        $gedcom_service = new GedcomService();
506
507        if (preg_match('/\n1 PLAC (.+)/', $gedcom, $match)) {
508            $place_name = $match[1];
509        } else {
510            return;
511        }
512
513        if (preg_match('/\n3 LATI ([NS].+)/', $gedcom, $match)) {
514            $latitude = $gedcom_service->readLatitude($match[1]);
515        } else {
516            return;
517        }
518
519        if (preg_match('/\n3 LONG ([EW].+)/', $gedcom, $match)) {
520            $longitude = $gedcom_service->readLongitude($match[1]);
521        } else {
522            return;
523        }
524
525        $location = new PlaceLocation($place_name);
526
527        if ($location->latitude() === null && $location->longitude() === null) {
528            DB::table('place_location')
529                ->where('id', '=', $location->id())
530                ->update([
531                    'latitude'  => $latitude,
532                    'longitude' => $longitude,
533                ]);
534        }
535    }
536
537    /**
538     * Legacy Family Tree software generates _PLAC records containing LAT/LONG values
539     *
540     * @param string $gedcom
541     */
542    private function importTNGPlac(string $gedcom): void
543    {
544        if (preg_match('/^0 _PLAC (.+)/', $gedcom, $match)) {
545            $place_name = $match[1];
546        } else {
547            return;
548        }
549
550        if (preg_match('/\n2 LATI (.+)/', $gedcom, $match)) {
551            $latitude = (float) $match[1];
552        } else {
553            return;
554        }
555
556        if (preg_match('/\n2 LONG (.+)/', $gedcom, $match)) {
557            $longitude = (float) $match[1];
558        } else {
559            return;
560        }
561
562        $location = new PlaceLocation($place_name);
563
564        if ($location->latitude() === null && $location->longitude() === null) {
565            DB::table('place_location')
566                ->where('id', '=', $location->id())
567                ->update([
568                    'latitude'  => $latitude,
569                    'longitude' => $longitude,
570                ]);
571        }
572    }
573
574    /**
575     * Extract all level 2 places from the given record and insert them into the places table
576     *
577     * @param string $xref
578     * @param Tree   $tree
579     * @param string $gedrec
580     *
581     * @return void
582     */
583    public function updatePlaces(string $xref, Tree $tree, string $gedrec): void
584    {
585        // Insert all new rows together
586        $rows = [];
587
588        preg_match_all('/\n2 PLAC (.+)/', $gedrec, $matches);
589
590        $places = array_unique($matches[1]);
591
592        foreach ($places as $place_name) {
593            $place = new Place($place_name, $tree);
594
595            // Calling Place::id() will create the entry in the database, if it doesn't already exist.
596            while ($place->id() !== 0) {
597                $rows[] = [
598                    'pl_p_id' => $place->id(),
599                    'pl_gid'  => $xref,
600                    'pl_file' => $tree->id(),
601                ];
602
603                $place = $place->parent();
604            }
605        }
606
607        // array_unique doesn't work with arrays of arrays
608        $rows = array_intersect_key($rows, array_unique(array_map('serialize', $rows)));
609
610        // PDO has a limit of 65535 placeholders, and each row requires 3 placeholders.
611        foreach (array_chunk($rows, 20000) as $chunk) {
612            DB::table('placelinks')->insert($chunk);
613        }
614    }
615
616    /**
617     * Extract all the dates from the given record and insert them into the database.
618     *
619     * @param string $xref
620     * @param int    $ged_id
621     * @param string $gedrec
622     *
623     * @return void
624     */
625    private function updateDates(string $xref, int $ged_id, string $gedrec): void
626    {
627        // Insert all new rows together
628        $rows = [];
629
630        preg_match_all("/\n1 (\w+).*(?:\n[2-9].*)*\n2 DATE (.+)(?:\n[2-9].*)*/", $gedrec, $matches, PREG_SET_ORDER);
631
632        foreach ($matches as $match) {
633            $fact = $match[1];
634            $date = new Date($match[2]);
635            $rows[] = [
636                'd_day'        => $date->minimumDate()->day,
637                'd_month'      => $date->minimumDate()->format('%O'),
638                'd_mon'        => $date->minimumDate()->month,
639                'd_year'       => $date->minimumDate()->year,
640                'd_julianday1' => $date->minimumDate()->minimumJulianDay(),
641                'd_julianday2' => $date->minimumDate()->maximumJulianDay(),
642                'd_fact'       => $fact,
643                'd_gid'        => $xref,
644                'd_file'       => $ged_id,
645                'd_type'       => $date->minimumDate()->format('%@'),
646            ];
647
648            $rows[] = [
649                'd_day'        => $date->maximumDate()->day,
650                'd_month'      => $date->maximumDate()->format('%O'),
651                'd_mon'        => $date->maximumDate()->month,
652                'd_year'       => $date->maximumDate()->year,
653                'd_julianday1' => $date->maximumDate()->minimumJulianDay(),
654                'd_julianday2' => $date->maximumDate()->maximumJulianDay(),
655                'd_fact'       => $fact,
656                'd_gid'        => $xref,
657                'd_file'       => $ged_id,
658                'd_type'       => $date->minimumDate()->format('%@'),
659            ];
660        }
661
662        // array_unique doesn't work with arrays of arrays
663        $rows = array_intersect_key($rows, array_unique(array_map('serialize', $rows)));
664
665        DB::table('dates')->insert($rows);
666    }
667
668    /**
669     * Extract all the links from the given record and insert them into the database
670     *
671     * @param string $xref
672     * @param int    $ged_id
673     * @param string $gedrec
674     *
675     * @return void
676     */
677    private function updateLinks(string $xref, int $ged_id, string $gedrec): void
678    {
679        // Insert all new rows together
680        $rows = [];
681
682        preg_match_all('/\n\d+ (' . Gedcom::REGEX_TAG . ') @(' . Gedcom::REGEX_XREF . ')@/', $gedrec, $matches, PREG_SET_ORDER);
683
684        foreach ($matches as $match) {
685            // Some applications (e.g. GenoPro) create links longer than 15 characters.
686            $link = mb_substr($match[1], 15);
687
688            // Take care of "duplicates" that differ on case/collation, e.g. "SOUR @S1@" and "SOUR @s1@"
689            $rows[$link . strtoupper($match[2])] = [
690                'l_from' => $xref,
691                'l_to'   => $match[2],
692                'l_type' => $link,
693                'l_file' => $ged_id,
694            ];
695        }
696
697        DB::table('link')->insert($rows);
698    }
699
700    /**
701     * Extract all the names from the given record and insert them into the database.
702     *
703     * @param string     $xref
704     * @param int        $ged_id
705     * @param Individual $record
706     *
707     * @return void
708     */
709    private function updateNames(string $xref, int $ged_id, Individual $record): void
710    {
711        // Insert all new rows together
712        $rows = [];
713
714        foreach ($record->getAllNames() as $n => $name) {
715            if ($name['givn'] === Individual::PRAENOMEN_NESCIO) {
716                $soundex_givn_std = null;
717                $soundex_givn_dm  = null;
718            } else {
719                $soundex_givn_std = Soundex::russell($name['givn']);
720                $soundex_givn_dm  = Soundex::daitchMokotoff($name['givn']);
721            }
722
723            if ($name['surn'] === Individual::NOMEN_NESCIO) {
724                $soundex_surn_std = null;
725                $soundex_surn_dm  = null;
726            } else {
727                $soundex_surn_std = Soundex::russell($name['surname']);
728                $soundex_surn_dm  = Soundex::daitchMokotoff($name['surname']);
729            }
730
731            $rows[] = [
732                'n_file'             => $ged_id,
733                'n_id'               => $xref,
734                'n_num'              => $n,
735                'n_type'             => $name['type'],
736                'n_sort'             => mb_substr($name['sort'], 0, 255),
737                'n_full'             => mb_substr($name['fullNN'], 0, 255),
738                'n_surname'          => mb_substr($name['surname'], 0, 255),
739                'n_surn'             => mb_substr($name['surn'], 0, 255),
740                'n_givn'             => mb_substr($name['givn'], 0, 255),
741                'n_soundex_givn_std' => $soundex_givn_std,
742                'n_soundex_surn_std' => $soundex_surn_std,
743                'n_soundex_givn_dm'  => $soundex_givn_dm,
744                'n_soundex_surn_dm'  => $soundex_surn_dm,
745            ];
746        }
747
748        DB::table('name')->insert($rows);
749    }
750
751    /**
752     * Extract inline media data, and convert to media objects.
753     *
754     * @param Tree   $tree
755     * @param string $gedcom
756     *
757     * @return string
758     */
759    private function convertInlineMedia(Tree $tree, string $gedcom): string
760    {
761        while (preg_match('/\n1 OBJE(?:\n[2-9].+)+/', $gedcom, $match)) {
762            $xref   = $this->createMediaObject($match[0], $tree);
763            $gedcom = strtr($gedcom, [$match[0] =>  "\n1 OBJE @" . $xref . '@']);
764        }
765        while (preg_match('/\n2 OBJE(?:\n[3-9].+)+/', $gedcom, $match)) {
766            $xref   = $this->createMediaObject($match[0], $tree);
767            $gedcom = strtr($gedcom, [$match[0] =>  "\n2 OBJE @" . $xref . '@']);
768        }
769        while (preg_match('/\n3 OBJE(?:\n[4-9].+)+/', $gedcom, $match)) {
770            $xref   = $this->createMediaObject($match[0], $tree);
771            $gedcom = strtr($gedcom, [$match[0] =>  "\n3 OBJE @" . $xref . '@']);
772        }
773
774        return $gedcom;
775    }
776
777    /**
778     * Create a new media object, from inline media data.
779     *
780     * GEDCOM 5.5.1 specifies: +1 FILE / +2 FORM / +3 MEDI / +1 TITL
781     * GEDCOM 5.5 specifies: +1 FILE / +1 FORM / +1 TITL
782     * GEDCOM 5.5.1 says that GEDCOM 5.5 specifies:  +1 FILE / +1 FORM / +2 MEDI
783     *
784     * Legacy generates: +1 FORM / +1 FILE / +1 TITL / +1 _SCBK / +1 _PRIM / +1 _TYPE / +1 NOTE
785     * RootsMagic generates: +1 FILE / +1 FORM / +1 TITL
786     *
787     * @param string $gedcom
788     * @param Tree   $tree
789     *
790     * @return string
791     */
792    private function createMediaObject(string $gedcom, Tree $tree): string
793    {
794        preg_match('/\n\d FILE (.+)/', $gedcom, $match);
795        $file = $match[1] ?? '';
796
797        preg_match('/\n\d TITL (.+)/', $gedcom, $match);
798        $title = $match[1] ?? '';
799
800        preg_match('/\n\d FORM (.+)/', $gedcom, $match);
801        $format = $match[1] ?? '';
802
803        preg_match('/\n\d MEDI (.+)/', $gedcom, $match);
804        $media = $match[1] ?? '';
805
806        preg_match('/\n\d _SCBK (.+)/', $gedcom, $match);
807        $scrapbook = $match[1] ?? '';
808
809        preg_match('/\n\d _PRIM (.+)/', $gedcom, $match);
810        $primary = $match[1] ?? '';
811
812        preg_match('/\n\d _TYPE (.+)/', $gedcom, $match);
813        if ($media === '') {
814            // Legacy uses _TYPE instead of MEDI
815            $media = $match[1] ?? '';
816            $type  = '';
817        } else {
818            $type = $match[1] ?? '';
819        }
820
821        preg_match_all('/\n\d NOTE (.+(?:\n\d CONT.*)*)/', $gedcom, $matches);
822        $notes = $matches[1] ?? [];
823
824        // Have we already created a media object with the same title/filename?
825        $xref = DB::table('media_file')
826            ->where('m_file', '=', $tree->id())
827            ->where('descriptive_title', '=', mb_substr($title, 0, 248))
828            ->where('multimedia_file_refn', '=', mb_substr($file, 0, 248))
829            ->value('m_id');
830
831        if ($xref === null) {
832            $xref = Registry::xrefFactory()->make(Media::RECORD_TYPE);
833
834            // convert to a media-object
835            $gedcom = '0 @' . $xref . "@ OBJE\n1 FILE " . $file;
836
837            if ($format !== '') {
838                $gedcom .= "\n2 FORM " . $format;
839
840                if ($media !== '') {
841                    $gedcom .= "\n3 TYPE " . $media;
842                }
843            }
844
845            if ($title !== '') {
846                $gedcom .= "\n3 TITL " . $title;
847            }
848
849            if ($scrapbook !== '') {
850                $gedcom .= "\n1 _SCBK " . $scrapbook;
851            }
852
853            if ($primary !== '') {
854                $gedcom .= "\n1 _PRIM " . $primary;
855            }
856
857            if ($type !== '') {
858                $gedcom .= "\n1 _TYPE " . $type;
859            }
860
861            foreach ($notes as $note) {
862                $gedcom .= "\n1 NOTE " . strtr($note, ["\n3" => "\n2", "\n4" => "\n2", "\n5" => "\n2"]);
863            }
864
865            DB::table('media')->insert([
866                'm_id'     => $xref,
867                'm_file'   => $tree->id(),
868                'm_gedcom' => $gedcom,
869            ]);
870
871            DB::table('media_file')->insert([
872                'm_id'                 => $xref,
873                'm_file'               => $tree->id(),
874                'multimedia_file_refn' => mb_substr($file, 0, 248),
875                'multimedia_format'    => mb_substr($format, 0, 4),
876                'source_media_type'    => mb_substr($media, 0, 15),
877                'descriptive_title'    => mb_substr($title, 0, 248),
878            ]);
879        }
880
881        return $xref;
882    }
883
884    /**
885     * update a record in the database
886     *
887     * @param string $gedrec
888     * @param Tree   $tree
889     * @param bool   $delete
890     *
891     * @return void
892     * @throws GedcomErrorException
893     */
894    public function updateRecord(string $gedrec, Tree $tree, bool $delete): void
895    {
896        if (preg_match('/^0 @(' . Gedcom::REGEX_XREF . ')@ (' . Gedcom::REGEX_TAG . ')/', $gedrec, $match)) {
897            [, $gid, $type] = $match;
898        } elseif (preg_match('/^0 (HEAD)(?:\n|$)/', $gedrec, $match)) {
899            // The HEAD record has no XREF.  Any others?
900            $gid  = $match[1];
901            $type = $match[1];
902        } else {
903            throw new GedcomErrorException($gedrec);
904        }
905
906        // Place links
907        DB::table('placelinks')
908            ->where('pl_gid', '=', $gid)
909            ->where('pl_file', '=', $tree->id())
910            ->delete();
911
912        // Orphaned places.  If we're deleting  "Westminster, London, England",
913        // then we may also need to delete "London, England" and "England".
914        do {
915            $affected = DB::table('places')
916                ->leftJoin('placelinks', function (JoinClause $join): void {
917                    $join
918                        ->on('p_id', '=', 'pl_p_id')
919                        ->on('p_file', '=', 'pl_file');
920                })
921                ->whereNull('pl_p_id')
922                ->delete();
923        } while ($affected > 0);
924
925        DB::table('dates')
926            ->where('d_gid', '=', $gid)
927            ->where('d_file', '=', $tree->id())
928            ->delete();
929
930        DB::table('name')
931            ->where('n_id', '=', $gid)
932            ->where('n_file', '=', $tree->id())
933            ->delete();
934
935        DB::table('link')
936            ->where('l_from', '=', $gid)
937            ->where('l_file', '=', $tree->id())
938            ->delete();
939
940        switch ($type) {
941            case Individual::RECORD_TYPE:
942                DB::table('individuals')
943                    ->where('i_id', '=', $gid)
944                    ->where('i_file', '=', $tree->id())
945                    ->delete();
946                break;
947
948            case Family::RECORD_TYPE:
949                DB::table('families')
950                    ->where('f_id', '=', $gid)
951                    ->where('f_file', '=', $tree->id())
952                    ->delete();
953                break;
954
955            case Source::RECORD_TYPE:
956                DB::table('sources')
957                    ->where('s_id', '=', $gid)
958                    ->where('s_file', '=', $tree->id())
959                    ->delete();
960                break;
961
962            case Media::RECORD_TYPE:
963                DB::table('media_file')
964                    ->where('m_id', '=', $gid)
965                    ->where('m_file', '=', $tree->id())
966                    ->delete();
967
968                DB::table('media')
969                    ->where('m_id', '=', $gid)
970                    ->where('m_file', '=', $tree->id())
971                    ->delete();
972                break;
973
974            default:
975                DB::table('other')
976                    ->where('o_id', '=', $gid)
977                    ->where('o_file', '=', $tree->id())
978                    ->delete();
979                break;
980        }
981
982        if (!$delete) {
983            $this->importRecord($gedrec, $tree, true);
984        }
985    }
986}
987