use std::borrow::Cow;
use ruprizzle::sqlx::{Column, ColumnIndex, Decode, Row, Type};
use ruprizzle::{Executor, Pool, RowBatch, Value};
use ruprizzle_core::ir::{Field, FieldKind, Provider, ScalarType};
use ruprizzle_dialect::DbDialect;
pub type TextRow = Vec<Option<String>>;
#[must_use]
pub fn to_text(provider: Provider, expr: &str) -> String {
match provider {
Provider::Postgres => format!("({expr})::text"),
Provider::Sqlite => format!("CAST({expr} AS TEXT)"),
Provider::Mysql => format!("CAST({expr} AS CHAR)"),
}
}
pub async fn fetch_text_rows(
pool: &Pool,
sql: String,
binds: Vec<Value>,
width: usize,
) -> Result<Vec<TextRow>, String> {
let batch = pool
.fetch_all_raw(Cow::Owned(sql), binds)
.await
.map_err(|e| e.to_string())?;
match batch {
RowBatch::Any(rows) => rows.iter().map(|row| row_cells(row, width)).collect(),
RowBatch::Postgres(rows) => rows.iter().map(|row| row_cells(row, width)).collect(),
RowBatch::Sqlite(rows) => rows.iter().map(|row| row_cells(row, width)).collect(),
RowBatch::Mysql(rows) => rows.iter().map(|row| row_cells(row, width)).collect(),
_ => Err(
"this database driver is not supported by Studio; use the default sqlx driver"
.to_string(),
),
}
}
pub async fn fetch_count(pool: &Pool, sql: String, binds: Vec<Value>) -> Result<i64, String> {
let rows = fetch_text_rows(pool, sql, binds, 1).await?;
rows.first()
.and_then(|row| row.first().cloned().flatten())
.ok_or_else(|| "count query returned no rows".to_string())
.and_then(|text| {
text.parse::<i64>()
.map_err(|_| format!("count query returned `{text}`, which is not a number"))
})
}
pub async fn execute(pool: &Pool, sql: String, binds: Vec<Value>) -> Result<u64, String> {
pool.execute_raw(Cow::Owned(sql), binds)
.await
.map_err(|e| e.to_string())
}
pub async fn fetch_dynamic(
pool: &Pool,
sql: String,
binds: Vec<Value>,
) -> Result<(Vec<String>, Vec<TextRow>), String> {
let batch = pool
.fetch_all_raw(Cow::Owned(sql), binds)
.await
.map_err(|e| e.to_string())?;
match batch {
RowBatch::Any(rows) => Ok(dynamic_rows(&rows)),
RowBatch::Postgres(rows) => Ok(dynamic_rows(&rows)),
RowBatch::Sqlite(rows) => Ok(dynamic_rows(&rows)),
RowBatch::Mysql(rows) => Ok(dynamic_rows(&rows)),
_ => Err(
"this database driver is not supported by Studio; use the default sqlx driver"
.to_string(),
),
}
}
#[allow(clippy::match_wildcard_for_single_variants)]
pub async fn fetch_dynamic_read_only(
pool: &Pool,
sql: &str,
) -> Result<(Vec<String>, Vec<TextRow>), String> {
use ruprizzle::sqlx::{self, Connection as _};
match pool {
Pool::Postgres(p) => {
let mut tx = p
.begin_with("BEGIN READ ONLY")
.await
.map_err(|e| e.to_string())?;
let rows = sqlx::query(sql).fetch_all(&mut *tx).await;
let _ = tx.rollback().await;
rows.map(|r| dynamic_rows(&r)).map_err(|e| e.to_string())
}
Pool::Mysql(p) => {
let mut tx = p
.begin_with("START TRANSACTION READ ONLY")
.await
.map_err(|e| e.to_string())?;
let rows = sqlx::query(sql).fetch_all(&mut *tx).await;
let _ = tx.rollback().await;
rows.map(|r| dynamic_rows(&r)).map_err(|e| e.to_string())
}
Pool::Sqlite(p) => {
let options = (*p.connect_options()).clone().read_only(true);
let mut conn = sqlx::SqliteConnection::connect_with(&options)
.await
.map_err(|e| format!("could not open a read-only connection: {e}"))?;
let rows = sqlx::query(sql).fetch_all(&mut conn).await;
let _ = conn.close().await;
rows.map(|r| dynamic_rows(&r)).map_err(|e| e.to_string())
}
_ => Err(
"this pool kind cannot be opened read-only, so the statement was not run; \
start studio with --allow-writes to run it unguarded"
.to_string(),
),
}
}
#[must_use]
pub fn bind_expr(
dialect: &dyn DbDialect,
provider: Provider,
index: usize,
field: &Field,
) -> String {
let placeholder = dialect.placeholder(index);
match scalar_of(field) {
Some(ScalarType::String) | None => placeholder,
Some(ty) => dialect.cast_expr(&to_text(provider, &placeholder), ty),
}
}
#[must_use]
pub fn scalar_of(field: &Field) -> Option<ScalarType> {
match &field.kind {
FieldKind::Scalar(ty) => Some(*ty),
FieldKind::Enum(_) => Some(ScalarType::String),
FieldKind::List(_) | FieldKind::Relation(_) => None,
}
}
#[must_use]
pub fn is_editable(field: &Field) -> bool {
field.has_column() && scalar_of(field).is_some()
}
fn dynamic_rows<R>(rows: &[R]) -> (Vec<String>, Vec<TextRow>)
where
R: Row,
usize: ColumnIndex<R>,
String: for<'r> Decode<'r, R::Database> + Type<R::Database>,
i64: for<'r> Decode<'r, R::Database> + Type<R::Database>,
f64: for<'r> Decode<'r, R::Database> + Type<R::Database>,
bool: for<'r> Decode<'r, R::Database> + Type<R::Database>,
{
let Some(first) = rows.first() else {
return (Vec::new(), Vec::new());
};
let names: Vec<String> = first
.columns()
.iter()
.map(|c| c.name().to_string())
.collect();
let width = names.len();
let cells = rows
.iter()
.map(|row| (0..width).map(|idx| cell_text(row, idx)).collect())
.collect();
(names, cells)
}
fn cell_text<R>(row: &R, idx: usize) -> Option<String>
where
R: Row,
usize: ColumnIndex<R>,
String: for<'r> Decode<'r, R::Database> + Type<R::Database>,
i64: for<'r> Decode<'r, R::Database> + Type<R::Database>,
f64: for<'r> Decode<'r, R::Database> + Type<R::Database>,
bool: for<'r> Decode<'r, R::Database> + Type<R::Database>,
{
if let Ok(value) = row.try_get::<Option<String>, _>(idx) {
return value;
}
if let Ok(value) = row.try_get::<Option<i64>, _>(idx) {
return value.map(|v| v.to_string());
}
if let Ok(value) = row.try_get::<Option<f64>, _>(idx) {
return value.map(|v| v.to_string());
}
if let Ok(value) = row.try_get::<Option<bool>, _>(idx) {
return value.map(|v| v.to_string());
}
Some("<unreadable>".to_string())
}
pub async fn count_rows(pool: &Pool, provider: Provider, table: &str) -> Result<i64, String> {
let ident = quote_ident(provider, table);
let count = to_text(provider, "COUNT(*)");
fetch_count(pool, format!("SELECT {count} FROM {ident}"), Vec::new()).await
}
pub async fn count_non_null(
pool: &Pool,
provider: Provider,
table: &str,
column: &str,
) -> Result<i64, String> {
let table_ident = quote_ident(provider, table);
let column_ident = quote_ident(provider, column);
let count = to_text(provider, "COUNT(*)");
fetch_count(
pool,
format!("SELECT {count} FROM {table_ident} WHERE {column_ident} IS NOT NULL"),
Vec::new(),
)
.await
}
pub async fn count_null(
pool: &Pool,
provider: Provider,
table: &str,
column: &str,
) -> Result<i64, String> {
let table_ident = quote_ident(provider, table);
let column_ident = quote_ident(provider, column);
let count = to_text(provider, "COUNT(*)");
fetch_count(
pool,
format!("SELECT {count} FROM {table_ident} WHERE {column_ident} IS NULL"),
Vec::new(),
)
.await
}
#[must_use]
pub fn quote_ident(provider: Provider, ident: &str) -> String {
match provider {
Provider::Mysql => format!("`{}`", ident.replace('`', "``")),
Provider::Postgres | Provider::Sqlite => format!("\"{}\"", ident.replace('"', "\"\"")),
}
}
fn row_cells<R>(row: &R, width: usize) -> Result<TextRow, String>
where
R: Row,
usize: ColumnIndex<R>,
String: for<'r> Decode<'r, R::Database> + Type<R::Database>,
i64: for<'r> Decode<'r, R::Database> + Type<R::Database>,
{
(0..width)
.map(|idx| match row.try_get::<Option<String>, _>(idx) {
Ok(value) => Ok(value),
Err(text_error) => match row.try_get::<Option<i64>, _>(idx) {
Ok(value) => Ok(value.map(|value| value.to_string())),
Err(_) => Err(text_error.to_string()),
},
})
.collect()
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn text_cast_is_provider_specific() {
assert_eq!(to_text(Provider::Postgres, "COUNT(*)"), "(COUNT(*))::text");
assert_eq!(
to_text(Provider::Sqlite, "COUNT(*)"),
"CAST(COUNT(*) AS TEXT)"
);
assert_eq!(
to_text(Provider::Mysql, "COUNT(*)"),
"CAST(COUNT(*) AS CHAR)"
);
}
#[test]
fn identifiers_are_quoted_and_escaped() {
assert_eq!(quote_ident(Provider::Postgres, "order"), "\"order\"");
assert_eq!(quote_ident(Provider::Mysql, "order"), "`order`");
assert_eq!(quote_ident(Provider::Postgres, "a\"b"), "\"a\"\"b\"");
assert_eq!(quote_ident(Provider::Mysql, "a`b"), "`a``b`");
}
}