1use crate::postgres::condition::{ComparisonOperator, Condition, SqlValue};
2use crate::postgres::field::{FieldType, OrderClause};
3use sqlx::postgres::PgPool;
4use sqlx::{TypeInfo, ValueRef};
5use std::collections::HashMap;
6
7macro_rules! bind_value_match {
25 ($query:expr, $param:expr) => {
26 match $param {
27 SqlValue::Null => $query.bind(Option::<i32>::None),
29 SqlValue::Bool(b) => $query.bind(*b),
31 SqlValue::Int(i) => $query.bind(*i),
33 SqlValue::Float(f) => $query.bind(*f),
35 SqlValue::String(s) => $query.bind(s.clone()),
37 SqlValue::Bytes(b) => $query.bind(b.clone()),
39 SqlValue::Json(j) => $query.bind(j.clone()),
41 SqlValue::DateTime(dt) => $query.bind(*dt),
43 SqlValue::Timestamp(ts) => $query.bind(*ts),
45 SqlValue::Decimal(decimal) => $query.bind(decimal.clone()),
47 SqlValue::Uuid(uuid) => $query.bind(*uuid),
49 SqlValue::DateTimeTz(dt) => $query.bind(*dt),
51 SqlValue::Date(date) => $query.bind(*date),
53 SqlValue::Time(time) => $query.bind(*time),
55 SqlValue::Interval(micros) => $query.bind(sqlx::postgres::types::PgInterval {
57 months: 0,
58 days: 0,
59 microseconds: *micros,
60 }),
61 SqlValue::Inet(s)
64 | SqlValue::Cidr(s)
65 | SqlValue::MacAddr(s)
66 | SqlValue::MacAddr8(s)
67 | SqlValue::Range(s)
68 | SqlValue::Multirange(s) => $query.bind(s.clone()),
69 SqlValue::Array(values) => match values
72 .iter()
73 .find(|value| !matches!(value, SqlValue::Null))
74 {
75 Some(SqlValue::Bool(_)) => $query.bind(
76 values
77 .iter()
78 .map(array_element_bool)
79 .collect::<Vec<Option<bool>>>(),
80 ),
81 Some(SqlValue::Int(_)) => $query.bind(
82 values
83 .iter()
84 .map(array_element_i64)
85 .collect::<Vec<Option<i64>>>(),
86 ),
87 Some(SqlValue::Float(_)) => $query.bind(
88 values
89 .iter()
90 .map(array_element_f64)
91 .collect::<Vec<Option<f64>>>(),
92 ),
93 Some(SqlValue::Uuid(_)) => $query.bind(
94 values
95 .iter()
96 .map(array_element_uuid)
97 .collect::<Vec<Option<uuid::Uuid>>>(),
98 ),
99 Some(SqlValue::Date(_)) => $query.bind(
100 values
101 .iter()
102 .map(array_element_date)
103 .collect::<Vec<Option<chrono::NaiveDate>>>(),
104 ),
105 Some(SqlValue::Time(_)) => $query.bind(
106 values
107 .iter()
108 .map(array_element_time)
109 .collect::<Vec<Option<chrono::NaiveTime>>>(),
110 ),
111 Some(SqlValue::DateTimeTz(_)) => $query.bind(
112 values
113 .iter()
114 .map(array_element_datetime_tz)
115 .collect::<Vec<Option<chrono::DateTime<chrono::Utc>>>>(),
116 ),
117 Some(SqlValue::DateTime(_)) => $query.bind(
118 values
119 .iter()
120 .map(array_element_datetime)
121 .collect::<Vec<Option<chrono::NaiveDateTime>>>(),
122 ),
123 Some(SqlValue::Decimal(_)) => $query.bind(
124 values
125 .iter()
126 .map(array_element_decimal)
127 .collect::<Vec<Option<bigdecimal::BigDecimal>>>(),
128 ),
129 Some(SqlValue::Timestamp(_)) => $query.bind(
130 values
131 .iter()
132 .map(array_element_timestamp)
133 .collect::<Vec<Option<i64>>>(),
134 ),
135 Some(SqlValue::Bytes(_)) => $query.bind(
136 values
137 .iter()
138 .map(array_element_bytes)
139 .collect::<Vec<Option<Vec<u8>>>>(),
140 ),
141 Some(SqlValue::Json(_)) => $query.bind(
142 values
143 .iter()
144 .map(array_element_json)
145 .collect::<Vec<Option<serde_json::Value>>>(),
146 ),
147 _ => $query.bind(
150 values
151 .iter()
152 .map(array_element_string)
153 .collect::<Vec<Option<String>>>(),
154 ),
155 },
156 SqlValue::TypedArray {
159 element_type,
160 values,
161 } => match *element_type {
162 "int2" => $query.bind(
163 values
164 .iter()
165 .map(array_element_i16)
166 .collect::<Vec<Option<i16>>>(),
167 ),
168 "int4" => $query.bind(
169 values
170 .iter()
171 .map(array_element_i32)
172 .collect::<Vec<Option<i32>>>(),
173 ),
174 "int8" => $query.bind(
175 values
176 .iter()
177 .map(array_element_i64)
178 .collect::<Vec<Option<i64>>>(),
179 ),
180 "float4" => $query.bind(
181 values
182 .iter()
183 .map(array_element_f32)
184 .collect::<Vec<Option<f32>>>(),
185 ),
186 "float8" => $query.bind(
187 values
188 .iter()
189 .map(array_element_f64)
190 .collect::<Vec<Option<f64>>>(),
191 ),
192 "bool" => $query.bind(
193 values
194 .iter()
195 .map(array_element_bool)
196 .collect::<Vec<Option<bool>>>(),
197 ),
198 "bytea" => $query.bind(
199 values
200 .iter()
201 .map(array_element_bytes)
202 .collect::<Vec<Option<Vec<u8>>>>(),
203 ),
204 "uuid" => $query.bind(
205 values
206 .iter()
207 .map(array_element_uuid)
208 .collect::<Vec<Option<uuid::Uuid>>>(),
209 ),
210 "date" => $query.bind(
211 values
212 .iter()
213 .map(array_element_date)
214 .collect::<Vec<Option<chrono::NaiveDate>>>(),
215 ),
216 "time" => $query.bind(
217 values
218 .iter()
219 .map(array_element_time)
220 .collect::<Vec<Option<chrono::NaiveTime>>>(),
221 ),
222 "timestamp" => $query.bind(
223 values
224 .iter()
225 .map(array_element_datetime)
226 .collect::<Vec<Option<chrono::NaiveDateTime>>>(),
227 ),
228 "timestamptz" => $query.bind(
229 values
230 .iter()
231 .map(array_element_datetime_tz)
232 .collect::<Vec<Option<chrono::DateTime<chrono::Utc>>>>(),
233 ),
234 "numeric" => $query.bind(
235 values
236 .iter()
237 .map(array_element_decimal)
238 .collect::<Vec<Option<bigdecimal::BigDecimal>>>(),
239 ),
240 "jsonb" => $query.bind(
241 values
242 .iter()
243 .map(array_element_json)
244 .collect::<Vec<Option<serde_json::Value>>>(),
245 ),
246 _ => $query.bind(
249 values
250 .iter()
251 .map(array_element_string)
252 .collect::<Vec<Option<String>>>(),
253 ),
254 },
255 }
256 };
257}
258
259const INSERT_BATCH_SIZE: usize = 500;
264
265const UPDATE_BATCH_SIZE: usize = 1000;
267
268const MAX_BIND_PARAMS: usize = 65_535;
273
274const MERGE_MIN_SERVER_VERSION: i32 = 150_000;
276
277const MERGE_BY_SOURCE_MIN_SERVER_VERSION: i32 = 170_000;
279
280fn merge_version_supported(version: i32) -> bool {
282 version >= MERGE_MIN_SERVER_VERSION
283}
284
285async fn server_version_num(
287 executor: &mut QueryExecutor<'_>,
288) -> Result<i32, crate::error::DbError> {
289 let sql = "SELECT current_setting('server_version_num')::int";
290 match executor {
291 QueryExecutor::Pool(pool) => Ok(sqlx::query_scalar::<_, i32>(sql).fetch_one(*pool).await?),
292 QueryExecutor::Transaction(transaction) => {
293 let connection = transaction.executor().ok_or_else(|| {
294 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
295 })?;
296 Ok(sqlx::query_scalar::<_, i32>(sql)
297 .fetch_one(&mut *connection)
298 .await?)
299 }
300 }
301}
302
303fn derive_update_batch_size(field_count: usize, requested: usize) -> usize {
305 let per_record = field_count.saturating_mul(2).saturating_add(1);
306 if per_record == 0 {
307 return requested;
308 }
309 (MAX_BIND_PARAMS / per_record).max(1).min(requested)
310}
311
312fn derive_insert_batch_size(column_count: usize, requested: usize) -> usize {
314 if column_count == 0 {
315 return requested;
316 }
317 (MAX_BIND_PARAMS / column_count).max(1).min(requested)
318}
319
320fn derive_upsert_batch_size(column_count: usize, requested: usize) -> usize {
324 if column_count == 0 {
325 return requested;
326 }
327 (MAX_BIND_PARAMS / column_count).max(1).min(requested)
328}
329
330fn update_field_count(records: &[serde_json::Value], where_field: &str) -> usize {
332 records
333 .first()
334 .and_then(|r| r.as_object())
335 .map(|obj| obj.keys().filter(|k| k.as_str() != where_field).count())
336 .unwrap_or(0)
337}
338
339fn push_placeholder(params: &mut Vec<SqlValue>, value: SqlValue) -> String {
345 params.push(value);
346 format!("${}", params.len())
347}
348
349fn pg_value_cast(field_type: Option<&FieldType>) -> Option<String> {
355 let field_type = field_type?;
356 match field_type {
357 FieldType::Inet => Some("::inet".to_string()),
358 FieldType::Cidr => Some("::cidr".to_string()),
359 FieldType::MacAddr => Some("::macaddr".to_string()),
360 FieldType::MacAddr8 => Some("::macaddr8".to_string()),
361 FieldType::Array(inner) => pg_value_cast(Some(inner)).map(|suffix| format!("{suffix}[]")),
362 FieldType::Range(_) | FieldType::Multirange(_) => field_type
365 .postgres_type_name()
366 .map(|name| format!("::{name}")),
367 _ => None,
368 }
369}
370
371fn push_typed_placeholder(
373 params: &mut Vec<SqlValue>,
374 value: SqlValue,
375 field_type: Option<&FieldType>,
376) -> String {
377 let placeholder = push_placeholder(params, value);
378 match pg_value_cast(field_type) {
379 Some(suffix) => format!("{placeholder}{suffix}"),
380 None => placeholder,
381 }
382}
383
384fn pg_array_element_type_name(field_type: &FieldType) -> Option<&'static str> {
391 match field_type {
392 FieldType::Standard => Some("int8"),
393 FieldType::Bool => Some("bool"),
394 FieldType::Text => Some("text"),
395 FieldType::Blob => Some("bytea"),
396 FieldType::Uuid => Some("uuid"),
397 FieldType::Date => Some("date"),
398 FieldType::Time => Some("time"),
399 FieldType::DateTime => Some("timestamp"),
400 FieldType::TimestampTz => Some("timestamptz"),
401 FieldType::Timestamp => Some("int8"),
402 FieldType::Decimal => Some("numeric"),
403 FieldType::Json | FieldType::Jsonb => Some("jsonb"),
404 FieldType::Inet => Some("inet"),
405 FieldType::Cidr => Some("cidr"),
406 FieldType::MacAddr => Some("macaddr"),
407 FieldType::MacAddr8 => Some("macaddr8"),
408 FieldType::Array(_)
409 | FieldType::Bit
410 | FieldType::Varbit
411 | FieldType::Range(_)
412 | FieldType::Multirange(_) => None,
413 }
414}
415
416#[allow(dead_code)]
418pub(crate) struct SqlGenerator {
419 sql: String,
421 params: Vec<SqlValue>,
423}
424
425#[allow(dead_code)]
426impl SqlGenerator {
427 pub(crate) fn new() -> Self {
433 Self {
434 sql: String::with_capacity(256),
436 params: Vec::with_capacity(8),
438 }
439 }
440
441 pub(crate) fn get_sql(&self) -> &str {
443 &self.sql
444 }
445
446 pub(crate) fn get_params(&self) -> &[SqlValue] {
448 &self.params
449 }
450
451 fn append(&mut self, fragment: &str) {
453 self.sql.push_str(fragment);
454 }
455
456 fn add_param(&mut self, param: SqlValue) {
458 self.params.push(param);
459 }
460
461 fn append_condition(&mut self, condition: Condition) -> Result<(), crate::error::DbError> {
462 let rendered = self.render_condition_fragment(condition)?;
463 self.sql.push_str(&rendered);
464 Ok(())
465 }
466
467 fn render_condition_fragment(
469 &mut self,
470 condition: Condition,
471 ) -> Result<String, crate::error::DbError> {
472 let rendered =
473 crate::postgres::condition::render_condition_checked(condition, self.params.len())?;
474 self.params.extend(rendered.params);
475 Ok(rendered.sql)
476 }
477
478 fn clear(&mut self) {
480 self.sql.clear();
481 self.params.clear();
482 }
483
484 #[cfg(test)]
486 pub(crate) fn clear_for_test(&mut self) {
487 self.clear();
488 }
489
490 #[cfg(test)]
492 pub(crate) fn sql_capacity(&self) -> usize {
493 self.sql.capacity()
494 }
495
496 #[cfg(test)]
498 pub(crate) fn params_capacity(&self) -> usize {
499 self.params.capacity()
500 }
501
502 fn build_select(&mut self, builder: &QueryBuilder) -> Result<(), crate::error::DbError> {
511 self.clear();
512 self.build_compound_select(builder)
513 }
514
515 fn build_compound_select(
520 &mut self,
521 builder: &QueryBuilder,
522 ) -> Result<(), crate::error::DbError> {
523 if !builder.ctes.is_empty() {
524 self.build_with(&builder.ctes)?;
525 }
526
527 self.build_set_chain(builder, &builder.set_ops, builder.set_ops.len())?;
528
529 if !builder.order_by.is_empty() {
531 self.build_order_by(&builder.order_by);
532 }
533
534 if let Some(limit) = builder.limit {
536 self.append(" LIMIT ");
537 self.append(&limit.to_string());
538 }
539
540 if let Some(offset) = builder.offset {
542 self.append(" OFFSET ");
543 self.append(&offset.to_string());
544 }
545
546 Ok(())
547 }
548
549 fn build_with(&mut self, ctes: &[Cte<'_>]) -> Result<(), crate::error::DbError> {
551 self.append("WITH ");
552 if ctes.iter().any(|cte| cte.recursive) {
553 self.append("RECURSIVE ");
554 }
555 for (index, cte) in ctes.iter().enumerate() {
556 if index > 0 {
557 self.append(", ");
558 }
559 self.append(&super::identifier::quote_identifier(&cte.name)?);
560 self.append(" AS (");
561 self.build_compound_select(&cte.query)?;
562 self.append(")");
563 }
564 self.append(" ");
565 Ok(())
566 }
567
568 fn build_set_chain(
574 &mut self,
575 head: &QueryBuilder,
576 ops: &[(SetOperator, Box<QueryBuilder>)],
577 count: usize,
578 ) -> Result<(), crate::error::DbError> {
579 if count == 0 {
580 return self.build_select_core(head);
581 }
582 let (operator, branch) = &ops[count - 1];
583 let needs_parens = count > 1
587 && (ops[..count - 1].iter().any(|(op, _)| !op.is_union()) || !operator.is_union());
588 if needs_parens {
589 self.append("(");
590 }
591 self.build_set_chain(head, ops, count - 1)?;
592 if needs_parens {
593 self.append(")");
594 }
595 self.append(operator.sql());
596 self.append("(");
597 self.build_compound_select(branch)?;
598 self.append(")");
599 Ok(())
600 }
601
602 fn build_select_core(&mut self, builder: &QueryBuilder) -> Result<(), crate::error::DbError> {
604 self.append("SELECT ");
606
607 if !builder.distinct_on.is_empty() {
609 if builder.distinct {
610 return Err(crate::error::DbError::InvalidArgument(
611 "distinct 与 distinct_on 不能同时使用".to_string(),
612 ));
613 }
614 let matched = builder.order_by.len() >= builder.distinct_on.len()
616 && builder
617 .order_by
618 .iter()
619 .take(builder.distinct_on.len())
620 .zip(&builder.distinct_on)
621 .all(|(order, expected)| order.field == *expected);
622 if !matched {
623 return Err(crate::error::DbError::InvalidArgument(
624 "distinct_on 要求最左 ORDER BY 与 DISTINCT ON 字段完全一致(顺序相同)"
625 .to_string(),
626 ));
627 }
628 self.append("DISTINCT ON (");
629 self.append(&builder.distinct_on.join(", "));
630 self.append(") ");
631 } else if builder.distinct {
632 self.append("DISTINCT ");
633 }
634
635 if builder.fields.is_empty()
637 && builder.projection_exprs.is_empty()
638 && builder.window_exprs.is_empty()
639 {
640 self.append("*");
641 } else {
642 let mut projection = builder.fields.clone();
644 for (expression, alias) in &builder.projection_exprs {
645 let placeholder = self.params.len() + 1;
646 let rendered = expression.postgres_render(placeholder);
647 for param in rendered.params {
648 self.params.push(param.into());
649 }
650 let mut piece = rendered.sql;
651 if let Some(alias) = alias {
652 piece.push_str(" AS ");
653 piece.push_str(&super::identifier::quote_identifier(alias)?);
654 }
655 projection.push(piece);
656 }
657 for window in &builder.window_exprs {
658 projection.push(window.render(self)?);
659 }
660 self.append(&projection.join(", "));
661 }
662
663 self.append(" FROM ");
665 self.append(&super::identifier::quote_table(&builder.table)?);
666
667 if !builder.joins.is_empty() {
669 self.build_joins(&builder.joins)?;
670 }
671
672 if !builder.conditions.is_empty() {
674 self.build_where(&builder.conditions)?;
675 }
676
677 if !builder.group_by.is_empty() || !builder.grouping_constructs.is_empty() {
679 self.build_group_by(&builder.group_by, &builder.grouping_constructs)?;
680 }
681
682 if !builder.having_clause.is_empty() || !builder.having_exprs.is_empty() {
687 if !builder.having_clause.is_empty()
688 && builder.group_by.is_empty()
689 && builder.grouping_constructs.is_empty()
690 {
691 return Err(crate::error::DbError::MissingGroupByClause);
692 }
693 self.build_having(&builder.having_clause, &builder.having_exprs)?;
694 }
695
696 if !builder.named_windows.is_empty() {
698 self.build_window_clause(&builder.named_windows)?;
699 }
700
701 Ok(())
702 }
703
704 fn build_window_clause(
706 &mut self,
707 windows: &[(String, WindowSpec)],
708 ) -> Result<(), crate::error::DbError> {
709 self.append(" WINDOW ");
710 for (index, (name, spec)) in windows.iter().enumerate() {
711 if index > 0 {
712 self.append(", ");
713 }
714 self.append(&super::identifier::quote_identifier(name)?);
715 self.append(" AS (");
716 self.append(&spec.render_body()?);
717 self.append(")");
718 }
719 Ok(())
720 }
721
722 fn build_where(&mut self, conditions: &[Condition]) -> Result<(), crate::error::DbError> {
734 if conditions.is_empty() {
735 return Ok(());
736 }
737
738 self.append(" WHERE ");
739
740 if conditions.len() == 1 {
741 self.append_condition(conditions[0].clone())?;
742 } else {
743 self.sql.push('(');
747 for (i, cond) in conditions.iter().enumerate() {
748 if i > 0 {
749 self.sql.push_str(" AND ");
750 }
751 self.append_condition(cond.clone())?;
752 }
753 self.sql.push(')');
754 }
755
756 Ok(())
757 }
758
759 fn build_joins(&mut self, joins: &[JoinSpec<'_>]) -> Result<(), crate::error::DbError> {
761 for join in joins {
762 let join_keyword = match join.kind {
763 JoinKind::Inner => " INNER JOIN ",
764 JoinKind::Left => " LEFT JOIN ",
765 JoinKind::Right => " RIGHT JOIN ",
766 JoinKind::Full => " FULL JOIN ",
767 JoinKind::Cross => " CROSS JOIN ",
768 JoinKind::Natural => " NATURAL JOIN ",
769 JoinKind::LeftLateral => " LEFT JOIN LATERAL ",
770 };
771 self.append(join_keyword);
772
773 match &join.target {
774 JoinTarget::Table(table) => {
775 self.append(&super::identifier::quote_table(table)?);
776 }
777 JoinTarget::Lateral(query, alias) => {
778 self.append("(");
779 self.build_compound_select(query)?;
780 self.append(") AS ");
781 self.append(&super::identifier::quote_identifier(alias)?);
782 }
783 }
784
785 match &join.condition {
786 JoinCondition::On(expression) => {
787 self.append(" ON ");
788 self.append(expression);
789 }
790 JoinCondition::Using(columns) => {
791 let quoted: Result<Vec<String>, crate::error::DbError> = columns
792 .iter()
793 .map(|column| super::identifier::quote_identifier(column))
794 .collect();
795 self.append(" USING (");
796 self.append("ed?.join(", "));
797 self.append(")");
798 }
799 JoinCondition::None => {}
800 }
801 }
802
803 Ok(())
804 }
805
806 fn build_order_by(&mut self, orders: &[OrderClause]) {
814 if orders.is_empty() {
815 return;
816 }
817
818 self.append(" ORDER BY ");
819
820 for (i, order) in orders.iter().enumerate() {
822 if i > 0 {
823 self.sql.push_str(", ");
824 }
825 self.sql.push_str(&order.field);
826 self.sql.push(' ');
827 self.sql.push_str(if order.asc { "ASC" } else { "DESC" });
828 }
829 }
830
831 fn build_group_by(
833 &mut self,
834 groups: &[String],
835 constructs: &[GroupingConstruct],
836 ) -> Result<(), crate::error::DbError> {
837 if groups.is_empty() && constructs.is_empty() {
838 return Ok(());
839 }
840
841 self.append(" GROUP BY ");
842 let mut first = true;
843 for group in groups {
844 if !first {
845 self.sql.push_str(", ");
846 }
847 first = false;
848 self.sql.push_str(group);
849 }
850 for construct in constructs {
851 if !first {
852 self.sql.push_str(", ");
853 }
854 first = false;
855 match construct {
856 GroupingConstruct::GroupingSets(sets) => {
857 if sets.is_empty() {
858 return Err(crate::error::DbError::InvalidArgument(
859 "grouping_sets 不能为空".to_string(),
860 ));
861 }
862 let rendered: Vec<String> = sets
863 .iter()
864 .map(|set| {
865 let fields: Vec<&str> =
866 set.iter().map(crate::FieldRef::postgres_quoted).collect();
867 format!("({})", fields.join(", "))
868 })
869 .collect();
870 self.sql
871 .push_str(&format!("GROUPING SETS ({})", rendered.join(", ")));
872 }
873 GroupingConstruct::Rollup(fields) => {
874 if fields.is_empty() {
875 return Err(crate::error::DbError::InvalidArgument(
876 "rollup 的字段列表不能为空".to_string(),
877 ));
878 }
879 let rendered: Vec<&str> = fields
880 .iter()
881 .map(crate::FieldRef::postgres_quoted)
882 .collect();
883 self.sql
884 .push_str(&format!("ROLLUP ({})", rendered.join(", ")));
885 }
886 GroupingConstruct::Cube(fields) => {
887 if fields.is_empty() {
888 return Err(crate::error::DbError::InvalidArgument(
889 "cube 的字段列表不能为空".to_string(),
890 ));
891 }
892 let rendered: Vec<&str> = fields
893 .iter()
894 .map(crate::FieldRef::postgres_quoted)
895 .collect();
896 self.sql
897 .push_str(&format!("CUBE ({})", rendered.join(", ")));
898 }
899 }
900 }
901 Ok(())
902 }
903
904 fn build_having(
906 &mut self,
907 conditions: &[Condition],
908 exprs: &[HavingExpr],
909 ) -> Result<(), crate::error::DbError> {
910 if conditions.is_empty() && exprs.is_empty() {
911 return Ok(());
912 }
913 self.append(" HAVING ");
914 let mut first = true;
915 for condition in conditions {
916 if !first {
917 self.sql.push_str(" AND ");
918 }
919 first = false;
920 self.append_condition(condition.clone())?;
921 }
922 for expr in exprs {
923 if !first {
924 self.sql.push_str(" AND ");
925 }
926 first = false;
927 self.sql
929 .push_str(&expr.expression.postgres_sql_without_alias());
930 self.sql.push(' ');
931 self.sql.push_str(comparison_operator_sql(expr.operator));
932 self.sql.push(' ');
933 match &expr.value {
934 SqlValue::Null => self.sql.push_str("NULL"),
935 value => {
936 let placeholder = push_placeholder(&mut self.params, value.clone());
937 self.sql.push_str(&placeholder);
938 }
939 }
940 }
941 Ok(())
942 }
943
944 pub(crate) fn build_insert(
960 &mut self,
961 table: &str,
962 data: &serde_json::Value,
963 field_types: &HashMap<String, FieldType>,
964 set_exprs: &[(String, crate::SqlExpr)],
965 ) -> Result<(), crate::error::DbError> {
966 self.clear();
968
969 let obj = data.as_object().ok_or_else(|| {
971 crate::error::DbError::SerializationError("插入数据必须是 JSON 对象".to_string())
972 })?;
973
974 if obj.is_empty() && set_exprs.is_empty() {
975 return Err(crate::error::DbError::SerializationError(
976 "插入数据不能为空".to_string(),
977 ));
978 }
979
980 let mut fields = Vec::with_capacity(obj.len());
982 let mut placeholders = Vec::with_capacity(obj.len());
983
984 for (key, value) in obj.iter() {
985 fields.push(super::identifier::quote_identifier(key)?);
986
987 let sql_value = self.json_value_to_sql_value(value, field_types.get(key))?;
989 match sql_value {
991 SqlValue::Null => placeholders.push("NULL".to_string()),
992 v => placeholders.push(push_typed_placeholder(
993 &mut self.params,
994 v,
995 field_types.get(key),
996 )),
997 }
998 }
999
1000 for (key, expression) in set_exprs {
1002 fields.push(super::identifier::quote_identifier(key)?);
1003 let placeholder = self.params.len() + 1;
1004 let rendered = expression.postgres_render(placeholder);
1005 for param in rendered.params {
1006 self.params.push(param.into());
1007 }
1008 placeholders.push(rendered.sql);
1009 }
1010
1011 self.append("INSERT INTO ");
1013 self.append(&super::identifier::quote_table(table)?);
1014 self.append(" (");
1015 self.append(&fields.join(", "));
1016 self.append(") VALUES (");
1017 self.append(&placeholders.join(", "));
1018 self.append(")");
1019
1020 Ok(())
1021 }
1022 pub(crate) fn build_insert_batch(
1027 &mut self,
1028 table: &str,
1029 data_list: &[serde_json::Value],
1030 field_types: &HashMap<String, FieldType>,
1031 ) -> Result<(), crate::error::DbError> {
1032 self.clear();
1033
1034 if data_list.is_empty() {
1035 return Err(crate::error::DbError::SerializationError(
1036 "批量插入数据不能为空".to_string(),
1037 ));
1038 }
1039
1040 let first_obj = data_list[0].as_object().ok_or_else(|| {
1041 crate::error::DbError::SerializationError("插入数据必须是 JSON 对象".to_string())
1042 })?;
1043
1044 if first_obj.is_empty() {
1045 return Err(crate::error::DbError::SerializationError(
1046 "插入数据不能为空".to_string(),
1047 ));
1048 }
1049
1050 let fields: Vec<String> = first_obj.keys().cloned().collect();
1051
1052 for (idx, data) in data_list.iter().enumerate().skip(1) {
1054 let obj = data.as_object().ok_or_else(|| {
1055 crate::error::DbError::SerializationError("插入数据必须是 JSON 对象".to_string())
1056 })?;
1057 if obj.len() != fields.len() || !fields.iter().all(|f| obj.contains_key(f)) {
1058 return Err(crate::error::DbError::InvalidArgument(format!(
1059 "批量插入第 {idx} 条记录的列集与首条不一致"
1060 )));
1061 }
1062 }
1063
1064 let quoted_fields: Result<Vec<String>, crate::error::DbError> = fields
1066 .iter()
1067 .map(|f| super::identifier::quote_identifier(f))
1068 .collect();
1069 let quoted_fields = quoted_fields?;
1070 self.append("INSERT INTO ");
1071 self.append(&super::identifier::quote_table(table)?);
1072 self.append(" (");
1073 self.append("ed_fields.join(", "));
1074 self.append(") VALUES ");
1075 for (record_idx, data) in data_list.iter().enumerate() {
1076 if record_idx > 0 {
1077 self.sql.push_str(", ");
1078 }
1079
1080 let obj = data.as_object().ok_or_else(|| {
1081 crate::error::DbError::SerializationError("插入数据必须是 JSON 对象".to_string())
1082 })?;
1083
1084 self.sql.push('(');
1085 for (field_idx, field) in fields.iter().enumerate() {
1086 if field_idx > 0 {
1087 self.sql.push_str(", ");
1088 }
1089 let value = obj.get(field).unwrap_or(&serde_json::Value::Null);
1090 let sql_value = self.json_value_to_sql_value(value, field_types.get(field))?;
1091 match sql_value {
1092 SqlValue::Null => self.sql.push_str("NULL"),
1094 v => {
1095 let ph =
1096 push_typed_placeholder(&mut self.params, v, field_types.get(field));
1097 self.sql.push_str(&ph);
1098 }
1099 }
1100 }
1101 self.sql.push(')');
1102 }
1103 Ok(())
1104 }
1105 pub(crate) fn build_update(
1110 &mut self,
1111 table: &str,
1112 data: &serde_json::Value,
1113 field_types: &HashMap<String, FieldType>,
1114 conditions: &[Condition],
1115 set_exprs: &[(String, crate::SqlExpr)],
1116 ) -> Result<(), crate::error::DbError> {
1117 self.clear();
1118
1119 if conditions.is_empty() {
1120 return Err(crate::error::DbError::MissingWhereClause);
1121 }
1122
1123 let obj = data.as_object().ok_or_else(|| {
1124 crate::error::DbError::SerializationError("更新数据必须是 JSON 对象".to_string())
1125 })?;
1126
1127 if obj.is_empty() && set_exprs.is_empty() {
1128 return Err(crate::error::DbError::SerializationError(
1129 "更新数据不能为空".to_string(),
1130 ));
1131 }
1132
1133 self.append("UPDATE ");
1134 self.append(&super::identifier::quote_table(table)?);
1135 self.append(" SET ");
1136 self.build_assignment_list(obj, field_types, set_exprs)?;
1137
1138 self.build_where(conditions)?;
1140
1141 Ok(())
1142 }
1143
1144 fn build_assignment_list(
1149 &mut self,
1150 obj: &serde_json::Map<String, serde_json::Value>,
1151 field_types: &HashMap<String, FieldType>,
1152 set_exprs: &[(String, crate::SqlExpr)],
1153 ) -> Result<(), crate::error::DbError> {
1154 let mut first = true;
1155 for (key, value) in obj.iter() {
1156 if !first {
1157 self.sql.push_str(", ");
1158 }
1159 first = false;
1160 let sql_value = self.json_value_to_sql_value(value, field_types.get(key))?;
1161 self.sql
1162 .push_str(&super::identifier::quote_identifier(key)?);
1163 self.sql.push_str(" = ");
1164 match sql_value {
1165 SqlValue::Null => self.sql.push_str("NULL"),
1166 v => {
1167 let ph = push_typed_placeholder(&mut self.params, v, field_types.get(key));
1168 self.sql.push_str(&ph);
1169 }
1170 }
1171 }
1172
1173 for (key, expression) in set_exprs {
1175 if !first {
1176 self.sql.push_str(", ");
1177 }
1178 first = false;
1179 self.sql
1180 .push_str(&super::identifier::quote_identifier(key)?);
1181 self.sql.push_str(" = ");
1182 let placeholder = self.params.len() + 1;
1183 let rendered = expression.postgres_render(placeholder);
1184 for param in rendered.params {
1185 self.params.push(param.into());
1186 }
1187 self.sql.push_str(&rendered.sql);
1188 }
1189 Ok(())
1190 }
1191
1192 pub(crate) fn build_arithmetic_update(
1194 &mut self,
1195 table: &str,
1196 field: &str,
1197 operator: ArithmeticOperator,
1198 amount: i64,
1199 conditions: &[Condition],
1200 ) -> Result<(), crate::error::DbError> {
1201 self.clear();
1202 if conditions.is_empty() {
1203 return Err(crate::error::DbError::MissingWhereClause);
1204 }
1205 let table = super::identifier::quote_table(table)?;
1206 let field = super::identifier::quote_identifier(field)?;
1207 self.sql.push_str("UPDATE ");
1208 self.sql.push_str(&table);
1209 self.sql.push_str(" SET ");
1210 self.sql.push_str(&field);
1211 self.sql.push_str(" = ");
1212 self.sql.push_str(&field);
1213 self.sql.push(' ');
1214 self.sql.push_str(operator.as_sql());
1215 self.sql.push(' ');
1216 let placeholder = push_placeholder(&mut self.params, SqlValue::Int(amount));
1217 self.sql.push_str(&placeholder);
1218 self.build_where(conditions)
1219 }
1220
1221 pub(crate) fn build_delete(
1223 &mut self,
1224 table: &str,
1225 conditions: &[Condition],
1226 ) -> Result<(), crate::error::DbError> {
1227 self.clear();
1228
1229 if conditions.is_empty() {
1230 return Err(crate::error::DbError::MissingWhereClause);
1231 }
1232
1233 self.append("DELETE FROM ");
1234 self.append(&super::identifier::quote_table(table)?);
1235 self.build_where(conditions)?;
1236
1237 Ok(())
1238 }
1239
1240 pub(crate) fn build_insert_from_select(
1242 &mut self,
1243 table: &str,
1244 columns: &[&crate::FieldRef],
1245 source: &QueryBuilder<'_>,
1246 ) -> Result<(), crate::error::DbError> {
1247 self.clear();
1248 self.append("INSERT INTO ");
1249 self.append(&super::identifier::quote_table(table)?);
1250 if !columns.is_empty() {
1251 let quoted: Result<Vec<String>, crate::error::DbError> = columns
1252 .iter()
1253 .map(|column| super::identifier::quote_identifier(column.as_str()))
1254 .collect();
1255 self.append(" (");
1256 self.append("ed?.join(", "));
1257 self.append(")");
1258 }
1259 self.append(" ");
1260 self.build_compound_select(source)?;
1261 Ok(())
1262 }
1263
1264 #[allow(clippy::too_many_arguments)]
1266 pub(crate) fn build_update_from(
1267 &mut self,
1268 table: &str,
1269 data: &serde_json::Value,
1270 field_types: &HashMap<String, FieldType>,
1271 set_exprs: &[(String, crate::SqlExpr)],
1272 conditions: &[Condition],
1273 from_table: &crate::TableRef,
1274 left: &crate::FieldRef,
1275 right: &crate::FieldRef,
1276 ) -> Result<(), crate::error::DbError> {
1277 self.clear();
1278 if conditions.is_empty() {
1279 return Err(crate::error::DbError::MissingWhereClause);
1280 }
1281 let obj = data.as_object().ok_or_else(|| {
1282 crate::error::DbError::SerializationError("更新数据必须是 JSON 对象".to_string())
1283 })?;
1284 if obj.is_empty() && set_exprs.is_empty() {
1285 return Err(crate::error::DbError::SerializationError(
1286 "更新数据不能为空".to_string(),
1287 ));
1288 }
1289
1290 self.append("UPDATE ");
1291 self.append(&super::identifier::quote_table(table)?);
1292 self.append(" SET ");
1293 self.build_assignment_list(obj, field_types, set_exprs)?;
1294 self.append(" FROM ");
1295 self.append(&super::identifier::quote_table(from_table.as_str())?);
1296
1297 let mut all_conditions = conditions.to_vec();
1299 all_conditions.push(join_condition(left, right));
1300 self.build_where(&all_conditions)?;
1301 Ok(())
1302 }
1303
1304 pub(crate) fn build_delete_using(
1306 &mut self,
1307 table: &str,
1308 conditions: &[Condition],
1309 using_table: &crate::TableRef,
1310 left: &crate::FieldRef,
1311 right: &crate::FieldRef,
1312 ) -> Result<(), crate::error::DbError> {
1313 self.clear();
1314 if conditions.is_empty() {
1315 return Err(crate::error::DbError::MissingWhereClause);
1316 }
1317
1318 self.append("DELETE FROM ");
1319 self.append(&super::identifier::quote_table(table)?);
1320 self.append(" USING ");
1321 self.append(&super::identifier::quote_table(using_table.as_str())?);
1322
1323 let mut all_conditions = conditions.to_vec();
1324 all_conditions.push(join_condition(left, right));
1325 self.build_where(&all_conditions)?;
1326 Ok(())
1327 }
1328
1329 pub(crate) fn build_merge(
1333 &mut self,
1334 table: &str,
1335 source: &crate::TableRef,
1336 target_key: &crate::FieldRef,
1337 source_key: &crate::FieldRef,
1338 columns: &[&crate::FieldRef],
1339 ) -> Result<(), crate::error::DbError> {
1340 self.build_merge_with_options(
1341 table,
1342 source,
1343 target_key,
1344 source_key,
1345 columns,
1346 &MergeOptions::default(),
1347 )
1348 }
1349
1350 pub(crate) fn build_merge_with_options(
1353 &mut self,
1354 table: &str,
1355 source: &crate::TableRef,
1356 target_key: &crate::FieldRef,
1357 source_key: &crate::FieldRef,
1358 columns: &[&crate::FieldRef],
1359 options: &MergeOptions,
1360 ) -> Result<(), crate::error::DbError> {
1361 self.clear();
1362 if columns.is_empty() {
1363 return Err(crate::error::DbError::InvalidArgument(
1364 "merge 的列列表不能为空".to_string(),
1365 ));
1366 }
1367
1368 let target_qualifier = table.rsplit('.').next().unwrap_or(table);
1369 let source_qualifier = source
1370 .as_str()
1371 .rsplit('.')
1372 .next()
1373 .unwrap_or(source.as_str());
1374
1375 self.append("MERGE INTO ");
1376 self.append(&super::identifier::quote_table(table)?);
1377 self.append(" USING ");
1378 self.append(&super::identifier::quote_table(source.as_str())?);
1379 self.append(" ON ");
1380 self.append(&qualify_merge_field(target_key, target_qualifier)?);
1381 self.append(" = ");
1382 self.append(&qualify_merge_field(source_key, source_qualifier)?);
1383
1384 self.append(" WHEN MATCHED");
1385 if let Some(condition) = &options.matched_condition {
1386 self.append(" AND ");
1387 self.append_condition(condition.clone())?;
1388 }
1389 self.append(" THEN UPDATE SET ");
1390 for (index, column) in columns.iter().enumerate() {
1391 if index > 0 {
1392 self.sql.push_str(", ");
1393 }
1394 let quoted = super::identifier::quote_identifier(column.as_str())?;
1395 self.sql.push_str("ed);
1396 self.sql.push_str(" = ");
1397 self.sql
1398 .push_str(&qualify_merge_field(column, source_qualifier)?);
1399 }
1400
1401 self.append(" WHEN NOT MATCHED THEN INSERT (");
1402 let quoted: Result<Vec<String>, crate::error::DbError> = columns
1403 .iter()
1404 .map(|column| super::identifier::quote_identifier(column.as_str()))
1405 .collect();
1406 self.append("ed?.join(", "));
1407 self.append(") VALUES (");
1408 let values: Result<Vec<String>, crate::error::DbError> = columns
1409 .iter()
1410 .map(|column| qualify_merge_field(column, source_qualifier))
1411 .collect();
1412 self.append(&values?.join(", "));
1413 self.append(")");
1414
1415 match &options.not_matched_by_source {
1416 None => {}
1417 Some(MergeNotMatchedBySource::Delete) => {
1418 self.append(" WHEN NOT MATCHED BY SOURCE THEN DELETE");
1419 }
1420 Some(MergeNotMatchedBySource::Update(assignments)) => {
1421 if assignments.is_empty() {
1422 return Err(crate::error::DbError::InvalidArgument(
1423 "WHEN NOT MATCHED BY SOURCE 的 UPDATE 赋值列表不能为空".to_string(),
1424 ));
1425 }
1426 self.append(" WHEN NOT MATCHED BY SOURCE THEN UPDATE SET ");
1427 for (index, (column, expression)) in assignments.iter().enumerate() {
1428 if index > 0 {
1429 self.sql.push_str(", ");
1430 }
1431 self.append(&super::identifier::quote_identifier(column.as_str())?);
1433 self.append(" = ");
1434 let rendered = expression.postgres_render(self.params.len() + 1);
1435 for param in rendered.params {
1436 self.params.push(param.into());
1437 }
1438 self.append(&rendered.sql);
1439 }
1440 }
1441 }
1442 Ok(())
1443 }
1444
1445 pub(crate) fn build_update_batch(
1452 &mut self,
1453 table: &str,
1454 records: &[serde_json::Value],
1455 id_field: &str,
1456 field_types: &std::collections::HashMap<String, FieldType>,
1457 ) -> Result<(), crate::error::DbError> {
1458 self.clear();
1459
1460 if records.is_empty() {
1461 return Err(crate::error::DbError::SerializationError(
1462 "批量更新数据不能为空".to_string(),
1463 ));
1464 }
1465
1466 let first = records[0].as_object().ok_or_else(|| {
1467 crate::error::DbError::SerializationError("更新数据必须是 JSON 对象".to_string())
1468 })?;
1469
1470 let update_fields: Vec<String> = first
1472 .keys()
1473 .filter(|k| k.as_str() != id_field)
1474 .cloned()
1475 .collect();
1476
1477 if update_fields.is_empty() {
1478 return Err(crate::error::DbError::SerializationError(
1479 "没有可更新的字段".to_string(),
1480 ));
1481 }
1482
1483 for (idx, record) in records.iter().enumerate() {
1485 let obj = record.as_object().ok_or_else(|| {
1486 crate::error::DbError::SerializationError("更新数据必须是 JSON 对象".to_string())
1487 })?;
1488 if !obj.contains_key(id_field) {
1489 return Err(crate::error::DbError::InvalidArgument(format!(
1490 "批量更新第 {idx} 条记录缺少主键字段 {id_field}"
1491 )));
1492 }
1493 let non_id_len = obj.keys().filter(|k| k.as_str() != id_field).count();
1494 if non_id_len != update_fields.len()
1495 || !update_fields.iter().all(|f| obj.contains_key(f))
1496 {
1497 return Err(crate::error::DbError::InvalidArgument(format!(
1498 "批量更新第 {idx} 条记录的列集与首条不一致"
1499 )));
1500 }
1501 }
1502
1503 let quoted_id = super::identifier::quote_identifier(id_field)?;
1505 self.sql.push_str("UPDATE ");
1506 self.sql.push_str(&super::identifier::quote_table(table)?);
1507 self.sql.push_str(" SET ");
1508
1509 for (field_idx, field) in update_fields.iter().enumerate() {
1511 if field_idx > 0 {
1512 self.sql.push_str(", ");
1513 }
1514 self.sql
1515 .push_str(&super::identifier::quote_identifier(field)?);
1516 self.sql.push_str(" = CASE ");
1517
1518 for record in records {
1519 let id_val = record.get(id_field).unwrap_or(&serde_json::Value::Null);
1520 let field_val = record
1521 .get(field.as_str())
1522 .unwrap_or(&serde_json::Value::Null);
1523
1524 let id_sql_val = self.json_value_to_sql_value(id_val, field_types.get(id_field))?;
1525 let field_sql_val =
1526 self.json_value_to_sql_value(field_val, field_types.get(field.as_str()))?;
1527
1528 self.sql.push_str("WHEN ");
1530 self.sql.push_str("ed_id);
1531 self.sql.push('=');
1532 match id_sql_val {
1533 SqlValue::Null => self.sql.push_str("NULL"),
1534 v => {
1535 let ph =
1536 push_typed_placeholder(&mut self.params, v, field_types.get(id_field));
1537 self.sql.push_str(&ph);
1538 }
1539 }
1540 self.sql.push_str(" THEN ");
1541 match field_sql_val {
1542 SqlValue::Null => self.sql.push_str("NULL"),
1543 v => {
1544 let ph = push_typed_placeholder(
1545 &mut self.params,
1546 v,
1547 field_types.get(field.as_str()),
1548 );
1549 self.sql.push_str(&ph);
1550 }
1551 }
1552 self.sql.push(' ');
1553 }
1554
1555 self.sql.push_str("END");
1556 }
1557
1558 self.sql.push_str(" WHERE ");
1560 self.sql.push_str("ed_id);
1561 self.sql.push_str(" IN (");
1562
1563 for (idx, record) in records.iter().enumerate() {
1564 if idx > 0 {
1565 self.sql.push(',');
1566 }
1567 let id_val = record.get(id_field).unwrap_or(&serde_json::Value::Null);
1568 let id_sql_val = self.json_value_to_sql_value(id_val, field_types.get(id_field))?;
1569 match id_sql_val {
1570 SqlValue::Null => self.sql.push_str("NULL"),
1571 v => {
1572 let ph = push_typed_placeholder(&mut self.params, v, field_types.get(id_field));
1573 self.sql.push_str(&ph);
1574 }
1575 }
1576 }
1577
1578 self.sql.push(')');
1579
1580 Ok(())
1581 }
1582 #[allow(clippy::too_many_arguments)]
1591 pub(crate) fn build_upsert(
1592 &mut self,
1593 table: &str,
1594 data: &serde_json::Value,
1595 field_types: &HashMap<String, FieldType>,
1596 conflict: &ConflictTarget,
1597 conflict_predicate: Option<&str>,
1598 do_nothing: bool,
1599 update_columns: Option<&[String]>,
1600 update_where: &[Condition],
1601 ) -> Result<(), crate::error::DbError> {
1602 self.clear();
1603
1604 let obj = data.as_object().ok_or_else(|| {
1605 crate::error::DbError::SerializationError("插入数据必须是 JSON 对象".to_string())
1606 })?;
1607
1608 if obj.is_empty() {
1609 return Err(crate::error::DbError::SerializationError(
1610 "插入数据不能为空".to_string(),
1611 ));
1612 }
1613
1614 let fields: Vec<String> = obj.keys().cloned().collect();
1615 let quoted_fields: Result<Vec<String>, crate::error::DbError> = fields
1617 .iter()
1618 .map(|f| super::identifier::quote_identifier(f))
1619 .collect();
1620 let quoted_fields = quoted_fields?;
1621
1622 self.sql.push_str("INSERT INTO ");
1623 self.sql.push_str(&super::identifier::quote_table(table)?);
1624 self.sql.push_str(" (");
1625 self.sql.push_str("ed_fields.join(", "));
1626 self.sql.push_str(") VALUES (");
1627
1628 for (i, field) in fields.iter().enumerate() {
1629 if i > 0 {
1630 self.sql.push_str(", ");
1631 }
1632 let val = obj.get(field.as_str()).unwrap_or(&serde_json::Value::Null);
1633 let sql_value = self.json_value_to_sql_value(val, field_types.get(field.as_str()))?;
1634 match sql_value {
1635 SqlValue::Null => self.sql.push_str("NULL"),
1636 v => {
1637 let ph = push_typed_placeholder(
1638 &mut self.params,
1639 v,
1640 field_types.get(field.as_str()),
1641 );
1642 self.sql.push_str(&ph);
1643 }
1644 }
1645 }
1646 self.sql.push(')');
1647
1648 self.append_on_conflict(
1649 table,
1650 &fields,
1651 conflict,
1652 conflict_predicate,
1653 do_nothing,
1654 update_columns,
1655 update_where,
1656 )
1657 }
1658
1659 #[allow(clippy::too_many_arguments)]
1665 fn append_on_conflict(
1666 &mut self,
1667 table: &str,
1668 fields: &[String],
1669 conflict: &ConflictTarget,
1670 predicate: Option<&str>,
1671 do_nothing: bool,
1672 update_columns: Option<&[String]>,
1673 update_where: &[Condition],
1674 ) -> Result<(), crate::error::DbError> {
1675 if predicate.is_some()
1676 && !matches!(conflict, ConflictTarget::Columns(columns) if !columns.is_empty())
1677 {
1678 return Err(crate::error::DbError::InvalidArgument(
1679 "ON CONFLICT 谓词只能与显式冲突列列表一起使用".to_string(),
1680 ));
1681 }
1682 let quoted_conflict: Vec<String> = match conflict {
1684 ConflictTarget::Columns(columns) => columns
1685 .iter()
1686 .map(|column| super::identifier::quote_identifier(column))
1687 .collect::<Result<_, _>>()?,
1688 ConflictTarget::Unspecified | ConflictTarget::Constraint(_) => Vec::new(),
1689 };
1690 let has_column_target =
1691 matches!(conflict, ConflictTarget::Columns(columns) if !columns.is_empty());
1692 if !has_column_target && !matches!(conflict, ConflictTarget::Constraint(_)) && !do_nothing {
1693 return Err(crate::error::DbError::InvalidArgument(
1694 "upsert 必须显式指定冲突目标(on_conflict / on_conflict_constraint / \
1695 on_conflict_with_predicate);如只想忽略冲突请使用 do_nothing()"
1696 .to_string(),
1697 ));
1698 }
1699 match conflict {
1700 ConflictTarget::Unspecified => {
1701 self.sql.push_str(" ON CONFLICT");
1702 }
1703 ConflictTarget::Columns(_) if !has_column_target => {
1704 self.sql.push_str(" ON CONFLICT");
1705 }
1706 ConflictTarget::Columns(_) => {
1707 self.sql.push_str(" ON CONFLICT (");
1708 self.sql.push_str("ed_conflict.join(", "));
1709 self.sql.push(')');
1710 }
1711 ConflictTarget::Constraint(name) => {
1712 self.sql.push_str(" ON CONFLICT ON CONSTRAINT ");
1713 self.sql
1714 .push_str(&super::identifier::quote_identifier(name)?);
1715 }
1716 }
1717
1718 if let Some(predicate) = predicate {
1719 let predicate = validate_conflict_predicate(predicate)?;
1720 self.sql.push_str(" WHERE (");
1721 self.sql.push_str(predicate);
1722 self.sql.push(')');
1723 }
1724
1725 if do_nothing || matches!(conflict, ConflictTarget::Columns(columns) if columns.is_empty())
1726 {
1727 self.sql.push_str(" DO NOTHING");
1728 return Ok(());
1729 }
1730
1731 let selected: Vec<&String> = match update_columns {
1733 Some(columns) => {
1734 if columns.is_empty() {
1735 return Err(crate::error::DbError::InvalidArgument(
1736 "update_columns 不能为空;如不想更新任何列请使用 do_nothing()".to_string(),
1737 ));
1738 }
1739 for column in columns {
1740 if !fields.contains(column) {
1741 return Err(crate::error::DbError::InvalidArgument(format!(
1742 "update_columns 中的列 `{column}` 不在插入数据中"
1743 )));
1744 }
1745 }
1746 columns.iter().collect()
1747 }
1748 None => fields
1749 .iter()
1750 .filter(|field| match conflict {
1751 ConflictTarget::Columns(columns) => !columns.contains(field),
1752 ConflictTarget::Unspecified | ConflictTarget::Constraint(_) => {
1755 !field.eq_ignore_ascii_case("id")
1756 }
1757 })
1758 .collect(),
1759 };
1760
1761 if selected.is_empty() && matches!(conflict, ConflictTarget::Constraint(_)) {
1764 return Err(crate::error::DbError::InvalidArgument(
1765 "ON CONFLICT ON CONSTRAINT 无法推断可更新列;请显式 update_columns(),\
1766 或改用 on_conflict(columns) / do_nothing()"
1767 .to_string(),
1768 ));
1769 }
1770
1771 self.sql.push_str(" DO UPDATE SET ");
1772
1773 if selected.is_empty() {
1776 let fallback = match conflict {
1777 ConflictTarget::Columns(columns) => columns.first(),
1778 ConflictTarget::Unspecified | ConflictTarget::Constraint(_) => fields.first(),
1779 }
1780 .ok_or_else(|| crate::error::DbError::InvalidArgument("没有可更新的列".to_string()))?;
1781 let quoted = super::identifier::quote_identifier(fallback)?;
1782 self.sql.push_str("ed);
1783 self.sql.push_str(" = EXCLUDED.");
1784 self.sql.push_str("ed);
1785 } else {
1786 for (index, field) in selected.iter().enumerate() {
1787 if index > 0 {
1788 self.sql.push_str(", ");
1789 }
1790 let quoted = super::identifier::quote_identifier(field.as_str())?;
1791 self.sql.push_str("ed);
1792 self.sql.push_str(" = EXCLUDED.");
1793 self.sql.push_str("ed);
1794 }
1795 }
1796
1797 if !update_where.is_empty() {
1803 let qualifier = table.rsplit('.').next().unwrap_or(table);
1804 let qualified_conditions: Vec<Condition> = update_where
1805 .iter()
1806 .cloned()
1807 .map(|condition| qualify_condition_fields(condition, qualifier))
1808 .collect();
1809 self.build_where(&qualified_conditions)?;
1810 }
1811
1812 Ok(())
1813 }
1814
1815 #[allow(clippy::too_many_arguments)]
1820 pub(crate) fn build_upsert_batch(
1821 &mut self,
1822 table: &str,
1823 data_list: &[serde_json::Value],
1824 field_types: &HashMap<String, FieldType>,
1825 conflict_columns: &[String],
1826 conflict_predicate: Option<&str>,
1827 do_nothing: bool,
1828 update_columns: Option<&[String]>,
1829 update_where: &[Condition],
1830 ) -> Result<(), crate::error::DbError> {
1831 self.build_insert_batch(table, data_list, field_types)?;
1832
1833 if conflict_columns.is_empty() && !do_nothing {
1834 return Err(crate::error::DbError::InvalidArgument(
1835 "批量 upsert 必须显式指定冲突列;如使用 id 请显式传入,\
1836 或调用 do_nothing() 走 ON CONFLICT DO NOTHING"
1837 .to_string(),
1838 ));
1839 }
1840
1841 let fields: Vec<String> = data_list[0]
1842 .as_object()
1843 .map(|object| object.keys().cloned().collect())
1844 .unwrap_or_default();
1845
1846 self.append_on_conflict(
1847 table,
1848 &fields,
1849 &ConflictTarget::Columns(conflict_columns.to_vec()),
1850 conflict_predicate,
1851 do_nothing,
1852 update_columns,
1853 update_where,
1854 )
1855 }
1856
1857 fn json_value_to_sql_value(
1862 &self,
1863 value: &serde_json::Value,
1864 field_type: Option<&FieldType>,
1865 ) -> Result<SqlValue, crate::error::DbError> {
1866 use serde_json::Value;
1867
1868 if let Some(ft) = field_type {
1869 match ft {
1870 FieldType::Json | FieldType::Jsonb => return Ok(SqlValue::Json(value.clone())),
1871 FieldType::Uuid => {
1872 if let Some(s) = value.as_str() {
1873 let uuid = uuid::Uuid::parse_str(s).map_err(|e| {
1874 crate::error::DbError::TypeConversionError(format!(
1875 "无法解析 UUID 字符串: {e}"
1876 ))
1877 })?;
1878 return Ok(SqlValue::Uuid(uuid));
1879 } else if value.is_null() {
1880 return Ok(SqlValue::Null);
1881 } else {
1882 return Err(crate::error::DbError::TypeConversionError(format!(
1883 "Uuid 字段期望字符串,实得 {value}"
1884 )));
1885 }
1886 }
1887 FieldType::TimestampTz => {
1888 if let Some(s) = value.as_str() {
1889 let parsed = chrono::DateTime::parse_from_rfc3339(s)
1890 .map(|dt| dt.with_timezone(&chrono::Utc))
1891 .or_else(|_| {
1892 chrono::NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S")
1893 .map(|dt| dt.and_utc())
1894 })
1895 .or_else(|_| {
1896 chrono::NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S%.f")
1897 .map(|dt| dt.and_utc())
1898 })
1899 .map_err(|e| {
1900 crate::error::DbError::TypeConversionError(format!(
1901 "无法解析 TIMESTAMPTZ 字符串: {e}"
1902 ))
1903 })?;
1904 return Ok(SqlValue::DateTimeTz(parsed));
1905 } else if value.is_null() {
1906 return Ok(SqlValue::Null);
1907 } else {
1908 return Err(crate::error::DbError::TypeConversionError(format!(
1909 "TimestampTz 字段期望字符串,实得 {value}"
1910 )));
1911 }
1912 }
1913 FieldType::Date => {
1914 if let Some(s) = value.as_str() {
1915 let date =
1916 chrono::NaiveDate::parse_from_str(s, "%Y-%m-%d").map_err(|e| {
1917 crate::error::DbError::TypeConversionError(format!(
1918 "无法解析 DATE 字符串: {e}"
1919 ))
1920 })?;
1921 return Ok(SqlValue::Date(date));
1922 } else if value.is_null() {
1923 return Ok(SqlValue::Null);
1924 } else {
1925 return Err(crate::error::DbError::TypeConversionError(format!(
1926 "Date 字段期望字符串,实得 {value}"
1927 )));
1928 }
1929 }
1930 FieldType::Time => {
1931 if let Some(s) = value.as_str() {
1932 let time = chrono::NaiveTime::parse_from_str(s, "%H:%M:%S")
1933 .or_else(|_| chrono::NaiveTime::parse_from_str(s, "%H:%M:%S%.f"))
1934 .or_else(|_| chrono::NaiveTime::parse_from_str(s, "%H:%M"))
1935 .map_err(|e| {
1936 crate::error::DbError::TypeConversionError(format!(
1937 "无法解析 TIME 字符串: {e}"
1938 ))
1939 })?;
1940 return Ok(SqlValue::Time(time));
1941 } else if value.is_null() {
1942 return Ok(SqlValue::Null);
1943 } else {
1944 return Err(crate::error::DbError::TypeConversionError(format!(
1945 "Time 字段期望字符串,实得 {value}"
1946 )));
1947 }
1948 }
1949 FieldType::DateTime => {
1950 if let Some(s) = value.as_str() {
1951 let dt = chrono::NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S")
1952 .map_err(|e| {
1953 crate::error::DbError::TypeConversionError(format!(
1954 "无法解析 DATETIME 字符串: {}",
1955 e
1956 ))
1957 })?;
1958 return Ok(SqlValue::DateTime(dt));
1959 } else if value.is_null() {
1960 return Ok(SqlValue::Null);
1961 } else {
1962 return Err(crate::error::DbError::TypeConversionError(format!(
1964 "DateTime 字段期望字符串,实得 {value}"
1965 )));
1966 }
1967 }
1968 FieldType::Timestamp => {
1969 if let Some(i) = value.as_i64() {
1970 return Ok(SqlValue::Timestamp(i));
1971 } else if value.is_null() {
1972 return Ok(SqlValue::Null);
1973 } else {
1974 return Err(crate::error::DbError::TypeConversionError(format!(
1975 "Timestamp 字段期望整数,实得 {value}"
1976 )));
1977 }
1978 }
1979 FieldType::Decimal => {
1980 if value.is_null() {
1982 return Ok(SqlValue::Null);
1983 }
1984 let parse_error = |detail: String| {
1985 crate::error::DbError::TypeConversionError(format!(
1986 "Decimal 字段期望数字或数字字符串,实得 {value}({detail})"
1987 ))
1988 };
1989 if let Some(s) = value.as_str() {
1990 return s
1991 .parse::<bigdecimal::BigDecimal>()
1992 .map(SqlValue::Decimal)
1993 .map_err(|error| parse_error(error.to_string()));
1994 }
1995 if let Some(i) = value.as_i64() {
1996 return Ok(SqlValue::Decimal(bigdecimal::BigDecimal::from(i)));
1997 }
1998 if let Some(u) = value.as_u64() {
1999 return Ok(SqlValue::Decimal(bigdecimal::BigDecimal::from(u)));
2000 }
2001 if let Some(f) = value.as_f64() {
2002 return f
2003 .to_string()
2004 .parse::<bigdecimal::BigDecimal>()
2005 .map(SqlValue::Decimal)
2006 .map_err(|error| parse_error(error.to_string()));
2007 }
2008 return Err(parse_error("无法识别的 JSON 数值".to_string()));
2009 }
2010 FieldType::Blob => {
2011 if let Some(s) = value.as_str() {
2012 use base64::Engine;
2013 if let Ok(bytes) = base64::engine::general_purpose::STANDARD.decode(s) {
2014 return Ok(SqlValue::Bytes(bytes));
2015 }
2016 return Ok(SqlValue::Bytes(s.as_bytes().to_vec()));
2017 } else if value.is_null() {
2018 return Ok(SqlValue::Null);
2019 } else {
2020 return Err(crate::error::DbError::TypeConversionError(format!(
2021 "Blob 字段期望字符串,实得 {value}"
2022 )));
2023 }
2024 }
2025 FieldType::Text => {
2026 if let Some(s) = value.as_str() {
2027 return Ok(SqlValue::String(s.to_string()));
2028 } else if value.is_null() {
2029 return Ok(SqlValue::Null);
2030 } else {
2031 return Err(crate::error::DbError::TypeConversionError(format!(
2032 "Text 字段期望字符串,实得 {value}"
2033 )));
2034 }
2035 }
2036 FieldType::Array(inner) => {
2037 if value.is_null() {
2038 return Ok(SqlValue::Null);
2039 }
2040 let elements = value.as_array().ok_or_else(|| {
2041 crate::error::DbError::TypeConversionError(format!(
2042 "数组字段期望 JSON 数组,实得 {value}"
2043 ))
2044 })?;
2045 let mut converted = Vec::with_capacity(elements.len());
2046 for element in elements {
2047 converted.push(self.json_value_to_sql_value(element, Some(inner))?);
2048 }
2049 let typeless = converted
2052 .iter()
2053 .all(|value| matches!(value, SqlValue::Null));
2054 if typeless {
2055 if let Some(element_type) = pg_array_element_type_name(inner) {
2056 return Ok(SqlValue::TypedArray {
2057 element_type,
2058 values: converted,
2059 });
2060 }
2061 }
2062 return Ok(SqlValue::Array(converted));
2063 }
2064 FieldType::Inet | FieldType::Cidr | FieldType::MacAddr | FieldType::MacAddr8 => {
2065 if value.is_null() {
2067 return Ok(SqlValue::Null);
2068 }
2069 let text = value.as_str().ok_or_else(|| {
2070 crate::error::DbError::TypeConversionError(format!(
2071 "网络字段期望字符串,实得 {value}"
2072 ))
2073 })?;
2074 return Ok(match ft {
2075 FieldType::Inet => SqlValue::Inet(text.to_string()),
2076 FieldType::Cidr => SqlValue::Cidr(text.to_string()),
2077 FieldType::MacAddr => SqlValue::MacAddr(text.to_string()),
2078 FieldType::MacAddr8 => SqlValue::MacAddr8(text.to_string()),
2079 _ => unreachable!("分支只覆盖四种网络类型"),
2080 });
2081 }
2082 FieldType::Bit | FieldType::Varbit => {
2083 return Err(crate::error::DbError::InvalidArgument(
2086 "Bit/Varbit 字段的写入需要位长信息,当前结构化 API 不支持;请使用原生 SQL 逃生舱"
2087 .to_string(),
2088 ));
2089 }
2090 FieldType::Range(_) | FieldType::Multirange(_) => {
2091 if value.is_null() {
2094 return Ok(SqlValue::Null);
2095 }
2096 if value.as_str().is_none() {
2097 return Err(crate::error::DbError::TypeConversionError(format!(
2098 "Range/Multirange 字段期望字符串,实得 {value}"
2099 )));
2100 }
2101 }
2102 FieldType::Standard | FieldType::Bool => {}
2103 }
2104 }
2105
2106 match value {
2107 Value::Null => Ok(SqlValue::Null),
2108 Value::Bool(b) => Ok(SqlValue::Bool(*b)),
2109 Value::Number(n) => {
2110 if let Some(i) = n.as_i64() {
2111 Ok(SqlValue::Int(i))
2112 } else if let Some(f) = n.as_f64() {
2113 Ok(SqlValue::Float(f))
2114 } else {
2115 Err(crate::error::DbError::TypeConversionError(
2116 "无法转换数字类型".to_string(),
2117 ))
2118 }
2119 }
2120 Value::String(s) => Ok(SqlValue::String(s.clone())),
2121 Value::Array(_) | Value::Object(_) => Ok(SqlValue::Json(value.clone())),
2122 }
2123 }
2124}
2125
2126#[derive(Debug, Clone, Copy, PartialEq, Eq)]
2132enum SetOperator {
2133 Union,
2134 UnionAll,
2135 Intersect,
2136 IntersectAll,
2137 Except,
2138 ExceptAll,
2139}
2140
2141impl SetOperator {
2142 const fn sql(self) -> &'static str {
2143 match self {
2144 Self::Union => " UNION ",
2145 Self::UnionAll => " UNION ALL ",
2146 Self::Intersect => " INTERSECT ",
2147 Self::IntersectAll => " INTERSECT ALL ",
2148 Self::Except => " EXCEPT ",
2149 Self::ExceptAll => " EXCEPT ALL ",
2150 }
2151 }
2152
2153 const fn is_union(self) -> bool {
2154 matches!(self, Self::Union | Self::UnionAll)
2155 }
2156}
2157
2158#[derive(Debug, Clone, Copy)]
2159pub(crate) enum ArithmeticOperator {
2160 Add,
2161 Subtract,
2162}
2163
2164struct Cte<'a> {
2166 name: String,
2168 recursive: bool,
2170 query: Box<QueryBuilder<'a>>,
2172}
2173
2174#[derive(Debug, Clone, Copy, PartialEq, Eq)]
2176enum JoinKind {
2177 Inner,
2178 Left,
2179 Right,
2180 Full,
2181 Cross,
2182 Natural,
2183 LeftLateral,
2184}
2185
2186enum JoinTarget<'a> {
2188 Table(String),
2190 Lateral(Box<QueryBuilder<'a>>, String),
2192}
2193
2194#[derive(Debug, Clone, PartialEq, Eq)]
2196enum JoinCondition {
2197 On(String),
2199 Using(Vec<String>),
2201 None,
2203}
2204
2205struct JoinSpec<'a> {
2207 kind: JoinKind,
2208 target: JoinTarget<'a>,
2209 condition: JoinCondition,
2210}
2211
2212#[derive(Debug, Clone, PartialEq, Eq)]
2214enum GroupingConstruct {
2215 GroupingSets(Vec<Vec<crate::FieldRef>>),
2217 Rollup(Vec<crate::FieldRef>),
2219 Cube(Vec<crate::FieldRef>),
2221}
2222
2223#[derive(Debug, Clone, Copy, PartialEq, Eq)]
2225pub enum FrameMode {
2226 Rows,
2228 Range,
2230 Groups,
2232}
2233
2234#[derive(Debug, Clone, Copy, PartialEq, Eq)]
2236pub enum FrameBound {
2237 UnboundedPreceding,
2239 Preceding(u64),
2241 CurrentRow,
2243 Following(u64),
2245 UnboundedFollowing,
2247}
2248
2249impl FrameBound {
2250 fn sql(self) -> String {
2251 match self {
2252 Self::UnboundedPreceding => "UNBOUNDED PRECEDING".to_string(),
2253 Self::Preceding(offset) => format!("{offset} PRECEDING"),
2254 Self::CurrentRow => "CURRENT ROW".to_string(),
2255 Self::Following(offset) => format!("{offset} FOLLOWING"),
2256 Self::UnboundedFollowing => "UNBOUNDED FOLLOWING".to_string(),
2257 }
2258 }
2259}
2260
2261#[derive(Debug, Clone, Copy, PartialEq, Eq)]
2263pub struct WindowFrame {
2264 mode: FrameMode,
2265 start: FrameBound,
2266 end: FrameBound,
2267}
2268
2269impl WindowFrame {
2270 pub fn rows_between(start: FrameBound, end: FrameBound) -> Self {
2272 Self {
2273 mode: FrameMode::Rows,
2274 start,
2275 end,
2276 }
2277 }
2278
2279 pub fn range_between(start: FrameBound, end: FrameBound) -> Self {
2281 Self {
2282 mode: FrameMode::Range,
2283 start,
2284 end,
2285 }
2286 }
2287
2288 pub fn groups_between(start: FrameBound, end: FrameBound) -> Self {
2293 Self {
2294 mode: FrameMode::Groups,
2295 start,
2296 end,
2297 }
2298 }
2299
2300 fn render(&self) -> Result<String, crate::error::DbError> {
2301 if matches!(self.start, FrameBound::UnboundedFollowing) {
2302 return Err(crate::error::DbError::InvalidArgument(
2303 "窗口帧的起始边界不能是 UNBOUNDED FOLLOWING".to_string(),
2304 ));
2305 }
2306 if matches!(self.end, FrameBound::UnboundedPreceding) {
2307 return Err(crate::error::DbError::InvalidArgument(
2308 "窗口帧的结束边界不能是 UNBOUNDED PRECEDING".to_string(),
2309 ));
2310 }
2311 let mode = match self.mode {
2312 FrameMode::Rows => "ROWS",
2313 FrameMode::Range => "RANGE",
2314 FrameMode::Groups => "GROUPS",
2315 };
2316 Ok(format!(
2317 "{mode} BETWEEN {} AND {}",
2318 self.start.sql(),
2319 self.end.sql()
2320 ))
2321 }
2322}
2323
2324#[derive(Debug, Clone, Default, PartialEq, Eq)]
2328pub struct WindowSpec {
2329 partition_by: Vec<crate::FieldRef>,
2330 order_by: Vec<(crate::FieldRef, crate::SortOrder)>,
2331 frame: Option<WindowFrame>,
2332 reference: Option<String>,
2334}
2335
2336impl WindowSpec {
2337 pub fn new() -> Self {
2339 Self::default()
2340 }
2341
2342 pub fn reference(name: &str) -> Self {
2346 Self {
2347 reference: Some(name.to_string()),
2348 ..Self::default()
2349 }
2350 }
2351
2352 pub fn partition_by(mut self, fields: &[&crate::FieldRef]) -> Self {
2354 self.partition_by
2355 .extend(fields.iter().map(|field| (*field).clone()));
2356 self
2357 }
2358
2359 pub fn order_by(mut self, field: &crate::FieldRef, order: crate::SortOrder) -> Self {
2361 self.order_by.push((field.clone(), order));
2362 self
2363 }
2364
2365 pub fn frame(mut self, frame: WindowFrame) -> Self {
2367 self.frame = Some(frame);
2368 self
2369 }
2370
2371 fn render_body(&self) -> Result<String, crate::error::DbError> {
2373 if matches!(self.frame.map(|frame| frame.mode), Some(FrameMode::Groups))
2375 && self.order_by.is_empty()
2376 {
2377 return Err(crate::error::DbError::InvalidArgument(
2378 "GROUPS 帧要求窗口定义包含 ORDER BY".to_string(),
2379 ));
2380 }
2381 let mut parts: Vec<String> = Vec::new();
2382 if !self.partition_by.is_empty() {
2383 let fields: Vec<&str> = self
2384 .partition_by
2385 .iter()
2386 .map(crate::FieldRef::postgres_quoted)
2387 .collect();
2388 parts.push(format!("PARTITION BY {}", fields.join(", ")));
2389 }
2390 if !self.order_by.is_empty() {
2391 let orders: Vec<String> = self
2392 .order_by
2393 .iter()
2394 .map(|(field, order)| {
2395 format!(
2396 "{} {}",
2397 field.postgres_quoted(),
2398 if order.is_ascending() { "ASC" } else { "DESC" }
2399 )
2400 })
2401 .collect();
2402 parts.push(format!("ORDER BY {}", orders.join(", ")));
2403 }
2404 if let Some(frame) = &self.frame {
2405 parts.push(frame.render()?);
2406 }
2407 Ok(parts.join(" "))
2408 }
2409
2410 fn render(&self) -> Result<String, crate::error::DbError> {
2411 if let Some(name) = &self.reference {
2412 if !self.partition_by.is_empty() || !self.order_by.is_empty() || self.frame.is_some() {
2413 return Err(crate::error::DbError::InvalidArgument(
2414 "引用命名窗口时不能再附带 PARTITION BY / ORDER BY / 帧".to_string(),
2415 ));
2416 }
2417 return Ok(format!(
2418 "OVER {}",
2419 super::identifier::quote_identifier(name)?
2420 ));
2421 }
2422 Ok(format!("OVER ({})", self.render_body()?))
2423 }
2424}
2425
2426#[derive(Debug, Clone, Copy, PartialEq, Eq)]
2428enum WindowFunctionKind {
2429 RowNumber,
2430 Rank,
2431 DenseRank,
2432 Sum,
2433 Avg,
2434 Count,
2435 Min,
2436 Max,
2437 Lag,
2438 Lead,
2439}
2440
2441#[derive(Debug, Clone)]
2446pub struct WindowFunction {
2447 kind: WindowFunctionKind,
2448 field: Option<crate::FieldRef>,
2449 filter: Option<Condition>,
2451}
2452
2453impl WindowFunction {
2454 fn rank_like(kind: WindowFunctionKind) -> Self {
2455 Self {
2456 kind,
2457 field: None,
2458 filter: None,
2459 }
2460 }
2461
2462 fn with_field(kind: WindowFunctionKind, field: &crate::FieldRef) -> Self {
2463 Self {
2464 kind,
2465 field: Some(field.clone()),
2466 filter: None,
2467 }
2468 }
2469
2470 pub fn row_number() -> Self {
2472 Self::rank_like(WindowFunctionKind::RowNumber)
2473 }
2474
2475 pub fn rank() -> Self {
2477 Self::rank_like(WindowFunctionKind::Rank)
2478 }
2479
2480 pub fn dense_rank() -> Self {
2482 Self::rank_like(WindowFunctionKind::DenseRank)
2483 }
2484
2485 pub fn sum(field: &crate::FieldRef) -> Self {
2487 Self::with_field(WindowFunctionKind::Sum, field)
2488 }
2489
2490 pub fn avg(field: &crate::FieldRef) -> Self {
2492 Self::with_field(WindowFunctionKind::Avg, field)
2493 }
2494
2495 pub fn count(field: &crate::FieldRef) -> Self {
2497 Self::with_field(WindowFunctionKind::Count, field)
2498 }
2499
2500 pub fn count_all() -> Self {
2502 Self::rank_like(WindowFunctionKind::Count)
2503 }
2504
2505 pub fn min(field: &crate::FieldRef) -> Self {
2507 Self::with_field(WindowFunctionKind::Min, field)
2508 }
2509
2510 pub fn max(field: &crate::FieldRef) -> Self {
2512 Self::with_field(WindowFunctionKind::Max, field)
2513 }
2514
2515 pub fn lag(field: &crate::FieldRef) -> Self {
2517 Self::with_field(WindowFunctionKind::Lag, field)
2518 }
2519
2520 pub fn lead(field: &crate::FieldRef) -> Self {
2522 Self::with_field(WindowFunctionKind::Lead, field)
2523 }
2524
2525 pub fn filter(mut self, condition: Condition) -> Self {
2531 self.filter = Some(condition);
2532 self
2533 }
2534
2535 fn render(&self, generator: &mut SqlGenerator) -> Result<String, crate::error::DbError> {
2537 let name = match self.kind {
2538 WindowFunctionKind::RowNumber => "ROW_NUMBER",
2539 WindowFunctionKind::Rank => "RANK",
2540 WindowFunctionKind::DenseRank => "DENSE_RANK",
2541 WindowFunctionKind::Sum => "SUM",
2542 WindowFunctionKind::Avg => "AVG",
2543 WindowFunctionKind::Count => "COUNT",
2544 WindowFunctionKind::Min => "MIN",
2545 WindowFunctionKind::Max => "MAX",
2546 WindowFunctionKind::Lag => "LAG",
2547 WindowFunctionKind::Lead => "LEAD",
2548 };
2549 let call = match &self.field {
2550 Some(field) => format!("{name}({})", field.postgres_quoted()),
2551 None if self.kind == WindowFunctionKind::Count => "COUNT(*)".to_string(),
2553 None => format!("{name}()"),
2554 };
2555 let Some(filter) = &self.filter else {
2556 return Ok(call);
2557 };
2558 if !matches!(
2559 self.kind,
2560 WindowFunctionKind::Sum
2561 | WindowFunctionKind::Avg
2562 | WindowFunctionKind::Count
2563 | WindowFunctionKind::Min
2564 | WindowFunctionKind::Max
2565 ) {
2566 return Err(crate::error::DbError::InvalidArgument(format!(
2567 "{name} 不是聚合函数,不能使用 FILTER (WHERE ...)"
2568 )));
2569 }
2570 let fragment = generator.render_condition_fragment(filter.clone())?;
2571 Ok(format!("{call} FILTER (WHERE {fragment})"))
2572 }
2573}
2574
2575struct WindowProjection {
2577 function: WindowFunction,
2578 spec: WindowSpec,
2579 alias: String,
2580}
2581
2582impl WindowProjection {
2583 fn render(&self, generator: &mut SqlGenerator) -> Result<String, crate::error::DbError> {
2584 let alias = super::identifier::quote_identifier(&self.alias)?;
2585 Ok(format!(
2586 "{} {} AS {alias}",
2587 self.function.render(generator)?,
2588 self.spec.render()?
2589 ))
2590 }
2591}
2592
2593struct HavingExpr {
2595 expression: crate::SelectExpr,
2596 operator: ComparisonOperator,
2597 value: SqlValue,
2598}
2599
2600fn comparison_operator_sql(operator: ComparisonOperator) -> &'static str {
2602 match operator {
2603 ComparisonOperator::Eq => "=",
2604 ComparisonOperator::Ne => "!=",
2605 ComparisonOperator::Gt => ">",
2606 ComparisonOperator::Lt => "<",
2607 ComparisonOperator::Gte => ">=",
2608 ComparisonOperator::Lte => "<=",
2609 }
2610}
2611
2612#[derive(Debug, Clone, PartialEq, Eq)]
2614pub(crate) enum ConflictTarget {
2615 Unspecified,
2617 Columns(Vec<String>),
2619 Constraint(String),
2621}
2622
2623#[derive(Debug, Clone, PartialEq, Eq)]
2625pub(crate) struct ReturningColumn {
2626 pub(crate) column: String,
2628 pub(crate) alias: Option<String>,
2630}
2631
2632#[derive(Debug, Clone, PartialEq, Eq)]
2634pub(crate) enum ReturningTarget {
2635 Column(String),
2637 Columns(Vec<ReturningColumn>),
2639 All,
2641}
2642
2643#[derive(Debug, Clone, PartialEq, Eq)]
2648#[non_exhaustive]
2649pub enum MergeNotMatchedBySource {
2650 Delete,
2652 Update(Vec<(crate::FieldRef, crate::SqlExpr)>),
2656}
2657
2658#[derive(Debug, Clone, Default)]
2664pub struct MergeOptions {
2665 matched_condition: Option<Condition>,
2667 not_matched_by_source: Option<MergeNotMatchedBySource>,
2669}
2670
2671impl MergeOptions {
2672 pub fn matched_condition(mut self, condition: Condition) -> Self {
2674 self.matched_condition = Some(condition);
2675 self
2676 }
2677
2678 pub fn not_matched_by_source(mut self, action: MergeNotMatchedBySource) -> Self {
2680 self.not_matched_by_source = Some(action);
2681 self
2682 }
2683}
2684
2685impl ArithmeticOperator {
2686 const fn as_sql(self) -> &'static str {
2687 match self {
2688 Self::Add => "+",
2689 Self::Subtract => "-",
2690 }
2691 }
2692}
2693
2694enum QueryExecutor<'a> {
2696 Pool(&'a PgPool),
2697 Transaction(&'a mut crate::postgres::Transaction),
2698}
2699
2700fn qualify_condition_fields(condition: Condition, qualifier: &str) -> Condition {
2704 fn qualify(field: String, qualifier: &str) -> String {
2705 if field.contains('.') {
2706 field
2707 } else {
2708 format!("{qualifier}.{field}")
2709 }
2710 }
2711
2712 match condition {
2713 Condition::Eq(field, value) => Condition::Eq(qualify(field, qualifier), value),
2714 Condition::Ne(field, value) => Condition::Ne(qualify(field, qualifier), value),
2715 Condition::Gt(field, value) => Condition::Gt(qualify(field, qualifier), value),
2716 Condition::Lt(field, value) => Condition::Lt(qualify(field, qualifier), value),
2717 Condition::Gte(field, value) => Condition::Gte(qualify(field, qualifier), value),
2718 Condition::Lte(field, value) => Condition::Lte(qualify(field, qualifier), value),
2719 Condition::In(field, values) => Condition::In(qualify(field, qualifier), values),
2720 Condition::NotIn(field, values) => Condition::NotIn(qualify(field, qualifier), values),
2721 Condition::Between(field, start, end) => {
2722 Condition::Between(qualify(field, qualifier), start, end)
2723 }
2724 Condition::Like(field, pattern) => Condition::Like(qualify(field, qualifier), pattern),
2725 Condition::ILike(field, pattern) => Condition::ILike(qualify(field, qualifier), pattern),
2726 Condition::NotILike(field, pattern) => {
2727 Condition::NotILike(qualify(field, qualifier), pattern)
2728 }
2729 Condition::RegexMatch(field, pattern) => {
2730 Condition::RegexMatch(qualify(field, qualifier), pattern)
2731 }
2732 Condition::RegexIMatch(field, pattern) => {
2733 Condition::RegexIMatch(qualify(field, qualifier), pattern)
2734 }
2735 Condition::NotRegexMatch(field, pattern) => {
2736 Condition::NotRegexMatch(qualify(field, qualifier), pattern)
2737 }
2738 Condition::NotRegexIMatch(field, pattern) => {
2739 Condition::NotRegexIMatch(qualify(field, qualifier), pattern)
2740 }
2741 Condition::IsDistinctFrom(field, value) => {
2742 Condition::IsDistinctFrom(qualify(field, qualifier), value)
2743 }
2744 Condition::IsNotDistinctFrom(field, value) => {
2745 Condition::IsNotDistinctFrom(qualify(field, qualifier), value)
2746 }
2747 Condition::IsNull(field) => Condition::IsNull(qualify(field, qualifier)),
2748 Condition::IsNotNull(field) => Condition::IsNotNull(qualify(field, qualifier)),
2749 Condition::ColumnComparison(left, operator, right) => {
2750 Condition::ColumnComparison(qualify(left, qualifier), operator, right)
2751 }
2752 Condition::FieldExpr(field, operator, expression) => {
2753 Condition::FieldExpr(qualify(field, qualifier), operator, expression)
2754 }
2755 Condition::Not(inner) => {
2756 Condition::Not(Box::new(qualify_condition_fields(*inner, qualifier)))
2757 }
2758 Condition::And(conditions) => Condition::And(
2759 conditions
2760 .into_iter()
2761 .map(|condition| qualify_condition_fields(condition, qualifier))
2762 .collect(),
2763 ),
2764 Condition::Or(conditions) => Condition::Or(
2765 conditions
2766 .into_iter()
2767 .map(|condition| qualify_condition_fields(condition, qualifier))
2768 .collect(),
2769 ),
2770 Condition::JsonContains(field, value) => {
2771 Condition::JsonContains(qualify(field, qualifier), value)
2772 }
2773 Condition::JsonContainedBy(field, value) => {
2774 Condition::JsonContainedBy(qualify(field, qualifier), value)
2775 }
2776 Condition::JsonHasKey(field, key) => Condition::JsonHasKey(qualify(field, qualifier), key),
2777 Condition::Any(field, value) => Condition::Any(qualify(field, qualifier), value),
2778 Condition::All(field, value) => Condition::All(qualify(field, qualifier), value),
2779 Condition::RangeContains(field, value) => {
2780 Condition::RangeContains(qualify(field, qualifier), value)
2781 }
2782 Condition::RangeOverlaps(field, value) => {
2783 Condition::RangeOverlaps(qualify(field, qualifier), value)
2784 }
2785 Condition::JsonPathExists(field, path) => {
2786 Condition::JsonPathExists(qualify(field, qualifier), path)
2787 }
2788 Condition::JsonPathMatch(field, path, vars) => {
2789 Condition::JsonPathMatch(qualify(field, qualifier), path, vars)
2790 }
2791 Condition::FullText(field, query) => Condition::FullText(qualify(field, qualifier), query),
2792 Condition::FullTextQuery(field, query) => {
2793 Condition::FullTextQuery(qualify(field, qualifier), query)
2794 }
2795 Condition::Exists(subquery) => Condition::Exists(subquery),
2797 Condition::NotExists(subquery) => Condition::NotExists(subquery),
2798 Condition::InSubquery(field, subquery) => Condition::InSubquery(field, subquery),
2799 }
2800}
2801
2802fn join_condition(left: &crate::FieldRef, right: &crate::FieldRef) -> Condition {
2804 Condition::ColumnComparison(
2805 left.as_str().to_string(),
2806 ComparisonOperator::Eq,
2807 right.as_str().to_string(),
2808 )
2809}
2810
2811fn qualify_merge_field(
2813 field: &crate::FieldRef,
2814 qualifier: &str,
2815) -> Result<String, crate::error::DbError> {
2816 if field.as_str().contains('.') {
2817 super::identifier::quote_qualified(field.as_str())
2818 } else {
2819 super::identifier::quote_qualified(&format!("{qualifier}.{}", field.as_str()))
2820 }
2821}
2822
2823struct IntegerPk(i64);
2829
2830const NEGATIVE_PK_DECODE_PREFIX: &str = "insert() 返回的主键为负数";
2836
2837fn integer_pk_from_i64(raw: i64) -> Result<IntegerPk, sqlx::error::BoxDynError> {
2844 if raw < 0 {
2845 return Err(format!("{NEGATIVE_PK_DECODE_PREFIX}: {raw}").into());
2846 }
2847 Ok(IntegerPk(raw))
2848}
2849
2850impl sqlx::Type<sqlx::Postgres> for IntegerPk {
2851 fn type_info() -> sqlx::postgres::PgTypeInfo {
2852 sqlx::postgres::PgTypeInfo::with_name("INT8")
2853 }
2854
2855 fn compatible(ty: &sqlx::postgres::PgTypeInfo) -> bool {
2856 matches!(ty.name(), "INT2" | "INT4" | "INT8")
2857 }
2858}
2859
2860impl<'r> sqlx::Decode<'r, sqlx::Postgres> for IntegerPk {
2861 fn decode(value: sqlx::postgres::PgValueRef<'r>) -> Result<Self, sqlx::error::BoxDynError> {
2862 let raw = match value.type_info().name() {
2863 "INT2" => i64::from(<i16 as sqlx::Decode<'r, sqlx::Postgres>>::decode(value)?),
2864 "INT4" => i64::from(<i32 as sqlx::Decode<'r, sqlx::Postgres>>::decode(value)?),
2865 "INT8" => <i64 as sqlx::Decode<'r, sqlx::Postgres>>::decode(value)?,
2866 other => {
2867 return Err(format!(
2868 "insert() 主键类型 {other} 不是整型;请使用 insert_returning::<T, R>() 按真实类型取回"
2869 )
2870 .into())
2871 }
2872 };
2873 integer_pk_from_i64(raw)
2874 }
2875}
2876
2877impl<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> for IntegerPk {
2878 fn from_row(row: &'r sqlx::postgres::PgRow) -> Result<Self, sqlx::Error> {
2879 sqlx::Row::try_get(row, 0)
2880 }
2881}
2882
2883fn map_insert_pk_decode_error(detail: String) -> crate::error::DbError {
2889 if let Some((_, value)) = detail.split_once(NEGATIVE_PK_DECODE_PREFIX) {
2890 return crate::error::DbError::InvalidArgument(format!(
2891 "{NEGATIVE_PK_DECODE_PREFIX}{value};insert() 的返回类型为 u64,不支持负数主键。\
2892 需要取回负值请使用 insert_returning::<T, R>() 按真实类型解码"
2893 ));
2894 }
2895 crate::error::DbError::InvalidArgument(format!(
2896 "insert() 仅支持整型主键:{detail}。非整型主键请使用 \
2897 insert_returning::<T, R>() 按列的真实类型取回"
2898 ))
2899}
2900
2901fn validate_conflict_predicate(predicate: &str) -> Result<&str, crate::error::DbError> {
2906 let trimmed = predicate.trim();
2907 if trimmed.is_empty() {
2908 return Err(crate::error::DbError::InvalidArgument(
2909 "ON CONFLICT 谓词不能为空".to_string(),
2910 ));
2911 }
2912 if trimmed.contains('\0')
2913 || trimmed.contains(';')
2914 || trimmed.contains("--")
2915 || trimmed.contains("/*")
2916 || trimmed.contains("*/")
2917 {
2918 return Err(crate::error::DbError::InvalidArgument(
2919 "ON CONFLICT 谓词不能包含分号、注释标记或 NUL".to_string(),
2920 ));
2921 }
2922 Ok(trimmed)
2923}
2924
2925fn comparison_operator(op: crate::CompareOp) -> Option<ComparisonOperator> {
2927 match op {
2928 crate::CompareOp::Eq => Some(ComparisonOperator::Eq),
2929 crate::CompareOp::Ne => Some(ComparisonOperator::Ne),
2930 crate::CompareOp::Gt => Some(ComparisonOperator::Gt),
2931 crate::CompareOp::Lt => Some(ComparisonOperator::Lt),
2932 crate::CompareOp::Gte => Some(ComparisonOperator::Gte),
2933 crate::CompareOp::Lte => Some(ComparisonOperator::Lte),
2934 _ => None,
2935 }
2936}
2937
2938fn comparison_condition(
2943 field: &str,
2944 op: crate::CompareOp,
2945 value: SqlValue,
2946) -> Result<Condition, crate::error::DbError> {
2947 let field = field.to_string();
2948 let pattern = |value: SqlValue| match value {
2949 SqlValue::String(pattern) => Ok(pattern),
2950 _ => Err(crate::error::DbError::UnsupportedOperator(
2951 "模式匹配操作符仅支持字符串模式值".to_string(),
2952 )),
2953 };
2954 match op {
2955 crate::CompareOp::Like => Ok(Condition::Like(field, pattern(value)?)),
2956 crate::CompareOp::ILike => Ok(Condition::ILike(field, pattern(value)?)),
2957 crate::CompareOp::NotILike => Ok(Condition::NotILike(field, pattern(value)?)),
2958 crate::CompareOp::RegexMatch => Ok(Condition::RegexMatch(field, pattern(value)?)),
2959 crate::CompareOp::RegexIMatch => Ok(Condition::RegexIMatch(field, pattern(value)?)),
2960 crate::CompareOp::NotRegexMatch => Ok(Condition::NotRegexMatch(field, pattern(value)?)),
2961 crate::CompareOp::NotRegexIMatch => Ok(Condition::NotRegexIMatch(field, pattern(value)?)),
2962 crate::CompareOp::IsDistinctFrom => Ok(Condition::IsDistinctFrom(field, value)),
2963 crate::CompareOp::IsNotDistinctFrom => Ok(Condition::IsNotDistinctFrom(field, value)),
2964 _ => {
2965 let operator = comparison_operator(op).ok_or_else(|| {
2966 crate::error::DbError::UnsupportedOperator("不支持的比较操作符".to_string())
2967 })?;
2968 Ok(match operator {
2969 ComparisonOperator::Eq => Condition::Eq(field, value),
2970 ComparisonOperator::Ne => Condition::Ne(field, value),
2971 ComparisonOperator::Gt => Condition::Gt(field, value),
2972 ComparisonOperator::Lt => Condition::Lt(field, value),
2973 ComparisonOperator::Gte => Condition::Gte(field, value),
2974 ComparisonOperator::Lte => Condition::Lte(field, value),
2975 })
2976 }
2977 }
2978}
2979
2980pub struct QueryBuilder<'a> {
2981 executor: QueryExecutor<'a>,
2982 table: String,
2983 fields: Vec<String>,
2984 projection_exprs: Vec<(crate::SqlExpr, Option<String>)>,
2986 window_exprs: Vec<WindowProjection>,
2988 named_windows: Vec<(String, WindowSpec)>,
2990 conditions: Vec<Condition>,
2991 joins: Vec<JoinSpec<'a>>,
2992 order_by: Vec<OrderClause>,
2993 group_by: Vec<String>,
2994 grouping_constructs: Vec<GroupingConstruct>,
2996 having_clause: Vec<Condition>,
2997 having_exprs: Vec<HavingExpr>,
2999 limit: Option<u64>,
3000 offset: Option<u64>,
3001 distinct: bool,
3002 distinct_on: Vec<String>,
3004 ctes: Vec<Cte<'a>>,
3006 set_ops: Vec<(SetOperator, Box<QueryBuilder<'a>>)>,
3008 field_types: HashMap<String, FieldType>,
3009 set_exprs: Vec<(String, crate::SqlExpr)>,
3011 conflict_target: ConflictTarget,
3017 conflict_predicate: Option<String>,
3019 do_nothing: bool,
3021 update_columns: Option<Vec<String>>,
3023 upsert_where: Vec<Condition>,
3025 returning: ReturningTarget,
3027 enable_logging: bool,
3028}
3029
3030impl<'a> QueryBuilder<'a> {
3031 pub(crate) fn new(pool: &'a PgPool, table_name: &str, enable_logging: bool) -> Self {
3033 Self {
3034 executor: QueryExecutor::Pool(pool),
3035 table: table_name.to_string(),
3036 fields: Vec::new(),
3037 projection_exprs: Vec::new(),
3038 window_exprs: Vec::new(),
3039 named_windows: Vec::new(),
3040 conditions: Vec::new(),
3041 joins: Vec::new(),
3042 order_by: Vec::new(),
3043 group_by: Vec::new(),
3044 grouping_constructs: Vec::new(),
3045 having_clause: Vec::new(),
3046 having_exprs: Vec::new(),
3047 limit: None,
3048 offset: None,
3049 distinct: false,
3050 distinct_on: Vec::new(),
3051 ctes: Vec::new(),
3052 set_ops: Vec::new(),
3053 field_types: HashMap::new(),
3054 set_exprs: Vec::new(),
3055 conflict_target: ConflictTarget::Unspecified,
3056 conflict_predicate: None,
3057 do_nothing: false,
3058 update_columns: None,
3059 upsert_where: Vec::new(),
3060 returning: ReturningTarget::Column("id".to_string()),
3061 enable_logging,
3062 }
3063 }
3064
3065 pub(crate) fn new_transaction(
3066 transaction: &'a mut crate::postgres::Transaction,
3067 table_name: &str,
3068 enable_logging: bool,
3069 ) -> Self {
3070 Self {
3071 executor: QueryExecutor::Transaction(transaction),
3072 table: table_name.to_string(),
3073 fields: Vec::new(),
3074 projection_exprs: Vec::new(),
3075 window_exprs: Vec::new(),
3076 named_windows: Vec::new(),
3077 conditions: Vec::new(),
3078 joins: Vec::new(),
3079 order_by: Vec::new(),
3080 group_by: Vec::new(),
3081 grouping_constructs: Vec::new(),
3082 having_clause: Vec::new(),
3083 having_exprs: Vec::new(),
3084 limit: None,
3085 offset: None,
3086 distinct: false,
3087 distinct_on: Vec::new(),
3088 ctes: Vec::new(),
3089 set_ops: Vec::new(),
3090 field_types: HashMap::new(),
3091 set_exprs: Vec::new(),
3092 conflict_target: ConflictTarget::Unspecified,
3093 conflict_predicate: None,
3094 do_nothing: false,
3095 update_columns: None,
3096 upsert_where: Vec::new(),
3097 returning: ReturningTarget::Column("id".to_string()),
3098 enable_logging,
3099 }
3100 }
3101
3102 pub fn field(mut self, field: &crate::FieldRef) -> Self {
3106 self.fields.push(field.postgres_quoted().to_string());
3107 self
3108 }
3109
3110 pub fn expr(mut self, expression: crate::SelectExpr) -> Self {
3112 self.fields.push(expression.postgres_sql());
3113 self
3114 }
3115
3116 pub fn select_expr(mut self, expression: crate::SqlExpr, alias: &crate::FieldRef) -> Self {
3121 self.projection_exprs
3122 .push((expression, Some(alias.as_str().to_string())));
3123 self
3124 }
3125
3126 pub fn set_expr(mut self, field: &crate::FieldRef, expression: crate::SqlExpr) -> Self {
3132 self.set_exprs
3133 .push((field.as_str().to_string(), expression));
3134 self
3135 }
3136
3137 pub fn fields(mut self, fields: &[&crate::FieldRef]) -> Self {
3139 for field in fields {
3140 self.fields.push(field.postgres_quoted().to_string());
3141 }
3142 self
3143 }
3144
3145 pub fn json(mut self, field: &crate::FieldRef) -> Self {
3147 self.field_types
3148 .insert(field.as_str().to_string(), FieldType::Json);
3149 self
3150 }
3151
3152 pub fn jsonb(mut self, field: &crate::FieldRef) -> Self {
3154 self.field_types
3155 .insert(field.as_str().to_string(), FieldType::Jsonb);
3156 self
3157 }
3158
3159 pub fn uuid(mut self, field: &crate::FieldRef) -> Self {
3161 self.field_types
3162 .insert(field.as_str().to_string(), FieldType::Uuid);
3163 self
3164 }
3165
3166 pub fn date(mut self, field: &crate::FieldRef) -> Self {
3168 self.field_types
3169 .insert(field.as_str().to_string(), FieldType::Date);
3170 self
3171 }
3172
3173 pub fn time(mut self, field: &crate::FieldRef) -> Self {
3175 self.field_types
3176 .insert(field.as_str().to_string(), FieldType::Time);
3177 self
3178 }
3179
3180 pub fn datetime(mut self, field: &crate::FieldRef) -> Self {
3182 self.field_types
3183 .insert(field.as_str().to_string(), FieldType::DateTime);
3184 self
3185 }
3186
3187 pub fn timestamp(mut self, field: &crate::FieldRef) -> Self {
3189 self.field_types
3190 .insert(field.as_str().to_string(), FieldType::Timestamp);
3191 self
3192 }
3193
3194 pub fn timestamp_tz(mut self, field: &crate::FieldRef) -> Self {
3196 self.field_types
3197 .insert(field.as_str().to_string(), FieldType::TimestampTz);
3198 self
3199 }
3200
3201 pub fn decimal(mut self, field: &crate::FieldRef) -> Self {
3203 self.field_types
3204 .insert(field.as_str().to_string(), FieldType::Decimal);
3205 self
3206 }
3207
3208 pub fn blob(mut self, field: &crate::FieldRef) -> Self {
3210 self.field_types
3211 .insert(field.as_str().to_string(), FieldType::Blob);
3212 self
3213 }
3214
3215 pub fn text(mut self, field: &crate::FieldRef) -> Self {
3217 self.field_types
3218 .insert(field.as_str().to_string(), FieldType::Text);
3219 self
3220 }
3221
3222 pub fn field_type(mut self, field: &crate::FieldRef, field_type: FieldType) -> Self {
3232 self.field_types
3233 .insert(field.as_str().to_string(), field_type);
3234 self
3235 }
3236
3237 pub fn distinct(mut self) -> Self {
3239 self.distinct = true;
3240 self
3241 }
3242
3243 pub fn distinct_on(mut self, fields: &[&crate::FieldRef]) -> Self {
3249 self.distinct_on = fields
3250 .iter()
3251 .map(|field| field.postgres_quoted().to_string())
3252 .collect();
3253 self
3254 }
3255
3256 pub fn with(mut self, name: &str, query: QueryBuilder<'a>) -> Self {
3261 self.ctes.push(Cte {
3262 name: name.to_string(),
3263 recursive: false,
3264 query: Box::new(query),
3265 });
3266 self
3267 }
3268
3269 pub fn with_recursive(mut self, name: &str, query: QueryBuilder<'a>) -> Self {
3271 self.ctes.push(Cte {
3272 name: name.to_string(),
3273 recursive: true,
3274 query: Box::new(query),
3275 });
3276 self
3277 }
3278
3279 pub fn window_expr(
3281 mut self,
3282 function: WindowFunction,
3283 spec: WindowSpec,
3284 alias: &crate::FieldRef,
3285 ) -> Self {
3286 self.window_exprs.push(WindowProjection {
3287 function,
3288 spec,
3289 alias: alias.as_str().to_string(),
3290 });
3291 self
3292 }
3293
3294 pub fn window(mut self, name: &str, spec: WindowSpec) -> Self {
3299 self.named_windows.push((name.to_string(), spec));
3300 self
3301 }
3302
3303 pub fn union(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
3305 self.add_set_operation(other, SetOperator::Union)
3306 }
3307
3308 pub fn union_all(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
3310 self.add_set_operation(other, SetOperator::UnionAll)
3311 }
3312
3313 pub fn intersect(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
3315 self.add_set_operation(other, SetOperator::Intersect)
3316 }
3317
3318 pub fn intersect_all(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
3320 self.add_set_operation(other, SetOperator::IntersectAll)
3321 }
3322
3323 pub fn except(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
3325 self.add_set_operation(other, SetOperator::Except)
3326 }
3327
3328 pub fn except_all(self, other: QueryBuilder<'a>) -> Result<Self, crate::error::DbError> {
3330 self.add_set_operation(other, SetOperator::ExceptAll)
3331 }
3332
3333 fn add_set_operation(
3334 mut self,
3335 other: QueryBuilder<'a>,
3336 operator: SetOperator,
3337 ) -> Result<Self, crate::error::DbError> {
3338 let left_len = self.projection_len();
3339 let right_len = other.projection_len();
3340 if left_len == 0 || right_len == 0 {
3341 return Err(crate::error::DbError::InvalidArgument(
3342 "集合运算各分支必须显式声明输出字段,不能使用未知列数的 *".to_string(),
3343 ));
3344 }
3345 if left_len != right_len {
3346 return Err(crate::error::DbError::InvalidArgument(format!(
3347 "集合运算输出列数不一致:左侧 {left_len} 列,右侧 {right_len} 列"
3348 )));
3349 }
3350 self.set_ops.push((operator, Box::new(other)));
3351 Ok(self)
3352 }
3353
3354 fn projection_len(&self) -> usize {
3356 self.fields.len() + self.projection_exprs.len() + self.window_exprs.len()
3357 }
3358
3359 pub fn grouping_sets(mut self, sets: Vec<Vec<crate::FieldRef>>) -> Self {
3363 self.grouping_constructs
3364 .push(GroupingConstruct::GroupingSets(sets));
3365 self
3366 }
3367
3368 pub fn rollup(mut self, fields: Vec<crate::FieldRef>) -> Self {
3370 self.grouping_constructs
3371 .push(GroupingConstruct::Rollup(fields));
3372 self
3373 }
3374
3375 pub fn cube(mut self, fields: Vec<crate::FieldRef>) -> Self {
3377 self.grouping_constructs
3378 .push(GroupingConstruct::Cube(fields));
3379 self
3380 }
3381
3382 pub fn on_conflict(mut self, columns: &[&crate::FieldRef]) -> Self {
3388 self.conflict_target = ConflictTarget::Columns(
3389 columns
3390 .iter()
3391 .map(|column| column.as_str().to_string())
3392 .collect(),
3393 );
3394 self.conflict_predicate = None;
3395 self
3396 }
3397
3398 pub fn on_conflict_constraint(mut self, constraint: &crate::FieldRef) -> Self {
3402 self.conflict_target = ConflictTarget::Constraint(constraint.as_str().to_string());
3403 self.conflict_predicate = None;
3404 self
3405 }
3406
3407 pub fn on_conflict_with_predicate(
3414 mut self,
3415 columns: &[&crate::FieldRef],
3416 predicate: &str,
3417 ) -> Self {
3418 self.conflict_target = ConflictTarget::Columns(
3419 columns
3420 .iter()
3421 .map(|column| column.as_str().to_string())
3422 .collect(),
3423 );
3424 self.conflict_predicate = Some(predicate.to_string());
3425 self
3426 }
3427
3428 pub fn update_columns(mut self, columns: &[&crate::FieldRef]) -> Self {
3432 self.update_columns = Some(
3433 columns
3434 .iter()
3435 .map(|column| column.as_str().to_string())
3436 .collect(),
3437 );
3438 self
3439 }
3440
3441 pub fn do_nothing(mut self) -> Self {
3446 self.do_nothing = true;
3447 self
3448 }
3449
3450 pub fn upsert_where_and<V>(
3454 mut self,
3455 field: &crate::FieldRef,
3456 op: crate::CompareOp,
3457 value: V,
3458 ) -> Result<Self, crate::error::DbError>
3459 where
3460 V: Into<SqlValue>,
3461 {
3462 let condition = comparison_condition(field.as_str(), op, value.into())?;
3463 self.upsert_where.push(condition);
3464 Ok(self)
3465 }
3466
3467 pub fn returning(mut self, column: &crate::FieldRef) -> Self {
3472 self.returning = ReturningTarget::Column(column.as_str().to_string());
3473 self
3474 }
3475
3476 pub fn returning_columns(mut self, columns: &[&crate::FieldRef]) -> Self {
3482 self.returning = ReturningTarget::Columns(
3483 columns
3484 .iter()
3485 .map(|column| ReturningColumn {
3486 column: column.as_str().to_string(),
3487 alias: None,
3488 })
3489 .collect(),
3490 );
3491 self
3492 }
3493
3494 pub fn returning_column_as(
3501 mut self,
3502 column: &crate::FieldRef,
3503 alias: &crate::FieldRef,
3504 ) -> Self {
3505 let target = ReturningColumn {
3506 column: column.as_str().to_string(),
3507 alias: Some(alias.as_str().to_string()),
3508 };
3509 match &mut self.returning {
3510 ReturningTarget::Columns(columns) => columns.push(target),
3511 ReturningTarget::Column(_) | ReturningTarget::All => {
3512 self.returning = ReturningTarget::Columns(vec![target]);
3513 }
3514 }
3515 self
3516 }
3517
3518 pub fn returning_all(mut self) -> Self {
3520 self.returning = ReturningTarget::All;
3521 self
3522 }
3523
3524 fn returning_clause(&self) -> Result<String, crate::error::DbError> {
3526 match &self.returning {
3527 ReturningTarget::Column(column) => Ok(format!(
3528 " RETURNING {}",
3529 super::identifier::quote_identifier(column)?
3530 )),
3531 ReturningTarget::Columns(columns) => {
3532 if columns.is_empty() {
3533 return Err(crate::error::DbError::InvalidArgument(
3534 "returning_columns 至少需要一个列".to_string(),
3535 ));
3536 }
3537 let rendered = columns
3538 .iter()
3539 .map(|target| {
3540 let column = super::identifier::quote_identifier(&target.column)?;
3541 match &target.alias {
3542 Some(alias) => Ok(format!(
3543 "{column} AS {}",
3544 super::identifier::quote_identifier(alias)?
3545 )),
3546 None => Ok(column),
3547 }
3548 })
3549 .collect::<Result<Vec<_>, crate::error::DbError>>()?;
3550 Ok(format!(" RETURNING {}", rendered.join(", ")))
3551 }
3552 ReturningTarget::All => Ok(" RETURNING *".to_string()),
3553 }
3554 }
3555
3556 fn coerce_condition_value(&self, field: &str, value: SqlValue) -> SqlValue {
3565 let Some(field_type) = self.field_types.get(field) else {
3566 return value;
3567 };
3568 match (field_type, value) {
3569 (FieldType::Inet, SqlValue::String(value)) => SqlValue::Inet(value),
3570 (FieldType::Cidr, SqlValue::String(value)) => SqlValue::Cidr(value),
3571 (FieldType::MacAddr, SqlValue::String(value)) => SqlValue::MacAddr(value),
3572 (FieldType::MacAddr8, SqlValue::String(value)) => SqlValue::MacAddr8(value),
3573 (_, value) => value,
3574 }
3575 }
3576
3577 pub fn where_and<V>(
3578 mut self,
3579 field: &crate::FieldRef,
3580 op: crate::CompareOp,
3581 value: V,
3582 ) -> Result<Self, crate::error::DbError>
3583 where
3584 V: Into<SqlValue>,
3585 {
3586 let field = field.as_str().to_string();
3587 let value = self.coerce_condition_value(&field, value.into());
3588 let condition = comparison_condition(&field, op, value)?;
3589 self.conditions.push(condition);
3590 Ok(self)
3591 }
3592
3593 pub fn where_or<V>(
3597 mut self,
3598 field: &crate::FieldRef,
3599 op: crate::CompareOp,
3600 value: V,
3601 ) -> Result<Self, crate::error::DbError>
3602 where
3603 V: Into<SqlValue>,
3604 {
3605 let name = field.as_str().to_string();
3606 let value = self.coerce_condition_value(&name, value.into());
3607 let condition = comparison_condition(&name, op, value)?;
3608
3609 if !self.conditions.is_empty() {
3610 let mut existing = std::mem::take(&mut self.conditions);
3611 self.conditions.push(Condition::Or(vec![
3612 if existing.len() == 1 {
3613 existing.remove(0)
3614 } else {
3615 Condition::And(existing)
3616 },
3617 condition,
3618 ]));
3619 } else {
3620 self.conditions.push(condition);
3621 }
3622
3623 Ok(self)
3624 }
3625
3626 pub fn where_in<V>(mut self, field: &crate::FieldRef, values: Vec<V>) -> Self
3628 where
3629 V: Into<SqlValue>,
3630 {
3631 let name = field.as_str().to_string();
3632 let sql_values: Vec<_> = values
3633 .into_iter()
3634 .map(|v| self.coerce_condition_value(&name, v.into()))
3635 .collect();
3636 self.conditions.push(Condition::In(name, sql_values));
3637 self
3638 }
3639
3640 pub fn where_exists(mut self, subquery: crate::postgres::Subquery) -> Self {
3642 self.conditions.push(Condition::Exists(Box::new(subquery)));
3643 self
3644 }
3645
3646 pub fn where_not_exists(mut self, subquery: crate::postgres::Subquery) -> Self {
3648 self.conditions
3649 .push(Condition::NotExists(Box::new(subquery)));
3650 self
3651 }
3652
3653 pub fn where_in_subquery(
3655 mut self,
3656 field: &crate::FieldRef,
3657 subquery: crate::postgres::Subquery,
3658 ) -> Self {
3659 self.conditions.push(Condition::InSubquery(
3660 field.as_str().to_string(),
3661 Box::new(subquery),
3662 ));
3663 self
3664 }
3665
3666 pub fn where_between<V>(mut self, field: &crate::FieldRef, start: V, end: V) -> Self
3668 where
3669 V: Into<SqlValue>,
3670 {
3671 let name = field.as_str().to_string();
3672 let start = self.coerce_condition_value(&name, start.into());
3673 let end = self.coerce_condition_value(&name, end.into());
3674 self.conditions.push(Condition::Between(name, start, end));
3675 self
3676 }
3677
3678 pub fn where_null(mut self, field: &crate::FieldRef) -> Self {
3680 self.conditions
3681 .push(Condition::IsNull(field.as_str().to_string()));
3682 self
3683 }
3684
3685 pub fn where_not_null(mut self, field: &crate::FieldRef) -> Self {
3687 self.conditions
3688 .push(Condition::IsNotNull(field.as_str().to_string()));
3689 self
3690 }
3691
3692 pub fn where_predicate(
3694 mut self,
3695 predicate: &crate::Predicate,
3696 ) -> Result<Self, crate::error::DbError> {
3697 self.conditions.push(predicate_condition(predicate)?);
3698 Ok(self)
3699 }
3700
3701 pub fn where_not(mut self, condition: Condition) -> Self {
3703 self.conditions.push(Condition::Not(Box::new(condition)));
3704 self
3705 }
3706
3707 pub fn where_column(
3713 mut self,
3714 left: &crate::FieldRef,
3715 op: crate::CompareOp,
3716 right: &crate::FieldRef,
3717 ) -> Result<Self, crate::error::DbError> {
3718 let operator = comparison_operator(op).ok_or_else(|| {
3719 crate::error::DbError::UnsupportedOperator(
3720 "列比较不支持 LIKE/ILIKE/正则/NULL-safe 比较符".to_string(),
3721 )
3722 })?;
3723 self.conditions.push(Condition::ColumnComparison(
3724 left.as_str().to_string(),
3725 operator,
3726 right.as_str().to_string(),
3727 ));
3728 Ok(self)
3729 }
3730
3731 pub fn where_json_contains<V>(mut self, field: &crate::FieldRef, value: V) -> Self
3736 where
3737 V: Into<SqlValue>,
3738 {
3739 self.conditions.push(Condition::JsonContains(
3740 field.as_str().to_string(),
3741 value.into(),
3742 ));
3743 self
3744 }
3745
3746 pub fn where_json_contained_by<V>(mut self, field: &crate::FieldRef, value: V) -> Self
3750 where
3751 V: Into<SqlValue>,
3752 {
3753 self.conditions.push(Condition::JsonContainedBy(
3754 field.as_str().to_string(),
3755 value.into(),
3756 ));
3757 self
3758 }
3759
3760 pub fn where_json_has_key(mut self, field: &crate::FieldRef, key: &str) -> Self {
3762 self.conditions.push(Condition::JsonHasKey(
3763 field.as_str().to_string(),
3764 key.to_string(),
3765 ));
3766 self
3767 }
3768
3769 pub fn where_json_path_exists(mut self, field: &crate::FieldRef, path: &str) -> Self {
3774 self.conditions.push(Condition::JsonPathExists(
3775 field.as_str().to_string(),
3776 path.to_string(),
3777 ));
3778 self
3779 }
3780
3781 pub fn where_json_path_match<V>(mut self, field: &crate::FieldRef, path: &str, vars: V) -> Self
3786 where
3787 V: Into<SqlValue>,
3788 {
3789 self.conditions.push(Condition::JsonPathMatch(
3790 field.as_str().to_string(),
3791 path.to_string(),
3792 vars.into(),
3793 ));
3794 self
3795 }
3796
3797 pub fn where_range_contains<V>(mut self, field: &crate::FieldRef, value: V) -> Self
3804 where
3805 V: Into<SqlValue>,
3806 {
3807 self.conditions.push(Condition::RangeContains(
3808 field.as_str().to_string(),
3809 value.into(),
3810 ));
3811 self
3812 }
3813
3814 pub fn where_range_overlaps<V>(mut self, field: &crate::FieldRef, value: V) -> Self
3818 where
3819 V: Into<SqlValue>,
3820 {
3821 self.conditions.push(Condition::RangeOverlaps(
3822 field.as_str().to_string(),
3823 value.into(),
3824 ));
3825 self
3826 }
3827
3828 pub fn where_full_text(mut self, field: &crate::FieldRef, query: &str) -> Self {
3832 self.conditions.push(Condition::FullText(
3833 field.as_str().to_string(),
3834 query.to_string(),
3835 ));
3836 self
3837 }
3838
3839 pub fn where_full_text_query(mut self, field: &crate::FieldRef, query: &str) -> Self {
3841 self.conditions.push(Condition::FullTextQuery(
3842 field.as_str().to_string(),
3843 query.to_string(),
3844 ));
3845 self
3846 }
3847
3848 pub fn where_any<V>(mut self, field: &crate::FieldRef, values: Vec<V>) -> Self
3850 where
3851 V: Into<SqlValue>,
3852 {
3853 self.conditions.push(Condition::Any(
3854 field.as_str().to_string(),
3855 SqlValue::Array(values.into_iter().map(Into::into).collect()),
3856 ));
3857 self
3858 }
3859
3860 pub fn where_all<V>(mut self, field: &crate::FieldRef, values: Vec<V>) -> Self
3862 where
3863 V: Into<SqlValue>,
3864 {
3865 self.conditions.push(Condition::All(
3866 field.as_str().to_string(),
3867 SqlValue::Array(values.into_iter().map(Into::into).collect()),
3868 ));
3869 self
3870 }
3871
3872 pub fn where_expr(
3876 mut self,
3877 field: &crate::FieldRef,
3878 op: crate::CompareOp,
3879 expression: crate::SqlExpr,
3880 ) -> Result<Self, crate::error::DbError> {
3881 let operator = comparison_operator(op).ok_or_else(|| {
3882 crate::error::DbError::UnsupportedOperator(
3883 "表达式比较不支持 LIKE/ILIKE/正则/NULL-safe 比较符".to_string(),
3884 )
3885 })?;
3886 self.conditions.push(Condition::FieldExpr(
3887 field.as_str().to_string(),
3888 operator,
3889 expression,
3890 ));
3891 Ok(self)
3892 }
3893
3894 pub fn having_cond<V>(
3896 mut self,
3897 field: &crate::FieldRef,
3898 op: crate::CompareOp,
3899 value: V,
3900 ) -> Result<Self, crate::error::DbError>
3901 where
3902 V: Into<SqlValue>,
3903 {
3904 let field = field.as_str().to_string();
3905 let value = self.coerce_condition_value(&field, value.into());
3906 let condition = comparison_condition(&field, op, value)?;
3907 self.having_clause.push(condition);
3908 Ok(self)
3909 }
3910
3911 pub fn having_expr<V>(
3917 mut self,
3918 expression: crate::SelectExpr,
3919 op: crate::CompareOp,
3920 value: V,
3921 ) -> Result<Self, crate::error::DbError>
3922 where
3923 V: Into<SqlValue>,
3924 {
3925 let operator = comparison_operator(op).ok_or_else(|| {
3926 crate::error::DbError::UnsupportedOperator(
3927 "HAVING 聚合表达式不支持 LIKE/ILIKE/正则/NULL-safe 比较符".to_string(),
3928 )
3929 })?;
3930 self.having_exprs.push(HavingExpr {
3931 expression,
3932 operator,
3933 value: value.into(),
3934 });
3935 Ok(self)
3936 }
3937
3938 pub fn join(
3943 mut self,
3944 table: &crate::TableRef,
3945 left: &crate::FieldRef,
3946 right: &crate::FieldRef,
3947 ) -> Self {
3948 self.joins.push(JoinSpec {
3949 kind: JoinKind::Inner,
3950 target: JoinTarget::Table(table.as_str().to_string()),
3951 condition: JoinCondition::On(format!(
3952 "{} = {}",
3953 left.postgres_quoted(),
3954 right.postgres_quoted()
3955 )),
3956 });
3957 self
3958 }
3959
3960 pub fn left_join(
3962 mut self,
3963 table: &crate::TableRef,
3964 left: &crate::FieldRef,
3965 right: &crate::FieldRef,
3966 ) -> Self {
3967 self.joins.push(JoinSpec {
3968 kind: JoinKind::Left,
3969 target: JoinTarget::Table(table.as_str().to_string()),
3970 condition: JoinCondition::On(format!(
3971 "{} = {}",
3972 left.postgres_quoted(),
3973 right.postgres_quoted()
3974 )),
3975 });
3976 self
3977 }
3978
3979 pub fn right_join(
3981 mut self,
3982 table: &crate::TableRef,
3983 left: &crate::FieldRef,
3984 right: &crate::FieldRef,
3985 ) -> Self {
3986 self.joins.push(JoinSpec {
3987 kind: JoinKind::Right,
3988 target: JoinTarget::Table(table.as_str().to_string()),
3989 condition: JoinCondition::On(format!(
3990 "{} = {}",
3991 left.postgres_quoted(),
3992 right.postgres_quoted()
3993 )),
3994 });
3995 self
3996 }
3997
3998 pub fn full_join(
4000 mut self,
4001 table: &crate::TableRef,
4002 left: &crate::FieldRef,
4003 right: &crate::FieldRef,
4004 ) -> Self {
4005 self.joins.push(JoinSpec {
4006 kind: JoinKind::Full,
4007 target: JoinTarget::Table(table.as_str().to_string()),
4008 condition: JoinCondition::On(format!(
4009 "{} = {}",
4010 left.postgres_quoted(),
4011 right.postgres_quoted()
4012 )),
4013 });
4014 self
4015 }
4016
4017 pub fn cross_join(mut self, table: &crate::TableRef) -> Self {
4019 self.joins.push(JoinSpec {
4020 kind: JoinKind::Cross,
4021 target: JoinTarget::Table(table.as_str().to_string()),
4022 condition: JoinCondition::None,
4023 });
4024 self
4025 }
4026
4027 pub fn natural_join(mut self, table: &crate::TableRef) -> Self {
4029 self.joins.push(JoinSpec {
4030 kind: JoinKind::Natural,
4031 target: JoinTarget::Table(table.as_str().to_string()),
4032 condition: JoinCondition::None,
4033 });
4034 self
4035 }
4036
4037 pub fn join_using(mut self, table: &crate::TableRef, columns: &[&crate::FieldRef]) -> Self {
4039 self.joins.push(JoinSpec {
4040 kind: JoinKind::Inner,
4041 target: JoinTarget::Table(table.as_str().to_string()),
4042 condition: JoinCondition::Using(
4043 columns
4044 .iter()
4045 .map(|column| column.as_str().to_string())
4046 .collect(),
4047 ),
4048 });
4049 self
4050 }
4051
4052 pub fn left_join_lateral(
4056 mut self,
4057 query: QueryBuilder<'a>,
4058 alias: &crate::FieldRef,
4059 left: &crate::FieldRef,
4060 right: &crate::FieldRef,
4061 ) -> Self {
4062 self.joins.push(JoinSpec {
4063 kind: JoinKind::LeftLateral,
4064 target: JoinTarget::Lateral(Box::new(query), alias.as_str().to_string()),
4065 condition: JoinCondition::On(format!(
4066 "{} = {}",
4067 left.postgres_quoted(),
4068 right.postgres_quoted()
4069 )),
4070 });
4071 self
4072 }
4073
4074 pub fn order(mut self, field: &crate::FieldRef, order: crate::SortOrder) -> Self {
4077 self.order_by.push(OrderClause {
4078 field: field.postgres_quoted().to_string(),
4079 asc: order.is_ascending(),
4080 });
4081 self
4082 }
4083
4084 pub fn group(mut self, field: &crate::FieldRef) -> Self {
4087 self.group_by.push(field.postgres_quoted().to_string());
4088 self
4089 }
4090
4091 pub fn limit(mut self, limit: u64) -> Self {
4093 self.limit = Some(limit);
4094 self
4095 }
4096
4097 pub fn offset(mut self, offset: u64) -> Self {
4099 self.offset = Some(offset);
4100 self
4101 }
4102
4103 pub fn try_to_sql(&self) -> Result<String, crate::error::DbError> {
4108 let mut generator = SqlGenerator::new();
4109 generator.build_select(self)?;
4110 Ok(generator.get_sql().to_string())
4111 }
4112
4113 pub(crate) fn render_for_transaction(
4114 &self,
4115 lock: Option<crate::RowLock>,
4116 ) -> Result<(String, Vec<SqlValue>), crate::error::DbError> {
4117 if lock.is_some() {
4118 self.ensure_row_lock_supported()?;
4119 }
4120 let mut generator = SqlGenerator::new();
4121 generator.build_select(self)?;
4122 if let Some(lock) = lock {
4123 generator.sql.push(' ');
4124 generator.sql.push_str(lock.as_sql());
4125 }
4126 Ok((generator.sql, generator.params))
4127 }
4128
4129 fn ensure_row_lock_supported(&self) -> Result<(), crate::error::DbError> {
4131 if self.distinct
4132 || !self.distinct_on.is_empty()
4133 || !self.group_by.is_empty()
4134 || !self.grouping_constructs.is_empty()
4135 || !self.having_clause.is_empty()
4136 || !self.having_exprs.is_empty()
4137 || !self.window_exprs.is_empty()
4138 || !self.ctes.is_empty()
4139 || !self.set_ops.is_empty()
4140 {
4141 return Err(crate::error::DbError::InvalidArgument(
4142 "行锁不支持 DISTINCT、GROUP BY/分组集、HAVING、窗口函数、CTE 或集合运算查询"
4143 .to_string(),
4144 ));
4145 }
4146 Ok(())
4147 }
4148
4149 pub fn try_to_sql_with_lock(
4158 &self,
4159 lock: crate::RowLock,
4160 of: &[&crate::FieldRef],
4161 nowait: bool,
4162 skip_locked: bool,
4163 ) -> Result<String, crate::error::DbError> {
4164 Ok(self.render_with_lock(lock, of, nowait, skip_locked)?.0)
4165 }
4166
4167 pub(crate) fn render_with_lock(
4169 &self,
4170 lock: crate::RowLock,
4171 of: &[&crate::FieldRef],
4172 nowait: bool,
4173 skip_locked: bool,
4174 ) -> Result<(String, Vec<SqlValue>), crate::error::DbError> {
4175 let implied_skip = matches!(
4176 lock,
4177 crate::RowLock::ForUpdateSkipLocked | crate::RowLock::ForShareSkipLocked
4178 );
4179 if nowait && (skip_locked || implied_skip) {
4180 return Err(crate::error::DbError::InvalidArgument(
4181 "行锁的 NOWAIT 与 SKIP LOCKED 不能同时使用".to_string(),
4182 ));
4183 }
4184 self.ensure_row_lock_supported()?;
4185 let mut generator = SqlGenerator::new();
4186 generator.build_select(self)?;
4187
4188 let base = match lock {
4189 crate::RowLock::ForUpdate | crate::RowLock::ForUpdateSkipLocked => "FOR UPDATE",
4190 crate::RowLock::ForShare | crate::RowLock::ForShareSkipLocked => "FOR SHARE",
4191 crate::RowLock::NoKeyUpdate => "FOR NO KEY UPDATE",
4192 crate::RowLock::KeyShare => "FOR KEY SHARE",
4193 };
4194 let mut clause = String::from(base);
4195 if !of.is_empty() {
4196 let quoted = of
4197 .iter()
4198 .map(|table| super::identifier::quote_qualified(table.as_str()))
4199 .collect::<Result<Vec<_>, _>>()?;
4200 clause.push_str(" OF ");
4201 clause.push_str("ed.join(", "));
4202 }
4203 if skip_locked
4204 || matches!(
4205 lock,
4206 crate::RowLock::ForUpdateSkipLocked | crate::RowLock::ForShareSkipLocked
4207 )
4208 {
4209 clause.push_str(" SKIP LOCKED");
4210 } else if nowait {
4211 clause.push_str(" NOWAIT");
4212 }
4213
4214 generator.sql.push(' ');
4215 generator.sql.push_str(&clause);
4216 Ok((generator.sql, generator.params))
4217 }
4218
4219 pub fn to_sql(&self) -> String {
4224 match self.try_to_sql() {
4225 Ok(sql) => sql,
4226 Err(err) => {
4227 if self.enable_logging {
4228 log::warn!("生成 SELECT SQL 失败: {err}");
4229 }
4230 "/* SQL generation failed */".to_string()
4231 }
4232 }
4233 }
4234
4235 #[tracing::instrument(
4237 name = "db.query",
4238 skip_all,
4239 fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
4240 )]
4241 pub async fn find<T>(mut self) -> Result<Option<T>, crate::error::DbError>
4242 where
4243 T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
4244 {
4245 self.limit = Some(1);
4246
4247 let mut generator = SqlGenerator::new();
4248 generator.build_select(&self)?;
4249 let sql = generator.get_sql();
4250 let params = generator.get_params();
4251
4252 if self.enable_logging {
4253 log::debug!("执行 find() 查询: {}", sql);
4254 log::debug!("参数: {:?}", params);
4255 }
4256
4257 let mut query = sqlx::query_as::<_, T>(sql);
4258 for param in params {
4259 query = bind_param(query, param);
4260 }
4261
4262 let result = match self.executor {
4263 QueryExecutor::Pool(pool) => query.fetch_optional(pool).await,
4264 QueryExecutor::Transaction(transaction) => {
4265 let connection = transaction.executor().ok_or_else(|| {
4266 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
4267 })?;
4268 query.fetch_optional(&mut *connection).await
4269 }
4270 };
4271 match result {
4272 Ok(row) => Ok(row),
4273 Err(e) => {
4274 log::error!("find() 查询失败: {}", e);
4275 Err(crate::error::DbError::from(e))
4276 }
4277 }
4278 }
4279
4280 #[tracing::instrument(
4282 name = "db.query",
4283 skip_all,
4284 fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
4285 )]
4286 pub async fn select<T>(self) -> Result<Vec<T>, crate::error::DbError>
4287 where
4288 T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
4289 {
4290 let mut generator = SqlGenerator::new();
4291 generator.build_select(&self)?;
4292 let sql = generator.get_sql();
4293 let params = generator.get_params();
4294
4295 if self.enable_logging {
4296 log::debug!("执行 select() 查询: {}", sql);
4297 log::debug!("参数: {:?}", params);
4298 }
4299
4300 let mut query = sqlx::query_as::<_, T>(sql);
4301 for param in params {
4302 query = bind_param(query, param);
4303 }
4304
4305 let result = match self.executor {
4306 QueryExecutor::Pool(pool) => query.fetch_all(pool).await,
4307 QueryExecutor::Transaction(transaction) => {
4308 let connection = transaction.executor().ok_or_else(|| {
4309 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
4310 })?;
4311 query.fetch_all(&mut *connection).await
4312 }
4313 };
4314 match result {
4315 Ok(rows) => Ok(rows),
4316 Err(e) => {
4317 log::error!("select() 查询失败: {}", e);
4318 Err(crate::error::DbError::from(e))
4319 }
4320 }
4321 }
4322
4323 async fn fetch_scalar<C>(
4325 mut self,
4326 select_expr: &str,
4327 ) -> Result<Option<C>, crate::error::DbError>
4328 where
4329 C: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
4330 {
4331 self.fields.clear();
4332 self.fields.push(select_expr.to_string());
4333 self.limit = Some(1);
4334
4335 let mut generator = SqlGenerator::new();
4336 generator.build_select(&self)?;
4337 let sql = generator.get_sql();
4338 let params = generator.get_params();
4339
4340 if self.enable_logging {
4341 log::debug!("执行标量查询: {}", sql);
4342 log::debug!("参数: {:?}", params);
4343 }
4344
4345 let mut query = sqlx::query_scalar::<_, C>(sql);
4346 for param in params {
4347 query = bind_scalar_param(query, param);
4348 }
4349
4350 let result = match self.executor {
4351 QueryExecutor::Pool(pool) => query.fetch_optional(pool).await,
4352 QueryExecutor::Transaction(transaction) => {
4353 let connection = transaction.executor().ok_or_else(|| {
4354 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
4355 })?;
4356 query.fetch_optional(&mut *connection).await
4357 }
4358 };
4359 match result {
4360 Ok(value) => Ok(value),
4361 Err(e) => {
4362 log::error!("标量查询失败: {}", e);
4363 Err(crate::error::DbError::from(e))
4364 }
4365 }
4366 }
4367
4368 #[tracing::instrument(
4370 name = "db.query",
4371 skip_all,
4372 fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
4373 )]
4374 pub async fn value<T>(self, field: &crate::FieldRef) -> Result<Option<T>, crate::error::DbError>
4375 where
4376 T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
4377 {
4378 if self.enable_logging {
4379 log::debug!("执行 value() 查询,字段: {}", field.as_str());
4380 }
4381 let field = super::identifier::quote_identifier(field.as_str())?;
4382 self.fetch_scalar::<T>(&field).await
4383 }
4384
4385 #[tracing::instrument(
4387 name = "db.query",
4388 skip_all,
4389 fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
4390 )]
4391 pub async fn count(self) -> Result<i64, crate::error::DbError> {
4392 if self.enable_logging {
4393 log::debug!("执行 count() 查询");
4394 }
4395 let result = self.fetch_scalar::<i64>("COUNT(*)").await?;
4396 Ok(result.unwrap_or(0))
4397 }
4398 #[tracing::instrument(
4403 name = "db.query",
4404 skip_all,
4405 fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
4406 )]
4407 pub async fn sum(self, field: &crate::FieldRef) -> Result<Option<f64>, crate::error::DbError> {
4408 if self.enable_logging {
4409 log::debug!("执行 sum() 查询,字段: {}", field.as_str());
4410 }
4411 let quoted_field = super::identifier::quote_identifier(field.as_str())?;
4413 let sum_expr = format!("CAST(SUM({quoted_field}) AS DOUBLE PRECISION)");
4414 self.fetch_scalar::<Option<f64>>(&sum_expr)
4415 .await
4416 .map(Option::flatten)
4417 }
4418
4419 #[tracing::instrument(
4423 name = "db.query",
4424 skip_all,
4425 fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
4426 )]
4427 pub async fn avg(self, field: &crate::FieldRef) -> Result<Option<f64>, crate::error::DbError> {
4428 if self.enable_logging {
4429 log::debug!("执行 avg() 查询,字段: {}", field.as_str());
4430 }
4431 let quoted_field = super::identifier::quote_identifier(field.as_str())?;
4433 let avg_expr = format!("CAST(AVG({quoted_field}) AS DOUBLE PRECISION)");
4434 self.fetch_scalar::<Option<f64>>(&avg_expr)
4435 .await
4436 .map(Option::flatten)
4437 }
4438
4439 #[tracing::instrument(
4441 name = "db.query",
4442 skip_all,
4443 fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
4444 )]
4445 pub async fn min<T>(self, field: &crate::FieldRef) -> Result<Option<T>, crate::error::DbError>
4446 where
4447 T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
4448 {
4449 if self.enable_logging {
4450 log::debug!("执行 min() 查询,字段: {}", field.as_str());
4451 }
4452 let quoted_field = super::identifier::quote_identifier(field.as_str())?;
4454 let min_expr = format!("MIN({quoted_field})");
4455 self.fetch_scalar::<Option<T>>(&min_expr)
4456 .await
4457 .map(Option::flatten)
4458 }
4459
4460 #[tracing::instrument(
4462 name = "db.query",
4463 skip_all,
4464 fields(db.system = "postgresql", db.operation = "select", db.collection = %self.table, otel.kind = "client")
4465 )]
4466 pub async fn max<T>(self, field: &crate::FieldRef) -> Result<Option<T>, crate::error::DbError>
4467 where
4468 T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
4469 {
4470 if self.enable_logging {
4471 log::debug!("执行 max() 查询,字段: {}", field.as_str());
4472 }
4473 let quoted_field = super::identifier::quote_identifier(field.as_str())?;
4475 let max_expr = format!("MAX({quoted_field})");
4476 self.fetch_scalar::<Option<T>>(&max_expr)
4477 .await
4478 .map(Option::flatten)
4479 }
4480 #[tracing::instrument(
4492 name = "db.query",
4493 skip_all,
4494 fields(db.system = "postgresql", db.operation = "insert", db.collection = %self.table, otel.kind = "client")
4495 )]
4496 pub async fn insert<T>(self, data: &T) -> Result<u64, crate::error::DbError>
4497 where
4498 T: serde::Serialize,
4499 {
4500 if !matches!(self.returning, ReturningTarget::Column(_)) {
4502 return Err(crate::error::DbError::InvalidArgument(
4503 "insert() 仅支持单列 RETURNING;多列或整行请使用 insert_returning::<T, R>()"
4504 .to_string(),
4505 ));
4506 }
4507 let id: IntegerPk = self
4511 .insert_returning(data)
4512 .await
4513 .map_err(|error| match error {
4514 crate::error::DbError::TypeConversionError(detail) => {
4515 map_insert_pk_decode_error(detail)
4516 }
4517 other => other,
4518 })?;
4519 u64::try_from(id.0).map_err(|_| {
4521 crate::error::DbError::InvalidArgument(NEGATIVE_PK_DECODE_PREFIX.to_string())
4522 })
4523 }
4524
4525 #[tracing::instrument(
4535 name = "db.query",
4536 skip_all,
4537 fields(db.system = "postgresql", db.operation = "insert", db.collection = %self.table, otel.kind = "client")
4538 )]
4539 pub async fn insert_returning<T, R>(self, data: &T) -> Result<R, crate::error::DbError>
4540 where
4541 T: serde::Serialize,
4542 R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
4543 {
4544 if self.enable_logging {
4545 log::debug!("执行 insert_returning() 操作,表: {}", self.table);
4546 }
4547
4548 let json_data = serde_json::to_value(data).map_err(|e| {
4549 crate::error::DbError::SerializationError(format!("数据序列化失败: {}", e))
4550 })?;
4551
4552 let mut generator = SqlGenerator::new();
4553 generator.build_insert(&self.table, &json_data, &self.field_types, &self.set_exprs)?;
4554 let sql = format!("{}{}", generator.get_sql(), self.returning_clause()?);
4555 let params = generator.get_params();
4556
4557 if self.enable_logging {
4558 log::debug!("执行 insert_returning() SQL: {}", sql);
4559 log::debug!("参数: {:?}", params);
4560 }
4561
4562 let mut query = sqlx::query_as::<_, R>(&sql);
4563 for param in params {
4564 query = bind_param(query, param);
4565 }
4566
4567 let result = match self.executor {
4568 QueryExecutor::Pool(pool) => {
4569 let mut tx = pool.begin().await.map_err(crate::error::DbError::from)?;
4571 match query.fetch_one(&mut *tx).await {
4572 Ok(row) => {
4573 tx.commit().await.map_err(crate::error::DbError::from)?;
4574 Ok(row)
4575 }
4576 Err(error) => {
4577 let _ = tx.rollback().await;
4578 Err(error)
4579 }
4580 }
4581 }
4582 QueryExecutor::Transaction(transaction) => {
4583 let connection = transaction.executor().ok_or_else(|| {
4584 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
4585 })?;
4586 query.fetch_one(&mut *connection).await
4587 }
4588 };
4589 match result {
4590 Ok(row) => Ok(row),
4591 Err(error) => {
4592 let db_err = crate::error::DbError::from(error);
4593 if db_err.is_constraint_violation() {
4594 log::warn!(
4595 "insert_returning() 失败: {}(约束冲突,可能为预期分支)",
4596 db_err
4597 );
4598 } else {
4599 log::error!("insert_returning() 失败: {}", db_err);
4600 }
4601 Err(db_err)
4602 }
4603 }
4604 }
4605 #[tracing::instrument(
4610 name = "db.query",
4611 skip_all,
4612 fields(db.system = "postgresql", db.operation = "insert", db.collection = %self.table, otel.kind = "client")
4613 )]
4614 pub async fn insert_batch<T>(self, data: &[T]) -> Result<u64, crate::error::DbError>
4615 where
4616 T: serde::Serialize,
4617 {
4618 self.insert_batch_with_size(data, INSERT_BATCH_SIZE).await
4619 }
4620
4621 #[tracing::instrument(
4625 name = "db.query",
4626 skip_all,
4627 fields(db.system = "postgresql", db.operation = "insert", db.collection = %self.table, otel.kind = "client")
4628 )]
4629 pub async fn insert_batch_with_size<T>(
4630 self,
4631 data: &[T],
4632 batch_size: usize,
4633 ) -> Result<u64, crate::error::DbError>
4634 where
4635 T: serde::Serialize,
4636 {
4637 if batch_size == 0 {
4638 return Err(crate::error::DbError::SerializationError(
4639 "batch_size 不能为 0".to_string(),
4640 ));
4641 }
4642
4643 if !self.set_exprs.is_empty() {
4644 return Err(crate::error::DbError::InvalidArgument(
4645 "insert_batch 不支持 set_expr;请逐条 insert/insert_returning".to_string(),
4646 ));
4647 }
4648
4649 if self.enable_logging {
4650 log::debug!(
4651 "执行 insert_batch_with_size() 操作,表: {},记录数: {},批次大小: {}",
4652 self.table,
4653 data.len(),
4654 batch_size
4655 );
4656 }
4657
4658 if data.is_empty() {
4659 return Err(crate::error::DbError::SerializationError(
4660 "批量插入数据不能为空".to_string(),
4661 ));
4662 }
4663
4664 let column_count = serde_json::to_value(&data[0])
4666 .map_err(|e| crate::error::DbError::SerializationError(format!("数据序列化失败: {e}")))?
4667 .as_object()
4668 .map(|object| object.len())
4669 .unwrap_or(0);
4670 let batch_size = derive_insert_batch_size(column_count, batch_size);
4671
4672 let chunks: Vec<&[T]> = data.chunks(batch_size).collect();
4674 if chunks.len() == 1 {
4675 let enable_logging = self.enable_logging;
4676 let affected = self.insert_chunk(chunks[0]).await?;
4677 if enable_logging {
4678 log::debug!("insert_batch_with_size() 单批完成,影响 {} 行", affected);
4679 }
4680 return Ok(affected);
4681 }
4682
4683 let mut total_affected = 0u64;
4684 match self.executor {
4685 QueryExecutor::Pool(pool) => {
4686 let mut transaction = pool.begin().await.map_err(crate::error::DbError::from)?;
4687 for (batch_index, chunk) in chunks.iter().enumerate() {
4688 let result = execute_insert_chunk(
4689 &mut transaction,
4690 &self.table,
4691 chunk,
4692 &self.field_types,
4693 )
4694 .await?;
4695 total_affected += result;
4696 if self.enable_logging {
4697 log::debug!("第 {} 批插入成功,影响 {} 行", batch_index + 1, result);
4698 }
4699 }
4700 transaction
4701 .commit()
4702 .await
4703 .map_err(crate::error::DbError::from)?;
4704 }
4705 QueryExecutor::Transaction(transaction) => {
4706 let connection = transaction.executor().ok_or_else(|| {
4707 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
4708 })?;
4709 for (batch_index, chunk) in chunks.iter().enumerate() {
4710 let result = execute_insert_chunk(
4711 &mut *connection,
4712 &self.table,
4713 chunk,
4714 &self.field_types,
4715 )
4716 .await?;
4717 total_affected += result;
4718 if self.enable_logging {
4719 log::debug!("第 {} 批插入成功,影响 {} 行", batch_index + 1, result);
4720 }
4721 }
4722 }
4723 }
4724
4725 if self.enable_logging {
4726 log::debug!(
4727 "insert_batch_with_size() 多批事务提交完成,总共影响 {} 行",
4728 total_affected
4729 );
4730 }
4731
4732 Ok(total_affected)
4733 }
4734 async fn insert_chunk<T>(self, data: &[T]) -> Result<u64, crate::error::DbError>
4737 where
4738 T: serde::Serialize,
4739 {
4740 let json_data_list: Result<Vec<_>, _> = data
4741 .iter()
4742 .map(|item| {
4743 serde_json::to_value(item).map_err(|e| {
4744 crate::error::DbError::SerializationError(format!("数据序列化失败: {}", e))
4745 })
4746 })
4747 .collect();
4748
4749 let json_data_list = json_data_list?;
4750
4751 let mut generator = SqlGenerator::new();
4752 generator.build_insert_batch(&self.table, &json_data_list, &self.field_types)?;
4753
4754 let sql = generator.get_sql();
4755 let params = generator.get_params();
4756
4757 if self.enable_logging {
4758 log::debug!("执行 insert_chunk() SQL: {}", sql);
4759 log::debug!("参数数量: {}", params.len());
4760 }
4761
4762 let mut query = sqlx::query(sql);
4763 for param in params {
4764 query = bind_execute_param(query, param);
4765 }
4766
4767 let result = match self.executor {
4768 QueryExecutor::Pool(pool) => query.execute(pool).await,
4769 QueryExecutor::Transaction(transaction) => {
4770 let connection = transaction.executor().ok_or_else(|| {
4771 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
4772 })?;
4773 query.execute(&mut *connection).await
4774 }
4775 };
4776 match result {
4777 Ok(query_result) => {
4778 let rows_affected = query_result.rows_affected();
4779 if self.enable_logging {
4780 log::debug!("insert_chunk() 成功,影响 {} 行", rows_affected);
4781 }
4782 Ok(rows_affected)
4783 }
4784 Err(e) => {
4785 let db_err = crate::error::DbError::from(e);
4787 if db_err.is_constraint_violation() {
4788 log::warn!(
4789 "insert_chunk() 失败: {}(约束冲突,可能为预期分支)",
4790 db_err
4791 );
4792 } else {
4793 log::error!("insert_chunk() 失败: {}", db_err);
4794 }
4795 Err(db_err)
4796 }
4797 }
4798 }
4799 #[tracing::instrument(
4805 name = "db.query",
4806 skip_all,
4807 fields(db.system = "postgresql", db.operation = "insert", db.collection = %self.table, otel.kind = "client")
4808 )]
4809 pub async fn insert_batch_returning<T, R>(
4810 self,
4811 data: &[T],
4812 ) -> Result<Vec<R>, crate::error::DbError>
4813 where
4814 T: serde::Serialize,
4815 R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
4816 {
4817 if !self.set_exprs.is_empty() {
4818 return Err(crate::error::DbError::InvalidArgument(
4819 "insert_batch_returning 不支持 set_expr;请逐条 insert_returning".to_string(),
4820 ));
4821 }
4822 if data.is_empty() {
4823 return Err(crate::error::DbError::SerializationError(
4824 "批量插入数据不能为空".to_string(),
4825 ));
4826 }
4827 let returning = self.returning_clause()?;
4828 let json_data_list: Vec<serde_json::Value> = data
4829 .iter()
4830 .map(|item| {
4831 serde_json::to_value(item).map_err(|error| {
4832 crate::error::DbError::SerializationError(format!("数据序列化失败: {error}"))
4833 })
4834 })
4835 .collect::<Result<_, _>>()?;
4836 let column_count = json_data_list[0]
4837 .as_object()
4838 .map(|object| object.len())
4839 .unwrap_or(0);
4840 let batch_size = derive_insert_batch_size(column_count, INSERT_BATCH_SIZE);
4841
4842 let mut rows = Vec::new();
4843 match self.executor {
4844 QueryExecutor::Pool(pool) => {
4845 let mut transaction = pool.begin().await.map_err(crate::error::DbError::from)?;
4846 for chunk in json_data_list.chunks(batch_size) {
4847 rows.extend(
4848 execute_insert_batch_returning::<R>(
4849 &mut transaction,
4850 &self.table,
4851 chunk,
4852 &self.field_types,
4853 &returning,
4854 )
4855 .await?,
4856 );
4857 }
4858 transaction
4859 .commit()
4860 .await
4861 .map_err(crate::error::DbError::from)?;
4862 }
4863 QueryExecutor::Transaction(transaction) => {
4864 let connection = transaction.executor().ok_or_else(|| {
4865 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
4866 })?;
4867 for chunk in json_data_list.chunks(batch_size) {
4868 rows.extend(
4869 execute_insert_batch_returning::<R>(
4870 &mut *connection,
4871 &self.table,
4872 chunk,
4873 &self.field_types,
4874 &returning,
4875 )
4876 .await?,
4877 );
4878 }
4879 }
4880 }
4881 Ok(rows)
4882 }
4883
4884 #[tracing::instrument(
4889 name = "db.query",
4890 skip_all,
4891 fields(db.system = "postgresql", db.operation = "upsert", db.collection = %self.table, otel.kind = "client")
4892 )]
4893 pub async fn upsert_batch_returning<T, R>(
4894 self,
4895 data: &[T],
4896 conflict_columns: &[&crate::FieldRef],
4897 ) -> Result<Vec<R>, crate::error::DbError>
4898 where
4899 T: serde::Serialize,
4900 R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
4901 {
4902 if !self.set_exprs.is_empty() {
4903 return Err(crate::error::DbError::InvalidArgument(
4904 "upsert_batch_returning 不支持 set_expr;请逐条 upsert_returning".to_string(),
4905 ));
4906 }
4907 if data.is_empty() {
4908 return Err(crate::error::DbError::SerializationError(
4909 "批量 upsert 数据不能为空".to_string(),
4910 ));
4911 }
4912 let conflict: Vec<String> = conflict_columns
4913 .iter()
4914 .map(|column| column.as_str().to_string())
4915 .collect();
4916 if conflict.is_empty() && !self.do_nothing {
4917 return Err(crate::error::DbError::InvalidArgument(
4918 "批量 upsert 必须显式指定冲突列;如使用 id 请显式传入,\
4919 或调用 do_nothing() 走 ON CONFLICT DO NOTHING"
4920 .to_string(),
4921 ));
4922 }
4923
4924 let returning = self.returning_clause()?;
4925 let json_data_list: Vec<serde_json::Value> = data
4926 .iter()
4927 .map(|item| {
4928 serde_json::to_value(item).map_err(|error| {
4929 crate::error::DbError::SerializationError(format!("数据序列化失败: {error}"))
4930 })
4931 })
4932 .collect::<Result<_, _>>()?;
4933 let column_count = json_data_list[0]
4934 .as_object()
4935 .map(|object| object.len())
4936 .unwrap_or(0);
4937 let batch_size = derive_upsert_batch_size(column_count, INSERT_BATCH_SIZE);
4938
4939 let mut rows = Vec::new();
4940 match self.executor {
4941 QueryExecutor::Pool(pool) => {
4942 let mut transaction = pool.begin().await.map_err(crate::error::DbError::from)?;
4943 for chunk in json_data_list.chunks(batch_size) {
4944 rows.extend(
4945 execute_upsert_batch_returning::<R>(
4946 &mut transaction,
4947 &self.table,
4948 chunk,
4949 &self.field_types,
4950 &conflict,
4951 self.conflict_predicate.as_deref(),
4952 self.do_nothing,
4953 self.update_columns.as_deref(),
4954 &self.upsert_where,
4955 &returning,
4956 )
4957 .await?,
4958 );
4959 }
4960 transaction
4961 .commit()
4962 .await
4963 .map_err(crate::error::DbError::from)?;
4964 }
4965 QueryExecutor::Transaction(transaction) => {
4966 let connection = transaction.executor().ok_or_else(|| {
4967 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
4968 })?;
4969 for chunk in json_data_list.chunks(batch_size) {
4970 rows.extend(
4971 execute_upsert_batch_returning::<R>(
4972 &mut *connection,
4973 &self.table,
4974 chunk,
4975 &self.field_types,
4976 &conflict,
4977 self.conflict_predicate.as_deref(),
4978 self.do_nothing,
4979 self.update_columns.as_deref(),
4980 &self.upsert_where,
4981 &returning,
4982 )
4983 .await?,
4984 );
4985 }
4986 }
4987 }
4988 Ok(rows)
4989 }
4990 #[tracing::instrument(
4995 name = "db.query",
4996 skip_all,
4997 fields(db.system = "postgresql", db.operation = "update", db.collection = %self.table, otel.kind = "client")
4998 )]
4999 pub async fn update<T>(self, data: &T) -> Result<u64, crate::error::DbError>
5000 where
5001 T: serde::Serialize,
5002 {
5003 if self.enable_logging {
5004 log::debug!("执行 update() 操作,表: {}", self.table);
5005 }
5006
5007 if self.conditions.is_empty() {
5008 log::warn!("update() 操作缺少 WHERE 条件,禁止全表更新");
5009 return Err(crate::error::DbError::MissingWhereClause);
5010 }
5011
5012 let json_data = serde_json::to_value(data).map_err(|e| {
5013 crate::error::DbError::SerializationError(format!("数据序列化失败: {}", e))
5014 })?;
5015
5016 let mut generator = SqlGenerator::new();
5017 generator.build_update(
5018 &self.table,
5019 &json_data,
5020 &self.field_types,
5021 &self.conditions,
5022 &self.set_exprs,
5023 )?;
5024
5025 let sql = generator.get_sql();
5026 let params = generator.get_params();
5027
5028 if self.enable_logging {
5029 log::debug!("执行 update() SQL: {}", sql);
5030 log::debug!("参数: {:?}", params);
5031 }
5032
5033 let mut query = sqlx::query(sql);
5034 for param in params {
5035 query = bind_execute_param(query, param);
5036 }
5037
5038 let result = match self.executor {
5039 QueryExecutor::Pool(pool) => query.execute(pool).await,
5040 QueryExecutor::Transaction(transaction) => {
5041 let connection = transaction.executor().ok_or_else(|| {
5042 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
5043 })?;
5044 query.execute(&mut *connection).await
5045 }
5046 };
5047 match result {
5048 Ok(query_result) => {
5049 let rows_affected = query_result.rows_affected();
5050 if self.enable_logging {
5051 log::debug!("update() 成功,影响 {} 行", rows_affected);
5052 }
5053 Ok(rows_affected)
5054 }
5055 Err(e) => {
5056 let db_err = crate::error::DbError::from(e);
5058 if db_err.is_constraint_violation() {
5059 log::warn!("update() 失败: {}(约束冲突,可能为预期分支)", db_err);
5060 } else {
5061 log::error!("update() 失败: {}", db_err);
5062 }
5063 Err(db_err)
5064 }
5065 }
5066 }
5067
5068 #[tracing::instrument(
5073 name = "db.query",
5074 skip_all,
5075 fields(db.system = "postgresql", db.operation = "update", db.collection = %self.table, otel.kind = "client")
5076 )]
5077 pub async fn update_returning<T, R>(self, data: &T) -> Result<Vec<R>, crate::error::DbError>
5078 where
5079 T: serde::Serialize,
5080 R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
5081 {
5082 if self.enable_logging {
5083 log::debug!("执行 update_returning() 操作,表: {}", self.table);
5084 }
5085
5086 if self.conditions.is_empty() {
5087 return Err(crate::error::DbError::MissingWhereClause);
5088 }
5089
5090 let json_data = serde_json::to_value(data).map_err(|e| {
5091 crate::error::DbError::SerializationError(format!("数据序列化失败: {}", e))
5092 })?;
5093
5094 let mut generator = SqlGenerator::new();
5095 generator.build_update(
5096 &self.table,
5097 &json_data,
5098 &self.field_types,
5099 &self.conditions,
5100 &self.set_exprs,
5101 )?;
5102 let sql = format!("{}{}", generator.get_sql(), self.returning_clause()?);
5103 let params = generator.get_params();
5104
5105 if self.enable_logging {
5106 log::debug!("执行 update_returning() SQL: {}", sql);
5107 log::debug!("参数: {:?}", params);
5108 }
5109
5110 let mut query = sqlx::query_as::<_, R>(&sql);
5111 for param in params {
5112 query = bind_param(query, param);
5113 }
5114
5115 let result = match self.executor {
5116 QueryExecutor::Pool(pool) => {
5117 let mut tx = pool.begin().await.map_err(crate::error::DbError::from)?;
5118 match query.fetch_all(&mut *tx).await {
5119 Ok(rows) => {
5120 tx.commit().await.map_err(crate::error::DbError::from)?;
5121 Ok(rows)
5122 }
5123 Err(error) => {
5124 let _ = tx.rollback().await;
5125 Err(error)
5126 }
5127 }
5128 }
5129 QueryExecutor::Transaction(transaction) => {
5130 let connection = transaction.executor().ok_or_else(|| {
5131 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
5132 })?;
5133 query.fetch_all(&mut *connection).await
5134 }
5135 };
5136 match result {
5137 Ok(rows) => Ok(rows),
5138 Err(error) => {
5139 let db_err = crate::error::DbError::from(error);
5140 if db_err.is_constraint_violation() {
5141 log::warn!(
5142 "update_returning() 失败: {}(约束冲突,可能为预期分支)",
5143 db_err
5144 );
5145 } else {
5146 log::error!("update_returning() 失败: {}", db_err);
5147 }
5148 Err(db_err)
5149 }
5150 }
5151 }
5152
5153 #[tracing::instrument(
5155 name = "db.query",
5156 skip_all,
5157 fields(db.system = "postgresql", db.operation = "update", db.collection = %self.table, otel.kind = "client")
5158 )]
5159 pub async fn increment(
5160 self,
5161 field: &crate::FieldRef,
5162 amount: i64,
5163 ) -> Result<u64, crate::error::DbError> {
5164 self.execute_arithmetic_update(field, amount, ArithmeticOperator::Add)
5165 .await
5166 }
5167
5168 #[tracing::instrument(
5170 name = "db.query",
5171 skip_all,
5172 fields(db.system = "postgresql", db.operation = "update", db.collection = %self.table, otel.kind = "client")
5173 )]
5174 pub async fn decrement(
5175 self,
5176 field: &crate::FieldRef,
5177 amount: i64,
5178 ) -> Result<u64, crate::error::DbError> {
5179 self.execute_arithmetic_update(field, amount, ArithmeticOperator::Subtract)
5180 .await
5181 }
5182
5183 async fn execute_arithmetic_update(
5184 self,
5185 field: &crate::FieldRef,
5186 amount: i64,
5187 operator: ArithmeticOperator,
5188 ) -> Result<u64, crate::error::DbError> {
5189 let mut generator = SqlGenerator::new();
5190 generator.build_arithmetic_update(
5191 &self.table,
5192 field.as_str(),
5193 operator,
5194 amount,
5195 &self.conditions,
5196 )?;
5197 let mut query = sqlx::query(generator.get_sql());
5198 for param in generator.get_params() {
5199 query = bind_execute_param(query, param);
5200 }
5201 let result = match self.executor {
5202 QueryExecutor::Pool(pool) => query.execute(pool).await,
5203 QueryExecutor::Transaction(transaction) => {
5204 let connection = transaction.executor().ok_or_else(|| {
5205 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
5206 })?;
5207 query.execute(&mut *connection).await
5208 }
5209 }?;
5210 Ok(result.rows_affected())
5211 }
5212
5213 #[tracing::instrument(
5218 name = "db.query",
5219 skip_all,
5220 fields(db.system = "postgresql", db.operation = "delete", db.collection = %self.table, otel.kind = "client")
5221 )]
5222 pub async fn delete(self) -> Result<u64, crate::error::DbError> {
5223 if self.enable_logging {
5224 log::debug!("执行 delete() 操作,表: {}", self.table);
5225 }
5226
5227 if self.conditions.is_empty() {
5228 log::warn!("delete() 操作缺少 WHERE 条件,禁止全表删除");
5229 return Err(crate::error::DbError::MissingWhereClause);
5230 }
5231
5232 let mut generator = SqlGenerator::new();
5233 generator.build_delete(&self.table, &self.conditions)?;
5234
5235 let sql = generator.get_sql();
5236 let params = generator.get_params();
5237
5238 if self.enable_logging {
5239 log::debug!("执行 delete() SQL: {}", sql);
5240 log::debug!("参数: {:?}", params);
5241 }
5242
5243 let mut query = sqlx::query(sql);
5244 for param in params {
5245 query = bind_execute_param(query, param);
5246 }
5247
5248 let result = match self.executor {
5249 QueryExecutor::Pool(pool) => query.execute(pool).await,
5250 QueryExecutor::Transaction(transaction) => {
5251 let connection = transaction.executor().ok_or_else(|| {
5252 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
5253 })?;
5254 query.execute(&mut *connection).await
5255 }
5256 };
5257 match result {
5258 Ok(query_result) => {
5259 let rows_affected = query_result.rows_affected();
5260 if self.enable_logging {
5261 log::debug!("delete() 成功,影响 {} 行", rows_affected);
5262 }
5263 Ok(rows_affected)
5264 }
5265 Err(e) => {
5266 let db_err = crate::error::DbError::from(e);
5268 if db_err.is_constraint_violation() {
5269 log::warn!("delete() 失败: {}(约束冲突,可能为预期分支)", db_err);
5270 } else {
5271 log::error!("delete() 失败: {}", db_err);
5272 }
5273 Err(db_err)
5274 }
5275 }
5276 }
5277 #[tracing::instrument(
5282 name = "db.query",
5283 skip_all,
5284 fields(db.system = "postgresql", db.operation = "delete", db.collection = %self.table, otel.kind = "client")
5285 )]
5286 pub async fn delete_returning<R>(self) -> Result<Vec<R>, crate::error::DbError>
5287 where
5288 R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
5289 {
5290 if self.enable_logging {
5291 log::debug!("执行 delete_returning() 操作,表: {}", self.table);
5292 }
5293
5294 if self.conditions.is_empty() {
5295 return Err(crate::error::DbError::MissingWhereClause);
5296 }
5297
5298 let mut generator = SqlGenerator::new();
5299 generator.build_delete(&self.table, &self.conditions)?;
5300 let sql = format!("{}{}", generator.get_sql(), self.returning_clause()?);
5301 let params = generator.get_params();
5302
5303 if self.enable_logging {
5304 log::debug!("执行 delete_returning() SQL: {}", sql);
5305 log::debug!("参数: {:?}", params);
5306 }
5307
5308 let mut query = sqlx::query_as::<_, R>(&sql);
5309 for param in params {
5310 query = bind_param(query, param);
5311 }
5312
5313 let result = match self.executor {
5314 QueryExecutor::Pool(pool) => {
5315 let mut tx = pool.begin().await.map_err(crate::error::DbError::from)?;
5316 match query.fetch_all(&mut *tx).await {
5317 Ok(rows) => {
5318 tx.commit().await.map_err(crate::error::DbError::from)?;
5319 Ok(rows)
5320 }
5321 Err(error) => {
5322 let _ = tx.rollback().await;
5323 Err(error)
5324 }
5325 }
5326 }
5327 QueryExecutor::Transaction(transaction) => {
5328 let connection = transaction.executor().ok_or_else(|| {
5329 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
5330 })?;
5331 query.fetch_all(&mut *connection).await
5332 }
5333 };
5334 match result {
5335 Ok(rows) => Ok(rows),
5336 Err(error) => {
5337 let db_err = crate::error::DbError::from(error);
5338 if db_err.is_constraint_violation() {
5339 log::warn!(
5340 "delete_returning() 失败: {}(约束冲突,可能为预期分支)",
5341 db_err
5342 );
5343 } else {
5344 log::error!("delete_returning() 失败: {}", db_err);
5345 }
5346 Err(db_err)
5347 }
5348 }
5349 }
5350
5351 #[tracing::instrument(
5356 name = "db.query",
5357 skip_all,
5358 fields(db.system = "postgresql", db.operation = "insert", db.collection = %self.table, otel.kind = "client")
5359 )]
5360 pub async fn insert_from_select(
5361 self,
5362 columns: &[&crate::FieldRef],
5363 source: QueryBuilder<'a>,
5364 ) -> Result<u64, crate::error::DbError> {
5365 let mut generator = SqlGenerator::new();
5366 generator.build_insert_from_select(&self.table, columns, &source)?;
5367 if self.enable_logging {
5368 log::debug!("执行 insert_from_select() SQL: {}", generator.get_sql());
5369 }
5370 execute_generated(self.executor, generator.get_sql(), generator.get_params()).await
5371 }
5372
5373 #[tracing::instrument(
5378 name = "db.query",
5379 skip_all,
5380 fields(db.system = "postgresql", db.operation = "update", db.collection = %self.table, otel.kind = "client")
5381 )]
5382 pub async fn update_from<T>(
5383 self,
5384 data: &T,
5385 from: &crate::TableRef,
5386 target_field: &crate::FieldRef,
5387 source_field: &crate::FieldRef,
5388 ) -> Result<u64, crate::error::DbError>
5389 where
5390 T: serde::Serialize,
5391 {
5392 if self.conditions.is_empty() {
5393 return Err(crate::error::DbError::MissingWhereClause);
5394 }
5395 let json_data = serde_json::to_value(data).map_err(|error| {
5396 crate::error::DbError::SerializationError(format!("数据序列化失败: {error}"))
5397 })?;
5398 let mut generator = SqlGenerator::new();
5399 generator.build_update_from(
5400 &self.table,
5401 &json_data,
5402 &self.field_types,
5403 &self.set_exprs,
5404 &self.conditions,
5405 from,
5406 target_field,
5407 source_field,
5408 )?;
5409 if self.enable_logging {
5410 log::debug!("执行 update_from() SQL: {}", generator.get_sql());
5411 }
5412 execute_generated(self.executor, generator.get_sql(), generator.get_params()).await
5413 }
5414
5415 #[tracing::instrument(
5420 name = "db.query",
5421 skip_all,
5422 fields(db.system = "postgresql", db.operation = "delete", db.collection = %self.table, otel.kind = "client")
5423 )]
5424 pub async fn delete_using(
5425 self,
5426 using: &crate::TableRef,
5427 target_field: &crate::FieldRef,
5428 source_field: &crate::FieldRef,
5429 ) -> Result<u64, crate::error::DbError> {
5430 if self.conditions.is_empty() {
5431 return Err(crate::error::DbError::MissingWhereClause);
5432 }
5433 let mut generator = SqlGenerator::new();
5434 generator.build_delete_using(
5435 &self.table,
5436 &self.conditions,
5437 using,
5438 target_field,
5439 source_field,
5440 )?;
5441 if self.enable_logging {
5442 log::debug!("执行 delete_using() SQL: {}", generator.get_sql());
5443 }
5444 execute_generated(self.executor, generator.get_sql(), generator.get_params()).await
5445 }
5446
5447 #[tracing::instrument(
5453 name = "db.query",
5454 skip_all,
5455 fields(db.system = "postgresql", db.operation = "update", db.collection = %self.table, otel.kind = "client")
5456 )]
5457 pub async fn update_batch<T>(
5458 self,
5459 records: &[T],
5460 where_field: &crate::FieldRef,
5461 ) -> Result<u64, crate::error::DbError>
5462 where
5463 T: serde::Serialize,
5464 {
5465 self.update_batch_with_size(records, where_field, UPDATE_BATCH_SIZE)
5466 .await
5467 }
5468
5469 #[tracing::instrument(
5474 name = "db.query",
5475 skip_all,
5476 fields(db.system = "postgresql", db.operation = "update", db.collection = %self.table, otel.kind = "client")
5477 )]
5478 pub async fn update_batch_with_size<T>(
5479 self,
5480 records: &[T],
5481 where_field: &crate::FieldRef,
5482 batch_size: usize,
5483 ) -> Result<u64, crate::error::DbError>
5484 where
5485 T: serde::Serialize,
5486 {
5487 if batch_size == 0 {
5488 return Err(crate::error::DbError::SerializationError(
5489 "batch_size 不能为 0".to_string(),
5490 ));
5491 }
5492
5493 if records.is_empty() {
5494 return Err(crate::error::DbError::SerializationError(
5495 "批量更新数据不能为空".to_string(),
5496 ));
5497 }
5498
5499 if !self.set_exprs.is_empty() {
5501 return Err(crate::error::DbError::InvalidArgument(
5502 "update_batch 不支持 set_expr();请改用逐条 update() 或在数据中提供字面量"
5503 .to_string(),
5504 ));
5505 }
5506
5507 let json_records: Vec<serde_json::Value> = records
5508 .iter()
5509 .map(|r| {
5510 serde_json::to_value(r).map_err(|e| {
5511 crate::error::DbError::SerializationError(format!("数据序列化失败: {}", e))
5512 })
5513 })
5514 .collect::<Result<_, _>>()?;
5515
5516 let field_count = update_field_count(&json_records, where_field.as_str());
5518 let per_record = field_count.saturating_mul(2).saturating_add(1);
5519 if per_record > MAX_BIND_PARAMS {
5520 return Err(crate::error::DbError::InvalidArgument(format!(
5521 "批量更新单行需要 {per_record} 个占位符,超过协议上限 {MAX_BIND_PARAMS},请减少更新列数"
5522 )));
5523 }
5524 let batch_size = derive_update_batch_size(field_count, batch_size);
5525
5526 let mut total = 0u64;
5527 match self.executor {
5528 QueryExecutor::Pool(pool) => {
5529 let mut transaction = pool.begin().await.map_err(crate::error::DbError::from)?;
5530 for chunk in json_records.chunks(batch_size) {
5531 total += execute_update_chunk(
5532 &mut transaction,
5533 &self.table,
5534 chunk,
5535 where_field,
5536 &self.field_types,
5537 )
5538 .await?;
5539 }
5540 transaction
5541 .commit()
5542 .await
5543 .map_err(crate::error::DbError::from)?;
5544 }
5545 QueryExecutor::Transaction(transaction) => {
5546 let connection = transaction.executor().ok_or_else(|| {
5547 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
5548 })?;
5549 for chunk in json_records.chunks(batch_size) {
5550 total += execute_update_chunk(
5551 &mut *connection,
5552 &self.table,
5553 chunk,
5554 where_field,
5555 &self.field_types,
5556 )
5557 .await?;
5558 }
5559 }
5560 }
5561 Ok(total)
5562 }
5563
5564 #[tracing::instrument(
5572 name = "db.query",
5573 skip_all,
5574 fields(db.system = "postgresql", db.operation = "upsert", db.collection = %self.table, otel.kind = "client")
5575 )]
5576 pub async fn upsert<T>(self, data: &T) -> Result<u64, crate::error::DbError>
5577 where
5578 T: serde::Serialize,
5579 {
5580 if !self.set_exprs.is_empty() {
5581 return Err(crate::error::DbError::InvalidArgument(
5582 "upsert 不支持 set_expr;请在插入数据中直接提供列值".to_string(),
5583 ));
5584 }
5585 if self.enable_logging {
5586 log::debug!("执行 upsert() 操作,表: {}", self.table);
5587 }
5588
5589 let json_data = serde_json::to_value(data).map_err(|e| {
5590 crate::error::DbError::SerializationError(format!("数据序列化失败: {}", e))
5591 })?;
5592
5593 let mut generator = SqlGenerator::new();
5594 generator.build_upsert(
5595 &self.table,
5596 &json_data,
5597 &self.field_types,
5598 &self.conflict_target,
5599 self.conflict_predicate.as_deref(),
5600 self.do_nothing,
5601 self.update_columns.as_deref(),
5602 &self.upsert_where,
5603 )?;
5604
5605 let sql = generator.get_sql();
5606 let params = generator.get_params();
5607
5608 if self.enable_logging {
5609 log::debug!("执行 upsert() SQL: {}", sql);
5610 }
5611
5612 let mut query = sqlx::query(sql);
5613 for param in params {
5614 query = bind_execute_param(query, param);
5615 }
5616
5617 let result = match self.executor {
5618 QueryExecutor::Pool(pool) => query.execute(pool).await,
5619 QueryExecutor::Transaction(transaction) => {
5620 let connection = transaction.executor().ok_or_else(|| {
5621 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
5622 })?;
5623 query.execute(&mut *connection).await
5624 }
5625 }
5626 .map_err(crate::error::DbError::from)?;
5627 let rows = result.rows_affected();
5628
5629 if self.enable_logging {
5630 log::debug!("upsert() 完成,rows_affected: {}", rows);
5631 }
5632
5633 Ok(rows)
5634 }
5635
5636 #[tracing::instrument(
5641 name = "db.query",
5642 skip_all,
5643 fields(db.system = "postgresql", db.operation = "upsert", db.collection = %self.table, otel.kind = "client")
5644 )]
5645 pub async fn upsert_returning<T, R>(self, data: &T) -> Result<R, crate::error::DbError>
5646 where
5647 T: serde::Serialize,
5648 R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
5649 {
5650 if !self.set_exprs.is_empty() {
5651 return Err(crate::error::DbError::InvalidArgument(
5652 "upsert 不支持 set_expr;请在插入数据中直接提供列值".to_string(),
5653 ));
5654 }
5655 if self.enable_logging {
5656 log::debug!("执行 upsert_returning() 操作,表: {}", self.table);
5657 }
5658
5659 let json_data = serde_json::to_value(data).map_err(|e| {
5660 crate::error::DbError::SerializationError(format!("数据序列化失败: {}", e))
5661 })?;
5662
5663 let mut generator = SqlGenerator::new();
5664 generator.build_upsert(
5665 &self.table,
5666 &json_data,
5667 &self.field_types,
5668 &self.conflict_target,
5669 self.conflict_predicate.as_deref(),
5670 self.do_nothing,
5671 self.update_columns.as_deref(),
5672 &self.upsert_where,
5673 )?;
5674 let sql = format!("{}{}", generator.get_sql(), self.returning_clause()?);
5675 let params = generator.get_params();
5676
5677 if self.enable_logging {
5678 log::debug!("执行 upsert_returning() SQL: {}", sql);
5679 }
5680
5681 let mut query = sqlx::query_as::<_, R>(&sql);
5682 for param in params {
5683 query = bind_param(query, param);
5684 }
5685
5686 let result = match self.executor {
5687 QueryExecutor::Pool(pool) => {
5688 let mut tx = pool.begin().await.map_err(crate::error::DbError::from)?;
5689 match query.fetch_one(&mut *tx).await {
5690 Ok(row) => {
5691 tx.commit().await.map_err(crate::error::DbError::from)?;
5692 Ok(row)
5693 }
5694 Err(error) => {
5695 let _ = tx.rollback().await;
5696 Err(error)
5697 }
5698 }
5699 }
5700 QueryExecutor::Transaction(transaction) => {
5701 let connection = transaction.executor().ok_or_else(|| {
5702 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
5703 })?;
5704 query.fetch_one(&mut *connection).await
5705 }
5706 };
5707 result.map_err(|error| {
5708 let db_err = crate::error::DbError::from(error);
5709 if db_err.is_constraint_violation() {
5710 log::warn!(
5711 "upsert_returning() 失败: {}(约束冲突,可能为预期分支)",
5712 db_err
5713 );
5714 } else {
5715 log::error!("upsert_returning() 失败: {}", db_err);
5716 }
5717 db_err
5718 })
5719 }
5720
5721 #[tracing::instrument(
5729 name = "db.query",
5730 skip_all,
5731 fields(db.system = "postgresql", db.operation = "upsert", db.collection = %self.table, otel.kind = "client")
5732 )]
5733 pub async fn upsert_batch<T>(
5734 self,
5735 data: &[T],
5736 conflict_columns: &[&crate::FieldRef],
5737 ) -> Result<u64, crate::error::DbError>
5738 where
5739 T: serde::Serialize,
5740 {
5741 self.upsert_batch_with_size(data, conflict_columns, INSERT_BATCH_SIZE)
5742 .await
5743 }
5744
5745 #[tracing::instrument(
5750 name = "db.query",
5751 skip_all,
5752 fields(db.system = "postgresql", db.operation = "upsert", db.collection = %self.table, otel.kind = "client")
5753 )]
5754 pub async fn upsert_batch_with_size<T>(
5755 self,
5756 data: &[T],
5757 conflict_columns: &[&crate::FieldRef],
5758 batch_size: usize,
5759 ) -> Result<u64, crate::error::DbError>
5760 where
5761 T: serde::Serialize,
5762 {
5763 if batch_size == 0 {
5764 return Err(crate::error::DbError::SerializationError(
5765 "batch_size 不能为 0".to_string(),
5766 ));
5767 }
5768 if !self.set_exprs.is_empty() {
5769 return Err(crate::error::DbError::InvalidArgument(
5770 "upsert_batch 不支持 set_expr;请改用 upsert() 或 update()".to_string(),
5771 ));
5772 }
5773 if data.is_empty() {
5774 return Err(crate::error::DbError::SerializationError(
5775 "批量 upsert 数据不能为空".to_string(),
5776 ));
5777 }
5778
5779 let conflict: Vec<String> = conflict_columns
5780 .iter()
5781 .map(|column| column.as_str().to_string())
5782 .collect();
5783 if conflict.is_empty() && !self.do_nothing {
5784 return Err(crate::error::DbError::InvalidArgument(
5785 "批量 upsert 必须显式指定冲突列;如使用 id 请显式传入,\
5786 或调用 do_nothing() 走 ON CONFLICT DO NOTHING"
5787 .to_string(),
5788 ));
5789 }
5790
5791 let json_data_list: Vec<serde_json::Value> = data
5792 .iter()
5793 .map(|item| {
5794 serde_json::to_value(item).map_err(|error| {
5795 crate::error::DbError::SerializationError(format!("数据序列化失败: {error}"))
5796 })
5797 })
5798 .collect::<Result<_, _>>()?;
5799
5800 let column_count = json_data_list[0]
5801 .as_object()
5802 .map(|object| object.len())
5803 .unwrap_or(0);
5804 let effective = derive_upsert_batch_size(column_count, batch_size);
5805 let chunk_count = json_data_list.len().div_ceil(effective);
5806
5807 let mut total = 0u64;
5808 if chunk_count == 1 {
5809 match self.executor {
5810 QueryExecutor::Pool(pool) => {
5811 let mut connection =
5812 pool.acquire().await.map_err(crate::error::DbError::from)?;
5813 total += execute_upsert_batch_chunk(
5814 &mut connection,
5815 &self.table,
5816 &json_data_list,
5817 &self.field_types,
5818 &conflict,
5819 self.conflict_predicate.as_deref(),
5820 self.do_nothing,
5821 self.update_columns.as_deref(),
5822 &self.upsert_where,
5823 )
5824 .await?;
5825 }
5826 QueryExecutor::Transaction(transaction) => {
5827 let connection = transaction.executor().ok_or_else(|| {
5828 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
5829 })?;
5830 total += execute_upsert_batch_chunk(
5831 &mut *connection,
5832 &self.table,
5833 &json_data_list,
5834 &self.field_types,
5835 &conflict,
5836 self.conflict_predicate.as_deref(),
5837 self.do_nothing,
5838 self.update_columns.as_deref(),
5839 &self.upsert_where,
5840 )
5841 .await?;
5842 }
5843 }
5844 return Ok(total);
5845 }
5846
5847 match self.executor {
5848 QueryExecutor::Pool(pool) => {
5849 let mut transaction = pool.begin().await.map_err(crate::error::DbError::from)?;
5850 for chunk in json_data_list.chunks(effective) {
5851 total += execute_upsert_batch_chunk(
5852 &mut transaction,
5853 &self.table,
5854 chunk,
5855 &self.field_types,
5856 &conflict,
5857 self.conflict_predicate.as_deref(),
5858 self.do_nothing,
5859 self.update_columns.as_deref(),
5860 &self.upsert_where,
5861 )
5862 .await?;
5863 }
5864 transaction
5865 .commit()
5866 .await
5867 .map_err(crate::error::DbError::from)?;
5868 }
5869 QueryExecutor::Transaction(transaction) => {
5870 let connection = transaction.executor().ok_or_else(|| {
5871 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
5872 })?;
5873 for chunk in json_data_list.chunks(effective) {
5874 total += execute_upsert_batch_chunk(
5875 &mut *connection,
5876 &self.table,
5877 chunk,
5878 &self.field_types,
5879 &conflict,
5880 self.conflict_predicate.as_deref(),
5881 self.do_nothing,
5882 self.update_columns.as_deref(),
5883 &self.upsert_where,
5884 )
5885 .await?;
5886 }
5887 }
5888 }
5889
5890 if self.enable_logging {
5891 log::debug!("upsert_batch_with_size() 完成,rows_affected: {total}");
5892 }
5893 Ok(total)
5894 }
5895
5896 #[tracing::instrument(
5909 name = "db.query",
5910 skip_all,
5911 fields(db.system = "postgresql", db.operation = "merge", db.collection = %self.table, otel.kind = "client")
5912 )]
5913 pub async fn merge(
5914 self,
5915 source: &crate::TableRef,
5916 target_key: &crate::FieldRef,
5917 source_key: &crate::FieldRef,
5918 columns: &[&crate::FieldRef],
5919 ) -> Result<u64, crate::error::DbError> {
5920 self.merge_with_options(
5921 source,
5922 target_key,
5923 source_key,
5924 columns,
5925 MergeOptions::default(),
5926 )
5927 .await
5928 }
5929
5930 #[tracing::instrument(
5937 name = "db.query",
5938 skip_all,
5939 fields(db.system = "postgresql", db.operation = "merge", db.collection = %self.table, otel.kind = "client")
5940 )]
5941 pub async fn merge_with_options(
5942 mut self,
5943 source: &crate::TableRef,
5944 target_key: &crate::FieldRef,
5945 source_key: &crate::FieldRef,
5946 columns: &[&crate::FieldRef],
5947 options: MergeOptions,
5948 ) -> Result<u64, crate::error::DbError> {
5949 if !self.conditions.is_empty() || !self.set_exprs.is_empty() {
5950 return Err(crate::error::DbError::InvalidArgument(
5951 "merge 不支持 where / set_expr;匹配条件请放入 ON 参数".to_string(),
5952 ));
5953 }
5954 let version = server_version_num(&mut self.executor).await?;
5955 if !merge_version_supported(version) {
5956 return Err(crate::error::DbError::UnsupportedOperator(format!(
5957 "MERGE 需要 PostgreSQL 15+(server_version_num >= {MERGE_MIN_SERVER_VERSION}),当前为 {version}"
5958 )));
5959 }
5960 if options.not_matched_by_source.is_some() && version < MERGE_BY_SOURCE_MIN_SERVER_VERSION {
5961 return Err(crate::error::DbError::UnsupportedOperator(format!(
5962 "WHEN NOT MATCHED BY SOURCE 需要 PostgreSQL 17+(server_version_num >= {MERGE_BY_SOURCE_MIN_SERVER_VERSION}),当前为 {version}"
5963 )));
5964 }
5965 let mut generator = SqlGenerator::new();
5966 generator.build_merge_with_options(
5967 &self.table,
5968 source,
5969 target_key,
5970 source_key,
5971 columns,
5972 &options,
5973 )?;
5974 if self.enable_logging {
5975 log::debug!("执行 merge_with_options() SQL: {}", generator.get_sql());
5976 }
5977 execute_generated(self.executor, generator.get_sql(), generator.get_params()).await
5978 }
5979}
5980
5981#[allow(clippy::too_many_arguments)]
5983async fn execute_upsert_batch_chunk(
5984 connection: &mut sqlx::PgConnection,
5985 table: &str,
5986 data: &[serde_json::Value],
5987 field_types: &HashMap<String, FieldType>,
5988 conflict_columns: &[String],
5989 conflict_predicate: Option<&str>,
5990 do_nothing: bool,
5991 update_columns: Option<&[String]>,
5992 update_where: &[Condition],
5993) -> Result<u64, crate::error::DbError> {
5994 let mut generator = SqlGenerator::new();
5995 generator.build_upsert_batch(
5996 table,
5997 data,
5998 field_types,
5999 conflict_columns,
6000 conflict_predicate,
6001 do_nothing,
6002 update_columns,
6003 update_where,
6004 )?;
6005 let mut query = sqlx::query(generator.get_sql());
6006 for param in generator.get_params() {
6007 query = bind_execute_param(query, param);
6008 }
6009 Ok(query.execute(connection).await?.rows_affected())
6010}
6011
6012fn array_element_bool(value: &SqlValue) -> Option<bool> {
6015 match value {
6016 SqlValue::Bool(value) => Some(*value),
6017 _ => None,
6018 }
6019}
6020
6021fn array_element_i64(value: &SqlValue) -> Option<i64> {
6022 match value {
6023 SqlValue::Int(value) => Some(*value),
6024 _ => None,
6025 }
6026}
6027
6028fn array_element_i16(value: &SqlValue) -> Option<i16> {
6029 array_element_i64(value).and_then(|value| i16::try_from(value).ok())
6030}
6031
6032fn array_element_i32(value: &SqlValue) -> Option<i32> {
6033 array_element_i64(value).and_then(|value| i32::try_from(value).ok())
6034}
6035
6036fn array_element_f32(value: &SqlValue) -> Option<f32> {
6037 match value {
6038 SqlValue::Float(value) => Some(*value as f32),
6039 SqlValue::Int(value) => Some(*value as f32),
6040 _ => None,
6041 }
6042}
6043
6044fn array_element_bytes(value: &SqlValue) -> Option<Vec<u8>> {
6045 match value {
6046 SqlValue::Bytes(value) => Some(value.clone()),
6047 _ => None,
6048 }
6049}
6050
6051fn array_element_json(value: &SqlValue) -> Option<serde_json::Value> {
6052 match value {
6053 SqlValue::Json(value) => Some(value.clone()),
6054 _ => None,
6055 }
6056}
6057
6058fn array_element_f64(value: &SqlValue) -> Option<f64> {
6059 match value {
6060 SqlValue::Float(value) => Some(*value),
6061 _ => None,
6062 }
6063}
6064
6065fn array_element_string(value: &SqlValue) -> Option<String> {
6066 match value {
6067 SqlValue::String(value)
6068 | SqlValue::Inet(value)
6069 | SqlValue::Cidr(value)
6070 | SqlValue::MacAddr(value)
6071 | SqlValue::MacAddr8(value)
6072 | SqlValue::Range(value)
6073 | SqlValue::Multirange(value) => Some(value.clone()),
6074 _ => None,
6075 }
6076}
6077
6078fn array_element_timestamp(value: &SqlValue) -> Option<i64> {
6079 match value {
6080 SqlValue::Timestamp(value) => Some(*value),
6081 _ => None,
6082 }
6083}
6084
6085fn array_element_uuid(value: &SqlValue) -> Option<uuid::Uuid> {
6086 match value {
6087 SqlValue::Uuid(value) => Some(*value),
6088 _ => None,
6089 }
6090}
6091
6092fn array_element_date(value: &SqlValue) -> Option<chrono::NaiveDate> {
6093 match value {
6094 SqlValue::Date(value) => Some(*value),
6095 _ => None,
6096 }
6097}
6098
6099fn array_element_time(value: &SqlValue) -> Option<chrono::NaiveTime> {
6100 match value {
6101 SqlValue::Time(value) => Some(*value),
6102 _ => None,
6103 }
6104}
6105
6106fn array_element_datetime(value: &SqlValue) -> Option<chrono::NaiveDateTime> {
6107 match value {
6108 SqlValue::DateTime(value) => Some(*value),
6109 _ => None,
6110 }
6111}
6112
6113fn array_element_datetime_tz(value: &SqlValue) -> Option<chrono::DateTime<chrono::Utc>> {
6114 match value {
6115 SqlValue::DateTimeTz(value) => Some(*value),
6116 _ => None,
6117 }
6118}
6119
6120fn array_element_decimal(value: &SqlValue) -> Option<bigdecimal::BigDecimal> {
6121 match value {
6122 SqlValue::Decimal(value) => Some(value.clone()),
6123 _ => None,
6124 }
6125}
6126
6127fn bind_execute_param<'q>(
6132 query: sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments>,
6133 param: &SqlValue,
6134) -> sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments> {
6135 bind_value_match!(query, param)
6136}
6137
6138pub(crate) fn bind_param<'q, T>(
6143 query: sqlx::query::QueryAs<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>,
6144 param: &SqlValue,
6145) -> sqlx::query::QueryAs<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>
6146where
6147 T: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
6148{
6149 bind_value_match!(query, param)
6150}
6151
6152fn bind_scalar_param<'q, C>(
6156 query: sqlx::query::QueryScalar<'q, sqlx::Postgres, C, sqlx::postgres::PgArguments>,
6157 param: &SqlValue,
6158) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, C, sqlx::postgres::PgArguments>
6159where
6160 C: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
6161{
6162 bind_value_match!(query, param)
6163}
6164
6165#[allow(dead_code)]
6170fn bind_scalar_param_option<'q, T>(
6171 query: sqlx::query::QueryScalar<'q, sqlx::Postgres, Option<T>, sqlx::postgres::PgArguments>,
6172 param: &SqlValue,
6173) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, Option<T>, sqlx::postgres::PgArguments>
6174where
6175 T: for<'r> sqlx::Decode<'r, sqlx::Postgres> + sqlx::Type<sqlx::Postgres> + Send + Unpin,
6176{
6177 bind_value_match!(query, param)
6178}
6179
6180async fn execute_generated(
6186 executor: QueryExecutor<'_>,
6187 sql: &str,
6188 params: &[SqlValue],
6189) -> Result<u64, crate::error::DbError> {
6190 let mut query = sqlx::query(sql);
6191 for param in params {
6192 query = bind_execute_param(query, param);
6193 }
6194 let result = match executor {
6195 QueryExecutor::Pool(pool) => query.execute(pool).await,
6196 QueryExecutor::Transaction(transaction) => {
6197 let connection = transaction.executor().ok_or_else(|| {
6198 crate::error::DbError::TransactionError("事务已提交或回滚".to_string())
6199 })?;
6200 query.execute(&mut *connection).await
6201 }
6202 }
6203 .map_err(crate::error::DbError::from)?;
6204 Ok(result.rows_affected())
6205}
6206
6207async fn execute_insert_chunk<T>(
6208 connection: &mut sqlx::PgConnection,
6209 table: &str,
6210 data: &[T],
6211 field_types: &HashMap<String, FieldType>,
6212) -> Result<u64, crate::error::DbError>
6213where
6214 T: serde::Serialize,
6215{
6216 let json_data_list = data
6217 .iter()
6218 .map(|item| {
6219 serde_json::to_value(item).map_err(|error| {
6220 crate::error::DbError::SerializationError(format!("数据序列化失败: {error}"))
6221 })
6222 })
6223 .collect::<Result<Vec<_>, _>>()?;
6224 let mut generator = SqlGenerator::new();
6225 generator.build_insert_batch(table, &json_data_list, field_types)?;
6226 let mut query = sqlx::query(generator.get_sql());
6227 for param in generator.get_params() {
6228 query = bind_execute_param(query, param);
6229 }
6230 Ok(query.execute(connection).await?.rows_affected())
6231}
6232
6233async fn execute_insert_batch_returning<R>(
6234 connection: &mut sqlx::PgConnection,
6235 table: &str,
6236 data: &[serde_json::Value],
6237 field_types: &HashMap<String, FieldType>,
6238 returning: &str,
6239) -> Result<Vec<R>, crate::error::DbError>
6240where
6241 R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
6242{
6243 let mut generator = SqlGenerator::new();
6244 generator.build_insert_batch(table, data, field_types)?;
6245 let sql = format!("{}{}", generator.get_sql(), returning);
6246 let mut query = sqlx::query_as::<_, R>(&sql);
6247 for param in generator.get_params() {
6248 query = bind_param(query, param);
6249 }
6250 Ok(query.fetch_all(connection).await?)
6251}
6252
6253#[allow(clippy::too_many_arguments)]
6255async fn execute_upsert_batch_returning<R>(
6256 connection: &mut sqlx::PgConnection,
6257 table: &str,
6258 data: &[serde_json::Value],
6259 field_types: &HashMap<String, FieldType>,
6260 conflict_columns: &[String],
6261 conflict_predicate: Option<&str>,
6262 do_nothing: bool,
6263 update_columns: Option<&[String]>,
6264 update_where: &[Condition],
6265 returning: &str,
6266) -> Result<Vec<R>, crate::error::DbError>
6267where
6268 R: for<'r> sqlx::FromRow<'r, sqlx::postgres::PgRow> + Send + Unpin,
6269{
6270 let mut generator = SqlGenerator::new();
6271 generator.build_upsert_batch(
6272 table,
6273 data,
6274 field_types,
6275 conflict_columns,
6276 conflict_predicate,
6277 do_nothing,
6278 update_columns,
6279 update_where,
6280 )?;
6281 let sql = format!("{}{}", generator.get_sql(), returning);
6282 let mut query = sqlx::query_as::<_, R>(&sql);
6283 for param in generator.get_params() {
6284 query = bind_param(query, param);
6285 }
6286 Ok(query.fetch_all(connection).await?)
6287}
6288
6289async fn execute_update_chunk(
6290 connection: &mut sqlx::PgConnection,
6291 table: &str,
6292 records: &[serde_json::Value],
6293 where_field: &crate::FieldRef,
6294 field_types: &HashMap<String, FieldType>,
6295) -> Result<u64, crate::error::DbError> {
6296 let mut generator = SqlGenerator::new();
6297 generator.build_update_batch(table, records, where_field.as_str(), field_types)?;
6298 let mut query = sqlx::query(generator.get_sql());
6299 for param in generator.get_params() {
6300 query = bind_execute_param(query, param);
6301 }
6302 Ok(query.execute(connection).await?.rows_affected())
6303}
6304
6305fn predicate_condition(predicate: &crate::Predicate) -> Result<Condition, crate::error::DbError> {
6306 let condition = match predicate {
6307 crate::Predicate::Compare(field, operator, value) => {
6308 comparison_condition(field.as_str(), *operator, predicate_value(value))?
6309 }
6310 crate::Predicate::In(field, values) => Condition::In(
6311 field.as_str().to_string(),
6312 values.iter().map(predicate_value).collect(),
6313 ),
6314 crate::Predicate::NotIn(field, values) => Condition::NotIn(
6315 field.as_str().to_string(),
6316 values.iter().map(predicate_value).collect(),
6317 ),
6318 crate::Predicate::Between(field, start, end) => Condition::Between(
6319 field.as_str().to_string(),
6320 predicate_value(start),
6321 predicate_value(end),
6322 ),
6323 crate::Predicate::IsNull(field) => Condition::IsNull(field.as_str().to_string()),
6324 crate::Predicate::IsNotNull(field) => Condition::IsNotNull(field.as_str().to_string()),
6325 crate::Predicate::And(values) => Condition::And(
6326 values
6327 .iter()
6328 .map(predicate_condition)
6329 .collect::<Result<Vec<_>, _>>()?,
6330 ),
6331 crate::Predicate::Or(values) => Condition::Or(
6332 values
6333 .iter()
6334 .map(predicate_condition)
6335 .collect::<Result<Vec<_>, _>>()?,
6336 ),
6337 };
6338 Ok(condition)
6339}
6340
6341fn predicate_value(value: &serde_json::Value) -> SqlValue {
6342 match value {
6343 serde_json::Value::Null => SqlValue::Null,
6344 serde_json::Value::Bool(value) => SqlValue::Bool(*value),
6345 serde_json::Value::Number(value) => {
6346 value.as_i64().map(SqlValue::Int).unwrap_or_else(|| {
6347 value
6348 .as_u64()
6349 .map(SqlValue::from)
6350 .or_else(|| value.as_f64().map(SqlValue::Float))
6351 .unwrap_or_else(|| SqlValue::String(value.to_string()))
6352 })
6353 }
6354 serde_json::Value::String(value) => SqlValue::String(value.clone()),
6355 serde_json::Value::Array(_) | serde_json::Value::Object(_) => SqlValue::Json(value.clone()),
6356 }
6357}
6358
6359#[cfg(test)]
6360mod predicate_scalar_tests {
6361 use super::{predicate_value, SqlValue};
6362
6363 #[test]
6364 fn controlled_predicates_bind_json_scalars_as_sql_scalars() {
6365 assert!(matches!(
6366 predicate_value(&serde_json::json!("alice")),
6367 SqlValue::String(value) if value == "alice"
6368 ));
6369 assert!(matches!(
6370 predicate_value(&serde_json::json!(42)),
6371 SqlValue::Int(42)
6372 ));
6373 assert!(matches!(
6374 predicate_value(&serde_json::json!(true)),
6375 SqlValue::Bool(true)
6376 ));
6377 assert!(matches!(
6378 predicate_value(&serde_json::json!({"role": "admin"})),
6379 SqlValue::Json(_)
6380 ));
6381 }
6382}
6383#[cfg(test)]
6386#[allow(deprecated)]
6387#[allow(clippy::unwrap_used, clippy::expect_used)]
6388mod tests {
6389 use super::*;
6390 use crate::postgres::Subquery;
6391 use sqlx::postgres::PgPoolOptions;
6392
6393 fn make_sync_test_pool() -> &'static PgPool {
6398 use std::sync::OnceLock;
6399 static RT: OnceLock<tokio::runtime::Runtime> = OnceLock::new();
6400 static POOL: OnceLock<PgPool> = OnceLock::new();
6401 let rt = RT.get_or_init(|| {
6402 tokio::runtime::Builder::new_multi_thread()
6403 .enable_all()
6404 .build()
6405 .expect("无法创建测试用 Tokio 运行时")
6406 });
6407 POOL.get_or_init(|| {
6408 rt.block_on(async {
6409 PgPoolOptions::new()
6410 .max_connections(1)
6411 .connect_lazy("postgres://postgres:postgres@localhost:5432/test")
6412 .expect("无法解析测试数据库 URL")
6413 })
6414 })
6415 }
6416
6417 #[test]
6418 fn test_controlled_subqueries_preserve_nested_placeholder_order() {
6419 let pool = make_sync_test_pool();
6420 let paid_order = Subquery::new("orders", "id")
6421 .expect("合法子查询")
6422 .where_column("orders.user_id", "=", "users.id")
6423 .expect("合法关联列")
6424 .where_value("orders.status", "=", "paid")
6425 .expect("合法绑定条件");
6426 let active_user = Subquery::new("memberships", "user_id")
6427 .expect("合法子查询")
6428 .where_value("memberships.active", "=", true)
6429 .expect("合法绑定条件");
6430 let banned_user = Subquery::new("bans", "id")
6431 .expect("合法子查询")
6432 .where_column("bans.user_id", "=", "users.id")
6433 .expect("合法关联列")
6434 .where_value("bans.reason", "=", "fraud")
6435 .expect("合法绑定条件");
6436
6437 let builder = QueryBuilder::new(pool, "users", false)
6438 .where_and(
6439 yang_db::field!("users.tenant_id"),
6440 yang_db::CompareOp::Eq,
6441 7,
6442 )
6443 .expect("固定受控条件应合法")
6444 .where_exists(paid_order)
6445 .where_in_subquery(yang_db::field!("users.id"), active_user)
6446 .where_not_exists(banned_user);
6447 let mut generator = SqlGenerator::new();
6448 generator.build_select(&builder).expect("子查询应可渲染");
6449
6450 assert_eq!(
6451 generator.get_sql(),
6452 "SELECT * FROM \"users\" WHERE (\"users\".\"tenant_id\" = $1 AND EXISTS (SELECT \"id\" FROM \"orders\" WHERE \"orders\".\"user_id\" = \"users\".\"id\" AND \"orders\".\"status\" = $2) AND \"users\".\"id\" IN (SELECT \"user_id\" FROM \"memberships\" WHERE \"memberships\".\"active\" = $3) AND NOT EXISTS (SELECT \"id\" FROM \"bans\" WHERE \"bans\".\"user_id\" = \"users\".\"id\" AND \"bans\".\"reason\" = $4))"
6453 );
6454 assert!(
6455 matches!(generator.get_params(), [SqlValue::Int(7), SqlValue::String(status), SqlValue::Bool(true), SqlValue::String(reason)] if status == "paid" && reason == "fraud")
6456 );
6457 }
6458
6459 #[test]
6460 fn test_controlled_subqueries_reject_adversarial_structure_before_rendering() {
6461 for payload in [
6462 "",
6463 "orders; DROP TABLE users",
6464 "orders --",
6465 "a.b",
6466 "orders\0",
6467 ] {
6468 assert!(
6469 Subquery::new(payload, "id").is_err(),
6470 "非法表名被接受: {payload:?}"
6471 );
6472 }
6473 for payload in ["", "id) FROM users --", "COUNT(*)", "a.b.c.d", "id\0"] {
6474 assert!(
6475 Subquery::new("orders", payload).is_err(),
6476 "非法投影被接受: {payload:?}"
6477 );
6478 }
6479 assert!(Subquery::new("orders", "id")
6480 .expect("合法子查询")
6481 .where_column("orders.user_id", "= 1 OR 1=1 --", "users.id")
6482 .is_err());
6483 assert!(Subquery::new("orders", "id")
6484 .expect("合法子查询")
6485 .where_value("orders.status --", "=", "paid")
6486 .is_err());
6487 assert!(crate::FieldRef::new("users.id) OR 1=1 --").is_err());
6488 }
6489
6490 #[test]
6491 fn test_union_all_keeps_branch_scope_and_numbered_parameter_order() {
6492 let pool = make_sync_test_pool();
6493 let branch = QueryBuilder::new(pool, "archived_users", false)
6494 .field(yang_db::field!("id"))
6495 .field(yang_db::field!("kind"))
6496 .where_and(yang_db::field!("tenant_id"), yang_db::CompareOp::Eq, 8)
6497 .expect("固定受控条件应合法")
6498 .order(yang_db::field!("id"), yang_db::SortOrder::Desc)
6499 .limit(2);
6500 let builder = QueryBuilder::new(pool, "users", false)
6501 .field(yang_db::field!("id"))
6502 .field(yang_db::field!("kind"))
6503 .where_and(yang_db::field!("tenant_id"), yang_db::CompareOp::Eq, 7)
6504 .expect("固定受控条件应合法")
6505 .union_all(branch)
6506 .expect("输出列数一致")
6507 .order(yang_db::field!("id"), yang_db::SortOrder::Asc)
6508 .limit(5);
6509 let mut generator = SqlGenerator::new();
6510 generator
6511 .build_select(&builder)
6512 .expect("UNION ALL 应可渲染");
6513
6514 assert_eq!(generator.get_sql(), "SELECT \"id\", \"kind\" FROM \"users\" WHERE \"tenant_id\" = $1 UNION ALL (SELECT \"id\", \"kind\" FROM \"archived_users\" WHERE \"tenant_id\" = $2 ORDER BY \"id\" DESC LIMIT 2) ORDER BY \"id\" ASC LIMIT 5");
6515 assert!(matches!(
6516 generator.get_params(),
6517 [SqlValue::Int(7), SqlValue::Int(8)]
6518 ));
6519 }
6520
6521 #[test]
6522 fn test_union_rejects_unknown_or_mismatched_output_and_bad_branch_table() {
6523 let pool = make_sync_test_pool();
6524 assert!(QueryBuilder::new(pool, "users", false)
6525 .union(QueryBuilder::new(pool, "archive", false))
6526 .is_err());
6527 assert!(QueryBuilder::new(pool, "users", false)
6528 .field(yang_db::field!("id"))
6529 .union(
6530 QueryBuilder::new(pool, "archive", false)
6531 .field(yang_db::field!("id"))
6532 .field(yang_db::field!("kind")),
6533 )
6534 .is_err());
6535 let builder = QueryBuilder::new(pool, "users", false)
6536 .field(yang_db::field!("id"))
6537 .union(
6538 QueryBuilder::new(pool, "archive; DROP TABLE users", false)
6539 .field(yang_db::field!("id")),
6540 )
6541 .expect("列数一致");
6542 let mut generator = SqlGenerator::new();
6543 assert!(generator.build_select(&builder).is_err());
6544 assert!(generator.get_params().is_empty());
6545 }
6546
6547 #[test]
6548 fn test_transaction_row_lock_rendering_is_typed_and_numbered() {
6549 let builder = QueryBuilder::new(make_sync_test_pool(), "accounts", false)
6550 .field(yang_db::field!("balance"))
6551 .where_and(yang_db::field!("id"), yang_db::CompareOp::Eq, 42)
6552 .expect("固定受控条件应合法")
6553 .limit(1);
6554 let (sql, params) = builder
6555 .render_for_transaction(Some(crate::RowLock::ForUpdate))
6556 .expect("事务锁查询应可渲染");
6557 assert_eq!(
6558 sql,
6559 "SELECT \"balance\" FROM \"accounts\" WHERE \"id\" = $1 LIMIT 1 FOR UPDATE"
6560 );
6561 assert!(matches!(params.as_slice(), [SqlValue::Int(42)]));
6562 }
6563
6564 #[test]
6565 fn test_transaction_row_lock_rejects_unsupported_query_shapes() {
6566 let pool = make_sync_test_pool();
6567 let grouped = QueryBuilder::new(pool, "accounts", false)
6568 .expr(crate::SelectExpr::count_all())
6569 .group(yang_db::field!("tenant_id"));
6570 assert!(grouped
6571 .render_for_transaction(Some(crate::RowLock::ForShare))
6572 .is_err());
6573
6574 let union = QueryBuilder::new(pool, "accounts", false)
6575 .field(yang_db::field!("id"))
6576 .union(QueryBuilder::new(pool, "archived_accounts", false).field(yang_db::field!("id")))
6577 .expect("列数一致的 UNION 应构建成功");
6578 assert!(union
6579 .render_for_transaction(Some(crate::RowLock::ForUpdate))
6580 .is_err());
6581 }
6582
6583 #[tokio::test]
6584 async fn test_atomic_update_rejects_missing_where_and_adversarial_field_before_io() {
6585 let pool = PgPoolOptions::new()
6586 .max_connections(1)
6587 .connect_lazy("postgres://postgres:postgres@localhost:5432/test")
6588 .expect("合法测试 URL");
6589 let missing_where = QueryBuilder::new(&pool, "accounts", false)
6590 .increment(yang_db::field!("balance"), 1)
6591 .await;
6592 assert!(matches!(
6593 missing_where,
6594 Err(crate::DbError::MissingWhereClause)
6595 ));
6596
6597 assert!(crate::FieldRef::new("balance = 0; DROP TABLE accounts --").is_err());
6598 }
6599
6600 #[test]
6601 fn test_atomic_update_renderer_binds_negative_amount_before_where_params() {
6602 let mut generator = SqlGenerator::new();
6603 generator
6604 .build_arithmetic_update(
6605 "accounts",
6606 "balance",
6607 ArithmeticOperator::Add,
6608 -3,
6609 &[Condition::Eq("id".to_string(), SqlValue::Int(9))],
6610 )
6611 .expect("原子更新应可渲染");
6612 assert_eq!(
6613 generator.get_sql(),
6614 "UPDATE \"accounts\" SET \"balance\" = \"balance\" + $1 WHERE \"id\" = $2"
6615 );
6616 assert!(matches!(
6617 generator.get_params(),
6618 [SqlValue::Int(-3), SqlValue::Int(9)]
6619 ));
6620 }
6621
6622 #[test]
6623 fn test_identifier_apis_quote_every_structural_fragment() {
6624 let pool = make_sync_test_pool();
6625 let builder = QueryBuilder::new(pool, "users", false)
6626 .field(yang_db::field!("users.id"))
6627 .expr(crate::SelectExpr::count_all().alias(yang_db::field!("cnt")))
6628 .join(
6629 yang_db::table!("profiles"),
6630 yang_db::field!("users.id"),
6631 yang_db::field!("profiles.user_id"),
6632 )
6633 .group(yang_db::field!("users.id"))
6634 .order(yang_db::field!("users.id"), yang_db::SortOrder::Asc);
6635
6636 let sql = builder.try_to_sql().expect("安全 API 应生成 SQL");
6637 assert!(sql.contains("SELECT \"users\".\"id\", COUNT(*) AS \"cnt\""));
6638 assert!(
6639 sql.contains("INNER JOIN \"profiles\" ON \"users\".\"id\" = \"profiles\".\"user_id\"")
6640 );
6641 assert!(sql.contains("GROUP BY \"users\".\"id\""));
6642 assert!(sql.contains("ORDER BY \"users\".\"id\" ASC"));
6643 }
6644
6645 #[test]
6646 fn test_identifier_apis_reject_adversarial_fragments() {
6647 let payloads = [
6648 "",
6649 ".id",
6650 "users.",
6651 "a.b.c.d",
6652 "users.id --",
6653 "users/*x*/.id",
6654 "users.\"id\"",
6656 "users.id\0",
6657 ];
6658
6659 for payload in payloads {
6660 assert!(crate::FieldRef::new(payload).is_err());
6661 }
6662 assert!(crate::TableRef::new("profiles p").is_err());
6663 }
6664
6665 #[test]
6666 fn test_try_to_sql_surfaces_invalid_table_identifier() {
6667 let pool = make_sync_test_pool();
6668 let builder = QueryBuilder::new(pool, "users; DROP TABLE users", false);
6669 let result = builder.try_to_sql();
6670
6671 assert!(matches!(result, Err(crate::DbError::InvalidArgument(_))));
6672 }
6673
6674 #[test]
6675 fn test_try_to_sql_rejects_invalid_condition_identifier() {
6676 assert!(crate::FieldRef::new("id;DROP").is_err());
6677 }
6678
6679 #[test]
6680 fn test_try_to_sql_accepts_qualified_where_and_having_identifiers() {
6681 let pool = make_sync_test_pool();
6682 let builder = QueryBuilder::new(pool, "users", false)
6683 .where_and(
6684 yang_db::field!("users.status"),
6685 yang_db::CompareOp::Eq,
6686 1i64,
6687 )
6688 .expect("固定受控条件应合法")
6689 .group(yang_db::field!("users.id"))
6690 .having_cond(
6691 yang_db::field!("users.score"),
6692 yang_db::CompareOp::Gt,
6693 10i64,
6694 )
6695 .expect("固定受控条件应合法");
6696
6697 let sql = builder
6698 .try_to_sql()
6699 .expect("合法的两段限定条件字段应生成 SQL");
6700
6701 assert!(sql.contains("WHERE \"users\".\"status\" = $1"));
6702 assert!(sql.contains("HAVING \"users\".\"score\" > $2"));
6703 }
6704
6705 #[test]
6706 fn test_try_to_sql_rejects_malicious_qualified_where_and_having_identifiers() {
6707 let invalid_fields = [
6708 "",
6709 ".id",
6710 "users.",
6711 "a.b.c.d",
6712 "users.id;DROP",
6713 "users.id --",
6714 "users.\"id\"",
6715 "COUNT(users.id)",
6716 ];
6717
6718 for field in invalid_fields {
6719 assert!(crate::FieldRef::new(field).is_err());
6720 }
6721 }
6722
6723 #[test]
6724 fn test_try_to_sql_surfaces_missing_group_by() {
6725 let pool = make_sync_test_pool();
6726 let builder = QueryBuilder::new(pool, "orders", false)
6727 .having_cond(yang_db::field!("cnt"), yang_db::CompareOp::Gt, 5i64)
6728 .expect("固定受控条件应合法");
6729 let result = builder.try_to_sql();
6730
6731 assert!(matches!(result, Err(crate::DbError::MissingGroupByClause)));
6732 }
6733
6734 #[test]
6735 fn test_try_to_sql_rejects_empty_in_condition() {
6736 let pool = make_sync_test_pool();
6737 let builder = QueryBuilder::new(pool, "users", false)
6738 .where_in(yang_db::field!("id"), Vec::<i64>::new());
6739 let result = builder.try_to_sql();
6740
6741 assert!(matches!(result, Err(crate::DbError::InvalidArgument(_))));
6742 }
6743
6744 #[test]
6745 fn test_try_to_sql_rejects_empty_boolean_condition() {
6746 let pool = make_sync_test_pool();
6747 let mut builder = QueryBuilder::new(pool, "users", false);
6748 builder.conditions.push(Condition::And(vec![]));
6749 let result = builder.try_to_sql();
6750
6751 assert!(matches!(result, Err(crate::DbError::InvalidArgument(_))));
6752 }
6753
6754 #[test]
6755 fn test_to_sql_does_not_fallback_to_raw_untrusted_table() {
6756 let pool = make_sync_test_pool();
6757 let builder = QueryBuilder::new(pool, "users; DROP TABLE users", false);
6758 let sql = builder.to_sql();
6759
6760 assert_eq!(sql, "/* SQL generation failed */");
6761 assert!(!sql.contains("DROP TABLE"));
6762 assert!(!sql.contains("users;"));
6763 }
6764
6765 fn empty_types() -> HashMap<String, FieldType> {
6767 HashMap::new()
6768 }
6769 #[test]
6772 fn test_sql_generator_new_empty() {
6773 let generator = SqlGenerator::new();
6774 assert_eq!(generator.get_sql(), "");
6775 assert_eq!(generator.get_params().len(), 0);
6776 }
6777
6778 #[test]
6779 fn test_build_insert_uses_dollar_placeholders() {
6780 let mut g = SqlGenerator::new();
6781 let data = serde_json::json!({"name": "张三", "age": 25});
6782 g.build_insert("users", &data, &empty_types(), &[]).unwrap();
6783 let sql = g.get_sql();
6784 assert_eq!(
6786 sql,
6787 "INSERT INTO \"users\" (\"age\", \"name\") VALUES ($1, $2)"
6788 );
6789 assert!(!sql.contains("RETURNING"));
6791 assert_eq!(g.get_params().len(), 2);
6792 }
6793
6794 #[test]
6795 fn test_build_insert_inlines_null_without_placeholder() {
6796 let mut g = SqlGenerator::new();
6797 let data = serde_json::json!({"name": "张三", "deleted_at": null});
6798 g.build_insert("users", &data, &empty_types(), &[]).unwrap();
6799 let sql = g.get_sql();
6800 assert_eq!(
6802 sql,
6803 "INSERT INTO \"users\" (\"deleted_at\", \"name\") VALUES (NULL, $1)"
6804 );
6805 assert_eq!(g.get_params().len(), 1);
6806 }
6807
6808 #[tokio::test]
6809 async fn test_insert_rejects_returning_all_before_io() {
6810 let pool = PgPoolOptions::new()
6813 .max_connections(1)
6814 .connect_lazy("postgres://postgres:postgres@localhost:5432/test")
6815 .expect("无法解析测试数据库 URL");
6816 let error = QueryBuilder::new(&pool, "users", false)
6817 .returning_all()
6818 .insert(&serde_json::json!({"name": "张三"}))
6819 .await
6820 .expect_err("returning_all 的 insert 应在触库前失败");
6821 assert!(matches!(error, crate::DbError::InvalidArgument(_)));
6822 }
6823
6824 #[test]
6825 fn test_insert_set_expr_appends_after_json_columns() {
6826 let mut g = SqlGenerator::new();
6827 let data = serde_json::json!({"name": "张三"});
6828 let set_exprs = vec![(
6829 "created_at".to_string(),
6830 crate::SqlExpr::unix_timestamp_add(5),
6831 )];
6832 g.build_insert("users", &data, &empty_types(), &set_exprs)
6833 .unwrap();
6834 assert_eq!(
6835 g.get_sql(),
6836 "INSERT INTO \"users\" (\"name\", \"created_at\") VALUES ($1, \
6837 EXTRACT(EPOCH FROM now()) + $2)"
6838 );
6839 assert_eq!(g.get_params().len(), 2);
6840 }
6841
6842 #[test]
6843 fn test_build_update_set_then_where_numbering_continues() {
6844 let mut g = SqlGenerator::new();
6845 let data = serde_json::json!({"name": "李四", "age": 30});
6846 let conditions = vec![Condition::Eq("id".to_string(), SqlValue::Int(1))];
6847 g.build_update("users", &data, &empty_types(), &conditions, &[])
6848 .unwrap();
6849 let sql = g.get_sql();
6850 assert_eq!(
6852 sql,
6853 "UPDATE \"users\" SET \"age\" = $1, \"name\" = $2 WHERE \"id\" = $3"
6854 );
6855 assert_eq!(g.get_params().len(), 3);
6856 }
6857
6858 #[test]
6860 fn test_build_update_inlines_null_in_set() {
6861 let mut g = SqlGenerator::new();
6862 let data = serde_json::json!({"description": serde_json::Value::Null, "name": "张三"});
6864 let conditions = vec![Condition::Eq("id".to_string(), SqlValue::Int(1))];
6865 g.build_update("users", &data, &empty_types(), &conditions, &[])
6866 .unwrap();
6867 let sql = g.get_sql();
6868 assert_eq!(
6870 sql,
6871 "UPDATE \"users\" SET \"description\" = NULL, \"name\" = $1 WHERE \"id\" = $2"
6872 );
6873 assert_eq!(g.get_params().len(), 2);
6875 }
6876
6877 #[test]
6878 fn test_build_delete_dollar_placeholder() {
6879 let mut g = SqlGenerator::new();
6880 let conditions = vec![Condition::Eq("id".to_string(), SqlValue::Int(7))];
6881 g.build_delete("users", &conditions).unwrap();
6882 assert_eq!(g.get_sql(), "DELETE FROM \"users\" WHERE \"id\" = $1");
6883 assert_eq!(g.get_params().len(), 1);
6884 }
6885
6886 #[test]
6887 fn test_build_delete_requires_where() {
6888 let mut g = SqlGenerator::new();
6889 let err = g.build_delete("users", &[]).unwrap_err();
6890 assert!(matches!(err, crate::error::DbError::MissingWhereClause));
6891 }
6892
6893 #[test]
6894 fn test_build_upsert_on_conflict_excluded() {
6895 let mut g = SqlGenerator::new();
6896 let data = serde_json::json!({"id": 1, "name": "张三", "email": "z@example.com"});
6897 let conflict = ConflictTarget::Columns(vec!["id".to_string()]);
6898 g.build_upsert(
6899 "users",
6900 &data,
6901 &empty_types(),
6902 &conflict,
6903 None,
6904 false,
6905 None,
6906 &[],
6907 )
6908 .unwrap();
6909 let sql = g.get_sql();
6910 assert!(sql.starts_with(
6912 "INSERT INTO \"users\" (\"email\", \"id\", \"name\") VALUES ($1, $2, $3)"
6913 ));
6914 assert!(sql.contains("ON CONFLICT (\"id\") DO UPDATE SET"));
6915 assert!(sql.contains("\"name\" = EXCLUDED.\"name\""));
6917 assert!(sql.contains("\"email\" = EXCLUDED.\"email\""));
6918 assert!(!sql.contains("\"id\" = EXCLUDED.\"id\""));
6919 assert_eq!(g.get_params().len(), 3);
6920 }
6921
6922 #[test]
6923 fn test_build_upsert_empty_conflict_do_nothing() {
6924 let mut g = SqlGenerator::new();
6925 let data = serde_json::json!({"name": "张三"});
6926 g.build_upsert(
6927 "users",
6928 &data,
6929 &empty_types(),
6930 &ConflictTarget::Columns(vec![]),
6931 None,
6932 true,
6933 None,
6934 &[],
6935 )
6936 .unwrap();
6937 assert!(g.get_sql().contains("ON CONFLICT DO NOTHING"));
6938 }
6939
6940 #[test]
6943 fn test_build_upsert_excluded_field_is_lowercase_pseudo_table() {
6944 let mut g = SqlGenerator::new();
6945 let data = serde_json::json!({"id": 1, "name": "a"});
6946 let where_cond = vec![Condition::Gt(
6947 "EXCLUDED.name".to_string(),
6948 SqlValue::String("x".to_string()),
6949 )];
6950 g.build_upsert(
6951 "users",
6952 &data,
6953 &empty_types(),
6954 &ConflictTarget::Columns(vec!["id".to_string()]),
6955 None,
6956 false,
6957 None,
6958 &where_cond,
6959 )
6960 .unwrap();
6961 let sql = g.get_sql();
6962 assert!(sql.contains("excluded.\"name\""), "SQL: {sql}");
6963 assert!(!sql.contains("\"EXCLUDED\""), "SQL: {sql}");
6964 }
6965
6966 #[test]
6969 fn test_build_upsert_constraint_excludes_id_by_default() {
6970 let mut g = SqlGenerator::new();
6971 let data = serde_json::json!({"id": 999, "email": "a@x", "name": "n"});
6972 g.build_upsert(
6973 "users",
6974 &data,
6975 &empty_types(),
6976 &ConflictTarget::Constraint("users_email_key".to_string()),
6977 None,
6978 false,
6979 None,
6980 &[],
6981 )
6982 .unwrap();
6983 let sql = g.get_sql();
6984 assert!(sql.contains("\"name\" = EXCLUDED.\"name\""), "SQL: {sql}");
6985 assert!(sql.contains("\"email\" = EXCLUDED.\"email\""), "SQL: {sql}");
6986 assert!(!sql.contains("\"id\" = EXCLUDED.\"id\""), "SQL: {sql}");
6987 }
6988
6989 #[test]
6991 fn test_build_upsert_constraint_without_updatable_columns_errors() {
6992 let mut g = SqlGenerator::new();
6993 let data = serde_json::json!({"id": 1});
6994 let err = g
6995 .build_upsert(
6996 "users",
6997 &data,
6998 &empty_types(),
6999 &ConflictTarget::Constraint("users_pkey".to_string()),
7000 None,
7001 false,
7002 None,
7003 &[],
7004 )
7005 .unwrap_err();
7006 assert!(matches!(err, crate::error::DbError::InvalidArgument(_)));
7007 }
7008
7009 #[tokio::test]
7011 async fn test_update_batch_rejects_set_expr_before_io() {
7012 let pool = PgPoolOptions::new()
7013 .max_connections(1)
7014 .connect_lazy("postgres://postgres:postgres@localhost:5432/test")
7015 .expect("无法解析测试数据库 URL");
7016 let error = QueryBuilder::new(&pool, "users", false)
7017 .set_expr(crate::field!("updated_at"), crate::SqlExpr::now())
7018 .update_batch(&[serde_json::json!({"id": 1})], crate::field!("id"))
7019 .await
7020 .expect_err("update_batch 应拒绝 set_expr");
7021 assert!(matches!(error, crate::error::DbError::InvalidArgument(_)));
7022 }
7023
7024 #[test]
7025 fn test_build_insert_batch_sequential_numbering() {
7026 let mut g = SqlGenerator::new();
7027 let list = vec![
7028 serde_json::json!({"name": "a", "age": 1}),
7029 serde_json::json!({"name": "b", "age": 2}),
7030 ];
7031 g.build_insert_batch("users", &list, &empty_types())
7032 .unwrap();
7033 let sql = g.get_sql();
7034 assert_eq!(
7036 sql,
7037 "INSERT INTO \"users\" (\"age\", \"name\") VALUES ($1, $2), ($3, $4)"
7038 );
7039 assert_eq!(g.get_params().len(), 4);
7040 }
7041
7042 #[test]
7043 fn test_sum_expr_uses_double_precision() {
7044 let pool = make_sync_test_pool();
7045 let sql = QueryBuilder::new(pool, "orders", false)
7047 .expr(crate::SelectExpr::sum(yang_db::field!("amount")).cast_double())
7048 .to_sql();
7049 assert!(sql.contains("DOUBLE PRECISION"));
7050 assert!(sql.contains("FROM \"orders\""));
7051 }
7052
7053 #[test]
7054 fn test_avg_expr_uses_double_precision() {
7055 let expr = format!("CAST(AVG({}) AS DOUBLE PRECISION)", "score");
7056 assert!(expr.contains("DOUBLE PRECISION"));
7057 }
7058
7059 #[test]
7060 fn test_to_sql_select_basic() {
7061 let pool = make_sync_test_pool();
7062 let sql = QueryBuilder::new(pool, "users", false)
7063 .field(yang_db::field!("id"))
7064 .field(yang_db::field!("name"))
7065 .to_sql();
7066 assert_eq!(sql, "SELECT \"id\", \"name\" FROM \"users\"");
7067 }
7068
7069 #[test]
7070 fn test_to_sql_where_uses_dollar() {
7071 let pool = make_sync_test_pool();
7072 let sql = QueryBuilder::new(pool, "users", false)
7073 .where_and(yang_db::field!("id"), yang_db::CompareOp::Eq, 1)
7074 .expect("固定受控条件应合法")
7075 .to_sql();
7076 assert_eq!(sql, "SELECT * FROM \"users\" WHERE \"id\" = $1");
7077 }
7078
7079 #[test]
7080 fn test_default_returning_and_conflict_columns() {
7081 let pool = make_sync_test_pool();
7082 let qb = QueryBuilder::new(pool, "users", false);
7083 assert_eq!(qb.returning, ReturningTarget::Column("id".to_string()));
7084 assert_eq!(qb.conflict_target, ConflictTarget::Unspecified);
7086 let qb2 = qb
7087 .returning(yang_db::field!("uid"))
7088 .on_conflict(&[yang_db::field!("email"), yang_db::field!("tenant_id")]);
7089 assert_eq!(qb2.returning, ReturningTarget::Column("uid".to_string()));
7090 assert_eq!(
7091 qb2.conflict_target,
7092 ConflictTarget::Columns(vec!["email".to_string(), "tenant_id".to_string()])
7093 );
7094 }
7095
7096 #[test]
7097 fn test_returning_all_renders_star() {
7098 let pool = make_sync_test_pool();
7099 let builder = QueryBuilder::new(pool, "users", false).returning_all();
7100 assert_eq!(builder.returning_clause().unwrap(), " RETURNING *");
7101 assert_eq!(
7102 builder.returning_clause().unwrap(),
7103 " RETURNING *".to_string()
7104 );
7105 }
7106
7107 #[test]
7108 fn test_on_conflict_constraint_and_update_columns() {
7109 let mut g = SqlGenerator::new();
7110 let data = serde_json::json!({"id": 1, "name": "张三", "email": "z@example.com"});
7111 g.build_upsert(
7112 "users",
7113 &data,
7114 &empty_types(),
7115 &ConflictTarget::Constraint("users_email_key".to_string()),
7116 None,
7117 false,
7118 Some(&["name".to_string()]),
7119 &[],
7120 )
7121 .unwrap();
7122 let sql = g.get_sql();
7123 assert!(sql.contains("ON CONFLICT ON CONSTRAINT \"users_email_key\" DO UPDATE SET"));
7124 assert!(sql.contains("\"name\" = EXCLUDED.\"name\""));
7125 assert!(!sql.contains("\"email\" = EXCLUDED.\"email\""));
7126 }
7127
7128 #[test]
7129 fn test_upsert_do_nothing_ignores_update_columns() {
7130 let mut g = SqlGenerator::new();
7131 let data = serde_json::json!({"id": 1, "name": "张三"});
7132 g.build_upsert(
7133 "users",
7134 &data,
7135 &empty_types(),
7136 &ConflictTarget::Columns(vec!["id".to_string()]),
7137 None,
7138 true,
7139 Some(&["name".to_string()]),
7140 &[],
7141 )
7142 .unwrap();
7143 let sql = g.get_sql();
7144 assert!(sql.contains("ON CONFLICT (\"id\") DO NOTHING"));
7145 assert!(!sql.contains("DO UPDATE"));
7146 }
7147
7148 #[test]
7149 fn test_upsert_where_continues_placeholder_numbering() {
7150 let mut g = SqlGenerator::new();
7151 let data = serde_json::json!({"id": 1, "name": "张三"});
7152 let update_where = vec![Condition::Gt("version".to_string(), SqlValue::Int(2))];
7153 g.build_upsert(
7154 "users",
7155 &data,
7156 &empty_types(),
7157 &ConflictTarget::Columns(vec!["id".to_string()]),
7158 None,
7159 false,
7160 None,
7161 &update_where,
7162 )
7163 .unwrap();
7164 let sql = g.get_sql();
7165 assert!(
7167 sql.ends_with("WHERE \"users\".\"version\" > $3"),
7168 "实际 SQL: {sql}"
7169 );
7170 assert_eq!(g.get_params().len(), 3);
7171 }
7172
7173 #[test]
7174 fn test_update_set_expr_continues_numbering() {
7175 let mut g = SqlGenerator::new();
7176 let data = serde_json::json!({"name": "张三"});
7177 let conditions = vec![Condition::Eq("id".to_string(), SqlValue::Int(1))];
7178 let set_exprs = vec![(
7179 "updated_at".to_string(),
7180 crate::SqlExpr::unix_timestamp_add(30),
7181 )];
7182 g.build_update("users", &data, &empty_types(), &conditions, &set_exprs)
7183 .unwrap();
7184 let sql = g.get_sql();
7185 assert_eq!(
7186 sql,
7187 "UPDATE \"users\" SET \"name\" = $1, \"updated_at\" = EXTRACT(EPOCH FROM now()) + $2 \
7188 WHERE \"id\" = $3"
7189 );
7190 assert_eq!(g.get_params().len(), 3);
7191 }
7192
7193 #[test]
7194 fn test_select_expr_numbering_precedes_where_params() {
7195 let pool = make_sync_test_pool();
7196 let builder = QueryBuilder::new(pool, "users", false)
7197 .select_expr(
7198 crate::SqlExpr::unix_timestamp_add(60),
7199 yang_db::field!("ts"),
7200 )
7201 .where_and(yang_db::field!("id"), yang_db::CompareOp::Eq, 7)
7202 .expect("固定受控条件应合法");
7203 let sql = builder.try_to_sql().unwrap();
7204 assert_eq!(
7205 sql,
7206 "SELECT EXTRACT(EPOCH FROM now()) + $1 AS \"ts\" FROM \"users\" WHERE \"id\" = $2"
7207 );
7208 }
7209
7210 #[test]
7211 fn test_where_expr_renders_controlled_expression() {
7212 let pool = make_sync_test_pool();
7213 let sql = QueryBuilder::new(pool, "users", false)
7214 .where_expr(
7215 yang_db::field!("updated_at"),
7216 yang_db::CompareOp::Lt,
7217 crate::SqlExpr::now(),
7218 )
7219 .expect("受控表达式比较应合法")
7220 .where_and(yang_db::field!("id"), yang_db::CompareOp::Eq, 3)
7221 .expect("固定受控条件应合法")
7222 .try_to_sql()
7223 .unwrap();
7224 assert!(sql.contains("\"updated_at\" < now()"), "实际 SQL: {sql}");
7225 assert!(sql.contains("\"id\" = $1"), "实际 SQL: {sql}");
7226 }
7227
7228 #[test]
7229 fn test_schema_qualified_table_is_quoted_per_segment() {
7230 let pool = make_sync_test_pool();
7231 let sql = QueryBuilder::new(pool, "audit.users", false)
7232 .field(yang_db::field!("audit.users.id"))
7233 .try_to_sql()
7234 .unwrap();
7235 assert_eq!(
7236 sql,
7237 "SELECT \"audit\".\"users\".\"id\" FROM \"audit\".\"users\""
7238 );
7239 }
7240}
7241
7242#[cfg(test)]
7244#[allow(clippy::unwrap_used, clippy::expect_used)]
7245mod phase_b_tests {
7246 use super::*;
7247 use crate::{field, table, CompareOp, SelectExpr, SortOrder};
7248 use sqlx::postgres::PgPoolOptions;
7249
7250 fn test_pool() -> &'static PgPool {
7252 use std::sync::OnceLock;
7253 static RT: OnceLock<tokio::runtime::Runtime> = OnceLock::new();
7254 static POOL: OnceLock<PgPool> = OnceLock::new();
7255 let runtime = RT.get_or_init(|| {
7256 tokio::runtime::Builder::new_multi_thread()
7257 .enable_all()
7258 .build()
7259 .expect("无法创建测试用 Tokio 运行时")
7260 });
7261 POOL.get_or_init(|| {
7262 runtime.block_on(async {
7263 PgPoolOptions::new()
7264 .max_connections(1)
7265 .connect_lazy("postgres://postgres:postgres@localhost:5432/test")
7266 .expect("无法解析测试数据库 URL")
7267 })
7268 })
7269 }
7270
7271 fn render(builder: &QueryBuilder<'_>) -> String {
7272 builder.try_to_sql().expect("SQL 应可渲染")
7273 }
7274
7275 #[test]
7276 fn cte_renders_with_recursive_and_keeps_placeholder_order() {
7277 let recent = QueryBuilder::new(test_pool(), "events", false)
7278 .field(field!("user_id"))
7279 .where_and(field!("kind"), CompareOp::Eq, "login")
7280 .expect("受控条件应合法");
7281 let tree = QueryBuilder::new(test_pool(), "nodes", false)
7282 .field(field!("id"))
7283 .where_null(field!("parent_id"));
7284 let builder = QueryBuilder::new(test_pool(), "users", false)
7285 .field(field!("id"))
7286 .with("recent", recent)
7287 .with_recursive("tree", tree)
7288 .where_and(field!("active"), CompareOp::Eq, true)
7289 .expect("受控条件应合法")
7290 .order(field!("id"), SortOrder::Asc)
7291 .limit(5);
7292
7293 assert_eq!(
7294 render(&builder),
7295 "WITH RECURSIVE \"recent\" AS (SELECT \"user_id\" FROM \"events\" WHERE \"kind\" = $1), \
7296 \"tree\" AS (SELECT \"id\" FROM \"nodes\" WHERE \"parent_id\" IS NULL) \
7297 SELECT \"id\" FROM \"users\" WHERE \"active\" = $2 ORDER BY \"id\" ASC LIMIT 5"
7298 );
7299 let mut generator = SqlGenerator::new();
7300 generator.build_select(&builder).expect("CTE 应可渲染");
7301 assert!(matches!(
7302 generator.get_params(),
7303 [SqlValue::String(kind), SqlValue::Bool(true)] if kind == "login"
7304 ));
7305 }
7306
7307 #[test]
7308 fn cte_name_is_validated_before_rendering() {
7309 let body = QueryBuilder::new(test_pool(), "users", false).field(field!("id"));
7310 let builder = QueryBuilder::new(test_pool(), "users", false)
7311 .field(field!("id"))
7312 .with("bad; DROP TABLE users", body);
7313 assert!(matches!(
7314 builder.try_to_sql(),
7315 Err(crate::error::DbError::InvalidArgument(_))
7316 ));
7317 }
7318
7319 #[test]
7320 fn window_functions_render_partition_order_and_frames() {
7321 let spec = WindowSpec::new()
7322 .partition_by(&[field!("dept")])
7323 .order_by(field!("amount"), SortOrder::Desc);
7324 let running = WindowSpec::new()
7325 .partition_by(&[field!("dept")])
7326 .order_by(field!("created_at"), SortOrder::Asc)
7327 .frame(WindowFrame::rows_between(
7328 FrameBound::UnboundedPreceding,
7329 FrameBound::CurrentRow,
7330 ));
7331 let builder = QueryBuilder::new(test_pool(), "sales", false)
7332 .field(field!("dept"))
7333 .window_expr(WindowFunction::row_number(), spec, field!("rn"))
7334 .window_expr(
7335 WindowFunction::sum(field!("amount")),
7336 running,
7337 field!("running"),
7338 );
7339
7340 assert_eq!(
7341 render(&builder),
7342 "SELECT \"dept\", \
7343 ROW_NUMBER() OVER (PARTITION BY \"dept\" ORDER BY \"amount\" DESC) AS \"rn\", \
7344 SUM(\"amount\") OVER (PARTITION BY \"dept\" ORDER BY \"created_at\" ASC \
7345 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS \"running\" \
7346 FROM \"sales\""
7347 );
7348 }
7349
7350 #[test]
7351 fn every_whitelisted_window_function_renders_expected_call() {
7352 let cases = [
7353 (WindowFunction::rank(), "RANK()"),
7354 (WindowFunction::dense_rank(), "DENSE_RANK()"),
7355 (WindowFunction::avg(field!("amount")), "AVG(\"amount\")"),
7356 (WindowFunction::count(field!("amount")), "COUNT(\"amount\")"),
7357 (WindowFunction::count_all(), "COUNT(*)"),
7358 (WindowFunction::min(field!("amount")), "MIN(\"amount\")"),
7359 (WindowFunction::max(field!("amount")), "MAX(\"amount\")"),
7360 (WindowFunction::lag(field!("amount")), "LAG(\"amount\")"),
7361 (WindowFunction::lead(field!("amount")), "LEAD(\"amount\")"),
7362 ];
7363 for (function, expected) in cases {
7364 let builder = QueryBuilder::new(test_pool(), "sales", false).window_expr(
7365 function,
7366 WindowSpec::new().frame(WindowFrame::range_between(
7367 FrameBound::Preceding(1),
7368 FrameBound::Following(1),
7369 )),
7370 field!("value"),
7371 );
7372 let sql = render(&builder);
7373 assert!(
7374 sql.contains(&format!(
7375 "{} OVER (RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS \"value\"",
7376 expected
7377 )),
7378 "窗口函数片段不符: {sql}"
7379 );
7380 }
7381 }
7382
7383 #[test]
7384 fn invalid_window_frames_fail_closed() {
7385 for frame in [
7386 WindowFrame::rows_between(FrameBound::UnboundedFollowing, FrameBound::CurrentRow),
7387 WindowFrame::rows_between(FrameBound::CurrentRow, FrameBound::UnboundedPreceding),
7388 ] {
7389 let builder = QueryBuilder::new(test_pool(), "sales", false).window_expr(
7390 WindowFunction::row_number(),
7391 WindowSpec::new().frame(frame),
7392 field!("rn"),
7393 );
7394 assert!(matches!(
7395 builder.try_to_sql(),
7396 Err(crate::error::DbError::InvalidArgument(_))
7397 ));
7398 }
7399 }
7400
7401 #[test]
7402 fn groups_frame_filter_and_named_window_render() {
7403 let named = WindowSpec::new()
7404 .partition_by(&[field!("dept")])
7405 .order_by(field!("id"), SortOrder::Asc);
7406 let builder = QueryBuilder::new(test_pool(), "sales", false)
7407 .field(field!("dept"))
7408 .window("w", named)
7409 .window_expr(
7410 WindowFunction::sum(field!("amount"))
7411 .filter(Condition::Gt("amount".to_string(), SqlValue::Int(100))),
7412 WindowSpec::new()
7413 .order_by(field!("id"), SortOrder::Asc)
7414 .frame(WindowFrame::groups_between(
7415 FrameBound::Preceding(1),
7416 FrameBound::CurrentRow,
7417 )),
7418 field!("running"),
7419 )
7420 .window_expr(
7421 WindowFunction::row_number(),
7422 WindowSpec::reference("w"),
7423 field!("rn"),
7424 );
7425
7426 assert_eq!(
7427 render(&builder),
7428 "SELECT \"dept\", \
7429 SUM(\"amount\") FILTER (WHERE \"amount\" > $1) \
7430 OVER (ORDER BY \"id\" ASC GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS \"running\", \
7431 ROW_NUMBER() OVER \"w\" AS \"rn\" \
7432 FROM \"sales\" WINDOW \"w\" AS (PARTITION BY \"dept\" ORDER BY \"id\" ASC)"
7433 );
7434 let mut generator = SqlGenerator::new();
7435 generator.build_select(&builder).expect("窗口 SQL 应可渲染");
7436 assert!(matches!(generator.get_params(), [SqlValue::Int(100)]));
7437 }
7438
7439 #[test]
7440 fn groups_frame_requires_order_by() {
7441 let builder = QueryBuilder::new(test_pool(), "sales", false).window_expr(
7442 WindowFunction::sum(field!("amount")),
7443 WindowSpec::new().frame(WindowFrame::groups_between(
7444 FrameBound::Preceding(1),
7445 FrameBound::CurrentRow,
7446 )),
7447 field!("running"),
7448 );
7449 assert!(matches!(
7450 builder.try_to_sql(),
7451 Err(crate::error::DbError::InvalidArgument(_))
7452 ));
7453 }
7454
7455 #[test]
7456 fn filter_on_non_aggregate_window_function_fails_closed() {
7457 let builder = QueryBuilder::new(test_pool(), "sales", false).window_expr(
7458 WindowFunction::row_number()
7459 .filter(Condition::Gt("amount".to_string(), SqlValue::Int(0))),
7460 WindowSpec::new(),
7461 field!("rn"),
7462 );
7463 assert!(matches!(
7464 builder.try_to_sql(),
7465 Err(crate::error::DbError::InvalidArgument(_))
7466 ));
7467 }
7468
7469 #[test]
7470 fn named_window_reference_rejects_extra_spec_parts() {
7471 let builder = QueryBuilder::new(test_pool(), "sales", false).window_expr(
7472 WindowFunction::row_number(),
7473 WindowSpec::reference("w").partition_by(&[field!("dept")]),
7474 field!("rn"),
7475 );
7476 assert!(matches!(
7477 builder.try_to_sql(),
7478 Err(crate::error::DbError::InvalidArgument(_))
7479 ));
7480
7481 let invalid_name = QueryBuilder::new(test_pool(), "sales", false)
7482 .window("bad name", WindowSpec::new())
7483 .window_expr(
7484 WindowFunction::row_number(),
7485 WindowSpec::reference("w"),
7486 field!("rn"),
7487 );
7488 assert!(invalid_name.try_to_sql().is_err());
7489 }
7490
7491 #[test]
7492 fn grouping_sets_rollup_and_cube_render() {
7493 let grouping_sets = QueryBuilder::new(test_pool(), "sales", false)
7494 .field(field!("region"))
7495 .expr(SelectExpr::sum(field!("amount")).alias(field!("total")))
7496 .group(field!("region"))
7497 .grouping_sets(vec![vec![field!("product").clone()], vec![]]);
7498 assert_eq!(
7499 render(&grouping_sets),
7500 "SELECT \"region\", SUM(\"amount\") AS \"total\" FROM \"sales\" \
7501 GROUP BY \"region\", GROUPING SETS ((\"product\"), ())"
7502 );
7503
7504 let rollup = QueryBuilder::new(test_pool(), "sales", false)
7505 .field(field!("region"))
7506 .rollup(vec![field!("region").clone(), field!("product").clone()]);
7507 assert_eq!(
7508 render(&rollup),
7509 "SELECT \"region\" FROM \"sales\" GROUP BY ROLLUP (\"region\", \"product\")"
7510 );
7511
7512 let cube = QueryBuilder::new(test_pool(), "sales", false)
7513 .field(field!("region"))
7514 .cube(vec![field!("region").clone(), field!("product").clone()]);
7515 assert_eq!(
7516 render(&cube),
7517 "SELECT \"region\" FROM \"sales\" GROUP BY CUBE (\"region\", \"product\")"
7518 );
7519 }
7520
7521 #[test]
7522 fn empty_grouping_constructs_fail_closed() {
7523 let empty_sets = QueryBuilder::new(test_pool(), "sales", false)
7524 .field(field!("region"))
7525 .grouping_sets(vec![]);
7526 assert!(matches!(
7527 empty_sets.try_to_sql(),
7528 Err(crate::error::DbError::InvalidArgument(_))
7529 ));
7530 let empty_rollup = QueryBuilder::new(test_pool(), "sales", false)
7531 .field(field!("region"))
7532 .rollup(vec![]);
7533 assert!(empty_rollup.try_to_sql().is_err());
7534 let empty_cube = QueryBuilder::new(test_pool(), "sales", false)
7535 .field(field!("region"))
7536 .cube(vec![]);
7537 assert!(empty_cube.try_to_sql().is_err());
7538 }
7539
7540 #[test]
7541 fn distinct_on_requires_matching_leftmost_order_by() {
7542 let builder = QueryBuilder::new(test_pool(), "events", false)
7543 .field(field!("user_id"))
7544 .field(field!("created_at"))
7545 .distinct_on(&[field!("user_id")])
7546 .order(field!("user_id"), SortOrder::Asc)
7547 .order(field!("created_at"), SortOrder::Desc);
7548 assert_eq!(
7549 render(&builder),
7550 "SELECT DISTINCT ON (\"user_id\") \"user_id\", \"created_at\" FROM \"events\" \
7551 ORDER BY \"user_id\" ASC, \"created_at\" DESC"
7552 );
7553
7554 let missing = QueryBuilder::new(test_pool(), "events", false)
7556 .field(field!("user_id"))
7557 .distinct_on(&[field!("user_id")]);
7558 assert!(matches!(
7559 missing.try_to_sql(),
7560 Err(crate::error::DbError::InvalidArgument(_))
7561 ));
7562
7563 let mismatched = QueryBuilder::new(test_pool(), "events", false)
7565 .field(field!("user_id"))
7566 .distinct_on(&[field!("user_id")])
7567 .order(field!("created_at"), SortOrder::Desc);
7568 assert!(mismatched.try_to_sql().is_err());
7569
7570 let both = QueryBuilder::new(test_pool(), "events", false)
7572 .field(field!("user_id"))
7573 .distinct()
7574 .distinct_on(&[field!("user_id")])
7575 .order(field!("user_id"), SortOrder::Asc);
7576 assert!(both.try_to_sql().is_err());
7577 }
7578
7579 #[test]
7580 fn join_extensions_render_expected_keywords() {
7581 let builder = QueryBuilder::new(test_pool(), "a", false)
7582 .field(field!("a.id"))
7583 .full_join(table!("b"), field!("a.id"), field!("b.a_id"))
7584 .cross_join(table!("c"))
7585 .natural_join(table!("d"))
7586 .join_using(table!("e"), &[field!("id")]);
7587 assert_eq!(
7588 render(&builder),
7589 "SELECT \"a\".\"id\" FROM \"a\" \
7590 FULL JOIN \"b\" ON \"a\".\"id\" = \"b\".\"a_id\" \
7591 CROSS JOIN \"c\" NATURAL JOIN \"d\" INNER JOIN \"e\" USING (\"id\")"
7592 );
7593 }
7594
7595 #[test]
7596 fn lateral_left_join_embeds_subquery_and_alias() {
7597 let lateral = QueryBuilder::new(test_pool(), "orders", false)
7598 .field(field!("id"))
7599 .field(field!("amount"))
7600 .where_and(field!("orders.status"), CompareOp::Eq, "paid")
7601 .expect("受控条件应合法")
7602 .where_column(field!("orders.user_id"), CompareOp::Eq, field!("users.id"))
7603 .expect("受控列比较应合法")
7604 .order(field!("amount"), SortOrder::Desc)
7605 .limit(3);
7606 let builder = QueryBuilder::new(test_pool(), "users", false)
7607 .field(field!("users.id"))
7608 .left_join_lateral(
7609 lateral,
7610 field!("o"),
7611 field!("users.id"),
7612 field!("o.user_id"),
7613 );
7614 assert_eq!(
7615 render(&builder),
7616 "SELECT \"users\".\"id\" FROM \"users\" \
7617 LEFT JOIN LATERAL (SELECT \"id\", \"amount\" FROM \"orders\" \
7618 WHERE (\"orders\".\"status\" = $1 AND \"orders\".\"user_id\" = \"users\".\"id\") \
7619 ORDER BY \"amount\" DESC LIMIT 3) AS \"o\" \
7620 ON \"users\".\"id\" = \"o\".\"user_id\""
7621 );
7622 }
7623
7624 #[test]
7625 fn set_operations_render_and_validate_column_counts() {
7626 let left = || {
7627 QueryBuilder::new(test_pool(), "a", false)
7628 .field(field!("id"))
7629 .field(field!("name"))
7630 };
7631 let right = |table_name| {
7632 QueryBuilder::new(test_pool(), table_name, false)
7633 .field(field!("id"))
7634 .field(field!("name"))
7635 };
7636
7637 assert_eq!(
7638 render(&left().intersect(right("b")).expect("列数一致")),
7639 "SELECT \"id\", \"name\" FROM \"a\" INTERSECT (SELECT \"id\", \"name\" FROM \"b\")"
7640 );
7641 assert_eq!(
7642 render(&left().intersect_all(right("b")).expect("列数一致")),
7643 "SELECT \"id\", \"name\" FROM \"a\" INTERSECT ALL (SELECT \"id\", \"name\" FROM \"b\")"
7644 );
7645 assert_eq!(
7646 render(&left().except(right("b")).expect("列数一致")),
7647 "SELECT \"id\", \"name\" FROM \"a\" EXCEPT (SELECT \"id\", \"name\" FROM \"b\")"
7648 );
7649 assert_eq!(
7650 render(&left().except_all(right("b")).expect("列数一致")),
7651 "SELECT \"id\", \"name\" FROM \"a\" EXCEPT ALL (SELECT \"id\", \"name\" FROM \"b\")"
7652 );
7653
7654 let mixed = left()
7656 .union(right("b"))
7657 .expect("列数一致")
7658 .intersect(right("c"))
7659 .expect("列数一致");
7660 assert_eq!(
7661 render(&mixed),
7662 "(SELECT \"id\", \"name\" FROM \"a\" UNION (SELECT \"id\", \"name\" FROM \"b\")) \
7663 INTERSECT (SELECT \"id\", \"name\" FROM \"c\")"
7664 );
7665
7666 let one_column = QueryBuilder::new(test_pool(), "c", false).field(field!("id"));
7668 assert!(left().intersect(one_column).is_err());
7669 }
7670
7671 #[test]
7672 fn having_expr_renders_aggregate_comparison_with_bound_value() {
7673 let builder = QueryBuilder::new(test_pool(), "orders", false)
7674 .field(field!("user_id"))
7675 .expr(SelectExpr::count_all())
7676 .group(field!("user_id"))
7677 .having_expr(SelectExpr::count_all(), CompareOp::Gt, 1)
7678 .expect("受控 HAVING 表达式应合法");
7679 assert_eq!(
7680 render(&builder),
7681 "SELECT \"user_id\", COUNT(*) FROM \"orders\" GROUP BY \"user_id\" \
7682 HAVING COUNT(*) > $1"
7683 );
7684
7685 let without_group = QueryBuilder::new(test_pool(), "orders", false)
7687 .expr(SelectExpr::count_all())
7688 .having_expr(SelectExpr::count_all(), CompareOp::Gt, 1)
7689 .expect("无 GROUP BY 的聚合 HAVING 应合法");
7690 assert_eq!(
7691 render(&without_group),
7692 "SELECT COUNT(*) FROM \"orders\" HAVING COUNT(*) > $1"
7693 );
7694
7695 let field_having_without_group = QueryBuilder::new(test_pool(), "orders", false)
7697 .field(field!("user_id"))
7698 .having_cond(field!("user_id"), CompareOp::Gt, 1)
7699 .expect("受控条件应合法");
7700 assert!(matches!(
7701 field_having_without_group.try_to_sql(),
7702 Err(crate::error::DbError::MissingGroupByClause)
7703 ));
7704
7705 let like = QueryBuilder::new(test_pool(), "orders", false)
7707 .field(field!("user_id"))
7708 .group(field!("user_id"))
7709 .having_expr(SelectExpr::count_all(), CompareOp::Like, "%x%");
7710 assert!(matches!(
7711 like,
7712 Err(crate::error::DbError::UnsupportedOperator(_))
7713 ));
7714 }
7715
7716 #[test]
7717 fn upsert_batch_builds_multi_row_values_and_conflict_clause() {
7718 let data = [
7719 serde_json::json!({"id": 1, "name": "a"}),
7720 serde_json::json!({"id": 2, "name": "b"}),
7721 ];
7722 let mut generator = SqlGenerator::new();
7723 generator
7724 .build_upsert_batch(
7725 "users",
7726 &data,
7727 &HashMap::new(),
7728 &["id".to_string()],
7729 None,
7730 false,
7731 None,
7732 &[],
7733 )
7734 .expect("批量 UPSERT 应可渲染");
7735 assert_eq!(
7736 generator.get_sql(),
7737 "INSERT INTO \"users\" (\"id\", \"name\") VALUES ($1, $2), ($3, $4) \
7738 ON CONFLICT (\"id\") DO UPDATE SET \"name\" = EXCLUDED.\"name\""
7739 );
7740 assert_eq!(generator.get_params().len(), 4);
7741
7742 let mut generator = SqlGenerator::new();
7744 assert!(matches!(
7745 generator.build_upsert_batch(
7746 "users",
7747 &data,
7748 &HashMap::new(),
7749 &[],
7750 None,
7751 false,
7752 None,
7753 &[]
7754 ),
7755 Err(crate::error::DbError::InvalidArgument(_))
7756 ));
7757
7758 let mut generator = SqlGenerator::new();
7760 generator
7761 .build_upsert_batch("users", &data, &HashMap::new(), &[], None, true, None, &[])
7762 .expect("DO NOTHING 批量 UPSERT 应可渲染");
7763 assert!(generator.get_sql().ends_with("ON CONFLICT DO NOTHING"));
7764
7765 let mut generator = SqlGenerator::new();
7767 assert!(matches!(
7768 generator.build_upsert_batch(
7769 "users",
7770 &data,
7771 &HashMap::new(),
7772 &["id".to_string()],
7773 None,
7774 false,
7775 Some(&["missing".to_string()]),
7776 &[]
7777 ),
7778 Err(crate::error::DbError::InvalidArgument(_))
7779 ));
7780 }
7781
7782 #[tokio::test]
7783 async fn upsert_batch_rejects_invalid_input_before_io() {
7784 let pool = PgPoolOptions::new()
7785 .max_connections(1)
7786 .connect_lazy("postgres://postgres:postgres@localhost:5432/test")
7787 .expect("无法解析测试数据库 URL");
7788 let error = QueryBuilder::new(&pool, "users", false)
7789 .upsert_batch(&[] as &[serde_json::Value], &[field!("id")])
7790 .await
7791 .expect_err("空数据应在触库前失败");
7792 assert!(matches!(
7793 error,
7794 crate::error::DbError::SerializationError(_)
7795 ));
7796
7797 let error = QueryBuilder::new(&pool, "users", false)
7798 .upsert_batch(&[serde_json::json!({"id": 1})], &[])
7799 .await
7800 .expect_err("缺少冲突列应在触库前失败");
7801 assert!(matches!(error, crate::error::DbError::InvalidArgument(_)));
7802
7803 let error = QueryBuilder::new(&pool, "users", false)
7804 .upsert_batch_with_size(&[serde_json::json!({"id": 1})], &[field!("id")], 0)
7805 .await
7806 .expect_err("batch_size 为 0 应在触库前失败");
7807 assert!(matches!(
7808 error,
7809 crate::error::DbError::SerializationError(_)
7810 ));
7811 }
7812
7813 #[test]
7814 fn insert_from_select_renders_target_columns_and_source_query() {
7815 let source = QueryBuilder::new(test_pool(), "users", false)
7816 .field(field!("id"))
7817 .field(field!("name"))
7818 .where_and(field!("active"), CompareOp::Eq, true)
7819 .expect("受控条件应合法");
7820 let mut generator = SqlGenerator::new();
7821 generator
7822 .build_insert_from_select("archive", &[field!("id"), field!("name")], &source)
7823 .expect("INSERT ... SELECT 应可渲染");
7824 assert_eq!(
7825 generator.get_sql(),
7826 "INSERT INTO \"archive\" (\"id\", \"name\") \
7827 SELECT \"id\", \"name\" FROM \"users\" WHERE \"active\" = $1"
7828 );
7829 }
7830
7831 #[test]
7832 fn update_from_renders_join_predicate_in_where() {
7833 let mut generator = SqlGenerator::new();
7834 generator
7835 .build_update_from(
7836 "users",
7837 &serde_json::json!({"name": "x"}),
7838 &HashMap::new(),
7839 &[],
7840 &[Condition::Eq(
7841 "profiles.active".to_string(),
7842 SqlValue::Bool(true),
7843 )],
7844 &crate::TableRef::new("profiles").expect("合法表名"),
7845 field!("users.id"),
7846 field!("profiles.user_id"),
7847 )
7848 .expect("UPDATE ... FROM 应可渲染");
7849 assert_eq!(
7850 generator.get_sql(),
7851 "UPDATE \"users\" SET \"name\" = $1 FROM \"profiles\" \
7852 WHERE (\"profiles\".\"active\" = $2 AND \"users\".\"id\" = \"profiles\".\"user_id\")"
7853 );
7854 }
7855
7856 #[test]
7857 fn delete_using_renders_join_predicate_in_where() {
7858 let mut generator = SqlGenerator::new();
7859 generator
7860 .build_delete_using(
7861 "users",
7862 &[Condition::Eq(
7863 "users.active".to_string(),
7864 SqlValue::Bool(false),
7865 )],
7866 &crate::TableRef::new("profiles").expect("合法表名"),
7867 field!("users.id"),
7868 field!("profiles.user_id"),
7869 )
7870 .expect("DELETE ... USING 应可渲染");
7871 assert_eq!(
7872 generator.get_sql(),
7873 "DELETE FROM \"users\" USING \"profiles\" \
7874 WHERE (\"users\".\"active\" = $1 AND \"users\".\"id\" = \"profiles\".\"user_id\")"
7875 );
7876 }
7877
7878 #[test]
7879 fn merge_renders_matched_update_and_not_matched_insert() {
7880 let mut generator = SqlGenerator::new();
7881 generator
7882 .build_merge(
7883 "users",
7884 &crate::TableRef::new("incoming").expect("合法表名"),
7885 field!("id"),
7886 field!("id"),
7887 &[field!("name"), field!("email")],
7888 )
7889 .expect("MERGE 应可渲染");
7890 assert_eq!(
7891 generator.get_sql(),
7892 "MERGE INTO \"users\" USING \"incoming\" ON \"users\".\"id\" = \"incoming\".\"id\" \
7893 WHEN MATCHED THEN UPDATE SET \"name\" = \"incoming\".\"name\", \
7894 \"email\" = \"incoming\".\"email\" \
7895 WHEN NOT MATCHED THEN INSERT (\"name\", \"email\") \
7896 VALUES (\"incoming\".\"name\", \"incoming\".\"email\")"
7897 );
7898 }
7899
7900 #[test]
7901 fn merge_renders_matched_condition_and_by_source_delete() {
7902 let mut generator = SqlGenerator::new();
7903 generator
7904 .build_merge_with_options(
7905 "users",
7906 &crate::TableRef::new("incoming").expect("合法表名"),
7907 field!("id"),
7908 field!("id"),
7909 &[field!("name")],
7910 &MergeOptions::default()
7911 .matched_condition(Condition::Eq(
7912 "users.name".to_string(),
7913 SqlValue::String("x".to_string()),
7914 ))
7915 .not_matched_by_source(MergeNotMatchedBySource::Delete),
7916 )
7917 .expect("带条件的 MERGE 应可渲染");
7918 assert_eq!(
7919 generator.get_sql(),
7920 "MERGE INTO \"users\" USING \"incoming\" ON \"users\".\"id\" = \"incoming\".\"id\" \
7921 WHEN MATCHED AND \"users\".\"name\" = $1 THEN UPDATE SET \"name\" = \"incoming\".\"name\" \
7922 WHEN NOT MATCHED THEN INSERT (\"name\") VALUES (\"incoming\".\"name\") \
7923 WHEN NOT MATCHED BY SOURCE THEN DELETE"
7924 );
7925 assert!(matches!(generator.get_params(), [SqlValue::String(x)] if x == "x"));
7926 }
7927
7928 #[test]
7929 fn merge_renders_by_source_update_with_controlled_expr() {
7930 let mut generator = SqlGenerator::new();
7931 generator
7932 .build_merge_with_options(
7933 "users",
7934 &crate::TableRef::new("incoming").expect("合法表名"),
7935 field!("id"),
7936 field!("id"),
7937 &[field!("name")],
7938 &MergeOptions::default().not_matched_by_source(MergeNotMatchedBySource::Update(
7939 vec![(field!("updated_at").clone(), crate::SqlExpr::now())],
7940 )),
7941 )
7942 .expect("BY SOURCE UPDATE 应可渲染");
7943 assert!(
7944 generator
7945 .get_sql()
7946 .ends_with("WHEN NOT MATCHED BY SOURCE THEN UPDATE SET \"updated_at\" = now()"),
7947 "{}",
7948 generator.get_sql()
7949 );
7950 }
7951
7952 #[test]
7953 fn merge_by_source_update_rejects_empty_assignments() {
7954 let mut generator = SqlGenerator::new();
7955 let error = generator
7956 .build_merge_with_options(
7957 "users",
7958 &crate::TableRef::new("incoming").expect("合法表名"),
7959 field!("id"),
7960 field!("id"),
7961 &[field!("name")],
7962 &MergeOptions::default()
7963 .not_matched_by_source(MergeNotMatchedBySource::Update(vec![])),
7964 )
7965 .expect_err("空赋值列表必须报错");
7966 assert!(matches!(error, crate::error::DbError::InvalidArgument(_)));
7967 }
7968
7969 #[test]
7970 fn array_field_type_converts_json_array_recursively() {
7971 let mut field_types = HashMap::new();
7972 field_types.insert(
7973 "tags".to_string(),
7974 FieldType::Array(Box::new(FieldType::Text)),
7975 );
7976 let mut generator = SqlGenerator::new();
7977 generator
7978 .build_insert(
7979 "docs",
7980 &serde_json::json!({"tags": ["a", "b"]}),
7981 &field_types,
7982 &[],
7983 )
7984 .expect("数组字段插入应可渲染");
7985 assert_eq!(
7986 generator.get_sql(),
7987 "INSERT INTO \"docs\" (\"tags\") VALUES ($1)"
7988 );
7989 assert!(matches!(
7990 generator.get_params(),
7991 [SqlValue::Array(values)]
7992 if matches!(values.as_slice(), [SqlValue::String(a), SqlValue::String(b)] if a == "a" && b == "b")
7993 ));
7994
7995 let mut generator = SqlGenerator::new();
7997 assert!(matches!(
7998 generator.build_insert(
7999 "docs",
8000 &serde_json::json!({"tags": "not-an-array"}),
8001 &field_types,
8002 &[]
8003 ),
8004 Err(crate::error::DbError::TypeConversionError(_))
8005 ));
8006
8007 let mut field_types = HashMap::new();
8009 field_types.insert(
8010 "ips".to_string(),
8011 FieldType::Array(Box::new(FieldType::Inet)),
8012 );
8013 let mut generator = SqlGenerator::new();
8014 generator
8015 .build_insert(
8016 "docs",
8017 &serde_json::json!({"ips": ["10.0.0.1"]}),
8018 &field_types,
8019 &[],
8020 )
8021 .expect("INET[] 插入应可渲染");
8022 assert_eq!(
8023 generator.get_sql(),
8024 "INSERT INTO \"docs\" (\"ips\") VALUES ($1::inet[])"
8025 );
8026 assert!(matches!(
8027 generator.get_params(),
8028 [SqlValue::Array(values)] if matches!(values.as_slice(), [SqlValue::Inet(ip)] if ip == "10.0.0.1")
8029 ));
8030 }
8031
8032 #[test]
8034 fn empty_and_all_null_boolean_arrays_bind_as_bool_array() {
8035 let mut field_types = HashMap::new();
8036 field_types.insert(
8037 "flags".to_string(),
8038 FieldType::Array(Box::new(FieldType::Bool)),
8039 );
8040 for value in [serde_json::json!([]), serde_json::json!([null, null])] {
8041 let mut generator = SqlGenerator::new();
8042 generator
8043 .build_insert(
8044 "docs",
8045 &serde_json::json!({"flags": value}),
8046 &field_types,
8047 &[],
8048 )
8049 .expect("布尔数组插入应可渲染");
8050 match generator.get_params() {
8051 [SqlValue::TypedArray {
8052 element_type,
8053 values,
8054 }] => {
8055 assert_eq!(
8056 *element_type, "bool",
8057 "空/全 NULL 布尔数组必须按 bool[] 绑定"
8058 );
8059 assert!(values.iter().all(|value| matches!(value, SqlValue::Null)));
8060 }
8061 other => panic!("期望 TypedArray(bool),实得 {other:?}"),
8062 }
8063 }
8064 }
8065
8066 #[test]
8067 fn network_field_types_accept_text_literals() {
8068 let mut field_types = HashMap::new();
8069 field_types.insert("ip".to_string(), FieldType::Inet);
8070 field_types.insert("mac".to_string(), FieldType::MacAddr);
8071 let mut generator = SqlGenerator::new();
8072 generator
8073 .build_insert(
8074 "hosts",
8075 &serde_json::json!({"ip": "10.0.0.1", "mac": "08:00:2b:01:02:03"}),
8076 &field_types,
8077 &[],
8078 )
8079 .expect("网络类型插入应可渲染");
8080 assert_eq!(
8081 generator.get_sql(),
8082 "INSERT INTO \"hosts\" (\"ip\", \"mac\") VALUES ($1::inet, $2::macaddr)"
8083 );
8084 }
8085
8086 #[test]
8087 fn json_predicates_and_array_quantifiers_render() {
8088 let builder = QueryBuilder::new(test_pool(), "docs", false)
8089 .where_json_contains(
8090 field!("data"),
8091 SqlValue::Json(serde_json::json!({"role": "admin"})),
8092 )
8093 .where_json_has_key(field!("data"), "active")
8094 .where_any(field!("id"), vec![1_i64, 2])
8095 .where_all(field!("score"), vec![1.5_f64]);
8096 assert_eq!(
8097 render(&builder),
8098 "SELECT * FROM \"docs\" WHERE ((\"data\")::jsonb @> $1 AND (\"data\")::jsonb ? $2 \
8099 AND \"id\" = ANY($3) AND \"score\" = ALL($4))"
8100 );
8101
8102 let mut generator = SqlGenerator::new();
8103 generator.build_select(&builder).expect("JSON 条件应可渲染");
8104 assert!(matches!(
8105 generator.get_params(),
8106 [SqlValue::Json(_), SqlValue::String(key), SqlValue::Array(ids), SqlValue::Array(scores)]
8107 if key == "active" && ids.len() == 2 && scores.len() == 1
8108 ));
8109 }
8110
8111 #[test]
8112 fn wave2_compare_ops_render_postgres_operators() {
8113 let cases: &[(crate::CompareOp, &str)] = &[
8114 (crate::CompareOp::ILike, "ILIKE"),
8115 (crate::CompareOp::NotILike, "NOT ILIKE"),
8116 (crate::CompareOp::RegexMatch, "~"),
8117 (crate::CompareOp::RegexIMatch, "~*"),
8118 (crate::CompareOp::NotRegexMatch, "!~"),
8119 (crate::CompareOp::NotRegexIMatch, "!~*"),
8120 ];
8121 for (op, operator) in cases {
8122 let builder = QueryBuilder::new(test_pool(), "users", false)
8123 .where_and(field!("name"), *op, "%a%")
8124 .expect("模式匹配条件应合法");
8125 assert_eq!(
8126 render(&builder),
8127 format!("SELECT * FROM \"users\" WHERE \"name\" {operator} $1")
8128 );
8129 }
8130
8131 let builder = QueryBuilder::new(test_pool(), "users", false)
8132 .where_and(field!("name"), crate::CompareOp::IsDistinctFrom, "a")
8133 .expect("NULL-safe 比较应合法");
8134 assert_eq!(
8135 render(&builder),
8136 "SELECT * FROM \"users\" WHERE \"name\" IS DISTINCT FROM $1"
8137 );
8138 let builder = QueryBuilder::new(test_pool(), "users", false)
8139 .where_and(
8140 field!("name"),
8141 crate::CompareOp::IsNotDistinctFrom,
8142 SqlValue::Null,
8143 )
8144 .expect("NULL-safe NULL 比较应合法");
8145 assert_eq!(
8146 render(&builder),
8147 "SELECT * FROM \"users\" WHERE \"name\" IS NOT DISTINCT FROM NULL"
8148 );
8149
8150 let result = QueryBuilder::new(test_pool(), "users", false).where_and(
8152 field!("name"),
8153 crate::CompareOp::ILike,
8154 42,
8155 );
8156 assert!(matches!(
8157 result,
8158 Err(crate::error::DbError::UnsupportedOperator(_))
8159 ));
8160 }
8161
8162 #[test]
8163 fn wave2_returning_columns_aliases_and_validation() {
8164 let builder = QueryBuilder::new(test_pool(), "users", false)
8165 .returning_columns(&[field!("id"), field!("email")]);
8166 assert_eq!(
8167 builder.returning_clause().unwrap(),
8168 " RETURNING \"id\", \"email\""
8169 );
8170
8171 let builder = QueryBuilder::new(test_pool(), "users", false)
8172 .returning_column_as(field!("id"), field!("user_id"))
8173 .returning_column_as(field!("email"), field!("mail"));
8174 assert_eq!(
8175 builder.returning_clause().unwrap(),
8176 " RETURNING \"id\" AS \"user_id\", \"email\" AS \"mail\""
8177 );
8178
8179 assert_eq!(
8180 QueryBuilder::new(test_pool(), "users", false)
8181 .returning_all()
8182 .returning_clause()
8183 .unwrap(),
8184 " RETURNING *"
8185 );
8186
8187 let mut invalid = QueryBuilder::new(test_pool(), "users", false);
8189 invalid.returning = ReturningTarget::Columns(vec![ReturningColumn {
8190 column: "id".to_string(),
8191 alias: Some("bad alias".to_string()),
8192 }]);
8193 assert!(matches!(
8194 invalid.returning_clause(),
8195 Err(crate::error::DbError::InvalidArgument(_))
8196 ));
8197 assert!(matches!(
8198 QueryBuilder::new(test_pool(), "users", false)
8199 .returning_columns(&[])
8200 .returning_clause(),
8201 Err(crate::error::DbError::InvalidArgument(_))
8202 ));
8203 }
8204
8205 #[test]
8206 fn wave2_conflict_predicate_and_unspecified_target() {
8207 let data = serde_json::json!({"email": "a@x", "active": true});
8208 let conflict = ConflictTarget::Columns(vec!["email".to_string()]);
8209 let mut generator = SqlGenerator::new();
8210 generator
8211 .build_upsert(
8212 "users",
8213 &data,
8214 &HashMap::new(),
8215 &conflict,
8216 Some("active"),
8217 false,
8218 None,
8219 &[],
8220 )
8221 .expect("带谓词的 UPSERT 应可渲染");
8222 assert_eq!(
8223 generator.get_sql(),
8224 "INSERT INTO \"users\" (\"active\", \"email\") VALUES ($1, $2) \
8225 ON CONFLICT (\"email\") WHERE (active) DO UPDATE SET \"active\" = EXCLUDED.\"active\""
8226 );
8227
8228 for payload in ["active; DROP TABLE users", "active -- x", "active /* x */"] {
8229 let mut generator = SqlGenerator::new();
8230 let error = generator
8231 .build_upsert(
8232 "users",
8233 &data,
8234 &HashMap::new(),
8235 &conflict,
8236 Some(payload),
8237 false,
8238 None,
8239 &[],
8240 )
8241 .expect_err("注入型谓词必须被拒绝");
8242 assert!(matches!(error, crate::error::DbError::InvalidArgument(_)));
8243 }
8244
8245 let mut generator = SqlGenerator::new();
8247 let error = generator
8248 .build_upsert(
8249 "users",
8250 &data,
8251 &HashMap::new(),
8252 &ConflictTarget::Unspecified,
8253 None,
8254 false,
8255 None,
8256 &[],
8257 )
8258 .expect_err("无冲突目标的 DO UPDATE 必须被拒绝");
8259 assert!(matches!(error, crate::error::DbError::InvalidArgument(_)));
8260
8261 let mut generator = SqlGenerator::new();
8262 generator
8263 .build_upsert(
8264 "users",
8265 &data,
8266 &HashMap::new(),
8267 &ConflictTarget::Unspecified,
8268 None,
8269 true,
8270 None,
8271 &[],
8272 )
8273 .expect("无冲突目标的 DO NOTHING 应可渲染");
8274 assert!(generator.get_sql().ends_with("ON CONFLICT DO NOTHING"));
8275 }
8276
8277 #[test]
8278 fn wave2_insert_batch_shrinks_by_column_count() {
8279 assert_eq!(derive_insert_batch_size(0, 500), 500);
8280 assert_eq!(derive_insert_batch_size(1, 500), 500);
8281 assert_eq!(derive_insert_batch_size(1_000, 500), 65);
8282 assert_eq!(derive_insert_batch_size(100_000, 500), 1);
8283 }
8284
8285 #[test]
8286 fn wave2_merge_version_gate() {
8287 assert!(!merge_version_supported(149_999));
8288 assert!(!merge_version_supported(140_000));
8289 assert!(merge_version_supported(150_000));
8290 assert!(merge_version_supported(160_015));
8291 }
8292
8293 #[test]
8294 fn wave2_row_lock_variants_and_combinations() {
8295 let builder = QueryBuilder::new(test_pool(), "users", false);
8296 assert_eq!(
8297 builder
8298 .try_to_sql_with_lock(crate::RowLock::NoKeyUpdate, &[field!("users")], false, true,)
8299 .unwrap(),
8300 "SELECT * FROM \"users\" FOR NO KEY UPDATE OF \"users\" SKIP LOCKED"
8301 );
8302 assert_eq!(
8303 builder
8304 .try_to_sql_with_lock(crate::RowLock::KeyShare, &[], true, false)
8305 .unwrap(),
8306 "SELECT * FROM \"users\" FOR KEY SHARE NOWAIT"
8307 );
8308 assert_eq!(
8309 builder
8310 .try_to_sql_with_lock(crate::RowLock::ForUpdateSkipLocked, &[], false, false)
8311 .unwrap(),
8312 "SELECT * FROM \"users\" FOR UPDATE SKIP LOCKED"
8313 );
8314 assert!(matches!(
8315 builder.try_to_sql_with_lock(crate::RowLock::ForUpdate, &[], true, true),
8316 Err(crate::error::DbError::InvalidArgument(_))
8317 ));
8318 assert!(matches!(
8320 builder.try_to_sql_with_lock(crate::RowLock::ForUpdateSkipLocked, &[], true, false),
8321 Err(crate::error::DbError::InvalidArgument(_))
8322 ));
8323 }
8324
8325 #[test]
8328 fn insert_pk_decode_rejects_negative_but_keeps_zero_and_positive() {
8329 assert_eq!(integer_pk_from_i64(0).expect("零值主键应可解码").0, 0);
8330 assert_eq!(integer_pk_from_i64(42).expect("正数主键应可解码").0, 42);
8331 let error = match integer_pk_from_i64(-5) {
8332 Err(error) => error,
8333 Ok(_) => panic!("负值主键必须被拒绝"),
8334 };
8335 assert!(
8336 error.to_string().contains(NEGATIVE_PK_DECODE_PREFIX),
8337 "负值错误应携带专属前缀: {error}"
8338 );
8339 assert!(error.to_string().contains("-5"), "错误应保留负值: {error}");
8340 }
8341
8342 #[test]
8344 fn insert_maps_negative_pk_to_distinct_invalid_argument() {
8345 let negative_detail = format!("列 0 解码失败: {NEGATIVE_PK_DECODE_PREFIX}: -5");
8346 match map_insert_pk_decode_error(negative_detail) {
8347 crate::error::DbError::InvalidArgument(message) => {
8348 assert!(message.contains("负数"), "文案应指出负数主键: {message}");
8349 assert!(message.contains("-5"), "文案应保留负值: {message}");
8350 assert!(
8351 !message.contains("仅支持整型主键"),
8352 "负值不得被误报为非整型主键: {message}"
8353 );
8354 }
8355 other => panic!("期望 InvalidArgument,实得 {other:?}"),
8356 }
8357
8358 let type_detail = "列 0 解码失败: UUID 不是整型".to_string();
8359 match map_insert_pk_decode_error(type_detail) {
8360 crate::error::DbError::InvalidArgument(message) => {
8361 assert!(message.contains("仅支持整型主键"));
8362 assert!(message.contains("insert_returning"));
8363 }
8364 other => panic!("期望 InvalidArgument,实得 {other:?}"),
8365 }
8366 }
8367}