use fv_value::Value;
use tokio_postgres::NoTls;
use crate::{ConnectError, Row};
use crate::ident::{mysql_col, mysql_ident as my_ident, pg_bare as bare, pg_ident as qident};
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('\'', "''")),
other => format!("'{}'", format!("{other:?}").replace('\'', "''")),
}
}
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(", ");
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}")))?;
}
}
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
}
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"
}
}
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(", ");
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)
));
}
}
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}")))?;
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(())
}