mnemo/
db.rs

1use anyhow::{Context, Result};
2use rusqlite::{Connection, OpenFlags};
3use std::path::Path;
4use std::time::{SystemTime, UNIX_EPOCH};
5
6use crate::migrations;
7
8/// Données nécessaires pour insérer une nouvelle commande.
9#[derive(Debug, Clone, Default)]
10pub struct NewCommand {
11    pub command: String,
12    pub cwd: Option<String>,
13    pub shell: Option<String>,
14    pub hostname: Option<String>,
15    pub exit_code: Option<i64>,
16    pub created_at: String,
17    /// Racine du dépôt Git (`git rev-parse --show-toplevel`), si applicable.
18    pub git_root: Option<String>,
19    /// Branche Git courante, si applicable.
20    pub git_branch: Option<String>,
21    /// URL du remote `origin`, si applicable.
22    pub git_remote: Option<String>,
23    /// Identifiant de session shell, si fourni (`MNEMO_SESSION_ID`).
24    pub session_id: Option<String>,
25}
26
27/// Commande lue depuis la base.
28///
29/// Certains champs (id, shell, hostname, exit_code) font partie du modèle de
30/// données mais ne sont pas tous affichés par le MVP de la TUI.
31#[allow(dead_code)]
32#[derive(Debug, Clone)]
33pub struct CommandRecord {
34    pub id: i64,
35    pub command: String,
36    pub cwd: Option<String>,
37    pub shell: Option<String>,
38    pub hostname: Option<String>,
39    pub exit_code: Option<i64>,
40    pub created_at: String,
41    pub git_root: Option<String>,
42    pub git_branch: Option<String>,
43    pub git_remote: Option<String>,
44    pub session_id: Option<String>,
45}
46
47/// Filtre optionnel appliqué à la recherche (contexte Git).
48#[derive(Debug, Clone, Default)]
49pub struct SearchFilter {
50    /// Filtre sur le projet : nom du dossier racine Git ou chemin `git_root`.
51    pub project: Option<String>,
52    /// Filtre sur la branche Git.
53    pub branch: Option<String>,
54}
55
56impl SearchFilter {
57    /// Vrai si aucun critère n'est défini.
58    #[allow(dead_code)]
59    pub fn is_empty(&self) -> bool {
60        self.project.is_none() && self.branch.is_none()
61    }
62}
63
64/// Filtre de requête avancé (`mnemo search`, `mnemo stats --since`).
65///
66/// Tous les critères sont **combinables** (ET logique). Les champs temporels
67/// `since`/`before` sont des bornes au format `YYYY-MM-DD HH:MM:SS` (ou un
68/// préfixe `YYYY-MM-DD`), comparées lexicographiquement à `created_at`, ce qui
69/// équivaut à une comparaison chronologique grâce au format ISO trié.
70#[derive(Debug, Clone, Default)]
71pub struct QueryFilter {
72    /// Projet : chemin `git_root` complet ou nom du dossier racine.
73    pub project: Option<String>,
74    /// Branche Git exacte.
75    pub branch: Option<String>,
76    /// Répertoire de travail exact.
77    pub cwd: Option<String>,
78    /// Shell exact (ex : `bash`).
79    pub shell: Option<String>,
80    /// Code de sortie exact.
81    pub exit_code: Option<i64>,
82    /// N'inclure que les échecs (`exit_code` présent et ≠ 0).
83    pub failed: bool,
84    /// Borne inférieure incluse : `created_at >= since`.
85    pub since: Option<String>,
86    /// Borne supérieure exclue : `created_at < before`.
87    pub before: Option<String>,
88}
89
90impl QueryFilter {
91    /// Construit le fragment `WHERE` et les paramètres liés associés.
92    ///
93    /// La clause est toujours valide (au minimum `1=1`), et chaque critère est
94    /// passé en paramètre lié (jamais interpolé) pour éviter toute injection.
95    fn build_where(&self) -> (String, Vec<Box<dyn rusqlite::ToSql>>) {
96        let mut clauses: Vec<String> = Vec::new();
97        let mut params: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
98
99        if let Some(branch) = &self.branch {
100            clauses.push("git_branch = ?".to_string());
101            params.push(Box::new(branch.clone()));
102        }
103        if let Some(project) = &self.project {
104            clauses.push("(git_root = ? OR git_root LIKE ?)".to_string());
105            params.push(Box::new(project.clone()));
106            params.push(Box::new(format!("%/{project}")));
107        }
108        if let Some(cwd) = &self.cwd {
109            clauses.push("cwd = ?".to_string());
110            params.push(Box::new(cwd.clone()));
111        }
112        if let Some(shell) = &self.shell {
113            clauses.push("shell = ?".to_string());
114            params.push(Box::new(shell.clone()));
115        }
116        if let Some(code) = self.exit_code {
117            clauses.push("exit_code = ?".to_string());
118            params.push(Box::new(code));
119        }
120        if self.failed {
121            clauses.push("exit_code IS NOT NULL AND exit_code != 0".to_string());
122        }
123        if let Some(since) = &self.since {
124            clauses.push("created_at >= ?".to_string());
125            params.push(Box::new(since.clone()));
126        }
127        if let Some(before) = &self.before {
128            clauses.push("created_at < ?".to_string());
129            params.push(Box::new(before.clone()));
130        }
131
132        let where_sql = if clauses.is_empty() {
133            "1 = 1".to_string()
134        } else {
135            clauses.join(" AND ")
136        };
137        (where_sql, params)
138    }
139}
140
141/// Charge les commandes correspondant à un [`QueryFilter`] avancé, des plus
142/// récentes aux plus anciennes. `limit` borne le nombre de lignes (`None` =
143/// toutes), utile pour `mnemo stats` qui agrège l'intégralité.
144pub fn fetch_query(
145    conn: &Connection,
146    filter: &QueryFilter,
147    limit: Option<usize>,
148) -> Result<Vec<CommandRecord>> {
149    let (where_sql, params) = filter.build_where();
150    let limit_sql = match limit {
151        Some(n) => format!("LIMIT {}", n as i64),
152        None => String::new(),
153    };
154    let sql = format!(
155        "SELECT id, command, cwd, shell, hostname, exit_code, created_at,
156                git_root, git_branch, git_remote, session_id
157         FROM commands
158         WHERE {where_sql}
159         ORDER BY created_at DESC, id DESC
160         {limit_sql}"
161    );
162    let mut stmt = conn.prepare(&sql)?;
163    let rows = stmt.query_map(rusqlite::params_from_iter(params.iter()), row_to_record)?;
164    let mut out = Vec::new();
165    for row in rows {
166        out.push(row?);
167    }
168    Ok(out)
169}
170
171/// Ouvre (ou crée) la base SQLite sur disque et initialise le schéma.
172pub fn open(path: &Path) -> Result<Connection> {
173    if let Some(parent) = path.parent() {
174        std::fs::create_dir_all(parent)
175            .with_context(|| format!("création du dossier {}", parent.display()))?;
176        crate::config::harden_dir(parent);
177    }
178    let conn = Connection::open(path)
179        .with_context(|| format!("ouverture de la base {}", path.display()))?;
180    migrations::apply(&conn)?;
181    // La base contient l'historique shell : permissions privées.
182    crate::config::harden_file(path);
183    Ok(conn)
184}
185
186/// Ouvre la base et renvoie aussi le résultat des migrations appliquées.
187/// Utilisé par `mnemo migrate` pour rendre compte de la transition de schéma.
188pub fn open_and_migrate(path: &Path) -> Result<(Connection, migrations::Outcome)> {
189    if let Some(parent) = path.parent() {
190        std::fs::create_dir_all(parent)
191            .with_context(|| format!("création du dossier {}", parent.display()))?;
192        crate::config::harden_dir(parent);
193    }
194    let conn = Connection::open(path)
195        .with_context(|| format!("ouverture de la base {}", path.display()))?;
196    let outcome = migrations::apply(&conn)?;
197    crate::config::harden_file(path);
198    Ok((conn, outcome))
199}
200
201/// Base SQLite en mémoire (utilisée pour les tests).
202#[cfg(test)]
203pub fn open_in_memory() -> Result<Connection> {
204    let conn = Connection::open_in_memory()?;
205    migrations::apply(&conn)?;
206    Ok(conn)
207}
208
209/// Ouvre une base existante en lecture seule, SANS créer ni modifier le schéma.
210/// Utilisé par `mnemo doctor` pour ne jamais altérer le système.
211pub fn open_readonly(path: &Path) -> Result<Connection> {
212    let conn = Connection::open_with_flags(path, OpenFlags::SQLITE_OPEN_READ_ONLY)
213        .with_context(|| format!("ouverture en lecture seule de {}", path.display()))?;
214    Ok(conn)
215}
216
217/// Indique si une table donnée existe dans la base.
218pub fn table_exists(conn: &Connection, name: &str) -> Result<bool> {
219    let n: i64 = conn.query_row(
220        "SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = ?1",
221        [name],
222        |row| row.get(0),
223    )?;
224    Ok(n > 0)
225}
226
227/// Hash FNV-1a 64 bits, déterministe, utilisé pour le dédoublonnage.
228///
229/// Le hash combine la commande et le répertoire courant : une même commande
230/// dans deux répertoires différents n'est donc pas considérée comme doublon.
231pub fn compute_hash(command: &str, cwd: Option<&str>) -> String {
232    const FNV_OFFSET: u64 = 0xcbf2_9ce4_8422_2325;
233    const FNV_PRIME: u64 = 0x0000_0100_0000_01b3;
234
235    let mut hash = FNV_OFFSET;
236    for b in command.bytes() {
237        hash ^= b as u64;
238        hash = hash.wrapping_mul(FNV_PRIME);
239    }
240    // Séparateur explicite entre commande et cwd.
241    hash ^= 0x1f;
242    hash = hash.wrapping_mul(FNV_PRIME);
243    if let Some(cwd) = cwd {
244        for b in cwd.bytes() {
245            hash ^= b as u64;
246            hash = hash.wrapping_mul(FNV_PRIME);
247        }
248    }
249    format!("{hash:016x}")
250}
251
252/// Insère une commande. Retourne `true` si elle a été insérée, `false` si
253/// c'était un doublon (même hash déjà présent).
254pub fn insert_command(conn: &Connection, cmd: &NewCommand) -> Result<bool> {
255    let hash = compute_hash(&cmd.command, cmd.cwd.as_deref());
256    let changed = conn.execute(
257        "INSERT OR IGNORE INTO commands
258            (command, cwd, shell, hostname, exit_code, created_at, hash,
259             git_root, git_branch, git_remote, session_id)
260         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)",
261        rusqlite::params![
262            cmd.command,
263            cmd.cwd,
264            cmd.shell,
265            cmd.hostname,
266            cmd.exit_code,
267            cmd.created_at,
268            hash,
269            cmd.git_root,
270            cmd.git_branch,
271            cmd.git_remote,
272            cmd.session_id,
273        ],
274    )?;
275    Ok(changed > 0)
276}
277
278/// Charge les commandes les plus récentes (limite paramétrable).
279#[allow(dead_code)]
280pub fn fetch_all(conn: &Connection, limit: usize) -> Result<Vec<CommandRecord>> {
281    fetch_filtered(conn, &SearchFilter::default(), limit)
282}
283
284/// Charge les commandes les plus récentes en appliquant un filtre de contexte
285/// Git optionnel (projet / branche). Le filtrage fuzzy sur le texte de la
286/// commande reste à la charge de l'appelant.
287pub fn fetch_filtered(
288    conn: &Connection,
289    filter: &SearchFilter,
290    limit: usize,
291) -> Result<Vec<CommandRecord>> {
292    // `project` correspond soit au chemin complet `git_root`, soit au nom du
293    // dossier racine (dernier segment du chemin).
294    let project_suffix = filter.project.as_ref().map(|p| format!("%/{p}"));
295    let mut stmt = conn.prepare(
296        "SELECT id, command, cwd, shell, hostname, exit_code, created_at,
297                git_root, git_branch, git_remote, session_id
298         FROM commands
299         WHERE (?1 IS NULL OR git_branch = ?1)
300           AND (?2 IS NULL OR git_root = ?2 OR git_root LIKE ?3)
301         ORDER BY created_at DESC, id DESC
302         LIMIT ?4",
303    )?;
304    let rows = stmt.query_map(
305        rusqlite::params![filter.branch, filter.project, project_suffix, limit as i64],
306        row_to_record,
307    )?;
308
309    let mut out = Vec::new();
310    for row in rows {
311        out.push(row?);
312    }
313    Ok(out)
314}
315
316/// Charge toutes les commandes correspondant au filtre Git (sans limite), pour
317/// le calcul des statistiques. Un filtre vide renvoie l'intégralité de la base.
318pub fn all_commands(conn: &Connection, filter: &SearchFilter) -> Result<Vec<CommandRecord>> {
319    let project_suffix = filter.project.as_ref().map(|p| format!("%/{p}"));
320    let mut stmt = conn.prepare(
321        "SELECT id, command, cwd, shell, hostname, exit_code, created_at,
322                git_root, git_branch, git_remote, session_id
323         FROM commands
324         WHERE (?1 IS NULL OR git_branch = ?1)
325           AND (?2 IS NULL OR git_root = ?2 OR git_root LIKE ?3)
326         ORDER BY created_at DESC, id DESC",
327    )?;
328    let rows = stmt.query_map(
329        rusqlite::params![filter.branch, filter.project, project_suffix],
330        row_to_record,
331    )?;
332    let mut out = Vec::new();
333    for row in rows {
334        out.push(row?);
335    }
336    Ok(out)
337}
338
339/// Convertit une ligne SQL en [`CommandRecord`].
340fn row_to_record(row: &rusqlite::Row) -> rusqlite::Result<CommandRecord> {
341    Ok(CommandRecord {
342        id: row.get(0)?,
343        command: row.get(1)?,
344        cwd: row.get(2)?,
345        shell: row.get(3)?,
346        hostname: row.get(4)?,
347        exit_code: row.get(5)?,
348        created_at: row.get(6)?,
349        git_root: row.get(7)?,
350        git_branch: row.get(8)?,
351        git_remote: row.get(9)?,
352        session_id: row.get(10)?,
353    })
354}
355
356/// Résumé d'une session de travail, agrégé depuis les commandes partageant un
357/// même `session_id`.
358#[derive(Debug, Clone)]
359pub struct SessionSummary {
360    pub session_id: String,
361    /// Nombre de commandes rattachées à la session.
362    pub count: i64,
363    /// Horodatage de la première commande (`YYYY-MM-DD HH:MM:SS`).
364    pub started_at: String,
365    /// Horodatage de la dernière commande.
366    pub ended_at: String,
367    /// Racine Git de la commande la plus récente de la session, si disponible.
368    pub git_root: Option<String>,
369}
370
371/// Liste les sessions connues, de la plus récente activité à la plus ancienne.
372///
373/// Seules les commandes portant un `session_id` non vide sont prises en compte ;
374/// les commandes importées ou enregistrées sans identifiant de session sont
375/// ignorées (elles ne constituent pas une session). Grâce au comportement des
376/// colonnes nues de SQLite avec `MAX(created_at)`, `git_root` provient de la
377/// commande la plus récente de chaque session.
378pub fn session_summaries(conn: &Connection, limit: Option<usize>) -> Result<Vec<SessionSummary>> {
379    let limit_sql = match limit {
380        Some(n) => format!("LIMIT {}", n as i64),
381        None => String::new(),
382    };
383    let sql = format!(
384        "SELECT session_id, COUNT(*) AS n,
385                MIN(created_at) AS started, MAX(created_at) AS ended,
386                git_root
387         FROM commands
388         WHERE session_id IS NOT NULL AND TRIM(session_id) <> ''
389         GROUP BY session_id
390         ORDER BY ended DESC, session_id DESC
391         {limit_sql}"
392    );
393    let mut stmt = conn.prepare(&sql)?;
394    let rows = stmt.query_map([], |row| {
395        Ok(SessionSummary {
396            session_id: row.get(0)?,
397            count: row.get(1)?,
398            started_at: row.get(2)?,
399            ended_at: row.get(3)?,
400            git_root: row.get(4)?,
401        })
402    })?;
403    let mut out = Vec::new();
404    for row in rows {
405        out.push(row?);
406    }
407    Ok(out)
408}
409
410/// Charge les commandes d'une session, dans l'ordre chronologique croissant.
411/// `limit` borne le nombre de commandes (`None` = toutes).
412pub fn session_commands(
413    conn: &Connection,
414    session_id: &str,
415    limit: Option<usize>,
416) -> Result<Vec<CommandRecord>> {
417    let limit_sql = match limit {
418        Some(n) => format!("LIMIT {}", n as i64),
419        None => String::new(),
420    };
421    let sql = format!(
422        "SELECT id, command, cwd, shell, hostname, exit_code, created_at,
423                git_root, git_branch, git_remote, session_id
424         FROM commands
425         WHERE session_id = ?1
426         ORDER BY created_at ASC, id ASC
427         {limit_sql}"
428    );
429    let mut stmt = conn.prepare(&sql)?;
430    let rows = stmt.query_map(rusqlite::params![session_id], row_to_record)?;
431    let mut out = Vec::new();
432    for row in rows {
433        out.push(row?);
434    }
435    Ok(out)
436}
437
438/// Identifiant de la session la plus récemment active, le cas échéant.
439pub fn latest_session_id(conn: &Connection) -> Result<Option<String>> {
440    let mut stmt = conn.prepare(
441        "SELECT session_id
442         FROM commands
443         WHERE session_id IS NOT NULL AND TRIM(session_id) <> ''
444         ORDER BY created_at DESC, id DESC
445         LIMIT 1",
446    )?;
447    let mut rows = stmt.query_map([], |row| row.get::<_, String>(0))?;
448    match rows.next() {
449        Some(r) => Ok(Some(r?)),
450        None => Ok(None),
451    }
452}
453
454/// Résumé agrégé de l'activité d'un projet (regroupement par `git_root`).
455#[derive(Debug, Clone)]
456pub struct ProjectSummary {
457    /// Racine Git du projet.
458    pub root: String,
459    /// Nombre total de commandes enregistrées.
460    pub command_count: i64,
461    /// Nombre de sessions distinctes (commandes portant un `session_id`).
462    pub session_count: i64,
463    /// Horodatage de la première commande connue.
464    pub first_activity: String,
465    /// Horodatage de la dernière commande connue.
466    pub last_activity: String,
467    /// Branches Git rencontrées, triées et dédupliquées.
468    pub branches: Vec<String>,
469    /// Un remote Git connu du projet, si disponible.
470    pub remote: Option<String>,
471}
472
473/// Découpe la concaténation `GROUP_CONCAT(DISTINCT git_branch)` en liste de
474/// branches triée, sans doublon ni valeur vide.
475fn split_branches(raw: Option<String>) -> Vec<String> {
476    let mut branches: Vec<String> = raw
477        .unwrap_or_default()
478        .split(',')
479        .map(|b| b.trim().to_string())
480        .filter(|b| !b.is_empty())
481        .collect();
482    branches.sort();
483    branches.dedup();
484    branches
485}
486
487/// Liste les projets connus de l'historique, du plus récemment actif au plus
488/// ancien. Seuls les `git_root` non nuls sont remontés. `limit` borne le nombre
489/// de projets (`None` = tous).
490pub fn project_summaries(conn: &Connection, limit: Option<usize>) -> Result<Vec<ProjectSummary>> {
491    let limit_sql = match limit {
492        Some(n) => format!("LIMIT {}", n as i64),
493        None => String::new(),
494    };
495    let sql = format!(
496        "SELECT git_root,
497                COUNT(*) AS n,
498                COUNT(DISTINCT CASE
499                    WHEN session_id IS NOT NULL AND TRIM(session_id) <> ''
500                    THEN session_id END) AS sessions,
501                MIN(created_at) AS first_at,
502                MAX(created_at) AS last_at,
503                GROUP_CONCAT(DISTINCT git_branch) AS branches,
504                MAX(git_remote) AS remote
505         FROM commands
506         WHERE git_root IS NOT NULL AND git_root <> ''
507         GROUP BY git_root
508         ORDER BY last_at DESC, git_root ASC
509         {limit_sql}"
510    );
511    let mut stmt = conn.prepare(&sql)?;
512    let rows = stmt.query_map([], project_summary_row)?;
513    let mut out = Vec::new();
514    for row in rows {
515        out.push(row?);
516    }
517    Ok(out)
518}
519
520/// Résumé d'un projet identifié par sa racine Git exacte, ou `None` si la racine
521/// est absente de l'historique.
522pub fn project_summary(conn: &Connection, root: &str) -> Result<Option<ProjectSummary>> {
523    let mut stmt = conn.prepare(
524        "SELECT git_root,
525                COUNT(*) AS n,
526                COUNT(DISTINCT CASE
527                    WHEN session_id IS NOT NULL AND TRIM(session_id) <> ''
528                    THEN session_id END) AS sessions,
529                MIN(created_at) AS first_at,
530                MAX(created_at) AS last_at,
531                GROUP_CONCAT(DISTINCT git_branch) AS branches,
532                MAX(git_remote) AS remote
533         FROM commands
534         WHERE git_root = ?1
535         GROUP BY git_root",
536    )?;
537    let mut rows = stmt.query_map(rusqlite::params![root], project_summary_row)?;
538    match rows.next() {
539        Some(r) => Ok(Some(r?)),
540        None => Ok(None),
541    }
542}
543
544/// Convertit une ligne agrégée en [`ProjectSummary`].
545fn project_summary_row(row: &rusqlite::Row) -> rusqlite::Result<ProjectSummary> {
546    Ok(ProjectSummary {
547        root: row.get(0)?,
548        command_count: row.get(1)?,
549        session_count: row.get(2)?,
550        first_activity: row.get(3)?,
551        last_activity: row.get(4)?,
552        branches: split_branches(row.get(5)?),
553        remote: row.get(6)?,
554    })
555}
556
557/// Liste les racines Git connues correspondant à `needle` : correspondance
558/// exacte sur `git_root`, ou suffixe `%/needle` (nom court du projet). Sert à
559/// résoudre l'argument de `mnemo project show|report`.
560pub fn match_project_roots(conn: &Connection, needle: &str) -> Result<Vec<String>> {
561    let suffix = format!("%/{needle}");
562    let mut stmt = conn.prepare(
563        "SELECT DISTINCT git_root
564         FROM commands
565         WHERE git_root IS NOT NULL AND git_root <> ''
566           AND (git_root = ?1 OR git_root LIKE ?2)
567         ORDER BY git_root ASC",
568    )?;
569    let rows = stmt.query_map(rusqlite::params![needle, suffix], |row| {
570        row.get::<_, String>(0)
571    })?;
572    let mut out = Vec::new();
573    for row in rows {
574        out.push(row?);
575    }
576    Ok(out)
577}
578
579/// Charge les commandes d'un projet (racine Git exacte), de la plus récente à la
580/// plus ancienne, en respectant un éventuel intervalle temporel et un filtre
581/// d'échecs. `limit` borne le nombre de résultats (`None` = tous).
582///
583/// En lecture seule : aucune commande n'est exécutée, le contenu (y compris les
584/// commandes déjà redactées) est restitué tel quel.
585pub fn project_records(
586    conn: &Connection,
587    root: &str,
588    since: Option<&str>,
589    before: Option<&str>,
590    failed_only: bool,
591    limit: Option<usize>,
592) -> Result<Vec<CommandRecord>> {
593    let limit_sql = match limit {
594        Some(n) => format!("LIMIT {}", n as i64),
595        None => String::new(),
596    };
597    let sql = format!(
598        "SELECT id, command, cwd, shell, hostname, exit_code, created_at,
599                git_root, git_branch, git_remote, session_id
600         FROM commands
601         WHERE git_root = ?1
602           AND (?2 IS NULL OR created_at >= ?2)
603           AND (?3 IS NULL OR created_at < ?3)
604           AND (?4 = 0 OR (exit_code IS NOT NULL AND exit_code <> 0))
605         ORDER BY created_at DESC, id DESC
606         {limit_sql}"
607    );
608    let mut stmt = conn.prepare(&sql)?;
609    let rows = stmt.query_map(
610        rusqlite::params![root, since, before, failed_only as i64],
611        row_to_record,
612    )?;
613    let mut out = Vec::new();
614    for row in rows {
615        out.push(row?);
616    }
617    Ok(out)
618}
619
620/// Nombre total de commandes stockées.
621pub fn count(conn: &Connection) -> Result<i64> {
622    let n = conn.query_row("SELECT COUNT(*) FROM commands", [], |row| row.get(0))?;
623    Ok(n)
624}
625
626/// Récupère une commande par son identifiant, ou `None` si absente.
627pub fn get_command(conn: &Connection, id: i64) -> Result<Option<CommandRecord>> {
628    let mut stmt = conn.prepare(
629        "SELECT id, command, cwd, shell, hostname, exit_code, created_at,
630                git_root, git_branch, git_remote, session_id
631         FROM commands WHERE id = ?1",
632    )?;
633    let mut rows = stmt.query_map([id], row_to_record)?;
634    match rows.next() {
635        Some(r) => Ok(Some(r?)),
636        None => Ok(None),
637    }
638}
639
640/// Supprime une commande par identifiant, dans une transaction. Renvoie le
641/// nombre de lignes effectivement supprimées (0 si l'ID n'existait pas).
642pub fn delete_command(conn: &Connection, id: i64) -> Result<usize> {
643    let tx = conn.unchecked_transaction()?;
644    let n = tx.execute("DELETE FROM commands WHERE id = ?1", [id])?;
645    tx.commit()?;
646    Ok(n)
647}
648
649/// Applique des redactions de texte de commande en une seule transaction.
650///
651/// Seul le champ `command` est mis à jour ; tous les autres champs
652/// (`created_at`, `cwd`, `exit_code`, contexte Git, `session_id`…) sont laissés
653/// intacts. Requêtes paramétrées. Renvoie le nombre de lignes effectivement
654/// modifiées. Atomique : en cas d'erreur, la transaction n'est pas validée.
655pub fn apply_redactions(conn: &Connection, items: &[(i64, String)]) -> Result<usize> {
656    let tx = conn.unchecked_transaction()?;
657    let mut changed = 0usize;
658    {
659        let mut stmt = tx.prepare("UPDATE commands SET command = ?1 WHERE id = ?2")?;
660        for (id, command) in items {
661            changed += stmt.execute(rusqlite::params![command, id])?;
662        }
663    }
664    tx.commit()?;
665    Ok(changed)
666}
667
668/// Compte les commandes plus anciennes que `cutoff` (format `YYYY-MM-DD
669/// HH:MM:SS`), en respectant le filtre de contexte Git.
670pub fn count_older_than(conn: &Connection, cutoff: &str, filter: &SearchFilter) -> Result<i64> {
671    let project_suffix = filter.project.as_ref().map(|p| format!("%/{p}"));
672    let n = conn.query_row(
673        "SELECT COUNT(*) FROM commands
674         WHERE created_at < ?1
675           AND (?2 IS NULL OR git_branch = ?2)
676           AND (?3 IS NULL OR git_root = ?3 OR git_root LIKE ?4)",
677        rusqlite::params![cutoff, filter.branch, filter.project, project_suffix],
678        |row| row.get(0),
679    )?;
680    Ok(n)
681}
682
683/// Charge un échantillon de commandes plus anciennes que `cutoff` (les plus
684/// récentes d'abord), pour prévisualiser un `prune`.
685pub fn fetch_older_than(
686    conn: &Connection,
687    cutoff: &str,
688    filter: &SearchFilter,
689    limit: usize,
690) -> Result<Vec<CommandRecord>> {
691    let project_suffix = filter.project.as_ref().map(|p| format!("%/{p}"));
692    let mut stmt = conn.prepare(
693        "SELECT id, command, cwd, shell, hostname, exit_code, created_at,
694                git_root, git_branch, git_remote, session_id
695         FROM commands
696         WHERE created_at < ?1
697           AND (?2 IS NULL OR git_branch = ?2)
698           AND (?3 IS NULL OR git_root = ?3 OR git_root LIKE ?4)
699         ORDER BY created_at DESC, id DESC
700         LIMIT ?5",
701    )?;
702    let rows = stmt.query_map(
703        rusqlite::params![
704            cutoff,
705            filter.branch,
706            filter.project,
707            project_suffix,
708            limit as i64
709        ],
710        row_to_record,
711    )?;
712    let mut out = Vec::new();
713    for row in rows {
714        out.push(row?);
715    }
716    Ok(out)
717}
718
719/// Supprime les commandes plus anciennes que `cutoff` (en respectant le filtre
720/// Git), dans une transaction. Renvoie le nombre de lignes supprimées.
721pub fn delete_older_than(conn: &Connection, cutoff: &str, filter: &SearchFilter) -> Result<usize> {
722    let project_suffix = filter.project.as_ref().map(|p| format!("%/{p}"));
723    let tx = conn.unchecked_transaction()?;
724    let n = tx.execute(
725        "DELETE FROM commands
726         WHERE created_at < ?1
727           AND (?2 IS NULL OR git_branch = ?2)
728           AND (?3 IS NULL OR git_root = ?3 OR git_root LIKE ?4)",
729        rusqlite::params![cutoff, filter.branch, filter.project, project_suffix],
730    )?;
731    tx.commit()?;
732    Ok(n)
733}
734
735/// Horodatage courant au format `YYYY-MM-DD HH:MM:SS` (UTC).
736pub fn now_timestamp() -> String {
737    let secs = SystemTime::now()
738        .duration_since(UNIX_EPOCH)
739        .map(|d| d.as_secs())
740        .unwrap_or(0);
741    format_timestamp(secs)
742}
743
744/// Convertit un timestamp Unix (secondes UTC) en `YYYY-MM-DD HH:MM:SS`.
745pub fn format_timestamp(secs: u64) -> String {
746    let days = (secs / 86_400) as i64;
747    let rem = secs % 86_400;
748    let hour = rem / 3600;
749    let min = (rem % 3600) / 60;
750    let sec = rem % 60;
751    let (y, m, d) = civil_from_days(days);
752    format!("{y:04}-{m:02}-{d:02} {hour:02}:{min:02}:{sec:02}")
753}
754
755/// Algorithme de Howard Hinnant : jours depuis l'époque -> (année, mois, jour).
756fn civil_from_days(z: i64) -> (i64, u32, u32) {
757    let z = z + 719_468;
758    let era = if z >= 0 { z } else { z - 146_096 } / 146_097;
759    let doe = (z - era * 146_097) as u64; // [0, 146096]
760    let yoe = (doe - doe / 1460 + doe / 36524 - doe / 146_096) / 365; // [0, 399]
761    let y = yoe as i64 + era * 400;
762    let doy = doe - (365 * yoe + yoe / 4 - yoe / 100); // [0, 365]
763    let mp = (5 * doy + 2) / 153; // [0, 11]
764    let d = (doy - (153 * mp + 2) / 5 + 1) as u32; // [1, 31]
765    let m = if mp < 10 { mp + 3 } else { mp - 9 } as u32; // [1, 12]
766    let y = if m <= 2 { y + 1 } else { y };
767    (y, m, d)
768}
769
770/// Valide une date au format `AAAA-MM-JJ` (sans heure).
771pub fn is_valid_date(s: &str) -> bool {
772    let b = s.as_bytes();
773    if b.len() != 10 {
774        return false;
775    }
776    for (i, c) in b.iter().enumerate() {
777        let ok = match i {
778            4 | 7 => *c == b'-',
779            _ => c.is_ascii_digit(),
780        };
781        if !ok {
782            return false;
783        }
784    }
785    let month: u32 = s[5..7].parse().unwrap_or(0);
786    let day: u32 = s[8..10].parse().unwrap_or(0);
787    (1..=12).contains(&month) && (1..=31).contains(&day)
788}
789
790/// Secondes Unix courantes (UTC).
791fn now_secs() -> u64 {
792    SystemTime::now()
793        .duration_since(UNIX_EPOCH)
794        .map(|d| d.as_secs())
795        .unwrap_or(0)
796}
797
798/// Résout une borne inférieure `--since` : durée (`24h`, `7d`, `2w`, `3m`, `1y`)
799/// ou date `AAAA-MM-JJ`. Renvoie `None` si la spec est invalide (**jamais de
800/// panique**) : l'appelant peut alors ignorer le filtre proprement.
801pub fn resolve_since(spec: &str) -> Option<String> {
802    let spec = spec.trim();
803    if let Ok(secs) = crate::prune::parse_duration(spec) {
804        return Some(format_timestamp(now_secs().saturating_sub(secs)));
805    }
806    if is_valid_date(spec) {
807        return Some(format!("{spec} 00:00:00"));
808    }
809    None
810}
811
812/// Résout une borne supérieure `--before` : date `AAAA-MM-JJ` (exclue) ou durée.
813/// Renvoie `None` si la spec est invalide (pas de panique).
814pub fn resolve_before(spec: &str) -> Option<String> {
815    let spec = spec.trim();
816    if is_valid_date(spec) {
817        // `created_at < "AAAA-MM-JJ"` exclut tout ce jour : « avant cette date ».
818        return Some(spec.to_string());
819    }
820    if let Ok(secs) = crate::prune::parse_duration(spec) {
821        return Some(format_timestamp(now_secs().saturating_sub(secs)));
822    }
823    None
824}
825
826#[cfg(test)]
827mod tests {
828    use super::*;
829
830    #[test]
831    fn hash_stable_et_distingue_le_cwd() {
832        let a = compute_hash("ls -la", Some("/home"));
833        let b = compute_hash("ls -la", Some("/home"));
834        let c = compute_hash("ls -la", Some("/tmp"));
835        assert_eq!(a, b);
836        assert_ne!(a, c);
837    }
838
839    #[test]
840    fn insertion_et_dedoublonnage() {
841        let conn = open_in_memory().unwrap();
842        let cmd = NewCommand {
843            command: "echo hi".into(),
844            cwd: Some("/tmp".into()),
845            shell: Some("bash".into()),
846            hostname: Some("host".into()),
847            exit_code: Some(0),
848            created_at: now_timestamp(),
849            ..Default::default()
850        };
851        assert!(insert_command(&conn, &cmd).unwrap());
852        // Même hash -> doublon ignoré.
853        assert!(!insert_command(&conn, &cmd).unwrap());
854        assert_eq!(count(&conn).unwrap(), 1);
855    }
856
857    #[test]
858    fn fetch_renvoie_les_commandes() {
859        let conn = open_in_memory().unwrap();
860        for c in ["a", "b", "c"] {
861            insert_command(
862                &conn,
863                &NewCommand {
864                    command: c.into(),
865                    cwd: None,
866                    shell: None,
867                    hostname: None,
868                    exit_code: None,
869                    created_at: now_timestamp(),
870                    ..Default::default()
871                },
872            )
873            .unwrap();
874        }
875        let all = fetch_all(&conn, 100).unwrap();
876        assert_eq!(all.len(), 3);
877    }
878
879    #[test]
880    fn format_timestamp_connu() {
881        // 1609459200 = 2021-01-01 00:00:00 UTC
882        assert_eq!(format_timestamp(1_609_459_200), "2021-01-01 00:00:00");
883        // 0 = 1970-01-01 00:00:00 UTC
884        assert_eq!(format_timestamp(0), "1970-01-01 00:00:00");
885    }
886
887    #[test]
888    fn fetch_filtered_par_projet_et_branche() {
889        let conn = open_in_memory().unwrap();
890        let insert = |command: &str, root: &str, branch: &str| {
891            insert_command(
892                &conn,
893                &NewCommand {
894                    command: command.into(),
895                    cwd: Some(root.into()),
896                    created_at: now_timestamp(),
897                    git_root: Some(root.into()),
898                    git_branch: Some(branch.into()),
899                    ..Default::default()
900                },
901            )
902            .unwrap();
903        };
904        insert("cargo build", "/home/u/proj/mnemo", "main");
905        insert("cargo test", "/home/u/proj/mnemo", "dev");
906        insert("ls", "/home/u/proj/autre", "main");
907
908        // Filtre par nom de projet (dernier segment du chemin).
909        let by_name = fetch_filtered(
910            &conn,
911            &SearchFilter {
912                project: Some("mnemo".into()),
913                branch: None,
914            },
915            100,
916        )
917        .unwrap();
918        assert_eq!(by_name.len(), 2);
919        assert!(by_name
920            .iter()
921            .all(|r| r.git_root.as_deref() == Some("/home/u/proj/mnemo")));
922
923        // Filtre par chemin git_root complet.
924        let by_path = fetch_filtered(
925            &conn,
926            &SearchFilter {
927                project: Some("/home/u/proj/autre".into()),
928                branch: None,
929            },
930            100,
931        )
932        .unwrap();
933        assert_eq!(by_path.len(), 1);
934
935        // Filtre par branche.
936        let by_branch = fetch_filtered(
937            &conn,
938            &SearchFilter {
939                project: None,
940                branch: Some("main".into()),
941            },
942            100,
943        )
944        .unwrap();
945        assert_eq!(by_branch.len(), 2);
946
947        // Combinaison projet + branche.
948        let both = fetch_filtered(
949            &conn,
950            &SearchFilter {
951                project: Some("mnemo".into()),
952                branch: Some("dev".into()),
953            },
954            100,
955        )
956        .unwrap();
957        assert_eq!(both.len(), 1);
958        assert_eq!(both[0].command, "cargo test");
959    }
960
961    /// Insère une commande en forçant `created_at` (pour tester les bornes).
962    fn insert_at(conn: &Connection, command: &str, shell: &str, exit: Option<i64>, when: &str) {
963        insert_command(
964            conn,
965            &NewCommand {
966                command: command.into(),
967                cwd: Some("/tmp".into()),
968                shell: Some(shell.into()),
969                hostname: Some("host".into()),
970                exit_code: exit,
971                created_at: when.into(),
972                git_root: None,
973                git_branch: None,
974                git_remote: None,
975                session_id: None,
976            },
977        )
978        .unwrap();
979    }
980
981    #[test]
982    fn query_filter_combine_les_criteres() {
983        let conn = open_in_memory().unwrap();
984        insert_at(&conn, "ok-bash", "bash", Some(0), "2026-01-01 10:00:00");
985        insert_at(&conn, "ko-bash", "bash", Some(1), "2026-03-01 10:00:00");
986        insert_at(&conn, "ok-zsh", "zsh", Some(0), "2026-06-01 10:00:00");
987
988        // Échecs uniquement.
989        let failed = fetch_query(
990            &conn,
991            &QueryFilter {
992                failed: true,
993                ..Default::default()
994            },
995            None,
996        )
997        .unwrap();
998        assert_eq!(failed.len(), 1);
999        assert_eq!(failed[0].command, "ko-bash");
1000
1001        // Code de sortie exact.
1002        let ok = fetch_query(
1003            &conn,
1004            &QueryFilter {
1005                exit_code: Some(0),
1006                ..Default::default()
1007            },
1008            None,
1009        )
1010        .unwrap();
1011        assert_eq!(ok.len(), 2);
1012
1013        // Shell + borne temporelle (avant le 2026-04-01).
1014        let bash_before = fetch_query(
1015            &conn,
1016            &QueryFilter {
1017                shell: Some("bash".into()),
1018                before: Some("2026-04-01".into()),
1019                ..Default::default()
1020            },
1021            None,
1022        )
1023        .unwrap();
1024        assert_eq!(bash_before.len(), 2);
1025
1026        // Borne since incluse.
1027        let since = fetch_query(
1028            &conn,
1029            &QueryFilter {
1030                since: Some("2026-03-01 00:00:00".into()),
1031                ..Default::default()
1032            },
1033            None,
1034        )
1035        .unwrap();
1036        assert_eq!(since.len(), 2);
1037
1038        // Limite respectée.
1039        let limited = fetch_query(&conn, &QueryFilter::default(), Some(1)).unwrap();
1040        assert_eq!(limited.len(), 1);
1041    }
1042}
mnemo/db.rs

mnemo/
db.rs
