Web based family history software

Question Gedcom GIVN and SURN missing

  • klemens
  • Topic Author
  • Offline
  • Junior Member
  • Junior Member
More
1 year 9 months ago #1 by klemens
Gedcom GIVN and SURN missing was created by klemens
Hallo, nach einem früheren Update sind einige (ca. 22000) Daten verlorengegangen.
In der Tabelle "wt_individuals" werden der Vor- und Zuname nicht mehr richtig aufgeführt.
In der Tabelle "wt_name" ist alles so wie es sein soll.
Das Beispiel soll das verdeutlichen.

Hat jemand eine Idee, wie man dies auf einfache Weise reparieren könnte?
Oder muß ich das alles manuell ändern.

Hello, after a previous update some (about 22000) data has been lost.
In the table "wt_individuals" the first and last name are not listed correctly anymore.
In the table "wt_name" everything is as it should be.
The example should clarify this.

Does anyone have an idea how to fix this in a simple way?
Or do I have to change all this manually.

Beispiel / Example;
tabelle "wt_individuals" feldname "i_gedcom"

Ist:
0 @I10223@ INDI
1 NAME Johann /Meier/
etc.

Soll:
0 @I5264@ INDI
1 NAME Johann /Meier/
2 GIVN Johann
2 SURN Meier
etc.

Klemens Miehe
Hannover Germany
anverwandte.info
stammbaum.anverwandte.info
webtrees 2.1.18
PHP Version 8.2.9
Linux v12015021622323418 4.19.0-23-amd64 #1 SMP Debian 4.19.269-1 (2022-12-20) x86_64

Please Log in or Create an account to join the conversation.

  • bertkoor
  • Away
  • Platinum Member
  • Platinum Member
  • Greetings from Utrecht, Holland
More
1 year 9 months ago #2 by bertkoor
Replied by bertkoor on topic Gedcom GIVN and SURN missing
Do you know how did that happen?

Assuming all the GIVN/SURN pairs have disappeared, you can try the following.
Go to the control panel / select your tree / select "data fixes" / select "search and replace"
Use "regular expression" as search method and "Individuals" as record type.
Search for:
Code:
1 NAME (.*) /(.*)/
Replace with:
Code:
1 NAME $1 /$2/\n2 GIVN $1\n2 SURN $2

Make sure you don't need to approve any changes, do use the "preview" option before you run it completely. I have not tested this.

stamboom.BertKoor.nl runs on webtrees v1.7.13

Please Log in or Create an account to join the conversation.

  • hermann
  • Away
  • Elite Member
  • Elite Member
More
1 year 9 months ago #3 by hermann
Replied by hermann on topic Gedcom GIVN and SURN missing
Your example shows two points:
- GIVN and SURN disappeared
- the XREF is different
How could this happen? An upgrade of webtrees would not change this genealogical data. Maybe a search/replace in the other direction as bertkoor showed (1st point)? Renumbering your tree (2nd point)? Or changing the GEDCOM outside of webtrees and reloading an erroneous GEDCOM file? I think it is important to know how this could happen to avoid it in the future.

Hermann
Designer of the custom module "Extended Family"

webtrees 2.1.19 (all custom modules installed, PHP 8.2, MariaDB 10.6) @ ahnen.hartenthaler.eu

Please Log in or Create an account to join the conversation.

  • klemens
  • Topic Author
  • Offline
  • Junior Member
  • Junior Member
More
1 year 9 months ago #4 by klemens
Replied by klemens on topic Gedcom GIVN and SURN missing
Hallo Bertkoor, hallo Hermann, danke für eure Infos.
Bertkoor, ich habe deinen Vorschlag getestet und es geht, nur dann bekommen alle Personen diesen Eintrag, also viele doppelt.

Wie konnte es passiert sein? Im Februar hatte ich bei Version 1.7. einen Fehler in der Datenbank (alte ID-Nr. wurden mit neuen Daten überschrieben). Beim Zurückspielen der Datensicherung könnte es passiert sein.
Ich werde doch wohl mein Tool benutzen, um die nicht sauberen Datensätze damit zu bearbeiten (Jahresarbeit, jeden Tag ein paar vorm Schlafengehen). Die GEDCOM Daten außerhalb zu bearbeiten ist mir bei 270.000 Personen zu riskant.
Sollte es noch andere Möglichkeiten geben, bin ich für alles offen.

