-- 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 -- 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;