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