Auteur Sujet: Retrouver l'ascendance  (Lu 3490 fois)

plus minus reset

0 Membres et 1 Invité sur ce sujet

Hors ligne emilise

  • Expert
  • ****
  • Messages: 104
Retrouver l'ascendance
« le: 17 Août 2008 à 20:27:33 »
Bonsoir,

J'aimerais faire une requête SQL qui me parait assez complexe. En donnant le n° d'un invidu, j'aimerais reconstruire en SQL toute son ascendance.
J'ai de bonne notions en SQL, donc trouver les parents d'un individu ne me pose pas de problème, par contre je ne sais pas comment faire une "boucle" pour trouver les parents des parents jusqu'à épuisement!

Je précise bien que je ne veux pas forcément partir du Sosa 1 de ma base, donc pas une requête sur les n° Sosa. Et je précise aussi que je n'arrive pas à sortir ce que je veux avec les rapports, donc pas ça non plus!
Je peux aussi imaginer des solutions avec un nombre de générations max fixé à l'avance, mais déjà ça me parait très lourd, et en plus j'aimerais bien quelques chose de générique.

Comment feriez-vous ça ?

Merci de votre aide.
Émilise - Ancestro v 1352
 

Hors ligne DDdeBerdeux

Retrouver l'ascendance
« Réponse #1 le: 18 Août 2008 à 00:47:56 »
Bonsoir,
Je ne vois pas comment faire une boucle dans une requête. C'est bien pour celà que c'est fait par une procédure stockée PROC_TQ_ASCENDANCE qui utilise une table temporaire.
A moins de faire une requête "execute block" qui permet de mettre le code d'une procédure dans une requête, mais je serai étonné que le BOA accepte une telle requête.
Vous pourriez déjà voir comment fonctionne la procédure pour vous en inspirer ou pour l'utiliser dans une requête.
A+
André
Une application pleinement satisfaisante est toujours complétée par une mise à jour buggée. (Loi des Mises à Jour)
 

Hors ligne emilise

  • Expert
  • ****
  • Messages: 104
Retrouver l'ascendance
« Réponse #2 le: 18 Août 2008 à 23:23:55 »
Bonsoir,

Merci beaucoup, je ne connaissais pas cette procédure. Elle m'a été bien utile et j'ai même réussi à obtenir la liste que je voulais!
Je la poste ici, elle resservira peut-être, qui sait.

Ayant pour projet de dessiner la cartographie de mes ancêtres côté maternel, je voulais une requête me donnant pour chaque génération, les villes d'origine et les villes de la génération suivante.
Je me suis d'abord basée uniquement sur les villes de naissances de chaque individu -> ville de naissance du parent, comparée à celle de l'enfant.
Pour étoffer un peu, j'ai rajouté la ville du mariage des parents si la naissance de l'enfant est inconnue -> Naissance du parent, ou à défaut mariage de ses propres parents; comparée à la naissance de l'enfant, à défaut la ville de mariage des parents
Pour compléter, dans les plus lointaines générations, j'ai rajouté la ville de décès si naissance et mariage des parents étaient inconnus.

Au final, on obtient une liste présentant, pour chaque génération, tous ses représentants, avec sa ville d'origine et la ville de migration (la ville d'origine de ses enfants, donc).

Bref, voilà le pavé. Malheureusement on est obligé de la dédoubler : une requête pour la mère de chaque individu et une pour son père, car Ancestro crashait si je mettais un simple OR dans la requête.

Pour réutiliser cette requête, il suffit de remplacer dans les deux passages : PROC_TQ_ASCENDANCE(5457,13,0,1) : 5457 par l'id de la fiche de départ et 13 par le nombre de générations à remonter.

