======= 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]]

===== 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]]
  * [[https://www.postgresqltutorial.com/|PostgreSQL Tutorial]]
  * [[https://mystery.knightlab.com/|SQL Murder Mystery]]

====== Installation de PostgreSQL ======
===== Sur Windows et Mac =====
  * Visitez la page de [[https://www.postgresql.org/download/|PostgreSQL]] 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 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]]. 


===== Accéder à PostgreSQL =====
**Sur Windows et Mac**: 
  * Trouvez PostgreSQL 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 et mot de passe =====
==== 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>

Étape important afin de sécurisé l'accès.
<file postgresql>
REVOKE CONNECT ON DATABASE atelier FROM PUBLIC;
</file>

<file postgresql>
GRANT ALL ON DATABASE atelier TO your_username;
</file>



==== Connecter PSQL et DBEAVER  ====
  *  Ouvrir DBeaver et choisir PostgreSQL dans la liste des systèmes.
  *  Pour le nom de la base de données, choisissez “postgres”, pour l'utilisateur, choisissez “postgres” et entrez le mot de passe que vous avez choisi lors de l'installation.


===== 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|
|real|nombre avec précision de 5 décimales|31.65973|
|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'|
|date|date seulement| '2012-10-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 de caractères spéciaux ou d'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 postgresql>
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>

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. 

++++

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>

==== Importation depuis DBeaver ====


Pour la **troisième table**, nous allons utiliser l'interface de **DBeaver**. Cliquez avec le bouton de gauche de la souris sur Tables dans Schemas>public dans le menu de gauche, et sélectionnez Import Data. Cliquez sur Next et choisissez le fichier CSV species_acro.csv sur votre ordinateur. Dans Tables mapping, assurez-vous que le nom de la table, le nom des colonnes et le format des colonnes est adéquat. Après avoir cliqué sur Next à nouveau, cliquez sur Proceed. 

++++ Commande PostgreSQL correspondante |
<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>
++++

===== 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.





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

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




====== Lier R avec PostgreSQL ======

Dans R:
lors de la première utilisation seulement :
<file rsplus | installer le paquet RPostgreSQL>
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>

===== Utilisation de dbplyr + dplyr =====

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