Section 1 : SQLite3 en ligne de commande¶

CREATE TABLE Tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    priority TEXT CHECK(priority IN ('LOW', 'MEDIUM', 'HIGH')) DEFAULT 'MEDIUM',
    is_completed INTEGER DEFAULT 0,  -- 0 = false, 1 = true (SQLite n'a pas de booléen)
    due_date DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    color TEXT DEFAULT '#000000',  -- Code couleur hexadécimal
    icon TEXT,  -- Nom de l'icône (ex: "work", "home", "shopping")
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE TaskCategory (
    task_id INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    PRIMARY KEY (task_id, category_id),
    FOREIGN KEY (task_id) REFERENCES Tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES Categories(id) ON DELETE CASCADE
);

-- Afficher toutes les tables
.tables

-- Afficher le schéma d'une table
.schema Tasks

-- Afficher le schéma d'une table avec PRAGMA
PRAGMA table_info(Tasks);

-- Catégories typiques d'une app de tâches
INSERT INTO Categories (name, color, icon) 
VALUES 
    ('Travail', '#FF5722', 'work'),
    ('Personnel', '#4CAF50', 'home'),
    ('Shopping', '#2196F3', 'shopping_cart'),
    ('Sport', '#FF9800', 'fitness_center'),
    ('Études', '#9C27B0', 'school');

-- Tâches avec différentes priorités
INSERT INTO Tasks (title, description, priority, is_completed, due_date) 
VALUES 
    ('Finir le TP SQLite', 'Compléter tous les exercices', 'HIGH', 0, '2025-01-15 23:59:00'),
    ('Faire les courses', 'Acheter du pain et du lait', 'MEDIUM', 0, '2025-01-10 18:00:00'),
    ('Séance de sport', 'Course à pied 5km', 'LOW', 1, '2025-01-08 07:00:00'),
    ('Préparer présentation', 'Slides pour la réunion', 'HIGH', 0, '2025-01-12 09:00:00'),
    ('Appeler le dentiste', 'Prendre RDV', 'MEDIUM', 0, NULL);

-- Associer les tâches à leurs catégories
INSERT INTO TaskCategory (task_id, category_id) 
VALUES 
    (1, 5),  -- TP SQLite → Études
    (2, 3),  -- Courses → Shopping
    (3, 4),  -- Sport → Sport
    (4, 1),  -- Présentation → Travail
    (5, 2);  -- Dentiste → Personnel

.mode column
.headers on
.width 5 30 10 15

-- Toutes les tâches non terminées (écran principal de l'app)
SELECT id, title, priority, due_date 
FROM Tasks 
WHERE is_completed = 0
ORDER BY due_date ASC;

-- Tâches à haute priorité
SELECT * FROM Tasks 
WHERE priority = 'HIGH' AND is_completed = 0;

-- Recherche de tâches (fonction de recherche dans l'app)
SELECT * FROM Tasks 
WHERE title LIKE '%TP%' OR description LIKE '%TP%';

-- Compter les tâches par statut (statistiques)
SELECT 
    CASE is_completed
        WHEN 0 THEN 'En cours'
        WHEN 1 THEN 'Terminées'
    END as statut,
    COUNT(*) as nombre
FROM Tasks
GROUP BY is_completed;

-- Requête typique pour afficher les tâches avec leur catégorie
SELECT 
    t.id,
    t.title,
    t.priority,
    c.name as category,
    c.color,
    t.due_date,
    t.is_completed
FROM Tasks t
LEFT JOIN TaskCategory tc ON t.id = tc.task_id
LEFT JOIN Categories c ON tc.category_id = c.id
ORDER BY t.due_date ASC;

-- Dashboard de l'app : nombre de tâches par catégorie
SELECT 
    c.name as category,
    c.color,
    COUNT(tc.task_id) as total_tasks,
    SUM(CASE WHEN t.is_completed = 1 THEN 1 ELSE 0 END) as completed,
    SUM(CASE WHEN t.is_completed = 0 THEN 1 ELSE 0 END) as pending
FROM Categories c
LEFT JOIN TaskCategory tc ON c.id = tc.category_id
LEFT JOIN Tasks t ON tc.task_id = t.id
GROUP BY c.id
ORDER BY total_tasks DESC;

SELECT 
    t.id,
    t.title,
    c.name as category,
    t.due_date,
    CAST((julianday('now') - julianday(t.due_date)) AS INTEGER) as days_overdue
FROM Tasks t
LEFT JOIN TaskCategory tc ON t.id = tc.task_id
LEFT JOIN Categories c ON tc.category_id = c.id
WHERE t.is_completed = 0 
  AND t.due_date < datetime('now')
ORDER BY days_overdue DESC;

-- Action utilisateur : cocher une tâche
UPDATE Tasks 
SET is_completed = 1,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- Vérifier la modification
SELECT id, title, is_completed, updated_at 
FROM Tasks 
WHERE id = 1;

UPDATE Tasks 
SET priority = 'HIGH',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 2;

-- Supprimer une tâche (l'association dans TaskCategory sera supprimée en CASCADE)
DELETE FROM Tasks WHERE id = 3;

-- Commencer une transaction
BEGIN TRANSACTION;

-- Créer une nouvelle tâche
INSERT INTO Tasks (title, description, priority, due_date) 
VALUES ('Préparer démo app', 'Finaliser la présentation du projet', 'HIGH', '2025-01-20 14:00:00');

-- Récupérer l'ID de la tâche créée
SELECT last_insert_rowid() as task_id;

-- Associer la tâche à une catégorie
INSERT INTO TaskCategory (task_id, category_id) 
VALUES (last_insert_rowid(), 1);

-- Valider la transaction
COMMIT;

.mode csv
.output tasks_backup.csv
SELECT * FROM Tasks;
.output stdout

-- Exporter le schéma et les données
.output tasks_database.sql
.dump
.output stdout

-- Exporter une table spécifique
.output categories.sql
.dump Categories
.output stdout