fv-connect 0.1.0

FusionVault Conduit: read relational, file and REST sources into rows with mandatory access control pushed into the read, write results back with atomic promotion, and drive incremental base-ingest scans, changelogs and label normalisation checked against the published conformance vectors.
Documentation
//! Write-back path — land a transform's output rows into a target relational
//! table with a promotion discipline: write a STAGING table, then an
//! ATOMIC swap into place (so the previous good output stays current until the new one is complete).
//! Postgres and MySQL.

use fv_value::Value;
use tokio_postgres::NoTls;

use crate::{ConnectError, Row};
// Identifier quoting consolidated in crate::ident; aliased to the names this
// module always used so the SQL-building call sites read unchanged.
use crate::ident::{mysql_col, mysql_ident as my_ident, pg_bare as bare, pg_ident as qident};

/// The SQL column type for a column, inferred from its values (numbers → double precision, booleans →
/// boolean, everything else → text). Curated tables store timestamps as text, matching the reference.
fn col_type(rows: &[Row], col: &str) -> &'static str {
    let (mut num, mut boolean, mut other) = (false, false, false);
    for r in rows {
        match r.iter().find(|(k, _)| k == col).map(|(_, v)| v) {
            Some(Value::Num(_)) => num = true,
            Some(Value::Bool(_)) => boolean = true,
            Some(Value::Null) | None => {}
            _ => other = true,
        }
    }
    if other || (!num && !boolean) {
        "text"
    } else if boolean && !num {
        "boolean"
    } else {
        "double precision"
    }
}

fn sql_literal(v: &Value) -> String {
    match v {
        Value::Null => "NULL".to_string(),
        Value::Bool(b) => if *b { "TRUE" } else { "FALSE" }.to_string(),
        Value::Num(n) => {
            if n.fract() == 0.0 && n.is_finite() {
                format!("{}", *n as i64)
            } else {
                format!("{n}")
            }
        }
        Value::Str(s) => format!("'{}'", s.replace('\'', "''")),
        // lists/objects land as their text form (curated tables don't carry these today)
        other => format!("'{}'", format!("{other:?}").replace('\'', "''")),
    }
}

/// Write `rows` into `table` (schema-qualified) on Postgres and atomically promote. `columns` fixes
/// the output column order (e.g. the dataset's declared schema); if empty, the union of row keys is
/// used. A leak-free no-op is a valid outcome (an empty result still swaps in an empty table).
pub async fn write_and_promote_postgres(
    dsn: &str,
    table: &str,
    columns: &[String],
    rows: &[Row],
) -> Result<(), ConnectError> {
    let cols: Vec<String> = if !columns.is_empty() {
        columns.to_vec()
    } else {
        let mut seen: Vec<String> = Vec::new();
        for r in rows {
            for (k, _) in r {
                if !seen.contains(k) {
                    seen.push(k.clone());
                }
            }
        }
        seen
    };
    if cols.is_empty() {
        return Err(ConnectError(format!("cannot write '{table}': no columns")));
    }

    let (mut client, connection) = tokio_postgres::connect(dsn, NoTls)
        .await
        .map_err(|e| ConnectError(format!("connect: {e}")))?;
    let handle = tokio::spawn(async move {
        let _ = connection.await;
    });

    let staging = format!("{table}__stg");
    let ddl_cols = cols
        .iter()
        .map(|c| format!("{} {} NULL", bare(c), col_type(rows, c)))
        .collect::<Vec<_>>()
        .join(", ");

    let result: Result<(), ConnectError> = async {
        client
            .batch_execute(&format!("DROP TABLE IF EXISTS {}", qident(&staging)))
            .await
            .map_err(|e| ConnectError(format!("drop staging: {e}")))?;
        client
            .batch_execute(&format!("CREATE TABLE {} ({ddl_cols})", qident(&staging)))
            .await
            .map_err(|e| ConnectError(format!("create staging: {e}")))?;

        if !rows.is_empty() {
            let collist = cols.iter().map(|c| bare(c)).collect::<Vec<_>>().join(", ");
            // Batch inserts so a large output doesn't build one giant statement.
            for chunk in rows.chunks(500) {
                let values = chunk
                    .iter()
                    .map(|r| {
                        let cells = cols
                            .iter()
                            .map(|c| {
                                sql_literal(r.iter().find(|(k, _)| k == c).map(|(_, v)| v).unwrap_or(&Value::Null))
                            })
                            .collect::<Vec<_>>()
                            .join(", ");
                        format!("({cells})")
                    })
                    .collect::<Vec<_>>()
                    .join(", ");
                client
                    .batch_execute(&format!("INSERT INTO {} ({collist}) VALUES {values}", qident(&staging)))
                    .await
                    .map_err(|e| ConnectError(format!("insert: {e}")))?;
            }
        }

        // Atomic promote: rename current → __old, staging → target, drop __old, in one transaction.
        let tx = client
            .transaction()
            .await
            .map_err(|e| ConnectError(format!("begin: {e}")))?;
        let old = format!("{table}__old");
        let exists: bool = tx
            .query_one("SELECT to_regclass($1) IS NOT NULL AS e", &[&qident(table)])
            .await
            .map_err(|e| ConnectError(format!("exists check: {e}")))?
            .get("e");
        tx.batch_execute(&format!("DROP TABLE IF EXISTS {}", qident(&old)))
            .await
            .map_err(|e| ConnectError(format!("drop old: {e}")))?;
        if exists {
            tx.batch_execute(&format!("ALTER TABLE {} RENAME TO {}", qident(table), bare(&old)))
                .await
                .map_err(|e| ConnectError(format!("rename cur: {e}")))?;
        }
        tx.batch_execute(&format!("ALTER TABLE {} RENAME TO {}", qident(&staging), bare(table)))
            .await
            .map_err(|e| ConnectError(format!("rename staging: {e}")))?;
        if exists {
            tx.batch_execute(&format!("DROP TABLE {}", qident(&old)))
                .await
                .map_err(|e| ConnectError(format!("drop old2: {e}")))?;
        }
        tx.commit().await.map_err(|e| ConnectError(format!("commit: {e}")))?;
        Ok(())
    }
    .await;

    drop(client);
    let _ = handle.await;
    result
}