Hello Bertkoor, hello Hermann, thanks for your info.
Bertkoor, I tested your suggestion and it works, only then all people get this entry, so many double.

How could it have happened? In February I had an error in the database with version 1.7. (old ID no. were overwritten with new data). It could have happened when restoring the backup.
I guess I will use my tool after all to edit the records that are not clean with it (yearly work, a few every day before going to bed). Editing the GEDCOM data outside is too risky for me with 270,000 people.
If there are other possibilities, I am open for everything.

Klemens Miehe
Hannover Germany
anverwandte.info
stammbaum.anverwandte.info
webtrees 2.1.18
PHP Version 8.2.9
Linux v12015021622323418 4.19.0-23-amd64 #1 SMP Debian 4.19.269-1 (2022-12-20) x86_64

Please Log in or Create an account to join the conversation.

  • bertkoor
  • Away
  • Platinum Member
  • Platinum Member
  • Greetings from Utrecht, Holland
More
1 year 9 months ago - 1 year 9 months ago #5 by bertkoor
Replied by bertkoor on topic Gedcom GIVN and SURN missing
One explanation is that the restore of table wt_individuals had failed.

The duplicates can be removed as follows:

Search for:
Code:
\n1 GIVN .*((\n\d.*)*\n1 GIVN .*)
Replace with:
Code:
$1

Then again search for:
Code:
\n1 SURN .*((\n\d.*)*\n1 SURN .*)
Replace with:
Code:
$1

It will remove the first occurrence, which is the one created by the search/replace done earlier today.
Cases where individuals had more than one NAME record need to be done by hand, or run the same fix multiple times and be very careful which replacements are correct and which are false.

stamboom.BertKoor.nl runs on webtrees v1.7.13
Last edit: 1 year 9 months ago by bertkoor.

Please Log in or Create an account to join the conversation.

  • klemens
  • Topic Author
  • Offline
  • Junior Member
  • Junior Member
More
1 year 9 months ago #6 by klemens
Replied by klemens on topic Gedcom GIVN and SURN missing
Hallo Bertkoor,
danke für deine Infos zur Datenkorrektur. Ich konnte es in meiner Testversion sehr gut durchführen, schaffte es aber nicht, die fehlenden „GIVN und SURN“ Eintragungen zu behalten.
Mein Programmierer hat dann nach langem Drängeln eine SQL-Prozedur geschrieben. Nach einem Testversuch habe ich die Prozedur durchlaufen lassen und jetzt sind die fehlenden Eintragungen wieder da.

Hello Bertkoor,
thanks for your info on data correction. I was able to do it very well in my test version, but did not manage to keep the missing "GIVN and SURN" entries.
My programmer then wrote an SQL procedure after much pestering. After a test attempt, I ran the procedure and now the missing entries are back.

SQL-Prozedur
##################
drop table if exists t1;
create temporary table t1
select i_id,i_gedcom,substr(i_gedcom,locate('1 NAME ',i_gedcom),locate(char(10),substr(i_gedcom,locate('1 NAME ',i_gedcom),80))) as alt
from webtrees.wt_individuals a
where i_gedcom not like '%2 GIVN%' and i_gedcom not like '%2 SURN%';

alter table t1 add index(i_id);

drop table if exists t2;
create temporary table t2
select * from t1 a
left outer join (select n_id,max(n_num) as n_num,n_givn,n_surname,n_full from webtrees.wt_name where n_type='NAME' group by n_id ) b
on a.i_id=b.n_id;


drop table if exists t3;
create temporary table t3
select *,concat(alt,'2 GIVN ',n_givn,char(10),'2 SURN ',n_surname,char(10)) as neu from t2;

alter table t3 add index(i_id);

SET SQL_SAFE_UPDATES = 0;
UPDATE webtrees.wt_individuals a left outer JOIN t3 b
ON a.i_id = b.i_id
SET a.i_gedcom = replace(a.i_gedcom,b.alt,b.neu)
where b.i_id is not null;
#####################

Klemens Miehe
Hannover Germany
anverwandte.info
stammbaum.anverwandte.info
webtrees 2.1.18
PHP Version 8.2.9
Linux v12015021622323418 4.19.0-23-amd64 #1 SMP Debian 4.19.269-1 (2022-12-20) x86_64

Please Log in or Create an account to join the conversation.

Powered by Kunena Forum
}