use super::prepare::Gated;
use super::read;
use crate::conn::OrFailure;
use crate::error::{Failure, Result};
use crate::output::Row;
use crate::sql::ident;
use mysql_async::prelude::Queryable;
use mysql_async::prelude::*;
use mysql_async::Conn;
use serde_json::{json, Value};
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct ResultSet {
pub index: usize,
pub columns: Vec<String>,
pub rows: Vec<Row>,
}
impl ResultSet {
#[must_use]
pub fn as_value(&self) -> Value {
json!({
"set": self.index,
"columns": self.columns,
"rows": self.rows,
})
}
}
pub fn build(routine: &str, database: Option<&str>, arguments: usize) -> Result<String> {
let name = match database {
Some(database) => ident::quote_qualified(&[database, routine])?,
None => ident::quote(routine)?,
};
let markers = vec!["?"; arguments].join(", ");
Ok(format!("CALL {name}({markers})"))
}
pub async fn execute(
conn: &mut Conn,
gated: &Gated,
conversion: &crate::types::Conversion,
) -> Result<Vec<ResultSet>> {
let mut result = conn
.exec_iter(gated.sql(), gated.params())
.await
.or_failure()?;
let mut sets = Vec::new();
while !result.is_empty() {
let columns = result.columns_ref().to_vec();
let labels = read::column_names(&columns);
let mut rows = Vec::new();
while let Some(row) = result.next().await.or_failure()? {
rows.push(read::convert_row(row, &columns, &labels, conversion)?);
}
if !columns.is_empty() {
sets.push(ResultSet {
index: sets.len() + 1,
columns: labels,
rows,
});
}
}
Ok(sets)
}
pub fn refuse_statement_as_name(routine: &str) -> Result<()> {
if !routine.contains(char::is_whitespace) && !routine.contains('(') {
return Ok(());
}
Err(Failure::usage(
"usage.not_a_routine_name",
format!("`{routine}` is a statement, and this command takes the name of a routine"),
)
.with_detail("detail", routine)
.with_hint("give the routine name alone, and its arguments with --param"))
}
const SIGNATURE: &str = "SELECT ORDINAL_POSITION, PARAMETER_NAME, PARAMETER_MODE \
FROM information_schema.PARAMETERS \
WHERE SPECIFIC_SCHEMA = COALESCE(:database, DATABASE()) \
AND SPECIFIC_NAME = :routine AND ORDINAL_POSITION > 0 \
ORDER BY ORDINAL_POSITION";
#[derive(Debug, Clone)]
pub struct Parameter {
pub position: usize,
pub name: String,
pub mode: String,
}
impl Parameter {
#[must_use]
pub fn carries_out(&self) -> bool {
let mode = self.mode.to_uppercase();
mode == "OUT" || mode == "INOUT"
}
}
pub async fn signature(
conn: &mut Conn,
database: Option<&str>,
routine: &str,
) -> Result<Vec<Parameter>> {
let rows: Vec<(u32, Option<String>, Option<String>)> = conn
.exec(
SIGNATURE,
mysql_async::params! {
"database" => database,
"routine" => routine,
},
)
.await
.or_failure()?;
Ok(rows
.into_iter()
.map(|(position, name, mode)| Parameter {
position: position as usize,
name: name.unwrap_or_default(),
mode: mode.unwrap_or_default(),
})
.collect())
}
#[derive(Debug, Clone)]
pub struct Planned {
pub sql: String,
pub bound: Vec<String>,
pub outputs: Vec<(String, String)>,
}
pub fn plan(
routine: &str,
database: Option<&str>,
declared: &[Parameter],
asked: &[String],
values: &[String],
) -> Result<Planned> {
let mut wanted = Vec::new();
for name in asked {
let found = declared
.iter()
.find(|parameter| parameter.name.eq_ignore_ascii_case(name) && parameter.carries_out());
let Some(parameter) = found else {
return Err(Failure::validation(
"sql.unknown_out_parameter",
format!("`{name}` is not an output parameter of this routine"),
)
.with_detail("detail", name.clone())
.with_hint(
"read the signature with `schema routines` and mark a parameter the routine declares as OUT or INOUT",
));
};
wanted.push(parameter.clone());
}
let name = match database {
Some(database) => ident::quote_qualified(&[database, routine])?,
None => ident::quote(routine)?,
};
let mut pieces = Vec::new();
let mut outputs = Vec::new();
for parameter in declared {
match wanted.iter().find(|out| out.position == parameter.position) {
Some(out) => {
let variable = format!("@mariadb_cli_out_{}", out.position);
pieces.push(variable.clone());
outputs.push((variable, out.name.clone()));
}
None => pieces.push("?".to_owned()),
}
}
Ok(Planned {
sql: format!("CALL {name}({})", pieces.join(", ")),
bound: values.to_vec(),
outputs,
})
}
async fn collected(
conn: &mut Conn,
outputs: &[(String, String)],
conversion: &crate::types::Conversion,
) -> Result<Row> {
if outputs.is_empty() {
return Ok(Row::new());
}
let projection = outputs
.iter()
.map(|(variable, name)| ident::quote(name).map(|alias| format!("{variable} AS {alias}")))
.collect::<Result<Vec<_>>>()?
.join(", ");
let mut result = conn
.exec_iter(format!("SELECT {projection}"), mysql_async::Params::Empty)
.await
.or_failure()?;
let columns = result.columns_ref().to_vec();
let Some(row) = result.next().await.or_failure()? else {
return Ok(Row::new());
};
let labels = read::column_names(&columns);
read::convert_row(row, &columns, &labels, conversion)
}
#[derive(Debug, Clone, Copy)]
pub struct Call<'a> {
pub routine: &'a str,
pub database: Option<&'a str>,
pub values: &'a [String],
pub outputs: &'a [String],
}
pub async fn serve(
call: Call<'_>,
conn: &mut Conn,
policy: &super::Policy,
write_state: &mut super::WriteState,
server_read_only: bool,
conversion: &crate::types::Conversion,
) -> Result<Value> {
refuse_statement_as_name(call.routine)?;
policy.admit_change("query call")?;
policy.admit_simulation("query call")?;
write_state.admit(server_read_only)?;
if call.outputs.is_empty() {
let request = build(call.routine, call.database, call.values.len())?;
let gated = super::prepare::gate(&request, call.values, policy)?;
let sets = execute(conn, &gated, conversion).await?;
return Ok(as_value(call.routine, call.database, &sets, &Row::new()));
}
let declared = signature(conn, call.database, call.routine).await?;
let planned = plan(
call.routine,
call.database,
&declared,
call.outputs,
call.values,
)?;
let gated = super::prepare::gate(&planned.sql, &planned.bound, policy)?;
let sets = execute(conn, &gated, conversion).await?;
let returned = collected(conn, &planned.outputs, conversion).await?;
Ok(as_value(call.routine, call.database, &sets, &returned))
}
#[must_use]
pub fn as_value(
routine: &str,
database: Option<&str>,
sets: &[ResultSet],
returned: &Row,
) -> Value {
json!({
"database": database,
"routine": routine,
"sets": sets.iter().map(ResultSet::as_value).collect::<Vec<_>>(),
"set_count": sets.len(),
"output_parameters": returned,
})
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn a_call_binds_one_marker_for_every_argument() {
assert_eq!(
build("recalcular", None, 0).expect("builds"),
"CALL `recalcular`()"
);
assert_eq!(
build("recalcular", None, 1).expect("builds"),
"CALL `recalcular`(?)"
);
assert_eq!(
build("recalcular", None, 3).expect("builds"),
"CALL `recalcular`(?, ?, ?)"
);
}
#[test]
fn the_database_holding_the_routine_is_quoted_part_by_part() {
assert_eq!(
build("recalcular", Some("loja"), 1).expect("builds"),
"CALL `loja`.`recalcular`(?)"
);
}
#[test]
fn a_routine_name_can_never_close_its_own_quoting() {
let built = build("p`(); DROP TABLE users; -- ", None, 0).expect("quotes");
assert_eq!(built, "CALL `p``(); DROP TABLE users; -- `()");
assert_eq!(
built.matches("``").count(),
1,
"the delimiter inside the name is doubled, not closed"
);
}
#[test]
fn a_name_that_cannot_be_quoted_is_refused_rather_than_pasted() {
assert!(build("", None, 0).is_err());
assert!(build("has\u{0}a control", None, 0).is_err());
}
#[test]
fn a_whole_statement_where_a_name_belongs_is_refused_with_the_real_reason() {
let failure = refuse_statement_as_name("CALL recalcular(1)").expect_err("must refuse");
assert_eq!(failure.code(), "usage.not_a_routine_name");
assert_eq!(failure.exit_number().code(), 2);
}
#[test]
fn a_plain_routine_name_passes() {
assert!(refuse_statement_as_name("recalcular").is_ok());
assert!(refuse_statement_as_name("loja.recalcular").is_ok());
}
#[test]
fn two_result_sets_reach_the_caller_as_two() {
let sets = vec![
ResultSet {
index: 1,
columns: vec!["a".to_owned()],
rows: vec![Row::new()],
},
ResultSet {
index: 2,
columns: vec!["b".to_owned()],
rows: Vec::new(),
},
];
let value = as_value("recalcular", Some("loja"), &sets, &Row::new());
assert_eq!(
value["database"], "loja",
"the answer must say which database the routine ran in"
);
assert_eq!(value["set_count"], 2);
assert_eq!(value["sets"][0]["set"], 1);
assert_eq!(value["sets"][0]["columns"][0], "a");
assert_eq!(value["sets"][1]["set"], 2);
assert_eq!(value["sets"][1]["columns"][0], "b");
}
#[test]
fn a_routine_that_answers_with_nothing_says_so_without_inventing_a_set() {
let value = as_value("recalcular", None, &[], &Row::new());
assert_eq!(value["set_count"], 0);
assert_eq!(value["sets"].as_array().expect("array").len(), 0);
}
}