sea_query/query/on_conflict.rs
1use crate::{ConditionHolder, DynIden, Expr, IntoCondition, IntoIden};
2
3#[derive(Debug, Clone, Default, PartialEq)]
4pub struct OnConflict {
5 pub(crate) targets: OnConflictTarget,
6 pub(crate) target_where: ConditionHolder,
7 pub(crate) action: Option<OnConflictAction>,
8 pub(crate) action_where: ConditionHolder,
9}
10
11/// Represents ON CONFLICT (upsert) targets
12///
13/// Targets can be a list of columns or expressions, even mixed, or just a
14/// single constraint name.
15#[derive(Debug, Clone, PartialEq)]
16pub enum OnConflictTarget {
17 /// List of column names or expressions
18 Identifiers(Vec<OnConflictIdentifier>),
19 /// A constraint name
20 Constraint(String),
21}
22
23/// Represents either a column or an expression in the conflict targets
24#[derive(Debug, Clone, PartialEq)]
25pub enum OnConflictIdentifier {
26 /// A column
27 Column(DynIden),
28 /// An expression `(LOWER(column), ...)`
29 Expr(Expr),
30}
31
32/// Represents ON CONFLICT (upsert) actions
33#[derive(Debug, Clone, PartialEq)]
34pub enum OnConflictAction {
35 /// Do nothing
36 DoNothing(Vec<DynIden>),
37 /// Update column value of existing row
38 Update(Vec<OnConflictUpdate>),
39}
40
41/// Represents strategies to update column in ON CONFLICT (upsert) actions
42#[derive(Debug, Clone, PartialEq)]
43pub enum OnConflictUpdate {
44 /// Update column value of existing row with inserting value
45 Column(DynIden),
46 /// Update column value of existing row with expression
47 Expr(DynIden, Expr),
48}
49
50impl Default for OnConflictTarget {
51 fn default() -> Self {
52 OnConflictTarget::Identifiers(vec![])
53 }
54}
55
56impl OnConflict {
57 /// Create a ON CONFLICT expression without target column,
58 /// a special method designed for MySQL
59 pub fn new() -> Self {
60 Default::default()
61 }
62
63 /// Set ON CONFLICT target column
64 pub fn column<C>(column: C) -> Self
65 where
66 C: IntoIden,
67 {
68 Self::columns([column])
69 }
70
71 /// Set ON CONFLICT target columns
72 pub fn columns<I, C>(columns: I) -> Self
73 where
74 C: IntoIden,
75 I: IntoIterator<Item = C>,
76 {
77 Self {
78 targets: OnConflictTarget::Identifiers(
79 columns
80 .into_iter()
81 .map(|c| OnConflictIdentifier::Column(c.into_iden()))
82 .collect(),
83 ),
84 target_where: ConditionHolder::new(),
85 action: None,
86 action_where: ConditionHolder::new(),
87 }
88 }
89
90 /// Set ON CONSTRAINT target constraint name
91 pub fn constraint(constraint: &str) -> Self {
92 Self {
93 targets: OnConflictTarget::Constraint(constraint.to_owned()),
94 target_where: ConditionHolder::new(),
95 action: None,
96 action_where: ConditionHolder::new(),
97 }
98 }
99
100 /// Set ON CONFLICT target expression
101 ///
102 /// # Examples
103 ///
104 /// ```
105 /// use sea_query::{tests_cfg::*, *};
106 ///
107 /// let query = Query::insert()
108 /// .into_table(Glyph::Table)
109 /// .columns([Glyph::Aspect, Glyph::Image])
110 /// .values_panic(["abcd".into(), 3.1415.into()])
111 /// .on_conflict(
112 /// OnConflict::new()
113 /// .expr(Expr::col(Glyph::Id))
114 /// .update_column(Glyph::Aspect)
115 /// .value(Glyph::Image, Expr::val(1).add(2))
116 /// .to_owned(),
117 /// )
118 /// .to_owned();
119 ///
120 /// assert_eq!(
121 /// query.to_string(MysqlQueryBuilder),
122 /// [
123 /// r#"INSERT INTO `glyph` (`aspect`, `image`)"#,
124 /// r#"VALUES ('abcd', 3.1415)"#,
125 /// r#"ON DUPLICATE KEY UPDATE `aspect` = VALUES(`aspect`), `image` = 1 + 2"#,
126 /// ]
127 /// .join(" ")
128 /// );
129 /// assert_eq!(
130 /// query.to_string(PostgresQueryBuilder),
131 /// [
132 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
133 /// r#"VALUES ('abcd', 3.1415)"#,
134 /// r#"ON CONFLICT ("id") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = 1 + 2"#,
135 /// ]
136 /// .join(" ")
137 /// );
138 /// assert_eq!(
139 /// query.to_string(SqliteQueryBuilder),
140 /// [
141 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
142 /// r#"VALUES ('abcd', 3.1415)"#,
143 /// r#"ON CONFLICT ("id") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = 1 + 2"#,
144 /// ]
145 /// .join(" ")
146 /// );
147 /// ```
148 pub fn expr<T>(&mut self, expr: T) -> &mut Self
149 where
150 T: Into<Expr>,
151 {
152 Self::exprs(self, [expr])
153 }
154
155 /// Set multiple target expressions for ON CONFLICT. See [`OnConflict::expr`]
156 pub fn exprs<I, T>(&mut self, exprs: I) -> &mut Self
157 where
158 T: Into<Expr>,
159 I: IntoIterator<Item = T>,
160 {
161 let es = exprs
162 .into_iter()
163 .map(|e| OnConflictIdentifier::Expr(e.into()));
164
165 match self.targets {
166 OnConflictTarget::Identifiers(ref mut ids) => {
167 ids.extend(es);
168 }
169 OnConflictTarget::Constraint(_) => {
170 self.targets = OnConflictTarget::Identifiers(es.collect())
171 }
172 }
173
174 self
175 }
176
177 /// Set ON CONFLICT do nothing.
178 ///
179 /// Please use [`Self::do_nothing_on()`] and provide primary keys if you are using MySQL.
180 ///
181 /// # Examples
182 ///
183 /// ```
184 /// use sea_query::{tests_cfg::*, *};
185 ///
186 /// let query = Query::insert()
187 /// .into_table(Glyph::Table)
188 /// .columns([Glyph::Aspect, Glyph::Image])
189 /// .values_panic(["abcd".into(), 3.1415.into()])
190 /// .on_conflict(
191 /// OnConflict::columns([Glyph::Id, Glyph::Aspect])
192 /// .do_nothing()
193 /// .to_owned(),
194 /// )
195 /// .to_owned();
196 ///
197 /// // Sadly this is not valid today.
198 /// assert_eq!(
199 /// query.to_string(MysqlQueryBuilder),
200 /// [
201 /// r#"INSERT INTO `glyph` (`aspect`, `image`)"#,
202 /// r#"VALUES ('abcd', 3.1415)"#,
203 /// r#"ON DUPLICATE KEY IGNORE"#,
204 /// ]
205 /// .join(" ")
206 /// );
207 /// assert_eq!(
208 /// query.to_string(PostgresQueryBuilder),
209 /// [
210 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
211 /// r#"VALUES ('abcd', 3.1415)"#,
212 /// r#"ON CONFLICT ("id", "aspect") DO NOTHING"#,
213 /// ]
214 /// .join(" ")
215 /// );
216 /// assert_eq!(
217 /// query.to_string(SqliteQueryBuilder),
218 /// [
219 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
220 /// r#"VALUES ('abcd', 3.1415)"#,
221 /// r#"ON CONFLICT ("id", "aspect") DO NOTHING"#,
222 /// ]
223 /// .join(" ")
224 /// );
225 /// ```
226 pub fn do_nothing(&mut self) -> &mut Self {
227 self.action = Some(OnConflictAction::DoNothing(vec![]));
228 self
229 }
230
231 /// Set ON CONFLICT do nothing, but with MySQL specific polyfill.
232 ///
233 /// # Examples
234 ///
235 /// ```
236 /// use sea_query::{tests_cfg::*, *};
237 ///
238 /// let query = Query::insert()
239 /// .into_table(Glyph::Table)
240 /// .columns([Glyph::Aspect, Glyph::Image])
241 /// .values_panic(["abcd".into(), 3.1415.into()])
242 /// .on_conflict(
243 /// OnConflict::columns([Glyph::Id, Glyph::Aspect])
244 /// .do_nothing_on([Glyph::Id])
245 /// .to_owned(),
246 /// )
247 /// .to_owned();
248 ///
249 /// assert_eq!(
250 /// query.to_string(MysqlQueryBuilder),
251 /// [
252 /// r#"INSERT INTO `glyph` (`aspect`, `image`)"#,
253 /// r#"VALUES ('abcd', 3.1415)"#,
254 /// r#"ON DUPLICATE KEY UPDATE `id` = `id`"#,
255 /// ]
256 /// .join(" ")
257 /// );
258 /// assert_eq!(
259 /// query.to_string(PostgresQueryBuilder),
260 /// [
261 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
262 /// r#"VALUES ('abcd', 3.1415)"#,
263 /// r#"ON CONFLICT ("id", "aspect") DO NOTHING"#,
264 /// ]
265 /// .join(" ")
266 /// );
267 /// assert_eq!(
268 /// query.to_string(SqliteQueryBuilder),
269 /// [
270 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
271 /// r#"VALUES ('abcd', 3.1415)"#,
272 /// r#"ON CONFLICT ("id", "aspect") DO NOTHING"#,
273 /// ]
274 /// .join(" ")
275 /// );
276 /// ```
277 pub fn do_nothing_on<C, I>(&mut self, pk_cols: I) -> &mut Self
278 where
279 C: IntoIden,
280 I: IntoIterator<Item = C>,
281 {
282 self.action = Some(OnConflictAction::DoNothing(
283 pk_cols.into_iter().map(IntoIden::into_iden).collect(),
284 ));
285 self
286 }
287
288 /// Set ON CONFLICT update column
289 ///
290 /// # Examples
291 ///
292 /// ```
293 /// use sea_query::{tests_cfg::*, *};
294 ///
295 /// let query = Query::insert()
296 /// .into_table(Glyph::Table)
297 /// .columns([Glyph::Aspect, Glyph::Image])
298 /// .values_panic([
299 /// "abcd".into(),
300 /// 3.1415.into(),
301 /// ])
302 /// .on_conflict(
303 /// OnConflict::columns([Glyph::Id, Glyph::Aspect])
304 /// .update_column(Glyph::Aspect)
305 /// .value(Glyph::Image, Expr::val(1).add(2))
306 /// .to_owned()
307 /// )
308 /// .to_owned();
309 ///
310 /// assert_eq!(
311 /// query.to_string(MysqlQueryBuilder),
312 /// [
313 /// r#"INSERT INTO `glyph` (`aspect`, `image`)"#,
314 /// r#"VALUES ('abcd', 3.1415)"#,
315 /// r#"ON DUPLICATE KEY UPDATE `aspect` = VALUES(`aspect`), `image` = 1 + 2"#,
316 /// ]
317 /// .join(" ")
318 /// );
319 /// assert_eq!(
320 /// query.to_string(PostgresQueryBuilder),
321 /// [
322 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
323 /// r#"VALUES ('abcd', 3.1415)"#,
324 /// r#"ON CONFLICT ("id", "aspect") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = 1 + 2"#,
325 /// ]
326 /// .join(" ")
327 /// );
328 /// assert_eq!(
329 /// query.to_string(SqliteQueryBuilder),
330 /// [
331 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
332 /// r#"VALUES ('abcd', 3.1415)"#,
333 /// r#"ON CONFLICT ("id", "aspect") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = 1 + 2"#,
334 /// ]
335 /// .join(" ")
336 /// );
337 /// ```
338 pub fn update_column<C>(&mut self, column: C) -> &mut Self
339 where
340 C: IntoIden,
341 {
342 self.update_columns([column])
343 }
344
345 /// Set ON CONFLICT update columns
346 ///
347 /// # Examples
348 ///
349 /// ```
350 /// use sea_query::{tests_cfg::*, *};
351 ///
352 /// let query = Query::insert()
353 /// .into_table(Glyph::Table)
354 /// .columns([Glyph::Aspect, Glyph::Image])
355 /// .values_panic([
356 /// 2.into(),
357 /// 3.into(),
358 /// ])
359 /// .on_conflict(
360 /// OnConflict::column(Glyph::Id)
361 /// .update_columns([Glyph::Aspect, Glyph::Image])
362 /// .to_owned(),
363 /// )
364 /// .to_owned();
365 ///
366 /// assert_eq!(
367 /// query.to_string(MysqlQueryBuilder),
368 /// r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (2, 3) ON DUPLICATE KEY UPDATE `aspect` = VALUES(`aspect`), `image` = VALUES(`image`)"#
369 /// );
370 /// assert_eq!(
371 /// query.to_string(PostgresQueryBuilder),
372 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = "excluded"."image""#
373 /// );
374 /// assert_eq!(
375 /// query.to_string(SqliteQueryBuilder),
376 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = "excluded"."image""#
377 /// );
378 /// ```
379 pub fn update_columns<C, I>(&mut self, columns: I) -> &mut Self
380 where
381 C: IntoIden,
382 I: IntoIterator<Item = C>,
383 {
384 let mut update_strats: Vec<OnConflictUpdate> = columns
385 .into_iter()
386 .map(|x| OnConflictUpdate::Column(IntoIden::into_iden(x)))
387 .collect();
388
389 match &mut self.action {
390 Some(OnConflictAction::Update(v)) => {
391 v.append(&mut update_strats);
392 }
393 Some(OnConflictAction::DoNothing(_)) | None => {
394 self.action = Some(OnConflictAction::Update(update_strats));
395 }
396 };
397 self
398 }
399
400 /// Set ON CONFLICT update exprs. Append to current list of expressions.
401 ///
402 /// # Examples
403 ///
404 /// ```
405 /// use sea_query::{tests_cfg::*, *};
406 ///
407 /// let query = Query::insert()
408 /// .into_table(Glyph::Table)
409 /// .columns([Glyph::Aspect, Glyph::Image])
410 /// .values_panic([
411 /// 2.into(),
412 /// 3.into(),
413 /// ])
414 /// .on_conflict(
415 /// OnConflict::column(Glyph::Id)
416 /// .values([(Glyph::Image, Expr::val(1).add(2))])
417 /// .to_owned()
418 /// )
419 /// .to_owned();
420 ///
421 /// assert_eq!(
422 /// query.to_string(MysqlQueryBuilder),
423 /// r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (2, 3) ON DUPLICATE KEY UPDATE `image` = 1 + 2"#
424 /// );
425 /// assert_eq!(
426 /// query.to_string(PostgresQueryBuilder),
427 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "image" = 1 + 2"#
428 /// );
429 /// assert_eq!(
430 /// query.to_string(SqliteQueryBuilder),
431 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "image" = 1 + 2"#
432 /// );
433 /// ```
434 pub fn values<C, I>(&mut self, values: I) -> &mut Self
435 where
436 C: IntoIden,
437 I: IntoIterator<Item = (C, Expr)>,
438 {
439 let mut update_exprs: Vec<OnConflictUpdate> = values
440 .into_iter()
441 .map(|(c, e)| OnConflictUpdate::Expr(c.into_iden(), e))
442 .collect();
443
444 match &mut self.action {
445 Some(OnConflictAction::Update(v)) => {
446 v.append(&mut update_exprs);
447 }
448 Some(OnConflictAction::DoNothing(_)) | None => {
449 self.action = Some(OnConflictAction::Update(update_exprs));
450 }
451 };
452 self
453 }
454
455 /// Set ON CONFLICT update value
456 pub fn value<C, T>(&mut self, col: C, value: T) -> &mut Self
457 where
458 C: IntoIden,
459 T: Into<Expr>,
460 {
461 self.values([(col, value.into())])
462 }
463
464 /// Set target WHERE
465 ///
466 /// # Examples
467 ///
468 /// ```
469 /// use sea_query::{tests_cfg::*, *};
470 ///
471 /// let query = Query::insert()
472 /// .into_table(Glyph::Table)
473 /// .columns([Glyph::Aspect, Glyph::Image])
474 /// .values_panic([
475 /// 2.into(),
476 /// 3.into(),
477 /// ])
478 /// .on_conflict(
479 /// OnConflict::column(Glyph::Id)
480 /// .value(Glyph::Image, Expr::val(1).add(2))
481 /// .target_and_where(Expr::col((Glyph::Table, Glyph::Aspect)).is_null())
482 /// .to_owned()
483 /// )
484 /// .to_owned();
485 ///
486 /// assert_eq!(
487 /// query.to_string(MysqlQueryBuilder),
488 /// r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (2, 3) ON DUPLICATE KEY UPDATE `image` = 1 + 2"#
489 /// );
490 /// assert_eq!(
491 /// query.to_string(PostgresQueryBuilder),
492 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") WHERE "glyph"."aspect" IS NULL DO UPDATE SET "image" = 1 + 2"#
493 /// );
494 /// assert_eq!(
495 /// query.to_string(SqliteQueryBuilder),
496 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") WHERE "glyph"."aspect" IS NULL DO UPDATE SET "image" = 1 + 2"#
497 /// );
498 /// ```
499 pub fn target_and_where(&mut self, other: Expr) -> &mut Self {
500 self.target_cond_where(other)
501 }
502
503 /// Set target WHERE
504 pub fn target_and_where_option(&mut self, other: Option<Expr>) -> &mut Self {
505 if let Some(other) = other {
506 self.target_cond_where(other);
507 }
508 self
509 }
510
511 /// Set target WHERE
512 pub fn target_cond_where<C>(&mut self, condition: C) -> &mut Self
513 where
514 C: IntoCondition,
515 {
516 self.target_where.add_condition(condition.into_condition());
517 self
518 }
519
520 /// Set action WHERE
521 ///
522 /// # Examples
523 ///
524 /// ```
525 /// use sea_query::{tests_cfg::*, *};
526 ///
527 /// let query = Query::insert()
528 /// .into_table(Glyph::Table)
529 /// .columns([Glyph::Aspect, Glyph::Image])
530 /// .values_panic([
531 /// 2.into(),
532 /// 3.into(),
533 /// ])
534 /// .on_conflict(
535 /// OnConflict::column(Glyph::Id)
536 /// .value(Glyph::Image, Expr::val(1).add(2))
537 /// .action_and_where(Expr::col((Glyph::Table, Glyph::Aspect)).is_null())
538 /// .to_owned()
539 /// )
540 /// .to_owned();
541 ///
542 /// assert_eq!(
543 /// query.to_string(MysqlQueryBuilder),
544 /// r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (2, 3) ON DUPLICATE KEY UPDATE `image` = 1 + 2"#
545 /// );
546 /// assert_eq!(
547 /// query.to_string(PostgresQueryBuilder),
548 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "image" = 1 + 2 WHERE "glyph"."aspect" IS NULL"#
549 /// );
550 /// assert_eq!(
551 /// query.to_string(SqliteQueryBuilder),
552 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "image" = 1 + 2 WHERE "glyph"."aspect" IS NULL"#
553 /// );
554 /// ```
555 pub fn action_and_where(&mut self, other: Expr) -> &mut Self {
556 self.action_cond_where(other)
557 }
558
559 /// Set action WHERE
560 pub fn action_and_where_option(&mut self, other: Option<Expr>) -> &mut Self {
561 if let Some(other) = other {
562 self.action_cond_where(other);
563 }
564 self
565 }
566
567 /// Set action WHERE
568 pub fn action_cond_where<C>(&mut self, condition: C) -> &mut Self
569 where
570 C: IntoCondition,
571 {
572 self.action_where.add_condition(condition.into_condition());
573 self
574 }
575}