select
tq_niveau+1 generation,
iif(naissance2.EV_IND_VILLE is null, iif(mariage2.EV_FAM_VILLE is null, deces2.ev_ind_ville, mariage2.ev_fam_ville), naissance2.EV_IND_VILLE) origine,
iif(
iif(naissance2.EV_IND_VILLE is null, iif(mariage2.EV_FAM_VILLE is null, deces2.ev_ind_ville, mariage2.ev_fam_ville), naissance2.EV_IND_VILLE)=iif(naissance.EV_IND_VILLE is null, iif(mariage.EV_FAM_VILLE is null, deces.ev_ind_ville, mariage.ev_fam_ville), naissance.EV_IND_VILLE),
'x',
iif(naissance.EV_IND_VILLE is null, iif(mariage.EV_FAM_VILLE is null, deces.ev_ind_ville, mariage.ev_fam_ville), naissance.EV_IND_VILLE)
) migration,
i2.nom nom_parent,
i2.prenom prenom_parent,
i.prenom prenom_enfant
from
(select tq_niveau, tq_cle_fiche from  PROC_TQ_ASCENDANCE(5457,13,0,1)),
individu i
left join EVENEMENTS_IND naissance on (i.cle_fiche = naissance.EV_IND_KLE_FICHE and naissance.EV_IND_TYPE = 'BIRT')
left join t_union u on (i.cle_pere = u.union_mari and i.cle_mere = u.union_femme)
left join EVENEMENTS_FAM mariage on (mariage.EV_FAM_KLE_FAMILLE= u.UNION_CLEF and mariage.EV_FAM_TYPE = 'MARR')
left join EVENEMENTS_IND deces on (i.cle_fiche = deces.EV_IND_KLE_FICHE and deces.EV_IND_TYPE = 'DEAT'),
individu i2
left join EVENEMENTS_IND naissance2 on (i2.cle_fiche = naissance2.EV_IND_KLE_FICHE and naissance2.EV_IND_TYPE = 'BIRT')
left join t_union u2 on (i2.cle_pere = u2.union_mari and i2.cle_mere = u2.union_femme)
left join EVENEMENTS_FAM mariage2 on (mariage2.EV_FAM_KLE_FAMILLE= u2.UNION_CLEF and mariage2.EV_FAM_TYPE = 'MARR')
left join EVENEMENTS_IND deces2 on (i2.cle_fiche = deces2.EV_IND_KLE_FICHE and deces2.EV_IND_TYPE = 'DEAT')
where
tq_cle_fiche = i.cle_fiche
and i.cle_pere = i2.cle_fiche
union
select
tq_niveau+1 generation,
iif(naissance2.EV_IND_VILLE is null, iif(mariage2.EV_FAM_VILLE is null, deces2.ev_ind_ville, mariage2.ev_fam_ville), naissance2.EV_IND_VILLE) origine,
iif(
iif(naissance2.EV_IND_VILLE is null, iif(mariage2.EV_FAM_VILLE is null, deces2.ev_ind_ville, mariage2.ev_fam_ville), naissance2.EV_IND_VILLE)=iif(naissance.EV_IND_VILLE is null, iif(mariage.EV_FAM_VILLE is null, deces.ev_ind_ville, mariage.ev_fam_ville), naissance.EV_IND_VILLE),
'x',
iif(naissance.EV_IND_VILLE is null, iif(mariage.EV_FAM_VILLE is null, deces.ev_ind_ville, mariage.ev_fam_ville), naissance.EV_IND_VILLE)
) migration,
i2.nom nom_parent,
i2.prenom prenom_parent,
i.prenom prenom_enfant
from
(select tq_niveau, tq_cle_fiche from  PROC_TQ_ASCENDANCE(5457,13,0,1)),
individu i
left join EVENEMENTS_IND naissance on (i.cle_fiche = naissance.EV_IND_KLE_FICHE and naissance.EV_IND_TYPE = 'BIRT')
left join t_union u on (i.cle_pere = u.union_mari and i.cle_mere = u.union_femme)
left join EVENEMENTS_FAM mariage on (mariage.EV_FAM_KLE_FAMILLE= u.UNION_CLEF and mariage.EV_FAM_TYPE = 'MARR')
left join EVENEMENTS_IND deces on (i.cle_fiche = deces.EV_IND_KLE_FICHE and deces.EV_IND_TYPE = 'DEAT'),
individu i2
left join EVENEMENTS_IND naissance2 on (i2.cle_fiche = naissance2.EV_IND_KLE_FICHE and naissance2.EV_IND_TYPE = 'BIRT')
left join t_union u2 on (i2.cle_pere = u2.union_mari and i2.cle_mere = u2.union_femme)
left join EVENEMENTS_FAM mariage2 on (mariage2.EV_FAM_KLE_FAMILLE= u2.UNION_CLEF and mariage2.EV_FAM_TYPE = 'MARR')
left join EVENEMENTS_IND deces2 on (i2.cle_fiche = deces2.EV_IND_KLE_FICHE and deces2.EV_IND_TYPE = 'DEAT')
where
tq_cle_fiche = i.cle_fiche
and i.cle_mere = i2.cle_fiche