// ── MySQL write-back (atomic RENAME swap; MySQL DDL auto-commits so RENAME is the atomic point) ────

fn my_type(rows: &[Row], col: &str) -> &'static str {
    let (mut num, mut boolean, mut other) = (false, false, false);
    for r in rows {
        match r.iter().find(|(k, _)| k == col).map(|(_, v)| v) {
            Some(Value::Num(_)) => num = true,
            Some(Value::Bool(_)) => boolean = true,
            Some(Value::Null) | None => {}
            _ => other = true,
        }
    }
    if other || (!num && !boolean) {
        "text"
    } else if boolean && !num {
        "tinyint(1)"
    } else {
        "double"
    }
}

/// Write `rows` into a MySQL `table` (optionally schema-qualified) and atomically promote
/// (backtick idents; `RENAME TABLE cur TO old, stg TO cur` as the atomic swap). `columns` fixes output order, else the union of row keys is used.
pub async fn write_and_promote_mysql(
    url: &str,
    table: &str,
    columns: &[String],
    rows: &[Row],
) -> Result<(), ConnectError> {
    use mysql_async::prelude::Queryable;

    let cols: Vec<String> = if !columns.is_empty() {
        columns.to_vec()
    } else {
        let mut seen: Vec<String> = Vec::new();
        for r in rows {
            for (k, _) in r {
                if !seen.contains(k) {
                    seen.push(k.clone());
                }
            }
        }
        seen
    };
    if cols.is_empty() {
        return Err(ConnectError(format!("cannot write '{table}': no columns")));
    }

    let pool = mysql_async::Pool::new(url);
    let mut conn = pool
        .get_conn()
        .await
        .map_err(|e| ConnectError(format!("connect: {e}")))?;

    let staging = format!("{table}__stg");
    let old = format!("{table}__old");
    let ddl_cols = cols
        .iter()
        .map(|c| format!("{} {} NULL", mysql_col(c), my_type(rows, c)))
        .collect::<Vec<_>>()
        .join(", ");

    // A borrowed-conn closure returning a future trips lifetimes, so run statements via a local macro.
    macro_rules! q {
        ($sql:expr) => {{
            let sql = $sql;
            conn.query_drop(sql.as_str())
                .await
                .map_err(|e| ConnectError(format!("{sql}: {e}")))?;
        }};
    }

    q!(format!("DROP TABLE IF EXISTS {}", my_ident(&staging)));
    q!(format!("CREATE TABLE {} ({ddl_cols})", my_ident(&staging)));

    if !rows.is_empty() {
        let collist = cols.iter().map(|c| mysql_col(c)).collect::<Vec<_>>().join(", ");
        for chunk in rows.chunks(500) {
            let values = chunk
                .iter()
                .map(|r| {
                    let cells = cols
                        .iter()
                        .map(|c| sql_literal(r.iter().find(|(k, _)| k == c).map(|(_, v)| v).unwrap_or(&Value::Null)))
                        .collect::<Vec<_>>()
                        .join(", ");
                    format!("({cells})")
                })
                .collect::<Vec<_>>()
                .join(", ");
            q!(format!(
                "INSERT INTO {} ({collist}) VALUES {values}",
                my_ident(&staging)
            ));
        }
    }

    // Atomic swap.
    let exists: Option<i64> = conn
        .query_first(format!(
            "SELECT 1 FROM information_schema.tables WHERE table_schema = COALESCE(NULLIF('{}',''), DATABASE()) AND table_name = '{}'",
            table.split('.').next().filter(|_| table.contains('.')).unwrap_or("").replace('\'', "''"),
            table.split('.').next_back().unwrap_or(table).replace('\'', "''"),
        ))
        .await
        .map_err(|e| ConnectError(format!("exists check: {e}")))?;
    // RENAME targets MUST stay schema-qualified: an unqualified target moves the table into the
    // connection's default database (MySQL cross-db RENAME), which is exactly the wrong place.
    q!(format!("DROP TABLE IF EXISTS {}", my_ident(&old)));
    if exists.is_some() {
        q!(format!(
            "RENAME TABLE {} TO {}, {} TO {}",
            my_ident(table),
            my_ident(&old),
            my_ident(&staging),
            my_ident(table)
        ));
        q!(format!("DROP TABLE {}", my_ident(&old)));
    } else {
        q!(format!("RENAME TABLE {} TO {}", my_ident(&staging), my_ident(table)));
    }

    drop(conn);
    let _ = pool.disconnect().await;
    Ok(())
}