﻿-- Ce script permet de regénérer complètement la base de données sorties.db
--
-- commandes : rm sorties.db ; sqlite3 sorties.db < sorties.sql

.bail ON
PRAGMA synchronous = OFF;       -- écritures rapides sur le disque

-- numéros de version de cette base de données
PRAGMA schema_version = 1;
PRAGMA user_version = 1;

--** création de la table android_metadata pour la base android
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE android_metadata (locale TEXT);
INSERT INTO "Android_metadata" VALUES('fr'); -- => "Android_metadata"._id=1
COMMIT;


-- activer les contraintes de type clé étrangère
PRAGMA foreign_keys = ON;

-- remarque sur les champs de type 6 : temps, durée, heure
-- select strftime('%s', temps, 'utc'); convertit le temps local en timestamp utc
-- ex: select strftime('%s', '00:00:00', 'utc'); donne 946681200 (2000/01/01 à 00:00)
-- select time(timestamp, 'unixepoch', 'localtime'); convertit un timestamp utc en temps local
-- ex: select time(946681200+121, 'unixepoch', 'localtime'); donne 00:02:01


--** création de la table des noms de champs **--
-- un enregistrement représente un nom de colonne décrivant un sport
BEGIN TRANSACTION;
CREATE TABLE Champs (
    _id integer primary key autoincrement,
    nom text not null,                          -- nom du champ
    type integer not null,                      -- type du champ
    unite text,                                 -- chaine qui donne l'unité, ex: km
    CONSTRAINT ChampsUniqueNom UNIQUE(nom)
);
COMMIT;

-- Notes sur les types de champs (Champs.type) :
-- type  1 = "Booléen"  -- vaut 0 ou 1
-- type  2 = "Note/5"   -- vaut 0..5
-- type  3 = "Nombre"   -- entier ou réel
-- type  4 = "Enum"     -- chaine de caractères parmi les éléments de unité, séparés par ';'
-- type  5 = "Ligne"    -- chaine de caractères libre, une seule ligne
-- type  6 = "Texte"    -- chaine de caractères libre de longueur importante
-- type  7 = "Lien"     -- chaine qui représente un URL
-- type  8 = "Date"     -- timestamp UTC
-- type  9 = "Temps"    -- nombre réel de secondes écoulées
-- type 10 = "Calcul"   -- l'unité contient une formule <PAS FAIT>
-- type 11 = "Gpx"      -- chaine qui représente un fichier GPX



--** création de la table Sports **--
-- un enregistrement représente une catégorie de sport
CREATE TABLE Sports (
    _id integer primary key autoincrement,
    nom text not null,                          -- nom du sport
    description text,                           -- description du sport
    couleur integer not null,                   -- couleur pour l'histogramme
    CONSTRAINT SportsUniqueNom UNIQUE(nom)
);


--** création de la table ChampsSports **--
-- elle indique qu'un champ doit être proposé à la saisie d'une sortie de ce sport
CREATE TABLE ChampsSports (
    _id integer primary key autoincrement,
    idsport integer not null,                   -- id du Sport
    idchamp integer not null,                   -- id du Champ
    ordre integer not null,                     -- position du champ dans les listes
    CONSTRAINT ChampsSportsFKidsport FOREIGN KEY(idsport) REFERENCES Sports(_id) ON DELETE CASCADE,
    CONSTRAINT ChampsSportsFKidchamp FOREIGN KEY(idchamp) REFERENCES Champs(_id) ON DELETE CASCADE,
    CONSTRAINT ChampsSportsUniqueIdsportIdchamp UNIQUE(idsport, idchamp),
    CONSTRAINT ChampsSportsUniqueIdsportOrdre UNIQUE(idsport, ordre)
);


--** création de la table TypesComposants **--
-- elle contient les différents types de composants constituant un équipement
BEGIN TRANSACTION;
CREATE TABLE TypesComposants (
    _id integer primary key autoincrement,
    nom text not null,                          -- dénomination du type de composant, ex: cassette
    description text,                           -- description du type pour préciser son emploi
    CONSTRAINT TypesComposantsUniqueNom UNIQUE(nom)
);


--** création de la table Composants **--
-- elle contient la totalité des pièces détachées qui sont utilisées ou ont été utilisées
CREATE TABLE Composants (
    _id integer primary key autoincrement,
    idtype integer not null,                    -- identifiant dans la table TypesComposants
    description text not null,                  -- description du composant, ex: ref, taille
    reference text,                             -- marque, modèle
    datemes numeric not null,                   -- date mise en service
    datedestr numeric,                          -- date de datedestr (ne sera plus proposé pour un remplacement)
    prix numeric,                               -- prix du composant
    CONSTRAINT ComposantsFKidtype FOREIGN KEY(idtype) REFERENCES TypesComposants(_id) ON DELETE CASCADE
);
COMMIT;


--** création de la table ComposantsUsure **--
-- elle contient quel(s) champ(s) indique(nt) l'usure d'un type de composant
-- par exemple : une chaine de vélo = distance parcourue, un diabolo = temps total
CREATE TABLE ComposantsUsure (
    _id integer primary key autoincrement,
    idtype integer not null,                    -- identifiant dans la table TypesComposants
    idchamp integer not null,                   -- identifiant dans la table Champs
    CONSTRAINT ComposantsUsureFKidtype FOREIGN KEY(idtype) REFERENCES TypesComposants(_id) ON DELETE CASCADE,
    CONSTRAINT ComposantsUsureFKidchamp FOREIGN KEY(idchamp) REFERENCES Champs(_id) ON DELETE CASCADE,
    CONSTRAINT ComposantsUsureUniqueIdtypeIdchamp UNIQUE(idtype, idchamp)
);


--** création de la table SelectionsMateriel **--
-- elle contient les différents matériels utilisés dans les sorties
BEGIN TRANSACTION;
CREATE TABLE Familles (
    _id integer primary key autoincrement,
    nom text not null,                          -- description ou résumé de la sélection de composants
    description text,                           -- commentaire au sujet de cette famille, ex : réglages, conditions d'emploi
    CONSTRAINT FamillesUniqueNom UNIQUE(nom)
);