Un petit aperçu du résultat :
GénérationOrigineMigration
1Ménil-Ciboult (Le)Périgny
1Pérignyx
2Culey-le-PatryPérigny
2Ménil-Ciboult (Le)x
2Pérignyx
2Saint-Quentin-les-ChardonneretsMénil-Ciboult (Le)

Le x signifie qu'il n'y a pas eu de migration sur cette génération. Pour le reste, par exemple, sur la génération 1, le père était du Ménil-Ciboult, la mère de Périgny ,et leur enfant est né à Périgny.
« Modifié: 18 Août 2008 à 23:30:53 par emilise »
Émilise - Ancestro v 1352
 

Hors ligne DDdeBerdeux

Retrouver l'ascendance
« Réponse #3 le: 19 Août 2008 à 20:50:10 »
Bonsoir, et bravo, voilà une requête qui peut être utile.

Pour vous aider à optimiser cette requête en supprimant les jointures cle_enfant<-->cle_parent et construire votre requête sans nécessiter d'UNION (quoique l'UNION est parfois plus rapide), vous devriez utiliser les champs réponses de la procédure suivants:
tq_cle_fiche qui est le code de l'ascendant
tq_descendant qui est le code de l'enfant, origine de la présence de son parent dans l'ascendance
tq_dossier est l'autre parent de tq_descendant
ainsi la jointure avec la table t_union peut être: left join t_union u on (u.union_mari=tq_cle_fiche and u.union_femme=tq_dossier) or (u.union_mari=tq_dossier and u.union_femme=tq_cle_fiche).

Si en entrée, vous mettez niveau=0 au lieu de 13, vous serez uniquement limitée par le nombre de générations présentes dans votre généalogie.

A+
André

Une application pleinement satisfaisante est toujours complétée par une mise à jour buggée. (Loi des Mises à Jour)
 

Hors ligne emilise

  • Expert
  • ****
  • Messages: 104
Retrouver l'ascendance
« Réponse #4 le: 21 Août 2008 à 19:23:26 »
Bonsoir,

Effectivement, c'est bien plus simple comme ça! Je n'avais pas fait attention à tous les champs de retour. Où peut-on trouver de la doc là-dessus ? Paramètres d'entrée, de sortie, et éventuellement code des procédures ?
Par contre le BOA crash si l'on utilise le (u.union_mari=tq_cle_fiche and u.union_femme=tq_dossier) or (u.union_mari=tq_dossier and u.union_femme=tq_cle_fiche), comme il crashait dans ma première requête si je cherchais le père ou la mère dans la même requête. Du coup j'ai gardé mon précédant fonctionnement. Mais on peut quand même éviter le dédoublement, c'est un peu plus digeste!

