tag2upload_service_manager/
db_schema.rs

1//! Programmatic schema generaton
2//!
3//! We don't have a macro facility for SQL.  Instead, this file
4//! simply generates the too-formulaic parts using Rust code.
5//!
6//! **Do not use information from `db_data.rs`**, eg via
7//! `bsql_column_names`.  That would bypass our detection of
8//! schema changes which might need explicit migration handling.
9// TODO ^ we don't have that detection yet.
10
11use crate::prelude::*;
12use db_migration::MigrationData;
13
14const SCHEMA_BASE: &str = include_str!("schema-base.sql");
15
16pub const SCHEMA_VERSION: SchemaVersion = 2;
17
18fn add_stats_triggers(
19    s: &mut String,
20    base_conn: &rusqlite::Connection,
21) -> Result<(), SchemaGenerationError> {
22
23    /*
24     * Runes for initial population, and/or regeneration:
25
26  BEGIN;
27  DELETE FROM stats_by_shown_status;
28  INSERT INTO stats_by_shown_status SELECT status AS shown_status, count(*) AS n_jobs FROM jobs WHERE duplicate_of IS NULL GROUP BY status;
29  INSERT INTO stats_by_shown_status SELECT 'Duplicate' AS shown_status, count(*) AS n_jobs FROM jobs WHERE duplicate_of IS NOT NULL;
30
31     * This inserts only rows with nonempty values.
32     * Zero rows will be added by db_workflow::startup_reset as needed.
33     */
34
35    let table = "stats_by_shown_status";
36    let mut trigs = BTreeMap::<_, Vec<String>>::new();
37
38    for (del_ins, old_new, delta) in [
39        ("DELETE", "OLD", "- 1"),
40        ("INSERT", "NEW", "+ 1"),
41    ] {
42        for (trig_kw, trig_cond) in [
43            (del_ins, ""),
44            ("UPDATE", " OF status, duplicate_of"),
45        ] {
46            trigs.entry(
47                (trig_kw, trig_cond)
48            ).or_default().push(format!(
49"        UPDATE {table}
50           SET n_jobs = n_jobs {delta}
51         WHERE shown_status = IIF(
52                   {old_new}.duplicate_of IS NOT NULL,
53                   'Duplicate',
54                   {old_new}.status
55         );
56"
57            ));
58        }
59    }
60
61    for ((trig_kw, trig_cond), bodies) in trigs {
62        let trig_name = format!("{table}_{trig_kw}")
63            .to_ascii_lowercase();
64
65        writeln!(s, 
66"CREATE TRIGGER IF NOT EXISTS {trig_name}
67    AFTER {trig_kw}{trig_cond}
68    ON jobs
69    FOR EACH ROW
70    BEGIN"
71        )?;
72        for b in bodies {
73            write!(s, "{b}")?;
74        }
75        writeln!(s,
76"    END;"
77        )?;
78    }
79
80    {
81        // Set up history recording trigger,
82        // on all the value columsn in `job_history`,
83        // which are those in `JobState`.
84
85        let cols = db_support::table_column_names(base_conn, "job_history")?
86            .into_iter().filter(|s| s != "histent")
87            .collect_vec();
88        let cols = || cols.iter().map(|s| &**s);
89        let col_names = cols()
90            .intersperse(", ").collect::<String>();
91        let col_values = cols().map(|c| format!("OLD.{c}"))
92            .intersperse(", ".into()).collect::<String>();
93        writeln!(s,
94"CREATE TRIGGER IF NOT EXISTS job_history_trigger
95    AFTER UPDATE
96    OF {col_names}
97    ON jobs
98    FOR EACH ROW
99    BEGIN
100        INSERT INTO job_history ({col_names})
101             VALUES ({col_values});
102     END;"
103        )?;
104    }
105
106    Ok(())
107}
108
109pub fn migration_data() -> MigrationData {
110    #[allow(unused)] // There might be no migrations.
111    use db_migration::{Migration, Exception as E};
112
113    MigrationData {
114        schemas: vec![
115            db_migration::Schema {
116                version: SCHEMA_VERSION,
117                schema: db_schema::schema(),
118            },
119        ],
120        migrations: vec![
121            Migration {
122                old_version: 1,
123                new_version: 2,
124                exceptions: vec![
125                    E::ReplacementColumnValue {
126                        table: "jobs",
127                        col: "retry_earliest",
128                        val_sql: "0".into(),
129                    },
130                    E::ReplacementColumnValue {
131                        table: "jobs",
132                        col: "retry_salient_count",
133                        val_sql: "0".into(),
134                    },
135                    E::ReplacementColumnValue {
136                        table: "job_history",
137                        col: "retry_salient_count",
138                        val_sql: "0".into(),
139                    },
140                ],
141            },
142        ],
143    }
144}
145
146pub fn schema() -> String {
147    (|| {
148        let mut s = String::new();
149
150        write!(s, "{SCHEMA_BASE}")?;
151
152        let base_conn = rusqlite::Connection::open_in_memory()
153            .db_context("open")?;
154        base_conn.execute_batch(&s)
155            .db_context("execute base")?;
156
157        add_stats_triggers(&mut s, &base_conn)?;
158
159        Ok::<_, SchemaGenerationError>(s)
160    })().expect("failed to construct dynamic parts of schema string")
161}
tag2upload_service_manager/db_schema.rs

tag2upload_service_manager/
db_schema.rs
