Maintenance Reports

From TNG_Wiki
Jump to: navigation, search

The report facility in TNG is very useful for checking and tidying the database. Many people find it useful to store these reports for future use although generally these reports are not made Active as they shouldn't be seen by anyone except administrators. However they can still be run from the Admin Reports page by clicking the button marked with a tick.

It may be useful to give these a higher Rank than other reports (e.g. 10) so as to move them to the bottom of the report list. Some reports can change the database simply by running them: these could sensibly be given a higher number so that they aren't run accidentally.

These reports are only specified using SQL rather than using the report generator as many can only be given this way. So on the "Add New" page on Admin Reports they are entered under the heading "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:". Set the Report Name and Description from the details below, give it a Rank/Priority of 10 or more but don't make it Active. (Apart from anything else, these queries do not apply any privacy or living protection.) Note that you will have to change table names (after FROM) in queries if you do not use the default names (e.g. tng_people).

More complex maintenance reports have their own page in the wiki and many of these are listed in the See Also sections.

From TNG v11, the Administration section contains a selection of these reports, to be found under Miscellaneous >> Data Validation.

Living flag

Since the living flag is not part of the GEDCOM standard it may be necessary to initially mark all living individuals. Also it's easy to forget when adding new individuals. These reports flag various possible mistakes.

Children of recent adults not marked as living

This looks for all people born after 1915 or children of people born after 1900 who are not marked as having died or been buried. It can be easily adapted to other dates.

SELECT distinct ch.personID, ch.gedcom, ch.firstname, ch.lastname, ch.birthdate
  FROM (tng_families fam, tng_children c, tng_people ch)
  left join tng_people dad on husband=dad.personID   and dad.gedcom=fam.gedcom
  left join tng_people mum on wife=mum.personID and mum.gedcom=fam.gedcom
  WHERE fam.familyID=c.familyID and c.personID=ch.personID
  and fam.gedcom=c.gedcom and c.gedcom=ch.gedcom 
  and ! and ch.deathdate="" and ch.burialdate=""
  and (( or year(mum.birthdatetr)>=1900)
    or ( or year(dad.birthdatetr)>=1900)
    or year(ch.birthdatetr)>=1915)
  order by ch.birthdatetr desc

Dead people marked as living