select
tq_niveau generation,
iif(naissance.EV_IND_VILLE is null, iif(mariage.EV_FAM_VILLE is null, deces.ev_ind_ville, mariage.ev_fam_ville), naissance.EV_IND_VILLE) origine,
iif(
iif(naissance.EV_IND_VILLE is null, iif(mariage.EV_FAM_VILLE is null, deces.ev_ind_ville, mariage.ev_fam_ville), naissance.EV_IND_VILLE) = iif(naissance2.EV_IND_VILLE is null, iif(mariage2.EV_FAM_VILLE is null, deces2.ev_ind_ville, mariage2.ev_fam_ville), naissance2.EV_IND_VILLE),
'x',
iif(naissance2.EV_IND_VILLE is null, iif(mariage2.EV_FAM_VILLE is null, deces2.ev_ind_ville, mariage2.ev_fam_ville), naissance2.EV_IND_VILLE)
) migration,
i.nom nomparent, i.prenom prenomparent, i2.nom nomenft, i2.prenom prenomenft
from
(select tq_niveau, tq_cle_fiche, tq_descendant from PROC_TQ_ASCENDANCE(5457,0,0,1)),
individu i
left join EVENEMENTS_IND naissance on (i.cle_fiche = naissance.EV_IND_KLE_FICHE and naissance.EV_IND_TYPE = 'BIRT')
left join t_union u on (i.cle_pere = u.union_mari and i.cle_mere = u.union_femme)
left join EVENEMENTS_FAM mariage on (mariage.EV_FAM_KLE_FAMILLE= u.UNION_CLEF and mariage.EV_FAM_TYPE = 'MARR')
left join EVENEMENTS_IND deces on (i.cle_fiche = deces.EV_IND_KLE_FICHE and deces.EV_IND_TYPE = 'DEAT'),
individu i2
left join EVENEMENTS_IND naissance2 on (i2.cle_fiche = naissance2.EV_IND_KLE_FICHE and naissance2.EV_IND_TYPE = 'BIRT')
left join t_union u2 on (i2.cle_pere = u2.union_mari and i2.cle_mere = u2.union_femme)
left join EVENEMENTS_FAM mariage2 on (mariage2.EV_FAM_KLE_FAMILLE= u2.UNION_CLEF and mariage2.EV_FAM_TYPE = 'MARR')
left join EVENEMENTS_IND deces2 on (i2.cle_fiche = deces2.EV_IND_KLE_FICHE and deces2.EV_IND_TYPE = 'DEAT')
where
tq_cle_fiche = i.cle_fiche
and tq_descendant = i2.cle_fiche
Émilise - Ancestro v 1352
 

Hors ligne DDdeBerdeux

Retrouver l'ascendance
« Réponse #5 le: 22 Août 2008 à 00:35:55 »
Bonsoir,

Juste une petite remise en forme de votre requête:
select generation
  ,coalesce(ville_nais_anc,coalesce(ville_mar_anc,ville_dc_anc)) as origine
  ,case
    when coalesce(ville_nais_anc,coalesce(ville_mar_anc,ville_dc_anc))
         =coalesce(ville_nais_desc,coalesce(ville_mar_desc,ville_dc_desc))
      then 'x'
    else
      coalesce(ville_nais_desc,coalesce(ville_mar_desc,ville_dc_desc))
    end as migration
  ,nom_parent
  ,prenom_parent
  ,nom_enfant
  ,prenom_enfant
from
(select t.tq_niveau as generation
  ,n.ev_ind_ville as ville_nais_anc
  ,(select first 1 ev_fam_ville
    from evenements_fam
    where EV_FAM_KLE_FAMILLE=u.UNION_CLEF
      and EV_FAM_TYPE = 'MARR'
      and ev_fam_ville is not null
    order by ev_fam_date_year,ev_fam_date_mois,ev_fam_date) as ville_mar_anc
  ,d.ev_ind_ville as ville_dc_anc
  ,n2.ev_ind_ville as ville_nais_desc
  ,(select first 1 ev_fam_ville
    from evenements_fam
    where EV_FAM_KLE_FAMILLE=u2.UNION_CLEF
      and EV_FAM_TYPE = 'MARR'
      and ev_fam_ville is not null
    order by ev_fam_date_year,ev_fam_date_mois,ev_fam_date) as ville_mar_desc
  ,d2.ev_ind_ville as ville_dc_desc
  ,i.nom as nom_parent
  ,i.prenom as prenom_parent
  ,i2.nom as nom_enfant
  ,i2.prenom as prenom_enfant
from (select tq_niveau
        ,tq_cle_fiche
        ,tq_dossier
        ,tq_descendant
      from PROC_TQ_ASCENDANCE(:indi,0,0,1)) t
  inner join individu i on i.cle_fiche=t.tq_cle_fiche
  left join EVENEMENTS_IND n on i.cle_fiche = n.EV_IND_KLE_FICHE and n.EV_IND_TYPE = 'BIRT'
  left join t_union u on i.cle_pere = u.union_mari and i.cle_mere = u.union_femme
  left join EVENEMENTS_IND d on i.cle_fiche = d.EV_IND_KLE_FICHE and d.EV_IND_TYPE = 'DEAT'
  inner join individu i2 on i2.cle_fiche=t.tq_descendant
  left join EVENEMENTS_IND n2 on i2.cle_fiche = n2.EV_IND_KLE_FICHE and n2.EV_IND_TYPE = 'BIRT'
  left join t_union u2 on i2.cle_pere = u2.union_mari and i2.cle_mere = u2.union_femme
  left join EVENEMENTS_IND d2 on i2.cle_fiche = d2.EV_IND_KLE_FICHE and d2.EV_IND_TYPE = 'DEAT')
