1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
| BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "INDIVIDU" (
"CLE_FICHE" INTEGER NOT NULL UNIQUE,
"KLE_DOSSIER" Integer,
"CLE_PERE" Integer,
"CLE_MERE" Integer,
"PREFIXE" Varchar(30),
"NOM" Varchar(40) NOT NULL,
"PRENOM" Varchar(60),
"SURNOM" Varchar(120),
"SUFFIXE" Varchar(30),
"SEXE" Integer,
"DATE_NAISSANCE" Date,
"ANNEE_NAISSANCE" Integer,
"DATE_DECES" Date,
"ANNEE_DECES" Integer,
"AGE_AU_DECES" Integer,
"SOURCE" Blob,
"COMMENT" Blob,
"NUM_SOSA" Double precision,
"NCHI" Smallint,
"NMR" Smallint,
"IND_CONFIDENTIEL" Smallint,
"DATE_CREATION" Timestamp,
"CREE_PAR_QUI" Varchar(30),
"DATE_MODIF" Timestamp,
"MODIF_PAR_QUI" Varchar(30),
"INDI_PHOTO" Blob,
PRIMARY KEY("CLE_FICHE" AUTOINCREMENT)
);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (1,NULL,3,4,NULL,'CAZAUX','Michel',NULL,NULL,NULL,'15/08/1947',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (2,NULL,3,4,NULL,'CAZAUX','Philippe',NULL,NULL,NULL,'02/08/1949',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'');
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (3,NULL,7,NULL,NULL,'CAZAUX','Gerard',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (4,NULL,9,10,NULL,'PUIBARAUD','Monique',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (5,NULL,NULL,4,NULL,'LOOS','SYLVIE',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (6,NULL,2,8,NULL,'CAZAUX','Kevin',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (7,NULL,NULL,NULL,NULL,'CAZAUX','Alexandre',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (8,NULL,NULL,NULL,NULL,'JACQUES','Francoise',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (9,NULL,11,12,NULL,'PUIBARAUD','Edgard',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (10,NULL,NULL,NULL,NULL,'ARTHAPIGNET','Marie',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (11,NULL,NULL,NULL,NULL,'PUIBARAUD','Hipolytte',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "INDIVIDU" ("CLE_FICHE","KLE_DOSSIER","CLE_PERE","CLE_MERE","PREFIXE","NOM","PRENOM","SURNOM","SUFFIXE","SEXE","DATE_NAISSANCE","ANNEE_NAISSANCE","DATE_DECES","ANNEE_DECES","AGE_AU_DECES","SOURCE","COMMENT","NUM_SOSA","NCHI","NMR","IND_CONFIDENTIEL","DATE_CREATION","CREE_PAR_QUI","DATE_MODIF","MODIF_PAR_QUI","INDI_PHOTO") VALUES (12,NULL,NULL,NULL,NULL,'ABADIE','Julia',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
CREATE INDEX IF NOT EXISTS "NOM_INDIVIDU" ON "INDIVIDU" (
"NOM"
);
CREATE VIEW LISTE_INDI_PARENTS AS
select
t.nom as nom_individu,
t.prenom as prenom_individu,
t.date_naissance as ne_individu,
t_pere.nom as nom_pere,
t_pere.prenom as prenom_pere,
t_mere.nom as nom_mere,
t_mere.prenom as prenom_mere
from INDIVIDU t
left join INDIVIDU t_pere on t_pere.cle_fiche = t.cle_pere
left join INDIVIDU t_mere on t_mere.cle_fiche = t.cle_mere;
COMMIT; |