This flags any people marked as having died who still have the living flag set. (If you want them still to be restricted it's probably better to use the Private flag.)

  SELECT personID,gedcom,firstname,lastname,birthdate,deathdate FROM tng_people 
  WHERE deathdate!="" and living

Sex of individuals

Sex of person has been omitted

This is inevitable for some entries (mainly stillbirths where sex is not recorded) but it's easy to miss setting the status when adding new individuals.

  SELECT personID, gedcom, firstname, lastname
  FROM tng_people 
  WHERE sex!="M" and sex!="F"

Sex doesn't match the traditional marriage relationship

Since same sex marriages is different, TNG doesn't enforce a rule that the husband is male and wife female, so this report picks out these out as well as mistakes.

  SELECT h.gedcom, h.personID,h.firstname,h.lastname,, w.personID,w.firstname,w.lastname,, marrtype 
  FROM tng_families f left join tng_people h on husband=h.personID and f.gedcom=h.gedcom 
      left join tng_people w on wife=w.personID and f.gedcom=w.gedcom   
  WHERE (h.personID is not NULL and !='M') or (w.personID is not NULL and!='F')
  ORDER BY f.familyID


People have different views on whether photos of living people should be shown to the world. The "always on" box overrides the TNG defaults and some of these queries can be adapted to check whether your own policy is being followed.

Children with photos always on

Children's photos are a particular concern on the internet so this lists those now under 18 who are shown to the world.

  SELECT p.personID,p.gedcom,firstname,lastname,birthdate 
  FROM tng_people p join tng_medialinks lnk on p.personID=lnk.personID and p.gedcom=lnk.gedcom
   join tng_media m on lnk.mediaID=m.mediaID 
  WHERE birthdatetr>current_date() - interval 18 year and alwayson

Young adults with photos always on

This checks those between 18 and 25. The test can be inverted by substituting "=0" at the end.

  SELECT DISTINCT p.personID, p.gedcom, firstname, lastname, birthdate
  FROM tng_people p
  JOIN tng_medialinks lnk ON p.personID = lnk.personID
  AND p.gedcom = lnk.gedcom
  JOIN tng_media m ON lnk.mediaID = m.mediaID
  WHERE birthdatetr > '0000-00-00'
  AND birthdatetr < current_date( ) - INTERVAL 18 year
  AND birthdatetr > current_date( ) - INTERVAL 25 year
  AND living AND alwayson =1


People marked as natural child of more than one family

This may indicate that an adopted or similar flag has been omitted, but more often is produced by unresolved issues in the research process. It shows any child who has more than one natural father or mother.

  SELECT a.personID,a.gedcom,p.firstname,p.lastname, a.familyID, b.familyID
  FROM tng_children a
  JOIN tng_children b ON a.personID = b.personID AND a.gedcom = b.gedcom
  join tng_people p on a.personID=p.personID and a.gedcom=p.gedcom
  WHERE a.familyID != b.familyID
  AND (((a.frel = "" OR a.frel = "birth") AND (b.frel = "" OR b.frel = "birth"))
  OR ((a.mrel = "" OR a.mrel = "birth") AND (b.mrel = "" OR b.mrel = "birth")))

Null families

There is only one parent link to a family structure and no other significant information in the record or children referring to it. These relics can occur when trying to add a parent or sibling. If you know a person was married but not who to, then add a (possibly uncertain) date or type to the record.

  SELECT f.familyID,f.gedcom,husband,wife,marrdate,marrtype FROM tng_families f
  WHERE (husband="" or wife="") and marrdate="" and marrtype=""
  and NOT EXISTS (select * from tng_children c where f.familyID=c.familyID and f.gedcom=c.gedcom)

It can also happen where there is just one child and no parents.

  SELECT f.familyID,f.gedcom,ch.personID, husband,wife,marrdate,marrtype FROM tng_children ch 
  JOIN tng_families f on ch.familyID=f.familyID and ch.gedcom=f.gedcom
  WHERE husband="" and wife="" and marrdate="" and marrtype=""
  and NOT EXISTS (select * from tng_children c 
   where f.familyID=c.familyID and f.gedcom=c.gedcom and c.personID!=ch.personID)

Ordering of children in families

TNG adds to the GEDCOM system an ordering on children within a family, which is particularly useful if dates of birth are not known for some or all children. This report prints out families in which the dates of birth of the children do not follow this ordering, together with a link to the father (or mother if no father).

  SELECT distinct c.familyID,concat("<a href='getperson.php?personID=",p.personID,"&tree=",p.gedcom,"'>",p.firstname," ",p.lastname,"</a>") as parent
  FROM tng_children c join tng_children d on c.familyID=d.familyID and c.gedcom=d.gedcom
    join tng_people c1 on c.personID=c1.personID and c.gedcom=c1.gedcom
    join tng_people d1 on d.personID=d1.personID and d.gedcom=d1.gedcom
    join tng_families f on c.familyID=f.familyID and c.gedcom=f.gedcom
    join tng_people p on if(f.husband!="",f.husband=p.personID, f.wife=p.personID) and f.gedcom=p.gedcom
  WHERE c1.birthdatetr!=0 and d1.birthdatetr!=0 and c1.birthdatetr<d1.birthdatetr and c.ordernum>d.ordernum
  ORDER by c.familyID

Note that TNG rather oddly makes both "BEF 1900" and "AFT 1900" the same as 1900-00-00 so if these GEDCOM styles are used, some extra lines may be printed on this report.



Places that might be the same

This lists places which are the same up to the first comma, on one line. The numbers in brackets are the total number of instances of that name and the names are listed in decreasing order of possibilities and instances. No account is taken of different trees. You need then to use the Admin Places edit and merge tabs to make any corrections necessary.

  select root, c, variants 
  from (select substring_index(place, ",",1) root, count(*) c, sum(ct) tot,
    group_concat( place," (", ct,")" order by place separator " + ") variants
   from (select place,count(*) ct 
    from (SELECT personID id, birthplace place, "b" t FROM tng_people WHERE birthplace !=""
      union SELECT personID,altbirthplace, "a" FROM tng_people WHERE altbirthplace !=""
      union SELECT personID,baptplace, "b" FROM tng_people WHERE baptplace !=""
      union SELECT personID,deathplace, "d" FROM tng_people WHERE deathplace !=""
      union SELECT personID,burialplace, "r" FROM tng_people WHERE burialplace !=""
      union SELECT personID,confplace, "c" FROM tng_people WHERE confplace !=""
      union SELECT personID,initplace, "i" FROM tng_people WHERE initplace !=""
      union SELECT personID,endlplace, "f" FROM tng_people WHERE endlplace !=""
      union SELECT eventID, eventplace, "e" FROM tng_events WHERE eventplace !=""
      union SELECT familyID, marrplace, "m" FROM tng_families WHERE marrplace !=""
      union SELECT familyID, divplace, "d" FROM tng_families WHERE divplace !=""
      union SELECT familyID, sealplace, "s" FROM tng_families WHERE sealplace !=""
    ) b group by place
   ) a group by root
  ) d
  where c>1
  order by c desc, tot desc

Redundant places in the places list

TNG stores latitude and longitude and place levels in a separate table but this isn't pruned when mistakes are corrected. This report lists those places which have no corresponding entry in the tree. It may be best to merge entries first to avoid losing information.

  select, p.latitude, p.longitude
  from tng_places p left join
    (SELECT  birthplace place FROM tng_people WHERE birthplace !=""
      union SELECT altbirthplace FROM tng_people WHERE altbirthplace !=""
      union SELECT baptplace FROM tng_people WHERE baptplace !=""
      union SELECT deathplace FROM tng_people WHERE deathplace !=""
      union SELECT burialplace FROM tng_people WHERE burialplace !=""
      union SELECT confplace FROM tng_people WHERE confplace !=""
      union SELECT initplace FROM tng_people WHERE initplace !=""
      union SELECT endlplace FROM tng_people WHERE endlplace !=""
      union SELECT eventplace FROM tng_events WHERE eventplace !=""
      union SELECT marrplace FROM tng_families WHERE marrplace !=""
      union SELECT divplace FROM tng_families WHERE divplace !=""
      union SELECT sealplace FROM tng_families WHERE sealplace !=""
    ) b
  where is null
  order by


See also