--** création de la table SportsFamilles **--
-- elle indique quelles familles sont utilisées par un sport
CREATE TABLE SportsFamilles (
    _id integer primary key autoincrement,
    idsport integer not null,                   -- id du sport pour cette famille
    idfamille integer not null,                 -- id de la famille
    CONSTRAINT SportsFamillesFKidsport FOREIGN KEY(idsport) REFERENCES Sports(_id) ON DELETE CASCADE,
    CONSTRAINT SportsFamillesFKidfamille FOREIGN KEY(idfamille) REFERENCES Familles(_id) ON DELETE CASCADE,
    CONSTRAINT SportsFamillesUniqueIdsportIdfamille UNIQUE(idsport, idfamille)
);


--** création de la table SelectionsMateriel **--
-- une famille = groupe de types de composants, une sélection = groupe de composants précis
CREATE TABLE SelectionsMateriel (
    _id integer primary key autoincrement,
    idfamille integer not null,                 -- numéro de la famille (donne le nom de la sélection)
    idselparent integer,                        -- sélection initiale, modifiée par un changement de composant
    dateselmat numeric,                         -- date de définition de la sélection
    CONSTRAINT SelectionsMaterielFKidfamille FOREIGN KEY(idfamille) REFERENCES Familles(_id) ON DELETE CASCADE,
    CONSTRAINT SelectionsMaterielFKidselparent FOREIGN KEY(idselparent) REFERENCES SelectionsMateriel(_id) ON DELETE CASCADE,
    CONSTRAINT SelectionsMaterielUniqueIdselparent UNIQUE(idselparent)
);


--** création de la table SelMatComposants **--
-- elle indique que telle sélection contient tel composant
CREATE TABLE SelMatComposants (
    _id integer primary key autoincrement,
    idselection integer not null,               -- identifiant dans la table SelectionsMateriel
    idcomposant integer not null,               -- identifiant dans la table Composants
    CONSTRAINT SelMatComposantsFKidselection FOREIGN KEY(idselection) REFERENCES SelectionsMateriel(_id) ON DELETE CASCADE,
    CONSTRAINT SelMatComposantsFKidcomposant FOREIGN KEY(idcomposant) REFERENCES Composants(_id) ON DELETE CASCADE,
    CONSTRAINT SelMatComposantsUniqueIdselectionIdcomposant UNIQUE(idselection,idcomposant)
);
COMMIT;


--** création de la table Sorties **-
CREATE TABLE Sorties (
    _id integer primary key autoincrement,
    idsport integer not null,                   -- identifiant dans la table Sports du sport pratiqué
    date integer not null,                      -- date et heure de la sortie
    lieu text,                                  -- lieu de la sortie
    infos text not null,                        -- remarques sur la sortie
    note integer not null,                      -- note de 0 à 5 sur la sortie
    idselmat integer,                           -- identifiant dans la table SelectionsMateriel, null si inconnu
    CONSTRAINT SortiesFKidsport FOREIGN KEY(idsport) REFERENCES Sports(_id) ON DELETE CASCADE,
    CONSTRAINT SortiesFKidselmat FOREIGN KEY(idselmat) REFERENCES SelectionsMateriel(_id) ON DELETE CASCADE
);


