[R] SQLite_Exec UPDATE et DELETE

[aulus]

Bonjour,

J'ai tenté les deux fonctions ci-dessous pour mettre à jour une ligne d'une base SQLite et pour supprimer une ligne. Aucune ne fonctionne. Sauriez-vous me dire si mes codes sont fautifs :

Table :

Code : Tout sélectionner

_SQLite_Exec ($Base_SQLite, "CREATE TABLE  liste (numero, nom, prenom, date, PereNom, PerePrenom, MereNom, MerePrenom, Type);")
 

1. UPDATE d'une ligne de la table "liste", ligne indexée $No :

Code : Tout sélectionner

Local $split = StringSplit($ligne, "|")
Local $array_ligne[$split[0]+1]
$array_ligne[0] = $split[0]
$array_ligne[1] = $split[1]
$array_ligne[2] = $split[2]
$array_ligne[3] = $split[3]
$array_ligne[4] = $split[4] & " (" & $split[5] & " " & $split[6] & ")"
$array_ligne[5] = $split[7]
$array_ligne[6] = $split[8]
$array_ligne[7] = $split[9]
$array_ligne[8] = $split[10]
$array_ligne[9] = $split[11]

_SQLite_Exec($Base_SQLite, "UPDATE liste  SET numero =  $array_ligne[1], nom = $array_ligne[2], prenom = $array_ligne[3], date =$array_ligne[4], PereNom = $array_ligne[5], PerePrenom = $array_ligne[6], MereNom = $array_ligne[7], MerePrenom = $array_ligne[8], Type = $array_ligne[9] WHERE numero = $No;")
 

2. DELETE d'une ligne de la table "liste", ligne indexée $No :

Code : Tout sélectionner

_SQLite_Exec($Base_SQLite, "DELETE FROM liste WHERE numero = $No;")
 

Une fonction dont je ne comprends pas l'effet. Le commentaire en anglais proposée dans l'aide m'échappe. Cette fonction doit-elle être utilisée dans le cas d'une UPDATE et d'un DELETE ?

Code : Tout sélectionner

_SQLite_FastEscape()
 

Grand merci.

[jchd]

Ces petits détails ne sont pas vraiment spécifiques à SQLite. Tu envoies au moteur de BDD une commande complète qui est une chaîne de caractères. Dans cette commande tu dois souvent insérer des variables ou plutôt leur contenu. Mettre $nom dans une chaîne ne fait que faire figurer les caractères $, n, o et m dans cette chaîne.

Par ailleurs, un litéral texte en SQL doit figurer entre simples quotes.

Si tu veux insérer le contenu de ta variable $nom, tu dois donc construire ta requête ainsi, en simplifiant ton exemple :
_SQLite_Exec($hDb, "update liste set nom = '" & $nom & "' where numero = " & $numero)

Tu remarqueras que la chaîne $nom doit impérativement être entre simples quotes pour être comprise comme un litéral. Le moteur reçoit donc, par exemple :
update liste set nom = 'Tartempion' where numero = 178

Mais une complication survient si ta chaîne $nom contient un ou plusieurs simple(s) quote(s) significatif(s). Si le gugusse en question s'appelle O'Neil le moteur recevrait :
update liste set nom = 'O'Neil' where numero = 178
Ceci provoquerait une erreur de syntaxe dans le meilleur des cas, car le litéral serait vu comme O suivi d'un élément invalide Neil'.

Pour pallier cette difficulté, on "échappe" toute apostrophe (simple quote) à l'intérieur de tout litéral texte pouvant un jour ou l'autre contenir ce caractère. L'échappement s'effectue en doublant le caractère. On doit ainsi envoyer :
update liste set nom = 'O''Neil' where numero = 178

D'où l'utilité de la fonction _SQLite_FastEscape qui fait ce travail. _SQLite_FastEscape("O'Neil") renvoie "O''Neil" et SQLite insèrera la chaîne O'Neil (en dédoublant l'apostrophe échappée).

Ne pas utiliser d'apostrophes autour d'une valeur numérique si la colonne est destinée à recevoir du numérique, car 1 est distinct de '1' et 3.1415926 différent de "3.1415926".

Aussi il est préférable de déclarer des types pour chaque colonne, bien que SQLite utilise un typage dynamique qui permet de stocker n'importe quel type dans à peu prêt toutes les colonnes.

Il y a plus à dire sur ces points, mais je simplifie pour ne pas te noyer dans des détails techniques.

