1use crate::sql_types::QualifiedIdentifier;
4use crate::DbError;
5use std::fmt;
6
7#[derive(Debug, Clone, PartialEq, Eq, PartialOrd, Ord, Hash)]
13pub struct TableRef {
14 raw: String,
15}
16
17impl TableRef {
18 pub fn new(value: impl Into<String>) -> Result<Self, DbError> {
23 let raw = value.into();
24 #[cfg(feature = "postgres")]
25 QualifiedIdentifier::parse_postgres_with_max_parts(&raw, 2)?;
26 #[cfg(not(feature = "postgres"))]
27 QualifiedIdentifier::parse_with_max_parts(&raw, 2)?;
28 Ok(Self { raw })
29 }
30
31 pub fn as_str(&self) -> &str {
33 &self.raw
34 }
35
36 #[doc(hidden)]
37 pub fn __from_validated_literal(value: &'static str) -> Self {
38 Self {
39 raw: value.to_string(),
40 }
41 }
42
43 #[doc(hidden)]
44 pub fn __from_validated_owned(raw: String) -> Self {
45 Self { raw }
46 }
47}
48
49impl fmt::Display for TableRef {
50 fn fmt(&self, formatter: &mut fmt::Formatter<'_>) -> fmt::Result {
51 formatter.write_str(&self.raw)
52 }
53}
54
55#[derive(Debug, Clone, PartialEq, Eq, PartialOrd, Ord, Hash)]
62pub struct FieldRef {
63 raw: String,
64 #[cfg(feature = "mysql")]
65 mysql: String,
66 #[cfg(feature = "postgres")]
67 postgres: String,
68}
69
70impl FieldRef {
71 pub fn new(value: impl Into<String>) -> Result<Self, DbError> {
76 let raw = value.into();
77 #[cfg(feature = "postgres")]
78 {
79 let postgres = QualifiedIdentifier::parse_postgres(&raw)?.render_postgres();
80 Ok(Self {
81 #[cfg(feature = "mysql")]
85 mysql: quote_qualified(&raw, '`'),
86 postgres,
87 raw,
88 })
89 }
90 #[cfg(not(feature = "postgres"))]
91 {
92 let identifier = QualifiedIdentifier::parse(&raw)?;
93 let _ = &identifier;
95 Ok(Self {
96 #[cfg(feature = "mysql")]
97 mysql: identifier.render('`'),
98 raw,
99 })
100 }
101 }
102
103 pub fn as_str(&self) -> &str {
105 &self.raw
106 }
107
108 #[doc(hidden)]
109 pub fn __from_validated_literal(value: &'static str) -> Self {
110 Self {
111 #[cfg(feature = "mysql")]
112 mysql: value
113 .split('.')
114 .map(|part| format!("`{part}`"))
115 .collect::<Vec<_>>()
116 .join("."),
117 #[cfg(feature = "postgres")]
118 postgres: value
119 .split('.')
120 .map(|part| format!("\"{part}\""))
121 .collect::<Vec<_>>()
122 .join("."),
123 raw: value.to_string(),
124 }
125 }
126
127 #[doc(hidden)]
128 pub fn __from_validated_owned(raw: String) -> Self {
129 Self {
130 #[cfg(feature = "mysql")]
131 mysql: raw
132 .split('.')
133 .map(|part| format!("`{part}`"))
134 .collect::<Vec<_>>()
135 .join("."),
136 #[cfg(feature = "postgres")]
137 postgres: raw
138 .split('.')
139 .map(|part| format!("\"{part}\""))
140 .collect::<Vec<_>>()
141 .join("."),
142 raw,
143 }
144 }
145
146 #[cfg(feature = "mysql")]
147 #[doc(hidden)]
148 pub fn mysql_quoted(&self) -> &str {
149 &self.mysql
150 }
151
152 #[cfg(feature = "postgres")]
153 #[doc(hidden)]
154 pub fn postgres_quoted(&self) -> &str {
155 &self.postgres
156 }
157}
158
159#[doc(hidden)]
160pub const fn __validate_table_literal(value: &str) {
161 validate_literal(value, 2);
162}
163
164#[doc(hidden)]
165pub const fn __validate_field_literal(value: &str) {
166 validate_literal(value, 3);
167}
168
169const fn validate_literal(value: &str, max_parts: usize) {
171 let bytes = value.as_bytes();
172 assert!(!bytes.is_empty(), "SQL identifier cannot be empty");
173 let mut index = 0;
174 let mut segment_start = true;
175 let mut dots = 0;
176 while index < bytes.len() {
177 let byte = bytes[index];
178 if byte == b'.' {
179 assert!(!segment_start, "identifier segment cannot be empty");
180 dots += 1;
181 assert!(dots < max_parts, "SQL identifier has too many segments");
182 segment_start = true;
183 } else if segment_start {
184 assert!(
185 byte == b'_' || byte.is_ascii_alphabetic(),
186 "identifier must start with ASCII letter or underscore"
187 );
188 segment_start = false;
189 } else {
190 assert!(
191 byte == b'_' || byte.is_ascii_alphanumeric(),
192 "identifier contains invalid character"
193 );
194 }
195 index += 1;
196 }
197 assert!(!segment_start, "identifier segment cannot be empty");
198}
199
200impl fmt::Display for FieldRef {
201 fn fmt(&self, formatter: &mut fmt::Formatter<'_>) -> fmt::Result {
202 formatter.write_str(&self.raw)
203 }
204}
205
206#[derive(Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord, Hash)]
208#[non_exhaustive]
209pub enum CompareOp {
210 Eq,
212 Ne,
214 Gt,
216 Lt,
218 Gte,
220 Lte,
222 Like,
224 ILike,
226 NotILike,
228 RegexMatch,
230 RegexIMatch,
232 NotRegexMatch,
234 NotRegexIMatch,
236 IsDistinctFrom,
238 IsNotDistinctFrom,
240}
241
242#[derive(Debug, Clone, Copy, PartialEq, Eq, PartialOrd, Ord, Hash)]
244pub enum SortOrder {
245 Asc,
247 Desc,
249}
250
251#[derive(Debug, Clone, PartialEq)]
253pub enum Predicate {
254 Compare(FieldRef, CompareOp, serde_json::Value),
256 In(FieldRef, Vec<serde_json::Value>),
258 NotIn(FieldRef, Vec<serde_json::Value>),
260 Between(FieldRef, serde_json::Value, serde_json::Value),
262 IsNull(FieldRef),
264 IsNotNull(FieldRef),
266 And(Vec<Predicate>),
268 Or(Vec<Predicate>),
270}
271
272#[derive(Debug, Clone, PartialEq, Eq)]
274pub struct SelectExpr {
275 aggregate: Aggregate,
276 field: Option<FieldRef>,
277 alias: Option<FieldRef>,
278 cast_double: bool,
279}
280
281#[derive(Debug, Clone, Copy, PartialEq, Eq)]
282enum Aggregate {
283 Count,
284 Sum,
285 Avg,
286 Min,
287 Max,
288}
289
290impl SelectExpr {
291 pub fn count_all() -> Self {
293 Self {
294 aggregate: Aggregate::Count,
295 field: None,
296 alias: None,
297 cast_double: false,
298 }
299 }
300
301 pub fn count(field: &FieldRef) -> Self {
303 Self::new(Aggregate::Count, field)
304 }
305
306 pub fn sum(field: &FieldRef) -> Self {
308 Self::new(Aggregate::Sum, field)
309 }
310
311 pub fn avg(field: &FieldRef) -> Self {
313 Self::new(Aggregate::Avg, field)
314 }
315
316 pub fn min(field: &FieldRef) -> Self {
318 Self::new(Aggregate::Min, field)
319 }
320
321 pub fn max(field: &FieldRef) -> Self {
323 Self::new(Aggregate::Max, field)
324 }
325
326 fn new(aggregate: Aggregate, field: &FieldRef) -> Self {
327 Self {
328 aggregate,
329 field: Some(field.clone()),
330 alias: None,
331 cast_double: false,
332 }
333 }
334
335 pub fn cast_double(mut self) -> Self {
337 self.cast_double = true;
338 self
339 }
340
341 pub fn alias(mut self, alias: &FieldRef) -> Self {
343 self.alias = Some(alias.clone());
344 self
345 }
346
347 #[cfg(feature = "mysql")]
348 pub(crate) fn mysql_sql(&self) -> String {
349 self.render('`', "DOUBLE")
350 }
351
352 #[cfg(feature = "postgres")]
353 pub(crate) fn postgres_sql(&self) -> String {
354 self.render('"', "DOUBLE PRECISION")
355 }
356
357 #[cfg(feature = "postgres")]
362 pub(crate) fn postgres_sql_without_alias(&self) -> String {
363 self.render_expression('"', "DOUBLE PRECISION")
364 }
365
366 #[cfg(any(feature = "mysql", feature = "postgres"))]
367 fn render(&self, quote: char, double_type: &str) -> String {
368 let expression = self.render_expression(quote, double_type);
369 self.alias.as_ref().map_or(expression.clone(), |alias| {
370 format!("{expression} AS {}", quote_qualified(alias.as_str(), quote))
371 })
372 }
373
374 #[cfg(any(feature = "mysql", feature = "postgres"))]
376 fn render_expression(&self, quote: char, double_type: &str) -> String {
377 let function = match self.aggregate {
378 Aggregate::Count => "COUNT",
379 Aggregate::Sum => "SUM",
380 Aggregate::Avg => "AVG",
381 Aggregate::Min => "MIN",
382 Aggregate::Max => "MAX",
383 };
384 let operand = self.field.as_ref().map_or_else(
385 || "*".to_string(),
386 |field| quote_qualified(field.as_str(), quote),
387 );
388 let aggregate = format!("{function}({operand})");
389 if self.cast_double {
390 format!("CAST({aggregate} AS {double_type})")
391 } else {
392 aggregate
393 }
394 }
395}
396
397#[derive(Debug, Clone, PartialEq, Eq)]
402#[non_exhaustive]
403pub enum ExprOperand {
404 Column(FieldRef),
406 Int(i64),
408 Text(String),
410}
411
412impl ExprOperand {
413 pub fn column(field: &FieldRef) -> Self {
415 Self::Column(field.clone())
416 }
417
418 pub fn int(value: i64) -> Self {
420 Self::Int(value)
421 }
422
423 pub fn text(value: impl Into<String>) -> Self {
425 Self::Text(value.into())
426 }
427}
428
429#[derive(Debug, Clone, PartialEq, Eq)]
439#[non_exhaustive]
440pub enum SqlExprParam {
441 Int(i64),
443 Numeric(String),
445 Bool(bool),
447 Text(String),
449}
450
451impl SqlExprParam {
452 pub fn text(value: impl Into<String>) -> Self {
454 Self::Text(value.into())
455 }
456
457 fn infer(value: &str) -> Self {
458 match value {
459 "true" => Self::Bool(true),
460 "false" => Self::Bool(false),
461 _ => {
462 if let Ok(int) = value.parse::<i64>() {
463 Self::Int(int)
464 } else if value.parse::<f64>().is_ok_and(f64::is_finite) {
465 Self::Numeric(value.to_string())
467 } else {
468 Self::Text(value.to_string())
469 }
470 }
471 }
472 }
473}
474
475impl From<&str> for SqlExprParam {
476 fn from(value: &str) -> Self {
477 Self::infer(value)
478 }
479}
480
481impl From<String> for SqlExprParam {
482 fn from(value: String) -> Self {
483 Self::infer(&value)
484 }
485}
486
487impl From<&String> for SqlExprParam {
488 fn from(value: &String) -> Self {
489 Self::infer(value)
490 }
491}
492
493impl From<i64> for SqlExprParam {
494 fn from(value: i64) -> Self {
495 Self::Int(value)
496 }
497}
498
499impl From<f64> for SqlExprParam {
500 fn from(value: f64) -> Self {
501 if value.is_finite() {
502 Self::Numeric(value.to_string())
503 } else {
504 Self::Text(value.to_string())
506 }
507 }
508}
509
510impl From<bool> for SqlExprParam {
511 fn from(value: bool) -> Self {
512 Self::Bool(value)
513 }
514}
515
516#[cfg(any(feature = "mysql", feature = "postgres"))]
518#[derive(Debug, Clone, PartialEq, Eq)]
519pub(crate) struct RenderedSqlExpr {
520 pub(crate) sql: String,
521 pub(crate) params: Vec<SqlExprParam>,
522}
523
524#[cfg(feature = "mysql")]
525impl From<SqlExprParam> for crate::mysql::SqlValue {
526 fn from(param: SqlExprParam) -> Self {
527 match param {
528 SqlExprParam::Int(value) => Self::Int(value),
529 SqlExprParam::Numeric(value) => match value.parse::<f64>() {
530 Ok(number) => Self::Float(number),
531 Err(_) => Self::String(value),
532 },
533 SqlExprParam::Bool(value) => Self::Bool(value),
534 SqlExprParam::Text(value) => Self::String(value),
535 }
536 }
537}
538
539#[cfg(feature = "postgres")]
540impl From<SqlExprParam> for crate::postgres::SqlValue {
541 fn from(param: SqlExprParam) -> Self {
542 match param {
543 SqlExprParam::Int(value) => Self::Int(value),
544 SqlExprParam::Numeric(value) => match value.parse::<bigdecimal::BigDecimal>() {
546 Ok(number) => Self::Decimal(number),
547 Err(_) => Self::String(value),
548 },
549 SqlExprParam::Bool(value) => Self::Bool(value),
550 SqlExprParam::Text(value) => Self::String(value),
551 }
552 }
553}
554
555#[derive(Debug, Clone, PartialEq, Eq)]
561#[non_exhaustive]
562pub enum SqlExpr {
563 UnixTimestamp,
565 UnixTimestampAdd(i64),
567 Now,
569 Coalesce {
571 field: FieldRef,
572 fallback: SqlExprParam,
573 },
574 Greatest(Vec<ExprOperand>),
579 Least(Vec<ExprOperand>),
584 NullIf {
586 field: FieldRef,
587 value: SqlExprParam,
588 },
589 Strpos { field: FieldRef, needle: String },
591 Lpad {
593 field: FieldRef,
594 length: i64,
595 fill: String,
596 },
597 Rpad {
599 field: FieldRef,
600 length: i64,
601 fill: String,
602 },
603 ToChar { field: FieldRef, format: String },
607}
608
609impl SqlExpr {
610 pub fn unix_timestamp() -> Self {
613 Self::UnixTimestamp
614 }
615
616 pub fn unix_timestamp_add(seconds: i64) -> Self {
618 Self::UnixTimestampAdd(seconds)
619 }
620
621 pub fn now() -> Self {
623 Self::Now
624 }
625
626 pub fn coalesce(field: &FieldRef, fallback: impl Into<SqlExprParam>) -> Self {
630 Self::Coalesce {
631 field: field.clone(),
632 fallback: fallback.into(),
633 }
634 }
635
636 pub fn greatest(operands: Vec<ExprOperand>) -> Self {
640 debug_assert!(operands.len() >= 2, "GREATEST 至少需要两个操作数");
641 Self::Greatest(operands)
642 }
643
644 pub fn least(operands: Vec<ExprOperand>) -> Self {
648 debug_assert!(operands.len() >= 2, "LEAST 至少需要两个操作数");
649 Self::Least(operands)
650 }
651
652 pub fn nullif(field: &FieldRef, value: impl Into<SqlExprParam>) -> Self {
655 Self::NullIf {
656 field: field.clone(),
657 value: value.into(),
658 }
659 }
660
661 pub fn strpos(field: &FieldRef, needle: impl Into<String>) -> Self {
663 Self::Strpos {
664 field: field.clone(),
665 needle: needle.into(),
666 }
667 }
668
669 pub fn lpad(field: &FieldRef, length: i64, fill: impl Into<String>) -> Self {
671 Self::Lpad {
672 field: field.clone(),
673 length,
674 fill: fill.into(),
675 }
676 }
677
678 pub fn rpad(field: &FieldRef, length: i64, fill: impl Into<String>) -> Self {
680 Self::Rpad {
681 field: field.clone(),
682 length,
683 fill: fill.into(),
684 }
685 }
686
687 pub fn to_char(field: &FieldRef, format: impl Into<String>) -> Self {
691 Self::ToChar {
692 field: field.clone(),
693 format: format.into(),
694 }
695 }
696
697 #[cfg(feature = "mysql")]
703 pub(crate) fn mysql_render(&self) -> Result<RenderedSqlExpr, DbError> {
704 let mut params = Vec::new();
705 let sql = match self {
706 Self::UnixTimestamp => "UNIX_TIMESTAMP()".to_string(),
707 Self::UnixTimestampAdd(seconds) => {
708 params.push(SqlExprParam::Int(*seconds));
709 "UNIX_TIMESTAMP() + ?".to_string()
710 }
711 Self::Now => "CURRENT_TIMESTAMP".to_string(),
712 Self::Coalesce { field, fallback } => {
713 params.push(fallback.clone());
714 format!("COALESCE({}, ?)", field.mysql_quoted())
715 }
716 Self::Greatest(operands) => mysql_function("GREATEST", operands, &mut params),
717 Self::Least(operands) => mysql_function("LEAST", operands, &mut params),
718 Self::NullIf { field, value } => {
719 params.push(value.clone());
720 format!("NULLIF({}, ?)", field.mysql_quoted())
721 }
722 Self::Strpos { field, needle } => {
723 params.push(SqlExprParam::Text(needle.clone()));
724 format!("LOCATE(?, {})", field.mysql_quoted())
725 }
726 Self::Lpad {
727 field,
728 length,
729 fill,
730 } => {
731 params.push(SqlExprParam::Int(*length));
732 params.push(SqlExprParam::Text(fill.clone()));
733 format!("LPAD({}, ?, ?)", field.mysql_quoted())
734 }
735 Self::Rpad {
736 field,
737 length,
738 fill,
739 } => {
740 params.push(SqlExprParam::Int(*length));
741 params.push(SqlExprParam::Text(fill.clone()));
742 format!("RPAD({}, ?, ?)", field.mysql_quoted())
743 }
744 Self::ToChar { .. } => {
745 return Err(DbError::UnsupportedOperator(
746 "to_char 仅支持 PostgreSQL:PG 格式串与 MySQL DATE_FORMAT 记号不等价"
747 .to_string(),
748 ));
749 }
750 };
751 Ok(RenderedSqlExpr { sql, params })
752 }
753
754 #[cfg(feature = "postgres")]
756 pub(crate) fn postgres_render(&self, placeholder: usize) -> RenderedSqlExpr {
757 let mut params = Vec::new();
758 let sql = match self {
759 Self::UnixTimestamp => "EXTRACT(EPOCH FROM now())".to_string(),
760 Self::UnixTimestampAdd(seconds) => {
761 params.push(SqlExprParam::Int(*seconds));
762 format!("EXTRACT(EPOCH FROM now()) + ${placeholder}")
763 }
764 Self::Now => "now()".to_string(),
765 Self::Coalesce { field, fallback } => {
766 params.push(fallback.clone());
767 format!("COALESCE({}, ${placeholder})", field.postgres_quoted())
768 }
769 Self::Greatest(operands) => {
770 postgres_function("GREATEST", operands, placeholder, &mut params)
771 }
772 Self::Least(operands) => postgres_function("LEAST", operands, placeholder, &mut params),
773 Self::NullIf { field, value } => {
774 params.push(value.clone());
775 format!("NULLIF({}, ${placeholder})", field.postgres_quoted())
776 }
777 Self::Strpos { field, needle } => {
778 params.push(SqlExprParam::Text(needle.clone()));
779 format!("strpos({}, ${placeholder})", field.postgres_quoted())
780 }
781 Self::Lpad {
782 field,
783 length,
784 fill,
785 } => {
786 params.push(SqlExprParam::Int(*length));
787 params.push(SqlExprParam::Text(fill.clone()));
788 format!(
789 "lpad({}, ${}::integer, ${})",
790 field.postgres_quoted(),
791 placeholder,
792 placeholder + 1
793 )
794 }
795 Self::Rpad {
796 field,
797 length,
798 fill,
799 } => {
800 params.push(SqlExprParam::Int(*length));
801 params.push(SqlExprParam::Text(fill.clone()));
802 format!(
803 "rpad({}, ${}::integer, ${})",
804 field.postgres_quoted(),
805 placeholder,
806 placeholder + 1
807 )
808 }
809 Self::ToChar { field, format } => {
810 params.push(SqlExprParam::Text(format.clone()));
811 format!("to_char({}, ${placeholder})", field.postgres_quoted())
812 }
813 };
814 RenderedSqlExpr { sql, params }
815 }
816}
817
818#[cfg(feature = "mysql")]
819fn mysql_function(name: &str, operands: &[ExprOperand], params: &mut Vec<SqlExprParam>) -> String {
820 let rendered = operands
821 .iter()
822 .map(|operand| match operand {
823 ExprOperand::Column(field) => field.mysql_quoted().to_string(),
824 ExprOperand::Int(value) => {
825 params.push(SqlExprParam::Int(*value));
826 "?".to_string()
827 }
828 ExprOperand::Text(value) => {
829 params.push(SqlExprParam::Text(value.clone()));
830 "?".to_string()
831 }
832 })
833 .collect::<Vec<_>>();
834 format!("{name}({})", rendered.join(", "))
835}
836
837#[cfg(feature = "postgres")]
838fn postgres_function(
839 name: &str,
840 operands: &[ExprOperand],
841 placeholder: usize,
842 params: &mut Vec<SqlExprParam>,
843) -> String {
844 let rendered = operands
845 .iter()
846 .map(|operand| match operand {
847 ExprOperand::Column(field) => field.postgres_quoted().to_string(),
848 ExprOperand::Int(value) => {
849 let index = placeholder + params.len();
850 params.push(SqlExprParam::Int(*value));
851 format!("${index}")
852 }
853 ExprOperand::Text(value) => {
854 let index = placeholder + params.len();
855 params.push(SqlExprParam::Text(value.clone()));
856 format!("${index}")
857 }
858 })
859 .collect::<Vec<_>>();
860 format!("{name}({})", rendered.join(", "))
861}
862
863#[cfg(any(feature = "mysql", feature = "postgres"))]
864fn quote_qualified(value: &str, quote: char) -> String {
865 value
866 .split('.')
867 .map(|segment| format!("{quote}{segment}{quote}"))
868 .collect::<Vec<_>>()
869 .join(".")
870}
871
872#[cfg(any(feature = "mysql", feature = "postgres"))]
873impl SortOrder {
874 pub(crate) const fn is_ascending(self) -> bool {
875 matches!(self, Self::Asc)
876 }
877}
878
879#[cfg(test)]
880#[allow(clippy::expect_used)]
881mod tests {
882 use super::*;
883
884 #[test]
885 fn controlled_references_validate_once_and_render_both_dialects() {
886 let table = TableRef::new("org_user").expect("合法表名应创建成功");
887 let field = FieldRef::new("org_user.username").expect("合法限定字段应创建成功");
888
889 assert_eq!(table.as_str(), "org_user");
890 assert_eq!(field.as_str(), "org_user.username");
891 #[cfg(feature = "mysql")]
892 assert_eq!(field.mysql_quoted(), "`org_user`.`username`");
893 #[cfg(feature = "postgres")]
894 assert_eq!(field.postgres_quoted(), "\"org_user\".\"username\"");
895 }
896
897 #[test]
898 fn controlled_references_reject_expressions_and_injection_payloads() {
899 for invalid in ["", "users;DROP", "COUNT(*)", "a.b.c.d", "a..b"] {
900 assert!(TableRef::new(invalid).is_err(), "表引用应拒绝 {invalid:?}");
901 assert!(
902 FieldRef::new(invalid).is_err(),
903 "字段引用应拒绝 {invalid:?}"
904 );
905 }
906 #[cfg(not(feature = "postgres"))]
908 for invalid in ["用户", "a$b", "a\"b"] {
909 assert!(TableRef::new(invalid).is_err(), "表引用应拒绝 {invalid:?}");
910 assert!(
911 FieldRef::new(invalid).is_err(),
912 "字段引用应拒绝 {invalid:?}"
913 );
914 }
915 assert!(FieldRef::new("users.id.extra").is_ok());
917 assert!(TableRef::new("users.id.extra").is_err());
918 }
919
920 #[cfg(feature = "postgres")]
923 #[test]
924 #[allow(clippy::expect_used)]
925 fn postgres_references_accept_extended_identifiers_and_cap_63_bytes() {
926 let table = TableRef::new("订单").expect("中文表名应合法");
927 assert_eq!(table.as_str(), "订单");
928
929 let field = FieldRef::new("金额").expect("中文字段名应合法");
930 assert_eq!(field.as_str(), "金额");
931 assert_eq!(field.postgres_quoted(), "\"金额\"");
932
933 let qualified = FieldRef::new("订单.金额").expect("中文限定字段名应合法");
934 assert_eq!(qualified.postgres_quoted(), "\"订单\".\"金额\"");
935
936 let dollar = FieldRef::new("a$b").expect("非首字符 `$` 应合法");
937 assert_eq!(dollar.postgres_quoted(), "\"a$b\"");
938
939 let quoted = FieldRef::new("a\"b").expect("内嵌双引号应合法");
941 assert_eq!(quoted.postgres_quoted(), "\"a\"\"b\"");
942
943 assert!(TableRef::new("a".repeat(63)).is_ok());
945 assert!(FieldRef::new("a".repeat(63)).is_ok());
946 assert!(TableRef::new("用".repeat(21)).is_ok());
947 for oversized in ["a".repeat(64), "用".repeat(22)] {
948 let error = TableRef::new(oversized.clone()).expect_err("超长表名必须拒绝");
949 assert!(matches!(error, DbError::InvalidArgument(_)), "{error:?}");
950 assert!(FieldRef::new(oversized).is_err(), "超长字段名必须拒绝");
951 }
952 }
953
954 #[test]
955 fn qualified_schema_references_render_every_segment() {
956 let table = TableRef::new("audit.users").expect("schema.table 应合法");
957 let field = FieldRef::new("audit.users.id").expect("schema.table.column 应合法");
958 assert_eq!(table.as_str(), "audit.users");
959 assert_eq!(field.as_str(), "audit.users.id");
960 #[cfg(feature = "mysql")]
961 assert_eq!(field.mysql_quoted(), "`audit`.`users`.`id`");
962 #[cfg(feature = "postgres")]
963 assert_eq!(field.postgres_quoted(), "\"audit\".\"users\".\"id\"");
964 }
965
966 #[test]
967 fn server_expressions_render_fixed_fragments_with_bound_dynamic_parts() {
968 #[cfg(feature = "mysql")]
969 {
970 let rendered = SqlExpr::unix_timestamp()
971 .mysql_render()
972 .expect("MySQL 表达式渲染应成功");
973 assert_eq!(rendered.sql, "UNIX_TIMESTAMP()");
974 assert!(rendered.params.is_empty());
975
976 let rendered = SqlExpr::unix_timestamp_add(900)
977 .mysql_render()
978 .expect("MySQL 表达式渲染应成功");
979 assert_eq!(rendered.sql, "UNIX_TIMESTAMP() + ?");
980 assert_eq!(rendered.params, vec![SqlExprParam::Int(900)]);
981
982 let rendered = SqlExpr::unix_timestamp_add(-30)
984 .mysql_render()
985 .expect("MySQL 表达式渲染应成功");
986 assert_eq!(rendered.sql, "UNIX_TIMESTAMP() + ?");
987 assert_eq!(rendered.params, vec![SqlExprParam::Int(-30)]);
988 }
989 #[cfg(feature = "postgres")]
990 {
991 let rendered = SqlExpr::unix_timestamp().postgres_render(1);
992 assert_eq!(rendered.sql, "EXTRACT(EPOCH FROM now())");
993 assert!(rendered.params.is_empty());
994
995 let rendered = SqlExpr::unix_timestamp_add(900).postgres_render(3);
996 assert_eq!(rendered.sql, "EXTRACT(EPOCH FROM now()) + $3");
997 assert_eq!(rendered.params, vec![SqlExprParam::Int(900)]);
998
999 let rendered = SqlExpr::now().postgres_render(1);
1000 assert_eq!(rendered.sql, "now()");
1001 assert!(rendered.params.is_empty());
1002 }
1003 }
1004
1005 #[test]
1006 fn string_expressions_render_dialect_specific_sql_with_bound_operands() {
1007 #[cfg(any(feature = "mysql", feature = "postgres"))]
1008 let field = FieldRef::new("t.c").expect("限定字段名应合法");
1009
1010 #[cfg(feature = "mysql")]
1011 {
1012 let rendered = SqlExpr::coalesce(&field, "x")
1013 .mysql_render()
1014 .expect("MySQL 表达式渲染应成功");
1015 assert_eq!(rendered.sql, "COALESCE(`t`.`c`, ?)");
1016 assert_eq!(rendered.params, vec![SqlExprParam::Text("x".into())]);
1017
1018 let operands = vec![
1019 ExprOperand::column(&field),
1020 ExprOperand::int(7),
1021 ExprOperand::text("z"),
1022 ];
1023 let rendered = SqlExpr::greatest(operands)
1024 .mysql_render()
1025 .expect("MySQL 表达式渲染应成功");
1026 assert_eq!(rendered.sql, "GREATEST(`t`.`c`, ?, ?)");
1027 assert_eq!(
1028 rendered.params,
1029 vec![SqlExprParam::Int(7), SqlExprParam::Text("z".into())]
1030 );
1031
1032 let operands = vec![ExprOperand::column(&field), ExprOperand::int(1)];
1033 let rendered = SqlExpr::least(operands)
1034 .mysql_render()
1035 .expect("MySQL 表达式渲染应成功");
1036 assert_eq!(rendered.sql, "LEAST(`t`.`c`, ?)");
1037 assert_eq!(rendered.params, vec![SqlExprParam::Int(1)]);
1038
1039 let rendered = SqlExpr::nullif(&field, "")
1040 .mysql_render()
1041 .expect("MySQL 表达式渲染应成功");
1042 assert_eq!(rendered.sql, "NULLIF(`t`.`c`, ?)");
1043 assert_eq!(rendered.params, vec![SqlExprParam::Text(String::new())]);
1044
1045 let rendered = SqlExpr::strpos(&field, "needle")
1046 .mysql_render()
1047 .expect("MySQL 表达式渲染应成功");
1048 assert_eq!(rendered.sql, "LOCATE(?, `t`.`c`)");
1049 assert_eq!(rendered.params, vec![SqlExprParam::Text("needle".into())]);
1050
1051 let rendered = SqlExpr::lpad(&field, 5, "*")
1052 .mysql_render()
1053 .expect("MySQL 表达式渲染应成功");
1054 assert_eq!(rendered.sql, "LPAD(`t`.`c`, ?, ?)");
1055 assert_eq!(
1056 rendered.params,
1057 vec![SqlExprParam::Int(5), SqlExprParam::Text("*".into())]
1058 );
1059
1060 let rendered = SqlExpr::rpad(&field, 6, "-")
1061 .mysql_render()
1062 .expect("MySQL 表达式渲染应成功");
1063 assert_eq!(rendered.sql, "RPAD(`t`.`c`, ?, ?)");
1064 assert_eq!(
1065 rendered.params,
1066 vec![SqlExprParam::Int(6), SqlExprParam::Text("-".into())]
1067 );
1068
1069 let error = SqlExpr::to_char(&field, "%Y-%m")
1071 .mysql_render()
1072 .expect_err("MySQL 不支持 to_char");
1073 assert!(
1074 matches!(error, DbError::UnsupportedOperator(_)),
1075 "{error:?}"
1076 );
1077 }
1078
1079 #[cfg(feature = "postgres")]
1080 {
1081 let rendered = SqlExpr::coalesce(&field, "x").postgres_render(1);
1082 assert_eq!(rendered.sql, "COALESCE(\"t\".\"c\", $1)");
1083 assert_eq!(rendered.params, vec![SqlExprParam::Text("x".into())]);
1084
1085 let operands = vec![
1086 ExprOperand::column(&field),
1087 ExprOperand::int(7),
1088 ExprOperand::text("z"),
1089 ];
1090 let rendered = SqlExpr::greatest(operands).postgres_render(1);
1091 assert_eq!(rendered.sql, "GREATEST(\"t\".\"c\", $1, $2)");
1092 assert_eq!(
1093 rendered.params,
1094 vec![SqlExprParam::Int(7), SqlExprParam::Text("z".into())]
1095 );
1096
1097 let operands = vec![ExprOperand::column(&field), ExprOperand::int(1)];
1098 let rendered = SqlExpr::least(operands).postgres_render(2);
1099 assert_eq!(rendered.sql, "LEAST(\"t\".\"c\", $2)");
1100 assert_eq!(rendered.params, vec![SqlExprParam::Int(1)]);
1101
1102 let rendered = SqlExpr::nullif(&field, "").postgres_render(1);
1103 assert_eq!(rendered.sql, "NULLIF(\"t\".\"c\", $1)");
1104 assert_eq!(rendered.params, vec![SqlExprParam::Text(String::new())]);
1105
1106 let rendered = SqlExpr::strpos(&field, "needle").postgres_render(1);
1107 assert_eq!(rendered.sql, "strpos(\"t\".\"c\", $1)");
1108 assert_eq!(rendered.params, vec![SqlExprParam::Text("needle".into())]);
1109
1110 let rendered = SqlExpr::lpad(&field, 5, "*").postgres_render(1);
1111 assert_eq!(rendered.sql, "lpad(\"t\".\"c\", $1::integer, $2)");
1112 assert_eq!(
1113 rendered.params,
1114 vec![SqlExprParam::Int(5), SqlExprParam::Text("*".into())]
1115 );
1116
1117 let rendered = SqlExpr::rpad(&field, 6, "-").postgres_render(1);
1118 assert_eq!(rendered.sql, "rpad(\"t\".\"c\", $1::integer, $2)");
1119 assert_eq!(
1120 rendered.params,
1121 vec![SqlExprParam::Int(6), SqlExprParam::Text("-".into())]
1122 );
1123
1124 let rendered = SqlExpr::to_char(&field, "YYYY-MM").postgres_render(4);
1125 assert_eq!(rendered.sql, "to_char(\"t\".\"c\", $4)");
1126 assert_eq!(rendered.params, vec![SqlExprParam::Text("YYYY-MM".into())]);
1127 }
1128 }
1129
1130 #[test]
1132 fn expression_literals_infer_binding_types() {
1133 assert_eq!(SqlExprParam::from("0"), SqlExprParam::Int(0));
1134 assert_eq!(SqlExprParam::from("-7"), SqlExprParam::Int(-7));
1135 assert_eq!(
1136 SqlExprParam::from("0.00"),
1137 SqlExprParam::Numeric("0.00".into())
1138 );
1139 assert_eq!(SqlExprParam::from("true"), SqlExprParam::Bool(true));
1140 assert_eq!(SqlExprParam::from("false"), SqlExprParam::Bool(false));
1141 assert_eq!(SqlExprParam::from("x"), SqlExprParam::Text("x".into()));
1142 assert_eq!(
1143 SqlExprParam::text("0"),
1144 SqlExprParam::Text("0".into()),
1145 "text() 必须绕过数值推断"
1146 );
1147
1148 #[cfg(feature = "postgres")]
1149 {
1150 let field = FieldRef::new("t.c").expect("限定字段名应合法");
1151 let rendered = SqlExpr::coalesce(&field, "0").postgres_render(1);
1152 assert_eq!(rendered.sql, "COALESCE(\"t\".\"c\", $1)");
1153 assert_eq!(rendered.params, vec![SqlExprParam::Int(0)]);
1154
1155 let rendered = SqlExpr::nullif(&field, "7").postgres_render(2);
1156 assert_eq!(rendered.sql, "NULLIF(\"t\".\"c\", $2)");
1157 assert_eq!(rendered.params, vec![SqlExprParam::Int(7)]);
1158
1159 let rendered = SqlExpr::coalesce(&field, "0.00").postgres_render(1);
1160 assert_eq!(rendered.params, vec![SqlExprParam::Numeric("0.00".into())]);
1161
1162 let rendered = SqlExpr::coalesce(&field, SqlExprParam::text("0")).postgres_render(1);
1163 assert_eq!(rendered.params, vec![SqlExprParam::Text("0".into())]);
1164 }
1165 }
1166
1167 #[cfg(feature = "mysql")]
1168 #[tokio::test]
1169 async fn mysql_query_builder_consumes_controlled_references_without_value_interpolation() {
1170 let pool = sqlx::mysql::MySqlPoolOptions::new()
1171 .connect_lazy("mysql://root:test@127.0.0.1:3306/test")
1172 .expect("lazy MySQL pool 应创建成功");
1173 let database = crate::Database::from_pool(pool, crate::DatabaseConfig::default())
1174 .expect("默认 MySQL 配置应有效");
1175 let table = TableRef::new("org_user").expect("固定表名有效");
1176 let username = FieldRef::new("username").expect("固定字段名有效");
1177
1178 let sql = database
1179 .table(&table)
1180 .field(&username)
1181 .where_and(&username, CompareOp::Eq, "alice")
1182 .expect("固定受控条件应合法")
1183 .order(&username, SortOrder::Asc)
1184 .try_to_sql()
1185 .expect("受控 MySQL 查询应生成 SQL");
1186
1187 assert!(sql.contains("`org_user`"));
1188 assert!(sql.contains("`username`"));
1189 assert!(sql.contains('?'));
1190 assert!(!sql.contains("alice"));
1191 }
1192
1193 #[cfg(feature = "postgres")]
1194 #[tokio::test]
1195 async fn postgres_query_builder_consumes_controlled_references_without_value_interpolation() {
1196 let pool = sqlx::postgres::PgPoolOptions::new()
1197 .connect_lazy("postgres://postgres:test@127.0.0.1:5432/test")
1198 .expect("lazy PostgreSQL pool 应创建成功");
1199 let database = crate::PgDatabase::from_pool(pool, crate::PgDatabaseConfig::default())
1200 .expect("默认 PostgreSQL 配置应有效");
1201 let table = TableRef::new("org_user").expect("固定表名有效");
1202 let username = FieldRef::new("username").expect("固定字段名有效");
1203
1204 let sql = database
1205 .table(&table)
1206 .field(&username)
1207 .where_and(&username, CompareOp::Eq, "alice")
1208 .expect("固定受控条件应合法")
1209 .order(&username, SortOrder::Desc)
1210 .try_to_sql()
1211 .expect("受控 PostgreSQL 查询应生成 SQL");
1212
1213 assert!(sql.contains("\"org_user\""));
1214 assert!(sql.contains("\"username\""));
1215 assert!(sql.contains("$1"));
1216 assert!(!sql.contains("alice"));
1217 }
1218}