where coalesce(ville_nais_anc,coalesce(ville_mar_anc,ville_dc_anc)) is not null
  and coalesce(ville_nais_desc,coalesce(ville_mar_desc,ville_dc_desc)) is not null
order by 1,2
Vous verrez que l'utilisation des jointures avec les tables "individu" la rend bien plus rapide que lorsque ces jointures sont définies par des filtres dans la clause WHERE (100ms au lieu de 1,5s chez moi). Firebird s'y retrouve sans doûte plus facilement pour définir un plan utilisant des champs indexés permettant de retrouver les enregistrements.
Par ailleurs l'utilisation de coalesce permet de simplifier l'écriture quand on teste si un champ est NULL.
Il reste un problème dans le cas de plusieurs événements MARR d'un même couple (civil puis religieux par exemple) car celà génère autant d'enregistrements. La solution consiste à sélectionner uniquement le premier mariage (select first 1 ev_fam_ville from t_union inner join evenements_fam on ev_fam_ville is not null and ... where ... order by ev_fam_year, ev_fam_mois, ev_fam_date) directement dans votre requête pour y remplacer ev_fam_ville. La table evenements_fam est alors à enlever de la clause FROM.

Concernant la documentation sur la base, je n'en connais pas. Par contre, dans toutes les procédures une présentation figure soit en commentaires en tête de la procédure, soit dans la description attachée à chaque procédure.
Malheureusement vous ne pouvez y accéder avec le BOA.
Je pense que le BOA est un très bon outil principalement pour les fonctions qu'il intègre, mais pour faire du SQL (comme çà a l'air de vous intéresser), il est préférable d'installer Firebird serveur sur votre PC et de vous équiper d'outils gratuits comme IBOConsole ou IBExpert.
Si vous êtes intéressée, le point de départ dans les tutoriels de Christophe est http://www.ancestrologie.org/forum/index.php?topic=8070.msg53048#msg53048

A+
André

Réédition: Requête modifiée. Pour éviter l'édition multiple due à plusieurs mariages, et ne pas calculer plusieurs fois la première ville de mariage, j'ai mis ce calcul dans une sous-requête. Les réponses avec villes nulles car inconnues sont éliminées.
Vous remarquerez aussi que j'ai remplacée le code de votre individu de départ par :indi . Dans des outils comme IBOConsole ou IBExpert, l'exécution d'une telle requête fait apparaître un tableau de variables à complèter, les variables étant identifiées dans le code en les précédant du symbole ":", façon de rendre une requête plus conviviale. Dans le BOA il faut remplacer :variable par la valeur réelle.
« Modifié: 22 Août 2008 à 10:29:25 par DDdeBerdeux »
Une application pleinement satisfaisante est toujours complétée par une mise à jour buggée. (Loi des Mises à Jour)
 

Hors ligne emilise

  • Expert
  • ****
  • Messages: 104
Retrouver l'ascendance
« Réponse #6 le: 22 Août 2008 à 18:46:11 »
Que d'infos, que d'infos!!! Je me suis empressée d'installer la version serveur de Firebird, c'est tout simplement génial! Je ne savais pas que ça existait. Je me trouvais toujours limitée par le BOA, j'ai l'impression que je vais découvrir plein de choses  :o

Merci beaucoup.
Émilise - Ancestro v 1352