Par ailleurs, tu peux parfaitement insérer directement des expressions dans la requête, pas besoin de passer par un tableau intermédiaire.

Ainsi ton exemple deviendrait :

Code : Tout sélectionner

_SQLite_Exec ($Base_SQLite, "CREATE TABLE  liste (numero integer not null primary key, nom text, prenom text, date text, PereNom text, PerePrenomtext, MereNom text, MerePrenom text, Type);")

Je suppose que numero est numérique (devrait certainement être autoincrement) mais je ne sais pas ce qu'est type...

Code : Tout sélectionner

Local $split = StringSplit($ligne, "|")

_SQLite_Exec($Base_SQLite, _
    "UPDATE liste SET " & _
        "numero =  " & _SQLite_FastEscape($split[1]) & ", " & _   ; ??? tu mets à jour le numero ? Et toutes les colonnes aussi ???
        "nom = " & _SQLite_FastEscape($split[2]) & ", " & _
        "prenom = " & _SQLite_FastEscape($split[3]) & ", " & _
        "date = ' (" & $split[5] & " " & $split[6] & ")', " & _   ; je suppose que les dates sans apostrophe, en format texte (e.g. 2013-06-13) et que l'espace avant la parenthèse ouvrante est voulu (j'en doute).
        "PereNom = " & _SQLite_FastEscape($split[7]) & ", " & _
        "PerePrenom = " & _SQLite_FastEscape($split[8]) & ", " & _
        "MereNom = " & _SQLite_FastEscape($split[9]) & ", " & _
        "MerePrenom = " & _SQLite_FastEscape($split[10]) & ", " & _
        "Type = '" & $split[11] & "' " & _ ; là aussi, je suppose que type est texte et sans apostrophe
    "WHERE " & _
        "numero = " & $No & _
    ";")
 

Cet UPDATE de l'ensemble des colonnes me semble suspicieux : on peut ne changer que les colonnes souhaitées !

C'est pareil pour DELETE :

Code : Tout sélectionner

_SQLite_Exec($Base_SQLite, "DELETE FROM liste WHERE numero = " & $No & ";")

Si j'ai commis une bévue en claviottant au vol ce ne sera pas pour m'étonner...

[jchd]

Code : Tout sélectionner

_SQLite_Exec(-1, "DELETE FROM liste WHERE numero = '" & $No & "';")

Là, j'ai un doute justement sur la colonne numero qui me semble plus numérique qu'alpha.

[mikell]

Hum j'avais supprimé mon message précédent qui n'avait plus de raison d'être
Mais dans mon tout 1er script d'initiation à sqlite j'avais quoté le n° sans problème (coup de chance probable ^^)

[jchd]

query: select 'different' where 1 != '1'
result: different

Ton truc fonctionne si tous les numeros sont en texte, mais alors le tri diverge s'il n'y a pas les zéros de tête requis...

[aulus]

Grand merci, j'y vois plus clair...

Type est alpha, tandis que numero est numérique.
Je note le rôle de ces quotes et l'utilité de _SQLite_FastEscape.
Ne sachant pas quel(s) champ(s) pourra(ont) être modifié(s) par l'utilisateur, j'ai trouvé plus simple de mettre à jour tous les champs, ceux-ci n'étant pas nombreux, plutôt que faire un test IF sur chaque champ.

Je me replonge dans mon code...

[mikell]

Ne sachant pas quel(s) champ(s) pourra(ont) être modifié(s) par l'utilisateur...

Tu ne le sais pas mais le script peut le savoir
Tout dépend de la manière dont l'utilisateur introduit la modification, le script peut très bien n'updater que le champ concerné
Changer le n° d'identification de la ligne n'est pas un bon plan dans la mesure où ce n° étant unique dans la table c'est la seule référence sûre et fixe pour la ligne

[aulus]

Effectivement, numero et type ne peuvent être modifiés par l'utilisateur. Ces deux champs sont gérés par le programme. Je les soustrais de la fonction.

Après avoir lutté plus d'une heure avec les guillemets et les quotes, je suis parvenu à faire fonctionner les fonctions.

Merci encore et encore...

[jchd]

Attends un peu, tu es loin d'avoir tout vu.

[mikell]

D'autant plus que si la modif concerne par exemple le nom d'un ascendant et que ledit a plusieurs enfants voire lui-même des ascendants, ça commence à sentir fort la Foreign Key

