======= Introduction à la gestion des bases de données avec des logiciels libres =======

Atelier offert par [[http://qcbs.ca/fr/ressources/professionnel-de-recherche/professionnels-de-recherche-guillaume-larocque-phd/|Guillaume Larocque]], professionnel de recherche au CSBQ.

===== Presentation =====
 [[http://prezi.com/tcks6siw8q-u/introduction-a-la-gestion-des-bases-de-donnees-avec-des-logiciels-libres/|Lien vers la présentation Prezi]]

 [[http://prezi.com/tcks6siw8q-u/present/?auth_key=wt03qbs&follow=GuillaumeLarocque&kw=present-tcks6siw8q-u&rc=ref-19631691|Lien vers la présentation simultanée]]

===== Ressources utiles =====

  * [[http://www.postgresql.org/docs/current/static/reference.html|PostgreSQL Reference Manual]]
  * [[http://link.springer.com/book/10.1007%2F978-1-4302-0018-5|Beginning databases with PostgreSQL]]
  * [[https://wiki.postgresql.org/wiki/Main_Page|PostgreSQL wiki]]
  * [[http://www.pgsql.ru/db/pgsearch/|PGSearch pour chercher des sites web en lien avec PostgreSQL.]]

====== Installation de PostgreSQL ======
===== Sur Windows et Mac =====
  * Visitez la page de [[http://www.enterprisedb.com/products-services-training/pgdownload#windows|EntrepriseDB]] et téléchargez la version appropriée pour votre plate-forme. Choisissez la version 64 bits. 
  * Éxécutez le fichier et suivez les étapes d'installation en acceptant les options par défaut. 
  * Quand on vous le demande, choisissez un mot de passe approprié pour l'utilisateur postgres. Prenez en note ce mot de passe. 
 
===== Sur Linux/Ubuntu =====
Dans un terminal de commande, tapez: 
<file>
sudo apt-get install postgresql-9.5 libreoffice libreoffice-sdbc-postgresql
</file>
Il est possible que la version de PostgreSQL disponible pour votre distribution soit antérieure à 9.5. Dans ce cas, installez la version disponible. 

Pour d'autres distributions, [[http://www.postgresql.org/download/|vérifiez cette page]]. 

====== Installation de LibreOffice ======

  * [[https://www.libreoffice.org/download/libreoffice-fresh/|Cliquez ici, téléchargez et installez LibreOffice]].

++++ Installation de MySQL |
====== Installation de MySQL ======
  * Visitez la [[http://www.mysql.com/downloads/mysql/|section téléchargements du site de MySQL]] et téléchargez la version de MySQL Community Server ou MySQL Installer appropriée pour votre ordinateur (Utilisateurs Windows: choisissez la version 32 bits de MySQL Installer). Notez que vous n'avez pas à vous inscrire. Cliquez simplement sur "No thanks, just take me to the downloads" en base de la fenêtre. 
  * Suivez les instructions d'installation. Vous pouvez choisir de n'installer que MySQL server.
  * Si une fenêtre s'affiche vous demandant de vous inscrire, **fermez cette fenêtre**.
  * Choisissez de continuer et de créer une "MySQL instance". Sélectionnez la type de configuration standard. 
  * Sur Windows, choisissez d'installer MySQL comme un service Windows. Choisissez également un mot de passe pour l'utilisateur 'root' et prenez bien soins de **noter ce mot de passe quelque part**. 
  * Téléchargez [[http://www.libreoffice.org/download/|LibreOffice 4 ou 4.x]] pour votre plate-forme et suivez les instructions d'installation. Choisissez l'option d'installation 'typique'. 
  * Sous Windows: Installez la [[http://dev.mysql.com/downloads/connector/odbc/|version 32 bit du connecteur MySQL ODBC]].

==== Configuration de la connection LibreOffice/MySQL ====
**Sur Windows**
Ouvrez LibreOffice Base. Sélectionnez la base de données...Se connecter à une base de données existante (Connect to an existing database)...MySQL. Sur l'écran suivant, choisissez 'Connect using ODBC'. Sur l'écran suivant, cliquez sur 'Browse' et choisissez 'Organize' en bas à droite de la fenêtre. Ensuite, choisissez d'ajouter une nouvelle source de données et trouvez 'MySQL ODBC 5.2w driver'. Sur l'écran suivant ... Nom de la source de données (Data source name): MySQL ODBC. TCP/IP Server : localhost. User: root. Password: Your password. Database: QCBSWORK. Ensuite, cliquez sur OK, sélectionnez MySQL ODBC et cliquez de nouveau sur OK. 

Sur l'écrant suivant: Username:root. Sélectionnez 'Password required'. Sélectionnez Next...Finish et sauvez la base de données dans une répertoire de votre choix. 

**Sur Mac**
[[http://dev.mysql.com/downloads/connector/j/|Téléchargez l'archive tar.gz ici]]. Ouvrez LibreOffice Writer. Dans le menu du haut, trouvez Préférences...LibreOffice>>Java>>Class path>>Add archive et sélectionnez l'archive que vous venez de télécharger".
Ouvrez LibreOffice Base. Sélectionnez 'Base de données... Connection à une base de données existante...MySQL. Sur l'écran suivant, choisissez 'Connect using JDBC'. Sur l'écran suivant...nom de la base de données: QCBSWORK. Serveur: localhost. Nom d'usager: root; 

**Sur Linux**
Ouvrez LibreOffice Base. Sélectionnez 'base de données...Connexion à une base de données existante...MySQL. Sur l'écran suivant, choisissez 'Connexion en utilisant JDBC'. Sur l'écran... Nom de la base: QCBSWORK. Serveur: localhost. Si cette opération échoue, allez dans le menu principal de LibreOffice et choisissez Outils>>Options>>LibreOffice>>Avancé et sélectionnez une installation de JRE.

++++

===== Accéder à PostgreSQL =====
**Sur Windows et Mac**: 
  * Trouvez PostgreSQL 9.6 dans le menu démarrer/Finder et cliquez sur "SQL shell (psql)"
  * Acceptez les paramètres par défaut en cliquant sur ENTER et tapez le mot de passe approprié. Vous ne verrez rien lorsque vous taperez votre mot de passe, c'est normal.

**Sur Linux** 
Tapez simplement:
<file>
psql
</file>
dans un terminal.


++++ Si ça ne fonctionne pas sur Mac ou Linux... |

Ouvrez le terminal; 

  * Configurez de façon à pouvoir vous connecter sans mot de passe:

Arrêtez le serveur postgres (launch daemon):
<file>
sudo launchctl unload /Library/LaunchDaemons/com.edb.launchd.postgresql-9.6.plist
</file>
  - Editez data/pg_hba.conf et changez 'md5' par 'trust' dans les lignes 'local' et 'host'.
  - Démarrez le serveur:
<file>
sudo launchctl load /Library/LaunchDaemons/com.edb.launchd.postgresql-9.6.plist
</file>


++++


++++ Autres instructions sous Linux... |

PostgreSQL doit utiliser le même nom d'usager que votre nom d'usager Linux. Pour créer un usager avec votre nom linux: 
<file>
     $ sudo -u postgres psql
     postgres=> alter user postgres password 'apassword';
     postgres=> create user yerusername createdb createuser password 'somepass';
     postgres=> create database yerusername owner yerusername;
</file>

Other option:
<file>
createuser -P -s -e your_linux_username
</file>

You can then connect to Postgres using this command

<file>
psql
</file>

If you want to create a new user when logged in, you can do so by executing the CREATE USER commands below. Then, in psql you type: 
<file>
SHOW hba_file;
</file>
then find that file and edit it. Replace this line
<file>
local all postgres peer
</file>
and replace it with 
<file>
local all postgres md5
</file>

Then, you should be able to connect to Postgres with
<file>
psql worshop -U your_username -W
</file>
++++
\\



====== Discussion de groupe ======

[[http://qcbs.ca/wp-content/uploads/2012/10/Crustacean_plankton_Canada.ods|Cliquez ici pour télécharger le jeu de données (feuille de calcul en format LibreOffice Calc)]]


\\
\\
\\
====== Exercices PostgreSQL  ======

==== Créer une nouvelle base de données: ====
 
Si vous voulez, vous pouvez créer un nom d'usager autre que 'postgres'. Pour ce faire, tapez la commande suivante dans psql, en remplacant nom_usager et mot_de_passe par des valeurs choisies. 
<file postgresql>
CREATE USER your_username WITH SUPERUSER PASSWORD 'your_password';
</file>

Créer une base de données 'atelier'
<file postgresql>
CREATE DATABASE atelier;
</file>

===== Connecter PSQL et Libre Office =====
  * Ouvrez Libre Office Base et sélectionnez "Nouvelle...base de données". 
  * Se connecter à une base existance>PostgreSQL
  * Pour l'URL de la source de données, tapez ceci:
<file postgresql>
dbname=atelier host=localhost port=5432
</file>
  * Cliquez sur suivant et indiquez le nom d'usager, et choisissez 'mot de passe requis'. Vous pouvez ensuite cliquer sur 'Tester la connexion' pour vous assurer que ça fonctionne.
  * Vous pouvez ensuite sauvez l'information de connexion à la base de données sous un fichier sur votre ordinateur, de façon à facilement ré-ouvrir la base de données par la suite. 

===== Commandes de base =====

Se connecter à la base de données
<file postgresql>
\c atelier
</file>

Voir la liste de bases de données disponibles: 
<file postgresql>
\l
</file>

Voir la liste des tables: 
<file postgresql>
\d+
</file>

Voir les colonnes dans une table
<file postgresql>
\d nom_de_la_table
</file>


Lister tous les usagers
<file postgresql>
\du
</file>

Quitter Postgresql
<file postgresql>
\q
</file>


===== Exercice 1 - déclarations CREATE et INSERT =====

Formats de colonnes les plus communs
^fonction^description^exemple^
|int|integer|5|
|decimal(,)|decimal(longueur totale, nombre de décimales)|122.52|
|varchar()|character(longueur maximale)| 'hirondelle bicolore'|
|text|texte d'une longueur non-définie| 'Il était une fois une hirondelle...'|
|timestamp|date et heure| '2012-10-21 10:03:21'|

[[http://www.postgresql.org/docs/current/static/datatype.html|Liste complète des type de données PostgreSQL.]] 

Assurez-vous d'avoir créé la base de données atelier et de vous y être connecté. 

<file postgresql>
\c atelier
</file>

Créer la table 
<file postgresql>
CREATE TABLE obs_oiseaux (obs_id SERIAL PRIMARY KEY,
obs_time timestamp,espece text,nombre integer,lat real,"long" real);
</file>
Note: nous utilisons les doubles guillements "" pour marquer l'utilisation d'un mot réservé, d'un mot avec des caractères spéciaux ou des espaces dans le nom d'une table ou d'une colonne. 

Insérer une nouvelle ligne dans la table
<file postgresql>
INSERT INTO obs_oiseaux (obs_time,espece,nombre,lat,"long") VALUES ('2012-06-19 12:31:16','Sturnus vulgaris',40,45.3522,-73.7930);
</file>

Notez que nous n'avons pas entré le champ obs_id puisqu'il est entré automatiquement avec le type de caractère SERIAL, tel que spécifié lors de la création de la table.

Pour visualiser la table
<file>
SELECT * FROM obs_oiseaux;
</file>


**Question 1** :?: Reproduisez la table suivante dans PostgreSQL, nommez la 'Acteur' et visualisez là dans PostgreSQL. 
^prenom^nom_de_famille^date_naissance^sexe^grandeur^marque_distinctive^
|Bruce|Willis|1955-03-19|M|1.81|Joue fréquemment des hommes qui souffrent d'une tragédie, ont perdu quelque chose, ou qui ont une crise de confiance ou de conscience.|
|Emma|Watson|1990-04-15|F|1.65|Joue souvent des personnages littéraires.|
**Note**: quand on entre du texte, il faut doubler les ' dans le texte ('') pour éviter que PostgreSQL ne les utilise comme marqueur de fin de l'entrée texte. 


===== Exercice 2 - Importer des données dans PostgreSQL =====

Téléchargez les fichiers suivants sur votre ordinateur (utilisez le bouton de droite pour cliquer sur le lien... sauvergarder sous). 
{{:lakes.csv|Lakes.csv}}
{{::species_acro.csv|species_acro.csv}}
{{:lakes_species.csv|lakes_species.csv}}

Sous Windows: copiez ces trois fichiers vers un dossier nommé 'bdlibre' dans le répertoire C: (i.e. C:/bdlibre). 
Sous Mac ou Linux: copiez ces trois fichiers vers un dossier facilement accessible (e.g. /home/votrenome/bdlibre). Modifiez les commandes plus bas en remplaçant <html>"C:/bdlibre"</html> avec le chemin vers ce dossier. 

Créer une table contenant l'information environnementale de chaque lac. - **Lakes**. 
<file postgresql>
CREATE TABLE lakes (lake_id SERIAL PRIMARY KEY,numero INT, lake_name text, 
province text, latitude DEC(10,5),longitude DEC(10,5), number_of_species INT, ecoprov VARCHAR(50), 
ecozone VARCHAR(50), gss DEC(10,2),gse DEC(10,2), gsl DEC(10,2), gdd10 DEC(10,2), egdd DEC(10,2), 
mean_ele DEC(10,2), pe_ann_p DEC(10,2), totp_ann DEC(10,2), srann_me DEC(10,2), shann_me DEC(10,2), 
tmax_ann DEC(10,2), tmean_an DEC(10,2), vpann_me DEC(10,2));
</file>


++++ MySQL |
<file>
CREATE TABLE lakes (lake_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,numero INT, lake_name text, 
province text, latitude DEC(10,5),longitude DEC(10,5), number_of_species INT, ecoprov VARCHAR(50), 
ecozone VARCHAR(50), gss DEC(10,2),gse DEC(10,2), gsl DEC(10,2), gdd10 DEC(10,2), egdd DEC(10,2), 
mean_ele DEC(10,2), pe_ann_p DEC(10,2), totp_ann DEC(10,2), srann_me DEC(10,2), shann_me DEC(10,2), 
tmax_ann DEC(10,2), tmean_an DEC(10,2), vpann_me DEC(10,2));
</file>
++++

Charger le fichier CSV dans cette table. 
<file postgresql>
COPY lakes FROM 'C:/bdlibre/lakes.csv' WITH csv HEADER DELIMITER AS ',';
</file>

++++ Si cette commande vous donne un message 'access denied' sous Windows |
  * Cliquez avec le bouton de droite de la souris sur le dossier contenant les données et choisissez 'Propriétés'. 
  * Sélectionner l'onglet Securité et cliquer sur 'éditer'.
  * Dans les "Permissions pour le dossier", choisissez "Ajouter". 
  * Tapez "Everyone" dans la boîte "Entrer le nom des objets à sélectionner" 
  * Cliquez OK et fermez la fenêtre. 
  * Vérifiez que les Permissions pour Lire et Éxécuter sont à 'Permettre/Allow'. 
  * Cliquez sur appliquer. 
++++

++++ Si cette commande vous donne un message d'erreur 'access denied' sur Mac ou Linux |

Copiez les fichiers dans un dossier situé sous /tmp/ ou /User/Shared/ et spécifiez ce dossier dans les commandes SQL. 

++++


++++ MySQL |
<file>
LOAD DATA LOCAL INFILE 'C:/bdlibre/lakes.csv' INTO TABLE lakes FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\n'   IGNORE 1 LINES;
</file>
Note: si cette commande ne fonctionne pas ('command not allowed'). Tapez 'exit;' et redémarrez MySQL avec la commande suivante <code>mysql -u root -p --local-infile=1;</code>
++++

Créez la table contentant les présences de espèces pour chaque lac et charger le fichier CSV dans cette table.- **lakes_species**

<file postgresql>
CREATE TABLE lakes_species (lake_id INT NOT NULL, species_id INT NOT NULL);
COPY lakes_species FROM 'C:/bdlibre/lakes_species.csv' WITH csv HEADER DELIMITER AS ',';
</file>

++++ MySQL |
<file>
CREATE TABLE lakes_species (lake_id INT NOT NULL, species_id varchar(25) NOT NULL);
LOAD DATA LOCAL INFILE 'C:/bdlibre/lakes_species.csv' INTO TABLE lakes_species 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'   IGNORE 1 LINES;
</file>
++++


Pour la troisième table, nous pourrions utiliser cette commande:

++++ Commande |
<file postgresql>
CREATE TABLE species_acro (species_id INT NOT NULL, full_name varchar(100), short_name varchar(25));
COPY species_acro FROM 'C:/bdlibre/species_acro.csv' WITH csv HEADER DELIMITER AS ',';
</file>
++++

Par contre, nous allons utiliser LibreOffice pour se familiariser avec l'interface. 
===== Importation de données dans LibreOffice Base, en utilisant LibreOffice Calc =====


=== Importation d'une table ===

  * Ouvrez le fichier species_acro.csv avec LibreOffice Calc (Dans Windows, trouvez LibreOffice Calc dans le menu démarrer). Spécifiez que le fichier est séparé par des virgules.
  * Sélectionnez la feuille de calcul en entier avec CTRL-A.
  * Ouvrez LibreOffice Base et allez à Edition...coller. Nommez la table "public.species_acro". Choisissez d'utiliser le nom des colonnes comme entêtes pour la table. Ne spécifiez pas la clé primaire pour l'instant.
  * Suivez les étapes... Déplacez les colonnes à droite. Ensuite, spécifiez le format de données (vérifiez le format des données dans le fichier csv). Cliquez sur le bouton de droite sur la colonne 'species_id' et sélectionnez 'Clé Primaire'. Spécifiez int [int4] pour species_id, text [varchar], 200 caractères pour full_name et text [varchar], 30 caractères pour short_name.


===== RÉFÉRENCES =====

** Fonctions mathématiques et d'aggrégation (GROUP BY)**
|AVG()	|La moyenne|
|COUNT(DISTINCT COLUMN)	|Le nombre d'entrées distinctes dans une colonne|
|COUNT()	|Le nombre de lignes|
|GROUP_CONCAT()	|Concaténation de multiples entrées textuelles|
|MAX()	|Valeur maximale|
|MIN()	|Valeur minimale|
|STDDEV_POP()	|Déviation standard de la population|
|STDDEV_SAMP()	|Déviation standard de l'échantillon|
|SUM()	|Somme|
|VAR_POP()	|Variance de la population|
|VAR_SAMP()	|Variance de l'échantillon|
[[http://www.postgresql.org/docs/9.3/static/functions-aggregate.html|Liste complète]]
\\
\\
**Sélection conditionnelle pour utilisation dans une clause 'WHERE'**
|=	|Égal|
|>	|Plus grand que|
|<	|Moins que|
|>=	|Plus grand que ou égal|
|< =	|Plus petit que ou égal|
|<> or !=|Non-égalité|
|LIKE|Requête d'équivalence d'entrées textuelles (string matching)|

**Ordre des opérations dans une requête 'SELECT'**:
<code>
SELECT columns FROM tables WHERE conditions JOIN GROUP BY columns HAVING condition ORDER BY columns;
</code>

===== Exercice 3 - commande SELECT =====
Sélectionnez toutes les lignes de la table Lakes
<file postgresql>
SELECT * FROM lakes;
</file>

Sélectionner toutes les lignes, mais n'afficher que les noms des lacs et la province
<file postgresql>
SELECT lake_name, province FROM lakes;
</file>

Voir tous les noms de provinces différents
<file postgresql>
SELECT DISTINCT province from lakes;
</file>

Voir tous le noms de provinces, utiliser une alias (renommer cette colonne dans les résultats) et trier par ordre descendant de nom. 
<file postgresql>
SELECT DISTINCT province as "province name" FROM lakes ORDER BY province DESC;
</file>

Voir l'année de naissance de chaque acteur
<file postgresql>
SELECT prenom, nom_de_famille, EXTRACT(year from date_naissance) FROM acteur;
</file>

Voir les initiales de chaque acteur
<file postgresql>
SELECT prenom, nom_de_famille, concat(substr(prenom,1,1),' ',substr(nom_de_famille,1,1)) as initiales FROM acteur;
</file>

Sélectionner tous les lacs du Québec où la température annuelle moyenne est en bas de -5 C. 
<file postgresql>
SELECT lake_name, tmean_an FROM lakes WHERE province='QUEBEC' AND tmean_an<-5;
</file>
Ou, pour compter le nombre de lacs
<file postgresql>
SELECT count(*) FROM lakes WHERE province='QUEBEC' AND tmean_an<-5;
autre alternative:
SELECT count(lake_id) FROM lakes WHERE province='QUEBEC' AND tmean_an<-5;
</file>


**Question 1** :?: -  Combien de lacs en Colombie-Britannique reçoivent plus de 3000 mm de précipitation (colonne totp_ann)?
\\
++++réponse| 
12
<file postgresql>
SELECT count(*) FROM lakes WHERE totp_ann>3000 AND province='BRITISH COLUMBIA';
</file>
++++

**Question 2** :?: -  Quel est l'altitude moyenne (colonne mean_ele) de tous les lacs dans l'écozone 'Montane Cordillera' (utilisez la fonction avg())?
\\
++++réponse| 
1364.36
<file postgresql>
SELECT avg(mean_ele) FROM lakes WHERE ecozone='Montane Cordillera';
</file>
++++
\\
Note: Le symbole % est utilisé pour remplacer le début ou la fin d'une entrée textuelle.
\\
Sélectionnez tous les noms de lacs qui contiennent le mot 'Small'
<file postgresql>
SELECT lake_name FROM lakes WHERE lake_name like '%Small%';
</file>

Sélectionnez tous les noms de lacs qui contiennent le mot 'Small' et qui ne sont pas situés en Ontario
<file postgresql>
SELECT lake_name, province 
FROM lakes
WHERE lake_name like '%Small%' AND province!='ONTARIO';
autre possibilité:
SELECT lake_name, province 
FROM lakes 
WHERE lake_name like '%Small%' AND province NOT LIKE 'ONTARIO';
</file>

**Question 3** :?: - Quelle est la latitude maximale de tous les lacs dans un Ecozone dont le nom commence par 'Taiga'
++++ réponse| 
68.8 

<file postgresql>
SELECT max(latitude) FROM lakes WHERE ecozone LIKE 'Taiga%';
</file>

++++

**Question 4** :?: - Combien d'espèces de Daphnia sont-elles listées dans la table species_acro?
++++ réponse| 
17
(21 ont le mot 'Daphnia' quelque part dans leur nom) 
<file postgresql>
SELECT count(*) FROM species_acro WHERE full_name like 'Daphnia%';
</file>
++++

===== Exercice 4 - REGROUPEMENT =====

Calculez la température annuelle moyenne de tous les lacs dans chaque province
<file postgresql>
SELECT province, avg(tmean_an) as mean_an_t
FROM  lakes
GROUP BY province;
</file>

Même table, mais triée par ordre de températures croissantes
<file postgresql>
SELECT province, avg(tmean_an) as mean_an_t
FROM  lakes
GROUP BY province ORDER BY avg(tmean_an);
</file>

**Question 5** :?: -  Quelle province a le lac ayant la température maximale (tmax_ann) la plus élevée?
++++ réponse|
BRITISH COLUMBIA 14.60
<file postgresql>
SELECT province, max(tmax_ann) as max_temp FROM lakes GROUP BY province ORDER BY max_temp DESC;
</file>
++++


Commande 'UPDATE'

Changer toutes les noms de province qui contiennent 'NWT', pour qu'ils aient le même nom ('NWT'). 
- **Faites attention, cette opération modifie la table!**
<file postgresql>
UPDATE lakes
SET province='NWT'
WHERE province like '%NWT%';
</file>

===== Exercice 5 - Travailler avec plusieurs tables =====

Compter le nombre d'espèces dans chaque lac. Afficher le nom du lac et le nombre de chaque espèce. 
<file postgresql>
SELECT lake_name, COUNT(DISTINCT species_id) as num_species FROM lakes, lakes_species 
WHERE lakes.lake_id=lakes_species.lake_id 
GROUP BY lakes.lake_id
ORDER BY num_species DESC;
</file>

Même requête, mais on limite l'affichage aux 20 premières entrées. 
<file postgresql>
SELECT lake_name, COUNT(DISTINCT species_id) as num_species FROM lakes, lakes_species 
WHERE lakes.lake_id=lakes_species.lake_id 
GROUP BY lakes.lake_id
ORDER BY num_species DESC LIMIT 20;
</file>

Compter le nombre d'espèces dans chaque Ecozone et Ecoprovince. 
<file postgresql>
SELECT ecozone, ecoprov, COUNT(species_id) as num_species FROM lakes, lakes_species 
WHERE lakes.lake_id=lakes_species.lake_id 
GROUP BY ecozone, ecoprov
ORDER BY ecozone, COUNT(species_id);
</file>

**Question 6** :?: Générer une table qui liste le nom complet de chaque espèce de Daphnia et le nombre de lacs où on retrouve cette espèce. 

++++ Réponse | 

<file postgresql>SELECT full_name, count(DISTINCT b.lake_id) as cnt FROM species_acro a, lakes_species b WHERE full_name like 'Daphnia%' AND a.species_id=b.species_id GROUP BY full_name;</file>
^full_name^cnt^
| Daphnia  pulex Leydig, 1860             |                       234 |
| Daphnia  similis Claus, 1876            |                        17 |
| Daphnia ambigua Scourfield, 1947        |                        59 |
| Daphnia catawba Coker, 1926             |                        28 |
| Daphnia dubia Herick, 1883              |                        71 |
| Daphnia galeata Sars, 1864              |                        17 |
| Daphnia longiremis G. O. Sars, 1862     |                       426 |
| Daphnia longispina hyalina ceresiana    |                         3 |
| Daphnia longispina hyalina microcephela |                        27 |
| Daphnia magna Straus, 1820              |                        18 |
| Daphnia mendotae Birge, 1918            |                       433 |
| Daphnia middendorffiana Fischer, 1851   |                       117 |
| Daphnia parvula Fordyce, 1901           |                        28 |
| Daphnia pulicaria Forbes, 1893          |                       110 |
| Daphnia retrocurva Forbes, 1882         |                       324 |
| Daphnia rosea G.O. Sars, 1862           |                        52 |
| Daphnia thorata  Forbes, 1893           |                        14 |
++++ \\

===== Exercice 6 - Opérations de jointures (JOIN) =====
|JOIN (INNNER JOIN,  CROSS JOIN)| Garder toutes les combinaisons possibles des lignes des tables A et B.|
|LEFT JOIN| Garder toutes les entrées de la table A, et joindre avec les entrées de B qui se trouvent également dans A (via un identificateur conjoint).|
|RIGHT JOIN| Garder toutes les entrées de la table B, et joindre avce les entrées de A qui se trouvent également dans B (via un identificateur conjoint).|
|OUTER JOIN| Garder toutes les entrées des tables A et B|

Créer une liste de toutes les espèces présentes dans les lacs du Québec, avec les noms complets des espèces et le nombre de lacs dans lesquelles elles sont présentes. 
<file postgresql>
SELECT full_name, count(full_name) 
FROM species_acro,lakes_species,lakes 
WHERE species_acro.species_id=lakes_species.species_id AND  lakes.lake_id=lakes_species.lake_id AND province='QUEBEC' 
GROUP BY full_name;
</file>

De façon équivalente...
<file postgresql>
SELECT full_name, count(full_name) 
FROM lakes
LEFT JOIN lakes_species ON (lakes.lake_id=lakes_species.lake_id)
LEFT JOIN species_acro ON (species_acro.species_id=lakes_species.species_id)
WHERE province='QUEBEC' 
GROUP BY full_name;
</file>

Pour toutes les combinaisons possibles de lacs se trouvant dans l'Ecoprovince Baffin Uplands, calculer la différence entre leurs températures annuelles moyennes. 
<file postgresql>
SELECT concat(a.lake_name,'-',b.lake_name) as lakes, a.tmean_an-b.tmean_an as temp_diff 
FROM lakes a, lakes b 
WHERE a.ecoprov='Baffin Uplands' AND b.ecoprov='Baffin Uplands';
</file>
===== Exercice 7 - Sous-requêtes (Subqueries) =====
Créez une table contenant le fréquence de chaque espèce dans les lacs au sud et au nord du 50e parallèle (latitude). 

<file postgresql>
SELECT a.species_id, count_50south, count_50north
FROM 
 (SELECT species_id, count(d.species_id) as count_50south FROM lakes c, lakes_species d WHERE c.lake_id=d.lake_id AND latitude<=50 GROUP BY species_id) a,
 (SELECT species_id, count(f.species_id) as count_50north FROM lakes e, lakes_species f WHERE e.lake_id=f.lake_id AND latitude>50 GROUP BY species_id) b 
WHERE a.species_id=b.species_id;
</file>

:?: **Question 7** En utilisant une sous-requête, trouvez l'élévation moyenne (colonne MEAN_ELE) des lacs dans lesquels au moins une espèce de Daphnia est présente. 
\\ ++ Réponse | 474.627172 \\ Code: SELECT avg(mean_ele) FROM (SELECT DISTINCT a.lake_id, mean_ele FROM lakes a,species_acro b,lakes_species c WHERE b.full_name like 'Daphnia%' AND a.lake_id=c.lake_id AND c.species_id=b.species_id ORDER BY a.lake_id) d;\\ ++
====== Utilisation de l'interface LibreOffice Base ======



===== Créer des tables et des formulaires =====

On vous donne un jeu de données dans le format suivant

^Parcelle 1^
^Espèce^DHP^État^Commentaires^
|Acer rubrum|12.4|vivant| |
|Acer saccharum|25.3|vivant| |
|Fagus grandifolia|14.1|vivant| |
|Fagus grandifolia|66.0|mort| |
|Fraxinus americana|30.1|vivant|écorce endommagée|
^Parcelle 2^
|Quercus rubra|12.4|vivant| |
|Fraxinus americana|64.2|vivant|gros trou dans le tronc|
|Fraxinus americana|53.1|mort|déraciné|
|Carpinus caroliana|10.3|vivant| |

:?: On vous dit que des milliers d'autres arbres devront être entrés. Comment pensez-vous construire une base de données pour entreposer cette information?


:?: Créez la table suivante avec la commande 'CREATE TABLE' dans PostgreSQL. Spécifiez le champs espece_id comme la clé primaire avec le type de fichier SERIAL PRIMARY KEY. Vous pouvez ensuite remplir la table avec les valeurs dans Libreoffice Base.  
Nom de la table: especes_arbres
^espece_id^nom_espece^
|1|Quercus rubra|
|2|Acer saccharum|
|3|Acer rubrum|
|4|Fagus grandifolia|
|5|Fraxinus americana|
|6|Carpinus caroliana|


:?: Créez cette table dans PostgreSQL et créez un formulaire pour la remplir avec le mode ébauche de formulaire, avec des menus déroulants pour sélectionner les espèces et des boutons radios pour la santé des arbres. arbre_id est la clé primaire. 
Nom de la table: parcelles_arbres
^parcelle_id^arbre_id^espece_id^dhp^etat^commentaires^
|1|1|3|12.4|vivant| |
|1|2|2|25.3|vivant| |
|1|3|4|14.1|mort| |
|1|4|4|66.0|vivant| |
|1|5|4|30.1|vivant|écorce endommagée|
|2|6|1|40.1|vivant| |
|2|7|5|64.2|vivant|gros trou dans le tronc|
|2|8|5|53.1|mort|déraciné|
|2|9|6|10.3|vivant| |



====== Utiliser Microsoft Access avec PostgreSQL ======

  * Dans Windows, téléchargez le pilote [[http://www.postgresql.org/ftp/odbc/versions/msi/|PostgreSQLODBC]] et installez-le sur votre ordinateur. 
  * Créez vos tables dans PostgreSQL.
  * Dans Access, cliquez sur "External data... ODBC database", et ensuite sélectionnez "Link to the data source by creating a linked table" et choisissez un nouveau nom de DSN. Spécifiez un nom pour le fichier et remplissez l'information pour vous connecter à la base de données. Le serveur est localhost si la base de données est sur votre propre ordinateur. Vous pouvez ensuite choisir les tables que vous voulez lier. 
  * Ces tables seront ensuite liées avec PostgreSQL et vous pouvez les modifier à partir de Access ou via des formulaire Access.

====== Création de formulaires pour le web ======

  * [[https://prezi.com/hmqxdokj_0_u/concepts-de-base-pour-la-creation-de-formulaires-web/|Accéder à la présentation Prezi ici. ]]
  * [[http://prezi.com/hmqxdokj_0_u/present/?auth_key=dzwkigq&follow=GuillaumeLarocque&kw=present-hmqxdokj_0_u&rc=ref-19631691|Présentation simultanée.]]




**Liens utiles**
  * [[http://www.codecademy.com/|Code Academy]] Site web génial pour apprendre HTML, CSS, PHP, Javascript JQuery, SQL ou autre. 
  * [[http://link.springer.com/book/10.1007/978-1-4302-0136-6|Beginning PHP and PostgreSQL 8. ]] Livre téléchargeable en e-book. Un peu vieux.

**Étapes principales**
  - Création d'une interface web avec le code html et CSS.
  - Création d'une base de données pour accueillir les données du formulaire. 
  - Création d'un code PHP qui va recevoir les données du formulaire et qui va les envoyer vers la base de données. 
  - Enrichissement possible du formulaire avec Javascript/JQuery


===== Installation de PHP, PostgreSQL, apache et PHPPGAdmin =====

[[https://bitnami.com/tag/postgresql|Télécharger et installer le serveur WAPP pour Windows, ou MAPP pour Mac.]]

Pour linux: 
<file>
sudo apt-get install postgresql-9.5 apache2 php5 phppgadmin php5-pgsql
</file>
en modifiant selon la version de postgresql disponible dans votre distribution. 

===== Partie 1 - Formulaire HTML =====

Composantes nécessaires pour un fichier HTML

<file html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta charset="UTF-8">
<title>Titre du document</title>
</head>
<body>
Le contenu du fichier est ici!
</body>
</html>
</file>

Création d'un formulaire


Le formulaire de base
<file html>
<form method="POST" action="fichierphp.php">
<input type="text">
</form>
</file>

[[http://www.w3schools.com/tags/att_input_type.asp|Liste des types d'entrée html]]

Entrée de texte
<file html>
<input type="text" name="nom_texte">
</file>

Menu déroulant
<file html>
<select name="nom_select">
<option value="1">Option 1</option>
<option value="2">Option 2</option>
<option value="3">Option 3</option>
</select>
</file>

Check box
<file html>
<input type="checkbox" name="poutine" value="oui"> J'aime la poutine
<input type="checkbox" name="sushis" value="oui"> J'aime les sushis
<input type="checkbox" name="poutine_sushis" value="oui"> J'aime la poutine aux sushis
</file>

Bouttons radio
<file html>
Je suis un(e)
<input type="radio" name="nom_radio" value="h" > Homme
<input type="radio" name="nom_radio" value="f"> Femme
</file>

Zone de texte
<file html>
<textarea name="zone_texte">
</textarea>
</file>

===== Stylisation avec le CSS =====

[[http://www.w3schools.com/cssref/|Guide de référence CSS]]

On peut donner un "id" à n'importe quel élément html. Important: il ne peut y avoir qu'un id unique par élément
<file html>
<input type="text" name="nom_texte" id="nom_texte">
</file>

Et on peut y faire référence dans l'entête de style css du fichier html avec le dièse "#"
<file html>
<head>
<style>
#nom_texte {
 width:300px;
 height:30px;
 color:red;
 background-color:#ffff00;
 font-family:verdana,arial;
 font-size:14px;
 font-weight:bold;
 padding:0px 5px 0px 5px; #top right bottom left
}
</style>
</head>
</file>

On peut aussi donner une "class" à n'importe quel élément html. Cette classe peut se répeter sur plusieurs éléments
<file html>
<input type="text" name="nom_texte1" class="classe_texte">
<input type="text" name="nom_texte2" class="classe_texte">
<input type="text" name="nom_texte3" class="classe_texte">
</file>

Et on peut y faire référence dans l'entête de style css du fichier html avec le point "."
<file html>
<head>
<style>
.classe_texte {
 width:300px;
 height:30px;
 color:red;
 background-color:#ffff00;
 font-family:verdana,arial;
 font-size:14px;
 font-weight:bold;
 padding:0px 5px 0px 5px; #top right bottom left
}
</style>
</head>
</file>

====== Partie 2 - Création de la base de données associée ======

[[https://secure.qcbs.ca/systeme/phppgadmin/|Lien vers phppgadmin]]


====== Partie 3 - Bases du langage PHP ======

Tous les fichiers doivent commencer par:
<file php>
<?php
Le code PHP est ici. 
?>
</file>

Commentaires
<file php>
<?php
// Commentaire sur une ligne
/*
Section de commentaires
*/
?>
</file>

<file php>
<?php
// Création de variables
$myvar=5;
$myvar='ceci est du texte';

// Création d'un array
$myarray=array('valeur1','valeur2','valeur3');

echo $myarray[0];
//valeur1

$myarray=[];
$myarray[0]='valeur1';
$myarray[1]='valeur2';

// Création d'un array "key-value"
$myarray=array(
'cle1'=>'valeur1',
'cle2'=>'valeur2',
'cle3'=>'valeur3',
);

echo $myarray['cle1'];
//valeur1
?>
</file>

Boucles et if/else
<file php>
<?php
if ($f==5){
//Do something
}else{
//Do something else
}

for ($i=0;$i<10;$i++){
echo 'i='.$i;
}
//i=0
//i=1
//...
//i=9


foreach ($myarray as $my){
//Faire quelque chose avec $my
?>
</file>

====== Partie 4 - Insertion dans PostgreSQL via PHP ======

[[http://php.net/manual/en/book.pgsql.php|Fonctions PostgreSQL dans PHP]]

Ce fichier PHP est celui qui est spécifié dans le champ "ACTION" du formulaire html.
<file php>
<?php
// Connexion et sélection de la base de données
$dbconn = pg_connect("host=localhost dbname=base_donnees user=nom_usager password=mot_de_passe");

// L'array $_POST contient toutes les données du formulaire. Chaque 'clé' de cet array correspond au nom donné à chacun des type d'entrées html. 
$element1=$_POST['nom_element1'];
$element2=$_POST['nom_element2'];
$element1=pg_escape_string($element1);
$element2=pg_escape_string($element2);

// Insertion dans la base de données
$query = "INSERT INTO ma_table (colonne1,colonne2) VALUES ($element1,$element2)";
$result = pg_query($query);
// Fermer la connexion
pg_close($dbconn);
echo "Formulaire entré dans la base de données avec succès!";
?>
</file>

Accéder à différents types d'éléments
<file php>
//Check boxes
foreach ($_POST['checkbox'] as $check){
   //Faire quelque chose ici avec $check
}
//Convertir les valeurs en liste séparée par des virgules
$check=implode(',',$_POST['checkbox']);

</file>


Retrouver des éléments dans la base de données
<file php>
// Connexion et sélection de la base de données
$dbconn = pg_connect("host=localhost dbname=base_donnees user=nom_usager password=mot_de_passe");
 
$query="SELECT * FROM nom_table";
// Insertion dans la base de données
$result = pg_query($query);
while ($t=pg_fetch_array($result)){
	echo $t['prenom'].' '.$t['nom_famille'];
}

// Fermer la connexion
pg_close($dbconn);
</file>
====== Lier R avec PostgreSQL ======

Dans R:
lors de la première utilisation seulement :
<file rsplus | installer le paquet RMySQL>
install.packages('RPostgreSQL')
</file>

<file rsplus | Code R>
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,host="localhost", user="your_username", password="your_password", dbname="atelier");
lakes <- dbGetQuery(con,'SELECT * FROM lakes');
</file>
S'assurer de changer le nom d'utilisateur et le mot de passe au besoin. 

<file rsplus| Générer un graphique à partir des données extraites>
lakesqc <- dbGetQuery(con,"SELECT * FROM lakes WHERE province='QUEBEC'")
hist(lakes$tmean_an)
</file>

===== Using the dplyr package=====

<file rsplus>
library(dplyr)
src<-src_postgres(dbname="workshop",host="localhost", port="5432",user="your_username",password="your_password")
lakes <- tbl(src, "lakes") # Define lakes table
lakes_qc<-filter(lakes, province  %=% 'QUEBEC') # Select lakes in Quebec
prov_tmean<-summarise(group_by(lakes, province), mean(tmean_an)) # Mean annual temperature per province
prov_tmean=collect(prov_tmean) # Transfer result to standard R data frame
lakes_qc2<-tbl(src, sql("SELECT * FROM lakes WHERE province='QUEBEC'")) #Perform any SQL statement
</file>