---
# System prepended metadata

title: Exercices SQLITE

---

# Exercices SQLITE

::: danger
**Ressources**

[Go web Examples](https://gowebexamples.com/mysql-database/)

[Official SQLite3 Github](https://github.com/mattn/go-sqlite3/blob/master/_example/simple/simple.go)

:::


Afin de réaliser ces exercices et votre projet, il vous faudra utiliser **les modules golang**. 
De plus si jamais vous souhaitez effectuer le sujet *"herokuize"*, les utiliser sera indispensable pour que cela fonctionne. 

Pour ce faire, à la racine de votre projet effectuez:
``` bash
go mod init project_name
```

Ensuite il ne vous reste plus qu'à **installer SQLite3**
``` bash
go get github.com/mattn/go-sqlite3
```

Pour utiliser SQLite dans votre projet il vous faut maintent l'importer comme suit:
``` go
import (
	"database/sql"
	_ "github.com/mattn/go-sqlite3"
)
```

Par la suite tout les packages que vous aurez créés vous même et qui nécessitent d'être importés seront sous la forme:
``` go
import "project_name/mysubpackage"
```

---

### Exercice 0 - Initialiser la BDD
Pour commencer nous allons créer la Base de Donnée.

Créer la base de donnée consiste à appeler quelques fonctions du package *sqlite3* pour ouvrir un nouveau fichier qui contiendra vos données.

* Créer une fonction `initDatabase` qui ouvre la base de donnée. Le nom de la BDD est libre, mais doit terminer par ***.db***
* Cette fonction *return* la BDD, son type est **\*sql.DB**

::: info 
En exécutant cette fonction, si celle-ci fonctionne, vous devriez maintenant avoir un nouveau fichier créé à la racine de votre projet du nom que vous avez choisi.

**Ex:** forum.db
:::

### Exercice 1 - Créer une Table
#### 1.1 ---
Dans cet exercice il faudra créer une table`users` dans votre base de donnée, **SI ELLE N'EXISTE PAS DEJA**.

* Rajouter à la fonction `initDatabase` les commandes nécessaire pour créer cette table.

La table doit se présenter comme suit :

| id | name | email | password |
| -------- | -------- | -------- | -- |
| INT     | TEXT     | TEXT     | TEXT |

- `id` doit être la clé primaire et **AUTOINCREMENT**
- l'`email` doit être **UNIQUE** dans la base de donnée. Deux users ne peuvent pas avoir le même email
- Tout les champs doivent être marque comme **NOT NULL**
- Lorsque vous relancer le programme la table doit rester intact et ne pas écraser celle qui existait déjà

:::    info
CREATE TABLE

**et**

db.Exec
:::

#### 1.2 ---

Maintenant la table créée, insérez trois nouveaux utilisateurs dans la table `users`.

Créez une fonction `insertIntoUsers` qui permet d'insérer un seul utilisateur dans la BDD.

``` go
func insertIntoUsers(db *sql.DB, name string, email string, password string) (int64, error);
```
Cette fonction doit aussi *return* le dernier Id inséré dans la BDD en utilisant la fonction. 
`result` est le retour de l'execution de la requête **INSERT INTO**
``` go
result.LastInsertId()
```

| Name | Email | Password |
| -------- | -------- | -------- |
| Mathieu     | m.m@gmail.com     | abcde     |
| Thomas     | t.t@gmail.com     | fghij     |
| Lucas     | l.l@gmail.com     | klmno    |

> Pour rappel, l'id doit s'incrémenter automatiquement.

:::    info
INSERT INTO

**et**

db.Exec
:::

<br>

### Exercice 2 - Selectionner

Maintenant que nous avons créé la table et que nous y avons insérer des lignes, il faut désormais récupérer les informations.

D'abord, **collez cette structure** dans votre code, nous en aurons besoin par la suite.

``` go
type User struct {
    Id       int
    Name     string
    Email    string
    Password string
}
```

#### 2.1 ---

Créez une fonction `selectAllFromTable` qui prend en paramètre la BDD et le nom de la table
``` go
func selectAllFromTable(db *sql.DB, table string) *sql.Rows;
```

* Cette fonction doit *return* toutes les lignes contenues dans la table spécifié

* Dans un second temps, affichez toutes ces lignes *(cf ressources + utilisez la structure)*


::: info
 SELECT ... FROM ...
 
 **et**
 
 db.Query
:::

#### 2.2 ---

Créez une fonction `selectUserById` qui prend en paramètre la BDD et un id
``` go
func selectUserById(db *sql.DB, id int) User;
```

* Cette fonction doit *return* une structure `User` qui contient les information de l'utilisateur à l'id donné

* Dans un second temps, affichez cet utilisateur


::: info
 SELECT ... FROM ... WHERE ...
 
 **et**
 
 db.QueryRow
:::

#### 2.3 ---

Créez une fonction `selectUserNameWithPattern` qui prend en paramètre la BDD et un pattern à trouver
``` go
func selectUserNameWithPattern(db *sql.DB, pattern string) *sql.Rows;
```

* Cette fonction doit *return* les lignes contenu dans la table spécifié où le nom correspond au pattern en paramètre

* Dans un second temps, affichez toutes ces lignes

> **Exemple:**
``` go 
selectUserNameWithPattern(db, "as");

{2, Thomas, t.t@gmail.com, fghij}
{3, Lucas, l.l@gmail.com, klmno}
```


::: info
 SELECT ... FROM ... WHERE ... LIKE %...%
 
 **et**
 
 db.Query
:::

<br>

### Exercice 3 - Foreign Keys

::: danger

Attention, pour que les FOREIGN KEYS fonctionnent, il faut éxécuter la requête sql suivante à l'initialisation de la BDD :

```
PRAGMA foreign_keys = ON;
```

**Ressource:**
[Foreign key SQLite](https://www.sqlitetutorial.net/sqlite-foreign-key/)

:::

#### 3.1 ---

Créez une nouvelle table `posts` dans votre base de donnée, **SI ELLE N'EXISTE PAS DEJA**.

* Rajouter à la fonction `initDatabase` les commandes nécessaire pour créer cette table.

La table doit se présenter comme suit :

| id | content | user_id |
| -------- | -------- | -------- |
| INT     | TEXT     | INT      |

- `id` doit être la clé primaire et **AUTOINCREMENT**
- `user_id` doit être une **FOREIGN KEY** et faire **REFERENCES** à l'id user dans la table `users`
- Tout les champs doivent être marque comme **NOT NULL**
- Lorsque vous relancer le programme la table doit rester intact et ne pas écraser celle qui existait déjà

:::    info
CREATE TABLE <br>
FOREIGN KEY ... REFERENCES ...

**et**

db.Exec
:::

#### 3.2 ---

Maintenant la table créée, insérez cinq nouveaux posts dans la table `posts`.


| Content | user_id |                   |
| ------- | ------- | ----------------- |
| Post1   | 1       | *(lié à Mathieu)* |
| Post2   | 2       | *(lié à Thomas)*  |
| Post3   | 2       | *(lié à Thomas)*  |
| Post4   | 1       | *(lié à Mathieu)* |
| Post5   | 3       | *(lié à Lucas)*   |

> Pour rappel, l'id du post doit s'incrémenter automatiquement.

:::    info
INSERT INTO

**et**

db.Exec
:::

#### 3.3 ---

Actuellement, chaque post est relié à un id user précis et qui existe. 
Que ce passe-t-il si on essaye d'insérer un nouveau post pour un utilisateur inexistant ?

* Essayez d'insérer un nouveau post avec l'id 4 (qui n'est pas censé exister si vous avez suivi le fil de ces exercices). Observez ce qu'il se passe.

<br>

### Exercice 4 - Autres
[Delete](https://www.sqlitetutorial.net/sqlite-delete/): Supprimer un ou plusieurs élément d'une table

[Update](https://www.sqlitetutorial.net/sqlite-update/): Mettre à jour un ou plusieurs élément d'une table

[OrderBy](https://www.sqlitetutorial.net/sqlite-order-by/): Permet de trier de manière croissant ou décroissant nos résultat SQL en fonction d'un des champs de la table

[Limit](https://www.sqlitetutorial.net/sqlite-limit/): Permet de limiter notre commande à n ligne ou dans une range de *n* à *m* lignes.
(**ex:** pagination)

[Count](https://www.sqlitetutorial.net/sqlite-count-function/): Permet de récupérer le nombre de ligne que notre command à retourner.
(**ex:** likes/dislike, nombres de commentaires, ...)

