use json::{object, JsonValue};
static DISABLE_FIELD: &[&str] = &[
"default",
"select",
"delete",
"insert",
"update",
"order",
"group",
"user",
"password",
"desc",
"index",
"from",
"host",
"user",
"read",
"partition",
];
pub mod sql_safety {
static SQL_KEYWORDS: &[&str] = &[
"select", "insert", "update", "delete", "drop", "truncate", "alter", "create", "exec",
"execute", "union",
];
static DANGEROUS_PATTERNS: &[&str] = &["--", "/*", "*/", ";", "xp_", "sp_", "0x"];
#[inline]
pub fn validate_table_name(name: &str) -> bool {
if name.is_empty() || name.len() > 128 {
return false;
}
let bytes = name.as_bytes();
let first = bytes[0];
if !first.is_ascii_alphabetic() && first != b'_' {
return false;
}
for &b in bytes {
if !b.is_ascii_alphanumeric() && b != b'_' {
return false;
}
}
let lower = name.to_lowercase();
if SQL_KEYWORDS.iter().any(|kw| lower == *kw) {
return false;
}
!DANGEROUS_PATTERNS.iter().any(|p| lower.contains(p))
}
#[inline]
pub fn validate_field_name(name: &str) -> bool {
if name.is_empty() || name.len() > 256 {
return false;
}
for part in name.split('.') {
if part.is_empty() {
return false;
}
let bytes = part.as_bytes();
let first = bytes[0];
if !first.is_ascii_alphabetic() && first != b'_' {
return false;
}
for &b in bytes {
if !b.is_ascii_alphanumeric() && b != b'_' {
return false;
}
}
}
let lower = name.to_lowercase();
if SQL_KEYWORDS.iter().any(|kw| lower == *kw) {
return false;
}
!DANGEROUS_PATTERNS.iter().any(|p| lower.contains(p))
}
#[inline]
pub fn escape_string(value: &str) -> String {
value
.replace('\\', "\\\\")
.replace('\'', "''")
.replace('\0', "")
.replace('\n', "\\n")
.replace('\r', "\\r")
}
#[inline]
pub fn validate_compare_orator(op: &str) -> bool {
matches!(
op.to_lowercase().as_str(),
"=" | "!="
| "<>"
| "<"
| ">"
| "<="
| ">="
| "like"
| "not like"
| "in"
| "not in"
| "notin"
| "between"
| "is"
| "isnot"
| "is not"
| "set"
| "location"
| "notlike"
| "json_contains"
)
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn test_validate_table_name_valid() {
assert!(validate_table_name("users"));
assert!(validate_table_name("user_profiles"));
assert!(validate_table_name("_private_table"));
assert!(validate_table_name("Table123"));
assert!(validate_table_name("batch_insert_perf"));
assert!(validate_table_name("my_update_log"));
}
#[test]
fn test_validate_table_name_invalid() {
assert!(!validate_table_name(""));
assert!(!validate_table_name("123table"));
assert!(!validate_table_name("user-name"));
assert!(!validate_table_name("table.name"));
assert!(!validate_table_name("table name"));
}
#[test]
fn test_validate_table_name_sql_keywords() {
assert!(!validate_table_name("select"));
assert!(!validate_table_name("SELECT"));
assert!(!validate_table_name("insert"));
assert!(!validate_table_name("drop"));
assert!(!validate_table_name("union"));
}
#[test]
fn test_validate_table_name_dangerous_patterns() {
assert!(!validate_table_name("table;drop"));
assert!(!validate_table_name("table--comment"));
assert!(!validate_table_name("xp_cmdshell"));
assert!(!validate_table_name("sp_execute"));
}
#[test]
fn test_validate_table_name_length() {
let long_name = "a".repeat(128);
assert!(validate_table_name(&long_name));
let too_long = "a".repeat(129);
assert!(!validate_table_name(&too_long));
}
#[test]
fn test_validate_field_name_valid() {
assert!(validate_field_name("id"));
assert!(validate_field_name("user_name"));
assert!(validate_field_name("_hidden"));
assert!(validate_field_name("table1.field1"));
}
#[test]
fn test_validate_field_name_invalid() {
assert!(!validate_field_name(""));
assert!(!validate_field_name("123field"));
assert!(!validate_field_name("field-name"));
assert!(!validate_field_name(".field"));
assert!(!validate_field_name("field."));
}
#[test]
fn test_escape_string() {
assert_eq!(escape_string("hello"), "hello");
assert_eq!(escape_string("it's"), "it''s");
assert_eq!(escape_string("new\nline"), "new\\nline");
assert_eq!(escape_string("carriage\rreturn"), "carriage\\rreturn");
}
#[test]
fn test_escape_string_sql_injection() {
assert_eq!(
escape_string("'; DROP TABLE users; --"),
"''; DROP TABLE users; --"
);
assert_eq!(escape_string("1' OR '1'='1"), "1'' OR ''1''=''1");
}
#[test]
fn test_validate_compare_operator_valid() {
assert!(validate_compare_orator("="));
assert!(validate_compare_orator("!="));
assert!(validate_compare_orator("like"));
assert!(validate_compare_orator("LIKE"));
assert!(validate_compare_orator("in"));
assert!(validate_compare_orator("between"));
}
#[test]
fn test_validate_compare_operator_invalid() {
assert!(!validate_compare_orator(""));
assert!(!validate_compare_orator("invalid"));
assert!(!validate_compare_orator("=="));
assert!(!validate_compare_orator("&&"));
}
}
}
#[inline]
pub fn quote_identifier(name: &str, mode: &str) -> String {
match mode {
"mysql" | "sqlite" => format!("`{}`", name),
"pgsql" => format!("\"{}\"", name),
"mssql" => format!("[{}]", name),
_ => name.to_string(),
}
}
#[cfg(feature = "db-mssql")]
pub mod mssql;
#[cfg(feature = "db-mysql")]
pub mod mysql;
#[cfg(feature = "db-mysql")]
pub mod mysql_transaction;
#[cfg(feature = "db-pgsql")]
pub mod pgsql;
#[cfg(feature = "db-pgsql")]
pub mod pgsql_transaction;
#[cfg(feature = "db-sqlite")]
pub mod sqlite;
#[cfg(feature = "db-sqlite")]
pub mod sqlite_transaction;
pub trait DbMode {
fn database_tables(&mut self) -> JsonValue;
fn database_create(&mut self, name: &str) -> bool;
fn backups(&mut self, _filename: &str) -> bool {
false
}
fn truncate(&mut self, table: &str) -> bool;
}
#[derive(Debug, Clone)]
pub struct TableOptions {
table_name: String,
table_title: String,
table_key: String,
table_fields: JsonValue,
table_unique: Vec<String>,
table_index: Vec<Vec<String>>,
table_partition: bool,
table_partition_columns: JsonValue,
}
impl TableOptions {
pub fn set_table_name(&mut self, name: &str) {
self.table_name = name.to_string()
}
pub fn set_table_title(&mut self, name: &str) {
self.table_title = name.to_string()
}
pub fn set_table_key(&mut self, name: &str) {
self.table_key = name.to_string()
}
pub fn set_table_fields(&mut self, fields: JsonValue) {
self.table_fields = fields;
}
pub fn set_table_unique(&mut self, unique: Vec<&str>) {
self.table_unique = unique.iter().map(|s| s.to_string()).collect();
}
pub fn set_table_index(&mut self, index: Vec<Vec<&str>>) {
self.table_index = index
.iter()
.map(|s| s.iter().map(|s| s.to_string()).collect())
.collect();
}
pub fn set_table_partition(&mut self, index: bool) {
self.table_partition = index;
}
pub fn set_table_partition_columns(&mut self, index: JsonValue) {
self.table_partition_columns = index;
}
}
impl Default for TableOptions {
fn default() -> Self {
Self {
table_name: "".to_string(),
table_title: "".to_string(),
table_key: "".to_string(),
table_fields: JsonValue::Null,
table_unique: vec![],
table_index: vec![],
table_partition: false,
table_partition_columns: JsonValue::Null,
}
}
}
pub trait Mode: DbMode {
fn table_create(&mut self, options: TableOptions) -> JsonValue;
fn table_update(&mut self, options: TableOptions) -> JsonValue;
fn table_info(&mut self, table: &str) -> JsonValue;
fn table_is_exist(&mut self, name: &str) -> bool;
fn table(&mut self, name: &str) -> &mut Self;
fn change_table(&mut self, name: &str) -> &mut Self;
fn autoinc(&mut self) -> &mut Self;
fn timestamps(&mut self) -> &mut Self;
fn fetch_sql(&mut self) -> &mut Self;
fn order(&mut self, field: &str, by: bool) -> &mut Self;
fn group(&mut self, field: &str) -> &mut Self;
fn distinct(&mut self) -> &mut Self;
fn json(&mut self, field: &str) -> &mut Self;
fn location(&mut self, field: &str) -> &mut Self;
fn field(&mut self, field: &str) -> &mut Self;
fn field_raw(&mut self, expr: &str) -> &mut Self;
fn hidden(&mut self, name: &str) -> &mut Self;
fn where_and(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self;
fn where_or(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self;
fn where_raw(&mut self, expr: &str) -> &mut Self;
fn where_in_sub(&mut self, field: &str, sub_sql: &str) -> &mut Self;
fn where_not_in_sub(&mut self, field: &str, sub_sql: &str) -> &mut Self;
fn where_exists(&mut self, sub_sql: &str) -> &mut Self;
fn where_not_exists(&mut self, sub_sql: &str) -> &mut Self;
fn where_column(&mut self, field_a: &str, compare: &str, field_b: &str) -> &mut Self;
fn update_column(&mut self, field_a: &str, compare: &str) -> &mut Self;
fn page(&mut self, page: i32, limit: i32) -> &mut Self;
fn limit(&mut self, count: i32) -> &mut Self;
fn column(&mut self, field: &str) -> JsonValue;
fn count(&mut self) -> JsonValue;
fn max(&mut self, field: &str) -> JsonValue;
fn min(&mut self, field: &str) -> JsonValue;
fn sum(&mut self, field: &str) -> JsonValue;
fn avg(&mut self, field: &str) -> JsonValue;
fn having(&mut self, expr: &str) -> &mut Self;
fn select(&mut self) -> JsonValue;
fn find(&mut self) -> JsonValue;
fn value(&mut self, field: &str) -> JsonValue;
fn insert(&mut self, data: JsonValue) -> JsonValue;
fn insert_all(&mut self, data: JsonValue) -> JsonValue;
fn upsert(&mut self, data: JsonValue, conflict_fields: Vec<&str>) -> JsonValue;
fn update(&mut self, data: JsonValue) -> JsonValue;
fn update_all(&mut self, data: JsonValue) -> JsonValue;
fn delete(&mut self) -> JsonValue;
fn transaction(&mut self) -> bool;
fn commit(&mut self) -> bool;
fn rollback(&mut self) -> bool;
fn sql(&mut self, sql: &str) -> Result<JsonValue, String>;
fn sql_execute(&mut self, sql: &str) -> Result<JsonValue, String>;
fn inc(&mut self, field: &str, num: f64) -> &mut Self;
fn dec(&mut self, field: &str, num: f64) -> &mut Self;
fn buildsql(&mut self) -> String;
fn join_fields(&mut self, fields: Vec<&str>) -> &mut Self;
fn join(
&mut self,
main_table: &str,
main_fields: &str,
right_table: &str,
right_fields: &str,
) -> &mut Self;
fn join_inner(&mut self, table: &str, main_fields: &str, second_fields: &str) -> &mut Self;
fn join_right(
&mut self,
main_table: &str,
main_fields: &str,
right_table: &str,
right_fields: &str,
) -> &mut Self;
fn join_full(
&mut self,
main_table: &str,
main_fields: &str,
right_table: &str,
right_fields: &str,
) -> &mut Self;
fn union(&mut self, sub_sql: &str) -> &mut Self;
fn union_all(&mut self, sub_sql: &str) -> &mut Self;
fn lock_for_update(&mut self) -> &mut Self;
fn lock_for_share(&mut self) -> &mut Self;
}
#[derive(Clone, Debug)]
pub struct Params {
pub mode: String,
pub autoinc: bool,
pub table: String,
pub where_and: Vec<String>,
pub where_or: Vec<String>,
pub where_column: String,
pub update_column: Vec<String>,
pub inc_dec: JsonValue,
pub page: i32,
pub limit: i32,
pub fields: JsonValue,
pub top: String,
pub top2: String,
pub order: JsonValue,
pub group: JsonValue,
pub distinct: bool,
pub json: JsonValue,
pub location: JsonValue,
pub sql: bool,
pub join: Vec<String>,
pub join_inner: Vec<String>,
pub join_table: String,
pub having: Vec<String>,
pub limit_only: i32,
pub timestamps: bool,
pub unions: Vec<String>,
pub lock_mode: String,
}
impl Params {
pub fn default(mode: &str) -> Self {
Self {
mode: mode.to_string(),
autoinc: false,
table: "".to_string(),
where_and: vec![],
where_or: vec![],
where_column: "".to_string(),
update_column: vec![],
inc_dec: object! {},
page: -1,
limit: 10,
fields: object! {},
top: String::new(),
top2: String::new(),
order: object! {},
group: object! {},
distinct: false,
json: object! {},
location: object! {},
sql: false,
join: Vec::new(),
join_inner: Vec::new(),
join_table: "".to_string(),
having: vec![],
limit_only: -1,
timestamps: false,
unions: vec![],
lock_mode: "".to_string(),
}
}
pub fn where_sql(&mut self) -> String {
let mut where_and_sql = vec![];
let mut where_or_sql = vec![];
let mut sql = vec![];
for item in self.where_or.iter() {
where_or_sql.push(item.clone());
}
if !where_or_sql.is_empty() {
sql.push(format!(" ( {} ) ", where_or_sql.join(" OR ")));
}
for item in self.where_and.iter() {
where_and_sql.push(item.clone());
}
if !where_and_sql.is_empty() {
sql.push(where_and_sql.join(" AND "));
}
if !self.where_column.is_empty() {
sql.push(self.where_column.clone());
}
if !sql.is_empty() {
return format!("WHERE {}", sql.join(" AND "));
}
"".to_string()
}
pub fn page_limit_sql(&mut self) -> String {
if self.page == -1 {
if self.limit_only > 0 {
return format!("LIMIT {}", self.limit_only);
}
return "".to_string();
}
match self.mode.as_str() {
"mysql" => {
format!(
"LIMIT {},{}",
self.page * self.limit - self.limit,
self.limit
)
}
"sqlite" | "pgsql" => {
format!(
"LIMIT {} OFFSET {}",
self.limit,
self.page * self.limit - self.limit
)
}
_ => "".to_string(),
}
}
pub fn fields(&mut self) -> String {
let mut fields = vec![];
for (_, value) in self.fields.entries() {
match self.mode.as_str() {
"mssql" => {
fields.push(format!("{value}"));
}
"mysql" => {
if let Some(s) = value.as_str() {
if DISABLE_FIELD.contains(&s) {
fields.push(format!("`{value}`"));
} else {
fields.push(format!("{value}"));
}
} else {
fields.push(format!("{value}"));
}
}
_ => {
fields.push(format!("{value}"));
}
}
}
let fields = {
if fields.is_empty() {
"*".into()
} else {
fields.join(",")
}
};
match self.mode.as_str() {
"mysql" => fields.to_string(),
"sqlite" => fields.to_string(),
"mssql" => fields.to_string(),
_ => fields.to_string(),
}
}
pub fn top(&mut self) -> String {
match self.mode.as_str() {
"mssql" => {
let wheres = self.where_sql();
if !self.top2.is_empty() {
let order = self.order();
if order.is_empty() {
self.top = format!(
"(select ROW_NUMBER() OVER(ORDER BY rand()) as ROW,* from {} {}) as ",
self.table, wheres
);
} else {
self.top = format!(
"(select ROW_NUMBER() OVER({}) as ROW,* from {} {}) as ",
order, self.table, wheres
);
}
return self.top.to_string();
}
self.top.to_string()
}
_ => "".to_string(),
}
}
pub fn top2(&mut self) -> String {
match self.mode.as_str() {
"mssql" => {
if self.where_and.is_empty() && self.where_or.is_empty() && !self.top2.is_empty() {
return format!("where {}", self.top2);
}
if (!self.where_and.is_empty() || !self.where_or.is_empty())
&& !self.top2.is_empty()
{
return format!("AND {}", self.top2);
}
self.top2.to_string()
}
_ => "".to_string(),
}
}
pub fn table(&mut self) -> String {
match self.mode.as_str() {
"mssql" => {
if !self.top2.is_empty() {
return "t".to_string();
}
self.table.to_string()
}
_ => self.table.to_string(),
}
}
pub fn join(&mut self) -> String {
match self.mode.as_str() {
"mssql" => self.join.join(" "),
_ => self.join.join(" "),
}
}
pub fn join_inner(&mut self) -> String {
match self.mode.as_str() {
"mysql" => {
let mut join_inner = "".to_string();
for item in self.join_inner.iter() {
join_inner = format!("{join_inner} {item}");
}
join_inner.to_string()
}
_ => "".to_string(),
}
}
pub fn order(&mut self) -> String {
let mut sql = vec![];
for (field, item) in self.order.entries() {
match self.mode.as_str() {
"mssql" => {
if DISABLE_FIELD.contains(&field) {
sql.push(format!("[{field}] {item}"));
} else {
sql.push(format!("{field} {item}"));
}
}
"pgsql" => {
if DISABLE_FIELD.contains(&field) {
sql.push(format!("\"{field}\" {item}"));
} else {
sql.push(format!("{field} {item}"));
}
}
_ => {
if DISABLE_FIELD.contains(&field) {
sql.push(format!("`{field}` {item}"));
} else {
sql.push(format!("{field} {item}"));
}
}
}
}
if !sql.is_empty() {
return format!("ORDER BY {}", sql.join(","));
}
"".to_string()
}
pub fn group(&mut self) -> String {
let mut sql = vec![];
for (_, field) in self.group.entries() {
let field_str = field.to_string();
if field_str.contains(".") {
sql.push(format!("{}", field));
} else {
match self.mode.as_str() {
"pgsql" => {
if DISABLE_FIELD.contains(&&*field_str) {
sql.push(format!("\"{field}\""));
} else {
sql.push(format!("{}.{}", self.table, field));
}
}
"mssql" => {
if DISABLE_FIELD.contains(&&*field_str) {
sql.push(format!("[{field}]"));
} else {
sql.push(format!("{}.{}", self.table, field));
}
}
_ => {
if DISABLE_FIELD.contains(&&*field_str) {
sql.push(format!("`{field}`"));
} else {
sql.push(format!("{}.{}", self.table, field));
}
}
}
}
}
if !sql.is_empty() {
return format!("GROUP BY {}", sql.join(","));
}
"".to_string()
}
pub fn having(&mut self) -> String {
if self.having.is_empty() {
return "".to_string();
}
format!("HAVING {}", self.having.join(" AND "))
}
pub fn distinct(&self) -> String {
if self.distinct {
"DISTINCT".to_string()
} else {
"".to_string()
}
}
pub fn select_sql(&mut self) -> String {
let base = format!(
"SELECT {} {} FROM {} {} {} {} {} {} {} {} {} {}",
self.distinct(),
self.fields(),
self.top(),
self.table(),
self.join(),
self.join_inner(),
self.where_sql(),
self.top2(),
self.group(),
self.having(),
self.order(),
self.page_limit_sql()
);
if self.unions.is_empty() {
if self.lock_mode.is_empty() {
base
} else {
format!("{base} {}", self.lock_mode)
}
} else {
let unions = self.unions.join(" ");
if self.lock_mode.is_empty() {
format!("{base} {unions}")
} else {
format!("{base} {unions} {}", self.lock_mode)
}
}
}
}
#[cfg(test)]
mod params_tests {
use super::*;
use json::object;
#[test]
fn test_quote_identifier_mysql() {
assert_eq!(quote_identifier("name", "mysql"), "`name`");
assert_eq!(quote_identifier("user", "mysql"), "`user`");
}
#[test]
fn test_quote_identifier_sqlite() {
assert_eq!(quote_identifier("name", "sqlite"), "`name`");
assert_eq!(quote_identifier("order", "sqlite"), "`order`");
}
#[test]
fn test_quote_identifier_pgsql() {
assert_eq!(quote_identifier("name", "pgsql"), "\"name\"");
assert_eq!(quote_identifier("select", "pgsql"), "\"select\"");
}
#[test]
fn test_quote_identifier_mssql() {
assert_eq!(quote_identifier("name", "mssql"), "[name]");
assert_eq!(quote_identifier("index", "mssql"), "[index]");
}
#[test]
fn test_quote_identifier_unknown() {
assert_eq!(quote_identifier("name", "unknown"), "name");
assert_eq!(quote_identifier("field", ""), "field");
}
#[test]
fn test_table_options_default() {
let opts = TableOptions::default();
assert_eq!(opts.table_name, "");
assert_eq!(opts.table_title, "");
assert_eq!(opts.table_key, "");
assert!(opts.table_fields.is_null());
assert!(opts.table_unique.is_empty());
assert!(opts.table_index.is_empty());
assert!(!opts.table_partition);
assert!(opts.table_partition_columns.is_null());
}
#[test]
fn test_table_options_set_table_name() {
let mut opts = TableOptions::default();
opts.set_table_name("users");
assert_eq!(opts.table_name, "users");
}
#[test]
fn test_table_options_set_table_title() {
let mut opts = TableOptions::default();
opts.set_table_title("User Table");
assert_eq!(opts.table_title, "User Table");
}
#[test]
fn test_table_options_set_table_key() {
let mut opts = TableOptions::default();
opts.set_table_key("id");
assert_eq!(opts.table_key, "id");
}
#[test]
fn test_table_options_set_table_fields() {
let mut opts = TableOptions::default();
let fields = object! { "name" => "varchar", "age" => "int" };
opts.set_table_fields(fields);
assert_eq!(opts.table_fields["name"], "varchar");
assert_eq!(opts.table_fields["age"], "int");
}
#[test]
fn test_table_options_set_table_unique() {
let mut opts = TableOptions::default();
opts.set_table_unique(vec!["email", "username"]);
assert_eq!(
opts.table_unique,
vec!["email".to_string(), "username".to_string()]
);
}
#[test]
fn test_table_options_set_table_index() {
let mut opts = TableOptions::default();
opts.set_table_index(vec![vec!["name", "age"], vec!["email"]]);
assert_eq!(opts.table_index.len(), 2);
assert_eq!(
opts.table_index[0],
vec!["name".to_string(), "age".to_string()]
);
assert_eq!(opts.table_index[1], vec!["email".to_string()]);
}
#[test]
fn test_table_options_set_table_partition() {
let mut opts = TableOptions::default();
assert!(!opts.table_partition);
opts.set_table_partition(true);
assert!(opts.table_partition);
}
#[test]
fn test_table_options_set_table_partition_columns() {
let mut opts = TableOptions::default();
let cols = object! { "col1" => "range", "col2" => "hash" };
opts.set_table_partition_columns(cols);
assert_eq!(opts.table_partition_columns["col1"], "range");
assert_eq!(opts.table_partition_columns["col2"], "hash");
}
#[test]
fn test_params_default_mysql() {
let p = Params::default("mysql");
assert_eq!(p.mode, "mysql");
assert!(!p.autoinc);
assert_eq!(p.table, "");
assert!(p.where_and.is_empty());
assert!(p.where_or.is_empty());
assert_eq!(p.where_column, "");
assert!(p.update_column.is_empty());
assert!(p.inc_dec.is_object());
assert_eq!(p.page, -1);
assert_eq!(p.limit, 10);
assert!(p.fields.is_object());
assert_eq!(p.top, "");
assert_eq!(p.top2, "");
assert!(p.order.is_object());
assert!(p.group.is_object());
assert!(!p.distinct);
assert!(p.json.is_object());
assert!(p.location.is_object());
assert!(!p.sql);
assert!(p.join.is_empty());
assert!(p.join_inner.is_empty());
assert_eq!(p.join_table, "");
}
#[test]
fn test_params_default_pgsql() {
let p = Params::default("pgsql");
assert_eq!(p.mode, "pgsql");
assert_eq!(p.page, -1);
assert_eq!(p.limit, 10);
}
#[test]
fn test_params_default_mssql() {
let p = Params::default("mssql");
assert_eq!(p.mode, "mssql");
}
#[test]
fn test_params_default_sqlite() {
let p = Params::default("sqlite");
assert_eq!(p.mode, "sqlite");
}
#[test]
fn test_where_sql_empty() {
let mut p = Params::default("mysql");
assert_eq!(p.where_sql(), "");
}
#[test]
fn test_where_sql_only_and() {
let mut p = Params::default("mysql");
p.where_and.push("a = 1".to_string());
p.where_and.push("b = 2".to_string());
assert_eq!(p.where_sql(), "WHERE a = 1 AND b = 2");
}
#[test]
fn test_where_sql_only_or() {
let mut p = Params::default("mysql");
p.where_or.push("a = 1".to_string());
p.where_or.push("b = 2".to_string());
assert_eq!(p.where_sql(), "WHERE ( a = 1 OR b = 2 ) ");
}
#[test]
fn test_where_sql_and_plus_or() {
let mut p = Params::default("mysql");
p.where_and.push("c = 3".to_string());
p.where_or.push("a = 1".to_string());
p.where_or.push("b = 2".to_string());
let result = p.where_sql();
assert!(result.starts_with("WHERE"));
assert!(result.contains("( a = 1 OR b = 2 )"));
assert!(result.contains("c = 3"));
assert!(result.contains(" AND "));
}
#[test]
fn test_where_sql_with_where_column() {
let mut p = Params::default("mysql");
p.where_column = "x.id = y.id".to_string();
assert_eq!(p.where_sql(), "WHERE x.id = y.id");
}
#[test]
fn test_where_sql_all_three() {
let mut p = Params::default("mysql");
p.where_and.push("a = 1".to_string());
p.where_or.push("b = 2".to_string());
p.where_or.push("c = 3".to_string());
p.where_column = "x.id = y.id".to_string();
let result = p.where_sql();
assert!(result.starts_with("WHERE"));
assert!(result.contains("( b = 2 OR c = 3 )"));
assert!(result.contains("a = 1"));
assert!(result.contains("x.id = y.id"));
}
#[test]
fn test_page_limit_sql_default_page_neg1() {
let mut p = Params::default("mysql");
assert_eq!(p.page_limit_sql(), "");
}
#[test]
fn test_page_limit_sql_mysql() {
let mut p = Params::default("mysql");
p.page = 1;
p.limit = 10;
assert_eq!(p.page_limit_sql(), "LIMIT 0,10");
}
#[test]
fn test_page_limit_sql_mysql_page2() {
let mut p = Params::default("mysql");
p.page = 2;
p.limit = 20;
assert_eq!(p.page_limit_sql(), "LIMIT 20,20");
}
#[test]
fn test_page_limit_sql_sqlite() {
let mut p = Params::default("sqlite");
p.page = 1;
p.limit = 10;
assert_eq!(p.page_limit_sql(), "LIMIT 10 OFFSET 0");
}
#[test]
fn test_page_limit_sql_sqlite_page3() {
let mut p = Params::default("sqlite");
p.page = 3;
p.limit = 5;
assert_eq!(p.page_limit_sql(), "LIMIT 5 OFFSET 10");
}
#[test]
fn test_page_limit_sql_unknown_mode() {
let mut p = Params::default("unknown");
p.page = 1;
p.limit = 10;
assert_eq!(p.page_limit_sql(), "");
}
#[test]
fn test_fields_empty() {
let mut p = Params::default("mysql");
assert_eq!(p.fields(), "*");
}
#[test]
fn test_fields_mysql_with_disable_field() {
let mut p = Params::default("mysql");
p.fields = object! { "f1" => "order", "f2" => "name" };
let result = p.fields();
assert!(result.contains("`order`"));
assert!(result.contains("name"));
assert!(!result.contains("`name`"));
}
#[test]
fn test_fields_mysql_normal() {
let mut p = Params::default("mysql");
p.fields = object! { "f1" => "id", "f2" => "name" };
let result = p.fields();
assert!(result.contains("id"));
assert!(result.contains("name"));
}
#[test]
fn test_fields_mssql() {
let mut p = Params::default("mssql");
p.fields = object! { "f1" => "id", "f2" => "name" };
let result = p.fields();
assert!(result.contains("id"));
assert!(result.contains("name"));
}
#[test]
fn test_fields_other_mode() {
let mut p = Params::default("pgsql");
p.fields = object! { "f1" => "id", "f2" => "email" };
let result = p.fields();
assert!(result.contains("id"));
assert!(result.contains("email"));
}
#[test]
fn test_top_non_mssql() {
let mut p = Params::default("mysql");
assert_eq!(p.top(), "");
}
#[test]
fn test_top_mssql_without_top2() {
let mut p = Params::default("mssql");
p.table = "users".to_string();
assert_eq!(p.top(), "");
}
#[test]
fn test_top_mssql_with_top2() {
let mut p = Params::default("mssql");
p.table = "users".to_string();
p.top2 = "ROW BETWEEN 1 AND 10".to_string();
let result = p.top();
assert!(result.contains("ROW_NUMBER"));
assert!(result.contains("users"));
assert!(result.contains("as "));
}
#[test]
fn test_top_mssql_with_top2_and_order() {
let mut p = Params::default("mssql");
p.table = "users".to_string();
p.top2 = "ROW BETWEEN 1 AND 10".to_string();
p.order = object! { "id" => "ASC" };
let result = p.top();
assert!(result.contains("ROW_NUMBER"));
assert!(result.contains("ORDER BY"));
assert!(result.contains("id ASC"));
}
#[test]
fn test_top2_non_mssql() {
let mut p = Params::default("mysql");
p.top2 = "something".to_string();
assert_eq!(p.top2(), "");
}
#[test]
fn test_top2_mssql_no_where_with_top2() {
let mut p = Params::default("mssql");
p.top2 = "ROW BETWEEN 1 AND 10".to_string();
let result = p.top2();
assert_eq!(result, "where ROW BETWEEN 1 AND 10");
}
#[test]
fn test_top2_mssql_with_where_and_top2() {
let mut p = Params::default("mssql");
p.where_and.push("a = 1".to_string());
p.top2 = "ROW BETWEEN 1 AND 10".to_string();
let result = p.top2();
assert_eq!(result, "AND ROW BETWEEN 1 AND 10");
}
#[test]
fn test_top2_mssql_empty_top2() {
let mut p = Params::default("mssql");
assert_eq!(p.top2(), "");
}
#[test]
fn test_table_mssql_with_top2() {
let mut p = Params::default("mssql");
p.table = "users".to_string();
p.top2 = "ROW BETWEEN 1 AND 10".to_string();
assert_eq!(p.table(), "t");
}
#[test]
fn test_table_mssql_without_top2() {
let mut p = Params::default("mssql");
p.table = "users".to_string();
assert_eq!(p.table(), "users");
}
#[test]
fn test_table_other_modes() {
let mut p = Params::default("mysql");
p.table = "orders".to_string();
assert_eq!(p.table(), "orders");
let mut p2 = Params::default("pgsql");
p2.table = "products".to_string();
assert_eq!(p2.table(), "products");
}
#[test]
fn test_join_empty() {
let mut p = Params::default("mysql");
assert_eq!(p.join(), "");
}
#[test]
fn test_join_with_entries() {
let mut p = Params::default("mysql");
p.join
.push("LEFT JOIN orders ON users.id = orders.user_id".to_string());
p.join
.push("LEFT JOIN items ON orders.id = items.order_id".to_string());
let result = p.join();
assert!(result.contains("LEFT JOIN orders ON users.id = orders.user_id"));
assert!(result.contains("LEFT JOIN items ON orders.id = items.order_id"));
assert!(result.contains(" "));
}
#[test]
fn test_join_mssql() {
let mut p = Params::default("mssql");
p.join.push("JOIN t2 ON t1.id = t2.fk".to_string());
let result = p.join();
assert_eq!(result, "JOIN t2 ON t1.id = t2.fk");
}
#[test]
fn test_join_inner_empty() {
let mut p = Params::default("mysql");
assert_eq!(p.join_inner(), "");
}
#[test]
fn test_join_inner_mysql() {
let mut p = Params::default("mysql");
p.join_inner
.push("INNER JOIN orders ON users.id = orders.uid".to_string());
p.join_inner
.push("INNER JOIN items ON orders.id = items.oid".to_string());
let result = p.join_inner();
assert!(result.contains("INNER JOIN orders ON users.id = orders.uid"));
assert!(result.contains("INNER JOIN items ON orders.id = items.oid"));
}
#[test]
fn test_join_inner_non_mysql() {
let mut p = Params::default("pgsql");
p.join_inner
.push("INNER JOIN orders ON users.id = orders.uid".to_string());
assert_eq!(p.join_inner(), "");
let mut p2 = Params::default("mssql");
p2.join_inner
.push("INNER JOIN orders ON users.id = orders.uid".to_string());
assert_eq!(p2.join_inner(), "");
}
#[test]
fn test_order_empty() {
let mut p = Params::default("mysql");
assert_eq!(p.order(), "");
}
#[test]
fn test_order_with_entries() {
let mut p = Params::default("mysql");
p.order = object! { "id" => "ASC", "name" => "DESC" };
let result = p.order();
assert!(result.starts_with("ORDER BY"));
assert!(result.contains("id ASC"));
assert!(result.contains("name DESC"));
}
#[test]
fn test_order_mysql_disable_field() {
let mut p = Params::default("mysql");
p.order = object! { "order" => "ASC" };
let result = p.order();
assert!(result.contains("`order` ASC"));
}
#[test]
fn test_order_pgsql_disable_field() {
let mut p = Params::default("pgsql");
p.order = object! { "order" => "DESC" };
let result = p.order();
assert!(result.contains("\"order\" DESC"));
}
#[test]
fn test_order_mssql_disable_field() {
let mut p = Params::default("mssql");
p.order = object! { "order" => "ASC" };
let result = p.order();
assert!(result.contains("[order] ASC"));
}
#[test]
fn test_group_empty() {
let mut p = Params::default("mysql");
assert_eq!(p.group(), "");
}
#[test]
fn test_group_with_entries() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
p.group = object! { "g1" => "status" };
let result = p.group();
assert!(result.starts_with("GROUP BY"));
assert!(result.contains("users.status"));
}
#[test]
fn test_group_disable_field() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
p.group = object! { "g1" => "order" };
let result = p.group();
assert!(result.contains("`order`"));
}
#[test]
fn test_group_dotted_field() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
p.group = object! { "g1" => "orders.status" };
let result = p.group();
assert!(result.contains("orders.status"));
assert!(!result.contains("users.orders.status"));
}
#[test]
fn test_distinct_false() {
let p = Params::default("mysql");
assert_eq!(p.distinct(), "");
}
#[test]
fn test_distinct_true() {
let mut p = Params::default("mysql");
p.distinct = true;
assert_eq!(p.distinct(), "DISTINCT");
}
#[test]
fn test_select_sql_basic() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
let result = p.select_sql();
assert!(result.contains("SELECT"));
assert!(result.contains("*"));
assert!(result.contains("FROM"));
assert!(result.contains("users"));
}
#[test]
fn test_select_sql_with_where_and_order() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
p.where_and.push("age > 18".to_string());
p.order = object! { "name" => "ASC" };
p.page = 1;
p.limit = 10;
let result = p.select_sql();
assert!(result.contains("SELECT"));
assert!(result.contains("FROM"));
assert!(result.contains("users"));
assert!(result.contains("WHERE age > 18"));
assert!(result.contains("ORDER BY name ASC"));
assert!(result.contains("LIMIT 0,10"));
}
#[test]
fn test_select_sql_with_distinct_and_group() {
let mut p = Params::default("mysql");
p.table = "orders".to_string();
p.distinct = true;
p.group = object! { "g1" => "status" };
p.fields = object! { "f1" => "status", "f2" => "count" };
let result = p.select_sql();
assert!(result.contains("DISTINCT"));
assert!(result.contains("GROUP BY"));
assert!(result.contains("orders.status"));
}
#[test]
fn test_having_empty() {
let mut p = Params::default("mysql");
assert_eq!(p.having(), "");
}
#[test]
fn test_having_single() {
let mut p = Params::default("mysql");
p.having.push("COUNT(*) > 5".to_string());
assert_eq!(p.having(), "HAVING COUNT(*) > 5");
}
#[test]
fn test_having_multiple() {
let mut p = Params::default("mysql");
p.having.push("COUNT(*) > 5".to_string());
p.having.push("SUM(amount) > 100".to_string());
assert_eq!(p.having(), "HAVING COUNT(*) > 5 AND SUM(amount) > 100");
}
#[test]
fn test_select_sql_with_having() {
let mut p = Params::default("mysql");
p.table = "orders".to_string();
p.group = object! { "g1" => "status" };
p.having.push("COUNT(*) > 1".to_string());
let result = p.select_sql();
assert!(result.contains("GROUP BY"));
assert!(result.contains("HAVING COUNT(*) > 1"));
}
#[test]
fn test_field_raw_in_select_sql() {
let mut p = Params::default("mysql");
p.table = "orders".to_string();
p.fields["raw1"] = "SUM(amount) as total".into();
p.fields["raw2"] = "COUNT(*) as cnt".into();
let result = p.select_sql();
assert!(result.contains("SUM(amount) as total"));
assert!(result.contains("COUNT(*) as cnt"));
assert!(!result.contains("orders.`SUM"));
}
#[test]
fn test_where_raw_in_select_sql() {
let mut p = Params::default("mysql");
p.table = "orders".to_string();
p.where_and.push("YEAR(created_at) = 2026".to_string());
let result = p.select_sql();
assert!(result.contains("WHERE YEAR(created_at) = 2026"));
}
#[test]
fn test_limit_only_default() {
let mut p = Params::default("mysql");
assert_eq!(p.page_limit_sql(), "");
}
#[test]
fn test_limit_only_mysql() {
let mut p = Params::default("mysql");
p.limit_only = 5;
assert_eq!(p.page_limit_sql(), "LIMIT 5");
}
#[test]
fn test_limit_only_sqlite() {
let mut p = Params::default("sqlite");
p.limit_only = 10;
assert_eq!(p.page_limit_sql(), "LIMIT 10");
}
#[test]
fn test_limit_only_pgsql() {
let mut p = Params::default("pgsql");
p.limit_only = 3;
assert_eq!(p.page_limit_sql(), "LIMIT 3");
}
#[test]
fn test_page_overrides_limit_only() {
let mut p = Params::default("mysql");
p.limit_only = 5;
p.page = 2;
p.limit = 10;
assert_eq!(p.page_limit_sql(), "LIMIT 10,10");
}
#[test]
fn test_limit_only_in_select_sql() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
p.limit_only = 3;
let result = p.select_sql();
assert!(result.contains("LIMIT 3"));
assert!(!result.contains("OFFSET"));
}
#[test]
fn test_where_in_sub() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
p.where_and
.push("`status` IN (SELECT status FROM active_users)".to_string());
let result = p.select_sql();
assert!(result.contains("WHERE `status` IN (SELECT status FROM active_users)"));
}
#[test]
fn test_where_not_in_sub() {
let mut p = Params::default("pgsql");
p.table = "users".to_string();
p.where_and
.push("\"id\" NOT IN (SELECT user_id FROM banned)".to_string());
let result = p.select_sql();
assert!(result.contains("WHERE \"id\" NOT IN (SELECT user_id FROM banned)"));
}
#[test]
fn test_union_single() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
p.unions.push("UNION SELECT * FROM admins".to_string());
let result = p.select_sql();
assert!(result.contains("UNION SELECT * FROM admins"));
}
#[test]
fn test_union_all() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
p.unions.push("UNION ALL SELECT * FROM guests".to_string());
let result = p.select_sql();
assert!(result.contains("UNION ALL SELECT * FROM guests"));
}
#[test]
fn test_union_multiple() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
p.unions.push("UNION SELECT * FROM admins".to_string());
p.unions.push("UNION ALL SELECT * FROM guests".to_string());
let result = p.select_sql();
assert!(result.contains("UNION SELECT * FROM admins"));
assert!(result.contains("UNION ALL SELECT * FROM guests"));
}
#[test]
fn test_no_union() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
let result = p.select_sql();
assert!(!result.contains("UNION"));
}
#[test]
fn test_timestamps_default_false() {
let p = Params::default("mysql");
assert!(!p.timestamps);
}
#[test]
fn test_lock_for_update_in_select_sql() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
p.lock_mode = "FOR UPDATE".to_string();
let result = p.select_sql();
assert!(result.contains("FOR UPDATE"));
}
#[test]
fn test_lock_for_share_in_select_sql() {
let mut p = Params::default("pgsql");
p.table = "users".to_string();
p.lock_mode = "FOR SHARE".to_string();
let result = p.select_sql();
assert!(result.contains("FOR SHARE"));
}
#[test]
fn test_no_lock_mode() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
let result = p.select_sql();
assert!(!result.contains("FOR UPDATE"));
assert!(!result.contains("FOR SHARE"));
}
#[test]
fn test_where_exists_in_select_sql() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
p.where_and
.push("EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)".to_string());
let result = p.select_sql();
assert!(result.contains("EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)"));
}
#[test]
fn test_where_not_exists_in_select_sql() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
p.where_and
.push("NOT EXISTS (SELECT 1 FROM banned WHERE banned.uid = users.id)".to_string());
let result = p.select_sql();
assert!(result.contains("NOT EXISTS (SELECT 1 FROM banned WHERE banned.uid = users.id)"));
}
#[test]
fn test_lock_with_union() {
let mut p = Params::default("mysql");
p.table = "users".to_string();
p.unions.push("UNION SELECT * FROM admins".to_string());
p.lock_mode = "FOR UPDATE".to_string();
let result = p.select_sql();
assert!(result.contains("UNION SELECT * FROM admins"));
assert!(result.contains("FOR UPDATE"));
}
#[test]
fn test_lock_mode_default_empty() {
let p = Params::default("mysql");
assert!(p.lock_mode.is_empty());
}
#[test]
fn test_unions_default_empty() {
let p = Params::default("mysql");
assert!(p.unions.is_empty());
}
}