--** création de la table Valeurs **--
-- elle donne la valeur d'un champ pour une sortie
CREATE TABLE Valeurs (
    _id integer primary key autoincrement,
    idsortie integer not null,                  -- identifiant de la sortie
    idchamp integer not null,                   -- identifiant du champ
    valeur numeric not null,                    -- valeur du champ (on utilise l'affinité des types)
    CONSTRAINT ValeursFKidsortie FOREIGN KEY(idsortie) REFERENCES Sorties(_id) ON DELETE CASCADE,
    CONSTRAINT ValeursFKidchamp FOREIGN KEY(idchamp) REFERENCES Champs(_id) ON DELETE CASCADE,
    CONSTRAINT ValeursUniqueIdsortieIdchamp UNIQUE(idsortie,idchamp)                    -- une seule valeur par type de champ et par sortie
);


--** création de la table Evenement **-
CREATE TABLE Evenements (
    _id integer primary key autoincrement,
    titre text not null,                        -- titre de l'événement
    date integer not null,                      -- date de l'événement
    description text not null,                  -- description de l'événement
    CONSTRAINT EvenementsUniqueTitreDate UNIQUE(titre,date)
);

--** création de la table Mesures **-
CREATE TABLE Mesures (
    _id integer primary key autoincrement,
    date integer not null,                      -- date de la mesure
    idchamp integer not null,                   -- identifiant dans la table Champs
    valeur numeric not null,                    -- valeur du champ (on utilise l'affinité des types)
    infos text,                                 -- commentaire sur la mesure
    CONSTRAINT MesuresFKidchamp FOREIGN KEY(idchamp) REFERENCES Champs(_id) ON DELETE CASCADE,
    CONSTRAINT MesuresUniqueDateIdchamp UNIQUE(date, idchamp)
);

--** création de la table Mesures **-
-- TODO pas au point : il faudrait pouvoir mentionner des mesures et des valeurs de sorties
CREATE TABLE Courbes (
    _id integer primary key autoincrement,
    titre text not null,                        -- nom de la courbe
    absisse text not null,                      -- formule pour les absisses, ex : date
    ordonnee text not null,                     -- formule pour les ordonnées, ex : Poids
    configuration text,                         -- réglages de la courbe
    CONSTRAINT CourbesUniqueTitre UNIQUE(titre)
);


------------------------------------------------------------------------

--** Création des vues nécessaires aux requêtes des programmes


--** Vue Selections = sélections et famille
CREATE VIEW SelectionsSports AS
    select SelectionsMateriel._id as _id,
        Familles._id as idfamille,
        Familles.nom as nomfamille,
        SportsFamilles.idsport as idsport,
        SelectionsMateriel.idselparent as idselparent,
        SelectionsMateriel.dateselmat as dateselmat
    from SelectionsMateriel,Familles,SportsFamilles
        on  Familles._id = SelectionsMateriel.idfamille
        and SportsFamilles.idfamille = Familles._id;


--** Vue Calendrier = sorties et événements (id, 'S' ou 'T' ou 'M', sport ou titre, date, lieu, date brute)
CREATE VIEW Calendrier AS
        select Sorties._id,"S",
            Sports.nom as nom,
            strftime('%d/%m/%Y %H:%M',Sorties.date,'unixepoch','localtime') as date,
            Sorties.lieu as lieu,
            Sorties.date as sdate
        from Sorties,Sports
            on Sports._id=Sorties.idsport
    union
        select Evenements._id,"E",
            Evenements.titre as nom,
            strftime('%d/%m/%Y %H:%M',Evenements.date,'unixepoch','localtime') as date,
            Evenements.description as lieu,
            Evenements.date as sdate
        from Evenements
    union
        select Mesures._id,"M",
            Champs.nom as nom,
            strftime('%d/%m/%Y %H:%M',Mesures.date,'unixepoch','localtime') as date,
            Mesures.valeur as lieu,
            Mesures.date as sdate
        from Mesures,Champs
            on Mesures.idchamp = Champs._id;


--** Vue SortiesSport = (Sorties.*,nomSport)
CREATE VIEW SortiesSport AS
    select Sorties.*,Sports._id as idSport,Sports.nom as nomSport,Sports.description as descSport
    from Sorties,Sports
        on Sports._id = Sorties.idsport;


--** Vue SortiesComposant = sorties d'un composant particulier
CREATE View SortiesComposants AS
    select Composants._id as idcomposant, Composants.idtype as idtype, Sorties._id as idsortie
    from Composants,SelMatComposants,Sorties
        on  Composants._id=SelMatComposants.idcomposant
        and Sorties.idselmat=SelMatComposants.idselection;


--** Vue ValeursUsureComposants = valeurs d'usure des composants
CREATE View ValeursUsureComposants AS
    select Valeurs.idchamp as _id, Champs.nom as nom, Champs.type as type, Champs.unite as unite, sum(valeur) as somme, SortiesComposants.idcomposant as idcomposant
    from SortiesComposants,Valeurs,ComposantsUsure,Champs
        on  Valeurs.idsortie=SortiesComposants.idsortie
        and ComposantsUsure.idtype=SortiesComposants.idtype
        and ComposantsUsure.idchamp=Valeurs.idchamp
        and Champs._id=Valeurs.idchamp
    group by SortiesComposants.idcomposant, Valeurs.idchamp;



--** Vue TypesComposantsDisponibles = liste des composants disponibles pour faire une sélection
CREATE VIEW TypesComposantsDisponibles AS
    select
        Composants._id as idcomposant,
        Composants.idtype as idtypecomp,
        TypesComposants.nom as nomtypecomp,
        SelectionsMateriel.idfamille as idfamille
    from TypesComposants,Composants
        on TypesComposants._id = Composants.idtype
        left outer join SelMatComposants
        on SelMatComposants.idcomposant = Composants._id
        left outer join SelectionsMateriel
        on SelectionsMateriel._id = SelMatComposants.idselection
    where Composants.datedestr is null;


--** Vue TypesComposantsSports = liste des types de composants utilisés dans un sport
CREATE VIEW TypesComposantsSports AS
    select distinct
        SportsFamilles.idsport as idsport,
        Familles._id as idfamille,
        Familles.nom as nomfamille,
        max(SelectionsMateriel._id) as idselmat,
        Composants._id as idcomposant,
        Composants.description as descrcomposant,
        Composants.reference as refcomposant,
        Composants.datemes as datemescomposant,
        Composants.prix as prixcomposant,
        TypesComposants._id as idtypecomp,
        TypesComposants.nom as nomtypecomp
    from SportsFamilles,Familles,SelectionsMateriel,SelMatComposants,Composants,TypesComposants
        on Familles._id = SelectionsMateriel.idfamille
        and SportsFamilles.idfamille = Familles._id
        and SelectionsMateriel._id = SelMatComposants.idselection
        and SelMatComposants.idcomposant = Composants._id
        and TypesComposants._id = Composants.idtype
    group by TypesComposants._id;

--** Vue ComposantsFamilles = liste des familles qui utilisent ce type de composant
CREATE VIEW TypesComposantsFamilles AS
    select distinct
        Familles._id as idfamille,
        Familles.nom as nomfamille,
        Composants.idtype as idtypecomposant
    from Composants,SelMatComposants,SelectionsMateriel,Familles
        on  Composants._id=SelMatComposants.idcomposant
        and SelectionsMateriel._id=SelMatComposants.idselection
        and Familles._id=SelectionsMateriel.idfamille;

--** Vue ComposantsTypes = composants associés à leur type
CREATE VIEW ComposantsTypes AS
    select
        Composants._id as _id,
        Composants.description as description,
        Composants.reference as reference,
        Composants.datemes as datemes,
        TypesComposants._id as idtype,
        TypesComposants.nom as nomtype
    from Composants,TypesComposants
        on Composants.idtype=TypesComposants._id;


--** Vue SelectionsComposants = composants associés à leurs sélections
CREATE VIEW SelectionsComposants AS
    select
        Composants._id as _id,
        Composants.description as description,
        Composants.reference as reference,
        Composants.datemes as datemes,
        Composants.idtype as idtype,
        TypesComposants.nom as nomtype,
        SelectionsMateriel._id as idselmat,
        SelectionsMateriel.idfamille as idfamille,
        SelectionsMateriel.idselparent as idselparent,
        SelectionsMateriel.dateselmat as dateselmat
    from SelectionsMateriel,SelMatComposants,Composants,TypesComposants
        on  SelectionsMateriel._id = SelMatComposants.idselection
        and SelMatComposants.idcomposant = Composants._id
        and Composants.idtype = TypesComposants._id;

--** Vue ComposantsSelections = sélections des composants
CREATE VIEW ComposantsSelections AS
    select
        SelMatComposants._id as _id,
        SelMatComposants.idcomposant as idcomposant,
        SelMatComposants.idselection as idselection,
        Familles.nom as nomfamille,
        Sports.nom as nomsport
    from SelMatComposants,SelectionsMateriel,Familles,SportsFamilles,Sports
        on  SelMatComposants.idselection = SelectionsMateriel._id
        and SelectionsMateriel.idfamille = Familles._id
        and Familles._id = SportsFamilles.idfamille
        and SportsFamilles.idsport = Sports._id;

--** Vue ComposantsSelections = sélections des composants
CREATE VIEW FamillesSelections AS
    select
        SelectionsMateriel._id as _id,
        Familles.nom as nomfamille,
        Sports.nom as nomsport
    from SelectionsMateriel,Familles,SportsFamilles,Sports
        on  SelectionsMateriel.idfamille = Familles._id
        and Familles._id = SportsFamilles.idfamille
        and SportsFamilles.idsport = Sports._id;

VACUUM;

------------------------------------------------------------------------

-- champs supplémentaires des sorties sportives
BEGIN TRANSACTION;
insert into Champs values (0, "GPX",        11, null);      -- => Champs._id=0  -- gpx
insert into Champs values (1, "Météo",      5, null);       -- => Champs._id=1  -- ligne
insert into Champs values (2, "Distance",   3, "km");       -- => Champs._id=2  -- nombre
insert into Champs values (3, "Durée",      9, null);       -- => Champs._id=3  -- temps
insert into Champs values (4, "Vmax",       3, "km/h");     -- => Champs._id=4  -- nombre
insert into Champs values (5, "FCmax",      3, "bpm");      -- => Champs._id=5  -- nombre
insert into Champs values (6, "FC90s",      3, "bpm");      -- => Champs._id=6  -- nombre
insert into Champs values (7, "Terrain",    5, null);       -- => Champs._id=7  -- ligne
insert into Champs values (8, "D+",         3, "m");        -- => Champs._id=8  -- nombre
insert into Champs values (9, "Forme",      5, null);       -- => Champs._id=9  -- ligne
insert into Champs values (10,"Cardio",     9, null);       -- => Champs._id=10 -- temps
insert into Champs values (11,"Machines",   9, null);       -- => Champs._id=11 -- temps
insert into Champs values (12,"Stretching", 9, null);       -- => Champs._id=12 -- temps
insert into Champs values (13,"Mécanique",  6, null);       -- => Champs._id=13 -- texte
insert into Champs values (14,"Leçons",     6, null);       -- => Champs._id=14 -- texte
insert into Champs values (15,"Calories",   3, null);       -- => Champs._id=15 -- nombre
insert into Champs values (16,"Fréquentation", 4,
    "désert;faible;moyenne;forte;bondé");                   -- => Champs._id=16 -- enum
insert into Champs values (17,"% gras",     3, "%");        -- => Champs._id=17 -- nombre
insert into Champs values (18,"FCmoy",      3, "bpm");      -- => Champs._id=18 -- nombre
insert into Champs values (19,"Programme",  5, null);       -- => Champs._id=19 -- ligne
insert into Champs values (20,"Réglages",   6, null);       -- => Champs._id=20 -- texte
insert into Champs values (21,"Fatigue",    5, null);       -- => Champs._id=21 -- ligne
insert into Champs values (22,"Vmoy",       10,
    "round(Distance*3600/Durée,1) || ' km/h'");             -- => Champs._id=22 -- formule
insert into Champs values (23,"Dtot VTT",   10,
    "round(sum(Distance)) || ' km' where sport='VTT' and idsortie<=%d"); -- => Champs._id=23 -- formule
insert into Champs values (24,"Poids",      3, "Kg");       -- => Champs._id=24 -- nombre
insert into Champs values (25,"FCrepos",    3, "bpm");      -- => Champs._id=25 -- nombre
insert into Champs values (26,"Région",     5, null);       -- => Champs._id=26 -- ligne
insert into Champs values (27,"Exercices",  6, null);       -- => Champs._id=27 -- texte
insert into Champs values (28,"Temps 3km",  9, null);       -- => Champs._id=28 -- temps

COMMIT;

-- champs d'une sortie vtt
BEGIN TRANSACTION;
insert into Sports values (1,"VTT", "Sorties VTT en campagne, sur chemins ou sentiers", (256*255+0)*256+255); -- => Sports._id=1
insert into ChampsSports values (null, 1,  2, 1); -- => ChampsSports._id=1    -- distance
insert into ChampsSports values (null, 1, 23, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=2    -- Dtot
insert into ChampsSports values (null, 1,  3, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=3    -- duree
insert into ChampsSports values (null, 1, 22, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=4    -- Vmoy
insert into ChampsSports values (null, 1,  4, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=5    -- Vmax
insert into ChampsSports values (null, 1,  8, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=6    -- D+
insert into ChampsSports values (null, 1,  1, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=7    -- meteo
insert into ChampsSports values (null, 1,  7, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=8    -- terrain
insert into ChampsSports values (null, 1, 19, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=9    -- programme
insert into ChampsSports values (null, 1,  0, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=10    -- gpx
insert into ChampsSports values (null, 1,  9, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=11    -- forme
insert into ChampsSports values (null, 1,  5, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=12    -- fcmax
insert into ChampsSports values (null, 1,  6, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=13    -- fc90s
insert into ChampsSports values (null, 1, 21, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=14    -- fatigue
insert into ChampsSports values (null, 1, 13, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=15    -- mécanique
insert into ChampsSports values (null, 1, 14, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=16    -- leçons
insert into ChampsSports values (null, 1, 26, (select max(ordre)+1 from ChampsSports where idsport=1)); -- => ChampsSports._id=17    -- région
COMMIT;
-- champs d'une sortie footing
BEGIN TRANSACTION;
insert into Sports values (2,"Footing", "Course à pied sur petites routes ou sentiers", (256*255+0)*256+255); -- => Sports._id=2
insert into ChampsSports values (null, 2,  0, 1); -- => ChampsSports._id=18    -- gpx
insert into ChampsSports values (null, 2,  1, (select max(ordre)+1 from ChampsSports where idsport=2)); -- => ChampsSports._id=19    -- meteo
insert into ChampsSports values (null, 2,  2, (select max(ordre)+1 from ChampsSports where idsport=2)); -- => ChampsSports._id=20    -- distance
insert into ChampsSports values (null, 2,  3, (select max(ordre)+1 from ChampsSports where idsport=2)); -- => ChampsSports._id=21    -- duree
insert into ChampsSports values (null, 2, 28, (select max(ordre)+1 from ChampsSports where idsport=2)); -- => ChampsSports._id=22    -- temps 3km
insert into ChampsSports values (null, 2,  7, (select max(ordre)+1 from ChampsSports where idsport=2)); -- => ChampsSports._id=23    -- temps 3km
insert into ChampsSports values (null, 2,  9, (select max(ordre)+1 from ChampsSports where idsport=2)); -- => ChampsSports._id=24    -- forme
insert into ChampsSports values (null, 2,  5, (select max(ordre)+1 from ChampsSports where idsport=2)); -- => ChampsSports._id=25    -- fcmax
insert into ChampsSports values (null, 2,  6, (select max(ordre)+1 from ChampsSports where idsport=2)); -- => ChampsSports._id=26    -- fc90s
insert into ChampsSports values (null, 2, 15, (select max(ordre)+1 from ChampsSports where idsport=2)); -- => ChampsSports._id=27    -- calories
insert into ChampsSports values (null, 2, 21, (select max(ordre)+1 from ChampsSports where idsport=2)); -- => ChampsSports._id=28    -- fatigue
insert into ChampsSports values (null, 2, 26, (select max(ordre)+1 from ChampsSports where idsport=2)); -- => ChampsSports._id=29    -- région
COMMIT;
-- champs d'une sortie planche
BEGIN TRANSACTION;
insert into Sports values (3,"Planche", "Funboard dans les vagues ou slalom sur eau plate", (256*255+0)*256+255); -- => Sports._id=3
insert into ChampsSports values (null, 3,  0, 1); -- => ChampsSports._id=30    -- gpx
insert into ChampsSports values (null, 3,  1, (select max(ordre)+1 from ChampsSports where idsport=3)); -- => ChampsSports._id=31    -- meteo
insert into ChampsSports values (null, 3,  3, (select max(ordre)+1 from ChampsSports where idsport=3)); -- => ChampsSports._id=32    -- duree
insert into ChampsSports values (null, 3, 15, (select max(ordre)+1 from ChampsSports where idsport=3)); -- => ChampsSports._id=33    -- calories
insert into ChampsSports values (null, 3,  9, (select max(ordre)+1 from ChampsSports where idsport=3)); -- => ChampsSports._id=34    -- forme
insert into ChampsSports values (null, 3, 21, (select max(ordre)+1 from ChampsSports where idsport=3)); -- => ChampsSports._id=35    -- fatigue
insert into ChampsSports values (null, 3, 19, (select max(ordre)+1 from ChampsSports where idsport=3)); -- => ChampsSports._id=36    -- programme
insert into ChampsSports values (null, 3, 20, (select max(ordre)+1 from ChampsSports where idsport=3)); -- => ChampsSports._id=37    -- réglages
insert into ChampsSports values (null, 3, 26, (select max(ordre)+1 from ChampsSports where idsport=3)); -- => ChampsSports._id=38    -- région
COMMIT;
-- champs d'une sortie muscu
BEGIN TRANSACTION;
insert into Sports values (4,"Musculation", "Musculation sportive et stretching", (256*255+0)*256+255); -- => Sports._id=4
insert into ChampsSports values (null, 4,  3, 1); -- => ChampsSports._id=39    -- duree
insert into ChampsSports values (null, 4, 19, (select max(ordre)+1 from ChampsSports where idsport=4)); -- => ChampsSports._id=40    -- programme
insert into ChampsSports values (null, 4, 27, (select max(ordre)+1 from ChampsSports where idsport=4)); -- => ChampsSports._id=41    -- exercices
insert into ChampsSports values (null, 4,  9, (select max(ordre)+1 from ChampsSports where idsport=4)); -- => ChampsSports._id=42    -- forme
insert into ChampsSports values (null, 4, 21, (select max(ordre)+1 from ChampsSports where idsport=4)); -- => ChampsSports._id=43    -- fatigue
insert into ChampsSports values (null, 4, 10, (select max(ordre)+1 from ChampsSports where idsport=4)); -- => ChampsSports._id=44    -- cardio
insert into ChampsSports values (null, 4, 11, (select max(ordre)+1 from ChampsSports where idsport=4)); -- => ChampsSports._id=45    -- machines
insert into ChampsSports values (null, 4, 12, (select max(ordre)+1 from ChampsSports where idsport=4)); -- => ChampsSports._id=46    -- stretching
insert into ChampsSports values (null, 4, 16, (select max(ordre)+1 from ChampsSports where idsport=4)); -- => ChampsSports._id=47    -- fréquentation
COMMIT;
-- champs d'une sortie randonnée
BEGIN TRANSACTION;
insert into Sports values (5,"Randonnée", "Randonnées pédestres", (256*255+0)*256+255); -- => Sports._id=6
insert into ChampsSports values (null, 5,  2, 1); -- => ChampsSports._id=49    -- distance
insert into ChampsSports values (null, 5,  3, (select max(ordre)+1 from ChampsSports where idsport=6)); -- => ChampsSports._id=50    -- duree
insert into ChampsSports values (null, 5, 26, (select max(ordre)+1 from ChampsSports where idsport=6)); -- => ChampsSports._id=51    -- région
COMMIT;

------------------------------------------------------------------------
-- Création de la vue Donnees: script MajVueDonnees.py

DROP VIEW IF EXISTS DonneesNumeriques;
DROP VIEW IF EXISTS DonneesSorties;
DROP VIEW IF EXISTS DonneesBrutes;
CREATE VIEW DonneesBrutes AS SELECT Sorties._id as _id, Sorties._id as idsortie, Sports._id as idsport, Sports.nom as sport, Sorties.date as date, Sorties.lieu as lieu, Sorties.infos as infos, Sorties.note as note, Valeurs0.valeur as "GPX", Valeurs1.valeur as "Météo", Valeurs2.valeur as "Distance", Valeurs3.valeur as "Durée", Valeurs4.valeur as "Vmax", Valeurs5.valeur as "FCmax", Valeurs6.valeur as "FC90s", Valeurs7.valeur as "Terrain", Valeurs8.valeur as "D+", Valeurs9.valeur as "Forme", Valeurs10.valeur as "Cardio", Valeurs11.valeur as "Machines", Valeurs12.valeur as "Stretching", Valeurs13.valeur as "Mécanique", Valeurs14.valeur as "Leçons", Valeurs15.valeur as "Calories", Valeurs16.valeur as "Fréquentation", Valeurs17.valeur as "% gras", Valeurs18.valeur as "FCmoy", Valeurs19.valeur as "Programme", Valeurs20.valeur as "Réglages", Valeurs21.valeur as "Fatigue", Valeurs24.valeur as "Poids", Valeurs25.valeur as "FCrepos", Valeurs26.valeur as "Région" FROM Sorties,Sports on Sorties.idsport=Sports._id LEFT JOIN Valeurs as Valeurs0 on Valeurs0.idsortie=Sorties._id and Valeurs0.idchamp=0 LEFT JOIN Valeurs as Valeurs1 on Valeurs1.idsortie=Sorties._id and Valeurs1.idchamp=1 LEFT JOIN Valeurs as Valeurs2 on Valeurs2.idsortie=Sorties._id and Valeurs2.idchamp=2 LEFT JOIN Valeurs as Valeurs3 on Valeurs3.idsortie=Sorties._id and Valeurs3.idchamp=3 LEFT JOIN Valeurs as Valeurs4 on Valeurs4.idsortie=Sorties._id and Valeurs4.idchamp=4 LEFT JOIN Valeurs as Valeurs5 on Valeurs5.idsortie=Sorties._id and Valeurs5.idchamp=5 LEFT JOIN Valeurs as Valeurs6 on Valeurs6.idsortie=Sorties._id and Valeurs6.idchamp=6 LEFT JOIN Valeurs as Valeurs7 on Valeurs7.idsortie=Sorties._id and Valeurs7.idchamp=7 LEFT JOIN Valeurs as Valeurs8 on Valeurs8.idsortie=Sorties._id and Valeurs8.idchamp=8 LEFT JOIN Valeurs as Valeurs9 on Valeurs9.idsortie=Sorties._id and Valeurs9.idchamp=9 LEFT JOIN Valeurs as Valeurs10 on Valeurs10.idsortie=Sorties._id and Valeurs10.idchamp=10 LEFT JOIN Valeurs as Valeurs11 on Valeurs11.idsortie=Sorties._id and Valeurs11.idchamp=11 LEFT JOIN Valeurs as Valeurs12 on Valeurs12.idsortie=Sorties._id and Valeurs12.idchamp=12 LEFT JOIN Valeurs as Valeurs13 on Valeurs13.idsortie=Sorties._id and Valeurs13.idchamp=13 LEFT JOIN Valeurs as Valeurs14 on Valeurs14.idsortie=Sorties._id and Valeurs14.idchamp=14 LEFT JOIN Valeurs as Valeurs15 on Valeurs15.idsortie=Sorties._id and Valeurs15.idchamp=15 LEFT JOIN Valeurs as Valeurs16 on Valeurs16.idsortie=Sorties._id and Valeurs16.idchamp=16 LEFT JOIN Valeurs as Valeurs17 on Valeurs17.idsortie=Sorties._id and Valeurs17.idchamp=17 LEFT JOIN Valeurs as Valeurs18 on Valeurs18.idsortie=Sorties._id and Valeurs18.idchamp=18 LEFT JOIN Valeurs as Valeurs19 on Valeurs19.idsortie=Sorties._id and Valeurs19.idchamp=19 LEFT JOIN Valeurs as Valeurs20 on Valeurs20.idsortie=Sorties._id and Valeurs20.idchamp=20 LEFT JOIN Valeurs as Valeurs21 on Valeurs21.idsortie=Sorties._id and Valeurs21.idchamp=21 LEFT JOIN Valeurs as Valeurs24 on Valeurs24.idsortie=Sorties._id and Valeurs24.idchamp=24 LEFT JOIN Valeurs as Valeurs25 on Valeurs25.idsortie=Sorties._id and Valeurs25.idchamp=25 LEFT JOIN Valeurs as Valeurs26 on Valeurs26.idsortie=Sorties._id and Valeurs26.idchamp=26;
CREATE VIEW DonneesSorties AS SELECT _id, idsortie, idsport, sport, date, lieu, infos, note, "GPX", "Météo", "Distance", "Durée", "Vmax", "FCmax", "FC90s", "Terrain", "D+", "Forme", "Cardio", "Machines", "Stretching", "Mécanique", "Leçons", "Calories", "Fréquentation", "% gras", "FCmoy", "Programme", "Réglages", "Fatigue", (select round(Distance*3600/Durée,1) || ' km/h' FROM DonneesBrutes WHERE idsortie=DB.idsortie) as "Vmoy", (select round(sum(Distance)) || ' km' FROM DonneesBrutes WHERE sport='VTT' and idsortie<=DB.idsortie) as "Dtot VTT", "Poids", "FCrepos", "Région" FROM DonneesBrutes AS DB;
CREATE VIEW DonneesNumeriques AS SELECT date,'S' as type,note,"Distance","Durée","Vmax","FCmax","FC90s","D+","Cardio","Machines","Stretching","Calories","% gras","FCmoy","Vmoy","Dtot VTT","Poids","FCrepos" FROM DonneesSorties UNION SELECT Mesures.date as date,'M' as type,null,Valeurs2.valeur as "Distance",Valeurs3.valeur as "Durée",Valeurs4.valeur as "Vmax",Valeurs5.valeur as "FCmax",Valeurs6.valeur as "FC90s",Valeurs8.valeur as "D+",Valeurs10.valeur as "Cardio",Valeurs11.valeur as "Machines",Valeurs12.valeur as "Stretching",Valeurs15.valeur as "Calories",Valeurs17.valeur as "% gras",Valeurs18.valeur as "FCmoy",Valeurs22.valeur as "Vmoy",Valeurs23.valeur as "Dtot VTT",Valeurs24.valeur as "Poids",Valeurs25.valeur as "FCrepos" FROM Mesures LEFT JOIN Mesures as Valeurs2 on Valeurs2._id=Mesures._id and Valeurs2.idchamp=2 LEFT JOIN Mesures as Valeurs3 on Valeurs3._id=Mesures._id and Valeurs3.idchamp=3 LEFT JOIN Mesures as Valeurs4 on Valeurs4._id=Mesures._id and Valeurs4.idchamp=4 LEFT JOIN Mesures as Valeurs5 on Valeurs5._id=Mesures._id and Valeurs5.idchamp=5 LEFT JOIN Mesures as Valeurs6 on Valeurs6._id=Mesures._id and Valeurs6.idchamp=6 LEFT JOIN Mesures as Valeurs8 on Valeurs8._id=Mesures._id and Valeurs8.idchamp=8 LEFT JOIN Mesures as Valeurs10 on Valeurs10._id=Mesures._id and Valeurs10.idchamp=10 LEFT JOIN Mesures as Valeurs11 on Valeurs11._id=Mesures._id and Valeurs11.idchamp=11 LEFT JOIN Mesures as Valeurs12 on Valeurs12._id=Mesures._id and Valeurs12.idchamp=12 LEFT JOIN Mesures as Valeurs15 on Valeurs15._id=Mesures._id and Valeurs15.idchamp=15 LEFT JOIN Mesures as Valeurs17 on Valeurs17._id=Mesures._id and Valeurs17.idchamp=17 LEFT JOIN Mesures as Valeurs18 on Valeurs18._id=Mesures._id and Valeurs18.idchamp=18 LEFT JOIN Mesures as Valeurs22 on Valeurs22._id=Mesures._id and Valeurs22.idchamp=22 LEFT JOIN Mesures as Valeurs23 on Valeurs23._id=Mesures._id and Valeurs23.idchamp=23 LEFT JOIN Mesures as Valeurs24 on Valeurs24._id=Mesures._id and Valeurs24.idchamp=24 LEFT JOIN Mesures as Valeurs25 on Valeurs25._id=Mesures._id and Valeurs25.idchamp=25;

-- courbes affichables

insert into Courbes values (null, "Evolution du poids", "date", "Poids", null); -- => Courbes._id=1
insert into Courbes values (null, "Vitesse moyenne", "date", "Vmoy", null); -- => Courbes._id=2


------------------------------------------------------------------------

-- sélection 1 : planche vague médium
BEGIN TRANSACTION;
insert into Familles values (1,"Planche vagues médium","Pour le vent entre 20 et 30 noeuds"); -- => Familles._id=1
insert into SportsFamilles values (null, 3,  (select max(_id) from Familles)); -- => SportsFamilles._id=1
insert into SelectionsMateriel values (null,1, null, null); -- => SelectionsMateriel._id=1

-- ajout des différents mats utilisés
insert into TypesComposants values (null,"Mat vague médium", "Pour le vent entre 20 et 30 noeuds"); -- => TypesComposants._id=1
insert into ComposantsUsure values (null, (select max(_id) from TypesComposants), 3); -- => ComposantsUsure._id=1
insert into Composants values (null, (select max(_id) from TypesComposants), "Mat 4m30", "Gun Sails", strftime('%s','2006-04-18','utc'), null, null); -- => Composants._id=1 -- => Composants._id=-1
insert into SelMatComposants values (null,(select max(_id) from SelectionsMateriel),(select max(_id) from Composants)); -- => SelMatComposants._id=1

-- ajout des différentes voiles utilisées
insert into TypesComposants values (null,"Voile vague médium", "Pour le vent entre 20 et 30 noeuds"); -- => TypesComposants._id=2
insert into ComposantsUsure values (null, (select max(_id) from TypesComposants), 3); -- => ComposantsUsure._id=2
insert into Composants values (null, (select max(_id) from TypesComposants), "Hammer 5 m²", "Gun Sails", strftime('%s','2008-06-22','utc'), null, null); -- => Composants._id=2 -- => Composants._id=-1
insert into SelMatComposants values (null,(select max(_id) from SelectionsMateriel),(select max(_id) from Composants)); -- => SelMatComposants._id=2

-- ajout des différents wishbones utilisés
insert into TypesComposants values (null,"Wishbone vague", "Pour toutes les voiles de vague"); -- => TypesComposants._id=3
insert into ComposantsUsure values (null, (select max(_id) from TypesComposants), 3); -- => ComposantsUsure._id=3
insert into Composants values (null, (select max(_id) from TypesComposants), "Décathlon", "140/190", strftime('%s','2008-11-07','utc'), null, null); -- => Composants._id=3 -- => Composants._id=-1
insert into SelMatComposants values (null,(select max(_id) from SelectionsMateriel),(select max(_id) from Composants)); -- => SelMatComposants._id=3

-- ajout des différentes carottes de pied de mat utilisées
insert into TypesComposants values (null,"Carotte pdm vague", null); -- => TypesComposants._id=4
insert into ComposantsUsure values (null, (select max(_id) from TypesComposants), 3); -- => ComposantsUsure._id=4
insert into Composants values (null, (select max(_id) from TypesComposants), "Carotte", null, strftime('%s','2008-11-07','utc'), null, null); -- => Composants._id=4 -- => Composants._id=-1
insert into SelMatComposants values (null,(select max(_id) from SelectionsMateriel),(select max(_id) from Composants)); -- => SelMatComposants._id=4
COMMIT;



-- sélection 3 : planche vague gros temps
BEGIN TRANSACTION;
insert into Familles values (3,"Planche vagues gros temps","Pour le vent au dessus de 30 noeuds"); -- => Familles._id=3
insert into SportsFamilles values (null, 3,  (select max(_id) from Familles)); -- => SportsFamilles._id=3
insert into SelectionsMateriel values (null,3, null, null); -- => SelectionsMateriel._id=3

-- ajout des différentes voiles utilisées
insert into TypesComposants values (null,"Voile vague gros temps","Pour le vent au dessus de 30 noeuds"); -- => TypesComposants._id=28
insert into ComposantsUsure values (null, (select max(_id) from TypesComposants), 3); -- => ComposantsUsure._id=54
insert into Composants values (null, (select max(_id) from TypesComposants), "Hammer 4.2 m²", "Gun Sails", strftime('%s','2008-06-22','utc'), null, null); -- => Composants._id=28 -- => Composants._id=-1
insert into SelMatComposants values (null,(select max(_id) from SelectionsMateriel),(select max(_id) from Composants)); -- => SelMatComposants._id=28

-- ajout des différents mats utilisés
insert into TypesComposants values (null,"Mat vague gros temps","Pour le vent au dessus de 30 noeuds"); -- => TypesComposants._id=29
insert into ComposantsUsure values (null, (select max(_id) from TypesComposants), 3); -- => ComposantsUsure._id=55
insert into Composants values (null, (select max(_id) from TypesComposants), "Mat 3m70", "Gun Sails", strftime('%s','2006-04-18','utc'), null, null); -- => Composants._id=29 -- => Composants._id=-1
insert into SelMatComposants values (null,(select max(_id) from SelectionsMateriel),(select max(_id) from Composants)); -- => SelMatComposants._id=29

-- ajout des différents wishbones utilisés (partage avec la sélection 1)
insert into SelMatComposants values (null,(select max(_id) from SelectionsMateriel),
    (select max(Composants._id) from Composants,TypesComposants on TypesComposants._id = Composants.idtype where TypesComposants.nom="Wishbone vague")); -- => SelMatComposants._id=30

-- ajout des différentes carottes de pied de mat utilisés (partage avec la sélection 1)
insert into SelMatComposants values (null,(select max(_id) from SelectionsMateriel),
    (select max(Composants._id) from Composants,TypesComposants on TypesComposants._id = Composants.idtype where TypesComposants.nom="Carotte pdm vague")); -- => SelMatComposants._id=31
COMMIT;

------------------------------------------------------------------------
-- on trouve ensuite toutes les sorties

-- ajout de la sortie Planche du 12/11/2010
BEGIN TRANSACTION;
INSERT INTO Sorties VALUES (null, 3,
    strftime('%s','2010-11-12 14:15','utc'), "St Michel",
    "Première sortie dans les vagues depuis bien longtemps. Bons réglages dès le début. Du mal à bien passer les vagues. Quelques bons surfs à la fin.",3.5,
    (select max(_id) from SelectionsMateriel where idfamille=1)); -- => Sorties._id=198
INSERT INTO Valeurs VALUES (null,(select max(_id) from Sorties),1,"Vent WSW 15 à 30n, vagues 1.5m, quelques algues"); -- => Valeurs._id=1207      -- météo
INSERT INTO Valeurs VALUES (null,(select max(_id) from Sorties),3,2*3600+50*60); -- => Valeurs._id=1208        -- temps
insert into Valeurs values (null,(select max(_id) from Sorties),26,"Trégor"); -- => Valeurs._id=1211      -- région
COMMIT;


-- changement de composants le 2010-11-15 09:00
BEGIN TRANSACTION;
-- nouvelle sélection basée sur la précédente
insert into SelectionsMateriel values (null,1, (select max(_id) from SelectionsMateriel where idfamille=1), strftime('%s','2010-11-15 09:00','utc')); -- => SelectionsMateriel._id=41
-- copie de tous les composants de la précédente sélection vers la nouvelle
insert into SelMatComposants (idselection,idcomposant) select
    (select max(_id) from SelectionsMateriel where idfamille=1), idcomposant from SelMatComposants
        where idselection=(select idselparent from SelectionsMateriel
            where _id=(select max(_id) from SelectionsMateriel where idfamille=1)); -- => SelMatComposants._id=933
-- ajout du composant wishbone vague : Décathlon, 140/190
insert into Composants values (null, (select _id from TypesComposants where nom="Wishbone vague"), "Décathlon", "140/190", strftime('%s','2010-11-15 09:00','utc'), null, null); -- => Composants._id=88 -- => Composants._id=-1
-- ajout du composant wishbone vague : Décathlon, 140/190 dans la sélection 1
delete from SelMatComposants
    where idselection=(select max(_id) from SelectionsMateriel where idfamille=1) and
        idcomposant in (select _id from ComposantsTypes where nomtype="Wishbone vague");
insert into SelMatComposants values (null,(select max(_id) from SelectionsMateriel where idfamille=1),(select max(_id) from Composants)); -- => SelMatComposants._id=934
-- ajout du composant wishbone vague : Décathlon, 140/190 dans la sélection 3
delete from SelMatComposants
    where idselection=(select max(_id) from SelectionsMateriel where idfamille=3) and
        idcomposant in (select _id from ComposantsTypes where nomtype="Wishbone vague");
insert into SelMatComposants values (null,(select max(_id) from SelectionsMateriel where idfamille=3),(select max(_id) from Composants)); -- => SelMatComposants._id=935
COMMIT;