En attendant voilà quelques idées pour modifications 'live'

► Afficher le texte

Code : Tout sélectionner

#include <WindowsConstants.au3>
#include <GuiConstantsEx.au3>
#include <GuiListView.au3>
#include <SQLite.au3>
;#include <SQLite.dll.au3>
#include <Array.au3>
Opt("GuiOnEventMode", 1)


; initialisation =============================

Global $details_gui, $details_LV, $modif_input, $modif_btn, $delete_btn
Global $modif_col, $numero, $main_item, $lastclicked = 0, $var = 0
Global $base = @ScriptDir & "\genea5.sqlite"
Local $array, $iRows, $iColumns, $iRval, $hQuery
_SQLite_Startup ()
_SQLite_Open($base)

;==================== création de la base =====================

If FileRead($base) = "" Then
   SplashTextOn ("", "Création de la base...", 280, 55, -1, -1, 49)

   _SQLite_Exec (-1, "CREATE TABLE IF NOT EXISTS liste " & _
            "(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " & _ 
            "date VARCHAR(16), inc1, inc2, inc3, " & _ 
            "nom VARCHAR(30), prenom VARCHAR(20), sexe CHAR(1), inc4, " & _
            "nompapa VARCHAR(30), prenompapa VARCHAR(20), infopapa, inc5, " & _ 
            "nommaman VARCHAR(30), prenommaman VARCHAR(20), inc6, inc7, " & _ 
            "inc8, inc9, inc10, inc11, preinc VARCHAR(20), info1, info2, inc12, inc13);")

   $file = FileOpen(@ScriptDir & "\genea.txt", 0)    ; fichier texte
   _SQLite_Exec(-1, "Begin;")
   While 1
       $line = FileReadLine($file)
       If @error = -1 Then ExitLoop
       $a = _TriLigne($line)
       $string = ""
       For $i = 1 to $a[0]-1
              $string &= _SQLite_FastEscape($a[$i]) & ", "
       Next
       $string &= _SQLite_FastEscape($a[$a[0]])

     _SQLite_Exec(-1, "INSERT INTO liste (date, inc1, inc2, inc3, nom, prenom, sexe, inc4, " & _
            "nompapa, prenompapa, infopapa, inc5, nommaman, prenommaman, inc6, inc7, " & _
            "inc8, inc9, inc10, inc11, preinc, info1, info2, inc12, inc13) VALUES (" & $string & ");")
   WEnd
   _SQLite_Exec(-1, "Commit;")
   FileClose($file)
   SplashOff()
EndIf

;================ chargement ==============================

SplashTextOn ("", "Chargement ...", 280, 55, -1, -1, 49)

$gui = GuiCreate("genea", 600, 600)
GuiSetOnEvent(-3, "_Exit")
$listview = GuiCtrlCreateListview("n°|date|nom|papa|maman", 5, 5, 590, 550, -1, _
    BitOR($LVS_EX_FULLROWSELECT, $LVS_EX_GRIDLINES, $WS_EX_CLIENTEDGE))
GUICtrlSendMsg($listview, $LVM_SETCOLUMNWIDTH, 0, 40)

_SQLite_GetTable2d (-1, "SELECT id || '|' || date || '|' || nom || ' ' || prenom || '|' || nompapa || ' ' || prenompapa || '|' || " & _
                    "nommaman || ' ' || prenommaman FROM liste;", $array, $iRows, $iColumns)
For $i = 1 to UBound($array)-1
    GUICtrlCreateListViewItem($array[$i][0], $listview)
Next
$menu  = GUICtrlCreateContextMenu($listview) 
$delete_menu  = GUICtrlCreateMenuItem("Supprimer", $menu) 
GuiCtrlSetOnEvent(-1, "_Modif")
$modif_menu  = GUICtrlCreateMenuItem("Modifer", $menu) 
GuiCtrlSetOnEvent(-1, "_Modif")

SplashOff()
GuiSetState()

GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY")

While 1
  Sleep(10)
Wend


;=====================================================

