SQLite backup

use sqlite_backup.qweruse sqlite_backup, sqlite_integrity from sqlite_backup      // Quillo 0.6: only these

rusqlite = { version = "0.32", features = ["bundled", "backup"] }

// A nightly backup of the shop's database: a consistent copy, checked, summed up, and a second copy for the off-site disk.////   quillo run sqlite_backup.qwe      then open http://localhost:8080/backupuse "sqlite_backup.qwer"db.open("data/shop.db")db.exec("create table if not exists orders (id integer primary key, customer text, total real)")db.exec("create table if not exists customers (id integer primary key, name text)")route GET /backup {    db.exec("delete from orders")    for i in range(250) { db.exec("insert into orders (customer, total) values (?, ?)", `customer ${i % 40}`, i * 1.5) }    // The program's own database is copied by its own SQLite...    write_file("data/backups/.keep", "")          // vacuum into makes no folders    delete_file("data/backups/shop.db")           // nor replaces a file    db.exec("vacuum into ?", "data/backups/shop.db")?    // ...and the copy, which nothing has open, checked and copied again.    {        check: sqlite_integrity("data/backups/shop.db")?,        info: sqlite_info("data/backups/shop.db")?,        offsite: sqlite_backup("data/backups/shop.db", "data/offsite/shop.db")?,    }}

//! SQLite databases backed up while in use, checked for damage, and summed up (tables and rows): for nightly backups you can trust.//! version: 1.0.0//!//! [dependencies]//! rusqlite = { version = "0.32", features = ["bundled", "backup"] }use rusqlite::{Connection, OpenFlags};use serde_json::{json, Value};fn open_ro(path: &str) -> Result<Connection, String> {    if !std::path::Path::new(path).is_file() {        return Err(format!("{}: no such database file", path));    }    Connection::open_with_flags(path, OpenFlags::SQLITE_OPEN_READ_ONLY | OpenFlags::SQLITE_OPEN_NO_MUTEX).map_err(|e| format!("{}: {}", path, e))}/// Copies the SQLite database `from` to `to` (folders made, an old `to` replaced) with SQLite's online backup, a page at a time:/// other programs may go on reading and writing `from` meanwhile, and the copy is whole and consistent. `{ pages, bytes }`./// For the database this program opened with `db`, use `db.exec("vacuum into ?", to)` instead: two SQLite libraries in one process must not open one file.pub fn sqlite_backup(from: &str, to: &str) -> Result<Value, String> {    let src = open_ro(from)?;    if let Some(dir) = std::path::Path::new(to).parent().filter(|d| !d.as_os_str().is_empty()) {        std::fs::create_dir_all(dir).map_err(|e| format!("{}: {}", dir.display(), e))?;    }    let tmp = format!("{}.part", to);    let _ = std::fs::remove_file(&tmp);    {        let mut dst = Connection::open(&tmp).map_err(|e| format!("{}: {}", tmp, e))?;        let b = rusqlite::backup::Backup::new(&src, &mut dst).map_err(|e| e.to_string())?;        b.run_to_completion(256, std::time::Duration::from_millis(5), None).map_err(|e| format!("backup of {}: {}", from, e))?;    }    std::fs::rename(&tmp, to).map_err(|e| format!("{}: {}", to, e))?;    let pages: i64 = open_ro(to)?.query_row("pragma page_count", [], |r| r.get(0)).map_err(|e| e.to_string())?;    Ok(json!({ "pages": pages, "bytes": std::fs::metadata(to).map_err(|e| e.to_string())?.len() }))}/// SQLite's own check of a database file: `{ ok, problems }`, `problems` the first 100 it finds (empty when ok). Run it on each backup.pub fn sqlite_integrity(path: &str) -> Result<Value, String> {    let c = open_ro(path)?;    let mut st = c.prepare("pragma integrity_check(100)").map_err(|e| format!("{}: {}", path, e))?;    let rows: Vec<String> = st.query_map([], |r| r.get::<_, String>(0)).map_err(|e| format!("{}: {}", path, e))?.filter_map(Result::ok).collect();    let ok = rows.len() == 1 && rows[0] == "ok";    Ok(json!({ "ok": ok, "problems": if ok { vec![] } else { rows } }))}/// A database file's summary: `{ bytes, page_size, pages, journal_mode, user_version, tables }`, each table `{ name, rows }`: to compare a backup with what it came from.pub fn sqlite_info(path: &str) -> Result<Value, String> {    let c = open_ro(path)?;    let e = |e: rusqlite::Error| format!("{}: {}", path, e);    let one = |sql: &str| -> Result<Value, String> {        c.query_row(sql, [], |r| r.get::<_, rusqlite::types::Value>(0)).map(|v| match v {            rusqlite::types::Value::Integer(i) => Value::from(i),            rusqlite::types::Value::Text(s) => Value::from(s),            _ => Value::Null,        }).map_err(e)    };    let mut st = c.prepare("select name from sqlite_schema where type = 'table' and name not like 'sqlite_%' order by name").map_err(e)?;    let names: Vec<String> = st.query_map([], |r| r.get(0)).map_err(e)?.filter_map(Result::ok).collect();    let mut tables = vec![];    for n in names.iter().take(1000) {        let rows: i64 = c.query_row(&format!("select count(*) from \"{}\"", n.replace('"', "\"\"")), [], |r| r.get(0)).map_err(e)?;        tables.push(json!({ "name": n, "rows": rows }));    }    Ok(json!({        "bytes": std::fs::metadata(path).map_err(|e| e.to_string())?.len(),        "page_size": one("pragma page_size")?,        "pages": one("pragma page_count")?,        "journal_mode": one("pragma journal_mode")?,        "user_version": one("pragma user_version")?,        "tables": tables,    }))}