Func WM_NOTIFY($hWnd, $iMsg, $iwParam, $ilParam)
    Local $tNMHDR, $hWndFrom, $iCode
    $tNMHDR = DllStructCreate($tagNMHDR, $ilParam)
    $hWndFrom = DllStructGetData($tNMHDR, "hWndFrom")
    $iCode = DllStructGetData($tNMHDR, "Code")
    $hListView = GuiCtrlGetHandle($listView)
    $hdetails_LV = GuiCtrlGetHandle($details_LV)

    Switch $hWndFrom
        Case $hListView
            Switch $iCode
              Case $LVN_COLUMNCLICK
                   $tInfo = DllStructCreate($tagNMLISTVIEW, $ilParam)
                   $colonne = DllStructGetData($tInfo, "SubItem")
                   If $colonne = $lastclicked Then
                        $var = not $var
                   Else
                        $var = 0
                   EndIf
                   $lastclicked = $colonne
                   _LVSort($colonne, $var)
              Case $NM_DBLCLK 
                   If $details_gui <> "" Then GuiDelete($details_gui)
                   Local $tInfo = DllStructCreate($tagNMITEMACTIVATE, $ilParam)
                   $main_item = DllStructGetData($tInfo, "Index")
                   Local $name = ControlListView($gui, "", $listview, "GetText", $main_item, 2)
                   $numero = ControlListView($gui, "", $listview, "GetText", $main_item, 0)
                 _ShowData($numero, $name)
           EndSwitch
        Case $hdetails_LV
            Switch $iCode
              Case $NM_CLICK
                   Local $tInfo = DllStructCreate($tagNMITEMACTIVATE, $ilParam)
                   Local $item = DllStructGetData($tInfo, "Index")
                   If $item = 0 Then 
                        GUICtrlSetState($modif_input, $GUI_DISABLE)
                        GUICtrlSetState($modif_btn, $GUI_DISABLE)
                        Return
                   Else
                        GUICtrlSetState($modif_input, $GUI_ENABLE)
                        GUICtrlSetState($modif_btn, $GUI_ENABLE)
                   EndIf
                   Local $subitem_text = ControlListView($details_gui, "", $details_LV, "GetText", $item, 1)
                   $modif_col = ControlListView($details_gui, "", $details_LV, "GetText", $item, 0)
                   GuiCtrlSetData($modif_input, $subitem_text)
           EndSwitch
    EndSwitch
    Return $GUI_RUNDEFMSG
EndFunc   ;==>WM_NOTIFY



Func _LVSort($nColumn, $var)
  Local $ar_tri, $iRows, $iColumns
  Local $sort[2] = ["ASC", "DESC"]
  Local $sens = $sort[int($var)]
Switch $nColumn
   Case 0
      $pattern = "id " & $sens
   Case 1
      $pattern = "date " & $sens
   Case 2
      $pattern = "nom " & $sens & ", prenom " & $sens
   Case 3
      $pattern = "nompapa " & $sens & ", prenompapa " & $sens
   Case 4
      $pattern = "nommaman " & $sens & ", prenommaman " & $sens
EndSwitch
  SplashTextOn ("", "Chargement ...", 280, 55, -1, -1, 49)
  Sleep(10)
 _SQLite_GetTable2d (-1, "SELECT id || '|' || date || '|' || nom || ' ' || prenom || '|' || nompapa || ' ' || prenompapa || '|' || " & _
                    "nommaman || ' ' || prenommaman FROM liste " & _
                    "ORDER BY " & $pattern & ";", $ar_tri, $iRows, $iColumns)
  _GUICtrlListView_DeleteAllItems($listview)
  For $i = 1 to UBound($ar_tri)-1
    GUICtrlCreateListViewItem($ar_tri[$i][0], $listview)
  Next
  SplashOff()
EndFunc


Func _ShowData($numero, $name)
   Local $aResult, $hQuery, $iRows, $iColumns
   _SQLite_GetTable(-1, "SELECT * FROM liste WHERE id='" & $numero &"';", $aResult, $iRows, $iColumns)
   Local $n = (UBound($aResult)-1)/2

   $details_gui = GuiCreate($name, 300, 360)
   GuiSetOnEvent(-3, "_Close", $details_gui)
   $details_LV = GuiCtrlCreateListview("col|details", 10, 10, 280, 280, -1, _
             BitOR($LVS_EX_FULLROWSELECT, $LVS_EX_GRIDLINES, $WS_EX_CLIENTEDGE))
   GUICtrlSendMsg($details_LV, $LVM_SETCOLUMNWIDTH, 0, 90)
   GUICtrlSendMsg($details_LV, $LVM_SETCOLUMNWIDTH, 1, 160)
   For $i = 1 to $n
     GUICtrlCreateListViewItem($aResult[$i] &"|"& $aResult[$i+$n], $details_LV)
   Next
   $modif_input = GuiCtrlCreateInput("", 10, 300, 200, 20)
   $modif_btn = GuiCtrlCreateButton("Modifier", 220, 300, 70, 20)
   GuiCtrlSetOnEvent(-1, "_Modif")
   $delete_btn = GuiCtrlCreateButton("Supprimer la fiche n° " & $numero & " - " & $name, 5, 330, 290, 20)
   GuiCtrlSetOnEvent(-1, "_Modif")
   GuiSetState()
EndFunc


Func _Modif()
Switch @GUI_CtrlId
  Case $modif_btn
     Local $hQuery, $rows, $new_subitem_text = GuiCtrlRead($modif_input)
     GUICtrlSetData(GuiCtrlRead($details_LV), "|" & $new_subitem_text) 
     _SQLite_Exec(-1, "UPDATE liste SET " & $modif_col & "=" & _SQLite_FastEscape($new_subitem_text) & _
                               " WHERE id=" & $numero & ";")
    _SQlite_Query (-1, "SELECT  id || '|' || date || '|' || nom || ' ' || prenom || '|' || nompapa || ' ' || prenompapa || '|' || " & _
                    "nommaman || ' ' || prenommaman  FROM liste WHERE id=" & $numero & ";", $hQuery)
    _SQLite_FetchData ($hQuery, $rows)
    _SQLite_QueryFinalize($hQuery)
    GUICtrlSetData(GuiCtrlRead($listview), $rows[0]) 
    $new_nom = ControlListView($details_gui, "", $details_LV, "GetText", 5, 1)
    $new_prenom = ControlListView($details_gui, "", $details_LV, "GetText", 6, 1)
    WinSetTitle($details_gui, "", $new_nom & " " & $new_prenom)
 Case $modif_menu
     Local $selected = ControlListView($gui, "", $listview, "GetSelected") 
     Local $name = ControlListView($gui, "", $listview, "GetText", $selected, 2)
     $numero = ControlListView($gui, "", $listview, "GetText", $selected, 0)
     _ShowData($numero, $name)
 Case $delete_btn
     _SQLite_Exec(-1, "DELETE FROM liste WHERE id=" & $numero & ";")
     _GUICtrlListView_DeleteItem($listview, $main_item)
     _GUICtrlListView_SetItemFocused($listview, $main_item, false)
     GuiDelete($details_gui)
 Case $delete_menu
     Local $selected = ControlListView($gui, "", $listview, "GetSelected") 
     $numero = Int(ControlListView($gui, "", $listview, "GetText", $selected, 0))
     _SQLite_Exec(-1, "DELETE FROM liste WHERE id=" & $numero & ";")
     _GUICtrlListView_DeleteItem($listview, $selected)
     _GUICtrlListView_SetItemFocused($listview, $selected, false)
EndSwitch
EndFunc


Func _TriLigne($ligne)
   $ligne = StringRegExpReplace($ligne, "/{2,}", "/")
   Local $split = StringSplit($ligne, "|")
   Local $date = StringSplit($split[1], "/")
   $split[1] = $date[3] & " (" & $date[2] & " " & $date[1] & ")"     ; date2
   Return $split  
EndFunc


Func _Exit()
  _SQLite_Close ()
  _SQLite_Shutdown ()
  Exit
EndFunc


Func _Close()
   GuiDelete(@GUI_WinHandle)
EndFunc

[jchd]

Pour ce faire il faut trouver un moyen fiable de résoudre les homonymes, surtout dans les ascendants. Passer de toutes façon par une table temporaire me semble impératif. On peut alors y faire toute recherche de cohérence sur les dates (de naissance, je suppose ?), les parentés, etc. Faire ça dans du texte est ingérable alors que SQL est bien adapté, même s'il faut un peu ruser pour lever les ambiguïtés.

[aulus]

Le dernier code de Mikell ne fonctionne pas sans #include <SQLite.dll.au3> .
Les fonctions __SQLite_Inline_Version() et __SQLite_Inline_Modified() sont déclarées indéfinies.
Ces erreurs disparaissent si je rétablis l'include, mais... le tableau reste blanc comme neige, quoique le fichier sqlite3.dll se trouve bien et dans le dossier windows/system32 et dans le dossier windows/sysWOW64 .

[mikell]

Tiens
Pourtant fondamentalement le script est le même que le précédent, du moins pour ce qui concerne sqlite
Le tableau qui reste blanc, a priori c'est la base qui ne se remplit pas
Peut-être une bête histoire de chemin de fichier ?

[aulus]

Bonjour Mikell,

Conernant l' #include <SQLite.dll.au3>, il me faut le décommenter : je confirme.
Concernant le tableau blanc, j'avais omis de modifier le nom de mon fichier TXT conformément à la déclaration dans votre code.

Donc tout marche désormais. Je suis ébloui par l'efficacité des codes "Modifier" et "Supprimer".

[mikell]

C'est surtout de la gestion de listview, en fait
L'idée est de simplifier au max les manipulations à faire par l'utilisateur, c'est pour ça que j'ai laissé les 2 accès possibles aux fonctionnalités
Mais le code peut encore être optimisé, par exemple le double-clic qui pourrait maintenant servir à autre chose, etc

L'étape suivante est de tenir compte des remarques de jchd, par exemple si tu modifies un paramètre pour un individu, il faudrait que cette modification puisse se faire automatiquement dans toutes les autres lignes où est mentionné l'individu, et c'est là que la gestion des homonymes devient pointue

[jchd]

Aulus a bien raison pour l'include. J'oublie sans cesse que j'utilise une version modifiée de SQLite.au3 qui ne pratique pas la mise à jour au vol de la DLL via Internet. J'ai toujours la dernière version de cette DLL à un endroit précis, donc commun à mes applis. Ceci dit cet environnement n'est pas pour diffusion car un brin particulier.

Plates excuses.

[mikell]

@jchd
ça y est, je cale complet sur le fond du problème que tu as déjà évoqué plusieurs fois... ^^

Admettons :
La table a 5 colonnes : id, date, enfant, père, mère
Il y a 2 pères qui s'appellent DUVAL Jean (ce sont des homonymes collatéraux) et qui ont chacun plusieurs enfants
La typo médiévale étant ce qu'elle est, l'un des 2 doit être renommé en DUVAL Alain
La correction devrait donc être effectuée dans toutes les lignes de la table correspondant aux enfants d'Alain
Existe-t-il un moyen de réaliser ça, sachant qu'il faudrait que ça puisse fonctionner aussi en cas de correction d'un nom de mère dans des circonstances comparables ?
Il n'est pas impossible d'ailleurs que Alain se soit remarié 200 lignes de table plus loin ...

[jchd]

C'est bien pour ça que la généalogie n'a pas toujours ce côté évident qu'on peut lui prêter de l'extérieur.

Ici pour rester simple le hiatus principal peut se produire sur les homonymes. Les noms et prénoms du géniteur et de la génitrice (celui-ci étant plus facilement traçable que celui-là) ne sont pas des références précises et posent un problème délicat s'il y a deux ou plusieurs DUVAL Alain dans l'ensemble constitué du fichier source et de la base existante.
Les dates de naissance des descendants peuvent éventuellement lever l'ambiguïté mais ce n'est pas toujours le cas général. C'est pourquoi je recommande vivement de partir sur des bases plus saines et extensibles en adoptant un schéma de ce genre pour la table définitive :

Code : Tout sélectionner

CREATE TABLE "Liste" (
  "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  "Nom" CHAR NOT NULL COLLATE NOCASE, 
  "Prénom" CHAR NOT NULL COLLATE NOCASE DEFAULT '', 
  "Sexe" CHAR(1) NOT NULL, 
  "Naissance" CHAR(10) NOT NULL, 
  "Décès" CHAR(10) NOT NULL, 
  "Type" CHAR(1) NOT NULL, 
  "PèreId" INTEGER CONSTRAINT "fkPère" REFERENCES "Liste"("Id") ON DELETE RESTRICT ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED DEFAULT (-1), 
  "MèreId" INTEGER CONSTRAINT "fkMère" REFERENCES "Liste"("Id") ON DELETE RESTRICT ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED DEFAULT (-2), 
  CONSTRAINT "ckSexe" CHECK("Sexe" in ('M', 'F')));

CREATE TRIGGER "delIdHomme"
BEFORE DELETE
ON "Liste"
FOR EACH ROW
WHEN old.Sexe = 'M'
BEGIN
     update Liste set PèreId = -1 where PèreId = old.Id;
END;

CREATE TRIGGER "delIdFemme"
BEFORE DELETE
ON "Liste"
FOR EACH ROW
WHEN old.Sexe = 'F'
BEGIN
     update Liste set MèreId = -2 where MèreId = old.Id;     
END;
 

On peut peupler cette table en laissant les Ids père et mère à leur valeur par défaut. Il faut bien évidemment avoir déjà créé les pères et mères inconnus dans une transaction :

Code : Tout sélectionner

Begin;
insert into Liste (id, nom, prénom, naissance, décès, type, pèreid, mèreid) values (-2, '- inconnue -', '', '', '', '?', -1, -2);
insert into Liste (id, nom, prénom, naissance, décès, type, pèreid, mèreid) values (-1, '- inconnu -', '', '', '', '?', -1, -2);
commit;

Ensuite on repasse dans le source pour identifier les ids des ascendants père et mère. Il est plus facile d'ajouter une colonne (ou plusieurs) à la table source pour indiquer la progression de ce dernier traitement.

Les triggers permettent de positionner à "inconnu" le père où la mère des descendants dans le cas de la suppression d'un ascendant direct. D'où la clause "on delete restrict" plutôt que cascade (on supprimerait tous les descendants !). On update cascade est correct : si l'on doit changer d'ID d'une personne, ses descendants doivent voir le même changement côté pèreId ou mèreId.

NB1 : tout le SQL dans les balises de code peut être scotché directement dans un onglet "SQL" de SQLite Expert puis F5. Pas la peine d'écrire une seule ligne de code pour ça. On peut ainsi jouer illico avec la base.

NB2 (argh !) : j'oublie aussi systématiquement que les clés étrangères ne sont pas actives par défaut. Pour que les contraintes qu'elles imposent prennent effet, il faut impérativement faire :

Code : Tout sélectionner

_SQLite_Exec($hdb, "pragma foreign_keys = 1;")

juste après l'établissement de chaque connection.

[jchd]

Effectivement, numero et type ne peuvent être modifiés par l'utilisateur. Ces deux champs sont gérés par le programme. Je les soustrais de la fonction.

Ce que tu dis est peut-être valable pour Type (bien que j'ignore la sémantique que tu lui rattaches). En tout cas pour numero ce n'est pas du tout le bon choix. Ce n'est pas à une application de gérer ça : SQL le fait pour toi automagiquement et sans aucun risque d'erreur alors je ne vois pas de raison valable de se mélanger les pinceaux à se gérer soi-même cet identifiant qui n'a de signification que technique et interne à la base.

Rappelons-nous le Basic primitif où l'on devait s'amuser à gérer en clair les numéros de ligne :
125 gosub 320

[aulus]

Existe-t-il un moyen de réaliser ça, sachant qu'il faudrait que ça puisse fonctionner aussi en cas de correction d'un nom de mère dans des circonstances comparables ?
Il n'est pas impossible d'ailleurs que Alain se soit remarié 200 lignes de table plus loin ...

L'association se limite à relever chaque acte des registres le plus fidèlement possible, respectant l'orthographe et les erreurs des clercs et autres scribes. Eventuellement, le dépouilleur indique en commentaire, dans le champ prévu à cet effet, son sentiment, ses doutes, etc. à propos du relevé.
Dans le cas d'un prénom illisible dans un acte, puis lisible dans un autre, le dépouilleur corrige manuellement chaque relevé. Se fier à la machine serait trop téméraire. Il lance une recherche sur le nom incriminé, vérifie s'il s'agit bien de la même personne, puis corrige manuellement... Relever un registre, c'est des années de patience...

Ce que tu dis est peut-être valable pour Type (bien que j'ignore la sémantique que tu lui rattaches). En tout cas pour numero ce n'est pas du tout le bon choix. Ce n'est pas à une application de gérer ça : SQL le fait pour toi automagiquement et sans aucun risque d'erreur alors je ne vois pas de raison valable de se mélanger les pinceaux à se gérer soi-même cet identifiant qui n'a de signification que technique et interne à la base.

Le champ "type" contient le type de registre dépouillé.
Le choix de confier à mon programme la gestion du numéro s'est fait parce que je ne connais pas le comportement de SQLite dans certaines manipulations de fichiers. Par exemple : que se passe-t-il dans le cas d'une concaténation de plusieurs fichiers dont les enregistrements portent des numéros identiques ? Etant trop ignorant, j'ai suivi le principe : "Dans le doute, abstiens-toi !"